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

คงปฏิเสธไม่ได้ว่า 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 โผล่ออกมาให้ใช้ครับ

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

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

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

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

โหลดผ่าน GitHub

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

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

การเอาข้อมูลเข้าสู่โหมด Data Model นั้นทำได้ 3 วิธี นั่นคือ

1. เอาเข้าด้วย Pivot Table (ผ่านการติ๊กเลือก Option ล่างสุดตอนสร้าง Pivot)
2. นำเข้าผ่านเครื่องมือ Power Pivot ในคำสั่ง Add to Data Model
3. นำเข้าผ่าน Power Query โดย Load To.. Connection Only แล้วติ๊ก Add to Data Model

หากใช้ข้อมูลเพียงตารางเดียวจากชีท TXData การเอาเข้าผ่าน Pivot Table ไปเลยจะง่ายที่สุดเลย

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

วิธีการเรียกใช้ Measure ให้คลิ๊กขวาที่ชื่อตารางใน Pivot Field List แล้วกด +Add Measure… (ถ้าคลิ๊กขวาแล้วไม่มีก็ไปเลือกใน Ribbon Power Pivot ตามวิธีถัดไป)

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

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

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

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

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

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

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

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

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

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

เรียนรู้ SUMX

SUMX(<table>, <expression>)

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

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

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

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

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

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

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

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

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

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

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

=[TotalSales] / [จำนวนลูกค้าแบบไม่ซ้ำ]

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

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

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

ตอนต่อไป

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

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

• วิธีกำหนดให้ Pivot Table แสดงเฉพาะแถว/คอลัมน์ที่ต้องการ
• การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน
• การใช้ Excel Power Pivot ตอนที่ 2 : ทำผลสรุป Value ให้เป็นข้อความด้วย DAX
• การใช้ Excel Power Pivot ตอนที่ 3 : ลาก่อน VLOOKUP สวัสดี Data Model
• การใช้ Excel Power Pivot ตอนที่ 4 : Data Model ทำงานอย่างไร
• การใช้ Excel Power Pivot ตอนที่ 5 : การทำรายงาน Actual vs Target
• Inventory Management ทำรายงานสินค้าคงคลังด้วย DAX และ Data Model ใน Excel
• วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด
• หลากวิธีเอาข้อมูลในกลุ่มเดียวกันไปรวมเป็นข้อความเดียวกัน