vlookup multiple

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

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

บทความนี้ผมจะมาบอกวิธีเพื่อแสดงผลลัพธ์หลายบรรทัดจากคำค้นหาเดียวให้เอง และจะทำให้ดูหลายแบบด้วยครับ!! ซึ่งมี VLOOKUP อยู่แค่แบบเดียวเท่านั้น นอกนั้นผมใช้วิธีอื่นหมดเลย 555 (ก็จริงๆ แล้ว VLOOKUP มันไม่ได้เหมาะกับเคสแบบนี้นี่นา)

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

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

กลุ่ม 1 : ใช้การเขียนสูตรเพื่อให้เกิดผลลัพธ์หลายค่า

1.1 ใช้ FILTER (ต้องมี Excel 365 หรือ Excel for web )

ถ้าเรามี Excel365 วิธีที่ง่ายสุดๆ สำหรับการเขียนสูตรคือใช้ฟังก์ชัน FILTER นั่นเองครับ ไม่ต้องไปใช้ VLOOKUP นะ (ก็ VLOOKUP มันได้ทีละค่าไง…)

=FILTER(array,include,[if_empty])
=FILTER(พื้นที่ผลลัพธ์,เงื่อนไขที่ต้องการ,[ถ้าไม่มีผลลัพธ์เลยให้แสดงอะไร])

ตรงเงื่อนไขที่ต้องการ เราใส่เป็น TRUE/FALSE หรือ 1,0 ก็ได้ นะครับ และไม่จำเป็นต้องอยู่ใน array ผลลัพธ์ก็ได้นะครับ

เช่น ผมต้องการ order_id ที่สินค้าเป็นแอปเปิ้ล ผมก็เขียนแบบนี้ได้เลย ใน H6

=FILTER(B3:B14,C3:C14=G3)

จะเห็นว่าเงื่อนไข C3:C14 ไม่ได้อยู่ใน B3:B14 นะครับ แค่ต้องจับคู่กันให้ได้เท่านั้นเอง

สังเกตว่าผลลัพธ์จะงอกออกมาหลายตัวเองเลยอัตโนมัติ ซึ่งเรียกว่า Spill ซึ่งเป็นความสามารถของ Excel 365 ที่เรียกว่า Dynamic Array ครับ ใครสนใจลองอ่านบทความนี้เพิ่มเติมได้

ถ้าอยากได้ผลลัพธ์หลายคอลัมน์แบบต่อเนื่องกัน ก็แค่ทำให้ array ผลลัพธ์มีหลายคอลัมน์แค่นั้นเอง

=FILTER(B3:E14,C3:C14=G3)

แต่ถ้ากรณีอยากได้ผลลัพธ์หลายคอลัมน์ที่ไม่ต่อกัน ก็จะต้องพลิกแพลงมากขึ้น ดังนี้

1.1.1 วิธีใช้ FILTER + FILTER

เราสามารถใช้ FILTER 2 รอบ เพื่อเลือกเฉพาะบางคอลัมน์ที่ต้องการได้ด้วยครับ (ขอบคุณ Excel Wizard สำหรับเทคนิคนี้)

=FILTER(FILTER(input สูตร Filter แถว),{เลือกว่าเอาคอลัมน์ไหนบ้าง เป็น 1,0 หรือ TRUE,FALSE})

ตรง Array ผลลัพธ์ผมเปลี่นเป็น B3:E14 ให้คลุมพื้นที่ทั้งหมด ซึ่งมี 4 คอลัมน์
แต่ผมจะเอาแค่ Order id วันที่ ผู้ขาย ซึ่งอยู่คอลัมนืที่ 1,3,4 หรือไม่เอาคอลัมน์ที่ 2 จึงเขียนว่า {1,0,1,1}

ถ้าไม่อยากมานั่งเลือก 1,0 เอง ก็ใช้ MATCH มาช่วยหาว่ามีในเป้าหมายหรือไม่ แล้วใช้ ISNUMBER แปลงเป็น TRUE, FALSE ก็ได้ ดังนี้

=FILTER(FILTER(B3:E14,C3:C14=G3),ISNUMBER(MATCH(B2:E2,H5:J5,0)))
ซึ่งตรง ISNUMBER นั้นถ้าลองกด F9 ดูจะเห็นเป็น {TRUE,FALSE,TRUE,TRUE} ซึ่งก็คือ {1,0,1,1} นั่นเอง

วิธีหลังจะดีกว่าตรงที่มัน Dynamic เปลี่ยนคอลัมน์ที่ต้องการได้ (แต่ลำดับยังเรียงเหมือนเดิมนะครับ)

=FILTER(FILTER(B3:E14,C3:C14=G3),{1,0,1,1})

1.1.2 วิธี FILTER+CHOOSE

สมมติผมอยากจะได้ Order id, ผู้ขาย, วันที่ (สลับเอาวันที่มาอยู่หลัง) ผมคิดว่าใช้ FILTER+CHOOSE น่าจะง่ายสุด ดังนี้

หลักการคือใช้ CHOOSE สร้าตารางจำลองขึ้นมาก่อน แล้วค่อย FILTER ตารางนั้น ซึ่งการสร้างตารางจำลองด้วย CHOOSE สามารถใช้ Array Formula มาช่วยได้ โดยใส่ว่าจะเอากี่คอลัมน์ เช่น เอา 3 คอลัมน์ก็ใส่ {1,2,3} แล้วก็เลือกเลยว่าจะเอาคอลัมน์ไหนเป็นคอลัมน์ 1,2,3

เช่น

=CHOOSE({1,2,3},B3:B14,E3:E14,D3:D14)

แบบนี้จะเป็นการสร้างตารางจำลองขึ้นมาใหม่ได้แล้ว

จากนั้นก็เอา FILTER ไปครอบ ดังนี้

=FILTER(CHOOSE({1,2,3},B3:B14,E3:E14,D3:D14),C3:C14=G3)

เท่านี้เราก็ได้ผลลัพธ์ตามต้องการแล้ว ซึ่งการใช้ FILTER น่าจะเหมาะกับคนที่มี Excel365 เท่านั้น หากใครมี Version เก่ากว่านี้ ก็ไปดูข้อถัดไปได้เลย

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

ปล. วิธีนี้จะเข้าใจง่ายกว่า 1.3 แต่ว่าจะเปลืองคอลัมน์มากกว่า ยังไงลองเลือกดูนะครับ

ต่อไป เราเอาชื่อสินค้าในบรรทัดนั้นๆ ไปเชื่อมกับเลขลำดับ ด้วยการใช้ &
สูตรในช่อง A3 :

=C3&"-"&COUNTIFS($C$3:C3,$G$3)

(ที่ใช้ – คั่นเพื่อความปลอดภัย เผื่อชื่อสินค้าเป็นตัวเลขแล้วจะงง)

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

ทีนี้เราก็ทำการ VLOOKUP ได้แล้ว เพราะค่าในคอลัมน์ A ไม่ซ้ำกันเลย (ในผลไม้ที่เราสนใจ) ดังนั้นสูตรใน H6 จะเป็นดังนี้ :

=VLOOKUP($G$3&"-"&G6,A:B,2,FALSE)

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

ถ้าไม่อยากให้ Error ก็อาจใส่ IFERROR ดักไปก็ได้ เช่น

=IFERROR(VLOOKUP($G$3&"-"&G6,A:B,2,FALSE),"-")
VLOOKUP หลายบรรทัด

1.3 ใช้ ROW กับ SMALL มาช่วย แบบ Array Formula

ถ้าเราใช้วิธีนี้ ก็จะไม่ต้องสร้างคอลัมน์เพิ่มเลย แต่ก็ต้องมีความเข้าใจเกี่ยวกับ Array Formula พอสมควร

หลักการคือ เราจะใช้ IF + ROW เพื่อ แสดงแถวของค่าที่ตรงกับ lookup value เท่านั้น และใช้ SMALL เพื่อเอาแถวที่น้อยสุดเป็นอันดับที่ 1,2,3 ขึ้นมาแสดง

ก่อนแื่น เราใช้ IF + ROW เพื่อ แสดงแถวของค่าที่ตรงกับ lookup value ก่อน ดังนี้

=IF(C3:C14=G3,ROW(C3:C14))

จะเห็นว่ามันแสดงค่าแถวออกมาเฉพาะลำดับที่ตรงกับแอปเปิ้ล แต่มันยังแสดงแบบเว้นๆ กันอยู่

ต่อไปเราจะใช้ SMALL มาช่วย เพื่อให้มันแสดงค่าที่น้อยสุดเป็นอันดับที่ 1,2,3 และอย่าลืม fix cell reference ด้วย เพราะเดี่ยวจะ copy ลงล่าง

=SMALL(IF($C$3:$C$14=$G$3,ROW($C$3:$C$14)),G6)

เท่านี้ผมก็ได้ลำดับแถวที่ตรงกับแอปเปิ้ลแล้ว คือ 3,4,11,14

จากนั้นผมก็ใช้ INDEX ครอบเข้าไปเพื่อ ดึง Order id จากลำดับแถวที่รู้จาก SMALL+ROW ได้เลย

=INDEX(B:B,SMALL(IF($C$3:$C$14=$G$3,ROW($C$3:$C$14)),G6))

กลุ่ม 2 : ใช้เครื่องมือเพื่อให้เกิดผลลัพธ์หลายค่า

2.1 ใช้ Slicer (Excel 2013 ขึ้นไป)

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

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

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

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

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

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

2.2 ใช้ Power Query (Excel 2013 ขึ้นไป)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

สรุปสารพัดวิธี VLOOKUP ผลลัพธ์หลายค่า

เพื่อนๆ ชอบวิธีไหนในการได้ผลลัพธ์หลายค่ามากกว่ากันเอ่ย? หรือใครมีวิธีที่ดีกว่านี้ก็แชร์กันได้นะครับ ^^ ส่วนตัวแล้วผมจะต้องจัดการทุกอย่างใน Power Query ก่อนจะส่งไป Pivot อยู่แล้ว ดังนั้นผมจึงชอบวิธี Power Query มากที่สุดแล้วครับ เพราะทั้งง่ายและทรงพลัง


Comments

One response to “สารพัดเทคนิคสำหรับคนอยาก VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว”

  1. Supatra Avatar

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

Leave a Reply

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