Формулы и функции

Выполнение расчетов с помощью формул

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

Формула — это специальный инструмент, предназначенный для расчетов, вычислений и анализа данных. Формула начинается со знака «=», после чего следуют операнды и операторы. В формулу могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, которое выводится как результат вычисления формулы по уже имеющимся данным. Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически. Например, формула =А1+В1 обеспечивает сложение чисел, хранящихся в ячейках А1 и В1, а формула =А1*5 — умножение числа, хранящегося в ячейке А1, на 5. При изменении исходных значений, входящих в формулу, результат пересчитывается. В процессе ввода формулы она отображается как в самой ячейке, так и в строке формул.

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

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

Абсолютные ссылки в формулах используются для указания фиксированного адреса ячейки. При перемещении или копировании формулы абсолютные ссылки не изменяются.

В абсолютных ссылках перед именем столбца и номером строки ставится знак доллара (например, $А$ 1). Так, при копировании формулы из активной ячейки С1, содержащей абсолютные ссылки на ячейки $А$ 1 и $В$ 1, значения столбцов и строк в формуле не изменятся.

Выполнение расчетов с помощью функций

Более сложные вычисления в таблицах MS Excel осуществляются с помощью специальных функций. Функция в MS Excel — это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат.

Наиболее распространенные функции MS Excel являются краткой записью часто используемых формул. Например запись функции =СУММ (А1:А4) аналогична записи =А1+А2+А3+А4. Каждая функция состоит из имени и аргумента. В нашем примере СУММ — это имя функции, а А1: А4 — аргумент. Аргумент заключается в круглые скобки.

Т.о., в состав каждой функции в обязательном порядке входят следующие элементы: знак равенства «=», имя или название (примеры имен — СУММ, СРЗНАЧ, СЧЕТ, МАКС и т. д.), а также аргумент (либо несколько аргументов). Аргументами функций могут быть числа, ссылки, формулы, текст, логические величины и др.

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

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

Также для вызова мастера функций можно воспользоваться комбинацией клавиш Shift и F3.

Перечислим основные функции MS Excel:

• 1. Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.
• 2. Дата и время. Функция «Дата и время» позволяет работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ.
• 3. Математические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.
• 4. Статистические функции позволяют выполнять статистический анализ данных. Например, можно определить среднее значение и дисперсию по выборке.
• 5. Ссылки и массивы позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве. Например, для поиска значения в строке таблицы используется функция ГПР.
• 6. Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.
• 7. Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.
• 8. Функция Проверка свойств и значений предназначена для определения данных, хранимых в ячейке. Эта функция проверяет значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ.