Excel

สรุป

รายการ คำอธิบาย
สถานะการเผยแพร่ ความพร้อมใช้งานทั่วไป
ผลิตภัณฑ์ Power BI (ชุดข้อมูล)
Power BI (กระแสข้อมูล)
Power Apps (กระแสข้อมูล)
Excel
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 ในสภาพแวดล้อมบริการระบบคลาวด์ ดังนั้น ถ้าคุณเห็นข้อผิดพลาดนี้ในโฮสต์ระบบคลาวด์ (เช่น Power Query Online) คุณจะต้องใช้เกตเวย์ที่มี ACE ติดตั้งเพื่อเชื่อมต่อกับไฟล์ Excelเดิม

รองรับความสามารถ

  • นำเข้า

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

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

  1. เลือกตัวเลือก Excel การเลือกตัวเชื่อมต่อ

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

    เลือกExcelสมุดงานจากFile Explorerของคุณ

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

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

    Excelนําเข้าลงใน Power Query Desktop Navigator

เชื่อมต่อสมุดงานExcelจากPower Queryออนไลน์

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

  1. เลือกตัวเลือก Excel การเลือกตัวเชื่อมต่อ

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

    ข้อมูลการเชื่อมต่อเพื่อExcelเวิร์กบุ๊กของคุณ

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

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

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

    Excelนําเข้าลงในตัวPower Queryทางออนไลน์

<a name="troubleshooting">การแก้ไขปัญหา

ความแม่นยสูงของตัวเลข (หรือ "เหตุใดตัวเลขของฉันจึงเปลี่ยนแปลง?")

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

คําตอบนั้นซับซ้อนเล็กน้อยและต้องคําตอบเกี่ยวกับวิธีที่Excelตัวเลขโดยใช้สิ่งที่เรียกว่า สเล็กชันแบบ ทศนิยมลอย ตัวไบนารี เส้นด้านล่างคือมีตัวเลขบางอย่างที่ระบบไม่สามารถExcelความแม่นยExcel 100% ได้ ถ้าคุณแตกเปิดไฟล์ .xlsx และดูค่าจริงที่เก็บไว้ คุณจะเห็นว่า ในไฟล์ .xlsx, 0.049 ถูก เก็บไว้เป็น 0.04900000000000000000000000 นี่คือค่า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 for ค่าใด ๆ ที่ไม่ตรงกับชนิด ตัวอย่างเช่น ถ้าคอลัมน์ประกอบด้วยตัวเลขในแปดแถวแรก (เช่น 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. ตรวจสอบสองสามบรรทัดแรกของไฟล์ ถ้าไฟล์มีขนาดเล็กพอ ให้เปิดในตัวแก้ไขข้อความ ถ้าไฟล์มีขนาดใหญ่เกินไปที่จะถูกเปิดในตัวแก้ไขข้อความ ให้เรียกใช้สั่งต่อไปนี้จากพร้อมท์สั่ง: เปิดSheet1.xml เพิ่มเติม
  6. ค้นหาแท็ก <dimension .../> (ตัวอย่างเช่น <dimension ref="A1:C200" /> )

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

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

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

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

  • เปิดและบันทึกเอกสารอีกครั้ง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เลเยอร์ใหม่