ในบทความนี้เราจะมาใช้ Matrix ช่วยค้นหาข้อมูลตามเงื่อนไขกันครับ ถ้าใครยังไม่ได้อ่าน 2 ตอนก่อนหน้าก็แนะนำให้อ่านก่อนจะได้เข้าใจนะ (โดยเฉพาะตอนที่ 2)
- เล่นกับ Matrix ใน Excel ตอนที่ 1 : ใช้ Matrix แก้สมการ
- เล่นกับ Matrix ใน Excel ตอนที่ 2 : สรุปข้อมูล Total แต่ละแกน
สารบัญ
ค้นหาข้อมูลตามเงื่อนไข
สมมติเรามี Data แบบนี้ แล้วเราจะหาว่ามีแผนกไหนบ้าง ที่ได้ยอดขายตามที่กำหนด
สมมติเราอยากจะ Detect เฉพาะยอดขายตั้งแต่ 70 ขึ้นไป ตามเงื่อนไขต่างๆ (ด้วยการฝึกใช้ Matrix) เช่น
- หาว่าแผนกไหนทำยอดตั้งแต่ 70 ขึ้นไปได้อย่างน้อย 1 เดือน
- หาว่าแผนกไหนทำยอดตั้งแต่ 70 ขึ้นไปได้อย่างน้อย 2 เดือน
การจะหาแบบนั้นได้ เราก็ต้องสร้าง Matrix เงื่อนไขที่ให้ผลลัพธ์เป็น 1,0 ก่อน ดังนี้ (ในวงเล็บจะได้ TRUE/FALSE แล้วพอจับคูณ 1 จะได้ 1,0)
=(C4:E7>=70)*1
ถ้าอยากรู้ว่า Dept แถวไหนบ้างที่ให้ค่าเป็น 1 กี่ตัวตามที่เราต้องการ เราก็ต้องทำการ Sum Total แต่ละแถว ให้ได้ก่อน ว่า Dept ๆ นั้นทำตามเงื่อนไขได้กี่เดือน
ซึ่งการหา Total แต่ละแถว เราได้เรียนรู้ไปในบทความก่อนหน้าแล้วไง
ดังนั้นเราลอง SUM แต่ละแถวก่อน ด้วยการเอาไปคูณ Matrix 3×1 ที่เป็นเลข 1 ทั้งหมด ซึ่งคราวนี้ผมจะ Manual Array {1;1;1} ลงไปในสูตรก่อนเพื่อความ Simple ในความเข้าใจ
=MMULT(G4#,{1;1;1})G4# เป็นการอ้างอิง Array ที่ Spill ออกมาทั้งอัน (ถ้าเป็น Excel version เก่า ก็คือ G4:I7 นั่นแหละ)
มียอดขายอย่างน้อยเดือนใดเดือนหนึ่งตั้งแต่ 70 ขึ้นไป
ถ้าอยากรู้ว่าทำได้อย่างน้อย 1 เดือน ก็เช็คว่า Sum แล้วได้มากกว่าหรือเท่ากับ 1 หรือไม่ แล้วแปลงเป็น 0,1 ซะ ด้วยการคูณ 1 หรือจะใส่ — ข้างหน้าก็ได้ ซึ่งผมจะใส่ — แล้วกันจะได้ดูแล้วไม่งงกับเลข 1 ที่จะใช้เทียบ
=--(MMULT(G4#,{1;1;1})>=1)
ถ้าเขียนรวบทั้งหมดเข้าด้วยกันจะเป็นแบบนี้
=--(MMULT((C4:E7>=70)*1,{1;1;1})>=1)
และถ้าไม่อยากจะ Manual พิมพ์ Matrix 3×1 ที่เป็นเลข 1 แต่เขียนสูตรแบบ Dynamic ตามขนาดของ Data จริงได้ทั้งหมด ก็จะใส่ TRANSPOSE(COLUMN(C4:E7)^0) ลงไปแทน {1;1;1} ก็จะได้แบบนี้
=--(MMULT((C4:E7>=70)*1,TRANSPOSE(COLUMN(C4:E7)^0))>=1)ถ้าอยากรู้ว่าทั้งหมดมีกี่แผนกที่ทำได้ ก็ใช้ SUMPRODUCT หรือ SUM ครอบไป
=SUMPRODUCT(--(MMULT((C4:E7>=70)*1,TRANSPOSE(COLUMN(C4:E7)^0))>=1))
มียอดขายตั้งแต่ 70 ขึ้นไป อย่างน้อย 2 เดือน
เราก็แค่เปลี่ยนเงื่อนไขเดิมเล็กน้อยจากหาอย่างน้อย 1 เดือน
=--(MMULT((C4:E7>=70)*1,{1;1;1})>=1)เป็นต้องได้อย่างน้อย 2 เดือน
=--(MMULT((C4:E7>=70)*1,{1;1;1})>=2)แค่นั้นก็จบแล้ว
ถ้าอยากดึงชื่อ Dept ออกมาด้วยล่ะ?
ถ้ามั่นใจว่าผลลัพธ์มี Dept เดียว (หรือต้องการแสดงแค่ Dept แรกสุด)
ถ้าผลลัพธ์มี Dept เดียวที่ทำได้ตามที่เราต้องการ เช่นในตัวอย่างอันที่สอง เราจะแสดงค่า Dept ออกมาได้ง่ายหน่อย โดยใช้ MATCH หาว่าเลข 1 อยู่ลำดับที่เท่าไหร่ (มันจะเจอ 1 ตัวแรก ซึ่งถ้ามี Dept เดียวจะไม่มีปัญหา) แล้วค่อยใช้ INDEX ถึงชื่อ Dept ตามลำดับที่ MATCH เจอ
=MATCH(1,--(MMULT((C4:E7>=70)*1,{1;1;1})>=2),0)
ซึ่งจะได้ว่าเจออยู่ในลำดับที่ 4
แล้วเราค่อยใช้ INDEX ดึง Dept ออกมา
=INDEX(B4:B7,MATCH(1,--(MMULT((C4:E7>=70)*1,{1;1;1})>=2),0))
ถ้าต้องการให้สามารถแสดง Dept หลายชื่อได้
เช่นในตัวอย่างแรก ที่มี 2 Dept ที่ทำได้ แบบนี้จะใช้ MATCH อย่างเดียวทื่อๆ ไม่ได้แล้ว เพราะมันจะเจอแค่ตัวแรก แต่ถ้าใครมี Office 365 ที่มีฟังก์ชัน Filter ล่ะก็ วิธีนี้จะง่ายกว่า MATCH อีก เช่น
จากเดิมที่เราทำไว้ว่า
เราก็แค่ใช้ฟังก์ชัน FILTER เอาเฉพาะ Dept ที่ได้ 1 ก็จบเลย
=FILTER(B4:B7,G4#)
และถ้าอยากให้มาแสดงในช่องเดียว คั่นด้วย Comma ก็ใช้ TEXTJOIN ได้อีก เช่น
=TEXTJOIN(", ",TRUE,FILTER(B4:B7,G4#))
และหากอยากจะรวบทุกอย่างเข้าด้วยกัน ไม่ทด 1,0 ออกมาข้างนอก ก็จะเป็นแบบนี้
=TEXTJOIN(", ",TRUE,FILTER(B4:B7,--(MMULT((C4:E7>=70)*1,TRANSPOSE(COLUMN(C4:E7)^0))>=1)))
หรือถ้าไม่ใช้ FILTER เราจะใช้ IF มาช่วยก็ได้ (คุณโบ Excel Wizard มาช่วย Comment สูตรให้ประมาณนี้)
=TEXTJOIN(", ",TRUE,IF(MMULT(N(C4:E7>=70),TRANSPOSE(COLUMN(C4:E7))^0),B4:B7,""))ซึ่ง N ก็จะเหมือนกับการเอาไป *1 นั่นแหละ และค่าไหนที่ logical_test ของ IF ออกมาเป็น 0 ก็จะได้ FALSE ซึ่งจะกลายเป็น “” ซึ่งเป็นค่าว่าง ทีนี้ TEXTJOIN สามารถ Skip ค่าว่างได้ ก็เลยสามารถใช้ได้เลยโดยไม่ต้อง FILTER ก็ได้
สำหรับบทความนี้จบเท่านี้ดีกว่า เพราะเริ่มยากละ 555 เดี๋ยวไว้ดูตัวอย่างอื่นที่น่าสนใจกันอีกในบทความหน้า
