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

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

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

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

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

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

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

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

	A	B	C
1	คะแนน	Rank	ที่ต้องการ
2	300	1	1
3	200	2	2
4	200	2	2
5	100	4	3

จากตารางจะเห็นว่าด้วยฟังก์ชั่น Rank จะมีการให้ลำดับข้ามไป คือไม่มีลำดับที่ 3 หากต้องการผลลัพธ์ตามคอลัมน์ B เราสามารถคีย์สูตรที่ B2 เป็น

=Rank($A2,$A$2:$A$5)

Enter > Copy ลงด้านล่าง

แต่หากต้องการผลลัพธ์ที่มีการให้ลำดับต่อเนื่องกันไปเช่นเดียวกับผลลัพธ์ในคอลัมน์ C ที่ C2 คีย์

=MATCH($A2,LARGE(IF(FREQUENCY(MATCH($A$2:$A$5,$A$2:$A$5,0),ROW($A$2:$A$5)-ROW($A$2)+1),$A$2:$A$5),ROW(INDIRECT("1:"&SUM(1/COUNTIF($A$2:$A$5,$A$2:$A$5))))),0)

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

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

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

การคำนวณโดยระบุกลุ่มข้อมูลจากข้อมูลทั้งหมด

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

ซึ่งกรณีที่จะยกตัวอย่างต่อไปนี้จะเป็นการคำนวณหาค่าค่าเฉลี่ย ค่ามากที่สุด ค่าน้อยที่สุด เฉพาะ 5 ค่าสุดท้ายของช่วงข้อมูลซึ่งไม่ติดกัน ทั้งดึงข้อมูลที่เกี่ยวข้องมาแสดง โดยข้อมูลอยู่ที่คอลัมน์ A:F และจะนำผลลัพธ์ซึ่งเป็นค่า Average, Min, Max และวันที่ที่เกี่ยวข้องของค่าต่าง ๆ มาแสดงที่คอลัมน์ I:M

ภาพตัวอย่างการคำนวณโดยระบุกลุ่มข้อมูลทั้งหมด

โดยมีลำดับการคำนวณดังข้างล่างนี้

1. เซลล์ H1 คีย์ ตัวเลขซึ่งเป็น n ลำดับสุดท้าย เช่นตามโจทย์คือ 5 อาจจะคีย์เป็น 7, 10 อันนี้แล้วแต่ความต้องการ
2. เซลล์ I2 คีย์สูตร เพื่อหาค่า Average สำหรับ Type A
   =AVERAGE(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))
   Ctrl+Shift+Enter
3. เซลล์ I3 คีย์สูตร เพื่อหาค่า Min สำหรับ Type A
   =MIN(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))
   Ctrl+Shift+Enter
4. เซลล์ I4 คีย์สูตร เพื่อหาวันที่ที่แสดงค่า Min สำหรับ Type A
   =INDEX(INDEX($A2:$A21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX($A2:$A21,MATCH(9.99999999999999E+307,B2:B21)),MATCH(I3,INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)),0))
   Ctrl+Shift+Enter
5. เซลล์ I5 คีย์สูตร เพื่อหาค่า Max สำหรับ Type A
   =MAX(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))
   Ctrl+Shift+Enter
6. เซลล์ I6 คีย์สูตร เพื่อหาวันที่ที่แสดงค่า Max สำหรับ Type A
   =INDEX(INDEX($A2:$A21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX($A2:$A21,MATCH(9.99999999999999E+307,B2:B21)),MATCH(I5,INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)),0))
   Ctrl+Shift+Enter
7. คลุม I2:I6 แล้ว Copy ไปทางด้านหลัง

======================================================

ตัวอย่างการแกะสูตร

จากสูตร

=AVERAGE(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))

จากช่วงของสูตร

INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))

เป็นการหาค่าแรกของข้อมูลที่ต้องการ

INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21))

เป็นการหาเซลสุดท้ายของช่วงข้อมูล(ที่เป็นตัวเลข)

มาดูไส้ของสูตรแรกกันใน ช่วงที่เป็น

LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1)

ความหมายคือ ถ้า B2:B21 เป็นตัวเลข ให้คืนค่าแถวลำดับ และนำลำดับที่ H1 (ตามแต่กำหนด) มาแสดง

เมื่อคลุมช่วง ISNUMBER(B2:B21) แล้วกดแป้น F9 จะได้

LARGE(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE},ROW(B2:B21)-ROW(B2)+1),$H$1)

เมื่อ ลากคลุมช่วง ROW(B2:B21)-ROW(B2)+1 กับลากคลุม H1 แล้วกด F9 จะได้

LARGE(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}),5)

ในขั้นตอนนี้จะได้สูตร IF ในมุมมองที่ชัดเจนขึ้น ตัวหน้าที่เป็น False จะไม่นำค่าตัวเลขในปีกกาด้านหลังมาแสดง ส่วนตัวที่เป็น True จะนำตัวเลขในปีกกาด้านหลังมาแสดง

เมื่อลากคลุม IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

แล้ว กดแป้น F9 จะได้

LARGE({FALSE;2;3;4;FALSE;6;7;FALSE;FALSE;FALSE;FALSE;FALSE;13;14;FALSE;FALSE;17;18;FALSE;20},5)

หากลากคลุมสูตรนี้ต่อก็จะได้ 13 (ซึ่งจะเห็นได้ว่าตัวที่ใหญ่ที่สุดเป็นอันดับ 5 ในสูตรนี้คือ 13)

เมื่อมองภาพรวมสูตรทั้งหมดอีกครั้งจะได้เป็น

=AVERAGE(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,13)):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))

Note: สูตร Index จะคืนค่าในเซลที่ต้องการค้นหา แต่เมื่อเชื่อมด้วย : แล้วสามารถแปลงจากค่าในเซลที่ค้นหาเป็นตำแหน่งเซลที่ บรรจุค่าที่ต้องการค้นหาทันที จากการใช้ Index:Index ซึ่งจะเกิดเป็นช่วงข้อมูล แต่หากลากคลุมสูตรด้านบนแล้วกดแป้น F9 ต่อไปจะได้ค่าที่เป็น ตัวเลข:ตัวเลข แทน จึงจำเป็นต้องพิสูจน์สูตรด้วยคำสั่ง Evaluate Formula จึงจะได้คำตอบที่ต้องการ

สำหรับ Excel 2003 จะไม่สามารถ ใช้คำสั่ง Evaluate Formula ตั้งแต่ต้นในการพิสูจน์สูตรที่ให้นี้ได้ (Excel จะ ล่ม จะเห็นได้จากภาพที่ Title bar มีคำว่า Recovered) ผมถือว่ามันเป็น Bug จึงจำเป็นต้องใช้ 2 วิธีในการแกะสูตรครับ