บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query

บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query 1
บทที่ 3 : ภาพรวมการทำงานกับ Power Query
บทที่ 5 : การจัดการหัวตาราง ใน Power Query

Power Query นั้นมีจุดแข็งอย่างมากเรื่องความสามารถในการดัดแปลงข้อมูลให้อยู่ในรูปแบบที่เหมาะสม โดยมีเครื่องมือมากมายที่จะช่วยเราในเรื่องนั้นได้ เรามาดูกันรวมๆ ก่อนว่ามันมีเครื่องมืออะไรที่น่าสนใจบ้าง ซึ่งผมคัดเลือกเครื่องมือที่ผมคิดว่าเจ๋งๆ มาให้บางส่วนดังนี้

ลองอ่านดูคร่าวๆ ก่อน จะได้เห็นภาพว่า Power Query นั้นทำอะไรได้แบบที่กดด้วยเครื่องมือได้เลยบ้าง สำหรับบางตัวที่มีความซับซ้อนมากๆ ผมจะมีการอธิบายโดยละเอียดอีกทีนึงด้วยครับ

เครื่องมือจัดการโครงสร้างหัวตาราง

ชื่อเครื่องมือ
ใน Power Query
ความสามารถคล้ายอะไร
ใน Excel?
Use First Row as Headersทำให้แถวแรกสุดเป็นหัวตารางInsert Table แล้วติ๊กหัวตาราง
Use Headers as First Rowทำให้หัวตารางมาเป็นแถวแรกConvert Table to Range
Change Data Typeเปลี่ยนประเภทข้อมูล=A1*1 แปลงเป็นเลข
=A1&”” แปลงเป็น Text

จัดการแถว

ชื่อเครื่องมือ
ใน Power Query
ความสามารถคล้ายอะไร
ใน Excel?
Remove Rows →
Remove Top/Bottom Rows
ลบแถวบนสุด/ล่างสุด xxx แถวDelete Row แบบ Manual
Remove Rows →
Remove Duplicates
ทำให้เหลือแต่ข้อมูลที่ไม่ซ้ำกันData → Remove Duplicates
Remove Rows →
Remove Blank Rows
เอาแถวว่างแบบว่างทั้งแถวออก
กด Filter หัวตารางแต่ละคอลัมน์เลือกว่าจะให้เหลือบรรทัดที่มีข้อมูลแบบไหนบ้างData → Filter
(แต่ Power Query เอาข้อมูลทิ้งไปเลย ไม่ใช่แค่ซ่อนแถวเหมือน Excel)
กด Sort หัวตารางแต่ละคอลัมน์เรียงข้อมูลตามที่กำหนด แต่กดหลายๆ Step ต่อเนื่องกันได้เลยData → Sort
Reverse Rowกลับแถวล่างสุดมาอยู่บนสุดData → Sort ข้อมูลกลับด้าน

จัดการคอลัมน์

ชื่อเครื่องมือ
ใน Power Query
ความสามารถคล้ายอะไร
ใน Excel?
Remove Columns →
Remove Columns
ลบคอลัมน์ที่เลือกDelete Column แบบ Manual
Remove Columns →
Remove Other Columns
ลบคอลัมน์ที่ไม่ได้เลือกออกให้หมด
และเรียงลำดับคอลัมน์ใหม่ตามลำดับการคลิ๊กด้วย

Choose Columnsติ๊กเลือกว่าจะเอาคอลัมน์ไหนไว้บ้าง
Merge Columnsรวมข้อมูลจากหลายคอลัมน์
เข้าด้วยกัน ใส่ตัวคั่นได้
เหมือนการใช้ & ในสูตร เช่น =A1&A2
Custom Columnเพิ่มคอลัมน์แบบสามารถเขียนสูตรเอง
โดยใช้ภาษา M Code เป็นตัวเขียน
เขียนสูตรใน Excel Table แบบ Structure Reference

เช่น
สูตรยอดขายจะเขียนว่า
=[@ราคาต่อหน่วย]*[@จำนวนชิ้น]
Column from Examplesเพิ่มคอลัมน์ตามตัวอย่างที่เรากรอกให้
แล้วมันจะคิดสูตรให้เอง
เครื่องมือ Flash Fill
Invoke Custom Functionเรียกใช้ฟังก์ชันที่เขียนขึ้นมาเอง เป็นสุดยอดเครื่องมือที่มีความสามารถสูง
เพราะเราสามารถรวบคำสั่งหรืองาน
ที่ต้องทำไว้เป็นฟังก์ชัน แล้วนำมาเรียกใช้ตรงนี้ได้
เรียกใช้ User Defined Function ที่เขียนใน VBA
Index Columnสร้างเลข Running ขึ้นมาได้ลาก Fill Handle แบบ Running

เครื่องมือแก้ไขตัวข้อมูลในแต่ละช่อง

ชื่อเครื่องมือ
ใน Power Query
ความสามารถคล้ายอะไร
ใน Excel?
Replace Valuesแทนค่าข้อมูลที่กำหนด ด้วยข้อมูลอีกตัวเครื่องมือ Find & Replace (Ctrl+H)
Fill → Down / Fill →  Upถมค่าช่องว่างด้วยค่าด้านบน /ด้านล่าง (ถ้าจะ Fill Right/Left ต้องใช้ Transpose มาช่วย) ดับเบิ้ลคลิ๊กที่ Fill Handle ของตัวด้านบน
Format →Lower Case / Upper Caseทำให้เป็นตัวพิมพ์เล็ก/พิมพ์ใหญ่ (จำเป็นมาก เพราะ Power Query เป็น Case-Sensitive)
Format →Trimตัดช่องว่างหน้าคำและหลังคำออก (ระวัง! Trim ใน Power Query ไม่ตัดช่องว่างกลางออกให้เหมือน Excel)ฟังก์ชัน TRIM

(แต่ TRIM ใน Excel ตัดช่องว่างตรงกลางให้เหลือแค่ 1 เคาะด้วย)
Extract →
Length
หาจำนวนตัวอักษรฟังก์ชัน LEN
Extract →
First/Last Character
เอาบางส่วนของข้อความจากทางซ้าย/ขวา ตามจำนวนตัวอักษรที่กำหนดฟังก์ชัน LEFT / RIGHT
Extract →
Range
เอาบางส่วนของข้อความจากช่วงที่กำหนดฟังก์ชัน MID
Extract →
Text Before/After Delimiter
เอาบางส่วนของข้อความที่อยู่ก่อน/หลัง ตัวคั่นที่กำหนด
Extract →
Text Between Delimiter
เอาบางส่วนของข้อความที่อยู่ระหว่างตัวคั่นที่กำหนด

เครื่องมือเกี่ยวกับโครงสร้างหน้าตาตาราง

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

ชื่อเครื่องมือ
ใน Power Query
ความสามารถคล้ายอะไร
ใน Excel?
Transposeพลิกแถวเป็นคอลัมน์ คอลัมน์เป็นแถว (มักใช้กับการแก้หัวตาราง)Copy → Paste แบบ Transpose
Append Queriesเอาข้อมูลสองตารางขึ้นไปมาต่อกัน โดยดูจากชื่อหัวตารางที่เหมือนกัน (บน-ล่าง)
Merge Queriesเอาคอลัมน์มาจากอีกตารางที่มีความเกี่ยวข้องกัน (ซ้าย-ขวา)ฟังก์ชัน VLOOKUP
Group ByGroup ข้อมูลตามคอลัมน์ที่กำหนด ทำให้เหลือรายการที่ไม่ซ้ำกัน แล้วสามารถสรุปข้อมูลในคอลัมน์ที่เหลือได้คล้ายใช้ Pivot Table ลาก Field ไปไว้ที่ Row แล้วลากอีก Field ไว้ที่ Value
Pivot Columnพลิกเอาข้อมูลที่มีหลายรายการในคอลัมน์ที่กำหนด ทำให้ไม่ซ้ำกัน แล้วย้ายให้ไปเป็นหัวตาราง (หลายคอลัมน์) แล้วสรุปข้อมูลจากคอลัมน์ที่กำหนดได้คล้ายใช้ Pivot Table ลาก Field ไปไว้ที่ Column แล้วลากอีก Field ไว้ที่ Value
Unpivot Columnsพลิกเอาข้อมูลที่แยกอยู่หลายคอลัมน์ ให้มาอยู่ในคอลัมน์เดียวแต่หลายบรรทัดแทน
Split Columnแยกข้อความคอลัมน์ที่กำหนดออกมาเป็นหลายๆ คอลัมน์หรือหลายๆแถวเครื่องมือ Text to Column
Convert to Listเปลี่ยนคอลัมน์ที่เลือกให้กลายเป็น List
Count Rowsให้ผลลัพธ์ค่าเดียวว่าตารางมีทั้งหมดกี่แถว (นับแถวว่างด้วย)

เครื่องมือเพื่อสรุปข้อมูล / คำนวณข้อมูล

ชื่อเครื่องมือ
ใน Power Query
ความสามารถคล้ายอะไร
ใน Excel?
Statistics → Sum / Minimum / Maximumหาผลรวม / ค่าน้อยสุด/มากสุดฟังก์ชัน SUM / MIN / MAX
Statistics →Median / Averageหาค่ามัธยฐาน (ค่าที่อยู่กลางเวลาเรียงจากน้อยไปมาก) /
หาค่าเฉลี่ย
ฟังก์ชัน MEDIAN / AVERAGE
Statistics →Count Valuesนับจำนวนข้อมูล (ไม่นับช่องว่าง)ฟังก์ชัน COUNTA
Statistics →Count Distinct Valuesนับจำนวนข้อมูลแบบไม่ซ้ำกัน (ไม่นับช่องว่าง)เหมือนใช้ Distinct Count ใน Power Pivot / DAX 
Standard →
Add/Multiply/
Subtract/Divide
บวก/ลบ/คูณ/หาร ปกติเหมือนเครื่องหมาย +, -, *,/
Standard →
Integer-Divide
หารแล้วปัดเศษทิ้งให้เป็นจำนวนเต็ม
Standard →
Modulo
คำนวณหาเศษเหลือจากการหาร
เช่น 10 หาร 3 เหลือเศษ 1
ฟังก์ชัน MOD
Standard →
Percentage
คำนวณเอาคอลัมน์ที่เลือกไปคูณกับ % ที่กำหนด
Standard →
Percent Of
คำนวณว่าค่าในคอลัมน์ที่เลือกเป็นกี่ % ของค่าที่กำหนด

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

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


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

Facebook Group : Power Query Thailand

บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query 2
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 วันด้วยครับ บริษัทไหนสนใจก็ลองคลิ๊กดูรายละเอียดได้เลย

บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query 3

........

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

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


บทที่ 3 : ภาพรวมการทำงานกับ Power Query
บทที่ 5 : การจัดการหัวตาราง ใน Power Query
  • 281
  •  
  •  
  •  
  •  

Posted on: October 16, 2019

Leave a Reply

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