Как сделать слияние таблиц в Excel?

Вы работали со сводными таблицами Excel? Если нет – нужно срочно начинать. Это супер-инструмент, который позволяет быстро и просто обрабатывать большие объемы данных.

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

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

Как построить сводную таблицу на основе данных нескольких источников? Есть несколько вариантов.

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

Как на основе таблиц с одинаковой шапкой построить единую базу? Добавив таблицы одну под другую

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

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

Одна таблица дополняет другую с помощью объединения по общему столбцу

В «обычном» Excel это делается с помощью формулы ВПР или других формул с похожим функционалом. Power Query предлагает нам больше способов объединения, чем ВПР. Используя этот инструмент, мы можем не «просто» связать таблицы, но и сделать это по определенным условиям.

Power Query предлагает на выбор шесть различных способов объединения:

Разберемся, какие именно способы объединения таблиц есть в Power Query.

• Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
• Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
• Полное внешнее (все строки из обеих таблиц)
• Внутреннее (только совпадающие строки)
• Анти-соединение слева (только строки в первой таблице)
• Анти-соединение справа (только строки во второй таблице)

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

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

По задумке круги – это схематичное изображение множеств или таблиц, которые мы хотим объединить (вы что, никогда не видели круглых таблиц? )) Для Power Query иллюстрации по объединению данных (множеств) выглядят точно так же:

Внешнее соединение слева
Left Outer Join

Внешнее соединение справа
Right Outer Join

Полное внешнее
Full Outer Join

Внутреннее соединение
Inner Join

Анти-соединение слева
Left Anti Join

Анти-соединение справа
Right Anti Join

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

Перекрестное объединение
Cross Join

Полное внешнее
без совпадающих строк

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

Когда вы выбираете «Внешнее соединение слева», к данным из первой таблицы добавляются все значения из второй таблицы, соответствующие столбцу поиска. Если во второй таблице нет искомых значений, вы получите null (null – это значит пусто).

Пример: объединим план продаж в штуках с плановыми ценами.

Добавим таблицы в Power Query: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона, или для новых версий Excel: вкладка Данные → Из таблицы / диапазона. Объединяем запросы: вкладка Главная → Объединить запросы (Объединить запросы в новый), выбираем столбец слияния «снаряд» и тип объединения «Внешнее соединение слева».

На некоторые товары плановые цены еще не установлены, поэтому после объединения в столбце «цена» для этих товаров будет проставлен null.

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

Обратите внимание, в нижней части окна появилось информационное сообщение: «Выбор согласовал 6 из первых строк (9)». Соответственно, в таблице с результатами цены заполнены только в шести строках, а в 3-х стоит null.

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

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

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

Смотрим, что получилось в результате объединения: из таблицы «исчезли» данные по городам, в которых нет потенциальных покупателей – Алматы и Санкт-Петербург. Зато появился город, в котором есть возможные покупатели – Астана. По этому городу потребуется проставить сумму ожидаемых средних продаж, чтобы выполнить дальнейшие расчеты.

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

Что происходит, когда вы выбираете «Полное внешнее соединение»: с помощью заданного общего столбца будут объединены все строки из первой и второй таблиц.

Пример: в первой таблице приведен объем производства по дням, во второй – объем брака.

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

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

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

Пример: есть два списка покупателей, принявших участие в акциях А и Б – по одному для каждой из акций. С помощью Внутреннего соединения получим список покупателей, участвовавших и в той, и в другой акции.

Поиск выполняем по общему столбцу «Покупатель». Результат — список покупателей, участвовавших в обеих акциях:

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

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

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

В результате получили список покупателей, которые не сделали покупки.

Анти-соединение справа работает так же, как и Анти-соединение слева. Разница здесь только в порядке расположения таблиц.

Пример: в первой таблице – отчет о выполненных задачах, во второй – список поставленных задач. Объединив таблицы с помощью Анти-соединения справа, найдем невыполненные задачи.

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

Кроме объединения таблиц с помощью запросов Power Query, такие операции можно выполнять и с помощью DAX-формул, применяя их в различных сочетаниях: GENERATEALL, NATURALLEFTOUTERJOIN, NATURALINNERJOIN, CROSSJOIN, FILTER+CROSSJOIN, GENERATE, EXCEPT и др.

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

Источник: https://finalytics.pro/inform/pq-join/

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

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

Здесь рассмотрен порядок объединения соседних ячеек, а также разделения ранее объединенных ячеек на несколько.

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

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

Выделить ячейки, которые нужно объединить.

В меню Excel перейти на вкладку «Главная» и в разделе «Выравнивание» щелкнуть левой кнопкой мышки по пункту «Объединить и поместить в центре».

Если монитор компьютера имеет невысокое разрешение, название этого пункта в меню «Excel» может не отображаться. В таком случае щелкать нужно по кнопке с обозначением «+а+» (см. изображение).

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

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

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

Процесс разъединение ячеек полностью аналогичен процессу их соединения:

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

Источник: https://www.chaynikam.info/obyedinenie-yacheek-excel.html

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

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

Имеется 3 таблицы (каждая — в отдельном файле) от трех разных менеджеров. Таблицы содержат данные о продажах товаров в разрезе артикулов и месяцев.

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

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

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

Команда на ленте

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

Окно «Консолидация»

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

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

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

Список диапазонов. Это общий список всех диапазонов/таблиц, которые Вы собираете консолидировать.

Удалить. Эта кнопка используется для удаления диапазона из общего списка диапазонов консолидации.

Использовать в качестве имен. Самая важная настройка.

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

Это называется консолидация по позиции. Если используете такой вариант — при указании диапазонов не указывайте заголовки строк и столбцов.

Если поставлена одна или обе галочки, то Excel будет ориентироваться на название строки/столбца.

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

Это называется консолидация по категории. При использовании этого варианта — указывайте вместе с диапазоном заголовки строк и столбцов.

Для работы инструмента консолидируемые заголовки должны совпадать. Обращайте внимание на наличие лишних пробелов в названиях строк/столбцов. Регистр при этом не важен (Январь и ЯНВАРЬ — одинаковые названия).

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

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

Для нашего примера настройки объединения будут выглядеть вот так:=

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

Итоговый результат

Когда мы выбираем вариант создания обновляемой итоговой таблицы, Excel автоматически создает структуру, сворачивая «под плюсик» строки с указанием, из какой именно таблицы взялась итоговая сумма.

Таблица в «развернутом» виде

Это происходит для удобства работы с формулами, которые добавляются при создании обновляемой таблицы. Ячейки, спрятанные под «плюсик» имеют формулы вида =[Петров.xlsx]

Лист1!$C$2, а итоговые ячейки подержат простые формулы суммы: =СУММ(D3:D5).

Необновляемая итоговая таблица

Весь процесс создания итоговой таблицы занял не более пары минут. Согласитесь, это довольно быстро.

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

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

Выберите в общем списке нужный диапазон. Он появится в строке «Ссылка». Теперь можно быстро его поправить, добавить в общий список, а старый удалить оттуда. Еще вариант — заранее выделить больший диапазон, захватив пустые строки и столбцы.

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

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

Источник: https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/5aa232b8fd96b174366402fc

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

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

Например, финансовой службе компании «Альфа» из исходного прайс-листа готовой продукции (см. рисунок 1. Прайс-лист готовой продукции) нужно выделить несколько позиций по их кодам и предоставить данные руководству в виде текста (см. рисунок 2. Поиск позиции по коду). Значит, условием поиска и объединения будет код каждой позиции.

На новом листе этой книги Excel необходимо создать новую таблицу или добавить столбцы к таблице на текущем листе (как это показано на рисунке 4).

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

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

Дополнительные данные для объединения ячеек

Функция ЕСЛИ() позволит объединить информацию из исходной таблицы в ячейку B4 только в том случае, если код, указанный в ячейке А4, соответствует наименованию готовой продукции в ячейке D4. Ее синтаксис ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь).

Первый аргумент этой функции проверяет совпадение кодов из двух ячеек A4 и D4 и представляет формулу СОВПАД(), а точнее, выражение СОВПАД(ПРАВСИМВ(D4;5);A4), где:

Второй аргумент функции ЕСЛИ() – выражение СЦЕПИТЬ(ПСТР(D4;1;ДЛСТР(D4)-6);» — «;F4&»руб./шт») (подробнее о синтаксисе функции см. выше), где:

Источник: https://www.fd.ru/recommend/2366-kak-s-pomoshchyu-excel-obedinit-dannye-i-razmestit-v-odnoy-yacheyke-otchetnoy-tablitsy

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

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

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

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

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

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

Таким образом, номер заказа 20050 состоит из двух элементов, код заказа 20051 — один товар, код заказа 20052 состоит из трех элементов и т. д.

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

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

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

Источник: https://toto-school.ru/excel-2/kak-obedinit-tablicy-v-excel-obedinenie-dvux-ili-neskolkix-tablic.html

Слияние двух таблиц Excel. На основе совпадающих данных в столбцах

Ответ Тейлин работал отлично для меня, но мне пришлось немного изменить его, чтобы получить правильные результаты. Я хочу предоставить расширенное объяснение тому, кому это понадобится.

Моя настройка была следующей:

• Лист1: полные данные 2014 года
• Sheet2: обновленные строки для 2015 года в формате A1: D50,
  отсортировано по первому столбцу.
• Лист3: объединенные строки
• У моих данных нет строки заголовка

Я положил следующую формулу в ячейку A1 листа 3:

=iferror(vlookup(Sheet1!A$1;Sheet2!$A$1:$D$50;column(A1);false);Sheet1!A1)

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

Примечания:

