Как согласовать импортированные данные с введенными вручную в Google Таблицах

Содержание:

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

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

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

Рассмотрим этот пример.

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

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

Этой настройки хватило на первые несколько дней использования; однако будут дни, когда запись будет переупорядочена (например, когда продукта нет в наличии, сотрудник не будет записывать его для этого дня, другой порядок продуктов). Из-за этого все расчеты и заметки, которые вы сделали в отдельной таблице, перепутались. Что вы можете сделать, чтобы это исправить?

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

Создание уникальных идентификаторов

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

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

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

Чтобы согласовать эти наборы данных, мы будем зависеть от нескольких функций: функций VLOOKUP, IMPORTRANGE, IFERROR, а также ARRAYFORMULA. Мы будем подробно обсуждать только первые две функции, поскольку они являются наиболее важными для поставленной задачи, в то время как последние две действуют скорее как ярлыки, чтобы упростить задачу. Следуйте ссылкам на эти функции для более подробного обсуждения.

Теперь давайте сначала узнаем больше о функции VLOOKUP в Google Таблицах!

Особенности функции VLOOKUP

Итак, синтаксис (то, как мы пишем) функции VLOOKUP следующий:

=VLOOKUP(search_key, range, index, [is_sorted])

Давайте проанализируем это и поймем, что означает каждый из этих терминов:

= знак равенства — это то, как мы запускаем любую функцию в Google Таблицах.
VLOOKUP () это наша функция VLOOKUP (ВПР). Она выполняет поиск ключа в первом столбце диапазона и возвращает значение указанной ячейки в найденной строке.
search_key — это значение для поиска.
range (диапазон) — это массив или диапазон, который следует учитывать при поиске. В первом столбце диапазона ищется ключ, указанный в search_key.
index — это индекс столбца возвращаемого значения, где первый столбец в диапазоне пронумерован 1.
is_sorted указывает, отсортирован ли столбец для поиска. Это необязательный параметр, по умолчанию он имеет значение TRUE (ИСТИНА). Когда is_sorted имеет значение TRUE, возвращается ближайшее совпадение. Однако рекомендуется установить для is_sorted значение FALSE, чтобы возвращалось точное совпадение.

Функция VLOOKUP будет отвечать за сопоставление уникальных идентификаторов каждой записи с правильной строкой в SF2. Эта функция сопоставляет уникальные идентификаторы (search_key) в SF2 с идентификаторами в SF1 (диапазон). Затем она возвращает значения, найденные в той же строке. Номер индекса указывает, какие данные будут возвращены: 2 — Товар, 3 — Категория, 4 — Количество на складе, 5 — Продажи.

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

Теперь давайте узнаем больше о функции IMPORTRANGE в Google Таблицах!

Особенности функции IMPORTRANGE

Итак, синтаксис (как мы пишем) функции IMPORTRANGE следующий:

=IMPORTRANGE(spreadhseet_url, range_string)

Давайте проанализируем это и поймем, что означает каждый из этих терминов:

= знак равенства — это то, как мы запускаем любую функцию в Google Таблицах.
IMPORTRANGE () это наша функция IMPORTRANGE. Она импортирует диапазон ячеек из указанной электронной таблицы.
spreadsheet_url — это URL-адрес электронной таблицы, из которой будут импортированы данные. Это значение может быть заключено в кавычки или может быть ссылкой на ячейку, содержащую URL.
range_string должен быть строковым значением, определяющим диапазон для импорта. Он должен соответствовать формату: «[имя_листа!] Диапазон» (например, «Лист1! A1: B3» или «A1: B3» ). Если имя_листа не указано, функция IMPORTRANGE будет импортировать из заданного диапазона первого листа. Значение range_string может быть заключено в кавычки или может быть ссылкой на ячейку, содержащую URL.

Теперь, когда мы узнали о функциях VLOOKUP и IMPORTRANGE, мы в значительной степени можем согласовать импортированные данные с данными, введенными вручную в Google Таблицах. Однако использование только этих двух функций потребует от нас ввода формулы в каждую ячейку электронной таблицы, из которой должны быть импортированы данные. Чтобы упростить эту задачу, мы будем использовать две дополнительные функции, упомянутые ранее.

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

=ARRAYFORMULA(IFERROR(VLOOKUP(Unique IDs,IMPORTRANGE(SF1 URL, Imported Data), index, FALSE)))

Теперь мы, наконец, готовы согласовать импортированные данные с данными, введенными вручную в Google Таблицах!

Как согласовать импортированные данные с данными, введенными вручную в Google Таблицах

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

Давайте посмотрим на рассмотренный ранее сценарий.

Создайте уникальные идентификаторы для каждой записи в обеих ваших таблицах. Отредактируйте свойства совместного использования SF1. Убедитесь, что выбран вариант Anyone with the link (Все, у кого есть ссылка), а не «Ограничено».

В SF2 просто щелкните любую ячейку, чтобы сделать ее активной. Для этого руководства я выберу B2, где я хочу показать свой результат. Затем введите формулу: =ARRAYFORMULA(IFERROR(VLOOKUP(

Далее, выберите первый столбец в качестве параметра SEARCH_KEY или просто введите A2: A . После этого введите запятую ‘ , ‘, чтобы указать, что мы хотели бы перейти к следующему параметру. Если все сделано правильно, должен быть выделен первый столбец, кроме заголовка.

Для параметра диапазона мы будем использовать функцию IMPORTRANGE. Введите IMPORTRANGE или выберите функцию при вводе. Заключите ссылку SF1 в кавычки, чтобы ввести параметр spreadsheet_url. Затем укажите диапазон, содержащий данные для импорта. В этом примере введите «Inventory! A2: E», чтобы выбрать первые 5 столбцов. Наконец, закройте параметр диапазона закрытой круглой скобкой и запятой. * Примечание: «Inventor» в формуле относится к имени листа в SF1, из которого вы хотите получить данные.

Для параметра индекса укажите количество столбцов, которые вы хотите импортировать. В этом примере мы хотим извлечь все данные в указанном диапазоне. Введите {2,3,4,5} и запятую. Первый столбец не выбран, поскольку он относится к уникальным идентификаторам. Введите FALSE для параметра is_sorted.

Нажмите Enter, чтобы заполнить формулу. Верните предыдущие данные в соответствующие выравнивания.

Наконец, вот некоторые вещи, на которые вам следует обратить внимание:

Важно, чтобы используемые идентификаторы были уникальными для каждой записи и использовались правильно и последовательно, чтобы избежать ошибок в дальнейшем.
В SF2 столбцы от B до F состоят из импортированных данных. Вам следует избегать ввода данных, введенных вручную, для этих столбцов, так как это приведет к ошибке.
Даже если некоторые идентификаторы в SF1 удалены или отсутствуют, тот же идентификатор все равно будет присутствовать в SF2, но столбцы, содержащие импортированные данные, будут пустыми.
Для импортированных данных из того же файла просто замените функцию IMPORTRANGE диапазоном в формате диапазона [имя_листа!] . Тогда общая формула будет такой:

=ARRAYFORMULA(IFERROR(VLOOKUP(Unique IDs,[sheet_name]!range, index, FALSE)))

Все сделано! Легко согласовать импортированные данные с данными , введенными вручную, не так ли?

Также просмотрите другие многочисленные формулы Google Таблиц, чтобы создать еще более эффективные формулы, которые могут значительно облегчить вашу жизнь.

Вас могут заинтересовать следующие статьи: