เทคนิคการใช้ Helper Column ทำให้คำนวณสรุป PivotTable ง่ายขึ้น
งานที่ผมเจอบ่อยๆ และเคยเสียเวลากับมันมาก ก็คือ กรณีที่ต้องทำงาน Routine เช่น Monthly Report ที่ต้องรายงานข้อมูลเป็นรายเดือนให้เห็นความคืบหน้าในทุกเดือนๆ ซึ่งผมเชื่อมั่นว่าคุณก็คงเคยทำมันบ้างล่ะ
แต่ปัญหาที่มักเกิดขึ้นคือ ข้อมูลที่เป็น Source Data นั้นเป็นข้อมูลรายวัน แล้วไม่ได้จบเดือนพอดี แถมผมก็ขี้เกียจมานั่งตัดให้ Data จบสิ้นเดือนพอดีด้วย เช่น จริงๆ มีรายการขายถึงวันที่ 3 มีนาคม แต่ผมอยากสรุปข้อมูลแค่สิ้นเดือน กุมภาพันธ์เท่านั้น
ถ้าทำแบบถึกๆ ไป ผมก็อาจจะนั่ง Group ข้อมูลให้เป็นรายเดือน ซึ่งมันก็จะมี Data เดือนมีนาคมออกมาด้วยติ่งหนึ่งเสมอ ซึ่งผมก็ต้องมา Filter มันออกภายหลัง
พอเดือนถัดไป ผมก็ต้องมานั่งแก้ Filter ให้เห็นเดือนมีนาคม แต่ต้องเอาเดือนเมษายนออก ทำแบบนี้ไปเรื่อยๆ และถ้ามี PivotTable หลายตาราง เราก็ต้องมานั่งแก้แบบนี้ทุกตาราง ตายพอดี!!
วิธีที่ดีกว่าคือการสร้าง Helper Column มาช่วยใน Source Data เลย เช่น กำหนดช่อง A1 ใน Sheet ที่ไม่ได้ใช้งานซักอันนึงเป็นวันที่สุดท้ายที่ต้องการแสดงใน PivotTable แล้วอาจตั้งชื่อว่า Latest_Date
ที่นี้กลับมาที่ Source Data ของเรา แล้วมาเริ่มสร้าง Helper Column กันครับ แต่จะสร้าง Helper Column ตรงไหนดี? ผมแนะนำแบบนี้ครับ
- กรณีที่เป็นข้อมูลปกติ แล้วสร้าง PivotTable ไปแล้ว: ผมแนะนำให้ทำการ “แทรก Column” ในพื้นที่ของ Data Source เดิม เช่น เดิมอยู่ในคอลัมน์ A:H ก็ไปแทรกให้อยู่ในระหว่างคอลัมน์ A กับ H ตรงไหนก็ได้ ทั้งนี้เพราะหากเราไปแทรกข้างหน้าหรือข้างหลัง Source Data เดิม PivotTable จะมองไม่เห็นมันครับทำให้ต้องมานั่งแก้ Source Data ใน PivotTable อีกทีซึ่งเสียเวลามาก วิธีที่ผมบอกจะช่วยแก้เรื่องนี้ได้ แต่มีข้อเสียคือต้องระวังว่า Helper Column จะถูกข้อมูลอื่นมาทับภายหลัง
- กรณีที่เป็นข้อมูลปกติ แล้วยังไม่ทันสร้าง PivotTable: ผมแนะนำให้สร้าง Helper Column ไว้ที่คอลัมน์ซ้ายสุดครับ เนื่องจากเวลามี Data ใหม่เข้ามา จะได้ Paste ลงไปตรงๆ ได้เลยแบบไม่ต้องกังวลว่าจะทับ Helper Column ของเรา
- กรณีที่ Source Data เป็น Table (Ctrl+T) : ผมแนะนำให้สร้าง Helper Column ไว้ที่คอลัมน์ซ้ายสุดครับ เนื่องจากเวลามี Data ใหม่เข้ามา จะได้ Paste ลงไปตรงๆ ได้เลยแบบไม่ต้องกังวลว่าจะทับ Helper Column ของเรา ซึ่งเราสามารถทำแบบนี้ได้เพราะว่าการใช้ Table เป็น Source Data มันจะมีความสามารถในการ Dynamic Range คือ PivotTable จะมองเห็น Helper Column ที่เราแทรกลงไปโดยอัตโนมัติ
ที่นี้ใน Helper Column ที่ผมตั้งชื่อว่า show date ก็อาจเขียนสูตรเช็คว่า วันที่แต่ละ record ใน Source Data นั้นน้อยกว่าหรือเท่ากับวันที่ Latest_Date ที่คุณกำหนดไว้หรือไม่?
ซึ่งคุณสามารถเอา showdate นี้ไปเป็น Report Filter ใน PivotTable แล้วเลือกเป็น TRUE ไว้ได้เลย พอเดือนถัดไปก็แค่ไปแก้ค่าในช่อง Latest_Date แล้วกด Refresh All ตรง PivotTable เท่านั้นเอง แค่นี้เป็นอันจบครับ!!
เพิ่มการคำนวณแบบพิเศษใน Pivot Table ด้วย Calculated Field & Calculated Item
ใน PivotTable นั้น เราสามารถสร้างข้อมูลการคำนวณขึ้นมาใหม่ โดยไม่จำเป็นต้องเข้าไปปรับแก้ใน Data Source เลย ซึ่งข้อมูลการคำนวณนี้จะแบ่งได้เป็น 2 ประเภท ซึ่งมีความแตกต่างกันดังนี้
- Calculated Field : จะเป็นการสร้าง Field ใหม่ขึ้นมาเลย เช่น เดิมมี Field ยอดขายอยู่ เราสามารถสร้าง Field ใหม่ชื่อ Commission เพิ่มเข้ามาได้ โดยอาจเกิดจาก ยอดขาย *10% ถ้าขายได้ยอดรวมมากกว่า 3000 บาท
- Calculated Item : เป็นการสร้าง Sub-Item ใหม่ใน Field ที่มีอยู่แล้ว เช่น ใน Field ประเภทสินค้า ในข้อมูลต้นทางอาจมีสินค้าเพียง 4 ประเภท คือ A,B,C,D แล้วเราจะสร้างประเภทสินค้าเข้ามาใหม่ คือ สินค้า E โดย คำนวณจาก A+B เป็นต้น
ทำไมต้องสร้าง Field คำนวณเพิ่มเติม?
สถานการณ์ต่อไปนี้ ค่อนข้างเหมาะกับการสร้าง Field คำนวณเพิ่มใน PivotTable ครับ
- ไม่สามารถเข้าไปแก้ไข Source Data ได้ หรือ แก้ไขได้ลำบาก
- การคำนวณนั้นมีเงื่อนไขที่ต้องดูผลรวมของข้อมูลทั้งหมดก่อน จึงจะคำนวณได้ ไม่สามารถคำนวณได้ในแต่ละบรรทัดใน Source Data เช่น กรณี Commission ที่คิดจากยอดขายรวม *10% โดยมีการกำหนดยอดรวมขั้นต่ำที่จะได้ Commission ด้วย
- การคำนวณนั้นเป็นการหาสัดส่วนที่เป็น % เช่น %Portion ต่างๆ โดยที่ Weight ของแต่ละข้อมูลไม่เท่ากัน เช่น หากเราใส่สูตรหา % Defect ใน Data Source เลย เวลา Pivot แล้วหาค่า Average จะได้ผลลัพธ์แปลกๆ เพราะไม่ได้เอา Weight มาเป็นปัจจัย เป็นยังไงมาดูกัน
Calculated Field
วิธีการสร้าง Calculated Field
- ให้กดที่ (Pivot Table)[[Options]]–> Calculations –> Fields, Items, and Sets –> Calculated Field…
- ตรง Name ให้ตั้งชื่อ Field ตามต้องการ
- ตรง Formula ให้ใส่สูตรการคำนวณว่าต้องการให้สัมพันธ์กับ Field อื่นๆ อย่างไร
สมมติว่าผมอยากคำนวณค่า Commission 10% ของยอดขาย โดยมีเงื่อนไขว่าต้องทำยอดขายรวมทุกสินค้าได้มากกว่า 3000 บาท จึงจะได้รับเงินนี้ไป
ข้อจำกัดของ Calculated Field
- เป็นการ SUM ข้อมูลแต่ละ Field ก่อน จากนั้นค่อยมาคำนวณตามสูตรที่เราเขียน
- สังเกตว่าช่อง Grand Total มันก็คำนวณ Commission ผิด เพราะมันดันคิดเอา Sum of จำนวนเงินที่จ่ายในแถว Grand Total คือ 19852 ซึ่งมากกว่า 3000 บาท มาคูณ10% เลย อันนี้ต้องระวังให้ดีครับ เพราะเป็นข้อจำกัดของ Calculated Field ล่ะ
Calculated Item
วิธีการสร้าง Calculated Item
- คลิ๊กที่ Field ที่ต้องการจะเพิ่ม Item ก่อน
- ให้กดที่ (Pivot Table)[[Options]]–> Calculations –> Fields, Items, and Sets –> Calculated Item…
- ตรง Name ให้ตั้งชื่อ Field ตามต้องการ
- ตรง Formula ให้ใส่สูตรการคำนวณตามต้องการว่าต้องการให้สัมพันธ์กับ Item อื่นๆ อย่างไร
สังเกตว่า เมื่อเราคลิ๊กช่องที่เป็น Calculated Item มันจะขึ้นสูตรใน Formula Bar ด้วย
ข้อจำกัดของ Calculated Item
- เป็นการ SUM ข้อมูลแต่ละ Item ก่อน แล้ว จากนั้นจึงคำนวณตามสูตรที่เราเขียน
- ไม่สามารถใช้ได้กับ PivotTable ที่มีการ Group ข้อมูลอยู่แล้ว (ไม่ว่าจะอยู่ที่ Field อื่นก็ตาม) แต่ว่าสามารถ Group ข้อมูลหลังจากใส่ Calculated Item ได้
- ไม่สามารถย้าย Field ไปที่ Report Filters ได้
- ไม่สามารถลาก Field นั้นลงไปที่ section Values หลายๆ รอบได้
การเขียนสูตรเพื่ออ้างอิงเข้าไปในตาราง Pivot และคำสั่ง GETPIVOTDATA
ปกติเวลาเราเขียนสูตร แล้วเข้าไปคลิ๊กในบริเวณที่เป็นตาราง PivotTable เราอาจจะเห็นสูตรพิลึกๆ ขึ้นมาเช่น ผมเขียนสูตรอ้างอิงโดยคลิ๊กเข้าไปในช่อง D11 แทนที่จะขึ้นมาว่า =D11 ตามปกติ มันดันขึ้นสูตรพิลึกๆ มาว่า =GETPIVOTDATA(“จำนวนเงินที่จ่าย”,$A$3,”ผู้ซื้อ”,”นาย c”,”ผู้ขาย”,”sales ข”,”วิธีการจ่าย”,”เครดิตการ์ด”) แทน ซึ่งทำให้เกิดปัญหาว่าเรา Copy สูตรไปที่อื่น มันก็ไม่เลื่อนช่องให้แบบเวลาใช้ Cell Reference ปกติ แถมสูตรก็ดูยากมากๆ
วิธีแก้เพื่อให้เวลาใช้สูตรแล้วคลิ๊กเข้าไปใน PivotTable แล้วเป็น Cell Reference ปกติ ให้ทำดังนี้
ให้กด (Pivot Table)[[Options]]–> PivotTable –> Options (ปุ่มลูกศร)–> ติ๊กคำว่า Generate Getpivotdata ออก เพียงเท่านี้เราก็จะสามารถคลิ๊กเข้าไปในแต่ละช่องของ PivotTable ได้แบบ Cell ทั่วไปแล้ว
ถ้ามันยุ่งยากแบบนี้แล้ว GETPIVOTDATA มันมีข้อดียังไง? แล้ว Excel มันใส่มาทำไมล่ะเนี่ย!?
ผมจะบอกว่าจริงๆแล้วมันก็มีข้อดีของมันอยู่ครับ นั่นก็คือ เวลาเรา Link ข้อมูลมาแล้วเปิดโหมด GetpivotData ไว้ ไม่ว่าตาราง PivotTable จะถูกพลิกบิดมุมมองไปมาแค่ไหน ค่าที่เรา Link ไว้ก็จะยังเป็นค่าเดิมเสมอ รวมถึงเวลามีข้อมูลเพิ่มด้วย มันก็ยังสามารถอ้างอิงไปยังข้อมูล Summary ต่างๆ ได้อยู่เช่นเดิม จึงมีประโยชน์ในแง่การเขียนสูตรอ้างอิงค่า Summary จาก PivotTableพอสมควร ต่างจากการใช้ Cell Reference ปกติ ที่อาจกลายเป็นค่าใหม่ที่เราไม่ต้องการไปโดยไม่รู้ตัวเลยก็ได้ เพราะช่องมันเลื่อนไปมาหมดแล้ว
ดังนั้นก่อนจะปิดโหมด GETPIVOTDATA จะต้องรู้ตัวให้ดีว่ากำลังทำอะไรอยู่นะครับ
