สูตรการค้นหา Excel ด้านซ้ายโดยใช้ VLOOKUP

01 จาก 03

ค้นหาข้อมูลไปทางซ้าย

ภาพรวมสูตร Excel Lookup Left Lookup

ฟังก์ชัน VLOOKUP ของ Excel ใช้เพื่อค้นหาและส่งคืนข้อมูลจากตารางข้อมูลตาม ค่าการค้นหา ที่คุณเลือก

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

โดยการรวม VLOOKUP กับ ฟังก์ชัน CHOOSE ; อย่างไรก็ตามคุณสามารถสร้างสูตรการ ค้นหาด้านซ้าย ที่จะ:

• อนุญาตให้มีการเลือกค่าการค้นหาจากคอลัมน์ใดก็ได้ในตารางข้อมูล
• ส่งคืนข้อมูลอยู่ในคอลัมน์ใด ๆ ทางด้านซ้ายของค่าการค้นหา

ตัวอย่าง: ใช้ VLOOKUP และเลือกฟังก์ชันในสูตรค้นหาด้านซ้าย

ขั้นตอนต่อไปนี้สร้างสูตรการค้นหาด้านซ้ายที่เห็นในภาพด้านบน

สูตร

= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: F $, $ D: $ D), 2, FALSE)

ทำให้สามารถหาส่วนที่จัดทำโดย บริษัท อื่นที่แสดงในคอลัมน์ 3 ของตารางข้อมูลได้

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

ขั้นตอนการสอน - การป้อนข้อมูลบทแนะนำ

1. ป้อนส่วนหัวต่อไปนี้ลงในเซลล์ที่ระบุ: D1 - ผู้จัดจำหน่าย E1 - ส่วน
2. ป้อนข้อมูล ตาราง ที่เห็นในรูปภาพด้านบนลงในเซลล์ D4 ถึง F9
3. แถว 2 และ 3 เว้นว่างไว้เพื่อให้สอดคล้องกับเกณฑ์การค้นหาและสูตรการค้นหาด้านซ้ายที่สร้างขึ้นในระหว่างบทแนะนำนี้

เริ่มต้นสูตรการค้นหาด้านซ้าย - เปิดกล่องโต้ตอบ VLOOKUP

แม้ว่าจะเป็นไปได้ที่จะพิมพ์สูตรข้างต้นโดยตรงลงในเซลล์ F1 ในแผ่นงานหลายคนมีปัญหากับ ไวยากรณ์ ของสูตร

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

ขั้นตอนการสอน

1. คลิกที่เซลล์ E2 ของ แผ่นงาน - ตำแหน่งที่ผลลัพธ์ของสูตรการค้นหาด้านซ้ายจะปรากฏขึ้น
2. คลิกแท็บ สูตร ของ ริบบิ้น
3. คลิกตัวเลือกการ ค้นหาและการอ้างอิง ใน Ribbon เพื่อเปิดรายการแบบเลื่อนลงของฟังก์ชัน
4. คลิกที่ VLOOKUP ในรายการเพื่อเรียกกล่องโต้ตอบของฟังก์ชั่น

02 จาก 03

การป้อนอาร์กิวเมนต์ลงในกล่องโต้ตอบ VLOOKUP - คลิกเพื่อดูภาพขนาดใหญ่

อาร์กิวเมนต์ของ VLOOKUP

อาร์กิวเมนต์ ของฟังก์ชันคือค่าที่ใช้โดยฟังก์ชันคำนวณผลลัพธ์

ในกล่องโต้ตอบของฟังก์ชันชื่อของแต่ละอาร์กิวเมนต์จะอยู่ในบรรทัดที่แยกจากกันตามด้วยเขตข้อมูลที่จะป้อนค่า

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

ค่าค้นหา

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

ตัวอย่างนี้ใช้การอ้างอิงเซลล์ไปยังตำแหน่งที่จะป้อนชื่อ บริษัท ลงในแผ่นงาน ประโยชน์ของการนี้คือการทำให้ชื่อ บริษัท เปลี่ยนชื่อได้ง่ายโดยไม่ต้องแก้ไข

ขั้นตอนการสอน

1. คลิกที่บรรทัด lookup_value ในไดอะล็อกบ็อกซ์
2. คลิกเซลล์ D2 เพื่อเพิ่มการอ้างอิงเซลล์นี้ไปยังบรรทัด lookup_value
3. กดแป้น F4 บนแป้นพิมพ์เพื่อให้การอ้างอิงเซลล์สมบูรณ์ - $ D $ 2

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

Table Array: การป้อนฟังก์ชัน CHOOSE

อาร์กิวเมนต์อาร์เรย์ของตารางคือการบล็อกข้อมูลที่ต่อเนื่องจากการเรียกข้อมูลเฉพาะ

โดยปกติ VLOOKUP จะมองไปทางด้านขวาของอาร์กิวเมนต์ ค่าค้นหา เพื่อค้นหาข้อมูลในอาร์เรย์ของตาราง เพื่อให้ดูด้านซ้าย VLOOKUP ต้องหลอกด้วยการจัดเรียงคอลัมน์ใหม่ในอาร์เรย์ของตารางโดยใช้ฟังก์ชัน CHOOSE

ในสูตรนี้ฟังก์ชัน CHOOSE จะทำงานได้สองอย่าง:

1. จะสร้างอาร์เรย์ตารางที่มีเพียงสองคอลัมน์กว้าง - คอลัมน์ D และ F
2. จะเปลี่ยนสิทธิในลำดับที่ยังเหลือของคอลัมน์ในอาร์เรย์ของตารางเพื่อให้คอลัมน์ F มาก่อนและคอลัมน์ D เป็นอันดับที่สอง

รายละเอียดวิธีการทำงานของ CHOOSE สามารถทำได้จาก หน้า 3 ของบทแนะนำ

ขั้นตอนการสอน

หมายเหตุ: เมื่อป้อนฟังก์ชันด้วยตนเองอาร์กิวเมนต์แต่ละฟังก์ชันจะต้องคั่นด้วยเครื่องหมายจุลภาค ","

1. ในกล่องโต้ตอบฟังก์ชัน VLOOKUP คลิกที่บรรทัด Table_array
2. ป้อนฟังก์ชัน CHOOSE ต่อไปนี้
3. CHOOSE ({1,2}, $ F: F $, $ D: $ D)

หมายเลขดัชนีของคอลัมน์

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

ฟังก์ชัน CHOOSE จะสร้างอาร์เรย์ของตารางซึ่งเป็นคอลัมน์สองคอลัมน์กว้างพร้อมกับคอลัมน์ F ตามด้วยคอลัมน์ D. เนื่องจากข้อมูลที่ต้องการ - ชื่อส่วน - อยู่ในคอลัมน์ D ค่า อาร์กิวเมนต์ของ ดัชนีคอลัมน์ต้องตั้งค่าเป็น 2

ขั้นตอนการสอน

1. คลิกที่ Col_index_num บรรทัดใน กล่องโต้ตอบ
2. พิมพ์ 2 ในบรรทัดนี้

การค้นหาช่วง

อาร์กิวเมนต์ Range_lookup ของ VLOOKUP เป็น ค่าตรรกะ (TRUE หรือ FALSE เท่านั้น) ที่ระบุว่าคุณต้องการให้ VLOOKUP หาค่าที่แน่นอนหรือใกล้เคียงกับค่าการค้นหาหรือไม่

• ถ้า TRUE หรือถ้า อาร์กิวเมนต์ นี้ถูกละเว้น VLOOKUP จะส่งกลับค่าที่ตรงกันทั้งหมดไปยัง Lookup_value หรือถ้าไม่พบการจับคู่ที่ตรงกัน VLOOKUP จะส่งค่าที่ใหญ่ที่สุดถัดไป สำหรับสูตรการทำเช่นนี้ข้อมูลในคอลัมน์แรกของ Table_array ต้อง เรียงตามลำดับจากน้อยไปมาก
• ถ้า FALSE VLOOKUP จะใช้การจับคู่แบบตรงทั้งหมดกับ Lookup_value เท่านั้น หากมีค่าตั้งแต่สองค่าขึ้นไปในคอลัมน์แรกของ Table_array ที่ตรงกับค่าการค้นหาค่าแรกที่พบจะถูกใช้ หากไม่พบการทำงานแบบตรงทั้งหมดระบบจะส่งคืนข้อผิดพลาด # N / A

ในบทแนะนำนี้เนื่องจากเรากำลังมองหาชื่อส่วนหนึ่งไว้ Range_lookup จะถูกกำหนดเป็น False เพื่อให้มีการจับคู่เฉพาะที่ตรงตามสูตร

ขั้นตอนการสอน

1. คลิกที่บรรทัด Range_lookup ในไดอะ ลอกบ็อกซ์
2. พิมพ์คำว่า False ในบรรทัดนี้เพื่อระบุว่าเราต้องการให้ VLOOKUP คืนค่าการจับคู่แบบตรงทั้งหมดสำหรับข้อมูลที่เรากำลังหาอยู่
3. คลิกตกลงเพื่อเสร็จสิ้นสูตรการค้นหาด้านซ้ายและปิดกล่องโต้ตอบ
4. เนื่องจากเรายังไม่ได้ป้อนชื่อ บริษัท ลงในเซลล์ D2 ควรมีข้อผิดพลาด # N / A ในเซลล์ E2

03 จาก 03

การทดสอบสูตรค้นหาด้านซ้าย

การส่งคืนข้อมูลด้วยสูตรการค้นหาจากซ้าย

หากต้องการหา บริษัท ที่จัดหาชิ้นส่วนให้พิมพ์ชื่อ บริษัท ลงในเซลล์ D2 และกดปุ่ม ENTER บนแป้นพิมพ์

ชื่อชิ้นส่วนจะปรากฏในเซลล์ E2

ขั้นตอนการสอน

1. คลิกที่เซลล์ D2 ใน แผ่นงาน ของคุณ
2. ใส่ Gadgets Plus ลงในเซลล์ D2 แล้วกดปุ่ม ENTER บนแป้นพิมพ์
3. แกดเจ็ต ข้อความ - ส่วนที่จัดเตรียมโดย บริษัท Gadgets Plus - ควรแสดงในเซลล์ E2
4. ทดสอบสูตรการค้นหาต่อไปโดยพิมพ์ชื่อ บริษัท อื่นลงในเซลล์ D2 และชื่อส่วนที่เกี่ยวข้องจะปรากฏในเซลล์ E2

VLOOKUP ข้อความแสดงข้อผิดพลาด

หากข้อความแสดงข้อผิดพลาดเช่น # N / A ปรากฏในเซลล์ E2 ตรวจสอบข้อผิดพลาดในการสะกดคำแรกในเซลล์ D2

ถ้าการสะกดไม่ใช่ปัญหารายการข้อผิดพลาด VLOOKUP นี้อาจช่วยให้คุณทราบว่าปัญหาอยู่ที่ใด

การแบ่งงานของฟังก์ชัน CHOOSE

ดังกล่าวในสูตรนี้ฟังก์ชัน CHOOSE มีสองงาน:

• จะสร้างอาร์เรย์ตารางที่มีเพียงสองคอลัมน์กว้าง - คอลัมน์ D และ F
• จะเปลี่ยนสิทธิในลำดับที่ยังเหลือของคอลัมน์ในอาร์เรย์ของตารางเพื่อให้คอลัมน์ F มาก่อนและคอลัมน์ D เป็นอันดับที่สอง

การสร้างอาร์เรย์ตารางสองคอลัมน์

ไวยากรณ์ สำหรับฟังก์ชัน CHOOSE คือ:

= CHOOSE (Index_number, Value1, Value2, ... Value254)

ฟังก์ชัน CHOOSE จะคืนค่าหนึ่งค่าจากรายการค่า (Value1 to Value254) ตามหมายเลขดัชนีที่ป้อน

ถ้าหมายเลขดัชนีเป็น 1 ฟังก์ชันจะส่งคืน Value1 จากรายการ ถ้าหมายเลขดัชนีเป็น 2 ฟังก์ชันจะส่งกลับ Value2 จากรายการและอื่น ๆ

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

การป้อนอาร์เรย์ทำได้โดยล้อมรอบตัวเลขที่ป้อนด้วยวงเล็บปีกกาหรือวงเล็บปีกกา ป้อนหมายเลขสองหมายเลขสำหรับดัชนี: {1,2}

ควรสังเกตว่า CHOOSE ไม่ จำกัด เฉพาะการสร้างตารางสองคอลัมน์ โดยการรวมหมายเลขเพิ่มเติมในอาร์เรย์เช่น {1,2,3} - และช่วงเพิ่มเติมในอาร์กิวเมนต์ค่าคุณสามารถสร้างตารางสามคอลัมน์ได้

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

การเปลี่ยนลำดับของคอลัมน์ด้วยฟังก์ชัน CHOOSE

ในฟังก์ชัน CHOOSE ที่ใช้ในสูตรนี้: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) ช่วงของคอลัมน์ F จะแสดงไว้ก่อนคอลัมน์ D

เนื่องจากฟังก์ชัน CHOOSE กำหนดอาร์เรย์ของ VLOOKUP - แหล่งข้อมูลสำหรับฟังก์ชันนั้น - การเปลี่ยนลำดับของคอลัมน์ในฟังก์ชัน CHOOSE จะถูกส่งผ่านไปยัง VLOOKUP

ตอนนี้เท่าที่ VLOOKUP เกี่ยวข้องตารางอาร์เรย์มีเพียงสองคอลัมน์กว้างกับคอลัมน์ F ด้านซ้ายและคอลัมน์ D ด้านขวา เนื่องจากคอลัมน์ F มีชื่อ บริษัท ที่เราต้องการค้นหาและตั้งแต่ D คอลัมน์มีชื่อส่วน VLOOKUP จะสามารถปฏิบัติหน้าที่การค้นหาตามปกติในการค้นหาข้อมูลที่อยู่ทางด้านซ้ายของค่าการค้นหา

ด้วยเหตุนี้ VLOOKUP จึงสามารถใช้ชื่อ บริษัท เพื่อค้นหาส่วนที่จัดหาได้