时间戳

首页 > 解决方案 > 查找数据透视字段未正确返回

excel - 查找数据透视字段未正确返回

问题描述

需要帮助在工作表“A”中查找数据透视表的字段，以匹配另一个工作表“B”中的值以返回工作表“A”中数据透视表旁边的值。

问题是在“B”表中找不到值，所以当它粘贴在它上面时与行号不匹配？

Set wbSite = ThisWorkbook
Set wsSite = wbSite.Worksheets("test")
Set critSite = wsSite.Range("B14:B10000")
TempArray = critSite
myArray = Application.Transpose(TempArray)

Set wbSource = Workbooks.Open("c:\temp\EmployeeReport.xlsx", , True)
Set wsSource = wbSource.Worksheets("Report 1")
wsSource.Range("A:BL").AutoFilter field:=3, Criteria1:=myArray, Operator:=xlFilterValues

Set wbDest = ThisWorkbook
Set wsDest = wbDest.Worksheets("test")
wsDest.Application.CutCopyMode = False

Set rng1 = Range(("L1:L" & Cells(Rows.Count, "L").End(xlDown).Row)).Cells
rng1.Copy
wsDest.Cells(13, 10).PasteSpecial
   
Set rng1 = Range(("M1:M" & Cells(Rows.Count, "M").End(xlUp).Row)).SpecialCells(xlCellTypeVisible)
rng1.Copy
wsDest.Cells(13, 11).PasteSpecial

表 A

ID 姓名
01 阿尔伯特
02 约翰
03 迈克尔
04 托尼
05 罗伯特

表 B

ID 车辆注册
01 H657545
02 H347545
05 H557545

预期结果

ID 姓名 车辆注册）
01 阿尔伯特 H657545
02 约翰 H347545
03 迈克尔 （无效的）
04 托尼 （无效的）
05 罗伯特 H557545

发生了什么

ID 姓名 车辆注册）
01 阿尔伯特 H657545
02 约翰 H347545
03 迈克尔 H557545
04 托尼 没有数据
05 罗伯特 没有数据

标签： excelvbapowerquery

解决方案

虽然在 vba 中是可能的，但我建议您使用 powerQuery 来完成此任务。此外，当操作数据枢轴不理想时，最好直接使用枢轴的来源：

  1. 打开你想要输出的xls；
  2. 转到菜单：数据 > 获取数据 > 从文件 > 从工作簿；
  3. 选择“employeeReport.xlsx”文件；
  4. 选择工作表1并选择“转换”；

Excel 将为您打开 Powerquery。转到菜单主页 > 高级编辑器。复制路径并删除所有代码。改为粘贴（替换文件的路径）：

    let
        PathToFile = "C:\Dropbox\@Scripts\tests\PowerBI\EmployeeReport.xlsx", //add the full path to your sourcefile. I assume data is in sheet 1 and 2
        Sheet1 = 
            let
                Source = Excel.Workbook(File.Contents(PathToFile), null, true),
                Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
                #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
            in
                #"Promoted Headers",
        
        Sheet2 =
            let
                Source = Excel.Workbook(File.Contents(PathToFile), null, true),
                Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
                #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true])
            in
                #"Promoted Headers",
        
        
        #"Merged Queries" = Table.NestedJoin(Sheet1, {"ID"}, Sheet2, {"ID"}, "Sheet2", JoinKind.LeftOuter),
        #"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged Queries", "Sheet2", {"Car Registration"}, {"Car Registration"})
    in
        #"Expanded Sheet2"

选择“完成”单击“关闭并加载到”选择表格，选择您想要表格的位置，点击确定。

如上所述，最好使用数据透视的来源，而不是数据透视，因此如果您需要额外的逻辑（分组、过滤、...），我们可以将其添加到查询中，而不是使用数据透视。

让我知道这对你有什么影响。

推荐阅读