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