ซ่อมคอมพิวเตอร์นอกสถานที่ บางกะปิ รามคำแหง

วันพุธที่ 18 กรกฎาคม พ.ศ. 2555

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


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


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


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


Last_n_ItemCal


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


  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 วิธีในการแกะสูตรครับ

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

แสดงความคิดเห็น