Создание Сводной таблицы макросом VBA Excel (большие объемы данных)

В процессе профессиональной деятельности, оператор-аналитик многократно выполняет перегруппировку исходных (сырых) данных в тот формат, который удобен ему для анализа (например: таблица, график, диаграмма). Это может быть фильтрация, сортировка, создание сводных таблиц и т.д. …

Вспомните и проанализируйте свою повседневную работу…
Как часто Вам приходится выполнять однотипные операции с однотипными файлами?
Это и есть - работа по определенному алгоритму…
А значит, эту работу можно запрограммировать и поручить компьютеру…

Макрос – это программа преобразования «исходных данных» в нужные «результирующие данные».

1. Мнение о Средствах (инструментах) специалиста-аналитика
2. Проверка вводимых данных в Excel (ключевых для работы макроса)
3. Диалоговые окна Windows в макросах Word и Excel
4. Фильтры данных (информации из баз данных Excel)
5. Пример создания Сводной таблицы макросом VBA Excel (эксель)

Исходных данных может быть много!
Возможно, это будет множество листов во множестве книг Excel !
Результатов тоже может быть много (я имею в виду получение промежуточных результатов), но цель (или конечный результат) должна быть ясна и четко обозначена…
Один макрос может прочитать мегабайты данных из разных источников (обязательно предварительно указанных) и создать несколько новых файлов (или дописать информацию в существующие файлы)…

Мнение о Средствах (инструментах) специалиста-аналитика

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

Файлы с расширением xlsx, а ранее xls (книги Excel) для этой цели подходят как нельзя лучше и используются чаще обычного.

В Вашей организации так? Или по-другому?

Начиная с MS Office 2007, для работы с макросами используются файлы с другим расширением: xlsm – книга Excel с поддержкой макросов. Поэтому наиболее целесообразно в виде инструментов специалисту-аналитику использовать именно файлы с расширением xlsm. Это будут небольшие файлы, не содержащие больших объемов данных, но содержащие программный код макроса и указывающие какой лист какой книги этому макросу следует обработать…

Такой инструмент дает возможность быстро использовать возможности макроса для многих файлов (регулярно поступающих к аналитику) без переноса VBA-модулей в них.

Проверка вводимых данных в Excel (ключевых для работы макроса)

Такой подход накладывает несколько более строгие требования к задаваемым ключевым данным для работы макроса (тем данным, которые вводятся над исполнительной командной кнопкой). И хотя (в случае их отсутствия) ничего страшного не произойдет и пользователь просто получит сообщение об ошибке, но желательно средствами макроса подсказать пользователю, что им «не так сделано» и как удовлетворить справедливые запросы макроса на данные…

Видимо, название листа с целевыми данными (в отличие от предыдущего файла) было изменено, но Вам всего лишь нужно уточнить (посмотреть) как именуется лист в новом файле и вбить новое название в ячейку «В2». Именно так! Не переименовывать лист под требования макроса, а изменить ключевые данные для макроса… Ведь те, кто создавал для Вас файл данных, по каким-то причинам переименовали Лист…, то есть, с большой вероятностью, и в следующем файле (например, через месяц) Лист будет называться по-новому…

Диалоговые окна Windows в макросах Word и Excel

Конечно, не очень сложно указать путь к требуемой книге Excel (ключевые данные для макроса в ячейке «В1»), но удобнее, как только Вы перешли в ячейку «В1» увидеть стандартное диалоговое окно Windows, специально предназначенное для открытия файлов и открыть книгу через него.

При этом в ячейку «В1» автоматически запишется имя открытой книги… В общем, сразу двух зайцев убиваем… И книга открыта, и макрос имеет на 100% безошибочные данные…

Фильтры данных (информации из баз данных Excel)

Фильтрация данных – это как процесс получения полезного продукта из руды…, то есть отсев всего не нужного и повышение, таким образом, полезности информации…
Фильтрация производится по эталонным данным… Вы должны указать, что Вам нужно или что Вам не нужно…
То есть можно смело говорить о двух видах фильтров:

• положительные фильтры – пропускают в результирующий файл информацию, которая совпала хотя бы с одним из эталонов, а остальную игнорируют…
• отрицательные фильтры – выбрасывают любую информацию, которая совпала хотя бы с одним из эталонов, и только оставшуюся пропускают в результирующий файл…

Для формирования сводных таблиц средствами макроса придется широко использовать и те, и другие виды фильтров.
Например, в определенной колонке листа указан город расположения аптеки…
Задача может ставиться так:

• суммирование данных по 3-5 конкретным городам – резонно использовать положительный фильтр;
• суммирование данных по всем городам, кроме Москвы и Санкт-Петербурга – здесь удобнее отрицательный фильтр;

Функция формирования фильтров для сводной таблицы (по массиву эталонных строковых значений) написана и есть в коде…

Пример создания Сводной таблицы макросом VBA Excel (эксель)

Хочу сразу оговориться, что многие авторитетные сайты, не считают задачу формирования сводной таблицы средствами VBA уместной. По их мнению, прекрасный инструментарий Excel позволяет с наименьшими трудностями обходиться без макросов в этом вопросе… Но, ко мне обращались, представители аналитического сообщества, которым скучно, и неприятно утомительно, из раза в раз устанавливать десятки флажков (checkbox) по нескольким полям большой Базы данных…. Так что все зависит от конкретного случая…. Для кого-то это решение может стать лучшим….

Этапы:

Определение строк сводной таблицы.

На этом этапе важно определить уровни группировки данных (по Вашему желанию или желанию Вашего руководства).

    With svT.PivotFields("Препарат")
       .Orientation = xlRowField
       .Position = 1
    End With
    With svT.PivotFields("Упаковка")
       .Orientation = xlRowField
       .Position = 2
    End With


Как видим, здесь в группу «Препарат» попали все виды упаковок данного лекарства.

Определение фильтров сводной таблицы.

На этом этапе определяем фильтры, по которым макрос создаст сводную таблицу, но подкорректировать эти фильтры возможно будет и в ручную… Excel для этого предоставляет удобные возможности.

'добавляем эталонные строковые значения в массив необходимого размера
ReDim ar(0 To 5)
ar(0) = "Москва":
ar(1) = "г Москва":
ar(2) = "Москва г":
ar(3) = "Москва, ТЦ ""Малая Родина""":
ar(4) = "г Владимир":
ar(5) = "Зеленоград"


А сейчас формируем положительный фильтр по этим нескольким значениям

FilterPlusMinus True, ar, "Город" 'оставляет только указанные города


Или отрицательный фильтр (результат показан на Рис.4)

FilterPlusMinus False, ar, "Город" 'оставляет города, за исключением указанных


Конечно, фильтр, допускающий только одно значение выглядит проще…

'добавление фильтра с одним значением
    With ActiveSheet.PivotTables(tNam).PivotFields("Поставщик")
       .Orientation = xlPageField
       .Position = 1
    End With
    svT.PivotFields("Поставщик").ClearAllFilters
    svT.PivotFields("Поставщик").CurrentPage = "Санофи Россия"


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

'добавление фильтра со всеми значениями (для ручной корректировки в процессе анализа)
    With ActiveSheet.PivotTables(tNam).PivotFields("Плательщик")
       .Orientation = xlPageField
       .Position = 1
    End With
    svT.PivotFields("Плательщик").CurrentPage = "(All)"


Определение полей сводной таблицы с Итоговыми данными (здесь суммами значений).

Итоговые данные (не обязательно суммирование) всегда представляют исключительный интерес… Правда?
Таких полей в сводной таблице может быть несколько, но в моем примере на «листе данных» больше нет числовых полей, поэтому и суммирование проводить больше, как по полю «Количество», не уместно…

svT.AddDataField svT.PivotFields("Количество"), "Сумма по полю Количество", xlSum


И вот, результат...

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

Поэтому, изучать язык программирования VBA очень интересно и выгодно.
Долго…? Времени на изучение жалко…?
В принципе… это не обязательно… Как не обязательно знать электронную начинку телевизора, чтобы использовать этот телевизор по назначению…
Если Вы сотрудничаете с программистом, то Вам останется – указать минимум данных и запустить уже готовый макрос… Все!!!
Пробуйте…

Скачать файл для тестирования

Могу создать макрос для формирования сводной таблицы с данными нескольких листов...

Другие примеры на тему «Автоматизация документов Microsoft Office Excel, Word, Access»

Другие примеры на языке «Visual Basic for application - VBA»



Поделиться в соц сетях:

Если на этой странице не нашлось того, что Вы так искали...

         Не расстраивайтесь, не все потеряно... Смело щелкайте...