1

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

Государственное образовательное учреждение высшего профессионального образования

Северо-Западный государственный заочный технический университет

Кафедра информатики

И Н Ф О Р М А Т И К А

Методические указания к выполнению лабораторных работ с табличным процессором Excel

Факультеты все

Направление подготовки дипломированных специалистов и отнесенные к ним специальности 650000 – техника и технологии

Специальности все, кроме 210100, 220100.

Направления подготовки бакалавров – 550000 – технические науки,

кроме 550200, 552800.

Санкт-Петербург

2005

2

Утверждено редакционно-издательским советом университета

УДК 881.3

Информатика. Методические указания к выполнению лабораторных работ с табличным процессором Excel. -СПб.:Изд-во СЗТУ. 2005. – 72с.

Методические указания разработаны на основе рабочей программы “Информатика” в соответствии с требованиями государственных образовательных стандартов высшего профессионального образования для подготовки дипломированных специалистов и отнесенные к ним специальности 650000 – «Техника и технологии» и направлению подготовки бакалавра 550000

–«Технические науки».

Вметодических указаниях рассматриваются десять лабораторных работ с табличным процессором Excel. В процессе выполнения данных работ студенты научатся: производить расчеты, строить диаграммы, создавать отчеты, фильтровать данные, решать экономические задачи. Работы позволяют ознакомиться со всеми основными возможностями Excel.

Методические указания предназначены для студентов первого курса всех специальностей, кроме 210100 и 220100.

Рассмотрено на заседании кафедры информатики и вычислительной математики 18.05.05 г., одобрено методической комиссией факультета информатики и системотехники 23.05.05 г.

РЕЦЕНЗЕНТЫ: кафедра информатики и вычислительной математики СЗТУ (зав. кафедрой Г.Г.Ткаченко, канд.физ.-мат. наук, доц.); М.И. Барабанова, канд. эконом. наук, кафедры информатики СПбГУЭФ, доц.

СОСТАВИТЕЛИ: С.В. Афанасьева, ст. преп.; Л.В. Боброва, канд. техн. наук, доц.;

Е.В. Курунова, ст. преп.; Е.А. Рыбакова, ст. преп.;

Н.А. Смирнова, канд техн. наук, доц.

© Северо-Западный государственный заочный технический университет, 2005

3

Общие указания

Целью методических указаний к выполнению лабораторных работ является развитие у студентов навыков использования перспективных информационных технологий путем освоения программного продукта Excel, входящего в состав Microsoft office и работающего в среде Windows. Осваивается методика решения инженерно-технических и экономических задач.

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

Библиографический список

1.Информатика: Практикум/ Под ред. проф. Д.В.Макаровой.- М.:Финансы и статистика, 1997.

2.Комягин В.Б., Коцюбинский А.О. Excel 7.0. в примерах. –М.: Нолидж, 1996.

3.Орвис В. Excel для ученых, инженеров и студентов. –Киев: Юниор, 1999.

4.Боброва Л.В., Быков М.Ф., Рыбакова Е.А., Смирнова Н.А. Информатика: Письменные лекции. –СПб.: Изд-во СЗТУ, 2005.

5.Лавренев С.М. Excel: Сборник примеров и задач. –М.: Финансы и статистика, 2001.

Введение

Электронные таблицы предназначены для хранения и обработки информации, представленной в табличной форме. Электронные таблицы - это двумерные массивы (которые обычно называют рабочими листами), состоящие из столбцов и строк. Программные средства для проектирования электронных таблиц называют табличными процессорами. Они позволяют не только создавать таблицы, но и автоматизировать обработку табличных данных. Кроме того, с помощью электронных таблиц можно выполнять различные экономические, бухгалтерские и инженерные расчеты, а также строить различного вида диаграммы, проводить сложный экономический анализ, моделировать и оптимизировать решение различных хозяйственных ситуаций и многое другое.

Функции табличных процессоров весьма разнообразны:

•создание и редактирование электронных таблиц;

•оформление и печать электронных таблиц;

•построение диаграмм, их модификация и решение экономических задач графическими методами;

•работа с электронными таблицами как с базами данных; сортировка таблиц, выборка данных по запросам;

•создание итоговых и сводных таблиц;

•использование при построении таблиц информации из внешних баз данных;

•решение экономических задач типа “что - если” путем подбора параметров;

•решение оптимизационных задач;

4

• статистическая обработка данных и т.д.

Табличные процессоры различаются в основном набором выполняемых функций и удобством интерфейса, поэтому целесообразно проанализировать лишь широко используемые программные продукты.

По оценкам фирмы PC Data of Reston, наиболее популярными электронными таблицами для персональных компьютеров являются табличные процессоры

Excel (фирма Microsoft), Lotus 1-2-3 (фирма Lotus Development) и Quattro Pro.

Результаты тестирования, проведенные лабораторией журнала “Home PC”, продемонстрировали явное преимущество Excel по многим параметрам, поэтому знакомство с особенностями и возможностями электронных таблиц будем осуществлять на базе пакета прикладных программ (табличного процессора) Excel. Окно Excel показано на рис. 1.

Рис.1 Электронные таблицы (ЭТ) состоят из столбцов и строк. Столбцы

идентифицированы буквами латинского алфавита (A, B, C, ...), расположенными в заглавной части таблицы. Строки идентифицированы цифрами (1, 2, 3...), расположенными в первой колонке. Количество строк и столбцов в различных ЭТ различно, например в табличном процессоре Excel 256 столбцов, более 65 тысяч строк. Место пересечения столбца и строки называется ячейкой. Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и имени строки, например А28, Р45 и т.п. Электронные таблицы могут содержать несколько рабочих листов, которые объединяются в один файл и носят название рабочей книги. В книгу можно поместить несколько различных типов документов, например рабочий лист с ЭТ, лист диаграмм, лист макросов и т.д.

ВЭТ можно работать как с отдельными ячейками, так и с группами ячеек, которые образуют блок. Имена ячеек в блоках разделяются двоеточием (:), например блок А1:В4 включает в себя ячейки А1, A2, A3, A4, B1, B2, B3 и В4.

Сблоками ячеек в основном выполняются операции копирования, удаления, перемещения, вставки и т.д.

ВЭТ используются, как правило, следующие типы данных:

5

•текст - это любая последовательность символов. Данные текстового типа используются для заголовков таблиц, заголовков строк и столбцов, а также для комментариев (например, ВЕДОМОСТЬ ЗАРПЛАТЫ);

•число - это числовая константа (например, 100; -12; 46,5);

•формула - это выражение, состоящее из числовых величин и арифметических операций. Кроме числовых величин, в формулу могут входить в качестве аргументов адреса ячеек, функции и другие формулы. В Excel формулы обязательно начинаются со знака равенства. Пример формул: =А5, =Н8*12. В ячейке, в которой находится формула, обычно виден только результат вычислений. Саму формулу можно увидеть в строке ввода, когда данная ячейка становится активной (или, если с помощью специальной команды вызвать режим показа формул);

•функции - это запрограммированные формулы, позволяющие проводить часто встречающиеся последовательности вычислений. Например, функция автосуммирования может быть представлена следующим образом:

=СУММ(А1:А4);

•дата, которая может быть представлена в различных форматах (например, 25.10.05 или 25 окт. 05).

Запуск программы Excel осуществляется исполнением команд: ПускПрограммы - Microsoft Excel. На экране появится окно (см.рис.1), которое состоит из стандартных элементов:

-номера строк перечисляются сверху вниз;

-строка заголовка;

-строка меню;

-панель инструментов (пиктографическое меню);

-панель форматирования;

-строка ввода;

-рабочий лист (окно документа);

-полосы прокрутки (вертикальной и горизонтальной);

-строка состояния.

ЛегкозаметитьбольшоесходствоокнаExcel сокномтекстовогоредактораWord. В Microsoft Excel можно работать с четырьмя основными типами документов: электронной таблицей (рабочим листом), рабочей книгой,

диаграммой, макротаблицей.

Рабочий лист служит для организации и анализа данных. Одновременно на нескольких листах данные можно: вводить, править, производить с ними вычисления. В книгу можно вставить листы диаграмм для графического представления данных и модули для создания и хранения макросов, используемых при выполнении специальных задач. Каждый лист содержит 256 столбцов и более 65 000 строк.

Рабочая книга представляет собой электронный эквивалент папкискоросшивателя. Книга состоит из листов, имена которых выводятся на ярлыках в нижней части экрана. По умолчанию книга открывается 16 рабочими листами. Лист1, Лист2, ..., Лист16, однако их число можно увеличить или уменьшить. В книгу можно поместить несколько различных

6

типов документов, например рабочий лист с электронной таблицей, лист диаграмм, лист макросов и т.п.

Диаграмма представляет собой графическое изображение связей между числами ЭТ. Она позволяет показать количественное соотношение между сопоставляемыми величинами.

Макротаблица (макрос) - это последовательность команд, которую приходится постоянно выполнять пользователю в повседневной работе. Макросы позволяют автоматизировать часто встречающиеся операции.

Работа 1 Создание и редактирование таблицы

1. Цель работы

Изучение основных команд меню Файл, Правка, Формат.

2.Основные теоретические положения

2.1.Ввод информации в ячейки электронной таблицы

Чтобы ввести в ячейку (например, в ячейку В3) текст или число:

• активизируйте ячейку щелчком мыши по ней (активизированная ячейка будет выделена рамкой);

•введите нужную информацию;

•нажмите <Enter>.

Чтобы ввести в ячейку (например, в ячейку С6) формулу:

•активизируйте ячейку щелчком мыши по ней;

•введите формулу, которая обязательно начинается знаком равенства;

•нажмите <Enter> (ПК вычислит по формуле полученное значение, запишет в

ячейку С6).

Если в ячейках одного столбца или строки должны быть записаны одни и те же константы или аналогичные формулы, применяется автозаполнение. Например, в ячейках В3:В10 должно быть записано одно и то же число или аналогичные формулы:

Чтобы провести автозаполнение следует:

•ввести в ячейку В3 нужное число или формулу;

•поместить курсор мыши на черную точку правого нижнего угла ячейки В3 (курсор примет вид тонкого черного крестика);

•при нажатой левой кнопке мыши переместить курсор в клетку В10.

Когда кнопка мыши будет отпущена, информация автоматически запишется в указанные ячейки.

Аналогично могут быть записаны списки, например список месяцев. Для этого достаточно ввести в ячейку название одного месяца, а оставшиеся названия месяцев ввести с помощью автозаполнения.

7

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

Задание. Создать электронную таблицу ВЕДОМОСТЬ ЗАРПЛАТЫ (табл.1). Зарплата сотрудникам фирмы начисляется, как произведение минимального размера оплаты труда (МРОТ) на индивидуальный коэффициент. После начисления зарплаты из нее следует вычесть подоходный налог в размере 13%. Ведомость (см. табл.1), кроме начисления зарплаты, содержит начисление премий работникам. Премия начисляется как произведение минимального

размера премии на премиальный коэффициент сотрудника фирмы.

Общая сумма к выдаче, начисляемая каждому сотруднику фирмы, вычисляется как сумма зарплаты (с вычетом налога) и премии.

При создании таблицы общее задание включает в себя пять основных пунктов (пять промежуточных заданий).

Задание 1.		Войти в среду Exсel.
Задание 2.		Ввести числовые и текстовые константы в таблицу.
Задание 3.		Ввести формулы.
Задание	4.	Сохранить таблицу.
Задание	5.	Отпечатать таблицу.

3.1.Выполнение задания 1 (вход в среду Exсel):

•щелкните по кнопке Пуск на панели задач Windows,

•используя путь Пуск – Программы - Microsoft Exсel, запустите Exсel.

										Таблица 1
	А	B	C	D		E	F		G	H	I
1				ВЕДОМОСТЬ			ЗАРПЛАТЫ
2			МРОТ	100					Минималь	200
			(рублей)					ная премия
	№		Коэффи-						Коэффи-		Сумма к
		Фамилия И.О.	циент	Оклад	Налог		Зарплата		циент	Премия
3	пп		оклада						премии		выдаче
4	1	Иванов И.И.	15	1500		195	1305	1		200	1505
5	2	Петров П.П.	22	2200		286	1914	1,5		300	2214
6	3	Сидоров С.С.	10	1000		130	870	2		400	1270
7	4	Антонов А.А.	12	1200		156	1044	1,7		340	1384
8	5	Федоров Ф.Ф.	20	2000		260	1740	1,5		300	2040
9	6	Семенов С.С.	17	1700		221	1479	3		600	2079
									Сумма
		СУММА НАЧИСЛЕННОЙ					8352		начис-	2140
		ЗАРПЛАТЫ							ленной
10									премии
11
12				ИТОГО	К ВЫДАЧЕ						10492

8

3.2. Выполнение задания 2 (ввод числовых и текстовых констант) 3.2.1. Ввод заголовка таблицы в ячейку А1

Она активная по умолчанию, следовательно, можно начать ввод символов с клавиатуры. Для этого:

•задайте размер букв по команде Формат Ячейки, например, 14 петитпунктов;

•наберите нужный текст, для исправления ошибок используйте клавиши управления курсором, вставки <Insert> и удаления <Delete>, <Backspase>;

•щелкните ко кнопке Ж - полужирный шрифт;

•нажмите <Enter>. Заголовок введен в А1. 3.2.2. Размещение заголовка в центре таблицы

•выделите всю первую строку: столбцы A1:I1 (зажав левую кнопку мыши);

•выполните команду Формат Ячейки;

•в открывшемся окне выберите вкладку Выравнивание;

• в открывшемся окне выберите По горизонтали;

•в окне По горизонтали выберите По центру выделения;

•щелкните по ОК.

3.2.3. Ввод второй строки текста

Во второй строке следует ввести информацию о МРОТ и минимальном размере премии.

Для этого:

•щелкните по ячейке С2, сделав ее активной;

•введите слова “МРОТ (рублей)” (чтобы расположить эту информацию в две строки, выполните команду Формат, Ячейки, Выравнивание, Переносить по словам);

•активизируйте ячейку D2 и введите в нее число 100;

•активизируйте ячейку G2 и введите в нее слова “Минимальная премия”, организовав перенос по словам;

•активизируйте ячейку Н2 и введите число 200.

3.2.4. Заполнение строк

Введите в ячейки А3:I3 информацию из табл.1. Расширить столбцы при необходимости или переносить текст по словам аналогично п.3.2.3.

3.2.5. Заполнение ячеек числовыми и текстовыми константами Заполните столбцы А, В, С, G данными согласно табл.1.

3.3. Выполнение задания 3 (ввод формул)

3.3.1. Ввод формул для определения величины оклада работников фирмы Оклад работников определяется как произведение МРОТ и индивидуального коэффициента. Величина МРОТ помещается в ячейке D2. Следовательно, формула для вычисления оклада Иванова И.И. должна быть =D2*C4 (в ячейке С4 находится значение индивидуального коэффициента для Иванова И.И.). Аналогичным образом, оклад для Петрова П.П. должен вычисляться по формуле =D2*C5, для Сидорова С.С. - по формуле =D2*C6. Так как формулы повторяются, есть смысл ввести ее только для Иванова, а затем использовать режим копирования формул. При этом адрес ячейки С4 должен изменяться на

9

Таблица 2

10

С5, С6 и т.д., а адрес ячейки D2 должен остаться неизменным, так как значение МРОТ находится только там. Поэтому используем абсолютный адрес ячейки D2: D$2 (знак $ запрещает изменять номер строки ячейки D2 при копировании).

Выполнить ввод формул:

•в ячейку D4 ввести формулу = D$2*С4;

•для перехода в режим копирования формулы поставить указатель мыши в нижний правый угол ячейки D4 (указатель мыши должен принять форму тонкого черного крестика);

•зажав левую клавишу мыши, протащить указатель по ячейкам D5:D9 и отпустить клавишу. В ячейках D5:D9 появится значение оклада, вычисленного по формулам;

•для проверки формул перейдем в режим показа формул в таблице (выполним команды Сервис, Параметры, в окне Параметры поставим флажок Формулы). Сравним формулы столбца D с формулами в табл.2 (Ведомость зарплаты в режиме показа формул);

•для дальнейшей работы с таблицей вернемся к режиму вычислений, выполнив команды Сервис, Параметры, в окне Параметры, снимем флажок Формулы.

3.3.2. Ввод формул для вычисления подоходного налога

Подоходный налог начисляется в размере 13% от оклада. Значит, для Иванова он будет вычисляться по формуле =0,13*D4, а для остальных формулу можно ввести копированием.

Ввод формул:

•в ячейку Е4 ввести формулу = 0,13*D4;

•скопировать формулу в ячейки Е5:Е9.

3.3.3. Ввод формул для вычисления зарплаты Зарплата определяется как разность оклада и подоходного налога, поэтому:

•ввести в ячейку F4 формулу =D4-E4;

•скопировать ее в ячейки F5:F9.

3.3.4. Ввод формул для вычислений премий

Размер премии рассчитывается как произведение минимального размера премии (хранящегося в ячейке Н2) на индивидуальный коэффициент премии

(ячейки G4:G9).

Для ввода формул:

•ввести в ячейку Н4 формулу =H$2*G4;

•скопировать ее в ячейки Н5:Н9.

3.3.5. Ввод формулы для вычисления суммы к выдаче

Сумма к выдаче для каждого работника определяется как сумма зарплаты и премии, поэтому:

•ввести в ячейку I4 формулу =F4+H4;

•скопировать ее в ячейки I5:I9.

11

3.3.6. Ввод формул для расчета суммы начисляемой зарплаты:

•ввести в ячейку В10 комментарий “Сумма начислений зарплаты” (используя объединение ячеек В10:Е10, аналогично 3.2.2 поместить заголовок в центре В10:Е10);

•активизировать ячейку F10;

•щелкнуть по пиктограмме автосуммирования Σ (появляются “бегущие муравьи” - Excel предлагает указать область суммирования);

•поставить указатель мыши на ячейку F4 и зажать левую клавишу мыши, затем протянуть указатель до ячейки F9 (т.е. указать область суммирования

F4:F9);

•нажать клавишу Enter.

3.3.7. Вычисление суммы начисленной премии

•в ячейку G10 ввести комментарий “Сумма начисленной премии” (используя перенос по словам и регулируя ширину строки);

•активизировать ячейку Н10;

•щелкнуть по пиктограмме автосуммирования Σ;

•указать область суммирования (поставить указатель мыши в ячейку Н4 и, зажав левую клавишу, протянуть до ячейки Н9);

•нажать клавишу Enter.

3.3.8. Вычисление итоговой суммы к выдаче

•в ячейку В12 ввести комментарий “ИТОГО К ВЫДАЧЕ”;

•поставить указатель мыши в ячейку В11, зажать левую клавишу и протянуть по строке 12 до ячейки Н12 для выделения диапазона В12:Н12;

•для выравнивания комментария по строке щелкнуть по пиктограмме

•чтобы выделить заголовок полужирным шрифтом, щелкнуть по пиктограмме Ж;

•поместить курсор в ячейку I12;

•щелкнуть по пиктограмме автосуммирования Σ;

•указать область суммирования (поставить указатель мыши на ячейку I4 и, зажав левую клавишу, протащить до ячейки I9). Нажать клавишу Enter.

3.4.Выполнение задания 4 (сохранение таблицы)

Для сохранения таблицы выполнить команды:

•Файл - Сохранить как…;

•В открывшемся диалоговом окне Сохранение документа выбрать папку Мои документы и ввести имя таблицы “Ведомость зарплаты”.

•Щелкнуть по кнопке Сохранить.

3.5.Выполнение задания 5 (печать таблицы)

Для печати таблицы в Excel следует:

•щелкнуть по пункту меню Файл.

•в падающем меню выбрать пункт Параметры страницы. Открывается одноименное диалоговое окно (рис.2).

12

•на вкладке Лист поставить флажки a Сетка и a Заголовки строк и столбцов (иначе таблица будет печататься без разделительных линий и имен строк и столбцов), щелкнуть по кнопке ОК.

Рис.2

3.6. Завершение работы с файлом Выполнить команды Файл – Закрыть; Файл - Выход.

4. Отчет по работе

Выполнение пункта 3.5.

Литература: [2], с.42-67.

Работа 2 Создание и редактирование диаграмм и графиков

1. Цель работы Изучение работы с Мастером диаграмм.

2. Основные теоретические положения

Графическое представление информации помогает осмыслить закономерности, лежащие в основе больших объемов данных. Excel предлагает богатые возможности для визуализации данных. Для этого существует Мастер

диаграмм (пиктограмма )

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

Задание 1. Построить гистограмму «Оклады работников фирмы» для ведомости зарплаты из работы 1.

13

Задание 2. Построить гистограмму «Оклады и премии работников фирмы» для ведомости зарплаты из работы 1.

Задание 3. Построение графиков линейных функций.

I. Построить график функции 3y+4x=7 на интервале [-1;1] с шагом x=0,2.

II.Построить график линейной функции согласно индивидуальному заданию. Задание 4. Построение графиков нелинейной функции.

I. Построить график функции 2y2+3x2=6 на интервале [0;1,4] с шагом x=0,1.

II.Построить график квадратичной функции согласно индивидуальному заданию.

Задание 5. Построение графика поверхности.

I.Построить поверхность, описанную формулой z=x2-y2 в диапазоне

x [-2;2], y [-2;2] с шагом х=1, у=0,2.

II. Построить график функции z= x2 + y2 +1 , где x [-5;5], y [-5;5], х= у=1.

3.1. Выполнение задания 1. Построение гистограммы «Оклады работников фирмы»

3.1.1. Открыть файл «Ведомость зарплаты» (если он был закрыт). Для этого выполнить команды:

•Пуск – Программы - Microsoft Excel;

•Файл - Открыть;

•В диалоговом окне Открытие документа в окне Папка находим папку

Мои документы;

•В открывшемся окне папки Мои документы находим имя своего файла «Ведомость зарплаты», выделим его и щелкнем по кнопке Открыть.

3.1.2. Выделение нужного диапазона данных

•зажать левую клавишу мыши и выделить ячейки В3:В9 (ФИО работников);

•отпустить левую клавишу мыши;

•нажать клавишу Ctrl (для одновременного выделения столбцов В и D);

•указатель мыши переместить в строку D и выделить ячейки D3:D9 (оклады). 3.1.3. Обращение к Мастеру диаграмм

Щелкнуть по пиктограмме

(появится окно Мастер диаграмм.)

3.1.4. Выбор типа диаграмм В первом окне Мастера диаграмм следует выбрать тип графика. Выберем

Гистограмма, Вид 4 (объемный вариант) и щелкнем по кнопке Далее.

3.1.5. На втором шаге (исходные данные) Мастера диаграмм следует выбрать ориентацию координатных осей: в строке или в столбцах. Выберем опцию в столбцах, щелкнем по кнопке Далее.

3.1.6. На третьем шаге (параметры диаграммы) Мастера диаграмм нужно ввести заголовки диаграммы и координатных осей:

а) щелкнем по окну Название диаграммы и введем “Ведомость зарплаты”; б) щелкнем по окну Ось Х и введем “Фамилии”;

в) щелкнем по окну Ось Z и введем “Оклады”. Щелкнем по кнопке Далее.

14
3.1.7. На четвертом шаге Мастера диаграмм (размещение диаграммы)
щелкнем по опции Отдельном листе и по кнопке Готово. На новом листе
Диаграмма 1 будет представлен график рис.3.
ВЕДОМОСТЬ ЗАРПЛАТЫ
2500
2000
1500
ОКЛАДЫ
1000			Оклад
500
0
Иванов И.И.Петров П.П. Сидоров	Антонов	Федоров	Семенов
С.С.	А.А.	Ф.Ф.	С.С.
ФАМИЛИИ
	Рис. 3
Обратите внимание! Для построения диаграммы в рабочей книге Excel
введен еще один лист Диаграмма (название - внизу рабочего окна), в то время
как основная таблица помещается на Лист 1. Чтобы вновь обратиться к
таблице, следует поставить указатель мыши на имя листа (Лист 1 внизу
окна Excel) и щелкнуть левой клавишей.

3.1.8. Изменение вида графика Для изменения вида графика следует:

•Поставить указатель мыши на белое поле сбоку диаграммы (появится сообщение Область диаграммы);

•Щелкнуть правой клавишей мыши (появиться контекстное меню);

•В контекстном меню выбрать команду Тип графика (произойдет возврат к первому окну Мастера диаграмм);

•В окне Мастера диаграмм выбрать Коническую диаграмму, щелкнув по ней мышью. Минуя остальные шаги Мастера диаграмм, будет построена новая диаграмма.

3.1.9. Печать гистограммы Выполнить команду:

•Файл – Печать – ОК.

3.2.Выполнение задания 2. Построение гистограммы «Оклады и премии работников фирмы»

3.2.1. Выделение массива информации

Для того чтобы выделить три столбца таблицы, при нажатой клавише Ctrl выполнить:

15

•при зажатой левой клавишей мыши выделить столбец В (Фамилии);

•отпустить левую клавишу мыши и переместить ее указатель в столбец D (Оклады);

•зажав левую клавишу мыши, выделить столбец D;

•отпустить левую клавишу мыши и переместить ее указатель в столбец Н (Премии);

•зажав левую клавишу мыши, выделить столбец Н;

•отпустить клавишу Ctrl и левую клавишу мыши. 3.2.2. Работа с Мастером диаграмм

• щелкнуть по пиктограмме

Мастер диаграмм;

•на первом шаге выбрать Тип графика – Гистограмма, Вид – Трехмерная гистограмма и щелкнуть по кнопке Далее;

•на втором шаге Мастера диаграмм выбрать ориентацию в строках и щелкнуть по кнопке Далее;

•на третьем шаге (параметры диаграмм) ввести только заголовок диаграммы – “Оклады и премии” и щелкнуть по кнопке Далее;

•на четвертом шаге (размещение диаграмм) выбрать опцию на отдельном листе и щелкнуть по кнопке Готово.

На новом листе Диаграмма 2 будет представлен график рис.4.

Рис. 4

3.3.Выполнение задания 3. Построение графика линейной функции.

I. Построение графика линейной функции 3y+4x=7.

3.1.1. Решить уравнение относительно y: y = 73 − 43 x .

16

3.3.2. Провести табуляцию значений х (задать в ЭТ, табл.3 значения х). Для этого:

			Таблица3	а) ввести в ячейку А3 значение –1, в ячейку
		А	В	А4 значение –0,8;
	1	Построение	графика 3y+4x=7	б) выделить ячейки А3:А4;
	2	Значения х	Значения y	в) поставить указатель мыши в правый
	3	-1	=7/3-4/3*А3	нижний угол ячейки А4 и, зажав левую
				клавишу,	заполнить	ячейки	А5:А13
	4	-0,8	=7/3-4/3*А4
				значениями х.
	5	-0,6	=7/3-4/3*А5
	6	-0,4	=7/3-4/3*А6	3.3.3. Провести табуляцию значений y:
	7	-0,2	=7/3-4/3*А7	а) ввести в ячейку В3 формулу =7/3-4/3*А3;
	8	0	=7/3-4/3*А8	б) скопировать формулу в ячейки В4:В13.
	9	,2	=7/3-4/3*А9	3.3.4. Построение графика функции:
	10	0,4	=7/3-4/3*А10
				а) Выполнить команды Вставка –
	11	0,6	=7/3-4/3*А11
				Диаграмма – График – График с
	12	0,8	=7/3-4/3*А12
				маркерами – Далее.
	13	1	=7/3-4/3*А13

б) На втором шаге Мастера диаграмм

- Ввести Диапазон данных В3:В11 ;

-щелкнуть по кнопке Ряд;

-ввести

Подписи оси Х А3:А11 , Далее.

г) На третьем шаге Мастера диаграмм ввести подписи по осям:
Ось X		Значения Х
Ось Y		Значения Y , Далее;
д) На четвертом шаге Мастера диаграмм выбрать опцию
На отдельном листе, Готово.
Полученный график приведен на рис. 5
Y	4,000
	3,000
Значения
	2,000					Ряд1
	1,000
	0,000
	1	-0,6	-0,2	0,2	0,6	1
	-
			Значения Х

Рис. 5

17

II. Построение графика линейной функции по индивидуальному заданию. Таблица 4

	Номер	Уравнение	Интервал	Шаг x	а) выбрать вариант задания из
	варианта
					табл. 4 по указанию преподава-
	1	2y-3x=5	[0;3]	0,2
					теля;
	2	4y+5x=6	[-2;0]	0,1
					б) провести построение графика
	3	3y-2x=4	[-1;2]	0,2
					аналогично пп 3.3.1-3.3.4 пункта I.
	4	5y+2x=3	[-2;2]	0,2
	5	6y-5x=4	[0;2]	0,1
	6	3y+5x=8	[-2;1]	0,2
	7	2y-5x=9	[-1;2]	0,2
	8	9y+8x=6	[2;4]	0,1
	9	4y-3x=8	[3;5]	0,1
	10	3y+8x=2	[2;6]	0,2

3.4. Выполнение задания 4. Построение графиков квадратичной функции.

I. Построение графика функции 2y2+3x2=6 на интервале [0;1,4] с шагом											x=0,1.
3.4.1. Решить уравнение относительно y:					y = ±		6 −3x 2	= ±	3 −	3	x	2	.
							2			2
Это уравнение равносильно двум следующим:
	3 −	3	x		2
y =		2
		3
	3 −			x		2
y = −		2

Нужно построить на заданном интервале график обеих функций.

3.4.2. Сведем вычисления в ЭТ, табл. 5.

а) Заполнить ячейки А3:А17 значениями х (аналогично п. 3.3.2 задания 3); б) ввести в ячейку В3 формулу =(3-3/2*А3^2)^(1/2) и скопировать ее в

ячейки В3:В17; в) ввести в ячейку С3 формулу =-В3 и скопировать ее в ячейки С4:С17.

3.4.3. Построение графика а) Выполнить команды Вставка – Диаграмма – График – График с

маркерами – Далее; б) На втором шаге Мастера диаграмм

- ввести

Диапазон данных В3:С17 ,

-щелкните по кнопке Ряд;

-ввести

Подписи оси Х А3:А17 , - Далее.

в) Закончить построение графика на отдельном листе. Получим график рис. 6.

18

				Таблица 5
	А		В	С
1		График функции 2y^2+3x^2=6
2	Значения х		y=(3-3/2*x^2)^(1/2)	y=-(3-3/2*x^2)^(1/2)
3	0		=(3-3/2*A3)^(1/2)	=-B3
4	0,1		=(3-3/2*A4)^(1/2)	=-B4
5	0,2		=(3-3/2*A5)^(1/2)	=-B5
6	0,3		=(3-3/2*A6)^(1/2)	=-B6
7	0,4		=(3-3/2*A7)^(1/2)	=-B7
8	0,5		=(3-3/2*A8)^(1/2)	=-B8
9	0,6		=(3-3/2*A9)^(1/2)	=-B9
10	0,7		=(3-3/2*A10)^(1/2)	=-B10
11	0,8		=(3-3/2*A11)^(1/2)	=-B11
12	0,9		=(3-3/2*A12)^(1/2)	=-B12
13	1		=(3-3/2*A13)^(1/2)	=-B13
14	1,1		=(3-3/2*A14)^(1/2)	=-B14
15	1,2		=(3-3/2*A15)^(1/2)	=-B15
16	1,3		=(3-3/2*A16)^(1/2)	=-B16
17	1,4		=(3-3/2*A17)^(1/2)	=-B17

	2
	1
	0															Ряд1
																Ряд2
	0	0,1	0,2	0,3	0,4	0,5	0,6	0,7	0,8	0,9	1	1,1	1,2	1,3	1,4
	-1
	-2

Рис 6

II. Построение графика квадратичной функции по индивидуальному заданию:

а) выбрать вариант задания из табл. 6 по указанию преподавателя; б) провести построение графика аналогично пп 3.4.1-3.4.3.

Таблица 6

№	Уравнение	Интервал	Шаг		№	Уравнение	Интервал	Шаг
варианта			х		варианта			х
1	3х2-2y2=5	[-2;0]	0,1		6	3x2+2y2=4	[1;5]	0,2
2	2x2+3y2=4	[-1;1]	0,2		7	2x2-3y2=5	[-2;2]	0,2
3	2y2-4x2=7	[0;2]	0,1		8
4	3y2+5x2=5	[1;3]	0,1		9
5	4y2+3x2=5	[0;4]	0,2		10

19

3.5. Построение графиков поверхности

I. Построение графика поверхности z=x2-y2 в диапазоне х [-2;2], y [-1;1] при шаге изменения аргументов х=0,2; у=0,2.

3.5.1. Провести табуляцию аргумента х:

•ввести в А2 число –2;

•ввести в А3 число –1,8;

•выделить ячейки А2:А3;

•поставить указатель мыши в правый нижний угол ячейки А3 (указатель примет вид тонкого крестика) и провести заполнение ячеек до А22.

3.5.2. Провести табуляцию аргумента у:

•ввести в В1 число –1;

•ввести в С1 число –0,8;

•выделить ячейки В1:С1;

•заполнить значениями аргумента у ячейки D1:L1.

3.5.3. Ввести формулы для вычисления Z:

•ввести в ячейку В2 формулу =$A2^2-B$1^2;

•скопировать формулу в ячейки В3:L22.

(Для этого следует скопировать формулу в ячейки В3:В22, затем выделить ячейки В2:В22 и скопировать весь выделенный столбец в ячейки С2:L22). В результате получим табл. 7 (режим показа вычислений) и табл. 8 (режим показа формул).

Таблица 7

	A	B	C	D	E	F	G	H	I	J	K	L
1		-1	-0,8	-0,6	-0,4	-0,2	0	0,2	0,4	0,6	0,8	1
2	-2	3	3,36	3,64	3,84	3,96	4	3,96	3,84	3,64	3,36	3
3	-1,8	2,24	2,6	2,88	3,08	3,2	3,24	3,2	3,08	2,88	2,6	2,24
4	-1,6	1,56	1,92	2,2	2,4	2,52	2,56	2,52	2,4	2,2	1,92	1,56
5	-1,4	0,96	1,32	1,6	1,8	1,92	1,96	1,92	1,8	1,6	1,32	0,96
6	-1,2	0,44	0,8	1,08	1,28	1,4	1,44	1,4	1,28	1,08	0,8	0,44
7	-1	0	0,36	0,64	0,84	0,96	1	0,96	0,84	0,64	0,36	0
8	-0,8	-0,36	0	0,28	0,48	0,6	0,64	0,6	0,48	0,28	0	-0,36
9	-0,6	-0,64	-0,28	0	0,2	0,32	0,36	0,32	0,2	0	-0,28	-0,64
10	-0,4	-0,84	-0,48	-0,2	0	0,12	0,16	0,12	0	-0,2	-0,48	-0,84
11	-0,2	-0,96	-0,6	-0,32	-0,12	0	0,04	0	-0,12	-0,32	-0,6	-0,96
12	0	-1	-0,64	-0,36	-0,16	-0,04	0	-0,04	-0,16	-0,36	-0,64	-1
13	0,2	-0,96	-0,6	-0,32	-0,12	0	0,04	0	-0,12	-0,32	-0,6	-0,96
14	0,4	-0,84	-0,48	-0,2	0	0,12	0,16	0,12	0	-0,2	-0,48	-0,84
15	0,6	-0,64	-0,28	0	0,2	0,32	0,36	0,32	0,2	0	-0,28	-0,64
16	0,8	-0,36	0	0,28	0,48	0,6	0,64	0,6	0,48	0,28	0	-0,36
17	1	0	0,36	0,64	0,84	0,96	1	0,96	0,84	0,64	0,36	0
18	1,2	0,44	0,8	1,08	1,28	1,4	1,44	1,4	1,28	1,08	0,8	0,44
19	1,4	0,96	1,32	1,6	1,8	1,92	1,96	1,92	1,8	1,6	1,32	0,96
20	1,6	1,56	1,92	2,2	2,4	2,52	2,56	2,52	2,4	2,2	1,92	1,56
21	1,8	2,24	2,6	2,88	3,08	3,2	3,24	3,2	3,08	2,88	2,6	2,24
22	2	3	3,36	3,64	3,84	3,96	4	3,96	3,84	3,64	3,36	3

			20
				D	Таблица 8
	A	B	C		E
1		-1	-0,8	-0,6	-0,4
2	-2	=$A2^2-B$1^2 =$A2^2-C$1^2		=$A2^2-D$1^2	=$A2^2-E$1^2
3	-1,8	=$A3^2-B$1^2 =$A3^2-C$1^2		=$A3^2-D$1^2	=$A3^2-E$1^2
4	-1,6	=$A4^2-B$1^2 =$A4^2-C$1^2		=$A4^2-D$1^2	=$A4^2-E$1^2
5	-1,4	=$A5^2-B$1^2 =$A5^2-C$1^2		=$A5^2-D$1^2	=$A5^2-E$1^2
6	-1,2	=$A6^2-B$1^2 =$A6^2-C$1^2		=$A6^2-D$1^2	=$A6^2-E$1^2
7	-1	=$A7^2-B$1^2 =$A7^2-C$1^2		=$A7^2-D$1^2	=$A7^2-E$1^2
8	-0,8	=$A8^2-B$1^2 =$A8^2-C$1^2		=$A8^2-D$1^2	=$A8^2-E$1^2
9	-0,6	=$A9^2-B$1^2	=$A9^2-C$1^2	=$A9^2-D$1^2	=$A9^2-E$1^2

10-0,4 =$A10^2-B$1^2 =$A10^2-C$1^2 =$A10^2-D$1^2 =$A10^2-E$1^2

11-0,2 =$A11^2-B$1^2 =$A11^2-C$1^2 =$A11^2-D$1^2 =$A11^2-E$1^2

120 =$A12^2-B$1^2 =$A12^2-C$1^2 =$A12^2-D$1^2 =$A12^2-E$1^2

130,2 =$A13^2-B$1^2 =$A13^2-C$1^2 =$A13^2-D$1^2 =$A13^2-E$1^2

140,4 =$A14^2-B$1^2 =$A14^2-C$1^2 =$A14^2-D$1^2 =$A14^2-E$1^2

150,6 =$A15^2-B$1^2 =$A15^2-C$1^2 =$A15^2-D$1^2 =$A15^2-E$1^2

160,8 =$A16^2-B$1^2 =$A16^2-C$1^2 =$A16^2-D$1^2 =$A16^2-E$1^2

171 =$A17^2-B$1^2 =$A17^2-C$1^2 =$A17^2-D$1^2 =$A17^2-E$1^2

181,2 =$A18^2-B$1^2 =$A18^2-C$1^2 =$A18^2-D$1^2 =$A18^2-E$1^2

191,4 =$A19^2-B$1^2 =$A19^2-C$1^2 =$A19^2-D$1^2 =$A19^2-E$1^2

201,6 =$A20^2-B$1^2 =$A20^2-C$1^2 =$A20^2-D$1^2 =$A20^2-E$1^2

211,8 =$A21^2-B$1^2 =$A21^2-C$1^2 =$A21^2-D$1^2 =$A21^2-E$1^2

222 =$A22^2-B$1^2 =$A22^2-C$1^2 =$A22^2-D$1^2 =$A22^2-E$1^2

3.5.4. Построить график поверхности:

а) Выполнить команды Вставка – Диаграмма – Поверхность – выбрать левый верхний тип поверхности (рис. 7) – Далее;

Рис. 7

	21
б) На втором шаге Мастера диаграмм:
•	ввести Диапазон данных		В2:L22		;
• перейти на вкладку Ряд;
•	ввести Подписи оси Х	А2:А22		;

•щелкнуть по кнопке Ряд, выбрать Ряд 1;

•щелкнуть по полю Имя, щелкнуть мышью по ячейке В1 (см. рис. 8);

•щелкнуть по кнопке Ряд, выбрать Ряд 2;

•щелкнуть по полю Имя, щелкнуть мышью по ячейке С1. Аналогичным образом ввести

Ряд	Адрес ячейки
Ряд 3	D1
Ряд 4	E1
………………..
Ряд 10	К1, щелкнуть Далее.

в) На третьем шаге ввести название осей: Х, Y, Z и щелкнуть Далее.

г) На четвертом шаге выбрать опцию На отдельном листе и щелкнуть

Готово.

В результате получим график поверхности рис. 9.

Рис. 8

22

4

3

Z

2

1

0 -1

-2 -1,2	-0,4	0,4	1,2	2
X

			3-4
			2-3
			1-2
		0,8	0-1
			-1-
	-1	Y

Рис. 9

3.5.5. Теперь построим график функции z = x 2 + y 2 +1 , где х [-

5;5],

y [-5;5]; x=1; y=1.

а) перейти на Лист3 открытой книги Excel;

б) В ячейках А2:А12 провести табуляцию переменной х (аналогично п. 3.5.1); в) В ячейках В1:L1 провести табуляцию переменной y (аналогично п. 3.5.2); г) Для вычисления функции Z ввести формулы в ячейки В2:L22:

•в ячейку В2 ввести формулу =КОРЕНЬ($A2^2+B$2^2+1);

Можно для извлечения квадратного корня вместо функции КОРЕНЬ

использовать возведение в дробную степень (1/2). Тогда формула будет выглядеть так: =($A2^2+B$2^2+1)^(1/2);

• Скопируем формулу из В2 в В3:L22 (аналогично п. 3.5.3);

									д)		построить			график
8								поверхности аналогично п.
7							7-8	3.5.4. (получим рис. 10).
6								е)		Провести				редакти-
							6-7	рование диаграммы;
5
							5-6	Выполнить						команды
Z 4
							4-5	Диаграмма –					Объемный
3							3-4	вид.	Появится диалоговое
2						5	2-3	окно			рис.	11.		Пункт
1							1-2	Объемный вид позволяет
					0
0						Y	0-1	управлять					подъемом
								обзора объемной диаграм-
-5	-3	1-	1	3	5
					-5			мы,	ее		углом		поворота,
		X
								перспективой и так далее.
				Рис.10