Список студентов группы №

№ п/п	ФИО	Дата рождения	Год поступления	Оценка
1	Шевчук С.И.	9.2.79
2	Пинчук О.Н.	09.02.79
3	Ковшик В.М.	9 фев 79
4	Русакевич Г.Б.	09 фев 79
5	Зайцев Т.Л.	9 Февраль, 1979
6	Лютаревич В.Н.	Февраль 79
7	Шитыко Ю.А.	9 фев

4. Сохранить рабочую книгу на диске.

5. Открыть новую рабочую книгу.

6. Создать таблицу вида (см. Таблицу 3)

Таблица 3

Форматы данных ms Excel

Числовой формат	Параметры формата	Число в общем формате	Результат
Общий		12345,678
Числовой	Число десятичных знаков — 1. Установить флажок "Разделитель групп разрядов".	12345,678
Денежный	Число десятичных знаков — 2. Обозначение — USD.	12345,678
Финансовый	Число десятичных знаков — 0. Обозначение — £ Английский (Великобритания).	12345,678
Дата	Тип — 16.04.97.	35795,75
Время	Тип — 13:30.	35795,75
Процентный	Число десятичных знаков — 0.	0,253
Дробный	Тип — "Простыми дробями (1/4)".	22,375
Экспоненциальный	Число десятичных знаков — 1.	=ПИ()*10000^2
Текстовый		=ПИ()*10000^2
Дополнительный 1	Номер телефона	8005551212
Дополнительный 2	Табельный номер	123456789
(все форматы)	Создать собственный числовой формат	12345,678

7. В ячейку ввести число с двумя знаками после запятой. Округлить до целого числа.

8. Ввести в ячейку число. Если это число больше 1000, оно отображается на экране синим цветом. Если меньше 100 — красным. В остальных случаях — зеленым.

9. В ячейку ввести число. Если это число отрицательное, оно отображается на экране красным цветом. Если положительное — зеленым. Нулевое значение вообще не выводится.

10. В три ячейки ввести коэффициенты квадратного уравнеиия a, b, c. В четвертой вычисляется дискриминант по формуле d=b²-4ac. Вместо численного результата на экран выводится текст о количестве корней уравнения.

11. В ячейку ввести число. Если это число меньше нуля, на экран выводится текст "Число отрицательное". Если оно больше нуля — "Число положительное". В оставшемся случае — "Нуль".

12. В ячейку ввести число. Если это число положительное, на экран вывести текст "Цена ххх рублей". Если отрицательное — "Отрицательной цены не бывает".

13. В ячейку ввести число. Если это число больше 100, на экран вывести текст "ххх больше 100". В противном случае — "ххх меньше 100".

14. В ячейку ввести число. Вывести его на экран в долларовом денежном формате с точностью до двух обязательных знаков после запятой. Например, $500,00.

15. В ячейку ввести свою фамилию. На экран вывести текст "Фамилия — студент(ка) группы 105ххх".

16. В ячейку ввести свои фамилию, имя, отчество. На экран вывести текст "Уважаемый ФИО".

17. В ячейку ввести текущую дату. Определить, сколько дней прошло от начала столетия до сегодняшнего дня.

18. В ячейку ввести текущую дату. На экран вывести текст "Сегодня" и полное названия дня недели.

19. В ячейку ввести текущую дату. На экран вывести текст "Сегодня" и полное названия месяца.

20. В ячейку ввести текущее время. Определить часть суток, прошедшую от начала дня до настоящего времени.

Контрольные вопросы

1. Основные типы данных MS Excel. Особенности их ввода.

2. Назначение числовых форматов.

3. Виды числовых форматов. Способы отображения данных.

4. Система хронологии MS Excel. Форматирование даты и времени.

5. Создание специального числового формата. Коды числовых форматов.

6. Копирование созданного формата в другую рабочую книгу.

7. Создание "скрывающего" формата.

8. Что происходит, если после применения формата число не помещается в ячейке?

9. Что происходит, если точность ввода числа не совпадает с точностью, задаваемой форматом? Как этого избежать?

Лабораторная работа №5

Ф О Р М А Т И Р О В А Н И Е Т А Б Л И Ц В M S E X C E L

Цель работы: изучить возможности оформления таблиц в MS Excel;

получить навыки работы со стилями;

научиться применять автоформатирование и форматирование по образцу.

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

1. Запустить MS Excel.

2. Открыть рабочую книгу, созданную в лабораторной работе №4.

3. Отредактировать таблицу в соответствии с заданием (см. Таблицу 4).

Таблица 4

Список студентов группы №
№	ФИО	Дата рождения	Год поступления	Оценки			Средний балл
				Математика	Физика	Англ.яз
1	Шевчук С.И.	9.2.79	1998	5	4	5
2	Пинчук О.Н.	09.02.79	1997	3	4	5
3	Ковшик В.М.	9 фев 79	1998	4	3	4
4	Русакевич Г.Б.	09 фев 79	1997	4	5	5
5	Зайцев Т.Л.	9 Февраль, 1979	1996	5	5	5
6	Лютаревич В.Н.	Февраль 79	1996	4	5	5
7	Шитыко Ю.А.	9 фев	1998	3	3	4

4. К таблице применить следующие атрибуты форматирования:

• выравнивание (Формат | Ячейки… | Выравнивание);

• шрифты (Формат | Ячейки… | Шрифт);

• цвет фона (Формат | Ячейки… | Вид);

• ширина столбцов и высота строк (Формат | Строка | Высота…) или (Формат | Столбец | Ширина…);

• рамка (Формат | Ячейки… | Граница). После вычерчивания рамок отключить вывод на экран сетки.

5. Скопировать отформатированную таблицу на этот же лист рабочей книги пользуясь буфером обмена или технологией Drag&Drop.

6. Перейти к новой таблице.

7. Отменить ранее примененные атрибуты форматирования и вернуть установленные по умолчанию (Выделить таблицу | Правка | Очистить | Форматы).

8. Создать свой стиль форматирования, присвоить ему имя и добавить в список имеющихся в MS Excel встроенных стилей (Формат | Стиль… | Ввести новое имя во поле "Имя стиля" | Добавить | Изменить… | Настроить требуемые параметры).

9. Создать еще три копии таблицы (на этом же листе рабочей книги).

10. Применить к третьей таблице созданный вами стиль форматирования (Выбрать ячейку | Формат | Стиль… | В поле "Имя стиля" выбрать требуемое имя | OK).

11. Применить к четвертой таблице автоформатирование (Выделить таблицу | Формат | Автоформат… | В "Списке форматов" выбрать необходимое имя | OK).

12. Пятую таблицу отформатировать по образцу первой таблицы (Выделить первую таблицу | Нажать кнопку "Формат по образцу" | Выделить вторую таблицу).

13. Сохранить рабочую книгу на диске.

Контрольные вопросы

1. Назначение стиля. Атрибуты стиля.

2. Создание нового стиля.

3. Восстановление атрибутов форматирования по умолчанию.

4. Копирования атрибутов формата с одних ячеек на другие.

5. Изменение размеров ячеек.

Лабораторная работа №6

А Д Р Е С А Ц И Я В M S E X C E L

Цель работы: освоить построение формул с использованием абсолютного, относительного и смешанного стилей ссылок;

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

1. Запустить MS Excel.

2. Создать таблицу вида (см. Таблицу 5)

Таблица 5

Коэффициент теплопроводности

№ п/п	Марка стали					t0	при температуре t
							1000	1010	1020	1030	1040	1050	1060	1070	1080	1090	1100
1	Малоуглеродистая	54	0	32	0,24	975
2	Среднеуглеродистая	48	0	27	0,29	935
3	Высокоуглеродистая	48	0	27	0,23	900
4	Низкоуглеродистая	42	0	19	0,24	950
5	Хромоникелевая	12	1,4	0	1	950
	Среднее Значение

Коэффициент теплопроводности  рассчитать по формуле

.

Формулу для расчетов ввести в одну ячейку. Пользуясь маркером заполнения растянуть формулу на другие ячейки.

3. Сохранить рабочую книгу на диске.

Контрольные вопросы

1. Стили ссылок A1 и R1C1. Переключение стилей.

2. Абсолютная, относительная и смешанная адресация. Замена абсолютных ссылок на относительные и наоборот.

3. Использование собственных имен ячеек.

Лабораторная работа №7

З А Щ И Т А Д А Н Н Ы Х В M S E X C E L

