Методические указания и задания к практическим работам по теме "Технология обработки числовых данных" для студентов 1 курса СПО. Практическая работа 2.

Практическая  работа №2.

Использование логической функции «ЕСЛИ».

Цель работы.

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

Практикум 1. Проходной балл.

Краткие теоретические сведения.

Логические функции предназначены для проверки выполнения условия или для  проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие ИСТИНА и другое значение, если условие ЛОЖЬ.

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

=ЕСЛИ(С4>3;«зачет сдан»;«зачет не сдан»). В данном случае, если значение ячейки С4 больше 3, то в ячейку, где записана функция ЕСЛИ, вносится фраза  «зачет сдан», если же  значение  ячейки С4 меньше 3 или равно 3, то в ячейку, где записана функция, вносится фраза  «зачет не сдан».

Функцию можно набрать с клавиатуры или вызвать мастер функций кнопкой f_x, находящейся в строке формул, или использовать комбинацию клавиш Shift+F3.

Для заполнения ячейки случайным числом в диапазоне между a и b используют функцию =СЛЧИС()*(b-a)+a. Например, формула =СЛЧИС()*(16-7)+7 дает случайное число в диапазоне между 7 и 16. Причем число не целое, например 9.546876532.

Для получения целого числа, используется функция  =ЦЕЛОЕ(число) или =ЦЕЛОЕ(адрес ячейки) или =ЦЕЛОЕ(функция) или =ЦЕЛОЕ(формула). Например, формула =ЦЕЛОЕ(СЛЧИС()*(16-7)+7) дает целое случайное число в диапазоне между 7 и 16.

В зависимости от содержания, можно настраивать ячейки, используя условное форматирование (формат шрифта, заливка ячеек). Для этого нужно: выделить ячейку, на вкладке «Главная» в группе «Стили» раскрыть список команды «Условное форматирование», выбрать  «Правила выделения ячеек»,  выбрать правило,  заполнить левое текстовое поле, в правом списке выбрать «Пользовательский формат»,  во вкладках «Шрифт» и «Заливка» установить нужные значения.

Порядок выполнения практикума.

Откройте приложение Microsoft Excel.

1. Создайте таблицу.
2. Оформите название таблицы командой  объединить и поместить в центре.

Ведомость
приёма на обучение
в Санкт-Петербургский вольный университет хакерства
		Проходной балл		30
№ абит.	Фамилия	Взлом паролей	Атака на сайт	Бесплатный выход в Интернет	Написание вирусов	Общий балл	Средний балл	Сообщение о зачислении
1	Паскаль
2	Вирт
3	Гейтс
4	Беббидж
5	Буль
6	Нортон
7	Возняк
8	Нейман
9	Винер
10	Иванов
Средний балл за экзамен

3. Заполните данные по оценкам с использованием функции получения случайных чисел в пределах от 5 до 10 включительно (СЛЧИС() и ЦЕЛОЕ).
4. Выполните подсчёт общего балла командой Автосуммирование.
5. Выполните подсчёт средних значений по каждому абитуриенту и экзамену с помощью функции СРЗНАЧ.
6. В столбце Сообщение о зачислении используйте функцию ЕСЛИ для сообщения «отказать», если сумма баллов меньше проходного, и сообщение «зачислен» в обратном случае.
7. С помощью Условного форматирования настройте ячейки столбца Сообщение о зачислении так, чтобы при появлении в них слова «зачислен» она заливалась желтым цветом, а само сообщение было выделено полужирным синим.
8. Примените шрифтовое оформление, заливку, нанесите границы.
9. Поменяв оценки у какого-нибудь абитуриента, убедитесь в работоспособности всех ваших формул.
10. Переименуйте Лист1 в  Балл.

Практикум 2. Аренда помещений.

Краткие теоретические сведения.

Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции имеют синтаксис:                      =И(условие1;условие2…)
                                                    =ИЛИ(условие1;условие2…)

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

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

Например,         =ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел".)

Если значение ячейки А2 больше 4 и значение ячейки А3 меньше 3, то в ячейку с функцией ЕСЛИ вносится значение «Прошел», иначе - значение «Не прошел».

Если в предыдущей формуле заменить функцию И на ИЛИ, то результатом функции ЕСЛИ  будет значение «Прошел», если выполняется хотя бы одно из условий (значение ячейки А2 больше 4 или значение ячейки А3 меньше 3).

Можно использовать вложенные функции ЕСЛИ. Например, =ЕСЛИ(А1=100;"Всегда";ЕСЛИ(А1>100;"Иногда";"Никогда"))
Формула читается следующим образом: если значение в ячейке А1 равно 100, то в ячейку с функцией ЕСЛИ  вносится слово «Всегда». В противном случае, если значение в ячейке А1 больше 100, то в ячейку с функцией ЕСЛИ  вносится слово «Иногда», иначе – слово «Никогда». Допускается до 64 уровней вложения функций ЕСЛИ.

Задача.

Составить таблицу расчета оплаты за аренду помещений в зависимости от площади: если арендуемая площадь меньше 100 м², то арендная плата составляет 500 руб. за 1 м², если арендуемая площадь больше, чем 100 м², но не превышает 200 м² , то арендная плата составляет 700 руб. за 1 м². За площадь более 200 м² арендная плата – 800 руб. за 1 м² .

Порядок выполнения практикума.

1.   На Листе 2 создайте таблицу:

