Технология вычисления функций в EXCEL

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

=B1+B2+B3+C4+C5+D2

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

=СУММ (B1: B3; C4: C5; D2)

Если сравнить запись формул, то видно, что двоеточием здесь обозначается блок ячеек. Запятой разделяются аргументы функций. Использование блоков ячеек, или областей, в качестве аргументов для функций целесообразно, поскольку оно, во-первых, нагляднее, а во вторых, при такой записи программе проще учитывать изменения на рабочем листе. Например нужно подсчитать сумму чисел в ячейках с А1 по А4. Это можно записать так: =СУММ (А1; А2; А3; А4) Другой способ: =СУММ (А1: А4).

 

Состав функций

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

В таблице 1 приведено несколько примеров.

Таблица 1

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
СЕГОДНЯ ()	Дата и время
СТОЛБЕЦ (диапазон)	Число столбцов в диапазоне
MEДИАНА (число1, число2.)	Медиана значений, определенных в списке аргументов
ПЛТ (ставка, кпер, пс)	Возвращает сумму периодического платежа дляаннуитета на основе постоянства сумм платежей и постоянства процентной ставки

Ввод функций

Простейший способ ввода функции – щелкнуть на кнопке Вставить функцию в строке формул и выбрать нужную в диалоговом окне Мастер функций – шаг 1 из 2 ( Рисунок 1 ).

Рис. 1

 

Если не знают, какую функцию надо использовать, вводят краткое описание ее действий в поле Поиск функции и щелкают на кнопке Найти.

Выбрав функцию, нужно щелкнуть на кнопке ОК. Появится диалоговое окно Аргументы функции ( Рисунок 2 ), позволяющее ввести аргументы функции.

В этом окне будут отображаться их текущие значения.

Рис. 2

 

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

Вложенные функции

Формула может состоять из одиночной функции, как =ТДАТА () или

=ОТБР (123, 65). Однако часто в одной функции, помимо ссылок на ячейки идиапазоны, в качестве аргумента используются другие функции. Например: =АВS (СУММ(С2: С20)).

Здесь функция СУММ суммирует значения в ячейках с С2 по С20. Это значение становится аргументом для функции ABS, возвращающей абсолютное значение суммы. Можно создавать сложные функции, содержащие до семи уровней вложенных функций.

Использование функции ЕСЛИ

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

Первый аргумент функции ЕСЛИ – логическое выражение может

иметь значения Истина или Ложь. Такие значения имеют, например, выражения В9< 6 – значение ячейки В9 меньше 6, a Q10< > R15 – Q10 не равно R15.

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

Например: =ЕСЛИ((R11+S11)> 0; (R11+S11)*Q4; «Нет данных»).

Формула помещает текст «Нет данных» в ячейку, если сумма значений

в ячейках R11 и S11 равна или меньше 0. Если нет – ячейка будет содержать значение число.

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

Обзор функций Excel

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

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

Функции Excel собраны по категориям.

Надо помнить, что Excel обладает гораздо большим разнообразием функций.

Информационные функции (см. таблицу 2) позволяют узнать о типе данных, хранящихся в ячейках.

Таблица 2

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
ЯЧЕЙКА	Информация, определяемая пользователем о данной ячейке (значения, формат, тип переменной или цвет)
СЧИТАТЬ ПУСТОТЫ	Количество пустых ячеек в выбранном диапазоне
ИНФОРМ	Точная информация о компьютере (размер оперативной памяти, оборудование и пр.)
ЕПУСТО	Истина, если указанная ячейка пуста
ЕЧИСЛО	Истина, если указанная ячейка содержит число
ЕТЕКСТ	Истина, если указанная ячейка содержит текст
ТИП	Число, представляющее тип значений, содержащихся в указанной ячейке

 

Важная группы функций это логические функции (см. Таблица 3).

Таблица 3

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
И	Истина, если значение всех аргументов Истина, в противном случае – Ложь
ЕСЛИ	Первое значение, если проверяемый аргумент равен Истине, и второе значение в противном случае
НЕ	Истина, если аргумент имеет значение Ложь, и Ложь, если аргумент имеет значение Истину
ИЛИ	Истина, если хотя бы один из аргументов имеет значение истина, и Ложь, если ни один не является истина

 

Функции даты и времени (см. Таблицу 4) позволяют создавать формулы, производящие вычисления в зависимости от времени и даты а также выполнять вычисления с датами. Excel использует специальные числа (значения даты) для хранения и оперирования датами. Для преобразования обычных дат в значения, используемые в Excel, можно использовать функцию ДАТА. Функции ДЕНЬ, ГОД и МЕСЯЦ преобразуют значения дат в понятные человеку.

Таблица 4

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
ДАТА	Значение общеиспользуемой записи даты в значения, используемые в Excel
ДЕНЬ	Целочисленная переменная, соответствующая дню месяца (подобные функции: ГОД, МЕСЯЦ, ЧАС, МИНУТЫ И СЕКУНДЫ)
ТДАТА	Значение даты и времени на PC. Используйте СЕГОДНЯ для возвращения только даты и ВРЕМЯ – только времени
ДЕНЬНЕД	День недели
РАБДЕНЬ	Дата следующего рабочего дня после указанного
ДОЛЯГОДА	Десятичная дробь, означающая часть года, равную интервалу между датами. Удобно использовать для расчета премий сотрудникам

 

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

Таблица 5

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
БИЗВЛЕЧЬ	Указанное значение из списка
ЧИСЛОСТ	Количество столбцов в диапазоне
ИНДЕКС	Значение указанной ячейки в диапазоне или адрес ячейки
АДРЕС	Возвращает ссылку на одну ячейку в рабочем листе в виде текста
ПОИСКПОЗ	Расположение предмета поиска в диапазоне (но не значения)
СМЕЩ	Ссылка на диапазон или ячейку, отстоящую на заданное число строк или столбцов от указанного диапазона

Математические и тригонометрические функции (см. Таблицу 6) выполняют математические расчеты.

Таблица 6

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
ABS	Модуль аргумента
COS	Косинус аргумента. В Excel присутствует весь набор тригонометрических функций
ФАКТР	Факториал аргумента
LOG	Логарифм аргумента (используют LN для получения натурального логарифма)
ПИ	Число ПИ
ПРОИЗВЕД	Произведение всех аргументов (можно указать диапазон ячеек, как в случае с функцией СУММ)
РИМСКОЕ	Римские числа
ОКРУГЛ	Округленное значение до указанного числа или знака после запятой
ЗНАК	1 – если аргумент положителен, 0 – если равен нулю, -1 – если аргумент отрицателен
КОРЕНЬ	Квадратный корень аргумента
СУМПРОИЗВ	Перемножает соответствующие элементы в двух или более массивах и затем суммирует результаты
ЦЕЛОЕ	Целая часть аргумента – отбрасывается часть после десятичной точки

 

Текстовые функции (см. Таблицу 7) приводят текстовые переменные к нужному виду или выводят текст на основании числовых аргументов.

Таблица 7

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
ПЕЧСИМВ	В тексте удаляются все непечатаемые символы
РУБЛЬ	Превращает числовое значение в текст, в денежном формате, с двумя знаками после десятичной точки
ЛЕВСИМВ	Указанное количество символов слева в строке (ПСТР и ПРАВСИМВ выбирают символы из центра или с правой стороны вводимой строки)
ДЛСТР	Число символов в строке
СТРОЧН	Текст строки переводится в нижний регистр
ТЕКСТ	Превращает цифры в соответствующий текст, оформленный по указанному образцу
СЖПРОБЕЛЫ	Удаляет лишние пробелы (одиночные пробелы между словами сохранены)

Поскольку главная задача Excel – помощь при ведении дел, в программе содержатся сотни функций для расчета долгов, инвестиций и подобных экономических величин (см. Таблицу 8).

Таблица 8

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
ACCRINT	Вычисление процентных ставок по процентам к уплате
ПС	Возвращает приведенную стоимость инвестиций
СКИДКА	Возвращает норму скидки для ценных бумаг
БС	Прибыли от инвестиций при постоянной процентной ставке
ИНОРМА	Возвращает процентную ставку для полностью инвестированных ценных бумаг
ПРПЛТ	Сумма платежей процентов по инвестициям или долгам
ДОХОД	Доход от ценных бумаг
ПЛТ	Возвращает сумму периодического платежа
ЧИСТНЗ	Чистая текущая стоимость инвестиций
ДОХОД	Начисления по процентным ставкам

 

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

Таблица 9

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
СРЗНАЧ	Среднее арифметическое аргументов
ХИ2ТЕСТ	Тест независимости данных, основанный на хи-квадрат распределении
ДОВЕРИТ	Доверительный интервал для популяции
СЧЕТ	Число ячеек в указанном диапазоне, содержащих цифровые значения
ФИШЕР	Преобразование Фишера
ЧАСТОТА	Массив, содержащий пределы, в которых изменяются значения
НАИБОЛЬШИЙ	Значение в специальных последовательностях (для определения, например, третьих по объему продаж СD в третьем квартале). НАИМЕНЬШИЙ дает противоположный результат
МАКС	Наибольшее значение в последовательности. МИН дает наименьшее значение
МЕДИАНА	Медиана аргумента
MORE	Наиболее часто встречающееся значение
НОРМРАСП	Нормальное распределение
СТАНДОТКЛОН	Оценка стандартного отклонения

 

Многие люди использует Excel в качестве менеджера баз данных простых файлов (без ссылок), сохраняя данные в строках и столбцах рабочего листа (столбец является полем, а строка – записью). В категории управления баз данными находится более десяти функций (см. таблицу 10). В большинстве случаев им требуется указать диапазон, содержащий базу данных, поля и критерии отбора.

Таблица 10

ФУНКЦИЯ	ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
СЧЕТЕСЛИ	Количество записей, соответствующих критерию
ДМАКС	Максимальное число среди соответствующих критерию
СУММЕСЛИ	Сумма значений в поле для всех записей, соответствующих критерию

Работа с формулами

Чтобы ввести в клетку формулу, надо в строке ввода набрать знак =, а затем саму формулу. Формула может содержать:

- число;

- адрес клетки, блока данных;

- функцию;

- операцию вычисления: +, -, *, /;

- скобки.

 

Чтобы ввести формулу:

1. Выделите клетку

2. Наберите =

3. Наберите формулу.

4. Нажмите Ввод или в строке ввода щелкните на кнопке ввода .

 

Exсel выводит текст формулы в строке формул, а результат вычислений помещает в ячейку. Чтобы увидеть формулы в самих клетках таблицы, надо командой меню Сервис® Параметры® Вид поставить флажок-галочку в окошке Формулы.

Для того чтобы быстро вставить клетку или блок в формулу необходимо:

1. набрать текст формулы до того места, где должна располагаться ссылка на клетку или интервал. Например, если хотим вычислить сумму ячеек с А1 по А4, сначала введем =сумм(

2. выделить мышью клетки с А1 по А4, в строке формул появится =сумм(А1: А4

3. набрать оставшуюся часть формулы, т.е. скобку, затем нажмите Ввод, в результате получится готовая формула =сумм(А1: А4).

В формуле можно указать сразу несколько интервалов, эти интервалы следует перечислять, отделяя точкой с запятой, например =сумм(А1: А4; С1: С4). В таком случае сначала надо выделить блок А1: А4, затем ввести точку с запятой и выделить второй блок С1: С4.

Для копирования формул используется общий механизм копирования информации через буфер. Пусть в клетке А4 находится формула, вычисляющая сумму. Скопируем эту формулу в клетку В4. Выполним следующее:

1. Выделим клетку А4.

2. Исполним команду меню Правка® Копировать.

3. Поместим курсор в клетку В4.

4. Исполним команду меню Правка® Вставить. В клетку В4 вставится формула =сумм(В1: В4), ее можно увидеть в строке ввода, а в самой клетке В4 будет результат вычисления суммы.

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

Просмотреть названия этих функций можно, воспользовавшись кнопкой Мастер функций панели инструментов .

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

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

 

Адреса ячеек можно выделить в исходной таблице, щелкнув на кнопку в правой части поля ввода . Нажмите OK.

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

- СУММ(Список) – статистическая функция определения суммы всех числовых значений в Списке. Пример: СУММ(В5: Е5).

- СРЗНАЧ(Список) – статистическая функция определения среднего арифметического значения всех перечисленных в списке величин.

- МАКС(Список) – статистическая функция, результатом которой является максимальное значение в указанном списке.

- ЕСЛИ(Условие; Истинно; Ложно) – логическая функция, проверяющая на истинность заданное логическое Условие. Если Условие выполняется, то результатом функции является значение аргумента Истинно (например, некоторое число или формула). Если Условие не выполняется, то результатом функции становится значение аргумента Ложно (например, другое число или другая формула). Пример: ЕСЛИ(В4< 100; 100; 200).

⇐ Предыдущая123456Следующая ⇒

Поделиться: