Вам когда-нибудь требовалось извлечь значение из диапазона на основе соответствующего значения в списке? Например, динамически вывести цену продукта на основе его идентификатора.
Если набор данных содержит идентификатор продукта, можно использовать формулу, чтобы вывести цену.
Если задать эту формулу как формулу столбца, то в каждой строке (и во всех позднее добавленных строках) будет отображаться соответствующая цена из набора данных о продукте.
Различные подходы к такой формуле
Существует три метода, которые можно использовать для извлечения данных из диапазона на основе совпадающего искомого значения.
- VLOOKUP
- INDEX(MATCH())
- INDEX(COLLECT())
Мы рассмотрим принципы применения каждой из этих формул, а также обсудим плюсы и минусы различных подходов.
VLOOKUP
Формула VLOOKUP ищет и возвращает значение в той же строке, но из другого (указанного) столбца. Формат формулы VLOOKUP показан ниже.
=VLOOKUP([Искомое значение], [Набор данных для поиска], [Номер столбца в наборе данных], ["Ложь" или "Истина" в зависимости от необходимости точного совпадения])
Чтобы с помощью VLOOKUP вывести цену в примере выше, необходимо использовать такую формулу:
=VLOOKUP([Идентификатор связанного продукта]@строка, {Product Data | Product}, 4, ложь)
Диапазон межтабличных ссылок {Product Data | Product} выглядит так:
И формула возвращает значения в столбце "Цена" таким образом:
СОВЕТ: перенесите функцию IFERROR в формулу для сценариев, если в искомом наборе данных не найдётся ни одного совпадения. В этом примере формула будет выглядеть так:
=IFERROR(VLOOKUP([Идентификатор связанного продукта]@строка, {Product Data | Product}, 4, ложь), "Совпадений не найдено")
Плюсы
- Самая простая формула.
Минусы
- Искомое значение должно быть первым столбцом в искомом наборе данных.
- Невозможно извлечь значения слева от столбца искомого значения.
- Может быть нарушена, если между искомым значением и столбцом, из которого извлекаются данные, добавлен или удалён столбец, а также если поменялся порядок следования столбцов.
INDEX(MATCH())
Формула INDEX(MATCH()) выполняет поиск в диапазоне и собирает значения, соответствующие заданным критериям. Формат формулы INDEX(MATCH()) показан ниже.
=INDEX([Диапазон с возвращаемым значением],MATCH([Искомое значение],[Диапазон с искомым значением],[0, 1 или -1 в зависимости от типа поиска]))
Чтобы с помощью INDEX(MATCH()) вывести цену в примере выше, необходимо использовать такую формулу:
=IFERROR(INDEX({Product Data | Price}, MATCH([Идентификатор связанного продукта]@строка, {Product Data | Product ID}, 0)), "Совпадений не найдено")
Диапазон межтабличных ссылок {Product Data | Price} выглядит так:
Диапазон межтабличных ссылок {Product Data | Product ID} выглядит так:
Как и в примере выше, в формулу INDEX(MATCH()) была перенесена функция IFERROR, чтобы отобразить сообщение "Совпадений не найдено", если для строки не найден соответствующий идентификатор продукта. Поэтому формула возвращает значения в столбце "Цена" таким образом:
Плюсы
- Можно изменять порядок столбцов или удалять неиспользуемые столбцы, не нарушая формулу.
- Можно извлекать значения из столбцов слева или справа от диапазона искомых значений.
- Позволяет быстрее обрабатывать большие массивы данных.
- Формулу INDEX(MATCH(),MATCH()) можно использовать для динамического сопоставления столбцов и строк.
- Общее количество ячеек с указанной ссылкой обычно невелико, что позволяет не превышать ограничение в 100 000 ячеек, указываемых в межтабличных ссылках.
Минусы
- Если ссылки на данные содержатся в отдельной таблице, требуется более одной межтабличной ссылки.
- Используется не более одного критерия совпадения.
INDEX(COLLECT())
Формула INDEX(COLLECT()) выполняет поиск в диапазоне и собирает значения, соответствующие одному или нескольким заданным критериям. Формат формулы INDEX(COLLECT()) показан ниже.
=INDEX(COLLECT([Диапазон с возвращаемым значением],[Диапазон с критерием],[Критерий],[Диапазон 2 с критерием],[Критерий] и т. д.),[1 для возвращаемого индекса строки])
Чтобы с помощью INDEX(COLLECT()) вывести цену в примере выше, необходимо использовать такую формулу:
=IFERROR(INDEX(COLLECT({Product Data | Price}, {Product Data | Product ID}, [Идентификатор связанного продукта]@строка), 1), "Совпадений не найдено")
Диапазон межтабличных ссылок {Product Data | Price} выглядит так:
Диапазон межтабличных ссылок {Product Data | Product ID} выглядит так:
Как и в примере выше, в формулу INDEX(COLLECT()) была перенесена функция IFERROR, чтобы отобразить сообщение "Совпадений не найдено", если для строки не найден соответствующий идентификатор продукта. Поэтому формула возвращает значения в столбце "Цена" таким образом:
Плюсы
- Можно изменять порядок столбцов или удалять неиспользуемые столбцы, не нарушая формулу.
- Можно извлекать значения из столбцов слева или справа от диапазона искомых значений.
- Работает быстрее, чем VLOOKUP, но медленнее, чем INDEX/MATCH.
- Позволяет использовать несколько критериев в формуле COLLECT для сопоставления по нескольким столбцам или создания более сложных критериев.
- Можно указать второе, третье и т. д. совпадения, заменяя ", 1" в конце формулы, а не указывать постоянно первое совпадение.
Минусы
- Если ссылки на данные содержатся в отдельной таблице, требуется более одной межтабличной ссылки.
- Может быть медленнее, чем INDEX/MATCH, особенно при использовании нескольких критериев.
Остались вопросы?
Используйте шаблон Руководство по работе с формулами, чтобы просмотреть дополнительные ресурсы и изучить более 100 формул. Руководство содержит глоссарий, описывающий каждую функцию, обращение с которой вы сможете отработать на практике, и примеры как часто используемых, так и более сложных функций.
Изучить примеры того, как эту функцию применяют другие пользователи Smartsheet, или задать интересующий вопрос можно в Сообществе Smartsheet.
Обратиться к Сообществу