Excel Forum

ผมมีข้อมูลที่ต้องการหาผลรวม เพื่อจัดอันดับ แต่ข้อมูลเกิดซ้ำกัน ต้องใช้สูตรไหนได้บ้างครับ
ขอบคุณมาก ๆ ครับ

ลองแบบนี้ครับ

ที่ D2 คีย์
=INDEX($B$2:$B$11,SMALL(IF(IF(FREQUENCY(SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11))=LARGE(IF(FREQUENCY(SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),ROWS(D$2:D2)),ROW($B$2:$B$11)-ROW($B$2)+1),1))
กด Ctrl+Shift ค้าง แล้ว Enter -> คัดลอกลงล่างจนเห็นค่าผิดพลาด

ที่ E2 คีย์
=IF(ISTEXT(D2),SUMIF($B$2:$B$11,D2,$C$2:$C$11),0)
Enter -> คัดลอกลงล่างครับ

อันนี้หายอดขึ้นมาก่อนครับ

E2 =IFERROR(LARGE(IF(FREQUENCY(MATCH(SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11),SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11),0),ROW($B$2:$B$11)-ROW($B$2)+1),SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11)),ROWS($E$2:E2)),"")

D2 =IF(N(E2),INDEX($B$2:$B$11,SMALL(IF(SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)=E2,ROW($B$2:$B$11)-ROW($B$2)+1),COUNTIF(E$2:E2,E2))),"")
ทั้งสองสูตรต้องกดแป้นแบบ Array คือ ctrl+shift+enter

DhitiBank wrote:ลองแบบนี้ครับ

ที่ D2 คีย์
=INDEX($B$2:$B$11,SMALL(IF(IF(FREQUENCY(SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11))=LARGE(IF(FREQUENCY(SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),ROWS(D$2:D2)),ROW($B$2:$B$11)-ROW($B$2)+1),1))
กด Ctrl+Shift ค้าง แล้ว Enter -> คัดลอกลงล่างจนเห็นค่าผิดพลาด

ที่ E2 คีย์
=IF(ISTEXT(D2),SUMIF($B$2:$B$11,D2,$C$2:$C$11),0)
Enter -> คัดลอกลงล่างครับ

สูตรเดิมนี้จะแสดงข้อมูลไม่ถูกต้องหากมีอักษรบางตัวบังเอิญมีผลรวมเท่ากัน ขอปรับสูตรใหม่เป็น
F2
=INDEX($B$2:$B$11,MATCH(0,IF((COUNTIF($B$2:$B$11,"<"&$B$2:$B$11)+1)*COUNTIF(F$1:F1,$B$2:$B$11),1,0),0))
Ctrl+Shift+Enter

G2
=SUMIF($B$2:$B$11,F2,$C$2:$C$11)
Enter

E2
=LARGE($G$2:$G$11,ROWS(E$2:E2))
Enter

D2
=INDEX($F$2:$F$11,SMALL(IF($G$2:$G$11=E2,ROW($G$2:$G$11)-ROW($G$2)+1),COUNTIF(E$2:E2,E2)))
Ctrl+Shift+Enter

แล้วคัดลอกสูตรทั้งหมดลงล่างจนเห็นว่าแสดงค่าผิดพลาดครับ (สูตรในคอลัมน์ F และ G เอามาช่วยหาอักษรที่ไม่ซ้ำและผลรวมของแต่ละตัว)

ส่วนเรื่อง conditional format ลองแบบนี้ครับ
(เอา conditional format เดิมออกก่อนครับ)
1. ลากคลุม B2:B11 -> Conditional format -> New Rule -> Use a formula to determine...
2. ที่ช่อง Format values where this formula is true: คีย์
=AND(B2=$D$2,COUNTIF($B$2:$B$11,$D$2)>1)
-> แล้วตั้งค่าสีพื้นหลังที่ชอบ -> OK
3. วนทำใหม่เรื่อยๆ จนครบตัวอักษร โดยเปลี่ยน D2 เป็นตำแหน่งอื่นครับ

logic wrote:อันนี้หายอดขึ้นมาก่อนครับ

E2 =IFERROR(LARGE(IF(FREQUENCY(MATCH(SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11),SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11),0),ROW($B$2:$B$11)-ROW($B$2)+1),SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11)),ROWS($E$2:E2)),"")

D2 =IF(N(E2),INDEX($B$2:$B$11,SMALL(IF(SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)=E2,ROW($B$2:$B$11)-ROW($B$2)+1),COUNTIF(E$2:E2,E2))),"")
ทั้งสองสูตรต้องกดแป้นแบบ Array คือ ctrl+shift+enter

ปรับ E2 ใหม่เป็นด้านล่างนะครับ (ส่วน D2 ใช้สูตรเดิม)

E2 =IFERROR(LARGE(IF(FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,0),ROW($B$2:$B$11)-ROW($B$2)+1),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),ROWS(P$2:P2)),"")

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

ขอแก้อีกครั้งเถอะครับ ครั้งสุดท้ายแล้ว

D2
=INDEX($B$2:$B$11,SMALL(IF(IF(FREQUENCY(MATCH($B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),$B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),0),MATCH($B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),$B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),0)),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11))=E2,ROW($C$2:$C$11)-ROW($C$2)+1),COUNTIF(E$2:E2,E2)))
Ctrl+Shift+Enter

E2
=LARGE(IF(FREQUENCY(MATCH($B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),$B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),0),MATCH($B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),$B$2:$B$11&SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11),0)),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),ROWS(E$2:E2))
Ctrl+Shift+Enter

แล้วคัดลอกทั้งสองสูตรลงล่างจนเห็นค่าผิดพลาดครับ คราวนี้หากมีบังเอิญผลรวมของอักษรเท่ากันก็ไม่มีปัญหาครับ

ขอขอบคุณ
คุณ DhitiBank
คุณ logic
คุณ menem
มาก ๆ ครับ สูตรทั้งหมดลองแล้วได้หมดเลยครับ ^ ^

logic wrote:อันนี้หายอดขึ้นมาก่อนครับ

E2 =IFERROR(LARGE(IF(FREQUENCY(MATCH(SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11),SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11),0),ROW($B$2:$B$11)-ROW($B$2)+1),SUMIF($B$2:$B$11,$B$3:$B$11,$C$2:$C$11)),ROWS($E$2:E2)),"")

D2 =IF(N(E2),INDEX($B$2:$B$11,SMALL(IF(SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)=E2,ROW($B$2:$B$11)-ROW($B$2)+1),COUNTIF(E$2:E2,E2))),"")
ทั้งสองสูตรต้องกดแป้นแบบ Array คือ ctrl+shift+enter

แต่ในส่วนของ คุณ logic พบว่าพอลองใส่ข้อมูลเพิ่มแล้ว
หากตัวอักษรที่ซ้ำ มีค่าเท่ากันจะแสดงตัวแรกที่พบครับ
แบบที่คุณ DhitiBank พบเลยครับ

ตัวอย่างเพิ่มเติมครับ

1. E2 คีย์
   =IFERROR(LARGE(IF(FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,0),ROW($B$2:$B$11)-ROW($B$2)+1),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)),ROWS(P$2:P2)),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
2. D2 คีย์
   =IF(N(E2),INDEX($B$2:$B$11,SMALL(IF(IF(FREQUENCY(MATCH($B$2:$B$11,$B$2:$B$11,0),ROW($B$2:$B$11)-ROW($B$2)+1),SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11))=$E2,ROW($B$2:$B$11)-ROW($B$2)+1),COUNTIF(E$2:E2,E2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง