5 способов объединить листы Google, добавить столбцы со связанными данными и вставить несоответствующие строки

Знаете ли вы, что при объединении двух таблиц Google вы можете не только обновлять записи в одном столбце, но и извлекать целые связанные столбцы и даже несовпадающие строки? Сегодня я покажу вам, как это делается с помощью функций VLOOKUP, INDEX/MATCH, QUERY и надстройки Merge Sheets.

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

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

На этот раз он стал больше: в нем есть два дополнительных столбца с именами поставщиков и их рейтингами. Я обновлю столбец Stock этой информацией в другой таблице, а также выберу поставщиков. Ну и рейтинги тоже 🙂

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

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

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

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

Кончик. Если вы ищете быстрое решение для экономии вашего времени, иди знакомься с Merge Sheets немедленно.

Давайте кратко повторим синтаксис формулы:

=ВПР(ключ_поиска, диапазон, индекс, [is_sorted])

search_key это то, что вы ищете.
диапазон там, где вы ищете.
индекс — номер столбца, из которого нужно вернуть значение.
[is_sorted] является совершенно необязательным и указывает, отсортирован ли ключевой столбец.

Кончик. В нашем блоге есть целый учебник, посвященный Google Sheets VLOOKUP, не стесняйтесь взглянуть.

Когда я объединил две таблицы Google и просто обновил данные в столбце «Акции», я использовал эту формулу ВПР:

=Формуламассива(ЕСЛИОШИБКА(ВПР($B$2:$B$10,Лист1!$B$2:$D$10,2,ЛОЖЬ),»»))

IFERROR позаботился об отсутствии ошибок в ячейках без совпадений, а ARRAYFORMULA обработал сразу весь столбец.

Итак, какие изменения мне нужно внести, чтобы поставщики также отображались в качестве нового столбца из таблицы поиска?

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

Проще всего было бы просто скопировать формулу в соседний столбец и увеличить ее индекс на один (заменить 2 с 3):

=Формуламассива(ЕСЛИОШИБКА(ВПР($B$2:$B$10,Лист1!$B$2:$D$10,3,ЛОЖЬ),»»))

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

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

Когда вы создаете массив в Google Sheets, вы перечисляете значения или ссылки на ячейки/диапазоны в скобках, например ={1, 2, 3} или же ={1; 2; 3}

Расположение этих записей на листе зависит от разделителя:

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

Последнее именно то, что вам нужно сделать в аргументе индекса VLOOKUP Google Таблиц.

Поскольку я объединяю листы Google, обновляю 2-й столбец и вытягиваю 3-й, мне нужно создать массив с этими столбцами: {2, 3}:

=Формуламассива(ЕСЛИОШИБКА(ВПР($B$2:$B$10,Лист1!$B$2:$D$10,{2,3},ЛОЖЬ),»»))

Таким образом, одна формула VLOOKUP Google Sheets сопоставляет имена, обновляет информацию об акциях и добавляет связанных поставщиков в пустой соседний столбец.

Сопоставьте и объедините листы и добавьте столбцы с ПОИСКПОЗОМ ИНДЕКС

Далее идет INDEX MATCH. Эти две функции вместе конкурируют с функцией ВПР, поскольку они обходят ее ограничения при объединении листов Google.

Кончик. Познакомьтесь с INDEX MATCH для Google Sheets в этом руководстве.

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

=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$C$1:$C$10,ПОИСКПОЗ(B2,Лист1!$B$1:$B$10,0)),»»)

В этой формуле Лист1!$C$1:$C$10 это столбец со значениями, которые вам нужны всякий раз, когда Лист1!$B$1:$B$10 соответствует тому же значению, что и в Би 2 в текущей таблице.

С учетом этих моментов Лист1!$C$1:$C$10 которые вам нужно изменить, чтобы не только объединять таблицы и обновлять ячейки, но и добавлять столбцы.

В отличие от Google Sheets VLOOKUP, здесь нет ничего особенного. Вы просто вводите диапазон со всеми этими необходимыми столбцами: тот, который нужно обновить, и другие, которые нужно добавить. В моем случае это будет Лист1!$C$1:$D$10:

=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$C$1:$D$10,ПОИСКПОЗ(B2,Лист1!$B$1:$B$10,0)),»»)

Или я могу расширить диапазон до Е10 чтобы добавить 2 столбца, а не только один:

=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$C$1:$E$10,ПОИСКПОЗ(B2,Лист1!$B$1:$B$10,0)),»»)

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

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

Объединяйте таблицы Google, обновляйте ячейки и добавляйте связанные столбцы — и все это с помощью QUERY.

QUERY — одна из самых мощных функций в электронных таблицах Google. Поэтому неудивительно, что я собираюсь использовать его сегодня для объединения нескольких листов Google, обновления ячеек и одновременного добавления дополнительных столбцов.

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

Кончик. Если вам интересно, как использовать функцию ЗАПРОС Google Таблиц, посетите этот пост в блоге.

Давайте вспомним формулу, которая сначала обновляет ячейки:

=ЕСЛИОШИБКА(ЗАПРОС(Лист1!$A$2:$C$10,»выберите C, где B='»&ЗАПРОС!$B2:$B$10&»‘»),»»)

Здесь QUERY просматривает таблицу с требуемыми данными в Sheet1, сопоставляет ячейки в столбце B с моей текущей новой таблицей и объединяет эти листы: извлекает данные из столбца C для каждого совпадения. ЕСЛИОШИБКА сохраняет результат безошибочным.

Чтобы добавить дополнительные столбцы для этих совпадений, вам нужно внести 2 небольших изменения в эту формулу:

список всех обязательных столбцов для Выбрать команда:
…выберите C,D,E…
расширить диапазон, чтобы выглядеть соответственно:
…ЗАПРОС(Лист1!$A$2:$E$10,…

Вот полная формула:

=ЕСЛИОШИБКА(ЗАПРОС(Лист1!$A$2:$E$10,»выберите C,D,E где B='»&Лист4!$B2:$B$10&»‘»),»»)

Он обновляет столбец акций и извлекает 2 дополнительных столбца из таблицы поиска в эту основную таблицу.

Как добавить несовпадающие строки с помощью FILTER + VLOOKUP

Представьте себе: вы объединяете 2 листа Google, обновляете старую информацию новой и получаете новые столбцы с дополнительными связанными значениями.

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

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

Я был приятно удивлен, что Google Sheets VLOOKUP умеет это делать. При использовании вместе с функцией ФИЛЬТР она объединяет листы Google, а также добавляет несоответствующие строки.

Кончик. В концея также покажу, как одно дополнение делает то же самое с одним флажком.

Аргументы FILTER Google Sheets довольно ясны:

=ФИЛЬТР(диапазон, условие1, [condition2, …])

диапазон это данные, которые вы хотите отфильтровать.
условие1 столбец или строка с критерием фильтрации.
критерии2, критерии3 и т. д. являются совершенно необязательными. Используйте их, когда вам нужно использовать несколько критериев.

Кончик. Вы узнаете больше о функции ФИЛЬТР Google Таблиц в этом сообщении блога.

Так как же эти две функции уживаются вместе и объединяют листы Google? Что ж, ФИЛЬТР возвращает данные на основе критериев фильтрации, созданных функцией ВПР.

Посмотрите на эту формулу:

=ФИЛЬТР(Лист1!$A$2:$E$10,ЕОШИБКА(ВПР(Лист1!$B$2:$B$10,$B$2:$C$10,2,ЛОЖЬ)=1))

Он сканирует 2 таблицы Google на наличие совпадений и извлекает несовпадающие строки из одной таблицы в другую:

Позвольте мне объяснить, как это работает:

FILTER переходит к справочному листу (таблица со всеми данными — Лист1!$A$2:$E$10) и использует функцию ВПР для получения правильных строк.
ВПР берет имена элементов из столбца B на этом листе поиска и сопоставляет их с именами из моей текущей таблицы. Если совпадений нет, функция ВПР говорит об ошибке.
ISERROR помечает каждую такую ошибку 1, указывая FILTER перенести эту строку на другой лист.

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

Это не так уж сложно, если немного поэкспериментировать с этим методом 🙂

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

Способ сопоставления и объединения данных без формул — надстройка Merge Sheets

Дополнение «Объединить листы» охватывает все 3 возможности при объединении листов Google:

он обновляет связанные ячейки на основе совпадений
добавляет новые столбцы для этих совпадений
вставляет строки с несовпадающими записями

Чтобы избежать путаницы, процесс разбит на 5 простых шагов:

В первых двух вы выбираете свои таблицы, даже если они находятся в разных электронных таблицах.
На 3d вы должны выбрать ключевые столбцы, которые должны быть проверены на совпадения.
Четвертый шаг позволяет настроить столбцы для обновления новыми записями или добавления с одного листа на другой:
Наконец, на 5-м шаге есть этот флажок, который заставит все несоответствующие строки отображаться в конце вашей текущей таблицы:

Прошло несколько секунд, прежде чем я увидел результат:

Установить листы слияния из магазин Google Таблиц и вы увидите, что он обрабатывает большие таблицы так же быстро. Благодаря Merge Sheets у вас будет больше времени для важных дел.