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

บทที่ 6 : การคำนวณเบื้องต้น 1
บทที่ 5 : การจัดการหัวตาราง ใน Power Query
บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ

การสรุปข้อมูลโดยใช้เครื่องมือ Statistics

คำสั่ง Statistics ต่างๆ เช่น Sum, Minimum, Count Rows อะไรพวกนี้มันทำงานได้ 2 แบบ คือ

  • หากเลือกคอลัมน์เดียวแล้วกดแบบ Transform จะให้ผลลัพธ์สรุปข้อมูลจากคอลัมน์ที่กำหนดออกมาเป็นตัวเลขค่าเดียวเลย
  • หากเลือกหลายคอลัมน์ก่อนแล้วกดแบบ Add Column ก็จะเป็นการคำนวณสำหรับข้อมูลในแต่ละแถวแยกกันออกมาเป็นคอลัมน์ใหม่

โหลดไฟล์ตัวอย่างได้ที่นี่ => คลิ๊ก

ตัวอย่าง กรณีที่ใช้ Statistics → Minimum
สมมติผมมีข้อมูลแบบนี้

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

หากเลือกคอลัมน์ราคาในห้าง → Transform → StatisticsMinimum จะได้ค่าที่น้อยสุดในคอลัมน์นั้นออกมาเป็นค่าเดียว

หากเลือกคอลัมน์ราคาในตลาด และราคาในห้างพร้อมกัน → Add Column  → StatisticsMinimum มันจะหาว่าค่าที่น้อยสุดในแต่และแถวเป็นค่าอะไร?

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

ซึ่งทั้ง 2 วิธีมีประโยชน์ทั้งคู่ และใช้ในกรณีต่างกัน สำหรับวิธีออกมาเป็นค่าเดียว เดี๋ยวเราจะได้ใช้อีกในบทหลังๆ ซึ่งเอาค่าค่าเดียวนั้นไปใช้งานต่อที่อื่นได้อีกครับ

การคำนวณโดยใช้เครื่องมือกลุ่ม Standard

การคำนวณแบบ Standard ก็คือการคำนวณพื้นฐาน ไม่ว่าจะเป็นการบวก ลบ คูณ หาร ยกกำลัง เป็นต้น

ซึ่งมีวิธีการใช้หลากหลายรูปแบบ เช่น

  • เลือกคอลัมน์เดียว หรือ เลือกหลายคอลัมน์ (ถ้าเลือกหลายคอลัมน์จะ Transform ไม่ได้)
  • ใช้ Transform หรือ Add Column

สมมติผมมีข้อมูลแบบนี้

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

ผมคำนวณยอดขายได้โดยการ เลือกคอลัมน์ ราคาต่อหน่วย และ จำนวนชิ้นที่ขายได้ พร้อมกัน 2 คอลัมน์ แล้วกด Add Column  → Standard → Multiplication

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

จะได้ดังรูป

Tips : หากเป็นการลบหรือหาร ต้องระวังลำดับในการคลิ๊กสองคอลัมน์ที่จำคำนวณด้วย เพราะลำดับจะให้ผลต่างกันว่าจะเอาอะไรมาเป็นตัวตั้งต้น

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

จากนั้นผมอยากคำนวณว่าถ้าผมต้องจ่าย Commission 10% ของยอดขาย จะต้องเสีย Commission เท่าไหร่ ในที่นี้ผมเลือกยอดขายช่องเดียว แล้ว Add Column→ Standard → Multiplication แล้วกรอก 10%

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

แล้วจะได้ผลลัพธ์ดังรูป

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

คราวนี้ผมจะแก้ชื่อคอลัมน์จากใน 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 ลงไป

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

จะเห็นว่าผลลัพธ์ที่ได้ มันเปลี่ยนที่คอลัมน์เดิมเลย โดยไม่มีการเพิ่มคอลัมน์ใหม่ครับ

ใน Power Query เราสามารถลบข้อมูล Step ต้นทางที่ไม่ใช้แล้วได้

เมื่อเราได้ผลลัพธ์ยอดขายกับ Commission แล้ว เราสามารถลบคอลัมน์ ราคาต่อหน่วย และ จำนวนชิ้น ได้เลย (หากไม่อยากได้แล้ว) โดยที่ผลลัพธ์ไม่พังด้วยนะครับ 

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

ซึ่งเรื่องแบบนี้เราทำใน Excel ไม่ได้เด็ดขาด เพราะแหล่งที่มาของข้อมูลที่ใช้ในสูตรมันหายไป แต่ว่าใน Power Query ทำได้ครับ

ระวังค่า null ตอนคำนวณตัวเลข

เวลามีค่าที่เป็น null เราจะไม่สามารถคำนวณด้วยเครื่องมือกลุ่ม Standard ได้ครับ ซึ่งมีวิธีแก้ได้หลายแนวทาง สมมติผมมีตารางบันทึกเงินเข้าออกไว้ดังนี้

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

หากเราจะคำนวณ Net Cashflow (เงินเข้า – เงินออก) แต่ละวันจะทำยังไง? มาดูกันครับ

ก่อนอื่น หากลองเอาเงินเข้า – เงินออกดูเลย จะพบว่า ตัวเลข กับ null มันคำนวณกันไม่ได้ ซึ่งแปลว่า Step ล่าสุดนี้ใช้ไม่ได้นะ

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

ทางแก้ทำได้หลายทาง

1. Replace ค่า null ในคอลัมน์เงินเข้าและเงินออกด้วย 0 ก่อนแล้วค่อย Subtract

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

โดยกด Step ก่อนจะ  Subtract แล้วเลือกคอลัมน์เงินเข้า และ เงินออก แล้วไปที่ Transform → Replace Values จะมีหน้าต่างให้ Confirm ว่าจะ Insert Step หรือไม่ ก็ ok ไปได้เลย จากนั้นกรอกแทน null ด้วย 0

พอ ok แล้วกดไปที่ Step สุดท้ายที่ทำการ Subtract ผลลัพธ์ก็จะไม่ Error แล้ว

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

2. เปลี่ยนมาใช้เครื่องมือกลุ่ม Statistics แทน เพราะกลุ่มนี้ทำงานกับ null ได้

แต่เนื่องจากกลุ่ม Statistics มีแต่ Sum ดังนั้นผมต้องทำการแปลงคอลัมน์เงินออกให้ติดลบก่อน โดยการ Transform→ Standard → Multiply ด้วย -1

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

จากนั้นเลือกคอลัมน์เงินเข้า และ เงินออก แล้ว Add Column → Statistics → Sum เพื่อเอาค่าในคอลัมน์ที่เลือกมารวมกัน แต่เมื่อบวกเจอค่าลบ ก็จะได้ผลลัพธ์เหมือนเอามาลบกันนั่นเอง และผมตั้งชื่อว่า Net

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

เพียงเท่านี้เราก็สามารถแก้ปัญหากรณีต้องคำนวณเลขแล้วเจอค่า null ได้แล้วครับ

บทความนี้มีที่มายังไง?

บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ Excel Power Up! เพิ่มพลังการใช้ Excel ของคุณด้วย Power Query ซึ่งผมกำลังจะตีพิมพ์สิ้นปี 2562 นี้ โดยผมเอาเนื้อหาบทแรกๆ ของหนังสือ ซัก 25-30% มาลงในเว็บให้อ่านกันฟรีๆ เลย คนอ่านจะได้ตัดสินใจได้ว่าอยากจะรู้เรื่องราวหลังจากนั้นอีกมั้ย? ซึ่งก็น่าจะช่วยงานคุณได้เยอะพอสมควรแล้วล่ะ


หากสนใจอ่านตัวอย่างบทอื่นๆ ของหนังสือ ลองดูที่สารบัญข้างล่างได้เลยครับ ^^

Facebook Group : Power Query Thailand

บทที่ 6 : การคำนวณเบื้องต้น 19
Facebook Group : Power Query Thailand

ผู้ที่สนใจ Power Query อย่างคุณที่มาอ่านบทความนี้ ผมขอเชิญชวนเข้ากลุ่มปิด Power Query Thailand ได้ตาม Link นี้ครับ

สารบัญ 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 [ไฟล์ประกอบ]
บทส่งท้าย : เทพที่แท้จริง

อยากรู้เนื้อหาทั้งหมดนี้ไวๆ ทำยังไง?

นอกเหนือจากบทความที่ทยอยลงและหนังสือที่กำลังจะพิมพ์แล้ว ตอนนี้ผมมีคอร์ส In-House Training ที่สอนเรื่อง Power Query ที่ใช้เวลาอบรม 2 วันด้วยครับ บริษัทไหนสนใจก็ลองคลิ๊กดูรายละเอียดได้เลย

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

........

ใครที่ชื่นชอบบทความนี้และเห็นว่ามีประโยชน์ ช่วยบอกต่อเพื่อนๆ ของคุณด้วยนะครับ
ยิ่งเกิดประโยชน์กับคนเยอะๆ ผู้เขียนก็ยิ่งดีใจครับ คนแชร์ก็ได้ร่วมสร้างประโยชน์ด้วยนะ ^^

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


บทที่ 5 : การจัดการหัวตาราง ใน Power Query
บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ
  • 236
  •  
  •  
  •  
  •  

Posted on: October 24, 2019
Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *