VLOOKUP

 

หากมีข้อมูลที่ทราบในสเปรดชีต คุณสามารถใช้ VLOOKUP เพื่อค้นหาข้อมูลที่เกี่ยวข้องตามแถวได้ เช่น หากต้องการซื้อส้ม คุณสามารถใช้ VLOOKUP เพื่อค้นหาราคา

VLOOKUP สําหรับ BigQuery

การค้นหาแนวตั้ง แสดงผลค่าในคอลัมน์ข้อมูลในตำแหน่งที่พบรายการที่ตรงกันในคอลัมน์การค้นหา

ตัวอย่างการใช้งาน

VLOOKUP("Apple",table_name!fruit,table_name!price)

รูปแบบคำสั่ง

VLOOKUP(search_key, range,index, is_sorted)

• search_key: ค่าที่จะค้นหาในคอลัมน์ค้นหา
• search_column: คอลัมน์ข้อมูลที่จะพิจารณาสําหรับการค้นหา
• result_column: คอลัมน์ข้อมูลที่จะพิจารณาสำหรับผลการค้นหา
• is_sorted: [ไม่บังคับ] ลักษณะในการค้นหารายการที่ตรงกันสําหรับ search_key
  • FALSE: แนะนําให้ใช้รูปแบบคำสั่งนี้สําหรับการจับคู่ที่ตรงกันทั้งหมด
  • TRUE: สําหรับการจับคู่แบบใกล้เคียง ระบบจะใช้รูปแบบคำสั่งนี้เป็นค่าเริ่มต้นนี้หากไม่ได้ระบุ is_sorted
    เคล็ดลับ: ก่อนใช้การจับคู่แบบใกล้เคียง ให้จัดเรียงคำค้นหาตามลําดับจากน้อยไปมาก ไม่เช่นนั้น คุณอาจได้ผลลัพธ์ที่ไม่ถูกต้อง ดูสาเหตุที่อาจทําให้ผลลัพธ์ไม่ถูกต้อง

เคล็ดลับ: ใช้ XLOOKUP หากต้องการให้การค้นหาฐานข้อมูลใน BigQuery มีความยืดหยุ่นมากขึ้น

รูปแบบคำสั่ง

=VLOOKUP(search_key, range, index, [is_sorted])

อินพุต

1. search_key: ค่าที่จะค้นหาในคอลัมน์แรกของช่วง
2. range: ค่าระดับบนและล่างที่พิจารณาสําหรับการค้นหา
3. index: ดัชนีของคอลัมน์ที่มีค่าที่แสดงของช่วง ดัชนีต้องเป็นจำนวนเต็มบวก
4. is_sorted: อินพุตที่ไม่บังคับ เลือกตัวเลือกต่อไปนี้
   • FALSE = การจับคู่ที่ตรงกันทั้งหมด ซึ่งเป็นตัวเลือกที่แนะนําอย่างยิ่ง
   • TRUE = การจับคู่แบบใกล้เคียง โดยระบบจะใช้รูปแบบคำสั่งนี้เป็นค่าเริ่มต้นนี้หากไม่ได้ระบุ is_sorted
     สําคัญ: ก่อนใช้การจับคู่แบบใกล้เคียง ให้จัดเรียงคำค้นหาตามลําดับจากน้อยไปมาก ไม่เช่นนั้น คุณอาจได้ผลลัพธ์ที่ไม่ถูกต้อง ดูสาเหตุที่อาจทําให้ผลลัพธ์ไม่ถูกต้อง

ผลลัพธ์

ค่าที่ตรงกันค่าแรกจากช่วงที่เลือก

รายละเอียดทางเทคนิค

ตัวอย่าง

=VLOOKUP(G9, B4:D8, 3, FALSE)

=VLOOKUP("Apple", B4:D8, 3, TRUE)

อินพุต	คำอธิบาย
search_key	ค่านี้คือค่าที่คุณค้นหาในคอลัมน์แรกของ range หากไม่ต้องการค่าที่ผิดพลาด คำค้นหาต้องอยู่ในคอลัมน์แรกของ range นอกจากนี้ ระบบยังรองรับการอ้างอิงเซลล์ด้วย วิธีการตรวจสอบแบบง่าย: เมื่อ search_key อยู่ที่ B3 range ก็ควรเริ่มต้นด้วยคอลัมน์ B
range	อินพุตนี้คือ range ซึ่งทำงานดังนี้ ฟังก์ชันจะค้นหาคคำค้นหาที่ระบุในคอลัมน์แรก VLOOKUP จะแสดงค่าจากคอลัมน์ที่ระบุโดย index นอกจากนี้คุณยังใช้ช่วงที่ตั้งชื่อแล้วได้เช่นกัน หากไม่ต้องค่าที่ผิดพลาด คำการค้นหาต้องอยู่ในคอลัมน์แรกของ range วิธีการตรวจสอบแบบง่าย: เมื่อ search_key อยู่ที่ B3 range ก็ควรเริ่มต้นด้วยคอลัมน์ B
index	เรียกอีกอย่างว่า "หมายเลขคอลัมน์" โดยข้อมูลนี้คือดัชนีของคอลัมน์ใน range ที่มีค่าผลลัพธ์ ดัชนีที่น้อยที่สุดที่เป็นไปได้คือ 1 ดัชนีที่มากที่สุดที่เป็นไปได้คือจํานวนคอลัมน์สูงสุดใน range นั้น หลังจากตั้งค่าช่วงแล้ว VLOOKUP จะดูเฉพาะคอลัมน์คำค้นหาเมื่อดัชนี = 1 หรือคอลัมน์ที่อยู่ทางขวา เคล็ดลับ: เมื่อใช้ VLOOKUP ให้สมมติว่าคอลัมน์ของ range มีลำดับตัวเลขจากซ้ายไปขวาและเริ่มต้นด้วย 1
is_sorted	อินพุตนี้ไม่บังคับ โดยมีตัวเลือกคือ TRUE และ FALSE หาก is_sorted เป็น TRUE นั้น VLOOKUP จะใช้การจับคู่แบบใกล้เคียง สําคัญ: ก่อนใช้การจับคู่แบบใกล้เคียง ให้จัดเรียงคำค้นหาตามลําดับจากน้อยไปมาก ไม่เช่นนั้น คุณอาจได้ค่าผลลัพธ์ที่ไม่คาดคิด ดูสาเหตุที่อาจทําให้ผลลัพธ์ไม่ถูกต้อง หาก is_sorted เป็น FALSE นั้น VLOOKUP จะใช้การจับคู่ที่ตรงกันทั้งหมด หากไม่ได้ระบุ is_sorted ไว้ ระบบจะกำหนดให้ TRUE เป็นค่าเริ่มต้น เราขอแนะนำให้คุณทำดังนี้ ใช้ FALSE สําหรับ is_sorted ตามลักษณะการทํางานแบบสอดคล้องกัน ไม่ว่าจะจัดเรียงคอลัมน์คำค้นหาหรือไม่ ระบุ is_sorted เสมอเพื่อให้อ่านง่ายขึ้น แม้อินพุตจะเป็นแบบไม่บังคับก็ตาม

 

เอาต์พุต

คำอธิบาย

ผลลัพธ์

เอาต์พุตนี้คือค่าที่ VLOOKUP แสดงตามข้อมูลที่คุณป้อน ฟังก์ชัน VLOOKUP แต่ละรายการจะแสดงผลลัพธ์เพียงค่าเดียว หากมีค่าคำค้นหาหลายรายการที่ตรงกัน ระบบจะแสดงผลค่าในคอลัมน์ผลลัพธ์ซึ่งมีคำค้นหาที่เชื่อมโยงที่ระบบจับคู่เป็นคู่แรกในคอลัมน์คำค้นหา หากผลลัพธ์เป็น #N/A แสดงว่าระบบไม่พบค่า หากคุณพบค่าที่ไม่คาดคิดหรือข้อผิดพลาด เช่น #N/A หรือ #VALUE! ให้เริ่มแก้ปัญหา และหากต้องการแทนที่ #N/A ด้วยค่าอื่น โปรดดูข้อมูลเพิ่มเติมเกี่ยวกับวิธีใช้ IFNA() ใน VLOOKUP()

ตัวอย่าง VLOOKUP พื้นฐาน

VLOOKUP ในคำค้นหาต่างๆ

ใช้ VLOOKUP เพื่อค้นหาราคาของส้ม (Orange) และแอปเปิล (Apple)

ลองเลย

คำอธิบาย

เมื่อใช้ VLOOKUP คุณจะใช้คำค้นหาต่างๆ ได้ เช่น "Apple" และ "Orange"

หากไม่ต้องค่าที่ผิดพลาด คำค้นหาเหล่านี้ต้องอยู่ในคอลัมน์แรกของ range และหากไม่ต้องการป้อนค่าสําหรับคำค้นหา คุณก็ใช้การอ้างอิงเซลล์ เช่น "G9" ได้เช่นกัน

search_key คือ "Orange"	=VLOOKUP("Orange", B4:D8, 3, FALSE) ผลลัพธ์ = $1.01
search_key คือ "Apple"	=VLOOKUP("Apple", B4:D8, 3, FALSE) ผลลัพธ์ = $1.50
search_key ที่ใช้การอ้างอิงเซลล์ของ "Apple" ใน G9	=VLOOKUP(G9, B4:D8, 3, FALSE) ผลลัพธ์ = $1.50

VLOOKUP ในดัชนีคอลัมน์ต่างๆ

ใช้ VLOOKUP เพื่อค้นหาจํานวนส้ม (Orange) ในคอลัมน์ดัชนีที่ 2

ลองเลย

คำอธิบาย

เมื่อใช้ VLOOKUP ให้สมมติว่าคอลัมน์ของ range มีตัวเลขจากซ้ายไปขวาและเริ่มต้นจาก 1 หากต้องการดูข้อมูลเป้าหมาย คุณต้องระบุดัชนีคอลัมน์ เช่น คอลัมน์ที่ 2 สําหรับจํานวน

Index = 2 ค้นหาจํานวนของส้ม (Orange) ซึ่งเป็นคอลัมน์ที่ 2 ของ range

=VLOOKUP(G3, B4:D8, 2, FALSE) ผลลัพธ์ = 5

การจับคู่ที่ตรงกันทั้งหมดหรือการจับคู่แบบใกล้เคียงของ VLOOKUP

• ใช้การจับคู่ที่ตรงกันทั้งหมดของ VLOOKUP เพื่อค้นหารหัสที่ตรงกัน
• ใช้การจับคู่แบบใกล้เคียงของ VLOOKUP เพื่อค้นหารหัสที่ใกล้เคียงกัน

ลองเลย

คำอธิบาย

ใช้การจับคู่แบบใกล้เคียงหรือ is_sorted = TRUE เมื่อค้นหารายการที่ตรงกันมากที่สุด แต่ไม่ใช่รายการที่ตรงกันทั้งหมด

หากต้องการค้นหารหัส = 102 ซึ่งไม่มีอยู่ในตาราง การจับคู่แบบใกล้เคียงจะแสดงผลลัพธ์น้อยกว่าที่ต้องการ 1 ระดับโดยแสดงเป็นรหัส = 101 เนื่องจาก 101 คือค่าที่ใกล้เคียงที่สุดและเป็นค่าที่น้อยกว่า 102 ในคอลัมน์คำค้นหา

การจับคู่แบบใกล้เคียงจะค้นหาในคอลัมน์คำค้นหาจนกว่าจะเจอค่าที่มากกว่าคำที่ค้นหา จากนั้นจะหยุดอยู่ที่แถวก่อนหน้าค่าที่มากกว่าดังกล่าว และแสดงค่าจากคอลัมน์ผลลัพธ์ในแถวนั้น ซึ่งหมายความว่าหากคอลัมน์คำค้นหาไม่ได้จัดเรียงจากน้อยไปมาก คุณอาจได้ผลลัพธ์ที่ไม่ถูกต้อง

สําคัญ: ก่อนใช้การจับคู่แบบใกล้เคียง ให้จัดเรียงคำค้นหาตามลําดับจากน้อยไปมากเพื่อให้ได้ผลลัพธ์ที่ถูกต้อง ไม่เช่นนั้น คุณอาจได้ค่าผลลัพธ์ที่ไม่คาดคิด

เมื่อค้นหาผลลัพธ์ที่ตรงกันทั้งหมด เช่น is_sorted = FALSE ระบบจะแสดงผลลัพธ์ที่ตรงกันทั้งหมด เช่น ชื่อผลไม้สําหรับรหัส = 103 คือ กล้วย (Banana) หากไม่มีรายการที่ตรงกันทั้งหมด คุณจะได้รับข้อผิดพลาด #N/A เราจึงขอแนะนำให้คุณใช้การจับคู่ที่ตรงกันทั้งหมด เนื่องจากคาดเดาลักษณะการทำงานได้ดีกว่า

การจับคู่ที่ตรงกันทั้งหมด	=VLOOKUP(G6, A4:D8, 2, FALSE) ผลลัพธ์ = "Apple"
การจับคู่แบบใกล้เคียง	=VLOOKUP(G3, A4:D8, 2, TRUE) หรือ =VLOOKUP(G3, A4:D8, 2) ผลลัพธ์ = "Banana"

การใช้งาน VLOOKUP ทั่วไป

แทนที่ค่าที่ผิดพลาดจาก VLOOKUP

คุณอาจต้องการแทนที่ค่าที่ผิดพลาดซึ่ง VLOOKUP แสดงผลเมื่อไม่มีคำค้นหาอยู่ ในกรณีนี้หากไม่ต้องการให้ระบบแสดง #N/A ให้ใช้ฟังก์ชัน IFNA() เพื่อแทนที่ #N/A ดูข้อมูลเพิ่มเติมเกี่ยวกับ IFNA()

ลองเลย

เริ่มแรก VLOOKUP แสดงผล #N/A เนื่องจากไม่มีคำค้นหา "Pencil" อยู่ในคอลัมน์ "Fruit" IFNA() จะแทนที่ค่าที่ผิดพลาด #N/A ด้วยอินพุตที่ 2 ที่ระบุในฟังก์ชัน ซึ่งในกรณีของเราคือ "NOT FOUND"

=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND") ผลลัพธ์ = “NOT FOUND”

เคล็ดลับ: หากคุณต้องการแทนที่ค่าที่ผิดพลาดอื่นๆ เช่น #REF! โปรดดูข้อมูลเพิ่มเติมเกี่ยวกับ IFERROR()

VLOOKUP ที่มีหลายเกณฑ์

คุณไม่สามารถใช้ VLOOKUP โดยตรงกับหลายเกณฑ์ได้ ดังนั้นให้สร้างคอลัมน์ Helper ใหม่เพื่อใช้ VLOOKUP โดยตรงกับหลายเกณฑ์เพื่อรวมคอลัมน์ที่มีอยู่หลายรายการเข้าด้วยกัน

ลองเลย

1. คุณสามารถสร้างคอลัมน์ Helper ได้หากใช้ "&" เพื่อรวม First Name และ Last Name ไว้ด้วยกัน	=C4&D4 แล้วลากจาก B4 ลงไปยัง B8 เพื่อสร้างคอลัมน์ Helper
2. ใช้การอ้างอิงเซลล์ B7 "JohnLee" เป็นคำค้นหา	=VLOOKUP(B7, B4:E8, 4, FALSE) ผลลัพธ์ = "Support"

VLOOKUP ที่มีไวลด์การ์ดหรือการจับคู่ที่ตรงกันบางส่วน

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

• เครื่องหมายคําถาม "?" จะจับคู่กับอักขระเดี่ยวตัวใดก็ได้
• เครื่องหมายดอกจัน "*" จะจับคู่กับลําดับอักขระใดก็ได้

หากต้องการใช้ไวลด์การ์ดใน VLOOKUP คุณต้องใช้การจับคู่ที่ตรงกันทั้งหมด: "is_sorted = FALSE"

ลองเลย

"St*" ใช้เพื่อจับคู่กับทุกรายการที่ขึ้นต้นด้วย "St" โดยไม่คํานึงถึงจํานวนอักขระ เช่น "Steve", "St1", "Stock" หรือ "Steeeeeeve"

=VLOOKUP("St*", B4:D8, 3, FALSE) ผลลัพธ์ = "Marketing"

แก้ปัญหาข้อผิดพลาดและแนวทางปฏิบัติแนะนํา

ผลลัพธ์ที่ไม่ถูกต้อง

• แสดงผลลัพธ์ที่ไม่คาดคิด: หากคุณตั้งค่า is_sorted เป็น TRUE แต่คอลัมน์แรกในช่วงไม่ได้จัดเรียงตามลําดับตัวเลขหรือมลําดับตัวอักษรจากน้อยไปมาก ให้เปลี่ยน is_sorted เป็น FALSE

• VLOOKUP ให้ผลลัพธ์ที่ตรงกันรายการแรก: VLOOKUP จะแสดงผลลัพธ์ที่ตรงกันรายการแรกเท่านั้น หากมีคำค้นหาที่ตรงกันหลายรายการ ระบบจะแสดงผลลพธ์ให้ แต่อาจไม่ใช่ค่าที่คาดไว้
• ข้อมูลไม่เป็นระเบียบ: ในบางครั้ง ค่าที่มีเว้นวรรคอยู่หน้าและหลังอาจดูเหมือนกัน แต่ VLOOKUP จะดำเนินการกับ 2 กรณีนี้แตกต่างกัน ตัวอย่างเช่น ค่าต่อไปนี้แตกต่างกันใน VLOOKUP
  • " Apple"
  • "Apple "
  • "Apple"

หากต้องการผลลัพธ์ตามที่คาดไว้ ให้นําเว้นวรรคออกก่อนใช้ VLOOKUP

หากต้องการดูข้อมูลเพิ่มเติม โปรดดูที่ส่วนแนวทางปฏิบัติแนะนําของเรา

#N/A

• หากใช้การจับคู่แบบใกล้เคียงหรือ is_sorted = TRUE และหากคำค้นหาใน VLOOKUP มีค่าน้อยกว่าค่าที่น้อยที่สุดในคอลัมน์แรก ในกรณีนี้ VLOOKUP จะแสดงผลลัพธ์เป็น #N/A
• หากใช้การจับคู่ที่ตรงกันทั้งหมดหรือ is_sorted = FALSE ระบบจะไม่พบรายการที่ตรงกันทั้งหมดของคำค้นหาใน VLOOKUP ในคอลัมน์แรก หากไม่ต้องการให้ผลลัพธ์เป็น #N/A เมื่อระบบไม่พบคำค้นหาในคอลัมน์แรก คุณสามารถใช้ฟังก์ชัน IFNA() ได้

#REF!

คุณอาจระบุ range ด้วยตัวเลขที่มากกว่าจํานวนสูงสุดของคอลัมน์ range โดยไม่ได้ตั้งใจ ดังนั้นเพื่อหลีกเลี่ยงปัญหานี้ โปรดตรวจสอบว่าคุณทําสิ่งต่อไปนี้

• นับคอลัมน์จาก range ที่เลือก ไม่ใช่ทั้งตาราง
• เริ่มนับจาก 1 แทน 0

#VALUE!

หากได้รับข้อผิดพลาด #VALUE! อาจมีสาเหตุดังนี้

• ป้อนข้อความหรือชื่อคอลัมน์ index ไม่ถูกต้อง
• ป้อนตัวเลขที่น้อยกว่า 1 ในส่วน index index ต้องมีค่าอย่างน้อยเท่ากับ 1 และน้อยกว่าจํานวนคอลัมน์สูงสุดของ range VLOOKUP จะสามารถค้นหาเฉพาะในคอลัมน์คำค้นหาเท่านั้น ในกรณีที่ index = 1 หรือคอลัมน์ที่อยู่ทางขวา

สําคัญ: index ยอมรับเฉพาะตัวเลขเท่านั้น

#NAME?

• คุณอาจไม่ได้ยกข้อความมาครบถ้วนในคำค้นหา ในกรณีที่ search_key เป็นข้อความ

แนวทางปฏิบัติแนะนำ

 

สิ่งต้องทำ	เหตุผล
ใช้การอ้างอิงแบบสัมบูรณ์สําหรับ range	คุณควรใช้สิ่งต่อไปนี้ การอ้างอิงแบบสัมบูรณ์สําหรับ VLOOKUP range VLOOKUP(G3, $B$3:$D$7, 3, FALSE) คุณไม่ควรใช้ชุดคำสั่งต่อไปนี้ VLOOKUP(G3, B3:D7, 3, FALSE) เพื่อป้องกันการเปลี่ยนแปลงที่คาดเดาไม่ได้ในส่วน range เมื่อมีการคัดลอกหรือลากลง
จัดเรียงคอลัมน์แรกตามลําดับจากน้อยไปมากเมื่อใช้การจับคู่แบบใกล้เคียง เช่น is_sorted = TRUE	หากใช้การจับคู่แบบใกล้เคียงหรือ is_sorted = TRUE คุณต้องจัดเรียงคอลัมน์แรกตามลําดับจากน้อยไปมาก ไม่เช่นนั้น คุณอาจมีโอกาสได้ผลลัพธ์ที่ไม่ถูกต้อง ดูข้อมูลเพิ่มเติมเกี่ยวกับวิธีจัดเรียง
จัดระเบียบข้อมูลก่อนใช้ VLOOKUP	ก่อนใช้ VLOOKUP อย่าลืมจัดระเบียบข้อมูลของคุณ เนื่องจากข้อมูลที่ไม่เป็นระเบียบอาจทําให้ VLOOKUP แสดงผลค่าที่คาดเดาไม่ได้ ซึ่งข้อผิดพลาดที่พบบ่อยเกี่ยวกับข้อมูลที่ไม่เป็นระเบียบมีดังนี้ เว้นวรรคนําหน้า: " apple" เว้นวรรคตามหลัง: "apple " ช่องว่างหรือเว้นวรรค: "" และ " " ไม่เท่ากัน หากต้องการตัดช่องว่างที่นำหน้าหรือตามหลังข้อความ คุณสามารถใช้ข้อมูล การจัดระเบียบข้อมูล ตัดช่องว่างได้
ไม่จัดเก็บค่าตัวเลขหรือวันที่เป็นข้อความ	ตรวจสอบว่าระบบไม่ได้เก็บค่าวันที่หรือตัวเลขในคอลัมน์แรกของช่วง VLOOKUP เป็นค่าข้อความ เช่น คอลัมน์คำค้นหา เนื่องจากคุณอาจได้ผลลัพธ์ที่ไม่คาดคิด ที่ด้านบนของชีต ให้เลือกคอลัมน์คำค้นหา แตะเมนูรูปแบบ ตัวเลข เลือกตัวเลือกตามประเภทข้อมูลที่ต้องการ ดังนี้ วันที่ ตัวเลข