ข้ามไปเนื้อหาหลัก
Wise Success (Thailand)
เทคนิค Excel

สอนใช้ Pivot Table ทำรายงานยอดขายใน Excel ทีละขั้น พร้อมภาพหน้าจอ

· อ่านประมาณ 14 นาที

ทุกสิ้นเดือนมีคำถามเดิม ๆ ว่า "ยอดขายภาคไหนสูงสุด สินค้าหมวดไหนขายดี เดือนนี้ลดลงจากเดือนก่อนเท่าไร" ถ้าตอบด้วยสูตรทีละช่อง ต้องเขียนสูตรเป็นสิบ ๆ ตัว แต่ถ้าใช้ Pivot Table จะได้รายงานสรุปที่ลากวางเสร็จในไม่กี่นาที และเปลี่ยนมุมมองได้ทันทีโดยไม่ต้องเขียนสูตรเลย

บทความนี้เป็นบทเรียนสอน Excelแบบลงมือทำ ตามไปทีละขั้นด้วยกรณีจริง คือ รายงานยอดขายครึ่งปีแรก จากข้อมูลใบสั่งขาย 120 รายการ ตั้งแต่สร้าง Pivot Table จัดกลุ่มวันที่เป็นเดือน แสดงเป็นเปอร์เซ็นต์ ไปจนถึงใส่ Slicer และทำ Pivot Chart (ถ้าอยากรู้ว่า Pivot Table คืออะไรและเทคนิคทั้งหมดอย่างครบถ้วน อ่านที่Pivot Table คืออะไร สอนทำรายงานสรุป)

ก่อนเริ่ม: ตารางข้อมูลต้นทางต้องเป็นแบบนี้

Pivot Table ทำงานได้ดีเมื่อข้อมูลเป็นตารางแบบ "ฐานข้อมูล" คือ แถวแรกเป็นหัวข้อ 1 แถว ทุกคอลัมน์มีหัวข้อไม่ซ้ำกัน หนึ่งแถวคือหนึ่งรายการ ไม่มีแถวว่าง ไม่มีเซลล์ที่ถูก Merge และไม่มีแถวรวมปนอยู่

ตารางข้อมูลใบสั่งขายใน Excel มีคอลัมน์เลขที่ วันที่ ภาค หมวดสินค้า และยอดขาย พร้อมสำหรับสร้าง Pivot Table
ข้อมูลต้นทาง 5 คอลัมน์ รวม 120 แถว (แสดงบางส่วน) หนึ่งแถวคือหนึ่งใบสั่งขาย

เคล็ดลับ: คลิกในตารางแล้วกด Ctrl + T เพื่อแปลงเป็น Table เมื่อเพิ่มข้อมูลใหม่ในภายหลัง Pivot Table จะรวมแถวใหม่ให้เมื่อ Refresh ถ้าวันที่ในคอลัมน์ B เป็นข้อความ ให้แปลงเป็นวันที่จริงก่อน (ดูวิธีในสูตรวันที่ใน Excel)

ขั้นที่ 1: สร้าง Pivot Table

  1. คลิกเซลล์ใดก็ได้ในตารางข้อมูล
  2. ไปที่แท็บ Insert > PivotTable > From Table/Range
  3. ตรวจช่วงข้อมูลที่ Excel เลือกให้ แล้วเลือก New Worksheet กด OK
หน้าต่างสร้าง PivotTable ใน Excel เลือกช่วงข้อมูลและเลือกวางในชีตใหม่
เลือกช่วงข้อมูลและวางใน New Worksheet เพื่อไม่ให้ทับข้อมูลต้นทาง

ขั้นที่ 2: ลากฟิลด์ลงพื้นที่ทั้ง 4

ชีตใหม่จะมีแผง PivotTable Fields ทางขวา ซึ่งแสดงหัวข้อคอลัมน์ทั้งหมดเป็นฟิลด์ ให้ลากฟิลด์ลงพื้นที่ใดพื้นที่หนึ่งใน 4 พื้นที่ ซึ่งแต่ละพื้นที่ทำหน้าที่ต่างกัน

พื้นที่ทำหน้าที่ในตัวอย่างนี้
Rowsหัวข้อแถวด้านซ้ายภาค
Columnsหัวข้อคอลัมน์ด้านบนหมวดสินค้า
Valuesตัวเลขที่ต้องการคำนวณ (ผลรวม นับ เฉลี่ย)ผลรวมของ ยอดขาย
Filtersตัวกรองทั้งรายงานยังไม่ใช้ (ใช้ Slicer แทนในขั้นที่ 5)
แผง PivotTable Fields ใน Excel ลากภาคไปที่ Rows หมวดสินค้าไปที่ Columns และยอดขายไปที่ Values
ภาคอยู่ใน Rows หมวดสินค้าอยู่ใน Columns และยอดขายอยู่ใน Values

ผลลัพธ์ที่ได้คือรายงานสรุปที่ Excel คำนวณให้ทั้งหมด รวมถึงยอดรวมแถวและคอลัมน์

ผลลัพธ์ Pivot Table ยอดขายแยกตามภาคและหมวดสินค้า พร้อมยอดรวมทั้งแถวและคอลัมน์
ยอดขายรวม 1,778,090 บาท แยกตาม 4 ภาคและ 4 หมวดสินค้า

อ่านรายงานนี้ได้ทันทีว่า ภาคกลางขายสูงสุด 555,890 บาท (31.3% ของทั้งหมด) และสินค้าเฟอร์นิเจอร์กับอุปกรณ์ ITรวมกันคิดเป็นกว่า 82% ของยอดขาย ส่วนเครื่องเขียนขายได้น้อยที่สุด ถ้าอยากเปลี่ยนมุมมองเป็น "หมวดสินค้าเป็นแถว ภาคเป็นคอลัมน์" ก็แค่ลากฟิลด์สลับกัน ไม่ต้องทำใหม่

ตกแต่งตัวเลขให้อ่านง่าย

  • ใส่คอมมาคั่นหลักพัน: คลิกขวาที่ตัวเลขใน Pivot > Value Field Settings > Number Format > Number > ติ๊ก Use 1000 Separator
  • เปลี่ยนชื่อหัวข้อ: คลิกเซลล์ "ผลรวมของ ยอดขาย" แล้วพิมพ์ชื่อใหม่ เช่น "ยอดขาย (บาท)"
  • เปลี่ยนสไตล์ตาราง: แท็บ PivotTable Design > เลือก Style ที่ต้องการ

ขั้นที่ 3: จัดกลุ่มวันที่เป็นเดือน

เมื่อลากฟิลด์ "วันที่" ลงใน Rows Excel รุ่นใหม่ (2016 ขึ้นไป) จะจัดกลุ่มเป็นเดือนให้เองโดยอัตโนมัติ แต่ถ้าเครื่องของคุณแสดงเป็นรายวัน หรืออยากเปลี่ยนเป็นไตรมาสหรือปี ให้ทำดังนี้

  1. คลิกขวาที่วันที่ใดก็ได้ในรายงาน เลือก Group
  2. เลือก Months (เลือกหลายอย่างพร้อมกันได้ เช่น Months + Years ถ้าข้อมูลข้ามปี) แล้วกด OK
หน้าต่าง Grouping ใน Pivot Table ของ Excel เลือกจัดกลุ่มวันที่เป็น Months
เลือก Months เพื่อรวมยอดรายวันเป็นรายเดือน

ถ้าเมนู Group เป็นสีเทาหรือขึ้นข้อความ "Cannot group that selection" แปลว่าในคอลัมน์วันที่มีเซลล์ว่างหรือเซลล์ที่เป็นข้อความปนอยู่ ให้แก้ข้อมูลต้นทางแล้ว Refresh

ขั้นที่ 4: แสดงค่าเป็นเปอร์เซ็นต์

ผู้บริหารมักอยากเห็นสัดส่วนมากกว่าตัวเลขดิบ ลากฟิลด์ "ยอดขาย" ลง Values อีกรอบ จะได้คอลัมน์ที่สอง แล้วคลิกขวา > Show Values As > % of Grand Total จะได้เปอร์เซ็นต์ของยอดรวมทั้งหมด

Pivot Table ยอดขายรายเดือนม.ค.ถึงมิ.ย. พร้อมคอลัมน์เปอร์เซ็นต์ของยอดรวม
ยอดขายรายเดือนและสัดส่วนต่อยอดรวม มกราคมสูงสุด 23.8% เมษายนต่ำสุด 11.4%
ตัวเลือกใน Show Values Asใช้ตอบคำถาม
% of Grand Totalแต่ละรายการคิดเป็นกี่ % ของยอดรวมทั้งหมด
% of Column Total / % of Row Totalสัดส่วนภายในแต่ละคอลัมน์ หรือแต่ละแถว
% Difference Fromเดือนนี้ต่างจากเดือนก่อนกี่ %
Running Total Inยอดสะสมจากต้นปีถึงเดือนนี้
Rank Largest to Smallestจัดอันดับยอดขาย

ขั้นที่ 5: ใส่ Slicer ให้กรองด้วยปุ่ม และทำ Pivot Chart

  1. คลิกใน Pivot Table แล้วไปที่แท็บ PivotTable Analyze > Insert Slicer ติ๊กฟิลด์ "ภาค" กด OK
  2. กดปุ่มภาคที่ต้องการ รายงานจะกรองทันที (กด Ctrl ค้างเพื่อเลือกหลายภาค หรือกดไอคอนล้างตัวกรองที่มุมขวาบนของ Slicer)
  3. เลือก PivotTable Analyze > PivotChart แล้วเลือก Clustered Column
Slicer เลือกภาคกลางและ Pivot Chart แสดงยอดขายรายเดือนของภาคกลางใน Excel
กด Slicer เลือกภาคกลาง Pivot Chart เปลี่ยนตามทันที ยอดสูงสุดคือเดือนมกราคม 215K

Pivot Chart คือกราฟที่ผูกกับ Pivot Table เมื่อกรองหรือจัดกลุ่มข้อมูล กราฟเปลี่ยนตาม การตกแต่งกราฟให้สวยทำได้เหมือนกราฟปกติ ดูวิธีในสอนสร้างกราฟใน Excel และกำหนดลักษณะกราฟ

ขั้นที่ 6: อัปเดตข้อมูลและแก้ปัญหาที่พบบ่อย

Pivot Table ไม่อัปเดตเองอัตโนมัติ เมื่อแก้หรือเพิ่มข้อมูลต้นทาง ให้คลิกขวาที่ Pivot > Refresh (หรือ Alt + F5) ถ้าอยากให้ Refresh ทุกครั้งที่เปิดไฟล์ ไปที่ PivotTable Options > Data > ติ๊ก Refresh data when opening the file

อาการสาเหตุที่พบบ่อยวิธีแก้
ยอดขายแสดงเป็น "จำนวนนับ" ไม่ใช่ผลรวมในคอลัมน์ยอดขายมีเซลล์ว่างหรือข้อความปนแก้ข้อมูลต้นทางให้เป็นตัวเลข หรือเปลี่ยนที่ Value Field Settings > Sum
Refresh แล้วข้อมูลใหม่ไม่เข้าช่วงข้อมูลไม่ได้ขยายแปลงข้อมูลเป็น Table ด้วย Ctrl + T หรือแก้ที่ Change Data Source
ชื่อภาคเดียวกันแยกเป็น 2 แถวมีช่องว่างเกินท้ายชื่อใช้ฟังก์ชัน TRIM ทำความสะอาดข้อมูลต้นทาง
กว้างคอลัมน์เปลี่ยนทุกครั้งที่ Refreshตั้งค่า Autofit ไว้PivotTable Options > ยกเลิก Autofit column widths on update

Pivot Table หรือสูตร SUMIFS ดี

Pivot Table เหมาะเมื่อต้องการสำรวจข้อมูลหลายมุมมองอย่างรวดเร็ว ส่วนSUMIFSเหมาะกับรายงานที่มีรูปแบบตายตัวและต้องการควบคุมตำแหน่งทุกช่อง หลายองค์กรใช้ทั้งสองแบบร่วมกัน คือใช้ Pivot สำรวจหาประเด็น แล้วใช้สูตรทำรายงานฉบับส่งผู้บริหาร

คำถามที่พบบ่อย

Pivot Table ใช้ได้ใน Excel เวอร์ชันไหน

ใช้ได้ทุกเวอร์ชันตั้งแต่ Excel 2007 ขึ้นไป ส่วน Slicer มีตั้งแต่ Excel 2010 และการจัดกลุ่มวันที่อัตโนมัติมีตั้งแต่ Excel 2016

Pivot Table ทำให้ข้อมูลต้นทางเปลี่ยนไหม

ไม่เปลี่ยน Pivot Table เป็นเพียงรายงานที่อ่านข้อมูลมาสรุป จะลากฟิลด์ เปลี่ยนมุมมอง หรือลบ Pivot ทิ้งก็ไม่กระทบข้อมูลต้นทาง

ข้อมูลมีเป็นแสนแถว Excel รับไหวไหม

ไหว Excel รองรับ 1 ล้านแถว Pivot Table ประมวลผลได้เร็ว ถ้าข้อมูลมาจากหลายไฟล์หรือหลายชีต แนะนำให้รวมด้วยPower Queryก่อนแล้วจึงทำ Pivot

สรุป

ขั้นตอนทำรายงานด้วย Pivot Table คือ จัดข้อมูลเป็นตารางฐานข้อมูล → Insert > PivotTable → ลากฟิลด์ลง Rows / Columns / Values → จัดกลุ่มวันที่เป็นเดือน → Show Values As เป็น % → ใส่ Slicer และ Pivot Chart → Refresh เมื่อข้อมูลเปลี่ยน เมื่อทำเป็น ใช้เวลาเพียงไม่กี่นาทีต่อรายงาน แทนที่จะเขียนสูตรเป็นชั่วโมง

ถ้าอยากฝึกกับข้อมูลจริงของงานคุณ Wise Success มีอบรม Excelและสอน Excelที่ครอบคลุม Pivot Table, Dashboard และการวิเคราะห์ข้อมูล ในหลักสูตรExcel Advance (2 วัน) ทั้งแบบPublic TrainingและIn-house สำหรับองค์กร หรือติดต่อเราเพื่อปรึกษาหัวข้อให้ตรงกับทีม

บทความที่เกี่ยวข้อง

เทคนิค Excel ·

สอนสร้างกราฟใน Excel: เลือกชนิดกราฟและกำหนดลักษณะกราฟให้ดูเป็นมืออาชีพ

สอน Excel ทำกราฟตั้งแต่เตรียมข้อมูล เลือกชนิดกราฟ สร้างกราฟ ไปจนถึงปรับแต่งชื่อกราฟ สี Data Labels แกน เส้นตาราง และกราฟผสมสองแกน พร้อมภาพหน้าจอและตารางแก้ปัญหาที่พบบ่อย

อ่านต่อ →

เทคนิค Excel ·

สูตรหา OT ใน Excel: คำนวณชั่วโมงและค่าล่วงเวลาทีละขั้น

สอนสูตรหา OT ใน Excel ตั้งแต่คำนวณชั่วโมงทำงานจากเวลาเข้า-ออก หาชั่วโมงล่วงเวลา ปัดเศษ แยกวันทำงานกับวันหยุด คำนวณค่าการทำงานล่วงเวลาตามอัตรา และสรุปยอดรายเดือนของพนักงานแต่ละคน

อ่านต่อ →

เทคนิค Excel ·

สูตรวันที่ใน Excel: รวมสูตร วัน เดือน ปี ที่ใช้บ่อย พร้อมตัวอย่าง

รวมสูตร Excel วัน เดือน ปี ที่ใช้บ่อย ตั้งแต่ DATE, YEAR, MONTH, DAY การแสดงวันที่ภาษาไทยแบบ พ.ศ. บวกเดือน หาวันสิ้นเดือน นับวันทำงาน และแปลงข้อความเป็นวันที่ พร้อมภาพหน้าจอตัวอย่าง

อ่านต่อ →

ต้องการจัดอบรมให้ทีมในองค์กร?

ปรับเนื้อหาให้ตรงกับงานจริงของทีมคุณ สอนที่บริษัทหรือ Online ได้ ส่งรายละเอียดมา เราตอบกลับพร้อมใบเสนอราคา

☎ โทรเลย LINE @wisesuccess

เลือกประเภทคุกกี้ที่คุณอนุญาต ปรับเปลี่ยนได้ทุกเมื่อที่ลิงก์ "ตั้งค่าคุกกี้" ท้ายเว็บไซต์