บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน 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 By | Group ข้อมูลตามคอลัมน์ที่กำหนด ทำให้เหลือรายการที่ไม่ซ้ำกัน แล้วสามารถสรุปข้อมูลในคอลัมน์ที่เหลือได้ | คล้ายใช้ 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
ผู้ที่สนใจ 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 วันด้วยครับ บริษัทไหนสนใจก็ลองคลิ๊กดูรายละเอียดได้เลย
........
ใครที่ชื่นชอบบทความนี้และเห็นว่ามีประโยชน์ ช่วยบอกต่อเพื่อนๆ ของคุณด้วยนะครับ
ยิ่งเกิดประโยชน์กับคนเยอะๆ ผู้เขียนก็ยิ่งดีใจครับ คนแชร์ก็ได้ร่วมสร้างประโยชน์ด้วยนะ ^^
อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ
• Microsoft Office Specialist : Excel ระดับ Expert
• ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 6 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views +
• หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel
• วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ
Posted on: October 16, 2019