Математические функции Excel

Наиболее распространенными и часто используемыми являются функции категории «Математические». Условно функции этой категории можно классифицировать следующим по группам:

§ функции для выполнения арифметических операций: СУММ, СУММКВ, ПРОИЗВЕД, ЦЕЛОЕ, ОСТАТ, СТЕПЕНЬ, КОРЕНЬ, и др.;

§ тригонометрические и обратные тригонометрические функции: SIN, COS, АSIN, ACOS, ATAN, LN, LOG, EXP и др.;

§ функции, использующие различные критерии округления: ОКРВВЕРХ, ОКРВНИЗ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОКРУГЛ, НЕЧЕТ, ЧЕТН;

§ функции для работы с векторами и матрицами: СУММПРОИЗВ, СУММКВРАЗН, СУММРАЗНКВ, СУММСУММКВ МОБР, МОПРЕД, МУМНОЖ.

Полную информацию обо всех функциях этой категории можно получить в справочной системе MS Excel.

Рассмотрим те функции, которые при расчетах используются наиболее часто.

Функции для выполнения арифметических операций

§ СУММ(число1; число2; . . . ; число n) - вычисляет сумму аргументов.

Число1, число2, ... , число n — от 1 до 30 аргументов, для которых требуется определить итог или сумму.

Например,

Функция =СУММ(B5:В10), означает, что нужно сложить содержимое ячеек с B5 до B10,

а функция =СУММ(B5:В10; А11) означает, что складывается содержимое ячеек с B5 до B10 с содержимым ячейки А11.

Поскольку функцию СУММ применяют чаще других функций, для ее вызова на системном пиктографическом меню (СПИ) предусмотрена специальная кнопка Автосуммирование .

§ СУММКВ(число1; число2; . . . ; число n)–вычисляет сумму квадратов аргументов.

§ ПРОИЗВЕД(число1; число2; . . . ; число n) – возвращает произведение аргументов.

§ СТЕПЕНЬ(число; степень) –– возвращает результат возведения аргумента число в указанную степень.

§ КОРЕНЬ(число) –– возвращает значение квадратного корня из аргумента число.

§ СУММЕСЛИ(диапазон; критерий; диапазон_суммирования)– суммирует ячейки, заданные указанным условием.

Диапазон – диапазон анализируемых ячеек.

Критерий – критерий в форме числа, выражения или текста, определяющий суммируемые ячейки.

Диапазон_суммирования – фактические ячейки для суммирования.

На рисунке 2 представлена таблица, в которой вычисляется общая сумма премии высокооплачиваемых работников (тех, у кого оклад больше 700 тыс. руб.). Для этого анализируется столбец Оклад (диапазон ячеек А2:А7), и суммируются те ячейки из столбца Премия (диапазон ячеек С2:С7), для которых значения в соответствующих ячейках столбца Оклад больше 700. Результат помещен в ячейку С8.

Рис.2 – Пример использования функции СУММЕСЛИ

Функции округления

§ ОКРУГЛ(число; число разрядов)–округляет число до указанного количества десятичных разрядов.

ОКРУГЛ(82,93; 0) вернет результат 83 (округление до целых).

ОКРУГЛ(82,93; 1) вернет результат 82,9 (округление до десятых).

ОКРУГЛ(82,93; -1) вернет результат 80 (округление до десятков).

§ ОКРУГЛВЕРХ(число; число разрядов)

§ ОКРУГЛВНИЗ (число; число разрядов) подобны функции ОКРУГЛ за исключением того, что округление производится либо с избытком (ОКРУГЛВЕРХ) либо с недостатком (ОКРУГЛВНИЗ).

Например

ОКРУГЛВЕРХ(82,93; -1) вернет результат 90 (округление до десятков с избытком).

ОКРУГЛВНИЗ(82,93; -1) вернет результат 80 (округление до десятков с недостатком).

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

§ ОКРВВЕРХ(число; точность) и ОКРВНИЗ (число; точность).

Эти функции выполняют округление с избытком (ОКРВВЕРХ)или недостатком (ОКРВНИЗ)до ближайшего целого, кратного заданной точности.

Например,

если в значениях цен необходимо избежать рублей, а товар стоит 8293 рубля, функция =ОКРВВЕРХ(8293;10) округлит цену до 8300 руб., а функция =ОКРВНИЗ(8293;10) округлит цену до 8290 руб. И в том и в другом случае точность округления равна 10 руб.

§ НЕЧЕТ(число) и ЧЕТН(число) округляют аргумент число до ближайшего нечетного или четного значения.

Функции для работы с векторами

Сюда можно отнести функции:

СУММПРОИЗВ, СУММКВРАЗН, СУММРАЗНКВ, СУММСУММКВ.

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

§ СУММПРОИЗВ( массив1; массив2; . . . ; массив n)

Массив1; массив2; . . . ; массив n – от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить.

На рисунке 3 показано, как в ячейке С9 с использованием функции СУММПРОИЗВ, может быть вычислена общая сумма премии по ведомости.

Рис.3 – Пример использования функции СУММПРОИЗВ

§ СУММКВРАЗН(массив_x;массив_y)

массив_x – первый массив или интервал значений.

массив_y – второй массив или интервал значений.

§ СУММРАЗНКВ(массив_x;массив_y) возвращает сумму разностей квадратов соответствующих значений в двух массивах.

§ СУММСУММКВ(массив_x;массив_y) возвращает сумму квадратов соответствующих элементов двух массивов.

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

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

Функций категории «Логические» всего шесть: ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ. Они достаточно понятны и просты в использовании.

§ ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)– используется для проверки значений выражений или формул и организации переходов в зависимости от результатов этой проверки.

Возвращает аргумент значение_если истина, если логическое выражение при вычислении дает значение ИСТИНА, и аргумент значение_если ложь, если аргумент логическое выражение при вычислении дает значение ЛОЖЬ. В качестве возвращаемых значений могут использоваться числовые значения, ссылки на ячейки, формулы или текст.

Пример

Предположим, что специалисту присваивается категория только в том случае, если он набрал более 100 баллов при сдаче квалификационного экзамена, в противном случае категория не присваивается. Реализуем это условие функцией ЕСЛИ:=ЕСЛИ(B4<100;"--";"I категория”)

Если в ячейке В4 содержится например, число 35, что меньше 50, результатом функции ЕСЛИ будет знак “--“ . Если в ячейке В4 содержится например, число 75, что больше 50, результатом функции ЕСЛИ будет текст “I категория”.

При организации сложных вычислений до 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов.

§ И(логическое_значение1; логическое_значение2; ....; логич_значение N)

§ ИЛИ(логическое_значение1; логическое_значение2; ....; логич_ значениеN)

Функции И и ИЛИ могут содержать до 30 проверяемых условий.

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

Расширим шкалу присвоения категории специалистам:

=ЕСЛИ(B4<50;"--";ЕСЛИ(И(B4>=50;B4<=100);"I категория” ;"Высшая категория ")).

Если ячейка В4 содержит число 75, то в качестве результата будет выведен текст « I категория», если в ячейке В4 содержится число 150 то результатом является текст «Высшая категория», если же в ячейке В4 содержится число меньше 50, то результатом будет знак “--“ .

§ НЕ(логическое_значение)Эта функция меняет на противоположное логическое значение своего аргумента и используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.

НЕ(8>2) возвратит значение ЛОЖЬ.

НЕ(8<2) возвратит значение ИСТИНА.

§ ИСТИНА –возвращаетлогическое значение ИСТИНА.

§ ЛОЖЬ- возвращаетлогическое значение ЛОЖЬ.