Excel Рабочие Листы Это Таблицы Excel • Работа с данными

Разделение таблицы по листам

В 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:

Созданные запросы для каждого города в Power Query

При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши — команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data — Refresh All) .

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations) . В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

Создание базы данных в Excel

Если ни один шаблон не подошел, у вас есть возможность составить таблицу самостоятельно. Я расскажу, как сделать это правильно, проведу вас по основным шагам – установке границ таблицы, заполнению ячеек, добавлению строки «Итог» и автоподсчету данных в колонках.

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Первый вариант работы самый распространенный на практике; предыдущие описания примеров относятся как раз к данным, хранящимся в диапазоне ячеек. Если же вы хотите что-то уточнить, обращайтесь ко мне!
В базе Excel есть макеты под разные цели: для бизнеса, бухгалтерии, ведения домашнего хозяйства (например, списки покупок), планирования, учета, расписания, организации учебы и прочего. Достаточно выбрать то, что больше соответствует вашим задачам.

Разделение таблицы по листам

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

Николай, подскажите, пожалуйста, как бороться с ошибкой возникающей при переименовании первого листа (либо ставкой листа в начало книги).
Ошибка возникает при выполнении строчки:
#»Переименованные столбцы» = Table.RenameColumns(#»Измененный тип»,>),

Таблица Эксель — Cоздание и настройка работы 2019
Пока ваша формула не закончена, щелкните на вкладке другого листа в нижней части программы. Очень важно, чтобы вы не закрывали формулу, перед тем как вы щелкните по другой ячейке, которую вы хотите вставить в формулу.
эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Очень важно, чтобы вы не закрывали формулу, перед тем как вы щелкните по другой ячейке, которую вы хотите вставить в формулу. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Вы можете продолжать набирать формулу, включая в нее ячейки с других листов и другие ячейки на этом же листе. Продолжайте комбинировать ссылки со всего рабочего документа, на все данные, которые вам нужны.
Фильтруем листы

Начните Создавать Формулу в Excel

Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each . Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:

Подготовка
В Microsoft Excel есть много инструментов для сборки данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функция ДВССЫЛ (INDIRECT) , надстройки Power Query и Power Pivot и т.д. С этой стороны баррикад всё выглядит неплохо.
эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Next реализован проход по ячейкам справочника таблГорода , где для каждого города происходит его фильтрация метод AutoFilter в исходной таблице продаж и затем копирование результатов на новый созданный лист. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Несколько сводных таблиц (или диаграмм) могут отображать данные одного и того же кэша. Этот вариант работы используется для отображения нескольких отчетных форм одних и тех же данных без использования интерфейса настройки измерений. В этом случае при обновлении одной из таблиц автоматически перестраивается и та, что основана на том же кэше.

OLAP-кубы

  • Для начала надо выделить на листе место под будущую таблицу. Для этого выбираем необходимое количество ячеек. При этом они могут быть как заполненными, так и пустыми;
  • Переходим на вкладку «Вставка» , выбираем раздел «Таблицы» и клацаем на пункт соответствующего названия;

В базе Excel есть макеты под разные цели: для бизнеса, бухгалтерии, ведения домашнего хозяйства (например, списки покупок), планирования, учета, расписания, организации учебы и прочего. Достаточно выбрать то, что больше соответствует вашим задачам.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector