Несколько сводных таблиц на одном листе excel
Добрый день!
Задача банальна:
имеем:
1). лист "спецификация" с номенклатурой и количеством
2). лист "прайс" с ценами
3). лист "КП", в который собираем данные
Необходимо:
на лист "КП" вытащить номенклатуру по группам "Профиль", "Уплотнители", "Комплектующие", "Крепеж", "Заполнения".
Количество строк в каждой группе-таблице может меняться в зависимости от данных в листе "спецификация".
Ключевое поле - артикул.
А примере все сделано вручную - так должен выглядеть конечный результат.
Добрый день!
Задача банальна:
имеем:
1). лист "спецификация" с номенклатурой и количеством
2). лист "прайс" с ценами
3). лист "КП", в который собираем данные
Необходимо:
на лист "КП" вытащить номенклатуру по группам "Профиль", "Уплотнители", "Комплектующие", "Крепеж", "Заполнения".
Количество строк в каждой группе-таблице может меняться в зависимости от данных в листе "спецификация".
Ключевое поле - артикул.
А примере все сделано вручную - так должен выглядеть конечный результат.
Необходимо:
на лист "КП" вытащить номенклатуру по группам "Профиль", "Уплотнители", "Комплектующие", "Крепеж", "Заполнения".
Количество строк в каждой группе-таблице может меняться в зависимости от данных в листе "спецификация".
Ключевое поле - артикул.
А примере все сделано вручную - так должен выглядеть конечный результат.
Заранее спасибо. Автор - jack-b82
Дата добавления - 29.05.2015 в 14:43
jack-b82, Добрый вечер
В лист спецификация вставил пару доп столбцов,которые берут данные с прайса.
Создал лист 1 на нем сводная таблица. в поле поставщик выбирайте фильтр по группам "Профиль", "Уплотнители", "Комплектующие", "Крепеж", "Заполнения".
Если вам надо чтобы было несколько таких таблиц, то просто скопируйте все строки и выбирайте другую группу, и т.д
jack-b82, Добрый вечер
В лист спецификация вставил пару доп столбцов,которые берут данные с прайса.
Создал лист 1 на нем сводная таблица. в поле поставщик выбирайте фильтр по группам "Профиль", "Уплотнители", "Комплектующие", "Крепеж", "Заполнения".
Если вам надо чтобы было несколько таких таблиц, то просто скопируйте все строки и выбирайте другую группу, и т.д Richman
С Уважением, Richman
В лист спецификация вставил пару доп столбцов,которые берут данные с прайса.
Создал лист 1 на нем сводная таблица. в поле поставщик выбирайте фильтр по группам "Профиль", "Уплотнители", "Комплектующие", "Крепеж", "Заполнения".
Если вам надо чтобы было несколько таких таблиц, то просто скопируйте все строки и выбирайте другую группу, и т.д Автор - Richman
Дата добавления - 29.05.2015 в 23:38
Richman, добрый день!
Как можно вывести в сводную таблицу доп. поле, к котором будет ставится скидка по общему итогу?
Richman, добрый день!
Как можно вывести в сводную таблицу доп. поле, к котором будет ставится скидка по общему итогу? jack-b82
Как можно вывести в сводную таблицу доп. поле, к котором будет ставится скидка по общему итогу? Автор - jack-b82
Дата добавления - 01.06.2015 в 11:21
Время от времени необходим повторный анализ одного и того же набора данных. [1] В большинстве случаев это требует создания отдельных сводных таблиц на основе одного и того же источника данных. При создании сводной таблицы исходный набор данных сохраняется в специальной области памяти, которая называется кешем сводной таблицы. Каждая последующая создаваемая сводная таблица увеличивает размер файла рабочей книги и повышает требования к ресурсам компьютера. Невольно возникает вопрос: почему бы не применять единожды заполненный кеш сводной таблицы в качестве источника данных сразу для многих сводных таблиц? Что мешает создавать все сводные таблицы, основанные на одном наборе данных, после однократного занесения этого набора данных в кеш-память? К тому же такая методика позволит существенно уменьшить размер конечного файла и снизит нагрузку на оперативную память.
В версии Excel 2007 и более ранних на этапе создания сводной таблицы, основанной на наборе данных, который используется в другой сводной таблице, программа автоматически предлагала применять информацию, сохраненную в кеш-памяти. В Excel 2013 эта возможность отсутствует.
При создании новой сводной таблицы в Excel 2013 автоматически используется общий кеш сводных таблиц Excel. При этом экономятся оперативная память и место на диске. Если сгруппировать одну сводную таблицу по месяцу и году, все остальные сводные таблицы, применяющие общий кеш, будут сгруппированы аналогичным образом. Если же нужно сгруппировать одну сводную таблицу по месяцу, а вторую по неделе, придется создавать отдельный кеш.
Чтобы создать в Excel отдельный кеш для сводной таблицы, выполните следующие действия.
Рис. 1. Если не хотите экономить память, щелкните на кнопке Нет; новая сводная таблица будет использовать собственный кеш, что развяжет вам руки (чтобы увеличить изображение щелкните на рисунке правой кнопкой мыши и выберите Открыть картинку в новой вкладке).
На экране появится пустая сводная таблица, использующая собственный кеш.
Если в вашем распоряжении имеется сводная таблица, можно воспользоваться альтернативным способом создания кеша сводной таблицы. Скопируйте и вставьте имеющуюся таблицу в новую рабочую книгу, а затем скопируйте и вставьте сводную таблицу обратно в исходную рабочую книгу.
Не стоит забывать о том, что в использовании общего для нескольких сводных таблиц кеша есть определенные недостатки. Например, предположим, что вы создали на основе данных одного кеша две сводные таблицы. Описанные ниже действия приведут к изменениям в обеих сводных таблицах.
- Обновление данных сводной таблицы. Нельзя обновить одну сводную таблицу отдельно от другой.
- Добавление вычисляемого поля. При создании вычисляемого поля в одной сводной таблице автоматически создается вычисляемое поле и во второй сводной таблице.
- Добавление вычисляемого элемента. При создании вычисляемого элемента в одной сводной таблице автоматически создается вычисляемый элемент и во второй сводной таблице.
- Группировка и разгруппировка полей. Любые операции по группировке и разгруппировке данных в одной таблице автоматически выполняются и в другой. В частности, можно сгруппировать в одной сводной таблице поле данных, содержащее даты в недели. Это же поле будет сгруппировано в другой сводной таблице автоматически.
Несмотря на то что ни один из описанных выше эффектов не является критическим при совместном использовании кеша сводных таблиц, с этим все же нужно считаться, особенно при выборе источника исходных данных.
[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 2.
Нередко исходные данные хранятся не в одном диапазоне данных, а в нескольких, или на разных листах, а то и в различных книгах… Не говоря уже данных, хранящихся не в Excel, а в текстовых файлах, таблицах Access или SQL Server. В этой заметке будет рассмотрены приемы работы с множественными диапазонами, т.е. с отдельными наборами данных, расположенными в одной рабочей книге. Эти наборы либо разделены пустыми ячейками (рис. 1), либо находятся на разных рабочих листах. [1] В следующей заметке будут рассмотрено создание сводной таблицы на основе внешних источников данных.
Рис. 1. Лист с тремя диапазонами данных, которые нужно свести вместе, чтобы проанализировать их как единую группу
Если нужно проанализировать данные, находящиеся в нескольких диапазонах, можно воспользоваться сводными таблицами, созданными на основе нескольких диапазонов консолидации. Концепция нескольких диапазонов консолидации появилась в версиях, предшествующих Excel 2003, когда каждый лист Excel включал не более 65 536 строк. В те уже далекие времена большие таблицы данных, превышающие предельное количество строк, приходилось разбивать на несколько таблиц. Это позволяло хранить данные в одной рабочей книге, но в то же время делало невозможным создание стандартной сводной таблицы, основанной на наборе данных, поскольку эти данные находились на разных листах.
Также можно было создавать сводные таблицы на основе нескольких диапазонов консолидации. При этом данные помещались в единый кеш сводной таблицы. В результате облегчалось выполнение анализа данных. Однако в более ранних версиях Excel возможности консолидация была весьма ограничены (см., например, Excel. Сводная таблица на основе нескольких листов).
Предположим, что в вашем распоряжении имеются три диапазона, которые требуется свести вместе для анализа в качестве единой группы (см. рис. 1). Можно создать сводную таблицу, используя множественные диапазоны консолидации. Это позволяет легко и быстро консолидировать все данные из выбранных диапазонов в отдельной сводной таблице.
Чтобы приступить к сведению данных в одну таблицу, запустите классический мастер сводных таблиц и диаграмм. Для выполнения этой задачи нажмите комбинацию клавиш Alt+D+P. К сожалению, эта комбинация клавиш предназначена для англоязычной версии Excel 2013. В русскоязычной версии ей соответствует комбинация клавиш Alt+Д+Н. Но она по неизвестным мне причинам не работает. Тем не менее, можно вывести старый добрый мастер сводных таблиц на панель быстрого доступа, см. Использование мастера сводных таблиц. После запуска мастера установите переключатель в нескольких диапазонах консолидации (рис. 2). Кликните Далее.
Рис. 2. Мастер сводных таблиц и диаграмм; окно 1 из 3
На следующем шаге мастера нужно решить, позволить программе создать одно поле страницы или несколько собственных полей страницы. Вы заметите, что поля страницы, создаваемые в Excel, чаще всего малопонятны или вообще бессмысленны, поэтому практически во всех случаях предпочтительнее создавать собственные поля страниц (рис. 3). Щелкните на кнопке Далее.
Рис. 3. Мастер сводных таблиц и диаграмм; окно 2 из 3
Теперь нужно указать каждый набор данных. Выделите диапазон первого набора данных и щелкните на кнопке Добавить (рис. 4). Чтобы сводная таблица создавалась корректно, первая строка каждого диапазона должна включать в себя заголовки столбцов. Выделите остальные диапазоны и добавьте их в список. Обратите внимание на то, что каждый набор данных представляет отдельный регион (Север, Юг и Запад). Когда сводная таблица объединит все три набора данных, вам потребуется заново проанализировать каждый регион. Чтобы иметь такую возможность, пометьте каждый диапазон в списке отдельным именем, идентифицирующим набор данных этого диапазона. В результате будет создано поле страницы, позволяющее выполнять фильтрацию данных для каждого региона.
Рис. 4. Выделяйте по одному диапазону и щелкайте на кнопке Добавить
Прежде чем создавать поле страницы Регион, укажите общее количество создаваемых полей страницы. Для идентификатора регионов требуется создать только одно поле страницы, так что щелкните на переключателе 1 (рис. 5). При этом активизируется текстовое поле Первое поле. Можно создать до четырех полей страницы. На следующем этапе нужно пометить каждый диапазон. Щелкните на первом диапазоне в списке и введите в поле Первое поле имя региона – Север. Повторите эту операцию для остальных регионов. Определившись с регионами, щелкните на кнопках Далее.
Рис. 5. Создание поля страницы Регион
На последнем шаге определите местоположение сводной таблицы. Выберите переключатель Новый лист и щелкните на кнопке Готово. Итак, вы успешно объединили три источника данных в одной сводной таблице (рис. 6).
Рис. 6. Сводная таблица, содержащая данные из трех источников
Структура сводной таблицы с несколькими диапазонами консолидации
Поле Строка всегда создается на основе первого столбца источника данных. Отметим, что на рис. 1 первым в источнике данных был столбец Направление деятельности. Поле Строка в новой сводной таблице как раз соответствует ей.
Поле Столбец включает остальные столбцы источника данных. Сводные таблицы, использующие несколько диапазонов консолидации, комбинируют все поля из исходных наборов данных (без первого столбца, который используется полем Строка) в некое «суперполе» с именем Столбец. Поля исходных наборов данных становятся элементами данных поля Столбец. В сводной таблице, представленной на рис. 6, в поле Столбец изначально применяется функция КОЛИЧЕСТВО. Если задать для поля Столбец функцию СУММ, это повлияет на все элементы данных поля Столбец.
Рис. 7. Элементы данных в поле Столбец интерпретируются как один объект. Замена функции КОЛИЧЕСТВО поля Столбец функцией СУММ выполняется по отношению ко всем элементам поля
Поле Значение содержит значения для всех данных поля Столбец. Обратите внимание на то, что даже те поля, которые изначально в наборе данных были текстовыми, трактуются как поля с числовыми значениями. Ярким примером является поле Менеджер направления (см. рис. 7). Несмотря на то что это поле содержало имена и фамилии менеджеров из исходного набора данных, теперь эти записи трактуются в сводной таблице как числа.
Как уже говорилось ранее, сводные таблицы, использующие несколько диапазонов консолидации, объединяют поля исходных наборов данных (кроме первого поля), делая их элементами данных поля Столбец. Поэтому, хотя вы и можете определять такие поля, как Менеджер направления, в качестве текстовых с собственными элементами данных, они не будут больше хранить эти данные. Эти поля будут преобразованы в элементы данных со значениями. В результате поля, изначально содержащие текст или даты, отображают в сводной таблице ничего не значащие числовые значения. Обычно эти поля лучше всего скрыть во избежание конфликтов.
[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.
Если вы строите несколько сводных таблиц на основе одного источника, то, возможно, уже сталкивались с одним мелким, но неприятным моментом: Excel не позволяет независимо группировать данные в таких сводных.
Допустим, мы построили две отдельных сводных таблицы для анализа выручки и объемов продаж:
Но выручку мы хотим видеть по месяцам, а объем продаж по кварталам, поэтому дальше логично применить группировку. Щелкаем правой кнопкой мыши по датам в первой сводной, выбираем Группировать - Месяцы - ОК и выручка действительно начинает отображаться по месяцам, как мы и хотели. Но и во второй таблице даты тоже сгруппировались по месяцам, хотя мы этого совершенно не просили:
И наоборот, если попытаться аналогичным способом сгруппировать даты во второй сводной по кварталам, то перегруппируется и первая сводная, теряя текущую группировку по месяцам.
Дело в том, что при построении любой сводной таблицы Microsoft Excel создает в файле так называемый кэш (Pivot Cache) - область памяти, куда загружаются исходные данные для анализа и вычислений. И когда вы делаете вторую, третью и т.д. сводные по той же исходной таблице Excel услужливо использует уже сформированный ранее для первой сводной кэш, а не создает новый.
С одной стороны, это правильно: данные не дублируются, файл меньше весит и быстрее считается. С другой, мы имеем ту самую проблему с группировкой, изменение которой в одной из сводных влияет на все остальные. Что же делать?
Способ 1. Строим сводную правильно
Чтобы построить сводную таблицу на новом кэше придется воспользоваться старым добрым Мастером сводных таблиц и диаграмм (Pivot Table and Chart Wizard) из версий Excel 97-2003. Как ни странно, но этот "устаревший" инструмент умеет при создании сводной выбирать - делать ли независимую сводную или сводную на основе уже имеющегося кэша. Кнопку для этого Мастера убрали из интерфейса еще в 2007 году, но он до сих пор доступен для совместимости и вполне работоспособен.
Щелкните правой кнпокой мыши по панели быстрого доступа в левом верхнем углу и выберите команду Настройка панели быстрого доступа (Customize Quick Access Toolbar) . Затем в выпадающем списке в верхней части окна выберите Все команды (All Commands) , найдите Мастер сводных таблиц и диаграмм и добавьте на панель:
Теперь можно строить сводную. Ставим активную ячейку в таблицу с данными или выделяем ее, жмем на добавленную кнопку и проходим последовательно шаги Мастера, выбрав на первом создание именно независимого отчета на собственном кэше:
Excel будет настойчиво предлагать сделать сводную на том же кэше - отказываемся и доходим до финала:
Все, теперь группировка этой сводной не будет влиять на соседнюю и обе таблицы можно сгруппировать по-разному:
Способ 2. Отвязываем уже созданную сводную от общего кэша
Если у вас уже есть готовая сводная, и создавать ее заново с помощью Мастера вы не хотите, то можно отвязать ее от общего кэша, используя тактическую хитрость:
- Скопируйте сводную в новый пустой файл
- Обновите сводную (правой кнопкой мыши по ней - Обновить)
- Скопируйте ее обратно на старое место
Такой бессмысленный, на первый взгляд, "финт ушами" заставляет Excel создать для сводной отдельную копию кэша, т.е. делает ее независимой.
Если поставить активную ячейку в сводную и запустить этот макрос с помощью кнопки Макросы на вкладке Разработчик или сочетания клавиш Alt+F8, то текущая сводная таблица будет отвязана от общего кэша, и вы сможете группировать ее независимо.
Нюансы
- Если у сводных таблиц не общий кэш, то их не получится одновременно фильтровать срезами и шкалой.
- Посмотреть количество кэшей в книге можно, если в редакторе Visual Basic (Alt+F11) открыть консоль через меню View - Immediate и ввести туда команду ? Activeworkbook.PivotCaches.Count и нажать на Enter:
При создании сложных отчетов и, особенно, дашбордов в Microsoft Excel, весьма часто возникает необходимость одновременной фильтрации сразу нескольких сводных таблиц. Давайте разберёмся, как такое можно можно реализовать.
Способ 1. Общий срез для фильтрации сводных на одном источнике данных
Если сводные построены по одной исходной таблице данных, то проще всего использовать для их одновременной фильтрации срез - графический кнопочный фильтр, подключенный сразу ко всем сводным таблицам.
Чтобы его добавить, выделите любую ячейку в одной из сводных и на вкладке Анализ выберите команду Вставить срез (Analyze - Insert slicer) . В открывшемся окошке пометьте галочками те столбцы, по которым вы хотите фильтровать данные и нажмите ОК:
Созданный срез будет, по умолчанию, фильтровать только ту сводную, для которой он был создан. Однако, воспользовавшись кнопкой Подключения к отчетам (Report connections) на вкладке Срез (Slicer) мы можем легко добавить к списку фильтруемых таблиц другие сводные:
Способ 2. Общий срез для фильтрации сводных на разных источниках
Если ваши сводные были построены не по одной, а по разным исходным таблицам данных, то приведённый выше способ не сработает, т.к. в окне Подключения к отчётам отображаются только те сводные, которые были построены по одному источнику.
Однако, можно легко обойти это ограничение, если использовать Модель Данных (мы подробно разбирали её в этой статье). Если загрузить наши таблицы в Модель и связать их там, то фильтрация станет распространяться на обе таблицы одновременно.
Допустим, что в качестве исходных данных у нас есть две таблицы по продажам и транспортным расходам:
Предположим, что перед нами стоит задача по каждой из них построить свою сводную и фильтровать их затем одновременно по городам общим срезом.
1 . Превращаем наши исходные таблицы в динамические "умные" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) и даём им имена таблПродажи и таблТранспорт на вкладке Конструктор (Design) .
2 . Загружаем по очереди обе таблицы в Модель с помощью кнопки Добавить в модель данных (Add to Data Model) на вкладке Power Pivot.
Напрямую связать эти таблицы в Модели не получится, т.к. пока Power Pivot поддерживает только тип связей "один-ко-многим", т.е. требует, чтобы в одной из таблиц не было дубликатов в столбце, по которому мы связываем. У нас же в обеих таблицах в поле Город присутствуют повторения. Так что потребуется создать ещё одну промежуточную таблицу-справочник со списком уникальных названий городов из обеих таблиц. Проще всего это сделать с помощью функционала надстройки Power Query, которая встроена в Excel начиная с 2016 версии (а для Excel 2010-2013 бесплатно скачивается с сайта Microsoft).
3 . Выделив любую ячейку внутри "умной" таблицы, загружаем их по очереди в Power Query кнопкой Из таблицы / диапазона на вкладке Данные (Data - From table/range) и затем в окне Power Query выбираем на Главной команды Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to. ) и вариант импорта Только создать подключение (Only create connection) :
4 . Соединяем обе таблицы в одну командой Данные - Объединить запросы - Добавить (Data - Combine queries - Append) . Совпадающие по названиям в шапке колонки встанут друг под друга (как столбец Город), а не совпадающие будут разнесены в разные столбцы (но для нас это не важно).
5 . Удаляем все столбцы, кроме колонки Город, щёлкнув по её заголовку правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) и затем удаляем все дубликаты названий городов, щёлкнув ещё раз правой кнопкой мыши по заголовку столбца и выбрав команду Удалить дубликаты (Remove duplicates) :
6 . Созданный список-справочник выгружаем в Модель Данных через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to. ) и выбираем вариант Только создать подключение (Only create connection) и - самое главное! - включаем флажок Добавить эти данные в модель данных (Add this data to Data Model) :
7 . Теперь можем, вернувшись в окно Power Pivot (вкладка Power Pivot - кнопка Управление), переключиться в Представление диаграммы (Diagram view) и связать наши таблицы продаж и траспортных расходов через созданный промежуточный справочник по городам (перетаскиванием полей между таблицами):
8 . Теперь можно создать все требуемые сводные таблицы по созданной модели с помощью кнопки Сводная таблица (Pivot Table) на Главной (Home) вкладке в окне Power Pivot и, выделив любую ячейку в любой сводной, на вкладке Анализ добавить срез кнопкой Вставить срез (Analyze - Insert Slicer) и выбрать для среза в списке поле Город в добавленном справочнике:
Теперь, нажав на знакомую кнопку Подключения к отчетам на вкладке Срез (Slicer - Report connections) мы увидим все наши сводные, т.к. построены они теперь по связанным исходным таблицам. Останется включить недостающие флажки и нажать на ОК - и наш срез начнёт фильтровать все выбранные сводные таблицы одновременно.
Читайте также: