หน้าเว็บ

วันจันทร์ที่ 18 เมษายน พ.ศ. 2554

การจัดเรียงข้อมูลจากน้อยไปหามากหรือตรงกันข้ามด้วยสูตร

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

ตามด้านล่างจะเป็นภาพการเรียงข้อมูลจากน้อยไปหามากด้วยสูตรเปรียบเทียบกับการเรียงข้อมูลด้วยเมนู Data > Sort

ภาพการเรียงข้อมูลจากน้อยไปหามากด้วยสูตร

SortByFormulas

โดยมีวิธีการดังนี้

  1. ข้อมูลอยู่ที่เซลล์ A1:A12
  2. ที่ B1 คีย์สูตรเพื่อนับว่ามีเซลล์ที่มีข้อมูลจำนวนเท่าไร

    =Counta(A:A)

    Enter
  3. ที่เซลล์ C1 คีย์สูตรเพื่อเรียงข้อมูลจากน้อยไปหามาก

    =IF(ROWS(C$1:C1)>$B$1,"",INDEX($A$1:$A$12,MATCH(SMALL(IF(ISERR(CODE($A$1:$A$12)),"",CODE($A$1:$A$12)+COUNTIF($A$1:$A$12,"<"&$A$1:$A$12)),ROWS(C$2:C2)),CODE($A$1:$A$12)+COUNTIF($A$1:$A$12,"<"&$A$1:$A$12),0)))

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

Note:

กรณีต้องการเรียงข้อมูลจากมากไปหาน้อยให้เปลี่ยน Small เป็น Large

6 ความคิดเห็น:

rat lim กล่าวว่า...

ทดสอบป้อนเป็นตัวเลข ตั้งแต่ 1-12 ผลลัพธ์ที่ได้เป็น
1
2
3
4
5
10
6
6
12
7
8
9
ไม่แน่ใจว่าทำผิดตรงไหนเพราะ Copy สูตรใส่ใน EXCEL ไม่ได้พิมพ์เอง
ขอบคุณครับ

snasui กล่าวว่า...

Copy ไปวางเฉย ๆ ไม่ได้ครับ สูตรนั้นเป็นสูตร Array เมื่อวางแล้วให้กดแป้น F2 จากนั้นกดแป้น Ctrl+Shift+Enter เพือทำเป็นสูตร Array หากยังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง ปีกกาที่ครอบสูตรนั้นจะคีย์เข้าไปเองไม่ได้ ต้องมาจากการกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เท่านั้น

rat lim กล่าวว่า...

ขอบคุณครับ ทำตามทุกอย่างมีเครื่องหมายปีกกาครบครับ โดยการกด Ctrl+Shift+Enter ครับ ถ้าใส่ข้อมูลตามตัวอย่าง ได้ผลตามตัวอย่างครับ

A B C
กกก 10 22
<
b >
su b
sa sa
z su
< t
> u
22 z
t กกก

แต่เมื่อทดลองป้อนตัวเลขอย่างเดียว ก็ได้ผลเหมือนเดิมครับ

A B C
1 11 1
2 2
3 3
4 4
5 5
6 10
7 6
8 6
9 7
10 8
11 9

หรือป้อนตัวเลขผสมอักษร ผลที่ได้

A B C
1 test1 11 1 test1
2 2
3 3
4 4
5 test5 4
6 10
7 test7 11
8 6
9 6
10 8
11 9
ไม่ทราบว่าผิดตรงไหน มั่นใจว่าทำตามตัวอย่างเป็นสูตร Array เมื่อ Copy มาวาง กด Ctrl+Shift+Enter ที่สูตรจะขึ้นปีกกามาให้อัตโนมัติ ครับ

ขอบคุณอีกครั้งครับ

snasui กล่าวว่า...

แนบไฟล์ปัญหาไปที่ http://www.snasui.com จะได้ช่วยดูให้ได้ครับ

Unknown กล่าวว่า...

มีข้อสงสัยอีกอย่างนึงครับ สมมุตติ มีตัวเลขดังนี้
A5425 (เลขหลังสุด คือ 5)
A2548 (เลขหลังสุด คือ 8)
A2411 (เลขหลังสุด คือ 1)
A6544 (เลขหลังสุด คือ 4)
A7894 (เลขหลังสุด คือ 4)
B5500 (เลขหลังสุด คือ 0)
D8489 (เลขหลังสุด คือ 9)
D9998 (เลขหลังสุด คือ 8)
อยากทราบว่า หากต้องการเรียงข้อมูล โดยยึดเฉพาะตัวเลขหลังสุดของแต่ละชุดข้อมูล จะใช้สูตรอย่างไนครับ

snasui กล่าวว่า...

กรณีสอบถามปัญหาเชิญที่ http://www.snasui.com ครับ