ФГОУ СПО «КИРОВСКИЙ МЕХАНИКО-ТЕХНОЛОГИЧЕСКИЙ ТЕХНИКУМ МОЛОЧНОЙ ПРОМЫШЛЕННОСТИ»		Лаборатория компьютеризации
ИНСТРУКЦИОННАЯ КАРТА НА ВЫПОЛНЕНИЕ	ПРАКТИЧЕСКОЙ  РАБОТЫ  № 8
ПО ДИСЦИПЛИНЕ: Учебная практика на ПЭВМ   ТЕМА: Электронные таблицы 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.        При вводе данных в ячейке получается  знак ###, ваши действия в этом случае?

3.        Какие правила следует выполнять при вводе формул?

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

5.        Какие действия необходимо выполнить, для того чтобы объединить ячейки?

6.        Какие действия необходимо выполнить для автозаполнения содержимым ячеек

 

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

 

Номер	Спортсмен	Страна	очки
1 (1)	Пит Сампас	США	3716
2 (2)	Петр Корда	Чехия	3377
3 (3)	Патрик Рафтер	Австралия	3211
4 (4)	Евгений Кафельников	Россия	3027
5 (5)	Грег Руседски	Великобритания	2812
6 (6)	Марсело Рисс	Чили	2777
7 (7)	Майкл Чанг	США	2686
8 (9)	Алекс Корретха	Испания	2551
9 (10)	Рихард Крайчек	Голландия	2308
10 (11)	Густаво Куэртен	Бразилия	2250
11 (12)	Кароль Кучера	Словакия	2171
12 (13)	Марк Филлиппусис	Австралия	1992
13 (15)	Серхи Бругера	Испания	1963
14 (14)	Седрик Пьолин	Франция	1932
15 (17)	Тим Хенмэн	Великобритания	1623

Совокупность данных в виде таблиц полей и записей называется списком или базой данных. MS EXCEL понятия список и база данных взаимозаменяемы. Действия, выполняемые со списком: сортировка (упорядочивание по определенному признаку), фильтрация (выборка данных по условию), автоввод (возможность ускорить ввод повторяющихся элементов списка), проверка вводимых (контроль на соответствие допустимому типу и значению), получение промежуточных итогов, построение сводных таблиц, подбор параметров (выбор из множества решений наиболее оптимального), связывание объектов (зависимость данных одного листа от данных, расположенных на другом листе),  консолидация (автоматическое получение итоговых результатов на основе данных, которые могут располагаться в разных местах), импорт и экспорт данных (объединение текстовых и графических объектов из других приложений на рабочих листах).

 

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

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

 

Исследуем возможности программы на примере таблицы лучших теннисистов.

 

Исследуем прием работы с формой.  ФОРМА – своеобразный шаблон записи в списке, состоящий из нескольких полей.

1.  Создайте на первом листе под именем «Теннис» таблицу по образцу.

2.  Введите заголовок таблицы «15 лучших теннисистов мира»

3. Установите курсорную рамку на любой столбец (поле) таблицы (списка)

4. выполните команду  Данные – Форма.

5.       На экране появиться диалоговое окно - ФОРМА 

6. Данное окно можно использовать для ввода новой записи в список (через кнопку ДОБАВИТЬ) или удалять существующие в списке записи (через кнопку УДАЛИТЬ).

7. Режим ФОРМЫ позволяет осуществить выбор данных из списка по критериям (через кнопку КРИТЕРИИ). В значения критерия можно использовать символ * (указывает произвольное количество неизвестных символов) и ? (указывает один неизвестный символ). При поиске числовых значений можно применять операторы сравнения: >, <, =

8. выход из режима ФОРМЫ осуществляется через кнопку Закрыть или через пиктограмму  Х

 

Упражнение 1: (используя режим ФОРМА)

 

1.      Добавьте  в список запись: 16(21) Томас Мустер Австрия 1611

2.      Удалите из списка запись: 12 (13)  Марк Филлиппусис Австралия 1992

3.      Осуществите поиск спортсменов из Испании:

§   Установите на полосе прокрутки бегунок в самое верхнее положение

§   нажмите кнопку Критерии

§   в поле Страна введите значение Испания

§   нажмите кнопку Далее

§   просмотрите содержимое списка по критерию выбора

4.      Осуществите поиск спортсменов Испании, набравших не более 2000 очков (примените операторы сравнения)

5.      Выполните поиск всех спортсменов, чьи имена начинаются на букву «С» из Франции

6.Выполните поиск всех спортсменов, чьи имена начинаются на букву «М» с набранными очками не менее 2700 очков

 

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

1.       установите курсорную рамку в область списка

2.       выполните команду Данные – Сортировка

3.       в появившемся диалоговом окне укажите область сортировки и ее параметры

4.       выбрав в диалоговом окне команду Параметры… на экране появиться новое диалоговое окно:

В данном окне можно уточнить вид сортировки, задающее направление сортировки: по строкам или столбцам, а так же необходимость сортировки по первому ключу: дни недели, месяцы, числа

 

Упражнение 2: Установите параметры сортировки таким образом, чтобы новая база приобрела следующий вид:

Номер	Спортсмен	Страна	очки
1 (1)	Пит Сампас	США	3716
10 (11)	Густаво Куэртен	Бразилия	2250
11 (12)	Кароль Кучера	Словакия	2171
12 (13)	Марк Филлиппусис	Австралия	1992
13 (15)	Серхи Бругера	Испания	1963
14 (14)	Седрик Пьолин	Франция	1932
…	…	…	…

 

 

 

 

 

 

 

 

 

Исследуем прием работы с использованием функции фильтра.  В EXCEL предусмотрена функция автофильтра, позволяющая выбирать и отображать записи по целому ряду критериев.

1       Установите курсорную рамку в область списка

2       Выполните команду Данные – Фильтр – Автофильтр

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

3        Выполните щелчок мышью по стрелке списка в поле СПОРТСМЕН. На экране появиться ниспадающий список (см. рис)

4        Выберите команду Условие…

5        На экране появиться диалоговое окно, в котором следует указать параметры фильтра

6        В качестве критерия автофильтра можно задавать условия.

НАПРИМЕР: чтобы исключить из списка самого лучшего и худшего теннисиста, укажите в окне Автофильтра условие:

 

 

Упражнение 3:

ð   Выберите из списка всех спортсменов, чьи имена начинаются на букву «Г» и «К».

ð   Выберите из списка всех спортсменов из России и Франции

 

2. Исследуем приемы Вычисления итогов:

1.   На новом листе «Отчет о продажах» рабочей книги создайте таблицу по образцу:       

ОТЧЕТ О ПРОДАЖАХ

 

продавец	аппаратура	производитель	класс	цена	дата
Сидоров	дека	Pioneer	HiFi	1 650р.	21.02.98
Петров	телевизор	Panasonic	обычн	2 500р.	07.02.98
Петров	проигрыватель СD	Pioneer	HiFi	1 375р.	06.02.98
Петров	усилитель	Marantz	HiFi	2 285р.	13.02.98
Сидоров	проигрыватель СD	Sony	HiFi	1 190р.	23.02.98
Петров	тюнер	Pioneer	HiFi	1 375р.	03.02.98
Сидоров	видеокамера	Sony	обычн	1 800р.	01.02.98
Петров	ресивер	Sony	HiFi	1 870р.	25.01.98
Петров	видеомагнитофон	Panasonic	обычн	2 490р.	09.02.98
Сидоров	усилитель	Sony	HiFi	1 750р.	02.02.98

 

2.   установитель курсорную рамку в область таблицы – любую ячейку и выполните команду Данные – Итоги

3.   На экране появиться диалоговое окно «Промежуточные итоги»

4.   В поле При каждом изменении в установите режим ПРОДАВЕЦ

5.   В поле Операция установите операцию – сумма

6.   В поле Добавить итоги по: установите флажок (V) в режиме ЦЕНА и исключите другие режимы

7.   выполните щелчок по кнопке ОК или нажмите клавишу ENTER

8.   Внешний вид таблицы изменится:

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

Упражнение 4:

ð   Добавить итоги по количеству проданной каждым продавцом аппаратуры.

(в поле Операция – установите КОЛИЧЕСТВО ЗНАЧЕНИЙ, в поле Добавить итоги по отметьте флажком АППАРАТУРА и снимите флажок Заменить текущие итоги)

ð   Сохраните документ под именем Урок 8*, где * - ваша фамилия

 

3. Исследуем приемы работы по Консолидации данных.

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

1. Откройте новый документ
2. на разных листах рабочей книги создайте таблицы по образцу

аппаратура	цена		аппаратура	цена
видеокамера	1 800р.		видеокамера	1 800р.
видеомагнитофон	2 490р.		видеокамера	1 800р.
дека	1 650р.		видеомагнитофон	2 490р.
проигрыватель СD	1 375р.		дека	1 650р.
проигрыватель СD	1 190р.		проигрыватель СD	1 375р.
рессивер	1 870р.		рессивер	1 870р.
телевизор	2 500р.		телевизор	2 550р.
тюнер	1 375р.		усилитель	2 285р.
усилитель	2 285р.		усилитель	1 750р.
усилитель	1 750р.		усилитель	1 850р.
ОБЩИЙ ИТОГ	18 285р.		ОБЩИЙ ИТОГ	19 420р.

 

3. на отдельном рабочем листе выберите ячейку, с которой начнется вставка данных
4. Выполните команду Данные – Консолидация
5. в появившемся диалоговом окне

в поле Функция укажите элемент – СУММА

В поле Ссылка укажите первый диапазон ячеек с данными подлежащими консолидации (выделяя названия столбцов) и нажмите кнопку ДОБАВИТЬ

В поле Ссылка укажите второй диапазон ячеек с данными подлежащими консолидации (выделяя названия столбцов) и снова нажмите кнопку ДОБАВИТЬ

В поле Список диапазонов должно быть две ссылки

6. Для лучшей читаемости данных отметьте флажком использование в качестве заголовков значения в левом столбце и подписи верхней строки.
7. выполните щелчок на кнопке ОК или нажмите клавишу ENTER
8. На экране вы получаете таблицу вида:

аппаратура	цена
видеокамера	5 400р.
видеомагнитофон	4 980р.
дека	3 300р.
проигрыватель СD	3 940р.
рессивер	3 740р.
телевизор	5 050р.
тюнер	1 375р.
усилитель	9 920р.
ОБЩИЙ ИТОГ	37 705р.

 

9. Сохраните документ под именем Консолидация*, где * - ваша фамилия

 

4. Исследуем приемы работы в режиме Поиск решения

 

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

ЗАДАЧА: необходимо оптимальным образом спланировать производство изделий, представленных в таблице. Цель операции – достичь итоговой прибыли 7000 рублей при условии выпуска не более 400 изделий.

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

Общая Прибыль = (Цена штуки - Затраты)* количество штук

Строка СУММА по всем столбцам должна содержать формулы итоговых значений

планирование производственного процесса
изделия	затраты	количество штук	цена штук	общая прибыль
штуцер	25р.	109	40р.	1 635р.
редуктор	55р.	76	95р.	3 040р.
вал	20р.	57	35р.	855р.
блок	15р.	32	20р.	160р.
крюк	10р.	75	17р.	504р.
сумма	125р.	346	207р.	6 194р.

2. Выполните команду Сервис – Поиск решения
3. на экране откроется диалоговое окно

 

 

 

 

 

 

 

 

 

4. в поле Установить целевую ячейку ввести адрес ячейки, значение в которой используется в качестве критерия – адрес ячейки, где расположена сумма общей прибыли (6 194 р.)
5. задать вид критерия, устанавливая отметку в поле минимального, максимального или точного значения (в нашем примере – 7000 р)
6. в поле изменяя ячейки задать ссылку на диапазон ячеек, содержащие данные, которые можно варьировать в процессе поиска решений (в нашем случае – это диапазон ячеек столбца – КОЛИЧЕСТВО ШТУК)
7. нажать клавишу ДОБАВИТЬ

8.       на экране появиться новое диалоговое окно:

9.       в открывшемся окне в поле ссылка на ячейку ввести адрес ячейки, хранящей значение величины, включаемой в формулу ограничения – адрес ячейки, содержащей сумму количества штук

10.    в поле оператора выбрать <=

11. в поле Ограничение ввести адрес ячейки, содержащей необходимое значение – в нашем случае это значение равно 400
12. закрыть диалоговое окно Добавление ограничения
13. в диалоговом окне Поиск решения вновь выполнить команду ДОБАВИТЬ
14. снова откроется окно Добавление ограничения
15. установите остальное ограничение – данные ячеек КОЛИЧЕСТВО ШТУК должны быть целыми числами
16. запустите программу Поиск решения нажатием кнопки ВЫПОЛНИТЬ или клавиши ENTER
17. после выполненного поиска решения на экране появиться диалоговое окно:
18. после выполненного поиска решения на экране появиться таблица вида:
19. Сохраните документ под именем Поиск решения*, где * - ваша фамилия

изделия	затраты	количество штук	цена штук	общая прибыль
штуцер	25р.	162	40р.	2 430р.
редуктор	55р.	75	95р.	3 000р.
вал	20р.	62	35р.	930р.
блок	15р.	30	20р.	150р.
крюк	10р.	70	17р.	490р.
сумма	125р.	399	207р.	7 000р.

 

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

1. создайте новый документ

Товар	Модель	Название	Цена	Кол-во	Сумма
Факс	F200 G	персональный	1076	56
ксерокс	C300 GLS	персональный	1291	20
ксерокс	C210 GLS	профессиональный	1896	26
ксерокс	C300 GLS	профессиональный	1755	120
факс	F100 G	деловой	1300	38
факс	F150 G	персональный	3180	85
Факс	F250 G	персональный	2030	421
ксерокс	C500 GLS	персональный	2300	50
Факс	F500 G	деловой	1890	59
Факс	F550 G	профессиональный	5120	90
Факс	F550 G	персональный	3000	39
ксерокс	C210 GLS	деловой	2500	8
ксерокс	C310 GLS	персональный	2000	19

2. создайте таблицу по образцу, вычислив недостающие данные:

 

3. По данным таблицы создайте свободную таблицу, для этого:

ü  Выделите всю таблицу и выполните Данные/ Свободная таблица

ü  (шаг 1) включите переключатель в списке или базе данных Microsoft Excel, Далее

ü  (шаг 2) проверьте указанный диапазон, Далее

ü  (шаг 3) укажите местоположение сводной таблицы – Новый лист, по кнопке МАКЕТ войдите в диалоговое окно конструктора и перетащите кнопку Товар на Строку, кнопку Название на Столбец, кнопку Сумма на Данные, Готово

ü  На новом листе вы получите сводную таблицу

4. Сравните полученную таблицу с образцом.

Сумма по полю сумма	Название
товар	деловой	персональный	профессиональный	Общий итог
Ксерокс	20000	178820	259896	458716
Факс	160910	1302186	460800	1923896
Общий итог	180910	1481006	720696	2382612

5. Выполнить двойной щелчок ЛКМ по полю Название в сводной таблице, выберите Ориентацию По строкам

6. Выполните двойной щелчок по полю Товар в сводной таблице, выберите Ориентацию  По столбцам

7. Назначьте панель инструментов Сводные таблицы (если она отсутствует на экране) – Вид/ Панель инструментов/ Сводные таблицы

8. Измените значения в основной таблице по факсам (название – Деловой), щелкните по кнопке Обновить данные на панели инструментов, просмотрите изменения в сводной таблице

9. Результат покажите преподавателю. Удалите сводную таблицу

10. Добавьте в основную таблицу столбец Год выпуска и введите значения 1999 и 2000 (произвольно)

11. Создайте новую сводную таблицу, на шаге № 3 выполните следующее:

ü  перетащите кнопку Товар на Страницу, кнопку Название – на Строку, кнопку Год выпуска – на Столбец, кнопку Количество и Сумма в Данные

ü  разместите таблицу на новом листе.   

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

13. Сохраните документ под именем Сводная таблица*, где * - ваша фамилия

 

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

 

Создайте таблицу по образцу. Определите:

№	Фамилия И О	отдел	должность	оклад
1	Савчук С. В.	Отдел кадров	Зав отделом	3500
2	Андронов П. В.	Бухгалтерия	Зав отделом	4300
3	Кожемякин П. А.	Планово-экономический	Экономист	2500
4	Короткова М. В.	Бухгалтерия	Кассир	2000
5	Драгунов Т. Т.	Бухгалтерия	Бухгалтер	2200
6	Шагунова М. О.	Планово-экономический	техник	1600
7	Бояров К. И	Планово-экономический	зав отделом	3800
8	Демина А. П	Отдел кадров	инспектор	2100
9	Васина И. Т	Бухгалтерия	бухгалтер	2000
10	Логунова И. М	Отдел кадров	инспектор	2100
11	Платунов И. С	Планово-экономический	техник	1800
12	Копысова П. А.	Планово-экономический	Экономист	2800

,

§     суммарную заработную плату

§     в отдельном столбце определите общую заработную плату по отделам

§     определите (в отдельном столбце) процент заработной платы отдела от общей суммы заработной платы

§     в отдельном столбце определите среднюю зарплату для каждой должности

§     используя режим Автофильтра, определите сотрудников, чей оклад от 3000 до 4000

§     создайте сводную таблицу по образцу:

Сумма по полю оклад	Фамилия И О
отдел	Андронов П. В.	Бояров К. И	…	Шагунова М. О.	Общий итог
Бухгалтерия	4300				10500
Отдел кадров					5600
Планово-экономический					2500
Планово-экономический		3800		1600	5400
Общий итог	4300	3800	…	1600	24000

 

§     Работу покажите преподавателю

§     Удалите лист со сводной таблицей и создайте свой макет сводной таблицы

§     Сохраните документ под именем Контроль*, где * - ваша фамилия

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

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

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

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

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

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

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

 

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

 

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

 

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

 

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

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

ð   цели

ð   средства

ð   план работы

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

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

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

ð   вид баз данных,

ð   общий алгоритм создания базы данных

ð   типы данных.