มีแฟนเพจ inbox ถามผมมาว่าจะ Lookup ข้อมูลจากหลายคอลัมน์ได้ยังไง? ซึ่งโจทย์เป็นประมาณนี้ครับ
ด้านซ้ายมีตารางที่บันทึกการทำงานเอาไว้ ว่าใครจะต้องทำงานวันไหนบ้าง แต่มีการบันทึกวันละ 3 คอลัมน์ แทนที่จะมีคอลัมน์เดียว (อาจเป็นเพราะวันนึงมี slot การทำงาน 3 คนก็เลยบันทึกแบบนี้) ซึ่งการบันทึกแยกกัน 3 คอลัมน์ก็จะทำให้การ Lookup ยุ่งยากไปด้วย
ส่วนด้านขวาก็เป็นสิ่งที่อยากรู้แล้วล่ะ ว่าแต่ละวัน พนักงานแต่ละคนทำงานหรือไม่? (ถ้าเจอในตารางซ้ายแปลว่าทำงาน)
ในบทความนี้เราจะมาดูกันว่ามีวิธีการ 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
วิธีที่ 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 ทุกบรรทัดให้เป็นค่าเดียว
วิธีที่ 3 : ใช้การรวมข้อความก่อนแล้วค่อยหา
ถ้ามีคอลัมน์พนักงาน1 อันเดียว เราก็จะ MATCH แบบนี้ได้
=MATCH(F4&G4,WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน1],0)
แต่ถ้ามีหลายพนักงาน เราก็อาจจะใช้วิธีนำคอลัมน์มารวมกันเป็นอันเดียวซะก่อน แล้วค่อยหา ซึ่งหาแบบ WildCard ก็ได้ เช่น
=MATCH("*"&F4&G4&"*",WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน1]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน2]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน3],0)
ถ้าผลลัพธ์ออกมาเป็นตัวเลขก็แสดงว่าหาเจอ เราก็ใช้ ISNUMBER มาช่วยเช็คได้
=ISNUMBER(MATCH("*"&F4&G4&"*",WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน1]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน2]&WorkTable[วันที่ทำงาน]&WorkTable[พนักงาน3],0))
วิธีที่ 4 : ใช้ Matrix
วิธีนี้จะเข้าใจยากที่สุด จะต้องเข้าใจ Matrix ให้ดีก่อน ดังนั้นถ้าใครอยากดูวิธีเข้าใจง่ายก็ข้ามไป Power Query อันถัดไปได้เลย
เราสามารถหา Total Row ของแต่ละแถวว่ามีพนักงานตรงกับที่กำหนดรึเปล่าได้โดย
=MMULT(--(WorkTable[[พนักงาน1]:[พนักงาน3]]=G4),TRANSPOSE(COLUMN(WorkTable[[พนักงาน1]:[พนักงาน3]])^0))
จากนั้นก็คูณเอาเงื่อนไขวันทำงานเข้าไป เช่น
=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))
จะได้แบบนี้ครับ
วิธีที่ 5 : ใช้ Unpivot และ Merge ใน Power Query
ในเมื่อตารางต้นฉบับแยกหลายคอลัมน์ทำให้มันยาก งั้นใช้ Power Query แปลงให้ถูกต้องก่อนด้วย Unpivot จะได้เป็นการแก้ปัญหาที่แท้จริงไปเลย
เอา WorkTable เข้า Power Query แล้ว Unpivot Other Columns ซะ
จะได้แบบนี้ ก็เปลี่ยนชื่อคอลัมน์ให้เรียบร้อย
จากนั้นก็เอาตารางที่เราจะ Check เข้า Power Query แล้ว Merge กับ WorkTable ที่ Unpivot แล้ว
ซึ่งเราสามารถเลือกคอลัมน์ที่เป็นตัวเชื่อมได้หลายอันพร้อมกันนะ แค่กด Ctrl ค้างไว้เท่านั้นเอง
กด Expand เอา Slot ออกมาก็จะเห็นแล้วว่าเค้าทำงานใน Slot ไหน ถ้า null ก็คือไม่ได้ทำงาน
ถ้าอยากได้ 1,0 หรือ True, False ก็ใช้ Conditional Column มาช่วยก็ได้ครับ ไม่น่ายากเนอะ
วิธีที่ 6 : ใช้ Array แบบรวบทั้งตาราง
(แนะนำโดยคุณโบ Excel Wizard)
หากต้องการแค่ทำงานหรือไม่ จริงๆ ใช้แบบวิธีนี้ทำได้ง่ายมากๆ เลย
=SUM((WorkTable[[พนักงาน1]:[พนักงาน3]]=G4)*(WorkTable[วันที่ทำงาน]=F4))
เพราะผลลัพธ์ของ Array ในแถวเดียวกันจะคูณด้วยกันหมดเลย จากนั้นเราก็แค่ sum เพื่อให้ได้ผลลัพธ์แบบ OR ทั้งตารางแค่นั้นเอง
วิธีอื่นๆ ของคุณโบ
- =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 ได้เลย
