Excel. Сводная таблица на основе нескольких листов

Если вы столкнулись с необходимостью создать сводную таблицу на основе данных, размещенных на нескольких листах одной книги (или разных книг), вас ждет разочарование. Стандартным образом Excel делает это «через одно место»… L

Да в Excel есть такая опция в мастере сводных таблиц, но привычно (как и для обычных сводных) она работает только для одного набора данных, например (см. файл Сводная_листы_один набор.xlsx ):

У вас есть список клиентов и объем продаж по ним по кварталам; данные за один квартал расположены на отдельном листе. Создадим сводную таблицу, консолидирующую данные за 4 квартала.

1. Поименуйте ваши исходные массивы данных; в принципе, это не обязательно, но, с другой стороны, это поднимает вашу работу на новый уровень J , облегчает восприятие информации другими пользователями, а также упрощает внесение любых изменений / дополнений в исходные данные в будущем (как создать динамически изменяемые именованные массивы см. здесь):

2. Запустите мастер сводных таблиц (как вывести мастер на панель быстрого доступа см. здесь); выберите опцию «в нескольких диапазонах консолидации », нажмите «далее»:

3. Оставьте предлагаемую по умолчанию опцию «Создать одно поле страницы»

4. Введите имя первого диапазона, нажмите «Добавить»:

5. Добавьте все четыре диапазона, нажмите «Далее»:

6. Оставьте предлагаемую по умолчанию опцию «новый лист», нажмите «Готово»:

Сводная таблица на основе данных, размещенных на четырех листах, создана. Сводная таблица (как и обычно) содержит четыре области. Вот только названия полей не соответствуют тому, что было в исходных данных:

https://pandia.ru/text/79/437/images/image010_66.jpg" width="348" height="233 id=">

Названия строк вместо «Клиент» – «Строка»; название столбца «Продажи» упрятано внутрь названия «Столбец»:

https://pandia.ru/text/79/437/images/image012_56.jpg" width="356 height=191" height="191">

В остальном с полученной сводной можно работать, как обычно. Например:

https://pandia.ru/text/79/437/images/image014_37.jpg" width="273" height="82 id=">

1. Поименуйте ваши исходные массивы данных.

2. Создайте сводную таблицу, как описано выше

Вот, что получилось:

EN-US">zip

Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.

Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345 ” и “12345-новый_суффикс “. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания ” в одной таблице может превратиться в “ЗАО «Некоторая Компания» ” в другой таблице, а “Новая Компания (бывшая Некоторая Компания) ” и “Старая Компания ” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP) , ПОИСКПОЗ (MATCH) , ГПР (HLOOKUP) и так далее.

Выберите подходящий пример, чтобы сразу перейти к нужному решению:

Ключевой столбец в одной из таблиц содержит дополнительные символы

Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.

В таблице с дополнительными символами создаём вспомогательный столбец. Можно добавить его в конец таблицы, но лучше всего вставить его следующим справа после ключевого столбца, чтобы он был на виду.

Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper :

Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

Другие формулы

• Извлечь первые Х символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:

  ПРАВСИМВ(A2;6)
  =RIGHT(A2,6)

• Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:

  ПСТР(A2;8;4)
  =MID(A2,8,4)

• Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:

  ЛЕВСИМВ(A2;НАЙТИ("-";A2)-1)
  =LEFT(A2,FIND("-",A2)-1)

Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – , мы сделаем всё возможное, чтобы помочь Вам.

Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY , где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.

В ячейке C2 запишем такую формулу:

СЦЕПИТЬ(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Здесь A2 – это адрес ячейки, содержащей код группы; символ “– ” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

Плохая новость: Данные, содержащиеся в этих двух таблицах Excel, придётся обрабатывать вручную, чтобы в дальнейшем было возможно объединить их.

Хорошая новость: Это придётся сделать только один раз, и получившуюся вспомогательную таблицу можно будет сохранить для дальнейшего использования. Далее Вы сможете объединять эти таблицы автоматически и сэкономить таким образом массу времени

1. Создаём вспомогательную таблицу для поиска.

Создаём новый лист Excel и называем его SKU converter . Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description ). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз:-).

В результате мы имеем вот такую таблицу:

2. Обновляем главную таблицу при помощи данных из таблицы для поиска.

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU .

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter Our.SKU , а для обновлённых данных – столбец Supp.SKU .

Столбец Supp.SKU заполняется оригинальными кодами производителя.

Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU , соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика. После этого повторяем шаг 2.

3. Переносим данные из таблицы поиска в главную таблицу

В нашей главной таблице есть ключевой столбец с точным совпадением с элементами таблицы поиска, так что теперь эта задача не вызовет сложностей

При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1 , используя для поиска соответствий столбец Supp.SKU .

Вот пример обновлённых данных в столбце Wholesale Price :

Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

Чтобы объединить таблицы в Excel , расположенные на разных листах или в других книгах Excel , составить общую таблицу, нужно сделать сводные таблицы Excel . Делается это с помощью специальной функции.
Сначала нужно поместить на панель быстрого доступа кнопку функции «Мастер сводных таблиц и диаграмм».
Внимание!
Это не та кнопка, которая имеется на закладке «Вставка».
Итак, нажимаем на панели быстрого доступа на функцию «Другие команды», выбираем команду «Мастер сводных таблиц и диаграмм».
Появился значок мастера сводных таблиц. На рисунке ниже, обведен красным цветом.
Теперь делаем сводную таблицу из нескольких отдельных таблиц.
Как создать таблицу в Excel , смотрите в статье "Как сделать таблицу в Excel ".
Нам нужно объединить данные двух таблиц, отчетов по магазинам, в одну общую таблицу. Для примера возьмем две такие таблицы Excel с отчетами по наличию продуктов в магазинах на разных листах.
Первый шаг. Встаем на лист с первой таблицей. Нажимаем на кнопку «Мастер сводных таблиц и диаграмм». В появившемся диалоговом окне указываем «в нескольких диапазонах консолидации». Указываем – «сводная таблица».

Нажимаем «Далее».
На втором шаге указываем «Создать поля страницы» (это поля фильтров, которые будут расположены над таблицей). Нажимаем кнопку «Далее».
Последний, третий шаг. Указываем диапазоны всех таблиц в строке «Диапазон…», из которых будем делать одну сводную таблицу.
Выделяем первую таблицу вместе с шапкой . Затем нажимаем кнопку «Добавить», переходим на следующий лист и выделяем вторую таблицу с шапкой. Нажимаем кнопку «Добавить».
Так указываем диапазоны всех таблиц, из которых будем делать сводную. Чтобы все диапазоны попали в список диапазонов, после ввода последнего диапазона, нажимаем кнопку «Добавить».
Теперь выделяем из списка диапазонов первый диапазон. Ставим галочку у цифры «1» - первое поле страницы сводной таблицы станет активным. Здесь пишем название параметра выбранного диапазона. В нашем примере, поставим название таблицы «Магазин 1».
Затем выделяем из списка диапазонов второй диапазон, и в этом же первом окне поля пишем название диапазона. Мы напишем – «Магазин 2». Так подписываем все диапазоны.
Здесь видно, что в первом поле у нас занесены названия обоих диапазонов. При анализе данные будут браться из той таблицы, которую мы выберем в фильтре сводной таблицы. А если в фильтре укажем – «Все», то информация соберется из всех таблиц. Нажимаем «Далее».
Устанавливаем галочку в строке «Поместить таблицу в:», указываем - «новый лист». Лучше поместить сводную таблицу на новом листе, чтобы не было случайных накладок, перекрестных ссылок, т.д. Нажимаем «Готово». Получилась такая таблица.

Если нужно сделать выборку по наименованию товара, выбираем товар в фильтре «Название строк».
Можно выбрать по складу – фильтр «Название столбца», выбрать по отдельному магазину или по всем сразу – это фильтр «Страница 1».
Когда нажимаем на ячейку сводной таблицы, появляется дополнительная закладка «Работа со сводными таблицами». В ней два раздела. С их помощью можно изменять все подписи фильтров, параметры таблицы.
Например, нажав на кнопку «Заголовки полей», можно написать свое название (например – «Товар»).
Если нажимаем на таблицу, справа появляется окно «Список полей сводной таблицы».Здесь тоже можно настроить много разных параметров.
Эта сводная таблица связана с исходными таблицами. Если изменились данные в таблицах исходных, то, чтобы обновить сводную таблицу, нужно из контекстного меню выбрать функцию «Обновить».
Нажав правой мышкой, и, выбрав функцию «Детали», можно увидеть всю информацию по конкретному продукту. Она появится на новом листе.
В Excel есть способ быстро и просто посчитать (сложить, вычесть, т.д.) данные из нескольких таблиц в одну. Подробнее, смотрите в статье "Суммирование в Excel"

Что вы будете делать, если нужно создать сводную таблицу, а ваши данные на разных листах? С установленным Excel 2013 у вас есть для этого простой способ. Существует технология, называемая Data Model и она использует отношения данных так, как это делает база данных.

В этом уроке я покажу вам всё для создания сводной таблицы в Excel 2013 из данных на нескольких листах, используя Data Model.

Видеоролик

Если вы хотите следовать уроку, используя собственный файл Excel, можете так и сделать. Или загрузите zip-файл к этому уроку, в котором содержится образец книги Pivot Consolidate.xlsx.

Изучение данных

В этой книге есть три рабочих листа: информация о клиенте, информация о заказе и информация об оплате.

Нажмите на лист Customer Info и убедитесь, что в нём содержатся номера заказов, а также название и состояние клиентов.

Customer Info лист

Нажмите на лист Order Info и посмотрите, что в нём содержатся номера заказов, а также поля за месяц, заказанные товары и то, являются ли эти продукты органическими.

Перейдите на вкладку Payment Info и убедитесь, что в ней содержатся номера заказов, сумма в долларах каждой продажи, способ оплаты и порядок размещения заказа новым или существующим клиентом.

Соединив все эти листы в области задач сводной таблицы, мы можем выбрать данные из каждого листа. Поскольку номера заказов существуют на всех трёх листах, они станут точками подключения. Это то, что база данных вызывает primary key. Обратите внимание: не обязательно иметь primary key, но он уменьшает вероятность ошибки.

Создание именованных таблиц

Перед созданием сводной таблицы давайте создадим таблицу из каждого листа.

Нажмите обратно в Customer Table , затем щёлкните в любом месте внутри области данных. Перейдите на вкладку Insert на панели ленты и щёлкните значок Table .

Диалоговое окно Create Table правильно определяет область таблицы. Флажок внизу должен также идентифицировать, что первая строка таблицы предназначена для заголовков. (Если нет, выберите этот вариант.)

Нажмите OK , и теперь у вас есть таблица с чередующимися штрихами и кнопками фильтра. Вы можете щёлкнуть внутри, чтобы снять выделение, если хотите лучше рассмотреть её (просто не нажимайте вне таблицы). На панели ленты также отображается вкладка Design для таблицы. На левой стороне ленты в поле Table Name отображается временное имя Table1. Удалите это и назовите его Customer_Info (используйте символ подчёркивания вместо пробела). Нажмите Enter .

Повторите эти действия с листами Order Info и Payment Info. Назовите таблицы Order_Info и Payment_Info .

Теперь мы готовы вставить PivotTable.

Вставка PivotTable

Убедитесь, что на листе Payment Info курсор находится где-то в таблице. Вернитесь на вкладку Insert ленты и щёлкните значок PivotTable (это самый первый значок).

В появившемся диалоговом окне необходимо правильно определить таблицу и выбрать, чтобы PivotTable перешла на новый рабочий лист. Внизу установите флажок Add this data to the Data Model . Нажмите OK .

Теперь у вас будет PivotTable на новом листе, в правой части экрана будет панель задач, а на ленте появится вкладка Analyze.

На панели задач отображается таблица и поля только активного листа, поэтому нажмите ALL , чтобы увидеть все созданные вами таблицы. Но прежде чем мы сможем их использовать, мы должны связать их друг с другом, а это означает создание отношений. Нажмите кнопку Relationships на панели ленты.

Настройка отношений таблиц

Нажатие этой кнопки отображает диалоговое окно Manage Relationships. Нажмите кнопку New и появится диалоговое окно Create Relationship . Мы создадим два отношения, используя поле Order # в качестве соединителя.

В раскрывающихся списках выберите Payment_Info для таблицы, а рядом с ним выберите Order # в раскрывающемся списке Column. Во второй строке выберите Customer_Info из раскрывающегося списка Related Table, а рядом с ней выберите Order # из раскрывающегося списка Related Column.

Это означает, что таблицы Payment_Info и Customer_Info связаны между собой по совпадению номера заказов.

Нажмите кнопку OK и мы увидим эти отношения, перечисленные в окне Manage Relationships.

Повторите этот процесс, чтобы создать связь между Payment_Info и Order_Info, также используя поле Order #. Теперь Manage Relationships окно выглядит так:

Обратите внимание, что нет необходимости создавать отношения между таблицами Order_Info и Customer_Info, так как они автоматически соединяются через таблицу Payment_Info.

Нажмите кнопку Close в нижней части окна. Теперь мы можем, наконец, перетащить поля в PivotTable.

Вставка полей в PivotTable

В разделе ALL на панели задач щёлкните маленькие стрелки, чтобы, развернув три таблицы, увидеть их поля. Перетащите поля в области PivotTable следующим образом:

• State и Month в строки
• Product в колонки
• $ Sale в значения
• Status в фильтры

Теперь вы можете использовать и изменять её, как и любую другую PivotTable.

Заключение

Используя новую функцию Object Data Model в Excel 2013, вы можете выбрать розовые поля из нескольких листов для создания единой PivotTable. Имейте в виду, что строки каждой таблицы должны быть каким-то образом связаны друг с другом. У вас больше шансов на успех, когда таблицы имеют общее поле с уникальными значениями.

Если вы ищете хорошие способы представления своих данных, Envato Market имеет хороший выбор , а также для преобразования данных Excel в веб-форматы и наоборот.

Сводная таблица применяется для быстрого анализа большого объема данных. Она позволяет объединять информацию из разных таблиц и листов, подсчитать общий результат. Этот универсальный аналитический инструмент существенно расширяет возможности программы Excel.

Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.

Сводная таблица в Excel

Для примера используем таблицу реализации товара в разных торговых филиалах.

Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтобы найти величину продаж по каждому отделу, придется посчитать вручную на калькуляторе. Либо сделать еще одну таблицу Excel, где посредством формул показать итоги. Такими методами анализировать информацию непродуктивно. Недолго и ошибиться.

Самое рациональное решение – это создание сводной таблицы в Excel:

Просто, быстро и качественно.

Важные нюансы:

• Первая строка заданного для сведения данных диапазона должна быть заполнена.
• В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
• В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

Как сделать сводную таблицу из нескольких таблиц

Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.

Порядок создания сводной таблицы из нескольких листов такой же.

Создадим отчет с помощью мастера сводных таблиц:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).

Справа от сводной таблицы у нас была область задач, где мы выбирали столбцы в списке полей. Если она исчезла, просто щелкаем мышью по табличке.

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

Чтобы изменить параметры в сводной таблице , достаточно снять галочки напротив имеющихся полей строк и установить у других полей. Сделаем отчет по наименованиям товаров, а не по отделам.

А вот что получится, если мы уберем «дату» и добавим «отдел»:

А вот такой отчет можно сделать, если перетащить поля между разными областями :

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

Покажем детали по конкретному продукту . На примере второй сводной таблицы, где отображены остатки на складах. Выделяем ячейку. Щелкаем правой кнопкой мыши – «развернуть».

В открывшемся меню выбираем поле с данными, которые необходимо показать.

Когда нажимаем на сводную таблицу, становится доступной закладка с параметрами отчета. С ее помощью можно менять заголовки, источники данных, группировать информацию.

Проверка правильности выставленных коммунальных счетов

С помощью сводных таблиц Excel легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.

Для начала предлагаем составить сводную таблицу тарифов по всем коммунальным платежам. Для разных городов данные будут свои.

Для примера мы сделали сводную табличку тарифов для Москвы:

Для учебных целей возьмем семью из 4 человек, которые проживают в квартире 60 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

Тариф * количество человек / показания счетчика / площадь

Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

Если при расчете коммунальных платежей применяются льготы, их тоже можно внести в формулы. Информацию по начислениям требуйте в бухгалтерии своей обслуживающей организации. Когда меняются тарифы – просто измените данные в ячейках.