ФГОУ СПО «КИРОВСКИЙ МЕХАНИКО-ТЕХНОЛОГИЧЕСКИЙ ТЕХНИКУМ МОЛОЧНОЙ ПРОМЫШЛЕННОСТИ»		Лаборатория компьютеризации
ИНСТРУКЦИОННАЯ КАРТА НА ВЫПОЛНЕНИЕ	ПРАКТИЧЕСКОЙ  РАБОТЫ  № 7
ПО ДИСЦИПЛИНЕ: Учебная практика на ПЭВМ   ТЕМА: Электронные таблицы EXCEL	СПЕЦИАЛЬНОСТЬ: 0606

 

НАИМЕНОВАНИЕ: Решение задач с помощью встроенных функций.

ЦЕЛЬ:

1.        Систематизировать  и применить приемы работы с числовой ин6формацией

2.        Исследовать и применить приемы решения задач с помощью встроенных функций:

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

4.        Развивать мышление через сравнение и анализ методов обработки числовой информации

5.      Воспитывать нормы работы за ПК (правовая культура): правила работы с вычислительной техникой, с информацией, правила техники безопасности

ПРИОБРЕТАЕМЫЕ УМЕНИЯ И НАВЫКИ:

1)       Навыки работы с мышкой

2)       Навыки работы с встроенными функциями

3)      Навыки построения экономической м математической моделей

СРЕДСТВА: инструкционная карта, ПК, электронные таблицы EXCEL, опыт студентов, опыт преподавателя

НОРМА ВРЕМЕНИ: 6 часов

ТЕХНИКА БЕЗОПАСНОСТИ:  ЗАПРЕЩАЕТСЯ:  

·          трогать разъемы соединительных кабелей,

·          включать и выключать аппаратуру без указания преподавателя,

·          прикасаться к экрану  и тыльной стороне монитора,

·          класть дискеты, книги, тетради, ручки и т.п. на клавиатуру и монитор.

При длительной работе за ПК необходимо соблюдать следующие санитарные правила:

·          при продолжительности работы 1,5 – 2 часа делать перерыв  10 мин. через  каждый час;

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

1)        При появлении запаха гари немедленно прекратить работу, отключить питание ПК и сообщить об этом преподавателю.

2)        Не пытайтесь самостоятельно устранять неисправности в работе аппаратуры.

 Вы отвечаете за сохранность рабочего места.

ПЛАН РАБОТЫ:

1)       Подготовительный этап

2)       Практический этап:

ð   Исследовательский этап

ð   Исполнительский этап

3)       Аналитический этап

4)       Домашнее задание

ХОД РАБОТЫ:

Подготовительный этап: Ответьте устно на вопросы:

 

1. Электронные таблицы предназначены для обработки ______ информации?
2. Какие форматы из перечисленных используют в электронных таблицах: числовой, общий, текстовый, OLE, денежный, MEMO, финансовый, логический, процентный?
3. Опишите общие рекомендации к конструированию табличной формы (экономической модели) задачи.
4. Что включает в себя понятие «Корректировка» табличной структуры?
5. Поясните, в каких случаях мы должны внутри ячейки настраивать перенос по словам. Какие способы для этого существуют?
6. При выполнении каких действий  применяют объект «легенда»? Какую роль этот объект играет в работе пользователя?
7. Как вы понимаете действие «Форматировать»? С какими объектами в EXCEL мы выполняем это действие?
8. Какие правила ввода формул вы знаете?
9. Какие рациональные способы работы с формулами вы знаете?

 

Практический этап: Ознакомьтесь с теоретическим материалом и выполните предложенные упражнения.

 

Электронные таблицы – самая распространенная и мощная технология для профессиональной работы с данными. Вычислительные возможности объединены с богатым набором функций, присущих текстовому, графическому редакторам и другим приложениям паек MS Office.

 

1. Исследуем приемы работы  в режиме автозаполнения:

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

Основной алгоритм работы с числовой информацией:

ð  Введите в ячейку А1 число 1, В ячейку В1 число 2

ð  Выделите обе ячейки

ð  Установите курсор мыши в правую нижнюю область курсорной рамки (см. рис), курсор примет вид «знака плюс - прицела»

ð  Нажмите ЛКМ и, удерживая ее, протяните курсорную рамку вдоль строки до столбца Н

ð  Вы заполнили первую строку с 1 номера по 8 с шагом  = 1

ð  Аналогично можно заполнить столбец А, для этого введите в ячейку А2 число 4

ð  Выделите ячейки А1 и А2 и по аналогии с вышеописанным, протяните курсорную рамку до 10 строки включительно

ð  Вы заполнили первый столбец числовыми данными от 1 до 28 с шагом = 3

Основной алгоритм работы с текстовой информацией:

ð  В ячейку В2 введите слово Май

ð  Установите курсор мыши в правую нижнюю область курсорной рамки,

ð  Нажмите ЛКМ и, удерживая ее, протяните курсорную рамку вдоль строки до столбца Н

ð  Вы заполнили вторую строку названиями месяцев с мая по ноябрь

ð  Аналогично заполните столбец до 10 строки (заполните с мая по январь)

 

Программа EXCEL позволяет дублировать повторяющуюся текстовую информацию при вводе. Например, если в первую ячейку вы ввели фразу «Среднее арифметическое», а в следующую ячейку вы хотите ввести текст «Средние данные», то программа автоматически повторить первую фразу. Это не должно вас смущать, вы можете согласиться с вводом и лишь подкорректировать введенную автоматически фразу.

 

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

 

Известно, что после того, как 13 апреля 1996 года в городе N появился первый компьютерный вирус, каждый месяц их количество увеличивается вдвое. Подготовьте статистический отчет о количестве вирусов за период с 13 апреля 1996 года по 13 мая1997 года.

 

1.  Введите  в ячейку А1 заголовок Дата, в ячейку В1 - Количество вирусов

2. Введите в ячейку А2: 13 апреля 1996, в ячейку В2 - число 1.

3. Установите курсор на ячейку А2. В меню ПРАВКА/ ЗАПОЛНИТЬ / ПРОГРЕССИЯ установить следующие параметры:

Прогрессия - по столбцам;  Тип - дата;  Единица даты - месяц; Предельное значение - 13 мая 1997.

Закройте диалоговое окно – выполнив щелчок ЛКМ по кнопке <ОК>.

4. Установите курсор на ячейку В2. В меню ПРАВКА / ЗАПОЛНИТЬ / ПРОГРЕССИЯ установить следующие параметры:   Прогрессия - по столбцам; Тип - геометрическая; Шаг - 2; Предельное значение - 10000.

Закройте диалоговое окно – щелчком ЛКМ по кнопке <ОК>.

 

Упражнение 1: выполните решение задач с помощью EXCEL в одной рабочей книге, на двух первых листах под соответствующими именами: Задача1 и Задача2:

 

Задание 1: Создать календарь на первые шесть месяцев.

 

Задание 2:  У продавца сломался калькулятор. Для облегчения расчетов с покупателями составьте «шпаргалку» таблицу стоимости товара от 200 г до 1 кг включительно с интервалом в 200 г. Исходные данные предложены в таблице:

Подумайте какие из исследуемых алгоритмов вы примените при создании данной таблицы:

	А	В	с	D	E	F	H
№ п/п	Наименование	Стоимость 1 гк	0,2	0,4	0,6	0,8	1,0
1	Изюм	8250
2	Орехи	9200
3	Мандарины	6500
4	Конфеты	10000

2.    Исследуем приемы работы с относительной и абсолютной адресацией:

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

·       Другое достоинство в том, что при копировании формул входящие в них ссылки изменяются (относительная адресация).

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

ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ

При копировании или переносе формул автоматически изменяются адреса ячеек в формулах. Поясним это на примере:

В ячейку В3 (столбец В, строка 3) занесена формула =А1+А2, то есть нужно сложить числа, находящиеся в предыдущем (левом) столбце А и двух предыдущих строках, 1 и 2.

При копировании ячейки В3, например, в ячейку Е5 соотношение в формуле сохраняется: автоматически складываются числа, находящиеся в предыдущем левом столбце D и двух предыдущих строках, 3 и 4.

Аналогично, при копировании ячейки В3 в ячейку В7 формула изменяется на =А5+А6.

 

АБСОЛЮТНАЯ АДРЕСАЦИЯ:  Иногда при копировании или переносе формул требуется запретить автоматическое изменение адресов ячеек в формулах.

Фиксирование производится подстановкой знака “$”. Например:

 

Относительный адрес	Абсолютный адрес фиксирование
	строки	столбца	всей ячейки
D12	D$12	$D12	$D$12

 

Поясним это на примере:

В ячейку В3 занесена формула =А$1+$A$2

(A$1 - зафиксирована первая строка, столбец будет изменяться;  $A$2 - зафиксированы и строка и столбец, то есть при любом копировании изменяться нее будут)

При копировании ячейки В3 в ячейку В7 формула не изменилась, так как не изменился столбец.

При копировании ячейки В3 в ячейку Е5 формула преобразовалась к виду =D$1+$A$

 

Исследуем прием работы с адресацией ячеек на конкретном примере (создать на третьем листе под именем Задание3):

 

Имеется сумма денег S. Приглашено N гостей. Составьте меню не менее, чем из трех продуктов, учитывая стоимость и порционное распределение продуктов на каждого гостя.

МАТЕМАТИЧЕСКАЯ   МОДЕЛЬ: Пусть b_i - порция i-го продукта на одного гостя, с_i  - цена за килограмм i-го продукта. Тогда стоимость i-го продукта  (d_i) на всех гостей вычисляется по формуле: d_i = b_i * с_i * N

Общие расходы на день рождения =

1.      Введите исходные данные:

	A                                     B                                C                                            D                                     E
1	РАСХОДЫ НА ДЕНЬ РОЖДЕНИЯ
2	Количество гостей	7		Сумма денег	50000
3
4	Наимен. Продукта	Порция (в кг)	Цена за кг (в руб)	Стоимость (в руб)
5	Конфеты	0,2	45000 р.
6	Бананы	0,5	8000 р.
7	Мороженое	0,25	23000 р.
8
9			ИТОГО:

 

ЧТО СДЕЛАТЬ	КАК СДЕЛАТЬ
В ячейку D5 введите формулу для расчета стоимости	Используйте абсолютный адрес ячейки В2:=В5 * С5 * $B$2
Скопируйте ячейку D5 в ячейки D6 : D7
В ячейку D9 введите формулу для итоговой суммы	Нажмите кнопку ; выделите блок D5:D7; нажмите <Enter>
Измените исходные данные так, чтобы уложиться в указанную сумму	Содержимое ячейки D9 должно быть меньше, чем в ячейке Е2
Отцентрируйте заголовок таблицы	Выделите блок А1:Е1; нажмите кнопку
Измените ширину столбцов А, В, С. D, чтобы полностью поместились названия столбцов	В поле имен столбцов превратить курсор в двойную стрелку; при нажатой кнопке мыши растянуть границы столбца до нужного размера; отпустить кн. мыши

 

3. Исследуем приемы работы с встроенными функциями:

Программа  Excel имеет библиотеку встроенных функций – Мастер функций  -  (см. рис), вызов которой осуществляется двумя основными способами:

ð  Через команду горизонтального меню ВСТАВКА – ФУНКЦИЯ

ð  Через пиктограмму  f_x

 

Исследуем основной алгоритм работы  на конкретном примере:

Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000 тыс, то 5 %, если  объем до 10000 – 2%, если свыше 10000 – 1,5 %

1. Постройте таблицу по образцу:

	A	B	C	D	E	F	G	H	I
1	ФИО	Объем сделок по месяцам			Размер вознаграждения по месяцам			Средний размер сделок	Средний размер вознаграждений
2		июнь	июль	август	июнь	июль	август
3	Иванов	2900	5000	19000
4	Сидоров	3000	12000	1500
5	Петров	9000	2900	20000
6

 

2.  Установите курсор на ячейку Е3 и запустите Мастер функций

3.  в диалоговом окне выберите категорию: Логические,  в поле Функции – функцию Если, выполните подтверждение – нажатием на кнопку Ок

4.         В появившемся диалоговом окне в поле Логическое_выражение внесите запись: В3<3000

В поле значение_если_истина введите формулу В3*5%

Поле значение_если_ложь оставьте пустым и подтвердите ввод нажатием на кнопку ОК

5.       установите курсорную рамку в стоке формул  (см. рис) в конце записи В3*5% и введите знак «;»

6.         выполните щелчок ЛКМ по кнопке ЕСЛИ (см. рис), вновь откроется диалоговое окно условия,

В поле Логическое_выражение внесите запись: В3<10000

В поле значение_если_истина введите формулу В3*2%

Поле значение_если_ложь введите В3*1,5%

145

45

135

 Подтвердите ввод нажатием на кнопку ОК

7.       Протяните формулу вдоль столбца, вы получите результаты:

8.       Выполните расчеты в других ячейках столбцов F и G.

9.       вычислим среднее значение сделок, для этого:

ð  установите курсор на ячейку Н3 и запустите Мастер функций

ð  среди Статистических функций  выберите функцию СРЗНАЧ

ð  подтвердите ее выбор, нажатием на кнопку ОК

ð  на экране откроется диалоговое окно, в котором укажите диапазон данных (ячейки с B3 по D3) и подтвердите выбор.

ð  Скопируйте формулу вдоль столбца Н

10.    аналогично вычислите средний размер вознаграждений.

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

12.    сохраните документ под именем Урок 7*, где * - ваша фамилия

 

Контрольное задание: Автоматизируйте решение задачи, согласно условию:

 

Выполните расчет платы за квартиру. Она состоит из оплаты коммунальных услуг (по 20 руб за кв. м) и оплаты за газ (по 15 руб на каждого проживающего в квартире человека). При изменении тарифов оплаты должен производиться автоматический пересчет квартплаты. Если квартплата превышает 800 рублей, то квартиросъемщику положены субсидии. Отразите это в таблице словами «Есть субсидии» и «Нет субсидий» 

Тарифы оплаты				Субсидии
Коммун/услуги	20	Газ	15
Расчет квартплаты
квартира	площадь	человек	Кв.плата
№ 1	80	4
№ 2	33	3
№ 3	60	4
№ 4	57	5
№ 5	76	2

 

Аналитический этап:

1.       Ответьте (устно) на вопросы выходного контроля:

A.     Как выделить в электронной таблице смежные и несмежные ячейки, диапазоны ячеек?

B.      Как скопировать и переместить содержимое ячейки, блока ячеек, рабочего листа?

C.      Как оформить таблицу EXCEL рамками и заливкой?

D.     Как в документ EXCEL вставить фрагмент текстового документа?

E.      Как вставить таблицу или диаграмму EXCEL в документ WORD?

 

2.       Выполните самоанализ деятельности по предложенной таблице:

 

Виды выполненной работы	Алгоритм деятельности	Затруднения
		действия	причины

 

Домашнее задание:

 

1.        создать отчет по схеме:

ð   тема практической работы

ð   цели

ð   средства

ð   план работы

ð   ответы на вопросы входного и выходного контроля

ð   выводы по работе, оформленные в виде таблицы самоанализа (см таблицу выше)

2. повторить материал по электронным таблицам EXCEL: работа с диаграммой.
3. изучить материал гл. 3 Угринович «Информатика и информационные технологии» и ответить на вопросы:

ð   основной алгоритм построения диаграмм,

ð   виды и типы диаграмм

ð   форматирование диаграмм.