 |
|
Условное форматирование
Если необходимо, чтобы ячейка меняла формат, который ей задан (заливка, шрифт, начертание, рамки и т.д.) при выполнении определенного условия, нужно:
1. Выделить ячейки, которые должны автоматически менять свой цвет.
2. Выбрать на вкладке Главная группы Стили команду Условное форматирование(рис. 16).
Рисунок 16. – Команда Условное форматирование.
В открывшемся окне можно задать условия и параметры форматирования ячейке при выполнении условия. Подробнее это можно рассмотреть выбрав команду Создать правило, и тем самым открыв диалоговое окно Создание правила форматирования (рис.17).
Рисунок. 17 - Диалоговое окно Создание правила форматирования.
Изменяя тип правила можно форматировать не только ячейки, но и значение, для которых выполняется приведенное условие (рис.18).
Рисунок 18. – Форматирование значений.
Если для диапазона ячеек заданные критерии условного форматирования, то нельзя отформатировать эти ячейки через меню Формат ячеек в ручную. Чтобы вернуть эту возможность надо удалить условия в окне Условное форматирование с помощью команды Удалить правила.
Рисунок 19. – Команда Удалить правила.
Порядок выполнения работы:
1. Подготовить и создать таблицу согласно варианту, указанному в индивидуальном задании.
2. Наложить ограничения по введению данных.
3. Определить основные функции для выполнения задачи и сформировать формулы для вычисления.
4. Провести вычисления по заданным условиям.
Варианты заданий
Вариант №1
1. Подготовить и создать таблицу:
| Сведения об успеваемости студентов гр. КН-15 | |||||||||||
| № п/п | Ф.И.О. | Математика | Программирование | Системотехника | Ин. Язык | КИТ | Пакеты | Средний балл | Стипендия | ||
| Малов С.Ю. | 4,67 | ||||||||||
| Величко Д.С. | 3,67 | ||||||||||
| Возняк А.М. | 4,33 | ||||||||||
| Малов С.Ю. | |||||||||||
| Иванченко А.В. | 5,00 | ||||||||||
| Липчик А.Г. | 4,00 | ||||||||||
| Мальцев А.Н. | 3,00 | ||||||||||
| Волков Н.В. | |||||||||||
| Матюшенко А.А. | |||||||||||
| Бурлака А.А. | 3,67 | ||||||||||
| Дата (текущая дата): | 22.02.2013 | ||||||||||
2. На ячейках по предметам наложить ограничения по введению от 2 до 5.
3. Средний бал по каждому студенту вычислить как среднее арифметическое оценок, больших чем "2". Если оценка по предмету "2", то средний бал не рассчитывается и не выводиться.
4. Стипендию вычислить согласно условиям 4,5 < ср. балл <= 5 стипендия 760 грн. 4 < ср. балл <= 4,5 стипендия 540 грн. 3,5 <= ср. балл <= 4 стипендия 350 грн.
5. Посчитать всего оценок "5", "4", "3", "2".
6. Отсортировать список по алфавиту.
Вариант №2
1. Подготовить и создать таблицу:
| Сведения о заработной плате рабочих с почасовой формой оплаты | |||||||||
| № п/п | Номер цеха | Табельный номер | ФИО | Ставка за час, грн | Часы | Премия | Начислено | Удержано | К выплате |
| Иванов И.С. | 30,00 | ||||||||
| Петренко С.А. | 20,50 | ||||||||
| Васнецов И.Е. | 18,30 | 50% | |||||||
| Игорев Е.А. | 23,45 | 100% | |||||||
| Макеенко А.С. | 23,50 | ||||||||
| Каровацкий П.С. | 28,00 | 100% | |||||||
| Музынкова Г.И. | 24,50 | 20% | |||||||
| Проценко Т.Н. | 30,00 | ||||||||
| Кириенко М.Н. | 17,00 | 100% | |||||||
| Матросова И.М. | 28,30 |
2. В ячейках Ставка и Часы наложить ограничения по введению данных: для ставки от 5 до 100, для количества часов - от 0 до 160. Следует отметить, что ставка может включать в себя копейки.
3. Процент премии вычислить по следующим условиях 100 <Часы <= 160 премия 100%; 80 <Часы <= 100 премия 50%; 50 <= Часы <= 80 премия 20%. Другие рабочие премию не получают.
4. Размер начисленной зарплаты по каждому работнику (столбец 8) вычислять как произведение ставки по времени и количества часов, которые отработал работник. При этом необходимо добавить соответствующую премию. Удержанные деньги (столбец 9) равны 20% от начисленной зарплаты. К выплате (столбец 10) разность столбцов 8 и 9. Если количество часов равно "0", то столбцы 7-10 не рассчитываются и не выводятся.
5. Посчитать количество рабочих по каждому цеху.
6. Отсортировать по цехам, а в них по табельному номеру.
Вариант №3
1. Подготовить и создать таблицу:
| Сведения о квартиросъемщиках | |||||||||||||
| № п/п | Фамилия | Имя | Отчество | Полный адрес | Площадь квартиры | Категория квартиры | Коли-чество | Плата, грн | ИТОГО | ||||
| комнат | человек | общая площадь | газ | вода | отопление | ||||||||
| Исайкин | Сергей | Федорович | г.Донецк, ул.Шопена, д.83., кв 45 | 64,00 | |||||||||
| Иполитов | Евгений | Фомич | г.Донецк, ул.Артема, д.68., кв.1 | 32,30 | |||||||||
| Матвийчук | Ирина | Николаевна | г. Макеевка, ул.Московская, д.45 | 85,00 | |||||||||
| Проклов | Игорь | Викторович | г. Донецк, ул. Разинкова, д.42, кв. 16 | 54,00 | |||||||||
| Астафьев | Виктор | Павлович | г.Донецк, Киевский пр-т, д.102, кв.1 | 47,60 | |||||||||
| Мураков | Игорь | Сергеевич | г.Донецк, ул.Щорса, д.2, кв.1 | 36,00 | |||||||||
| Ворон | Василий | Николаеви | г.Макеевка, ул.Овсиенко, д.32., кв.1 | 80,00 | |||||||||
| Орленко | Олег | Юрьевич | г.Донецк, ул.Куйбышева, д.65, кв.89 | 20,00 |
2. На ячейку «Категория квартиры» наложить ограничения по введению данных от 1 до 3.
3. Разрешить вводить пользователю в ячейки 7-9 только целые числа.
4. Плата за общую площадь квартиры должна зависеть от ее категории. Если квартира принадлежит к первой категории, то цена 1 м2 равна 1,96 грн, для второй категории - 1,53 грн, для третьего - 1,18.
5. Расчетные столбце 11, 12 зависят от количества людей, проживающих в квартире. Плата за газ на одного человека составляет 5,21 грн, за воду - 46,54 грн. Плата за отопление зависит от площади квартиры (цена за 1 м2 соответствует 6,2 грн).
6. Определить цвет для ячеек столбца 14. Если общая плата лежит в диапазоне от 500 до 800 грн, то ячейка должна иметь желтый цвет, если плата менее 500 - зеленый, более 800 - розовый.
7. Сортировать данные в таблице по столбцу 9 в следующем порядке: 2, 5, 3, 4.
8. Посчитать количество квартир каждой категории.
Вариант №4
1. Подготовить и создать таблицу:
| Адрес | Холодная вода | Горячая вода | ||||||
| Начальные показания | Конечные показания | Разность | Сумма платежа | Начальные показания | Конечные показания | Разность | Сумма платежа | |
| ул.Артема,д.10, кв.10 | ||||||||
| ул.Артема,д.10, кв.11 | ||||||||
| ул.Артема,д.10, кв.12 | ||||||||
| ул.Артема,д.10, кв.13 | ||||||||
| пр.Мира,д.2, кв.1 | ||||||||
| пр.Мира,д.2, кв.2 |
2. Разрешить вводить пользователю в ячейки 2,3,4,6,7,8 только целые числа.
3. Столбцы Разность оформить как вычисляемые.
4. Столбцы Сумма платежа оформить как вычисляемые – за 1 ед. холодной воды до 5м3 оплата составляет 2 д.е., более -2,5 д.е. За . 1 ед. горячей воды до 4м3 оплата составляет – 11,5 д.е., более -15 д.е.
5. Вставить строку ИТОГО и подсчитать в ней суммы по соответствующим столбцам.
6. Упорядочить данные таблицы по возрастанию оплаты за горячую воду.
7. Соблюсти размер и форматирование строк, столбцов и ячеек.
8. В результате должна получиться Таблица, для которой выполните автоматическое форматирование Автоформат Таблицы, по своему усмотрению.
9. Выделить таблицу и построить гистограмму «Оплата за потребленную воду».
2. На ячейки начала и окончания плановых и фактических работ наложить ограничения по введению данных: - Все даты должны быть меньше сегодняшней и не позднее даты одного месяца спустя; - Дата и время окончания работы должна быть больше чем дата ее начала; Формат ввода данных в ячейки 4-7 должен быть соответствующий задачи. Значение в ячейки 1 не должно превышать сегодняшнюю дату.
3. Сумма платы за работу зависит от своевременности ее выполнения. Если задержка выполнения работы не превышает 15 минут, то работа становится дешевле на 10%, в противном случае на 50%. Следует отметить, что в Excel даты могут представляться в виде цифр (один день = 1). То есть 15 минут это 15 / 1440≈0,01.
4. Посчитать количество работ за каждый день.
Вариант №5
1. Подготовить и создать таблицу:
| № | ФИО | Название дисциплин | Объем часов | Ставка за час. | Начис-лено | Отчис-ления | к выдаче | ||
| Лекции | Практ. | Всего | |||||||
| Найденова Т.Б. | Основы экономики | 52,25 | 376,2 | ||||||
| Сорока А.А. | Основы законодательства | 68,80 | 2201,6 | 330,24 | |||||
| Осипова С.Ю. | Архитектура ПК | 52,25 | |||||||
| Гулиев Т.В. | Архитектура ПК | 40,25 | 128,8 | ||||||
| Зуева С.А. | Операционные системы | 52,25 | 501, | ||||||
| Казак Т.В. | Операционные системы | 40,25 | 64,4 | ||||||
| Воеводина А.Ю. | Сервисные программы | 68,80 | 1100,8 | ||||||
| ИТОГО | |||||||||
| Дата (текущая дата) 19.10.2014 |
2. На ячейки «Лекции» и «Практ» наложить ограничения по введению не больше 120.
3. «Всего» рассчитывать как сумму «Лекции» и «Практ».
4. «Начислено», «Отчисления» и «К выдаче» - вычислять по формулам.
5. «Отчисления» рассчитывать по следующим условияв:
6. Если «Начислено» <2000, процент отчисления составляет 10%, если> 4000 - 20%, иначе 15%.
7. Посчитать «ИТОГО».
8. Посчитать сколько выплат более 4000, выделить их автоматически другим цветом.
Вариант №6
1. Подготовить и создать таблицу:
| № п/п | Ф.И.О. | Математика | Программирование | Системотехника | КИТ | Средний балл | Стипендия | ||||
| Бурлака А.А. | |||||||||||
| Величко Д.С. | |||||||||||
| Возняк А.М. | |||||||||||
| Волков Н.В. | |||||||||||
| Иванченко А.В. | |||||||||||
| Липчик А.Г. | |||||||||||
| Мальцев А.Н. | |||||||||||
| Малов С.Ю. | |||||||||||
2. В ячейках 3,5,7,9 занесены оценки в баллах ( до 100), в ячейках 4,6,8,10 – оценки в национальной шкале (от 2 до 5)
3. Ячейки по предметам наложить ограничения по введению от 30 до 100.
4. Ячейки 4,6,8,10 оформить как вычисляемые, балы в национальной шкале посчитать по следующим условиям: < 60 – 2; 60<=3<=74; 75<=4<=89; 90<=5<=100;
5. Средний бал по каждому студенту вычислить как среднее арифметическое оценок, больших чем "2". Если есть оценка по предмету "2", то средний бал не рассчитывается и не выводиться.
6. Стипендию вычислить согласно условиям 4,5 < ср. балл <= 5 стипендия 450 грн. 4 < ср. балл <= 4,5 стипендия 400 грн. 3,5 <= ср. балл <= 4 стипендия 300 грн.
7. Посчитать всего оценок "5", "4", "3", "2".
8. Выделить цветом стипендии отличников.
9. Отсортировать список по алфавиту.
Вариант №7
1. Подготовить и создать таблицу:
| № | Наименование товара | Код товара | Цена ($ США) | Цена (грн.) | Количество товара | Стоимость товара грн. | НДС грн. | Оплата грн. |
| Увлажнитель волос | 3,2 | |||||||
| Ополаскиватель для волос | 1,6 | |||||||
| Шампунь из программы по восстановлению волос | 4,8 | |||||||
| Кондиционер для укрепления волос | 1,2 | |||||||
| Кондиционер для кожи головы | 4.8 | |||||||
| Жидкий гель для укрепления волос | 5,1 | |||||||
| Гель для укладки волос | 1,2 | |||||||
| ИТОГО | ||||||||
| Дата (текущая дата) 19.10.2014 |
2. На ячейках «Код товара» и «Количество товара» наложить ограничения по введению – целые числа.
3. Пересчитать цену товара в гривнах, считая курс равным 24,2 грн. и стоимость.
4. Начисления НДС в зависимости от стоимости посчитать по следующему правилу: если стоимость меньше 10 000 грн. -20%; до 20000 -15%, выше -10%.
5. Рассчитать оплату как сумму стоимости и НДС.
6. Посчитать ИТОГО.
7. Ввести сегодняшнюю дату.
8. Выделить цветом НДС посчитанные по разным значениям %.
9. Отсортировать в порядке убывания по 9 столбцу.
Вариант №8
1. Подготовить и создать таблицу:
| № п/п | Фамилия | Полный адрес | Количество проживающих | Льгота (вид) | Площадь квартиры | Плата, грн | ИТОГО | |||
| площадь | газ | вода | отопление | |||||||
| Исайкин Сергей Федорович | г.Донецк, ул.Шопена, д.83., кв 45 | 125,44 | ||||||||
| Ипполитов Евгений Федорович | г.Донецк, ул.Артема, д.68., кв.1 | 38,11 | ||||||||
| Матвийчук Ирина Николевна | г. Макеевка, ул.Московская, д.45 | 166,60 | ||||||||
| Прокофьева Владислава Викторовна | г. Донецк, ул. Разинкова, д.42, кв. 16 | 105,84 | ||||||||
| Астафьев Виктор Павлович | г.Донецк, Киевский пр-т, д.102, кв.1 | 72,83 | ||||||||
| Мураков Игорь Сергеевич | г.Донецк, ул.Щерса, д.2, кв.1 | 42,48 | ||||||||
| Ифсиленков Василий Николаевач | г.Макеевка, ул.Овсиенко, д.32., кв.1 | 156,80 | ||||||||
| Орленко Олег Юрьевич | г.Донецк, ул.Куйбышева, д.65, кв.89 | 23,60 | ||||||||
| Семенов Игорь Валерьевич | г.Донецк, ул.Постышева, д.23, кв.34 | 67,97 | ||||||||
| Евстафьев Владимир Михайлович | г.Донецк, пер.Орешково, д.17, кв.34 | 76,50 |
2. На ячейку «Льгота» наложить ограничения по введению данных от 0 до 2.
3. Разрешить вводить пользователю в ячейку 4 только целые числа.
4. Плата за общую площадь квартиры и тепло должна зависеть от площади: .квартира - за 1 м2 равна 1,26 грн.; тепло - за 1 м2 равна 4,87 грн
5. Расчетные столбцы 8, 9 зависят от количества людей, проживающих в квартире. Плата за газ на одного человека составляет 5,21 грн, за воду - 26,54 грн.
6. Если в квартире льготник вида 1, то за него по всем платежам, кроме квартиры снимается 25%. Если льготник типа 2, то за него, включая плату за квартиру, снимается по 50%.
7. Определить цвет для ячеек столбца 11 в зависимости от типа льготы.
Вариант №9
1. Подготовить и создать таблицу:
| Адрес | Количество проживающих | Льгота | Площадь квартиры кв.м. | Оплата отопления | Горячая вода | Общая сумма платежа за тепловую энергию | |||
| Начальные показания | Конечные показания | Разность | Сумма платежа за горячую воду | ||||||
| ул.Артема,д.10, кв.10 | |||||||||
| ул.Артема,д.10, кв.11 | |||||||||
| ул.Артема,д.10, кв.12 | |||||||||
| ул.Артема,д.10, кв.13 | |||||||||
| пр.Мира,д.2, кв.1 | |||||||||
| пр.Мира,д.2, кв.2 | |||||||||
| ИТОГО | |||||||||
| Дата составления расчета |
2. На ячейку «Льгота» наложить ограничения по введению данных от 0 до 50.
3. Разрешить вводить пользователю в ячейки 2,3,4 только целые числа.
4. Плата за отопление зависит от площади квартиры (цена за 1 м2 соответствует 6,2 грн).
5. Процент льготы снижает оплату на 25% или на 50%, но только для одного человека.
6. Определить цвет для ячеек столбца 10. Если общая плата лежит в диапазоне от 500 до 800 грн, то ячейка должна иметь желтый цвет, если плата менее 500 - зеленый, более 800 - розовый.
7. Сортировать данные в таблице по столбцу 4.
8. Посчитать количество квартир с каждым типом льготы.