สอน VLOOKUP แสดงผลลัพธ์หลายค่า จากคำค้นหาเดียว

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

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

บทความนี้ผมจะมาบอกวิธีทำให้ และจะทำให้ดูหลายแบบด้วยครับ

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

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

วิธีที่ 1 : ใช้สูตร

ใช้ 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 จะขยายเพิ่มขึ้นเรื่อยๆ

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

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

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

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

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

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

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

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

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

วิธีที่ 2 : ใช้เครื่องมือ

2.1 ใช้ Slicer

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

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

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

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

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

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

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

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

2.2 ใช้ Power Query

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

สอน VLOOKUP แสดงผลลัพธ์หลายค่า จากคำค้นหาเดียว 18

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

สอน VLOOKUP แสดงผลลัพธ์หลายค่า จากคำค้นหาเดียว 19

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

สอน VLOOKUP แสดงผลลัพธ์หลายค่า จากคำค้นหาเดียว 20

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

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ
==========================================

  • 2.8K
  •  
  •  
  •  
  •  

Posted on: May 9, 2019
Tags: , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *