Блог

У меня есть сводная таблица с более чем 11 000 строками данных и 53 столбцами. Мне нужен фрагмент кода, который очень эффективно извлекает данные из определенной ячейки сводной таблицы.

Вот как выглядит сводная таблица:

Мне нужно иметь возможность извлекать данные из определенных ячеек, используя три (3) столбца слева в качестве условий выбора правильной строки и смещения в правильный столбец, используя номера недель 1-50 вверху.

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

 Dim oWB As Workbook: Set oWB = Workbooks.Open(tWB.Path amp; "Telledata egeneide YTD.xlsx", ReadOnly:=True)
Dim oWS As Worksheet: Set oWS = oWB.Sheets("Telledata")
Dim pivotdata As Integer
Dim storeNo As Integer = 1101 'Example only - this can be anything between 1101 and 1199
Dim storeLoc As String = "0002" 'Example only - this value can be anything between 0001 and 0003
Dim materialGroup As Integer = 1120 'Example only - this can be anything between 1110 and 1899
Dim week as Integer = 11 'Example only - this can be anything between 1 and 50

pivotdata = Application.WorksheetFunction.CountIfs(oWS.Range("A:A"), storeNo, _
                                    oWS.Range("B:B"), storeLoc, _
                                    oWS.Range("C:C"), materialGroup, _
                                    oWS.Range("C:C").Offset(0, week), ">4")
 

Я ищу значения, превышающие 4. Как уже упоминалось, запрос в конце должен выполняться до 48 раз в том виде, в каком он есть в настоящее время, поскольку переменные в фрагменте кода изменяются.

Пример запроса:

 storeNo: 1101
storeLoc: 0001
materialGroup: 1141, 1142, 1143, 1410, 1420, 1451, 1220, 1260, 1270, 1710, 1720, 1730
week: 11, 12

storeNo: 1101
storeLoc: 0002
materialGroup: 1141, 1142, 1143, 1410, 1420, 1451, 1220, 1260, 1270, 1710, 1720, 1730
week: 11, 12
 

Как вы можете видеть, все запросы должны выполняться дважды (для каждого хранилища), плюс я должен проверить два столбца НЕДЕЛИ (выбранная неделя и неделя после).

Я думал о цикле для / для каждого цикла, но не уверен, как это сделать…

Вот один из подходов, использующих поиск по словарю на основе уникальных комбинаций 3 столбцов поиска:

(непроверенный)

 Sub Tester()
    
    Dim pt As PivotTable, rngRows As Range, rngData As Range
    Dim dict As Object, arrRows, arrData, r As Long, k
    
    Set pt = ActiveSheet.PivotTables(1)
    
    'https://peltiertech.com/referencing-pivot-table-ranges-in-vba/
    Set rngRows = pt.RowRange   'get the rows range (includes headers)
    Set rngRows = rngRows.Offset(1, 0).Resize(rngRows.Rows.Count - 1) 'exclude headers
    arrRows = rngRows.Value     '... get as 2D array
    
    'create lookup based on the 3 columns
    Set dict = CreateObject("scripting.dictionary")
    For r = 1 To UBound(arrRows, 1)
        k = Key(arrRows(r, 1), arrRows(r, 2), arrRows(r, 3))
        dict(k) = r 'link the key to the row number
    Next r
    
    Set rngData = pt.DataBodyRange    'the data from the table
    arrData = rngData.Value           '... as 2D array
    
    
    'now you can use the lookup to quickly locate the data you want
    k = Key(storeNo, storeLoc, materialGroup)
    If dict.exists(k) Then
        weekVal = arrData(dict(k), WeekNum)
        If weekNum < UBound(arrData, 2) Then 
            nextWeekVal = arrData(dict(k), WeekNum   1)
        End If
    Else
        'no match found
    End If
    
End Sub

'create key by concatenating the values with "|"
Function Key(v1, v2, v3)
    Key = Join(Array(v1, v2, v3), "|")
End Function

 

Решение формулы

Вы можете использовать INDEX amp; MATCH здесь, чтобы извлечь значение, соответствующее строке, которая соответствует вашему storeNo, storeLoc, materialGroup, и столбцу, который соответствует вводу недели.

 =INDEX($A$1:$F$4,MATCH(1,($A$1:$A$4=H2)*($B$1:$B$4=I2)*($C$1:$C$4=J2),0),MATCH(K2,$A$1:$F$1))
 

Ссылка на таблицу

Предполагая структуру таблицы (или сводной таблицы), как показано ниже…

И входная таблица с целевыми критериями, подобными этому…

В зависимости от вашей версии Excel вам может потребоваться подтвердить эту формулу в виде массива. Для этого введите формулу и подтвердите с CTRL помощью SHIFT ENTER