Как объединить сводные таблицы в одну excel

Как объединить сводные таблицы в одну excel

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

Примечание: Другой способ консолидации данных — использование Power Query. Дополнительные сведения см. в справке по Power Query для Excel.

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

Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».

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

При консолидации данных можно использовать поля страницы, содержащие элементы, которые представляют один или несколько исходных диапазонов. Например, при консолидации данных бюджета отдела маркетинга, отдела продаж и производственного отдела поле страницы может содержать отдельный элемент с данными по каждому из этих отделов, а также элемент, содержащий сводные данные. В примере ниже показана сводная таблица, в которой выбраны одно поле страницы и несколько элементов.

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

В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).

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

Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:

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

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

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

Щелкните значок мастера на панели быстрого доступа.

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

В разделе Во-первых, укажите количество полей страницы сводной таблицы введите 0, а затем нажмите кнопку Далее.

На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

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

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

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

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

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать одно поле страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

Нажмите кнопку Далее.

На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле — данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:

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

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

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

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

В разделе Во-первых, укажите количество полей страницы сводной таблицы щелкните число полей, которые вы хотите использовать.

В группе Затем выберите диапазон в списке и укажите метку элемента в каждом из доступных окон полей. Повторите операцию для каждого диапазона для каждого из полей страницы выберите диапазон ячеек, а затем укажите его имя.

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».

Нажмите кнопку Далее.

На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

Как объединить сводные таблицы в одну excel

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Проблема

Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.

Пример

В нашем примере мы располагаем 4-мя таблицами: Заказы , Строки заказов , Товары , Клиенты .

Таблица Строк заказов:

Исходные таблицы оформлены в виде умных таблиц: Orders , OrderLines , Goods и Clients .

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа , таблицы Orders и Clients — по полю ID_клиента , таблицы OrderLines и Goods — по полю ID_товара .

Скачать пример

Создание модели данных

Создадим сводную таблицу на основе любой из имеющихся таблиц.

Выбираем в меню Вставка пункт Сводная таблица . В указанном диалоговом окне мы видим опцию Добавить эти данные в модель данных . Мы могли бы её выбрать, но я рекомендую другой, более удобный способ. Просто нажмите OK .

В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ.

Нажмём её. Появится такой вопрос:

Отвечаем Да и видим, что в список полей добавились все наши таблицы:

Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ.

Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines . Обратите внимание, что Excel умеет создавать связь типа » один к одному » или » один ко многим «. Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.

Аналогично создаём другие связи.

В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения

Чтобы видеть больше полей на панеле Поля сводной таблицы , можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:

Результат будет таким:

В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:

Просто и удобно!

Читайте также:

Введение в сводные таблицы

Автоматизация форматирования сводных таблиц

0

Проблема, поднятая Николаем очень правильная. Тут действительно не всё так просто. Поэтому подумал, что мой ответ будет интересен и другим читателям этой статьи:
————————————-
Николай,здравствуйте.

Я понимаю ваши затруднения. Например, чтобы посчитать стоимость какого-либо
товара в заказе, надо [OrderLines].[количество]
умножить на [Goods].[Цена]. Это делается при помощи
вычисляемого поля, которое вы создать в меню Анализ сводной таблицы не можете,
так как эта таблица построена на основе Модели данных, а это уже часть PowerPivot функционала. Добавлять
вычисляемый столбец надо через модуль PowerPivot,
который у вас в Excel будет
только в версии Prof Plus. Речь идёт про MS Office 2013.

0

Получил такое письмо:
——————————-
Денис, здравствуйте,
спасибо за вашу статью про сводные таблицы по нескольким диапазонам.
http://perfect-excel.ru/publ. -1-0-67

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

например, построить такие отчеты.

— вид продукта — общая стоимость согласно заказам
— клиент — общая сумма заказов
— заказ № — стоимость заказа
и т.п.

именно через сводные таблицы, а не модификацией текущих четырех таблиц.
Заранее большое спасибо.

Как объединить две или несколько таблиц в Excel

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

Объединение нескольких таблиц — одна из самых сложных задач в Excel. Если вы решите сделать это вручную, вы можете потратить часы только на то, чтобы обнаружить, что испортили важную информацию. Если вы опытный специалист в области Excel, то можете положиться на формулы ВПР и ИНДЕКС ПОИСКПОЗ. Вы также можете использовать Power Query или дополнительные надстройки к Excel. Выбор остается за вами.

Как объединить две таблицы в Excel с помощью формул.

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

Суммирование.

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

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

Поэтому сложение значений соответствующих ячеек на каждом листе здесь подходит отлично.

Более технологичным вариантом является так называемая 3D сумма —

Мы указываем первый и последний лист, а также координаты ячейки. Все промежуточные координаты Эксель находит сам. Если между первым и последним листом добавить еще несколько листов с аналогичными показателями, то они также будут просуммированы.

Конечно, это не в полном смысле объединение таблиц, так как набор показателей не меняется, новые строки и колонки не добавляются. Но при этом объединяются имеющиеся данные. Далее рассмотрим более сложные случаи — когда нужно объединить неодинаковые таблицы.

Используем ВПР.

Если вы хотите объединить две таблицы на основе одного столбца , то функция ВПР – то, что нужно.

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

То есть, в случае совпадения заказов информация о товаре будет добавлена в первую таблицу в соответствующую строку.

Как видите, порядок следования заказов в основной таблице не соответствует второй. Поэтому простой метод копирования / вставки не сработает.

Чтобы нам было проще ориентироваться, используем именованные диапазоны. Первый назовём «Заказ», второй – «Товар».

или с обычными ссылками —

Далее в G2 запишем —

В H2 аналогично —

Копируем эти формулы вниз и получаем результат объединения:

Объединенная таблица состоит из основной и добавленных данных, извлеченных из массива поиска.

Имейте в виду, что ВПР в Excel имеет несколько ограничений, наиболее важными из которых являются:

1) невозможность извлечения данных, находящихся слева от столбца поиска,

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

Рассмотрим более сложный случай, когда сведения были получены нами из разных источников, и ключевой показатель (код заказа) у них не полностью совпадает. К примеру, к нему добавлены какие-то символы.

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

Итак, вставим в нашу таблицу заказов вспомогательный столбец B. Для этого кликнем правой кнопкой мыши по заголовку колонки B и в появившемся контекстном меню выберем «Вставить». Получим пустую колонку, а все остальные сдвинутся вправо.

Здесь мы и «очистим» наши номера заказов от лишнего мусора. В нашем случае вполне подойдёт формула:

A2 — это адрес ячейки, из которой мы извлекаем первые 4 символа.

Если, например, мы имеем “Б-1007”, то используйте формулу

Но ведь наши идентификаторы могут выглядеть и по-другому.

Скажем, нужно пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “СББЛ-1007-ШКЛ”. Здесь нам нужно пропустить первые пять символов и извлечь следующие четыре. Формула будет выглядеть так:

Если нужно извлечь все знаки до разделителя, причем количество их может быть разным (“1007-СРР 256” или “10071007-Б111НРР”), то действуем так —

Получаем соответственно 1007 и 10071007.

Если нужно получить последнее слово после разделителя “Б-С-100777”, причем когда количество символов в нем может быть разным —

Словом, текстовые функции помогут нам заполнить вспомогательный столбец.

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

Как видите, при помощи дополнительной колонки мы решили проблему несовпадения ключевых кодов и успешно объединили данные.

Комбинация ИНДЕКС + ПОИСКПОЗ.

Если вы ищете более мощную и универсальную альтернативу функции ВПР, воспользуйтесь комбинацией функций ИНДЕКС и ПОИСКПОЗ.

ИНДЕКС( диапазон_возврата ; ПОИСКПОЗ ( значение_подстановки ; диапазон_просмотра ; 0))

Если вернуться к предыдущему примеру с функцией ВПР, то формулу

ВПР(A2;Товар!$A$2:$D$200;2;0) в ячейке Е2 можно заменить на

Казалось бы, ничего особенного, только сложнее стало. Однако, здесь открывается гораздо больше возможностей. Если будет необходимо, мы сможем провести поиск в колонке B и вернуть соответствующее значение из А. То есть, порядок расположения столбцов в этом случае не важен. ВПР нам не позволить сделать такое.

Вот, например, если во второй таблице показатели будут расположены как на скриншоте внизу, то функция ВПР нам здесь не поможет.

Выражение в ячейке C2 выглядит так:

Это так называемый «левый ВПР», который не зависит от взаимного расположения столбцов.

Обратите внимание на знак $, который фиксирует диапазоны, чтобы предотвратить их изменение при копировании формулы.

В случае нечёткого совпадения кодов заказов, который мы рассматривали выше, комбинация ИНДЕКС + ПОИСКПОЗ позволяет обойтись без вспомогательной колонки.

Нам поможет формула массива

Как видите, мы берём первые 4 символа из номера заказа и ищем совпадения в товарах.

Естественно, вместо ЛЕВСИМВ мы можем использовать другие варианты текстовых функций. Мы о них подробно говорили выше, когда рассматривали возможное содержание вспомогательного столбца. При помощи формулы массива мы как бы вставляем его целиком прямо в формулу поиска.

Как объединить на основе нескольких столбцов.

Если две таблицы, которые вы хотите объединить, не имеют уникального идентификатора, такого как номер заказа, вы можете сопоставить значения в двух или более колонках как в ключевых, используя эту формулу:

ИНДЕКС( таблица_поиска ; ПОИСКПОЗ(1; ( искомое_значение1 = диапазон_поиска1 ) * ( искомое_значение2 = диапазон_поиска2 ); 0); номер_возвращаемого_столбца )

Это формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter для правильного её ввода.

Предположим, у вас есть две таблицы, которые нужно объединить в одну. Поскольку показатель «Заказ» в массиве поиска отсутствует, единственный способ сопоставить их — по Покупателю и Товару:

Основываясь на приведенном выше скриншоте, давайте определим аргументы для нашей формулы:

• диапазон поиска — $F$3:$H$10
• искомое_значение1 — $B3
• диапазон_поиска1 — $F$3:$F$10
• искомое_значение2 — $C3
• диапазон_поиска2 — $G$3:$G$10
• номер_возвращаемого_столбца — 3

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

Введите формулу в D3, нажмите Ctrl + Shift + Enter . Cкопируйте её вниз и проверьте результат:

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

Объединяем с помощью Excel Power Query

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

• Power Query может объединить две таблицы, сопоставив один или несколько столбцов.
• Исходные сведения могут находиться на одном листе или на разных листах.
• Исходные данные не будут изменены. Они копируются в новую таблицу, которую можно импортировать в существующий или новый рабочий лист.
• В Excel 2016 и Excel 2019 Power Query — это встроенная функция. В Excel 2010 и Excel 2013 его можно загрузить как надстройку.

Проще говоря, Power Query (также известный как Get & Transform в Excel 2016 и Excel 2019) — это инструмент для объединения, очистки и преобразования данных из нескольких источников в нужный вам формат —обычную таблицу, сводную таблицу или сводную диаграмму.

Чтобы результаты соответствовали вашим ожиданиям, имейте в виду следующее:

• Объединяемые таблицы должны иметь по крайней мере, один общий столбец (также называемый как общий идентификатор или ключевой). Кроме того, эти общие столбцы должны содержать только уникальные значения без повторов.
• В отличие от формул, Power Query не переносит данные из одной таблицы в другую. Он создает совершенно новую таблицу, которая объединяет информацию из исходных таблиц.
• Результирующая таблица не обновляется автоматически. Вы должны явно указать Excel сделать это.

Источник данных

В качестве примера давайте объединим 3 таблицы на основе общих показателей «Код заказа» и «Код покупателя». Обратите внимание, что в каждой из них разное количество строк, первая и вторая имеют дубликаты покупателей и товаров, третья содержит только уникальные записи.

Наша задача — сопоставить данные в таблице 1 с соответствующими записями из двух других и объединить все это примерно следующим образом:

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

• первый называется Заказ,
• второй — Товар,
• третий — Скидка.

Создание подключений Power Query

Чтобы не загромождать вашу книгу копиями исходных данных, мы собираемся преобразовать их в соединения, выполнить слияние в редакторе Power Query, а затем загрузить только итоговый результат.

Чтобы создать соединение в Power Query, выполните следующие действия:

1. Выберите свою первый диапазон (Заказ) или любую ячейку в нем.
2. Перейдите на вкладку «Данные» и нажмите «Из таблицы / диапазона».

1. В открывшемся редакторе Power Query щелкните стрелку раскрывающегося списка «Закрыть и загрузить» (а не саму кнопку!) и выберите параметр «Закрыть и загрузить в…» .

1. В диалоговом окне «Импорт данных» выберите параметр «Только создать подключение» и нажмите « ОК» .

Это создаст соединение с именем вашего диапазона и отобразит его на панели запросов и подключений, которая появится в правой части вашей книги.

1. Повторите вышеуказанные шаги для всех остальных таблиц, которые вы хотите объединить.

Когда закончите, вы увидите все подключения на правой панели:

Как объединить два соединения в одну таблицу.

Теперь давайте посмотрим, как вы можете объединить две таблицы в одну при помощи созданных подключений:

1. На вкладке «Данные» нажмите кнопку «Получить данные», выберите «Объединить запросы» в раскрывающемся списке и нажмите «Объединить»:

1. В диалоговом окне «Слияние» сделайте следующее:
   • Выберите свой первый диапазон (Заказ) из первого раскрывающегося списка.
   • Выберите второй (Товар) из второго раскрывающегося списка.
   • В обоих предварительных просмотрах щелкните соответствующий столбец (код заказа), чтобы выбрать его. Он будет выделен зеленым.
   • В раскрывающемся списке «Тип присоединения» оставьте значение по умолчанию: «Левый внешний» (все с первого, соответствие со второго).
   • Щелкните ОК.

После выполнения вышеуказанных шагов редактор Power Query покажет вашу первую таблицу (Заказ) с одним дополнительным столбцом. Он будет назван как ваша вторая таблица (Товар) и добавлен в конец. В нём пока нет значений, только слово «Таблица» во всех ячейках. Но не расстраивайтесь, вы все сделали правильно, и мы скоро это исправим!

Выберите столбцы для добавления из второй таблицы

На текущий момент у вас есть результат, изображенный на скриншоте ниже. Чтобы завершить процесс слияния, выполните следующие действия в редакторе Power Query Editor:

1. В последнем столбце (Товар) нажмите на двустороннюю стрелку в заголовке.
2. В открывшемся окне сделайте следующее:
   • Не снимайте флажок «Развернуть».
   • Отмените весь выбор, а затем укажите только те, которые вы хотите скопировать. В этом примере мы выбираем только колонки Товар и Количество, потому что в исходной информации уже есть код заказа.
   • Снимите флажок Использовать исходное имя столбца как префикс (если вы не хотите, чтобы имя столбца было с префиксом с именем таблицы, из которой он был взят).
   • Щелкните ОК.

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

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

Как объединить больше таблиц.

Если вам нужно присоединиться к трем или более столам, вам предстоит еще кое-что подработать. Я кратко опишу шаги здесь, потому что вы уже выполняли все это при объединении первых двух диапазонов:

1. Сохраните результат, полученный на предыдущем шаге (показанную на скриншоте выше), как соединение:
   • В редакторе запросов питания, нажмите кнопку Закрыть и загрузить на стрелку раскрывающегося списка и выберите Закрыть и загрузить . .
   • В диалоговом окне «Импорт данных» выберите «Только создать подключение» и нажмите « ОК» .

Это добавит еще одно соединение с именем Слияние1 на панель запросов и подключений. Вы можете переименовать это соединение, если хотите. Щелкните по нему правой кнопкой мыши и выберите «Переименовать» во всплывающем меню.

1. Объедините Слияние1 с вашей третьей таблицей (Скидка), выполнив уже знакомые нам действия (вкладка Данные —Получить данные — Объединить запросы).

На скриншоте ниже показаны мои настройки:

1. При нажатии кнопки ОК в диалоговом окне «Слияние» открывается редактор Power Query. В нем вы выбираете все нужное для добавления из третьей таблицы.

В этом примере нам нужен только показатель Скидка:

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

Как импортировать объединенную таблицу в Excel.

С полученными результатами в редакторе Power Query вам остается сделать только одно — загрузить их в свою книгу Excel. И это самая легкая часть!

1. В редакторе запросов нажмите Закрыть и загрузить.
2. В диалоговом окне «Импорт данных» выберите параметры «Таблица» и «Новый лист».
3. Щелкните ОК.

Новая таблица, объединяющая информацию из двух или более источников, появится на новом листе. Поздравляю, у вас получилось!

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

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

Как сделать объединение на основе нескольких столбцов.

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

В диалоговом окне » Слияние» удерживайте клавишу Ctrl и щелкайте по ключевым столбцам один за другим, чтобы выбрать их. Важно, чтобы вы нажимали их в одном и том же порядке в обоих предварительных просмотрах, чтобы аналогичные столбцы имели одинаковые номера. Например, менеджер — это ключевой столбец №1, а товар — ключевой №2. Пустые ячейки или строки, которые Power Query не может сопоставить, показывают null :

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

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

Лучшее в Power Query — это то, что настраивать объединенную таблицу нужно только один раз. Когда вы вносите некоторые изменения в исходные файлы, вам не нужно снова повторять весь процесс. Просто нажмите кнопку «Обновить» на панели «Запросы и подключения», и объединенная таблица сразу же обновится:

Если панель исчезла из Excel, нажмите кнопку «Запросы и подключения», чтобы вернуть ее.

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

А теперь рассмотрим применение специальной программы.

Быстрый способ объединить таблицы путем сопоставления ключевых столбцов.

Если вы еще не очень хорошо знакомы с формулами Excel и у вас нет времени разбираться в загадочных особенностях Power Query, мастер объединения таблиц Merge Two Tables может существенно сэкономить вам время и нервы. Ниже я покажу три наиболее популярных варианта использования его для объединения таблиц.

Пример 1. Объедините две таблицы по нескольким столбцам.

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

В этом примере мы будем использовать уже знакомые по предыдущим разделам этой статьи таблицы и объединять их на основе 2 столбцов: «Код покупателя» и « Товар».

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

С помощью мастера объединения таблиц, вам нужно сделать следующее:

1. Выберите любую ячейку в основной таблице и нажмите кнопку «Объединить две таблицы (Merge Two Tables)» на вкладке «Ablebits Data»:

1. Убедитесь, что программа правильно выбрала диапазон ячеек, и нажмите Далее (Next).
2. Выберите таблицу поиска и нажмите Далее:

1. Укажите пары столбцов, которые нужно сопоставить в обеих таблицах, в нашем случае Продавец и Товар, и нажмите Далее:

Примечание. Если регистр текста в ключевых столбцах имеет значение, установите флажок Учитывать регистр, чтобы прописные и строчные буквы обрабатывались как разные символы.

1. При желании выберите столбцы, которые нужно обновить значениями из таблицы поиска. Поскольку у нас нечего обновлять, мы оставляем все их как есть и просто нажимаем «Далее».

1. Выберите столбцы, которые нужно добавить в основную таблицу, и нажмите « Далее». Добавляем скидку, как и планировали.

1. На этом этапе вы указываете мастеру, как именно вы хотите объединить таблицы. Все опции имеют описательные надписи, поэтому я не буду вдаваться в подробные объяснения. Если вы не уверены в каком-либо варианте, щелкните знак вопроса рядом с ним, и небольшая диаграмма покажет вам, как таблицы будут объединены.

В нашем случае параметры по умолчанию работают нормально, поэтому мы нажимаем Finish, ничего не меняя:

Дайте мастеру несколько секунд на обработку и просмотрите результат:

Как видно на скриншоте выше, мастер сделал следующее:

1. Добавлен столбец «Скидка» путем сопоставления имени продавца и наименования товара в обеих таблицах.
2. В столбце имеются пустые ячейки, поскольку не для всех товаров этим продавцам были предоставлены скидки.

Пример 2. Объединение таблиц и обновление выбранных столбцов.

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

Продолжим рассматривать данные предыдущего примера. Предположим, некоторым из покупателей был изменен размер скидки, а также были добавлены дополнительные позиции в таблицу скидок. В связи с этим наша основная таблица устарела и нуждается в обновлении. Давайте снова объединим 2 таблицы по покупателю и наименованию товара и обновим значения в столбце Скидка.

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

Шаг 1. Выберите основную таблицу.

Шаг 2. Выберите таблицу поиска.

Шаг 3. Выберите «Код покупателя» и «Товар» как ключевые столбцы.

Шаг 4. Выберите столбец «Скидка» для обновления. То есть, в этот столбец основной таблицы будут перенесены обновленные значения из соответствующего столбца из таблицы поиска. Параметры этого добавления вы укажете чуть позже.

Шаг 5. Пропустите, потому что у нас нет столбцов для добавления. Нам нужно просто обновить информацию.

Шаг 6. Поскольку в столбце «Скидка» есть несколько пустот, мы выбираем обновление только в том случае, если ячейки в таблице поиска содержат данные. Таким образом мы обновим устаревшие значения, но не потеряем никакие данные, которые по какой-то причине исчезли из таблицы поиска. При желании вы можете выделить обновленные ячейки любым цветом по вашему выбору. На скриншоте ниже показаны настройки:

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

И вот результат:

Как видите, две скидки были добавлены и две — обновлены, так как изменились их величины.

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

Пример 3. Объединение по нескольким совпадающим значениям из двух таблиц.

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

Предположим, ваша основная таблица содержит только по одному заказу от каждого покупателя, а таблица поиска содержит дополнительные заказы по множеству покупателей. Теперь вы хотите добавить в основную таблицу все заказы по уже имеющимся в ней покупателям и сгруппировать их по коду продавца. К примеру, найти во второй таблице все заказы покупателя с кодом «Красный» и поместить их в первую сразу после уже имеющейся записи по этому покупателю (то есть, начиная с 3-й строки).

Похоже, предстоит много работы? Нет, если в вашем распоряжении есть Мастер слияния таблиц Merge Two Tables 🙂

А теперь рассмотрим все действия по объединению таблиц пошагово.

Шаг 1. Выберите основную таблицу.

Шаг 2. Выберите таблицу поиска.

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

Шаг 3. Выберите Код покупателя в соответствующем столбце.

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

Шаг 4. Выбираем столбцы, информацию в которых нужно обновить.

Нам нужны все столбцы, поэтому можно просто нажать на чекбокс в шапке таблицы, чтобы выбрать все сразу.

Шаг 5. Нет столбцов для добавления. Пропускаем.

Шаг 6. Вставляем дополнительные совпадающие строки после строки с тем же значением ключа. При желании установите цвет фона для добавленных строк, чтобы сразу просмотреть изменения:

Чтобы не потерять уже имеющиеся в основной таблице значения, активируем опцию «Обновлять в основной таблице только пустые ячейки».

И вот результат:

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

В приведенных выше примерах показаны только 3 из множества возможных способов объединения таблиц в Excel. Вы можете загрузить 14-дневную пробную версию и попробовать объединение таблиц на своих данных.

Если будут вопросы или замечания, не стесняйтесь использовать комментарии.

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

Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft…
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального…
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать…
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы…
Проверка данных в Excel: как сделать, использовать и убрать — Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи,…
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Как быстро объединить несколько файлов Excel — Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…
6 примеров — как консолидировать данные и объединить листы Excel в один — В статье рассматриваются различные способы объединения листов в Excel в зависимости от того, какой результат вы хотите получить: объединить все данные с выбранных листов,объединить несколько листов с различным порядком столбцов,объединить…
Как полностью или частично зафиксировать ячейку в формуле — При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только…
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они…

Создание сводной таблицы Excel из нескольких листов

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

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

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

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

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

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

1. Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
2. В меню «Вставка» выбираем «Сводная таблица».
3. Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
4. Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

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

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

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

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

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

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

1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
3. Следующий этап – «создать поля». «Далее».
4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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