Разделение таблицы по листам
В Microsoft Excel есть много инструментов для сборки данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функция ДВССЫЛ (INDIRECT) , надстройки Power Query и Power Pivot и т.д. С этой стороны баррикад всё выглядит неплохо.
Давайте подробно рассмотрим, как это можно реализовать.
Постановка задачи
Имеем в качестве исходных данных вот такую таблицу размером больше 5000 строк по продажам:
Задача: разнести данные из этой таблицы по городам на отдельные листы этой книги. Т.е. на выходе нужно получить на каждом листе только те строки из таблицы, где продажа была в соответствующем городе:
Подготовка
Чтобы не усложнять код макроса и сделать его максимально простым для понимания, выполним пару подготовительных действий.
Поскольку новые листы в Excel по умолчанию создаются перед (левее) текущего (предыдущего), то имеет смысл также отсортировать города в этом справочнике по убыванию (от Я до А) — тогда после создания листы-города расположатся по алфавиту.
Во-вторых, преобразуем обе таблицы в динамические («умные»), чтобы с ними было проще работать. Используем команду Главная — Форматировать как таблицу (Home — Format as Table) или сочетание клавиш Ctrl + T . На появившейся вкладке Конструктор (Design) назовём их таблПродажи и таблГорода, соответственно:
Способ 1. Макрос для деления по листам
На вкладке Разработчик (Developer) нажмите на кнопку Visual Basic или используйте сочетание клавиш Alt + F11 . В открывшемся окне редактора макросов вставьте новый пустой модуль через меню Insert — Module и скопируйте туда следующий код:
Здесь с помощью цикла For Each . Next реализован проход по ячейкам справочника таблГорода, где для каждого города происходит его фильтрация (метод AutoFilter) в исходной таблице продаж и затем копирование результатов на новый созданный лист. Попутно созданный лист переименовывается в то же имя города и на нем включается автоподбор ширины столбцов для красоты.
Запустить созданный макрос в Excel можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или сочетанием клавиш Alt + F8 .
Способ 2. Создаем множественные запросы в Power Query
У предыдущего способа, при всей его компактности и простоте, есть существенный недостаток — созданные макросом листы не обновляются при изменениях в исходной таблице продаж. Если обновление «на лету» необходимо, то придется использовать связку VBA+Power Query, а точнее — создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.
Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each . Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:
После его запуска мы увидим те же листы по городам, но формировать их будут уже созданные запросы Power Query:
При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши — команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data — Refresh All) .
[expert_bq id=»1570″]Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations) . В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:Создание базы данных в Excel
Если ни один шаблон не подошел, у вас есть возможность составить таблицу самостоятельно. Я расскажу, как сделать это правильно, проведу вас по основным шагам – установке границ таблицы, заполнению ячеек, добавлению строки «Итог» и автоподсчету данных в колонках.
[expert_bq id=»1570″]Первый вариант работы самый распространенный на практике; предыдущие описания примеров относятся как раз к данным, хранящимся в диапазоне ячеек. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
В базе Excel есть макеты под разные цели: для бизнеса, бухгалтерии, ведения домашнего хозяйства (например, списки покупок), планирования, учета, расписания, организации учебы и прочего. Достаточно выбрать то, что больше соответствует вашим задачам.
Разделение таблицы по листам
- В одном столбце могут располагаться несколько полей сводной таблицы, выделенных отступами (сжатая форма).
- Срезы сводной таблицы позволяют визуально отображать текущий фильтрующий набор значений.
- Измерения в области фильтра поддерживают множественный выбор.
- Элементы измерения могут быть скрыты/отображены через кнопки, расположенные в той же ячейке, что и сам заголовок.
- Появилось несколько новых параметров в свойствах поля и таблицы.
- Доступны стили сводных таблиц, позволяющие изменить внешний вид отчетов в любой момент времени.
Николай, подскажите, пожалуйста, как бороться с ошибкой возникающей при переименовании первого листа (либо ставкой листа в начало книги).
Ошибка возникает при выполнении строчки:
#»Переименованные столбцы» = Table.RenameColumns(#»Измененный тип»,>),
Начните Создавать Формулу в Excel
Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each . Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:
OLAP-кубы
- Для начала надо выделить на листе место под будущую таблицу. Для этого выбираем необходимое количество ячеек. При этом они могут быть как заполненными, так и пустыми;
- Переходим на вкладку «Вставка» , выбираем раздел «Таблицы» и клацаем на пункт соответствующего названия;
В базе Excel есть макеты под разные цели: для бизнеса, бухгалтерии, ведения домашнего хозяйства (например, списки покупок), планирования, учета, расписания, организации учебы и прочего. Достаточно выбрать то, что больше соответствует вашим задачам.