ค้นหาเขตข้อมูลหลายช่องด้วย Excel VLOOKUP

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

01 จาก 10

ส่งคืนค่าหลายค่าด้วย Excel VLOOKUP

ทำตามขั้นตอนด้านล่างนี้เพื่อสร้างสูตรการค้นหาที่เห็นในภาพด้านบนซึ่งจะส่งคืนค่าหลายค่าจากข้อมูลเดียว

สูตรการค้นหากำหนดให้ คอลัมน์ ที่อยู่ภายในของ VLOOKUP. การวางซ้อนฟังก์ชันเกี่ยวข้องกับการป้อนฟังก์ชันที่สองเป็นหนึ่งในอาร์กิวเมนต์สำหรับฟังก์ชันแรก

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

02 จาก 10

ป้อนข้อมูลบทแนะนำ

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

• ป้อนช่วงข้อมูลด้านบนลงใน เซลล์ D1 ไปยัง G1.
• ป้อนช่วงที่สองลงใน เซลล์ D4 ไปยัง G10.

เกณฑ์การค้นหาและสูตรการค้นหาที่สร้างระหว่างบทแนะนำนี้จะถูกป้อนลงไป แถวที่ 2 ของแผ่นงาน

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

03 จาก 10

การสร้างช่วงที่ตั้งชื่อสำหรับตารางข้อมูล

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

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

ชื่อช่วงไม่รวมหัวเรื่องหรือชื่อฟิลด์สำหรับข้อมูล (แถวที่ 4) แต่เฉพาะข้อมูลเท่านั้น

1. ไฮไลท์ เซลล์ D5 ไปยัง G10 ในแผ่นงานเพื่อเลือก
2. คลิกที่ กล่องชื่อ ตั้งอยู่ด้านบน คอลัมน์ A.
3. ชนิด ตาราง เข้าไปใน กล่องชื่อ.
4. กด เข้าสู่ บนแป้นพิมพ์
5. เซลล์ D5 ไปยัง G10 ตอนนี้มีชื่อช่วงของ ตาราง. เราจะใช้ชื่อสำหรับ VLOOKUP อาร์เรย์ของตาราง อาร์กิวเมนต์ในบทแนะนำต่อไป

04 จาก 10

การเปิดกล่องโต้ตอบ VLOOKUP

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

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

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

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

05 จาก 10

การป้อนอาร์กิวเมนต์ค่าค้นหา

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

การอ้างอิงเซลล์สัมบูรณ์

โดยปกติเมื่อมีการคัดลอกสูตรใน Excel การอ้างอิงเซลล์จะเปลี่ยนไปเพื่อแสดงตำแหน่งใหม่ หากเกิดเหตุการณ์เช่นนี้ขึ้น D2, การอ้างอิงเซลล์สำหรับ ค้นหา ราคาจะเปลี่ยนเป็นสูตรที่ถูกคัดลอกสร้างข้อผิดพลาดมา เซลล์ F2 และ G2.

การอ้างอิงเซลล์สัมบูรณ์จะไม่เปลี่ยนแปลงเมื่อคัดลอกสูตร

เพื่อป้องกันข้อผิดพลาดเราจะแปลงการอ้างอิงเซลล์ D2 ลงในการอ้างอิงเซลล์แบบสัมบูรณ์ การอ้างอิงเซลล์สัมบูรณ์ถูกสร้างขึ้นโดยการกดปุ่ม F4 บนแป้นพิมพ์ การทำเช่นนี้จะเพิ่มเครื่องหมายดอลลาร์ที่อยู่รอบ ๆ การอ้างอิงเซลล์เช่น $ D $ 2.

1. คลิกที่ lookup_value บรรทัดในกล่องโต้ตอบ
2. คลิกที่ เซลล์ D2 เพื่อเพิ่มการอ้างอิงเซลล์นี้ไปที่ lookup_value บรรทัด - นี่คือเซลล์ที่เราจะพิมพ์ชื่อชิ้นส่วนที่เราต้องการหาข้อมูล
3. โดยไม่ต้องย้ายจุดแทรกให้กดปุ่ม F4 คีย์บนแป้นพิมพ์เพื่อแปลง D2 ลงในการอ้างอิงเซลล์แบบสัมบูรณ์ $ D $ 2.
4. ออกจาก VLOOKUP เปิดกล่องโต้ตอบฟังก์ชันสำหรับขั้นตอนถัดไปในบทแนะนำ

06 จาก 10

การป้อนอาร์กิวเมนต์ตาราง

อาร์เรย์ของตารางคือตารางข้อมูลที่สูตรค้นหาค้นหาเพื่อค้นหาข้อมูลที่เราต้องการอาร์เรย์ของตารางต้องมีอย่างน้อยสองคอลัมน์ของข้อมูล

คอลัมน์แรกประกอบด้วยอาร์กิวเมนต์ค่าค้นหา (ขั้นตอนก่อนหน้าในบทแนะนำ) ในขณะที่คอลัมน์ที่สองจะค้นหาตามสูตรการค้นหาเพื่อหาข้อมูลที่เราระบุ

อาร์กิวเมนต์อาร์เรย์ของตารางต้องป้อนเป็นช่วงที่มีการอ้างอิงเซลล์สำหรับตารางข้อมูลหรือเป็นชื่อช่วง

1. คลิกที่ table_array บรรทัดในกล่องโต้ตอบ
2. ชนิด ตาราง เพื่อป้อนชื่อช่วงของอาร์กิวเมนต์นี้
3. ออกจาก VLOOKUP เปิดกล่องโต้ตอบฟังก์ชันสำหรับขั้นตอนถัดไปในบทแนะนำ

07 จาก 10

ทำรังที่ COLUMN Function

ปกติ VLOOKUP จะแสดงเฉพาะข้อมูลจากคอลัมน์หนึ่งของตารางข้อมูลและคอลัมน์นี้จะกำหนดโดย หมายเลขดัชนีคอลัมน์ ข้อโต้แย้ง.

ในตัวอย่างนี้เรามีคอลัมน์ 3 คอลัมน์ที่เราต้องการจะส่งคืนข้อมูลดังนั้นเราจำเป็นต้องมีวิธีในการเปลี่ยนได้อย่างง่ายดาย หมายเลขดัชนีคอลัมน์ โดยไม่ต้องแก้ไขสูตรการค้นหาของเรา - นี่คือที่ คอลัมน์ เข้ามา

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

ฟังก์ชันการทำรัง

คอลัมน์ ฟังก์ชั่นจึงทำหน้าที่เป็น VLOOKUP's หมายเลขดัชนีคอลัมน์ ข้อโต้แย้ง; นี้สามารถทำได้โดยการทำรัง คอลัมน์ ฟังก์ชันภายในของ VLOOKUP ใน col_index_num บรรทัดของกล่องโต้ตอบ

การป้อนฟังก์ชัน COLUMN ด้วยตนเอง

เมื่อทำหน้าที่ทำรัง Excel ไม่อนุญาตให้เราเปิดกล่องโต้ตอบของฟังก์ชันที่สองเพื่อป้อนอาร์กิวเมนต์ คอลัมน์ ดังนั้นจึงต้องป้อนด้วยตนเองใน col_index_num เส้น คอลัมน์ ฟังก์ชันมีเพียงหนึ่งอาร์กิวเมนต์ - การอ้างอิง อาร์กิวเมนต์ซึ่งเป็นการอ้างอิงเซลล์

การเลือกอาร์กิวเมนต์อ้างอิงของฟังก์ชัน COLUMN

คอลัมน์ งานของฟังก์ชันคือการส่งคืนจำนวนคอลัมน์ให้เป็น การอ้างอิง ข้อโต้แย้ง. กล่าวคือจะแปลงตัวอักษรคอลัมน์เป็นตัวเลขด้วย คอลัมน์ A เป็นคอลัมน์แรก, คอลัมน์ B ที่สองและอื่น ๆ

ตั้งแต่ฟิลด์แรกของข้อมูลสิ่งที่เราต้องการคืนคือราคาของรายการซึ่งอยู่ในคอลัมน์ที่สองของตารางข้อมูล เราสามารถเลือกการอ้างอิงเซลล์สำหรับเซลล์ใดก็ได้ คอลัมน์ B เป็น การอ้างอิง อาร์กิวเมนต์เพื่อให้ได้หมายเลข 2 สำหรับ col_index_num ข้อโต้แย้ง.

1. ใน VLOOKUP กล่องโต้ตอบคลิกที่ col_index_num เส้น
2. พิมพ์ชื่อฟังก์ชัน คอลัมน์ ตามด้วย a วงเล็บกลมเปิด.
3. คลิกที่ เซลล์ B1 ในแผ่นงานเพื่อป้อนข้อมูลอ้างอิงเซลล์ดังกล่าว การอ้างอิง ข้อโต้แย้ง.
4. พิมพ์ a ปิดวงเล็บกลม เพื่อให้เสร็จสมบูรณ์ คอลัมน์ ฟังก์ชัน
5. ออกจาก VLOOKUP เปิดกล่องโต้ตอบฟังก์ชันสำหรับขั้นตอนถัดไปในบทแนะนำ

08 จาก 10

การป้อนอาร์กิวเมนต์ Lookup Range VLOOKUP

VLOOKUP's range_lookup อาร์กิวเมนต์เป็นค่าตรรกะ (TRUE หรือ FALSE) ที่ระบุว่าคุณต้องการหรือไม่ VLOOKUP เพื่อค้นหาข้อมูลที่ตรงหรือตรงกับ lookup_value.

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

ในบทแนะนำนี้เนื่องจากเรากำลังมองหาข้อมูลเฉพาะเกี่ยวกับรายการฮาร์ดแวร์เฉพาะเราจะตั้งค่า range_lookup เท่ากับ เท็จ.

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

09 จาก 10

การคัดลอกสูตรค้นหา

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

ในบทแนะนำนี้เราต้องการให้ดึงข้อมูลจาก คอลัมน์ 2, 3, และ 4 ของตารางข้อมูล - นั่นคือราคาเลขที่ชิ้นส่วนและชื่อผู้จัดจำหน่ายเมื่อเราป้อนชื่อบางส่วนเป็น lookup_value.

เนื่องจากข้อมูลถูกวางไว้ในรูปแบบปกติในแผ่นงานเราจึงสามารถคัดลอกสูตรการค้นหาได้ เซลล์ E2 ไปยัง เซลล์ F2 และ G2.

เมื่อสูตรถูกคัดลอก Excel จะอัปเดตการอ้างอิงเซลล์สัมพัทธ์ใน คอลัมน์ ฟังก์ชัน (เซลล์ B1) เพื่อสะท้อนตำแหน่งใหม่ของสูตร

Excel ไม่เปลี่ยนแปลงการอ้างอิงเซลล์แบบสัมบูรณ์ $ D $ 2 และช่วงที่มีชื่อ ตาราง เป็นสูตรจะถูกคัดลอก

มีวิธีการคัดลอกข้อมูลใน Excel มากกว่าหนึ่งวิธี แต่อาจเป็นวิธีที่ง่ายที่สุดคือการใช้ Fill Handle.

1. คลิกที่ เซลล์ E2, ซึ่งมีสูตรการค้นหาอยู่เพื่อทำให้เป็นเซลล์ที่ใช้งานอยู่
2. วาง ตัวชี้เมาส์ ผ่าน สี่เหลี่ยมสีดำ ที่มุมล่างขวา - ตัวชี้จะเปลี่ยนเป็นเครื่องหมายบวกเพื่อแสดงถึงที่จับเติม
3. คลิกปุ่มซ้ายของเมาส์และ ลากที่จับเติม ข้ามไป เซลล์ G2.
4. ปล่อย ปุ่มเมาส์และ เซลล์ F3 ควรมีสูตรการค้นหาแบบสองมิติ
5. ถ้าทำอย่างถูกต้อง เซลล์ F2 และ G2 ตอนนี้ควรมี # N / A ข้อผิดพลาดที่มีอยู่ใน เซลล์ E2

10 จาก 10

การป้อนเกณฑ์การค้นหา

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

เมื่อดำเนินการเสร็จแล้วเซลล์แต่ละสูตรที่มีสูตรการค้นหาจะมีข้อมูลที่แตกต่างกันเกี่ยวกับรายการฮาร์ดแวร์ที่คุณกำลังค้นหา

1. คลิกที่ เซลล์ D2 ในแผ่นงาน
2. ชนิด วิดเจ็ต เข้าไป เซลล์ D2 และกดปุ่ม เข้าสู่ บนแป้นพิมพ์
3. ควรแสดงข้อมูลต่อไปนี้ เซลล์ E2 ไปยัง G2.
   1. E2: $ 14.76 - ราคาของวิดเจ็ต
   2. F2: PN-98769 - หมายเลขชิ้นส่วนสำหรับวิดเจ็ต
   3. G2: วิดเจ็ตอิงค์ - ชื่อซัพพลายเออร์สำหรับวิดเจ็ต
4. ทดสอบ VLOOKUP สูตรอาร์เรย์เพิ่มเติมโดยการพิมพ์ชื่อของส่วนอื่น ๆ ลงใน เซลล์ D2 และการสังเกตผลใน เซลล์ E2 ไปยัง G2