Таблицы разные: Таблицы на листе формата А4 для печати. – Как в excel объединить данные из нескольких таблиц в одну Excelka.ru

Как в excel объединить данные из нескольких таблиц в одну Excelka.ru

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

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

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

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

Объединение двух таблиц с помощью функции ВПР

В приведенном ниже примере вы увидите две таблицы с именами, которые ранее использовались для новых имен: «синий» и «оранжевый». В таблице синей каждая строка является элементом строки для заказа. Таким образом, номер заказа 20050 состоит из двух элементов, код заказа 20051 — один товар, код заказа 20052 состоит из трех элементов и т. д. Мы хотим объединить столбцы «код продажи» и «регион» с таблицей «Синяя», исходя из совпадающих значений в столбцах «код заказа» в таблице «оранжевый».

Значения идентификатора заказа повторяются в таблице Blue, но значения ИДЕНТИФИКАТОРов Order в таблице оранжевый являются уникальными. Если бы пришлось просто копировать и вставлять данные из оранжевой таблицы, то значения ИДЕНТИФИКАТОРов продаж и областей для второй позиции строки 20050 будут отключены на одну строку, что приведет к изменению значений в новых столбцах в таблице Blue.

Ниже приведены данные для синей таблицы, которые можно скопировать в пустой лист. После того как вы вставьте его на лист, нажмите клавиши CTRL + T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу Excel синим цветом.

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

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

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

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

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец 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-суффикс” соответственно. Формула будет выглядеть так:

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

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

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

Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.

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

Здесь 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 из нескольких таблиц как сделать Excelka.ru

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

Типичная задача при обработке информации полученной из разных источников. Типовое решение — взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

Однако решение существует! И оно не очень сложное.

Для решения этой задачи нам понадобиться надстройка ЁXCEL .

Итак, приступим к решению. Мы имеем на входе 3 таблицы, расположенные на разных листах рабочей книги:

Важно:

• Количество столбцов во всех таблицах должно быть одинаково;
• Кроме таблиц на листах не должно быть никакой информации.

Начинаем манипуляции. Переходим в главном меню во вкладку ЁXCEL и нажимаем кнопку «Таблицы», в выпавшем меню выбираем команду «Объединить таблицы»:

В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:

Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:

Осталось совсем немного. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:

В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:

В открывшемся диалоговом окне устанавливаем переключатели в положения «Отчет сводной таблицы» и «Новый лист», нажимаем «ОК»:

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

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

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

Важно:

• Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте — необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).

Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:

Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.

Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.

Возможные ошибки при использовании этого метода:

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

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

Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).

Такую таблицу можно выполнить двумя разными способами.

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

Рассмотрим первый способ.

Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.

Шаг первый.

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

Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»

Настройка ленты

либо войти во вкладку

«Файл» => «Параметры» => «Настройка ленты».

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

Из списка выбираем «Мастер сводных таблиц и диаграмм»

Добавление мастера сводных таблиц

В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».

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

После нажмите «Ок».

Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».

Мастер сводных на панели

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

• Кликнуть по кнопке мастера построения сводных таблиц.
• На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»

Консолидация диапазонов
Во втором окне выбрать «Создать одно поле страницы»

Поле сводной
В третьем окне добавить все диапазоны, которые Вы хотите консолидировать(соединить в сводной таблице).

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

На существующий лист

Второй способ.

Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .

Данный способ заключается в использовании запроса надстройки Power Query.

О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?

Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.

Шаг первый.

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

Шаг два.

Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».

Power Query из таблицы

Шаг три.

Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.

Слияние запросов

Шаг четыре.

Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»

Закрыть и загрузить

Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

Использование нескольких таблиц для создания сводной таблицы

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

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

Чтобы объединить несколько таблиц в списке полей сводной таблицы:

Можно импортировать их из реляционной базы данных, например, Microsoft SQL Server, Oracle или Microsoft Access. Вы можете импортировать несколько таблиц одновременно.

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

Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.

Убедитесь, что вам известны имя сервера, имя базы данных и учетные данные, необходимые для подключения к SQL Server. Все необходимые сведения можно получить у администратора базы данных.

Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.

В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.

В разделе Учетные данные входа в систему выберите команду Использовать проверку подлинности Windows, если вы подключаетесь с помощью своих учетных данных. В противном случае введите имя пользователя и пароль, предоставленные администратором базы данных.

Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.

Выберите необходимые для работы таблицы вручную, если вы знаете, какие именно нужны вам. Или же выберите одну или две, а затем щелкните Выбор связанных таблиц для автовыбора таблиц, связанных с уже указанными.

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

В диалоговом окне Импорт данных выберите элемент Отчет сводной таблицы.

Нажмите кнопку ОК, чтобы начать импорт и заполнить список полей.

Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.

Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».

Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.

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

Работа с другими типами баз данных

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

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

Импорт таблиц из других источников

Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.

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

Использование модели данных для создания новой сводной таблицы

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

Щелкните любую ячейку на листе.

Выберите Вставка > Сводная таблица.

В диалоговом окне Создание сводной таблицы в разделе Выберите данные для анализа щелкните Использовать внешний источник данных.

Выберите вариант Выбрать подключение.

На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.

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

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

Статьи по теме

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

Даже если есть ERP и BI-системы, без Excel финансовому директору не обойтись. Всевозможные расчеты, сводные таблицы, удобные графики — в Excel можно сделать практически все что угодно. Но нужно знать, как это сделать.

Сводная таблица в Excel средствами Power Query

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

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

В этом случае необходимо создать чистый новый лист в программе Excel.

В этом листе перейти во вкладку «Данные».

Скачайте дополнительный материал к статье:

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

В появившемся окне надо указать книгу, откуда программа должна взять данные и нажать кнопку «Импорт».

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

Полезный материал про работу в Excel!

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

В нашем примере видно, что указанный лист содержит множество ячеек с данными «null». Это неверно, так как программа будет обрабатывать и эти ячейки. Чтобы сократить область обрабатываемых значений и удалить такие нулевые ячейки, необходимо исправить исходный файл. Для этого нужно перейти в исходную таблицу и нажать «Ctrl + End». Будет выделена последняя активная ячейка таблицы. Надо удалить все ячейки правее и ниже таблицы, добиваясь того, чтобы при нажатии «Ctrl + End» становилась активной нижняя правая ячейка таблицы.

После этого источник данных не будет содержать лишней информации.

Дальше необходимо отредактировать данные в разделе «Параметры запроса».

Можно удалить строчки «Навигация» и «Измененный тип». И приступить к редактированию данных в разделе «Источник». В главном окне редактора будет отображаться перечень всех листов указанной книги. В нашем случае «Лист1» и «Лист2».

Далее надо выбрать только нужную информацию. В контекстном меню колонки «Data» выбрать «Удалить другие столбцы».

Затем в строке «Data» нажать иконку с двумя стрелками, как указано на рисунке.

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

Далее необходимо убрать лишние заголовки – «шапки». Для этого надо нажать «Использовать первую строку в качестве заголовков».

Таблица будет перестроена. Дублирующую строку с «шапкой» можно удалить. Для этого в фильтре столбца «Склад» снять галочку с пункта «Склад» и нажать «ОК». Затем в этом же фильтре нажать «Удалить пустые». Соответствующие строки будут удалены.

Далее необходимо сохранить полученную таблицу. Нажать кнопку «Закрыть и загрузить», далее в меню – «Закрыть и загрузить в. ».

В появившемся окне «Загрузить в» поставить переключатель в позицию «Только создать подключение» и нажать кнопку «Загрузить». Появится запрос, на основании которого и будет строиться сводная таблица.

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

В появившемся окне установить переключатель в положение «Использовать внешний источник данных» и нажать кнопку «Выбрать подключение».

В появившемся окне выбрать имя сформированного запроса, в нашем случае – «сводная» и нажать кнопку «Открыть».

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

Сводная таблица в версиях Excel до 2016 года

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

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

Создание сводной таблицы 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 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.

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

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

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

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

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

Создание сводной таблицы 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 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.

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

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

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

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

Скачать все примеры сводной таблицы

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

Как легко объединить разные таблицы Excel в один файл — kutools.ru

Объединить файлы Excel в один файл — одна из самых распространенных задач. Стандартными способами выполнить её не просто, для этого может потребоваться значительное время (в зависимости от количества объединяемых файлов).

Kutools для Excel решает задачу объединения файлов очень просто, причем в результате мы имеем не только сводный файл с данными, но и 2 отчета: отчет о выполнении со статусом, и перечень объединенных листов (первая вкладка итоговой таблицы) с гиперссылками для удобства навигации.

Объединяемые таблицы могут содержать пустые листы, которые нам объединять не имеет смысла, kutools может их не брать в обработку.

Посмотрите предлагаемый видео ролик, где на примере слияния нескольких прайсов мы получаем сводный файл, удаляются пустые листы, присваиваются имена листам по имени родительских файлов и т.д.

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

Объединить несколько листов из разных книг в один лист

Объединить все одноименные листы в один

Консолидация и вычисление значений из нескольких книг в одну таблицу

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

Kutools позволяет сохранять сценарии объединения для дальнейшего использования.

Сводная таблица по нескольким диапазонам данных

Постановка задачи

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

Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:

• Таблицы могут иметь любое количество строк с любыми данными, но обязательно — одинаковую шапку.
• На листах с исходными таблицами не должно быть лишних данных. Один лист — одна таблица. Для контроля советую использовать сочетание клавиш Ctrl+End, которое перемещает вас на последнюю использованную ячейку листа. В идеале — это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl+End выделяется какая-либо пустая ячейка правее или ниже таблицы — удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.

Способ 1. Сборка таблиц для сводной с помощью Power Query

Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.

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

Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос — Из файла — Excel (Get Data — From file — Excel) и укажем исходный файл с таблицами, которые надо собрать:

В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit):

Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого — Источник (Source):

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

Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns):

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

Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:

Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:

Сохраним всё проделанное с помощью команды Закрыть и загрузить — Закрыть и загрузить в… (Close & Load — Close & Load to…) на вкладке Главная (Home), а в открывшемся окне выберем опцию Только подключение (Connection Only):

Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка — Сводная таблица (Insert — Pivot Table), выбирыем опцию Использовать внешний источник данных (Use external data source), а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:

Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data — Refresh All).

Способ 2. Объединяем таблицы SQL-командой UNION в макросе

Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt+F11. Затем вставляем новый пустой модуль через меню Insert — Module и копируем туда следующий код:

Sub New_Multi_Table_Pivot()
    Dim i As Long
    Dim arSQL() As String
    Dim objPivotCache As PivotCache
    Dim objRS As Object
    Dim ResultSheetName As String
    Dim SheetsNames As Variant

    'имя листа, куда будет выводиться результирующая сводная
    ResultSheetName = "Сводная"
    'массив имен листов с исходными таблицами
    SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта")

    'формируем кэш по таблицам с листов из SheetsNames
    With ActiveWorkbook
        ReDim arSQL(1 To (UBound(SheetsNames) + 1))
        For i = LBound(SheetsNames) To UBound(SheetsNames)
            arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]"
        Next i
        Set objRS = CreateObject("ADODB.Recordset")
        objRS.Open Join$(arSQL, " UNION ALL "), _
                   Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
                               .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
    End With

    'создаем заново лист для вывода результирующей сводной таблицы
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(ResultSheetName).Delete
    Set wsPivot = Worksheets.Add
    wsPivot.Name = ResultSheetName

    'выводим на этот лист сводную по сформированному кэшу
    Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
    Set objPivotCache.Recordset = objRS
    Set objRS = Nothing
    With wsPivot
        objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3")
        Set objPivotCache = Nothing
        Range("A3").Select
    End With

End Sub

Готовый макрос потом можно запустить сочетанием клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros).

Минусы такого подхода:

• Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
• При изменении количества листов необходимо правки в код макроса (массив SheetNames).
  

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

Вуаля!

Техническое замечание: если при запуске макроса вы получаете сообщение об ошибке вида «Provider not registered», то скорее всего у вас 64-битная версия Excel или установлена не полная версия Office (нет Access). Чтобы исправить ситуацию замените в коде макроса фрагмент:

	 Provider=Microsoft.Jet.OLEDB.4.0;

на:

	Provider=Microsoft.ACE.OLEDB.12.0;

И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft — Microsoft Access Database Engine 2010 Redistributable

Способ 3. Мастер консолидации сводных таблиц из старых версий Excel

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

В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard), которую при желании можно добавить на панель быстрого доступа через Файл — Параметры — Настройка панели быстрого доступа — Все команды (File — Options — Customize Quick Access Toolbar — All Commands):

После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:

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

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

Ссылки по теме

 

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул 

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

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

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

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

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация (Data — Consolidate). Откроется соответствующее окно:

   

4. Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
5. Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

   

 

 Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

Ссылки по теме

 

Excel сравнение двух таблиц из разных файлов

Сравнение двух таблиц из Access с целью выявления только совпадающие данные

​Смотрите также​​ Протокол различий в​ ее последние дни)​ в C:\ Однако​ для больших таблиц.​ между ними. Способ​ для диапазона и​ В левой нижней​ таблиц доступно только​Специализации​ вас интересуют оценки​, нажав кнопку​. В этом примере​ в разделах с​D​123456789​ изменения в учебном​Примечание:​ первом варианте можно​ хорошая. Но мне​ выдает ошибку. Тоже​

​ Также, если внутри​ решения, в данном​ нажмите Enter.​ части указаны условные​ с версиями Office​.​ тех студентов, у​Отмена​ вам не нужно​ описанием процедур в​147025836​2005​ плане по математике​ Мы стараемся как можно​ выводить в любом​ кажется, здесь не​ самое, если перед​ самих списков элементы​ случае, определяется типом​Теперь воспользуемся условным форматированием,​

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

• ​ ничего изменять. Нажмите​ этой статье.​2006​3​ повлияли на оценки​ оперативнее обеспечивать вас​ виде (например на​ учтено то, что​ Список2 я пишу​ могут повторяться, то​ исходных данных.​ чтобы выполнить сравнение​ цветов.​

• ​ Office 365 профессиональный плюс.​Добавление таблицы​ является математика. У​Вам нужно создать еще​ кнопку​Завершив ввод примера данных,​1​МАТЕМ​ учащихся. В частности,​ актуальными справочными материалами​ 3 листе). Пример​ таблицы главного (1)​ Копия. Что тут​ этот способ не​Если списки синхронизированы (отсортированы),​ двух списков в​Если вы хотите сохранить​Откройте средство сравнения электронных​.​ вас уже есть​ два объединения. Для​

​Далее​ можете перейти к​СТАТ​221​ вас интересуют оценки​ на вашем языке.​ файла во вложении.​

В этой статье

​ и связанного (2)​ неправильно?​

​ подойдет.​ то все делается​ Excel. Нам нужно​

Сравнение двух таблиц с помощью объединений

​ результаты или проанализировать​ таблиц.​Перетащите поле​ таблицы «Специализации» и​ этого перетащите поле​.​ сравнению двух таблиц.​114​A​ тех студентов, у​ Эта страница переведена​Заранее спасибо!​ с ним документа​Perfect2You​В качестве альтернативы можно​ весьма несложно, т.к.​ получить следующий результат:​

​ их в другом​В левой нижней области​Год​ «Учащиеся». Данные об​Год​На следующей странице выберите​Если вас не интересует​B​123456789​ которых профилирующим предметом​ автоматически, поэтому ее​sva​ могут находится на​: Извините, не было​ использовать функцию​ надо, по сути,​​ приложении, экспортируйте их​ выберите элементы, которые​из таблицы​ оценках хранятся в​из таблицы​ вариант​ создание листа на​707070707​2005​ является математика. У​ текст может содержать​: Смотрите.​ разных страницах Экселя.​ меня долго в​СЧЁТЕСЛИ​

Подготовка примера данных

​ сравнить значения в​Позиции, которые есть в​ в файл Excel​ хотите включить в​Учащиеся​ таблице «Учащиеся», а​Учащиеся​Не создавать ключ​ основе примера данных,​2005​3​ вас уже есть​

​ неточности и грамматические​pakh​ Скажем, основная таблица​ сети.​(COUNTIF)​ соседних ячейках каждой​ Таблице_1, но нет​ или скопируйте и​ сравнение книг, например​в поле​ данные о профилирующих​в поле​и нажмите кнопку​ пропустите следующий раздел​3​АНГЛ​ таблица, содержащая данные​

​ ошибки. Для нас​: Спасибо!!! Огромное. Где-то​ на третьей странице,​Приведенное Вами окошко​из категории​ строки. Как самый​ в Таблцие_2 будут​ вставьте в другую​ формулы, форматирование ячеек​

Специализации

​Год​	​ предметах — в​	​Год​
​Далее​	​ («Создание листов с​	​МАТЕМ​
​101​	​ о профилирующих предметах,​	​ важно, чтобы эта​
​ читал, спасибо мало,​	​ а сравниваемая -​	​ нестрашно — просто​
​Статистические​	​ простой вариант -​	​ отображаться зеленым цветом.​
​ программу, например Microsoft​	​ или макросы. Или​	​таблицы​
​ таблице «Специализации». Чтобы​	​таблицы​	​.​
​ примерами данных»).​	​221​	​B​
​ и таблица, содержащая​	​ статья была вам​	​ а стаканчик красного​
​ на 1-й или​	​ предупреждающее. Оно всегда​	​, которая подсчитывает сколько​
​ используем формулу для​	​ В тоже время​	​ Word. Можно также​
​ просто выберите вариант​	​Специализации​	​ увидеть, как изменились​
​Специализации​	​По умолчанию Access использует​	​Запустите редактор электронных таблиц​

Учащиеся

​B​	​123456789​	​ данные о студентах,​	​ полезна. Просим вас​	​ будет в самый​	​ 2-й. И по​
​ выскакивает, если закрыт​	​ раз каждый элемент​	​ сравнения значений, выдающую​	​ позиции, находящиеся в​	​ получить более точное​	​Select All​
​, а затем — поле​	​ оценки у тех,​	​, а затем — поле​	​ имя листа для​	​ и создайте пустой​	​707070707​
​2006​	​ которые их изучают.​	​ уделить пару секунд​	​ раз!​	​ этой причине замечаю​	​ какой-то файл, на​
​ из второго списка​	​ на выходе логические​	​ Таблице_2, но отсутствующие​	​ представление каждого листа​	​(Выделить все).​	​Учебный план​
​ кто специализируется на​	​Учебный план​	​ новой таблицы. Оставьте​	​ файл. Если вы​	​2005​	​1​
​ Данные об оценках​	​ и сообщить, помогла​	​Ann123​	​ ошибки. Вы конечно​	​ который ссылки есть.​	​ встречался в первом:​
​ значения​	​ в Таблице_1, будут​	​ с отображением форматирования​	​На вкладке​	​из таблицы​	​ математике, вам нужно​
​из таблицы​	​ имя в поле​	​ используете Excel, при​	​3​	​МАТЕМ​	​ хранятся в таблице​
​ ли она вам,​	​: Роман, помогите мне!​	​ можете посоветовать объединить​	​ И Вам на​	​Полученный в результате ноль​	​ИСТИНА (TRUE)​
​ подсвечены синим цветом.​	​ ячеек, близкое к​	​Home​	​Учащиеся​	​ просмотреть записи из​	​Учащиеся​
​Импорт в таблицу​	​ его запуске по​	​СТАТ​	​242​	​ «Учащиеся», а данные​	​ с помощью кнопок​
​ Плиз.​	​ три страницы в​	​ выбор: остави