“อยาก Rank ข้อมูลที่เป็น Text ทำยังไงดีครับ?” ตั้มจู่โจมด้วยคำถาม

ผมฟังแล้วสตันไป 3 วิ เพราะฟังก์ชัน RANK.EQ หรือ RANK.AVG ใช้ได้กับข้อมูลที่เป็นตัวเลข
แต่ใช้กับข้อมูลที่เป็น Text ไม่ได้!

งั้นทำยังไงดี?

ต้องมี ‘เล่นแรแปรธาตุ’ กันนิดนึงครับ ^_^

ก่อนจะ Rank หาอันดับของ Text เรามาทำความเข้าใจหลักการเรียงข้อมูลกันก่อน
ในมุมมองของ Excel นั้น อักขระ (Character) ทุกตัวมีโค้ด เช่น
โค้ดของ 1 คือ 49
โค้ดของ 2 คือ 50
โค้ดของ A คือ 65
โค้ดของ B คือ 66
โค้ดของ a คือ 97
โค้ดของ b คือ 98

โค้ดที่ว่านี้ อ้างอิงตามหลัก ASCII ซึ่งเป็นหลักสากล

สามารถเช็คโค้ดของทุกอักขระโดยใช้ฟังก์ชัน CODE (หรือ UNICODE)

จากหลักการนี้
A<B
B<C
และ A<B<C

คราวนี้กลับมาเรื่องการ Rank ข้อมูลที่เป็นตัวอักษรกัน
เพื่อให้เข้าใจง่ายขึ้น สมมติข้อมูลเป็นชื่อสินค้า หน้าตาประมาณนี้

ต้องการเขียนสูตรที่คอลัมน์ B เพื่อแสดงอันดับของสินค้า
ถ้าเป็น Computer ต้องได้ผลลัพธ์เท่ากับ 3
ถ้าเป็น Book ต้องได้ผลลัพธ์เท่ากับ 1
ถ้าเป็น Chair ต้องได้ผลลัพธ์เท่ากับ 2

ตรรกะที่ใช้คือ นับข้อมูลที่น้อยกว่าหรือเท่ากับตัวมันเอง

จากหลักการเรียงข้อมูล
A<B<C
นั่นคือ
Book<Chair<Computer<Desk

ช่วงข้อมูลคือ $A$2:$A$11
ถ้าตัวมันเป็น Book นับแค่ Book
ถ้าตัวมันเป็น Chair นับแค่ Book, Chair
ถ้าตัวมันเป็น Computer นับแค่ Book, Chair, Computer

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

=COUNTIF($A$2:$A$11,"<="&A2)

“ว้าว! เพิ่งรู้ว่าเขียนสูตรแบบนี้ได้ด้วย” ตั้มโห่ร้อง
“มันต้องเล่นแร่แปรธาตุกันนิดนึง” ผมยิ้ม

“ว่าแต่.. ทดลองแล้วเจอปัญหานิดนึงครับ”
“ปัญหาคือ?” ผมถามกลับ
“คือถ้ามีข้อมูลซ้ำจะได้อันดับเท่ากัน แต่ผมอยากให้อันดับเรียงต่อกันไปเลย ทำไงดีครับ?”

เพื่อให้เห็นภาพ สมมติ Chair, Notebook มีข้อมูลซ้ำ แล้วใช้สูตรเดิม ผลลัพธ์จะกลายเป็น

แต่สิ่งที่ตั้มต้องการคือ ต้องเรียงอันดับต่อกันไปเลย

ถ้าต้องการผลลัพธ์แบบนี้ ต้องเปลี่ยนตรรกะการเขียนสูตรเป็น

นับข้อมูลที่น้อยกว่าตัวเอง + นับข้อมูลที่เป็นตัวเองตั้งแต่แถวแรกจนถึงแถวที่ตัวเองอยู่

โดย นับข้อมูลที่น้อยกว่าตัวเอง เขียนสูตรได้เป็น

=COUNTIF($A$2:$A$11,"<"&A2)

ถ้าเทียบกับสูตรแรก ก็แค่เปลี่ยนเครื่องหมายจาก “<=” เป็น “=” นั่นเอง

ส่วน นับข้อมูลที่เป็นตัวเองตั้งแต่แถวแรกจนถึงแถวที่ตัวเองอยู่ เขียนสูตรได้เป็น

=COUNTIF($A$2:A2,A2)

สังเกตว่าใช้ช่วงข้อมูลเป็น $A$2:A2 แปลว่าให้ล็อกข้อมูลแถวแรก ($A$2) ส่วนข้อมูลแถวหลัง (A2) เปลี่ยนไปตามบรรทัดนั้น ๆ

รวมสูตรเข้าด้วยกันเป็น

=COUNTIF($A$2:$A$11,"<"&A2)
+COUNTIF($A$2:A2,A2)

“โอ้! ใช่เลยครับ” ตั้มร้อง

“ว่าแต่… ผมเจอปัญหาอีกนิดนึงครับ?”
“ว่าไงตั้ม”
“คือถ้าข้อมูลมีตัวเลขด้วย สูตรจะผิดครับ”

ถ้ามีตัวเลขด้วย ข้อมูลที่เป็นตัวหนังสือกับตัวเลขจะถูกนับแยกกัน

แต่สิ่งที่ตั้มต้องการคือ ต้องนับตัวเลขก่อน แล้วต่อด้วยตัวหนังสือ แบบนี้

งั้นคงใช้ตรรกะเดิมไม่ได้ ต้องใช้ตรรกะใหม่เป็น

นับข้อมูลที่น้อยกว่าหรือเท่ากับตัวมันเอง + นับจำนวนตัวเลขทั้งหมด (ถ้าตัวมันเองเป็น Text)

นับข้อมูลที่น้อยกว่าหรือเท่ากับตัวมันเอง ใช้สูตรเดิมคือ COUNTIF($A$2:$A$11,”<=”&A2)

ปัญหาคือ นับจำนวนตัวเลขทั้งหมด (ถ้าตัวมันเองเป็น Text) จะเขียนสูตรยังไง?

ถ้าเป็น นับจำนวนตัวเลขทั้งหมด
สูตรที่ใช้คือ COUNT($A$2:$A$11)
แต่ต้องนับเฉพาะเคสที่ตัวมันเองเป็น Text

หลายคนอาจนึกถึงฟังก์ชัน IF
จริง ๆ แล้วใช้ IF ก็ได้ แต่สูตรจะยาวนิดนึง
เคสนี้ใช้ฟังก์ชัน ISTEXT ดีกว่า โดยใช้ ISTEXT เช็คว่าเป็น Text หรือเปล่า แล้วนำไปคูณกัน
(ผลลัพธ์ของ ISTEXT คือ TRUE, FALSE)
หรือเขียนสูตรเป็น

=COUNT($A$2:$A$11)*ISTEXT(A2)

รวมสูตรเข้าด้วยกันเป็น

=COUNTIF($A$2:$A$11,"<="&A2)
+COUNT($A$2:$A$11)*ISTEXT(A2)

“ว้าว! แบบนี้ใช่เลยครับพี่” ตั้มโห่ร้องอีกครั้ง

“ว่าแต่.. แล้วถ้าอยากแสดงข้อมูลตั้งแต่ลำดับแรกจนถึงลำดับสุดท้ายล่ะครับ เขียนสูตรยังไงดี?” ตั้มเกิดความคิดสร้างสรรค์

ถ้าอยากแสดงข้อมูลตั้งแต่ลำดับแรกจนถึงลำดับสุดท้าย อาจใช้ร่วมกับฟังก์ชัน INDEX, MATCH และ ROWS หรือเขียนสูตรเป็น

=INDEX($A$2:$A$11,MATCH(ROWS($B$2:B2),$B$2:$B$11,0))

“โห! สูตรยากจัง มีวิธีที่ง่ายกว่านี้ไหมครับ?” ตั้มเห็นสูตรแล้วห่อไหล่
“ตั้มใช้ Excel เวอร์ชันอะไร?”
“Microsoft 365 ครับ”
“งั้นใช้ฟังก์ชัน SORT ได้เลย” ผมยิ้ม

สูตรที่ใช้คือ

=SORT(A2:A11)

“เฮ้ย! แบบนี้ก็ได้หรือพี่” ตั้มตะโกนเสียงดัง

ฟังก์ชัน SORT เป็นหนึ่งในฟังก์ชันตระกูล Dynamic Array Formula ซึ่งใช้ได้เฉพาะ Microsoft 365 เท่านั้น (Excel 2016/ 2019 ไม่มีฟังก์ชันนี้)

นอกจากเป็นฟังก์ชันที่ฉลาดมากแล้ว ยังเขียนสูตรแค่บรรทัดแรกบรรทัดเดียว (เช่น จากภาพเขียนสูตรที่ D2) แล้วข้อมูลที่เหลือจะ SPILL ขึ้นมาเองโดยอัตโนมัติ (ไม่ต้องก็อปปี้สูตร)
เจ๋งสุด ๆ ใช่ไหมล่ะ ^_^

หวังว่าบทความนี้จะจุดประกายไอเดียการเขียนสูตร แม้บางครั้ง Excel อาจไม่มีฟังก์ชันที่ให้ผลลัพธ์แบบที่เราต้องการ แต่ถ้าใช้ตรรกะ ต่อให้ผลลัพธ์พิสดารแค่ไหน ก็ไม่ไกลเกินไปแน่นอน

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

บทความนี้แชร์ได้นะครับ ยิ่งมีคนอ่าน คนเขียนยิ่งมีกำลังใจครับ ^_^

Reference:
https://exceljet.net/formula/basic-text-sort-formula
https://exceljet.net/formula/sort-text-and-numbers-with-formula
https://www.get-digital-help.com/how-to-rank-text-uniquely-in-excel/