Excel

สรุป

รายการ	คำอธิบาย
สถานะการเผยแพร่	ความพร้อมใช้งานทั่วไป
ผลิตภัณฑ์	Excel Power BI (แบบจําลองความหมาย) Power BI (กระแสข้อมูล) ผ้า (กระแสข้อมูลรุ่น 2) Power Apps (กระแสข้อมูล) Dynamics 365 Customer Insights Analysis Services
ชนิดการรับรองความถูกต้องที่ได้รับการสนับสนุน	ไม่ระบุชื่อ (ออนไลน์) พื้นฐาน (ออนไลน์) บัญชีองค์กร (ออนไลน์)
เอกสารอ้างอิงฟังก์ชัน	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 ในสภาพแวดล้อมบริการคลาวด์ได้ ดังนั้นถ้าคุณเห็นข้อผิดพลาดนี้ในโฮสต์ระบบคลาวด์ (เช่น 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.04900000000000000002 ตัวเลขนี้คือค่าที่ Power Query อ่านจาก .xlsxและด้วยเหตุนี้จึงเป็นค่าที่ปรากฏขึ้นเมื่อคุณเลือกเซลล์ใน Power Query (สําหรับข้อมูลเพิ่มเติมเกี่ยวกับความแม่นยําของตัวเลขใน Power Query ให้ไปที่ส่วน "เลขทศนิยม" และ "เลขทศนิยมคงที่" ของ ชนิดข้อมูลใน Power Query)

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

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

1. เปิดเอกสารใน Excel Desktop
2. เปิดเมนู ไฟล์ เลือกแท็บ ข้อมูล จากนั้นเลือก คัดลอกเส้นทาง
3. คัดลอกที่อยู่ลงในช่อง File Path หรือ 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 จะแสดงเฉพาะค่าตามที่ปรากฏใน Excel ไปยัง Power Query ไม่ใช่ตัวเลขพื้นฐาน

ค่า 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 บนคอมพิวเตอร์โฮสต์ มีเพียง ACE เท่านั้นที่สามารถอ่านสมุดงานที่บันทึกไว้ในรูปแบบ "สเปรดชีต XML แบบเปิดอย่างเข้มงวด" อย่างไรก็ตาม เนื่องจากเวิร์กบุ๊กดังกล่าวใช้นามสกุลไฟล์เดียวกันเป็นเวิร์กบุ๊ก 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.

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

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

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

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