Сочетания формул для межтабличных ссылок

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

• Ознакомьтесь с полным списком формул. 
• Скачайте шаблон формулы.
• Задайте вопрос о конкретном сценарии использования в интернет-сообществе Smartsheet.

Решения для межтабличных формул

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

Любой критерий, заключённый в кавычки, можно заменить прямой ссылкой на ячейку, например [Название столбца]@row, где значение находится в ячейке, а не вводится в формуле. См. статью Создание ссылки на ячейку или столбец в формуле.

Поиск одной ячейки с помощью совпадающего значения

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

Используйте INDEX и MATCH:

=INDEX({Возвращаемый столбец}, MATCH("Совпадающее значение", {Столбец с сопоставляемым значением}, 0))

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

Поиск одной ячейки с использованием нескольких критериев

Верните значение из одного столбца на основе нескольких совпадающих значений в других столбцах. 

Используйте INDEX и COLLECT:

=INDEX(COLLECT({Возвращаемый столбец}, {Столбец 1 с сопоставляемым значением}, "Значение 1", {Столбец 2 с сопоставляемым значением}, "Значение 2"), 1)

Сбор всего совпадающего контента в одной ячейке

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

Используйте JOIN и COLLECT:

=JOIN(COLLECT({Столбец с возвращаемыми значениями}, {Столбец с критерием 1}, "Критерий 1", {Столбец с критерием 2}, "Критерий 2"), ", ")

Среднее с несколькими условиями

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

Используйте AVG и COLLECT:

=AVG(COLLECT({Столбец, для которого требуется найти среднее}, {Столбец с критерием 1}, "Критерий 1", {Столбец с критерием 2}, "Критерий 2"))

Поиск самой поздней даты или самого большого числа с помощью условий

Верните самую позднюю дату или самое большое число в одном столбце на основе нескольких критериев в других столбцах. 

Используйте MAX и COLLECT:

=MAX(COLLECT({Столбец, в котором требуется найти максимальную дату или число}, {Столбец с критерием 1}, "Критерий 1", {Столбец с критерием 2}, "Критерий 2"), ", ")

Если вы ищете самую раннюю дату или самое маленькое число, используйте MIN вместо MAX. 

Остались вопросы?

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

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

Обратиться к Сообществу