ต้องการเรียงลำดับข้อมูล

[nopanan]

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

[snasui]

ในการจับภาพ ควรจับให้มีหัวคอลัมน์และหัวบรรทัดมาด้วยเสมอจะได้ตอบโดยระบุตำแหน่งได้ และควรแนบไฟล์ตัวอย่างมาด้วยครับ ผู้ตอบจะได้ไม่ต้องทำไฟล์เลียนแบบขึ้นมาอีกรอบ

[nopanan]

ขออภัยครับขอแนบไฟล์ใหม่ครับ

[snasui]

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

1. ที่เซลล์ B11 คีย์สูตรเพื่อเรียงค่า Sum Item จากน้อยไปหามาก
   =LARGE($B$7:$H$7,ROWS(B$11:B11))
   Enter > Copy ลงด้านล่าง
2. ที่เซลล์ A11 คีย์สูตรเพื่อแสดงรายการเซลล์ที่สัมพันธ์กับยอด Sum Item
   =INDEX($B$1:$H$1,SMALL(IF($B$7:$H$7=$B11,COLUMN($B$1:$H$1)-COLUMN($B$1)+1),COUNTIF($B$11:$B11,$B11)))
   กดแป้นให้รับสูตรด้วยการกด Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter เนื่องจากเป็นสูตร Array และที่ต้องใช้สูตร Array เนื่องจากยอด Sum Item มีโอกาสซ้ำกันได้แต่ยอดเซลล์ควรแจกแจงออกมาทุกค่าแม้ว่า Sum Item จะซ้ำกัน

[nopanan]

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

[nopanan]

ขออนุญาตถามต่อนิดครับ สูตรตรงที่ column ($B$1)+1) ความหมายของสูตรทำไมต้อง +1 ครับ

[norkaz]

ผมเข้าใจว่า คุณ คนควนวางสูตรแบบนั้น เพื่อให้มัน Dynamic และการวางสูตรด้วยวิธีนี้ ทำให้ การ Index สามารถไป Index จาก Range แรก คือ ตั้งแต่ คอลัมน์ B ตามโจทย์กำหนดได้เลย

ขอชมว่าคุณ คนควน ใช้วิธีนี้โดยนำไป +1 ด้วย และใส่ String ล็อกคอลัมน์ ฺB ไว้ ทำให้สูตรสวยงามมาก ซึ่งทั่วๆไป จะเลือกตัวเลขมาลบออกแล้ว บวก 1 เช่น -2+1 แม้จะถูกต้องใช้ได้ (ผมก็บวกแบบนี้บ่อยๆ...ไม่ค่อยดีเท่าไหร่ ) แต่มันไม่ยืดหยุ่นแบบที่คุณคนควนวางไว้

ในกรณีที่ไม่ทำแบบนั้น คือไม่ล็อกคอลัมน์ B แบบที่คุณคนควนแสดง จะต้องไป เริ่ม INDEX ที่คอลัมน์ Aแทน ซึ่งหากไม่คุ้นเคยอาจจะพลาดได้ การบังคับแบบคุณคนควน ทำให้ตัดปัญหา ความสับสนว่าเมื่อใดจะต้อง INDEX ที่คอลัมน์แรก หรือคอลัมน์ตามโจทย์กำหนด

หวังว่าผมน่าจะเข้าใจถูก หากมีอะไรที่ผมเข้าใจผิดพลาด ขออภัยด้วยครับ ผมความรู้น้อย อาศัยเชิงชกอย่างเดียว

Norkaz

[snasui]

สำหรับที่ถามมานั้นควรดูเป็นชุดครับจะได้เข้าใจคือดูที่ COLUMN($B$1:$H$1)-COLUMN($B$1)+1 ซึ่งมีความมุ่งหมายเพื่อที่จะให้แสดงค่าลำดับเริ่มจากลำดับที่ 1

ซึ่งส่วนแรกคือ COLUMN($B$1:$H$1) จะให้ค่าลำดับเริ่มตั้งแต่คอลัมน์ B:H

การทดสอบให้คลุม COLUMN($B$1:$H$1) แล้วกดแป้น F9 จะได้ค่าเป็น {2,3,4,5,6,7,8} แต่เป้าหมายคือต้องการที่จะให้เริ่มจาก 1 ก็เลยจะต้องหักค่าคอลัมน์แรกแล้ว +1 เข้าไป คือหักด้วย COLUMN($B$1)+1

การหักด้วย COLUMN($B$1) ให้ทดลองคลุม COLUMN($B$1:$H$1)-COLUMN($B$1) แล้วกดแป้น F9 จะได้ผลเป็น {0,1,2,3,4,5,6} ซึ่งเริ่มจาก 0 จึงจำเป็นต้องนำ 1 เข้าไปบวกอีกครั้งเพื่อให้เริ่มจาก 1 ในที่สุด ซึ่งจะได้ผลลัพธ์เป็น {1,2,3,4,5,6,7} และเรานำค่านี้ไปใช้ในสูตรที่ครอบอยู่ด้านนอก ๆ ในลำดับต่อไป

[norkaz]

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

คำถามที่ ว่า ทำไมต้องบวก 1 ที่ column ($B$1)+1)
ควรถามว่า ทำไมต้อง -column($B$1)+1 (มีเครื่องหมายลบด้วย)
ความหมายคือ -2+1 นั่นเองครับ เพราะ column($B$1) มีค่า เ่ท่ากับ 2

ตรงนั้นจะไม่มีในสูตรก็ได้ แต่ที่ต้องบังคับแบบนี้ตลอดเพื่อให้ ค่าของคอลัมน์ ทุกค่าในคอลัมน์ที่ค้นพบ เช่น 2,3,4,5,6,...เอาไป -2+1 ทุกครั้ง

เมื่อมันเจอ Small ไปบังคับ แล้วให้มันแสดงค่าของลำดับ ตามช่วงของโจทย์กำหนด เช่น 1,2,3,4,5,6,...
ไม่ใช่แสดง 2,3,4,5,6...

การแสดงค่า ตั้งแต่เลข 1 จะช่วยให้ INDEX ที่เอาไปครอบนั้น สามารถ INDEX ช่วงตามโจทย์กำหนดได้เลย ในที่นี้คือ INDEX ตั้งแต่คอลัมน์ B

ตรงนั้นจะไม่มีในสูตรก็ได้ คือไม่เขียนสูตร -COLUMN($B$1)+1 ค่าของลำดับจะเริ่มต้นตามคอลัมน์ B เป็น 2,3,4,5,6,... ซึ่งการ INDEX ทั้งชุดนั้น จะต้อง INDEX จากคอลัมน์ A สถานเดียวครับ

การบังคับแบบที่คุณคนควนแสดง จะสามารถ INDEX จากคอลัมน์ B ตามโจทย์ ซึ่งน่าจะง่ายกว่าและไม่สับสนว่า โจทย์กำหนดเริ่มต้นที่ คอลัมน์ B แล้ว ไฉนต้องไปเริ่ม INDEX ที่คอลัมน์ A นั่นเอง

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

Norkaz

[norkaz]

ผมโพสต์ช้าไป คุณคนควนอธิบายได้ชัดเจนกว่าผมซะอีก

นับถือ
Norkaz

[snasui]

ไม่เป็นไรครับ ยินดีด้วยกับเพื่อนสมาชิกที่คุณ norkaz มาช่วยกันตอบครับ

[nopanan]

ขอบคุณทั้งสองท่านมากครับ มีปัญหาจะมาถามใหม่ครับ

[norkaz]

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

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

1. ลากคลุม B1:H1 -> กด Ctrl ค้างไว้
2. ลากคลุม B7:H7 -> ปล่อยมือที่กด Ctrl
3. คลิกขวา -> Copy
4. ไปปลายทางในที่นี้คือ A11
5. คลิกขวา -> Paste Special -> ติ๊กที่ Values -> ติ๊กที่ Transpose
6. OK
7. Sort Data โดยเลือก Sort อันดับแรก จาก Sum Item เรียง มาก->น้อย

์ด้วยความปรารถนาดี
Norkaz

[nopanan]

ขอบคุณสำหรับ Tip & Trick ครับ