Задачи на использование функции ЕСЛИ()

Пояснение к задачам 7.1.1–7.1.9.

В этих задачах предполагается два варианта заполнения одной и той же ячейки (см. подразд. 6.2).

7.1.1. В табл. 7.1.1 представлена ведомость покупок, в которой надо заполнить три последние графы. Оформите эту ведомость в Excel. "Налог1, р." по каждому товару равен 12 % от суммы покупки, если она меньше порога в 30 000 р., иначе – 30 %. "Налог2, р." исчисляется аналогично, но процентные ставки соответственно 9 и 40 %. В отдельные ячейки введите текущий курс доллара (для заполнения графы "Сумма, р."), порог стоимости покупки для изменения налогового процента и сами налоговые проценты. Разместите рядом с этими константами подписи и присвойте ячейкам с константами имена.

Таблица 7.1.1

7.1.2. На рис. 7.1.2 изображена личная карточка квартиросъемщика для расчета квартплаты. Отметьте знаком "+" льготы Петрова, введите его номинальную квартплату и составьте формулу, которая рассчитывает реальную квартплату по максимальной из льгот. Проверьте, как выбирается льготный процент при разных комбинациях льгот.

Подсказка

"Квартплата с учетом скидок" = "Номинальная квартплата" * (1 – МАКС((если в строке "Инвалиды" стоит "+", то 60 %, в противном случае 0) ; (если в строке "Участник войны" стоит "+", то 50 %, в противном случае 0) ; ...)

Рис. 7.1.2

7.1.3. Оформите ведомость, представленную в табл. 7.1.3. Заполните пустые столбцы, подведите итоги. Недостающие константы введите или рассчитайте в отдельных ячейках.

Таблица 7.1.3

Формулы для расчета:

· "Премия" = 20 % от "Оклад";

· "Начислено" = "Оклад" + "Премия" + 0,1 % от "Оклад" за каждый год работы;

· "Подоходный налог" = 13 % от "Начислено";

· "На руки" = "Начислено" – "Подоходный налог";

· "Благосостояние" = "хорошее", если "На руки" > "Среднее на рукипо фирме", иначе – "низкое".

7.1.4. В табл. 7.1.4 приведены характеристики квартир построенного дома. Средняя стоимость 1 м² площади составляет $500.

Подсказка

"Реальная цена" = "средняя цена" * (1 + (если "Этаж" = 1, то –20 %, в противном случае 0) + (если "Этаж" = 8, то –10 %, в противном случае 0) + ...)

7.1.5. В связи с праздником фирма выделяет некоторую сумму на материальную помощь малоимущим и премии. Принципы распределения денег:

1. Малооплачиваемым (зарплата не превышает 5 МРОТ) – доплата до 5 МРОТ.

2. Если выделенной суммы не хватает, то фактическая доплата уменьшается пропорционально планируемым максимальным суммам так, чтобы уложиться в выделенную сумму.

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

Составьте таблицу Excel, которая распределяла бы любую сумму в соответствии с этими принципами.

Подсказка

На рис. 7.1.1 приведен макет распределения информации. Формулы в пустых графах должны обеспечить следующие расчеты:

· "Доплата максимальная" малооплачиваемым: если "Зарплата" <= (5 * "МРОТ"), то (5 * "МРОТ" – "Зарплата"), иначе 0.

· "Помощь фактическая": если "Всего Доплата максимальная" <= "Сумма", то "Доплата максимальная", иначе ("Сумма" / "Всего Доплата максимальная" ) * "Доплата максимальная".

· "Премия": если "Всего Доплата фактическая" <= "Сумма", то ("Сумма" –"Всего Доплата фактическая" ) * ("Зарплата" / "Всего зарплата" ), иначе 0.

· "Итого на руки": "Доплата фактическая" + "Премия".

Рис. 7.1.1

7.1.6. Соревнование лесорубов состоит из двух этапов: рубка дерева и метание топора.

На первом этапе очки начисляются так: если участник не уложился в 10‑минутный норматив, то он получает 0 очков, в противном случае он получает количество очков, равное 600 – количество секунд, потраченное на выполнение задания.

На втором этапе начисляется 1 очко за каждый сантиметр, превышающий 5 метров, и 0, если расстояние меньше 5 метров. Оформите ведомость, как показано на рис. 7.1.2, внесите в нее результаты соревнований (5–10 фамилий) и отсортируйте участников по набранным очкам (для сортировки поставьте курсор в столбец Сумма очков и нажмите кнопку Сортировка по убыванию). В заголовок попробуйте добавить текущую дату (функция СЕГОДНЯ() из категории "Дата и время", функции СЦЕПИТЬ() и ТЕКСТ() из категории "Текстовые" Мастера функций. В функции ТЕКСТ() первый аргумент – СЕГОДНЯ(), второй – форматная строка "ДД.ММ.ГГ". См. также подразд. 1.15).

Рис. 7.1.2

7.1.7. На рис. 7.1.3 приведены результаты голосования (в процентах от числа избирателей) по 8-и округам за кандидатов 4-х партий. В каждом округе проходит тот кандидат, за которого отдан максимум голосов. Используя функцию МАКС(), разместите в столбце F максимальный процент голосов в каждом округе. В строке 10 должно быть показано, сколько кандидатов от той или иной партии прошло по совокупности всех округов.

Рис. 7.1.3

Подсказка

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

7.1.8.В пункте проката автомобилей расчет с клиентами производится по схеме, которая показана на рис. 7.1.4.

Рис. 7.1.4

В момент взятия автомобиля в компьютер вводятся дата и время в графе "Взято с" и предполагаемое время возврата в графе "Оплачено по". В графах "Оплачено" находятся формулы для предварительного расчета времени и стоимости проката. В момент возврата в графу "Дата возврата" вводятся фактические дата и время возврата. В графе "Разница" – формулы для вычисления разницы в часах и деньгах между уже оплаченными и фактическими значениями. В графе "Доплата/возврат" подводится итог: если клиент вернул авто раньше, ему возвращают часть денег с "Коэффициентом возврата", если вернул авто позже – он доплачивает недостающую сумму с "Коэффициентом доплаты".

Оформите в Excel расчет по этой схеме.

Подсказка

1. В таблице "Учет проката" для графы "Тип авто" удобно создать подстановочный список возможных значений с помощью команды Данные ® Проверка…, вкладка Параметры ("Тип данных" – Список, "Источник" – названия машин в таблице "Тарифы проката", перечисленные через ";").

2. "Цена часа" – функция ВПР() с аргументами:

· Искомое_значение: тип авто из столбца А;

· Табл_массив (где искать строку, начинающуюся с нужной машины): $F$3:$G$7;

· Номер_столбца: 2 (в каком столбце найденной строки следует искать нужный тариф);

· Тип_просмотра: 0 (требуется строка, в которой значение в первом столбце точно совпадает с аргументом "Искомое_значение").

3. "Взято с" – вводятся с клавиатуры даты и время.

4. "Оплачено часов" – формула: 24 * ("Оплачено по:" – "Взято с:").

5. "Разница часы" – функция ЕСЛИ(), которая если "Дата возврата" пустая, заносит в ячейку прочерк, в противном случае – выражение 24 * ("Дата возврата" – "Оплачено по:").

6. Графа "Разница сумма" заполняется аналогично п. 5.

7. "Доплата/возврат" – функция ЕСЛИ(), которая если "Дата возврата пустая", заносит в ячейку прочерк, в противном случае, если "Разница часы" > 0, то использует выражение "Разница часы" * "Коэффициент доплаты", иначе использует выражение "Разница часы" * "Коэффициент возврата".

8. Для всех граф, где фигурируют денежные суммы, используйте денежное форматирование, а в графе "Доплата/возврат" закажите дополнительно использование красного шрифта для отрицательных сумм.

7.1.9. У Вас есть свободная неделя, в течение которой Вы решили провести такую хозяйственную операцию: взять в банке кредит, закупить на него фрукты, например бананы, по оптовой цене, а продать – по рыночной. Операция осложняется такими факторами: чтобы получить выручку побольше, хочется установить высокую цену, но объем продаж в день зависит от соотношения Вашей цены и среднерыночной. Чем выше Ваша цена, тем он меньше и, следовательно, тем дольше длится операция. Затягивать ее нежелательно, так как фрукты со временем гниют (это убытки), а банк требует до окончательного расчета с ним всю выручку сдавать ему и каждый день взимает "грабительский" процент с непогашенной части долга. Требуется проанализировать, может ли эта операция принести выгоду, и, если да, подобрать оптимальные условия ее проведения.

Подсказка

На рис. 7.1.5 приведена схема расположения информации для моделирования этой операции. В блоке D2:D9 расположены данные, необходимые для расчетов. Вы можете произвольно менять только значения в ячейках D3 и D5, остальные данные зависят от этого выбора и состояния рынка.

Рис. 7.1.5

Вероятный объем продаж в день (ячейка D6) зависит от соотношения среднерыночной и Вашей цены, географического положения региона и ряда других факторов. Формулы для описания сложных зависимостей обычно подбирают опытным путем (см. подразд. 6.8), оставляя в них в качестве аргументов только существенные для решения текущей задачи факторы, а все остальные факторы учитывают в усредненном виде с помощью числовых коэффициентов. В данной задаче можно условно воспользоваться формулой

=3000*EXP(D4*0,015*(0,95*D3-D4))/(1+EXP(D4*0,025*(1,2*D3-D4)))

Формулы, необходимые для заполнения таблицы "Планирование торговой операции", описаны ниже словами. Там, где используются исходные данные, следует применять не их значения, а ссылки с абсолютной адресацией.

1. Строка 12 (первый день торговли).

· "Остаток долга банку" = "Сумма банковского кредита".

· "Остаток бананов" = "Общий объем закупки".

· "Продано бананов" = если "Остаток бананов" больше, чем "Объем продажи за день", то "Объем продажи за день", иначе "Остаток бананов".

· "Сгнило бананов" = ("Остаток бананов" – "Продано бананов") * "Скорость гниения" * "День продажи".

· "Выручка" = "Продано бананов" * "Цена продажи".

· "Текущая выплата банку" = если "Остаток долга" больше, чем "Выручка", то "Выручка", иначе "Остаток долга".

· "Прибыль" = "Выручка" – "Текущая выплата банку".

2. Строка 13 (второй день торговли).

· "Остаток долга банку" = ("Остаток долга банку" за предыдущий день – "Выплата банку" за предыдущий день) * (1 + "Процент банковского кредита").

· "Остаток бананов" = "Остаток бананов" за предыдущий день – "Продано бананов" за предыдущий день – "Сгнило бананов" за предыдущий день.

· Остальные формулы – такие же, как и в первый день продажи.

3. В следующих строках формулы такие же, как и во второй день торговли. Можно скопировать их на остальные строки таблицы.

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

После нескольких вариантов "ручного" перебора указанных параметров, полезно воспользоваться командой Сервис ® Поиск решения… (см. подразд. 6.7). Эта команда дает математический ответ, выраженный "некруглыми" числами, неудобными в практической деятельности. Поэтому закончить решение можно применением команды Сервис ® Сценарии… В качестве разных сценариев можно задать комбинации "круглых" цен и объемов закупки, находящихся вокруг точного математического ответа. В качестве ячейки результата – итоговую прибыль. Тогда в отчете типа "Структура" будут собраны результаты сразу по всем комбинациям реальных значений параметров, и выбрать окончательный вариант будет легче.

Пояснение к задачам 7.1.10–7.1.14.

В этих задачах необходимо предусмотреть проверку нескольких условий, чтобы выбрать нужный вариант (см. подразд. 6.3).

7.1.10. Составьте и заполните таблицу по образцу рис. 7.1.6.

Рис. 7.1.6

Введите в таблицу сведения о 20–30-и лотах, представленных на аукционе (названия лотов можно ввести протяжкой). В ячейках F4 и G4 разместите их минимальную и максимальную цены (функции МИН() и МАКС()), в ячейках H4 и I4 – предполагаемые границы цен Вашей покупки. В столбец С введите функцию ЕСЛИ(), которая отметит символом "+" строку с самым дешевым лотом, в столбец D – c самым дорогим. Остальные ячейки должны выглядеть пустыми. Отформатируйте ячейки с функциями ЕСЛИ(): выравнивание по центру, шрифт 16 пт, выберите цвета заливки и шрифта. В столбец Е введите функцию ЕСЛИ(), которая скопирует название лота из столбца А в столбец Е для тех лотов, цена которых попадает в нужный для покупателя диапазон. Для других лотов ячейки этого столбца должны выглядеть пустыми.

7.1.11. На рис. 7.1.7 изображены схема начисления премий сотрудникам некоторой фирмы и премиальная ведомость на текущий месяц. Оформите их в Excel. В графу "Премия" введите формулы автоматического расчета величины премии для любого месяца, указанного в ячейке В4. В графу "Всего" и строку "Итого" составьте и введите итоговые формулы.

Рис 7.1.7

Подсказка

"Премия" = (если "Пол" женский и "Текущий месяц" равен 3, то 300 , в противном случае 0) + (если "Пол" мужской и ...) + ...

Проверьте правильность введенных формул, подставляя в ячейку В4 разные номера месяцев.

7.1.12. Оформите в Excel ведомость, представленную в табл. 7.1.5. Дополните ее графой "Выбор". В ячейках этого столбца функция ЕСЛИ() должна разместить слово "Годится", если цена за товар менее $160, а размер экрана равен 17 дюймам. В остальных строках ячейки должны остаться пустыми. Константы, по которым проводится отбор товара, занесите в отдельные ячейки рабочего листа. С помощью функции СЧЕТЕСЛИ() подсчитайте количество товаров, удовлетворяющих критерию отбора (в поле "Диапазон" следует указать ячейки графы "Выбор", в поле "Условие" – слово "Годится" в кавычках).

Таблица 7.1.5

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

Рис. 7.1.8

7.1.14. В столбцах E, F и G рис. 7.1.8 представлены итоги ряда сделок. Удачными считаются те из них, в которых доход оказался больше либо равен 10 тыс. долларов, а количество нарушений – не больше одного. С помощью функции ЕСЛИ() отметьте символом "+" в столбце Н строки с удачными сделками. Остальные ячейки этого столбца должны выглядеть пустыми.

Пояснение к задачам 7.1.15–7.1.21

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

7.1.15. В табл. 7.1.6 приведены результаты тестирования ряда студентов.

Таблица 7.1.6

Оформите эти данные в виде ведомости Excel ("ФИО" – столбец А, "Грамматика" – столбец В, "Фонетика" – столбец С, "Итог" – столбец D). Отдельно в ячейку F1 введите порог для суммарного балла, ниже которого студент считается не аттестованным. Для начала введите значение порога равным 9. Функция ЕСЛИ() должна в графе "Итог" поместить оценку "Отлично", если сумма баллов по грамматике и фонетике превзошла 14; если же она менее порога, то "Плохо". В остальных случаях – "Норма". Проверьте влияющие ячейки для последней строки ведомости. Замените значение порога на 10, потом на 8. Посмотрите, как автоматически изменяется количество неаттестованных студентов. Воспользуйтесь командой Формат ® Условное форматирование… для того, чтобы по-разному оформлялись ячейки с разными результатами аттестации. Повторите перебор разных значений порога. Сравните, в каком случае удобнее находить в ведомости нужные значения: при использовании условного форматирования или без него.

7.1.16. Введите данные с рис. 7.1.9. В таблице квалификаций показаны граничные баллы, определяющие квалификацию спортсмена. С помощью функции ЕСЛИ() поместите в графу "Разряд" название квалификации, соответствующей баллу спортсмена. С помощью команды Формат ® Условное форматирование… выделите в наградном листе красным цветом заливки или шрифта квалификацию "Мастер", голубым – "1-й разряд", зеленым – "2-й разряд".

Рис. 7.1.9

7.1.17. Олимпиада по программированию оценивается по сумме очков, полученных за каждую из трех задач. Для учащихся младше 10 класса сумма очков увеличивается на 10 %. Набравшие больше 27 баллов получают диплом I степени, от 27 до 25 баллов – диплом II степени, от 25 до 23 – диплом III степени. В табл. 7.1.7 приведены результаты проверки олимпиадных работ. Заполните графы "Сумма баллов" и "Диплом" в табл. 7.1.7. С помощью условного форматирования выделите тех, кто получил диплом I степени, красной заливкой, диплом II степени – синей, диплом III степени – зеленой.

Таблица 7.1.7

7.1.18. Страховые агенты отчитываются за сделки ежемесячно. Агентам начисляется комиссионное вознаграждение по тарификационной таблице в зависимости от суммы сделок. Схема расчетов приведена на рис. 7.1.10.

Рис. 7.1.10

Определите для каждого агента общую сумму сделок за первый квартал и причитающееся вознаграждение. Добавьте в строки 11–14 расчет среднего объема сделок, их минимальный и максимальный объем, учет количества не отчитавшихся агентов (функция СЧИТАТЬПУСТОТЫ() в категории Статистические). Добавьте примечания типа "Лучший в марте" к фамилиям агентов (команда Вставка ® Примечание). С помощью нестандартного форматирования добавьте ко всем денежным суммам текст "тыс. р.".

7.1.19. Фирма выпускает CD-диски. На рис. 7.1.11 представлен макет размещения информации и исходные данные для составления ведомости оплаты труда и оценки ожидаемой прибыли. Блок А1:С5 содержит константы для расчета расходов и выручки. Тариф оплаты труда зависит от дневной выработки, схема его выбора расположена в блоке А6:С12. Заполните таблицы "Ведомость оплаты труда" и "Оценка ожидаемой прибыли".

Рис. 7.1.11

Пояснение

В графу "Принято" функция ЕСЛИ() должна внести значение из графы "Сделано", если оно меньше или равно А11, иначе – 0; в графе "Выплатить" нужно перемножить то, что принято от работника, на тариф оплаты, который выбирает функция ЕСЛИ() в зависимости от объема принятой продукции; в Е6:G6 надо рассчитать итоги, в G8:G10 – разместить формулы, которые для оценки ожидаемой прибыли правильно выбирают нужный итог из таблицы "Ведомость оплаты труда".

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

Подсказка

На рис. 7.1.12 приведена одна из возможных схем расположения информации.

Рис. 7.1.12

Расчетные формулы:

1. "Цена закупки Рубли" = "Цена закупки Национальная валюта" * курс в день покупки, который ищет функция ВПР() с аргументами:

· "Искомое_значение": дата из графы "Товар закуплен";

· "Табл_массив": $А$8:$D$15 (где искать строку, начинающуюся с нужной даты);

· "Номер_столбца" (в каком столбце найденной строки искать нужное данное): вложенные друг в друга функции ЕСЛИ(), которые сравнивают букву в графе "Вид валюты" последовательно с буквами из строки "Код". Если совпало с $В$3, то "Номер_столбца" = 2, если с $С$3, то "Номер_столбца" = 3 и т. д.;

· "Тип_просмотра": 1 (требуется дата со значением наиболее близким, но не превышающим то, которое указано в аргументе "Искомое_значение").

2. "Цена сегодня" – формула аналогична предыдущей, но "Искомое_значение": $I$1.

Примечание

Аргумент "Номер_столбца" можно рассчитать более просто, не привлекая функцию ЕСЛИ(), с помощью функции ПОИСК() из категории "Текстовые", у которой в аргумент "Внутр_текст" следует ввести строку "ДДМФ", составленную из кодов валют.

7.1.21. Стоимость партии товара С зависит от ее объема и вычисляется по формуле

Здесь с₁, с₂ и с₃ соответственно розничная, мелко- и крупнооптовая цены; а₁ и а₂ – границы, при которых начинают действовать соответствующие скидки; х – количество закупленного товара. Надо составить ведомость, которая отражает стоимость товара, отпущенного каждому покупателю, общую сумму выручки и среднюю стоимость одной партии товара. Показать на диаграмме, как средняя стоимость меняется с увеличением количества покупателей (по аналогии с задачей 5.9).