使用M语言提取Excel中My_Sheet的G3单元格值失败求助

问题场景 #

我有多个Excel格式的报告文件，每个文件中都包含名为My_Sheet的工作表，该工作表的G3单元格存在有效数值（例如截图中的3）。我需要制作一份汇总Excel报告，将每个文件的G3数值单独列为一列展示。使用Code_Copilot生成的M代码执行后，所有结果均返回「No data」，但实际单元格存在数值，需要修正这段代码。

原代码 #

// Load the files from the folder
    Origen = Folder.Files("C:\Users\J\MyFolder"),

// Filter hidden files
#"Filter hidden attributes" = Table.SelectRows(Origen, each [Attributes]?[Hidden]? <> true),

// Add a function to extract G3
#"Invocar función para extraer G3" = Table.AddColumn(#"Filter hidden attributes", "G3 Value", each 
    let
        // Load the content of the Excel file
        Source = Excel.Workbook([Content], true), // Open the file
        // Select the sheet that contains the specific table
        MySheet_Sheet = Table.SelectRows(Source, each ([Name] = "My_Sheet")), 
        
        // Check if the sheet exists
        G3_Value = if Table.RowCount(MySheet_Sheet) > 0 then
            let
                // Get the content of the sheet
                SheetContent = MySheet_Sheet{0}[Content],
                // Extract the value from cell G3 (third row, seventh column)
                ValueG3 = try SheetContent{2}[Column7] otherwise "No data"
            in
                ValueG3
        else
            "Sheet not found" // If the sheet doesn't exist
    in
        G3_Value
),

// Select only the columns with the file name and G3 value
#"Select final columns" = Table.SelectColumns(#"Invocar función para extraer G3", {"Name", "G3 Value"})
in
    #"Select final columns"

问题分析 #

1. 表头设置导致行索引偏移：原代码中Excel.Workbook([Content], true)的第二个参数true表示将工作表第一行作为表头，这会导致Power Query识别的数据行从Excel的第二行开始计数。因此Excel的G3单元格对应Power Query中的行索引1，而非原代码中的2。
2. 数据区域识别限制：如果G3单元格不在Power Query自动识别的连续数据区域内，SheetContent可能不会包含该单元格，导致无法提取值。

修正后的代码 #

Origen = Folder.Files("C:\Users\J\MyFolder"),
    #"Filter hidden attributes" = Table.SelectRows(Origen, each [Attributes]?[Hidden]? <> true),
    #"Invocar función para extraer G3" = Table.AddColumn(#"Filter hidden attributes", "G3 Value", each 
        let
            // 不将第一行作为表头，确保行索引与Excel行号对应（索引0=行1，索引2=行3）
            Source = Excel.Workbook([Content], false),
            // 直接按工作表名称查找，替代Table.SelectRows更高效
            MySheet = try Source{[Name="My_Sheet"]} otherwise null,
            G3_Value = if MySheet <> null then
                let
                    SheetContent = MySheet[Content],
                    // 提取G3：行索引2（对应Excel行3），Column7对应G列
                    ValueG3 = try SheetContent{2}[Column7] otherwise 
                        // 若自动识别区域不包含G3，尝试加载整个工作表单元格
                        try Excel.Workbook([Content], false, true){[Name="My_Sheet"]}[Data]{2}[Column7] otherwise "No data"
                in
                    ValueG3
            else
                "Sheet not found"
        in
            G3_Value
    ),
    #"Select final columns" = Table.SelectColumns(#"Invocar función para extraer G3", {"Name", "G3 Value"})
in
    #"Select final columns"

关键修改点 #

• 将Excel.Workbook的第二个参数改为false，关闭表头识别，使Power Query的行索引与Excel行号直接对应（索引0对应Excel行1，索引2对应Excel行3）。
• 改用Source{[Name="My_Sheet"]}直接查找工作表，比Table.SelectRows更高效且简洁。
• 添加备用提取逻辑：当自动识别的数据区域不包含G3时，通过Excel.Workbook([Content], false, true)强制加载整个工作表的单元格数据（第三个参数true表示加载所有单元格，而非仅识别的数据区域）。

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