Snasui Excel Forum

ยกตัวอย่าง
ชื่อ-นามสกุล ปริมาณเอกสารที่ค้าง จำนวนวันที่ค้าง อันดับตามวันที่ค้าง อันดับตามปริมาณ
นาย1 1 30 2 1
นาง 1 1 40 1 2
นาย 1 10 10 2 1
นาง 1 2 2 1 2
นาง 1 1 5 1 2

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

อยากให้จัดอันดับในcolumn K and L โดย K จัดอันดับตามจำนวนวันที่ค้างนานที่สุด (ไม่ต้อง Sum วันรวมกันนะคะ) และ L จัดอันดับตาม ชื่อ MKT.ที่มีซ้ำเยอะที่สุด(ต้อง Sumชื่อซ้ำเยอะที่สุด) โดยอันดับที่มากที่สุด เรียงเป็น 1,2,3,... ไปเรื่อยๆ
ขอบคุณมากนะคะ

ลองตามไฟล์แนบครับ

1. ที่ K3 คีย์

=RANK(I3,$I$3:$I$13)

Enter > Copy ลงด้านล่าง

2. ที่ L3 คีย์

=MATCH(COUNTIF($J$3:$J$13,J3),LARGE(IF(FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1),COUNTIF($J$3:$J$13,$J$3:$J$13)),ROW(INDIRECT("1:"&SUM(1/COUNTIF($J$3:$J$13,$J$3:$J$13))))),0)

Ctrl+Shift+Enter > Copy ลงด้านล่าง สังเกตการกดแป้น ต้องกด 3 แป้นนี้เสมอเมื่อสร้างหรือแก้ไขปรับปรุงสูตร เนื่องจากเป็นสูตร Array ครับ

ขอความกรุณาอาจารย์ช่วยอธิบายสูตรที่คีย์ใน L3 ทีละคำสั่งด้วยครับ ถือว่าเป็นวิทยาทาน ขอบคุณครับ

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

จากสูตร =MATCH(COUNTIF($J$3:$J$13,J3),LARGE(IF(FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1),COUNTIF($J$3:$J$13,$J$3:$J$13)),ROW(INDIRECT("1:"&SUM(1/COUNTIF($J$3:$J$13,$J$3:$J$13))))),0)

เป็นการหาว่าผลลัพธ์จากสูตร COUNTIF($J$3:$J$13,J3) อยู่ในลำดับที่เท่าไรของผลลัพธ์จากสูตร LARGE(IF(FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1),COUNTIF($J$3:$J$13,$J$3:$J$13)),ROW(INDIRECT("1:"&SUM(1/COUNTIF($J$3:$J$13,$J$3:$J$13)))))

ซึ่งสูตรด้านบนใช้หาค่าที่ใหญ่ที่สุดที่ได้จากผลลัพธ์ของสูตร ROW(INDIRECT("1:"&SUM(1/COUNTIF($J$3:$J$13,$J$3:$J$13))) ในช่วงที่เป็นผลลัพธ์จากสูตร IF(FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1),COUNTIF($J$3:$J$13,$J$3:$J$13))

เนื่องจากเป็นสูตร Array ผลลัพธ์ที่ได้จากสูตร ROW(INDIRECT("1:"&SUM(1/COUNTIF($J$3:$J$13,$J$3:$J$13))) จะแจกแจงออกมาเป็นเช่น {1,2,3,4,5} จึงหมายความว่า ให้หาค่าที่ใหญ่ที่สุดในลำดับที่ 1, 2, 3, 4, 5 จากผลลัพธ์ของ IF(FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1),COUNTIF($J$3:$J$13,$J$3:$J$13))

จากสูตร IF(FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1),COUNTIF($J$3:$J$13,$J$3:$J$13)) หมายความว่า หาก FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1) เป็นจริงคือไม่เท่ากับ 0 แล้ว ให้แสดงผลลัพธ์จากสูตร COUNTIF($J$3:$J$13,$J$3:$J$13) และถ้าไม่เป็นจริงจะแสดงค่า False

จากสูตร FREQUENCY(MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0),ROW($J$3:$J$13)-ROW($J$3)+1) หมายความถึงให้หาค่าที่ได้จากสูตร MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0) มีอยู่ในช่วงของค่าที่ได้จากสูตร ROW($J$3:$J$13)-ROW($J$3)+1) เป็นจำนวนกี่ครั้งบ้าง ซึ่งสูตร ROW($J$3:$J$13)-ROW($J$3)+1) จะให้ผลลัพธ์เป็นค่าลำดับ เช่น 1, 2, 3, 4, 5....

จากสูตร MATCH(COUNTIF($J$3:$J$13,$J$3:$J$13),COUNTIF($J$3:$J$13,$J$3:$J$13),0) หมายถึงให้หาว่าค่าที่ได้จากสูตร COUNTIF($J$3:$J$13,$J$3:$J$13) อยู่ในลำดับที่ใดบ้างในช่วง COUNTIF($J$3:$J$13,$J$3:$J$13) สังเกตว่าเป็นการนำค่าเดียวกันมา Match กัน

จากสูตร COUNTIF($J$3:$J$13,$J$3:$J$13) เป็นการหาว่ามีค่า $J$3:$J$13 อยู่ในช่วง $J$3:$J$13 จำนวนกี่ครั้ง สังเกตว่าเป็นการนับจากช่วงเดียวกัน

ขอบคุณมากมายครับอาจารย์