Excel. Сводная таблица на основе нескольких листов
Если вы столкнулись с необходимостью создать сводную таблицу на основе данных, размещенных на нескольких листах одной книги (или разных книг), вас ждет разочарование. Стандартным образом Excel делает это «через одно место»… L
Да в Excel есть такая опция в мастере сводных таблиц, но привычно (как и для обычных сводных) она работает только для одного набора данных, например (см. файл Сводная_листы_один набор.xlsx ):
У вас есть список клиентов и объем продаж по ним по кварталам; данные за один квартал расположены на отдельном листе. Создадим сводную таблицу, консолидирующую данные за 4 квартала.
1. Поименуйте ваши исходные массивы данных; в принципе, это не обязательно, но, с другой стороны, это поднимает вашу работу на новый уровень J , облегчает восприятие информации другими пользователями, а также упрощает внесение любых изменений / дополнений в исходные данные в будущем (как создать динамически изменяемые именованные массивы см. здесь):
2. Запустите мастер сводных таблиц (как вывести мастер на панель быстрого доступа см. здесь); выберите опцию «в нескольких диапазонах консолидации », нажмите «далее»:
3. Оставьте предлагаемую по умолчанию опцию «Создать одно поле страницы»
4. Введите имя первого диапазона, нажмите «Добавить»:
5. Добавьте все четыре диапазона, нажмите «Далее»:
6. Оставьте предлагаемую по умолчанию опцию «новый лист», нажмите «Готово»:
Сводная таблица на основе данных, размещенных на четырех листах, создана. Сводная таблица (как и обычно) содержит четыре области. Вот только названия полей не соответствуют тому, что было в исходных данных:
http://pandia.ru/text/79/437/images/image010_66.jpg" width="348" height="233 id=">
Названия строк вместо «Клиент» – «Строка»; название столбца «Продажи» упрятано внутрь названия «Столбец»:
http://pandia.ru/text/79/437/images/image012_56.jpg" width="356 height=191" height="191">
В остальном с полученной сводной можно работать, как обычно. Например:
http://pandia.ru/text/79/437/images/image014_37.jpg" width="273" height="82 id=">
1. Поименуйте ваши исходные массивы данных.
2. Создайте сводную таблицу, как описано выше
Вот, что получилось:
EN-US">zip
(внутри два файла Excel 2007 с поддержкой макросов: от Кирилла Лапина и с моим набором данных) сводные.zipЧтобы объединить таблицы в Excel
, расположенные на разных листах
или в других книгах Excel
, составить общую таблицу, нужно сделать сводные таблицы Excel
. Делается это с помощью специальной функции.
Сначала нужно поместить на панель быстрого доступа кнопку функции «Мастер сводных таблиц и диаграмм».
Внимание!
Это не та кнопка, которая имеется на закладке «Вставка».
Итак, нажимаем на панели быстрого доступа на функцию «Другие команды», выбираем команду «Мастер сводных таблиц и диаграмм».
Появился значок мастера сводных таблиц. На рисунке ниже, обведен красным цветом.
Теперь делаем сводную таблицу из нескольких отдельных таблиц.
Как создать таблицу в Excel
, смотрите в статье "Как сделать таблицу в Excel ".
Нам нужно объединить данные двух таблиц, отчетов по магазинам, в одну общую таблицу.
Для примера возьмем две такие таблицы Excel с отчетами по наличию продуктов в магазинах на разных листах.
Первый шаг. Встаем на лист с первой таблицей.
Нажимаем на кнопку «Мастер сводных таблиц и диаграмм». В появившемся диалоговом окне указываем «в нескольких диапазонах консолидации». Указываем – «сводная таблица».
Нажимаем «Далее».
На втором шаге указываем «Создать поля страницы» (это поля фильтров, которые будут расположены над таблицей). Нажимаем кнопку «Далее».
Последний, третий шаг. Указываем диапазоны всех таблиц в строке «Диапазон…», из которых будем делать одну сводную таблицу.
Выделяем первую таблицу вместе с шапкой
. Затем нажимаем кнопку «Добавить», переходим на следующий лист и выделяем вторую таблицу с шапкой. Нажимаем кнопку «Добавить».
Так указываем диапазоны всех таблиц, из которых будем делать сводную. Чтобы все диапазоны попали в список диапазонов, после ввода последнего диапазона, нажимаем кнопку «Добавить».
Теперь выделяем из списка диапазонов первый диапазон. Ставим галочку у цифры «1» - первое поле страницы сводной таблицы станет активным. Здесь пишем название параметра выбранного диапазона. В нашем примере, поставим название таблицы «Магазин 1».
Затем выделяем из списка диапазонов второй диапазон, и в этом же первом окне поля пишем название диапазона. Мы напишем – «Магазин 2». Так подписываем все диапазоны.
Здесь видно, что в первом поле у нас занесены названия обоих диапазонов. При анализе данные будут браться из той таблицы, которую мы выберем в фильтре сводной таблицы. А если в фильтре укажем – «Все», то информация соберется из всех таблиц.
Нажимаем «Далее».
Устанавливаем галочку в строке «Поместить таблицу в:», указываем - «новый лист». Лучше поместить сводную таблицу на новом листе, чтобы не было случайных накладок, перекрестных ссылок, т.д.
Нажимаем «Готово». Получилась такая таблица.
Если нужно сделать выборку по наименованию товара, выбираем товар в фильтре «Название строк».
Можно выбрать по складу – фильтр «Название столбца», выбрать по отдельному магазину или по всем сразу – это фильтр «Страница 1».
Когда нажимаем на ячейку сводной таблицы, появляется дополнительная закладка «Работа со сводными таблицами». В ней два раздела. С их помощью можно изменять все подписи фильтров, параметры таблицы.
Например, нажав на кнопку «Заголовки полей», можно написать свое название (например – «Товар»).
Если нажимаем на таблицу, справа появляется окно «Список полей сводной таблицы».
Здесь тоже можно настроить много разных параметров.
Эта сводная таблица связана с исходными таблицами. Если изменились данные в таблицах исходных, то, чтобы обновить сводную таблицу, нужно из контекстного меню выбрать функцию «Обновить».
Нажав правой мышкой, и, выбрав функцию «Детали», можно увидеть всю информацию по конкретному продукту. Она появится на новом листе.
В Excel есть способ быстро и просто посчитать (сложить, вычесть, т.д.) данные из нескольких таблиц в одну. Подробнее, смотрите в статье "Суммирование в Excel"
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:
Чем примечательна эта сводная таблица? Обратите внимание: в списке полей справа отображается не одна таблица, а целая коллекция таблиц, содержащих поля, которые могут быть объединены в отдельную сводную таблицу для анализа данных в различных представлениях. Нет никакой необходимости в форматировании или подготовке данных вручную. Вы можете создать сводную таблицу, основанную на связанных таблицах, сразу после импорта данных.
Чтобы объединить несколько таблиц в списке полей сводной таблицы:
Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ , СТРОКИ или СТОЛБЦЫ .
Импорт таблиц из других источников
Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
Реляционные базы данных - это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
Использование модели данных для создания новой сводной таблицы
Возможно, вы создали связи между таблицами в модели данных и теперь готовы использовать эти данные для анализа. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:
Чем примечательна эта сводная таблица? Обратите внимание: в списке полей справа отображается не одна таблица, а целая коллекция таблиц, содержащих поля, которые могут быть объединены в отдельную сводную таблицу для анализа данных в различных представлениях. Нет никакой необходимости в форматировании или подготовке данных вручную. Вы можете создать сводную таблицу, основанную на связанных таблицах, сразу после импорта данных.
Чтобы объединить несколько таблиц в списке полей сводной таблицы:
Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ , СТРОКИ или СТОЛБЦЫ .
Импорт таблиц из других источников
Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
Реляционные базы данных - это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
Использование модели данных для создания новой сводной таблицы
Возможно, вы создали связи между таблицами в модели данных и теперь готовы использовать эти данные для анализа. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
