เนื้อหา
วันนี้เราจะเริ่มบทความชุดหนึ่งที่อธิบายคุณลักษณะที่มีประโยชน์มากที่สุดอย่างหนึ่งของ Excel − VPR (VLOOKUP). ในเวลาเดียวกัน ฟังก์ชันนี้เป็นหนึ่งในฟังก์ชันที่ซับซ้อนและเข้าใจน้อยที่สุด
ในบทช่วยสอนนี้เกี่ยวกับ VPR ฉันจะพยายามจัดวางพื้นฐานให้เรียบง่ายที่สุดเพื่อให้กระบวนการเรียนรู้ชัดเจนที่สุดสำหรับผู้ใช้ที่ไม่มีประสบการณ์ นอกจากนี้ เราจะศึกษาตัวอย่างต่างๆ ด้วยสูตร Excel ที่จะแสดงให้เห็นกรณีการใช้งานทั่วไปสำหรับฟังก์ชันนี้ VPR.
ฟังก์ชัน VLOOKUP ใน Excel – คำอธิบายทั่วไปและไวยากรณ์
แล้วมันคืออะไร VPR? อย่างแรกเลย มันคือฟังก์ชัน Excel เธอทำอะไร? จะค้นหาค่าที่คุณระบุและส่งกลับค่าที่สอดคล้องกันจากคอลัมน์อื่น ในทางเทคนิคแล้ว VPR ค้นหาค่าในคอลัมน์แรกของช่วงที่กำหนดและส่งกลับผลลัพธ์จากคอลัมน์อื่นในแถวเดียวกัน
ในการใช้งานทั่วไป ฟังก์ชัน VPR ค้นหาฐานข้อมูลสำหรับตัวระบุเฉพาะที่กำหนดและดึงข้อมูลบางส่วนที่เกี่ยวข้องกับฐานข้อมูลนั้นออกจากฐานข้อมูล
อักษรตัวแรกในชื่อฟังก์ชัน VPR (VLOOKUP) แปลว่า Вแนวตั้ง (Vแนวตั้ง). โดยคุณสามารถแยกแยะ VPR ราคาเริ่มต้นที่ GPR (HLOOKUP) ซึ่งค้นหาค่าในแถวบนสุดของช่วง − Гแนวนอน (Hแนวนอน)
ฟังก์ชัน VPR มีอยู่ใน Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP และ Excel 2000
ไวยากรณ์ของฟังก์ชัน VLOOKUP
ฟังก์ชัน VPR (VLOOKUP) มีรูปแบบดังนี้:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
อย่างที่คุณเห็น ฟังก์ชัน VPR ใน Microsoft Excel มี 4 ตัวเลือก (หรืออาร์กิวเมนต์) สามตัวแรกเป็นข้อบังคับ อันสุดท้ายเป็นทางเลือก
- lookup_value (lookup_value) – ค่าที่จะค้นหา ซึ่งอาจเป็นค่า (ตัวเลข วันที่ ข้อความ) หรือการอ้างอิงเซลล์ (ที่มีค่าการค้นหา) หรือค่าที่ส่งคืนโดยฟังก์ชัน Excel อื่นๆ ตัวอย่างเช่น สูตรนี้จะค้นหาค่า 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
หากค่าการค้นหาน้อยกว่าค่าที่น้อยที่สุดในคอลัมน์แรกของช่วงที่กำลังค้นหา ฟังก์ชัน VPR จะรายงานข้อผิดพลาด #ที่ (#ไม่มี)
- table_array (ตาราง) – ข้อมูลสองคอลัมน์ขึ้นไป จำไว้ว่าฟังก์ชัน VPR ค้นหาค่าในคอลัมน์แรกของช่วงที่ระบุในอาร์กิวเมนต์เสมอ table_array (โต๊ะ). ช่วงที่สามารถดูได้สามารถมีข้อมูลต่างๆ เช่น ข้อความ วันที่ ตัวเลข บูลีน ฟังก์ชันไม่คำนึงถึงขนาดตัวพิมพ์ ซึ่งหมายความว่าอักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็กจะถือว่าเหมือนกัน ดังนั้นสูตรของเราจะมองหาค่า 40 ในเซลล์จาก A2 ไปยัง A15เนื่องจาก A เป็นคอลัมน์แรกของช่วง A2:B15 ที่ระบุในอาร์กิวเมนต์ table_array (โต๊ะ):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (column_number) คือจำนวนของคอลัมน์ในช่วงที่กำหนดซึ่งค่าในแถวที่พบจะถูกส่งกลับ คอลัมน์ซ้ายสุดในช่วงที่กำหนดคือ 1, คอลัมน์ที่สองคือ 2, คอลัมน์ที่สามคือ 3 และอื่นๆ ตอนนี้คุณสามารถอ่านสูตรทั้งหมด:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)สูตรหาค่า 40 อยู่ในช่วง A2: A15 และส่งคืนค่าที่สอดคล้องกันจากคอลัมน์ B (เนื่องจาก B เป็นคอลัมน์ที่สองในช่วง A2:B15)
ถ้าค่าของอาร์กิวเมนต์ col_index_num (column_number) น้อยกว่า 1แล้วก็ VPR จะรายงานข้อผิดพลาด #VALUE! (#ค่า!). และถ้ามันมากกว่าจำนวนคอลัมน์ในช่วง table_array (ตาราง) ฟังก์ชันจะส่งคืนข้อผิดพลาด #REF! (#ลิงก์!).
- range_lookup (range_lookup) – กำหนดสิ่งที่ต้องค้นหา:
- ตรงกันทุกประการ อาร์กิวเมนต์ต้องเท่ากัน FALSE (เท็จ);
- การจับคู่โดยประมาณ, อาร์กิวเมนต์เท่ากับ ทรูโค้ด (TRUE) หรือไม่ระบุเลย
พารามิเตอร์นี้เป็นทางเลือก แต่สำคัญมาก ภายหลังในบทช่วยสอนนี้เกี่ยวกับ VPR ฉันจะแสดงตัวอย่างที่อธิบายวิธีการเขียนสูตรเพื่อค้นหาผลลัพธ์ที่ตรงกันและใกล้เคียงกัน
ตัวอย่าง VLOOKUP
ฉันหวังว่าฟังก์ชัน VPR ชัดเจนขึ้นเล็กน้อยสำหรับคุณ ทีนี้มาดูกรณีการใช้งานกันบ้าง VPR ในสูตรที่มีข้อมูลจริง
วิธีใช้ VLOOKUP เพื่อค้นหาในแผ่นงาน Excel อื่น
ในทางปฏิบัติสูตรที่มีฟังก์ชัน VPR ไม่ค่อยได้ใช้เพื่อค้นหาข้อมูลในเวิร์กชีตเดียวกัน บ่อยครั้งคุณจะค้นหาและดึงค่าที่เกี่ยวข้องจากชีตอื่น
เพื่อที่จะใช้ VPR, ค้นหาในแผ่นงาน Microsoft Excel อื่น คุณต้องอยู่ในอาร์กิวเมนต์ table_array (ตาราง) ระบุชื่อแผ่นงานด้วยเครื่องหมายอัศเจรีย์ตามด้วยช่วงของเซลล์ ตัวอย่างเช่น สูตรต่อไปนี้แสดงว่า range A2: B15 อยู่บนแผ่นชื่อ Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
แน่นอนว่าไม่จำเป็นต้องป้อนชื่อแผ่นงานด้วยตนเอง เพียงแค่เริ่มพิมพ์สูตรและเมื่อถึงอาร์กิวเมนต์ table_array (ตาราง) สลับไปที่แผ่นงานที่ต้องการและเลือกช่วงของเซลล์ที่ต้องการด้วยเมาส์
สูตรที่แสดงในภาพหน้าจอด้านล่างจะค้นหาข้อความ "ผลิตภัณฑ์ 1" ในคอลัมน์ A (เป็นคอลัมน์ที่ 1 ของช่วง A2:B9) บนเวิร์กชีต ราคา.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
โปรดจำไว้ว่าเมื่อค้นหาค่าข้อความ คุณต้องใส่ค่าดังกล่าวในเครื่องหมายคำพูด (“”) ตามปกติในสูตรของ Excel
สำหรับการโต้แย้ง table_array (ตาราง) ขอแนะนำให้ใช้การอ้างอิงแบบสัมบูรณ์เสมอ (พร้อมเครื่องหมาย $) ในกรณีนี้ ช่วงการค้นหาจะไม่เปลี่ยนแปลงเมื่อคัดลอกสูตรไปยังเซลล์อื่น
ค้นหาในสมุดงานอื่นด้วย VLOOKUP
ในการทำงาน VPR ทำงานระหว่างสมุดงาน Excel สองเล่ม คุณต้องระบุชื่อสมุดงานในวงเล็บเหลี่ยมก่อนชื่อแผ่นงาน
ตัวอย่างเช่น ด้านล่างเป็นสูตรที่ค้นหาค่า 40 บนแผ่น Sheet2 ในหนังสือเล่มนี้ เบอร์.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
นี่เป็นวิธีที่ง่ายที่สุดในการสร้างสูตรใน Excel ด้วย VPRซึ่งเชื่อมโยงไปยังสมุดงานอื่น:
- เปิดหนังสือทั้งสองเล่ม ไม่จำเป็น แต่จะง่ายกว่าในการสร้างสูตรด้วยวิธีนี้ คุณไม่ต้องการป้อนชื่อเวิร์กบุ๊กด้วยตนเองใช่ไหม นอกจากนี้ มันจะปกป้องคุณจากการพิมพ์ผิดโดยไม่ได้ตั้งใจ
- เริ่มพิมพ์ฟังก์ชั่น VPRและเมื่อพูดถึงการโต้เถียง table_array (ตาราง) สลับไปยังสมุดงานอื่นและเลือกช่วงการค้นหาที่ต้องการในนั้น
ภาพหน้าจอด้านล่างแสดงสูตรโดยตั้งค่าการค้นหาเป็นช่วงในสมุดงาน รายการราคา.xlsx บนแผ่น ราคา.
ฟังก์ชัน VPR จะทำงานแม้ว่าคุณจะปิดเวิร์กบุ๊กที่ค้นหาและพาธแบบเต็มไปยังไฟล์เวิร์กบุ๊กจะปรากฏในแถบสูตรดังที่แสดงด้านล่าง:
หากชื่อเวิร์กบุ๊กหรือชีตมีช่องว่าง จะต้องใส่เครื่องหมายอะพอสทรอฟี:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
วิธีใช้ช่วงหรือตารางที่มีชื่อในสูตรด้วย VLOOKUP
หากคุณวางแผนที่จะใช้ช่วงการค้นหาเดียวกันในหลายฟังก์ชัน VPRคุณสามารถสร้างช่วงที่มีชื่อและป้อนชื่อลงในสูตรเป็นอาร์กิวเมนต์ได้ table_array (โต๊ะ).
ในการสร้างช่วงที่มีชื่อ เพียงเลือกเซลล์และป้อนชื่อที่เหมาะสมในฟิลด์ ชื่อจริงทางด้านซ้ายของแถบสูตร
ตอนนี้คุณสามารถเขียนสูตรต่อไปนี้เพื่อค้นหาราคาของผลิตภัณฑ์ ผลิตภัณฑ์ 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
ชื่อช่วงส่วนใหญ่ใช้ได้กับเวิร์กบุ๊ก Excel ทั้งหมด ดังนั้นจึงไม่จำเป็นต้องระบุชื่อชีตสำหรับอาร์กิวเมนต์ table_array (ตาราง) แม้ว่าสูตรและช่วงการค้นหาจะอยู่บนเวิร์กชีตที่ต่างกัน หากอยู่ในสมุดงานอื่น คุณต้องระบุชื่อเวิร์กบุ๊กก่อนชื่อช่วง เช่น
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
ดังนั้นสูตรจึงดูชัดเจนขึ้นมาก เห็นด้วยไหม? นอกจากนี้ การใช้ช่วงที่มีชื่อยังเป็นทางเลือกที่ดีสำหรับการอ้างอิงแบบสัมบูรณ์ เนื่องจากช่วงที่มีชื่อจะไม่เปลี่ยนแปลงเมื่อคุณคัดลอกสูตรไปยังเซลล์อื่น ซึ่งหมายความว่าคุณสามารถมั่นใจได้ว่าช่วงการค้นหาในสูตรจะยังคงถูกต้องอยู่เสมอ
หากคุณแปลงช่วงของเซลล์เป็นสเปรดชีต Excel แบบเต็มโดยใช้คำสั่ง ตาราง (ตาราง) แท็บ การแทรก (แทรก) เมื่อคุณเลือกช่วงด้วยเมาส์ Microsoft Excel จะเพิ่มชื่อคอลัมน์ (หรือชื่อตารางหากคุณเลือกทั้งตาราง) ลงในสูตรโดยอัตโนมัติ
สูตรที่เสร็จแล้วจะมีลักษณะดังนี้:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
หรือแม้กระทั่งเช่นนี้:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
เมื่อใช้ช่วงที่มีชื่อ ลิงก์จะชี้ไปที่เซลล์เดียวกันไม่ว่าคุณจะคัดลอกฟังก์ชันจากที่ใด VPR ภายในสมุดงาน
การใช้สัญลักษณ์แทนในสูตร VLOOKUP
เช่นเดียวกับฟังก์ชั่นอื่นๆ อีกมากมาย VPR คุณสามารถใช้อักขระตัวแทนต่อไปนี้:
- เครื่องหมายคำถาม (?) – แทนที่อักขระตัวเดียว
- เครื่องหมายดอกจัน (*) – แทนที่ลำดับของอักขระใดๆ
การใช้สัญลักษณ์แทนในฟังก์ชัน VPR มีประโยชน์ในหลายกรณี เช่น
- เมื่อคุณจำข้อความที่คุณต้องการค้นหาไม่ได้
- เมื่อคุณต้องการค้นหาคำบางคำที่เป็นส่วนหนึ่งของเนื้อหาของเซลล์ รู้ว่า VPR ค้นหาตามเนื้อหาของเซลล์โดยรวม ราวกับว่าตัวเลือกเปิดใช้งานอยู่ จับคู่เนื้อหาของเซลล์ทั้งหมด (ทั้งเซลล์) ในการค้นหา Excel มาตรฐาน
- เมื่อเซลล์มีช่องว่างพิเศษที่จุดเริ่มต้นหรือจุดสิ้นสุดของเนื้อหา ในสถานการณ์เช่นนี้ คุณสามารถเก็บสมองไว้ได้นาน โดยพยายามหาสาเหตุว่าทำไมสูตรถึงใช้ไม่ได้ผล
ตัวอย่างที่ 1: ค้นหาข้อความที่ขึ้นต้นหรือลงท้ายด้วยอักขระบางตัว
สมมติว่าคุณต้องการค้นหาลูกค้ารายใดรายหนึ่งจากฐานข้อมูลที่แสดงด้านล่าง คุณจำนามสกุลเขาไม่ได้ แต่คุณรู้ว่ามันขึ้นต้นด้วย “ack” นี่คือสูตรที่จะทำงานได้ดี:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
เมื่อคุณแน่ใจว่าคุณพบชื่อที่ถูกต้องแล้ว คุณสามารถใช้สูตรเดียวกันเพื่อค้นหาจำนวนเงินที่ชำระโดยลูกค้ารายนี้ เมื่อต้องการทำสิ่งนี้ เพียงแค่เปลี่ยนอาร์กิวเมนต์ที่สามของฟังก์ชัน VPR ไปยังหมายเลขคอลัมน์ที่ต้องการ ในกรณีของเรา นี่คือคอลัมน์ C (ที่ 3 ในช่วง):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
ต่อไปนี้คือตัวอย่างเพิ่มเติมบางส่วนที่มีสัญลักษณ์แทน:
~ ค้นหาชื่อที่ลงท้ายด้วย “man”:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ ค้นหาชื่อที่ขึ้นต้นด้วย "ad" และลงท้ายด้วย "son":
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ เราพบชื่อในรายการประกอบด้วย 5 ตัวอักษร:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
ในการทำงาน VPR ด้วยไวด์การ์ดทำงานอย่างถูกต้อง เนื่องจากอาร์กิวเมนต์ที่สี่ คุณควรใช้ FALSE (เท็จ). หากช่วงการค้นหามีค่ามากกว่าหนึ่งค่าที่ตรงกับคำค้นหาที่มีสัญลักษณ์แทน ค่าแรกที่พบจะถูกส่งคืน
ตัวอย่างที่ 2: รวมสัญลักษณ์แทนและการอ้างอิงเซลล์ในสูตร VLOOKUP
ทีนี้มาดูตัวอย่างการค้นหาโดยใช้ฟังก์ชันที่ซับซ้อนขึ้นเล็กน้อยกันเถอะ VPR ตามค่าในเซลล์ ลองนึกภาพว่าคอลัมน์ A คือรายการคีย์ใบอนุญาต และคอลัมน์ B คือรายชื่อที่เป็นเจ้าของใบอนุญาต นอกจากนี้ คุณมีส่วน (หลายอักขระ) ของคีย์ใบอนุญาตบางประเภทในเซลล์ C1 และต้องการค้นหาชื่อเจ้าของ
สามารถทำได้โดยใช้สูตรต่อไปนี้:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
สูตรนี้จะค้นหาค่าจากเซลล์ C1 ในช่วงที่กำหนดและส่งกลับค่าที่สอดคล้องกันจากคอลัมน์ B โปรดทราบว่าในอาร์กิวเมนต์แรก เราใช้อักขระเครื่องหมายและ (&) ก่อนและหลังการอ้างอิงเซลล์เพื่อเชื่อมโยงสตริงข้อความ
ดังที่คุณเห็นในรูปด้านล่าง ฟังก์ชัน VPR ส่งคืน "Jeremy Hill" เนื่องจากคีย์ใบอนุญาตของเขามีลำดับของอักขระจากเซลล์ C1
โปรดทราบว่าอาร์กิวเมนต์ table_array (ตาราง) ในภาพหน้าจอด้านบนมีชื่อของตาราง (Table7) แทนที่จะระบุช่วงของเซลล์ นี่คือสิ่งที่เราทำในตัวอย่างก่อนหน้านี้
การจับคู่ที่แน่นอนหรือใกล้เคียงกันในฟังก์ชัน VLOOKUP
และสุดท้าย มาดูอาร์กิวเมนต์สุดท้ายที่ระบุสำหรับฟังก์ชันกันดีกว่า VPR - range_lookup (interval_view) ดังที่กล่าวไว้ตอนต้นของบทเรียน อาร์กิวเมนต์นี้มีความสำคัญมาก คุณสามารถได้ผลลัพธ์ที่แตกต่างอย่างสิ้นเชิงในสูตรเดียวกันกับค่าของมัน ทรูโค้ด (TRUE) หรือ FALSE (เท็จ).
อันดับแรก มาดูกันว่า Microsoft Excel หมายถึงอะไรจากการจับคู่แบบตรงทั้งหมดและแบบคร่าวๆ
- ถ้าข้อโต้แย้ง range_lookup (range_lookup) เท่ากับ FALSE (FALSE) สูตรจะค้นหาการจับคู่แบบตรงทั้งหมด กล่าวคือ ค่าเดียวกับที่ระบุในอาร์กิวเมนต์ lookup_value (lookup_value) ถ้าอยู่ในคอลัมน์แรกของช่วง tสามารถ_array (ตาราง) พบสองค่าขึ้นไปที่ตรงกับอาร์กิวเมนต์ lookup_value (search_value) จากนั้นรายการแรกจะถูกเลือก หากไม่พบรายการที่ตรงกัน ฟังก์ชันจะรายงานข้อผิดพลาด #ที่ (#N/A). ตัวอย่างเช่น สูตรต่อไปนี้จะรายงานข้อผิดพลาด #ที่ (#N/A) หากไม่มีค่าอยู่ในช่วง A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - ถ้าข้อโต้แย้ง range_lookup (range_lookup) เท่ากับ ทรูโค้ด (TRUE) สูตรจะค้นหาค่าที่ตรงกันโดยประมาณ ให้แม่นยำยิ่งขึ้นก่อนอื่น ฟังก์ชัน VPR ค้นหาที่ตรงกันทุกประการ และหากไม่พบ ให้เลือกค่าที่ใกล้เคียง การจับคู่โดยประมาณคือค่าที่ใหญ่ที่สุดที่ไม่เกินค่าที่ระบุในอาร์กิวเมนต์ lookup_value (lookup_value)
ถ้าข้อโต้แย้ง range_lookup (range_lookup) เท่ากับ ทรูโค้ด (TRUE) หรือไม่ได้ระบุ ดังนั้นค่าในคอลัมน์แรกของช่วงควรเรียงลำดับจากน้อยไปหามาก กล่าวคือ จากน้อยไปหามาก มิฉะนั้นฟังก์ชัน VPR อาจส่งคืนผลลัพธ์ที่ผิดพลาด
เพื่อให้เข้าใจถึงความสำคัญของการเลือกมากขึ้น ทรูโค้ด (ความจริง) หรือ FALSE (FALSE) มาดูสูตรอื่นๆ กันบ้างกับฟังก์ชัน VPR และดูผลลัพธ์
ตัวอย่างที่ 1: ค้นหาการจับคู่ที่แน่นอนด้วย VLOOKUP
อย่างที่คุณจำได้ เพื่อค้นหาการจับคู่แบบตรงทั้งหมด อาร์กิวเมนต์ที่สี่ของฟังก์ชัน VPR ควรจะสำคัญ FALSE (เท็จ).
กลับไปที่ตารางจากตัวอย่างแรกและค้นหาว่าสัตว์ชนิดใดสามารถเคลื่อนที่ด้วยความเร็วได้ 50 ไมล์ต่อชั่วโมง. ฉันเชื่อว่าสูตรนี้จะไม่ทำให้คุณลำบาก:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
โปรดทราบว่าช่วงการค้นหาของเรา (คอลัมน์ A) มีค่าสองค่า 50 – ในเซลล์ A5 и A6. สูตรส่งกลับค่าจากเซลล์ B5. ทำไม เพราะเมื่อมองหาฟังก์ชันที่ตรงกันทุกประการ VPR ใช้ค่าแรกที่พบว่าตรงกับค่าที่กำลังค้นหา
ตัวอย่างที่ 2: การใช้ VLOOKUP เพื่อค้นหาการจับคู่โดยประมาณ
เมื่อคุณใช้ฟังก์ชัน VPR เพื่อค้นหาการจับคู่โดยประมาณ เช่น เมื่ออาร์กิวเมนต์ range_lookup (range_lookup) เท่ากับ ทรูโค้ด (TRUE) หรือละเว้น สิ่งแรกที่คุณต้องทำคือจัดเรียงช่วงตามคอลัมน์แรกโดยเรียงลำดับจากน้อยไปมาก
ซึ่งสำคัญมากเพราะว่าหน้าที่ VPR ส่งคืนค่าที่มากที่สุดถัดไปหลังจากค่าที่กำหนด จากนั้นการค้นหาจะหยุดลง หากคุณละเลยการเรียงลำดับที่ถูกต้อง คุณจะพบผลลัพธ์ที่แปลกประหลาดหรือข้อความแสดงข้อผิดพลาด #ที่ (#ไม่มี)
ตอนนี้คุณสามารถใช้หนึ่งในสูตรต่อไปนี้:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
อย่างที่คุณเห็นฉันต้องการค้นหาสัตว์ที่มีความเร็วใกล้เคียงที่สุด 69 ไมล์ต่อชั่วโมง. และนี่คือผลลัพธ์ที่ฟังก์ชันกลับมาหาฉัน VPR:
อย่างที่คุณเห็น สูตรส่งคืนผลลัพธ์ ละมั่ง (ละมั่ง) ซึ่งมีความเร็ว 61 ไมล์ต่อชั่วโมง แม้ว่ารายการจะรวม เสือชีต้า (เสือชีตาห์) ที่วิ่งด้วยความเร็ว 70 ไมล์ต่อชั่วโมง และ 70 ก็ใกล้ 69 มากกว่า 61 ใช่ไหม? ทำไมสิ่งนี้ถึงเกิดขึ้น? เพราะหน้าที่ VPR เมื่อค้นหาการจับคู่โดยประมาณ ให้คืนค่าที่มากที่สุดซึ่งไม่เกินค่าที่ค้นหา
ฉันหวังว่าตัวอย่างเหล่านี้จะให้ความกระจ่างในการทำงานกับฟังก์ชันนี้ VPR ใน Excel และคุณจะไม่มองว่าเธอเป็นคนนอกอีกต่อไป ตอนนี้ไม่เจ็บที่จะทำซ้ำประเด็นสำคัญของเนื้อหาที่เราศึกษามาสั้น ๆ เพื่อที่จะแก้ไขในหน่วยความจำได้ดีขึ้น
VLOOKUP ใน Excel – คุณต้องจำไว้!
- ฟังก์ชัน VPR Excel มองไปทางซ้ายไม่ได้ จะค้นหาค่าในคอลัมน์ซ้ายสุดของช่วงที่กำหนดโดยอาร์กิวเมนต์เสมอ table_array (โต๊ะ).
- ในฟังก์ชัน VPR ค่าทั้งหมดไม่คำนึงถึงขนาดตัวพิมพ์ เช่น ตัวอักษรเล็กและใหญ่มีค่าเท่ากัน
- หากค่าที่คุณกำลังค้นหาน้อยกว่าค่าต่ำสุดในคอลัมน์แรกของช่วงที่กำลังค้นหา ฟังก์ชัน VPR จะรายงานข้อผิดพลาด #ที่ (#ไม่มี)
- ถ้าอาร์กิวเมนต์ที่ 3 col_index_num (column_number) น้อยกว่า 1ฟังก์ชัน VPR จะรายงานข้อผิดพลาด #VALUE! (#ค่า!). หากมากกว่าจำนวนคอลัมน์ในช่วง table_array (ตาราง) ฟังก์ชันจะรายงานข้อผิดพลาด #REF! (#ลิงก์!).
- ใช้การอ้างอิงเซลล์แบบสัมบูรณ์ในอาร์กิวเมนต์ table_array (ตาราง) เพื่อรักษาช่วงการค้นหาที่ถูกต้องเมื่อคัดลอกสูตร ลองใช้ช่วงหรือตารางที่มีชื่อใน Excel เป็นทางเลือก
- เมื่อทำการค้นหาที่ตรงกันโดยประมาณ จำไว้ว่าคอลัมน์แรกในช่วงที่คุณกำลังค้นหาต้องเรียงลำดับจากน้อยไปมาก
- สุดท้าย จำความสำคัญของอาร์กิวเมนต์ที่สี่ ใช้ค่า ทรูโค้ด (ความจริง) หรือ FALSE (FALSE) อย่างจงใจแล้วท่านจะหายปวดหัวมากมาย
ในบทความต่อไปนี้ของบทช่วยสอนเกี่ยวกับฟังก์ชันของเรา VPR ใน Excel เราจะเรียนรู้ตัวอย่างขั้นสูงเพิ่มเติม เช่น การคำนวณต่างๆ โดยใช้ VPRการแยกค่าจากหลายคอลัมน์ และอื่นๆ ขอบคุณสำหรับการอ่านบทช่วยสอนนี้และหวังว่าจะได้พบคุณอีกในสัปดาห์หน้า!