สร้าง โหลด หรือแก้ไขคิวรีใน Excel (Power Query)

ทราบว่าคุณกําลังใช้สภาพแวดล้อมใดอยู่ Power Query จะรวมอยู่ในส่วนติดต่อผู้ใช้ของ Excel โดยเฉพาะเมื่อคุณนําเข้าข้อมูล ทํางานกับการเชื่อมต่อ และแก้ไขตาราง Pivot ตาราง 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 ตอนนี้มีการล้างว่าแท็บใดมีข้อมูลและแท็บใดมีคิวรี

คุณสามารถสร้างคิวรีจากข้อมูลที่นําเข้าหรือสร้างคิวรีเปล่า

สร้างคิวรีจากข้อมูลที่นําเข้า

นี่เป็นวิธีทั่วไปในการสร้างคิวรี

1. นําเข้าข้อมูลบางอย่าง สําหรับข้อมูลเพิ่มเติม ให้ดูที่ นําเข้าข้อมูลจากแหล่งข้อมูลภายนอก

2. เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข

สร้างคิวรีเปล่า

คุณอาจต้องการเริ่มใหม่ตั้งแต่ต้น มีวิธีทำสองวิธี

• เลือก ข้อมูล > รับ > ข้อมูลจากแหล่งข้อมูลอื่น > คิวรีเปล่า

• เลือก ข้อมูล > รับ >เปิดใช้ตัวแก้ไข Power Query

ณ จุดนี้ คุณสามารถเพิ่มขั้นตอนและสูตรได้ด้วยตนเอง ถ้าคุณทราบภาษาสูตร Power Query M

หรือคุณสามารถเลือก หน้าแรก แล้วเลือกคําสั่งในกลุ่ม คิวรีใหม่ ให้เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้

• เลือก แหล่งข้อมูลใหม่ เพื่อเพิ่มแหล่งข้อมูล คําสั่งนี้เหมือนกับคําสั่ง ข้อมูล > รับข้อมูล ใน Ribbon ของ Excel

• เลือก แหล่งข้อมูลล่าสุด เพื่อเลือกจากแหล่งข้อมูลที่คุณทํางานอยู่ คําสั่งนี้เหมือนกับคําสั่ง ข้อมูล > แหล่งข้อมูลล่าสุด ใน Ribbon ของ Excel

• เลือก ใส่ข้อมูล เพื่อใส่ข้อมูลด้วยตนเอง คุณอาจเลือกคําสั่งนี้เพื่อลองใช้ตัวแก้ไข Power Query โดยไม่ขึ้นกับแหล่งข้อมูลภายนอก

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

โหลดการสอบถามจากตัวแก้ไข Power Query

ในตัวแก้ไข Power Query ให้เลือกทําอย่างใดอย่างหนึ่งต่อไปนี้

• เมื่อต้องการโหลดลงในเวิร์กชีต ให้เลือก หน้าแรก > ปิด การโหลด & > ปิดการโหลด &

• เมื่อต้องการโหลดไปยังตัวแบบข้อมูล ให้เลือก หน้าแรก > ปิดการโหลด & > ปิด & โหลดไปยัง
  
  ในกล่องโต้ตอบ นําเข้าข้อมูล ให้เลือก เพิ่มข้อมูลนี้ลงในตัวแบบข้อมูล

เคล็ดลับ   บางครั้งคําสั่ง โหลดไปยัง เป็นสีจางหรือถูกปิดใช้งาน ซึ่งอาจเกิดขึ้นได้ในครั้งแรกที่คุณสร้างคิวรีในเวิร์กบุ๊ก ถ้าเกิดเหตุการณ์นี้ขึ้น ให้เลือก ปิด & โหลด ในเวิร์กชีตใหม่ ให้เลือก คิวรี > ข้อมูล& Connections > แท็บ คิวรี คลิกขวาที่คิวรี แล้วเลือก โหลดไปยัง หรือ บน Ribbon ตัวแก้ไข Power Query ให้เลือก คิวรี > โหลดไปยัง

โหลดคิวรีจากบานหน้าต่างคิวรีและ Connections 

ใน Excel คุณอาจต้องการโหลดคิวรีลงในเวิร์กชีตหรือตัวแบบข้อมูลอื่น

1. ใน Excel ให้เลือก > คิวรี & Connections> แล้วเลือกแท็บ คิวรี

2. ในรายการของคิวรี ให้ค้นหาคิวรี คลิกขวาที่คิวรี แล้วเลือก โหลดไปยัง กล่องโต้ตอบนําเข้าข้อมูลจะปรากฏขึ้น

3. ตัดสินใจว่าคุณต้องการนําเข้าข้อมูลอย่างไร จากนั้นเลือก ตกลง สําหรับข้อมูลเพิ่มเติมเกี่ยวกับการใช้กล่องโต้ตอบนี้ ให้เลือกเครื่องหมายคําถาม (?)

มีหลายวิธีในการแก้ไขคิวรีที่โหลดไปยังเวิร์กชีต

แก้ไขคิวรีจากข้อมูลในเวิร์กชีต Excel

• เมื่อต้องการแก้ไขคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข

แก้ไขคิวรีจากบานหน้าต่าง & Connections คิวรี

คุณอาจพบว่าคิวรี & Connections บานหน้าต่างจะสะดวกกว่าเมื่อคุณมีคิวรีจํานวนมากในเวิร์กบุ๊กเดียวและต้องการค้นหาอย่างรวดเร็ว

1. ใน Excel ให้เลือก > คิวรี & Connections> แล้วเลือกแท็บ คิวรี

2. ในรายการของคิวรี ให้ค้นหาคิวรี คลิกขวาที่คิวรี แล้วเลือก แก้ไข

แก้ไขคิวรีจากกล่องโต้ตอบ คุณสมบัติคิวรี

• ใน Excel ให้เลือก ข้อมูล > ข้อมูล & Connections > แท็บ คิวรี คลิกขวาที่คิวรีและเลือก คุณสมบัติ เลือกแท็บ ข้อกําหนด ในกล่องโต้ตอบ คุณสมบัติ แล้วเลือก แก้ไขคิวรี

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

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

1. เมื่อต้องการเปิดตัวแบบข้อมูล ให้เลือก Power Pivot > จัดการ

2. ที่ด้านล่างของหน้าต่าง Power Pivot ให้เลือกแท็บเวิร์กชีตของตารางที่คุณต้องการ
   
   ยืนยันว่าตารางที่ถูกต้องแสดงขึ้น ตัวแบบข้อมูลสามารถมีตารางได้หลายตาราง

3. โปรดสังเกตชื่อของตาราง

4. เมื่อต้องการปิดหน้าต่าง Power Pivot ให้เลือก ไฟล์ > ปิด อาจใช้เวลาสักครู่เพื่อเรียกคืนหน่วยความจํา

5. เลือก ข้อมูล > Connections & คุณสมบัติ > แท็บ คิวรี คลิกขวาที่คิวรี แล้วเลือก แก้ไข

6. เมื่อทําการเปลี่ยนแปลงในตัวแก้ไข Power Query เสร็จแล้ว ให้เลือก ไฟล์ > ปิด & โหลด

ผลลัพธ์

คิวรีในเวิร์กชีตและตารางในตัวแบบข้อมูลจะถูกอัปเดต

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

Microsoft ทราบถึงปัญหานี้และอยู่ระหว่างการตรวจสอบ

คุณสามารถโหลด Power Query:

• ไปยังเวิร์กชีต ในตัวแก้ไข Power Query เลือก หน้าแรก > ปิด > โหลด &ปิดการโหลด &

• ไปยังตัวแบบข้อมูล ในตัวแก้ไข Power Query เลือก หน้าแรก > ปิด > โหลด &ปิด & โหลดไปยัง 
  
  ตามค่าเริ่มต้น Power Query จะโหลดคิวรีลงในเวิร์กชีตใหม่เมื่อโหลดคิวรีเดียว และโหลดหลายคิวรีพร้อมกันไปยังตัวแบบข้อมูล  คุณสามารถเปลี่ยนลักษณะการทํางานเริ่มต้นสําหรับเวิร์กบุ๊กทั้งหมดของคุณหรือเฉพาะเวิร์กบุ๊กปัจจุบันเท่านั้น เมื่อตั้งค่าตัวเลือกเหล่านี้ Power Query จะไม่เปลี่ยนแปลงผลลัพธ์คิวรีในเวิร์กชีตหรือข้อมูลตัวแบบข้อมูลและคําอธิบายประกอบ
  
  คุณยังสามารถแทนที่การตั้งค่าเริ่มต้นสําหรับคิวรีแบบไดนามิกได้โดยใช้กล่องโต้ตอบ นําเข้า ซึ่งจะแสดงหลังจากที่คุณเลือก ปิด & โหลด ไปยัง

การตั้งค่าส่วนกลางที่นําไปใช้กับเวิร์กบุ๊กทั้งหมดของคุณ

1. ใน power ตัวแก้ไขคิวรี ให้เลือก ตัวเลือก> ไฟล์ และการตั้งค่า > ตัวเลือกคิวรี

2. ในกล่องโต้ตอบ ตัวเลือกคิวรี ทางด้านซ้าย ภายใต้ส่วน ส่วนกลาง ให้เลือก โหลดข้อมูล

3. ภายใต้ส่วน การตั้งค่าการโหลดคิวรีเริ่มต้น ให้ทําดังต่อไปนี้

   • เลือก ใช้การตั้งค่าการโหลดมาตรฐาน 

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

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

การตั้งค่าเวิร์กบุ๊กที่นําไปใช้กับเวิร์กบุ๊กปัจจุบันเท่านั้น

1. ในกล่องโต้ตอบ ตัวเลือกคิวรี ทางด้านซ้าย ภายใต้ส่วน เวิร์กบุ๊กปัจจุบัน ให้เลือก โหลดข้อมูล

2. ให้เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้:

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

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

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

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

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