ด้วยการรวมฟังก์ชัน VLOOKUP ของ Excel กับฟังก์ชัน COLUMN คุณสามารถสร้างสูตรการค้นหาที่คืนค่าหลายค่าจากแถวเดียวของฐานข้อมูลหรือตารางข้อมูล เรียนรู้วิธีสร้างสูตรการค้นหาที่คืนค่าหลายค่าจากบันทึกข้อมูลเดียว
คำแนะนำในบทความนี้ใช้กับ Excel 2019, 2016, 2013, 2010; และ Excel สำหรับ Microsoft 365
บรรทัดล่าง
สูตรการค้นหากำหนดให้ฟังก์ชัน COLUMN ซ้อนอยู่ภายใน VLOOKUP การซ้อนฟังก์ชันเกี่ยวข้องกับการป้อนฟังก์ชันที่สองเป็นหนึ่งในอาร์กิวเมนต์สำหรับฟังก์ชันแรก
ป้อนข้อมูลการสอน
ในบทช่วยสอนนี้ ฟังก์ชัน COLUMN จะถูกป้อนเป็นอาร์กิวเมนต์หมายเลขดัชนีคอลัมน์สำหรับ VLOOKUP ขั้นตอนสุดท้ายในบทช่วยสอนนี้เกี่ยวข้องกับการคัดลอกสูตรการค้นหาไปยังคอลัมน์เพิ่มเติมเพื่อดึงค่าเพิ่มเติมสำหรับส่วนที่เลือก
ขั้นตอนแรกในบทช่วยสอนนี้คือ การป้อนข้อมูลลงในเวิร์กชีต Excel ในการทำตามขั้นตอนในบทช่วยสอนนี้ ให้ป้อนข้อมูลที่แสดงในภาพด้านล่างลงในเซลล์ต่อไปนี้:
- ใส่ช่วงบนสุดของข้อมูลลงในเซลล์ D1 ถึง G1
- ป้อนช่วงที่สองลงในเซลล์ D4 ถึง G10
เกณฑ์การค้นหาและสูตรการค้นหาที่สร้างในบทช่วยสอนนี้จะถูกป้อนในแถวที่ 2 ของเวิร์กชีต
บทช่วยสอนนี้ไม่มีการจัดรูปแบบ Excel พื้นฐานที่แสดงในรูปภาพ แต่สิ่งนี้ไม่ส่งผลต่อวิธีการทำงานของสูตรการค้นหา
สร้างช่วงที่มีชื่อสำหรับตารางข้อมูล
ช่วงที่ตั้งชื่อเป็นวิธีที่ง่ายในการอ้างถึงช่วงของข้อมูลในสูตร แทนที่จะพิมพ์การอ้างอิงเซลล์สำหรับข้อมูล ให้พิมพ์ชื่อของช่วง
ข้อดีประการที่สองของการใช้ช่วงที่มีชื่อคือการอ้างอิงเซลล์สำหรับช่วงนี้จะไม่เปลี่ยนแปลงแม้ว่าจะคัดลอกสูตรไปยังเซลล์อื่นในเวิร์กชีตก็ตาม ชื่อช่วงเป็นทางเลือกแทนการใช้การอ้างอิงเซลล์แบบสัมบูรณ์เพื่อป้องกันข้อผิดพลาดเมื่อคัดลอกสูตร
ชื่อช่วงไม่มีหัวเรื่องหรือชื่อเขตข้อมูลสำหรับข้อมูล (ดังแสดงในแถวที่ 4) เฉพาะข้อมูลเท่านั้น
-
ไฮไลท์ เซลล์ D5 ถึง G10 ในแผ่นงาน
Image -
วางเคอร์เซอร์ในกล่องชื่อที่อยู่เหนือคอลัมน์ A พิมพ์ Table จากนั้นกด Enter เซลล์ D5 ถึง G10 มีชื่อช่วงของตาราง
Image - ชื่อช่วงสำหรับอาร์กิวเมนต์อาร์เรย์ตาราง VLOOKUP จะใช้ในภายหลังในบทช่วยสอนนี้
เปิดกล่องโต้ตอบ VLOOKUP
แม้ว่าจะสามารถพิมพ์สูตรการค้นหาลงในเซลล์ในเวิร์กชีตได้โดยตรง แต่หลายคนพบว่าการรักษาไวยากรณ์ให้ตรงเป็นเรื่องยาก โดยเฉพาะอย่างยิ่งสำหรับสูตรที่ซับซ้อน เช่น สูตรที่ใช้ในบทช่วยสอนนี้
ให้ใช้กล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชัน VLOOKUP แทน ฟังก์ชันเกือบทั้งหมดของ Excel มีกล่องโต้ตอบที่ป้อนอาร์กิวเมนต์ของฟังก์ชันแต่ละรายการในบรรทัดแยกกัน
-
เลือก เซลล์ E2 ของเวิร์กชีต นี่คือตำแหน่งที่แสดงผลลัพธ์ของสูตรการค้นหาแบบสองมิติ
Image -
บนริบบิ้น ไปที่แท็บ Formulas และเลือก การค้นหาและอ้างอิง
Image -
เลือก VLOOKUP เพื่อเปิด อาร์กิวเมนต์ของฟังก์ชัน กล่องโต้ตอบ
Image - กล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันคือที่ที่ป้อนพารามิเตอร์ของฟังก์ชัน VLOOKUP
ป้อนอาร์กิวเมนต์ค่าการค้นหา
โดยปกติ ค่าการค้นหาจะตรงกับเขตข้อมูลในคอลัมน์แรกของตารางข้อมูล ในตัวอย่างนี้ ค่าการค้นหาอ้างอิงถึงชื่อของส่วนที่คุณต้องการค้นหาข้อมูล ประเภทข้อมูลที่อนุญาตสำหรับค่าการค้นหา ได้แก่ ข้อมูลข้อความ ค่าตรรกะ ตัวเลข และการอ้างอิงเซลล์
การอ้างอิงเซลล์แบบสัมบูรณ์
เมื่อคัดลอกสูตรใน Excel การอ้างอิงเซลล์จะเปลี่ยนเพื่อแสดงตำแหน่งใหม่ หากเป็นเช่นนี้ D2 การอ้างอิงเซลล์สำหรับค่าการค้นหา จะเปลี่ยนแปลงและสร้างข้อผิดพลาดในเซลล์ F2 และ G2
การอ้างอิงเซลล์แบบสัมบูรณ์จะไม่เปลี่ยนแปลงเมื่อมีการคัดลอกสูตร
เพื่อป้องกันข้อผิดพลาด ให้แปลงการอ้างอิงเซลล์ D2 เป็นการอ้างอิงเซลล์แบบสัมบูรณ์ เมื่อต้องการสร้างการอ้างอิงเซลล์แบบสัมบูรณ์ ให้กดแป้น F4 เพิ่มเครื่องหมายดอลลาร์รอบๆ การอ้างอิงเซลล์ เช่น $D$2.
-
ในกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชัน วางเคอร์เซอร์ในกล่องข้อความ lookup_value จากนั้น ในเวิร์กชีต ให้เลือก เซลล์ D2 เพื่อเพิ่มการอ้างอิงเซลล์นี้ไปที่ lookup_value เซลล์ D2 คือตำแหน่งที่จะป้อนชื่อชิ้นส่วน
Image -
โดยไม่ต้องย้ายจุดแทรก ให้กดปุ่ม F4 เพื่อแปลง D2 เป็นการอ้างอิงเซลล์แบบสัมบูรณ์ $D$2.
Image - เปิดกล่องโต้ตอบฟังก์ชัน VLOOKUP ไว้สำหรับขั้นตอนต่อไปในบทช่วยสอน
ป้อนอาร์กิวเมนต์ Table Array
อาร์เรย์ตารางคือตารางข้อมูลที่สูตรค้นหาค้นหาเพื่อค้นหาข้อมูลที่คุณต้องการ อาร์เรย์ของตารางต้องมีข้อมูลอย่างน้อยสองคอลัมน์
คอลัมน์แรกประกอบด้วยอาร์กิวเมนต์ค่าการค้นหา (ซึ่งตั้งค่าไว้ในส่วนก่อนหน้า) ในขณะที่คอลัมน์ที่สองถูกค้นหาโดยสูตรการค้นหาเพื่อค้นหาข้อมูลที่คุณระบุ
ต้องป้อนอาร์กิวเมนต์อาร์เรย์ของตารางเป็นช่วงที่มีการอ้างอิงเซลล์สำหรับตารางข้อมูลหรือเป็นชื่อช่วง
ในการเพิ่มตารางข้อมูลไปยังฟังก์ชัน VLOOKUP ให้วางเคอร์เซอร์ในกล่องข้อความ table_array ในกล่องโต้ตอบและพิมพ์ Tableเพื่อป้อนชื่อช่วงสำหรับอาร์กิวเมนต์นี้
ซ้อนฟังก์ชัน COLUMN
โดยปกติ VLOOKUP จะส่งคืนข้อมูลจากคอลัมน์เดียวของตารางข้อมูลคอลัมน์นี้ถูกกำหนดโดยอาร์กิวเมนต์หมายเลขดัชนีคอลัมน์ อย่างไรก็ตาม ในตัวอย่างนี้ มีสามคอลัมน์ และต้องเปลี่ยนหมายเลขดัชนีคอลัมน์โดยไม่ต้องแก้ไขสูตรการค้นหา ในการทำสิ่งนี้ให้สำเร็จ ให้ซ้อนฟังก์ชัน COLUMN ภายในฟังก์ชัน VLOOKUP เป็นอาร์กิวเมนต์ Col_index_num
เมื่อซ้อนฟังก์ชัน Excel จะไม่เปิดกล่องโต้ตอบของฟังก์ชันที่สองเพื่อป้อนอาร์กิวเมนต์ ต้องป้อนฟังก์ชัน COLUMN ด้วยตนเอง ฟังก์ชัน COLUMN มีอาร์กิวเมนต์เพียงตัวเดียว นั่นคืออาร์กิวเมนต์ Reference ซึ่งเป็นการอ้างอิงเซลล์
ฟังก์ชัน COLUMN ส่งกลับจำนวนคอลัมน์ที่ระบุเป็นอาร์กิวเมนต์อ้างอิง มันแปลงตัวอักษรประจำคอลัมน์เป็นตัวเลข
ในการหาราคาของสินค้า ให้ใช้ข้อมูลในคอลัมน์ 2 ของตารางข้อมูล ตัวอย่างนี้ใช้คอลัมน์ B เป็นข้อมูลอ้างอิงเพื่อแทรก 2 ลงในอาร์กิวเมนต์ Col_index_num
-
ในกล่องโต้ตอบ อาร์กิวเมนต์ของฟังก์ชัน วางเคอร์เซอร์ใน Col_index_num กล่องข้อความ และพิมพ์ COLUMN(. (อย่าลืมใส่วงเล็บเหลี่ยมที่เปิดอยู่)
Image -
ในเวิร์กชีต เลือก cell B1 เพื่อป้อนการอ้างอิงเซลล์นั้นเป็นอาร์กิวเมนต์ Reference
Image - พิมพ์ a ปิดวงเล็บเหลี่ยม เพื่อให้ฟังก์ชัน COLUMN สมบูรณ์
ป้อนอาร์กิวเมนต์การค้นหาช่วง VLOOKUP
อาร์กิวเมนต์ Range_lookup ของ VLOOKUP เป็นค่าตรรกะ (TRUE หรือ FALSE) ที่ระบุว่า VLOOKUP ควรค้นหาค่าที่ตรงกันหรือค่าประมาณที่ตรงกับ Lookup_value
- TRUE or Omitted: VLOOKUP ส่งคืนการจับคู่ที่ใกล้เคียงกับ Lookup_value หากไม่พบการจับคู่แบบตรงทั้งหมด VLOOKUP จะส่งกลับค่าที่มากที่สุดถัดไป ข้อมูลในคอลัมน์แรกของ Table_array ต้องเรียงลำดับจากน้อยไปหามาก
- FALSE: VLOOKUP ใช้การจับคู่แบบตรงทั้งหมดกับ Lookup_value ถ้ามีค่าตั้งแต่สองค่าขึ้นไปในคอลัมน์แรกของ Table_array ที่ตรงกับค่าการค้นหา ค่าแรกที่พบจะถูกใช้ หากไม่พบการจับคู่ที่ตรงกัน ข้อผิดพลาด N/A จะถูกส่งคืน
ในบทช่วยสอนนี้ เราจะค้นหาข้อมูลเฉพาะเกี่ยวกับฮาร์ดแวร์รายการใดรายการหนึ่ง ดังนั้น Range_lookup จะถูกตั้งค่าเป็น FALSE
ในกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชัน ให้วางเคอร์เซอร์ในกล่องข้อความ Range_lookup แล้วพิมพ์ False เพื่อบอกให้ VLOOKUP คืนค่าข้อมูลที่ตรงกันทั้งหมด
เลือก OK เพื่อกรอกสูตรการค้นหาและปิดกล่องโต้ตอบ เซลล์ E2 จะมีข้อผิดพลาด N/A เนื่องจากไม่ได้ป้อนเกณฑ์การค้นหาลงในเซลล์ D2 ข้อผิดพลาดนี้เกิดขึ้นชั่วคราว จะได้รับการแก้ไขเมื่อมีการเพิ่มเกณฑ์การค้นหาในขั้นตอนสุดท้ายของบทช่วยสอนนี้
คัดลอกสูตรการค้นหาและป้อนเกณฑ์
สูตรการค้นหาดึงข้อมูลจากหลายคอลัมน์ของตารางข้อมูลในครั้งเดียว เมื่อต้องการทำเช่นนี้ สูตรการค้นหาต้องอยู่ในทุกฟิลด์ที่คุณต้องการข้อมูล
ในการดึงข้อมูลจากคอลัมน์ 2, 3 และ 4 ของตารางข้อมูล (ราคา หมายเลขชิ้นส่วน และชื่อซัพพลายเออร์) ให้ป้อนชื่อบางส่วนเป็น Lookup_value
เนื่องจากข้อมูลถูกวางในรูปแบบปกติในเวิร์กชีต ให้คัดลอกสูตรการค้นหาใน เซลล์ E2 ถึง เซลล์ F2 และ G2 เมื่อคัดลอกสูตรแล้ว Excel จะอัปเดตการอ้างอิงเซลล์สัมพัทธ์ในฟังก์ชัน COLUMN (เซลล์ B1) เพื่อแสดงตำแหน่งใหม่ของสูตร Excel จะไม่เปลี่ยนการอ้างอิงเซลล์แบบสัมบูรณ์ (เช่น $D$2) และช่วงที่มีชื่อ (ตาราง) เมื่อมีการคัดลอกสูตร
มีหลายวิธีในการคัดลอกข้อมูลใน Excel แต่วิธีที่ง่ายที่สุดคือการใช้ Fill Handle
-
Select cell E2 ที่มีสูตรการค้นหาอยู่ เพื่อทำให้เป็นเซลล์ที่ทำงานอยู่
Image -
ลากที่จับเติมไปที่ เซลล์ G2 เซลล์ F2 และ G2 แสดงข้อผิดพลาด N/A ที่มีอยู่ในเซลล์ E2
Image -
หากต้องการใช้สูตรการค้นหาเพื่อดึงข้อมูลจากตารางข้อมูล ในเวิร์กชีต ให้เลือก cell D2 พิมพ์ Widget แล้วกด Enter.
Image ข้อมูลต่อไปนี้แสดงในเซลล์ E2 ถึง G2
- E2: $14.76 - ราคาของวิดเจ็ต
- F2: PN-98769 - หมายเลขชิ้นส่วนสำหรับวิดเจ็ต
- G2: Widgets Inc. - ชื่อของซัพพลายเออร์สำหรับวิดเจ็ต
-
ทดสอบสูตรอาร์เรย์ VLOOKUP ให้พิมพ์ชื่อส่วนอื่นๆ ลงในเซลล์ D2 และสังเกตผลลัพธ์ในเซลล์ E2 ถึง G2
Image - แต่ละเซลล์ที่มีสูตรการค้นหาจะมีข้อมูลที่แตกต่างกันเกี่ยวกับรายการฮาร์ดแวร์ที่คุณค้นหา
ฟังก์ชัน VLOOKUP ที่มีฟังก์ชันซ้อนกัน เช่น COLUMN เป็นวิธีที่มีประสิทธิภาพในการค้นหาข้อมูลภายในตาราง โดยใช้ข้อมูลอื่นเป็นข้อมูลอ้างอิงในการค้นหา
