Электронные таблицы: Excel, Функции

Информацию по соответствию встроенных функций Excel 2010 для английской и русской версий (и еще для 14 языков) можно найти здесь.

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

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

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

Применение функций

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

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

Аргументы и их типы

Каждая функция может получать аргументы. (Некоторые в них не нуждаются, но об этом – позднее.)

Аргументы, как уже сказано выше, записываются в скобках через запятую.

• Диапазон, либо отдельная ячейка, то есть их координаты (наиболее распространенный случай). Подробнее см. здесь.
• Числовые значения.
• Текстовые значения. Записываются в кавычках.
• Логические. ИСТИНА (TRUE) или ЛОЖЬ (FALSE).
• Именованные ссылки.
• Массивы.
• Аргумены смешанных типов.

Простейшие функции

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

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

Диапазон в формуле выделен. Это означает, что если он определен неправильно, то его можно исправить, выделив нужный мышкой или введя его вручную.
После того, как всё сделано правильно, следует нажать клавишу Enter для подтверждения формулы.

Другой способ. Выделить нужный диапазон и нажать кнопку суммирования. Excel сам найдет свободную ячейку (справа или внизу, в зависимости от выделения) и вставит в неё сумму.

Если вам нужна не сумма, а другая функция, нажмите на стрелку кнопки . Появится список

,
из которого можно выбрать необходимое действие.

Перечисленное в списке осуществляется описанными ниже функциями. Аргументы совершенно одинаковые: число1, число2... | диапазон. Каждая из приведенных функций может обработать до 30 числовых аргументов. (В скобках указаны английские эквиваленты.)

• СУММ(аргументы) (SUM) – складывает аргументы и возвращает сумму.
  Единственная функция, использование которой просто с числами нелепо, так как формула СУММ(1,2,3) абсолютно идентична формуле =1+2+3.
• СРЗНАЧ(аргументы) (AVERAGE) – находит среднее арифметическое аргументов.
• СЧЕТ(аргументы) (COUNT) – подсчитывает количество чисел среди аргументов.
• МАКС(аргументы) (MAX) – находит наибольшее число среди аргументов.
• МИН(аргументы) (MIN) – находит наименьшее число среди аргументов.

Логические функции

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

ЕСЛИ (IF)

ЕСЛИ(логическое условие, значение_для_истинности, значение_для_ложности)

Таким образом, функция оценит логическое условие и, если оно истинно, то возвратит значение_для_истинности, а в противном случае – значение_для_ложности.

В качестве условия можно сослаться на ячейку (оценить её значение), а также записать выражение, которое может содержать сложное составное условие.

В случае сложного анализа, одна функция ЕСЛИ может принимать в качестве аргумента другую (вложение функции).

Пример: запись словом положительной оценки, выраженной числом (пусть это будет x).

ЕСЛИ(x=3, "удовл.", ЕСЛИ(x=4, "хор.", "отл."))

Обратите внимание, что здесь возникает логическая ошибка, так как если не 3 или 4, то 5. А если переменная содержит 2 или текст? Было бы логичнее учесть и это:

ЕСЛИ(x=3, "удовл.", ЕСЛИ(x=4, "хор.", ЕСЛИ(x=5, "отл.", "НЕ ОЦЕНКА!")))

Важно! Вложить друг в друга можно только до 7 выражений ЕСЛИ.

СЧЕТЕСЛИ (COUNTIF)

СЧЕТЕСЛИ(диапазон, условие)

Считает количество ячеек из указанного диапазона, отвечающих условию.

Если условием является равенство числу, то в качестве условия можно записать число.

Все остальные случаи записываются в кавычках.

Варианты: "=0", ">0", ">=0", "<0", "<=0", "<>0" (не равно), "0".

Крайне сложно находимая информация! В выражении условия для символьных данных можно использовать подстановочные символы (?, *, ~). Они означают:

• ? – любой одиночный символ (без учета регистра).
• * – любое сочетание символов (без учета регистра).
• ~ (тильда) – ставиться перед символами ?, * и ~ для их использования в прямом смысле.

Примеры:

• СЧЕТЕСЛИ(A:A, "=*н*") – количество ячеек в колонке A содержащих букву "н".
• СЧЕТЕСЛИ(A:A, "=н*") – ячеек начинающихся с буквы "н".
• СЧЕТЕСЛИ(A:A, "=*н?м*") – ячеек содержащих любые сочетания: ном, нем, ним, ням, и т.д.
• СЧЕТЕСЛИ(A:A, "=~~*") – ячеек начинающихся с тильды.
• СЧЕТЕСЛИ(A:A, "=*?") – ячеек заканчивающихся знаком вопроса.
• СЧЕТЕСЛИ(A:A, ">н") – ячеек начинающихся с символа "н" или большего. Да-да, условие отрабатывает как ">=".

СУММЕСЛИ (SUMIF)

СУММЕСЛИ(диапазон, условие)

Суммирует числовые значения ячеек, отвечающие условию.

Подробнее см. COUNTIF.

ИЛИ (OR)

ИЛИ(аргумент1[, аргумент2...])

Возвращает истину, если хотя бы один аргумент истенен.

Может обработать до 30 аргументов.

И (AND)

И(аргумент1[, аргумент2...])

Возвращает истину, если ВСЕ аргументы истинны.

Может обработать до 30 аргументов.

НЕ (NOT)

НЕ(выражение)

Возвращает истину, если выражение ложно и наоборот. То есть превращает логическое значение выражения в противоположное.

ИСТИНА (TRUE)

ИСТИНА()

Возвращает ложь. Может использоваться в условных функциях для ввода в ячейку логического значения. Пример см. ЛОЖЬ.

ЛОЖЬ (FALSE)

ЛОЖЬ()

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

ЕСЛИ(x=3, ИСТИНА(), ЛОЖЬ())

ЕПУСТО (ISBLANK)

ЕПУСТО(ячейка)

Если ячейка не содержит информации (пуста), то возвращается ИСТИНА. То есть, даже если в ячейке стоит пробел, то она не пуста!

Полный перечень IS-функций: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT.