PivotTable

Excel PivotTable & PivotChart หลักสตู รการใชโปรแกรม Microsoft Excel Pivot Table วนั ที่ 6-7 กรกฎาคม 2560 เวลา 08.30 - 16.00 น. ณ หองปฏิบตั ิการคอมพวิ เตอร< มหาวิทยาลัยสงขลานครินทร< วิทยาเขตตรัง 55751 อาจารย<สิริพรรณ แซAต่ิม สาขาวิชาการจดั การเทคโนโลยีสารสนเทศ คณะพาณิชยศาสตร<และการจัดการ มหาวิทยาลัยสงขลานครินทร<

สารบญั หัวขอ หนา PivotTable 1 3 PivotTable กับการสรุปขอมูล 3 ข้ันตอนการใชงาน PivotTable 5 การเตรียมขอมูลเพ่ือใชวิเคราะห-และสรุปผลขอมลู 10 สว0 นประกอบของ PivotTable 13 WorkShop ท่ี 1 การเตรยี มขอมลู 17 การสรางและการแกไข PivotTable 23 WorkShop ท่ี 2 สราง PivotTable 1 มิติ & การฟอร-แมตตวั เลขและกําหนดชื่อฟ@ลด- 31 WorkShop ที่ 3 สราง PivotTable 1 มิติ & การคัดกรองดวย Label Filter, Value Filter 37 WorkShop ท่ี 4 สราง PivotTable 2 มติ ิ & การคัดกรองดวย Date Filter 43 WorkShop ที่ 5 สราง PivotTable 1 มติ ิ & การคัดกรองขอมลู ดวย Slicer 50 WorkShop ที่ 6 สราง PivotTable 2 มิติ & การคัดกรองดวย Timeline 58 WorkShop ท่ี 7 สราง PivotTable 2 มติ ิ & การจดั กลุ0มตามช0วงเวลา 65 WorkShop ท่ี 8 สราง PivotTable 2 มติ ิ & การจดั กลุ0มขอมลู ที่ไม0ใช0วันท่ี WorkShop ท่ี 9 สราง PivotTable 2 มิติ & การจดั กลุ0มขอมลู ตามตัวเลข 70 WorkShop ท่ี 10 สราง PivotTable 3 มิติ & การใส0ฟล@ ดก- ารคาํ นวณ (Calculate Field) & 81 การกําหนดการสรุปผล WorkShop ที่ 11 การปรับแตง0 PivotTable PivotChart 91 93 การสราง PivotChart จาก PivotTable และทาํ การกรองขอมลู 95 การสราง PivotChart จากขอมูลดบิ และกรองขอมูลโดยใช Slicer และ Timeline การกําหนดชอ่ื (Define Name) ใหกบั เซลล-หรือชว0 งเซลล-ขอมูล

PivotTable PivotTable เปนเคร่อื งมือหนึ่งในโปรแกรม Microsoft Excel ท่ีมีการนําเอาขอ) มูลดิบจํานวนมาก มา สรุปผลเปนตารางสรุปผลข)อมูล ผ)ูใช)สามารถปรับเปล่ียนการแสดงผลข)อมูลได)อย:างสะดวกง:ายดาย โปรแกรม สามารถแสดงผลการวิเคราะห<ในรูปแบบตารางที่มีมุมมองแตกต:างกันหลายรูปแบบ เช:น PivotTable 1 มิติ PivotTable 2 มิติ หรือ PivotTable หลายมิติ ก็ได) นอกจากนี้ ยังสนับสนุนการจัดกลุ:มข)อมูล (Grouping) การคดั กรองขอ) มลู (Filtering) การคาํ นวณ (Calculate Field) เปนต)น 1. PivotTable กบั การสรปุ ขอมูล ความสามารถของ PivotTable ในการสรปุ ขอ) มูลมีดังน้ี 1. PivotTable สามารถสรุปข)อมูลจํานวนมากได)อย:างรวดเร็ว เช:น การสรุปยอดขาย Top 10 แยกตามกลม:ุ สนิ คา) 2. สามารถหาผลรวมยอ: ย (Subtotal) และการรวมข)อมูลตัวเลข (Grand Total) การสรุปขอ) มูล ตามประเภทและประเภทย:อย และการสร)างการคํานวณและสูตรแบบกําหนดเอง 3. สามารถขยายและการยุบระดับของข)อมูลเพื่อเน)นผลลัพธ< และการเจาะลงไปในรายละเอียด จากขอ) มลู สรุปสาํ หรับเรื่องทอี่ ย:ูในความสนใจได) 4. สามารถกรองข)อมูล สามารถเรียงลําดับ เช:น ให)แสดงข)อมูลสูงสุด 10 อันดับแรก ข)อมูลที่ มากกวา: /น)อยกวา: ค:าท่กี าํ หนด กําหนดชว: งขอ) มูลเอง เปนต)น 5. สามารถจัดกล:ุมตามช:วงเวลา เชน: วินาที นาที ชั่วโมง ไตรมาสรายปV หรือกําหนดช:วงเวลาเอง เปนต)น สามารถจัดรปู แบบตามเงื่อนไขของเซตข)อมลู ยอ: ยท่ีสนใจ เปนตน) 2. ตัวอยา\" งคําถาม ที่ PivotTable สามารถใหคําตอบได o สนิ ค)าแต:ละกลุ:มมียอดขายเทา: ใด / สินค)าใดขายดที ส่ี ดุ ในแต:ละกลมุ: o ลูกคา) รายใหญ:ของบรษิ ทั คือใคร / รายได)หลกั ของบริษทั มาจากที่ใด o การขายสินคา) แตล: ะรายการมีความสมั พนั ธ<กบั ชว: งเวลาหรอื ไม: o พฤตกิ รรมลูกค)าในการซอื้ สินค)า มคี วามเกี่ยวข)องกบั ช:วงเวลาหรือไม: หนา 1

ตัวอยา\" งที่ 1 ตารางขอมูลค\"าใชจ\"ายภายในบาน ท่ีถูกนาํ ไปสราง PivotTable และ PivotChart  ข)อมูลคา: ใชจ) า: ยภายในบ)าน  สร)างเปน PivotTable  สร)างเปน PivotChart ภาพท่ี 1 การสราง PivotTable และ PivotChart จากขอมูล ทีม่ า: https://goo.gl/y5FYC0 หนา 2

3. ขั้นตอนการใชงาน PivotTable ข้ันตอนการสรา) ง PivotTable มดี งั นี้ 1. เตรียมข)อมูลใน MS Excel โดยมีลักษณะเปนฐานข)อมูล (Data Source) หรือใช)ข)อมูล ภายนอก 2. กําหนดรูปแบบของรายงาน (Report Form) 3. สรา) ง PivotTable ตามรูปแบบท่ีกาํ หนด 4. ตกแต:ง PivotTable 4. การเตรยี มขอมูลเพอ่ื ใชวิเคราะหแF ละสรปุ ผลขอมูล เนื่องจาก PivotTable เปนเครื่องมือสําหรับการวิเคราะห<ข)อมูล ดังนั้นผ)ูใช)จึงสามารถใช)ข)อมูลจาก 4 แหลง: ดังน้ี 1. Microsoft Excel List or Database : ขอ) มูลดิบอยู:ในไฟล<ปcจจบุ ัน 2. External Data Source : ข)อมลู ดิบอย:ูในไฟล< Excel อ่นื 3. Multiple Consolidation Range : ขอ) มูลดิบกระจดั กระจายใน Worksheet ตา: ง ๆ 4. PivotTable Report or PivotChart Report : สร)าง PivotTable จาก PivotTable เดมิ โดยผ)ใู ช)ตอ) งเตรยี มข)อมูลที่ตอ) งการจะวิเคราะห< ซึ่งควรมลี กั ษณะดังต:อไปน้ี 1. ขอ) มูลมีลกั ษณะเปนตาราง มีหวั เรอ่ื งชัดเจน ไมซ: ํา้ ซ)อน 2. หากในตารางมีข)อมลู ท่ีเปนลักษณะหัวข)อย:อย/จํานวนรวมย:อย ใหท) ําการลบท้งิ 3. หากตารางมีข)อมลู ไม:ครบถ)วน ควรเติมขอ) มูลให)เตม็ ทุกช:อง 4. ตารางท่ีนํามาทาํ Multiple Consolidation Range แต:ละตารางควรมีข)อมูลรปู แบบเดียวกนั ตัวอยา\" งที่ 2 ลกั ษณะตารางที่ไม:เหมาะต:อการทํา PivotTable เนื่องจากมีลกั ษณะเปนตารางไขว) (Crosstab) ภาพท่ี 2 ลกั ษณะตารางขอมูลแบบ Crosstab หนา 3

ตวั อยา\" งที่ 3 ลกั ษณะตารางขอมลู ท่ีเหมาะสม สําหรบั ทาํ PivotTable ภาพท่ี 3 ลกั ษณะตารางขอมูลแบบ List หนา 4

5. ส\"วนประกอบของ PivotTable PivotTable จะมี 3 ส:วนหลัก ไดแ) ก: สว\" น A คอื เครื่องมอื สําหรับการกาํ หนดคา: ของขอ) มูลใน PivotTable สว\" น B คอื สว: นของฟgลดใ< นตารางที่ใช)สร)าง PivotTable ส\"วน C คอื พื้นทส่ี าํ หรับแสดงข)อมลู มีลกั ษณะเปนตารางข)อมูล เรยี กว:า PivotTable ภาพท่ี 4 สว: นประกอบของ PivotTable ทง้ั น้ี เมอื่ มกี ารเลือกข)อมลู ในสว: นของ B ผลลัพธจ< ะแสดงในส:วน C ดงั ภาพ หนา 5

ภาพที่ 5 รายการเขตข)อมูล PivotTable ทีม่ า: https://goo.gl/yPLbqQ ในที่นี้หมายเลข 2-5 ในตารางมีความหมายดังนี้ หมายเลข 2 (FILTERS) หมายเลข 3 (COLUMNS) หมายถงึ การเลอื กฟลg ด<ให)แสดงใน หมายถึงการเลือกฟgลด<ให)แสดงใน ลักษณะ Page Field แนวคอลัมน< หมายเลข 4 (ROWS) หมายเลข 5 (VALUES) หมายถงึ การเลอื กฟgลด<ให)แสดงใน หมายถึงค:าของข)อมลู จากฟgลดท< ี่ แนวแถว ถกู เลอื ก มุมมองรายการเขตขอมลู (PivotTable Field List) ผ)ูใช)สามารถเปล่ียนมุมมองรายการเขตข)อมูลที่แตกต:างกัน 5 มุมมอง โดยการเลือกมุมมองรายการ เขตขอ) มูล ดังนี้ หนา 6

ภาพท่ี 6 การเปลยี่ นมุมมองรายการเขตขอ) มูล PivotTable โดยแตล: ะมมุ มองจะแตกตา: งกนั ดงั นี้ มุมมองแบบที่ 1: มุมมองรายการเขตข)อมูล PivotTable แบบ Fields Section and Areas Section Stacked เปนมุมมองเรม่ิ ต)น เหมาะสาํ หรับข)อมลู ทม่ี ปี ริมาณน)อย ภาพที่ 7 มุมมองรายการเขตขอ) มลู แบบ Fields Section and Areas Section Stacked หนา 7

มุมมองแบบท่ี 2: มุมมองรายการเขตข)อมูล PivotTable แบบ Fields Section and Areas Section Side-By-Side เปนมุมมองสําหรับการเพิ่มและเอาเขตข)อมูลออก เม่ือมีเขตข)อมูลมากกว:าสี่เขตข)อมูล ในแตล: ะพื้นที่ ภาพที่ 8 มมุ มองรายการเขตข)อมูลแบบ Fields Section and Areas Section Side-By-Side มุมมองแบบท่ี 3: มุมมองรายการเขตข)อมูล PivotTable แบบ Fields Section Only เปนมุมมอง สาํ หรับการเพม่ิ และเอาเขตขอ) มูลหลายเขตข)อมูลออกเทา: นั้น ภาพท่ี 9 มมุ มองรายการเขตข)อมูลแบบ Fields Section Only หนา 8

มุมมองแบบท่ี 4: มุมมองรายการเขตข)อมูล PivotTable แบบ Areas Section Only (2 by 2) เปนมุมมองสาํ หรบั การจดั เรยี งเขตขอ) มลู หลายเขตข)อมูลใหม:เทา: น้ัน ภาพที่ 10 มุมมองรายการเขตขอ) มลู แบบ Areas Section Only (2 by 2) มุมมองแบบท่ี 5: มุมมองรายการเขตข)อมูล PivotTable แบบ Areas Section Only (1 by 4) เปนมมุ มองสาํ หรับการจัดเรยี งเขตขอ) มูลหลายเขตข)อมลู ใหม:เทา: นัน้ ภาพที่ 11 มุมมองรายการเขตขอ) มูลแบบ Areas Section Only (1 by 4) หนา 9

WorkShop ท่ี 1 • เตรยี มขอมลู 1. เปgดไฟล< Sale1.xlsx โดยจะมีการใช)ขอ) มูลจาก Worksheet ช่ือ YearData ดังภาพ ภาพท่ี 12 ลักษณะข)อมูลที่ตอ) งได)รับการปรับปรงุ ก:อนการทํา PivotTable 2. ข)อมูลในไฟล< Sale1.xlsx เปนการบันทึกข)อมูลการขายสินค)าของบริษัทหน่ึง ซึ่งมีสาขาอยู:ในภูมิภาค ต:างๆ ในภาคเหนือ ภาคกลาง ภาคตะวนั ออก ภาคตะวันตก และภาคใต) ประกอบด)วยข)อมลู 11 คอลมั น< ดังน้ี 2.1 Region (ภมู ิภาค) / Province (จงั หวัด) / District (อาํ เภอ) 2.2 CusName (ชอื่ ลกู ค)า) 2.3 Product_type (ประเภทสนิ ค)า) 2.4 Product_Name (ชื่อสนิ คา) ) 2.5 Sale_Date (วนั ท่ีขาย) 2.6 Cost Price (ราคาทนุ ) 2.7 Sales Price (ราคาขาย) 2.8 Amount (จาํ นวนที่ขาย) 2.9 Discount (ส:วนลดเปนเปอร<เซน็ ต<) 2.10 Net Sales (ยอดชาํ ระสุทธิ) 2.11 Pay_Method (วิธชี ําระเงินของลกู ค)า) หนา 10

3. ใน Worksheet ช่ือ YearData จะพบว:า Column A ประกอบด)วยขอ) มูล 3 กลม:ุ ซ้าํ ซอ) นกันภายใน ซ่ึง ประกอบด)วยข)อมูล ดงั นี้ (1) Region (ภูมิภาค) (2) Province (จงั หวดั ) (3) District (อําเภอ) ดังน้นั จึงต)องปรับปรุงโครงสร)างตารางไม:ให) Column A มีข)อมูลซํ้าซ)อนกัน โดยการสร)างคอลัมน<เพ่ิม แล)วนาํ ขอ) มูลท่ซี ้ําซอ) นนัน้ กระจายไปบนั ทึกในคอลมั น<ใหม: ดังนี้ 3.1 สร)าง 2 คอลัมน< (Column) ก. ทําการ Insert จํานวน 2 Column ตาํ แหน:ง Column A และ Column B ข. เพิ่มข)อมลู ภาคและจังหวัดให)ครบทกุ เซลล< ใน 2 Column ดังกล:าว 3.2 ลบแถวขอ) มูล (Row) ท่ีมีลักษณะเปนผลสรุป เช:น ผลรวมของรายได)ของภาคกลาง ผลรวม รายได)ของจังหวดั จากตัวอยา: งใหล) บแถวข)อมลู หมายเลข 4, 5, 22, 30, 31, 41, 49, 50, 58, 64, 76, 85, 91, 92, 98, 105, 110, 119, 133, 142, 149, 150, 157, 173, 180 ลบข)อมลู บรรทัดที่ 4-5 ลบข)อมูลบรรทดั ท่ี 22 ลบข)อมูลบรรทัดที่ 30-31 ภาพท่ี 13 ตัวอยา: งแถวข)อมูลทลี่ บ หนา 11

4. ผลลัพธ<ของการปรับปรงุ จะทําให)มีจํานวนคอลัมน<ท้ังหมดเปน 13 คอลัมน< ดังภาพ ภาพที่ 14 ผลลพั ธ<โครงสร)างตารางท่ีพร)อมสาํ หรบั การสร)าง PivotTable โครงสรา) งใหม:จะมีจํานวน 13 คอลมั น< ดังนี้ (หรอื ใชขอมลู จาก Worksheet ช่ือ YearData2) 4.1 Region (ภูมิภาค) 4.2 Province (จงั หวดั ) 4.3 District (อําเภอ) 4.4 CusName (ชือ่ ลูกคา) ) 4.5 Product_type (ประเภทสนิ คา) ) 4.6 Product_Name (ชื่อสนิ ค)า) 4.7 Sale_Date (วนั ท่ีขาย) 4.8 Cost Price (ราคาทุน) 4.9 Sales Price (ราคาขาย) 4.10 Amount (จาํ นวนท่ีขาย) 4.11 Discount (ส:วนลดเปนเปอร<เซ็นต)< 4.12 Net Sales (ยอดชําระสุทธิ) 4.13 Pay_Method (วิธีชําระเงนิ ของลูกคา) ) หนา 12

6. การสรางและการแกไข PivotTable การสรา) ง PivotTable สามารถทาํ ได)ดงั ภาพ โดย 1. คลิกเซลล<ใดเซลล<หน่ึง ภายในบริเวณตารางข)อมูลสําหรับทาํ PivotTable 2. คลกิ เลอื กเมนู Insert เลือกคําส่ัง PivotTable ภาพท่ี 15 การสร)าง PivotTable 3. จากนั้นโปรแกรมจะสอบถามข)อมูลท่ีต)องการใช)สร)าง PivotTable ในท่นี คี่ อื พื้นท่ีขอ) มลู Data2!$A$3:$M$161 ซ่งึ มคี วามหมายดังนี้ Data2! $A$3 : $M$161 ชอ่ื Worksheet เซลลแ< รกที่เลอื ก เคร่ืองหมายคน่ั ระหว:างเซลล<แรกและ เซลลส< ุดท)ายท่ีเลอื ก เซลลส< ุดทา) ยที่เลอื ก พร)อมกันน้ีจะมีเส)นประ ปรากฏล)อมรอบพ้ืนท่ีตารางข)อมูล จากเซลล< A3 ถึง เซลล< M161 ดงั ภาพ หนา 13

ภาพท่ี 16 ข)อมลู ที่ใช)สรา) ง PivotTable 4. คลกิ ปุ}ม OK จะปรากฏหน)าจอ PivotTable ดงั ภาพ หนา 14

ภาพที่ 17 PivotTable 5. ทําการ Drag ขอ) มลู จากส:วน A แล)ววางฟลg ด<ในส:วน B จากนัน้ ข)อมูลจะถกู แสดงในส:วน C 6. หากต)องการยกเลิกข)อมูล PivotTable สามารถทําได)โดยการคลิกช่ือฟgลด<ในส:วน A ที่มี เครือ่ งหมาย  และคลิกเลือกฟลg ด<ใหม:ที่ต)องการ ภาพท่ี 18 PivotTable 7. จากภาพท่ี 18 เปนการแสดงรายได)ทไี่ ด)จากวิธีชาํ ระเงินแบบต:างๆ ซ่งึ รายได)จากวิธีชําระเงินด)วย บัตรเครดิต Mastercard มีมูลค:าสูงสุดคดิ เปน 4,075,743.725 บาท รองลงมาได)แก: การชําระด)วยบัตรเครดิต หนา 15

Visa มีมูลค:า 3,709,967.395 บาท การชําระด)วยเช็คมมี ูลค:า 14,506,913.65 บาท และการชําระด)วยเงินสด มลู ค:า 997,707.55 บาท ตามลาํ ดบั ภาพที่ 19 PivotTable 8. จากภาพที่ 19 เปนการแสดงรายได)จากสินค)าประเภทต:าง ๆ ซึ่ง บริษัทสามารถขายสินค)าได)มาก ที่สุด 3 อับอับแรกคือ ประเภทเคร่ืองใช)ไฟฟ€า มูลค:า 10,498,800.82 บาท รองลงมาคอื สินค)าประเภทอุปกรณ< สาํ นกั งาน มลู คา: 12,288,234.5 บาท และสนิ ค)าประเภทเกษตร มลู คา: 297,409.5 บาท ตามลาํ ดับ หนา 16

WorkShop ท่ี 2 • สราง PivotTable 1 มิติ • การฟอรFแมตตัวเลขและกําหนดช่อื ฟdลดF รายงาน PivotTable 1 มิติ คือ รายงานข)อมูลอย:างง:าย เช:น รายได)การขายสินค)าของแต:ละภาค รายไดก) ารขายสนิ ค)าของแตล: ะจังหวดั รายไดก) ารขายสนิ คา) ของแตล: ะอําเภอ เปนต)น โจทยF - ต)องการทราบรายได)รวม และรายได)เฉลี่ยการขายสินค)าของแตล: ะภาค - ฟลg ดท< ีใ่ ชค) ือ Region, Net Sales 1. ในไฟล< Sale1.xlsx  ใน Worksheet ชอื่ YearData2 ให)คลกิ เซล<ใดๆ ภายในตารางขอ) มูล  เลือกเมนู Insert > PivotTable ดงั ภาพ ภาพที่ 20 การสร)าง PivotTable 2. โปรแกรมจะแสดงหนา) ตา: ง Create PivotTable โดย  หัวข)อ Select a Table or Range : โปรแกรมจะเลือกข)อมูลทุกเซลล<ให)อัตโนมัติ เปน YearData2!$A$3:$M$161 หรือผู)ใช)สามารถเลือกข)อมูลเองได)โดยการ Drag and Drop บริเวณข)อมูลท่ี ตอ) งการ ซ่งึ ในทนี่ ี้ คอื เซลล< YearData2!$A$3:$M$161  หัวข)อ Choose where you want the PivotTable report to be placed ให)เลือก New Worksheet หมายถึง การสร)าง PivotTable ใน Worksheet ใหม: จากน้ันให)คลิกปม}ุ OK หนา 17

ภาพท่ี 21 การเลือกพืน้ ที่ข)อมูลสําหรับสร)าง PivotTable 3. โปรแกรมจะสร)าง Worksheet ใหม: โดยมี PivotTable อย:ูภายใน ช่ือ PivotTable1 4. ให)เลือกฟgลด< ที่จะสร)างตาราง Pivot โดย  Drag ฟgลด<ชอื่ Region มาวางในกรอบ Rows  Drag ฟgลดช< อ่ื Net Sales มาวางในกรอบ Values 2 คร้ัง ภาพท่ี 22 การเลือกฟgลดส< ําหรบั สร)างตาราง PivotTable หนา 18

5. กําหนดรูปแบบตัวเลข ให)มีเคร่ืองหมายจุลภาค โดยคลกิ เมาสFขวาในเซลล<ข)อมูล Sum of NetSales จากนัน้ เลอื กคําสง่ั Value Field Setting ภาพที่ 23 การเรียกคาํ ส่ัง เลือกคําสง่ั Value Field Setting 6. โปรแกรมแสดงหนา) ตา: ง Value Field Setting ดงั ภาพ  ในหัวข)อ Custom Name ใหพ) ิมพ<คาํ วา: “ยอดขาย”  ต:อมาคลกิ ปมุ} Number Format ภาพท่ี 24 การกาํ หนดหัวข)อ Custom Name หนา 19

7. กําหนดรปู แบบขอ) มูลตัวเลข ดงั ภาพ ภาพท่ี 25 การกาํ หนดรปู แบบขอ) มูลตัวเลข 8. จากน้ันดําเนินการคลิกเมาส<ขวาในเซลล<ข)อมูล Sum of NetSales2 จากนั้นเลอื กคําส่ัง Value Field Setting อีกครง้ั ภาพท่ี 26 ผลลัพธ<การฟอร<แมตตวั เลขและกาํ หนดชื่อฟgลด<ใหม: หนา 20

9. โปรแกรมแสดงหนา) ตา: ง Value Field Setting ดงั นี้  ในหัวขอ) Summarize value field by ให)เลือก Average ซ่งึ เปนค:าเฉลี่ยของรายได)  ในหวั ขอ) Custom Name ใหพ) มิ พค< ําวา: “รายได)เฉลย่ี ”  คลกิ ปุม} Number Format แล)วกาํ หนดรูปแบบขอ) มลู ตัวเลขให)มี , ระหว:างตัวเลข ภาพที่ 27 การเปล่ียนการสรุปขอ) มลู และการกาํ หนดหัวข)อ Custom Name ภาพท่ี 28 การกําหนดรปู แบบขอ) มูลตวั เลข หนา 21

10. ผลลัพธ<ที่ได) ประกอบด)วย 3 คอลมั น< ประกอบด)วย  คอลัมน< A คือ ภาค  คอลมั น< B คอื ยอดขาย  คอลัมน< C คือ รายไดเ) ฉลย่ี ภาพที่ 29 ผลลพั ธก< ารเปล่ียนฟอรแ< มตและการเปล่ยี นการสรุปข)อมูล 11. ตัง้ ชือ่ Worksheet เปน Workshop2 หนา 22

WorkShop ที่ 3 • สราง PivotTable 1 มิติ • การคัดกรองดวย Label Filter, Value Filter โจทยF - ตอ) งการทราบรายได)การขายสนิ ค)า ของทุกจงั หวัดในภาคเหนือ และภาคใต) - จากน้ันทาํ การคัดกรองเฉพาะจังหวดั ทม่ี ี “ร” ในชอื่ และรายได)มากกว:า 1 ลา) นบาท - ฟลg ด<ท่ใี ช) คอื Region, Province, Net Sales 1. ในไฟล< Sale1.xlsx  ใน Worksheet ชือ่ YearData2 ใหค) ลกิ เซลใ< ดๆ ภายในตารางข)อมลู  เลือกเมนู Insert > PivotTable ดังภาพ ภาพที่ 30 การสรา) ง PivotTable 2. โปรแกรมจะแสดงหนา) ตา: ง Create PivotTable โดย  หัวข)อ Select a Table or Range : โปรแกรมจะเลือกข)อมูลทุกเซลล<ให)อัตโนมัติ เปน YearData2!$A$3:$M$161 หรือผ)ูใช)สามารถเลือกข)อมูลที่จะใช)สร)าง PivotTable โดยการ Drag บริเวณ ขอ) มลู ทต่ี )องการ ซ่ึงในทน่ี ี้ คือเซลล< YearData2!$A$3:$M$161  หั วข) อ Choose where you want the PivotTable report to be placed ให) เลื อ ก New Worksheet หมายถึง การสร)าง PivotTable ใน Worksheet ใหม: จากนั้นให)คลิกปม}ุ OK หนา 23

ภาพที่ 31 การเลือกพืน้ ที่ข)อมูลสาํ หรบั สร)าง PivotTable 3. โปรแกรมจะสร)าง Worksheet ใหม: ภาพที่ 32 Worksheet PivotTable ที่ถูกสรา) งใหม: 4. ให)เลือกฟgลด< ท่ีจะสร)างตาราง Pivot ดังน้ี  Drag ฟลg ดช< ่ือ Region วางในกรอบ Filters  Drag ฟลg ดช< อ่ื Province วางในกรอบ Rows  Drag ฟgลด<ช่อื Net Sales วางในกรอบ Values หนา 24

ภาพที่ 33 การเลือกฟลg ด< 5. โปรแกรมจะแสดงข)อมลู คอื แสดงชอ่ื จงั หวดั ตา: งๆ และรายได)ทุกจงั หวดั ทัว่ ประเทศ ภาพท่ี 34 ตารางแสดงรายได)ของบริษทั ในจงั หวดั ตา: งๆ ทั่วประเทศ หนา 25

6. ทาํ การกรองข)อมูล เพอ่ื ให)แสดงขอ) มลู รายไดเ) ฉพาะภาคเหนือและใต) จะปรากฏกลุ:มรายการคําสง่ั  ในส:วนของฟลg ด< Region ที่เซลล< B2 ให)คลิก สัญลกั ษณ<  จากน้นั เลอื ก  หนา) ข)อความ Select Multiple Items  ต:อมาคลิก  เฉพาะภาคเหนอื และใต) ดงั ภาพ ภาพท่ี 35 การกรองข)อมลู เฉพาะภาคใต)และผลลพั ธ< 7. ผลลัพธก< ารกรองขอ) มลู แสดงดังภาพ ภาพท่ี 36 ผลลัพธก< ารกรองข)อมลู เฉพาะจังหวัดสงขลาและสุราษฎร<ธานี หนา 26

8. เน่ืองจากมีเง่ือนไขการคัดกรองข)อมูล คือ (1) จังหวัดท่ีมี “ร” ในช่ือ และ (2) รายได)มากกว:า 1 ล)าน บาท ให)แก:ฟgลด<ชื่อ Province ดังน้ันจึงต)องกําหนดค:าใน PivotTable Options ให)สามารถกําหนดเง่ือนไข แกฟ: gลดใ< ดๆ ไดม) ากกวา: 1 เงือ่ นไขดงั น้ี  คลกิ เมาส<ขวาในข)อมลู ใดๆ ของตาราง PivotTable  เลือกคําสัง่ PivotTable Options ภาพที่ 37 การเรียกคาํ สงั่ PivotTable Options 9. ใน Tab ชอ่ื Totals & Filters ให)คลกิ  หน)าขอ) ความ Allow multiple filters per field ภาพที่ 38 การกําหนดใหส) ามารถใช)เง่ือนไขแก:ฟgลด<ใดๆ ได)มากกวา: 1 เงื่อนไข หนา 27

10. ทําการคัดกรองข)อมูลเฉพาะจังหวัดท่ีมี “ร” ในชื่อ โดยคลิกสัญลักษณ< ในส:วนของ Row Labels จากนน้ั เลอื กคาํ สัง่ Label Filters > Contains… ดังภาพ ภาพที่ 39 การคดั กรองข)อมูลโดยใช)คําสงั่ Label Filters 11. จากนน้ั พมิ พ< “ร” ลงในชอ: งว:างดงั ภาพ ภาพที่ 40 การกําหนดค:าข)อมูลเฉพาะจังหวัดท่ีมี “ร” ในชอื่ หนา 28

12. ผลลัพธ<การคัดกรองข)อมูลเฉพาะจังหวัดที่มี “ร” มีจํานวน 5 จังหวัด ได)แก: ชุมพร ตรัง นครศรีธรรมราช แพร: และสุราษฎร<ธานี ภาพที่ 41 ผลลพั ธ<การคัดกรองข)อมูลเฉพาะจงั หวัดที่มี “ร” 13. ทําการคัดกรองข)อมูลเฉพาะจังหวัดท่ีมีรายได)มากกว:า 1 ล)านบาท โดยคลิกสัญลักษณ< ในส:วน ของ Row Labelsจากนัน้ เลือกคําส่ัง Value Filters > Greater Than… ดังภาพ ภาพที่ 42 การคัดกรองข)อมลู โดยใชค) าํ สัง่ Value Filters หนา 29

14. จากนนั้ พิมพ< 1000000 ลงในชอ: งวา: งดงั ภาพ ภาพที่ 43 การกาํ หนดค:าข)อมูลเฉพาะจังหวัดทมี่ ีรายไดม) ากกวา: 1 ลา) นบาท 15. ผลลพั ธ<การคัดกรอง จะปรากฏรายช่อื 2 จังหวัดท่ีมียอดรวมรายไดม) ากกวา: 1 ล)านบาท ไดแ) ก: จังหวัด แพร: และจงั หวัดสุราษฎร<ธานี ดงั ภาพ ภาพที่ 44 ผลลพั ธ<การคดั กรองขอ) มูลเฉพาะจังหวัดที่มีรายไดม) ากกว:า 1 ล)านบาท 16. ตงั้ ชอ่ื Worksheet เปน Workshop3 หนา 30

WorkShop ที่ 4 • สราง PivotTable 2 มิติ • การคดั กรองดวย Date Filter โจทยF - ต)องการทราบข)อมูลรายได)การขายสนิ ค)าของแตล: ะจังหวดั ของเดอื นมกราคม - ฟลg ดท< ี่ใช) คอื Province, Sale_Date, Net Sales 1. ในไฟล< Sale1.xlsx  ทําการปรับปรุงข)อมูลในคอลัมน< Sale_Date เปนประเภทข)อมูล Date และเปนภาษาอังกฤษ เพ่ือให)การแสดงข)อมูลใน Timeline สามารถแสดงเรียงลําดบั ตามระยะเวลา อดีต-ปcจจุบนั ไดถ) กู ต)อง ภาพท่ี 45 การเรียกคาํ สง่ั Format Cells หนา 31

ภาพที่ 46 การเปลี่ยนประเภทขอ) มลู Date เปนภาษาองั กฤษ  ใน Worksheet ช่ือ YearData2 ให)คลิกเซล<ใดๆ ภายในตารางข)อมูล แล)วเลือกเมนู Insert > PivotTable ดังภาพ ภาพท่ี 47 การสร)าง PivotTable หนา 32

