“อยากให้ Excel แสดงเลขลำดับบรรทัดที่ตรงกับเงื่อนไข ต้องเขียนสูตรยังไงครับ?” ตั้มจู่โจมด้วยคำถาม
“พอจะยกตัวอย่างได้ไหม” ผมถามกลับ
“คืออย่างนี้ครับ สมมติผมมีคอลัมน์แสดง Product Code อยากเขียนสูตรแสดงลำดับ 1, 2, 3, 4 เฉพาะ Product Code ที่มีตัว a ครับ”

ข้อมูลที่ตั้มพูดถึง หน้าตาแบบนี้ครับ

ต้องการเขียนสูตรแล้วได้ผลลัพธ์แบบนี้

โจทย์ข้อนี้ดูเหมือนง่าย แต่เอาเข้าจริงแล้วไม่ง่ายเลย เวลาคิดมีทั้งหมด 4 step ด้วยกันคือ

Step 1: ระบุบรรทัดที่ตรงกับเงื่อนไข

สมมติระบุเงื่อนไข (ตัวอักษร a) ที่เซลล์ B3 อยากหาว่า Product Code ใดบ้างที่มีตัว a
ก็ใช้ฟังก์ชัน FIND
เขียนสูตรที่เซลล์ B6 แบบนี้

=FIND($B$3,A6)

ตัวเลขที่แสดงคือ ลำดับของตัวอักษร a ในเซลล์นั้นๆ
เช่น ลำดับที่ 4 ในเซลล์ B6 (GE-amr-0430)
ลำดับที่ 6 ในเซลล์ B8 (AZ-kba-0964)

แต่ปัญหาคือ ข้อมูลที่ไม่ตรงกับเงื่อนไขจะแสดงค่าเป็น #VALUE!
จึงเป็นที่มาของ Step 2

Step 2: จัดการค่าที่ไม่ตรงกับเงื่อนไข

อันที่จริงเคสนี้ไม่ได้ต้องการลำดับของตัวอักษร a แต่ต้องการเช็คว่าข้อมูลใดที่สอดคล้องกับเงื่อนไข และข้อมูลใดที่ไม่สอดคล้องกับเงื่อนไข

ถ้าใช้ FIND จะเห็นว่าข้อมูลที่สอดคล้องกับเงื่อนไขแสดงผลลัพธ์เป็นตัวเลข ส่วนข้อมูลที่ไม่สอดคล้องแสดงผลลัพธ์เป็น #VALUE!

งั้นก็ใช้ฟังก์ชัน ISNUMBER มาครอบ FIND เพื่อกำจัด #VALUE! แบบนี้

=ISNUMBER( FIND($B$3,A6) )

ISNUMBER คือฟังก์ชันที่ใช้ตรวจสอบว่า ข้อมูลนั้นเป็นตัวเลขหรือไม่
ถ้าเป็นตัวเลข ให้ผลลัพธ์เป็น TRUE
ถ้าไม่ใช่ตัวเลข ให้ผลลัพธ์เป็น FALSE

จะพบว่า #VALUE! ถูกเปลี่ยนเป็น FALSE
แต่… ข้อมูลที่สอดคล้องกับเงื่อนไขดันกลายเป็น TRUE นี่สิ
จึงเป็นที่มาของ step 3

Step 3: เปลี่ยน TRUE ให้เป็นเลขลำดับ

การเปลี่ยน TRUE ให้เป็นเลขลำดับนั้น ถ้าพูดง่ายๆก็คือการนับข้อมูลที่เป็น TRUE นั่นเอง แต่ต้องนับช่วงตั้งแต่บรรทัดแรกจนถึงบรรทัดนั้นๆ

เช่น
ถ้าตอนนี้อยู่ที่เซลล์ A6 ต้องนับช่วง A6:A6
ถ้าตอนนี้อยู่ที่เซลล์ A7 ต้องนับช่วง A6:A7
ถ้าตอนนี้อยู่ที่เซลล์ A8 ต้องนับช่วง A6:A8

นั่นคือต้องล็อคค่าเริ่มต้นที่ $A$6 และไม่ต้องล็อคค่าสุดท้าย
ถ้าตอนนี้อยู่ที่เซลล์ A12 ต้องนับช่วง $A$6:A12
หรือเขียน FIND ใหม่เป็น

FIND($B$3,$A$6:A12)

นำ ISNUMBER ไปครอบเหมือนเดิม หรือเขียนสูตรเป็น

ISNUMBER( FIND($B$3,$A$6:A12) )

แต่… ถ้าเขียนสูตรแค่นี้ จะได้ผลลัพธ์เป็น

TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE

เคสนี้เราต้องการนับ TRUE
ดังนั้นต้องเปลี่ยนคำว่า TRUE ให้เป็นตัวเลข
เทคนิคการเปลี่ยน TRUE ให้เป็นตัวเลข คือการใส่เครื่องหมาย – – (เครื่องหมายลบติดกันสองครั้ง) นำหน้า

• – –ISNUMBER( FIND($B$3,$A$6:A12) )

ถ้าเขียนสูตรแค่นี้ จะได้ผลลัพธ์เป็น

1;0;1;0;0;0;1

แต่เราต้องการผลลัพธ์เป็น 3 ดังนั้นต้องรวมค่าทั้งหมดเข้าด้วยกัน หรือนำฟังก์ชัน SUMPRODUCT ไปครอบ
เขียนสูตรเป็น

=SUMPRODUCT( – -ISNUMBER( FIND($B$3,$A$6:A12) ) )

เริ่มใกล้ความจริงแล้วใช่ไหมครับ ^__^

อันที่จริงเคสนี้ใช้ฟังก์ชัน SUM แทน SUMPRODUCT ก็ได้
แต่.. สูตรนี้เป็นสูตรอาร์เรย์ (Array Formula) ถ้าไม่ได้ใช้ Office 365 เวอร์ชันล่าสุดจำเป็นต้องกด Ctrl+Shft+Enter ด้วย พอกดแล้วสูตรจะมีวงเล็บปีกกาครอบหัว-ท้ายแบบนี้

{=SUM( –ISNUMBER(FIND($B$3,$A$6:A6) ) )}

ดูแล้วใช้ SUMPRODUCT น่าจะง่ายกว่าใช่ไหมครับ ^^

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

ดังนั้น ต้องหาทางเปลี่ยนค่าที่ไม่ตรงกับเงื่อนไขให้เป็นค่าว่าง
จึงเป็นที่มาของ Step 4

Step 4: เปลี่ยนค่าที่ไม่ตรงกับเงื่อนไขให้เป็นค่าว่าง

เคสนี้ทุกเซลล์แสดงผลลัพธ์ได้ 2 แบบ
แบบแรกคือ ถ้าตรงกับเงื่อนไข ให้แสดงเป็นเลขลำดับ
แบบที่สองคือ ถ้าไม่ตรงกับเงื่อนไข ให้แสดงเป็นค่าว่าง

พอเขียนตรรกะเป็นพรรณาโวหารแบบนี้ อ่านดูแล้วคล้ายเข้าเหลี่ยมฟังก์ชันใดไหมครับ?
ติ๊กต่อก
ติ๊กต่อก …

ใช่แล้วครับ เข้าเหลี่ยมฟังก์ชัน IF นั่นเอง ^_^

นั่นคือ ถ้าผลลัพธ์เป็น TRUE ให้แสดงเป็นเลขลำดับ หรือก็คือสูตรเดิม SUMPRODUCT(- -ISNUMBER( FIND($B$3,$A$6:A6) ) )

ถ้าผลลัพธ์เป็น FALSE ให้แสดงเป็นค่าว่าง หรือก็คือ “”

แล้วเงื่อนไขล่ะ?

เงื่อนไขคือ เซลล์นั้นต้องมีตัว a
พอเขียนตรรกะเป็นพรรณาโวหารแบบนี้ คล้ายเข้าเหลี่ยมฟังก์ชันใดไหมครับ?

ใช่แล้วครับ เข้าเหลี่ยมฟังก์ชัน FIND เพื่อนเก่าเรานั่นเอง!
เราก็ใช้ฟังก์ชัน FIND แบบนี้

FIND($B$3,A6)

แต่ถ้าเขียนแค่นี้ เซลล์ที่ไม่มี a จะกลายเป็น #VALUE!
ต้องการเปลี่ยน #VALUE! ให้กลายเป็น FALSE ก็นำฟังก์ชัน ISNUMBER มาครอบ
หรือเขียนเป็น

ISNUMBER( FIND($B$3,A6) )

หรือเขียนสูตรทั้งหมดเต็มๆเป็น

=IF( ISNUMBER(FIND($B$3,A6)), SUMPRODUCT(- -ISNUMBER( FIND($B$3,$A$6:A6) ) ), “”)

“จัดระเบียบ” สูตรให้อ่านง่ายหน่อย อาจเรียบเรียงเป็น

ว้าว! สูตรยาวนิดนึง แต่ถ้าเข้าใจก็ไม่ยากใช่ไหมครับ ^__^

จริงๆแล้วถ้าเคสนี้ไม่ใช่ case sensitive คือเป็นได้ทั้ง a และ A ให้ใช้ฟังก์ชัน SEARCH แทน FIND

หรือเขียนสูตรเป็น

=IF( ISNUMBER(SEARCH($B$3,A6)), SUMPRODUCT(- -ISNUMBER( SEARCH($B$3,$A$6:A6) ) ), “”)

เพราะความต่างระหว่าง FIND กับ SEARCH คือ
FIND เป็น case sensitive
SEARCH ไม่เป็น case sensitive นั่นคือเคสนี้เป็น a หรือ A ก็ได้

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

“คือพอได้เลขบรรทัดที่ต้องการแล้ว ตั้มก็จะฟิลเตอร์ตารางนี้ ก็อปปี้ข้อมูลเฉพาะบรรทัดที่ต้องการ นำไปแปะอีกชีตนึง แล้วก็ทำอะไรบางอย่างต่อใช่ไหม?”
“เฮ้ย! พี่รู้ได้ไงอ่ะ?” ตั้มอึ้งเมื่อได้ยินสิ่งที่ผมพูด

จากประสบการณ์ตรง ผมพบว่าบางครั้งเราอาจยึดติดกับ “กระบวนการ” มากเกินไป โดยไม่ได้มองว่าจริงๆแล้ว output สุดท้ายที่ต้องการคืออะไร

ถ้ามองที่ Output สุดท้ายเพียงอย่างเดียวแล้วเปิดใจกว้าง จะพบการใช้ Power Query ร่วมกับ Pivot Table/ Power Pivot ตอบโจทย์สุดท้ายที่ต้องการแบบตรงจุด ลดกระบวนการที่ไม่จำเป็นได้เยอะมาก

โจทย์ข้อนี้ ถ้ามองในแง่ของการฝึกตรรกะถือว่าดีมาก เพราะได้ลับสมองและทำความเข้าใจฟังก์ชันในเชิงลึก
แต่ถ้ามองในแง่ของ Productivity จะพบว่านี่คือกระบวนการที่หลีกเลี่ยงได้ ยังมีวิธีอีกมากมายที่ได้ Output สุดท้ายแบบเดียวกันและมีขั้นตอนน้อยกว่า

คล้ายกับชีวิตเรา ถ้าโฟกัสที่เป้าหมาย จะพบว่ามีกระบวนการดีๆอีกมากมาย
แต่ถ้าโฟกัสที่กระบวนการ สุดท้ายอาจผิดเป้าหมายโดยไม่รู้ตัว …

แล้วผมรู้ได้ไงว่าตั้มจะเอาไปฟิลเตอร์แล้วก็อปแปะน่ะหรือครับ?
เพราะผมเคยทำมาก่อนไงล่ะ ^^
ชูวส์ !!

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่โดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

อ้อ! ตอนนี้ผมมีสอนคอร์สออนไลน์ ชื่อ “Excel In Essence: รู้ Excel เท่านี้ ชีวิตดีขึ้นมากมาย” สนใจดูรายละเอียดได้จาก ลิงค์นี้ ครับ