Snasui Excel Forum

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

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

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

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

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 จะซ้ำกัน

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

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

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

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

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

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

Norkaz

สำหรับที่ถามมานั้นควรดูเป็นชุดครับจะได้เข้าใจคือดูที่ 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} และเรานำค่านี้ไปใช้ในสูตรที่ครอบอยู่ด้านนอก ๆ ในลำดับต่อไป

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

คำถามที่ ว่า ทำไมต้องบวก 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 มาช่วยกันตอบครับ

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

กรณีไม่เขียนสูตร หรือนานๆทำข้อมูลแบบนี้ที จะใช้วิธี 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

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