В списках и библиотеках допускается использование формул и функций для расчета данных различными способами. Добавив вычисляемый столбец в список или библиотеку, можно создать формулу, содержащую данные из других столбцов, и использовать функции для расчета дат и времени, выполнения математических вычислений или обработки текста. Например, в списке задач можно использовать столбец для расчета количества дней, необходимых для выполнения каждой задачи, на основе значений в столбцах «Начальная дата» и «Дата завершения».
Примечание.
В этой статье описываются основы использования формул и функций. Для получения дополнительных сведения о конкретной функции см. статью об этой функции.
В этой статье
Обзор формул
Обзор функций
Использование в формулах ссылок на столбцы
Использование констант в формулах
Использование операторов в формулах
Обзор формул
Формулы представляют собой выражения, по которым выполняются вычисления в списке или библиотеке. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5.
=5+2*3
Формула может использоваться в вычисляемом столбце, а также для вычисления значений по умолчанию для столбца. Формула может содержать функции (Функция. Стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные.), ссылки на столбцы, операторы (Оператор. Знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок.) и константы (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Выражение и результат вычисления выражения константами не являются.), как показано в следующем примере.
=PI()*[Result]^2
Элемент | Описание |
---|
Функция | Функция ПИ() возвращает значение числа π: 3,141592654. |
Ссылка (или имя столбца) | [Результат] представляет значение в столбце «Результат» для текущей строки. |
Константа | Числовые или текстовые значения, вводимые непосредственно в формулу, например, 2. |
Оператор | Оператор * (звездочка) умножает числа, а оператор ^ (знак вставки) возводит число в степень. |
В формуле может использоваться один или несколько элементов из предыдущей таблицы. Вот несколько примеров формул (в порядке возрастания сложности).
Простые формулы (например, =128+345)
Следующие формулы содержат константы и операторы.
Пример | Описание |
---|
=128+345 | Складывает 128 и 345 |
=5^2 | Возводит 5 в квадрат |
Формулы, содержащие ссылки на столбцы (например, =[Выручка] >[Затраты])
Следующие формулы содержат ссылки на другие столбцы в том же списке или библиотеке.
Пример | Описание |
---|
=[Выручка] | Использует значение в столбце «Выручка». |
=[Выручка]*10/100 | 10% значения столбца «Выручка». |
=[Выручка] > [Затраты] | Возвращает «Да», если значение в столбце «Выручка» больше значения в столбце «Затраты».
|
Формулы, обращающиеся к функциям (например, =СРЗНАЧ(1, 2, 3, 4, 5))
Следующие формулы обращаются к встроенным функциям.
Пример | Описание |
---|
=СРЗНАЧ(1; 2; 3; 4; 5) | Возвращает среднее значение множества значений. |
=МАКС([Q1]; [Q2]; [Q3]; [Q4]) | Возвращает наибольшее значение из набора значений. |
=ЕСЛИ([Затраты]>[Выручка], "Неверно", "ОК") | Возвращает «Неверно», если затраты превышают выручку. В противном случае возвращает «ОК». |
=ДЕНЬ("15-апр-2008") | Возвращает часть даты, обозначающую день. Эта формула возвращает число 15. |
Формулы с вложенными функциями (например, =СУММ(ЕСЛИ([A]>[B]; [A]-[B]; 10); [C]))
В следующих формулах функции используются в качестве аргументов других функций.
Пример | Описание |
---|
=СУММ(ЕСЛИ([A]>[B]; [A]-[B]; 10); [C]) | Функция ЕСЛИ возвращает разность между значениями столбцов A и B, или число 10. Функция СУММ прибавляет к значению, возвращаемому функцией ЕСЛИ, значение столбца C. |
=ГРАДУСЫ(ПИ()) | Функция PI возвращает число 3,141592654. Функция ГРАДУСЫ преобразует радианы в градусы. Эта формула возвращает число 180. |
=ЕЧИСЛО(НАЙТИ("БД";[Столбец1])) | Функция НАЙТИ ищет строку «БД» в столбце Столбец1 и возвращает начальную позицию строки. Если строка не найдена, функция возвращает ошибку. Функция ЕЧИСЛО возвращает «Да», если функция ЕСЛИ возвращает число. В противном случае она возвращает «Нет». |
К началу страницыОбзор функций
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Например, следующий экземпляр функции ОКРУГЛ округляет число в столбце «Затраты» до двух десятичных знаков.
=ROUND([Cost], 2)
Следующий словарь будет полезен при изучении функций и формул.
Структура Структура функции начинается со знака равенства (=), за ним следует имя функции, открывающая скобка, список аргументов, разделенных запятыми, закрывающая скобка.
Имя функции Имя функции, поддерживаемое списками или библиотеками. Каждая функция принимает определенное количество аргументов, обрабатывает их и возвращает значение.
Аргументы Существуют различные типы аргументов: число, текст, логическое значение (ИСТИНА и ЛОЖЬ) или ссылки на столбцы. В качестве аргументов могут также использоваться константы, формулы и другие функции.
Допускается использование функции в качестве аргумента другой функции. Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с суммой значений двух столбцов.
=AVERAGE([Cost1], SUM([Cost2]+[Discount]))
Допустимые типы вычисляемых значений Функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение «Да» или «Нет», то вложенная функция в результате вычислений тоже должна давать логическое значение «Да» или «Нет». Иначе появится сообщение об ошибке #ЗНАЧ!.
Ограничение количества уровней вложения функций В формулах можно использовать до восьми уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. В приведенном выше примере функция СУММ считается функцией второго уровня, потому что она является аргументом функции СРЗНАЧ. Функция, вложенная в качестве аргумента в функцию СУММ, будет функцией третьего уровня и т. д.
Примечания
- Списками и библиотеками не поддерживаются функции СЛЧИСЛ и ТДАТА.
- Функции СЕГОДНЯ и Я не поддерживаются в вычисляемых столбцах, но поддерживаются в значениях по умолчанию, задаваемых для столбцов.
К началу страницыИспользование в формулах ссылок на столбцы
Ссылка указывает на ячейку в текущей строке и на список или библиотеку, где располагаются значения или данные, которые требуется использовать в формуле. Например, [Затраты] ссылается на значение в столбце «Затраты» в текущей строке. Если значение столбца «Затраты» для текущей строки равно 100, тогда =[Затраты]*3 возвращает значение 300.
Благодаря ссылкам в формулах можно использовать данные, содержащиеся в различных столбцах списка или библиотеки. В формулах можно ссылаться на столбцы следующих типов данных: одиночная текстовая строка, число, денежное значение, дата или время, выбор, логическое и вычисляемое значение.
Для ссылки в формуле используется отображаемое имя столбца. Если имя содержит пробел или специальный символ, необходимо заключить имя в квадратные скобки ([ ]). В ссылках регистр букв не учитывается. Например, в формуле можно сослаться на столбец «Цена единицы товара» как [Цена единицы товара] или [цена единицы товара].
Примечания
- Можно ссылаться только на значение в текущей строке.
- Нельзя ссылаться на значение в другом списке или библиотеке.
- Нельзя ссылаться на идентификатор только что вставленной строки. Во время выполнения вычислений этот идентификатор еще не существует.
- Нельзя ссылаться на другой столбец в формуле, которая создает для столбца значение по умолчанию.
К началу страницыИспользование констант в формулах
Константа представляет собой готовое (невычисляемое) значение. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. Константы могут быть следующих типов данных.
- Строка (Пример: =[Фамилия] = "Орехов")
Строковые константы заключаются в кавычки и могут содержать до 255 символов.
- Число (Пример: =[Затраты] >= 29,99)
Числовые константы могут включать десятичные разряды и могут быть положительными и отрицательными.
- Дата (Пример: =[Дата] > ДАТА(2007;7;1))
Для констант типа дата необходимо использование функции ДАТА(год;месяц;день).
- Логический (Пример: =ЕСЛИ([Затраты]>[Выручка], "Потери", "Потерь нет")
«Да» и «Нет» ― логические константы. Они могут использоваться в условных выражениях. В приведенном выше примере если затраты превышают выручку, функция ЕСЛИ возвращает «Да», а формула возвращает строку «Потери». Если затраты равны или меньше выручки, функция возвращает «Нет», а формула возвращает строку «Потерь нет».
К началу страницыИспользование операторов в формулах
Операторами обозначаются операции, которые следует выполнить над операндами формулы. Списками и библиотеками поддерживаются три различных типа операторов: арифметические, текстовые и операторы сравнения.
Арифметические операторы
Следующие арифметические операторы используются для выполнения основных математических операций (сложение, выделение, деление и умножение), для объединения чисел, а также для получения числовых результатов.
Арифметический оператор | Значение (пример) |
---|
+ (знак плюс) | Сложение (3+3) |
– (знак минус) | Вычитание (3–1) Отрицание (–1) |
* (звездочка) | Умножение (3*3) |
/ (косая черта) | Деление (3/3) |
% (знак процента) | Процент (20%) |
^ (крышка) | Возведение в степень (3^2) |
Операторы сравнения
Используются для сравнения двух значений. Результатом сравнения является логическое значение: «Да» или «Нет».
Оператор сравнения | Значение (пример) |
---|
= (знак равенства) | Равно (A=B) |
> (знак больше) | Больше (A>B) |
< (знак меньше) | Меньше (A<B) |
>= (знак больше или равно) | Больше или равно (A>=B) |
<= (знак меньше или равно) | Меньше или равно (A<=B) |
<> (знак не равно) | Не равно (A<>B) |
Текстовый оператор
Амперсанд (&) используется для объединения нескольких текстовых строк в одну.
Текстовый оператор | Значение (пример) |
---|
& (амперсанд) | Объединение последовательностей знаков в одну последовательность ("Северный"&"ветер") |
Порядок выполнения операций в формуле листом или библиотекой
Формулы вычисляют значения в определенном порядке. Формула может начинаться со знака равенства (=). Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула вычисляется слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет оператора
Если в одной формуле используется несколько операторов, списки и библиотеки выполняют операции в порядке, показанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом — например, операторы деления и умножения — они выполняются слева направо.
Оператор | Описание |
---|
– | Знак «минус» |
% | Процент |
^ | Возведение в степень |
* и / | Умножение и деление |
+ и – | Сложение и вычитание |
& | Конкатенация (объединение двух строк текста в одну). |
= < > <= >= <> | Сравнение |
Использование круглых скобок
Для того чтобы изменить порядок выполнения, заключите часть формулы, которая должна выполняться первой, в скобки. Например, результатом следующей формулы будет число 11, поскольку список или библиотека выполняет умножение до сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 5.
=5+2*3
Если же с помощью скобок изменить синтаксис, список или библиотека сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.
=(5+2)*3
В приведенном ниже примере скобки вокруг первой части формулы определяют следующий порядок вычислений: определяется значение [Затраты]+25, а затем полученный результат делится на сумму значений в столбцах EC1 и EC2.
=([Cost]+25)/SUM([EC1]+[EC2])
К началу страницы