Excel

สรุป

รายการ	คำอธิบาย
สถานะการเผยแพร่	ความพร้อมใช้งานทั่วไป
ผลิตภัณฑ์	Excel Power BI (แบบจําลองความหมาย) Power BI (กระแสข้อมูล) ผ้า (กระแสข้อมูลรุ่น 2) Power Apps (กระแสข้อมูล) Dynamics 365 Customer Insights การบริการด้านการวิเคราะห์
ชนิดการรับรองความถูกต้องที่ได้รับการสนับสนุน	ไม่ระบุชื่อ (ออนไลน์) พื้นฐาน (ออนไลน์) บัญชีองค์กร (ออนไลน์)
เอกสารอ้างอิงฟังก์ชัน	Excel.Workbook Excel.CurrentWorkbook

หมายเหตุ

ความสามารถบางอย่างอาจมีอยู่ในผลิตภัณฑ์เดียว แต่ไม่ใช่อย่างอื่นเนื่องจากตารางเวลาการปรับใช้และความสามารถเฉพาะของโฮสต์

ข้อกำหนดเบื้องต้น

เมื่อต้องการเชื่อมต่อกับเวิร์กบุ๊กเดิม (เช่น .xls หรือ .xlsb) จําเป็นต้องใช้ตัวให้บริการ Access Database Engine OLEDB (หรือ ACE) หากต้องการติดตั้งผู้ให้บริการนี้ ให้ ไปที่หน้า ดาวน์โหลดและติดตั้งเวอร์ชันที่เกี่ยวข้อง (32 บิต หรือ 64 บิต) ถ้าคุณยังไม่ได้ติดตั้ง คุณจะเห็นข้อผิดพลาดต่อไปนี้เมื่อเชื่อมต่อกับเวิร์กบุ๊กเดิม:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ไม่สามารถติดตั้ง ACE ในสภาพแวดล้อมบริการคลาวด์ได้ ดังนั้น ถ้าคุณเห็นข้อผิดพลาดนี้ในโฮสต์บน Cloud (เช่น Power Query Online) คุณจะต้องใช้เกตเวย์ที่มี ACE ติดตั้งเพื่อเชื่อมต่อกับไฟล์ Excel แบบดั้งเดิม

รองรับความสามารถแล้ว

• นำเข้า

เชื่อมต่อไปยังสมุดงาน Excel จาก Power Query Desktop

เมื่อต้องการทําการเชื่อมต่อจาก Power Query Desktop:

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

2. เรียกดู และเลือกเวิร์กบุ๊ก Excel ที่คุณต้องการโหลด จากนั้นเลือก เปิด

   

   ถ้าเวิร์กบุ๊ก Excel อยู่ในสถานะออนไลน์ ให้ใช้ตัว เชื่อมต่อ เว็บเพื่อเชื่อมต่อกับเวิร์กบุ๊ก

3. ในตัวนําทาง ให้เลือกข้อมูลเวิร์กบุ๊กที่คุณต้องการ จากนั้นเลือก โหลด เพื่อโหลดข้อมูลหรือ แปลงข้อมูล เพื่อแปลงข้อมูลในตัวแก้ไข Power Query ต่อไป

   

เชื่อมต่อไปยังสมุดงาน Excel จาก Power Query Online

เมื่อต้องการทําการเชื่อมต่อจาก Power Query ออนไลน์:

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

   

2. ในกล่องโต้ตอบ Excel ที่ปรากฏขึ้น ให้ระบุเส้นทางไปยังเวิร์กบุ๊ก Excel

   

3. ถ้าจําเป็น ให้เลือกเกตเวย์ข้อมูลภายในองค์กรเพื่อเข้าถึงเวิร์กบุ๊ก Excel

4. ถ้านี่เป็นครั้งแรกที่คุณเข้าถึงเวิร์กบุ๊ก Excel นี้ ให้เลือกชนิดการรับรองความถูกต้อง และลงชื่อเข้าใช้บัญชีของคุณ (ถ้าจําเป็น)

5. ในตัวนําทาง ให้เลือกข้อมูลเวิร์กบุ๊กที่คุณต้องการ จากนั้นแปลงข้อมูลเพื่อแปลงข้อมูลในตัวแก้ไข Power Query ต่อไป

   

ตารางที่แนะนํา

ถ้าคุณเชื่อมต่อกับเวิร์กบุ๊ก Excel ที่ไม่มีตารางเดียวโดยเฉพาะ ตัวนําทาง Power Query จะพยายามสร้างรายการตารางที่แนะนําที่คุณสามารถเลือกได้ ตัวอย่างเช่น พิจารณาตัวอย่างสมุดงานต่อไปนี้ที่ประกอบด้วยข้อมูลจาก A1 ถึง C5 ข้อมูลเพิ่มเติมจาก D8 ถึง E10 และอื่น ๆ จาก C13 ถึง F16

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

ถ้าคุณเลือกทั้งแผ่นงานในตัวนําทาง เวิร์กบุ๊กจะแสดงดังที่ปรากฏใน Excel พร้อมกับเซลล์ว่างทั้งหมดที่เติมด้วย null

ถ้าคุณเลือกหนึ่งตารางที่แนะนํา แต่ละตารางที่ Power Query สามารถกําหนดจากเค้าโครงของเวิร์กบุ๊กจะแสดงในตัวนําทาง ตัวอย่างเช่น ถ้าคุณเลือก ตารางที่ 3 ข้อมูลที่ปรากฏในเซลล์ C13 ถึง F16 จะปรากฏขึ้น

หมายเหตุ

ถ้าแผ่นงานเปลี่ยนแปลงมากพอ ตารางอาจรีเฟรชไม่ถูกต้อง คุณอาจสามารถแก้ไขการรีเฟรชโดยการนําเข้าข้อมูลอีกครั้งและเลือกตารางที่แนะนําใหม่

การแก้ไขปัญหา

ความแม่นยําที่เป็นตัวเลข (หรือ "ทําไมตัวเลขของฉันเปลี่ยน?")

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

คําตอบนั้นซับซ้อนเล็กน้อยและเกี่ยวข้องกับวิธีที่ Excel เก็บตัวเลขโดยใช้สิ่งที่เรียกว่า สัญประกาศไบนารีจุดทศนิยมลอยตัว บรรทัดล่างคือ มีตัวเลขบางอย่างที่ Excel ไม่สามารถแสดงด้วยความแม่นยํา 100% ได้ ถ้าคุณแตกเปิดไฟล์.xlsx และดูที่ค่าจริงที่กําลังจัดเก็บคุณจะเห็นว่าในไฟล์.xlsx 0.049 จะถูก เก็บไว้จริง ๆ เป็น 0.049000000000000000002 นี่คือค่าที่ Power Query อ่านจาก.xlsx และดังนั้นค่าที่ปรากฏขึ้นเมื่อคุณเลือกเซลล์ใน Power Query (สําหรับข้อมูลเพิ่มเติมเกี่ยวกับความแม่นยําของตัวเลขใน Power Query ให้ไปที่ส่วน "เลขทศนิยม" และ "เลขทศนิยมคงที่" ของ ชนิดข้อมูลใน Power Query)

เชื่อมต่อไปยังเวิร์กบุ๊ก Excel ออนไลน์

ถ้าคุณต้องการเชื่อมต่อกับเอกสาร Excel ที่โฮสต์ใน Sharepoint คุณสามารถทําได้ผ่าน ตัวเชื่อมต่อเว็บ ใน Power BI Desktop, Excel และกระแสข้อมูล และยังมีตัวเชื่อมต่อ Excel ในกระแสข้อมูล วิธีการรับลิงก์ไปยังไฟล์:

1. เปิดเอกสารใน Excel Desktop
2. เปิดเมนู ไฟล์ เลือกแท็บ ข้อมูล จากนั้นเลือก คัดลอกเส้นทาง
3. คัดลอกที่อยู่ลงในเขตข้อมูลเส้นทางหรือ URL ของไฟล์ และลบ ?web=1 ออกจากส่วนท้ายของที่อยู่

ตัวเชื่อมต่อ ACE แบบดั้งเดิม

Power Query จะอ่านเวิร์กบุ๊กเดิม (เช่น .xls หรือ .xlsb) โดยใช้ผู้ให้บริการ OLEDB ของกลไกจัดการฐานข้อมูล Access (หรือ ACE) ด้วยเหตุนี้ คุณอาจพบลักษณะการทํางานที่ไม่คาดคิดเมื่อนําเข้าเวิร์กบุ๊กเดิมที่ไม่เกิดขึ้นเมื่อนําเข้าเวิร์กบุ๊ก OpenXML (เช่น .xlsx) ต่อไปนี้คือตัวอย่างทั่วไปบางส่วน

การจัดรูปแบบค่าที่ไม่คาดคิด

