Ограничения на ввод данных

Чтобы определить данные, которые допустимы к вводу, необходимо:

1. Выбрать ячейку, которую нужно проверить.

2. Выбрать команду Проверка данных в группе Работа с данными вкладки Данные. В диалоговом окне Проверка вводимых значений открыть вкладку Параметры.

3. Определить необходимый тип проверки.

 

 

 

Чтобы разрешить ввод значений, которые находятся в заданных пределах. В списке Тип данных необходимо выбрать вариант Целое число или Действительное. В списке Значения надо задать необходимое ограничение. Например, чтобы установить нижнюю и верхнюю границу, следует выбрать значение между. Задать минимальное, максимальное или определено допустимое значение (рис. 13).

 

Рисунок 13. - Диалоговое окно Проверка вводимых значений. Условие проверки.

Инструкции:

1. Выбрать вкладку Сообщение об ошибке и установить флажок Выводить сообщение об ошибке.

2. Выбрать один из следующих параметров для поля Вид (Останов, Предупреждение, Сообщение).

Для отображения информационного сообщения, которое не запрещает введение неправильных данных, необходимо выбрать значение Сообщение. Для отображения предупреждения, которое не запрещает введение неправильных данных, необходимо выбрать значение Предупреждение. Чтобы запретить ввод неверных данных, необходимо выбрать значение Останов.

3. Указать название и ввести текст для сообщения (до 225 знаков).

 

Рисунок 14. – Окно проверки вводимых значений.

Работа с формулами

Формулы представляют собой выражение, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=). Ниже приведен пример формулы, который умножает 2 на 3 и добавляет к результату 5.

= 5 + 2 * 3

Формула также может включать следующие элементы: функции, ссылки, операторы и константы. = ПИ()*А2^5

Элементы формулы:

1. Функции. Функция ПИ() возвращает значение числа π: 3,142

2. Ссылка (или имена). A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например, 5.

4. Операторы. Оператор ^ возвдит число в степень, а звездочка (*) выполняет умножение. В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 50.

= ЕСЛИ (СРЗНАЧ (F2: F5)> 50; СУММ (G2: G5), 0)

Допустимые типы вычисляемых значений. Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение или ИСТИНА или ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение или ИСТИНА или ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!». Логические операции можно добавлять выбрав команду Логические группы Библиотека функций вкладки Формулы (рис.14).

 

 

Ограничение количества уровней вложения функций. В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функции СРЗНАЧ и СУММ считаются функциями второго уровня, так как обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня и так далее.

Чтобы вставить функцию в формулу, установите указатель на позицию, в которую следует ввести формулу, и нажмите кнопку мыши.

1. На вкладке Формулы в группе Библиотека функций выбрать команду Вставить функцию.

2. В списке Категория выберите категорию функции.

3. В списке Функция выберите необходимую функцию.

4. Нажмите кнопку ОК (рис.15).

5. Введите величины, ссылки, имена, формулы и функции в поля аргументов.

 

 

Рисунок. 15. - Мастер вставки функций

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

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

1. Выделить ячейки, которые должны автоматически менять свой цвет.

2. Выбрать на вкладке Главная группы Стили команду Условное форматирование (рис. 16).

Рисунок 16. – Команда Условное форматирование.

В открывшемся окне можно задать условия и параметры форматирования ячейке при выполнении условия. Подробнее это можно рассмотреть выбрав команду Создать правило, и тем самым открыв диалоговое окно Создание правила форматирования (рис.17).

 

Рисунок. 17 - Диалоговое окно Создание правила форматирования.

Изменяя тип правила можно форматировать не только ячейки, но и значение, для которых выполняется приведенное условие (рис.18).

 

 

Рисунок 18. – Форматирование значений.

Если для диапазона ячеек заданные критерии условного форматирования, то нельзя отформатировать эти ячейки через меню Формат ячеек в ручную. Чтобы вернуть эту возможность надо удалить условия в окне Условное форматирование с помощью команды Удалить правила.

 

 

Рисунок 19. – Команда Удалить правила.

Порядок выполнения работы:

1. Подготовить и создать таблицу согласно варианту, указанному в индивидуальном задании.

2. Наложить ограничения по введению данных.

3. Определить основные функции для выполнения задачи и сформировать формулы для вычисления.

4. Провести вычисления по заданным условиям.

Варианты заданий

Вариант №1

1. Подготовить и создать таблицу:

Сведения об успеваемости студентов гр. КН-15
№ п/п	Ф.И.О.	Математика	Программирование	Системотехника	Ин. Язык	КИТ	Пакеты	Средний балл	Стипендия
	Малов С.Ю.
	Величко Д.С.
	Возняк А.М.
	Мальцев А.Ю.
	Иванченко А.В.
	Липчик А.Г.
	Малышко А.Н.
	Волков Н.В.
	Матюшенко А.А.
	Бурлака А.А.
				Дата (текущая дата):	22.02.2015

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. Отсортировать список по алфавиту.

7. Вывести текущую дату.

 

 

Вариант №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

Семенов

Игорь

Валерьевич

г.Донецк, ул.Постышева, д.23, кв.34

57,60

Евстафьев

Владимир

Михайлович

г.Донецк, пер.Орешково, д.17, кв.34

50,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. Сортировать данные в таблице по столбцу 6.

8. Посчитать количество квартир каждой категории.

Вариант №4

1. Подготовить и создать таблицу:

Акт выполненных работ СТО
Дата заказа	Номер заказа	Работа	План	Факт	Цена работы, грн	Сумма оплаты, грн
Начало	Окончание	Начало	Окончание
12.11.09		Дезинфекция системы кондиционирования	13.11.09	13.11.09	13.11.09	13.11.09	100,00
12.11.09		Прокачка сцепления	13.11.09	13.11.09	13.11.09	15.11.09	35,00
12.11.09		Замена троса ручного тормоза	13.11.09	13.11.09	13.11.09	13.11.09	80,00
12.11.09		Замена тормозных трубок	14.11.09	14.11.09	14.11.09	14.11.09	110,00
12.11.09		Замена троса ручного тормоза	14.11.09	15.11.09	14.11.09	15.11.09	200,00
13.11.09		Шиномонтаж R17/20	13.11.09	13.11.09	13.11.09	16.11.09	30,00
13.11.09		Дезинфекция системы кондиционирования	14.11.09	15.11.09	14.11.09	15.11.09	80,00
13.11.09		Снятие/установка колеса, замена пружины стойки	15.11.09	16.11.09	15.11.09	17.11.09	230,00
13.11.09		Замена рулевой тяги	15.11.09	17.11.09	15.11.09	17.11.09	70,00

 

2. На ячейки начала и окончания плановых и фактических работ наложить ограничения по введению данных: - Все даты должны быть позже даты заказа и не позднее даты одного месяца спустя; - Дата окончания работы должна быть больше чем дата ее начала.

3. Сумма платы за работу зависит от своевременности ее выполнения. Если задержка выполнения работы есть, но не превышает 1 дня, то работа становится дешевле на 5%, если превышает от 1 дня до 3 дней – то на 20%, в противном случае на 50%.

4. Вывести текущую дату и время.

5. Посчитать количество работ, выполненных не в срок

6. Отсортировать по столбцу 9.

 

Вариант №5

1. Подготовить и создать таблицу:

Сведения о почасовой оплате
№	ФИО	Название дисциплин	Объем часов	Ставка за час.	Начис-лено	Отчис-ления	к выдаче
Лекции	Практ.	Всего
	Найденова Т.Б.	Основы экономики				52,25
	Сорока А.А.	Основы законодательства				68,80
	Осипова С.Ю.	Архитектура ПК				52,25
	Гулиев Т.В.	Архитектура ПК				40,25
	Зуева С.А.	Операционные системы				52,25
	Казак Т.В.	Операционные системы				40,25
	Воеводина А.Ю.	Сервисные программы				68,80
		ИТОГО
Дата (текущая дата) 19.10.2014

 

2. На ячейки «Лекции» и «Практ» наложить ограничения по введению не более 120.

3. «Всего» рассчитывать как сумму «Лекции» и «Практ».

4. «Начислено», «Отчисления» и «К выдаче» - вычислять по формулам.

5. «Отчисления» рассчитывать по следующим условиям:

Если «Начислено» <2000, процент отчисления составляет 10%, если> 4000 - 20%, иначе 15%.

6. Посчитать «ИТОГО».

7. Посчитать сколько выплат более 4000, выделить их автоматически другим цветом.

8. Вывести текущую дату и время.

 

Вариант №6

1. Подготовить и создать таблицу:

Начисление стипендии
№ п/п	Ф.И.О.	Математика	Программирование	Системотехника	КИТ	Средний балл	Стипендия
	Бурлака А.А.
	Величко Д.С.
	Возняк А.М.
	Волков Н.В.
	Иванченко А.В.
	Липчак А.Г.
	Мальцев А.Н.
	Малов С.Ю.
	Матюшенко А.А.
	Марченко В.В.
				22.02.2015

2. На ячейках по предметам наложить ограничения по введению от 30 до 100.

3. Балы в национальной шкале посчитать по следующим условиям: < 60 – 2; 60<=3<=74; 75<=4<=89; 90<=5<=100;

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

5. Стипендию вычислить согласно условиям 4,5 < ср. балл <= 5 стипендия 450 грн. 4 < ср. балл <= 4,5 стипендия 400 грн. 3,5 <= ср. балл <= 4 стипендия 300 грн.

6. Посчитать всего оценок "5", "4", "3", "2".

7. Выделить цветом стипендии отличников.

8. Отсортировать список по алфавиту.

9. Вывести текущую дату и время.

Вариант №7

1. Подготовить и создать таблицу:

№	Наименование товара	Код товара	Цена ($ США)	Цена (грн.)	Количество товара	Стоимость товара грн.	НДС грн.	Оплата грн.
	Увлажнитель волос		3,2
	Ополаскиватель для волос		1,6
	Шампунь из программы по восстановлению волос		4,8
	Кондиционер для укрепления волос		1,2
	Кондиционер для кожи головы		4.8
	Жидкий гель для укрепления волос		5,1
	Лак для волос.		4,8
	Гель для укладки волос		1,2
ИТОГО
Дата (текущая дата) 19.10.2014

2. На ячейках «Код товара» и «Количество товара» наложить ограничения по введению – целые числа.

3. Пересчитать цену товара в гривнах, считая курс равным 24,2 грн. и стоимость, равную произведению 5 и 6 столбцов.

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

 

2. На ячейку «Льгота» наложить ограничения по введению данных от 0 до 2.

3. Разрешить вводить пользователю в ячейку 4 только целые числа.

4. Плата за общую площадь квартиры и тепло должна зависеть от площади квартиры - за 1 м² плата равна 1,26 грн.; тепло - за 1 м² равна 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 м² соответствует 41,2 грн).

5. Процент льготы снижает оплату на 25% или на 50%, но только для одного человека.

6. Определить цвет для ячеек столбца 10. Если общая плата лежит в диапазоне от 500 до 800 грн, то ячейка должна иметь желтый цвет, если плата менее 500 - зеленый, более 800 - розовый.

7. Сортировать данные в таблице по столбцу 4.

8. Посчитать количество квартир с каждым типом льготы.вести дату составления отчета.

 

Вариант №10

1. Подготовить и создать таблицу:

	Учет выполненных работ по ремонту компьютерной техники
Дата заказа	Работа	Номер заказа	Категория работы	План	Факт	Цена работы, грн	Сумма оплаты, грн
Начало	Окончание	Начало	Окончание
12.11.09	Заправка картриджа принтера			13.11.09	13.11.09	13.11.09	13.11.09	70,00
12.11.09	Диагностика материнской платы			13.11.09	13.11.09	13.11.09	15.11.09	120,00
12.11.09	Замена картриджа принтера			13.11.09	13.11.09	13.11.09	13.11.09	70,00
12.11.09	Диагностика материнской платы			14.11.09	15.11.09	14.11.09	17.11.09	120,00
12.11.09	Замена видео карты			14.11.09	15.11.09	14.11.09	15.11.09	100,00
13.11.09	Замена звуковой карты			13.11.09	13.11.09	13.11.09	16.11.09	100,00
13.11.09	Прочистка системного блока			14.11.09	15.11.09	14.11.09	15.11.09	80,00
13.11.09	Заправка картриджа принтера			15.11.09	16.11.09	15.11.09	17.11.09	70,00

 

2. На ячейки начала и окончания плановых и фактических работ наложить ограничения по введению данных: - Все даты должны позже даты заказа и не позднее даты одного месяца спустя; - Дата и время окончания работы должна быть больше чем дата ее начала.

3. Значения в столбцах 2 и 3 должны быть только целыми.

4. Сумма платы за работу зависит от своевременности ее выполнении и ее категории. Если задержка выполнения работы есть, но не превышает 1 дня, то работа становится дешевле на 5%. Если превышает от 1 дня до 3 дней – то для работ категории 2 на 20%, а для работ категории 3 на 15%. Если задержка более 3 дней, то на 50% для категории 1, 2, и на 30% для категории 3.

5. Посчитать количество работ каждой категории.

6. Отсортировать по категориям, а в них по величине оплаты.

Вариант №11

1. Подготовить и создать таблицу:

№ п/п	Ф.И.О.	Средний балл при поступлении	Нейросетевые технологии	Параллельные вычисления	Нечеткая логика	Корпоративные системы	Средний балл за семестр	Общий средний балл	Стипендия
	Андреев С.Ю.	5,0
	Величко Д.С.	3,7
	Возняк А.М.	4,5
	Малов С.Ю.	5,0
	Иванченко А.В.	4,9
	Липчик А.Г.	4,8
	Кузнецов А.Н.	3,9
	Волков Н.В.	4,2
	Матюшенко А.А.	4,3
	Бурлака А.А.	4,0
			Дата (текущая дата):	22.02.2015

 

2. На ячейках по предметам 4, 5, 6, 7 наложить ограничения по введению целых чисел от 2 до 5.

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

4. Стипендию вычислить согласно условиям 4,5 < ср. балл < 5 стипендия 760 грн. 4 < ср. балл <= 4,5 стипендия 540 грн. 3,5 <= ср. балл <= 4 стипендия 350 грн.

5. Если средний бал за семестр = 5, то добавляется надбавка 25% от 760, и если при этом бал при поступлении = 5, то еще 10% от 760.

6. Посчитать всего оценок "5", "4", "3", "2".

7. Отсортировать список по алфавиту.

8. Вывести дату составления отчета.

 

 

Вариант №12

1. Подготовить и создать таблицу:

<

Сведения о заработной плате рабочих со сдельной формой оплаты
№ п/п	Номер бригады	Табельный номер	ФИО	Ставка за ед.изделия грн	Количество изделий	Количество бракованных изделий	Начислено	Удержано	К выплате
			Иванов И.С.	30,00
			Петренко С.А.	30,00
			Васнецов И.Е.	24,50
			Игорев Е.А.	24,50
			Макеенко А.С.	20,00
			Каровацкий П.С.	30,00
			Музынкова Г.И.	24,50
			Проценко Т.Н.	30,00
			Кириенко М.Н.	24,50
			Матросова И.М.	24,50

Поделиться: