Слияние двух таблиц в excel

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

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

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

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

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

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

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

Читайте также:  Почему некорректно отображается страница

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Инструкция

    Устанавливаем себе надстройку ЁXCEL . Читаем справку.

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

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

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

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

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

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

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

    Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку "Данные" и нажмите кнопку "Обновить все":

    Читайте также:  Сайт передачи жди меня поиск людей бесплатно

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

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

    Видео-пример

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

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

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

    Файлы для скачивания:

    Файл Описание Размер файла: Скачивания
    Пример 21 Кб 1575

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

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

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

    Leave a Reply

    Ваш адрес email не будет опубликован. Обязательные поля помечены *

    You may use these HTML tags and attributes:

    <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>