Цель работы: изучить средства защиты данных в MS Excel.

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

1. Запустить MS Excel.

2. Открыть рабочую книгу, созданную в лабораторной работе №6.

3. Включить защиту листа “Лист1”, установив пароль для доступа. Ввод разрешить только в столбец "Марка стали" (Выделить столбец "Марка стали" | Формат | Ячейки… | Защита | Сбросить флажок "Защищаемая ячейка" | OK | Сервис | Защита | Защитить лист… | OK).

4. Защитить файл рабочей книги с помощью пароля (Файл | Сохранить как… | Параметры… | Задать пароль для открытия файла | OK | Подтвердить пароль | OK).

5. Закрыть рабочую книгу.

6. Открыть повторно файл рабочей книги. Снять защиту с “Лист1” (Сервис | Защита | Снять защиту листа…), снять защиту с рабочей книги (Файл | Сохранить как… | Параметры… | Удалить пароль для открытия файла | OK).

7. Перейти на “Лист3”. Скрыть содержимое ячеек А1:В6 (Выделить диапазон ячеек | Формат | Ячейки… | Число | В поле "Числовые форматы" выбрать "(все форматы)" | В поле "Тип" задать ";;;" | OK). Чтобы вновь отобразить скрытые данные, установите тип числового формата "Общий".

8. Перейти на “Лист2”. Скрыть столбцы А и В, строки 3 и 4 (Выделить требуемые столбцы/строки | Формат | Столбец/Строка | Скрыть).

9. Скрыть весь “Лист2” (Формат | Лист | Скрыть). Скрыть окно рабочей книги (Окно | Скрыть).

10. Отобразить на экране ранее скрытую информацию (Окно | Отобразить | Выбрать имя требуемой рабочей книги | OK | Выделить столбцы/строки, смежные со скрытыми | Формат | Столбец/Строка | Отобразить).

Контрольные вопросы

1. Средства защиты данных в MS Excel.

2. Реализация защиты ячеек, рабочих листов и рабочих книг.

3. Скрытие строки, столбца, листа рабочей книги, формул в ячейках, окна рабочей книги.

Лабораторная работа №8

В Ы Ч И С Л Е Н И Я В Т А Б Л И Ц А Х M S E X C E L

Цель работы: изучить возможности применения формул для выполнения расчетов при представлении данных в табличном виде;

приобрести опыт работы с мастером функций MS Excel.

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

Информацию по использованию любой из заданных функций получить с помощью справочной системы Microsoft Excel.

1. Перемножить две матрицы A и B с помощью функции МУМНОЖ. Для одной из матриц вычислить определитель, для другой — найти обратную. Использовать функции МОПРЕД, МОБР.

2. Поменять ориентацию массива с информацией (см Таблицу 6), использую функцию ТРАНСП.

Таблица 6

	Январь	Февраль	Март	Апрель
1 декада	150 р.	200 р.	200 р.	350 р.
2 декада	150 р.	200 р.	300 р.	350 р.
3 декада	200 р.	200 р.	350 р.	350 р.

3. Создать таблицу для расчета стоимости проката товара (см. Таблицу 7).

Таблица 7

Название товара	Дата выдачи	Дата возврата	Продолжи-тельность проката	Стоимость проката за сутки	Сумма к оплате
Телевизор	12.03.98	22.03.98		7 000 р.
Холодильник	10.05.98	10.05.99		15 000 р.
Магнитофон	17.04.98	17.07.98		2 000 р.
Радиоприемник	22.12.98	8.01.99		500 р.

4. Создать таблицу для расчета подоходного налога (см. Таблицу 8).

Таблица 8

Фамилия, инициалы	Начислено	Подоходный налог

Величина подоходного налога определяется по таблице 9, исходя из совокупного годового дохода и величины среднемесячной заработной платы.

Таблица 9

Размер дохода	Сумма налога
до 240 мин. з/п	9%
от 240 мин. з/п + 1 руб. до 600 мин. з/п.	21,6 мин. з/п + 15% от суммы, превышающей 240 мин. з/п
от 600 мин. з/п + 1 руб. до 840 мин. з/п.	75,6 мин. з/п + 20% от суммы, превышающей 600 мин. з/п
от 840 мин. з/п + 1 руб. до 1080 мин. з/п.	123,6 мин. з/п + 25% от суммы, превышающей 840 мин. з/п
от 1080 мин. з/п + 1 руб.	183,6 мин. з/п + 30% от суммы, превышающей 1080 мин. з/п

4. Пользуясь расписанием движения поездов (см. Таблицу 10) , рассчитать продолжительность поездки на каждом составе.

Таблица 10

№ поезда	Маршрут	Время отправления	Время прибытия	Продолжительность поездки
1	Минск-Москва	20:40	6:23
52	Минск-Санкт-Петербург	16:42	8:33
197	Калининград-Москва	10:46	23:54

4. Используя функцию ИНФОРМ, определить количество активных рабочих листов, текущую версию операционной системы.

5. Используя функции округления ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ округлить число 53,47 до целого, до десятков в большую сторону, до десятков в меньшую сторону, до десятых по правилам округления.

6. Из фамилии, имени, отчества, номера группы, хранящихся в различных ячейках, сформировать строку вида: Фамилия, инициалы — студент группы 105ххх. Использовать функцию ПСТР и оператор конкатенации (сцепления) строк "&".

7. Используя функции РИМСКОЕ, ЧАС, МИНУТЫ, ТДАТА, а также оператор конкатенации (сцепления) строк "&" определить текущее время и вывести его на экран в виде: "ххх часов ххх минут", где ххх — римские цифры.

8. Составить таблицу успеваемости студентов (см. Таблицу 11). Для расчетов использовать функции СЧЕТЕСЛИ, СЧЕТЗ.

Таблица 11

Фамилия, инициалы	Предмет			Средний балл	Количество сданных экзаменов	Количество оценок "отлично"	Количество оценок "хорошо"	Количество оценок "удовлетворительно"
	Математика	Физика	Информатика
Зайцев Т.Л.	3	5	5
Лютаревич В.Н.	4		5
Шитыко Ю.А.		5	5

4. Используя функции MIN, ПОИСКПОЗ, ИНДЕКС, по таблице 12 определить фамилию человека, имеющего наименьшую заработную плату.

Таблица 12

Фамилия, инициалы	Заработная плата
Ковшик В.М.	3500000
Русакевич Г.Б.	2460000
Зайцев Т.Л.	5670000
Лютаревич В.Н.	4100000
Шитыко Ю.А.	4500000

4. Рассчитать тепловой баланс кольцевой печи на основе данных из таблицы 13. Подсчитать итоги и удельные веса каждой статьи затрат с точностью до двух знаков после запятой.

Таблица 13

Статья баланса	кДж/ч106	Удельный вес, %
Приход тепла
Теплота горения топлива	52,46
Физическое тепло воздуха	3,97
Тепло, выделенное от окисления железа	3,57
ИТОГО:
Расход тепла
Тепло, усвоенное металлом от горения топлива	26,17
Тепло, усвоенное металлом от окисления железа	2,17
Потери тепла с уходящими газами	26,40
Потери тепла через кладку	3,35
Потери тепла через окна	0,61
Потери тепла с окалиной	1,30
ИТОГО:

4. Грузовой автомобиль стоимостью 30000 долл. имеет срок эксплуатации 15 лет. В конце срока эксплуатации его остаточная стоимость 5300 долл. С помощью функции АМГД рассчитать годовую амортизацию за каждый год эксплуатации.

14. Долговое обязательство казначейства выпускается при следующих условиях:

• Дата выпуска 28.02.99.

• Дата соглашения 1.04.99.

• Дата первой выплаты 30.06.99.

• Ставка 12%.

• Номинал 10000 руб.

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

• Базис европейский.

Пользуясь функцией НАКОПДОХОД из "Пакета анализа" рассчитать величину накопленного дохода.

Контрольные вопросы

1. Составные элементы формул. Правила записи формул. Приоритет операций.

2. Наиболее распространенные коды ошибок и методы их устранения.

3. Трассировка ошибок. Влияющие и зависимые ячейки. Изменение ссылок в формулах при перемещении или копировании влияющих ячеек, при перемещении или копировании самих формул.

4. Понятие внешних ссылок.

5. Выполнение пересчета формул вручную.

6. Отображение формул вместо значений. Замена формул в ячейках на их значения.

7. Категории функций MS Excel.

8. Вызов мастера функций. Вставка функции в формулу с использованием мастера функций. Получение справочной информации по функциям MS Excel.

9. Понятия автовычисления и автосуммирования. Правила их использования.

Лабораторная работа №9

П О Д Б О Р П А Р А М Е Т Р А И П О И С К Р Е Ш Е Н И Я

С Р Е Д С Т В А М И M S E X C E L

Цель работы: изучить особенности работы со средством "Подбор параметра" и надстройкой "Поиск решения".

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

1. Используя команду "Подбор параметра…" из меню "Сервис", найти значение х, при котором формула возвратит указанный результат. Результаты работы отобразить в виде таблицы (см. Таблицу 14):

Таблица 14

Переменная			Функция	Результат
x	y	Z
	1,7	3,46			7,85
	2,31	1,28			14,43
	4,25	3,04			9,82
	4,87	3,23			7,03
	2,05	0,28			9,31
	1,02	2,47			8,15
	1,38	2,49			11,44
	2,13	1,29			10,08
	4,53	12,01			12,21
	1,02	0,03			4,05
	1,02	5,67			12,82
	2,13	9,14			4,02

2. Используя надстройку "Поиск решения…" из меню "Сервис", найти точку пересечения трех кривых: f₁(x)=ln(x), f₂(x)=0,047x² , f₃(x)=0,293x. Начальное значение х=10 для каждой функции. В качестве целевой выбрать функцию СРОТКЛ. Аргументами функции являются значения расчетных формул. Если надстройка отсутствует в меню "Сервис", установить соответствующий флажок в списке "Надстройки…", вызываемом одноименной командой из меню "Сервис". Исходные данные и результаты расчетов разместить на листе в виде таблицы (см. Таблицу 15).

Таблица 15

Начальное значение	Формулы	Целевая ячейка
10
10
10

3. Пользуясь надстройкой "Поиск решения" определить размеры бака, имеющего форму прямоугольного параллелепипеда, объемом 2000 см³, чтобы на его изготовление пошло как можно меньше материала.

4. Пользуясь надстройкой "Поиск решения" определить размеры бака, имеющего форму прямоугольного параллелепипеда, стоимость сварки которого не должна превышать 500 у.е., чтобы его объем был максимальным. Стороны бака должны быть целыми числами.

5. Пользуясь надстройкой "Поиск решения" определить, в каком количестве надо выпускать продукцию 4-х типов П1, П2, П3, П4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы. Нормы расхода каждого ресурса, а также прибыль, полученная при реализации единицы каждого типа продукции, наличие располагаемого ресурса приведены в таблице 16.

Таблица 16

Ресурс	П1	П2	П3	П4	Наличие
Прибыль	60	70	120	130	—
Трудовые	1	1	1	1	16
Сырье	6	5	4	3	110
Финансы	4	6	10	13	100

Контрольные вопросы

1. Область применения средства "Подбор параметра". Правила его использования.

2. Надстройка "Поиск решения". Понятие "целевая функция". Задание ограничений. Настройка параметров.

Лабораторная работа №10

П О С Т Р О Е Н И Е Г Р А Ф И К О В И Д И А Г Р А М М

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

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

1. По имеющимся данным: "за" — 7 чел., "против" — 11 чел., "воздержалось" — 2 чел., построить круговую объемную диаграмму, отражающую процентное соотношение результатов голосования. Цвета секторов — красный, синий и зеленый соответственно. Вызов мастера диаграмм — командой "Диаграмма" из меню "Вставка".

2. По указанию преподавателя построить на отдельном листе график функции y=sin(x) или y=cos(x) на интервале x[-180^о, 180^о] с шагом изменения x — 10^о. Подписи осей и название графика обязательны. При разработке формулы учесть, что для встроенных тригонометрических функций MS Excel требуются аргументы, заданные в радианах.

3. По указанию преподавателя построить на том же листе график функции

или

Область определения функции x[-5, 5] с шагом изменения аргумента, равным 1.

4. По данным таблицы 17 построить различные типы диаграмм: отображающих:

• долю каждого энергоносителя в потреблении 1990 года;

• динамику изменения потребления нефти за период с 1965 по 1990 годы;

• потребление различных видов энергоносителей за весь указанный период.

Таблица 17