จะทำยังไงถึงจะทำ 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),"-")
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 แล้วเลือกสินค้า
จากนั้นเลือกสินค้าที่สนใจได้อย่างง่ายดาย
เนื่องจากวิธีนี้ดูขี้โกงไปหน่อย งั้นแถมวิธีใช้ 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 ก็อาจจะช่วยได้)
เพียงเท่านี้ก็เสร็จแล้วครับ
สรุปสารพัดวิธี VLOOKUP ผลลัพธ์หลายค่า
เพื่อนๆ ชอบวิธีไหนในการได้ผลลัพธ์หลายค่ามากกว่ากันเอ่ย? หรือใครมีวิธีที่ดีกว่านี้ก็แชร์กันได้นะครับ ^^ ส่วนตัวแล้วผมจะต้องจัดการทุกอย่างใน Power Query ก่อนจะส่งไป Pivot อยู่แล้ว ดังนั้นผมจึงชอบวิธี Power Query มากที่สุดแล้วครับ เพราะทั้งง่ายและทรงพลัง
