Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Фильтр поля данных в сводной таблице при помощи макроса
 
#1
21.01.2020 10:32:12
Добрый день! Прошу помочь с задачей!

Excel 2016 / 365
Исходная таблица загружена в модель данных при помощи PowerQuery.
На основе этой таблицы создано несколько сводных таблиц для подсчета различных метрик и визуализации различных группировок.
Т.к. исторические данные не сохраняются, данные в сводной таблице меняются.
Пользователям необходимо всё обновлять при помощи одной кнопки. Поэтому рассматривается только решение при помощи макроса.

В файле примера приведены только данные для конкретной сводной таблицы: код продукта (число как текст) и количество заявок по этому продукту (число).
Сводная таблица отображает:
- в строках - код продукта
- в данных - Distinct Count количества заявок
- в фильтре - количество заявок должно быть неравно нулю.

Необходимо при помощи макроса в фильтре выбирать все значения, а затем оставлять только те, которые не равны нулю.

Всё, что до этого находил, так и не смог адаптировать под свои нужды.

Например, вот такой код выглядит наиболее подходящим для этой задачи:
Код
Dim arr(), li, lu As Long
For li = 1 To 1000
    ReDim Preserve arr(lu)
    arr(lu) = "[Table1].[Количество заявок].&[" & li & "]"
    lu = lu + 1
Next
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table1].[Количество заявок].[Количество заявок]").VisibleItemsList = arr
Но фактический список уникальных значений "количества заявок" представляет из себя последовательность типа "0, 1, 2, 3, 5, 10, 11, 70, 80, 150", поэтому приведенный выше код не срабатывает, а как выбрать из этого списка по порядку - не знаю.

Пожалуйста, помогите.
 
 
 
#2
04.02.2020 12:50:11
Доброе время суток.
Вариант. Будет ли работать в 2013 - не знаю. Тестировал в 2016
Код
Public Sub ShowMoreZeroItems()
    Dim pField As PivotField, sItems() As String, i As Long
    Set pField = ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table1].[Количество заявок].[Количество заявок]")
    sItems = GetFieldNonZeroItems("Table1", "Количество заявок")
    For i = 1 To UBound(sItems)
        sItems(i) = "[Table1].[Количество заявок].&[" & sItems(i) & "]"
    Next
    pField.VisibleItemsList = sItems
End Sub

Private Function GetFieldNonZeroItems(ByVal TableName As String, ByVal FieldName As String) As String()
    Const baseSQL = "EVALUATE FILTER(DISTINCT('table$'[field$]), 'table$'[field$] <> 0)"
    Dim pConn As Object, pRSet As Object, sResult() As String, i As Long
    Set pConn = ThisWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection
    Set pRSet = pConn.Execute(Replace$(Replace$(baseSQL, "table$", TableName), "field$", FieldName))
    ReDim sResult(1 To pRSet.RecordCount)
    i = 0
    Do Until pRSet.EOF
        i = i + 1
        sResult(i) = pRSet(0).Value
        pRSet.MoveNext
    Loop
    pRSet.Close
    GetFieldNonZeroItems = sResult
End Function
 
 
 
#3
04.02.2020 12:59:43
Вот вечно им хирургам хочется все резать и резать!
Вот вам таблетка - съешьте и все само отвалится!  :D
Безо всяких макросов.
Вот горшок пустой, он предмет простой...
 
 
 
#4
04.02.2020 13:07:47
Цитата
PooHkrd написал:
Безо всяких макросов.
Привет, Алексей.
Не уловил, а чем это лучше, чем просто в исходном файле в фильтре снять галку с 0? Потом окажется - что это часть некоторой большой задачи, которую несколько неудачно декомпозировали.
 
 
 
#5
04.02.2020 13:14:17
Андрей VG, тем что по сути такой фильтр равнозначен тому что хотел ТС при помощи макроса, любые значения <> 0 будут соответствовать значению в фильтре "пусто", т.е. при появлении новых значений не требуется каждый раз лезть в фильтр и отмечать новые значения. При этом даже если в источнике вдруг пропадут строки с нулями, а потом появятся, то фильтр не слетит, т.к. в фильтрующей таблице он приколочен гвоздями. Кстати. а что в этом случае скажет макрос?
В общем ТСу предоставили 2 варианта решения - пусть выбирает.
Изменено: PooHkrd - 04.02.2020 13:15:06
Вот горшок пустой, он предмет простой...
 
 
 
#6
04.02.2020 13:38:11
Цитата
PooHkrd написал:
любые значения <> 0 будут соответствовать значению в фильтре "пусто", т.е. при появлении новых значений не требуется каждый раз лезть в фильтр и отмечать новые значения.
Ну, для такого случая можно и меру прописать с учётом требуемого
Код
=CALCULATE(DISTINCTCOUNT('Table1'[Количество заявок]); 'Table1'[Количество заявок] <> 0)

Цитата
PooHkrd написал:
а что в этом случае скажет макрос?
Ровно тоже самое, что в DAX Studio вернёт?
Код
EVALUATE FILTER(DISTINCT('Table1'[Количество заявок]), 'Table1'[Количество заявок] <> 0)
 
 
 
#7
04.02.2020 13:54:00
Андрей VG, я тоже думал про меру, но гражданин просил фильтр, потому предложил так. А по поводу ДаксСтудии, нету её у меня на работе - не знаю я что она скажет.
Вот горшок пустой, он предмет простой...
 
 
 
#8
04.02.2020 15:51:44
Цитата
Андрей VG написал:
Доброе время суток.Вариант.
Опробовал, всё отлично работает. Спасибо!!!

Цитата
PooHkrd написал:
Вот вам таблетка - съешьте и все само отвалится!  
И Вам спасибо. Этот вариант оставлю на заметку.

А вот с мерами вообще, видимо, самое элегантное решение. К стыду своему стоит признать, что с мерами вообще не знаком был до вчерашнего дня.

В общем, большое спасибо!!!!
 
 
 
Страницы: 1
Наверх