บทที่ 6 : การคำนวณเบื้องต้น

การสรุปข้อมูลโดยใช้เครื่องมือ 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 ได้แล้วครับ