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

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

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

01 จาก 10

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

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

สูตรการค้นหาต้องการให้ฟังก์ชัน COLUMN ซ้อนกันภายใน VLOOKUP

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

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

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

บทแนะนำเนื้อหา

• การป้อนข้อมูลบทแนะนำ
• การสร้างช่วงที่ตั้งชื่อสำหรับตารางข้อมูล
• เริ่มต้นฟังก์ชัน VLOOKUP
• การป้อนค่าอาร์กิวเมนต์ค่าค้นหาโดยใช้การอ้างอิงเซลล์สัมบูรณ์
• การป้อนอาร์กิวเมนต์ตาราง
• การป้อน COLUMN ฟังก์ชันที่ซ้อนกัน
• การทำ VLOOKUP Function
• การคัดลอกสูตรค้นหาด้วย Fill Handle
• กำลังเรียกข้อมูลด้วยสูตรค้นหา

02 จาก 10

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

ขั้นตอนแรกในการกวดวิชาคือการใส่ ข้อมูล ลงใน แผ่นงาน Excel

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

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

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

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

ข้อมูลเกี่ยวกับตัวเลือกการจัดรูปแบบที่คล้ายคลึงกับที่เห็นข้างต้นมีอยู่ในคู่มือ การจัดรูปแบบ Excel ขั้นพื้นฐาน นี้

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

1. ป้อนข้อมูลตามที่เห็นในภาพด้านบนลงในเซลล์ D1 ถึง G10

03 จาก 10

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

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

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

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

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

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

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

04 จาก 10

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

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

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

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

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

05 จาก 10

การป้อนค่าอาร์กิวเมนต์ค่าค้นหาโดยใช้การอ้างอิงเซลล์สัมบูรณ์

โดยปกติ ค่าการค้นหาจะ ตรงกับ เขต ข้อมูลใน คอลัมน์ แรกของตารางข้อมูล

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

ประเภท ข้อมูลที่ อนุญาตสำหรับ ค่าการค้นหา คือ:

• ข้อมูลข้อความ
• ค่าตรรกะ (TRUE หรือ FALSE เท่านั้น)
• หมายเลข
• การ อ้างอิงเซลล์ ไปยังค่าในแผ่นงาน

ในตัวอย่างนี้เราจะป้อนการอ้างอิงเซลล์ไปยังตำแหน่งที่ชื่อส่วนหนึ่งจะอยู่ - เซลล์ D2

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

ในขั้นตอนต่อมาในบทแนะนำเราจะคัดลอกสูตรการค้นหาในเซลล์ E2 ไปที่เซลล์ F2 และ G2

โดยปกติเมื่อมีการคัดลอกสูตรใน Excel การอ้างอิงเซลล์จะเปลี่ยนไปเพื่อแสดงตำแหน่งใหม่

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

เพื่อป้องกันความผิดพลาดเราจะแปลงข้อมูลอ้างอิงเซลล์ D2 เป็น ข้อมูลอ้างอิงของเซลล์สัมบูรณ์

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

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

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

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

06 จาก 10

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

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

อาร์เรย์ของตารางต้องมีอย่างน้อยสองคอลัมน์ของ ข้อมูล

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

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

สำหรับตัวอย่างนี้เราจะใช้ชื่อช่วงที่สร้างขึ้นในขั้นตอนที่ 3 ของบทแนะนำ

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

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

07 จาก 10

ทำรังที่ COLUMN Function

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

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

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

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

ฟังก์ชัน COLUMN จึงทำหน้าที่เป็น อาร์กิวเมนต์ หมายเลขดัชนี ของ VLOOKUP

ทำได้โดยการทำฟังก์ชัน COLUMN ภายใน VLOOKUP ในบรรทัด Col_index_num ของกล่องโต้ตอบ

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

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

ฟังก์ชัน COLUMN ต้องป้อนด้วยตนเองในบรรทัด Col_index_num

ฟังก์ชัน COLUMN มีเพียงอาร์กิวเมนต์เดียวอาร์กิวเมนต์ อ้างอิง ซึ่งเป็นข้อมูลอ้างอิงของเซลล์

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

ฟังก์ชัน COLUMN คือการส่งคืนจำนวนคอลัมน์ที่กำหนดให้เป็นอาร์กิวเมนต์ อ้างอิง

กล่าวคือจะแปลงตัวอักษรคอลัมน์เป็นตัวเลขที่มีคอลัมน์ A เป็นคอลัมน์แรกคอลัมน์ B ที่สองเป็นต้น

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

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

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

08 จาก 10

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

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

• ถ้า 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
5. ข้อผิดพลาดนี้จะได้รับการแก้ไขเมื่อเราจะเพิ่มเกณฑ์การค้นหาในขั้นตอนสุดท้ายของการสอน

09 จาก 10

การคัดลอกสูตรค้นหาด้วย Fill Handle

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

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

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

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

เมื่อสูตรถูกคัดลอก Excel จะอัปเดตการ อ้างอิงเซลล์สัมพัทธ์ ในฟังก์ชัน COLUMN (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) และกดปุ่ม ENTER บนแป้นพิมพ์

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

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

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

หากข้อความแสดงข้อผิดพลาดเช่น #REF! ปรากฏในเซลล์ E2, F2 หรือ G2 รายการข้อผิดพลาด VLOOKUP รายการนี้อาจช่วยให้คุณทราบว่าปัญหาอยู่ที่ใด