Объединить 2 листа Google и обновить данные на основе общих записей
В сегодняшней публикации блога представлены все способы объединения двух Google Таблиц. Вы будете использовать надстройки VLOOKUP, INDEX/MATCH, QUERY и Merge Sheets для обновления ячеек в одном листе из записей из другого на основе совпадений в общих столбцах.
Объединяйте листы Google с помощью функции ВПР
Первое, к чему вы можете обратиться, когда вам нужно сопоставить и объединить два листа Google, — это функция ВПР.
Синтаксис и использование
Эта функция ищет в указанном вами столбце определенное значение ключа и извлекает одну из связанных записей из той же строки в другую таблицу или лист.
Хотя Google Sheets VLOOKUP обычно считается одной из сложных функций, на самом деле это довольно просто и даже легко, как только вы с ней познакомитесь.
Давайте кратко рассмотрим его компоненты:
=ВПР(ключ_поиска, диапазон, индекс, [is_sorted])
- search_key ключевое значение, которое вы ищете. Это может быть любая текстовая строка, число или ссылка на ячейку.
- диапазон это та группа ячеек (или таблица), где вы будете искать search_key и откуда вы будете извлекать соответствующие записи.
Примечание. Функция ВПР в Google Таблицах всегда сканирует первый столбец таблицы. диапазон для search_key.
- индекс это номер столбца внутри этого диапазон откуда вы хотите получить данные.
Например, если ваш диапазон для поиска — A2:E20, и это столбец E, из которого вам нужно получить данные, введите 5. Но если ваш диапазон — D2:E20, вам нужно будет ввести 2, чтобы получить записи из столбца E.
- [is_sorted] единственный аргумент, который можно опустить. Используется, чтобы сказать, отсортирован ли столбец со значениями ключа (ИСТИНА) или нет (ЛОЖЬ). Если TRUE, то функция будет работать с ближайшим соответствием, если FALSE — с полным. Если этот параметр опущен, по умолчанию используется TRUE.
Кончик. У нас есть подробное руководство, посвященное ВПР в Google Таблицах. Пожалуйста, ознакомьтесь с ним, чтобы узнать больше о функции, ее особенностях и ограничениях, а также получить больше примеров формул.
Имея в виду эти аргументы, давайте воспользуемся функцией ВПР для объединения двух листов Google.
Предположим, у меня есть небольшая таблица с ягодами и их идентификаторами в Sheet2. Наличие на складе неизвестно:
Назовем эту таблицу основной, так как моя цель ее заполнить.
В Sheet1 есть еще одна таблица со всеми данными, включая наличие на складе:
Я назову ее таблицей поиска, так как я буду изучать ее, чтобы получить данные.
Я буду использовать функцию Google Sheets VLOOKUP, чтобы объединить эти 2 листа. Функция будет сопоставлять ягоды в обеих таблицах и извлекать соответствующую информацию о запасах из поиска в основную таблицу.
=ВПР(B2,Лист1!$B$2:$C$10,2,ЛОЖЬ)
Вот как эта формула точно объединяет два листа Google:
- Он ищет значение из B2 (основной лист) в столбце B на Sheet1 (лист поиска).
Примечание. Помните, что функция ВПР сканирует 1-й столбец указанного диапазона — Лист1!$B$2:$C$10.
Примечание. Я использую абсолютные ссылки для диапазона, потому что я копирую формулу вниз по столбцу, и поэтому мне нужно, чтобы этот диапазон оставался одинаковым в каждой строке, чтобы результат не ломался.
- FALSE в конце говорит, что данные в столбце B (в таблице поиска) не отсортированы, поэтому будут учитываться только точные совпадения.
- Как только есть совпадение, функция ВПР Google Таблиц извлекает связанную запись из второго столбца этого диапазона (столбец C).
Скрыть ошибки, возвращаемые функцией ВПР, в Google Таблицах — ЕСЛИОШИБКА
Но как насчет этих ошибок #N/A?
Вы видите их в тех рядах, где ягоды не имеют спичек в другом листе и возвращать нечего. К счастью, есть способ оставить такие ячейки пустыми.
Просто оберните свой Google Sheets VLOOKUP в IFERROR:
=ЕСЛИОШИБКА(ВПР(B2,Лист1!$B$2:$C$10,2,ЛОЖЬ),””)
Кончик. Выявите и исправьте другие ошибки, которые может возвращать функция ВПР Google Таблиц, используя решения из этого руководства.
Сопоставление и обновление записей для всего столбца одновременно — ArrayFormula
Еще одна вещь, которую я хотел бы упомянуть, это то, как сопоставлять и объединять данные Google Sheets для всего столбца одновременно.
Ничего особенного, просто еще одна функция — ArrayFormula.
Просто замените свою ключевую запись с одной ячейкой в Google Sheets VLOOKUP на весь столбец и поместите всю эту формулу в ArrayFormula:
=Формуламассива(ЕСЛИОШИБКА(ВПР(B2:B10,Лист1!$B$2:$C$10,2,ЛОЖЬ),””))
Таким образом, вам не нужно будет копировать формулу вниз по столбцу. ArrayFormula сразу вернет правильный результат в каждую ячейку.
Хотя функция ВПР в Google Таблицах идеально подходит для таких простых задач, она имеет некоторые ограничения. Вот один из недостатков: он не может смотреть налево. Какой бы диапазон вы ни указывали, он всегда сканирует свой первый столбец.
Таким образом, если вам нужно объединить 2 листа Google и вытащить идентификаторы (данные 1-го столбца) на основе ягод (2-й столбец), ВПР не поможет. Вы просто не сможете построить правильную формулу.
В таких случаях в игру вступает INDEX MATCH для Google Sheets.
Сопоставьте и объедините листы Google с помощью INDEX MATCH duo
ПОИСКПОЗ ИНДЕКС, а точнее ИНДЕКС и ПОИСКПОЗ, на самом деле являются двумя разными функциями Google Sheets. Но когда они используются вместе, это похоже на ВПР следующего уровня.
Да, они также объединяют листы Google: обновляют ячейки в одной таблице записями из другой таблицы на основе общих ключевых записей.
Но они делают это намного лучше, поскольку игнорируют все те ограничения, которые имеет ВПР.
Сегодня я не буду описывать все основы, потому что я сделал это в этом сообщении в блоге. Но я приведу несколько примеров формул INDEX MATCH, чтобы вы могли увидеть, как они работают непосредственно в электронных таблицах Google. Я буду использовать те же примеры таблиц, что и выше.
INDEX MATCH в действии в Google Sheets
Во-первых, давайте объединим эти листы Google и обновим наличие на складе для всех подходящих ягод:
=ИНДЕКС(Лист1!$C$1:$C$10,ПОИСКПОЗ(B2,Лист1!$B$1:$B$10,0))
Как работают ИНДЕКС и ПОИСКПОЗ, когда используются вместе?
- MATCH смотрит на B2 и ищет точно такую же запись в столбце B на Sheet1. После нахождения он возвращает номер строки, содержащей это значение — в моем случае 10.
- ИНДЕКС также идет в эту 10-ю строку на Листе 1, только он берет значение из другого столбца — C.
Теперь давайте попробуем проверить INDEX MATCH с тем, что не может сделать Google Sheets VLOOKUP — объединить листы и обновить крайний левый столбец необходимыми идентификаторами:
=ИНДЕКС(Лист1!$A$2:$A$10,ПОИСКПОЗ(B2,Лист1!$B$2:$B$10,0))
Очень просто 🙂
Обработка ошибок, возвращаемых INDEX MATCH в Google Таблицах
Давайте пойдем дальше и избавимся от этих ошибок в ячейках без совпадений. IFERROR снова поможет. Просто укажите свой ИНДЕКС ПОИСКПОЗ в Google Таблицах в качестве первого аргумента.
Пример 1.
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$C$1:$C$10,ПОИСКПОЗ(B2,Лист1!$B$1:$B$10,0)),””)
Пример 2.
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$A$2:$A$10,ПОИСКПОЗ(B2,Лист1!$B$2:$B$10,0)),””)
Теперь, как вы объедините эти листы Google с помощью INDEX MATCH и обновите все ячейки во всем столбце одновременно?
Ну… нет. Есть небольшая проблема: ArrayFormula не работает с этими двумя.
Вам нужно будет скопировать формулу INDEX MATCH вниз по столбцу или использовать функцию QUERY Google Таблиц в качестве альтернативы.
Объединяйте листы Google и обновляйте ячейки с помощью QUERY
Google Sheets QUERY — самая мощная функция электронных таблиц. Имея это в виду, неудивительно, что он предлагает способ объединения таблиц — сопоставление и объединение значений из разных листов.
=ЗАПРОС(данные, запрос, [headers])
Кончик. Если вы никогда раньше не пользовались Google Sheets QUERY, это руководство познакомит вас с его своеобразным языком.
Как должна выглядеть формула QUERY для обновления Запас столбец с фактическими данными?
=ЗАПРОС(Лист1!$A$2:$C$10,”выберите C, где B='”&Лист4!$B2:$B$10&”‘”)
- Google Sheets QUERY просматривает мой лист поиска (Лист1 с записями, которые мне нужно вытащить в мою основную таблицу)
- и возвращает все те ячейки из столбца C, где столбец B соответствует ягодам в моей основной таблице.
Позвольте мне просто потерять эти ошибки для ячеек без совпадений:
=ЕСЛИОШИБКА(ЗАПРОС(Лист1!$A$2:$C$10,”выберите C, где B='”&Лист4!$B2:$B$10&”‘”),””)
Ну так лучше 🙂
Объединяйте таблицы из разных электронных таблиц Google — функция IMPORTRANGE
Есть еще одна функция, о которой я хотел бы упомянуть. Это важно, поскольку позволяет объединять листы, находящиеся в разных электронных таблицах (файлах) Google.
Функция называется IMPORTRANGE:
=IMPORTRANGE(“адрес_таблицы”,”строка_диапазона”)
- первый идет по ссылке на эту электронную таблицу, из которой вы извлекаете данные
- последний идет лист и диапазон, который вы хотите взять из этой электронной таблицы
Примечание. очень рекомендую пройти Документы Google по этой функции чтобы не упустить ни одного важного нюанса его работы.
Представьте, что ваша таблица поиска (со справочными данными) находится в электронной таблице 2 (также известной как таблица поиска). Ваш основной лист находится в электронной таблице 1 (основная электронная таблица).
Примечание. Для работы IMPORTRANGE необходимо подключить оба файла. И хотя Google Sheet предлагает кнопку для этого сразу после того, как вы введете формулу в ячейку и нажмете Enter, для формул ниже вам может потребоваться сделать это заранее. Это пошаговое руководство поможет вам.
Ниже приведены примеры объединения листов Google из разных файлов с помощью IMPORTRANGE с каждой функцией, которую вы изучили ранее сегодня.
Пример 1. IMPORTRANGE + ВПР
Используйте IMPORTRAGE как диапазон в VLOOKUP, чтобы объединить 2 отдельные электронные таблицы Google:
=Формуламассива(ЕСЛИОШИБКА(ВПР(B2:B10,ИМПОРТРАНЖ(“
Пример 2. IMPORTRANGE + INDEX MATCH
Что касается INDEX MATCH & IMPORTRANGE, формула становится более объемной, поскольку вам нужно дважды ссылаться на другую электронную таблицу: как диапазон для INDEX и как диапазон для MATCH:
=ЕСЛИОШИБКА(ИНДЕКС(ИМПОРТРАНЖ(“
Пример 3. IMPORTRANGE + ЗАПРОС
Этот тандем формул – мой личный фаворит. При совместном использовании они могут работать практически с чем угодно в электронных таблицах. Объединение листов Google из отдельных электронных таблиц не является исключением.
=ЕСЛИОШИБКА(ЗАПРОС(ИМПОРТАЖ(” Col3, где Col2='”&ЗАПРОС!$B2:$B$10&”‘”),””)
Вау!
Это все, что касается функций и формул.
Вы можете выбрать любую функцию и построить собственную формулу по приведенным выше примерам…
или же…
…попробуйте специальный инструмент, который объединяет таблицы Google для вас! 😉
Способ сопоставления и объединения данных без формул — надстройка Merge Sheets для Google Sheets
Если у вас нет времени на создание или даже изучение формул, или если вы просто ищете самый простой способ объединения данных на основе общих записей, слияние листов будет идеальным решением.
Все, что вам нужно сделать, это поставить галочки в 5 удобных для пользователя шагов:
- выберите основной лист
- выберите свой поисковый лист
- отметьте ключевые столбцы (те, которые содержат записи для сопоставления) флажками
- выберите столбцы для обновления:
- настроить дополнительные параметры, например, отметить обновленные записи цветом или в колонке статуса и т.д.
Есть даже возможность сохранить все выбранные опции в сценарий и повторно использовать его, когда вам нужно:
Посмотрите это 3-минутное демонстрационное видео, чтобы узнать, как это работает:
Я рекомендую вам установить Merge Sheets из Магазин Google Таблиц и следуйте этим инструкциям, чтобы попытаться обновить свою таблицу информацией с другого листа.
Таблица с примерами формул
Объединение листов Google и обновление данных — примеры формул (сделать копию файла)
