Ции Excel 20 07. Условные вычисления.

 

Цель работы:

 

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

 

для сравнения отдельных данных или выражений при решении фи-

 

нансово-экономических задач.

 

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

 

нять для анализа данных с использованием условий. Например, для

 

вычисления суммы значений, попадающих в интервал, заданный

 

текстовой строкой или числами, используется функцию

 

СУММЕСЛИ. А для подсчета количества ячеек внутри диапазона,

 

удовлетворяющих заданному критерию используется СЧЁТЕСЛИ.

 

Часто выбор формулы для вычислений зависит от каких-либо усло-

 

вий. Например, при расчете торговой скидки могут использоваться раз-

 

личные формулы в зависимости от размера покупки. Для выполнения та-

 

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

 

ментов значений вставляются соответствующие формулы.

 

Синтаксис функции: ЕСЛИ(А;В;С),

 

где A – логическое выражение, правильность которого следует прове-

 

рить; В– значение, если логическое выражение истинно; C – значение, если

 

логическое выражение ложно.

 

Следующая формула возвращает значение 10, если значение в ячейке

 

А1 больше 3, а в противном случае – 20:        =ЕСЛИ(А1>3;10;20).

 

Действие функции: функция Е СЛИ, з аписанная в ячейку таблицы,

 

выполняется с л едующим образом: ес ли ус л овие А истинно, то зн ачение

 

д анной ячейки определит В, в противном случае С.

 

 

50

 

 

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

 

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

 

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

 

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

 

функции. Например, =ЕСЛИ(СУММ(А1:А3)=30;А10;""), здесь при невы-

 

полнении условия функция возвращает пустую строку вместо 0.

 

Аргумент A (логическое выражение функции ЕСЛИ) может содер-

 

жать текстовое значение. Например, =ЕСЛИ(А1="Динамо";10;290). Эта

 

формула возвращает значение 10, если ячейка А1 содержит строку "Дина-

 

мо", и 290, если в ней находится любое другое значение. Совпадение меж-

 

ду сравниваемыми текстовыми значениями должно быть точным.

 

Логические выражения строятся с помощью операций отношения (<,

 

>, <= (меньше или равно), >= (больше или равно), =, <> (не равно)) и логи-

 

ческих операций (логическое И, логическое ИЛИ, логическое отрицание

 

НЕ). Результатом вычисления логического выражения являются логиче-

 

ские значения ИСТИНА или ЛО ЖЬ.

 

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

 

имеют синтаксис:

 

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

 

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

 

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

 

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

 

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

 

ниями, массивами или ссылками на ячейки, содержащие логические значе-

 

ния.

 

Иногда бывает очень трудно решить логическую задачу только с по-

 

мощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях

 

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

 

 

51

 

 

уровней вложения функций ЕСЛИ. Например, в следующей формуле ис-

 

пользуются три функции ЕСЛИ:

 

=ЕСЛИ(А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), которая имеет следующий синтак-

 

сис: =ЕПУСТО(значение), Аргумент значение может быть ссылкой на

 

ячейку или диапазон. Если значение ссылается на пустую ячейку или диа-

 

пазон, функция возвращает логическое значение ИСТИНА, в противном

 

случае ЛОЖЬ.

 

 

52

 

 

В ы борочноесумм ирование. Иногда необходимо суммировать не

 

весь диапазон, а только ячейки, отвечающие некоторым условиям (крите-

 

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

 

A – диапазон вычисляемых ячеек; В – критерий в форме числа, выражения

 

или текста, определяющего суммируемые ячейки; С – фактические ячейки

 

для суммирования. В тех случаях, когда диапазон вычисляемых ячеек и

 

диапазон фактических ячеек для суммирования совпадают, аргумент С

 

можно не указывать.

 

Можно суммировать значения, отвечающие заданному условию. Напри-

 

мер, в таблице на рис. 4.1 суммированы только продажи по фирмам, сред-

 

няя цена продукции у которых не меньше 20 тыс. руб.

 

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

 

 

53

 

 

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

 

значений, удовлетворяющих критерию В.

 

Функции СУММЕСЛИМН и СЧЕТЕСЛИМН работают аналогично

 

классическим функциям СУММЕСЛИ и СЧЕТЕСЛИ, но умеют проверять

 

не одно, а несколько условий (до 128 условий).

 

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

 

ет не сумму, а среднее арифметическое.

 

Функция Е С ЛИОШИБКА возвращает заданное вами значение или со-

 

общение, если результатом вычисления формулы является значение ошиб-

 

ки, иначе возвращает результат вычисления формулы:

 

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

 

З адание 1. Рассчитать подоходный налог на доходы физических

 

лиц, если необлагаемая база для лиц, имеющих доход меньше 20000 руб-

 

лей, равна 400 руб., в противном случае размер налога равен 13% от вели-

 

чины дохода.

 

Р ешение.

 

A                 B                                    C

 

1                         Налоги н а д оходы ф и з и ческих лиц 2 размер налога                 13%

необлагаемая база

 

3

для лиц, имеющих доход    меньше

 

400руб.

 

20000 рублей

 

Ф амилия

 

5 Белоус С.В.

 

Доход                                Налог

 

20050    ЕСЛИ(В5<20000; (В5-$B$3)*$B$2; B5*$B$3)

 

54

 

 

 

6 Котощук Л.В.    15000 7 Харитонов Б.М.  199550 8 Шевченко А.В.   24900

ЕСЛИ(В6<20000; (В6-$B$3)*$B$2; B6*$B$3) ЕСЛИ(В7<20000; (В7-$B$3)*$B$2; B7*$B$3)

ЕСЛИ(В8<20000; (В8-$B$3)*$B$2; B8*$B$3)

 

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

 

А                     В

 

1 Ценная бумага В ыручка 2        Акция 1             1000 3        Акция 2             1500 4        Акция 1               500 5        Акция 3               800 6        Акция 1             2000 7        Акция 1             2400 8        Акция 3             3900

 

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

 

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).

 

 

55

 

 

4) С помощью функции ранжирования расположить в порядке возрас-

 

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

 

5) Написать формулу, осуществляющую автоматическую вставку со-

 

общения «лидер на рынке ценных бумаг» для того типа акций, который

 

имеет наибольший рейтинг.

 

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

 

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

 

Требуется просуммировать все заказы, которые менеджер Григорь-

 

ев реализовал для магазина "Копейка".

 

Р е ш ение: Если бы в задаче было только одно условие (все заказы

 

Григорьева или все заказы в "Копейку"), то задача решалась бы при помо-

 

щи встроенной функции Excel СУММЕСЛИ. Но в данном случае имеют-

 

ся два условия, а не одно, поэтому этот способ не подходит. 56

 

 

Добавим к таблице еще один столбец, который будет служить свое-

 

образным индикатором: если заказ был в "Копейку" и от Григорьева, то в

 

ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо

 

ввести в этот столбец очень простая:

 

=(A2="Копейка")*(B2="Григорьев")

 

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что

 

для Excel равносильно 1 и 0. Таким образом, в результате умножения двух

 

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

 

выполняются. Теперь стоимости продаж осталось умножить на значения

 

получившегося столбца и просуммировать полученные суммы:

 

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

 

 

57

 

 

З адание 4.

 

1. Создать таблицу по образцу:

 

Продажихолодильников

 

М о д ель   Страна-изготовитель Stinol        Россия

Sharp       Таиланд Samsung   Южная Корея Bosh          Испания

LG           Южная Корея Daewoo    Южная Корея Electrolux  Швеция Whiripool  США

Атлант    Россия Indezit       Франция Ariston      Франция

DeLongy  Италия

В ес, к г Цена, $ Количество 78       310                 18 69       750                 10 56       450                 13 52       419                 17 69       600                   8 71       840                   4 75       680                 12 80       790                   9 76       300                 25 81       420                 14 59       415                 10

60      395              15

 

 

2. Добавьте столбец «Сумма» и подсчитайте сумму от продаж холо-

 

дильников. Примените к числовым значениям денежный формат соответ-

 

ствующего наименования там, где это необходимо.

 

3. Подсчитать количество холодильников, произведенных каждой

 

страной. (Данные оформить в виде новой таблицы).

 

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

 

дильников, произведенных каждой страной.

 

5. Если оборот больше 10000 $, то написать сообщение о предостав-

 

ляемой скидке 2%.

 

6. Подсчитать число проданных холодильников, вес которых больше

 

их среднего веса.

 

7. Добавьте столбцы «Новая цена» и «Новая сумма». В столбце «Но-

 

вая цена» рассчитайте новую цену, используя функцию ЕСЛИ. Известно,

 

что производители России и Южной Кореи снизили цены на 10%, а произ-

 

водители Франции и Испании подняли цены на 16% (единая формула вво-

 

дится в первую ячейку столбца «Новая цена»). 58

 

 

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 59

 

 

месяцев, то – 1,5 раза. Получить ведомость уценки товара, которая должна

 

включать следующую информацию: наименование товара, срок хранения,

 

цена товара до уценки, цена товара после уценки.

 

З адание 8.

 

В сельскохозяйственном кооперативе на сборе урожая помидоров

 

работают 10 сезонных рабочих. Оплата труда производится по количеству

 

собранных овощей. Дневная норма сбора составляет k килограммов. Сбор

 

1 кг помидоров стоит m рублей. Сбор каждого килограмма сверх нормы

 

оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабо-

 

чий за собранный урожай?

 

З адание 9.

 

Автоматизировать начисление стипендии студентам (группа – 10

 

человек) по итогам летней сессии. Количество экзаменов – 5, баллы – от

 

2,5 до 5. Стипендия начисляется в размере МРОТ (600 руб.), если все экза-

 

мены сданы с оценкой не ниже 3. При сдаче всех экзаменов с оценками не

 

ниже 4 баллов выплачивается надбавка 20%, не ниже 5 баллов – надбавка

 

50%.

 

 

60