Vlookup หลายไฟล์และหลายชีตในคราวเดียว

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

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

VDO แสดงการใช้ Vlookup ค้นหาจากหลายไฟล์และหลายชีตพร้อมกันในคราวเดียว

Vlookup หลายชีตพร้อมกันในคราวเดียว

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

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

VDO แสดงการค้นหาข้อมูลจากหลายชีตพร้อมกันในคราวเดียว

Vlookup หาค่าจากบรรทัดด้านบน

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

VDO แสดงการใช้ Vlookup หาค่าจากบรรทัดด้านบน

Vlookup หลายคอลัมน์พร้อมกันในคราวเดียว

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

VDO แสดงการค้นหาข้อมูลจากหลายคอลัมน์พร้อมกันในคราวเดียว

Vlookup จากขวาไปซ้าย

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

VDO แสดงการประยุกต์ใช้ Vlookup จากขวาไปซ้าย

เมื่อ Vlookup แล้วได้ค่าผิดพลาด #N/A

เมื่อ Vlookup แล้วได้ค่าผิดพลาด #N/A แสดงว่าค่าที่เรานำไป Lookup นั้นไม่มีอยู่ในคอลัมน์แรกของตารางที่เราใช้ในสูตร Vlookup VDO ด้านล่างนี้อธิบายสาเหตุของปัญหาดังกล่าวพร้อมทั้งแสดงวิธีการตรวจสอบและแก้ไขปัญหาเฉพาะหน้าหาก Vlookup แล้วได้ค่าผิดพลาด #N/A ซึ่งจะทำให้เราสามารถตรวจสอบความผิดพลาดได้ง่าย เร็ว สะดวกต่อการทำงานเป็นอย่างมาก 

VDO แสดงสาเหตุและการแก้ไขปัญหา Vlookup แล้วเกิด #N/A

การค้นหาข้อมูลจากหลายไฟล์หลายชีตหลายคอลัมน์ด้วย 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

ฟังก์ชั่นพื้นฐานที่ใช้บ่อย

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

แม้จะเป็นระดับการประยุกต์การใช้งานที่ซับซ้อนก็ตาม เราก็ต้องอาศัยฟังก์ชั่นเหล่านี้เป็นหลัก ซึ่งได้แก่
✏ Min หาค่าที่น้อยที่สุด เช่น Min(A1:C10)
✏ Max หาค่าที่มากที่สุด เช่น Max(D8:E20)
✏ Average หาค่าเฉลี่ย เช่น Average(B5:F50)
✏ Count นับว่ามีตัวเลขกี่ตัว เช่น Count(I5:K8)
✏ Counta นับว่าไม่เป็นเซลล์ว่างกี่เซลล์ เช่น Counta(H8:V3)
✏ Countifs นับตามเงื่อนไข เช่น Countifs(A2:A5,"OK",B2:B5,1) เป็นการนับว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Sumifs รวมตามเงื่อนไข เช่น Sumifs(C2:C5,A2:A5,"OK",B2:B5,1) เป็นการรวมค่าใน C2:C5 โดยมีเงื่อนไขว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Left เป็นการตัดอักขระด้านซ้าย เช่น Left(A2,8) เป็นการตัดอักขระด้านซ้ายของเซลล์ A2 มา 8 อักขระ
✏ Right เป็นการตัดอักขระด้านขวา เช่น Right(B2,4) เป็นการตัดอักขระด้านขวาของเซลล์ B2 มา 4 อักขระ
✏ Mid เป็นการตัดอักขระโดยระบุตำแหน่งเริ่มและจำนวนอักขระที่ตัด เช่น Mid(D2,3,4) เป็นการตัดอักขระในเซลล์ D2 โดยเริ่มอักขระที่ 3 มาจำนวน 4 อักขระ
✏ Len เป็นการนับจำนวนอักขระ เช่น Len(C8) เป็นการนับว่าในเซลล์ C8 มีกี่อักขระ
✏ If เป็นการพิจารณาตามเงื่อนไขว่าจริงหรือเท็จ เช่น If(A2=5,"Yes","No") เป็นการพิจารณาว่า A2 เท่ากับ 5 จริงหรือไม่ หากเป็นจริงให้แสดงคำตอบเป็น Yes หากเป็นเท็จให้แสดงคำตอบเป็น No
✏ Vlookup เป็นการค้นหาค่าใด ๆ ในคอลัมน์แรกของตารางแล้วแสดงผลลัพธ์เป็นค่าในคอลัมน์ใด ๆ ของตารางตามที่กำหนด เช่น Vlookup("Finish",B3:F100,2,0) เป็นการค้นหาคำว่า Finish ใน B3:B100 (คอลัมน์แรกของตาราง) แล้วแสดงผลลัพธ์เป็นค่าใน C3:C100 (คอลัมน์ที่ 2 ของตาราง) หากไม่พบคำว่า Finish ใน B3:B100 จะให้ผลลัพธ์เป็น #N/A
✏ Sumproduct เป็นการหาผลรวมของผลคูณ เช่น Sumproduct(A2:A4,B2:B4) เป็นการหาผลรวมของผลคูณของ A2:A4 และ B2:B4 ลักษณะการทำงานคือ A2*B2+A3*B3+A4*B4
✏ Index เป็นการระบุพื้นที่แล้วแสดงผลลัพธ์ในตำแหน่งที่กำหนด เช่น Index(A3:C8,1,3) เป็นการนำบรรทัดที่ 1 คอลัมน์ที่ 3 ของพื้นที่ A3:C8 มาแสดง ค่าผลลัพธ์คือ C3
✏ Match เป็นการค้นหาค่าใด ๆ ในช่วงแล้วแสดงผลลัพธ์เป็นลำดับที่พบ เช่น Match(True,B5:B10,0) เป็นการหาว่าค่า True อยู่ในลำดับที่เท่าไรของช่วง B5:B10 หากไม่พบค่า True จะแสดงผลลัพธ์เป็น #N/A
✏ Indirect เป็นการอ้างอิงโดยอ้อม เช่น B2 มีค่าเท่ากับ 5, A10 มีค่าเท่ากับ B2 เซลล์ใด ๆ คีย์ Indirect("A10") คำตอบจะได้ 5 ลักษณะการทำงานของสูตรคือ หาก่อนว่าค่า A10 คืออะไร เมื่อพบว่า A10 คือ B2 ฟังก์ชั่นนี้จึงแสดงคำตอบที่เป็นค่าใน B2 สังเกตว่าค่าที่จะคีย์ลงไปใน Indirect ได้จะต้องเป็นการอ้างอิงไปยังปลายทางที่เป็นเซลล์หรือช่วงเซลล์เท่านั้น
✏ Offset เป็นการระบุตำแหน่งเซลล์โดยมีจุดเริ่ม ตำแหน่งบรรทัดที่ถัดจากจุดเริ่ม ตำแหน่งคอลัมน์ที่ถัดจากจุดเริ่ม ความสูงของข้อมูล และความกว้างของข้อมูล เช่น Offset(B4,0,0,1,5) เป็นการระบุช่วงข้อมูลโดยเริ่มที่ B4 ตำแหน่งบรรทัดที่ห่างจาก B4 เป็น 0 ตำแหน่งคอลัมน์ที่ห่างจาก B4 เป็น 0 แสดงว่ายังอยู่ที่จุดเริ่มเช่นเดิมคือ B4 ความสูงของข้อมูลเป็น 1 ความกว้างของข้อมูลเป็น 5 แสดงว่าจาก B4 ขยายไปทางขวา 5 คอลัมน์ ผลลัพธ์จะได้เป็นช่วงเซลล์ B4:F4

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

มีปัญหาการใช้งาน Microsoft Excel and VBA สอบถามได้ที่ snasui.com

Revised: December 23, 2017 at 12:04

การค้นหาข้อมูลที่สัมพันธ์กันแต่บรรทัดไม่ตรงกัน

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

ตัวอย่างตามด้านล่างจะเห็นว่า Line 3 ของแต่ละ Product ไม่ได้อยู่ในตำแหน่งเดียวกัน แต่เราสามารถที่จะ Lookup ค่ามาแสดงได้ตามตาราง E1:G5

ภาพตัวอย่างประกอบการหาข้อมูลที่สัมพันธ์กันแต่บรรทัดไม่ตรงกัน

