Лабораторная работа №5. Табличный процессор Microsoft Excel 2007

1. ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

1.10. Примеры записи и использования некоторых функций Excel

Некоторые математические функции представлены в табл. 3. Обозначение «арг» ‑ аргумент функции: константа, адрес/имя ячейки или диапазона.

Таблица 3. Некоторые математические функции

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

ЕСЛИ (Условие ; Значение_если_истина ; Значение_если_ложь )

Условие ‑ любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10 = 100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае — значение ЛОЖЬ.

Значение_если_истина – значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА.

Значение_если_ложь – значение, которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ.

Пример 2.

Введем в ячейки В2 и В3 числа 1500 и 500, соответственно, в ячейки С2 и С3 – числа 900, 900. В ячейке D2, введем формулу ЕСЛИ. Имя функции заносится в строку формул, а диалоговое окно-палитра функции изменяется, давая возможность ввести аргумент функции (Рис. 10).

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

Если протянуть формулу на ячейку D3, то в ней соответственно появится значение ОК.

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

Рис. 10 Палитра функции ЕСЛИ с введенными условиями
и действиями на выполнение

Пример 3.

В ячейки A, B, C строки 5 вводим значения трех сторон треугольника. В ячейке D5 требуется определить, не является ли треугольник равнобедренным. Для этого потребуется заполнить формулами три ячейки:

F5: =ЕСЛИ(B5=C5;"равнобедренный";"разносторонний")

E5: =ЕСЛИ(A5=C5; "равнобедренный"; F5)

D5: =ЕСЛИ(A5=B5; "равносторонний"; E5).

Логические функции И / ИЛИ используются как самостоятельно, так и для построения сложных условий в других функциях. Функция И дает результат ИСТИНА только в том случае, если все ее аргументы имеют истинное значение, во всех остальных случаях её значение – ЛОЖЬ. Функция ИЛИ – наоборот, дает значение ИСТИНА, если хотя бы один из её аргументов имеет истинное значение, только, если все аргументы ложны, то ИЛИ дает ЛОЖЬ. Примеры записи и работы описанных функций показаны на рис. 11.

Рис. 11 Пример записи и результаты работы функции И /ИЛИ

С применением функций И / ИЛИ решение задачи из Примера 3 об определении типа треугольника возможно с помощью только одной функции ЕСЛИ. Пример такого решения показан на рис. 12.

Рис. 12 Пример записи и результат работы функции ЕСЛИ
с использованием функций И /ИЛИ

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

Таблица 4.Некоторые функции подсчета и суммирования

Функция	Назначение
КВАДРОТКЛ	Возвращает сумму квадратов отклонений
СУММ	Суммирует свои аргументы
СУММЕСЛИ	Суммирует значения в ячейках, выбираемых по заданному критерию
СУММКВ	Возвращает сумму квадратов аргументов
СЧЁТ	Подсчитывает количество чисел в списке аргументов
СЧЁТЕСЛИ	Подсчитывает в диапазоне количество непустых ячеек, удовлетворяющих заданному критерию
СЧЁТ3	Подсчитывает количество непустых значений в списке аргументов
СЧИТАТЬПУСТОТЫ	Подсчитывает количество пустых ячеек в заданном диапазоне

На рис. 13 представлен фрагмент листа с данными. В столбце С показаны результаты работы функции СЧЁТЕСЛИ для разных условий:

=СЧЁТЕСЛИ(A2:A5;"яблоки")	Количество ячеек, содержащих текст «яблоки» в первом столбце таблицы
=СЧЁТЕСЛИ(A2:A5;A4)	Количество ячеек, содержащих текст «персики» в первом столбце таблицы
=СЧЁТЕСЛИ(B2:B5;">55")	Количество ячеек со значением больше 55 во втором столбце таблицы
=СЧЁТЕСЛИ(B2:B5;"<>"&B4)	Количество ячеек со значением не равным 75 во втором столбце таблицы

Рис. 13 Результаты работы по формуле функции СЧЁТЕСЛИ

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

Интервал – это интервал вычисляемых ячеек.

Критерий – это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как 32, ²32², ²>² , ²вишня².

Сумм_интервал – это фактические ячейки для суммирования. Ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют аргументу критерий.

Пример 4.

Пусть ячейки А1:А4 содержат следующие величины: 10000 руб.; 20000 руб.; 30000 руб.; 40000 руб. Это стоимости четырех ПК. Пусть ячейки В1:В4 содержат следующие комиссионные при продаже соответствующих ПК: 700 руб.; 1440 руб.; 2100 руб.; 2880 руб.

Тогда функция

СУММЕСЛИ (А1:А4;²>16000²;В1:В4) даст значение 6420 руб. – это сумма комиссионных (столбец В) от продажи ПК, стоимость которых (столбец А) выше 16000 руб.

Функции генерации случайных чисел используются в разных задачах, например, при компьютерном моделировании обработки измеряемых данных; для создания исходных данных при разработке и отладке программ и т.д. В Excel имеется две функции этого типа: СЛЧИС() и СЛУЧМЕЖДУ(нижн_граница;верхн_граница). Рис. 14 демонстрирует пример их записи и результатов работы.

СЛЧИС() – функция возвращает равномерно распределенное случайное вещественное число, которое большее или равно 0 и меньше 1. Функция не имеет аргументов. Новое случайное вещественное число возвращается при каждом вычислении листа. Если требуется применить функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, можно ввести в строке формулы =СЛЧИС(), а затем нажать клавишу < F9 >, чтобы заменить формулу случайным числом.

(нижн_граница;верхн_граница) ‑ Возвращает случайное целое число, находящееся в диапазоне между двумя заданными числами. При каждом вычислении листа возвращается новое случайное целое число. Нижн_граница ‑ наименьшее целое число, которое возвращает функция СЛУЧМЕЖДУ. Верхн_граница ‑ наибольшее целое число, которое возвращает функция СЛУЧМЕЖДУ.

а) б)

Рис. 14 Вычисление случайных чисел: запись функций и их результаты