เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Excel Array FormulaExcel and Matrix

เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข

เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 1

ในบทความนี้เราจะมาใช้ Matrix ช่วยค้นหาข้อมูลตามเงื่อนไขกันครับ ถ้าใครยังไม่ได้อ่าน 2 ตอนก่อนหน้าก็แนะนำให้อ่านก่อนจะได้เข้าใจนะ (โดยเฉพาะตอนที่ 2)

ค้นหาข้อมูลตามเงื่อนไข

สมมติเรามี Data แบบนี้ แล้วเราจะหาว่ามีแผนกไหนบ้าง ที่ได้ยอดขายตามที่กำหนด

สมมติเราอยากจะ Detect เฉพาะยอดขายตั้งแต่ 70 ขึ้นไป ตามเงื่อนไขต่างๆ (ด้วยการฝึกใช้ Matrix) เช่น

  • หาว่าแผนกไหนทำยอดตั้งแต่ 70 ขึ้นไปได้อย่างน้อย 1 เดือน
  • หาว่าแผนกไหนทำยอดตั้งแต่ 70 ขึ้นไปได้อย่างน้อย 2 เดือน
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 2

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

=(C4:E7>=70)*1
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 3

ถ้าอยากรู้ว่า 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 นั่นแหละ)

เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 4

มียอดขายอย่างน้อยเดือนใดเดือนหนึ่งตั้งแต่ 70 ขึ้นไป

ถ้าอยากรู้ว่าทำได้อย่างน้อย 1 เดือน ก็เช็คว่า Sum แล้วได้มากกว่าหรือเท่ากับ 1 หรือไม่ แล้วแปลงเป็น 0,1 ซะ ด้วยการคูณ 1 หรือจะใส่ — ข้างหน้าก็ได้ ซึ่งผมจะใส่ — แล้วกันจะได้ดูแล้วไม่งงกับเลข 1 ที่จะใช้เทียบ

=--(MMULT(G4#,{1;1;1})>=1)
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 5

ถ้าเขียนรวบทั้งหมดเข้าด้วยกันจะเป็นแบบนี้

=--(MMULT((C4:E7>=70)*1,{1;1;1})>=1)
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 6

และถ้าไม่อยากจะ 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))
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 7

มียอดขายตั้งแต่ 70 ขึ้นไป อย่างน้อย 2 เดือน

เราก็แค่เปลี่ยนเงื่อนไขเดิมเล็กน้อยจากหาอย่างน้อย 1 เดือน

=--(MMULT((C4:E7>=70)*1,{1;1;1})>=1)

เป็นต้องได้อย่างน้อย 2 เดือน

=--(MMULT((C4:E7>=70)*1,{1;1;1})>=2)

แค่นั้นก็จบแล้ว

เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 8

ถ้าอยากดึงชื่อ 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 อีก เช่น

จากเดิมที่เราทำไว้ว่า

เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 9

เราก็แค่ใช้ฟังก์ชัน FILTER เอาเฉพาะ Dept ที่ได้ 1 ก็จบเลย

=FILTER(B4:B7,G4#)
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 10

และถ้าอยากให้มาแสดงในช่องเดียว คั่นด้วย Comma ก็ใช้ TEXTJOIN ได้อีก เช่น

=TEXTJOIN(", ",TRUE,FILTER(B4:B7,G4#))
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 11

และหากอยากจะรวบทุกอย่างเข้าด้วยกัน ไม่ทด 1,0 ออกมาข้างนอก ก็จะเป็นแบบนี้

=TEXTJOIN(", ",TRUE,FILTER(B4:B7,--(MMULT((C4:E7>=70)*1,TRANSPOSE(COLUMN(C4:E7)^0))>=1)))
เล่นกับ Matrix ใน Excel ตอนที่ 3 : ค้นหาข้อมูลตามเงื่อนไข 12

หรือถ้าไม่ใช้ 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 เดี๋ยวไว้ดูตัวอย่างอื่นที่น่าสนใจกันอีกในบทความหน้า

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