เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Highlights : บทความแนะนำ

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 1

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

บทความนี้ผมจะมาบอกวิธีทำ VLOOKUP เพื่อแสดงผลลัพธ์หลายบรรทัดให้เอง และจะทำให้ดูหลายแบบด้วยครับ!!

โจทย์คือแบบนี้ครับ เราต้องการเลือกสินค้า แล้วให้แสดง Order ID ทั้งหมดของสินค้านั้นออกมา (ถ้าเราเอา Order ID ออกมาได้แล้ว อย่างอื่นก็คงไม่ยากแล้วจริงมั้ยครับ?)

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 2

วิธีที่ 1 : ใช้สูตรเพื่อทำ VLOOKUP ผลลัพธ์หลายค่า

ใช้ VLOOKUP + Helper Column

ในเมื่อ ปัญหาคือมี lookup_value ซ้ำกันหลายตัว ใน table_array ดังนั้นหลักการในการแก้ไขก็คือ ทำให้ข้อมูลไม่ซ้ำกันซะก่อน เช่น แอปเปิ้ล มีซ้ำกัน 4 ตัว ดังนั้นเราจะทำให้มันไม่ซ้ำกัน เช่น ทำให้เป็น แอปเปิ้ล1 แอปเปิ้ล2 แอปเปิ้ล3 แอปเปิ้ล 4 ซะ

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

สังเกตในสูตรช่อง A7 : =COUNTIFS($C$3:C7,$G$3) ในรูปข้างล่าง
ส่วน criteria_range1 ผมใส่เป็น $C$3:C7 ซึ่งมีการ Fix ตำแหน่งของ C3 ไว้ แต่ไม่ Fix ที่ C7 ทำให้เวลา Copy ลงมาข้างล่าง Range จะขยายเพิ่มขึ้นเรื่อยๆ

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 3

ต่อไป เราเอาชื่อสินค้าในบรรทัดนั้นๆ ไปเชื่อมกับเลขลำดับ ด้วยการใช้ &
สูตรในช่อง A3 : =C3&”-“&COUNTIFS($C$3:C3,$G$3)
(ที่ใช้ – คั่นเพื่อความปลอดภัย เผื่อชื่อสินค้าเป็นตัวเลขแล้วจะงง)

เราก็จะได้ผลลัพธ์แบบนี้

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 4

ทีนี้เราก็ทำการ VLOOKUP ได้แล้ว เพราะค่าในคอลัมน์ A ไม่ซ้ำกันเลย (ในผลไม้ที่เราสนใจ) ดังนั้นสูตรใน H6 จะเป็นดังนี้ : =VLOOKUP($G$3&”-“&G6,A:B,2,FALSE)

ซึ่งในส่วน lookup_value เราเขียนสูตรเอาสินค้าที่สนใจ ไปเชื่อมกับเลขลำดับ ว่า
$G$3&”-“&G6 เพื่อให้ผลลัพธ์ออกมาเหมือนกับในคอลัมน์ A นั่นเอง

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 5

ถ้าไม่อยากให้ Error ก็อาจใส่ IFERROR ดักไปก็ได้ เช่น =IFERROR(VLOOKUP($G$3&”-“&G6,A:B,2,FALSE),”-“)

VLOOKUP หลายบรรทัด

วิธีที่ 2 : ใช้เครื่องมือเพื่อเลียนแบบ VLOOKUP ผลลัพธ์หลายค่า

2.1 ใช้ Slicer

วิธีนี้จะเรียกว่าโกงก็ได้ มันคือการแปลง Data ให้เป็น Table แล้วใส่ Slicer จบเลย 555

เลือกข้อมูล 1 ช่อง กด Insert –> Table หรือ Ctrl+T

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 6

กด Insert Slicer แล้วเลือกสินค้า

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 7

จากนั้นเลือกสินค้าที่สนใจได้อย่างง่ายดาย

Silcer และ Table เพื่อเลียนแบบ VLOOKUP หลายบรรทัด

เนื่องจากวิธีนี้ดูขี้โกงไปหน่อย งั้นแถมวิธีใช้ Power Query ให้ละกันครับ

2.2 ใช้ Power Query

ก่อนอื่น เราต้องเอาข้อมูลสินค้าที่เราสนใจเข้า PowerQuery ด้วย ก่อน
ด้วยการ Get Data from Table/Range ดังรูป

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 8

ผมตั้งชื่อตารางแรกว่า OrderDatabase แล้ว กด Close & Load to… Only Create Connection เพื่อให้ยังไม่ต้องสร้างตารางผลลัพธ์ออกมา

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 9

จากนั้น เอาข้อมูลสินค้าที่เราสนใจเข้า PowerQuery ด้วย Get Data From Table อีกที

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 10

แล้วตั้งชื่อว่า SelectedProduct แล้ว Close&Load to… only create connection อีกที

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 11

จากนั้นเราจะใช้ Merge Query ดึงข้อมูลมาเฉพาะสิ่งที่สนใจ

ให้เราไปสร้าง Merge Query ขึ้นมาใหม่

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 12

จากนั้นก็เลือกทั้งสองตาราง แล้วเลือกคอลัมน์ที่เป็นตัวเชื่อมซะ ซึ่งก็คือสินค้า แล้วเลือก Join Kind เป็น Inner (แปลว่าต้องเจอค่าในทั้ง 2 ตาราง) แล้ว กด ok

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 13

กดปุ่มขวาบนตามรูป และติ๊กว่าไม่เอา prefix (เพราะมันอ่านยาก)

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 14

ลบคอลัมน์ที่ไม่ต้องการซะ เช่น สินค้า แล้วถ้าต้องการเลข running ก็ Add Index Column From 1 ได้

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 15

กด Close & Load to … Table แล้วเลือกให้วางยัง Existing Worksheet ตรงที่ต้องการ แล้วกด ok จบ

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 16

จากนั้นเราก็จะได้ผลลัพธ์ออกมาครับ

สอน 2 เทคนิคการทำ VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว 17

แต่วิธีนี้มีข้อเสียคือ ถ้าเปลี่ยนสินค้าแล้ว อย่าลืมกดคลิ๊กขวาที่ตารางแล้ว Refresh นะ (ยกเว้นจะผูกกับ VBA ก็อาจจะช่วยได้)

Power Query เพื่อเลียนแบบ VLOOKUP หลายบรรทัด

เพียงเท่านี้ก็เสร็จแล้วครับ

เพื่อนๆ ชอบวิธีไหนในการทำ VLOOKUP ผลลัพธ์หลายค่า มากกว่ากันเอ่ย? หรือใครมีวิธีที่ดีกว่านี้ก็แชร์กันได้นะครับ ^^

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 3.1K
  •  
  •  
  •  
  •  
  • 3.1K
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

1 Comment

  • ข้อมูลของแอปเปิ้ล แต่อยากจะให้โชว์เฉพาะข้อมูลล่าสุดเป็นOR0012
    ต้องเขียนสูตรยังไงคะ

Leave a Reply