การทำเช่นนี้ เราสามารถเขียนสูตรที่ G2 ตามด้านล่างครับ

=VLOOKUP(F2,OFFSET($B$2,MATCH($E2,$A$2:$A$13,0)-1,0,5,2),2,0)

Enter > Copy ลงด้านล่าง

การเปลี่ยนค่าผิดพลาด #N/A เป็นค่าใด ๆ ตามต้องการ

ฟังก์ชั่นที่ใช้บ่อยที่สุด Top Hit ติด Chart ฟังก์ชั่นหนึ่งคือ Vlookup ซึ่งเป็นการ Lookup ข้อมูลที่มีค่าตรงกันกับค่าที่ต้องการ แล้วนำข้อมูลที่เกี่ยวข้องมาแสดง และเมื่อ Lookup กันแล้วเมื่อไม่เจอค่าที่ต้องการจะเกิดค่าผิดพลาดขึ้นมาเป็น #N/A

ปัญหาที่ตามมาคือต้องการไม่ให้แสดงค่าเป็น #N/A จะทำอย่างไร เพราะเมื่อติดค่าผิดพลาดเป็น #N/A แล้วจะทำให้นำไปคำนวณต่อไม่ได้

อันที่จริงแล้วการจัดการไม่ให้แสดงผล #N/A มีหลายวิธี หรือแม้จะติดค่า #N/A มาก็ตามก็จะยังสามารถที่จะใช้งานได้ แต่จะยากมากขึ้น เพื่อให้ทำงานง่ายผมขอแนะนำให้เปลี่ยนค่า #N/A ด้วยฟังก์ชั่น If ดังนี้ครับ

=If(Isna(Vlookup(A1,$C$1:$E$100,3,0)),0,Vlookup(A1,$C$1:$E$100,3,0))

เมื่อ

1. A1 คือค่าที่ต้องการ Lookup
2. $C$1:$E$100 คือตารางฐานข้อมูล
3. 3 คือคอลัมน์ที่ต้องการนำมาแสดงผล
4. 0 คือ รูปแบบการ Lookup ซึ่งเป็นการ Lookup แบบตรงตัว ถ้าเป็น 1 เป็นการ Lookup แบบหาค่าใกล้เคียง

จากสูตรด้านบนหมายความว่า ถ้า Vlookup แล้วเป็นค่าผิดพลาด #N/A ให้แสดงค่า 0 (สามารถเป็นเป็นค่าใด ๆ ตามต้องการ เช่น " ","Not Found" ฯลฯ) ถ้าไม่เป็นค่าผิดพลาดก็ให้นำค่าผลลัพธ์จากการ Vlookup มาแสดง

หรืออีกตัวอย่าง

=If(Isnumber(Match(A1,$C$1:$C$100,0)),Vlookup(A1,$C$1:$E$100,3,0),"")

หมายความว่า ถ้าพบค่า A1 ในช่วงข้อมูล $C$1:$C$100 ก็ให้ทำการ Vlookup ค่ามาให้ ถ้าไม่พบก็ให้แสดงค่าว่าง

Note:

1. ฟังก์ชั่น Match จะแสดงลำดับที่พบข้อมูล ถ้าไม่พบจะแสดงค่า #N/A
2. ฟังก์ชั่น Isnumber จะเป็นการตรวจสอบว่าค่าที่ได้จากการ Match ว่าเป็นตัวเลขหรือไม่

ประเภทข้อมูลใน Excel

สำหรับ Excel แล้ว สิ่งที่ผู้ใช้จำเป็นต้องทราบเกี่ยวกับประเภทข้อมูลใน  Excel คือ

1. Excel รองรับข้อมูลแบบใดบ้าง
2. การวางตำแหน่งภายในเซลล์ของข้อมูลแบบต่าง ๆ เป็นอย่างไร

Excel รองรับข้อมูลแบบใดบ้าง

ข้อมูลที่คีย์ลงใน Excel สามารถมีได้ 4 รูปแบบคือ

1. ข้อมูลประเภทตัวอักษร ได้แก่ตัวหนังสือ เช่น Santipong และตัวเลขที่คีย์เป็นรูปแบบของตัวหนังสือ เช่น 1-23453-6 เป็นต้น
2. ข้อมูลประเภทตัวเลข ได้แก่ตัวเลขต่าง ๆ รวมทั้ง วันที่และเวลา
3. ข้อมูลประเภทค่าตรรกะ เช่น True, False
4. ข้อมูลประเภทค่าผิดพลาด เช่น #N/A, #Ref!, #Div0/! เป็นต้น

การวางตำแหน่งภายในเซลล์ของข้อมูลแบบต่าง ๆ เป็นอย่างไร

หากเรายังไม่ทำการจัดรูปแบบใด ๆ การจัดวางตำแหน่งภายในเซลล์ของข้อมูลประเภทต่าง ๆ ตามข้างต้น จะเป็นดังตารางด้านล่าง

ประเภทข้อมูล	การวางตำแหน่งในเซลล์	ตัวอย่าง
ข้อมูลประเภทตัวอักษร	ชิดซ้ายเซลล์	Santipong
ข้อมูลประเภทตัวเลข	ชิดขวาเซลล์	1000
		30/11/2010
		6:59
ข้อมูลประเภทค่าตรรกะ	อยู่ตรงกลางเซลล์	True
ข้อมูลประเภทค่าผิดพลาด	อยู่ตรงกลางเซลล์	#Div/0!

การที่เราทราบว่าข้อมูลแต่ละประเภทวางตรงตำแหน่งใดของเซลล์มีความสำคัญมาก เนื่องมาจากว่าหากเรานำข้อมูลเหล่านี้ไปเข้าสูตรหรือฟังก์ชั่นการค้นหาต่าง ๆ เพื่อใช้ค้นหาข้อมูลจากแหล่งอื่น ๆ เราสามารถจะค้นหาข้อผิดพลาดได้ง่าย

ยกตัวอย่างเช่น เรานำข้อมูลที่เป็นตัวอักษรไปค้นหาข้อมูลด้วยฟังก์ชั่น Vlookup หรือ Match ที่มีรูปแบบเป็นตัวเลขจะได้ค่าผิดพลาด #N/A แสดงว่าหาไม่พบ เนื่องจากเป็นข้อมูลต่างประเภทกัน ปกติการค้นหาข้อมูลจะต้องใช้ข้อมูลแบบเดียวกันถึงจะสามารถค้นหากันได้

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

สิ่งสำคัญที่จะลืมไปเสียไม่ได้ในการเริ่มทำงานใด ๆ กับ Excel คือเรื่องการจัดรูปแบบต่าง ๆ ควรจะทำหลังสุด หลังจากได้มีการทำเรื่องอื่น ๆ เรียบร้อยแล้ว เพื่อจะได้ไม่เป็นประเด็นให้ต้องทำงานซ้ำ ๆ

การใช้ Vlookup

ฟังก์ชั่น Vlookup เป็นฟังก์ชั่นหนึ่งที่ใช้บ่อยมาก แต่ผู้ที่ใช้ส่วนมากก็ยังไม่เข้าใจว่าทำงานอย่างไร อ่านใน Help แล้วก็ยังไม่เคลียร์ เมื่อเกิดปัญหาก็เลยไม่สามารถแก้ไขได้

มาดูส่วนประกอบหรือไวยากรณ์ของ Vlookup กันก่อนครับ Vlookup มีไวยากรณ์ดังนี้

Vlookup(lookup_value,table_array,col_index_num,range_lookup)

หรือแปลตามแบบฉบับของผมเพื่อให้ง่ายต่อการเข้าใจ

Vlookup(ค่าที่ต้องการค้นหา, ตารางที่บรรจุค่าที่ต้องการค้นหา, คอลัมน์ที่ต้องการแสดงผล, รูปแบบการค้นหา)

 ซึ่งจะเห็นว่า Vlookup มีส่วนประกอบ 4 ส่วนครับ คือ

1. ค่าที่ต้องการค้นหา เป็นค่าใด ๆ ก็ได้ทั้งนั้น
2. ตารางที่บรรจุค่าที่ต้องการค้นหา แน่นอนครับว่าต้องเป็นตารางและคอลัมน์แรกของตารางต้องมีค่าที่ต้องการค้นหาในข้อ 1. อยู่ด้วย (แต่ไม่เสมอไป ขึ้นอยู่กับรูปแบบการค้นหาซึ่งเป็นส่วนประกอบสุดท้ายของฟังก์ชั่น)
   ตารางที่บรรจุค่าที่ต้องการค้นหา เป็นตารางที่มีคอลัมน์เดียวได้หรือไม่ คำตอบคือ ได้ นั่นคือมีแต่เฉพาะค่าที่ต้องการค้นหาเท่านั้น
3. คอลัมน์ที่ต้องการแสดงผล หมายถึง ลำดับของคอลัมน์ในตารางที่บรรจุผลลัพธ์(ปกติคำว่าตารางมักจะมากกว่า 1 คอลัมน์และคอลัมน์ผลลัพธ์เป็นคอลัมน์ใด ๆ ก็ได้ในตารางแล้วแต่ความต้องการ) และที่สำคัญคอลัมน์ที่บรรจุค่าที่ต้องการค้นหานั้นต้องนับเป็นคอลัมน์แรกหรือคอลัมน์ที่ 1 เสมอ
4. รูปแบบการค้นหา มี แค่ 2 รูปแบบเท่านั้น คือ หาค่าแบบตรงตัว และ หาค่าแบบใกล้เคียง

การหาค่าแบบตรงตัว จะใช้ False หรือ 0 ในสูตร การหาค่าแบบใกล้เคียง จะใช้ True หรือ 1 หรือ ปล่อยว่าง

 รูปแบบสูตรหาค่าตรงตัวจะได้เป็น

=Vlookup(ค่าที่ต้องการหา, ตารางที่บรรจุค่าที่ต้องการหา, คอลัมน์ที่บรรจุผลลัพธ์, False) หรือ

=Vlookup(ค่าที่ต้องการหา, ตารางที่บรรจุค่าที่ต้องการหา, คอลัมน์ที่บรรจุผลลัพธ์, 0)

เช่น

=Vlookup(A2,Sheet2!B5:D100,3,False)

หมายความว่า ให้หาค่าที่เท่ากับ A2 ในช่วงข้อมูล B5:B100 ของ Sheet2 แล้วนำค่าในคอลัมน์ที่ 3 ของตาราง B5:D100 (ซึ่งอยู่ในบรรทัดเดียวกัน) มาแสดง นั่นคือ นำค่าในคอลัมน์ D มาแสดง ( 1 คือคอลัมน์ B, 2 คือคอลัมน์ C, 3 คือคอลัมน์ D)

 รูปแบบสูตรหาค่าใกล้เคียงจะได้เป็น

=Vlookup(ค่าที่ต้องการหา, ตารางที่บรรจุค่าที่ต้องการหา, คอลัมน์ที่บรรจุผลลัพธ์, True) หรือ

=Vlookup(ค่าที่ต้องการหา, ตารางที่บรรจุค่าที่ต้องการหา, คอลัมน์ที่บรรจุผลลัพธ์, 1) หรือ

=Vlookup(ค่าที่ต้องการหา, ตารางที่บรรจุค่าที่ต้องการหา, คอลัมน์ที่บรรจุผลลัพธ์)

เช่น

=Vlookup(A2,Sheet2!B5:D100,3,True)

หมายความว่า ให้หาค่าที่น้อยกว่าหรือเท่ากับ A2 ในช่วงข้อมูล B5:B100 ของ Sheet2 แล้วนำค่าในคอลัมน์ที่ 3 ของตาราง B5:D100 (ซึ่งอยู่ในบรรทัดเดียวกัน) มาแสดง นั่นคือ นำค่าในคอลัมน์ D มาแสดง ( 1 คือคอลัมน์ B, 2 คือคอลัมน์ C, 3 คือคอลัมน์ D)

การหาค่าแบบใกล้เคียงนี้ จะหาค่าได้อย่างรวดเร็วและจำเป็นต้องเรียงข้อมูลตามคอลัมน์ B (จากตัวอย่าง) จากน้อยไปหามากเสมอ ทั้งสองรูปแบบการค้นหา ถ้าไม่เจอค่าที่ต้องการ จะแสดงค่าผิดพลาดเป็น #N/A

ภาพตัวอย่างการใช้งานฟังก์ชัน Vlookup แบบตรงตัวและแบบใกล้เคียง