เคยสงสัยไหมครับว่า ทำไมบางครั้ง Pivot Table สรุปตัวเลขเป็น Sum Of บางครั้งเป็น Count Of?
อยากได้ Sum Of แต่ดันได้ Count Of แทนซะงั้น!
ข้อมูลก็เป็นตัวเลขแท้ๆ…
อาการที่ว่าหน้าตาแบบนี้ครับ
สิ่งเล็กๆเหล่านี้อาจดูเหมือนขี้ประติ๋ว แต่สร้างความรำคาญใจให้เราไม่น้อย
ใช่เลย เบื่อปัญหานี้มาก มีทางเซ็ต Sum Of ให้เป็นค่าดีฟอลต์เลยได้ไหม?
ในทางตรงแล้ว “ไม่มี” ครับ
ไม่มีปุ่มใดๆในพิเวิทเทเบิลที่สามารถเซ็ตค่าดีฟอลต์นี้ได้
แต่ในทางอ้อมแล้ว “มี” ครับ
เหรอๆ ทำยังไงล่ะ?
ก่อนอื่น เราต้องทำความเข้าใจกับ “สาเหตุ” กันก่อนครับ
สาเหตุของอาการนี้คือ ข้อมูลที่คิดว่าเป็นตัวเลขนั้น ไม่ใช่ตัวเลข 100%
สิ่งที่ทำให้ไม่เป็นตัวเลข 100% คือ ตัวหนังสือ (Text) และ เซลล์ว่าง นั่นเอง!
หลักการสรุปค่าของเจ้าพิเวิทเทเบิลคือ
ถ้าข้อมูลเป็นตัวเลข สรุปเป็น Sum Of
ถ้าข้อมูลไม่ใช่ตัวเลข สรุปเป็น Count Of
เพราะฉะนั้น วิธีการแก้ไขก็คือ เปลี่ยนข้อมูลให้เป็นตัวเลข 100% นั่นเอง
บางครั้งข้อมูลเป็นตัวเลขนะ แต่พิเวิทสรุปให้เป็น Count Of เฉยเลย
คำว่า “ไม่ใช่ตัวเลข” นั้นแบ่งกว้างๆได้เป็น 2 กรณีครับ
- เป็นตัวเลข แต่บางเซลล์เป็นค่าว่าง
- หน้าตาเป็นตัวเลข แต่จริงๆแล้วเป็นเท็กซ์
สำหรับกรณีที่ 1 “คือปัญหาที่เราไม่รู้ว่าเป็นปัญหา
ลองเช็คข้อมูลในตารางดิบ (ฐานข้อมูลของพิเวิทเทเบิลนั้น) ว่าคอลัมน์ที่เราต้องการให้สรุปเป็น Sum Of มี “ค่าว่าง” หรือเปล่า?
เจ้าค่าว่างนั้นเอง ที่ทำให้ตัวเลขไม่เป็นตัวเลข 100%
ค่าว่างที่ว่า ไม่ใช่ศูนย์นะครับ แต่เป็นค่าว๊างว่าง หรือไม่มีตัวเลขอะไรเลย
จากข้อมูลด้านบน ถ้าเราสรุปข้อมูลจากคอลัมน์ Volume จะได้เป็น Count of Volume
แต่ถ้าเราสรุปข้อมูลจากคอลัมน์ Sales จะได้เป็น Sum of Sales
เพราะคอลัมน์ Volume มีค่าว่าง
คอลัมน์ Sales มีค่าบางค่าเป็นศูนย์ แต่ศูนย์ไม่ใช่ค่าว่าง ปัญหานี้จึงไม่เกิดขึ้น
ศูนย์แปลว่ามีค่าเป็นศูนย์ ไม่ได้แปลว่าเป็นค่าว่าง สองอย่างนี้ไม่เหมือนกันนะครับ
วิธีแก้ก็คือต้อง เปลี่ยนค่าว่างให้เป็นศูนย์ นั่นเอง!
ข้อมูลมีตั้ง 5,000 แถว จะเปลี่ยนยังไงล่ะ เปลี่ยนแมนวลทีละแถวเนี่ยนะ?
ทำได้ง่ายๆ โดยเทคนิคนี้ครับ
1 ลากครอบคอลัมน์ที่มีค่าว่าง (ครอบเฉพาะส่วนที่มีข้อมูลนะครับ อย่าเลือกทั้งคอลัมน์)
2 กด F5 แล้วเลือก Special...
3 เลือก Blanks
เอ็กเซลจะไฮไลต์เซลล์ว่างทุกเซลล์ในคอลัมน์นั้น ได้หน้าตาเป็นแบบนี้
4 พิมพ์ 0 (ศูนย์) แล้วกด Ctrl+Enter
ย้ำ! ว่าต้องกด Ctrl+Enter ไม่ใช่ Enter นะครับ
ถ้าทำถูกต้อง เลขศูนย์จะแทนที่เซลล์ว่างทุกเซลล์แบบนี้ครับ
5 เซฟไฟล์ แล้วคลิกรีเฟรชที่พิเวิทเทเบิล พอสรุปค่าใหม่จะได้เป็น Sum Of แน่นอน เยส!!
สำหรับกรณีที่ 2 “หน้าตาเป็นตัวเลข แต่จริงๆแล้วเป็นเท็กซ์”
มักเกิดจากการเอ็กซ์พอร์ตข้อมูลออกมาจากฐานข้อมูลอะไรซักอย่าง (หรือโปรแกรมอะไรบางอย่าง) บังเอิ๊ญเจ้าโปรแกรมนั้นดันเก็บข้อมูลที่หน้าตาเป็นตัวเลขในลักษณะของเท็กซ์
จะรู้ได้ยังไงล่ะ?
สังเกตง่ายๆที่มุมซ้ายบนของแต่ละเซลล์ จะมีสามเหลี่ยมสีเขียวโผล่ออกมา และตัวเลขจะอยู่ชิดซ้าย (แทนที่จะชิดขวา)
แบบนี้ครับ
แก้ไขยังไงดี มีวิธีเร็วๆไหม?
ง่ายมากครับ เพียง 2 คลิกเท่านั้น
1 ลากครอบข้อมูลทั้งหมดในคอลัมน์นั้น ที่มุมซ้ายบนจะปรากฏเครื่องหมายคล้ายๆคำเตือนขึ้นมา
ที่เป็นเช่นนี้ เพราะเอ็กเซลฟ้องเราว่า สิ่งที่หน้าตาเหมือนตัวเลขในคอลัมน์นี้ จริงๆแล้วเป็นเท็กซ์นะ!
2 คลิกที่เครื่องหมายคำเตือนนั้น แล้วเลือก Convert to Number
ถ้าทำถูก เครื่องหมายสามเหลี่ยมสีเขียวที่มุมซ้ายบนจะหายไป และตัวเลขจะกลับมาชิดขวาแบบนี้ครับ
เซฟไฟล์ แล้วคลิกรีเฟรชที่พิเวิทเทเบิล พอสรุปค่าใหม่จะได้เป็น Sum Of แน่นอน เย้!
อันที่จริงแล้ว ปัญหาเรื่อง Sum Of, Count Of แก้ไขง่ายๆโดยการเปลี่ยน Summarize value field by ใน Value Field Settings จาก Count ให้เป็น Sum
แต่ถ้าสาเหตุไม่ถูกแก้ เราก็ต้องแก้ปัญหาแบบนี้ร่ำไป ขัดใจจอร์จจริงๆเล๊ย..
ลองหาสาเหตุกันดูก่อนครับ เชื่อว่าอยู่ใน 1 ใน 2 กรณีที่ผมอธิบายแน่นอน
อย่างไรก็ตาม ปัญหานี้จะไม่เกิดขึ้นเลย ถ้าเรามีวินัยในการเก็บข้อมูล
ถ้าใครไม่เคยเจอปัญหานี้ ขอแสดงความยินดีด้วยครับ ระบบงานของคุณมีวินัยดีมากๆ ^_^
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^
แล้วถ้าไม่ต้องการให้คอลัมภ์sumแต่แสดงตัวเลขเฉยๆตามค่าที่ดึงมา ทำได้มั้ยคะ