การค้นหาข้อมูลจากหลายไฟล์หลายชีตหลายคอลัมน์ด้วย Vlookup

ในกรณีที่เรามีข้อมูลชนิดเดียวกันอยู่หลายหลายชีตในแต่ละชีตมีข้อมูลอยู่หลายคอลัมน์ โดยข้อมูลในแต่ละชีตมีลักษณะที่ไม่เป็นระเบียบ คือข้อมูลแต่ละชุดมีการเยื้องกันไปมาทั้งในแนวบรรทัดและแนวคอลัมน์ เราสามารถใช้ Vlookup มาค้นหาข้อมูลได้ตามบทความนี้ครับ แต่หากต้องการค้นหาข้อมูลจากหลายไฟล์ หลายชีต โดยในแต่ละชีตมีข้อมูลหลายคอลัมน์และจัดวางอย่างไม่เป็นระเบียบ เราก็ยังจะสามารถใช้ Vlookup เข้ามาค้นหาได้อีกเช่นกัน ความซับซ้อนของสูตรจะเพิ่มขึ้นอีกเล็กน้อยเนื่องจากต้องระบุชื่อไฟล์เข้ามาด้วย

ยกตัวอย่างข้อมูลของเราเป็นข้อมูล Product อยู่ใน 3 ไฟล์ แต่ละไฟล์จะมีข้อมูลอยู่ที่ Sheet2 - Sheet4 ประกอบด้วย Product Name, Quantity และ Amount โดยต้องการจะ Lookup ค่า Product ในเซลล์ A4 ของ Sheet1 ของไฟล์หลัก จาก Sheet2 - Sheet4 ของแต่ละไฟล์ โดยให้สามารถค้นหาได้ทุกคอลัมน์ หากค้นพบให้นำข้อมูล Quantity และ Amount มาวางที่ เซลล์ B4, C4 ของ Sheet1 ของไฟล์หลักตามลำดับ ซึ่งได้มีการกำหนดชื่อไฟล์และชื่อชีตที่จะดึงข้อมูลเอาไว้ล่วงหน้าที่เซลล์ H1:I9 ของ Sheet1 ของไฟล์หลัก

ภาพการใช้ Vlookup ดึงข้อมูลที่ไม่เป็นระเบียบจากหลายไฟล์ หลายชีต หลายคอลัมน์มาแสดง

ไฟล์หลัก

ไฟล์ที่ 1

ไฟล์ที่ 2

เราสามารถเขียนสูตรที่เซลล์ B4 ของ Sheet1 ของไฟล์หลัก เพื่อค้นหาค่า Quantity ได้ดังนี้

=VLOOKUP($A4,OFFSET(INDIRECT(INDEX($H$1:$H$9&$I$1:$I$9&"!A2",
MIN(IF(COUNTIF(OFFSET(INDIRECT($H$1:$H$9&$I$1:$I$9&"!A2:A100"),
0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4),ROW($I$1:$I$9)-ROW($I$1)+1)))),
0,MATCH(TRUE,COUNTIF(OFFSET(INDIRECT(INDEX($H$1:$H$9&$I$1:$I$9
&"!A2:A100",MIN(IF(COUNTIF(OFFSET(INDIRECT($H$1:$H$9&$I$1:$I$9
&"!A2:A100"),0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4),ROW($I$1:$I$9)-
ROW($I$1)+1)))),0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4)>0,0)-1,100,3),
COLUMNS($A3:B3),0)

กดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array จากนั้น Copy สูตรไปที่ C4 เพื่อให้แสดงค่า Amount สามารถดาวน์โหลดไฟล์ด้านล่างไปศึกษาได้ตามสะดวกครับ

VlookupMultipleFilesSheetsCols

การค้นหาข้อมูลจากหลายชีตหลายคอลัมน์ที่ไม่เป็นระเบียบด้วย Vlookup

ในกรณีที่เรามีข้อมูลชนิดเดียวกันอยู่หลายชีตและในแต่ละชีตมีข้อมูลอยู่หลายคอลัมน์ โดยมีลักษณะที่ไม่เป็นระเบียบ คือข้อมูลแต่ละชุดมีการเยื้องกันไปมาทั้งในแนวบรรทัดและแนวคอลัมน์ เป็นที่น่ายินดีว่าเราสามารถใช้ Vlookup ค้นหาข้อมูลที่มีลักษณะดังกล่าวได้ แต่การค้นหาข้อมูลลักษณะนี้ จำเป็นจะต้องพึ่งพาสูตรที่มีความซับซ้อนสูงมาก การจะเข้าใจการทำงานของ Vlookup ลักษณะนี้นั้น จำเป็นจะต้องเข้าใจแต่ละฟังก์ชั่นที่ประกอบเข้าด้วยกันเพื่อใช้ดึงข้อมูลโดยละเอียด

ยกตัวอย่างข้อมูลของเราเป็นข้อมูล Product อยู่ใน Sheet2 - Sheet4 ประกอบด้วย Product Name, Quantity และ Amount โดยต้องการจะ Lookup ค่า Product ในเซลล์ A4 ของ Sheet1 จาก Sheet2 - Sheet4 โดยให้สามารถค้นหาได้ทุกคอลัมน์ หากค้นพบให้นำข้อมูล Quantity และ Amount มาวางที่ เซลล์ B4, C4 ของ Sheet1 ตามลำดับ ซึ่งได้มีการกำหนดชื่อชีตที่จะดึงข้อมูลเอาไว้ล่วงหน้าที่เซลล์ I1:I3 ของ Sheet1

ภาพการใช้ Vlookup ดึงข้อมูลที่ไม่เป็นระเบียบจากหลายชีต หลายคอลัมน์มาแสดง

ชีตที่ใช้ Vlookup ดึงข้อมูล

ข้อมูลใน Sheet2

ข้อมูลใน Sheet3

ข้อมูลใน Sheet4

เราสามารถเขียนสูตรที่เซลล์ B4 ของ Sheet1 เพื่อค้นหาค่า Quantity ได้ดังนี้

=VLOOKUP($A4,OFFSET(INDIRECT(INDEX($I$1:$I$3&"!A2",MIN(IF(
COUNTIF(OFFSET(INDIRECT($I$1:$I$3&"!A2:A100"),0,
COLUMN($A$1:$O$1)-COLUMN($A$3)),$A4),ROW($I$1:$I$3)-ROW($I$1)+1)))),
0,MATCH(TRUE,COUNTIF(OFFSET(INDIRECT(
INDEX($I$1:$I$3&"!A2:A100",MIN(IF(COUNTIF(OFFSET(INDIRECT(
$I$1:$I$3&"!A2:A100"),0,COLUMN($A$1:$O$1)-COLUMN($A$3)),$A4),
ROW($I$1:$I$3)-ROW($I$1)+1)))),0,COLUMN($A$1:$O$1)-COLUMN($A$3)),
$A4)>0,0)-1,100,3),COLUMNS($A3:B3),0)

กดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array จากนั้น Copy สูตรไปที่ C4 เพื่อให้แสดงค่า Amount สามารถดาวน์โหลดไฟล์ด้านล่างไปศึกษาได้ตามสะดวกครับ

VlookupMultiSheetsMultiColumns

การนำข้อมูลที่อยู่ในคอลัมน์เดียวไปเรียงหลายคอลัมน์

สำหรับการนำข้อมูลที่เรียงข้อมูลในคอลัมน์เดียวไปเรียงใหม่ให้อยู่ในหลายคอลัมน์ โดยกำหนดได้ว่าแต่ละคอลัมน์มีข้อมูลเพียงกี่บรรทัดเราสามารถใช้สูตรมาจัดการได้ครับ

ตัวอย่างเช่นข้อมูลอยู่ที่ A2:A100 และต้องการเรียงใหม่ที่คอลัมน์ B เริ่มจาก B2 เป็นต้นไปโดยสามารถเลือกได้ว่าแต่ละคอลัมน์จะให้มีข้อมูลเพียงกี่บรรทัด

สามารถทำได้ดังนี้

1. ที่เซลล์ B1 ทำ Validation เพื่อเลือกจำนวนบรรทัดที่ต้องการ
2. ที่เซลล์ B2 คีย์สูตรเพื่อจัดเรียงข้อมูลใหม่
   =IF(ROWS(B$2:B2)>$B$1,"",IFERROR(INDEX($A$2:$A$100,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))),"")
   Enter > Copy ไปด้านขวาและลงด้านล่างตามต้องการ
   
   สูตรข้างต้นใช้กับ Excel Version 2007 เป็นต้นไป ส่วน Excel 2003 สามารถใช้สูตรตามด้านล่างครับ
   =IF(OR(ROWS(B$2:B2)>$B$1,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))>COUNTA($A$2:$A$100)),"",INDEX($A$2:$A$100,ROWS(B$2:B2)+($B$1*(COLUMNS($B2:B2)-1))))
   Enter > Copy ไปทางด้านขวาและลงด้านล่าง

ภาพประกอบการนำข้อมูลที่อยู่ในคอลัมน์เดียวไปเรียงหลายคอลัมน์

การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว

สำหรับข้อมูลที่อยู่ในฟอร์มเดียวกันแต่แยกเป็นหลาย Sheet และต้องการนำมาต่อกันเป็นฐานข้อมูลใน Sheet เดียวกันนั้น สามารถใช้สูตรทำได้ครับ แต่อาจจะมีวิธีการหลายขั้นตอน ดังจะแสดงตามด้านล่าง

ภาพ Sheet ที่ใช้รวมข้อมูล 



ภาพ Sheet ที่ 1 



ภาพ Sheet ที่ 2 



ภาพ Sheet ที่ 3 



จากภาพด้านบนเป็นการนำข้อมูล Sheet1, 2, 3 มาต่อกัน ให้ทำตามลำดับดังนี้ครับ

1. เขียนชื่อชีทเรียงกันไว้ตามต้องการที่ G1:G3
2. เซลล์ H1 คีย์สูตรเพื่อนับว่ามีข้อมูลใน Sheet1 เท่าไร
   =COUNTA(INDIRECT("'"&G1&"'!"&"A2:A65536"))
   Enter แล้ว Copy ลงด้านล่าง
3. เซลล์ H4 รวมจำนวนจากด้านบน คีย์
   =SUM(H1:H3)
   Enter
4. เซลล์ I1 ใช้สูตรในการ Run ข้อมูลใหม่เพื่อใช้ในสูตร Lookup
   =SUM($H$1:H1)-H1+1
   Enter แล้ว Copy ลงด้านล่าง
5. เซลล์ E2 คีย์สูตรเพื่อ List รายชื่อ Sheet
   =IF(ROWS($E$2:E2)>$H$4,"",LOOKUP(ROWS($E$2:E2),$I$1:$I$3,$G$1:$G$3))
   Enter แล้ว Copy ไปด้านล่าง
6. เซลล์ A2 คีย์สูตรเพื่อนำข้อมูลใน Sheet ต่าง ๆ มาแสดงต่อกัน
   =IF($E2<>"",INDIRECT("'"&$E2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($E$2:$E2,$E2)+1),"")
   Enter แล้ว Copy ไปทางขวาและลงด้านล่าง

จะเห็นว่าการนำข้อมูลจากหลาย ๆ Sheet มาต่อกันนั้น ไม่ใช่เรื่องยากอีกต่อไปสำหรับพวกเราครับ