การจัดเรียงบ้านเลขที่

การจัดเรียงบ้านเลขที่นั้นไม่สามารถทำได้อย่างง่าย ๆ เนื่องจากบ้านเลขที่มักจะมีเครื่องหมายทับ (/) ปนอยู่ด้วย ทำให้เครื่องมือของ Excel ที่ใช้ในการจัดเรียงไม่สามารถให้ผลลัพธ์ที่ต้องการได้

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

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

 

จากภาพด้านบนจะเป็นการนำข้อมูลบ้านเลขที่ในคอลัมน์ A มาเรียงใหม่ โดยให้เรียงจากน้อยไปหามาก

สูตรในเซลล์ C2 คือ

=INDEX($A$2:$A$14,MATCH(SMALL(--SUBSTITUTE(IF(ISNUMBER(FIND("/",$A$2:$A$14)),
$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT("0",10-LEN(MID($A$2:$A$14,
FIND("/",$A$2:$A$14&"/")+1,255)))),ROWS(C$2:C2)),--SUBSTITUTE(IF(
ISNUMBER(FIND("/",$A$2:$A$14)),$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT(
"0",10-LEN(MID($A$2:$A$14,FIND("/",$A$2:$A$14&"/")+1,255)))),0))

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

การเปลี่ยนค่าผิดพลาด #N/A เป็นค่าใด ๆ ตามต้องการ

ฟังก์ชั่นที่ใช้บ่อยที่สุด Top Hit ติด Chart ฟังก์ชั่นหนึ่งคือ Vlookup ซึ่งเป็นการ Lookup ข้อมูลที่มีค่าตรงกันกับค่าที่ต้องการ แล้วนำข้อมูลที่เกี่ยวข้องมาแสดง และเมื่อ Lookup กันแล้วเมื่อไม่เจอค่าที่ต้องการจะเกิดค่าผิดพลาดขึ้นมาเป็น #N/A

ปัญหาที่ตามมาคือต้องการไม่ให้แสดงค่าเป็น #N/A จะทำอย่างไร เพราะเมื่อติดค่าผิดพลาดเป็น #N/A แล้วจะทำให้นำไปคำนวณต่อไม่ได้

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

=If(Isna(Vlookup(A1,$C$1:$E$100,3,0)),0,Vlookup(A1,$C$1:$E$100,3,0))

เมื่อ

1. A1 คือค่าที่ต้องการ Lookup
2. $C$1:$E$100 คือตารางฐานข้อมูล
3. 3 คือคอลัมน์ที่ต้องการนำมาแสดงผล
4. 0 คือ รูปแบบการ Lookup ซึ่งเป็นการ Lookup แบบตรงตัว ถ้าเป็น 1 เป็นการ Lookup แบบหาค่าใกล้เคียง

จากสูตรด้านบนหมายความว่า ถ้า Vlookup แล้วเป็นค่าผิดพลาด #N/A ให้แสดงค่า 0 (สามารถเป็นเป็นค่าใด ๆ ตามต้องการ เช่น " ","Not Found" ฯลฯ) ถ้าไม่เป็นค่าผิดพลาดก็ให้นำค่าผลลัพธ์จากการ Vlookup มาแสดง

หรืออีกตัวอย่าง

=If(Isnumber(Match(A1,$C$1:$C$100,0)),Vlookup(A1,$C$1:$E$100,3,0),"")

หมายความว่า ถ้าพบค่า A1 ในช่วงข้อมูล $C$1:$C$100 ก็ให้ทำการ Vlookup ค่ามาให้ ถ้าไม่พบก็ให้แสดงค่าว่าง

Note:

1. ฟังก์ชั่น Match จะแสดงลำดับที่พบข้อมูล ถ้าไม่พบจะแสดงค่า #N/A
2. ฟังก์ชั่น Isnumber จะเป็นการตรวจสอบว่าค่าที่ได้จากการ Match ว่าเป็นตัวเลขหรือไม่

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

กรณีการคำนวณเฉพาะกลุ่มข้อมูลที่กำหนดเช่น 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 วิธีในการแกะสูตรครับ

การสรุปข้อมูลจากฐานข้อมูลที่แบ่งเป็นช่วง ๆ

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

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

จากภาพด้านล่างจะเป็นการสรุปข้อมูลตามที่ต้องการโดยการเลือก Region และ Product โปรแกรมจะแสดงยอดรวม ทั้งแจกแจงรายการทั้งหมดที่ตรงตาม Region และ Product

ภาพตัวอย่างการสรุปข้อมูลจากฐานข้อมูลที่แบ่งเป็นช่วง ๆ



โดยมีวิธีการและขั้นตอนดังนี้

A. List รายการของ Product ทั้งหมด

1. นับ Product ที่มีทั้งหมดโดยไม่นับค่าที่ซ้ำ H1 คีย์
   =SUM(IF(FREQUENCY(IF(ISNUMBER($B$1:$B$39),MATCH("~"&$A$1:$A$39,$A$1:$A$39&"",0)),ROW($B$1:$B$39)-ROW($B$1)),1))
   Ctrl+Shift+Enter
2. List รายการ Product ทั้งหมดโดยไม่เอาค่าซ้ำที่ H2 คีย์
   =IF(ROWS($H$2:H2)<=$H$1,INDEX($A$1:$A$39,SMALL(IF(FREQUENCY(IF(ISNUMBER($B$1:$B$39),MATCH("~"&$A$1:$A$39,$A$1:$A$39&"",0)),ROW($A$1:$A$39)-ROW($A$1)+1),ROW($A$1:$A$39)-ROW($A$1)+1),ROWS($H$2:H2))),"")
   Ctrl+Shift+Ener > Copy ลงด้านล่าง

B. List รายการ Region ทั้งหมด

1. นับจำนวน Region โดยไม่เอาค่าซ้ำที่ G1 คีย์
   =SUMPRODUCT(--(B1:B39=""),--(A1:A39<>""))
   Enter
2. List Region ทั้งหมด G2 คีย์
   =IF(ROWS($G$2:G2)<=$G$1,INDEX($A$1:$A$39,SMALL(IF($B$1:$B$39="",IF($A$1:$A$39<>"",ROW($B$1:$B$39)-ROW($B$1)+1)),ROWS($G$2:G2))),"")
   Ctrl+Shift+Ener > Copy ลงด้านล่าง

C. หาตำแหน่งเซลล์ว่างสุดท้ายในบรรทัดด้านล่างถัดจาก Region ที่เลือกใน E2 ที่ E1 คีย์

=SMALL(IF(A1:A39="",ROW(A1:A39)-ROW(A1)+1),MATCH(E2,G2:G7,0))
Ctrl+Shift+Ener

D. List รายการที่ตรงตามเงื่อนไขทั้ง Region และ Prod ที่เลือกใน E2 และ E3 ตามลำดับ

1. นับว่าตรงตามเงื่อนไขทั้งหมดมีจำนวนเท่าไรที่ D6 คีย์
   =COUNTIF(OFFSET(A2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)),E3)
   Enter
2. List Product ทั้งหมดที่ตรงตามเงื่อนไข ที่ D8 คีย์
   =IF(ROWS($D$8:D8)<=$D$6,INDEX(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)),SMALL(IF(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0))=$E$3,ROW(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)))-MATCH($E$2,$A$1:$A$39,0)-1),ROWS($D$8:D8))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. List Value ทั้งหมดที่ตรงตามเงื่อนไข ที่ E8 คีย์
   =IF(ROWS($D$8:D8)<=$D$6,INDEX(OFFSET($B$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)),SMALL(IF(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0))=$E$3,ROW(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)))-MATCH($E$2,$A$1:$A$39,0)-1),ROWS($D$8:D8))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

E. สูตรหายอดรวมรายการที่ตรงตามเงื่อนไขโดยไม่ต้องแจกแจงรายการออกมาก่อน ที่ E4 คีย์

=SUMIF(OFFSET(A2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)),E3,OFFSET(B2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)))
Enter