นับจำนวนค่าที่ซ้ำกันแล้วนำมาแยกว่ามีใครซ้ำกันบ้างครับ

[niwat2811]

นับจำนวนค่าที่ซ้ำกันแล้วนำมาแยกว่ามีใครซ้ำกันบ้างครับ ไม่ทราบว่าควรต้องใส่สูตรอย่างไร ตรงไหนบ้างครับ ขอบคุณมากครับ

[bank9597]

ผมดูคำตอบที่ต้องการแล้ว ค่อนข้างงงครับ

เราสามารถหาค่าที่ซ้ำกันได้ โดยที่ L1 คีย์ =SUMPRODUCT(1/COUNTIF(A1:A28,A1:A28))

ที่ D1 คีย์ =IF(ROWS(I$1:I1)>$L$1,"",INDEX($A$1:$A$28,SMALL(IF(MATCH($A$1:$A$28,$A$1:$A$28,0)=ROW($A$1:$A$28)-ROW($A$1)+1,ROW($A$1:$A$28)-ROW($A$1)+1),ROWS(I$1:I1))))

กด Ctrl+Shift+Enter แล้วลากลงมา

ก็จะได้ค่าที่ไม่ซ้ำออกมา ไม่รู้ว่าจะใช้ได้หรือไม่ ลองดูก่อนครับ

[niwat2811]

ยังไม่ตรงกับความต้องการครับ อาจจะเป็นเพราะว่าผมยังอธิบายความต้องการไม่ถูกต้องครับ ต้องขอโทษด้วยครับ คือว่า ที่คอลัมภน์ A หมายความถึงเป็นเลขสลากที่ลูกค้าซื้อซึ่งอาจจะมีหมายเลขที่ซ้ำกันอยู่บ้าง ที่คอลัมภน์ B คือรายชื่อลูกค้าที่ซื้อ ส่วนคอลัมภน์ F คือรางวัลที่ออก ที่คอลัมภน์ G นำเลขที่ออกมานับว่าถูกสลากกี่คน ที่คอลัมภน์ I คือเลขรางวัลที่ออกซึ่งอาจจะมีผู้ที่ถูกเลขเดียวกันอยู่หลายคน ที่คอลัมภน์ J คือรายชื่อผู้ที่ถูกรางวัลครับ ต้องขอโทษด้วยที่อธิบายไม่ชัดเจนครับ ยังไงก็ขอขอบคุณคุณ bank9597 ด้วยครับที่กรุณาช่วยชี้แนะ ขอบคุณมากครับ

[bank9597]

เข้าใจแล้วครับ
ตัวเลขสลากที่ออก เราคีย์เองด้วยมือใช่ไหมครับ

[bank9597]

ลองตามนี้ครับ
ที่ G15 คีย์ =SUM(G2:G15)
ที่ G16 คีย์ =MAX(G1:G14)
ที่ I1 คีย์ =IF(ROWS($I$1:I1)>$G$15,"",INDEX($F$1:$F$14,SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&$G$16)))<=$G$1:$G$14,ROW($G$1:$G$14)-ROW($G$1)+1),ROWS($I$1:I1))))
กด Ctrl+Shift+Enter คัดลอกลงมา

เครดิต http://snasui.blogspot.com/search/label/Index

ที่ H1 คีย์ =IF(I1=I2,COUNTIF($I$1:I1,I1),COUNTIF($I$1:I1,I1)+COUNTIF(I2:I2,I1)) คัดลอกลงมา
ที่ C1 คีย์ =IF(A1=A2,COUNTIF($A$1:A1,A1),COUNTIF($A$1:A1,A1)+COUNTIF(A2:A2,A1)) คัดลอกลงมา

ที่ J1 คีย์ =INDEX($B$1:$B$28,MATCH(1,IF($I1=$A$1:$A$28,IF($H1=$C$1:$C$28,1)),0))
กด Ctrl+Shift+Enter คัดลอกลงมา

อยากทราบส่วนไหนอย่างไร ค่อยถามมาต่อน่ะครับ

[niwat2811]

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

[bank9597]

ที่ G15 คีย์ =SUM(G2:G15)
ที่ G16 คีย์ =MAX(G1:G14)
ที่ I1 คีย์ =IF(ROWS($I$1:I1)>$G$15,"",INDEX($F$1:$F$14,SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&$G$16)))<=$G$1:$G$14,ROW($G$1:$G$14)-ROW($G$1)+1),ROWS($I$1:I1))))
กด Ctrl+Shift+Enter คัดลอกลงมา

สูตรด้านบน ผมได้แนบลิงค์สำหรับการศึกษาเพิ่มเติมไว้แล้ว อนึ่งสูตรดังกล่าวเป็นสูตรที่ยากมาก ผมต้องบอกว่าผมอธิบายในส่วนนี้ไม่ได้ อาจจะผิดพลาดจากความเป็นจริงได้ครับ ต้องให้อาจารย์เป็นผู้อธิบายจะดีที่สุด ซึ่งวิธีการดังกล่าวนั้นเป็นการแยกข้อมูลออกมาตามจำนวนซ้ำ เช่น 1000000 นับได้ 2 ครั้ง เราแยก 1000000 ออกมา 2 ตัว อยู่ในแกนแนวตั้งโดยใช้ TRANSPOSE ทั้งนี้ข้อมูลเดิมที่คุณได้ทำการนับรวมมาให้นั้นจึงเป็นประโยชน์ต่อการใช้สูตรนี้ครับ

ที่ H1 คีย์ =IF(I1=I2,COUNTIF($I$1:I1,I1),COUNTIF($I$1:I1,I1)+COUNTIF(I2:I2,I1)) คัดลอกลงมา

สูตรที่ยกมานั้น ผมใช้เรียงอันดับข้อมูลที่ซ้ำกัน เพื่อกำหนดเป็นรหัสเฉพาะของข้อมูลนั้นเพื่อใช้ประโยชน์ในการดึงชื่อผู้ถูกรางวัลออกมา ซึ่งเราจะดึงข้อมูลไม่ได้ หรือได้ยากมาก เนื่องจาก ข้อมูลอ้างอิงเหมือนกัน เช่น 1000000 มีคนถูกรางวัล 2 คน คือ นายก. กับ นายข. ถ้าใช้รหัส 1000000 เป็นตัวอ้างอิง มันก็จะดึงค่ามาได้แค่คนเดียวเท่านั้น เพราะเหตุนี้ เราจึงต้องกำกับรหัสเสริมเข้าไปเพื่อให้เกิดความต่างกันของรหัส 1000000 คือ รหัส 1000000 ลำดับที่ 1 รหัส 1000000 ลำดับที่ 2 เป็นต้น เวลาเราใช้สูตรอ้างอิงเพื่อหาคำตอบเราสามารถอ้างอิง 2 ที่ แม้ว่ารหัสจะเหมือนกัน แต่ลำดับไม่ซ้ำกันอย่างแน่นอนครับ

ที่ C1 คีย์ =IF(A1=A2,COUNTIF($A$1:A1,A1),COUNTIF($A$1:A1,A1)+COUNTIF(A2:A2,A1)) คัดลอกลงมา

สูตรนี้ก็เช่นเดียวกันครับ เมื่อกำหนดรหัสปลายทางแล้ว รหัสต้นทางเราก็ต้องกำหนดให้เหมือนกันด้วย เพื่อจะได้ใช้สูตรอ้างอิงกันและกันได้ โดยใช้สูตร Index+Match

ที่ J1 คีย์ =INDEX($B$1:$B$28,MATCH(1,IF($I1=$A$1:$A$28,IF($H1=$C$1:$C$28,1)),0))
กด Ctrl+Shift+Enter คัดลอกลงมา

ในการ Match กันระหว่างรหัส และ ลำดับที่ ของข้อมูลต้นทางและปลายทางโดยใช้ Index+Match พิสูจน์สูตรได้ดังนี้
ไวยากรณ์ของสูตร Index คือ Index(ช่วงข้อมูล,ตำแหน่งข้อมูล) เช่น =INDEX($B$1:$B$28,2) หมายความว่า แสดงค่าของข้อมูลในช่วงข้อมูล $B$1:$B$28 บรรทัดที่ 2

ส่วนการใช้ match เป็นการหาตำแหน่งขอข้อมูลว่าอยู่ในบรรทัดที่เท่าไหร่ (ในส่วนของรูปแบบการใช้สูตรให้ลองศึกษาเองได้ครับ)

อธิบายภาพรวมน่ะครับ ตามไฟล์แนบน่ะครับ
สูตร =INDEX($B$1:$B$28,MATCH(1,IF($I1=$A$1:$A$28,IF($H1=$C$1:$C$28,1)),0))
แยกออกมาแล้ว จะได้ =INDEX($B$1:$B$28) กับ =MATCH(1,IF($I1=$A$1:$A$28,IF($H1=$C$1:$C$28,1)),0)

=MATCH(1,IF($I1=$A$1:$A$28,IF($H1=$C$1:$C$28,1)),0) ได้ค่าออกมาคือ 1
เมื่อรวมกับสูตร Index ก็จะได้ =INDEX($B$1:$B$28,1) หมายความว่าแสดงค่าในบรรดทัด 1 ช่วงข้อมูล B1:B28 คำตอบคือ นายก. ครับ

ผมอธิบายไม่ค่อยชัดเจน ขออภัยด้วยครับ เนื่องจากสูตรนั้นค่อนข้างยาก ตัวผมเองก็ใช้ได้ด้วยความเข้าใจส่วนตัวและจินตนาการส่วนตัว จึงไม่สามารถอธิบายให้ใครเข้าใจได้เหมือนกันทุกคนครับ ต้องศึกษาเองประกอบไปด้วยครับ

[niwat2811]

ครับ ต้องขอขอบคุณ คุณ bank9597 ที่ให้ความกระจ่างเพิ่มขึ้นมาก เนื่องจากผมมีพื้นฐานทางด้านสูตร Excel น้อยมาก คงต้องใช้เวลาศึกษาอีกนานครับ ขอบคุณมากครับ

[bank9597]

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

[snasui]

ต่อยอดจากสูตรของคุณ Bank9597 ในเซลล์ F15, F16 และ H1:H14

ทำการเรียงข้อมูลจากคอลัมน์ B มาที่คอลัมน์ I โดยที่ I1 คีย์สูตร

=INDEX($B$1:$B$28,SMALL(IF($A$1:$A$28=H1,ROW($B$1:$B$28)-ROW($B$1)+1),COUNTIF(H$1:H1,H1)))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ดูภาพประกอบครับ

[niwat2811]

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

[bank9597]

ขอบคุณอาจารย์มากครับ มันเป็นสูตรที่ลดความยุ่งยากออกไปให้เหลือในขั้นตอนเดียว เยี่ยมไปเลยครับ