Сводная таблица из нескольких файлов Excel
«Пример создания Сводной таблицы макросом VBA Excel (эксель)»
В процессе профессиональной деятельности, оператор-аналитик многократно выполняет перегруппировку исходных (сырых) данных в тот формат, который удобен ему для анализа (например: таблица, график, диаграмма). Это может быть фильтрация, сортировка, создание сводных таблиц
и т.д. …
Макрос — это программа преобразования «исходных данных» в нужные «результирующие данные».
Исходных данных может быть много! Возможно, это будет множество листов во множестве книг Excel ! Результатов тоже может быть много (я имею в виду получение промежуточных результатов), но цель (или конечный результат) должна быть ясна и четко обозначена… Один макрос может прочитать мегабайты данных из разных источников (обязательно предварительно указанных) и создать несколько новых файлов (или дописать информацию в существующие файлы)…
Сфера деятельности специалиста-аналитика может быть любой (определяется компанией-работодателем), но в любом случае это работа с большими объемами информации. Как правило, информация поступает в виде однотипных файлов, генерируемых системами автоматического слежения за процессами или статистической отчетностью подразделений компании.
В Вашей организации так? Или по-другому?
Такой инструмент дает возможность быстро использовать возможности макроса для многих файлов (регулярно поступающих к аналитику) без переноса VBA-модулей в них.
Такой подход накладывает несколько более строгие требования к задаваемым ключевым данным для работы макроса (тем данным, которые вводятся над исполнительной командной кнопкой). И хотя (в случае их отсутствия) ничего страшного не произойдет и пользователь просто получит сообщение об ошибке, но желательно средствами макроса подсказать пользователю, что им «не так сделано» и как удовлетворить справедливые запросы макроса на данные…
Видимо, название листа с целевыми данными (в отличие от предыдущего файла) было изменено, но Вам всего лишь нужно уточнить (посмотреть) как именуется лист в новом файле и вбить новое название в ячейку «В2». Именно так! Не переименовывать лист под требования макроса, а изменить ключевые данные для макроса… Ведь те, кто создавал для Вас файл данных, по каким-то причинам переименовали Лист…, то есть, с большой вероятностью, и в следующем файле (например, через месяц) Лист будет называться по-новому…
Конечно, не очень сложно указать путь к требуемой книге Excel (ключевые данные для макроса в ячейке «В1»), но удобнее, как только Вы перешли в ячейку «В1» увидеть стандартное диалоговое окно Windows, специально предназначенное для открытия файлов и открыть книгу через него.
При этом в ячейку «В1» автоматически запишется имя открытой книги… В общем, сразу двух зайцев убиваем… И книга открыта, и макрос имеет на 100% безошибочные данные…
Фильтрация данных — это как процесс получения полезного продукта из руды…, то есть отсев всего не нужного и повышение, таким образом, полезности информации… Фильтрация производится по эталонным данным… Вы должны указать, что Вам нужно или что Вам не нужно… То есть можно смело говорить о двух видах фильтров:
Для формирования сводных таблиц средствами макроса придется широко использовать и те, и другие виды фильтров. Например, в определенной колонке листа указан город расположения аптеки… Задача может ставиться так:
Функция формирования фильтров для сводной таблицы (по массиву эталонных строковых значений) написана и есть в коде…
Хочу сразу оговориться, что многие авторитетные сайты, не считают задачу формирования сводной таблицы средствами VBA уместной. По их мнению, прекрасный инструментарий Excel позволяет с наименьшими трудностями обходиться без макросов в этом вопросе… Но, ко мне обращались, представители аналитического сообщества, которым скучно, и неприятно утомительно, из раза в раз устанавливать десятки флажков (checkbox) по нескольким полям большой Базы данных…. Так что все зависит от конкретного случая…. Для кого-то это решение может стать лучшим….
Определение строк сводной таблицы.
На этом этапе важно определить уровни группировки данных (по Вашему желанию или желанию Вашего руководства).
Как видим, здесь в группу «Препарат» попали все виды упаковок данного лекарства.
На этом этапе определяем фильтры, по которым макрос создаст сводную таблицу, но подкорректировать эти фильтры возможно будет и в ручную… Excel для этого предоставляет удобные возможности.
А сейчас формируем положительный фильтр по этим нескольким значениям
Или отрицательный фильтр (результат показан на Рис.4)
Конечно, фильтр, допускающий только одно значение выглядит проще…
А это фильтр, который по умолчанию фильтром не является, так как пропускает любые значения…, но все же есть смысл его добавить к сводной таблице, чтобы иногда… (в некоторых трудно прогнозируемых случаях), можно было его подстроить вручную…
Определение полей сводной таблицы с Итоговыми данными (здесь суммами значений).
Итоговые данные (не обязательно суммирование) всегда представляют исключительный интерес… Правда? Таких полей в сводной таблице может быть несколько, но в моем примере на «листе данных» больше нет числовых полей, поэтому и суммирование проводить больше, как по полю «Количество», не уместно…
И вот, результат…
Безошибочность работы макроса в разы превышает возможности человека… Про скорость я уж вообще не говорю…, Ни каких сравнений…
Скачать файл для тестирования
Условия получения кода? Показать?
Другие примеры на тему «Автоматизация документов Microsoft Office Excel, Word, Access»
Если на этой странице не нашлось того, что Вы так искали…
Не расстраивайтесь, не все потеряно… Смело щелкайте…