1. โปรแกรมจะแสดงหน)าต:าง Create PivotTable โดย  หัวข)อ Select a Table or Range : โปรแกรมจะเลือกข)อมูลทุกเซลล<ให)อัตโนมัติ เปน YearData2!$A$3:$M$161 หรือผู)ใช)สามารถเลือกข)อมูลท่ีจะใช)สร)าง PivotTable โดยการ Drag บริเวณ ข)อมูลท่ีต)องการ ซึง่ ในท่ีน้ี คือเซลล< YearData2!$A$3:$M$161  หัวข)อ Choose where you want the PivotTable report to be placed : ให)เลือก New Worksheet หมายถงึ การสร)าง PivotTable ใน Worksheet ใหม: จากนัน้ ให)คลกิ ป}ุม OK ภาพท่ี 48 การเลือกพนื้ ที่ข)อมูลสาํ หรบั สร)าง PivotTable 2. โปรแกรมจะสรา) ง Worksheet ใหม: ภาพท่ี 49 Worksheet PivotTable ทถ่ี ูกสร)างใหม: หนา 33

3. ให)เลือกฟลg ด< ท่ีจะสรา) งตาราง Pivot โดย  Drag ฟลg ด<ชอื่ Sale_Date วางในกรอบ Columns  Drag ฟลg ด<ช่ือ Province วางในกรอบ Rows  Drag ฟลg ดช< อ่ื Net Sales วางในกรอบ Values ภาพที่ 50 การเลือกฟgลด< 4. โปรแกรมจะแสดงข)อมูล ไดแ) ก: ช่อื จงั หวัด วนั ทข่ี าย และรายได) ภาพที่ 51 ข)อมลู รายไดข) องแต:ละจังหวดั ในปV 2011-2015 หนา 34

5. ทําการคัดกรองให)แสดงข)อมูลเฉพาะเดือนมกราคม โดย  คลกิ ป}มุ ใน Sum of Net_sales Column  เลือกคําสั่ง Date Filters > All Dates in Period > January ภาพท่ี 52 การคดั กรองโดยใช)คาํ สงั่ Date Filters หนา 35

6. ผลลัพธก< ารคัดกรองเฉพาะเดือนมกราคม ภาพที่ 53 ผลลพั ธ<การคัดกรองโดยคาํ ส่งั Date Filters 7. ต้งั ช่อื Worksheet เปน Workshop4 หนา 36

WorkShop ที่ 5 • สราง PivotTable 1 มิติ • การคัดกรองขอมลู ดวย Slicer โจทยF - ตอ) งการทราบรายได)การขายสนิ ค)าของทุกอําเภอทางภาคใต) ในจังหวัดสงขลาและสุราษฎรธ< านี เทา: น้ัน - ฟgลด<ทใี่ ช) คือ Region, Province, District, Net Sales 1. ในไฟล< Sale1.xlsx  ใน Worksheet ชอื่ YearData2 ให)คลิกเซล<ใดๆ ภายในตารางขอ) มลู  เลือกเมนู Insert > PivotTable ดังภาพ ภาพที่ 54 การสรา) ง PivotTable 2. โปรแกรมจะแสดงหนา) ตา: ง Create PivotTable โดย  หัวข)อ Select a Table or Range : โปรแกรมจะเลือกข)อมูลทุกเซลล<ให)อัตโนมัติ เปน YearData2!$A$3:$M$161 หรือผู)ใช)สามารถเลือกข)อมูลที่จะใช)สร)าง PivotTable โดยการ Drag บริเวณ ข)อมูลท่ีต)องการ ซึ่งในท่นี ้ี คอื เซลล< YearData2!$A$3:$M$161  หัวข)อ Choose where you want the PivotTable report to be placed : ให)เลือก New Worksheet หมายถงึ การสรา) ง PivotTable ใน Worksheet ใหม: จากนัน้ ใหค) ลกิ ป}ุม OK หนา 37

ภาพที่ 55 การเลือกพนื้ ท่ีข)อมูลสําหรับสร)าง PivotTable 3. โปรแกรมจะสรา) ง Worksheet ใหม: ภาพท่ี 56 Worksheet PivotTable ท่ถี ูกสร)างใหม: 4. ให)เลือกฟลg ด< ที่จะสร)างตาราง Pivot ดังน้ี  Drag ฟgลด<ชือ่ Region วางในกรอบ Filters  Drag ฟgลดช< ่อื Province และ District วางในกรอบ Rows  Drag ฟลg ด<ชือ่ Net Sales วางในกรอบ Values หนา 38

ภาพที่ 57 การเลือกฟgลด< 5. โปรแกรมจะแสดงข)อมูล คือ แสดงช่ือจังหวัด ช่ืออําเภอในจังหวัดต:างๆ และรายได)การขายสินค)า ของแต:ละอาํ เภอ ของทั้งประเทศ ภาพที่ 58 ตารางแสดงรายได)ของบริษัทสาขาอาํ เภอ ในจังหวดั ตา: งๆ ทว่ั ประเทศ หนา 39

6. แทรก Slicer ท่สี รา) งจาก Region และ Province เขา) มาในโปรแกรม ดังนี้  คลกิ เซลลใ< ดๆ ในตารางขอ) มลู  คลิกเมนู ANALYZE ในเคร่ืองมือ PRIVOTTABLE TOOLS  คลกิ ปุ}ม Insert Slicer  คลกิ  หนา) ฟลg ด< Region และฟลg ด< Province ภาพท่ี 59 การเรียกเครื่องมือ Slicer สําหรับกรองข)อมูล หนา 40

7. โปรแกรมแสดงเคร่ืองมอื Slicer ของ Region และ Province ดังภาพ ภาพท่ี 60 เคร่ืองมือ Slicer สําหรับกรองขอ) มลู 8. ใน Slicer ของ Regiong ให)คลิกเลือก “ใต)” และ Slicer ของ Province ให)คลิกเลือก “สงขลา” และ “สรุ าษฎร<ธานี” โดยกดปม}ุ Ctrl ร:วมด)วย ดงั ภาพ ภาพที่ 61 การกรองข)อมลู ด)วย Slicer หนา 41

9. ผลลัพธก< ารกรองข)อมูลแสดงดงั ภาพ ภาพท่ี 62 ผลลพั ธก< ารกรองข)อมลู ด)วย Slicer 10. หากต)องการเลอื กข)อมูลภาคอ่ืนๆ หรอื จังหวดั อ่นื ๆ ให)คลิกป}ุม Clear Filter ภาพที่ 63 การคลกิ ป}ุม Clear Filter ใน Slicer 11. ตัง้ ชอ่ื Worksheet เปน Workshop5 หนา 42

WorkShop ที่ 6 • สราง PivotTable 2 มิติ • การคดั กรองดวย Timeline โจทยF - ตอ) งการทราบขอ) มลู รายได)ของแตล: ะจังหวดั ต้ังแต:เดอื นพฤศจิกายน 2013 – เมษายน 2014 - ฟลg ดท< ใี่ ช) คอื Province, Sale_Date, Net Sales 2. ในไฟล< Sale1.xlsx  ทําการปรับปรุงข)อมูลในคอลัมน< Sale_Date เปนประเภทข)อมูล Date และเปนภาษาอังกฤษ เพื่อให)การแสดงข)อมลู ใน Timeline สามารถแสดงเรียงลาํ ดับตามระยะเวลา อดตี -ปจc จบุ ัน ไดถ) กู ตอ) ง ภาพท่ี 64 การเรยี กคาํ ส่งั Format Cells หนา 43

ภาพที่ 65 การเปลี่ยนประเภทขอ) มลู Date เปนภาษาองั กฤษ  ใน Worksheet ช่ือ YearData2 ให)คลิกเซล<ใดๆ ภายในตารางข)อมูล แล)วเลือกเมนู Insert > PivotTable ดังภาพ ภาพท่ี 66 การสร)าง PivotTable หนา 44

8. โปรแกรมจะแสดงหน)าต:าง Create PivotTable โดย  หัวข)อ Select a Table or Range : โปรแกรมจะเลือกข)อมูลทุกเซลล<ให)อัตโนมัติ เปน YearData2!$A$3:$M$161 หรือผู)ใช)สามารถเลือกข)อมูลท่ีจะใช)สร)าง PivotTable โดยการ Drag บริเวณ ข)อมูลท่ีต)องการ ซึง่ ในท่ีน้ี คือเซลล< YearData2!$A$3:$M$161  หัวข)อ Choose where you want the PivotTable report to be placed : ให)เลือก New Worksheet หมายถงึ การสร)าง PivotTable ใน Worksheet ใหม: จากนัน้ ให)คลกิ ป}ุม OK ภาพท่ี 67 การเลือกพนื้ ที่ข)อมูลสาํ หรบั สร)าง PivotTable 9. โปรแกรมจะสรา) ง Worksheet ใหม: ภาพท่ี 68 Worksheet PivotTable ทถ่ี ูกสร)างใหม: หนา 45

10. ให)เลือกฟลg ด< ท่ีจะสรา) งตาราง Pivot โดย  Drag ฟgลด<ชอ่ื Sale_Date วางในกรอบ Columns  Drag ฟลg ด<ช่ือ Province วางในกรอบ Rows  Drag ฟgลดช< ือ่ Net Sales วางในกรอบ Values ภาพท่ี 69 การเลือกฟgลด< 11. โปรแกรมจะแสดงข)อมลู ไดแ) ก: ช่ือจังหวดั วนั ทข่ี าย และรายได) ภาพท่ี 70 ข)อมลู รายไดข) องแต:ละจังหวดั ในปV 2011-2015 หนา 46

12. เรียกเครอื่ งมือ Timeline ขน้ึ มาแสดง โดย  คลกิ เซลล<ในพื้นทข่ี อ) มูล  คลิกเมนู ANALYZE  คลกิ เลือกคาํ สั่ง Insert Timeline ดงั ภาพ ภาพที่ 71 การเรียกเครื่องมือ Timeline 13. โปรแกรมจะแสดงหน)าต:าง Insert Timeline ให)ผู)ใช)เลือกการสร)างTimeline จากฟgลด<ท่ีมีประเภท ขอ) มลู เปน Date ซ่งึ ใน Worksheet นี้ มีเพยี งฟgลดเ< ดยี วคือ Sale_Date ดังนั้นให)คลิกเลอื กฟลg ดด< ังกล:าว ภาพที่ 72 การเลือกฟgลด<สาํ หรับสรา) ง Timeline หนา 47

14. โปรแกรมจะแสดงเครื่องมือ Timeline จากนั้นให)กําหนดการแสดงข)อมูลตั้งแต:เดือนพฤศจิกายน 2013 – เมษายน 2014 ดังภาพ ภาพท่ี 73 การกําหนด Timeline ต้ังแต:เดือนพฤศจิกายน 2013 – เมษายน 2014 15. ผลลพั ธ< ภาพที่ 74 ผลลพั ธก< ารกําหนด Timeline 16. นอกจากน้สี ามารถกาํ หนด Timeline ใหแ) สดงผลแบบอื่นๆได)แก:  แบบรายปV ภาพที่ 75 การกําหนด Timeline ให)แสดงผลแบบรายปV หนา 48