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

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

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

Межтабличные формулы

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

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

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

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

Use INDEX and MATCH, for example:

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

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

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

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

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

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

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

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

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

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

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

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

Use AVG and COLLECT, for example:

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

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

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

Use MAX and COLLECT, for example:

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

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

Optimize cross-sheet references

The total number of cells that formulas can reference in a sheet can reach 25 million. If you exceed this limit, you will be prompted to reduce the number of cells referenced by formulas.

To optimize the number of cells referenced by formulas in your sheet, here are a few helpful tips:

• Use INDEX(MATCH) instead of VLOOKUP: VLOOKUP references an entire table range, while INDEX(MATCH) only refers to the necessary columns, reducing the number of cells involved.
• Distribute formulas across multiple sheets: Instead of using a single master sheet, spread your data by creating separate sheets for different request types. This helps to distribute formulas more efficiently.
• Reference only essential cells: Formulas should only refer to the specific cells needed for calculations, which can help reduce the total number of referenced cells.

These steps can help you make the most of the software's capabilities and improve performance.

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

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

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

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