การสรุปข้อมูลโดยใช้เครื่องมือ Statistics
คำสั่ง Statistics ต่างๆ เช่น Sum, Minimum, Count Rows อะไรพวกนี้มันทำงานได้ 2 แบบ คือ
- หากเลือกคอลัมน์เดียวแล้วกดแบบ Transform จะให้ผลลัพธ์สรุปข้อมูลจากคอลัมน์ที่กำหนดออกมาเป็นตัวเลขค่าเดียวเลย
- หากเลือกหลายคอลัมน์ก่อนแล้วกดแบบ Add Column ก็จะเป็นการคำนวณสำหรับข้อมูลในแต่ละแถวแยกกันออกมาเป็นคอลัมน์ใหม่
โหลดไฟล์ตัวอย่างได้ที่นี่ => คลิ๊ก
ตัวอย่าง กรณีที่ใช้ Statistics → Minimum
สมมติผมมีข้อมูลแบบนี้
หากเลือกคอลัมน์ราคาในห้าง → Transform → Statistics → Minimum จะได้ค่าที่น้อยสุดในคอลัมน์นั้นออกมาเป็นค่าเดียว
หากเลือกคอลัมน์ราคาในตลาด และราคาในห้างพร้อมกัน → Add Column → Statistics → Minimum มันจะหาว่าค่าที่น้อยสุดในแต่และแถวเป็นค่าอะไร?
ซึ่งทั้ง 2 วิธีมีประโยชน์ทั้งคู่ และใช้ในกรณีต่างกัน สำหรับวิธีออกมาเป็นค่าเดียว เดี๋ยวเราจะได้ใช้อีกในบทหลังๆ ซึ่งเอาค่าค่าเดียวนั้นไปใช้งานต่อที่อื่นได้อีกครับ
การคำนวณโดยใช้เครื่องมือกลุ่ม Standard
การคำนวณแบบ Standard ก็คือการคำนวณพื้นฐาน ไม่ว่าจะเป็นการบวก ลบ คูณ หาร ยกกำลัง เป็นต้น
ซึ่งมีวิธีการใช้หลากหลายรูปแบบ เช่น
- เลือกคอลัมน์เดียว หรือ เลือกหลายคอลัมน์ (ถ้าเลือกหลายคอลัมน์จะ Transform ไม่ได้)
- ใช้ Transform หรือ Add Column
สมมติผมมีข้อมูลแบบนี้
ผมคำนวณยอดขายได้โดยการ เลือกคอลัมน์ ราคาต่อหน่วย และ จำนวนชิ้นที่ขายได้ พร้อมกัน 2 คอลัมน์ แล้วกด Add Column → Standard → Multiplication
จะได้ดังรูป
Tips : หากเป็นการลบหรือหาร ต้องระวังลำดับในการคลิ๊กสองคอลัมน์ที่จำคำนวณด้วย เพราะลำดับจะให้ผลต่างกันว่าจะเอาอะไรมาเป็นตัวตั้งต้น
หากต้องการเปลี่ยนชื่อหัวตาราง ผมสามารถดับเบิ้ลคลิ๊กที่หัวตารางเพื่อเปลี่ยนชื่อจาก Multiplication เป็น ยอดขาย ได้เลย (หรือจะกดปุ่ม F2 เพื่อเปลี่ยนชื่อก็ได้)
จากนั้นผมอยากคำนวณว่าถ้าผมต้องจ่าย Commission 10% ของยอดขาย จะต้องเสีย Commission เท่าไหร่ ในที่นี้ผมเลือกยอดขายช่องเดียว แล้ว Add Column→ Standard → Multiplication แล้วกรอก 10%
แล้วจะได้ผลลัพธ์ดังรูป
คราวนี้ผมจะแก้ชื่อคอลัมน์จากใน Formula Bar เลย จะได้ไม่ต้องมี Step เพิ่ม
= Table.AddColumn(#”Renamed Columns”, “Multiplication”, each [ยอดขาย] * 0.1, type
= Table.AddColumn(#”Renamed Columns”, “Commission”, each [ยอดขาย] * 0.1, type number)
ซึ่งวิธีแก้ชื่อคอลัมน์ที่ Formula Bar หรือ ที่ M Code ไปเลยจะดีกว่าครับ เพราะโดยหลักการแล้วยิ่งขั้นตอนน้อย ยิ่งคำนวณเร็วกว่า และการเพิ่ม Step โดยไม่จำเป็นจำทำให้อ่านแล้วงงด้วย
สมมติว่า Commission ที่จะจ่าย ต้องหักค่าดำเนินการพิเศษ 50 บาทเสมอ ดังนั้นผมจะเลือกที่คอลัมน์ Commission → Transform → Standard → Substract แล้วใส่เลข 50 ลงไป
จะเห็นว่าผลลัพธ์ที่ได้ มันเปลี่ยนที่คอลัมน์เดิมเลย โดยไม่มีการเพิ่มคอลัมน์ใหม่ครับ
ใน Power Query เราสามารถลบข้อมูล Step ต้นทางที่ไม่ใช้แล้วได้
เมื่อเราได้ผลลัพธ์ยอดขายกับ Commission แล้ว เราสามารถลบคอลัมน์ ราคาต่อหน่วย และ จำนวนชิ้น ได้เลย (หากไม่อยากได้แล้ว) โดยที่ผลลัพธ์ไม่พังด้วยนะครับ
ซึ่งเรื่องแบบนี้เราทำใน Excel ไม่ได้เด็ดขาด เพราะแหล่งที่มาของข้อมูลที่ใช้ในสูตรมันหายไป แต่ว่าใน Power Query ทำได้ครับ
ระวังค่า null ตอนคำนวณตัวเลข
เวลามีค่าที่เป็น null เราจะไม่สามารถคำนวณด้วยเครื่องมือกลุ่ม Standard ได้ครับ ซึ่งมีวิธีแก้ได้หลายแนวทาง สมมติผมมีตารางบันทึกเงินเข้าออกไว้ดังนี้
หากเราจะคำนวณ Net Cashflow (เงินเข้า – เงินออก) แต่ละวันจะทำยังไง? มาดูกันครับ
ก่อนอื่น หากลองเอาเงินเข้า – เงินออกดูเลย จะพบว่า ตัวเลข กับ null มันคำนวณกันไม่ได้ ซึ่งแปลว่า Step ล่าสุดนี้ใช้ไม่ได้นะ
ทางแก้ทำได้หลายทาง
1. Replace ค่า null ในคอลัมน์เงินเข้าและเงินออกด้วย 0 ก่อนแล้วค่อย Subtract
โดยกด Step ก่อนจะ Subtract แล้วเลือกคอลัมน์เงินเข้า และ เงินออก แล้วไปที่ Transform → Replace Values จะมีหน้าต่างให้ Confirm ว่าจะ Insert Step หรือไม่ ก็ ok ไปได้เลย จากนั้นกรอกแทน null ด้วย 0
พอ ok แล้วกดไปที่ Step สุดท้ายที่ทำการ Subtract ผลลัพธ์ก็จะไม่ Error แล้ว
2. เปลี่ยนมาใช้เครื่องมือกลุ่ม Statistics แทน เพราะกลุ่มนี้ทำงานกับ null ได้
แต่เนื่องจากกลุ่ม Statistics มีแต่ Sum ดังนั้นผมต้องทำการแปลงคอลัมน์เงินออกให้ติดลบก่อน โดยการ Transform→ Standard → Multiply ด้วย -1
จากนั้นเลือกคอลัมน์เงินเข้า และ เงินออก แล้ว Add Column → Statistics → Sum เพื่อเอาค่าในคอลัมน์ที่เลือกมารวมกัน แต่เมื่อบวกเจอค่าลบ ก็จะได้ผลลัพธ์เหมือนเอามาลบกันนั่นเอง และผมตั้งชื่อว่า Net
เพียงเท่านี้เราก็สามารถแก้ปัญหากรณีต้องคำนวณเลขแล้วเจอค่า null ได้แล้วครับ
บทความนี้มีที่มายังไง?
บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ Excel Power Up! เพิ่มพลังการใช้ Excel ของคุณด้วย Power Query โดยผมเอาเนื้อหาบทแรกๆ ซัก 25-30% มาลงในเว็บให้อ่านกันฟรีๆ เลย คนอ่านจะได้ตัดสินใจได้ว่าอยากจะรู้เรื่องราวหลังจากนั้นอีกมั้ย? ซึ่งแค่นี้ก็น่าจะช่วยงานคุณได้เยอะพอสมควรแล้วล่ะ
หากสนใจอ่านตัวอย่างบทอื่นๆ ของหนังสือ ลองดูที่สารบัญข้างล่างได้เลยครับ ^^
สารบัญ Power Query
บทนำ : ทำไมต้องเรียนรู้ Power Query? [ไฟล์ประกอบ]
บทที่ 1 : เข้าใจขั้นตอนการทำรายงานสรุป / วิเคราะห์ข้อมูล [ไฟล์ประกอบ]
บทที่ 2 : ก้าวสู่การเตรียมข้อมูลยุคใหม่ด้วย Power Query [ไฟล์ประกอบ]
บทที่ 3 : ภาพรวมการทำงานกับ Power Query [ไฟล์ประกอบ]
บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query [ไฟล์ประกอบ]
บทที่ 5 : การจัดการหัวตาราง [ไฟล์ประกอบ]
บทที่ 6 : การคำนวณเบื้องต้น [ไฟล์ประกอบ]
บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ [ไฟล์ประกอบ]
บทที่ 8 : การสร้างคอลัมน์ใหม่แบบกำหนดเองด้วย Custom Column [ไฟล์ประกอบ]
บทที่ 9 : การสร้างคอลัมน์ใหม่ตามเงื่อนไข [ไฟล์ประกอบ]
บทที่ 10 : การรวมกลุ่มข้อมูลด้วย Group By [ไฟล์ประกอบ]
บทที่ 11 : การพลิกคอลัมน์เป็นหัวตารางด้วย Pivot Column [ไฟล์ประกอบ]
บทที่ 12 : การยุบหัวตารางหลายคอลัมน์ให้เหลือคอลัมน์เดียวด้วย Unpivot [ไฟล์ประกอบ]
บทที่ 13 : การแยกข้อมูลในคอลัมน์เดียวออกจากกันด้วย Split Column [ไฟล์ประกอบ]
บทที่ 14 : การใช้ Query เป็นตัวแปร [ไฟล์ประกอบ]
บทที่ 15 : การรวมข้อมูลจากหลาย Query [ไฟล์ประกอบ]
บทที่ 16 : การดึงข้อมูลจาก Excel ไฟล์อื่น [ไฟล์ประกอบ]
บทที่ 17 : การดึงข้อมูลจาก Text File/ CSV File [ไฟล์ประกอบ]
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder [ไฟล์ประกอบ]
บทที่ 19 : การดึงข้อมูลจากแหล่งอื่นๆ
บทที่ 20 : การเตรียม Data เพื่อทำ Dashboard
บทที่ 21 : การทำ Pivot Table เพื่อสร้าง Dashboard
บทที่ 22 : เจาะลึก M Code หัวใจของ Power Query
บทที่ 23 : Function คือ ขุมพลังที่แท้จริงของ M Code [ไฟล์ประกอบ]
บทที่ 24 : ตัวอย่างการสร้าง Custom Function [ไฟล์ประกอบ]
บทที่ 25 : การวน Loop [ไฟล์ประกอบ]
บทส่งท้าย : เทพที่แท้จริง
อ่านเนื้อหาบท 22 เป็นต้นไปแบบปรับปรุงใหม่ได้ฟรี ที่นี่ (อัปเดทเรื่อยๆ)
Facebook Group : Power Query Thailand
ผู้ที่สนใจ Power Query อย่างคุณที่มาอ่านบทความนี้ ผมขอเชิญชวนเข้ากลุ่มปิด Power Query Thailand ได้ตาม Link นี้ครับ
