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

นําเข้าแหล่งข้อมูล

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

1. เลือก รับข้อมูล>

   

2. หากต้องการเลือกแหล่งข้อมูลที่ต้องการ ให้เลือกรับข้อมูล (Power Query)

3. ในกล่องโต้ตอบ เลือกแหล่งข้อมูล ให้เลือกหนึ่งในแหล่งข้อมูลที่พร้อมใช้งาน

   

4. เชื่อมต่อกับแหล่งข้อมูล เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการเชื่อมต่อกับแต่ละแหล่งข้อมูล ให้ดูที่ นําเข้าข้อมูลจากแหล่งข้อมูล

5. เลือกข้อมูลที่คุณต้องการนําเข้า

6. โหลดข้อมูลโดยคลิกที่ปุ่ม โหลด

ผลลัพธ์

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

ขั้นตอนถัดไป

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

จัดรูปแบบข้อมูลด้วยตัวแก้ไขของ Power Query

ขั้น ตอน

1. เลือก รับข้อมูล>(Power Query)

2. เมื่อต้องการเปิดตัวแก้ไขคิวรี ให้เลือก เปิดใช้ตัวแก้ไข Power Query

   

   เคล็ดลับ

   คุณยังสามารถเข้าถึงตัวแก้ไขคิวรีได้โดยเลือกรับข้อมูล (Power Query) เลือกแหล่งข้อมูล แล้วคลิกถัดไป

3. จัดรูปแบบและแปลงข้อมูลของคุณโดยใช้ตัวแก้ไขคิวรีในแบบเดียวกับที่คุณดำเนินการใน Excel for Windows

   

   สำหรับข้อมูลเพิ่มเติม ให้ดูที่ความช่วยเหลือเกี่ยวกับ Power Query สำหรับ Excel

4. เมื่อคุณทําเสร็จแล้ว ให้เลือก หน้าแรก>ปิด & โหลด

ผลลัพธ์

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

รีเฟรชแหล่งข้อมูล

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  

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

  

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

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

เขียนและถ่ายโอนโค้ด VBA ของ Power Query

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

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

1. ใน Excel Windows ให้พัฒนาคิวรีโดยใช้ VBA โค้ด VBA ที่ใช้เอนทิตีต่อไปนี้ในรูปแบบวัตถุของ Excel จะทํางานใน Excel for Mac เช่นกัน: วัตถุคิวรี, วัตถุ WorkbookQuery, คุณสมบัติ Workbook.Querys สําหรับข้อมูลเพิ่มเติม ให้ดูที่ การอ้างอิง VBA ของ Excel

2. ใน Excel ตรวจสอบให้แน่ใจว่า Visual Basic Editor เปิดอยู่โดยกด ALT+F11

3. คลิกขวาที่มอดูล แล้วเลือกส่งออกไฟล์ กล่องโต้ตอบ ส่งออก จะปรากฏขึ้น

4. ใส่ชื่อไฟล์ ตรวจสอบให้แน่ใจว่านามสกุลของไฟล์คือ .bas แล้วเลือกบันทึก

5. อัปโหลดไฟล์ VBA ไปยังบริการออนไลน์เพื่อให้สามารถเข้าถึงไฟล์ได้จาก Mac

   คุณสามารถใช้ Microsoft OneDrive ได้ สำหรับข้อมูลเพิ่มเติม ให้ดูที่ซิงค์ไฟล์กับ OneDrive บน Mac OS X

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

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

นําเข้าและรีเฟรชไฟล์ TXT หรือ CSV

1. เปิดเวิร์กบุ๊ก Excel

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

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

4. เลือก ข้อมูลจาก>ข้อความ (ดั้งเดิม) กล่องโต้ตอบ ตัวค้นหา จะปรากฏขึ้น

5. ค้นหาไฟล์ .txt หรือ .csv แล้วเลือก เปิดตัวช่วยสร้างการนําเข้าข้อความ จะปรากฏขึ้น

   เคล็ด ลับ ตรวจสอบ การแสดงตัวอย่างของบานหน้าต่างข้อมูลที่เลือก ซ้ําๆ เพื่อยืนยันตัวเลือกของคุณ

6. ในหน้าแรก ให้ดำเนินการต่อไปนี้:
   ชนิดแฟ้ม เมื่อต้องการเลือกชนิดของไฟล์ข้อความ ให้เลือก ใช้ตัวคั่น หรือ ความกว้างคงที่

   หมายเลขแถว ในเริ่มนำเข้าที่แถว ให้เลือกหมายเลขแถวเพื่อระบุแถวแรกของข้อมูลที่คุณต้องการนำเข้า

   ชุดอักขระ ใน ที่มาของไฟล์ ให้เลือกชุดอักขระที่ใช้ในไฟล์ข้อความ ในกรณีส่วนใหญ่ คุณสามารถปล่อยให้การตั้งค่านี้เป็นค่าเริ่มต้นได้

7. ในหน้าที่สอง ให้ทําดังต่อไปนี้:

   ใช้ตัวคั่น

   ถ้าคุณเลือก ใช้ตัวคั่น บนหน้าแรก ภายใต้ ตัวคั่น ให้เลือกอักขระตัวคั่น หรือใช้กล่องกาเครื่องหมาย อื่นๆ เพื่อใส่อักขระที่ไม่อยู่ในรายการ

   เลือกมองตัวคั่นที่อยู่ติดกันเป็นตัวเดียวกัน หากข้อมูลของคุณมีตัวคั่นมากกว่าหนึ่งอักขระระหว่างเขตข้อมูล หรือหากข้อมูลของคุณมีตัวคั่นแบบกำหนดเองหลายตัว

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

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

8. ในหน้าที่สาม ให้ทําดังต่อไปนี้:

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

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

9. เลือกตำแหน่งที่คุณต้องการเพิ่มข้อมูล ไม่ว่าจะเป็นในแผ่นงานที่มีอยู่ ในแผ่นงานใหม่ หรือใน PivotTable

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

เชื่อมต่อกับฐานข้อมูล SQL (ODBC)

1. เลือก ข้อมูลจาก>SQL Server ODBC กล่องโต้ตอบเชื่อมต่อกับแหล่งข้อมูล ODBC ของ SQL Server จะปรากฎขึ้น

   

2. ใส่เซิร์ฟเวอร์ในกล่อง ชื่อเซิร์ฟเวอร์ และใส่ฐานข้อมูลในกล่อง ชื่อฐานข้อมูล

   รับข้อมูลนี้จากผู้ดูแลระบบฐานข้อมูล

3. ในหัวข้อการตรวจสอบสิทธิ์ ให้เลือกวิธีการจากรายการ ได้แก่ ชื่อผู้ใช้/รหัสผ่าน, Kerberos หรือ NTLM

4. ใส่ข้อมูลประจําตัวในกล่อง ชื่อผู้ใช้และรหัสผ่าน

5. เลือก เชื่อมต่อ กล่องโต้ตอบ ตัวนําทาง จะปรากฏขึ้น

6. ในบานหน้าต่างด้านซ้าย ให้นำทางไปยังตารางที่คุณต้องการ แล้วเลือกตารางดังกล่าว

7. ยืนยันคําสั่ง SQL ในบานหน้าต่างด้านขวา คุณสามารถเปลี่ยนแปลงคำสั่ง SQL ได้ตามที่เห็นสมควร

8. หากต้องการแสดงตัวอย่างข้อมูล ให้เลือกเรียกใช้

9. เมื่อคุณพร้อมแล้ว ให้เลือกส่งกลับข้อมูล กล่องโต้ตอบนําเข้าข้อมูลจะปรากฏขึ้น

   

10. เลือกตำแหน่งที่คุณต้องการเพิ่มข้อมูล ไม่ว่าจะเป็นในแผ่นงานที่มีอยู่ ในแผ่นงานใหม่ หรือใน PivotTable

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

12. เลือก ตกลง

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

เชื่อมต่อกับฐานข้อมูลอื่น (Microsoft Query)

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

1. เลือก ข้อมูลจาก>ฐานข้อมูล (Microsoft Query)

2. เพิ่มแหล่งข้อมูลสำหรับฐานข้อมูลของคุณ แล้วคลิกตกลง

3. ที่พร้อมท์ข้อมูลประจำตัวของ SQL Server ให้ใส่วิธีการตรวจสอบสิทธิ์ ชื่อผู้ใช้ และรหัสผ่าน

   

4. ทางด้านซ้าย ให้เลือกลูกศรถัดจากเซิร์ฟเวอร์เพื่อดูฐานข้อมูล

5. เลือกลูกศรถัดจากฐานข้อมูลที่คุณต้องการ

6. เลือกตารางที่คุณต้องการ

7. หากต้องการแสดงตัวอย่างข้อมูล ให้เลือกเรียกใช้

8. เมื่อคุณพร้อมแล้ว ให้เลือกส่งกลับข้อมูล

9. ในกล่องโต้ตอบนำเข้าข้อมูล ให้เลือกตำแหน่งที่คุณต้องการให้ข้อมูลอยู่ ไม่ว่าจะเป็นในแผ่นงานที่มีอยู่ ในแผ่นงานใหม่ หรือใน PivotTable

10. เลือก ตกลง

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

ล้างข้อมูลประจําตัวการเข้าสู่ระบบของคุณ

หากสิทธิ์ของคุณไม่ทํางาน คุณอาจต้องล้างสิทธิ์ก่อน แล้วจึงเข้าสู่ระบบ

1. เลือกการเชื่อมต่อข้อมูล> กล่องโต้ตอบการเชื่อมต่อเวิร์กบุ๊กจะปรากฏขึ้น

2. เลือกการเชื่อมต่อที่คุณต้องการในรายการ แล้วเลือกล้างสิทธิ์