เนื่องจาก ACE ค่าจากเวิร์กบุ๊ก Excel แบบดั้งเดิมอาจถูกนําเข้าด้วยความแม่นยําหรือความเที่ยงตรงน้อยกว่าที่คุณคาดหวัง ตัวอย่างเช่น สมมติว่าไฟล์ Excel ของคุณมีตัวเลข 1024.231 ซึ่งคุณได้จัดรูปแบบสําหรับแสดงเป็น "1,024.23" เมื่อนําเข้าไปยัง Power Query ค่านี้จะแสดงเป็นค่าข้อความ "1,024.23" แทนที่จะเป็นจํานวนเต็มพื้นฐาน (1024.231) ทั้งนี้เนื่องจากในกรณีนี้ ACE ไม่ได้แสดงค่าพื้นฐานไปยัง Power Query แต่จะแสดงเฉพาะค่าตามที่แสดงใน Excel เท่านั้น

ค่า Null ที่ไม่คาดคิด

เมื่อ ACE โหลดแผ่นงาน จะดูที่แปดแถวแรกเพื่อกําหนดชนิดข้อมูลของคอลัมน์ ถ้าแปดแถวแรกไม่แสดงแถวถัดมา ACE อาจใช้ชนิดที่ไม่ถูกต้องกับคอลัมน์นั้นและส่งกลับค่า null สําหรับค่าใด ๆ ที่ไม่ตรงกับชนิด ตัวอย่างเช่น ถ้าคอลัมน์มีตัวเลขในแปดแถวแรก (เช่น 1000, 1001 และอื่น ๆ) แต่มีข้อมูลที่ไม่ใช่ตัวเลขในแถวหลัง (เช่น "100Y" และ "100Z") ACE จะสรุปว่าคอลัมน์ประกอบด้วยตัวเลข และค่าที่ไม่ใช่ตัวเลขใด ๆ จะถูกส่งกลับเป็น null

การจัดรูปแบบค่าที่ไม่สอดคล้องกัน

ในบางกรณี ACE จะส่งกลับผลลัพธ์ที่แตกต่างกันอย่างสิ้นเชิงในการรีเฟรช การใช้ตัวอย่างที่อธิบายไว้ใน ส่วนการจัดรูปแบบ คุณอาจเห็นค่า 1024.231 แทนที่จะเป็น "1,024.23" ทันที ความแตกต่างนี้อาจเกิดจากการเปิดเวิร์กบุ๊กเดิมใน Excel ขณะนําเข้าไปยัง Power Query เมื่อต้องการแก้ไขปัญหานี้ ให้ปิดสมุดงานดังกล่าว

ข้อมูล Excel หายไปหรือไม่สมบูรณ์

ในบางครั้ง Power Query ล้มเหลวในการแยกข้อมูลทั้งหมดจากแผ่นงาน Excel ความล้มเหลวนี้มักจะเกิดจากแผ่นงานที่มี ขนาด ที่ไม่ถูกต้อง (ตัวอย่างเช่น มีขนาดของ A1:C200 เมื่อข้อมูลจริงมีมากกว่าสามคอลัมน์หรือ 200 แถว)

วิธีการวินิจฉัยมิติที่ไม่ถูกต้อง

เมื่อต้องการดูขนาดของแผ่นงาน:

1. เปลี่ยนชื่อไฟล์ xlsx ด้วยนามสกุล.zip
2. เปิดไฟล์ใน File Explorer
3. นําทางไปยัง xl\worksheets
4. คัดลอกไฟล์ xml สําหรับแผ่นงานที่มีปัญหา (ตัวอย่างเช่น Sheet1.xml) ออกจากไฟล์ zip ไปยังตําแหน่งที่ตั้งอื่น
5. ตรวจสอบสองสามบรรทัดแรกของไฟล์ ถ้าไฟล์มีขนาดเล็กพอ ให้เปิดในตัวแก้ไขข้อความ ถ้าไฟล์มีขนาดใหญ่เกินกว่าที่จะเปิดในตัวแก้ไขข้อความ ให้เรียกใช้คําสั่งต่อไปนี้จาก Command Prompt: Sheet1.xml เพิ่มเติม
6. ค้นหา <dimension .../> แท็ก (ตัวอย่างเช่น <dimension ref="A1:C200" />)

ถ้าไฟล์ของคุณมีแอตทริบิวต์มิติที่ชี้ไปยังเซลล์เดียว (เช่น <dimension ref="A1" />) Power Query จะใช้แอตทริบิวต์นี้เพื่อค้นหาแถวและคอลัมน์เริ่มต้นของข้อมูลบนแผ่นงาน

อย่างไรก็ตาม ถ้าไฟล์ของคุณมีแอตทริบิวต์มิติที่ชี้ไปยังเซลล์หลายเซลล์ (เช่น <dimension ref="A1:AJ45000"/>) Power Query จะใช้ช่วงนี้เพื่อค้นหาแถวและคอลัมน์ เริ่มต้น เช่นเดียวกับแถวและคอลัมน์สุดท้าย ถ้าช่วงนี้ไม่มีข้อมูลทั้งหมดบนแผ่นงาน ข้อมูลบางส่วนจะไม่ถูกโหลด

วิธีการแก้ไขขนาดที่ไม่ถูกต้อง

คุณสามารถแก้ไขปัญหาที่เกิดจากมิติที่ไม่ถูกต้องโดยทําหนึ่งในการดําเนินการต่อไปนี้:

• เปิดและบันทึกเอกสารใน Excel การดําเนินการนี้จะเป็นการเขียนทับมิติที่ไม่ถูกต้องซึ่งจัดเก็บไว้ในไฟล์ด้วยค่าที่ถูกต้อง

• ตรวจสอบให้แน่ใจว่าเครื่องมือที่สร้างไฟล์ Excel ถูกแก้ไขเพื่อแสดงขนาดอย่างถูกต้อง

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

  นี่คือตัวอย่างของวิธีการระบุตัวเลือกนี้:

  Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

ทํางานช้าหรือช้าเมื่อโหลดข้อมูล Excel

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

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

ประสิทธิภาพการทํางานไม่ดีเมื่อโหลดข้อมูลจาก SharePoint

เมื่อดึงข้อมูลจาก Excel บนเครื่องของคุณหรือจาก SharePoint ให้พิจารณาทั้งปริมาณของข้อมูลที่เกี่ยวข้อง และความซับซ้อนของเวิร์กบุ๊ก

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

ข้อผิดพลาดเมื่อใช้ตัวเชื่อมต่อ Excel เพื่อนําเข้าไฟล์ CSV

แม้ว่าไฟล์ CSV จะสามารถเปิดใน Excel แต่ไฟล์เหล่านั้นไม่ใช่ไฟล์ Excel ใช้ตัว เชื่อมต่อ ข้อความ/CSV แทน

ข้อผิดพลาดเมื่อนําเข้าสมุดงาน "กระดาษคํานวณ XML แบบเข้มงวด"

คุณอาจเห็นข้อผิดพลาดต่อไปนี้เมื่อนําเข้าสมุดงานที่บันทึกในรูปแบบ "สเปรดชีต XML แบบเข้มงวด" ของ Excel:

DataFormat.Error: The specified package is invalid. The main part is missing.

ข้อผิดพลาดนี้เกิดขึ้นเมื่อ ไม่ได้ติดตั้งโปรแกรมควบคุม ACE บนคอมพิวเตอร์โฮสต์ สมุดงานที่ถูกบันทึกในรูปแบบ "สเปรดชีต XML แบบเข้มงวด" สามารถอ่านได้โดย ACE เท่านั้น อย่างไรก็ตาม เนื่องจากเวิร์กบุ๊กดังกล่าวใช้นามสกุลไฟล์เดียวกันเป็นเวิร์กบุ๊ก Open XML ทั่วไป (.xlsx) เราจึงไม่สามารถใช้ส่วนขยายนี้เพื่อแสดงข้อความข้อผิดพลาดปกติ the Access Database Engine OLEDB provider may be required to read this type of file ได้

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

ข้อผิดพลาด "ไฟล์มีข้อมูลเสียหาย"

คุณอาจเห็นข้อผิดพลาดต่อไปนี้เมื่อนําเข้าเวิร์กบุ๊ก Excel บางตัว

DataFormat.Error: File contains corrupted data.

โดยปกติแล้วข้อผิดพลาดนี้บ่งชี้ว่ามีปัญหาเกี่ยวกับรูปแบบของไฟล์

อย่างไรก็ตามบางครั้งข้อผิดพลาดนี้สามารถเกิดขึ้นได้เมื่อไฟล์ปรากฏเป็นไฟล์ XML เปิด (เช่น.xlsx) แต่จําเป็นต้องใช้โปรแกรมควบคุม ACE เพื่อประมวลผลไฟล์ ไปยังส่วน ตัวเชื่อมต่อ Legacy ACE สําหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการประมวลผลไฟล์ที่จําเป็นต้องใช้โปรแกรมควบคุม ACE

ปัญหาและข้อจำกัดที่ทราบ

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