ข้ามไปที่เนื้อหาหลัก
การสนับสนุน
ลงชื่อเข้าใช้

สร้างสูตร Power Query ใน Excel

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

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

ส่วนต่างๆ ของตัวแก้ไขคิวรี

  1. Ribbon ตัวแก้ไข Power Query ที่คุณใช้ในรูปร่างข้อมูลของคุณ

  2. บานหน้าต่างคิวรีที่คุณใช้เพื่อค้นหาแหล่งข้อมูลและตาราง

  3. เมนูบริบทที่เป็นทางลัดไปยังการสั่งที่สะดวกใน Ribbon

  4. แสดงตัวอย่างข้อมูลที่แสดงผลลัพธ์ของขั้นตอนที่ใช้กับข้อมูล

  5. บานหน้าต่างการตั้งค่าคิวรีที่แสดงรายการคุณสมบัติและขั้นตอนแต่ละรายการในคิวรี

เบื้องหลัง แต่ละขั้นตอนในคิวรีจะยึดตามสูตรที่สามารถมองเห็นได้ในแถบสูตร

ตัวอย่างสูตรในตัวแก้ไขคิวรี

อาจมีบางครั้งที่คุณต้องการปรับเปลี่ยนหรือสร้างสูตร สูตรใช้ภาษาสูตรของ Power Query ซึ่งคุณสามารถใช้ในการสร้างนิพจน์ทั้งแบบง่ายและซับซ้อนได้ For more information about syntax, arguments, remarks, functions, and examples, see Power Query M formula language.

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

เบราว์เซอร์ของคุณไม่สนับสนุนวิดีโอ

กระบวนงาน

  1. เมื่อต้องการนําเข้าข้อมูล>ข้อมูลจากเว็บ ป้อน "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" ในกล่องURL แล้วเลือกตกลง

  2. ในกล่องโต้ตอบ ตัว นําทาง ให้เลือกตาราง ผลลัพธ์ [แก้ไข] ทางด้านซ้าย แล้วเลือก แปลง ข้อมูล ที่ด้านล่าง ตัวแก้ไข Power Query จะปรากฏขึ้น

  3. เมื่อต้องการเปลี่ยนชื่อคิวรีเริ่มต้น ในบานหน้าต่าง คิวรี การตั้งค่าภายใต้ คุณสมบัติให้ลบ"ผลลัพธ์ [แก้ไข]" จากนั้นใส่ "UEFA UEFA"

  4. เมื่อต้องการเอาคอลัมน์ที่ไม่ต้องการออก ให้เลือกคอลัมน์แรก คอลัมน์ที่สี่ และคอลัมน์ที่ห้าแล้วเลือก>เอาคอลัมน์>เอาคอลัมน์อื่นออก

  5. เมื่อต้องการเอาค่าที่ไม่ต้องการออกให้เลือก คอลัมน์1 >หน้าแรก>แทนที่ค่า ใส่ "รายละเอียด" ในกล่อง ค่าที่จะค้นหาแล้วเลือกตกลง

  6. เมื่อต้องการเอาแถวที่มีข้อความว่า "ปี" ในแถวเหล่านั้นออก ให้เลือกลูกศรตัวกรองใน คอลัมน์ 1ล้างกล่องกาเครื่องหมายที่อยู่ถัดจาก "ปี" แล้วเลือกตกลง

  7. เมื่อต้องการเปลี่ยนชื่อส่วนหัวของคอลัมน์ ให้ดับเบิลคลิกที่แต่ละส่วนหัว แล้วเปลี่ยน "คอลัมน์1" เป็น "ปี" "คอลัมน์4" เป็น "ผู้ชนะ" และ "คอลัมน์5" เป็น "คะแนนสุดท้าย"

  8. เมื่อต้องการบันทึกคิวรีให้เลือก หน้าแรก>ปิด& โหลด

ผลลัพธ์

ผลลัพธ์ของบทสรุป - แถวสองสามแถวแรก

ตารางต่อไปนี้เป็นข้อมูลสรุปของแต่ละขั้นตอนที่ปรับใช้และสูตรที่สอดคล้องกัน

ขั้นตอนคิวรีและงาน

สูตร

แหล่งที่มา

เชื่อมต่อไปยังแหล่งข้อมูลเว็บ

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

การนำทาง

เลือกตารางเพื่อเชื่อมต่อ

=Source{2}[Data]

ชนิดที่เปลี่ยนแปลง

เปลี่ยนชนิดข้อมูล (ซึ่ง Power Query จะเปลี่ยนแปลงโดยอัตโนมัติ)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

เอาคอลัมน์อื่นออกแล้ว

เอาคอลัมน์อื่นออกเพื่อแสดงเฉพาะคอลัมน์ที่สนใจเท่านั้น

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

ค่าที่ถูกแทนที่

แทนที่ค่าเพื่อล้างข้อมูลค่าในคอลัมน์ที่เลือก

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

แถวที่กรองแล้ว

กรองค่าในคอลัมน์

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

คอลัมน์ที่เปลี่ยนชื่อ

ส่วนหัวของคอลัมน์ที่เปลี่ยนแปลงเพื่อให้สื่อความหมาย

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

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

แสดงหรือซ่อนแถบสูตร

แถบสูตรจะแสดงตามค่าเริ่มต้น แต่ถ้าไม่ปรากฏขึ้น คุณสามารถแสดงอีกครั้งได้

  • เลือกมุมมอง > เค้าโครง > แถบสูตร

การคลาดสีสูตรในแถบสูตร

  1. เมื่อต้องการเปิดคิวรี ให้ค้นหาที่โหลดจากตัวแก้ไข Power Query ก่อนหน้านี้ เลือกเซลล์ในข้อมูล แล้วเลือกคิวรี>แก้ไข For more information see Create, load, or edit a query in Excel.

  2. ในบานหน้าต่างคิวการตั้งค่ารีภายใต้ ขั้นตอนที่ปรับใช้ ให้เลือกขั้นตอนที่คุณต้องการแก้ไข

  3. ในแถบสูตร ให้ค้นหาและเปลี่ยนค่าพารามิเตอร์ แล้วเลือกไอคอน ไอคอน Enter ทางด้านซ้ายของแถบสูตรใน Power Query หรือกด Enter ตัวอย่างเช่น เปลี่ยนสูตรนี้เพื่อรักษาคอลัมน์2: ก่อน

    : = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    หลังจาก:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. เลือกไอคอน ไอคอน Enter ทางด้านซ้ายของแถบสูตรใน Power Query Enter หรือกด Enter เพื่อดูผลลัพธ์ใหม่ที่แสดงในการแสดงตัวอย่างข้อมูล

  5. เมื่อต้องการดูผลลัพธ์ในเวิร์กชีต Excel เวิร์กชีต เลือกหน้าแรก>ปิด& โหลด

สร้างสูตรในแถบสูตร

ตัวอย่างเช่น สูตรอย่างง่าย ลองแปลงค่าข้อความให้เป็นตัวพิมพ์ที่เหมาะสมโดยใช้ฟังก์ชันText.Proper

  1. เมื่อต้องการเปิดคิวรีเปล่า Excel ให้เลือกข้อมูล>รับ>จากแหล่งข้อมูล>คิวรีเปล่า For more information see Create, load, or edit a query in Excel.

  2. ในแถบสูตร ให้=Text.Proper("text value")สูตร แล้วเลือกไอคอน ไอคอน Enter ทางด้านซ้ายของแถบสูตรใน Power Query หรือกด Enter

    ผลลัพธ์จะแสดงใน แสดงตัวอย่างข้อมูล

  3. เมื่อต้องการดูผลลัพธ์ในเวิร์กชีต Excel เวิร์กชีต เลือกหน้าแรก>ปิด& โหลด

ผลลัพธ์:

Text.Proper

 เมื่อคุณสร้างสูตร Power Query จะตรวจสอบความถูกต้องของไวยากรณ์สูตร However, when you insert, reorder, or delete an intermediate step in a query you might mightly break a query.  ตรวจสอบผลลัพธ์ในการแสดงตัวอย่างข้อมูลเสมอ

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

แก้ไขสูตรโดยใช้กล่องโต้ตอบ

วิธีนี้จะใช้กล่องโต้ตอบที่แตกต่างกันโดยขึ้นอยู่กับขั้นตอน คุณไม่ต้องการทราบไวยากรณ์ของสูตร

  1. เมื่อต้องการเปิดคิวรี ให้ค้นหาที่โหลดจากตัวแก้ไข Power Query ก่อนหน้านี้ เลือกเซลล์ในข้อมูล แล้วเลือกคิวรี>แก้ไข For more information see Create, load, or edit a query in Excel.

  2. ในบานหน้าต่างคิวรี การตั้งค่าภายใต้ ขั้นตอนที่ปรับใช้ ให้เลือกไอคอนแก้ไข การตั้งค่า ไอคอนการตั้งค่า ของขั้นตอนที่คุณต้องการแก้ไขหรือคลิกขวาที่ขั้นตอน แล้วเลือก แก้ไขการตั้งค่า

  3. ในกล่องโต้ตอบ ให้เปลี่ยนแปลง แล้วเลือกตกลง

แทรกขั้นตอน

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

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

  2. เลือก ไอคอน เพิ่ม ไอคอนฟังก์ชัน ขั้นตอน ทางด้านซ้ายของแถบสูตร หรือ คลิกขวาที่ขั้นตอน แล้วเลือก แทรก ขั้นตอนหลังจากสูตรใหม่จะถูกสร้างขึ้นในรูปแบบ := <nameOfTheStepToReference>

    เช่น =Production.WorkOrder

  3. พิมพ์สูตรใหม่โดยใช้รูปแบบ:

    =Class.Function(ReferenceStep[,otherparameters])

    ตัวอย่างเช่น สมมติว่าคุณมีตารางที่มีคอลัมน์ เพศ และคุณต้องการเพิ่มคอลัมน์ด้วยค่า "Ms" หรือ "นาย" ทั้งนี้ขึ้นอยู่กับเพศของบุคคล สูตรจะเป็น:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

ตัวอย่างสูตร

จัดล.กล.ขั้นตอนใหม่

  • ในบานหน้าต่างคิวการตั้งค่ารีภายใต้ ขั้นตอนที่ใช้ ให้คลิกขวาที่ขั้นตอน แล้วเลือกย้ายขึ้นหรือ ย้ายลง

ลบขั้นตอน

  • เลือกไอคอน ลบขั้นตอน ลบ ที่ด้านซ้ายของขั้นตอน หรือคลิกขวาที่ขั้นตอน แล้วเลือก ลบหรือลบจนกว่าจะสิ้นสุด ไอคอน ลบขั้นตอน ลบจะยังอยู่ทางด้านซ้ายของแถบสูตร

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

ตัวอย่างเช่น คุณมีตารางExcelชื่อ Orders ที่มีคอลัมน์ ProductName ที่คุณต้องการแปลงเป็นตัวพิมพ์ที่เหมาะสม 

ก่อน:

ก่อน

หลัง:

ขั้นตอนที่ 4 - ผลลัพธ์

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

let  
    Source = Text.Proper("hello world")
in  
    Source  

คุณจะเห็นว่าแต่ละขั้นตอนสร้างขึ้นจากขั้นตอนก่อนหน้าโดยการอ้างถึงขั้นตอนตามชื่อ ในฐานะตัวเตือน ภาษาสูตรของ Power Query จะตรงตามตัวพิมพ์ใหญ่-เล็ก

ขั้นตอนที่ 1: เปิดตัวแก้ไขขั้นสูง

  1. In Excel, select Data> Get Data > Other Sources > Blank Query. For more information see Create, load, or edit a query in Excel.

  2. ใน ตัวแก้ไข Power Query ให้เลือก หน้าแรก>ตัวแก้ไขขั้นสูงซึ่งจะเปิดขึ้นพร้อมกับเทมเพลตของ let expression

ตัวแก้ไขขั้นสูง 2

ขั้นที่ 2: กําหนดแหล่งข้อมูล

  1. สร้างให้นิพจน์โดยใช้Excel ฟังก์ชัน CurrentWorkbookดังนี้:



    let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in
        Source

    ขั้นตอนที่ 1 - ตัวแก้ไขขั้นสูง

  2. เมื่อต้องการโหลดคิวรีลงในเวิร์กชีตให้เลือกเสร็จสิ้น แล้วเลือก>ปิด&โหลด>ปิด&โหลด

ผลลัพธ์:

ขั้นตอนที่ 1 - ผลลัพธ์

ขั้นที่ 3: เลื่อนระดับแถวแรกเป็นส่วนหัว

  1. เมื่อต้องการเปิดคิวรี จากเวิร์กชีต ให้เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี>แก้ไข For more information see Create, load, or edit a query in Excel (Power Query).

  2. ใน ตัวแก้ไข Power Queryให้เลือกหน้าแรก>ตัวแก้ไขขั้นสูง ซึ่งจะเปิดขึ้นพร้อมกับข้อความที่คุณสร้างในขั้นตอนที่ 2: กําหนดแหล่งข้อมูล

  3. ในนิพจน์letให้เพิ่ม #"First Row as Header" และTable.PromoteHeadersดังนี้:

    let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

  4. เมื่อต้องการโหลดคิวรีลงในเวิร์กชีตให้เลือกเสร็จสิ้น แล้วเลือก>ปิด&โหลด>ปิด&โหลด

ผลลัพธ์:

ขั้นตอนที่ 3 - ผลลัพธ์

ขั้นที่ 4: เปลี่ยนแต่ละค่าในคอลัมน์เป็นตัวพิมพ์ที่เหมาะสม

  1. เมื่อต้องการเปิดคิวรี จากเวิร์กชีต ให้เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี>แก้ไข For more information see Create, load, or edit a query in Excel.

  2. ใน ตัวแก้ไข Power Query ให้เลือก หน้าแรก>ตัวแก้ไขขั้นสูง ซึ่งจะเปิดขึ้นพร้อมกับข้อความที่คุณสร้างใน ขั้นตอนที่ 3: เลื่อนระดับแถวแรกเป็นส่วนหัว

  3. ในนิพจน์letให้แปลงค่าคอลัมน์ ProductName แต่ละค่าให้เป็นข้อความที่เหมาะสมโดยใช้ฟังก์ชันTable.TransformColumnsซึ่งอ้างอิงขั้นตอนของสูตรคิวรี "First Row as Header" ก่อนหน้า โดยเพิ่ม #"Capitalized Each Word" ลงในแหล่งข้อมูล จากนั้นกําหนด #"Capitalized Each Word"เป็นผลลัพธ์

    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"

  4. เมื่อต้องการโหลดคิวรีลงในเวิร์กชีตให้เลือกเสร็จสิ้น แล้วเลือก>ปิด&โหลด>ปิด&โหลด

ผลลัพธ์:

ขั้นตอนที่ 4 - ผลลัพธ์

คุณสามารถควบคุมลักษณะการเกิดขึ้นของแถบสูตรในตัวแก้ไข Power Query ของเวิร์กบุ๊กทั้งหมดได้

แสดงหรือซ่อนแถบสูตร

  1. เลือก>ไฟล์ การตั้งค่า>ตัวเลือกคิวรี

  2. ในบานหน้าต่างด้านซ้าย ภายใต้ส่วนกลางให้เลือก ตัวแก้ไขPower Query

  3. ในบานหน้าต่างด้านขวา ภายใต้ เค้าโครงให้เลือกหรือล้าง แสดงแถบสูตร

เปิดหรือปิด M IntelliSense

  1. เลือก>ไฟล์ การตั้งค่า>ตัวเลือกคิวรี

  2. ในบานหน้าต่างด้านซ้าย ภายใต้ส่วนกลางให้เลือก ตัวแก้ไขPower Query

  3. ในบานหน้าต่างด้านขวา ภายใต้ สูตร ให้เลือกหรือล้าง เปิดใช้งาน M IntelliSense ในแถบสูตร ตัวแก้ไขขั้นสูง และกล่องโต้ตอบคอลัมน์แบบปรับแต่งเอง

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

ดูเพิ่มเติม

ความช่วยเหลือ Power Query สำหรับ Excel

สร้างและเรียกใช้ฟังก์ชันแบบปรับแต่งเอง

การใช้รายการ ขั้นตอนที่ใช้ (docs.com)

การใช้ฟังก์ชันแบบปรับแต่งเอง (docs.com)

สูตร M ของ Power Query (docs.com)

การจัดการกับข้อผิดพลาด (docs.com)

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

ขยายทักษะ Office ของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

×