Лабораторная работа. Логические выражения и функции Excel 2007. Условные вычисления.. Теоретический материал

Лабораторная работа

Логические выражения и функции Excel 2007. Условные вычисления.

Цель работы:

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

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

Теоретический материал

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

Синтаксис функции: ЕСЛИ(А;В;С), где A – логическое выражение, правильность которого следует проверить; В– значение, если логическое выражение истинно; C – значение, если логическое выражение ложно. Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае – 20: =ЕСЛИ(А1>3;10;20). Действие функции: функция ЕСЛИ, записанная в ячейку таблицы, выполняется следующим образом: если условие А истинно, то значение данной ячейки определит В, в противном случае С.

В и С могут быть числами, текстами или формулами.

В функции ЕСЛИ можно использовать текстовые аргументы:

=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал").

В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, =ЕСЛИ(СУММ(А1:А3)=30;А10;""), здесь при невыполнении условия функция возвращает пустую строку вместо 0.

Аргумент A (логическое выражение функции ЕСЛИ) может содержать текстовое значение. Например, =ЕСЛИ(А1="Динамо";10;290). Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным.

Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или равно), =, <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические значения ИСТИНА или ЛОЖЬ.

Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

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

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

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

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

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

=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))

Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда".

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА()

=ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Выдать кредит", если выражение в ячейке А1 имеет значение ИСТИНА:

=ЕСЛИ(А1=ИСТИНА();"Выдать кредит";"Не выдавать кредит"), в противном случае формула возвратит "Не выдавать кредит".

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис: =ЕПУСТО(значение), Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.

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

Можно суммировать значения, отвечающие заданному условию. Например, в таблице на рис.1 суммированы только продажи по фирмам, средняя цена продукции у которых не меньше 20 тыс. руб.

Рис.1 Выборочное суммирование

Функция СЧЕТЕСЛИ(А;В), подсчитывает в диапазоне A количество значений, удовлетворяющих критерию В.

Функции СУММЕСЛИМН и СЧЕТЕСЛИМН работают аналогично классическим функциям СУММЕСЛИ и СЧЕТЕСЛИ, но умеют проверять не одно, а несколько условий (до 128 условий).

Функция СРЗНАЧЕСЛИМН аналогична двум предыдущим, но считает не сумму, а среднее арифметическое.

Функция ЕСЛИОШИБКА возвращает заданное вами значение или сообщение, если результатом вычисления формулы является значение ошибки, иначе возвращает результат вычисления формулы:

Рис. 2. Проверка вычислений с помощью функции ЕСЛИОШИБКА.

Задание 1. Рассчитать подоходный налог на доходы физических лиц, если необлагаемая база для лиц, имеющих доход меньше 20000 рублей, равна 400 руб., в противном случае размер налога равен 13% от величины дохода.

Решение.

Задание 2: Создать на листе следующую таблицу

Ответить на следующие вопросы:

1) Как изменится выручка, если курс акций 1 упадет на 2%, а курс акций 3 поднимется на 5%.

2) Как изменится выручка, если курс акций 1 упадет на 2%, а курс акций 2 и курс акций 3 поднимется на 5%.

3) Вычислить общую выручку по каждой ценной бумаге: «Акция 1», «Акция 2», «Акция 3». (Результаты оформить в виде новой таблицы).

Указание. Для суммирования ячеек по заданному условию используется функция СУММЕСЛИ, имеющая следующий формат:

=СУММЕСЛИ(Диапазон; Критерий; Диапазон_суммирования)

АргументДиапазон - это интервал вычисляемых ячеек. Аргумент Критерий представляет собой число, выражение или текст, который определяет условия суммирования ячеек. АргументДиапазон_суммирования- это фактические ячейки для суммирования.

Согласно данному выше описанию функции СУММЕСЛИ, выручка по ценной бумаге "Акция 1" может быть вычислена с помощью следующей формулы: =СУММЕСЛИ(А2:А6; "Акция 1"; B2:B6) или = СУММЕСЛИ(А2:А6; А4; B2:B6).

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

5) Написать формулу, осуществляющую автоматическую вставку сообщения «лидер на рынке ценных бумаг» для того типа акций, который имеет наибольший рейтинг.

Задание 3. Выборочное суммирование по двум критериям.

Имеем таблицу по продажам следующего вида:

Требуется просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Решение: Если бы в задаче было только одно условие (все заказы Григорьева или все заказы в «Копейку»), то задача решалась бы помощи встроенной функции Excel СУММЕСЛИ.Но в данном случае имеются два условия, а не одно, поэтому этот способ не подходит.

Добавим к таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая: =(А2="Копейка^м)*(В2='Тригорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, в результате умножения двух выражений, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:

Рис. 3. Выборочное суммирование по двум критериям

12 Следующая ⇒