GroupBy+CountIF ไม่ใช่แค่ใช้แทน PivotTable แต่ทำได้เหนือกว่า
PivotTable ว่ากันตรงๆเป็นของใช้กับรายงานสำหรับเด็กๆ ไม่เหมาะสำหรับให้ผู้ใหญ่หรือผู้บริหารนักหรอก
ถ้าในฐานข้อมูลมีหัวข้อเยอะมาก เวลาที่ต้องการแสดงแค่บางหัวข้อต้องเสียเวลามา Filter หรือใช้ Slicer ตัดรายการที่ไม่ต้องการทิ้งไป เช่น ถ้าต้องการแสดงแค่บางจังหวัด พอสร้างตาราง Pivot ออกมาจะแสดงทุกจังหวัดที่บันทึกไว้ทั้งหมดขึ้นมาก่อน จากนั้นต้องเสียเวลามาใช้ Filter/Slicer เพื่อเลือกแสดงแค่บางจังหวัด
หลักการใช้ Excel สร้างรายงานสำหรับผู้บริหาร ต้องยึดหลัก Management by Exception นั่นคือ ตั้งแต่แรกต้องทำให้แสดงเฉพาะข้อมูลที่สำคัญหรืออยากจะแสดงเท่านั้น ไม่ใช่ว่าต้องมาเสียเวลาตัดทิ้งทีหลัง
ตัวอย่างตามภาพนี้ ในกรอบสี่เหลี่ยมสีเขียว ใช้คำสั่ง Data > Validation สำหรับให้ผู้บริหารคลิกเลือกเฉพาะ ItemChoice ที่อยากจะแสดง จากนั้นตารางฐานข้อมูลด้านซ้ายกับตารางหายอดรวมแยกประเภทด้านขวาสุดก็จะเปลี่ยนสีพื้นแสดงรายการที่ตรงกับ ItemChoice ที่เลือกให้เห็นชัดเจนว่าอยู่ตรงไหน พร้อมหายอดรวมที่เกี่ยวข้องให้เท่านั้นด้วย รายการอื่นจะกลายเป็น 0 ไปเลย
สูตรที่ทำหน้าที่แบบนี้ได้มาจากการซ้อนสูตร CountIF เข้าไป
=GROUPBY( CHOOSECOLS( B3:E27,K1 ), F3:G27*(COUNTIF(ITEMChoice,ITEM)>=1), SUM )
CHOOSECOLS( B3:E27,K1 ) ทำหน้าที่เลือกแสดงตามหัวตารางที่เลือกได้ในช่องสีเหลือง
F3:G27 เป็นพื้นที่ตารางตัวเลข Budget กับ Actual
(COUNTIF(ITEMChoice,ITEM)>=1) เป็นสูตรสำคัญที่ช่วยเลือกเฉพาะรายการที่ตรงกับที่เลือกใน ItemChoice ซึ่งสูตรนี้แหละที่ทำให้เหนือกว่าการใช้ PivotTable
ถ้าลากทับส่วนของสูตร (COUNTIF(ITEMChoice,ITEM)>=1) แล้วกดปุ่ม F9
จะพบว่าสูตรนี้ทำหน้าที่ตรวจสอบหาตำแหน่งรายการว่าตรงไหนที่ตรงกับ ItemChoice บ้าง
{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
ตำแหน่งที่เป็น TRUE เป็น Item ที่ตรงกับที่เลือก พอนำไปคูณกับ F3:G27 จะเก็บตัวเลขเฉพาะตำแหน่งที่เท่ากับ TRUE ส่วนที่เป็น FALSE จะไม่นำไปใช้หายอดรวม
สาเหตุที่ใช้สมการ >=1 นั่นเพื่อให้ไม่ว่าจะคลิกช่องสีเขียวแล้วเผลอเลือก Item ซ้ำก็จะไม่ทำให้คำนวณผิดพลาด ลองดูแล้วจะเห็นเองครับ
Download ได้จาก
https://drive.google.com/file/d/1OCXLG1GUGZnwSP3IXykBgj1U3EwN9Ru3/view?usp=sharing
ตัวอย่างนี้ต่อเนื่องมาจากตัวอย่างก่อนหน้าที่ไม่ซับซ้อนเท่าไร เชิญติดตามดูได้จาก Excel Expert Blog ที่
ความคิดเห็น
แสดงความคิดเห็น