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

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์

lookup-multi-columns

มีแฟนเพจ inbox ถามผมมาว่าจะ Lookup ข้อมูลจากหลายคอลัมน์ได้ยังไง? ซึ่งโจทย์เป็นประมาณนี้ครับ

ด้านซ้ายมีตารางที่บันทึกการทำงานเอาไว้ ว่าใครจะต้องทำงานวันไหนบ้าง แต่มีการบันทึกวันละ 3 คอลัมน์ แทนที่จะมีคอลัมน์เดียว (อาจเป็นเพราะวันนึงมี slot การทำงาน 3 คนก็เลยบันทึกแบบนี้) ซึ่งการบันทึกแยกกัน 3 คอลัมน์ก็จะทำให้การ Lookup ยุ่งยากไปด้วย

ส่วนด้านขวาก็เป็นสิ่งที่อยากรู้แล้วล่ะ ว่าแต่ละวัน พนักงานแต่ละคนทำงานหรือไม่? (ถ้าเจอในตารางซ้ายแปลว่าทำงาน)

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 1

ในบทความนี้เราจะมาดูกันว่ามีวิธีการ Lookup ข้อมูลจากหลายคอลัมน์ด้วยวิธีไหนกันบ้างโดยไม่แก้ข้อมูลต้นฉบับครับ

ถ้าสังเกตดูแล้ว มันก็คือการ Lookup 2 Criteria จำนวน 3 รอบนั่นเองครับ ซึ่งเราแก้ไขได้หลายแบบ

วิธีที่ 1 : ใช้ COUNTIFS หลายๆ รอบแล้วบวกกัน

วิธีแรกน่าจะเข้าใจง่ายสุด นั่นก็คือ ใช้ COUNTIFS ทีละคอลัมน์แล้วจับบวกกัน เช่น ใน H4

หาจากคอลัมน์พนักงาน1 : =COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน1],G4)
หาจากคอลัมน์พนักงาน2 : =COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน2],G4)
หาจากคอลัมน์พนักงาน3 : =COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน3],G4)

จับบวกกัน ก็ได้แบบนี้ ซึ่งถ้าทำงานก็จะออกมาเป็น 1 ถ้าไม่ทำงานก็ออกมาเป็น 0 (กรณีที่ทำซ้ำกันในวันเดียวไม่ได้นะ)

=COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน1],G4)+COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน2],G4)+COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน3],G4)

ถ้ามีสิทธิ์ที่จะทำงานวันนึงซ้ำกันได้มากกว่า 1 รอบ ก็ต้องเช็คว่า ถ้ามันออกมามากกว่า 0 แสดงว่าทำงาน ผลลัพธ์ก็จะออกมาเป็น TRUE/FALSE ทันที จบ

=(COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน1],G4)+COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน2],G4)+COUNTIFS(WorkTable[วันที่ทำงาน],F4,WorkTable[พนักงาน3],G4))>0
สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 2

วิธีที่ 2 : ใช้ Array แบบ Boolean Logic

การใช้ Boolean ถ้าเงื่อนไขเป็น AND จะใช้การคูณ ถ้าเงื่อนไขเป็น OR จะใช้การบวกแล้วเทียบว่ามากกว่า 0 รึเปล่า ดังนั้นสูตรจะออกมาเป็นแบบนี้

=SUMPRODUCT((WorkTable[วันที่ทำงาน]=F4)*(((WorkTable[พนักงาน1]=G4)+(WorkTable[พนักงาน2]=G4)+(WorkTable[พนักงาน3]=G4))>0))

อธิบายทีละส่วนนะ

ส่วนสีฟ้า (((WorkTable[พนักงาน1]=G4)+(WorkTable[พนักงาน2]=G4)+(WorkTable[พนักงาน3]=G4))>0) เป็นการเช็คเงื่อนไขว่าพนักงานคอลัมน์ในคอลัมน์หนึ่งเป็น G4 หรือไม่ ถ้าใช่จะมีอย่างน้อย 1 ตัวได้ 1 พอบวกกันต้องมากกว่า 0 แน่นอน แสดงว่านี้คือเงื่อนไขแบบ OR

แล้วเอาไปคูณกับส่วนสีแดง คือ วันที่ต้องตรงกับ F4 ด้วย การคูณจะเป็นเงื่อนไขแบบ AND เพราะถ้าตัวใดตัวหนึ่งเป็นเท็จ ก็คือจะเป็นการคูณ 0 จะได้ผลลัพธ์ 0 ที่แสดงว่าเป็นเท็จนั่นเอง

ผลลัพธ์แต่ละบรรทัดจะได้ 0,1 แล้วแต่ว่าตรงตามเงื่อนไขหรือไม่ สุดท้ายเราจึงใช้ SUMPRODUCT หรือ SUM เพื่อรวม 0,1 ทุกบรรทัดให้เป็นค่าเดียว

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 3

วิธีที่ 3 : ใช้การรวมข้อความก่อนแล้วค่อยหา

ถ้ามีคอลัมน์พนักงาน1 อันเดียว เราก็จะ MATCH แบบนี้ได้

=MATCH(F4&G4,WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน1],0)

แต่ถ้ามีหลายพนักงาน เราก็อาจจะใช้วิธีนำคอลัมน์มารวมกันเป็นอันเดียวซะก่อน แล้วค่อยหา ซึ่งหาแบบ WildCard ก็ได้ เช่น

=MATCH("*"&F4&G4&"*",WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน1]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน2]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน3],0)
สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 4

ถ้าผลลัพธ์ออกมาเป็นตัวเลขก็แสดงว่าหาเจอ เราก็ใช้ ISNUMBER มาช่วยเช็คได้

