เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Power PivotDAX Formula

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน

power pivot basic

คงปฏิเสธไม่ได้ว่า Pivot Table นั้นเป็นเครื่องมือสรุปข้อมูลที่ใช้ง่ายที่สุดของ Excel (จริงๆ ผมว่าถ้าเทียบกับโปรแกรมอื่น Pivot ก็ยังง่ายและเจ๋งกว่าอยู่ดี) ซึ่งการใช้ Pivot Table แบบทั่วๆ ไปก็สามารถตอบโจทย์การทำงานได้มหาศาลแล้ว อย่างไรก็ตาม ก็ยังมีงานบางอย่าง ที่ Pivot Table ธรรมดายังตอบโจทย์ไม่ได้ แต่ต้องใช้ Power Pivot แทน เช่น

  1. การนับข้อมูลแบบไม่ซ้ำ เช่น นับจำนวนลูกค้า, จำนวนวันที่ที่ขายของ, จำนวน sales, จำนวนประเภทสินค้า
  2. การทำ Calculated Field ที่ไม่ใช่การ SUM
  3. การแสดงข้อมูลสรุปในช่อง Value ออกมาเป็น Text เช่น แสดงชื่อลูกค้าคนล่าสุด แสดงรายการสินค้าออกมาคั่นด้วย comma แสดงสินค้าขายดี Top3
  4. การวิเคราะห์ข้อมูลเทียบกับช่วงเวลาก่อนหน้า
  5. การวิเคราะห์ข้อมูลจากหลายตาราง เช่น การเอาค่าจากอีกตารางมาโดยไม่ต้อง VLOOKUP, การคำนวณ Actual vs Target

นี่คือตัวอย่างของสิ่งที่Pivot ธรรมดาๆ ทำไม่ได้…

แต่ว่าไม่ต้องเสียใจไป เพราะจริงๆ แล้วถ้าเราใช้ Pivot Table อีกโหมดนึงที่เรียกว่าโหมด Data Model ซึ่งจะทำให้ Pivot Table ธรรมดากลายเป็น Power Pivot ซึ่งจะมีความสามารถเพิ่มขึ้นมหาศาลใกล้เคียงกับความสามารถของ Power BI เลยล่ะ

อย่างไรก็ตาม Excel ที่จะใช้ Data Model และ Power Pivot ได้จะต้องเป็น Excel 2010 ขึ้นไปเท่านั้นนะครับ เก่ากว่านี้หมดสิทธิ์ ซึ่งถ้าเป็น 2013 ขึ้นไปจะมีโหมด Data Model ให้ใช้ในตัว แต่ถ้าเป็น Excel 2010 จำเป็นต้องโหลด Add-in Power Pivot มาใช้ซะก่อน

ในบทความตอนนี้ผมจะทำความเจ๋ง 2 เรื่องข้างบนให้ดูก่อน เพราะง่ายมากและใช้งานจริงได้เยอะ ส่วนเรื่องที่เหลือรอต่อตอนถัดไปนะ

เตรียมความพร้อม Data Model / Power Pivot

คนที่ใช้ Excel 2010 ให้ไปโหลด Add-in Power Pivot นี่แล้ว Install ซะ มันจะมี Ribbon Power Pivot โผล่ออกมาให้ใช้ครับ

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 1

คนที่ Version ใหม่กว่า 2010 ก็ให้ไป Enable Power Pivot add-in ซะก่อน โดยไปที่ File -> Options -> Add-in -> Com Add-ins -> Go

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 2

จากนั้นก็เลือก Power Pivot ซะ

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 3

หลังจาก ok ไปจนครบ ก็จะมี Ribbon Power Pivot มาให้ใช้แล้ว

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 4

โหลดไฟล์ประกอบได้ที่นี่

โหลดผ่าน GitHub

ก่อนอื่นเรามาดูวิธีเรียกใช้ Pivot Table โหมด Data Model กันครับ

วิธีเรียกใช้ Pivot Table โหมด Data Model

การเอาข้อมูลเข้าสู่โหมด Data Model นั้นทำได้ 2 วิธี นั่นคือ เอาเข้าด้วย Pivot Table (ผ่านการเลือก Option ที่เดี๋ยวผมจะแสดงให้ดู) หรือไม่ก็นำเข้าผ่านเครื่องมือ Power Pivot ในคำสั่ง Add to Data Model

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 5

การทำตามข้อ 1-3 นั้นใช้ข้อมูลเพียงตารางเดียวจากชีท TXData ซึ่งการจะเอาข้อมูลจากแค่ตารางเดียวเข้าสู่ Data Model นั้นเอาเข้าผ่าน Pivot Table ไปเลยจะง่ายกว่า

Tips : ก่อนจะเอาข้อมูลเข้าไปวิเคราะห์ใน Pivot Table เพื่อให้ในอนาคตชีวิตสบายขึ้น ควรแปลงข้อมูลนั้นให้เป็น Table ก่อน ด้วยการกด Insert -> Table เพื่อให้ตัว PivotTable สามารถอ้างอิงข้อมูลจาก Data Source ที่ขยายอาณาเขตตัวเองตามข้อมูลใหม่ที่ใส่เพิ่มได้โดยอัตโนมัติ ซึ่งผมตั้งชื่อ Table นี้ว่า TXData ละกัน (แต่ขั้นตอนนี้ไม่ได้จำเป็นกับการทำ Data Model นะ)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 6

การทำให้ข้อมูลเป็น Table จะทำให้ง่ายตอนเพิ่มข้อมูล เช่น ผมเพิ่มเดือนกับปีเข้าไป พื้นที่ตารางมันก็จะขยายมาคลุมให้เอง (แถม Copy สูตรลงไปให้เองจนครบทุกบรรทัดด้วย)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 7

พอแปลงเป็น Table แล้วให้เลือกข้อมูลที่จะเอาเข้า Pivot Table แล้วกด Insert -> Pivot Table ตามปกติ แต่ให้ติ๊ก Add this Data to the Data Model ด้วย (สำคัญมากกกกกก)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 8

พอกด ok เราจะได้ PivotTable โหมดพิเศษที่เป็น Mode Data Model ขึ้นมาแล้ว ซึ่งหน้าตาแทบจะเหมือน Pivot Table ธรรมดาๆ เลย แต่มีความต่างตรงที่ผมตีกรอบให้ดู

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 9

เอาล่ะเรามาเริ่มทำอะไรเจ๋งๆ ที่ Pivot Table ปกติทำไม่ได้กัน

ความเจ๋ง 1 : นับข้อมูลแบบไม่ซ้ำ

ปกติแล้วเวลาเราลากข้อมูลเข้ามา Count ใน Pivot มันก็จะรับแค่ว่าคอลัมน์ที่เราเลือกมีข้อมูลอยู่กี่ตัว ไม่ได้สนว่าซ้ำรึเปล่า (ซึ่งส่วนใหญ่จะซ้ำกระจาย) เช่น ลากลูกค้าลงมาดูคู่กับสินค้าและผู้ขาย เพื่อดูว่ามีลูกค้ากี่คนที่ซื้อสินค้านั้นๆ โดยผู้ขายคนนั้นๆ (ผมกดเปลี่ยน Report Layout เป็น Tabular Form จะได้เห็นชื่อ Field ชัดๆ)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 10

จะเห็นว่าตรงช่องที่ผมทาสีเหลือง มัน Count ลูกค้าได้ 27 แบบนี้ หลายคนอาจตีความว่า มีลูกค้า 27 คนที่ซื้อของเล่นที่ขายโดย sales ง รึเปล่า?

ถ้าเราดับเบิ้ลคลิ๊กที่เลข 27 จะเห็นชัดเลย ว่าลูกค้าที่มันเอามานับนั้นซ้ำกระจาย (แค่เบอร์ 14 ก็ซ้ำไป 3 รอบแล้ว)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 11

และถ้าเราอยากจะนับแบบไม่ซ้ำจะทำยังไงล่ะ?

คำตอบง่ายมาก แค่กลับไปที่ Pivot แล้วเปลี่ยนวิธีสรุปข้อมูลจาก Count ธรรมดาๆ เป็น Distinct Count ก็จบเลย (แต่มันอยู่ใน More Options… นะ)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 12
การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 13

ซึ่งพอกด ok เราก็จะได้จำนวนลูกค้าแบบไม่ซ้ำกันแล้ว ซึ่ง PivotTable โหมดปกติจะทำอะไรแบบนี้ไม่ได้เลยนะ

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 14

ซึ่งถ้าเราอยากจะนับคอลัมน์ไหนแบบไม่ซ้ำ ก็ไปปรับที่ตัวนั้นๆ ได้เลย เช่นจะนับว่ามีการขายของนั้นๆ ทั้งหมดกี่วัน ก็เอาวันที่มานับแบบ Distinct Count ได้ หรือจะดูว่ามีการขายครบทุกเดือนมั้ย ก็เอาเดือนมา Distinct Count ได้ ซึ่งเราสามารถใช้ร่วมกับการ Filter หรือ Row/Column ต่างๆ ได้อย่างอิสระเลย

เช่นในปี 2020 หนังสือมีการขายแค่ 11 เดือน ในขณะที่ตัวอื่นขายครบ 12 เดือน

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 15

ความเจ๋ง 2 : การทำ Calculated Field ที่ไม่ใช่การ SUM ด้วย Measure

ปกติแล้วเวลาเราใช้ Calculated Field ใน Pivot Table นั้น สูตรที่เราเขียน มันจะใช้การสรุปผลด้วยการ Sum เท่านั้น ไม่สามารถเปลี่ยนเป็น Count, Max, Min อะไรได้เลย

แต่ถ้าเราใช้ Mode Data Model แล้ว Calculated Field จะง่อยกว่าเดิม! เพราะมันใช้ไม่ได้เลยเนื่องจากหลายเป็นสีเทาไปแล้ว…

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 16

แต่ไม่ต้องเสียใจไป ที่มันเป็นสีเทาเพราะมันมีตัวที่เจ๋งกว่าให้ใช้ นั่นก็คือ Measure นั่นเอง (ซึ่งคือตัวเดียวกับ Power BI)

วิธีการเรียกใช้ Measure ให้คลิ๊กขวาที่ชื่อตารางใน Pivot Field List แล้วกด +Add Measure…

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 17

นอกจากการกด +Add Measure เราจะไปกดใน Ribbon Power Pivot ก็ได้นะครับ (เมนูจะเจ๋งกว่าด้วย เพราะมีสีสวยงามตอนเขียนสูตร)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 18

ในที่นี้ผมจะกดสร้าง Measure ผ่านเมนูของ Power Pivot ละกันนะครับ เพราะมีตัวช่วยเยอะกว่า

พอกดสร้าง Measure ปุ๊ป จะเห็นช่องให้ใส่สูตร ถ้าเรากดปุ่ม fx มันจะ List ฟังก์ชันที่ใช้ได้ขึ้นมาเพียบเลย ซึ่งไม่ได้มีแค่ SUM แล้วเนอะ

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 19

ฟังก์ชันที่ใช้ได้นี้เป็นสูตรเฉพาะที่เรียกว่า DAX (Data Analysis eXpression) ซึ่งเป็นภาษาที่ใช้ใน PowerPivot กับ Power BI และพวก Analysis Service ต่างๆ ซึ่งมีความคล้ายกับฟังก์ชันใน Excel ของเรามากเลย หลายๆ ฟังก์ชันที่เรารู้จักใน Excel ก็สามารถนำมาใช้ในนี้ได้ และก็มีหลายๆ ฟังก์ชันถูกใส่เข้ามามากกว่าใน Excel ปกติ เพื่อใช้ในการวิเคราะห์ข้อมูลโดยเฉพาะ

ยกตัวอย่างเช่น การนับลูกค้าแบบไม่ซ้ำ นอกจากใช้คำสั่ง Distinct Count ใน Summarize Value By… เราก็ยังสามารถใช้ฟังก์ชัน DISTINCTCOUNT ใน DAX มาช่วยได้

วิธีเขียนสูตรคือ ให้พิมพ์ชื่อฟังก์ชันแล้วกด Tab เลือกเอา มันจะมีตัวช่วยขึ้นมาว่าจะ DISTINCTCOUNT คอลัมน์ไหน

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 20

เราก็เลือกว่า DISTINCTCOUNT(TXData[ลูกค้า] แล้วอย่าลืมพิมพ์วงเล็บปิดด้วย สรุปได้สูตรนี้

=DISTINCTCOUNT(TXData[ลูกค้า])

พอกด ok มันก็จะสร้าง Measure ขึ้นมาใหม่ และ add เข้า Pivot ให้เราเลย (ถ้ามันยังไม่ add ให้ก็ลากมาใส่เองได้)

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 21

นอกจากการ DistinctCount แล้ว มันยังทำเรื่องต่างๆ ได้อีกมากมาย เช่น เราจะสร้างยอดขายรวมขึ้นมาโดยไม่ต้องมีคอลัมน์ยอดขายในตารางจริง แต่จะใช้คอลัมน์จำนวนชิ้น * ราคาต่อชิ้น แล้ว SUM ด้วย SUMX

เรียนรู้ SUMX

SUMX(<table>, <expression>)

SUMX จะใช้สูตร <expression> ที่ระบุลงไปในแต่ละแถวของตาราง <table> (เรียกว่า Iterate) แล้วสุดท้ายค่อย SUM ซึ่งจะคล้ายๆกับสูตร SUMPRODUCT ใน Excel แต่ยืดหยุ่นกว่ามากๆ

เราจะให้ <table> เป็นตาราง TXData ของเรานี่แหละ ส่วน <expression> เราจะเอา จำนวนชิ้น * ราคาต่อชิ้น

ดังนั้นสูตรออกมาจะเป็นดังนี้

=SUMX(TXData,TXData[ราคาต่อชิ้น]*TXData[จำนวนชิ้น])

ซึ่งเราสามารถเลือก Number Format ให้ Measure นั้นๆ ได้เลยด้วย

Power Pivot

แค่นี้เราก็จะได้ยอดขายรวมแล้ว

การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 22

หายอดขายเฉลี่ยต่อลูกค้า

ยกตัวอย่างเช่น หากเราต้องการจะแสดงยอดขายเฉลี่ยต่อลูกค้า 1 คน เราก็สามารถเขียนสูตรโดยเอาเลขสรุป 2 ค่ามาหารกัน

  • ให้ตัวเศษ = ยอดขายรวม
  • ตัวส่วน = จำนวนลูกค้า(แบบไม่ซ้ำ)

ซึ่งความเจ๋งของ Measure คือ มันอ้างอิง Measure ที่มีอยู่แล้วได้ ด้วยการใส่ [ชื่อMeasure]

ดังนั้นเราจะเขียนสูตรแบบนี้ได้เลย

=[TotalSales] / [จำนวนลูกค้าแบบไม่ซ้ำ]
การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 23
การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 24

และนี่ก็คือตัวอย่างของการเริ่มหัดใช้ Power Pivot แบบง่ายๆ เดี๋ยวตอนต่อไปจะเริ่มซับซ้อนขึ้นแล้ว

ถ้าอยากปูพื้นฐานก่อน…

ถ้าใครอ่านบทความนี้แล้วรู้สึกว่าอยากจะมีความรู้ PivotTable ให้ดีกว่านี้ เพราะยังไม่เข้าใจบางจุด ก็สามารถไปศึกษาคอร์สออนไลน์ของผมได้นะครับ ซึ่งดูตอนไหนก็ได้ กี่รอบก็ได้ ไม่มีหมดอายุ แถมยังอัปเดทเนื้อหาให้เรื่อยๆ อีก (ขอโฆษณาซะหน่อย 555)

มี 2 คอร์สที่มีเนื้อหา PivotTable คือ

ตอนต่อไป

จะมาดูวิธีทำให้ช่อง Value ของ Pivot สามารถแสดงข้อความได้กันครับ

สารบัญซีรีส์ Power Pivot

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 2K
  •  
  •  
  •  
  •  
  • 2K
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply