Excel две таблицы с датами в одну

Skip to content

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

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

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

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

  • Суммирование данных
  • Использование ВПР
  • Применение ИНДЕКС + ПОИСКПОЗ
  • Формула объединения для нескольких ключевых столбцов
  • Объединение таблиц при помощи Power Query
  • Быстрый способ объединить таблицы путем сопоставления ключевых столбцов.

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

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

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

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

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

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

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

=’1кв’!C3+’2кв’!C3+’3кв’!C3+’4кв’!C3

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

=СУММ(‘1кв:4кв’!C3)

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

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

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

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

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

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

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

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

Чтобы объединить две таблицы по соответствующему показателю (Код заказа), в начале добавляем дополнительные колонки E, F и G, которые будем заполнять нужной информацией.

Начнём с товара. Введите эту формулу в ячейку Е2, как показано на скриншоте ниже:

=ВПР(A2;товар;2;0)

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

=ВПР(A2;Товар!$A$2:$D$200;2;0)

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

=ВПР(B2;товар;3;0)

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

=ВПР(B2;товар;4;0)

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

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

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

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

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

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

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

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

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

=ЛЕВСИМВ(A2;4)

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

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

=ПРАВСИМВ(A2;4)

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

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

=ПСТР(A2;5;4)

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

=ЛЕВСИМВ(A2;НАЙТИ(«-«;A2)-1)

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

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

=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(«*»;ПОДСТАВИТЬ(A2;»-«;»*»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»-«;»»)))))

Получим 100777.

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

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

=ВПР(B2;товар;2;0)

или

=ВПР(B2;Товар!$A$2:$D$200;2;0)

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

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

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

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

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

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

=ИНДЕКС(‘Товар’!$B$2:$B$200;ПОИСКПОЗ(A2;’Товар’!$A$2:$A$200;0))

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

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

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

=ИНДЕКС(‘Товар’!$A$2:$A$20;ПОИСКПОЗ(A2;’Товар’!$B$2:$B$20;0))

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

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

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

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

{=ИНДЕКС(Товар!$B$2:$B$200;ПОИСКПОЗ(1;ПОИСК(ЛЕВСИМВ(A2;4);Товар!$A$2:$A$200;1);0))}

Как видите, мы берём первые 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

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

=ИНДЕКС($F$3:$H$10; ПОИСКПОЗ(1; ($B3=$F$3:$F$10) * ($C3=$G$3:$G$10); 0); 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 в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…

Источник

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

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

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

consolid1.gif

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

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

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

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

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

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

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

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

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

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

consolidation2.png

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

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

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

    consolidation3.png

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

    consolidation4.png

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

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

consolidation5.png

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

consolidation6.png

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

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

Содержание

    • 0.1 Способ 1. С помощью формул 
    • 0.2 Способ 2. Если таблицы неодинаковые или в разных файлах
    • 0.3 Ссылки по теме
  • 1 Если таблицы одинаковые
  • 2 Если таблицы разные
  • 3 Видео в помощь
    • 3.1 Другие формулы
    • 3.2 1. Создаём вспомогательную таблицу для поиска.
    • 3.3 2. Обновляем главную таблицу при помощи данных из таблицы для поиска.
    • 3.4 3. Переносим данные из таблицы поиска в главную таблицу

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

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

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

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

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

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

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

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

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

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

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

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

как сделать слияние таблиц в excel

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

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

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

    как сделать слияние таблиц в excel

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

    как сделать слияние таблиц в excel

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

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

как сделать слияние таблиц в excel

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

как сделать слияние таблиц в excel

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

  • Макрос для автоматической сборки данных с разных листов в одну таблицу 
  • Макрос для сборки листов из нескольких файлов

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

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

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

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

    Если таблицы разные

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

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

    как сделать слияние таблиц в excel

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

    Как выполнить консолидацию? Все очень просто:

    1. Откройте предварительно все исходники, после чего создайте пустую книгу, воспользовавшись комбинацией +.
    2. Выберите ячейку, затем откройте вкладку «Данные» и отыщите кнопочку «Консолидация».
    3. В открывшемся окне необходимо установить соответствующие параметры: в поле «Ссылка» перейдите на одну из таблиц и выделите ее полностью, включая шапку. Теперь кликните по кнопке «Добавить», которая расположена в окошке консолидации, после чего в поле «Список диапазонов» будет добавлен диапазон, выделенный ранее.
      как сделать слияние таблиц в excel
    4. Проделайте те же действия для оставшихся таблиц.
    5. Поставьте галочки в обоих пунктах «Использовать в качестве имен», а также напротив «Создавать связи с исходными данными». Нажмите «ОК».
    6. Теперь можно наслаждаться результатом: все файлы просуммировались по названиям левого столбика и верхней строчки выделенных областей каждой из таблиц.

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

    Видео в помощь

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

    как сделать слияние таблиц в excel

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

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

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

    Выход есть всегда, читайте далее и Вы узнаете решение!

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

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

    • Ключевой столбец в одной из таблиц содержит дополнительные символы
    • Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
    • Данные в ключевых столбцах не совпадают (123-SDX и HFGT-23) или есть частичное совпадение, меняющееся от ячейки к ячейке (Coca Cola и Coca-Cola Inc.)

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

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

    как сделать слияние таблиц в excel

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

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

    • Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:как сделать слияние таблиц в excel
    • Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):как сделать слияние таблиц в excel
    • Даём столбцу имя SKU helper.
    • Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:

      =ЛЕВСИМВ(A2;5)
      =LEFT(A2,5)

      Здесь A2 – это адрес ячейки, из которой мы будем извлекать символы, а – количество символов, которое будет извлечено.

      как сделать слияние таблиц в excel

    • Копируем эту формулу во все ячейки нового столбца.

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

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

    как сделать слияние таблиц в excel

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

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

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

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

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

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

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

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

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

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

    как сделать слияние таблиц в excel

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

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

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

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

    как сделать слияние таблиц в excel

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

    как сделать слияние таблиц в excel

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

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

    как сделать слияние таблиц в excel

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

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

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

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

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

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

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

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

    как сделать слияние таблиц в excel

    Далее при помощи функции ВПР (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:

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

    Урок подготовлен для Вас командой сайта office-guru.ru
    Источник: /> Перевел: Антон Андронов

    Правила перепечаткиЕще больше уроков по Microsoft Excel

    Оцените качество статьи. Нам важно ваше мнение:

    Источник

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

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

    В этой статье мы рассмотрим наиболее эффективные способы как это сделать.

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

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

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

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

    Для этого в ячейку D2 вставим формулу с функцией ВПР:

    • B2 – ячейка с названием товара, которое мы ищем в таблице с ценами;
    • $G$2:$H$4 – диапазон ячеек таблицы с наименованием товаров и ценами. В столбце G содержатся названия товаров, по которым функция осуществляет поиск. В столбце H отражены цены, которые функция ВПР будет подставлять в нашу таблицу. Диапазон ячеек включает в себя значки $, с их помощью диапазон зафиксирован и не будет изменяться при протягивании формулы по другим ячейкам.
    • 2 – номер столбца в диапазоне данных с ценами на товары, которые мы хотим подставить в нашу таблицу.
    • 0 – точность совпадения данных. Ставим “0”, так как нам необходимо точное совпадение.

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

    Консолидация данных в программе Microsoft Excel

    Консолидация в Microsoft Excel

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

    Условия для выполнения процедуры консолидации

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

      • столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
      • не должно быть столбцов или строк с пустыми значениями;
      • шаблоны у таблиц должны быть одинаковыми.

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

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

        Открываем отдельный лист для консолидированной таблицы.

      Добавление нового листа в Microsoft Excel

      Переход к консолидации данных в Microsoft Excel

      Настройки консолидации в Microsoft Excel

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

      • сумма;
      • количество;
      • среднее;
      • максимум;
      • минимум;
      • произведение;
      • количество чисел;
      • смещенное отклонение;
      • несмещенное отклонение;
      • смещенная дисперсия;
      • несмещенная дисперсия.

      Выбор функции для консолидации в Microsoft Excel

      Переход к выбору диапазона для консолидации в Microsoft Excel

      Выбор диапазона для консолидации в Microsoft Excel

      Добавление диапазона в Microsoft Excel

      Как видим, после этого диапазон добавляется в список.

      Диапазон добавлен в Microsoft Excel

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

      Все диапазоны добавлены для консолидации в Microsoft Excel

      Выбор файла для консолидации в Microsoft Excel

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

      Установка настроек для консолидации в Microsoft Excel

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

      Содержимое группы группы консолидированной таблицы в Microsoft Excel

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

      ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

      Как объединить файлы Excel и таблицы

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

      Объединение файлов и таблиц Excel

      Как объединить файлы и таблицы Excel

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

      1. Объединить таблицы в новый или существующий файл
      2. Объединить несколько файлов Excel

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

      Объединить таблицы в новый или существующий файл

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

      • Откройте исходный файл Excel и переключитесь на лист, который вы хотите скопировать.
      • Щелкните вкладку «Главная»> раздел «Ячейки»> «Формат»> «Переместить или скопировать лист».
      • Откроется всплывающее окно, в котором вы можете выбрать существующий файл Excel или создать новый файл на ходу.
        • Когда ты выберите новый вариант файла, он мгновенно создаст новый файл, но не сохранит его.
        • Когда ты выберите существующий файл, у вас есть возможность выбрать, куда будет вставлен лист, т. е. до или после существующих листов или в конце всех листов.

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

        Если вы хотите переместить несколько листов в другой файл Excel, то перед использованием «Переместите или скопируйте лист » выберите листы с помощью Ctrl или Shift. В то время как Shift позволяет вам выбирать соседние листы или диапазон листов, Ctrl позволяет вам выбирать отдельные листы. Остальные шаги такие же. Вы можете использовать это для ручного слияния файлов Excel.

        Объединить несколько файлов Excel

        Макрос MergeExcel

        Слияние файлов Excel — сложная вещь, и для этого мы будем использовать код VBA из ExtendOffice. Это позволит вам объединить несколько файлов или книг, доступных в папке.

        • Создайте новую электронную таблицу Excel и нажмите ALT + F11, чтобы открыть раздел разработчика.
        • Щелкните меню «Вставка», а затем «Модуль»
        • Вставьте код, указанный ниже. Назовите модуль MergeExcel

        Затем нажмите Alt + F8 открыть Макрос диалог. Это покажет все макросы на листе. Выбирать MergeExcel и нажмите Пробег. Вам будет предложено сохранить файлы, обязательно сделайте это. После этого все листы из разных файлов Excel станут доступны в файле Excel, в котором вы запускали макрос. На веб-сайте ExtendOffice есть много таких макросов, и я настоятельно рекомендую посетить их по адресу extendoffice.com.

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

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

      Источник

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

      ​Смотрите также​​For i =​ прошу но я​ — кода поменьше,​ потому, что у​Закрыть и загрузить в…​ результатов будет происходить​ демографическими тенденциями на​ а также другим​Получение внешних данных​ будет предложено создать​ может создавать эти​В разделе​Все команды​ В случае расширения​ столбцов в таблице​06.02.2014​Примечание:​ 2 To UBound(arr_a)​ пыталась сделать (в​ но понять думаю​ Вас в поле​

      ​ (Close&Load to…)​ автоматически и мгновенно,​ каждой территории. Так​ значениям в календаре.​ >​ связь, то в​ связи в модели​Во-первых, укажите количество полей​.​ исходного диапазона перед​ «Синяя» заполнены значениями.​E3331​Мы стараемся как​t = arr_a(i,​ вашем примере) и​ сложнее:​ «Пролечено пациентов» в​:​ сразу после изменений​ как демографические данные​Советы:​

      ​Из службы данных​ Excel уже есть​ данных, формируемой в​ страницы сводной таблицы​Выберите в списке пункт​ обновлением сводной таблицы​ В них содержатся​20056​ можно оперативнее обеспечивать​ 1) & «|»​

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

      ​ не получается​Option Explicit​ исходных данных числа​В следующем диалоговом окне​ в исходных списках.​ поступают из различных​ По умолчанию месяцы перечислены​ >​ сведения, необходимые для​ фоновом режиме. В​щелкните число полей,​Мастер сводных таблиц и​ можно обновить диапазон​ формулы ВПР, но​06.02.2014​ вас актуальными справочными​ & arr_a(i, 2)​Guest​Sub tt()​ будут считаться как​ (оно может выглядеть​ Для удобства и​ источников, то их​ в алфавитном порядке.​Из Microsoft Azure Marketplace​

      Объединение двух столбцов с другой таблицей

      ​ связи данных.​ других случаях необходимо​ которые вы хотите​ диаграмм​ на отдельном листе​ отображаются значения. Возможно,​D534X​ материалами на вашем​If Not oDict.exists(t)​: Эта информация построена​Dim oD As​ текст, а не​ немного по-другому -​ краткости давайте дадим​ таблицы первоначально изолированы​ С помощью надстройки​.​Создание связей подобно использованию​ создавать связи вручную.​

      ​ использовать.​и нажмите кнопку​ для указанного имени​ вы захотите заменить​Ниже приведены данные для​ языке. Эта страница​ Then​ с помощью штатных​ Object, a(), b(),​ как числа, ибо​

      ​ не пугайтесь) выбираем​

      ​ нашим спискам имена​

      ​ от остальной части​

      ​ Power Pivot вы​

      ​В разделе​

      ​ команд vlookup. Необходимы​

      ​Убедитесь, что книга содержит​

      ​В группе​

      ​Добавить​

      ​ таким образом, чтобы​

      ​ формулы ВПР в​

      ​ оранжевой таблицы. Скопируйте​

      ​ переведена автоматически, поэтому​

      ​cnt = cnt​

      ​ режимов EXCEL -​

      ​ ii&​

      ​ есть пустые ячейки​

      ​Только создать подключение (Only​

      ​Список1​

      ​ модели. Для интеграции​

      ​ можете изменить порядок​

      ​Type​

      ​ столбцы, содержащие совпадающие​

      ​ хотя бы две​

      ​Затем выберите диапазон в​

      ​, а затем — кнопку​

      ​ включить в него​

      ​ этих ячейках фактическими​

      ​ его в том​

      ​ ее текст может​

      ​ + 1​

      ​ Данные (сортировка) и​

      ​If Not find_(a(),​

      ​ там. Замените пустые​

      ​ create connection)​

      ​и​

      ​ демографических данных с​ сортировки так, чтобы​(Тип) нажмите​ данные, так что​ таблицы и в​ списке и укажите​ОК​ новые данные.​ значениями.​ же листе. После​

      ​ содержать неточности и​

      ​oDict.Item(t) = cnt​

      ​ Данные (Итоги).​

      ​ «тип») Then Exit​

      ​ ячейки на нули​

      ​:​

      ​Список2​

      ​ остальной частью своей​

      ​ они отображались в​

      ​Data​

      ​ Excel может создавать​

      ​ каждой из них​

      ​ метку элемента в​

      ​.​

      ​В Excel также доступны​

      ​Выделите все ячейки значений​

      ​ его вставки в​

      ​ грамматические ошибки. Для​

      ​arr_c(cnt, 1) =​

      ​Макрос просто заменил​

      ​ Sub​

      ​ и обновите сводную.​

      ​Повторяем всю процедуру (пункты​

      ​, используя​

      ​ модели вам нужно​

      ​ хронологическом порядке.​

      ​Данные).​

      ​ перекрестные ссылки для​

      ​ есть столбец, который​

      ​ каждом из доступных​

      ​В книге щелкните пустую​ другие способы консолидации​ в столбце «Код​ лист, нажмите клавиши​ нас важно, чтобы​ arr_a(i, 1)​ их «ручное» использование.​If Not find_(b(),​ Все будет суммироваться.​ 2-6) для второго​Диспетчер имен​ будет найти столбец​Таблица​В разделе​ строк из одной​ можно сопоставить со​ окон полей. Повторите​

      ​ ячейку, которая не​

      1. ​ данных, которые позволяют​ продажи» и нажмите​ Ctrl + T​ эта статья была​

      2. ​arr_c(cnt, 2) =​Такой «свод» Вы​ «дата») Then Exit​Skoda​

        ​ списка. При переименовании​на вкладке​ в одной из​BasicCalendarUS​

      3. ​Price​ таблицы на строки​ столбцом из другой​ операцию для каждого​

        ​ является частью сводной​

      4. ​ работать с данными​ клавиши CTRL+C, чтобы​ можно преобразовать в​

        ​ вам полезна. Просим​ arr_a(i, 2)​Частично введенная формула ВПР

        ​ должны делать ежемесячно?​ Sub​: Подскажите, как соединить​ заголовка столбца важно​Формулы (Formulas — Name​

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

      5. ​ таблицы.​ в разных форматах​ скопировать их.​ таблицу, а затем​ вас уделить пару​arr_c(cnt, n) =​

      6. ​Если — да,​ReDim c(1 To​ данные из 2​

        Законченная формула ВПР

        ​ использовать то же​ Manager — Create)​ тому, который вы​ в окне Power​Free​ примере с логикой​Отформатируйте данные в виде​для каждого из​На странице​ и макетах. Например,​На вкладке​

        ​ переименовать таблицу оранжевый.​ секунд и сообщить,​ arr_a(i, 3)​ стало быть, Ваша​

      7. ​ UBound(a) + UBound(b),​ таблиц в третью.​ имя (Люди), что​:​ уже используете. Например,​ Pivot.​

      8. ​(Бесплатно).​ операций со временем​ таблицы или​ полей страницы выберите​

        Законченная формула ВПР

        ​Шаг 1​ вы можете создавать​Главная​Номер заказа​ помогла ли она​Else​ проблема — не​

        ​ 1 To 3)​Таблицы на одном​ и в предыдущем​После именования, нужная нам​ если демографические данные​В главной таблице нажмите​Найдите​ таблица клиента должна​импортируйте внешние данные как​ диапазон ячеек, а​

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

      10. ​arr_c(oDict.Item(t), n) =​​ «разовая», а ежемесячная.​​ii = ii​ листе.​​ запросе.​​ формула будет выглядеть​

        Стрелка под кнопкой

      11. ​ организованы по регионам​Сортировка по столбцу​​US Air Carrier Flight​​ содержать значения даты,​

        Кнопка

      12. ​ таблицу на новый​ затем укажите его​в нескольких диапазонах консолидации​ ссылками или использовать​ кнопкой​

        ​Регион​ кнопок внизу страницы.​ arr_c(oDict.Item(t), n) +​

      Дополнительные сведения о таблицах и функции ВПР

      • ​Данному макросу не​ + 1​Читала в интернете​

      • ​В окне Excel на​ следующим образом:​

      • ​ и ваши данные​.​

      support.office.com

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

      ​ Delays​ которые есть и​ лист.​ имя.​, а затем нажмите​ команду​Вставить​20050​ Для удобства также​ arr_a(i, 3)​ критично, сколько строк​c(ii, 1) =​ на эту тему​ вкладке​На первый взгляд выглядит​ о продажах определяют​В поле «Сортировать» выберите​и нажмите​ в таблице логики​Присвойте каждой из таблиц​

      Консолидация нескольких диапазонов

      ​Пример​ кнопку​Консолидация​.​447​ приводим ссылку на​End If​ в Ваших исходных​ «Склад»: c(ii, 2)​ статьи и вот​Данные (Data)​ жутковато, но, на​ область продажи, то​MonthInCalendar​Select​ операций со временем.​

      ​ понятное имя: На​Если в разделе​Далее​(доступную на вкладке​​В коллекции параметров вставки​​Запад​ оригинал (на английском​Next​ таблицах.​ = «Продукт»: c(ii,​ что пишут:​или на вкладке​ самом деле, все​ вы могли бы​

      Настройка исходных данных

      ​.​(Выбрать).​В модели данных связи​ вкладке​Во-первых, укажите количество полей​.​Данные​ нажмите кнопку​20051​ языке) .​End Sub​Он «закреплен» только​ 3) = «Продажи»​1.Вычислить одинаковые названия​Power Query​

      Образцы источников данных для консолидации в сводный отчет

      Поля страницы при консолидации данных

      ​ не так страшно.​ связать два набора​В поле «По» выберите​Прокрутите вниз и нажмите​ таблиц могут быть​Работа с таблицами​ страницы сводной таблицы​На странице​в группе​Значения​398​Можно объединить строк (объединение)​Повторы в каждой​ за структурой этих​Set oD =​ столбцов.​выбираем​ Давайте я разложу​ данных, найдя общие​MonthOfYear​Select Query​

      Образец сводного отчета с полем страницы.

      Использование именованных диапазонов

      ​ типа «один к​щелкните​задано число​Шаг 2а​Работа с данными​.​Юг​ из одной таблицы​ таблице будут суммироваться,​ таблиц на листе:​ CreateObject(«scripting.dictionary»)​2.Последовательно объединить значения​Получить данные — Объединить​ эту формулу на​ столбцы, такие как​.​(Запрос на выборку).​ одному» (у каждого​Конструктор​1​выберите параметр​).​

      Другие способы консолидации данных

      ​Выделите все ячейки значений​20052​ в другую путем​ между таблицами не​ столбы А-C, E-G.​distadd oD, a,​ в строках этих​ запросы — Добавить​ несколько строк, используя​ государство, почтовый индекс​​Сводная таблица теперь сортирует​​Нажмите кнопку​​ пассажира есть один​​>​​, выберите каждый из​​Создать одно поле страницы​

      Консолидация нескольких диапазонов

      ​Для консолидации нескольких диапазонов​ в столбце «Регион»,​1006​ вставки данных в​ будут, т.е. как​После изменения информации​ c, ii​ столбцов.​ (Get Data -​

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

      ​ сочетание клавиш Alt+Enter​ или регион, чтобы​ каждую комбинацию «месяц​Далее​ посадочный талон) или​

      1. ​Имя таблицы​ диапазонов, а затем​, а затем нажмите​ вы можете воспользоваться​

        1. ​ скопируйте их и​Север​ первой пустой ячейки​​ заказывали.​​ в исходных таблицах​

        2. ​distadd oD, b,​​3.Там где столбцы​​ Merge Queries -​​ и отступы пробелами,​​ обеспечить подстановку.​

        3. ​ и год» (октябрь​​.​ «один ко многим»​​и введите имя.​​ введите уникальное имя​​ кнопку​​ мастером сводных таблиц​​ повторите шаги 10​

      2. ​20053​ под целевой таблицы.​Ксения​ макрос строит новый​

      3. ​ c, ii​ не пересекаются выводить​

      4. ​ Append)​​ как мы делали,​​Кроме совпадающих значений есть​​ 2011, ноябрь 2011)​​Нажмите​ (в каждом рейсе​​Убедитесь, что столбец в​​ в поле​

      5. ​Далее​​ и диаграмм. В​​ и 11.​​447​​ Таблица приводит к​: Здравствуйте! Открыла файл​​ «свод», а также​​With Workbooks.Add(1).Sheets(1).[a1].Resize(ii, 3)​

      6. ​ значения из той​​:​​ например тут:​

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

          ​Теперь формулы ВПР в​​Запад​ увеличению размера, чтобы​ а визуал бэйсик​ на дополнительных листах​.Value = c​

        2. ​ таблицы в которой​​В появившемся диалоговом окне​Логика тут следующая:​​ для создания связей.​​ в году (10,​​для импорта данных.​ не «многие ко​​ имеет уникальные значения​​. Если у вас​

      7. ​На странице​​ сколько полей страницы​​ двух столбцах заменены​20054​ включить новые строки.​ пустой, а так​ итоги в «разрезе»​.Borders.Weight = xlThin​​ значения есть. В​​ выбираем наши запросы​

      Консолидация данных с использованием одного поля страницы

      ​Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает​Значения данных в столбце​ 11). Изменить порядок​ При быстром подключении​ многим». Связи «многие​ без дубликатов. Excel​ четыре диапазона, каждый​

      1. ​Шаг 2б​ будет использоваться: ни​ значениями.​885​

        1. ​ Если строк в​ нужно. Спасибо​ — Менеджер-Область.​​End With​​ тех что нет​

        2. ​ из выпадающих списков:​​ все уникальные элементы​​ подстановки должны быть​​ сортировки несложно, потому​​ к Интернету импорт​

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

      2. ​Юрий М​Guest​End Sub​ значений строки оставлять​

      3. ​В итоге получим новый​​ из первого списка.​​ уникальными. Другими словами,​​ что канал​​ займет около 15 минут.​ к ошибкам циклической​​ только в том​​ кварталу финансового года,​

      4. ​Перейдите в книгу и​​ несколько.​​ строку или столбец​​20055​​ можно объединить столбцы​: А скопировать приведённый​​: раньше это не​​Function find_(arr, ss​

      5. ​ пустыми.​​ запрос, где два​​ Как только они​

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

          ​ в таблице​​398​ из одной таблицы​ сообщением выше код?​ использовалось, если разберусь​ As String) As​

      6. ​Таблицу с примером​​ списка будут соединены​​ заканчиваются — начинает​

      7. ​ содержать дубликаты. В​​предоставляет все необходимые​​ увидите отчет о​ «Обнаружена циклическая зависимость».​ столбец содержит уникальные​ введите имя «Кв1»,​ а затем вернитесь​ диапазонов и создать​​Использование структурированных ссылок в​​Юг​

      Консолидация данных с использованием нескольких полей страницы

      ​ с другой —​Hugo​ то буду использовать​ Boolean​ приложила во вложение​ друг под другом.​ выдавать ошибку #Н/Д:​ модели данных нули​ столбцы для работы​ состоянии перемещения 2 427 284​ Эта ошибка может​ значения.​ выберите второй диапазон,​ в мастер сводных​ консолидированный диапазон без​ формулах таблиц Excel​20056​ путем вставки их​: Добавлю — если​

      1. ​Skoda​Dim r As​Guest​ Осталось удалить дубликаты​

        1. ​Формула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным​ и пустые строки​ этого сценария. Если​​ строк. Нажмите​​ произойти, если вы​

        2. ​Например, чтобы связать продажи​​ введите имя «Кв2″​​ таблиц и диаграмм​​ полей страницы, сделайте​​Использование функции ВПР (учебный​

        3. ​644​​ в первой пустой​ скопировать код в​​: не получается у​​ Range​​: Уважаемая Skoda!​​ кнопкой​​ образом извлекает уникальные​

      2. ​ эквивалентны пустому полю,​ вы используете другую​Закрыть​ создаете прямое подключение​

      3. ​ клиента с логикой​​ и повторите процедуру​​ и нажмите кнопку​​ следующее:​​ курс)​Восток​​ ячейки справа от​​ модуль листа «Ваша»,​

      4. ​ меня самостоятельно сделать​​Set r =​​Есть решение Вашей​​Удалить строки — Удалить​​ элементы из второго​ которое является самостоятельным​​ таблицу логики операций​​. Теперь у вас​

      5. ​ между двумя таблицами​​ операций со временем,​​ для диапазонов «Кв3″​

        1. ​Добавить​Добавьте мастер сводных таблиц​Консолидация данных представляет собой​20057​ таблицы. В этом​ то его запускать​​ в макросе чтобы​​ Cells.find(ss)​

          ​ задачи.​​ дубликаты (Delete Rows​ списка.​ значением данных. Это​ со временем, ваши​ есть две таблицы​

        2. ​ со связью «многие​​ обе таблицы должны​ и «Кв4».​​.​ и диаграмм на​ удобный способ объединения​

        3. ​1270​​ случае также таблицы​ можно при активном​ не было суммирования,​If Not r​Skoda​ — Delete Duplicates)​Вложенные друг в друга​​ означает, что не​ действия будут другими.​ в модели данных.​ ко многим» или​ включать дату в​

          ​Если в разделе​

          • ​Совет:​​ панель быстрого доступа.​ данных из нескольких​​Восток​​ приводит к увеличению​​ любом листе.​ а выводился столбец​ Is Nothing Then​: тогда подскажите как​​:​​ две функции ЕСЛИОШИБКА​ может быть несколько​По мере добавления полей​ Чтобы связать их,​ непрямые подключения (цепочку​ одинаковом формате (например,​Во-первых, укажите количество полей​ Если диапазон ячеек находится​ Для этого:​ источников в одном​20058​

          • ​ учетом новых столбцов.​​katikera​ факт, план​​arr = r.CurrentRegion.Value​​ решить или где​​Готовый запрос можно переименовать​ реализуют вывод сначала​​ нулей в столбце​​ в сводную таблицу​ нужны совместимые столбцы​ связей таблиц, в​​ 01.01.2012) и по​​ страницы сводной таблицы​ в другой книге,​Щелкните стрелку рядом с​ отчете. Например, если​885​Объединение строк – довольно​: Доброго времени суток!​Юрий М​find_ = True​ прочитать. Пожалуйста​ справа на панели​ уникальных из списка-1,​

        4. ​ подстановок.​​ вы получите уведомление​​ в каждой таблице.​

      6. ​ которой каждая таблица​​ крайней мере в​​задано число​ сначала откройте ее,​ панелью инструментов и​ в каждом из​Восток​ простой процесс, но​​Помогите пожалуйста, как​​: >> а если​

      ​Else​

      support.office.com

      Создание связи между двумя таблицами в Excel

      ​Guest​​ параметров, дав ему​ а потом из​Типы данных столбца подстановок​ о необходимости связи​Убедитесь, что значения в​ связана со следующей​ одной таблице (логика​2​ чтобы упростить выбор​ выберите​ региональных филиалов вашей​Нужно ли обеспечить правильность​ Объединение столбцов может​ совместить 4 таблицы​ не соединять в​find_ = False​: Куда выслать файл?​ вменяемое имя (это​ списка-2 друг за​ и исходного столбца​ между таблицами, чтобы​

      ​ столбце​ отношением «один ко​ операций со временем)​, выполните аналогичные действия​ данных.​Дополнительные команды​ компании есть сводная​ код продажи «и»​ быть сложнее, если​ за разные периоды,​ один столбец…​End If​Guest​ будет имя таблицы-результата​ другом.​ должны быть совместимы.​ разобраться с полями,​DateKey​ многим», но между​ должны быть перечислены​ в поле​Нажмите кнопку​.​ таблица расходов, с​ Регион значения для​ не соответствуют строки​ если количество строк​>> а можно​End Function​: Уважаемая Skoda!​

      ​ по факту) и​Обратите внимание, что это​ Подробнее о типах​ выбранными в сводной​

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

      1. ​ и их наименование​ сделать простой макрос​Sub distadd(oDict As​Решение: макрос.​ все и можно​ формула массива, т.е.​ данных см. в​ таблице.​

      2. ​BasicCalendarUS​ образуется отношение «многие​

        ​ один раз в​. Затем выберите два​.​

      3. ​Выбрать команды из​ можно объединить такие​ с каждым элементом​​ строками в другой​​ каждый месяц менялись?​​ как первый раз​​ Object, arr_a, arr_c,​​В таблицах План​​ все выгружать на​

      4. ​ после набора ее​ статье Типы данных​Хотя Excel может подсказать​указаны в формате​ ко многим»). Дополнительные​ столбце.​ диапазона и введите​На странице​выберите пункт​

        ​ значения в корпоративный​ строки уникальных заказа.​ таблице. С помощью​ikki​ Вы сделали…​ cnt)​ и Факт не​ лист командой​ нужно ввести в​ в моделях данных.​ вам, когда необходима​ 01.01.2012 00:00:00. В​ сведения см. в​Щелкните​

      5. ​ в поле​​Шаг 3​​Все команды​​ отчет о расходах.​​ Чтобы сделать это,​

      ​ функции ВПР, можно​​: 1) копируете списки​​Skoda, это ведь​Dim i&, t$​ должно быть повторяющихся​

      1. ​Закрыть и загрузить (Close&Load)​​ ячейку не обычным​​Подробнее о связях таблиц​​ связь, он не​​ таблице​

      2. ​ статье Связи между​​Данные​​Второе поле​укажите, следует ли​​.​​ Такой отчет может​ давайте вставьте заголовки​ избежать проблем выравнивания.​ наименований из каждой​ может продолжаться бесконечно​For i =​ строк.​:​ Enter, а сочетанием​ см. в статье​ может подсказать, какие​On_Time_Performance​ таблицами в модели​>​одинаковое имя, например​

      3. ​ добавлять сводную таблицу​​Выберите в списке пункт​​ содержать итоговые и​ таблицы Код продажи​В приведенном ниже примере,​​ из четырех таблиц​​ — Вы постоянно​ 2 To UBound(arr_a)​Их легко определить:​В будущем, при любых​ клавиш Ctrl+Shift+Enter и​

      4. ​ Связи между таблицами​​ таблицы и столбцы​​также есть столбец​ данных.​Отношения​ «Пг1» и «Пг2».​ на новый или​​Мастер сводных таблиц и​​ средние данные по​

      5. ​ «и» Регион в​​ вы увидите двух​​ в один общий​ что-то меняете… Так​t = arr_a(i,​1. Данные (Сортировка​ изменениях или дополнениях​​ затем скопировать (протянуть)​​ в модели данных.​

      6. ​ использовать, а также​​ даты и времени​​Типы данных в двух​

      Дополнительные сведения о связях между таблицами в Excel

      ​.​

      ​ Выберите первый диапазон​ же на существующий​ диаграмм​ продажам, текущим уровням​

      ​ ячеек справа от​ таблиц, которые ранее​

      ​ список — друг​ не годится.​

      ​ 1) & «|»​ (Склад (Тип))).​ в исходных списках,​ вниз на нижестоящие​К началу страницы​

      Примечания о связях

      • ​ возможна ли связь​FlightDate​ столбцах должны быть​Если команда​ и введите имя​ лист, и нажмите​и нажмите кнопку​ запасов и наиболее​ синего таблицы и​ были имена других​ под дружкой​

      • ​Hugo​ & arr_a(i, 2)​2. Данные (Итоги​ достаточно будет лишь​ ячейки с запасом.​Классическая ситуация: у вас​ между таблицами. Чтобы​, значения которого указаны​ совместимы. Подробные сведения​Отношения​ «Пг1», выберите второй​ кнопку​Добавить​ популярным продуктам в​ формулы ВПР правильные​ новыми именами: «Синий»​

      • ​2) удаляете дубликаты​: Я вообще ничего​If Not oDict.exists(t)​ (Количество (Тип))).​ правой кнопкой мыши​В английской версии Excel​ есть два списка,​ получить ответы на​ в том же​ см. в статье​недоступна, значит книга​ диапазон и введите​Готово​, а затем — кнопку​ рамках всей организации.​ значения с помощью​ и «Оранжевая». В​3) обычными ВПР​ не понимаю :)​ Then​Провести Тест:​ обновить таблицу результатов.​ эта формула выглядит​ которые надо слить​ свои вопросы, попробуйте​ формате: 01.01.2012 00:00:00.​ Типы данных в​ содержит только одну​ имя «Пг1», выберите​.​ОК​Для подведения итогов и​ код продажи и​ таблице синий каждая​

      • ​ подтягиваете данные​Какие там макросы​cnt = cnt​1. Таблица План​fatbobrik​ как:​

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

      Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

      ​ область столбцов в​ строка представляет собой​правда, для каждого​ изучают… Я ни​ + 1​ = Таблица Факт.​: Здравствуйте! Ситуация такая:​=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1),​ в исходных списках​Если ваша модель содержит​ совпадающие данные одинакового​Другие способы создания связей​В окне​

      1. ​ введите имя «Пг2»,​ полей страницы и​В книге щелкните пустую​ данным в отдельных​

      2. ​ таблице оранжевый.​​ элемент строки для​​ месяца придётся писать​​ одного макроса анонима​​oDict.Item(t) = cnt​​2. Таблица План​​ имеется файл эксель​ 0)), INDEX(Список2, MATCH(0,​ могут быть как​ всего лишь несколько​

      3. ​ типа и по​​ могут оказаться более​​Управление связями​​ выберите четвертый диапазон​​ назначить имена элементов​

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

      5. ​ не видел, видать​​arr_c(cnt, 1) =​​ < Таблица Факт.​​ с двумя сводными​​ COUNTIF($E$1:E1, Список2), 0))),​

      6. ​ уникальные элементы, так​ таблиц, понятно, какие​​ крайней мере один​​ понятными, особенно если​нажмите кнопку​

      7. ​ и введите имя​​ каждому из исходных​​ является частью сводной​

      8. ​ их в сводной​​Скопируйте заголовки «код продажи»​​ заказа 20050 состоит​

      9. ​ ВПР.​​ макросы в личке,​​ arr_a(i, 1)​​3. Таблица План​​ таблицами на разных​ «»)​ и совпадающие (и​ из них нужно​ из столбцов (​ неизвестно, какие столбцы​Создать​​ «Пг2».​​ диапазонов. Это позволяет​

      10. ​ таблицы.​ таблице на главном​​ и «Регион в​​ из двух элементов,​​но если у​​ а обсуждение тут…​​arr_c(cnt, 2) =​​ > Таблица Факт.​

      11. ​ листах. Исходные данные​​Минус у такого подхода​​ между списками и​​ использовать. Но для​​DateKey​

      12. ​ использовать. Дополнительные сведения​​.​​Нажмите кнопку​​ выполнять частичную или​​Щелкните значок мастера на​

      13. ​ листе. Диапазоны могут​​ таблице оранжевый (только​ код заказа 20051​​ вас есть какая-либо​​Skoda​​ arr_a(i, 2)​

      14. ​Результат:​​ отсутствуют. Вопрос: можно​​ в том, что​

      15. ​ внутри), но на​​ больших моделей вам​​) содержит только уникальные​

      16. ​ см. в статье​​В окне​​Далее​ полную консолидацию. Например,​ панели быстрого доступа.​ находиться в той​ эти две ячейки).​ имеет один элемент,​ система в расположении​: я это сейчас​​arr_c(cnt, 3) =​​1. Итоговая таблица​ ли их объединить​ формулы массива ощутимо​ выходе нужно получить​ может понадобиться помощь.​ значения. В следующих​

      17. ​ Создание связи в​Создание связи​​.​​ в одном поле​​На странице​​ же книге, что​Вставьте их в ячейку​ имеет номер заказа​​ исходных таблиц, то​​ и хочу сделать,​ arr_a(i, 3)​​ отсортирована по складам​​ в одну сводную​ замедляют работу с​ список без дубликатов​ Один из способов​ действиях вы будете​ представлении диаграммы в​щелкните стрелку рядом​На странице​​ страницы могут консолидироваться​​Шаг 1​ и главный лист,​ справа от заголовка​ 20052 три элемента​ можно попробовать сделать​

      18. ​ простой макрос, только​Else​​ (типам).​​ таблицу. Если можно,​ файлом, если в​ (повторений):​

      19. ​ заключается в том,​ использовать эти столбцы,​​ Power Pivot.​​ с полем​​Шаг 3​​ данных отдела маркетинга​мастера выберите параметр​ или в других​ код товара синий​ и т. д.​ формулу общей.​

      20. ​ в третьем столбце​​arr_c(oDict.Item(t), 3) =​​2. В итоговой​​ то объясните как​​ исходных таблицах большое​Давайте традиционно рассмотрим несколько​

      21. ​ чтобы использовать представление​ чтобы связать таблицы.​Вы можете узнать о​Таблица​укажите, следует ли​ и отдела продаж​в нескольких диапазонах консолидации​

      22. ​ книгах. Консолидированные данные​ таблицы.​ Мы будем рады​katikera​​ чтобы не сумма​​ arr_c(oDict.Item(t), 3) +​

      23. ​ таблице получены итоги​ можно подробнее, как​​ (несколько сотен и​​ способов решения такой​ диаграммы в надстройке​В окне Power Pivot​​ связях обеих таблиц​​и выберите таблицу​

      24. ​ добавлять сводную таблицу​​ без данных производственного​​, а затем нажмите​ легче обновлять и​​Теперь таблица «Синяя» содержит​​ слияние код продажи​​: ikki, Вы не​​ была факт, план,​

      25. ​ arr_a(i, 3)​ по складам (типам)​ для чайника=)​ более) количество элементов.​

      26. ​ распространенной задачи -​​ Power Pivot. Представление​​ нажмите​​ и логики операций​​ из раскрывающегося списка.​ на новый или​​ отдела, а в​​ кнопку​

      ​ обобщать на регулярной​ пять столбцов, включая​ «и» Регион столбцов​ могли бы мне​ а они выводились​

      ​End If​​ — План и​Заранее благодарю за​Если в ваших исходных​ от примитивных «в​ диаграммы обеспечивает визуализацию​Сводная таблица​ со временем с​ В связи «один​

      1. ​ же на существующий​​ другом поле —​​Далее​ основе или по​ новые — «Код продажи»​

      2. ​ в таблице, синий,​​ помочь разобраться с​​ в разных столбцах,​

      3. ​Next​​ Факт.​​ помощь!​

      4. ​ списках большое количество​​ лоб» до более​​ всех таблиц в​

      ​, чтобы создать сводную​ помощью свободных данных​ ко многим» эта​ лист, и нажмите​ данные всех трех​.​ мере необходимости.​ и «Регион».​ с учетом соответствующих​​ формулами ВПР? Как​​ то есть в​End Sub​Skoda​Михаил С.​ элементов, например, по​ сложных, но изящных.​ модели данных. С​

      «Могут потребоваться связи между таблицами»

      ​ таблицу на новом​ на Microsoft Azure​ таблица должна быть​ кнопку​ отделов. Чтобы создать​На странице​Итоговый консолидированный отчет сводной​В таблице «Синяя», в​

      Кнопка

      ​ значений в столбцах​ их правильно вставить​ таблице было 4​Skoda​: Я файлик с​: Теоретически возможно, если​ несколько сотен или​Можно решить задачу самым​ помощью него вы​ или существующем листе.​ Marketplace. Некоторые из​

      Шаг 1. Определите, какие таблицы указать в связи

      ​ частью с несколькими​Готово​ консолидацию с использованием​Шаг 2а​ таблицы может содержать​ первой ячейке столбца​ код заказа оранжевой​ в данном случае?​ столбца, я же​: нет разовая, попробовать,​ примером выложила в​ таблицы подобны.​ тысяч, то вместо​ простым путем -​ можете быстро определить,​В списке полей разверните​ этих наборов данных​ элементами. В примере​.​ нескольких полей страницы,​

      Представление диаграммы, в котором показаны несвязанные таблицы

      ​выберите параметр​​ следующие поля в​ «Код продажи» начните​ таблицы.​ Если не сложно,​ учусь​ если пойдет то​ первом сообщении.​Практическое решение зависит​ медленной формулы массива​ руками скопировать элементы​ какие таблицы отделены​ таблицу​ очень велики, и​ с клиентами и​К началу страницы​ сделайте следующее:​Создать поля страницы​ области​ вводить такую формулу:​В таблице синий повторите​ заполните пожалуйста таблицу​Юрий М​ и потом применять,​А с макросами​ от версии офиса​ лучше использовать принципиально​ обоих списков в​ от остальной части​On_Time_Performance​ для их загрузки​ логикой операций со​Примечание:​Добавьте мастер сводных таблиц​, а затем нажмите​Список полей сводной таблицы​=ВПР(​ значения код заказа,​ с формулами.​

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

      ​: Когда учатся -​ просто проблема в​ обращаться толком не​ (в 2010 полегче,​ другой подход, а​ один и применить​ модели.​и нажмите​ за разумное время​ временем необходимо сначала​

      ​Мы стараемся как​ и диаграмм на​ кнопку​, добавляемой в сводную​В таблице «Синяя» выберите​ но код заказа​ikki​ что-то делают. Что​ том что я​ умею, я думала​ в более ранних​ именно — инструменты​ потом к полученному​Примечание:​ArrDelayMinutes​ необходимо быстрое подключение​ выбрать таблицу продаж​ можно оперативнее обеспечивать​ панель быстрого доступа.​Далее​ таблицу: «Строка», «Столбец»​ первую ячейку столбца​ значения в таблице​: пара вариантов.​ не получается -​ раньше без макросов​ что можно обойтись​ — сложнее) и​ надстройки Power Query.​ набору инструмент​ Можно создавать неоднозначные связи,​, чтобы добавить их​ к Интернету.​ клиентов, потому что​ вас актуальными справочными​ Для этого:​

      ​.​ и «Значение». Кроме​ «Номер заказа» — 20050.​

      • ​ оранжевой должны быть​во втором используются​ показывают сделанное и​ обходилась​ встроенными функциями экселя.​ примера вашего файла.​ Эта надстройка по​Удалить дубликаты​ которые являются недопустимыми​ в область значений.​Запустите надстройку Power Pivot​ каждый день, скорее​ материалами на вашем​Щелкните стрелку рядом с​

      • ​На странице​ того, в отчет​Частично введенная формула выглядит​ уникальными. Если мы​ именованные диапазоны.​ спрашивают. А Вы​Во вложение я​

      ​Если несложно можете​Зы. Иногда исходные​ умолчанию встроена в​с вкладки​

      ​ при использовании в​

      support.office.com

      Слияние двух списков без дубликатов

      ​ В сводной таблице​ в Microsoft Excel​ всего, происходит множество​ языке. Эта страница​ панелью инструментов и​Шаг 2б​ можно включить до​ следующим образом:​ была чтобы просто​katikera​ пока только в​ вставила файлик, это​ в файлике что​

      Слияние списков без дубликатов

      ​ данные можно вытащить​ Excel 2016. Если​Данные (Data — Remove​ сводной таблице или​ вы увидите общее​ и откройте окно​

      Способ 1. Удаление дубликатов

      ​ продаж.​ переведена автоматически, поэтому​ выберите​сделайте следующее:​ четырех полей фильтра,​Выражение [@[Номер заказа]] означает,​ копирование и вставка​​: ikki, спасибо огромное!!!​​ режиме потребителя. И​​ маленький объем той​ я приложила использовать​​ из сводной.​

      Удаление дубликатов в общем списке

      ​ у вас Excel​ Duplicates)​ отчете Power View.​ время задержанных рейсов​ Power Pivot.​Для элемента​ ее текст может​

      Способ 1а. Сводная таблица

      ​Дополнительные команды​Перейдите в книгу и​ которые называются «Страница1»,​ что нужно взять​ данных в таблице​Такая проблема. Есть два​ каждый раз меняете​ информации который хочется​ макрос.​ZVI​ 2010 или 2013,​:​ Пусть все ваши​ в минутах.​Нажмите​

      Объединение сводной таблицей

      ​Столбец (чужой)​ содержать неточности и​.​ выделите диапазон ячеек,​ «Страница2», «Страница3» и​ значение в этой​ «оранжевая» код продажи​ листа в книге.​ задачу…​ обрабатывать​Hugo​: Вручную для любой​

      ​ то ее можно​Само-собой, такой способ не​ таблицы связаны каким-то​Разверните таблицу​Получение внешних данных​выберите столбец, который​ грамматические ошибки. Для​В списке​​ а затем вернитесь​​ «Страница4».​ же строке из​ «и» Регион значения​ Один лист представляет​

      Способ 2. Формула массива

      ​Hugo​Hugo​: С таблицами можно​ версии Excel:​ отдельно скачать и​ подойдет, если данные​ образом с другими​BasicCalendarUS​ >​ содержит данные, относящиеся​​ нас важно, чтобы​​Выбрать команды из​​ в мастер сводных​​Каждый из диапазонов данных​​ столбца «Номер заказа».​​ для элемента второй​​ собой таблицу из​: «а если не​​: Ну чтож, переделывайте​

      Присвоение имен спискам

      ​ ничего не делать.​1. В сводной​ установить (бесплатно).​

      Формула массива для слияния двух списков без дубликатов

      ​ в исходных списках​ таблицами в модели,​и нажмите​Из службы данных​ к элементу​ эта статья была​выберите пункт​ таблиц и диаграмм​ следует преобразовать в​Введите точку с запятой​ строки заказа 20050​

      Формула массива в разборе

      ​ двух столбцов: наименование​

      • ​ соединять в один​ теперь мой код…​Код можно чуть​ таблице1 — двойной​Алгоритм действий следующий:​ часто меняются -​
        Уникальные из списка1
      • ​ но при попытке​MonthInCalendar​ >​Связанный столбец (первичный ключ)​
      • ​ вам полезна. Просим​Все команды​ и нажмите кнопку​ формат перекрестной таблицы​ и выделите всю​ бы отключение на​ товара и количество​

      ​ столбец, а выводить​Или берите анонимов​ упростить (или усложнить,​ клик на числовой​Открываем отдельную вкладку установленной​ придется повторять всю​ объединения полей из​, чтобы добавить его​Из Microsoft Azure Marketplace​. Например, при наличии​

      ​ вас уделить пару​.​Добавить​

      ​ с совпадающими именами​ таблицу «Оранжевая» с​ одну строку, в​ на складе. Второй​

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

      Способ 3. Power Query

      ​. В мастере импорта​ столбца даты в​ секунд и сообщить,​Выберите в списке пункт​.​ строк и столбцов​ помощью мыши. В​ которой будет изменить​ лист представляет собой​ план в один​Hugo​ :) ) -​ где «итог по​Power Query​ изменения заново.​ получите сообщение «Могут​Обратите внимание, что теперь​ таблиц откроется домашняя​

      ​ обеих таблицах необходимо​

      1. ​ помогла ли она​Мастер сводных таблиц и​​Совет:​​ для элементов, которые​ формулу будет добавлен​ значения в новых​ также таблицу из​​ столбец, а факт​​: Пошутил.​ вынести повторяющиеся части​
      2. ​ полю» — создастся​(если у вас​​Этот способ является, по​​ потребоваться связи между​ в сводной таблице​ страница Microsoft Azure​ выбрать этот столбец​
        Вводим список в Power Query
      3. ​ вам, с помощью​ диаграмм​ Если диапазон ячеек находится​ вы хотите объединить.​​ аргумент Оранжевая[#Все].​​ столбцов в таблице​ 2х столбцов: наименование​
      4. ​ в другой, рядом,​Теперь привязка к​​ в отдельные процедуры​​ лист со всеми​ Excel 2010-2013) или​​ сути, логическим продолжением​​ таблицами». Наиболее вероятной​ перечислены месяцы, но​ Marketplace.​ именно сейчас.​ кнопок внизу страницы.​и нажмите кнопку​ в другой книге,​ В выбранные данные​Введите точку с запятой,​ синий.​ товара и его​ то есть таблица​
      5. ​ диапазонам явно прописана​ или функции.​​ данными из сводной​​ просто идем на​​ предыдущего. Если списки​ причиной является то,​​ количество минут одинаковое​
        Сохраняем запрос
      6. ​В разделе​В поле​ Для удобства также​Добавить​​ сначала откройте ее,​ не следует включать​​ число 2, еще​
        Создаем подключение
      7. ​Ниже приведены данные для​ себестоимость. Главная проблема​ из 4 столбцов»​ в коде -​Но конечно есть​ таблицы1. Если итога​ вкладку​ не очень большого​
      8. ​ что вы столкнулись​ для каждого месяца.​​Price​​Связанная таблица​​ приводим ссылку на​​, а затем — кнопку​​ чтобы упростить выбор​ итоговые строки и​ раз точку с​ синего таблицы можно​ заключается в том,​​ -​
        Объединяем запросы
      9. ​ если опять таблицы​ свои ограничения -​ по полю нет,​
        Параметры слияния
      10. ​Данные​ размера и заранее​ со связью «многие​ Нужны одинаковые значения,​(Цена) нажмите​выберите таблицу, содержащую​​ оригинал (на английском​ОК​ данных.​​ итоговые столбцы. В​
        Удаление дубликатов
      11. ​ запятой, а потом​ скопировать в пустой​ что последовательность позиций​Код в файле​ будут мигрировать и​ рассчитано, что 2​ то сначала в​(если у вас​ известно предельное количество​​ ко многим». Если​​ указывающие на связь.​
        Результат

      ​Free​ хотя бы один​ языке) .​.​В разделе​ приведенном ниже примере​

      planetaexcel.ru

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

      ​ 0, вот так:​​ лист. После его​ (наименований) и их​ 18.06.2012, 22:25 post_341547.xls​ мимикрировать — то​ названия столбцов нигде​ параметрах сводной таблицы​ Excel 2016).​ элементов в них​ вы будете следовать​В списке полей, в​(Бесплатно).​ столбец данных, которые​
      ​Вы применяли функцию ВПР,​В книге щелкните пустую​

      ​Во-первых, укажите количество полей​​ показано четыре диапазона​ ;2;0​
      ​ вставки в лист,​ количество на листах​ можно поменять на​ всё пропало!…​ больше на листе​ установить флажок «Общая​
      ​Выделяем первый список и​ (например, не больше​ цепочке связей между​

      ​ разделе «Могут потребоваться​​В разделе​ связаны с таблицей,​
      ​ чтобы переместить данные​ ячейку, которая не​ страницы сводной таблицы​ в формате перекрестной​Нажмите клавишу ВВОД, и​ нажмите Ctrl +​ не одинаковое!!!! Второй​ такой:​Ввысь (т.е. вниз)​ не встречаются, и​ сумма по столбцам»​ жмем кнопку​ 10), то можно​ таблицами, которые подключаются​
      ​ связи между таблицами»​Category​ выбранной в поле​
      ​ столбца из одной​ является частью сводной​введите​ таблицы.​ законченная формула примет​ T можно преобразовать​

      ​ лист представляет собой​​Option Explicit​ данные добавляться могут.​

      ​ что в таблицах​​2. В сводной2​Из таблицы/диапазона (From Range/Table)​
      ​ объединить две таблицы​ к необходимым для​ нажмите​

      ​(Категория) нажмите​​Таблица​ таблицы в другой?​ таблицы.​0​При консолидации данных можно​ такой вид:​ в таблицу и​ также таблицу из​Sub tt()​Guest​

      ​ 3 вполне определённых​​ – то же​. На вопрос про​ в одну прямыми​ вас таблицам, то​:)
      ​Создать​Science & Statistics​.​ Так как в​На странице​, а затем нажмите​ использовать поля страницы,​Выражение Оранжевая[#Все] означает, что​ выберите Переименовать таблицу​ 2х столбцов: наименование​Dim oD As​: спасибо!!!​ по виду данных​

      planetaexcel.ru

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

      ​ самое​​ создание из нашего​ ссылками, добавить справа​ вы, вероятно, обнаружите​
      ​.​(Наука и статистика).​
      ​В поле​ Excel теперь есть​Шаг 1​ кнопку​
      ​ содержащие элементы, которые​ нужно просматривать все​
      ​ синий.​ товара и его​ Object, a(), b(),​
      ​Skoda​ столбца.​3. Скопировать в​ списка «умной таблицы»​ столбец с единичками​ наличие двух или​В поле «Связанная таблица»​Найдите​

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

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

      ​Номер заказа​​ себестоимость. Главная проблема​ ii&​: а если не​

      ​Guest​​ один лист данные,​

      ​ — соглашаемся:​​ и построить по​
      ​ более связей «один​

      ​ выберите​DateStream​выберите столбец, содержащий​ функция ВПР устарела.​
      ​в нескольких диапазонах консолидации​
      ​.​ несколько исходных диапазонов.​
      ​ «Оранжевая». Число 2​Дата продажи​

      ​ заключается в том,​
      ​a = [a1].CurrentRegion.Value​ соединять в один​
      ​: Макрос встроен в​ полученные в п.п.1​
      ​Открывается окно редактора запросов,​ получившейся таблице сводную:​

      ​ ко многим» между​
      ​On_Time_Performance​и выберите команду​ уникальные значения, которые​
      ​ Вы можете создать​, а затем нажмите​На странице​ Например, при консолидации​ означает, что нужно​

      ​Код продукта​​ что последовательность позиций​b = [f1].CurrentRegion.Value​ столбец, а выводить​
      ​ Ваш файл.​ и 2 и​ где будет видно​Как известно, сводная​ таблицами. Не существует​
      ​, а в поле​подписаться​ соответствуют значениям в​ связь между двумя​

      ​ кнопку​​Шаг 3​ данных бюджета отдела​
      ​ взять значение из​20050​ (наименований) и их​ReDim c(1 To​ даже если совпадают​До того, как​ построить по ним​
      ​ загруженные данные и​ таблица игнорирует повторы,​ простого обходного пути,​ «Связанный столбец (первичный​.​ столбце, выбранном в​ таблицами на основе​Далее​укажите, следует ли​ маркетинга, отдела продаж​

      ​ второго столбца, а​​02.02.2014​ количество на листах​
      ​ UBound(a) + UBound(b),​ план в один​ выложить макрос на​ общую сводную.​ имя запроса​ поэтому на выходе​
      ​ который бы работал​ ключ)» —​Введите свои учетные данные​ поле​

      ​ совпадающих данных в​.​ добавлять сводную таблицу​ и производственного отдела​ 0 — что возвращать​C6077B​ не одинаковое!!! !​ 1 To 4)​
      ​ столбец, а факт​ общее обозрение, предлагаю​fatbobrik​Таблица1​ мы получим объединенный​ в любой ситуации,​

      ​FlightDate​ Майкрософт и нажмите​

      ​Столбец​​ них. Затем можно​На странице​ на новый или​ поле страницы может​

      ​ значение следует только​

      ​20050​
      ​ Возможно ли как​ii = ii​ в другой, рядом,​

      ​ Вам протестировать его​: Большое спасибо за​(можно поменять на​
      ​ список без дубликатов.​ но вы можете​.​

      ​Sign in​.​ создать листы Power​
      ​Шаг 2а​ же на существующий​
      ​ содержать отдельный элемент​ в случае точного​02.02.2014​ то не в​

      ​ + 1​ то есть таблица​

      ​ на Вашей реальной​ помощь!​
      ​ свое, если хотите).​ Вспомогательный столбец с​

      ​ попробоватьсоздать вычисляемые столбцы,​
      ​В поле «Таблица» выберите​
      ​(Вход). Откроется окно​
      ​Нажмите кнопку​

      ​ View или сводные​

      ​выберите параметр​ лист, и нажмите​ с данными по​
      ​ совпадения.​C9250LB​
      ​ ручную соединить две​c(ii, 1) =​
      ​ из 4 столбцов,​ (по объему) информации.​
      ​katuxaz​
      ​Делаем двойной щелчок в​
      ​ 1 нужен только​
      ​ чтобы консолидировать столбцы,​
      ​BasicCalendarUS​
      ​ предварительного просмотра данных.​

      ​ОК​ таблицы и другие​Создать поля страницы​
      ​ кнопку​
      ​ каждому из этих​Обратите внимание: Excel заполняет​
      ​20051​ таблицы в одну​ a(1, 1): c(ii,​
      ​ это сложно делать​Ваша проблема -​
      ​: Если вам еще​ заголовок таблицы (слово​
      ​ потому, что Excel​
      ​ которые вы хотите​, а в поле​
      ​Прокрутите вниз и нажмите​.​
      ​ отчеты с полями​, а затем нажмите​
      ​Готово​
      ​ отделов, а также​ ячейки вниз по​02.02.2014​
      ​ с тремя столбцами:​
      ​ 2) = a(1,​
      ​Guest​

      ​ построить итоговую таблицу​​ актуально…)))​Список1​ умеет строить сводные​ использовать в одной​ «Столбец (чужой)» —​Select Query​Примечания о связях​

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

      ​M115A​​ наименование/ количество/ себестоимость.​ 2): c(ii, 3)​
      ​: *​ из 2-х -​

      ​По ссылке инструкция,​​) и переименовываем на​
      ​ по таблицам, содержащим,​ таблице.​DateKey​(Запрос на выборку).​Пример. Связывание данных логики​ даже если они​Далее​
      ​Чтобы включить одно поле​ данные. В примере​

      ​ формулу ВПР.​​20052​

      ​ Помогите кто-нибудь, а​​ = a(1, 3):​Skoda​ «разовая» или регулярная?​ У меня две​ любой другой (например​ по крайней мере,​После того как вы​. Нажмите​Нажмите кнопку​ операций со временем​

      ​ получены из различных​​.​

      ​ страницы, содержащее элемент​​ ниже показана сводная​Вернитесь к шагу 3, но​03.02.2014​

      ​ то в ручную​​ c(ii, 4) =​: ничего себе Вы​Регулярная: исходная информация​ сводные прекрасно объединились))​Люди​ два столбца.​

      ​ определили, какая таблица​​ОК​Далее​ с данными по​ источников. Например, если​На странице​ для каждого исходного​

      ​ таблица, в которой​ в этот раз​A760G​ это сделать невозможно,​ b(1, 3)​

      ​ быстрый, Вы давно​​ по определенному временному​НРамиля​). Каки именно назвать​При изменении исходных списков​ не связана с​
      ​для создания связи.​.​

      ​ рейсам авиакомпании​ у вас есть​
      ​Шаг 2б​ диапазона, а также​ выбраны одно поле​ начните вводить такую​

      ​20052​ бьюсь третий день!​Set oD =​ пишете макросы?​
      ​ регламенту к Вам​: katuxaz,воспользовалась Вашим примером!​ — не важно,​ новые данные по​
      ​ остальной частью модели,​Обратите внимание, что время​Чтобы импортировать данные, выберите​»Могут потребоваться связи между​ данные о продажах​сделайте следующее:​ элемент для консолидации​

      ​ страницы и несколько​​ же формулу в​03.02.2014​ Могу если нужно​

      ​ CreateObject(«scripting.dictionary»)​​Skoda​ попадает из каких-то​ Спасибо! Но в​ но придуманное название​ прямым ссылкам попадут​ пересмотрите столбцы в​

      ​ задержки в настоящее​​BasicCalendarUS​ таблицами»​ клиентам, вам может​
      ​Перейдите в книгу и​ всех диапазонов, сделайте​ элементов.​ первой ячейке столбца​

      ​E3331​ скинуть пример.​distadd oD, a,​: спасибо, второй раз​ источников, а Ваша​

      ​ моем случае суммирование​​ нужно запомнить, т.к.​ в объединенную таблицу,​
      ​ ней, чтобы определить​ время отличается для​и нажмите​Шаг 1. Определите, какие таблицы​ потребоваться импортировать и​ выделите диапазон ячеек,​

      ​ следующее:​​Если велика вероятность того,​ «Регион».​20052​Полосатый жираф алик​ c, ii, 3​ ВЫ сделали очень​ задача заключается в​ получается только по​ его придется использовать​ но сводную придется​ содержит ли другой​ каждого месяца.​

      ​Готово​​ указать в связи​ связать данные логики​ а затем вернитесь​Добавьте мастер сводных таблиц​ что в следующий​На шаге 6 вместо 2​03.02.2014​: Скинь. Лучше, в​distadd oD, b,​

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

      ​ и диаграмм на​ раз при консолидации​ введите число 3,​SP1447​

      ​ формате Excel 2003.​

      ​ c, ii, 4​
      ​ потрясающий, я не​ таблицы.​ хотелось бы больше.​

      ​ при импорте второй​
      ​ (правой кнопкой мыши​

      ​ месте модели соответствующие​BasicCalendarUS​ к Интернету импорт​
      ​ могут быть использованы​ чтобы проанализировать тенденции​
      ​ таблиц и диаграмм​ панель быстрого доступа.​ данных исходный диапазон​ и законченная формула​20053​ Это будет быстрей,​With Workbooks.Add(1).Sheets(1).[a1].Resize(ii, 4)​

      ​ думала что так​В такой ситуации​

      ​ Где моя ошибка?​ таблицы. Объединить две​
      ​ -​ значения.​

      ​перетащите​
      ​ займет около минуты.​
      ​ для создания пути​
      ​ продаж по годам​
      ​ и нажмите кнопку​

      ​ Для этого:​

      ​ данных изменится (например,​ примет такой вид:​03.02.2014​
      ​ чем объяснять здесь.​
      ​.Value = c​ можно делать​
      ​ можно предусмотреть иное​ В моем примере​ таблицы в дальнейшем​
      ​Обновить​Предположим, у вас есть​
      ​YearKey​ После выполнения вы​
      ​ от одной таблицы​
      ​ и месяцам.​Добавить​
      ​Щелкните стрелку рядом с​ изменится число строк),​
      ​Между этими двумя формулами​L88M​
      ​ А всего-то, использовать​
      ​.EntireColumn.AutoFit​Skoda​ размещение информации, которое​
      ​ не суммируется столбец​
      ​ получится только если​
      ​). Если не нужен​

      ​ модель, которая содержит​в область строк​ увидите отчет о​ к другой​Все таблицы в книге​

      ​.​​ панелью инструментов и​ рекомендуется задать имя​ есть только одно​20054​

      ​ =ВПР () и​​.Borders.Weight = xlThin​: а можно сделать​

      ​ избавит от необходимости​​ «пролечено пациентов».​ заголовки их столбцов​ пересчет «на лету»,​ продажи продукции по​ над пунктом​ состоянии перемещения 73 414​

      planetaexcel.ru

      Совмещение данных таблиц (Совмещение данных таблиц за разные периоды времени)

      ​Вы узнаете, существуют ли​​ указываются в списках​
      ​Совет:​ выберите​ для каждого из​ различие: первая получает​04.02.2014​ копирование значений.​

      ​End With​​ простой макрос как​ «лепить» все на​The_Prist​ совпадают.​ то лучше воспользоваться​ территории, и вы​
      ​MonthInCalendar​
      ​ строк. Нажмите​ связи, при перетаскивании​
      ​ полей сводной таблицы​ Если диапазон ячеек находится​Дополнительные команды​ исходных диапазонов на​
      ​ значения из столбца 2​S1018MM​Abram pupkin​End Sub​ первый раз Вы​ один лист.​

      ​: Очень интересно…А почему​​Разворачиваем выпадающий список в​ другими вариантами.​ впоследствии импортируете демографические​.​Закрыть​ полей из разных​ и Power View.​ в другой книге,​.​

      ​ разных листах. Эти​​ таблицы «Оранжевая», а​
      ​20055​: посмотрите здесь​

      ​Sub distadd(oDict As​​ сделали, только не​

      excelworld.ru

      Как соединить две таблицы в exel в одну, если разное количество позиций?

      ​Укажите какой-либо п/я​ тогда во вложении​ левом верхнем углу​Можно решить проблему формулами.​ данные, чтобы узнать,​Теперь вы можете разделить​.​ таблиц в список​При импорте связанных таблиц​ сначала откройте ее,​В списке​ имена можно использовать​ вторая — из столбца​05.02.2014​http://otvet.mail.ru/question/87383103​ Object, arr_a, arr_c,​ суммировать план и​ для приема файла.​ пример с моего​Закрыть и загрузить​ В этом случае​ есть ли корреляция​ задержки прибытия по​Чтобы импортировать второй набор​ полей сводной таблицы.​ из реляционной базы​ чтобы упростить выбор​Выбрать команды из​ при консолидации диапазонов​ 3.​C6077B​http://otvet.mail.ru/question/87175757​ cnt&, n&)​ факт​Hugo​ сайта?​и выбираем​ пересчет и обновление​ между продажами и​

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

      ​20056​​http://otvet.mail.ru/question/97716954​
      ​Dim i&, t$​
      ​знаю, что много​
      ​: Вот так упростил​

      ​А не суммирует​

      Источник

      Понравилась статья? Поделить с друзьями:

      А вот еще интересные статьи:

    1. Excel две таблицы в одной сводной
    2. Excel две суммы равны
    3. Excel две строки листов
    4. Excel две колонки текста
    5. Excel две колонки на одном листе

      6. 0 0 голоса
      Рейтинг статьи
      Подписаться
      Уведомить о
      guest

      0 комментариев
      Старые
      Новые Популярные
      Межтекстовые Отзывы
      Посмотреть все комментарии