นำเข้าและสร้างข้อมูลใน Excel for Mac (Power Query)

Excel for Mac ผสานรวมเทคโนโลยี Power Query (หรือที่เรียกว่า “รับและแปลง”) เพื่อให้มีความสามารถมากขึ้นในการนําเข้า รีเฟรช และรับรองความถูกต้องของแหล่งข้อมูล, การจัดการแหล่งข้อมูล Power Query, การล้างข้อมูลประจําตัว, การเปลี่ยนตําแหน่งของแหล่งข้อมูลตามไฟล์ และการจัดรูปแบบข้อมูลลงในตารางที่ตรงกับความต้องการของคุณ คุณยังสามารถสร้างคิวรี Power Query โดยใช้ VBA ได้อีกด้วย

Microsoft 365 รุ่นดั้งเดิม

หมายเหตุ: คุณสามารถนำเข้าแหล่งข้อมูลของฐานข้อมูล SQL Server ได้ใน Insiders รุ่นเบต้าเท่านั้น

คุณสามารถนำเข้าข้อมูลไปยัง Excel ได้โดยใช้ Power Query จากแหล่งข้อมูลที่หลากหลาย ได้แก่ เวิร์กบุ๊ก Excel, ข้อความ/CSV, XML, JSON, ฐานข้อมูล SQL Server, รายการ SharePoint Online, OData, ตารางเปล่า และคิวรีเปล่า

เลือกข้อมูล > รับข้อมูล
หากต้องการเลือกแหล่งข้อมูลที่ต้องการ ให้เลือกรับข้อมูล (Power Query)
ในกล่องโต้ตอบเลือกแหล่งข้อมูล ให้เลือกหนึ่งในแหล่งข้อมูลที่พร้อมใช้งาน
เชื่อมต่อกับแหล่งข้อมูล หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการเชื่อมต่อกับแหล่งข้อมูลแต่ละแห่ง ให้ดูที่นําเข้าข้อมูลจากแหล่งข้อมูล
เลือกข้อมูลที่คุณต้องการนําเข้า
โหลดข้อมูลโดยคลิกที่ปุ่มโหลด

ผลลัพธ์

ข้อมูลที่นำเข้าจะปรากฏในแผ่นงานใหม่

ผลลัพธ์ทั่วไปสำหรับคิวรี

ขั้นตอนต่อไป

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

หมายเหตุ: ฟีเจอร์นี้พร้อมใช้งานโดยทั่วไปสำหรับสมาชิก Microsoft 365 ซึ่งใช้ Excel for Mac เวอร์ชัน 16.69 (23010700) หรือใหม่กว่า หากคุณเป็นสมาชิก Microsoft 365 โปรดตรวจสอบให้แน่ใจว่าคุณมี Office เวอร์ชันล่าสุด

กระบวนงาน

เลือกข้อมูล > รับข้อมูล (Power Query)
หากต้องการเปิดตัวแก้ไขคิวรี ให้เลือกเปิดใช้ตัวแก้ไขของ Power Query

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

ผลลัพธ์

ข้อมูลที่นำเข้าใหม่จะปรากฏในแผ่นงานใหม่

ผลลัพธ์ทั่วไปสำหรับคิวรี

คุณสามารถรีเฟรชแหล่งข้อมูลต่อไปนี้ได้ ได้แก่ ไฟล์ SharePoint, รายการ SharePoint, โฟลเดอร์ SharePoint, OData, ไฟล์ข้อความ/CSV, เวิร์กบุ๊ก Excel (.xlsx), ไฟล์ XML และ JSON, ตารางและช่วงภายในเครื่อง และฐานข้อมูล Microsoft SQL Server

รีเฟรชเป็นครั้งแรก

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

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

รีเฟรชในครั้งต่อๆ ไป

วิธีการรีเฟรช:

สำหรับแหล่งข้อมูลทั้งหมดในเวิร์กบุ๊ก ให้เลือกข้อมูล > รีเฟรชทั้งหมด
สำหรับแหล่งข้อมูลที่เฉพาะเจาะจง ให้คลิกขวาที่ตารางคิวรีในแผ่นงาน แล้วเลือกรีเฟรช
สำหรับ PivotTable ให้เลือกเซลล์ใน PivotTable แล้วเลือกวิเคราะห์ PivotTable > รีเฟรชข้อมูล

ครั้งแรกที่คุณเข้าถึง SharePoint, SQL Server, OData หรือแหล่งข้อมูลอื่นๆ ที่ต้องได้รับสิทธิ์ คุณต้องระบุข้อมูลประจำตัวที่เหมาะสม คุณอาจต้องการล้างข้อมูลประจำตัวเพื่อใส่ข้อมูลใหม่

ใส่ข้อมูลประจําตัว

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

หากจําเป็นต้องเข้าสู่ระบบ กล่องโต้ตอบใส่ข้อมูลประจําตัวจะปรากฏขึ้น

ตัวอย่างเช่น

ข้อมูลประจําตัวของ SharePoint:
ข้อมูลประจําตัวของ SQL Server:

ล้างข้อมูลประจําตัว

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

แม้ว่าการเขียนในตัวแก้ไขของ Power Query จะไม่พร้อมใช้งานใน Excel for Mac แต่ VBA ก็สนับสนุนการเขียน Power Query การถ่ายโอนมอดูลโค้ด VBA ในไฟล์จาก Excel for Windows ไปยัง Excel for Mac เป็นกระบวนการสองขั้นตอน เรามีโปรแกรมตัวอย่างให้คุณในช่วงท้ายของส่วนนี้

ขั้นตอนที่หนึ่ง: Excel for Windows

ใน Excel Windows ให้พัฒนาคิวรีโดยใช้ VBA โค้ด VBA ที่ใช้เอนทิตีต่อไปนี้ในโมเดลวัตถุของ Excel ยังทำงานใน Excel for Mac ได้อีกด้วย ได้แก่ วัตถุของ Queries, วัตถุของ WokbookQuery, คุณสมบัติของ Workbook.Queries สําหรับข้อมูลเพิ่มเติม ให้ดูที่แหล่งอ้างอิงเกี่ยวกับ VBA ของ Excel
ใน Excel ตรวจสอบให้แน่ใจว่า Visual Basic Editor เปิดอยู่โดยกด ALT+F11
คลิกขวาที่มอดูล แล้วเลือกส่งออกไฟล์ กล่องโต้ตอบส่งออกจะปรากฏขึ้น
ใส่ชื่อไฟล์ ตรวจสอบให้แน่ใจว่านามสกุลของไฟล์คือ .bas แล้วเลือกบันทึก
อัปโหลดไฟล์ VBA ไปยังบริการออนไลน์เพื่อให้สามารถเข้าถึงไฟล์ได้จาก Mac คุณสามารถใช้ Microsoft OneDrive ได้ สำหรับข้อมูลเพิ่มเติม ให้ดูที่ซิงค์ไฟล์กับ OneDrive บน Mac OS X

ขั้นตอนที่สอง: Excel for Mac

ดาวน์โหลดไฟล์ VBA เป็นไฟล์ภายในเครื่อง ซึ่งเป็นไฟล์ VBA ที่คุณบันทึกไว้ใน "ขั้นตอนที่หนึ่ง: Excel for Windows" และอัปโหลดไปยังบริการออนไลน์
ใน Excel for Mac ให้เลือกเครื่องมือ > แมโคร > Visual Basic Editor หน้าต่าง Visual Basic Editor จะปรากฏขึ้น
คลิกขวาที่วัตถุในหน้าต่างโครงการ แล้วเลือกนำเข้าไฟล์ กล่องโต้ตอบนําเข้าไฟล์จะปรากฏขึ้น
ค้นหาไฟล์ VBA แล้วเลือกเปิด

โค้ดตัวอย่าง

นี่คือโค้ดพื้นฐานบางส่วนที่คุณสามารถปรับใช้ได้ นี่คือคิวรีตัวอย่างที่สร้างรายการที่มีค่าตั้งแต่ 1 ถึง 100

Sub CreateSampleList()
    ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
        "let" & vbCr & vbLf & _
            "Source = {1..100}," & vbCr & vbLf & _
            "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
            "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
        "in" & vbCr & vbLf & _
            "RenamedColumns"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SampleList]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SampleList"
        .Refresh BackgroundQuery:=False
    End With
End Sub

เปิดเวิร์กบุ๊ก Excel
หากคุณได้รับคำเตือนความปลอดภัยเกี่ยวกับการปิดใช้งานการเชื่อมต่อข้อมูลภายนอก ให้เลือกเปิดใช้งานเนื้อหา
หากกล่องโต้ตอบให้สิทธิ์เข้าถึงไฟล์ปรากฏขึ้น ให้เลือกเลือก แล้วเลือกให้สิทธิ์เข้าถึงไปยังโฟลเดอร์ระดับบนสุดที่มีไฟล์แหล่งข้อมูล
เลือกข้อมูล > จากข้อความ (ดั้งเดิม) กล่องโต้ตอบ.ตัวค้นหาจะปรากฏขึ้น
ค้นหาไฟล์ .txt หรือ .csv แล้วเลือกเปิด ตัวช่วยสร้างการนำเข้าข้อความจะปรากฏขึ้นเคล็ดลับ ตรวจสอบบานหน้าต่างแสดงตัวอย่างข้อมูลที่เลือกเพื่อยืนยันตัวเลือกของคุณ
ในหน้าแรก ให้ดำเนินการต่อไปนี้:

ชนิดไฟล์ หากต้องการเลือกชนิดของไฟล์ข้อความ ให้เลือกใช้ตัวคั่นหรือความกว้างคงที่หมายเลขแถว ในเริ่มนำเข้าที่แถว ให้เลือกหมายเลขแถวเพื่อระบุแถวแรกของข้อมูลที่คุณต้องการนำเข้าชุดอักขระ ในที่มาของไฟล์ ให้เลือกชุดอักขระที่ใช้ในไฟล์ข้อความ ในกรณีส่วนใหญ่ คุณสามารถปล่อยให้การตั้งค่านี้เป็นค่าเริ่มต้นได้
ในหน้าที่สอง ให้ดำเนินการต่อไปนี้:ใช้ตัวคั่น หากคุณเลือกใช้ตัวคั่นในหน้าแรก ในหัวข้อตัวคั่น ให้เลือกอักขระตัวคั่นหรือใช้กล่องกาเครื่องหมาย “อื่นๆ” เพื่อใส่อักขระที่ไม่อยู่ในรายการ เลือกมองตัวคั่นที่อยู่ติดกันเป็นตัวเดียวกัน หากข้อมูลของคุณมีตัวคั่นมากกว่าหนึ่งอักขระระหว่างเขตข้อมูล หรือหากข้อมูลของคุณมีตัวคั่นแบบกำหนดเองหลายตัว ในตัวระบุข้อความ ให้เลือกอักขระที่ล้อมรอบค่าในไฟล์ข้อความของคุณ ซึ่งมักจะเป็นอักขระเครื่องหมายคำพูด (")

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

หากต้องการตรวจสอบให้แน่ใจว่าการเชื่อมต่อใช้งานได้ ให้ป้อนข้อมูลบางส่วน แล้วเลือกการเชื่อมต่อ > รีเฟรช

เลือกข้อมูล > จาก ODBC ของ SQL Server กล่องโต้ตอบเชื่อมต่อกับแหล่งข้อมูล ODBC ของ SQL Server จะปรากฎขึ้น
ใส่เซิร์ฟเวอร์ในกล่องชื่อเซิร์ฟเวอร์ และเลือกใส่ฐานข้อมูลในกล่องชื่อฐานข้อมูลหรือไม่ก็ได้ รับข้อมูลนี้จากผู้ดูแลระบบฐานข้อมูล
ในหัวข้อการตรวจสอบสิทธิ์ ให้เลือกวิธีการจากรายการ ได้แก่ ชื่อผู้ใช้/รหัสผ่าน, Kerberos หรือ NTLM
ใส่ข้อมูลประจําตัวในกล่องชื่อผู้ใช้และรหัสผ่าน
เลือกเชื่อมต่อ กล่องโต้ตอบตัวนำทางจะปรากฏขึ้น
ในบานหน้าต่างด้านซ้าย ให้นำทางไปยังตารางที่คุณต้องการ แล้วเลือกตารางดังกล่าว
ยืนยันคําสั่ง SQL ในบานหน้าต่างด้านขวา คุณสามารถเปลี่ยนแปลงคำสั่ง SQL ได้ตามที่เห็นสมควร
หากต้องการแสดงตัวอย่างข้อมูล ให้เลือกเรียกใช้
เมื่อคุณพร้อมแล้ว ให้เลือกส่งกลับข้อมูล กล่องโต้ตอบนําเข้าข้อมูลจะปรากฏขึ้น
เลือกตำแหน่งที่คุณต้องการเพิ่มข้อมูล ไม่ว่าจะเป็นในแผ่นงานที่มีอยู่ ในแผ่นงานใหม่ หรือใน PivotTable
หากต้องการตั้งค่าคุณสมบัติการเชื่อมต่อบนแท็บการใช้งานและคำจำกัดความของกล่องโต้ตอบคุณสมบัติ ให้เลือกคุณสมบัติ หลังจากที่คุณนำเข้าข้อมูลแล้ว คุณยังสามารถเลือกข้อมูล > การเชื่อมต่อ จากนั้นในกล่องโต้ตอบคุณสมบัติการเชื่อมต่อ ให้เลือกคุณสมบัติ
เลือก ตกลง
หากต้องการตรวจสอบให้แน่ใจว่าการเชื่อมต่อใช้งานได้ ให้ป้อนข้อมูลบางส่วน แล้วเลือกข้อมูล > รีเฟรชทั้งหมด

หากคุณต้องการใช้แหล่งข้อมูลภายนอกที่ไม่ใช่ฐานข้อมูล SQL (ตัวอย่างเช่น FileMaker Pro) คุณสามารถใช้โปรแกรมควบคุม Open Database Connectivity (ODBC) ที่ติดตั้งบน Mac ของคุณได้ ข้อมูลเกี่ยวกับโปรแกรมควบคุมมีอยู่ในเว็บเพจนี้ เมื่อติดตั้งโปรแกรมควบคุมสําหรับแหล่งข้อมูลของคุณแล้ว ให้ทําตามขั้นตอนเหล่านี้:

เลือกข้อมูล > จากฐานข้อมูล (Microsoft Query)
เพิ่มแหล่งข้อมูลสำหรับฐานข้อมูลของคุณ แล้วคลิกตกลง
ที่พร้อมท์ข้อมูลประจำตัวของ SQL Server ให้ใส่วิธีการตรวจสอบสิทธิ์ ชื่อผู้ใช้ และรหัสผ่าน
ทางด้านซ้าย ให้เลือกลูกศรถัดจากเซิร์ฟเวอร์เพื่อดูฐานข้อมูล
เลือกลูกศรถัดจากฐานข้อมูลที่คุณต้องการ
เลือกตารางที่คุณต้องการ
หากต้องการแสดงตัวอย่างข้อมูล ให้เลือกเรียกใช้
เมื่อคุณพร้อมแล้ว ให้เลือกส่งกลับข้อมูล
ในกล่องโต้ตอบนำเข้าข้อมูล ให้เลือกตำแหน่งที่คุณต้องการให้ข้อมูลอยู่ ไม่ว่าจะเป็นในแผ่นงานที่มีอยู่ ในแผ่นงานใหม่ หรือใน PivotTable
เลือก ตกลง
หากต้องการตรวจสอบให้แน่ใจว่าการเชื่อมต่อใช้งานได้ ให้ป้อนข้อมูลบางส่วน แล้วเลือกข้อมูล > รีเฟรชทั้งหมด