2020-09-27
531
Наименование работы: Применение логических функций в таблице MS Excel
Цель работы: Научиться составлять и использовать логические функции при решении задач с учетом определенных условий. Формировать ОК-2, ОК-4, ОК-5, ОК-6, ПК-1.2, ПК-1.3, ПК-1.6, ПК-3.1, ПК-3.2
Литература:
1. Кунтаева Г.Х., Строева Л.Р. Пакеты прикладных программ. Учебное пособие, 2015
2. Фуфаев Э.В., Фуфаева Л.И. Пакеты прикладных программ, 2014.
Задание: Выполните требуемое задание в соответствии со своим вариантом
Вариант 1
Кондитерская фабрика «Шоко и К°» для производства трех видов шоколада: A, B, и C использует три вида сырья: масло-какао, тертое какао и сахарную пудру.
Нормы расхода сырья на 1000 (кг) шоколада соответственно равны:
Требуется:
а) при помощи электронной таблицы рассчитать:
− расход сырья каждого вида;
− выход сырья (итог);
− какао-массу (масло какао + тертое какао) в процентах;
− определить вид шоколада, зависящий от какао-массы: если какао-масса больше 50%, то вид шоколада «Горький», иначе шоколад «Молочный», и вывести «Белый», если меньше 32%;
|
|
|
б) построить диаграмму по расходу сырья каждого вида для производства шоколада А, В, С.
Вариант 2
Требуется:
а) при помощи электронной таблицы рассчитать:
− стоимость продукции;
− в столбце «% скидки», вывести «7%», если сумма продукции находится в диапазоне от 8000 до 10000 руб., иначе выводить скидку в «10%», если сумма меньше 8000 руб., вывести «0%»;
− сумму с учетом скидки;
б) построить диаграмму, отражающую сумму от реализации каждого вида продукции.
Вариант 3
Требуется:
а) при помощи электронной таблицы рассчитать:
− величину перерасхода энергии за каждый месяц;
− сумму штрафа за каждый киловатт электрической энергии, израсходованной сверх нормы, если перерасход составляет больше 100 киловатт;
− сумму к оплате;
б) построить диаграмму, отражающую перерасход электрической энергии по каждому месяцу.
Вариант 4
Требуется:
а) при помощи электронной таблицы рассчитать:
− количество упаковок, сформировать случайным образом в диапазоне от 20 до 300;
− выручку отдельно за I и II квартал, с учетом, что в 1 упаковке находится 6 бутылок;
− общую выручку за I и II квартал;
− с помощью функции ЕСЛИ столбец Рекомендации, при условии, если выручка за II квартал больше выручки за I квартал, то в рекомендациях автоматически вывести «увеличение прибыли», если выручка за I и II квартал равны, то «план выполнен», иначе «план не выполнен, увеличить продажи!!!»;
б) построить диаграмму, отображающую рост выручки продукции за I и II квартал.
Вариант 5
Требуется:
а) при помощи электронной таблицы рассчитать:
|
|
|
− вес (г), сформировать случайным образом с помощью функции СЛЧИС в диапазоне от 35 до 80;
− автоматически срок хранения, при условии, если яйцо диетическое, то срок хранения «7 дней», если столовое, то «25 дней»;
− автоматически определить категорию яйца в зависимости от веса:
б) в столбце «Категория», яйцо высшей категории автоматически выводить шрифтом красного цвета.
Вариант 6
Требуется:
а) при помощи электронной таблицы рассчитать:
− энергетическую ценность продуктов в ккал;
− к какой степени калорийности относится продукт, т.е. в столбце «Итог», вывести «высококалорийный», если энергетическая ценность продукта находится в диапазоне от 200 до 900 ккал, иначе выводить «среднекалорийный», если энергоценность меньше 100 ккал, вывести «низкокалорийный продукт»;
б) построить диаграмму, отображающую энергетическую ценность продуктов.
Вариант 7
Требуется:
а) при помощи электронной таблицы рассчитать:
− закупочную цену ($) в зависимости от текущего курса $, который заносится в отдельную ячейку;
− сумму закупки = закупочная цена товара * количество;
− сумму реализации = розничная цена товара * количество, при условии, что розничная цена на ананасы – 210 руб/кг, бананы – 49 руб/кг, виноград – 159 руб/кг, апельсин – 68 руб/кг, груша – 55 руб/кг;
− валовый доход = сумма реализации – сумма закупки;
б) построить диаграмму, отображающую отдельно сумму закупки и сумму реализации товара.
Вариант 8
Требуется:
а) при помощи электронной таблицы рассчитать:
− оклад, сформировать случайным образом с помощью функции СЛЧИС в диапазоне от 10000 до 15000 руб.;
− автоматически определить доплату работникам, в зависимости от разряда:
− общую сумму;
б) построить диаграмму, отображающую соотношение размера оклада и общей суммы.
Вариант 9
Требуется:
а) при помощи электронной таблицы рассчитать:
− количество товара, сформировать случайным образом в диапазоне от 96 до 270;
− % скидки, с помощью функции ЕСЛИ, при условии, если сумма больше 5000 руб., то в столбце «% скидки» автоматически вывести «2%», если сумма больше 10000, то скидка «5%», иначе «0%»;
− скидку в рублях;
− итоговую сумму с вычетом скидки и общую сумму за всю продукцию;
б) в столбце «% скидки», 5% скидку автоматически выводить шрифтом желтого цвета.
Вариант 10
Требуется:
а) при помощи электронной таблицы рассчитать:
− случайно сформированное количество поставки продукции по месяцам в диапазоне от 10 до 100 кг;
− в столбце “Всего” общее количество реализованной продукции;
− в столбце “Выручка” организовать автоматический вывод слова “максимальная выручка” в ячейке той строки, в которой получается наибольшая выручка от продажи рыбной продукции;
б) построить диаграмму, отражающую количество реализованной продукции по месяцам.
Порядок выполнения работы:
1. Получить допуск к работе;
2. Запустить Microsoft Excel и выполнить задания;
3. Составить отчет;
4. Ответить на контрольные вопросы.
Содержание отчета:
1. Наименование, цель занятия, задание;
2. Выполненное задание;
3. Ответы на контрольные вопросы.
Контрольные вопросы:
1. Как запустить мастер функции?
2. Перечислите категории функции в MS Excel.
3. Перечислите математические функции в MS Excel.
4. Перечислите логические функции в MS Excel.
5. Перечислите статистические функции в MS Excel.
6. Каков формат записи условной функции ЕСЛИ?
7. Какие логические функции существуют в Excel для записи сложных условий? Приведите формат этой записи.
8. Что такое режим автозаполнения ячеек?
ПРИЛОЖЕНИЕ
Логические функции. Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий.
|
|
|
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать, по крайней мере, один оператор сравнения, который определяет отношение между элементами логического выражения.
| оператор | значение |
| = | равно |
| > | больше |
| < | меньше |
| <= | меньше или равно |
| >= | больше или равно |
| <> | не равно |
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Логические функции в Excel представлены списком из шести функций.
Функция ЕСЛИ. Функция ЕСЛИ имеет следующий синтаксис:
=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)
Например: нужно определить размер скидки при условии, если сумма покупки больше 1000 руб., то скидка 5%, иначе скидка не предоставляется. =ЕСЛИ(А1>1000;5%;0%) – эта запись означает, что формула возвращает значение 5%, если значение в ячейке А1 больше 1000, а в противном случае – 0%.
В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы, например: =ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал").
Функции И, ИЛИ. Функции И, ИЛИ - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:
=И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...)
Аргументы функций И, ИЛИ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.
Пример: вычислить значения функции в зависимости от значений аргумента: 
=ЕСЛИ(ИЛИ(А1>0;A1=0);A1*A1-4;A1+5) – эта запись означает, что формула возвращает выражение X*X-4, если значение в ячейке А1 больше 0 или равно 0, а в противном случае – X+5.