• В моей версии формулы «;» используется как разделитель параметров вместо «,» . Это потому, что я нахожусь в Европе, и мы используем «,» как десятичный разделитель. Изменить «;» вернуться к «,» если вы живете в стране, где «.» является десятичным разделителем.
• A $1: означает, что всегда нужно брать столбец 1 при копировании формулы в ячейку в другом столбце. $A $1 означает: всегда принимать точную ячейку A1 даже при копировании формулы в другую строку или столбец.

После вставки формулы в A1 я расширил диапазон до столбцов B, C и т.д. до тех пор, пока не будет достигнута полная ширина моей таблицы. Из-за используемых $-значений это дает следующую формулу в ячейках B1, C1 и т.д.:

=IFERROR(VLOOKUP(‘Sheet1′!$A1;’Sheet2′!$A$1:$D$50;COLUMN(B1);FALSE);’Sheet1’!B1)
=IFERROR(VLOOKUP(‘Sheet1′!$A1;’Sheet2′!$A$1:$D$50;COLUMN(C1);FALSE);’Sheet1’!C1)

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

Затем выберите прямоугольник в Листе 3, начиная с A1 и имеющего размер данных в Sheet1 (такое же количество строк и столбцов). Нажмите Ctrl-D, чтобы скопировать формулы первой строки во все выбранные ячейки.

Клетки A2, A3 и т.д. получат следующие формулы:

=IFERROR(VLOOKUP(‘Sheet1′!$A2;’Sheet2′!$A$1:$D$50;COLUMN(A2);FALSE);’Sheet1’!A2)
=IFERROR(VLOOKUP(‘Sheet1′!$A3;’Sheet2′!$A$1:$D$50;COLUMN(A3);FALSE);’Sheet1’!A3)

Источник: http://qaru.site/questions/355230/merge-two-excel-tables-based-on-matching-data-in-columns

Слияние двух таблиц Excel. На основе совпадающих данных в столбцах

Поместите таблицу во второе изображение на Sheet2, столбцы D на F.

В Sheet1 ячейка D2 использует формулу

=iferror(vlookup($A2,Sheet2!$D$1:$F$100,column(A1),false),»»)

копировать взад и вперед.

Изменить : вот фотография. Данные находятся в двух листах. На Sheet1 введите формулу в ячейку D2. Затем скопируйте формулу в F2, а затем опустите столько строк, сколько вам нужно.

Ответ Тейлина отлично подействовал на меня, но мне пришлось немного изменить его, чтобы получить правильные результаты. Я хочу предоставить расширенное объяснение тому, кому это понадобится.

Моя настройка была следующей:

• Sheet1: полные данные 2014 года
• Sheet2: обновленные строки для 2015 года в формате A1: D50, отсортированные по первому столбцу
• Sheet3: объединенные строки
• У моих данных нет строки заголовка

Я поместил следующую формулу в ячейку A1 листа 3:

=iferror(vlookup(Sheet1!A$1;Sheet2!$A$1:$D$50;column(A1);false);Sheet1!A1)

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

Заметки:

• В моей версии формулы «;» используется как разделитель параметров вместо «,». Это потому, что я нахожусь в Европе, и мы используем «,» как десятичный разделитель. Изменить «;» вернуться к «,» если вы живете в стране, где «.» является десятичным разделителем.
• A $ 1: означает, что при копировании формулы в ячейку в другом столбце всегда требуется столбец 1. $ A $ 1 означает: всегда принимать точную ячейку A1, даже при копировании формулы в другую строку или столбец.

После вставки формулы в A1 я расширил диапазон до столбцов B, C и т. Д. До тех пор, пока не будет достигнута полная ширина моей таблицы. Из-за используемых $ -значений это дает следующую формулу в ячейках B1, C1 и т. Д .:

=IFERROR(VLOOKUP(‘Sheet1′!$A1;’Sheet2′!$A$1:$D$50;COLUMN(B1);FALSE);’Sheet1’!B1) =IFERROR(VLOOKUP(‘Sheet1′!$A1;’Sheet2′!$A$1:$D$50;COLUMN(C1);FALSE);’Sheet1’!C1)

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

Затем выберите прямоугольник в Листе 3, начиная с A1 и имеющего размер данных в Sheet1 (такое же количество строк и столбцов). Нажмите Ctrl-D, чтобы скопировать формулы первой строки во все выбранные ячейки.

Клетки A2, A3 и т. Д. Получат следующие формулы:

=IFERROR(VLOOKUP(‘Sheet1′!$A2;’Sheet2′!$A$1:$D$50;COLUMN(A2);FALSE);’Sheet1’!A2) =IFERROR(VLOOKUP(‘Sheet1′!$A3;’Sheet2′!$A$1:$D$50;COLUMN(A3);FALSE);’Sheet1’!A3) Из-за использования $ -signs область поиска является постоянной, но входные данные используются из текущей строки.

Источник: https://excel.bilee.com/excel-x43e-2.html