Snasui Excel Forum

เรียนท่านผู้รู้ช่วยด้วยครับ
จากสูตรต้องการเรียงลำดับผลรวมจากค่ามากไปหาค่าน้อยที่ได้มาโดยมีเงื่อนไขดังนี้ 1.ในคอลัมน์A1:A15 เป็นการคีย์ตัวเลข 1 2 และ 3 เท่านั้น 2.ในคอลัมน์B1:C15 เป็นการคีย์ตัวเลข 1-1000 ครับ 3.ในคอลัมน์ D1:D3 E1:E3 และ F1:F3 จะเป็นผลรวมที่ได้มาจากคอลัมน์A1:C15ครับ เงื่อนไขที่ผมต้องการมีดังนี้ครับ 1.ต้องการให้เลขสองในคอลัมน์ดีแถวที่สองขึ้นไปอยู่ในคอลัมน์ดีแถวที่หนึ่งโดยมีเลขสามแถวที่สองในคอลัมน์อีและเอฟตามขึ้นไปด้วย ส่วนเลขหนึ่งคอลัมน์ D E F แถวที่หนึ่งก็ลงมาแทนคอลัมน์ D E F แถวที่สาม และคอลัมน์ D E F แถวที่สามก็ขึ้นไปแทนแถวที่สองดังตัวอย่างที่1 2.ในกรณีค่าในคอลัมน์อีและเอฟไม่เท่ากันให้อ้างอิงค่าในคอลัมน์อีจากมากไปหาน้อยดังตัวอย่างที่2 3. ดังตัวอย่างที่3จะสังเกตุเห็นได้ว่าในคอลัมน์อีมีค่ามากกว่าสามก็คือสี่ขึ้นไปแทนคอลัมน์ดีแถวที่หนึ่ง
ตามไฟล์ที่แนบมาครับ

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

ส่วนการอธิบาย ควรแบ่งย่อหน้าด้วยครับ คอลัมน์ดี ให้เขียนแทนด้วย คอลัมน์ D ผมปรับข้อความมาให้ใหม่ตามด้านล่าง

tonnam2627 wrote:จากสูตรต้องการเรียงลำดับผลรวมจากค่ามากไปหาค่าน้อยที่ได้มาโดยมีเงื่อนไขดังนี้

1. ในคอลัมน์A1:A15 เป็นการคีย์ตัวเลข 1 2 และ 3 เท่านั้น

2. ในคอลัมน์B1:C15 เป็นการคีย์ตัวเลข 1-1000 ครับ

3. ในคอลัมน์ D1:D3 E1:E3 และ F1:F3 จะเป็นผลรวมที่ได้มาจากคอลัมน์ A1:C15 ครับ เงื่อนไขที่ผมต้องการมีดังนี้ครับ

3.1) ต้องการให้เลข 2 ในคอลัมน์ด D แถวที่ 2 ขึ้นไปอยู่ในคอลัมน์ D แถวที่ 1โดยมีเลข 3 แถวที่ 2 ในคอลัมน์ E และ F ตามขึ้นไปด้วย ส่วนเลข 1 คอลัมน์ D E F แถวที่ 1 ก็ลงมาแทนคอลัมน์ D E F แถวที่ 3 และคอลัมน์ D E F แถวที่สามก็ขึ้นไปแทนแถวที่ 2 ดังตัวอย่างที่ 1

3.2) ในกรณีค่าในคอลัมน์ E และ F ไม่เท่ากันให้อ้างอิงค่าในคอลัมน์ E จากมากไปหาน้อยดังตัวอย่างที่ 2

3.3) ดังตัวอย่างที่ 3 จะสังเกตุเห็นได้ว่าในคอลัมน์อีมีค่ามากกว่า 3 ก็คือ 4 ขึ้นไป

เวลาอธิบายโจทย์ ให้มีการเว้นวรรค เว้นบรรทัดลงมาบ้างครับ ผู้ตอบจะได้อ่านสะดวกหน่อย

จากการอธิบายมาทั้ง คิดว่าเข้าใจ เลยลองทำมาให้ดูดังนี้

ขั้นแรก สร้างตารางรวมค่าของรหัสต่างๆออกมาก่อน ไว้อ้างอิงในการเรียงลำดับ ดังนี้
ที่เซลล์ A18:A20 คีย์ 1 -3
ที่เซลล์ B18 คีย์ =SUMIF($A$1:$A$15,A18,$B$1:$B$15) คัดลอกลงมา

ขั้นสอง นำข้อมูลที่ได้ไปวางเรียงลำดับ ดังนี้
ที่เซลล์ E1 คีย์ =LARGE($B$18:$B$20,ROWS($E$1:E1)) คัดลอกลงมา
ที่เซลล์ D1 คีย์ =INDEX($A$18:$A$20,MATCH(E1,$B$18:$B$20,0)) คัดลอกลงมา
ที่เซลล์ F1 คีย์ =SUMIF($A$1:$A$15,D1,C$1:C$15)

ขออนุญาตเรียนถามอาจารย์ครับ เรื่องเดียวกันกับกระทู้นี้ แต่จะลึกลงไปอีก

ตามไฟล์แนบน่ะครับ
ที่เซลล์ E1 ผมคีย์สูตร =LARGE(SUMIF($A$1:$A$15,$A$1:$A$3,$B$1:$B$15),ROWS($E$1:E1)) กดแป้นพิมพ์แบบ Array คัดลอกลงมา 3 บรรทัด
ส่วนที่เซลล์ D1 ผมอยากรู้ว่า ผลรวมที่ได้ในคอลัมน์ E นั้น มันจะตรงกับรหัสใดครับ ในเซลล์ A1:A3 ครับ

ไม่ทราบว่าต้องใช้สูตรอย่างไรดีครับ

bank9597 wrote:ที่เซลล์ D1 ผมอยากรู้ว่า ผลรวมที่ได้ในคอลัมน์ E นั้น มันจะตรงกับรหัสใดครับ ในเซลล์ A1:A3 ครับ

ลองตามด้านล่างครับ

=INDEX($A$1:$A$15,SMALL(IF(SUMIF($A$1:$A$15,$A$1:$A$15,$B$1:$B$15)=$E1,ROW($A$1:$A$15)-ROW($A$1)+1),COUNTIF(E$1:E1,E1)))

Ctrl+Shift+Enter

ขอบคุณมากครับ ได้ตรงที่ต้องการเลยครับ

เพิ่มเติมอีกนิดน่ะครับ สูตรที่ผมใช้
=LARGE(SUMIF($A$1:$A$15,$A$1:$A$3,$B$1:$B$15),ROWS($E$1:E1)) กดแป้นแบบ Array
รู้สึกว่าการใช้ Sumif แบบนี้มันไม่เหมาะสม เราจะเปลี่ยนไปใช้สูตรไหนได้บ้างครับ รบกวนแนะนำหน่อยได้ไหมครับ

ขออนุญาตตอบคำถามคุณ tonnam2627 เลยน่ะครับ

ตามที่ผมได้สอบถามจากอาจารย์ในส่วนที่ยังติดปัญหาอยู๋ ได้ประติดประต่อสูตรให้มีความสมบูรณ์มากขึ้นดังนี้ครับ

ที่เซลล์ E1 คีย์ =LARGE(SUMIF($A$1:$A$15,$A$1:$A$3,$B$1:$B$15),ROWS($E$1:E1))
กด Ctrl+Shift ค้าง ตามด้วยปุ่ม Enter คัดลอกลงมา (โดยสูตรจะมีเครื่องหมาย {} (ปีกกา) คร่อมสูตรไว้ แสดงว่าถูกต้อง เราไม่สามารใส่เครื่องหมานี้เองได้)
สูตรดังกล่าวใช้เรียงค่ารวมจากมาไปหาน้อย

ที่เซลล์ D1 คีย์ =INDEX($A$1:$A$15,SMALL(IF(SUMIF($A$1:$A$15,$A$1:$A$15,$B$1:$B$15)=$E1,ROW($A$1:$A$15)-ROW($A$1)+1),COUNTIF(E$1:E1,E1)))
กด Ctrl+Shift+Enter คัดลอกลงมา

ที่เซลล์ F1 คีย์ =SUMIF($A$1:$A$15,D1,$C$1:$C$15) คัดลอกลงมา

bank9597 wrote: ขอบคุณมากครับ ได้ตรงที่ต้องการเลยครับ

เพิ่มเติมอีกนิดน่ะครับ สูตรที่ผมใช้
=LARGE(SUMIF($A$1:$A$15,$A$1:$A$3,$B$1:$B$15),ROWS($E$1:E1)) กดแป้นแบบ Array
รู้สึกว่าการใช้ Sumif แบบนี้มันไม่เหมาะสม เราจะเปลี่ยนไปใช้สูตรไหนได้บ้างครับ รบกวนแนะนำหน่อยได้ไหมครับ

กรณีใช้เงื่อนไขในสูตรแค่ 3 เซลล์และ Copy มาใช้แค่ 3 เซลล์ โดยมี Rows เป็นเงื่อนไขในการเรียงก็ไม่มีปัญหาใดครับ แต่ถ้าใช้เงื่อนไขทั้ง 15 เซลล์ และ Copy ลงด้านล่างทั้ง 15 เซลล์ โดยมี Rows เป็นเงื่อนไขในการเรียงก็จะได้ค่าที่ซ้ำ ๆ กันครับ

แบบนี้เอง ขอบคุณมากครับ

bank9597 wrote: ขออนุญาตตอบคำถามคุณ tonnam2627 เลยน่ะครับ

ตามที่ผมได้สอบถามจากอาจารย์ในส่วนที่ยังติดปัญหาอยู๋ ได้ประติดประต่อสูตรให้มีความสมบูรณ์มากขึ้นดังนี้ครับ

ที่เซลล์ E1 คีย์ =LARGE(SUMIF($A$1:$A$15,$A$1:$A$3,$B$1:$B$15),ROWS($E$1:E1))
กด Ctrl+Shift ค้าง ตามด้วยปุ่ม Enter คัดลอกลงมา (โดยสูตรจะมีเครื่องหมาย {} (ปีกกา) คร่อมสูตรไว้ แสดงว่าถูกต้อง เราไม่สามารใส่เครื่องหมานี้เองได้)
สูตรดังกล่าวใช้เรียงค่ารวมจากมาไปหาน้อย

ที่เซลล์ D1 คีย์ =INDEX($A$1:$A$15,SMALL(IF(SUMIF($A$1:$A$15,$A$1:$A$15,$B$1:$B$15)=$E1,ROW($A$1:$A$15)-ROW($A$1)+1),COUNTIF(E$1:E1,E1)))
กด Ctrl+Shift+Enter คัดลอกลงมา

ที่เซลล์ F1 คีย์ =SUMIF($A$1:$A$15,D1,$C$1:$C$15) คัดลอกลงมา

ขอบคุณครับ อาจารย์snasui และพี่ bank9597
ที่ช่วยเหลือ สูตรตรงตามที่ต้องการเลยครับ เก่งมากครับ
ส่วนที่commentมาผมจะปรับปรุงในครั้งต่อไปครับ