วิธีใช้ Power Query ใน Excel: คู่มือฉบับสมบูรณ์

Power Query เป็นเครื่องมือข่าวกรองธุรกิจที่มีประสิทธิภาพใน Excel ที่ช่วยให้คุณสามารถนำเข้า ล้างข้อมูล และแปลงข้อมูลตามความต้องการของคุณ หากคุณเคยใช้เวลาหลายชั่วโมงในการล้างข้อมูล หรือมีปัญหาในการรวมข้อมูลจากแหล่งต่างๆ ฟีเจอร์นี้จะทำให้ชีวิตคุณง่ายขึ้น

วิธีที่ดีที่สุดในการเรียนรู้วิธีใช้ Power Query ใน Excel คือการทำงานทั่วไปบางอย่าง เช่น การนำเข้าข้อมูลและการใช้ Power Query Editor เพื่อแปลงตารางผลลัพธ์และรวมข้อมูล

บทความนี้ช่วยให้คุณเริ่มต้นด้วยการแสดงตัวอย่าง Power Query ที่ชัดเจนพร้อม คำแนะนำทีละขั้นตอน ด้วยการเรียนรู้ Power Query คุณสามารถยกระดับงานของคุณไปอีกขั้น

คุณพร้อมไหม? ลงมือทำกันเถอะ.

สารบัญ

1. จะรู้ได้อย่างไรว่าคุณสามารถเข้าถึง Power Query
2. วิธีนำเข้าข้อมูลด้วย Power Query
3. วิธีใช้ตัวแก้ไข Power Query
   • ดูตัวอย่างข้อมูล
   • คุณสมบัติ
4. วิธีทำงานกับแหล่งข้อมูลหลายแหล่ง
5. วิธีผนวกข้อมูลด้วย Power Query
6. วิธีผสานข้อมูลด้วย Power Query
7. 5 วิธีในการแปลงข้อมูลด้วย Power Query
8. การทำงานกับสูตรและภาษา M
   • สูตรบาร์
   • การเขียนและแก้ไข M Code
9. ไวยากรณ์ภาษา M คืออะไร?
10. วิธีรีเฟรชการเชื่อมต่อข้อมูล
11. วิธีการรวม Power Query กับตัวแบบข้อมูล
12. วิธีการทำงานอัตโนมัติด้วย Power Query และ VBA
13. คุณลักษณะขั้นสูงของ Power Query
14. การทำงานร่วมกันและการแบ่งปัน
15. มาสรุปเรื่องนี้กันเถอะ

จะรู้ได้อย่างไรว่าคุณสามารถเข้าถึง Power Query

ก่อนที่คุณจะเริ่มใช้ Power Query คุณต้องแน่ใจว่าคุณมี Excel เวอร์ชันที่มีคุณลักษณะนี้

นี่คือภาพรวมโดยย่อของ Power Query ตามเวอร์ชัน:

เวอร์ชัน Excel	รายละเอียด
Excel 2010/2013	เป็น Add-in ที่จำเป็นต้องดาวน์โหลดและติดตั้ง
Excel 2016	รวมเข้ากับ Excel ในชื่อ “Get & Transform”
Excel 2019	ปรับปรุงประสิทธิภาพและฟังก์ชันการทำงาน
Excel 2021/Microsoft 365	การรวมกับแหล่งข้อมูลบนคลาวด์
Excel สำหรับ Mac	พร้อมใช้งานจาก Microsoft 365 เวอร์ชัน 16.69 และใหม่กว่า

ในการตรวจสอบว่าเครื่องมือนั้นรวมอยู่ในสมุดงาน Excel ของคุณ (2016 หรือใหม่กว่า) ให้ไปที่แท็บข้อมูลแล้วมองหาส่วน "รับและแปลงข้อมูล" ในรูปภาพนี้:

ถ้าคุณมี Excel 2010 หรือ 2013 บน Windows คุณสามารถ ดาวน์โหลดติดตั้ง และเปิดใช้งาน Add-in ของ Power Query

วิธีนำเข้าข้อมูลด้วย Power Query

เครื่องมือ Power Query ช่วยให้คุณสามารถนำเข้าและเชื่อมต่อกับแหล่งข้อมูลที่หลากหลาย:

• ไฟล์ รวมถึงรูปแบบ CSV, XML และ JSON

• URL ของเว็บหรือจุดสิ้นสุดของ API

• ฐานข้อมูล รวมถึง Access, SQL Server, Oracle และฐานข้อมูลที่ทันสมัยที่สุด

• บริการ Azure, ไฟล์ SharePoint และตัวดึงข้อมูล OData

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

• โจ, บล็อก

• แอนน์, ไรอัน

• เจมส์, สจ๊วต

• แมรี, บราวน์

อย่าลืมบันทึกไฟล์ด้วยนามสกุล .CSV ไฟล์ของคุณจะมีลักษณะดังนี้:

เมื่อต้องการโหลดข้อมูลด้วย Power Query ให้ทำตามขั้นตอนเหล่านี้:

1. ไปที่แท็บข้อมูล

2. คลิกปุ่ม "รับไฟล์" ในแท็บรับและแปลงข้อมูล

