2_MS_Excel

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

6.Переименуйте лист 1, дав ему название Площадь треугольника.

7.На листе 2 решите задачу: найдите действительные корни уравнения a 2+bx+c=0 по заданным коэффициентам а, b, с.

8.Установите контроль на ввод недопустимых значений чисел и реакцию на ошибочные входные данные в форме - "Решений нет", "Действительных решений нет", а также сообщений "Любое число является решением", "Одно решение кратности два"

(дискриминант равен нулю) .

10.Ячейки ввода чисел а, b, с сделайте незащищенными.

11.Ячейки с формулами защитите от несанкционированного доступа.

12.Переименуйте лист 2, дав ему название Квадратное уравнение.

13.Сохраните книгу под именем Задание_4_1.

Задание 2. Настройка режима проверки вводимых данных.

1.Откройте созданный ранее файл Задание_2_2.

2.Следует поставить ограничения на ввод данных в некоторые поля. Например, в ячейке B3/ куда вводится значение процентной ставки, должно находиться действительное число в интервале от 0 до 100. Чтобы установить такое ограничение:

–Установите курсор на ячейке B3.

–Дайте команду Данные → Проверка.

–В поле Тип данных выберите вариант Действительное. В полях Минимум и Максимум укажите соответственн, значения 0 и 100. Щелкните на кнопке ОК.

–Убедитесь, что поле B3 не допускает ввода значений вне заданного диапазона.

3.Добавьте дополнительную графу в таблицу: в ячейку A9 введите заголовок: «Наименование ссуды». Необходимо, чтобы для поля B9 была организована возможность выбора значения из нескольких допустимых. Для этого:

–в ячейки вне таблицы (например, в столбец F) введите несколько названий для кредитов, по одному названию в ячейке: «Срочный», «Образовательный», «Ипотечный», проч. (еще несколько вариантов придумайте сами).

–установите курсор на ячейку B9.

–Дайте команду Данные → Проверка.

–В поле Тип данных выберите вариант Список. Переключитесь на поле Источник и введите (или выберите) диапазон ячеек с вариантами названий кредитов. Щелкните на кнопке ОК.

–Проверьте, как теперь работает ввод данных в ячейку B9.

4.Сохраните файл под именем Задание_4_2.

Лабораторная работа № 5. Операции работы с базой данных Excel: сортировка и фильтрация записей.

Цель работы: научиться создавать базы данных средствами Excel, уметь производить сортировку данных, выборку по различным критериям, поиск записи; познакомиться со способами фильтрации записей списка, автофильтрацией, изучить работу с формой данных.

16 Воспользуйтесь командой Сервис→Защита→Защитить лист

	Дисциплина «Информатика и технологии	Питеркин В.М.	Раздел II: MS Excel
	программирования»
		Сироткин А.И.	стр. 21/25
	Лабораторный практикум

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

Задание 1. Сортировка данных по нескольким уровням.

1.Создайте новую рабочую книгу.

2.Присвойте листу имя «Сведения о поставках».

3.Предполагается, что предприятие получает пять видов материалов:

–бумагу,

–фанеру,

–картон,

–полиэтилен,

–ткань от пяти поставщиков, находящихся в:

–Братске,

–Казани,

–Курске,

–Мурманске,

–Череповце.

Каждый из поставщиков может поставлять любой вид материалов. Поставки производятся не чаще раза в месяц, единица измерения — тонна.

4.В ячейки А1-D1 введите заголовки полей базы данных, соответственно: Месяц,

Поставщик, Товар, Объем.

5.Введите несколько десятков записей, имеющих описанную выше структуру. Реальные «объемы поставки» значения не имеют.

6.Произведите общую сортировку базы данных. Сделайте текущей любую ячейку базы данных и дайте команду Данные → Сортировка. Убедитесь, что при этом выделяется вся (кроме заголовков полей) база данных.

7.В списке - Сортировать по, выберите пункт Месяц и Режим по возрастанию.

8.В списке - Затем по (вторичная сортировка), выберите пункт Поставщик и режим По возрастанию.

9.В списке - В последнюю очередь, выберите пункт Товар и режим По возрастанию.

10.Убедитесь, что база данных отсортирована по указанным критериям.

11.Выполните последовательную сортировку базы данных. С помощью кнопки - Отменить на панели инструментов восстановите прежний порядок записей базы данных.

12.Выберите любую ячейку в столбце Товар и щелкните на кнопке Сортировка по возрастанию на панели инструментов.

13.Выберите любую ячейку в столбце Поставщик и щелкните на кнопке Сортировка по возрастанию на панели инструментов.

14.Выберите любую ячейку в столбце Месяц и щелкните на кнопке Сортировка по возрастанию на панели инструментов. Убедитесь, что итоговый порядок сортировки тот же, что и в предыдущем случае.

15.Сохраните рабочую книгу под именем Задание_5_1.

Задание 2. Выборка данных по критериям. Работа с формой данных.

1.Откройте файл Задание_5_1

	Дисциплина «Информатика и технологии	Питеркин В.М.	Раздел II: MS Excel
	программирования»
		Сироткин А.И.	стр. 22/25
	Лабораторный практикум

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

2.Произведите фильтрацию данных. Выделите заголовки столбцов. Выполните команду Данные → Фильтр → Автофильтр. Обратите внимание на появление раскрывающих кнопок у заголовков полей базы данных.

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

4.Чтобы отобрать наиболее крупные разовые поставки, щелкните на раскрывающей стрелке у поля Объем и выберите в списке вариант Первые 10.

5.Выберите с помощью счетчика число 20 и далее пункт наибольших и вариант % от количества элементов. Щелкните на кнопке ОК. В результате будет отобрано 20% записей, содержащих наибольшие значения объема поставок.

6.Просмотрите записи списка с помощью формы данных, добавьте новые.17

7.Сформируйте условие отбора с помощью формы данных: поиск всех поставок картона из Мурманска.18

9.Просмотрите отобранные записи.

10.Сформируйте собственные условия отбора записей и просмотрите их.

11.Сохраните книгу под именем Задание_5_2.

Лабораторная работа № 6. Структурирование таблиц, автоматическое подведение итогов.

Цель работы: научиться структурировать таблицу ручным способом, автоструктурировать, добавлять дополнительные иерархические уровни, уметь выполнять автоматическое подведение итогов.

Задание 1. Автоструктурирование, создание иерархических уровней.

1.Создайте новую рабочую книгу.

2.На первом листе создайте таблицу расчета заработной платы, заполнив ячейки в соответствии со списком:

А1	Фамилия
В1	Зарплата
С1	Подоходный налог
D1	Пенсионный фонд
E1	Общий налог
F1	Надбавка
G1	Премия
H1	Итого доплат
I1	Сумма к выдаче

17установите курсор в поле списка; выберите Данные→Форма

18Воспользуйтесь кнопкой Критерии в окне Форма данных

	Дисциплина «Информатика и технологии	Питеркин В.М.	Раздел II: MS Excel
	программирования»
		Сироткин А.И.	стр. 23/25
	Лабораторный практикум

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

A6 Итого

3.Переименуйте лист в Автоструктурирование.

4.Столбцы Фамилия, Зар. плата, Надбавка, Премия заполните константами, которые придумайте сами.

5.В строке Итого подсчитайте суммы по каждому столбцу.

6.В остальные столбцы введите формулы.

7.Создайте автоструктуру таблицы расчета заработной платы. Для этого:

–установите курсор в ячейку А1;

–выберите Данные → Группа и Структура → Создание структуры.

8.Введите в структурированную таблицу дополнительный иерархический уровень по строкам. Для этого:

–вставьте пустые строки после первых двух фамилий и вторых двух фамилий;

–выделите строки с первыми двумя фамилиями;

–выберите Данные → Группа и структура → Группировать;

–аналогичную операцию проделайте для других двух строк.

9.Сохраните книгу под именем Задание_6_1.

Задание 2. Автоматическое подведение итогов.

1.Создайте новую рабочую книгу, первому ее листу дайте имя «Итоги», остальные – удалите.

2.Создайте на листе «Итоги» таблицу так, как это показано на рисунке:

			А	В		С	D	E		F	G
			Номер	Номер		Код	Табельный №	Вид
	1			зачетной						Дата	Оценка
			группы		предмета		преподавателя	занятия
				книжки
	2		133	1		п1	а1	лек		5.09.05	3
	3		133	1		п1	а1	практ		7.09.05	4
	4		133	1		п2	а2	практ		3.10.05	5
	5		134	2		п2	а3	практ		10.10.05	2
	6		134	3		п1	а3	практ		24.10.05	3
	7		133	5		п2	а2	лек		20.09.05	3
	8		133	4		п2	а1	лек		12.09.05	4
	9		135	4		п2	а3	лек		28.09.05	5
	10		133	6		п1	а3	практ		27.10.05	5
	11		135	6		п1	а2	лек		13.10.05	4
	12		134	3		п2	а2	практ		29.09.05	4
Дисциплина «Информатика и технологии							Питеркин В.М.			Раздел II: MS Excel
		программирования»
							Сироткин А.И.			стр. 24/25
	Лабораторный практикум

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

13	135	2	п1	а1	лек	30.09.05	3
14	135	2	п1	а2	практ	22.10.05	2
15	134	6	п2	а1	лек	15.09.05	5
16	136	4	п1	а1	лек	18.10.05	2
17	133	3	п2	а3	практ	14.10.05	2

3.Отсортируйте записи по номеру группы, коду предмета, виду занятий.

4.Создайте первый уровень итогов – средний балл по каждой учебной группе; второй уровень – средний балл по каждому предмету для каждой учебной группы; третий уровень – средний балл по каждому виду занятий для каждого предмета по всем учебным группам. Для этого:

–установите курсор в поле списка;

–выберите Данные → Итоги;

–в окне Промежуточные итоги укажите для первого уровня:

При каждом изменении в: Номер группы Операция: среднее Добавить итоги по: оценка

Заменить текущие итоги: нет Конец страницы между группами: нет Итоги над данными: да

–аналогичные операции проделайте для второго и третьего уровней.

5.Просмотрите элементы структуры, закройте и откройте иерархические уровни.

6.Сохраните книгу под именем Задание_6_2.

	Дисциплина «Информатика и технологии	Питеркин В.М.	Раздел II: MS Excel
	программирования»
		Сироткин А.И.	стр. 25/25
	Лабораторный практикум