Excel: как создать сводную таблицу из нескольких листов
В следующем пошаговом примере показано, как создать сводную таблицу из нескольких листов в Excel.
Шаг 1: введите данные
Предположим, у нас есть электронная таблица с двумя листами, названными неделя1 и неделя2 :
1 неделя:
Неделя 2:
Предположим, мы хотим создать сводную таблицу, используя данные с обоих листов.
Шаг 2. Объедините данные в один лист
Прежде чем мы сможем создать сводную таблицу, используя оба листа, мы должны объединить все данные в один лист.
Для этого мы можем использовать следующую формулу FILTER :
=FILTER(week2!A2:C11, week2!A2:A11<>"") Мы можем ввести эту формулу в ячейку A12 листа week1 :
Эта формула указывает Excel вернуть все строки из листа week2 , где значение в диапазоне A2: A11 этого листа не является пустым.
Все строки из листов неделя1 и неделя2 теперь объединены в один лист.
Шаг 3: Создайте сводную таблицу
Чтобы создать сводную таблицу, щелкните вкладку « Вставка », затем щелкните « Сводная таблица» в группе « Таблицы ».
В появившемся новом окне введите следующую информацию и нажмите OK :
На панели « Поля сводной таблицы », которая появляется в правой части экрана, перетащите « Магазин » в поле «Строки», перетащите « Продукт » в поле «Столбцы» и перетащите « Продажи » в поле «Значения».
Автоматически будет создана следующая сводная таблица:
Окончательная сводная таблица включает данные как из листов неделя 1, так и за неделю 2.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Сводная таблица из нескольких листов
Иногда требуется сводная таблица из нескольких листов. Самый доступный способ просто скопировать данные друг под другом, но иногда это не вариант. В эксель сводная таблица из нескольких листов делается только с помощью «трюков».
Рассмотрим один из самых простых способов реализации, но даже он потребует внимательности и четкого выполнения. Для простоты рассмотрения темы мы будем подразумевать, что все таблицы с исходными данными однотипны, т.е. имеют один и тот же набор столбцов.
1. Создание табличных массивов
Первым делом все таблицы с исходными данными приводим в формат «умной таблицы». Делается – это на вкладке «Вставка», потом выбираем «Таблица» или можно использовать Ctrl+T:
Создание умной таблицы
2. Создание подключения через Microsoft Query
На этом этапе нужно сохранить файл. Затем нужно открыть лист, но котором мы хотим видеть сводную таблицу из нескольких листов. Там заходим в «Данные» и нажимаем «Из других источников», а там «из Microsoft Query»:
Создание подключения Microsoft Query
В окне выбора источника данных выбираем «Excel files» и убираем галку «Использовать мастер запросов» и нажимаем «OK»:
Выбор источника данных Excel files
Выбираем расположение нашей же книги и нажимаем «OK».
В окне «Добавление таблицы» нажимаем «Параметры» и там отмечаем флажок в поле «Системные таблицы», далее выбираем первую таблицу на первом листе и нажимаем «Добавить» и «Закрыть»
Настройка запроса для первого листа
3. Настройка SQL запроса
Останется открытым окно Microsoft Query, в нем дважды кликаем по названию каждого столбца таблицы с первого листа:
Создание запроса SQL
Далее нажимаем кнопку «SQL» и копируем все, что там написано:
Копирование запроса SQL
Потом внизу под существующим запросом пишем слово UNION и вставляем ранее скопированный код. В части кода под Union меняем название листа:
Правка кода для таблицы второго листа
Если Вам нужна сводная болеет чем с двух листов, то соответственно повторяете Union, вставку и редактирование название листа несколько раз.
Далее «OK» на сообщение о невозможности представить результат графически отвечаем положительно.
Далее в окне Microsoft Query нажимаем «Файл» и «Вернуть данные в Microsoft Excel».
В окне «Импорт данных» выбираем место куда поместим сводную и отмечаем точкой «Отчет сводной таблицы».
4. Настройка сводной таблицы
Далее, уже идет обычная настройка сводной таблицы. Как настраивать сводную можно почитать в статье «Сводные таблицы в Microsoft Excel»
Все прекрасно обновляется. Если Вы допишите на любой из листов новые строки, то просто обновите сводную таблицу. Как обновляется сводная таблица можно почитать в статье «Сводные таблицы в Microsoft Excel»
Ещё у нас есть online курс Умные и сводные таблицы, пройдя который Вы получите практические навыки в обработке больших массивов данных в том числе с помощью сводных.
Создание сводной таблицы Excel из нескольких листов
Сводная таблица применяется для быстрого анализа большого объема данных. Она позволяет объединять информацию из разных таблиц и листов, подсчитать общий результат. Этот универсальный аналитический инструмент существенно расширяет возможности программы Excel.
Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.
Сводная таблица в Excel
Для примера используем таблицу реализации товара в разных торговых филиалах.
Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтобы найти величину продаж по каждому отделу, придется посчитать вручную на калькуляторе. Либо сделать еще одну таблицу Excel, где посредством формул показать итоги. Такими методами анализировать информацию непродуктивно. Недолго и ошибиться.
Самое рациональное решение – это создание сводной таблицы в Excel:
- Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
- В меню «Вставка» выбираем «Сводная таблица».
- Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
- Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.
Просто, быстро и качественно.
- Первая строка заданного для сведения данных диапазона должна быть заполнена.
- В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
- В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.
Как сделать сводную таблицу из нескольких таблиц
Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.
Порядок создания сводной таблицы из нескольких листов такой же.
Создадим отчет с помощью мастера сводных таблиц:
- Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
- Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
- Следующий этап – «создать поля». «Далее».
- Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
- Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
- Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:
Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.
Как работать со сводными таблицами в Excel
Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).
Справа от сводной таблицы у нас была область задач, где мы выбирали столбцы в списке полей. Если она исчезла, просто щелкаем мышью по табличке.
Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.
Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:
Чтобы изменить параметры в сводной таблице , достаточно снять галочки напротив имеющихся полей строк и установить у других полей. Сделаем отчет по наименованиям товаров, а не по отделам.
А вот что получится, если мы уберем «дату» и добавим «отдел»:
А вот такой отчет можно сделать, если перетащить поля между разными областями :
Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.
Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».
Покажем детали по конкретному продукту . На примере второй сводной таблицы, где отображены остатки на складах. Выделяем ячейку. Щелкаем правой кнопкой мыши – «развернуть».
В открывшемся меню выбираем поле с данными, которые необходимо показать.
Когда нажимаем на сводную таблицу, становится доступной закладка с параметрами отчета. С ее помощью можно менять заголовки, источники данных, группировать информацию.
Проверка правильности выставленных коммунальных счетов
С помощью сводных таблиц Excel легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.
Для начала предлагаем составить сводную таблицу тарифов по всем коммунальным платежам. Для разных городов данные будут свои.
Для примера мы сделали сводную табличку тарифов для Москвы:
Для учебных целей возьмем семью из 4 человек, которые проживают в квартире 60 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.
Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:
= тариф * количество человек / показания счетчика / площадь
Для удобства рекомендуем сделать промежуточный столбец, в который будут заноситься показания по счетчикам (переменная составляющая).
Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.
Если при расчете коммунальных платежей применяются льготы, их тоже можно внести в формулы. Информацию по начислениям требуйте в бухгалтерии своей обслуживающей организации. Когда меняются тарифы – просто измените данные в ячейках.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Использование нескольких таблиц для создания сводной таблицы
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете создать сводную таблицу.
 |
 |
Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
Создание сводной таблицы с использованием нескольких таблиц
Ниже приведены три основных шага для добавления нескольких таблиц в список полей сводной таблицы.
Шаг 1. Импорт связанных таблиц из базы данных
Импортируйте их из реляционной базы данных, например Microsoft SQL Server, Oracle или Access. Вы можете импортировать несколько таблиц одновременно:
Может потребоваться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
Шаг 2. Добавление полей в сводную таблицу
Обратите внимание: список полей содержит несколько таблиц.
Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ. Вы можете:
- Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».
- Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
Шаг 3. Создание связей при необходимости
Иногда нужно создать связь между двумя таблицами, прежде чем использовать их в сводной таблице. Если появится сообщение о необходимости такой связи между таблицами, щелкните Создать, чтобы начать работу с ними.
Использование модели данных для создания новой сводной таблицы
Примечание Модели данных не поддерживаются в Excel для Mac.
Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Дополнительные сведения см. в статье Импорт нескольких таблиц из других источников данных.
Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей. Дополнительные сведения см. в статьях Добавление данных с листа в модель данных с помощью связанной таблицы,Создание связи между двумя таблицами иСоздание связей в представлении схемы.
После создания модели данных эти данные можно использовать в анализе. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
- Щелкните любую ячейку на листе.
- Выберите Вставка и щелкните стрелку вниз элемента Сводная таблица.
- Выберите Из внешнего источника данных.
- Нажмите Выбрать подключение.
- На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.
См. также
- Создание модели данных в Excel
- Получение данных с помощью надстройки Power Pivot
- Упорядочение полей сводной таблицы с помощью списка полей
- Создание сводной таблицы для анализа данных на листе
- Создание сводной таблицы для анализа внешних данных
- Создание сводной таблицы, подключенной к наборам данных Power BI
- Изменение диапазона исходных данных для сводной таблицы
- Обновление данных в сводной таблице
- Удаление сводной таблицы