กรณีการคำนวณเฉพาะกลุ่มข้อมูลที่กำหนดเช่น 10 ค่าสุดท้าย หรือ 5 ค่าสุดท้ายหรือกลุ่มข้อมูลช่วงใด ๆ ของข้อมูลทั้งหมดคงไม่เป็นเรื่องยากหากว่าไม่มีเซลล์ว่างคั่นระหว่างข้อมูล แต่หากข้อมูลไม่เป็นระเบียบโดยมีการเว้นว่างไว้ด้วย การคำนวณโดยระบุจำนวนค่าที่กำหนดจะยุ่งยากมากขึ้นหลายเท่า
ซึ่งกรณีที่จะยกตัวอย่างต่อไปนี้จะเป็นการคำนวณหาค่าค่าเฉลี่ย ค่ามากที่สุด ค่าน้อยที่สุด เฉพาะ 5 ค่าสุดท้ายของช่วงข้อมูลซึ่งไม่ติดกัน ทั้งดึงข้อมูลที่เกี่ยวข้องมาแสดง โดยข้อมูลอยู่ที่คอลัมน์ A:F และจะนำผลลัพธ์ซึ่งเป็นค่า Average, Min, Max และวันที่ที่เกี่ยวข้องของค่าต่าง ๆ มาแสดงที่คอลัมน์ I:M
ภาพตัวอย่างการคำนวณโดยระบุกลุ่มข้อมูลทั้งหมด
โดยมีลำดับการคำนวณดังข้างล่างนี้
- เซลล์ H1 คีย์ ตัวเลขซึ่งเป็น n ลำดับสุดท้าย เช่นตามโจทย์คือ 5 อาจจะคีย์เป็น 7, 10 อันนี้แล้วแต่ความต้องการ
- เซลล์ 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 - เซลล์ 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 - เซลล์ 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 - เซลล์ 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 - เซลล์ 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 - คลุม 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 วิธีในการแกะสูตรครับ
ไม่มีความคิดเห็น:
แสดงความคิดเห็น