Блог

Нужна помощь, чтобы найти поле сводной таблицы на листе «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:tempEmployeeReport.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" amp; Cells(Rows.Count, "L").End(xlDown).Row)).Cells
rng1.Copy
wsDest.Cells(13, 10).PasteSpecial
   
Set rng1 = Range(("M1:M" amp; Cells(Rows.Count, "M").End(xlUp).Row)).SpecialCells(xlCellTypeVisible)
rng1.Copy
wsDest.Cells(13, 11).PasteSpecial
 

Лист А

Лист В

ожидаемый результат

Что случилось

Хотя это возможно в vba, я рекомендую вам использовать PowerQuery для этой задачи. Кроме того, когда управление сводными данными не является идеальным, лучше напрямую использовать источник сводных данных:

1. Откройте xls, в котором вы хотите получить выходные данные;
2. Перейдите в меню: Данные > Получить данные >> из файла >>> из книги;
3. выберите «employeeReport.xlsx» файл;
4. Выберите лист 1 и выберите «Преобразовать».;

Excel откроет для вас Powerquery. Перейдите в меню Главная страница > Расширенный редактор. скопируйте путь и удалите весь код. Вставьте это вместо этого (замените путь к вашему файлу).:

     let
        PathToFile = "C:Dropbox@ScriptstestsPowerBIEmployeeReport.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"
 

Выберите «готово».
нажмите «закрыть и загрузить»
, выберите таблицу, выберите, где вы хотите разместить свою таблицу, нажмите «ОК».

Как упоминалось выше, лучше использовать источник вашей сводки, а не сводку, поэтому, если вам нужна дополнительная логика (группировка, фильтр,…), мы можем добавить ее в запрос вместо использования сводки.

дайте мне знать, как это работает для вас.