Часто задаваемые вопросы об использовании формул

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

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

Искать сведения о формулах в обсуждениях Сообщества >

Ответы на вопросы об использовании формул в Smartsheet

Может ли формула ссылаться на ячейку в другой таблице?

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

Поддерживается ли в Smartsheet функция VLOOKUP?

Да! Дополнительные сведения см. в статье Функция VLOOKUP.

Как получить формулу, в которую будут включаться новые добавленные строки?

Если вставить новую строку выше или ниже строки с формулой либо между двумя строками с одной и той же формулой, то формула будет добавлена автоматически (подробнее об этом см. в статье Автоматическое заполнение формул и форматирование).

Вы можете создать формулу, использующую все значения в столбце, исключив ссылки на строки и используя только имя столбца при указании диапазона, например =SUM([Всего часов]:[Всего часов] ). В этом случае все добавляемые строки будут автоматически включаться в вычисления.

Если вы настроили формулу в столбце, она автоматически будет применяться ко всем новым ячейкам в нём. Дополнительные сведения см. в статье Настройка формул для всех строк с помощью формул в столбцах.

Можно ли управлять столбцами типа «Символы» с помощью формул?

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

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

Звёздочка, флаг и флажок

Для этих типов столбцов символ может быть установлен или снят. Чтобы установить его, используйте 1, а чтобы снять — 0. Например, можно поместить эту формулу в столбец с флажками, чтобы установить галочку при наличии статуса «Завершено». Для этого рекомендуем использовать формулу следующего вида:

=IF([Статус]5 = "Завершено", 1, 0)

 

Светофор (красный, жёлтый, зелёный, серый)

Можно использовать слова «Красный», «Жёлтый» и «Зелёный», например:

=IF([Статус]5 = "Под угрозой"; "Красный"; "Зелёный")

 

Приоритет

В этих столбцах используются значения «Высокий» или «Низкий», например:

=IF([Статус]5 = "Просрочено", "Высокий", "Низкий"))

Диаграммы выполнения

Здесь используются символы четвертей круга со значениями «Четверть», «Половина», «Три четверти», «Полная» и «Пустая», например:

=IF([% выполнения]5 = 1, "Полная", "Пустая")

Формула введена правильно, но отображается в ячейке как текст. В чем проблема?

Ниже приведены три наиболее вероятные причины появления в ячейке текста вместо формулы.

Отсутствует знак = 

Формула должна начинаться со знака равенства (=).

В таблице включены зависимости 

Когда в таблице включены зависимости, запрещено вводить формулы в столбцах с поддержкой зависимостей: «Дата начала», «Длительность», «Дата окончания», «Предшественники», «% выполнения» и «% выделения».

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

При создании формулы использовался отчёт или мобильное устройство 

В настоящее время создание формул из отчётов, а также из приложений для Android и iOS не поддерживается. Формула, созданная из отчёта или с мобильного устройства, будет отображаться как текст, а вычисления выполняться не будут.

Почему не работает формула, ссылающаяся на ячейку в столбце «% выполнения»?

Если в столбцах, отформатированных с помощью кнопки процентного значения на панели инструментов, отключить форматирование, то вы увидите, что хотя значение отображается в виде процентов (например, 50%), на самом деле оно имеет вид десятичной дроби (0,5). Для правильного сравнения отформатированных процентных значений в формулах (а также в критериях отчётов и правилах условного форматирования) используйте для их представления десятичные значения. Пример: =IF([% выполнения]5

Можно ли создавать формулы в столбцах таблицы проекта?

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

Формула, ссылающаяся на столбец даты начала или окончания, не работает. Как это исправить?

Если для таблицы включены зависимости, то в столбцах дат начала и окончания содержится скрытый компонент времени. Хотя время и не отображается в столбце, оно влияет на результаты вычислений. Предположим, что в таблице есть формула, вычитающая TODAY() из значения ячейки даты начала, а дата начала — сегодня. Результат этой формулы должен равняться нулю, однако на самом деле отобразится десятичное значение, так как Smartsheet производит вычитание с учётом скрытой метки времени. В качестве обходного решения можно использовать функцию DATEONLY(). Например, формулу

=[Дата начала]2 - TODAY()

необходимо переписать таким образом: 

=DATEONLY([Дата начала]2) - TODAY()

Тогда при расчёте Smartsheet будет учитывать только дату, опуская значение времени.

Что происходит с формулами при использовании команд «Перенести в другую таблицу» и «Копировать в другую таблицу» для перемещения или копирования в другую таблицу строки с формулами?

При использовании этих команд для перемещения или копирования строк формулы не сохраняются. Однако для копирования и вставки формул можно использовать сочетания клавиш. Дополнительные сведения см. в разделе «Копирование и вставка формул» справочной статьи «Использование копирования и вставки».

С чем может быть связано некорректное вычисление значений в моих формулах? Например, при сложении 1 и 12 получается 112, а не 13.

Часто такая ошибка в вычислениях связана с тем, что одно из значений, на которые ссылается формула, воспринимается как текст. Чтобы исправить эту ошибку, проверьте, нет ли в соответствующем значении символа, который преобразовывает число в текст. Например, такое происходит, если перед числом 12 вставить апостроф (‘).

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

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

Также выравнивание по левому краю автоматически применяется к числовым значениям в основном столбце. Основной столбец легко определить по ПОЛУЖИРНОМУ ШРИФТУ заголовка. Числовые значения в основном столбце будут восприниматься как числа, если иное не обусловлено свойствами самой ячейки.

I’m using a SUMIFS formula that should be returning a value, but it is returning a 0.

If you are evaluating the same range of data for multiple criteria, you’ll want to use the OR Function and @cell parameter in order to have the correct value returned.

Item

Quantity

Cost

Warehouse

Shirt

26

20.00

A

Pants

24

50.00

A

Socks

10

10.00

A

Shirt

18

25.00

B

Pants

16

75.00

B

Socks

46

15.00

B

=SUMIFS(Cost:Cost, Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks"))

Sums the Cost of anything with a Quantity over 15 from Warehouse "A" where the Item is either "Shirt" or "Socks"

 

20.00

I’m using a COUNTIFS formula that should be returning a value, but it is returning a 0.

If you are evaluating the same range of data for multiple criteria, you’ll want to use the OR Function and @cell parameter in order to have the correct value returned.

Item

Quantity

Cost

Warehouse

Shirt

26

20.00

A

Pants

24

50.00

A

Socks

10

10.00

A

Shirt

18

25.00

B

Pants

16

75.00

B

Socks

46

15.00

B

=COUNTIFS(Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks"))

Counts anything with a Quantity over 15 from Warehouse "A" where the Item is either "Shirt" or "Socks"

 

1