Power Query มีหลายวิธีในการสร้างและโหลด Power queries ลงในเวิร์กบุ๊กของคุณ คุณยังสามารถตั้งค่าการโหลดคิวรีเริ่มต้นในหน้าต่าง ตัวเลือกคิวรี ได้
เคล็ด ลับ เมื่อต้องการบอกว่าข้อมูลในเวิร์กชีตมีรูปร่างเป็น Power Query หรือไม่ ให้เลือกเซลล์ของข้อมูล และถ้าแท็บ Ribbon บริบทคิวรี ปรากฏขึ้น แสดงว่าข้อมูลถูกโหลดจาก Power Query
เกี่ยวกับการรวม Power Query เข้ากับ Excel
ทราบว่าสภาพแวดล้อมใดที่คุณอยู่ใน Power Query รวมอยู่ในส่วนติดต่อผู้ใช้ของ Excel โดยเฉพาะเมื่อคุณนําเข้าข้อมูล ทํางานกับการเชื่อมต่อ และแก้ไข Pivot Table, ตาราง Excel และช่วงที่มีชื่อ เพื่อหลีกเลี่ยงความสับสน สิ่งสําคัญคือต้องทราบว่าคุณกําลังอยู่ในสภาพแวดล้อมใด Excel หรือ Power Query เมื่อใดก็ได้
| เวิร์กชีต Excel, Ribbon และเส้นตารางที่คุ้นเคย | Ribbon ตัวแก้ไข Power Query และการแสดงตัวอย่างข้อมูล |
|---|---|
|
|
ตัวอย่างเช่น การจัดการข้อมูลในเวิร์กชีต Excel จะแตกต่างจาก Power Query นอกจากนี้ ข้อมูลที่เชื่อมต่อที่คุณเห็นในเวิร์กชีต Excel อาจหรือไม่ได้ Power Query ทํางานอยู่เบื้องหลังเพื่อจัดรูปแบบข้อมูล ซึ่งจะเกิดขึ้นเมื่อคุณโหลดข้อมูลลงในเวิร์กชีตหรือตัวแบบข้อมูลจาก Power Query เท่านั้น
เปลี่ยนชื่อแท็บเวิร์กชีต คุณควรเปลี่ยนชื่อแท็บเวิร์กชีตด้วยวิธีที่มีความหมาย โดยเฉพาะถ้าคุณมีแท็บจํานวนมาก การอธิบายความแตกต่างระหว่างเวิร์กชีตข้อมูลและเวิร์กชีตที่โหลดจากตัวแก้ไข Power Query นั้นเป็นสิ่งสําคัญ แม้ว่าคุณจะมีเพียงสองเวิร์กชีต แต่เวิร์กชีตหนึ่งมีตาราง Excel ที่เรียกว่า Sheet1 และอีกคิวรีหนึ่งสร้างขึ้นโดยการนําเข้าตาราง Excel ที่เรียกว่า Table1 ก็เป็นเรื่องง่ายที่จะสับสน คุณควรเปลี่ยนชื่อเริ่มต้นของแท็บเวิร์กชีตเป็นชื่อที่เหมาะสมกับคุณมากขึ้นเสมอ ตัวอย่างเช่น เปลี่ยนชื่อ Sheet1 เป็น DataTable และ Table1 ไปยัง QueryTable ตอนนี้มีการล้างว่าแท็บใดมีข้อมูลและแท็บใดมีคิวรี
สร้างคิวรี
คุณสามารถสร้างคิวรีจากข้อมูลที่นําเข้าหรือสร้างคิวรีเปล่า
สร้างคิวรีจากข้อมูลที่นําเข้า
นี่เป็นวิธีทั่วไปในการสร้างคิวรี
- นําเข้าข้อมูลบางอย่าง สําหรับข้อมูลเพิ่มเติม ให้ดูที่ นําเข้าข้อมูลจากแหล่งข้อมูลภายนอก
- เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี>
สร้างคิวรีเปล่า
คุณอาจต้องการเริ่มใหม่ตั้งแต่ต้น มีวิธีทำสองวิธี
- เลือก รับ>ข้อมูลจาก>แหล่งข้อมูล>อื่นคิวรีเปล่า
- เลือกตัวแก้ไข Power Query เปิดใช้ข้อมูล>>
ณ จุดนี้ คุณสามารถเพิ่มขั้นตอนและสูตรได้ด้วยตนเอง ถ้าคุณทราบภาษาสูตร Power Query M
หรือคุณสามารถเลือก หน้าแรก แล้วเลือกคําสั่งในกลุ่ม คิวรีใหม่ ให้เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้
- เลือก แหล่งข้อมูลใหม่ เพื่อเพิ่มแหล่งข้อมูล คําสั่งนี้เหมือนกับคําสั่ง รับข้อมูล> ใน Ribbon ของ Excel
- เลือก แหล่งข้อมูลล่าสุด เพื่อเลือกจากแหล่งข้อมูลที่คุณทํางานอยู่ คําสั่งนี้เหมือนกับ>คําสั่งแหล่งข้อมูลล่าสุด ใน Ribbon ของ Excel
- เลือก ใส่ข้อมูล เพื่อใส่ข้อมูลด้วยตนเอง คุณอาจเลือกคําสั่งนี้เพื่อลองใช้ตัวแก้ไข Power Query โดยไม่ขึ้นกับแหล่งข้อมูลภายนอก
โหลดคิวรี
สมมติว่าคิวรีของคุณถูกต้องและไม่มีข้อผิดพลาด คุณสามารถโหลดกลับไปยังเวิร์กชีตหรือตัวแบบข้อมูลได้
โหลดการสอบถามจากตัวแก้ไข Power Query
ในตัวแก้ไข Power Query ให้เลือกทําอย่างใดอย่างหนึ่งต่อไปนี้
เมื่อต้องการโหลดลงในเวิร์กชีต ให้เลือก หน้าแรก>ปิด & โหลด>ปิด & โหลด
เมื่อต้องการโหลดไปยังตัวแบบข้อมูล ให้เลือก Home>Close & Load>Close & Load To
ในกล่องโต้ตอบนําเข้าข้อมูล ให้เลือก เพิ่มข้อมูลนี้ลงในตัวแบบข้อมูล
เคล็ด ลับ บางครั้งคําสั่ง โหลดไปยัง เป็นสีจางหรือถูกปิดใช้งาน ซึ่งอาจเกิดขึ้นได้ในครั้งแรกที่คุณสร้างคิวรีในเวิร์กบุ๊ก ถ้าเกิดเหตุการณ์นี้ขึ้น ให้เลือก ปิด & โหลด ในเวิร์กชีตใหม่ ให้เลือก คิวรีข้อมูล> & แท็บคิวรีการเชื่อมต่อ> คลิกขวาที่คิวรี แล้วเลือก โหลดไปยัง หรือบน Ribbon ตัวแก้ไข Power Query ให้เลือก โหลดคิวรี>ไปยัง
โหลดคิวรีจากบานหน้าต่างคิวรีและการเชื่อมต่อ
ใน Excel คุณอาจต้องการโหลดคิวรีลงในเวิร์กชีตหรือตัวแบบข้อมูลอื่น
- ใน Excel ให้เลือก คิวรีข้อมูล>& การเชื่อมต่อ แล้วเลือกแท็บ คิวรี
- ในรายการของคิวรี ให้ค้นหาคิวรี คลิกขวาที่คิวรี แล้วเลือก โหลดไปยัง กล่องโต้ตอบนําเข้าข้อมูลจะปรากฏขึ้น
- ตัดสินใจว่าคุณต้องการนําเข้าข้อมูลอย่างไร จากนั้นเลือก ตกลง สําหรับข้อมูลเพิ่มเติมเกี่ยวกับการใช้กล่องโต้ตอบนี้ ให้เลือกเครื่องหมายคําถาม (?)
แก้ไขคิวรีจากเวิร์กชีต
มีหลายวิธีในการแก้ไขคิวรีที่โหลดไปยังเวิร์กชีต
แก้ไขคิวรีจากข้อมูลในเวิร์กชีต Excel
- เมื่อต้องการแก้ไขคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี>
แก้ไขคิวรีจากคิวรี & บานหน้าต่างการเชื่อมต่อ
คุณอาจพบว่า คิวรี & บานหน้าต่างการเชื่อมต่อ จะสะดวกกว่าเมื่อคุณมีคิวรีจํานวนมากในเวิร์กบุ๊กเดียวและต้องการค้นหาอย่างรวดเร็ว
- ใน Excel ให้เลือก คิวรีข้อมูล>& การเชื่อมต่อ แล้วเลือกแท็บ คิวรี
- ในรายการของคิวรี ให้ค้นหาคิวรี คลิกขวาที่คิวรี แล้วเลือก แก้ไข
แก้ไขคิวรีจากกล่องโต้ตอบ คุณสมบัติคิวรี
- ใน Excel ให้เลือก>ข้อมูล &แท็บคิวรีการเชื่อมต่อ > คลิกขวาที่คิวรีแล้วเลือก คุณสมบัติ เลือกแท็บ ข้อกําหนด ในกล่องโต้ตอบ คุณสมบัติ แล้วเลือก แก้ไขคิวรี
เคล็ด ลับ ถ้าคุณอยู่ในเวิร์กชีตที่มีคิวรี ให้เลือกคุณสมบัติข้อมูล> เลือกแท็บ ข้อกําหนด ในกล่องโต้ตอบ คุณสมบัติ แล้วเลือก แก้ไขคิวรี
แก้ไขคิวรีของตารางในตัวแบบข้อมูล
โดยทั่วไปตัวแบบข้อมูลจะมีหลายตารางที่จัดเรียงอยู่ในความสัมพันธ์ คุณโหลดคิวรีไปยังตัวแบบข้อมูลโดยใช้คําสั่ง โหลดไปยัง เพื่อแสดงกล่องโต้ตอบ นําเข้าข้อมูล แล้วเลือกกล่องกาเครื่องหมาย เพิ่มข้อมูลนี้ไปยังโหมดข้อมูลl สําหรับข้อมูลเพิ่มเติมเกี่ยวกับตัวแบบข้อมูล ให้ดูที่ ค้นหาว่าแหล่งข้อมูลใดถูกใช้ในตัวแบบข้อมูลของเวิร์กบุ๊ก สร้างตัวแบบข้อมูลใน Excel และ ใช้หลายตารางเพื่อสร้าง PivotTable
เมื่อต้องการเปิดตัวแบบข้อมูล ให้เลือก จัดการ ของ Power Pivot>
ที่ด้านล่างของหน้าต่าง Power Pivot ให้เลือกแท็บเวิร์กชีตของตารางที่คุณต้องการ
ยืนยันว่าตารางที่ถูกต้องแสดงขึ้น ตัวแบบข้อมูลสามารถมีตารางได้หลายตาราง
โปรดสังเกตชื่อของตาราง
เมื่อต้องการปิดหน้าต่าง Power Pivot ให้เลือก ปิดไฟล์> อาจใช้เวลาสักครู่เพื่อเรียกคืนหน่วยความจํา
เลือกการเชื่อมต่อข้อมูล> & แท็บคิวรีคุณสมบัติ> คลิกขวาที่คิวรี แล้วเลือก แก้ไข
เมื่อทําการเปลี่ยนแปลงในตัวแก้ไข Power Query เสร็จแล้ว ให้เลือก ปิดไฟล์>& โหลด
ผลลัพธ์
คิวรีในเวิร์กชีตและตารางในตัวแบบข้อมูลจะถูกอัปเดต
การโหลดคิวรีไปยังตัวแบบข้อมูลใช้เวลานานผิดปกติ
ถ้าคุณสังเกตเห็นว่าการโหลดคิวรีไปยังตัวแบบข้อมูลใช้เวลานานกว่าการโหลดไปยังเวิร์กชีต ให้ตรวจสอบขั้นตอน Power Query ของคุณเพื่อดูว่าคุณกําลังกรองคอลัมน์ข้อความหรือคอลัมน์ที่มีโครงสร้างรายการโดยใช้ตัวดําเนินการ มี หรือไม่ การกระทํานี้ทําให้ Excel แจงนับอีกครั้งตลอดทั้งชุดข้อมูลสําหรับแต่ละแถว นอกจากนี้ Excel ไม่สามารถใช้การดําเนินการแบบหลายเธรดได้อย่างมีประสิทธิภาพ สําหรับการแก้ไขปัญหา ให้ลองใช้ตัวดําเนินการอื่น เช่น เท่ากับ หรือ เริ่มต้นด้วย
Microsoft ทราบถึงปัญหานี้และอยู่ระหว่างการตรวจสอบ
ตั้งค่าตัวเลือกการโหลดคิวรี
คุณสามารถโหลด Power Query:
ไปยังเวิร์กชีต ในตัวแก้ไข Power Query เลือก หน้าแรก>ปิด & โหลด>ปิด & โหลด
ไปยังตัวแบบข้อมูล ในตัวแก้ไข Power Query เลือก หน้าแรก>ปิด & โหลดปิดการโหลด> & LoadTo
ตามค่าเริ่มต้น Power Query จะโหลดคิวรีลงในเวิร์กชีตใหม่เมื่อโหลดคิวรีเดียว และโหลดหลายคิวรีพร้อมกันไปยังตัวแบบข้อมูล คุณสามารถเปลี่ยนลักษณะการทํางานเริ่มต้นสําหรับเวิร์กบุ๊กทั้งหมดของคุณหรือเฉพาะเวิร์กบุ๊กปัจจุบันเท่านั้น เมื่อตั้งค่าตัวเลือกเหล่านี้ Power Query จะไม่เปลี่ยนแปลงผลลัพธ์คิวรีในเวิร์กชีตหรือข้อมูลตัวแบบข้อมูลและคําอธิบายประกอบ
คุณยังสามารถแทนที่การตั้งค่าเริ่มต้นสําหรับคิวรีแบบไดนามิกได้โดยใช้กล่องโต้ตอบ นําเข้า ซึ่งจะแสดงหลังจากที่คุณเลือก ปิด & LoadTo
การตั้งค่าส่วนกลางที่นําไปใช้กับเวิร์กบุ๊กทั้งหมดของคุณ
ในตัวแก้ไข Power Query ให้เลือก ตัวเลือกไฟล์>และการตั้งค่า>ตัวเลือกคิวรี
ในกล่องโต้ตอบ ตัวเลือกคิวรี ทางด้านซ้าย ภายใต้ส่วน ส่วนกลาง ให้เลือก โหลดข้อมูล
ภายใต้ส่วน การตั้งค่าการโหลดคิวรีเริ่มต้น ให้ทําดังต่อไปนี้
- เลือก ใช้การตั้งค่าการโหลดมาตรฐาน
- เลือก ระบุการตั้งค่าการโหลดเริ่มต้นแบบกําหนดเอง แล้วเลือกหรือล้าง โหลดลงในเวิร์กชีต หรือ โหลดไปยังตัวแบบข้อมูล
เคล็ด ลับ ที่ด้านล่างของกล่องโต้ตอบ คุณสามารถเลือก คืนค่าเริ่มต้น เพื่อกลับไปยังการตั้งค่าเริ่มต้นได้อย่างสะดวก
การตั้งค่าเวิร์กบุ๊กที่นําไปใช้กับเวิร์กบุ๊กปัจจุบันเท่านั้น
ในกล่องโต้ตอบ ตัวเลือกคิวรี ทางด้านซ้าย ภายใต้ส่วน เวิร์กบุ๊กปัจจุบัน ให้เลือก โหลดข้อมูล
ให้เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้:
ภายใต้ การตรวจหาชนิด ให้เลือกหรือล้าง ตรวจหาชนิดคอลัมน์และส่วนหัวสําหรับแหล่งข้อมูลที่ไม่มีโครงสร้าง
ลักษณะการทํางานเริ่มต้นคือการตรวจหา ล้างตัวเลือกนี้ถ้าคุณต้องการจัดรูปแบบข้อมูลด้วยตัวคุณเอง
ภายใต้ ความสัมพันธ์ ให้เลือกหรือล้าง สร้างความสัมพันธ์ระหว่างตารางเมื่อเพิ่มลงในตัวแบบข้อมูลเป็นครั้งแรก
ก่อนที่จะโหลดไปยังตัวแบบข้อมูล ลักษณะการทํางานเริ่มต้นคือการค้นหาความสัมพันธ์ที่มีอยู่ระหว่างตาราง เช่น Foreign Key ในฐานข้อมูลเชิงสัมพันธ์ และนําเข้าด้วยข้อมูล ล้างตัวเลือกนี้ถ้าคุณต้องการทําเช่นนี้ด้วยตัวคุณเองภายใต้ ความสัมพันธ์ ให้เลือกหรือล้าง อัปเดตความสัมพันธ์เมื่อรีเฟรชคิวรีที่โหลดไปยังตัวแบบข้อมูล
ลักษณะการทํางานเริ่มต้นคือไม่ให้อัปเดตความสัมพันธ์ เมื่อรีเฟรชคิวรีที่โหลดไปยังตัวแบบข้อมูลแล้ว Power Query จะค้นหาความสัมพันธ์ที่มีอยู่ระหว่างตาราง เช่น Foreign Key ในฐานข้อมูลเชิงสัมพันธ์และทําการอัปเดต ซึ่งอาจเอาความสัมพันธ์ที่สร้างขึ้นด้วยตนเองออกหลังจากนําเข้าข้อมูลหรือเริ่มใช้ความสัมพันธ์ใหม่ อย่างไรก็ตาม หากคุณต้องการทําเช่นนี้ ให้เลือกตัวเลือก
ภายใต้ ข้อมูลพื้นหลัง ให้เลือกหรือล้าง อนุญาตให้ดาวน์โหลดการแสดงตัวอย่างข้อมูลในพื้นหลัง
ลักษณะการทํางานเริ่มต้นคือการดาวน์โหลดการแสดงตัวอย่างข้อมูลในเบื้องหลัง ล้างตัวเลือกนี้ถ้าคุณต้องการดูข้อมูลทั้งหมดทันที