Здавалка
Главная | Обратная связь

Условное форматирование



Если необходимо, чтобы ячейка меняла формат, который ей задан (заливка, шрифт, начертание, рамки и т.д.) при выполнении определенного условия, нужно:

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. Посчитать количество квартир с каждым типом льготы.

 

 







©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.