如何通过Power Query批量提取多发票工作簿指定单元格数据

Alright, let's solve this batch extraction task with Power Query—you've got 700+ workbooks, but this is straightforward once you know the steps. Here's a step-by-step solution tailored to your needs:

Step 1: Connect to the Folder Containing Your Workbooks #

• Open a blank Excel workbook, go to the Data tab
• Select Get Data > From File > From Folder
• Navigate to the folder holding all your 700+ invoice workbooks, then click OK

Step 2: Add a Custom Column to Extract Target Cells #

In the Folder Content query editor:

1. First, you can clean up the view by removing unnecessary columns (like "Content Type" or "Folder Path") if you want—keep at least "Name" (to track which workbook each row comes from) and "Content".
2. Go to Add Column > Custom Column, paste this M code into the formula box:

let
    // Load the workbook content
    WorkbookSource = Excel.Workbook([Content], null, true),
    // Target the "Sheet 1" worksheet
    Sheet1Data = WorkbookSource{[Item="Sheet 1", Kind="Sheet"]}[Data],
    // Extract cells: note Power Query uses 0-based indexing
    HoursValue = Sheet1Data{32}[Column4], // D33 = row 33 (index 32), column D (index 4)
    TravelTimeValue = Sheet1Data{34}[Column4], // D35 = row 35 (index 34)
    MileageValue = Sheet1Data{36}[Column4] // D37 = row 37 (index 36)
in
    [工时 = HoursValue, 旅行时长 = TravelTimeValue, 里程数 = MileageValue]

Quick note on indexing: Power Query counts rows/columns starting at 0, so row 33 is index 32, column D is index 4. This matches your cell references perfectly.

Step 3: Expand the Custom Column #

• Click the expand icon (⊕) on the right side of your new custom column header
• Check all three fields (工时, 旅行时长, 里程数) in the dropdown, then click OK

Step 4: Finalize and Load the Data #

• You’ll now see a table with the filename and all three extracted values for each workbook
• Double-check a few rows to confirm the data matches the source workbooks
• Click Close & Load to bring the consolidated data into a new Excel worksheet

Bonus Tips #

• If some workbooks might have missing "Sheet 1" or invalid cells, add error handling to the M code using try...otherwise (e.g., try Sheet1Data{32}[Column4] otherwise null) to avoid query failures.
• Processing 700 files might take a minute or two—let Power Query finish running before making changes.

内容的提问来源于stack exchange，提问作者DougB