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

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

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

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

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

สารบัญ

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

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

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

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

• Excel 2010 และ Excel 2013: เป็น Add-in ที่จำเป็นต้องดาวน์โหลดและติดตั้ง

• Excel 2016: รวมเข้ากับ Excel ในชื่อ “Get & Transform”

• Excel 2019: ปรับปรุงประสิทธิภาพและฟังก์ชันการทำงาน

• Excel 2021 และ Excel สำหรับ 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

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

คุณสังเกตเห็นว่าไฟล์ตัวอย่างไม่มีส่วนหัวหรือไม่? หากโหลดลงใน Excel โดยตรง ส่วนหัวจะเป็น "คอลัมน์ 1" และ "คอลัมน์ 2"

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

ขณะนี้ Power Query Editor เปิดขึ้น ซึ่งนำเราไปสู่ส่วนถัดไป

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

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

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

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

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

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

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

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

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

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

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

คุณสมบัติ

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

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

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

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

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

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

หากคุณต้องการเรียนรู้เพิ่มเติมเกี่ยวกับตัวเลือกที่มี ให้ลองดูภาพรวมของ Power Query Editor

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

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

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

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

• โจ, บล็อก

• ลอรา, เคน

ขอให้สังเกตว่าหนึ่งในชื่อเหล่านี้ซ้ำกับไฟล์ก่อนหน้า

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

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

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

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

4. คลิกปุ่มแปลง

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

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

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

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

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

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

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

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

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

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

ขอให้สังเกตว่ามีการทำซ้ำหนึ่งแถว - นี่คือวิธีการทำงานของการต่อท้าย เป็นการเพิ่มข้อมูลจากตารางหนึ่งไปยังอีกตารางหนึ่ง โดยไม่คำนึงว่าข้อมูลนั้นมีอยู่แล้วหรือไม่

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

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

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

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

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

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

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

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

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

ทำตามคำแนะนำในส่วนก่อนหน้าเพื่อโหลดแต่ละไฟล์ลงใน Power Query ของตนเอง จากนั้นคุณสามารถฝึกการรวมตารางได้

ในกรณีของข้อมูลตัวอย่างของเรา ควรเลือกทั้งสองคอลัมน์ (ชื่อและนามสกุล) เป็นคอลัมน์ที่ตรงกัน กด ปุ่ม Ctrl ค้างไว้ เพื่อเลือกหลายคอลัมน์

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

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

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

3. เลือก "ผสานแบบสอบถาม" จากกลุ่ม "รวม"

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

5. คลิกที่คอลัมน์แรกในตารางแรก

6. กดปุ่ม Ctrl ค้างไว้แล้วคลิกที่คอลัมน์ที่สองในตารางแรก

7. กดปุ่ม Ctrl ค้างไว้แล้วคลิกทั้งสองคอลัมน์ในตารางที่สอง

8. เก็บตัวดำเนินการเข้าร่วมเริ่มต้นเป็น "Left Outer"

9. คลิก “ตกลง” เพื่อใช้การเปลี่ยนแปลง

ตัวดำเนินการรวม "Left Outer" ช่วยให้มั่นใจได้ว่าข้อมูลที่ซ้ำกันจะถูกกำจัด

ในตัวแก้ไข Power Query คุณจะเห็นคอลัมน์ใหม่ที่ชื่อว่า "ตาราง" หรือ "ตารางที่ซ้อนกัน" ซึ่งมีข้อมูลที่ผสานจากคิวรีที่สอง

1. คลิกที่ไอคอนขยาย (ลูกศรสองอัน) ในส่วนหัวของคอลัมน์นี้

2. ยกเลิกการเลือกช่อง "ใช้คอลัมน์เดิมเป็นคำนำหน้า" (เพื่อหลีกเลี่ยงชื่อคอลัมน์ที่ยาว)

3. คลิก “ตกลง”

4. คลิก "ปิดและโหลด"

ตารางที่ผสานมีสี่คอลัมน์ โดยมีรายละเอียดโทรศัพท์และที่อยู่รวมกัน คุณอาจยังมีงานที่ต้องจัดการอีกเล็กน้อย

ตัวอย่างเช่น คุณอาจต้องการแก้ไขชื่อของตาราง (Merge1) และคอลัมน์ที่อยู่ในรูปภาพด้านล่าง

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

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

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

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

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

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

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

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

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

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

ส่วนก่อนหน้านี้แสดงวิธีการขยายโดยรวมข้อมูลจากตารางที่เกี่ยวข้อง

คุณยังสามารถย่อให้รวมเฉพาะข้อมูลสรุปตามการรวมที่คุณเลือก เช่น ผลรวม จำนวน หรือเปอร์เซ็นต์

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

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

คลิกที่ลูกศรชี้ลงข้างชื่อคอลัมน์ แล้วเลือก “เรียงลำดับจากน้อยไปหามาก” หรือ “เรียงลำดับจากน้อยไปหามาก”

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

แท็บการแปลงในตัวแก้ไข Power Query มีตัวเลือกมากมาย

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

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

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

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

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

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

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

• แถบสูตร

• รหัสเอ็ม

• องค์ประกอบที่สำคัญของไวยากรณ์ภาษา M

สูตรบาร์

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

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

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

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

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

• รวมรหัส M กับตาราง Excel, ตาราง Pivot และแหล่งข้อมูลภายนอก เช่น Azure และไฟล์ข้อความเพื่อสร้างรายงานแบบไดนามิกและอัตโนมัติ

• ปรับตัวเลือกข้อความค้นหาเพื่อปรับแต่งการตั้งค่าการโหลดข้อมูลและปรับปรุงประสิทธิภาพ

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

หากต้องการดูรหัสที่อยู่เบื้องหลังแบบสอบถาม ให้ทำตามขั้นตอนเหล่านี้:

1. เปิดแบบสอบถามในตัวแก้ไข Power Query

2. คลิกที่ “ตัวแก้ไขขั้นสูง” ในแท็บหน้าแรก

นี่คือรหัสเบื้องหลังตัวอย่างตารางผสานของเรา (ของคุณอาจดูแตกต่างออกไป):

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

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

• ภาษา M คำนึงถึงตัวพิมพ์เล็กและใหญ่ ดังนั้นโปรดระวังการใช้อักษรตัวพิมพ์ใหญ่

• ใช้วงเล็บเหลี่ยม [] เพื่ออ้างอิงคอลัมน์ บันทึก และรายการ

• หากต้องการอ้างอิงขั้นตอนก่อนหน้าในแบบสอบถามของคุณ ให้ใช้ชื่อตัวแปร (เช่น #”ชื่อขั้นตอน”)

• แสดงขั้นตอนสูตรคิวรีด้วยคำสั่ง "in"

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

ดูวิดีโอด้านล่าง ถึงเวลาหยิบแปรงขัดและทำความสะอาดข้อมูลที่ยุ่งเหยิงของคุณ

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

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

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

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

เมื่อเปิดบานหน้าต่าง แบบสอบถาม & การเชื่อมต่อ จะแสดงรายการแบบสอบถามทั้งหมด ทำตามขั้นตอนเหล่านี้:

1. คลิกขวาที่แบบสอบถามที่คุณต้องการรีเฟรช

2. เลือกรีเฟรชจากเมนูบริบท

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

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

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

โมเดลข้อมูลนั้นเป็นฐานข้อมูลที่ซ่อนอยู่ซึ่งเก็บข้อมูลที่นำเข้าและความสัมพันธ์ระหว่างตาราง

เป็นส่วนหนึ่งของฟีเจอร์ Power Pivot ซึ่งมีอยู่ใน Excel เวอร์ชันปัจจุบัน คุณอาจต้องติดตั้ง Power Pivot เป็น Add-in เพื่อเข้าถึงในเวอร์ชันก่อนหน้า)

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

1. โหลดข้อมูลของคุณโดยใช้ Power Query ตามที่อธิบายไว้ในส่วนก่อนหน้า

2. แทนที่จะโหลดลงในสมุดงานโดยตรง ให้เลือกโหลดไปที่จากเมนูโหลด

3. เลือกเพิ่มข้อมูลนี้ไปยังกล่องกาเครื่องหมายตัวแบบข้อมูลในกล่องโต้ตอบนำเข้าข้อมูล

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

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

คุณสามารถใช้ประโยชน์จากทักษะ VBA ที่มีอยู่เพื่อสร้างโมเดลข้อมูลขั้นสูงโดยการรวมข้อมูลจากแพลตฟอร์มต่างๆ เช่น Microsoft Access, SharePoint และ Analysis Services

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

ซึ่งช่วยให้คุณสร้างเวิร์กโฟลว์ข้อมูลอัตโนมัติที่มีประสิทธิภาพ และทำให้กระบวนการวิเคราะห์ข้อมูลของคุณคล่องตัวขึ้น

ต่อไปนี้คือโค้ดตัวอย่างบางส่วนที่รีเฟรช Power Query ในสมุดงานปัจจุบัน

:

Dim wb เป็นสมุดงาน pq เป็นวัตถุ

ตั้งค่า wb = สมุดงานนี้

ตั้งค่า pq = wb.Query("ชื่อลูกค้า")

 pq.รีเฟรช

นี่คือรหัสภายในแมโคร Excel VBA:

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

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

เครื่องมือวิเคราะห์ข้อมูลในตัวประกอบด้วย:

• คอลัมน์เงื่อนไขสำหรับการใช้ตรรกะที่ซับซ้อนกับการแปลงข้อมูลของคุณ

• ฟังก์ชันการจัดกลุ่มและการรวมเพื่อสรุปข้อมูลของคุณอย่างมีความหมาย

• การดำเนินการ Pivot และ Unpivot เพื่อปรับรูปร่างข้อมูลของคุณใหม่เพื่อการวิเคราะห์ที่ง่ายขึ้น

คุณจะพบฟีเจอร์เหล่านี้มากมายในแท็บการแปลงในตัวแก้ไข Power Query

นอกจากนี้ คุณสามารถเชื่อมต่อ Power Query กับเครื่องมือ Business Intelligence เช่น LuckyTemplatesสำหรับตัวเลือก การวิเคราะห์ข้อมูลและ การแสดงภาพ ขั้นสูงเพิ่มเติม

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

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

คุณสามารถแชร์เวิร์กบุ๊กกับคิวรีได้โดยบันทึกลงในไดรฟ์เครือข่าย, SharePointหรือตำแหน่งที่เก็บข้อมูลที่ใช้ร่วมกันอื่นๆ

คุณยังสามารถส่งออกรหัส M ที่อยู่เบื้องหลังการค้นหาเดียวด้วยขั้นตอนเหล่านี้:

1. แก้ไขแบบสอบถามในตัวแก้ไข Power Query

2. คลิกที่ “ตัวแก้ไขขั้นสูง” ในแท็บหน้าแรก

3. คัดลอกรหัส M ทั้งหมดในหน้าต่างตัวแก้ไขขั้นสูง

4. เปิดตัวแก้ไขข้อความ (เช่น Notepad) และวางรหัส M ที่คัดลอกลงในโปรแกรมแก้ไขข้อความ

5. บันทึกไฟล์ข้อความและส่งให้เพื่อนร่วมงานของคุณ

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

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

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

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