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

Цель работы:

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 – диапазон вычисляемых ячеек; В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки; С – фактические ячейки для суммирования. В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для суммирования совпадают, аргумент С можно не указывать. Можно суммировать значения, отвечающие заданному условию. Напри-мер, в таблице на рис. 3.1 суммированы только продажи по фирмам, средняя цена продукции у которых не меньше 20 тыс. руб.

 

 

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

 

Функция СЧЕТЕСЛИ(А;В), подсчитывает в диапазоне A количество значений, удовлетворяющих критерию В. Функции СУММЕСЛИМН и СЧЕТЕСЛИМН работают аналогично классическим функциям СУММЕСЛИ и СЧЕТЕСЛИ, но умеют проверять не одно, а несколько условий (до 128 условий). Функция СРЗНАЧЕСЛИМН аналогична двум предыдущим, но считает не сумму, а среднее арифметическое. Функция ЕСЛИОШИБКА возвращает заданное вами значение или сообщение, если результатом вычисления формулы является значение ошибки, иначе возвращает результат вычисления формулы:

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

 

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

 

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

 

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

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. Формула, которую надо ввести в этот столбец очень простая: =(A2="Копейка")*(B2="Григорьев") Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, в результате умножения двух выражений, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:

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

 

Задание 4.

1. Создать таблицу по образцу: Продажи холодильников

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

3. Подсчитать количество холодильников, произведенных каждой страной. (Данные оформить в виде новой таблицы).

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

 

5. Если оборот больше 10000 $, то написать сообщение о предоставляемой скидке 2%. 6. Подсчитать число проданных холодильников, вес которых больше их среднего веса.

7. Добавьте столбцы «Новая цена» и «Новая сумма». В столбце «Новая цена» рассчитайте новую цену, используя функцию ЕСЛИ. Известно, что производители России и Южной Кореи снизили цены на 10%, а производители Франции и Испании подняли цены на 16% (единая формула вводится в первую ячейку столбца «Новая цена»).

8. Рассчитайте, какова будет новая сумма при том же количестве про-данного товара.

9. Используя условный фильтр, отметьте в столбце «Вес» ячейки, значение которых больше 70, желтым фоном и жирным красным шрифтом, а ячейки, значение которых меньше или равно 60, синим фоном и жирным желтым шрифтом.

10. Сохраните файл под именем «Холодильники.xlsx».

 

Задания для самостоятельной работы:

Решить задачи путем построения электронной таблицы. Исходные данные для заполнения таблицы подобрать самостоятельно (не менее 10 строк).

Задание 5. Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 5%; если объем до 10000, то 2%; если выше 10000, то 1,5%. Введите в ячейку А10 текст «Объем сделки», в ячейку А11 – «Размер вознаграждения». В ячейку В10 введите объем сделки, а в В11 – формулу, вычисляющую размер вознаграждения.

 

Задание 6. В доме проживает 10 жильцов. Подсчитать, сколько каждый из них должен платить за электроэнергию и определить суммарную плату для всех жильцов. Известно, что 1 кВт/ч электроэнергии стоит m рублей, а некоторые жильцы имеют 50% скидку при оплате.

Задание 7. Торговый склад производит уценку хранящейся на складе продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то – 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.

 

Задание 8. В сельскохозяйственном кооперативе на сборе урожая помидоров работают 10 сезонных рабочих. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет k килограммов. Сбор 1 кг помидоров стоит m рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?

Задание 9. Автоматизировать начисление стипендии студентам (группа – 10 человек) по итогам летней сессии. Количество экзаменов – 5, баллы – от 2,5 до 5. Стипендия начисляется в размере МРОТ (600 руб.), если все экзамены сданы с оценкой не ниже 3. При сдаче всех экзаменов с оценками не ниже 4 баллов выплачивается надбавка 20%, не ниже 5 баллов – надбавка 50%.