3. เลือกไฟล์ตัวอย่างของคุณ

ให้รอสักครู่เพื่อให้เครื่องมือดึงข้อมูลไปยังบานหน้าต่างแสดงตัวอย่าง Power Query.

เคล็ดลับ: ปิดแอปพลิเคชันอื่นเพื่อเร่งประสิทธิภาพ

Power Query จะแสดงหน้าต่าง "นำเข้าข้อความ/CSV" พร้อมแสดงตัวอย่างข้อมูลของคุณ:

หากข้อมูลดูถูกต้อง คุณสามารถโหลดหรือแปลงข้อมูลต่อไปได้:

• ปุ่มโหลดจะโหลดข้อมูลโดยตรงลงใน Excel

• ปุ่มแปลงข้อมูลช่วยให้คุณแก้ไขข้อมูลก่อนที่จะโหลด

วิธีใช้ตัวแก้ไข Power Query

ในส่วนนี้ เราจะสำรวจแง่มุมที่สำคัญบางประการของการทำงานกับ Power Query Editor

ดูตัวอย่างข้อมูล

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

เมื่อคุณทำเสร็จแล้ว Excel จะโหลดข้อมูลที่แปลงแล้วลงในแผ่นงาน

เมื่อใช้ไฟล์ตัวอย่าง คุณสามารถแก้ไขชื่อคอลัมน์ได้โดยตรงในบานหน้าต่างนี้

ในการทำเช่นนั้น:

1. คลิกขวาที่ส่วนหัวของคอลัมน์

2. คลิก “เปลี่ยนชื่อ” ในเมนูแบบเลื่อนลง

3. พิมพ์ชื่อที่ต้องการลงในช่องส่วนหัว

คุณสมบัติ

การตั้งค่าคุณสมบัติสำหรับการสืบค้นของคุณเป็นสิ่งสำคัญสำหรับการจัดระเบียบและติดตามงานของคุณ

บานหน้าต่างการตั้งค่าแบบสอบถามอยู่ทางด้านขวาของหน้าต่าง Power Query

1. คลิกลิงก์ "คุณสมบัติทั้งหมด"

2. แก้ไขชื่อแบบสอบถามให้เป็นคำอธิบาย

3. เพิ่มคำอธิบายเพื่อให้ข้อมูลเพิ่มเติม

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

วิธีทำงานกับแหล่งข้อมูลหลายแหล่ง

ตัวแก้ไข Power Query มีสองวิธีหลักสำหรับการรวมข้อมูลจากหลายแหล่ง: การต่อท้ายและการผสาน

การต่อท้ายรวมตารางในแนวตั้ง วางซ้อนกัน การผสานทำให้คุณสามารถรวมตารางตามคอลัมน์ทั่วไป

หากต้องการดูวิธีการทำงาน ให้สร้างไฟล์ CSV ขนาดเล็กไฟล์ที่สองที่มีโครงสร้างเหมือนกับไฟล์แรก คราวนี้ใส่สองชื่อ (ชื่อแรกและนามสกุล) ลงในไฟล์:

• โจ, บล็อก

• ลอรา, เคน

ทำตามขั้นตอนเหล่านี้เพื่อดึงข้อมูลลงในสมุดงาน Microsoft Excel ของคุณ

1. ปิดหน้าต่างตัวแก้ไข Power Query และบันทึกการเปลี่ยนแปลงของคุณ

2. เพิ่มแผ่นงานใหม่

3. ทำตามขั้นตอนในส่วนก่อนหน้าเพื่อนำเข้าไฟล์ที่สอง

4. คลิกป button "แปลง"

5. เปลี่ยนชื่อส่วนหัวของคอลัมน์เป็น “ชื่อ” และ “นามสกุล”

บานหน้าต่างด้านขวาในเวิร์กชีต Excel จะแสดง Query & Connections ปัจจุบัน คุณควรเห็นแบบสอบถามสองรายการในบานหน้าต่าง

วิธีผนวกข้อมูลด้วย Power Query

ในส่วนนี้ คุณจะต่อท้ายไฟล์ CSV ตัวอย่างที่สองต่อท้ายไฟล์แรก

ทำตามขั้นตอนเหล่านี้เพื่อเพิ่มข้อมูล:

1. คลิกขวาที่ตารางแรก (ชื่อลูกค้า) ในบานหน้าต่าง Query & Connections

2. เลือก "แก้ไข" เพื่อเปิดตารางในตัวแก้ไข Power Query

3. คลิกที่ผนวกแบบสอบถามในส่วนรวม

4. เลือกตารางที่สองจากรายการแบบเลื่อนลงของตารางที่จะต่อท้าย

ผลลัพธ์จะเป็นสองแถวพิเศษในตารางแรก

คุณสามารถ ลบข้อมูลที่ซ้ำกันในตัวแก้ไข Power Queryด้วยขั้นตอนเหล่านี้:

1. คลิกที่ส่วนหัวของคอลัมน์ "ชื่อ"

2. กดป button “Ctrl” ค้างไว้ (หรือปุ่ม “Cmd” บน Mac)

3. คลิกที่ส่วนหัวของคอลัมน์ทั้งสอง

4. คลิกที่ "ลบแถว" ในแท็บหน้าแรกของ Ribbon ตัวแก้ไข Power Query

5. เลือก “ลบรายการที่ซ้ำกัน” จากเมนูแบบเลื่อนลง

วิธีผสานข้อมูลด้วย Power Query

เมื่อคุณผสานข้อมูล คุณต้องเลือกคอลัมน์ที่ตรงกันระหว่างแบบสอบถาม

สมมติว่าคุณมีไฟล์ที่คั่นด้วยเครื่องหมายจุลภาคสองไฟล์ โดยที่ไฟล์หนึ่งมีหมายเลขโทรศัพท์ของลูกค้า และอีกไฟล์หนึ่งมีที่อยู่ ไฟล์ทั้งสองมีลักษณะดังนี้:

สุดท้ายคุณจะต้องทำการรวมข้อมูลตามคอลัมน์ที่คุณได้เลือกไว้จากทั้งสองไฟล์.

5 วิธีในการแปลงข้อมูลด้วย Power Query

การแปลงข้อมูลในไฟล์ Excel ทำได้ง่ายด้วย Power Query ทำให้คุณสามารถล้างข้อมูล จัดระเบียบ และจัดการข้อมูลได้อย่างมีประสิทธิภาพ

วิธีทั่วไปในการแปลงข้อมูลคือ:

1. การขยายและย่อข้อมูล
2. การเรียงลำดับข้อมูล
3. การจัดรูปแบบข้อมูล
4. การเพิ่มและลบคอลัมน์
5. การเปลี่ยนประเภทข้อมูล

1. การขยายและย่อข้อมูล

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

2. การเรียงลำดับข้อมูล

ในการจัดเรียงข้อมูล คุณสามารถเปลี่ยนลำดับได้อย่างรวดเร็ว ด้วยค่าจากน้อยไปมากหรือตามที่คุณต้องการ

3. การจัดรูปแบบข้อมูล

แท็บการแปลงในตัวแก้ไข Power Query มีตัวเลือกมากมายสำหรับการจัดรูปแบบข้อมูลของคุณ

4. การเพิ่มและลบคอลัมน์

Power Query ทำให้กระบวนการเพิ่ม ลบ และแก้ไขคอลัมน์ง่ายขึ้น

5. การเปลี่ยนประเภทข้อมูล

การทำงานกับประเภทข้อมูลที่ไม่เปลี่ยนแปลงเป็นสิ่งสำคัญเพื่อให้แน่ใจได้ว่าการวิเคราะห์ของคุณนั้นถูกต้อง.

การทำงานกับสูตรและภาษา M

การใช้ Power Query ใน Excel คุณจะพบกับภาษาตัว M ซึ่งเป็นหัวใจของการสร้างสูตรขั้นสูงในการจัดการข้อมูล

สูตรบาร์

แถบสูตรในตัวแก้ไข Power Query คือพื้นที่ที่คุณจะป้อนสูตรและรหัส M.

การเขียนและแก้ไข M Code

หากต้องการสร้างคิวรีที่หลากหลายและมีประสิทธิภาพใน Excel คุณจะต้องเขียนและแก้ไขภาษา Power Query ที่รู้จักกันในชื่อรหัส M

ไวยากรณ์ภาษา M คืออะไร?

การทำความเข้าใจไวยากรณ์ของภาษา M เป็นสิ่งสำคัญสำหรับการสร้างสูตร Power Query ที่มีประสิทธิภาพ

วิธีรีเฟรชการเชื่อมต่อข้อมูล

คุณลักษณะที่สำคัญอย่างหนึ่งของ Power Query คือความสามารถในการรีเฟรชการเชื่อมต่อ Power Query ของคุณได้อย่างง่ายดาย

วิธีการรวม Power Query กับตัวแบบข้อมูล

รวมการเชื่อมต่อข้อมูล Power Query ของคุณกับแบบจำลองข้อมูลเพื่อการวิเคราะห์ที่มีประสิทธิภาพมากขึ้น

วิธีการทำงานอัตโนมัติด้วย Power Query และ VBA

ด้วยการรวม VBA คุณสามารถสร้างการเชื่อมต่อข้อมูลของคุณและทำให้ขั้นตอนการแปลงข้อมูลเป็นอัตโนมัติ

คุณลักษณะขั้นสูงของ Power Query

Power Query มีคุณลักษณะมากมายเพื่อปรับปรุงขั้นตอนการวิเคราะห์ของคุณ

การทำงานร่วมกันและการแบ่งปัน

การแชร์การเชื่อมต่อข้อมูลเป็นสิ่งสำคัญสำหรับการทำงานร่วมกันอย่างมีประสิทธิภาพ

มาสรุปเรื่องนี้กันเถอะ

Power Query เป็นเครื่องมือที่จำเป็นสำหรับผู้ใช้ Excel ที่ต้องการทำงานกับข้อมูลภายนอกอย่างมีประสิทธิภาพ

มันช่วยคุณประหยัดเวลาและเพิ่มประสิทธิภาพในการทำงานกับไฟล์ Excel