Кафе	Площадь (кв.м)	Сумма арендной платы (способ1)	Сумма арендной платы (способ2)
«Эдельвейс»	167
«Нарцисс»	95
«Камелот»	234
«Рандеву»	128
«Экспромт»	173

2. В столбце «Сумма арендной платы (способ1)» используйте вложенные функции ЕСЛИ.

(Если арендуемая площадь больше 200, то вычислить арендную плату по 800 руб. В противном случае, если арендуемая площадь больше 100, то вычислить арендную плату по 700 руб., иначе  вычислить арендную плату по 500 руб.)

3. В столбце «Сумма арендной платы (способ2)» используйте вложенные функции ЕСЛИ с логической функцией И.

(Если арендуемая площадь меньше 100, то вычислить арендную плату по 500 руб. В противном случае, если арендуемая площадь больше 100 и меньше 200, то вычислить арендную плату по 700 руб., иначе  вычислить арендную плату по 800 руб.)

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

Практикум 3. Начисление зарплаты.

1. На Листе 3 подготовьте таблицу:

2. Напишите соответствующие формулы в столбцах Оклад(руб), % премии, Премия(руб), Итого(руб), Налог(руб) и На руки(руб).
- Оклад в рублях рассчитывается исходя из оклада в долларах, курса доллара плюс 10% к получившейся сумме от доброго хозяина фирмы.
- Процент премии рассчитывается так: если работник отработал все рабочие дни, то получает 100% премии, если больше 14 дней, то 50%, во всех остальных случаях 25%.
- Премия в рублях рассчитывается как оклад в рублях, умноженный на % премии.
- Итого в рублях получается по формуле:
                                                       (отработка×оклад/количество рабочих дней)+премия
- Налог: если Итого получается меньше 10 000 рублей, то 13%, иначе 25%.
- На руки: Итого – Налог.

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

4. Примените шрифтовое оформление и заливку, нанесите границы.

5. Переименуйте Лист3 в Зарплата.

6. «Обрушив» курс доллара до 30 рублей, убедитесь, что все формулы работают нормально.
Сохраните результаты работы под именем «Лаб2».

Отчет по практической работе №2.

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

2. Запишите синтаксис функции ЕСЛИ. Приведите пример с комментарием.

3. Запишите формулу для получения целого случайного числа от 10 до 20.

4. Запишите формулу для занесения в заданную ячейку слова «Молодец», если значение ячейки
D4 равно четырем или пяти и слова «посредственно»  в остальных случаях. Предусмотреть
вариант, что формулу нужно будет копировать вниз, а в ячейках D5, D6 и ниже находится текст.

5. Опишите последовательность действий для настройки ячейки так, чтобы при появлении в ней слова «Мастер» она заливалась серым цветом, а само сообщение было выделено полужирным курсивом.

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

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

1 ВАРИАНТ

Таблица содержит следующие данные об учениках школы: фамилия, возраст и рост ученика. Сколько учеников могут заниматься в баскетбольной секции, если туда принимают детей с ростом не менее 160 см? Возраст не должен превышать 13 лет. Подсчет количества учеников осуществить с помощью функции =СЧЁТЕСЛИ.

		Максимальный возраст
		Минимальный рост
Фамилия	Возраст	Рост	Результат
Итого

2 ВАРИАНТ

Каждому пушному зверьку в возрасте от  1-го до 2-х месяцев полагается дополнительный стакан молока в день, если его вес меньше 3 кг. Количество зверьков, возраст и вес каждого известны. Выяс­нить сколько литров молока в месяц необходимо для зверофермы. Один стакан молока составляет 0,2 литра.

			Один стакан молока, л
Номер	Возраст	Вес	Доп-ный стакан	Л в день	Л в месяц
1
2
3
4
5
6
7
8
9
10
Итого

3 ВАРИАНТ

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

	Стоимость 1 кВт
Жилец	Скидка 50%	Сумма к оплате
1	есть
2	есть
3	нет
4	нет
5	нет
6	нет
7	есть
8	нет
9	нет
10	есть
Итого

4 ВАРИАНТ

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

Продукт	Хранится на складе (мес.)	Цена до уценки	Цена после уценки

5 ВАРИАНТ

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

	Дневная норма
	1 кг помидоров(руб.)
Рабочий	Собрал	Деньги за день
1
2
3
4
5
6
7
8
9
10

6 ВАРИАНТ

Билет на пригородном поезде стоит 5 монет, если расстояние до станции не больше 20 км; 13 монет, если расстояние больше 20 км, но не превышает 75 км; 20 монет, если расстояние больше 75 км. Составить таблицу, содержащую следующие сведения: пункт назначения, расстояние, стоимость билета. Выяснить сколько станций находится в радиусе 50 км от города.

Пункт назначения	Расстояние	Стоимость билета
1
2
3
4
5
6
7
8
9
10
	Станций в 50 км

7 ВАРИАНТ

Телефонная компания взимает плату за услуги телефонной связи по следующему тарифу: 370 мин в месяц оплачиваются как абонентская плата, которая составляет 200 монет. За каждую минуту сверх нормы необходимо платить по 2 монеты. Составить ведомость оплаты услуг телефонной связи для 10 жильцов за один месяц.

		Норма в месяц, мин
		Абонентская плата
		Мин. Сверх нормы
Жилец	Потратил, мин	Начислено
1
2
3
4
5
6
7
8
9
10
	Итого за месяц