Snasui Excel Forum

รบกวนขอสูตรครับ จากไฟล์แนบที่ cell L2...L11 ต้องการจัดเรียงลำดับความถี่ที่พบข้อความเดิมๆบ่อยที่สุด โดยให้จัดเรียงลำดับจากจำนวนที่พบบ่อยมากสุดไปน้อยสุด ข้อมูลพิจารณาอยู่ที่ L16...Lใดๆ (ลักษณะข้อมูลเพิ่มขึ้นเรื่อยๆ)

H16 =COUNTIF($L$16:$L$509,$L16)
Enter > Copy ลงล่าง

J2 =MAX($H$16:$H$509)
Enter

J3 =MAX(IF($H$16:$H$509<$J2,$H$16:$H$509))
Ctrl + Shift + Enter > Copy ลงล่างถึง J11

L2 =INDEX($L$16:$L$509,MATCH($J2,$H$16:$H$509,0))
Enter > Copy ถึง L11

ขอบคุณสำหรับสูตรนะครับ แต่เป็นไปได้ไหมครับที่ว่าสูตรจะจบที่ภายใน cell L2:L11 ได้เลย และถ้าบางรายการพบในจำนวนที่เท่ากันก็ให้แสดงเป็นลำดับถัดไปตามตัวอย่างครับ

ดูตามไฟล์แนบนะครับ

ลำดับที่ควรจัดเรียงคือตามรูปครับ ที่จำนวนความถี่ที่พบเท่ากันตัวถัดไปให้นำมาเรียงต่อกันเลยครับ เช่น อาหารพบ = 3, เพื่อนใช้หนี้พบ = 3, โค้กพบ = 3 เป็นลำดับที่ 5,6,7 ตามลำดับเป็นต้นครับ

รอท่านอื่นมาตอบนะครับ

Jancha wrote: Tue Jul 10, 2018 4:47 pm รบกวนขอสูตรครับ จากไฟล์แนบที่ cell L2...L11 ต้องการจัดเรียงลำดับความถี่ที่พบข้อความเดิมๆบ่อยที่สุด โดยให้จัดเรียงลำดับจากจำนวนที่พบบ่อยมากสุดไปน้อยสุด ข้อมูลพิจารณาอยู่ที่ L16...Lใดๆ (ลักษณะข้อมูลเพิ่มขึ้นเรื่อยๆ)

ตัวอย่างสูตรตามด้านล่างครับ

1. เซลล์ B15:D15 คีย์ List, Count, Rank เพื่อเป็นหัวคอลัมน์
2. ที่ B16 คีย์
   =IFERROR(INDEX($L$16:$L$72,SMALL(IF(FREQUENCY(MATCH($L$16:$L$72,$L$16:$L$72,0),ROW($L$16:$L$72)-ROW($L$16)+1),ROW($L$16:$L$72)-ROW($L$16)+1),ROWS(B$16:B16))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. ที่ C16 คีย์
   =IF(B16="","",COUNTIF($L$16:$L$72,B16))
   Enter > Copy ลงด้านล่าง
4. ที่ D16 คีย์
   =IF(B16="","",SUMPRODUCT(--(C16<$C$16:$C$43))+COUNTIF(C$16:C16,C16))
   Enter > Copy ลงด้านล่าง
5. เซลล์ L2 คีย์
   =INDEX($B$16:$B$43,MATCH(K2,$D$16:$D$43,0))
   Enter > Copy ลงด้านล่าง

!

Note: Ctrl+Shift+Enter เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

ขอบคุณทั้งสองท่านมากครับ สูตรอาจารย์ตรงกับที่ต้องการเลยครับ ตรงส่วน column l ถ้ารายการมีการเพิ่มเติมขึ้นตลอดไม่ต้องการมาปรับ range บ่อยๆที่ B16 ต้องปรับสูตรอย่างไรครับ

Jancha wrote: Tue Jul 10, 2018 8:36 pm ขอบคุณทั้งสองท่านมากครับ สูตรอาจารย์ตรงกับที่ต้องการเลยครับ ตรงส่วน column l ถ้ารายการมีการเพิ่มเติมขึ้นตลอดไม่ต้องการมาปรับ range บ่อยๆที่ B16 ต้องปรับสูตรอย่างไรครับ

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

การปรับสูตรใน B16 ให้มั่นคงต่อเซลล์ว่างก็จะเป็นตามด้านล่างครับ

=IFERROR(INDEX($L$16:$L$72,SMALL(IF(FREQUENCY(If($L$16:$L$72<>"",MATCH($L$16:$L$72,$L$16:$L$72,0)),ROW($L$16:$L$72)-ROW($L$16)+1),ROW($L$16:$L$72)-ROW($L$16)+1),ROWS(B$16:B16))),"")

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