หมายเหตุ: บทความนี้ได้ทำหน้าที่จนเสร็จสิ้นแล้ว และกำลังจะเลิกใช้ในไม่ช้า เมื่อต้องการป้องกันปัญหา "ไม่พบหน้า" เรากำลังนำลิงก์ที่เราทราบออก ถ้าคุณได้สร้างลิงก์ไปยังหน้านี้ โปรดนำออกแล้วเราจะเชื่อมต่อเว็บเข้าด้วยกัน
หมายเหตุ: Power Query เรียกว่า รับและแปลง ใน Excel 2016 ข้อมูลที่มีอยู่ต่อไปนี้นำไปใช้กับทั้งสองรายการ เมื่อต้องการเรียนรู้เพิ่มเติม ให้ดู รับ & แปลงใน Excel 2016
เมื่อต้องการสร้างสูตร Power Query ใน Excel คุณสามารถใช้แถบสูตรตัวแก้ไขคิวรี หรือ ตัวแก้ไขขั้นสูง ตัวแก้ไขคิวรีเป็นเครื่องมือที่รวมอยู่ใน Power Query ที่ช่วยให้คุณสร้างคิวรีข้อมูลและสูตรใน Power Query ได้ ภาษาที่ใช้ในการสร้างสูตรเหล่านั้นคือ ภาษาสูตรของ Power Query มีสูตร Power Query หลายสูตรที่คุณสามารถใช้เพื่อค้นหา รวม และปรับปรุงข้อมูลได้ เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับสูตร Power Query แบบเต็ม ให้ดูที่ ประเภทสูตร Power Query
ลองมาสร้างสูตรอย่างง่าย แล้วตามด้วยการสร้างสูตรขั้นสูง
สร้างสูตรอย่างง่าย
สำหรับตัวอย่างสูตรอย่างง่าย ให้แปลงค่าข้อความเป็นตัวพิมพ์ที่เหมาะสมโดยใช้สูตร Text.Proper()
-
บนแท็บ Ribbon ของ POWER QUERY ให้เลือก จากแหล่งข้อมูลอื่น > คิวรีที่ว่างเปล่า
-
ใน แถบสูตรตัวแก้ไขคิวรี ให้พิมพ์ = Text.Proper("text value") แล้วกด Enter หรือเลือกไอคอน Enter
-
Power Query จะแสดงผลลัพธ์ในบานหน้าต่างผลลัพธ์ของสูตร
-
เมื่อต้องการดูผลลัพธ์ในเวิร์กชีต Excel ให้เลือก ปิดและโหลด
ผลลัพธ์ในเวิร์กชีตจะมีลักษณะดังนี้
คุณยังสามารถสร้างสูตรคิวรีขั้นสูงใน ตัวแก้ไขคิวรี
สร้างสูตรขั้นสูง
สําหรับตัวอย่างสูตรขั้นสูง ให้แปลงข้อความในคอลัมน์ให้เป็นตัวพิมพ์ที่เหมาะสมโดยใช้การผสมสูตร คุณสามารถใช้ ภาษาสูตรของ Power Query เพื่อรวมสูตรหลายสูตรลงในขั้นตอนคิวรีที่มีผลลัพธ์ของชุดข้อมูล คุณสามารถนําเข้าผลลัพธ์ลงในเวิร์กชีต Excel ได้
หมายเหตุ: หัวข้อนี้เป็นบทนําสู่สูตร Power Query ขั้นสูง เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับสูตร Power Query ให้ดูที่ เรียนรู้เกี่ยวกับสูตร Power Query
ตัวอย่างเช่น สมมติว่าคุณมีตาราง Excel ที่มีชื่อผลิตภัณฑ์ที่คุณต้องการแปลงเป็นตัวพิมพ์ที่เหมาะสม
ตารางต้นฉบับมีลักษณะดังนี้
และคุณต้องการทำให้ตารางมีลักษณะดังนี้
ลองมาดูขั้นตอนของสูตรคิวรีที่จะเปลี่ยนตารางต้นฉบับ เพื่อให้ค่าในคอลัมน์ ProductName เป็นตัวพิมพ์ที่เหมาะสม
ตัวอย่างของคิวรีขั้นสูงที่ใช้ตัวแก้ไขขั้นสูง
เมื่อต้องการล้างข้อมูลตารางต้นฉบับ ให้คุณใช้ ตัวแก้ไขขั้นสูง เพื่อสร้างขั้นตอนของสูตรคิวรี มาสร้างขั้นตอนของสูตรคิวรีแต่ละขั้นตอนเพื่อแสดงวิธีการสร้างคิวรีขั้นสูง ขั้นตอนของสูตรคิวรีที่สมบูรณ์จะแสดงอยู่ด้านล่าง เมื่อคุณสร้างคิวรีขั้นสูง คุณทําตามขั้นตอนนี้:
-
สร้างชุดของขั้นตอนสูตรคิวรีที่เริ่มต้นด้วยคําสั่ง let โปรดทราบว่า ภาษาสูตรของ Power Query เป็นแบบตรงตามตัวพิมพ์ใหญ่-เล็ก
-
ขั้นตอนของสูตรคิวรีแต่ละขั้นตอนจะสร้างขึ้นจากขั้นตอนก่อนหน้าโดยการอ้างอิงขั้นตอนตามชื่อ
-
แสดงผลลัพธ์ขั้นตอนของสูตรคิวรีโดยใช้คําสั่ง in โดยทั่วไปแล้ว ขั้นตอนคิวรีสุดท้ายจะถูกใช้เป็นผลลัพธ์ในชุดข้อมูลสุดท้าย
ขั้นตอนที่ 1 - เปิดตัวแก้ไขขั้นสูง
-
บนแท็บ Ribbon ของ POWER QUERY ให้เลือก จากแหล่งข้อมูลอื่น > คิวรีที่ว่างเปล่า
-
ใน ตัวแก้ไขคิวรี ให้เลือก ตัวแก้ไขขั้นสูง
-
คุณจะเห็น ตัวแก้ไขขั้นสูง
ขั้นตอนที่ 2 - กำหนดแหล่งข้อมูลต้นฉบับ
ใน ตัวแก้ไขขั้นสูง:
-
ใช้คําสั่ง let ที่กําหนด สูตร Source = Excel.CurrentWorkbook() วิธีนี้จะใช้ตาราง Excel เป็นแหล่งข้อมูล สําหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตร Excel.CurrentWorkbook() ให้ดูที่ Excel.CurrentWorkbook
-
กำหนด Source ในผลลัพธ์ in
let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content] in Source
-
คิวรีขั้นสูงของคุณจะมีลักษณะดังนี้ใน ตัวแก้ไขขั้นสูง
-
เมื่อต้องการดูผลลัพธ์ในเวิร์กชีตให้ทำดังนี้
-
คลิก เสร็จสิ้น
-
บน Ribbon ของ ตัวแก้ไขคิวรี ให้คลิก ใช้และปิด
-
ผลลัพธ์ในเวิร์กชีตจะมีลักษณะดังนี้
ขั้นตอนที่ 3: เลื่อนระดับแถวแรกเป็นส่วนหัว
เมื่อต้องการแปลงค่าในคอลัมน์ ProductName เป็นข้อความที่เหมาะสม ก่อนอื่นคุณต้องเลื่อนระดับแถวแรกให้เป็นส่วนหัวของคอลัมน์ คุณทําสิ่งนี้ในตัว แก้ไขขั้นสูง:
-
เพิ่มสูตร #"First Row as Header" = Table.PromoteHeaders() ลงในขั้นตอนของสูตรคิวรีและอ้างอิงแหล่งข้อมูลเป็นแหล่งข้อมูล สําหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตร Table.PromoteHeaders() ให้ดูที่ Table.PromoteHeaders
-
กำหนด #"First Row as Header" ให้กับผลลัพธ์ in
let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source) in #"First Row as Header"
ผลลัพธ์ในเวิร์กชีตจะมีลักษณะดังนี้
ขั้นตอนที่ 4 - เปลี่ยนค่าแต่ละค่าในคอลัมน์ให้เป็นตัวพิมพ์ที่เหมาะสม
เมื่อต้องการแปลงค่าแต่ละค่าของคอลัมน์ ProductName เป็นข้อความที่เหมาะสม ให้คุณใช้ Table.TransformColumns() และอ้างอิงขั้นตอนของสูตรคิวรี "First Row as Header" คุณทําสิ่งนี้ในตัว แก้ไขขั้นสูง:
-
เพิ่มสูตร #"Capitalized Each Word" = Table.TransformColumns() ลงในขั้นตอนของสูตรคิวรีและอ้างอิง #"First Row as Header" เป็นแหล่งข้อมูล สําหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตร Table.TransformColumns() ให้ดูที่ Table.TransformColumns
-
กำหนด #"Capitalized Each Word" ในผลลัพธ์ in
let
Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
#"First Row as Header" = Table.PromoteHeaders(Source),
#"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
in
#"Capitalized Each Word"
ผลลัพธ์สุดท้ายจะเปลี่ยนแปลงค่าแต่ละค่าในคอลัมน์ ProductName ให้เป็นตัวพิมพ์ที่เหมาะสม และผลลัพธ์ในเวิร์กชีตจะมีลักษณะดังนี้
ด้วย ภาษาสูตรของ Power Query คุณสามารถสร้างคิวรีข้อมูลแบบง่ายไปจนถึงขั้นสูงเพื่อค้นหา รวม และปรับปรุงข้อมูลได้ เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับ Power Query ให้ดูที่ วิธีใช้ Microsoft Power Query สําหรับ Excel