=ISNUMBER(MATCH("*"&F4&G4&"*",WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน1]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน2]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน3],0))
สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 5

วิธีที่ 4 : ใช้ Matrix

วิธีนี้จะเข้าใจยากที่สุด จะต้องเข้าใจ Matrix ให้ดีก่อน ดังนั้นถ้าใครอยากดูวิธีเข้าใจง่ายก็ข้ามไป Power Query อันถัดไปได้เลย

เราสามารถหา Total Row ของแต่ละแถวว่ามีพนักงานตรงกับที่กำหนดรึเปล่าได้โดย

=MMULT(--(WorkTable[[พนักงาน1]:[พนักงาน3]]=G4),TRANSPOSE(COLUMN(WorkTable[[พนักงาน1]:[พนักงาน3]])^0))
สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 6

จากนั้นก็คูณเอาเงื่อนไขวันทำงานเข้าไป เช่น

=MMULT(–(WorkTable[[พนักงาน1]:[พนักงาน3]]=G4),TRANSPOSE(COLUMN(WorkTable[[พนักงาน1]:[พนักงาน3]])^0))*(WorkTable[วันที่ทำงาน]=F4)

จากนั้นก็ SUMPRODUCT หรือ SUM เพื่อรวมผลลัพธ์ทุกแถวเข้าด้วยกัน

=SUMPRODUCT(MMULT(--(WorkTable[[พนักงาน1]:[พนักงาน3]]=G4),TRANSPOSE(COLUMN(WorkTable[[พนักงาน1]:[พนักงาน3]])^0))*(WorkTable[วันที่ทำงาน]=F4))

จะได้แบบนี้ครับ

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 7

วิธีที่ 5 : ใช้ Unpivot และ Merge ใน Power Query

ในเมื่อตารางต้นฉบับแยกหลายคอลัมน์ทำให้มันยาก งั้นใช้ Power Query แปลงให้ถูกต้องก่อนด้วย Unpivot จะได้เป็นการแก้ปัญหาที่แท้จริงไปเลย

เอา WorkTable เข้า Power Query แล้ว Unpivot Other Columns ซะ

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 8

จะได้แบบนี้ ก็เปลี่ยนชื่อคอลัมน์ให้เรียบร้อย

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 9

จากนั้นก็เอาตารางที่เราจะ Check เข้า Power Query แล้ว Merge กับ WorkTable ที่ Unpivot แล้ว

ซึ่งเราสามารถเลือกคอลัมน์ที่เป็นตัวเชื่อมได้หลายอันพร้อมกันนะ แค่กด Ctrl ค้างไว้เท่านั้นเอง

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 10

กด Expand เอา Slot ออกมาก็จะเห็นแล้วว่าเค้าทำงานใน Slot ไหน ถ้า null ก็คือไม่ได้ทำงาน

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 11

ถ้าอยากได้ 1,0 หรือ True, False ก็ใช้ Conditional Column มาช่วยก็ได้ครับ ไม่น่ายากเนอะ

วิธีที่ 6 : ใช้ Array แบบรวบทั้งตาราง
(แนะนำโดยคุณโบ Excel Wizard)

หากต้องการแค่ทำงานหรือไม่ จริงๆ ใช้แบบวิธีนี้ทำได้ง่ายมากๆ เลย

=SUM((WorkTable[[พนักงาน1]:[พนักงาน3]]=G4)*(WorkTable[วันที่ทำงาน]=F4))

เพราะผลลัพธ์ของ Array ในแถวเดียวกันจะคูณด้วยกันหมดเลย จากนั้นเราก็แค่ sum เพื่อให้ได้ผลลัพธ์แบบ OR ทั้งตารางแค่นั้นเอง

สารพัดวิธี Lookup ข้อมูลจากหลายคอลัมน์ 12

วิธีอื่นๆ ของคุณโบ

  • =SUM((B$4:D$7=G4)*(A$4:A$7=F4))
  • =SUMPRODUCT((B$4:D$7=G4)*(A$4:A$7=F4))
  • ==–OR(INDEX(B$4:D$7,MATCH(F4,A$4:A$7,),)=G4)
  • =COUNTIF(INDEX(B$4:D$7,MATCH(F4,A$4:A$7,),),G4)
  • =–OR(ISNUMBER(MATCH(B$4:D$7,G4,))*A$4:A$7=F4)
  • =SUM(COUNTIF(F4,IF(COUNTIF(G4,B$4:D$7),A$4:A$7)))
  • =SUMPRODUCT(–(INDEX(B$4:D$7,MATCH(F4,A$4:A$7,),)=G4))

Excel 365 เท่านั้น

  • =–OR(FILTER($B$4:$D$7,$A$4:$A$7=F4)=G4)
  • =–ISNUMBER(XMATCH(G4,XLOOKUP(F4,$A$4:$A$7,$B$4:$D$7)))

สรุปผลจากการลอง Lookup ข้อมูลจากหลายคอลัมน์

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

นี่ก็คือวิธีการทั้งหมดที่ผมอยากนำเสนอ ใครมีวิธีอื่นอีกก็บอกมาได้เลยนะครับ ^^

ส่วนตัวผมก็ชอบวิธี Power Query ที่สุด เพราะว่ากดง่ายดี ไม่ต้องเขียนสูตรครับ แถมมีคอลัมน์เพิ่มก็ไม่กลัวด้วย Refresh ได้เลย

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 814
  •  
  •  
  •  
  •  
  • 814
  •  
  •  
  •  
  •  
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 ให้บริษัทชั้นนำ

Leave a Reply