Электронные таблицы: Excel, Функции
Условные обозначения: | - вводится одно из нескольких возможных значений, вертикальная черта является разделителем[ ] - необязательные параметры,
<информация, которая должна быть введена пользователем или программой>.
Введение
Информацию по соответствию встроенных функций Excel 2010 для английской и русской версий (и еще для 14 языков) можно найти здесь.
Сразу следует обратить внимание, что переводу не подвергаются только тригонометрические функции.
Excel содержит большое количество встроенных функций. Именно они и являются наиболее важной его частью.
Функции представляют собой подпрограммы, выполняющие некоторую обработку информации и всегда возвращающие результат этой обработки. Функция — классический пример вспомогательного алгоритма. Именно функции обладают наиболее выраженным свойством массовости, так как многократно используются при решении большого и разнообразного числа задач.
Кроме того, можно сказать, что функция — простейший пример «черного ящика»: они принимают некий набор правильных данных, как-то его обрабатывают и выдают на выход нечто, что можно заранее примерно предполагать.
Применение функций
Функции могут использоваться как самостоятельно, так и в составе сложных формул. В первом случае они должны начинаться со знака равенства.
С точки зрения правил записи (синтаксиса), функция состоит из имени (записывается заглавными буквами) и круглых скобок. Внутри скобок через запятую или точку с запятой(!) записываются аргументы.
Аргументы и их типы
Каждая функция может получать аргументы. (Некоторые в них не нуждаются, но об этом — позднее.)
Аргументы, как уже сказано выше, записываются в скобках через разделитель.
- Диапазон, либо отдельная ячейка, то есть их координаты
(наиболее распространенный случай). Подробнее см. здесь.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Простейшие функции
Некоторые математические действия используются чаще других, что позволило их выделить на отдельную кнопку инструментальной панели: 
.
На ней изображен знак суммирования (греческая буква сигма).
Нажатие на эту кнопку приведет к оценке содержимого ячеек сначала сверху, а затем слева от активной ячейки.
Если в них содержатся числа, то произойдет их выделение и ввод в ячейку формулы с диапазоном:
Диапазон в формуле выделен. Это означает, что если он определен неправильно, то его можно исправить, выделив нужный мышкой или введя его вручную. Если проще откорректировать единичные символы в координатах, то это следует делать, перейдя в режим редактирования (клавишей F2).
После того, как введены нужные координаты диапазона, следует нажать клавишу завершения редактирования, например, Enter для подтверждения формулы.
Другой способ. Выделить нужный диапазон и нажать кнопку суммирования. Excel сам найдет свободную ячейку (справа или внизу, в зависимости от выделения) и вставит в неё сумму.
Если вам нужна не сумма, а другая функция, нажмите на стрелку кнопки .
Появится список
,
из которого можно выбрать необходимое действие.
Перечисленное в списке осуществляется описанными ниже функциями. Аргументы совершенно одинаковые: «число1, число2... | диапазон». Каждая из приведенных функций может обработать до 30 числовых аргументов. (В скобках указаны английские эквиваленты.)
- СУММ(аргументы) (SUM) — складывает аргументы и возвращает сумму.
Единственная функция, использование которой просто с числами нелепо, так как формула СУММ(1,2,3) абсолютно идентична формуле =1+2+3. - СРЗНАЧ(аргументы) (AVERAGE) — находит среднее арифметическое аргументов.
- СЧЁТ(аргументы) (COUNT) — подсчитывает количество чисел среди аргументов.
- МАКС(аргументы) (MAX) — находит наибольшее число среди аргументов.
- МИН(аргументы) (MIN) — находит наименьшее число среди аргументов.
СУММПРОИЗВ (SUMPRODUCT)
Данная функция не относится к простейшим, но чрезвычайно важна при подсчете строк по нескольким условиям. Такая задача довольно жизненна, а решить её логичными способами не получится. Важным моментом является её полная работоспособность в версиях до 2007 года.
Альтернативная технология, основанная на тривиальных формулах. Её неоспоримым достоинством будет понятность и простая воспроизводимость начинающими пользователями. Заодно она позволит оценить трудоемкость работы с СУММПРОИЗВ.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Вернемся к СУММПРОИЗВ. Сразу оговоримся, что здесь рассмотрена лишь малая толика информации, так как функция «Возвращает сумму произведений значений из нескольких одинаковых массивов». Это ценно, но именно такой аспект обсуждать мы не будем, он описан везде. Просто упомянем идею: есть столбцы с ценой и количеством, обработав которых можно вывести итоговую стоимость без введения суммы цены товара для строки.
Синтаксис чрезвычайно примитивен (как и Справка о функции):
=СУММПРОИВ(массив1;[массив2];[массив3];...),
но вряд ли у вас получится сделать то, что требуется нам.
Отметьте первое главное условие: обрабатываются несколько массивов идентичного размера, например, A:A и B:B, то есть два столбца целиком; либо A1:A5 и B1:B5, то есть по 5 ячеек.
Все остальные обстоятельства будут соответствовать приведенному выше примеру.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Логические функции
К данной группе следует отнести все функции, обрабатывающие логические данные, либо возвращающие логический результат.
С другой стороны, ниже в том числе приведены наиболее важные функции, в основе работы которых лежит оценка (логическая) значений на соответствие условию.
По большому счету можно говорить о том, что без использования логических функций невозможна запись очень большого числа сложных формул. При этом их синтаксис значительно отличается от того, который задействуется во всех языках программирования.
ЕСЛИ (IF)
ЕСЛИ(логическое условие, значение_для_истинности, значение_для_ложности)
Таким образом, функция оценит логическое условие и, если оно истинно, то возвратит значение_для_истинности, а в противном случае — значение_для_ложности. Каждое из этих значений может быть как результатом расчетов, так и введено вручную.
В качестве условия можно сослаться на ячейку (оценить её значение), а также записать выражение, которое может содержать сложное составное условие, например, содержащее координаты, дополнительные функции и элементы ручного ввода.
В случае сложного анализа, одна функция ЕСЛИ может принимать в качестве аргумента другую (вложение функции).
Пример: запись словом положительной оценки, выраженной числом (пусть это будет x).
ЕСЛИ(x=3; "удовл."; ЕСЛИ(x=4; "хор."; "отл."))
Последовательно «прочитаем» формулу.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Также принципиальным является то, что логика работы именно данной функции лежит в основе всех последующих, содержащих в названии слово «ЕСЛИ».
Функция лишь выводит результат, никак не помогая пользователю обнаружить качественные различия. Для получения зрительных эффектов потребуется дополнительно обратиться к условному форматированию.
Используем еще один интересный визуальный эффект.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
....
СЧЁТЕСЛИ (COUNTIF)
СЧЁТЕСЛИ(диапазон; условие)
Диапазон не может содержать несколько значений!
Сосчитывается количество ячеек из указанного диапазона, отвечающих условию. Если условием является равенство числу, то в качестве условия можно записать просто само число. Все остальные случаи записываются в кавычках.
Варианты: "=0" (или 0), ">0", ">=0", "<0", "<=0", "<>0" (не равно).
Крайне сложно находимая информация!
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Примеры стандартного использования и описанного выше:
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Обратите внимание, что пустая ячейка не равна нулю!
СЧЁТЕСЛИМН (COUNTIFS)
Считает ячейки, соответствующие множеству условий.
Синтаксис:
СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2]; ... )
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
....
Подробнее см. СЧЁТЕСЛИ и СУММЕСЛИМН (SUMIFS), так как всё остальное работает идентично.
СУММЕСЛИ (SUMIF)
СУММЕСЛИ(диапазон; условие[; диапазон суммирования])
Суммирует числовые значения ячеек, отвечающие единичному условию.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Примеры:
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Подробнее см. СЧЁТЕСЛИ, так как всё остальное работает идентично.
СУММЕСЛИМН (SUMIFS)
Предназначена для суммирования числовых данных, отобранных с помощью нескольких критериев.
Синтаксис:
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; ...)
Смысл аргументов:
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Обратите внимание, что логическое значение ячейки ИСТИНА будет воспринято равным 1!
Подробнее см. СЧЁТЕСЛИ, так как всё остальное работает идентично.
ИЛИ (OR)
ИЛИ(аргумент1[, аргумент2...])
Возвращает истину, если хотя бы один аргумент истинен.
Может обработать до 30 аргументов.
И (AND)
И(аргумент1[, аргумент2...])
Возвращает истину, если ВСЕ аргументы истинны.
Может обработать до 30 аргументов.
НЕ (NOT)
НЕ(выражение)
Возвращает истину, если выражение ложно и наоборот. То есть превращает логическое значение выражения в противоположное.
ИСТИНА (TRUE)
ИСТИНА()
Возвращает ложь. Может использоваться в условных функциях для ввода в ячейку логического значения. Пример см. ЛОЖЬ.
ЛОЖЬ (FALSE)
ЛОЖЬ()
Возвращает истину. Может использоваться в условных функциях для ввода в ячейку логического значения. Пример:
ЕСЛИ(x=3, ИСТИНА(), ЛОЖЬ())
ЕПУСТО (ISBLANK)
ЕПУСТО(ячейка)
Если ячейка не содержит информации (пуста), то возвращается ИСТИНА. То есть, даже если в ячейке стоит пробел результат работы функции, выводящий пустую строку, то она не пуста! Таким образом, данная функция определяет, что в ячейке не содержится совсем ничего.
Полный перечень IS-функций: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT.
Работа с датами и временем
Весьма ограниченный блок, как по востребованности, так и по возможностям программы. Но он становится камнем преткновения во всех ситуациях когда к нему приходится обращаться.
Для начала уточним, что в Excel выделяются 2 типа данных, относимых к категории: Дата и Время. Это естественным образом вытекает из требования форматирования ячеек, то есть того варианта вывода, который удобен для нас с вами.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
....
Сводка функций от Microsoft.
Функция ДАТА (DATE)
Позволяет «собрать» значение, представляющее собой дату из трех чисел, соответствующих году, месяцу и дню.
Синтаксис: ДАТА(год;месяц;день)
Следует обратить внимание на два момента.
- Год должен указываться в четырехзначном формате от 1900. Любое указание до 1899 включительно означает не год, а прибавление лет к нулевой дате!
- День и месяц могут быть любым числом (положительным и отрицательным), а не тем, что указано в подсказке. Они будут обработаны арифметически, то есть избыток будет прибавлен к дате, а отрицательное число приведет к вычитанию и переходу в предыдущий период.
Мощности имеющихся специализированных функций вполне хватает для решения любых задач, но их список всё же пополняется.
Для развлечения можно предложить определение числа исполнившихся лет на конкретную дату, что имеет высокую юридическую значимость для любой страны. Прекрасная тренировка навыка и безумная работа для компенсации отсутствующего.
....
Функция СЕГОДНЯ (TODAY)
Возвращает текущую дату. Таким образом, при открытии таблицы она всегда будет актуальна.
Является одним из основных источников ошибок при несознательном использовании. Многие пользователи (не только начинающие) полагают, что она зафиксируется на тот момент, когда будет введена, но эффект будет иным. Для ввода текущей даты надо вводить её в явном виде, а не пользоваться данной функцией.
Её необходимость определяется использованием внутри формул, когда надо вычислить время, прошедшее на данный момент с определенной даты. Либо наоборот, время, оставшееся до определенной даты.
Функция ТДАТА (NOW) полностью идентична, но возвращает дату вместе с текущим временем
....
Строковые функции
Моей личной точкой зрения всегда было и останется мнение о почти полном отсутствии в процессе всех видов обучения обсуждения данной тематики. Вместе с тем, именно эти знания будут востребованы всегда.
Столь категоричное высказывание неслучайно. Оно основано на опыте множества ситуаций с большими таблицами, содержащими текстовые столбцы с ошибками, исправление которых вручную выливалось в сотни человеко-часов. Использование текстовых функций позволяло проделать ту же работу за несколько минут.
Порядок описания функций тоже возникает закономерно
НАЙТИ (FIND)
Одна из самых важных функций, позволяющая найти в ячейке номер позиции (символ), с которого начинается искомый текст.
Поиск является регистрозависимым, то есть если искать строчную букву, то заглавная будет пропущена. Отсюда вытекает косвенный вывод, что данная функция работает с максимальной скоростью за счет отсутствия преобразования текста. Таким образом, именно ей следует отдавать предпочтение при всех обстоятельствах.
Синтаксис:
=НАЙТИ(что ищем;строка в которой ищем;[начать поиск с позиции]).
Возвращается номер символа начальной строки от начала строки-источника вне зависимости от указания начала позиции поиска.
Использование подстановочных знаков в функции невозможно!
Примеры:
1. =НАЙТИ("м";A1).
2. =НАЙТИ("м";"мимика") (вернет 1).
3. =НАЙТИ("м";"мимика";2) (вернет 3).
Если искомый фрагмент найден не будет, то возвратится ошибка #ЗНАЧ! Отсюда неизбежным становится возможность использование функции только для обработки однозначных ситуаций.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
Самостоятельный поиск подобного рода может присутствовать, но чаще он нужен для использования в функции ПСТР.
ПРОПИСН (UPPER)
Так как функция НАЙТИ учитывает регистр, можно произвести предварительную обработку исходной строки функцией ПРОПИСН(<строка>), в результате чего все включенные в неё буквы станут прописными. Таким образом, поиск прописной буквы будет положительным для любого варианта её написания.
Уместным будет предположение, что поиск в обработанных подобным образом значениях, проводимый без(!) учета регистра, будет работать быстрее за счет того, что массовый перевод более эффективен. Но это целесообразно лишь при больших объемах.
Обратим внимание также на существование обратной функции СТРОЧН (LOWER), переводящей символы в нижний регистр (строчные).
ПОИСК (SEARCH)
Полностью идентична функции НАЙТИ, за двумя исключениями, важнейшим из которых является то, что регистр букв не учитывается.
Вторым отличием будет возможность использования подстановочных знаков.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
ПСТР (MID)
Практически всё сказанное про строки выше нужно для расширенного извлечения фрагментов из текста.
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
....
ЗАМЕНИТЬ (REPLACE)
Доступ к этим материалам предоставляется только зарегистрированным пользователям!
....
Тригонометрические функции
Единственной причиной обсуждения данной категории является то, что Excel использует для действий исключительно радианную меру угла, вынуждая пользователей буквально «вставать на уши».
Поскольку я не ставлю перед собой задачу полного рассмотрения темы, приведу ссылку на соответствующую станицу на сайте Microsoft.
Первое, на что следует обратить внимание, что подавляющее большинство функций не переводится, а используется их написание латиницей.
Следующим немаловажным моментом является неспособность программы вывести на экран значение угла с минутами и секундами (10°5'10"). Единственным вариантом решения остается написание пользовательской функции на VBA. Она настолько востребована, что Microsoft приводит вариант на своем сайте в качестве примера. Но его существенным недостатком является некорректная обработка десятичной доли секунд. Сам текст описания изобилует ошибками.
В обратной функции прямо сказано, что она не будет работать, если вы ошибетесь хоть чуток.
ПИ (PI)
Возвращает число 3,14159265358979 — математическую константу π (пи) с точностью до 15 цифр.
Здесь она приведена, так как является основой работы с радианами — базовой угловой единицей в Excel. Пи радиан соответствует угловой величине в 180°.
ГРАДУСЫ (DEGREES)
Преобразует угловой размер, выраженный в радианах, в угловые градусы.
В качестве аргумента передается значения угла в радианах!
Обратной функцией является РАДИАНЫ (RADIANS), востребованная для перевода «нормального» значения в систему измерений, понятную программе.
Альтернативно можно использовать формулу Угол°*ПИ()/180, результатом которой также будет значение в радианах. То есть, например, TAN(45*ПИ()/180) полностью эквивалентно TAN(РАДИАНЫ(45)), но будет вычисляться чуть дольше.
Дополнительно. Градусная мера для использования в программе может быть выражена только десятичным числом!
SIN
На примере данной функции будет разобран весь основной массив.
Как уже было упомянуто, все тригонометрические функции в Excel используют радианную меру угловых величин.
Таким образом необходимо использовать синтаксис SIN(<угол в радианах>), что далеко не всегда удобно.
Чтобы воспользоваться стандартными угловыми градусами, придется дополнять формулу:
SIN(РАДИАНЫ(<угол в градусах>)).
Arc-функции
В программе используется ряд обратных функций, извлекающих из значения тригонометрической функции угол (в радианах!), которому они соответствуют. Все они начинаются с буквы «A»: ACOS, ACOSH, ACOT, ACOTH, ASIN, ASINH, ATAN, ATAN2, ATANH.
Таким образом, например, зная, что тангенс угла равен 0,5, можно вычислить значение угла:
=ГРАДУСЫ(ATAN(0,5)).
Результатом будет 26,56505118 (угловых градусов).
Таким образом, использование этого класса функций может облегчить изучение тригонометрии и проводить проверку вычислений.
См. также Перечень некоторых функций.
Copyright © 1993–2024 Мацкявичюс Д.А. Все права защищены.
Никакая часть сайта не может быть воспроизведена никаким способом без письменного разрешения правообладателя и явной ссылки на данный ресурс.
