Надстройка для Excel, облегчающая установку фильтров при работе с кубами (VBA)

    Как известно, из коробки Excel не позволяет устанавливать фильтры по списку значений для сводных таблиц, а это ведь такая нужная вещь! Как отфильтровать товары по сотне кодов, а потом по другой сотне? Есть, конечно, способы, но все это не то…

    Или, например, установить для куба фильтр по измерению с датами, но не проставлять галочки на каждом дне/месяце/годе, а задать диапазон С… ПО ....

    Еще достаточно часто требуются установки периодов по фиксированным шаблонам, типа текущий день/месяц/год. Для этих целей можно добавить наборы (в многомерной модели куба) или добавить специальные атрибуты в измерение (это не очень удобно, но в табличной модели куба наборов нет).

    Мы запилили на VBA расширение, добавляющее на ленту панель Инструменты куба с волшебными кнопочками.


    В оригинале на панели кнопочек больше, но они были вырезаны, по понятным причинам.



    Фильтр по датам можно применять к нескольким сводным таблицам одновременно, как на одном, так и на разных листах. Это удобно, когда необходимо установить одинаковые периоды сразу в нескольких таблицах.



    Подключение надстройки


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



    Надстройка заточена под календарь (таблицу дат) определенной структуры, поэтому вряд ли она взлетит у вас без доработок, но они обещают быть небольшими. А вот установка фильтров по списку должна заработать без проблем. Единственное ее ограничение — не поддерживается установка фильтров для иерархии, но это и не нужно.

    Структура таблицы дат, под которую создавалась надстройка, выглядит так:


    Скачать надстройку можно здесь ExCubeTools.zip
    Реклама
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее

    Комментарии 13

      +2

      Статья гуд, но хаб "BigData" причем? Да и "Data Mining" притянут за уши ИМХО. Давно считал и писал НЛО, о необходимости хаба "Excel". Это не просто очередной продукт MS, а явление/среда разработки, в которой и ОС пишут и игрушки.

        +1
        Понятное дело, что данная поделка не имеет ничего общего с BigData, к SQL server она относится примерно также. На мой взгляд, целевая аудитория, и, вероятно, содержащая их в своем зоопарке, обитает именно в этих хабах, поэтому получился такой набор. Может быть я ошибаюсь.
        В какой хаб надо было поместить?
        0
        А какую полезную нагрузку несёт этот код? :-)

        Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
                 MsgBox "['kkj"
                 'ActiveCell.Value = 1 ' or your sub
        End Sub


          +2
          Это магическая часть, без нее работать не будет)))
          Как Вы понимаете, не было цели блеснуть идеальным кодом. Хотелось поделиться инструментом. Эх, если бы со мной кто-то поделился таким лет несколько назад, хотя бы одной кнопочкой установки фильтров по списку…
          +3

          Мда, приехал очередной велосипед для любителя Excel 2003
          Из коробки:
          Активная ячейка на сводной таблице->Анализ->Вставить временную шкалу
          image


          Одним кликом непринужденно оперируем любыми периодами
          image


          Раздаем контроль на любое количество сводных
          image

            +1
            Готов посыпать голову пеплом, если покажете, как с помощью этого красивого ползунка установить период С… ПО… введя его с клавиатуры, а не выискивать где же там эта дата. Не переключаясь сначала на годы, потом кварталы и месяцы/дни.

            Это риторический вопрос. Нужно пойти в фильтр, во вложенном меню поставить «между».
            Но теперь нам надо добавить к этому периоду аналогичный предшествующего года.
            Иии… что делать?

            Далее нам нужно быстро применить только к одной текущей таблице, а через минуту ко всем на листе.
            Проставлять галочки в настройке, к каким таблицам применить? А если их создавали в разной последовательности на разных листах, таким образом на первом листе есть таблица1, таблица5 и таблица8, это нужно сначала посмотреть какие таблицы на листе и потом по одной галочке снимать/ставить? А когда в книге пара десятков таблиц, но в окошке «Подключение к отчетам» нет кнопочки «Выделить все» и «Снять выделение», будем полчаса щелкать, снимая галочки, а потом столько же их ставить назад?
            Спасибо, но я предпочитаю день подождать…

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

            ЗЫ. Пепел я уже приготовил :-)
              0
              Ммм, мне казалось, что исходная задача в Power pivot работает из коробки? Или нет?
                0
                С Power Pivot не все так просто.
                Во-первых, с точки зрения лицензии — начиная с Office2013 надстройка есть только в редакциях Pro Plus. Цену можете загуглить.
                Во-вторых, сама надстройка изначально сделана не для добавления плюшек к интерфейсу по работе с кубом, она сделана для удобства создания локальных моделей данных, пусть даже эти данные базируются на кубе и других источниках. Это прослойка, позволяющая получить данные из различных источников с дополнительными трансформациями и слияниями, чтобы впоследствии использовать этого франкенштейна как единую модель, которую предварительно надо готовить и все равно в ней не будет сабжа.
              +1
              Как известно, из коробки Excel не позволяет устанавливать фильтры по списку значений для сводных таблиц, а это ведь такая нужная вещь! Как отфильтровать товары по сотне кодов, а потом по другой сотне?

              На этот вопрос Вы не ответили своими картинками. Расскажите, как это делается для любителя Excel 2010?
              0
              если нужны прям навороченные механизмы фильтрации данных, например текущий месяц и этот же месяц годом ранее и кварталом ранее, то эта логика прописывается в данных в виде формул AND() и OR() ссылаясь на свои ячейки и в сводной таблице фильтруете все по ВашеУсловие=истина
                0
                Это неудобно. В каких-то случаях — да, если речь идет о нескольких мерах, на поведение которых можно повлиять с помощью специального «настроечного» измерения.
                Здесь речь идет о периоде, который должен влиять на 99,9% показателей, за исключением каких-нибудь счетчиков по товарной или клиентской базе.
                Когда пользователей сотни и показателей тоже, то для каждого варианта формул не напасешься. Сегодня им надо так, а завтра иначе. Если прописывать в каждой мере все эти OR'ы и AND'ы, то листинг выражений станет такой, что проще застрелиться, чем поддерживать этого мутанта.
                  0
                  неудобно пользоваться экселем для BI когда столько пользователей, это просто мазохизм.
                  Tableau / Looker / PowerBI все делают так, как я описал (custom calculation & filter) и прекрасно масшатибируются
                    0
                    Когда нужна красивая визуализация, то да, когда нужны простыни таблиц, то нет. Но эта тема уже из другой области.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое