Математические функции в Excel: особенности и примеры
Главным достоинством данного редактора являются математические функции в Excel. При работе с таблицами, диапазонами и массивами количество времени, потраченное на вычисление тех или иных значений, снижается в разы при умении пользоваться встроенными возможностями. В зависимости от категории математической функции можно получить результат при арифметических действиях, построить график или диаграмму, использовать относительные и абсолютные ссылки.
Excel: общие сведения
Программный продукт позволяет выполнять расчеты с различными типами данных и управлять электронными таблицами. Под последними понимается средство, необходимое для обработки и анализа оцифрованных данных посредством компьютера.
Важная особенность электронной таблицы – автоматический пересчет значений при изменении данных в ячейках. Математические функции в Excel позволяют не только получить искомое, но и построить графики и диаграммы.
Книга – тот файл, с которым работает программный комплекс. Она состоит из листов. К каждому из них можно обратиться при необходимости посредством встроенных функций или ручного ввода формулы.
Основа рабочего листа – таблица, состоящая из строк и столбцов. Их перекрестие составляет ячейка, куда вводятся данные или формулы. Строки названы арабскими цифрами, а столбцы – латинскими буквами. Считалось, что рабочий лист бесконечен в обе стороны, однако это не так. Он содержит 65536 строк и 256 столбцов. По другим данным в рабочем листе содержатся 16384 столбцов и 1048576 строк. Каждой ячейке присваивается уникальный адрес:А5.
Использование ссылок
При работе с Excel можно применять в работе различные виды ссылок. Начинающим пользователям доступны простейшие из них. Важно научиться использовать все форматы в своей работе.
Существуют:
- простые;
- ссылки на другой лист;
- абсолютные;
- относительные ссылки.
Простые адреса используются чаще всего. Простые ссылки могут быть выражены следующим образом:
- пересечение столбца и строки (А4);
- массив ячеек по столбцу А со строки 5 до 20 (А5:А20);
- диапазон клеток по строке 5 со столбца В до R (В5:R5);
- все ячейки строки (10:10);
- все клетки в диапазоне с 10 по 15 строку (10:15);
- по аналогии обозначаются и столбцы: В:В, В:К;
- все ячейки диапазона с А5 до С4 (А5:С4).
Следующий формат адресов: ссылки на другой лист. Оформляется это следующим образом: Лист2!А4:С6. Подобный адрес вставляется в любую функцию.
Абсолютные и относительные ссылки
Особого внимания требуют такие форматы адресов.
Выделяют:
- абсолютные;
- относительные;
- смешанные ссылки.
Под относительными адресами понимают указанные диапазоны или конкретные ячейки, которые при копировании формулы и ее последующей вставке изменяются автоматически. К примеру, необходимо суммировать в столбце С несколько значений. Формула будет выглядеть следующим образом: =сумм(С5:С9). Если допустить, что подобных столбцов несколько, и в каждом нужно найти сумму, проще скопировать формулу и вставить в нужные клетки. После проделанных манипуляций можно заметить, что диапазоны поменялись автоматически.
Под абсолютными ссылками понимаются ячейки, которые в процессе копирования не меняют своего вида. К примеру, имеется клетка А5. Она участвует в формуле вычисления суммы нескольких значений. Чтобы она не изменилась ни при одном копировании, перед обозначением столбца и строки ставят знак $. Абсолютная ссылка будет выглядеть следующим образом: $А$5.
Основные математические функции Excel предпочитают использовать при больших объемах данных смешанные адреса. В таком формате можно зафиксировать только столбец или строку. К примеру, $С5 или С$5. В первом случае не меняется название столбца, во втором – строки.
Ссылки формата R1C1
В новых версиях Excel адреса ячеек видоизменились. Некоторые не могут понять, в чем разница между А1 и R[-1]C[-5].
Разработчики приводят несколько примеров подобных адресов:
- относительный адрес строки, расположенной на 3 позиции выше указанной: R[-3];
- абсолютная ссылка на ячейку, содержащуюся на 10-й строке 10-го столбца: R10C10;
- относительный адрес на ячейку, расположенную на 5 позиций выше в активном столбце (где прописывается формула): R[-5]C;
- абсолютная ссылка на текущую клетку, где прописывается формула: R;
- относительная ссылка на ячейку, расположенную на 8 строк правее и 5 строк ниже активной: R[5]C[8].
Категории математических функций
При выпадающем списке в программе на вкладке «Формулы» содержится 11 групп. В их число входят и математические функции в Excel, которые условно разделяются на следующие категории. Рассмотрим основные. Арифметические операции:
- СУММ: складывает необходимые значения.
- ПРОИЗВЕД: находит произведение заданных чисел или содержимого ячеек.
- ЦЕЛОЕ: необходимо для нахождения целой части.
- СТЕПЕНЬ: возводит число в заданную степень.
- КОРЕНЬ: извлекает корень из содержимого ячейки или заданного вручную числа и т. д.
Также можно выделить тригонометрические функции:
- SIN: находит синус от заданного значения.
- ASIN: необходима для вычисления арксинуса числа.
- LN: находит натуральный логарифм.
- EXP: возводит число Е в заданную пользователем степень и др.
Округление по различным критериям:
- ОКРВВЕРХ: округляет значение до ближайшего целого.
- ОКРУГЛВВЕРХ: находит число, округленное до ближайшего целого по модулю.
- НЕЧЕТ: функция, позволяющая округлить до нечетного числа. Положительное – в большую сторону, отрицательное – в меньшую.
- ОКРУГЛ: необходима для нахождения результата округления с заданием количества десятичных разрядов и др.
Для работы с векторами и матрицами:
- СУММПРОИЗВ: функция, необходимая для возвращения суммы массивов.
- СУММВРАЗН: при наличии 2 диапазонов возвращает сумму квадратов разностей.
- МОБР: функция, необходимая для получения обратной матрицы.
- МОПРЕД: требуется для нахождения определителя матрицы и т. д.
Применение математических функций
Математические функции в Excel могут вводиться тремя способами:
- вручную;
- через панель инструментов;
- посредством диалогового окна «Вставка функции».
В первом случае пользователь вручную вводит формулу на основе своих знаний и умений в специальной строке или конкретной ячейке. Начинающим рекомендуется прибегнуть ко второму и третьему вариантам.
С помощью панели инструментов нужно перейти на вкладку «Формулы» и выбрать необходимую группу формул: логическую, статистическую, математическую и т. д. В выпадающем списке пользователь использует потребовавшуюся функцию. Когда появляется диалоговое окно «Аргументы и функции» проставляются необходимые значения и подтверждаются сделанные манипуляции.
Чтобы воспользоваться третьим вариантом, нужно нажать кнопку Fx или CTRL+F3. На панели инструментов также имеется «Мастер подстановки» на вкладке «Формулы». Послы выполнения одного из перечисленных действий появляется окно, в котором нужно выбрать соответствующую группу, а после – нужную функцию. Далее последует окно «Аргументы и функции», в котором выполняются необходимые манипуляции.
Некоторых пугают особенности вызова встроенных математических функций Excel. Достаточно проделать все шаги представленного алгоритма и в таблице отобразится искомое решение.
Использование встроенных функций
Популярны среди пользователей встроенные математические функции в Excel. Их синтаксис состоит из 2 частей: имени и аргументов. Последних может быть один или несколько. Обязательно формула начинается со знака «=». В противном случае Excel выдаст ошибку о неверном вводе функции.
К примеру, имеется формула «=СУММ(А2:М10)». В таком случае речь пойдет о суммировании всех значений диапазона А2:М10. Аргументы обязательно заключаются в скобки и указываются без пробелов. Писать имя функции с заглавной или строчной буквы – дело каждого. Для программного продукта это не играет никакой роли.
Если указана формула «=СУММ(А2;С7;М10)», это означает, что суммируются только 3 указанные ячейки. В состав функции могут входить до 30 элементов в аргументе.
Графики математических функций
Построить графики математических функций в Excel достаточно просто. Важные условия: правильно задать условия и выбрать подходящий тип отображения.
Пример. Построить график функции для оператора sin. Шаг приращения = 0,5.
Примеры математических функций в Excel
Задание 1. Использовать функцию «суммесли». Данный оператор суммирует значения, если они удовлетворяют определенному условию и находятся в нужном диапазоне.
Задание 2. Использовать функцию «abs». Она находит модуль от заданного числа. В диалоговом окне указывают лишь один аргумент в виде одной ячейки. Диапазон участвовать в данном операторе не может.
Задание 3. Применить функцию «степень». Суть заключается в возведении заданного числа в указанную пользователем степень. У функции 2 аргумента.
Задание 4. Получить римскую запись от числа, написанного арабскими цифрами. Для этого понадобится формула «Римское».
Задание на закрепление материала
Провести эксперименты со всеми вариантами, где возможно использование математических функций в Excel. Можно начать с простых, а потом усложнять запись со вставкой формулы в уже имеющейся.