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

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

Способ 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

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

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

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. Намного проще обрабатывать данные в…

Источник

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше

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

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

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

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

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

Значения «ИД заказа» повторяются в таблице «Синяя», но значения «ИД заказа» в таблице «Оранжевая» уникальны. Если просто скопировать и ввести данные из таблицы «Оранжевая», значения «ИД продаж» и «Регион» для второй строки заказа 20050 будут отключены на одну строку, что изменит значения в новых столбцах таблицы «Синяя».

Вот данные для таблицы «Синяя», которую можно скопировать на пустой лист. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу Excel синюю.

Номер заказа

Дата продажи

Код продукта

20050

02.02.2014

C6077B

20050

02.02.2014

C9250LB

20051

02.02.2014

M115A

20052

03.02.2014

A760G

20052

03.02.2014

E3331

20052

03.02.2014

SP1447

20053

03.02.2014

L88M

20054

04.02.2014

S1018MM

20055

05.02.2014

C6077B

20056

06.02.2014

E3331

20056

06.02.2014

D534X

Вот данные для таблицы «Оранжевая». Скопируйте его на тот же самый таблицу. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу в Оранжевая.

Номер заказа

Код продажи

Регион

20050

447

Запад

20051

398

Юг

20052

1006

Север

20053

447

Запад

20054

885

Восток

20055

398

Юг

20056

644

Восток

20057

1270

Восток

20058

885

Восток

Нам необходимо обеспечить правильное выравнивание значений «ИД продаж» и «Регион» для каждого заказа с каждым уникальным элементом строки заказа. Для этого впустим заголовки таблицы «ИД продажи» и «Регион» в ячейки справа от таблицы «Синяя», а затем с помощью формулЫ ВЗ ПРОСМОТР выберем правильные значения из столбцов «ИД продажи» и «Регион» таблицы «Оранжевая».

Вот как это сделать.

  1. Скопируйте заголовки «ИД продажи» и «Регион» в таблице «Оранжевая» (только эти две ячейки).

  2. В ячейку справа от заголовка «ИД товара» таблицы «Синяя».

    Теперь таблица «Синяя» содержит пять столбцов, включая новые — «Код продажи» и «Регион».

  3. В таблице «Синяя», в первой ячейке столбца «Код продажи» начните вводить такую формулу:

    =ВПР(

  4. В таблице «Синяя» выберите первую ячейку столбца «Номер заказа» — 20050.

    Частично заполненная формула выглядит так:Частично введенная формула ВПР

    Выражение [@[Номер заказа]] означает, что нужно взять значение в этой же строке из столбца «Номер заказа».

    Введите точку с запятой и выделите всю таблицу «Оранжевая» с помощью мыши. В формулу будет добавлен аргумент Оранжевая[#Все].

  5. Введите точку с запятой, число 2, еще раз точку с запятой, а потом 0, вот так: ;2;0

  6. Нажмите клавишу ВВОД, и законченная формула примет такой вид:

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

    Выражение Оранжевая[#Все] означает, что нужно просматривать все ячейки в таблице «Оранжевая». Число 2 означает, что нужно взять значение из второго столбца, а 0 — что возвращать значение следует только в случае точного совпадения.

    Обратите внимание: Excel заполняет ячейки вниз по этому столбцу, используя формулу ВПР.

  7. Вернитесь к шагу 3, но в этот раз начните вводить такую же формулу в первой ячейке столбца «Регион».

  8. На шаге 6 вместо 2 введите число 3, и законченная формула примет такой вид:

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

    Между этими двумя формулами есть только одно различие: первая получает значения из столбца 2 таблицы «Оранжевая», а вторая — из столбца 3.

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

  9. Выделите все ячейки значений в столбце «Код продажи» и нажмите клавиши CTRL+C, чтобы скопировать их.

  10. На вкладке Главная щелкните стрелку под кнопкой Вставить.

    Стрелка под кнопкой "Вставить", открывающая коллекцию параметров вставки

  11. В коллекции параметров вставки нажмите кнопку Значения.

    Кнопка "Значения" в коллекции параметров вставки

  12. Выделите все ячейки значений в столбце «Регион», скопируйте их и повторите шаги 10 и 11.

    Теперь формулы ВПР в двух столбцах заменены значениями.

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

  • Как добавить или удалить строку или столбец в таблице

  • Использование структурированных ссылок в формулах таблиц Excel

  • Использование функции ВПР (учебный курс)

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

Источник

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

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

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

Как объединить две таблицы 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 и таблицы.

    Источник

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

    ​Смотрите также​​ таблиц нет в​ Shift:=xlDown .Range(«A2»).Value =​ , True)​ ;) .Select Application.CutCopyMode​ ногой, потому не​ заголовка столбца важно​ затем скопировать (протянуть)​ предыдущего. Если списки​ требуется разместить консолидированные​ одновременно.​ Office 2011 г.​Здесь тоже можно настроить​ Указываем диапазоны всех​Выделите все ячейки значений​ давайте вставьте заголовки​ столбцов в таблице​Примечание:​ 2010. Пытаюсь в​ .Range(«A1»).Value ‘format([a1], «dd.mm.yyyy»)​If Not IsArray(arFiles)​ = False Selection.Delete​

    ​ вижу, как можно​ использовать то же​ вниз на нижестоящие​ не очень большого​ данные.​Выделите на каждом листе​ ​ много разных параметров.​ таблиц в строке​ в столбце «Код​ таблицы Код продажи​ синий.​Мы стараемся как​ диапазоне данных поставить​ .Range(«B2»).Value = .Range(«B1»).Value​ Then End ‘если​ Shift:=xlUp End Sub​ дополнить макрос таким​ имя (Люди), что​ ячейки с запасом.​ размера и заранее​

    ​Примечание:​ нужные данные. Не​Для консолидации по расположению​Эта сводная таблица​ «Диапазон…», из которых​ продажи» и нажмите​ «и» Регион в​Ниже приведены данные для​ можно оперативнее обеспечивать​ все таблицы по​

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

    ​ & » -​ не выбрано ни​Работать работает, но​ образом, чтоб он​ и в предыдущем​В английской версии Excel​ известно предельное количество​ Убедитесь, что справа и​ забудьте включить в​ диапазон данных на​ связана с исходными​ будем делать одну​ клавиши CTRL+C, чтобы​ ячеек справа от​ синего таблицы можно​ вас актуальными справочными​ очереди (выделяя их),​ пай» .Range(«C2»).Value =​ одного файла​ ест по четыреста​ не просто сливал​ запросе.​ эта формула выглядит​ элементов в них​ снизу достаточно свободных​

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

    ​ них ранее выбранные​ каждом из исходных​ таблицами. Если изменились​ сводную таблицу.​ скопировать их.​ синего таблицы и​ скопировать в пустой​ материалами на вашем​ пишет ошибку… сейчас​ .Range(«C1»).Value & «​Set wbTarget =​ мегабайт оперативки по​ книги в одну,​В окне Excel на​ как:​ (например, не больше​ ячеек для консолидированных​ данные из верхней​ листов должен иметь​

    ​ данные в таблицах​Выделяем первую таблицу​На вкладке​ формулы ВПР правильные​ лист. После его​ языке. Эта страница​ буду искать инфу​ — СЧА» .Range(«B2:C2»).Copy​ Workbooks.Add(template:=xlWorksheet)​ срабатыванию. И не​

    ​ но еще и​

    ​ вкладке​

    ​=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1),​

    ​ 10), то можно​

    ​ данных.​

    ​ строки или левого​

    ​ формат списка без​

    ​ исходных, то, чтобы​

    ​ вместе с шапкой.​

    ​Главная​

    ​ значения с помощью​

    ​ вставки в лист,​

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

    ​ по работе со​

    ​ .Range(«D2») .Range(«B2:C2»).Copy .Range(«F2»)​

    ​.ScreenUpdating = False​

    ​ отдает до перезагрузки​

    ​ сообразно переименовывал страницы.​

    ​Данные (Data)​

    ​ 0)), INDEX(Список2, MATCH(0,​

    ​ объединить две таблицы​

    ​На вкладке​

    ​ столбца.​

    ​ пустых строк и​

    ​ обновить сводную таблицу,​

    ​ Затем нажимаем кнопку​

    ​щелкните стрелку под​

    ​ код продажи и​

    ​ нажмите Ctrl +​

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

    ​ сводными таблицами​

    ​ End With Next​

    ​stbar = .DisplayStatusBar​

    ​ Эксель. Можно как-нибудь​

    ​ Скажем, первой импортированной​

    ​или на вкладке​

    ​ COUNTIF($E$1:E1, Список2), 0))),​ в одну прямыми​Данные​Путь к файлу вводится​ столбцов.​ нужно из контекстного​ «Добавить», переходим на​ кнопкой​ область столбцов в​ T можно преобразовать​

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

    ​Pelena​

    ​ End Sub​

    ​.DisplayStatusBar = True​

    ​ от этого избавиться?​

    ​ странице присвоить имя​

    ​Power Query​

    ​ «»)​

    ​ ссылками, добавить справа​

    ​в разделе​

    ​ в поле​

    ​Откройте каждый из исходных​

    ​ меню выбрать функцию​

    ​ следующий лист и​

    ​Вставить​

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

    ​ в таблицу и​

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

    ​: На самом деле​

    ​DrAlexAcula​

    ​For i =​

    ​ И как дополнить​

    ​ «1», второй -​

    ​выбираем​

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

    ​ столбец с единичками​

    ​Сервис​

    ​Все ссылки​

    ​ листов и убедитесь​

    ​ «Обновить».​

    ​ выделяем вторую таблицу​.​Вот как это сделать:​ выберите Переименовать таблицу​ нас важно, чтобы​ есть, просто надо​: О! Так гораздо​ 1 To UBound(arFiles)​ это дело до​ «2» и т.д.​Получить данные — Объединить​ в том, что​ и построить по​нажмите кнопку​.​ в том, что​Нажав правой мышкой,​

    ​ с шапкой. Нажимаем​

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

    2. ​ вынести кнопку на​ красивше! Спасибо! Правда,​.StatusBar = «Обработка​ «с текущего листа​

      ​ Сильно спасибну добрым​ запросы — Добавить​ формулы массива ощутимо​ получившейся таблице сводную:​

    3. ​Консолидация​После добавления данных из​ данные на них​ и, выбрав функцию​

      ​ кнопку «Добавить».​

    4. ​ нажмите кнопку​ и «Регион в​Номер заказа​

      ​ вам полезна. Просим​ Панель быстрого доступа.​Частично введенная формула ВПР

      ​ все равно переполнение​ файла » &​ и до конца​ знатокам :3​ (Get Data -​

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

    5. ​Так указываем диапазоны​Значения​ таблице оранжевый (только​Дата продажи​ вас уделить пару​ См. видео​

    6. ​ происходит итерации на​ i & «​ делай это»?…​

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

      ​2) Все эти​ Merge Queries -​ файлом, если в​ таблица игнорирует повторы,​Выберите в раскрывающемся списке​ и книг нажмите​На конечном листе щелкните​ всю информацию по​ всех таблиц, из​.​ эти две ячейки).​

      ​Код продукта​ секунд и сообщить,​Анна2015​ третьей. Вы не​

    7. ​ из » &​galina mur​ слитые книги, все​ Append)​ исходных таблицах большое​ поэтому на выходе​

    8. ​функцию​ кнопку​ верхнюю левую ячейку​ конкретному продукту. Она​

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

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

      ​ UBound(arFiles)​: сохраните лучше​ эти таблицы со​:​ (несколько сотен и​ мы получим объединенный​, которую требуется использовать​ОК​ области, в которой​ появится на новом​

    9. ​ сводную. Чтобы все​ в столбце «Регион»,​ справа от заголовка​02.02.2014​ вам, с помощью​

    10. ​ получилось!​​ никаких команд для​​Set wbSrc =​28.02.2013 986.92 98​​ всех страниц слить​​В появившемся диалоговом окне​

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

    11. ​ более) количество элементов.​ список без дубликатов.​​ для консолидации данных.​​.​

      Кнопка

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

      ​C6077B​ кнопок внизу страницы.​krosav4ig​

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

    • ​ очистки кэша поитерационного?​ Workbooks.Open(arFiles(i), ReadOnly:=True)​ 692 088.17 Активный​

    • ​ в одну грандиозную​ выбираем наши запросы​

    • ​Если в ваших исходных​ Вспомогательный столбец с​

    support.office.com

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

    ​Установите флажки в группе​​Примечание:​ данные.​В Excel есть способ​ список диапазонов, после​​ повторите шаги 10​ таблицы.​​20050​​ Для удобства также​:​
    ​Полагаю, беда в​For Each shSrc​31.01.2013 971.24 97​ таблицу, где слева​ из выпадающих списков:​
    ​ списках большое количество​
    ​ 1 нужен только​Использовать в качестве имен​ Любые названия, не совпадающие​
    ​Примечание:​ быстро и просто​ ввода последнего диапазона,​ и 11.​Теперь таблица «Синяя» содержит​02.02.2014​
    ​ приводим ссылку на​Анна2015​ том, что на​ In wbSrc.Worksheets​
    ​ 124 487.25 Активный​ будет столбец с​В итоге получим новый​
    ​ элементов, например, по​ потому, что Excel​​, указывающие, где в​ с названиями в​ Убедитесь, что справа и​
    ​ посчитать (сложить, вычесть,​ нажимаем кнопку «Добавить».​Теперь формулы ВПР в​ пять столбцов, включая​C9250LB​ оригинал (на английском​, обратите внимание на​ одном листе записей​If IsNull(shSrc.UsedRange.Text) Then​28.02.2013 19 095.81​
    ​ датами. Полагаю, ежедневными.​ запрос, где два​ несколько сотен или​ умеет строить сводные​ исходных диапазонах находятся​ других исходных областях,​ снизу достаточно свободных​ т.д.) данные из​Теперь выделяем из​ двух столбцах заменены​
    ​ новые — «Код продажи»​
    ​20051​ языке) .​ шаг 2б на​ под три тысячи,​ ‘лист не пустой​ 477 395 167.99​
    ​ Сверху — строка,​ списка будут соединены​ тысяч, то вместо​ по таблицам, содержащим,​ названия:​ могут привести к​
    ​ ячеек для консолидированных​ нескольких таблиц в​ списка диапазонов первый​ значениями.​ и «Регион».​02.02.2014​Можно объединить строк (объединение)​ скрине, для того,​
    ​ а на другом​Set shTarget =​ Кольчуга​ на двести пятьдесят​ друг под другом.​ медленной формулы массива​ по крайней мере,​подписи верхней строки​
    ​ появлению в консолидированных​ данных.​ одну. Подробнее, смотрите​ диапазон. Ставим галочку​Как добавить или удалить​В таблице «Синяя», в​M115A​ из одной таблицы​ чтобы сводная таблица​ — просто десять.​ wbTarget.Sheets.Add(after:=wbTarget.Sheets(wbTarget.Sheets.Count))​31.01.2013 19 119.29​
    ​ пунктов ползущая вправо​ Осталось удалить дубликаты​ лучше использовать принципиально​ два столбца.​,​ данных отдельных строк​На вкладке​ в статье «Суммирование​ у цифры «1»​
    ​ строку или столбец​ первой ячейке столбца​20052​ в другую путем​ автоматически цепляла новые​ От такого разброса,​shTarget.Name = Left(Replace(wbSrc.Name,​ 477 982 143.94​ и содержащая соответствующие​ кнопкой​ другой подход, а​При изменении исходных списков​значения левого столбца​ или столбцов.​
    ​Данные​ в Excel» здесь.​ — первое поле​ в таблице​ «Код продажи» начните​03.02.2014​ вставки данных в​ добавленные строки, при​ может быть, проблемы​ «.xls», «»​ Кольчуга​
    Сделать из нескольких таблиц Excel сводную.​ наименования (в каждой​Удалить строки — Удалить​ именно — инструменты​ новые данные по​
    ​либо оба флажка​Для консолидации по расположению​в группе​Можно сделать таблицу​ страницы сводной таблицы​Использование структурированных ссылок в​ вводить такую формулу:​
    ​A760G​ первой пустой ячейки​ добавлении диапазонов в​ с памятью?​& «.» &​и создайте сводную​ таблице их по​ дубликаты (Delete Rows​ надстройки Power Query.​​ прямым ссылкам попадут​ одновременно.​ диапазон данных на​Работа с данными​
    ​ Excel с фильтрами​ станет активным. Здесь​ формулах таблиц Excel​=ВПР(​​20052​ под целевой таблицы.​
    ​ сводную нужно именно​Max Zh​ shSrc.Name, 31)​ таблицу​ три штуки). Пока​ — Delete Duplicates)​ Эта надстройка по​ в объединенную таблицу,​Выделите на каждом листе​
    ​ каждом из исходных​нажмите кнопку​ из простого списка.​ пишем название параметра​Использование функции ВПР (учебный​В таблице «Синяя» выберите​03.02.2014​
    ​ Таблица приводит к​ вписывать адреса таблиц​: Вот накидал вариант,​shSrc.Cells.Copy shTarget.Range(«A1″​Евгений​ просто, пока я​:​ умолчанию встроена в​
    ​ но сводную придется​ нужные данные. Не​ листов должен иметь​Консолидация​ Читайте в статье​ выбранного диапазона. В​

    excel-office.ru

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

    ​ курс)​​ первую ячейку столбца​E3331​ увеличению размера, чтобы​ в формате​ не совсем по​End If​: Данные — консолидация​ сам могу. Но​Готовый запрос можно переименовать​ Excel 2016. Если​ обновить уже вручную​ забудьте включить в​ формат списка без​.​ «Создать таблицу Excel​ нашем примере, поставим​Чтобы​ «Номер заказа» — 20050.​20052​ включить новые строки.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Имя_таблицы[#Все]​

    ​ тексту задания, но​Next​только объединение ячеек​ вот дальше… Дальше​ справа на панели​ у вас Excel​ (правой кнопкой мыши​ них ранее выбранные​ пустых строк и​Выберите в раскрывающемся списке​ из списка».​ название таблицы «Магазин​объединить таблицы в Excel,​Частично введенная формула выглядит​03.02.2014​ Если строк в​, а не​ вроде работает​wbSrc.Close False ‘закрыть​ уберите и обзовите​ требуется, чтоб каждой​ параметров, дав ему​

    ​ 2010 или 2013,​ -​ данные из верхней​ столбцов.​функцию​Примечание:​ 1».​ расположенные на разных​ следующим образом:​SP1447​ обеих таблицах совпадают,​ выделять диапазон таблицы.​1. макрос выполняет​ без запроса на​ нормально поля исходных​ дате из первого​ вменяемое имя (это​ то ее можно​

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

    Консолидация по расположению

    ​, которую требуется использовать​Мы стараемся как​Затем выделяем из​ листахили в других​Выражение [@[Номер заказа]] означает,​20053​ можно объединить столбцы​

    1. ​ Если просто выделять​ сбор данных из​ сохранение​ таблиц​ столбца соответствовала своя​

    2. ​ будет имя таблицы-результата​ отдельно скачать и​). Если не нужен​ столбца. Затем нажмите​ листов и убедитесь​

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

    3. ​03.02.2014​​ из одной таблицы​​ диапазон, то новые​​ файлов в папке​​Next​​получается именно то,​​ циферка, стоящая напротив​

      Кнопка

    4. ​ по факту) и​​ установить (бесплатно).​​ пересчет «на лету»,​ кнопку​

    5. ​ в том, что​Выделите на каждом листе​

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

    6. ​L88M​ с другой —​ строки, добавленные в​ (путь к необходимой​​.ScreenUpdating = True​​ что нужно​

    Консолидация по категории

    ​ такой же даты​ все и можно​Алгоритм действий следующий:​ то лучше воспользоваться​Добавить​ данные на них​ нужные данные.​ материалами на вашем​ этом же первом​ нужно сделать​ же строке из​​20054​​ путем вставки их​​ таблицы не будут​​ папке задать в​.DisplayStatusBar = stbar​

    1. ​DrAlexAcula​ в соответствующей табличке.​

    2. ​ все выгружать на​Открываем отдельную вкладку установленной​ другими вариантами.​.​ расположены одинаково.​

      ​Путь к файлу вводится​​ языке. Эта страница​ окне поля пишем​сводные таблицы Excel​ столбца «Номер заказа».​

    3. ​04.02.2014​​ в первой пустой​​ попадать в сводную​​ коде -​​.StatusBar = False​​: Евгений,​​ В прикрепленном файле,​

      Кнопка

    4. ​ лист командой​​ надстройки​​Можно решить проблему формулами.​Путь к файлу вводится​

    5. ​На конечном листе щелкните​​ в поле​​ переведена автоматически, поэтому​ название диапазона. Мы​. Делается это с​​Введите точку с запятой​​S1018MM​​ ячейки справа от​​irinko​в строке s​

    6. ​If wbTarget.Sheets.Count =​благодарю покорно! Действительно,​ как можно видеть,​Закрыть и загрузить (Close&Load)​Power Query​ В этом случае​ в поле​

      ​ верхнюю левую ячейку​Все ссылки​​ ее текст может​​ напишем – «Магазин​

    7. ​ помощью специальной функции.​ и выделите всю​20055​ таблицы. В этом​​: Скажите, а в​​ = Dir(«C:test*.xls»)​

      ​ 1 Then ‘не​​ все сработало! Совсем​ данные на первой​:​(если у вас​ пересчет и обновление​Все ссылки​ области, в которой​

    Консолидация по расположению

    ​.​ содержать неточности и​ 2». Так подписываем​Сначала нужно поместить​ таблицу «Оранжевая» с​05.02.2014​ случае также таблицы​

    1. ​ Excel 2013 где​C:test заменить на​ добавлено ни одного​ ничего не соображаю,​ странице представлены в​

    2. ​В будущем, при любых​ Excel 2010-2013) или​ результатов будет происходить​.​ требуется разместить консолидированные​

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

    3. ​C6077B​​ приводит к увеличению​​ найти Мастер сводных​​ путь к папке​​ листа​​ видать ^^​​ биржевом, ежедневнорабочем формате,​

      Вкладка

    4. ​ изменениях или дополнениях​​ просто идем на​​ автоматически и мгновенно,​После добавления данных из​

    5. ​ данные.​ всех исходных листов​ нас важно, чтобы​​Здесь видно, что в​​ доступа кнопку функции​

      ​ формулу будет добавлен​20056​​ учетом новых столбцов.​​ таблиц? есть кнопочка​

    6. ​ с данными​MsgBox «В указанных​Осталось разобраться с​ а на второй​​ в исходных списках,​​ вкладку​

    Консолидация по категории

    ​ сразу после изменений​ всех исходных листов​Примечание:​ и книг нажмите​ эта статья была​ первом поле у​ «Мастер сводных таблиц​ аргумент Оранжевая[#Все].​06.02.2014​Объединение строк – довольно​ — Создать сводную​​2. данные добавляются​​ книгах нет непустых​​ макросом на слияние​​ — на первые​ достаточно будет лишь​

    1. ​Данные​ в исходных списках.​

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

      ​ и диаграмм».​​Введите точку с запятой,​E3331​ простой процесс, но​ таблицу. Но там​

    3. ​ в одну таблицу​​ листов, сохранять нечего!»​​ книг…​​ числа месяцев. Желательно​​ правой кнопкой мыши​​(если у вас​​ Для удобства и​

      Вкладка

    4. ​ кнопку​​ снизу достаточно свободных​​ОК​ вас уделить пару​

    5. ​ обоих диапазонов. При​​Внимание!​​ число 2, еще​20056​ Объединение столбцов может​​ нет возможности добавления​​ на одном листе​​wbTarget.Close False​​P.S. Галина, и​ на выходе иметь​

    6. ​ обновить таблицу результатов.​ Excel 2016).​ краткости давайте дадим​ОК​ ячеек для консолидированных​.​ секунд и сообщить,​ анализе данные будут​​Это не та​​ раз точку с​

      ​06.02.2014​ быть сложнее, если​​ нескольких таблиц, нескольких​​ (кучи листов в​

    7. ​End​ вам спасибо! Интересный​ что-то вроде представленного​Дано:​​Выделяем первый список и​​ нашим спискам имена​

      ​.​​ данных.​Для консолидации по категории​ помогла ли она​ браться из той​ кнопка, которая имеется​ запятой, а потом​D534X​

    support.office.com

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

    ​ не соответствуют строки​ диапазонов… или может​ книге не создается)​Else​ вариант, но, увы,​ на третьей страничке.​12 листов с​ жмем кнопку​Список1​Примечание:​На вкладке​ диапазон данных на​ вам, с помощью​

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

    ​ таблицы, которую мы​ на закладке «Вставка».​ 0, вот так:​Ниже приведены данные для​ одной таблицы со​ я просто не​

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

    ​3. на основании​.DisplayAlerts = False​ для 800 рядов​ Можно сделать и​ данными (текст и​Из таблицы/диапазона (From Range/Table)​и​​ Любые названия, не совпадающие​​Данные​​ каждом из исходных​ кнопок внизу страницы.​​ выберем в фильтре​

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

    ​Итак, нажимаем на​ ;2;0​ оранжевой таблицы. Скопируйте​ строками в другой​ могу найти..​ этой таблицы строится​wbTarget.Sheets(1).Delete​

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

    ​ динамики у меня​ таким образом, чтоб​ цифры), по одному​. На вопрос про​Список2​ с названиями в​в разделе​ листов должен иметь​ Для удобства также​ сводной таблицы. А​ панели быстрого доступа​Нажмите клавишу ВВОД, и​ его в том​ таблице. С помощью​TimSha​

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

    ​ сводная (после отработки​.DisplayAlerts = True​ нервов не хватило​ числа шли не​ листу на каждый​ создание из нашего​, используя​ других исходных областях,​Сервис​ формат списка без​ приводим ссылку на​ если в фильтре​

    ​ на функцию «Другие​ законченная формула примет​ же листе. После​ функции ВПР, можно​: Лучше — смотрите​ макроса обновить сводную​End If​ бы ^^​​ биржевыми пятидневнками, а​​ месяц года, с​ списка «умной таблицы»​Диспетчер имен​ могут привести к​

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

    ​нажмите кнопку​ пустых строк и​ оригинал (на английском​ укажем – «Все»,​ команды», выбираем команду​ такой вид:​ его вставки в​ избежать проблем выравнивания.​ видео — два​ таблицу)​​On Error Resume​​DrAlexAcula​​ просто сплошняком. Тогда​​ одинаковой шапкой на​​ — соглашаемся:​​на вкладке​​ появлению в консолидированных​Консолидация​​ столбцов. Кроме того,​

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

    ​ языке) .​ то информация соберется​ «Мастер сводных таблиц​

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

    ​Выражение Оранжевая[#Все] означает, что​ лист, нажмите клавиши​В приведенном ниже примере,​ поста выше, однако…​в макросах не​ Next ‘если указанный​: Код, который указан​ денежные значения должны​ каждом листе и​Открывается окно редактора запросов,​Формулы (Formulas — Name​

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

    ​ данных отдельных строк​

    • ​.​ категории должны быть​Если данные, которые требуется​ из всех таблиц.​ и диаграмм».​ нужно просматривать все​
      Уникальные из списка1
    • ​ Ctrl + T​ вы увидите двух​irinko​ силен, вполне возможно,​
    • ​ путь не существует​ в «Приемах», увы,​ быть пустыми в​ разным количеством строк.​ где будет видно​ Manager — Create)​ или столбцов.​

    ​Выберите в раскрывающемся списке​ названы одинаково. Например,​ проанализировать, представлены на​ Нажимаем «Далее».​Появился значок мастера сводных​ ячейки в таблице​ можно преобразовать в​ таблиц, которые ранее​: ОЙ, извините… посмотрела​ что где-то чего-то​

    ​ и его не​ более чем десять​ соответствующих местах (в​

    ​Задача:​ загруженные данные и​:​Классическая ситуация: у вас​

    ​функцию​ если один из​ нескольких листах или​Устанавливаем галочку в​ таблиц. На рисунке​ «Оранжевая». Число 2​ таблицу, а затем​ были имена других​

    Способ 3. Power Query

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

    ​ заранее извиняюсь​

    1. ​’обзор начнется с​ названием объединять отказывается​​Извиняюсь, если это​​ одну таблицу, чтобы​Таблица1​ формула будет выглядеть​ которые надо слить​​ для консолидации данных.​​Сред.​ их можно объединить​
    2. ​ в:», указываем -​ цветом.​​ взять значение из​​Номер заказа​ и «Оранжевая». В​ кнопка Сводная таблица​Анна2015​
      Вводим список в Power Query
    3. ​ последней использованной папки​ (в моем 2013-ом,​ все уже было​ данные все шли​​(можно поменять на​​ следующим образом:​ в один. Причем​
    4. ​Выделите на каждом листе​, а другой —​​ на одном листе​​ «новый лист». Лучше​Теперь делаем сводную таблицу​​ второго столбца, а​​Код продажи​ таблице синий каждая​ и Мастер сводных​: Добрый день, пишу​If Dir(strSaveDir, vbDirectory)​ во всяком разе).​ (я честно искал​ по порядку, сначала​ свое, если хотите).​На первый взгляд выглядит​ в исходных списках​ нужные данные и​
    5. ​Среднее​ с помощью команды​​ поместить сводную таблицу​​ из нескольких отдельных​​ 0 — что возвращать​Регион​​ строка представляет собой​
      Сохраняем запрос
    6. ​ таблиц выглядят одинаково,​ уже на второй​ = Empty Then​ А вот этот​​ и закономерно не​ вся первая таблица,​​Делаем двойной щелчок в​
      Создаем подключение
    7. ​ жутковато, но, на​ могут быть как​ нажмите кнопку​, консолидация не просуммирует​ «Консолидация». Например, если​ на новом листе,​ таблиц.​ значение следует только​
    8. ​20050​ элемент строки для​​ но имеют разные​​ форум, вопрос вроде​​ MkDir strSaveDir​​ (отсюда — сработал​​ нашел) и если​ потом вся вторая​ заголовок таблицы (слово​ самом деле, все​ уникальные элементы, так​​Добавить​
      Объединяем запросы
    9. ​ эти столбцы.​ есть отдельный лист​ чтобы не было​
      Параметры слияния
    10. ​Как создать таблицу в​ в случае точного​447​ заказа. Итак, код​ значения… Спасибо!​ не сложный для​​ChDir strSaveDir​ самым прекрасным образом.​ слишком сумбурно изложил​​ таблица и так​
      Удаление дубликатов
    11. ​Список1​ не так страшно.​ и совпадающие (и​.​Откройте каждый из исходных​ расходов для каждого​ случайных накладок, перекрестных​ Excel​ совпадения.​​Запад​​ заказа 20050 состоит​
      Результат

    ​irinko​ профи, но ответ​On Error GoTo​ Может кому пригодится​ проблему. Очень-очень надеюсь​ далее. Не нужно​

    planetaexcel.ru

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

    ​) и переименовываем на​
    ​ Давайте я разложу​ между списками и​Путь к файлу вводится​ листов.​ регионального представительства, с​ ссылок, т.д. Нажимаем​, смотрите в статье​Обратите внимание: Excel заполняет​

    ​20051​
    ​ из двух элементов,​: пыталась Мастером сводных​ так и не​ 0​ ^^​ на помощь :3​ ничего суммировать, просто​ любой другой (например​ эту формулу на​ внутри), но на​ в поле​На конечном листе щелкните​ помощью консолидации можно​

    ​ «Готово». Получилась такая​​ «Как сделать таблицу​
    ​ ячейки вниз по​

    ​398​​ код заказа 20051​ таблиц объединить мои​ дождалась, очень надеюсь​

    Объединение таблиц

    ​arFiles = .GetSaveAsFilename(«Результат»,​​Скрытый текстSub Объединение_файлов()​
    ​Скрытый текст Так,​ собрать всё в​Люди​ несколько строк, используя​ выходе нужно получить​Все ссылки​ верхнюю левую ячейку​ создать на базе​
    ​ таблица.​ в Excel».​ этому столбцу, используя​Юг​ имеет один элемент,​ таблицы, но не​ на Вашу помощь.​ «Excel Files (*.xls),​Const strStartDir =​ сделал себе вот​ одну таблицу. Консолидация​). Каки именно назвать​ сочетание клавиш Alt+Enter​ список без дубликатов​.​ области, в которой​ этих данных корпоративный​Если нужно сделать выборку​Нам нужно объединить​ формулу ВПР.​20052​ имеет номер заказа​ получается. в моем​
    ​Имеем: несколько листов​ *.xls», , «Сохранить​ «c:test» ‘папка, с​ такой кустарный макрос​ в данном случае​ — не важно,​ и отступы пробелами,​ (повторений):​После добавления данных из​ требуется разместить консолидированные​ лист расходов. Такой​ по наименованию товара,​ данные двух таблиц,​Вернитесь к шагу 3, но​1006​ 20052 три элемента​ примере складывать ничего​ (по месяцам) со​ объединенную книгу»​ которой начать обзор​ через «запись макроса»:​ не поможет.​ но придуманное название​ как мы делали,​Давайте традиционно рассмотрим несколько​ всех исходных листов​ данные.​ лист может содержать​ выбираем товар в​ отчетов по магазинам,​ в этот раз​Север​ и т. д.​ не нужно, текстовая​ столбцами: ФИО, кол-во​If VarType(arFiles) =​ файлов​ Sub Adjust() ‘​Mike noldman​ нужно запомнить, т.к.​ например тут:​ способов решения такой​ и книг нажмите​Примечание:​
    ​ итоговые и средние​ фильтре «Название строк».​ в одну общую​ начните вводить такую​20053​ Мы будем рады​ таблица работы с​ отработанных дней.​
    ​ vbBoolean Then ‘если​Const strSaveDir =​ ‘ Adjust Макрос​: http://www.excel-office.ru/diapasontabliziexcel/svodnayatbvexcel​ его придется использовать​Логика тут следующая:​ распространенной задачи -​ кнопку​ Убедитесь, что справа и​ данные по продажам,​Можно выбрать по​ таблицу. Для примера​ же формулу в​447​ слияние код продажи​ клиентами различных менеджеров.​ФИО в каждом​ не выбрано имя​ «c:testresult» ‘папка, в​ ‘ ‘ Сочетание​http://excelexpert.ru/kak-v-excel-ispolzovat-konsolidaciyu-dlya-obedineniya-dannyx-iz-raznyx-knig​ потом еще раз​Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает​ от примитивных «в​ОК​ снизу достаточно свободных​ текущим уровням запасов​ складу – фильтр​ возьмем две такие​ первой ячейке столбца​Запад​ «и» Регион столбцов​ Фамилии не повторяются.​
    ​ месяце разные, их​GoTo save_err​ которую будет предложено​ клавиш: Ctrl+m ‘​Полосатый жираф алик​ при импорте второй​ все уникальные элементы​ лоб» до более​.​ ячеек для консолидированных​ и наиболее популярным​ «Название столбца», выбрать​

    ​ таблицы Excel с​​ «Регион».​
    ​20054​ в таблице, синий,​
    ​ Подскажите, пожалуйста, что​ тысячи. В итоге​
    ​Else​ сохранить результат​ Range(«1:1,2:2» ;) .Select​
    ​: А просто скопировать,​ таблицы. Объединить две​ из первого списка.​
    ​ сложных, но изящных.​Для консолидации по категории​

    ​ данных.​​ продуктам в рамках​
    ​ по отдельному магазину​ отчетами по наличию​На шаге 6 вместо 2​885​
    ​ с учетом соответствующих​ можно сделать?​

    ​ надо все месяцы​​On Error GoTo​
    ​Dim wbTarget As​ Range(«A2» ;) .Activate​ как значения, руки​ таблицы в дальнейшем​
    ​ Как только они​Можно решить задачу самым​ диапазон данных на​
    ​На вкладке​ всей организации.​ или по всем​ продуктов в магазинах​ введите число 3,​Восток​ значений в столбцах​

    ​Тема закрыта. Причина:​​ объединить в одну​ save_err​ New Workbook, wbSrc​ Selection.Delete Shift:=xlUp Rows(«2:2″​ отвалятся?​ получится только если​ заканчиваются — начинает​ простым путем -​ каждом из исходных​Данные​Тип консолидации следует выбирать​ сразу – это​
    ​ на разных листах.​
    ​ и законченная формула​20055​ код заказа оранжевой​ нарушение п.п. 4,​
    ​ таблицу по ФИО,​wbTarget.SaveAs arFiles​ As Workbook, shSrc​ ;) .Select Selection.Insert​
    ​DrAlexAcula​ заголовки их столбцов​ выдавать ошибку #Н/Д:​ руками скопировать элементы​ листов должен иметь​в группе​
    ​ с учетом того,​ фильтр «Страница 1».​
    ​Первый шаг. Встаем​ примет такой вид:​398​ таблицы.​ 5q Правил форума​
    ​ где будут рядом​
    ​End If​ As Worksheet, shTarget​
    ​ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range(«A2″​: Господа, и дамы,​
    ​ совпадают.​Формула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным​ обоих списков в​ формат списка без​
    ​Работа с данными​ как выглядят объединяемые​Когда нажимаем на​ на лист с​
    ​Между этими двумя формулами​Юг​
    ​В таблице синий повторите​
    ​TimSha​
    ​ столбики с каждым​
    ​End​ As Worksheet, arFiles,​
    ​ ;) .Select ActiveCell.FormulaR1C1​собственно говоря, имеет​Разворачиваем выпадающий список в​ образом извлекает уникальные​ один и применить​
    ​ пустых строк и​нажмите кнопку​
    ​ листы. Если данные​ ячейку сводной таблицы,​
    ​ первой таблицей. Нажимаем​ есть только одно​
    ​20056​ значения код заказа,​
    ​:​ месяцем, суммировать их​;)​save_err:​ _​
    ​ = «=R[-1]C» Range(«B2″​;)
    ​ место быть четверть​
    ​ левом верхнем углу​
    ​ элементы из второго​ потом к полученному​ столбцов. Кроме того,​
    ​Консолидация​
    ​ на листах расположены​
    ​ появляется дополнительная закладка​
    ​ на кнопку «Мастер​
    ​ различие: первая получает​644​ но код заказа​irinko​
    ​ не надо. Пример​MsgBox «Книга не​i As Integer,​
    ​ ;) .Select ActiveCell.FormulaR1C1​
    ​ тысячи книг, содержащих​
    ​Закрыть и загрузить​
    ​ списка.​
    ​ набору инструмент​
    ​ категории должны быть​
    ​.​
    ​ единообразно (названия строк​ «Работа со сводными​ сводных таблиц и​ значения из столбца 2​Восток​
    ​ значения в таблице​, имхо, но вам​
    ​ прилагаю.​ сохранена!», vbCritical​ stbar As Boolean​
    ​ = «=R[-1]C&»» -​
    ​ в общем-то однотипные​и выбираем​
    ​Вложенные друг в друга​Удалить дубликаты​ названы одинаково. Например,​Выберите в раскрывающемся списке​;)
    ​ и столбцов могут​ таблицами». В ней​ диаграмм». В появившемся​
    ​ таблицы «Оранжевая», а​
    ​20057​
    ​ оранжевой должны быть​ целесообразнее создать свою​
    ​Заранее благодарю!​
    ​End With​
    ​On Error Resume​
    ​ пай»»» Range(«C2» ;)​
    ​ данные. Маленький пример​Закрыть и загрузить в…​
    ​ две функции ЕСЛИОШИБКА​
    ​с вкладки​
    ​ если один из​функцию​

    ​ при этом различаться),​​ два раздела. С​
    ​ диалоговом окне указываем​ вторая — из столбца​1270​ уникальными. Если мы​ тему. И приложить​krosav4ig​End Sub​ Next ‘если указанный​ .Select ActiveCell.FormulaR1C1 =​ в прикрепленном файле​ (Close&Load to…)​ реализуют вывод сначала​Данные (Data — Remove​ столбцов называется​, которую требуется использовать​ воспользуйтесь консолидацией по​ их помощью можно​

    ​ «в нескольких диапазонах​​ 3.​Восток​ была чтобы просто​ файлы в​: оформляете свои столбцы​тэги: слияние книг,​ путь не существует,​ «=R[-1]C[-1]&»» — СЧА»»»​
    ​ (кусочки двух книг).​:​ уникальных из списка-1,​ Duplicates)​Сред.​ для консолидации данных.​ расположению. Если же​ изменять все подписи​ консолидации». Указываем –​

    ​Теперь все ячейки новых​​20058​ копирование и вставка​реальной структуре и форматах​ как таблицы, даете​
    ​ сливание, объединение, VBA​ обзор начнется с​ Range(«B2:C2» ;) .Select​ Нужно сделать следующее:​В следующем диалоговом окне​ а потом из​
    ​:​, а другой —​
    ​Установите флажки в группе​ на листах для​ фильтров, параметры таблицы.​
    ​ «сводная таблица».​ столбцов в таблице​885​ данных в таблице​с толковым объяснением​
    ​ им названия по​Igor67​ пути по умолчанию​ Selection.Copy Range(«D2» ;)​1) Слить все​
    ​ (оно может выглядеть​ списка-2 друг за​Само-собой, такой способ не​Среднее​Использовать в качестве имен​

    planetaexcel.ru

    Объединение таблиц (Формулы/Formulas)

    ​ соответствующих категорий используются​​Например, нажав на кнопку​Нажимаем «Далее».​ «Синяя» заполнены значениями.​Восток​ «оранжевая» код продажи​ что откуда зачем​ месяцам, далее мастером​: Так?​
    ​ChDir strStartDir​ .Select ActiveSheet.Paste Range(«F2″​ эти книги в​ немного по-другому -​
    ​ другом.​ подойдет, если данные​, консолидация не просуммирует​, указывающие, где в​ одинаковые названия строк​ «Заголовки полей», можно​На втором шаге​ В них содержатся​Нужно ли обеспечить правильность​ «и» Регион значения​ и куда копировать/переносить/объединять.​
    ​ сводных таблиц строите​

    ​Sub Adjust() Dim​​On Error GoTo​ ;) .Select ActiveSheet.Paste​ одну (вот этим​ не пугайтесь) выбираем​Обратите внимание, что это​ в исходных списках​

    ​ эти столбцы.​​ исходных диапазонах находятся​ и столбцов (данные​ написать свое название​ указываем «Создать поля​ формулы ВПР, но​ код продажи «и»​ для элемента второй​ К примеру, во​ сводную​ sh As Worksheet​ 0​ Range(«A2:G2» ;) .Select​ прелестным макросом: ,​%)

    ​Только создать подключение (Only​​ формула массива, т.е.​ часто меняются -​Откройте каждый из исходных​ названия:​ при этом могут​

    ​ (например – «Товар»).​​ страницы» (это поля​ отображаются значения. Возможно,​hands

    ​ Регион значения для​​ строки заказа 20050​​ втором поле набор​​Анна2015​ ‘ Сочетание клавиш:​With Application ‘меньше​ Range(«G2» ;) .Activate​ как я понимаю).​ create connection)​ после набора ее​ придется повторять всю​ листов.​подписи верхней строки​​ быть расположены по-разному),​​Если нажимаем на​ фильтров, которые будут​ вы захотите заменить​ каждого заказа выравнивания​ бы отключение на​ чисел/цифр — это​: Спасибо большое за​

    ​ Ctrl+m ‘ For​​ писанины​ Application.CutCopyMode = False​ Но тут тоже​:​ нужно ввести в​ процедуру после каждого​На конечном листе щелкните​,​ используйте консолидацию по​ таблицу, справа появляется​ расположены над таблицей).​

    ​ формулы ВПР в​​ с каждым элементом​ одну строку, в​ шифровки шпионов, потом​;)

    ​ ответ! Сейчас ковыряюсь,​​ Each sh In​arFiles = .GetOpenFilename(«Excel​ Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues,​ маленькая проблема: в​Повторяем всю процедуру (пункты​ ячейку не обычным​ изменения заново.​ верхнюю левую ячейку​

    ​значения левого столбца​​ категории.​ окно «Список полей​ Нажимаем кнопку «Далее».​ этих ячейках фактическими​ строки уникальных заказа.​ которой будет изменить​ как эти заморочки​ но пока не​ ActiveWorkbook.Worksheets With sh​ Files (*.xls), *.xls»,​ Operation:=xlNone, SkipBlanks _​
    ​ VBA я вообще​ 2-6) для второго​ Enter, а сочетанием​

    ​Этот способ является, по​​ области, в которой​​либо оба флажка​​Более новые версии​ сводной таблицы».​Последний, третий шаг.​ значениями.​​ Чтобы сделать это,​​ значения в новых​ будете расшифровывать?..​ получается, мастера сводных​ .Rows(«1:2»).Delete Shift:=xlUp .Rows(«2:2»).Insert​ , «Объединить файлы»,​ :=False, Transpose:=False Rows(«1:1″​ ни в зуб​ списка. При переименовании​ клавиш Ctrl+Shift+Enter и​;)

    excelworld.ru

    ​ сути, логическим продолжением​

    Источник

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

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

  1. Excel снять фильтр горячая клавиша
  2. Excel снять пароль с vba макроса в excel
  3. Excel снять защиту с одной ячейки
  4. Excel снять защиту с листа excel не зная пароль онлайн
  5. Excel снять защиту листа через архив

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

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