Инструкционная карта к практической работе Exce
ИНСТРУКЦИОННАЯ КАРТА
НА ВЫПОЛНЕНИЕ ПРАКТИЧЕСКОЙ РАБОТЫ
По дисциплине: Информационные технологии в профессиональной деятельности
Специальность:
Тема: Обработка данных средствами электронных таблиц Microsoft Excel
Цель: исследовать возможности корректировки и форматирования структуры электронных таблиц, анализа массива данных и его обработки; изучить приемы моделирования ситуационных задач;
Приобретаемые умения и навыки:
проводить анализ массива данных и его обработку (сортировка, фильтрация данных, использование функций); составлять план проведения поэтапного моделирования; моделировать ситуационные задачи
Используемые средства:
ПЭВМ, ОС Windows, MS Excel, ИК, опыт преподавателя, опыт студента
Норма времени: 4 часа
Техника безопасности:
ЗАПРЕЩАЕТСЯ:
Трогать разъемы соединительных кабелей,
Включать и выключать аппаратуру без указания преподавателя,
Прикасаться к экрану и тыльной стороне монитора,
Класть дискеты, книги, тетради, ручки и т.п. на клавиатуру и монитор.
При длительной работе за ПК необходимо соблюдать следующие санитарные правила:
При продолжительности работы 1,5-2 часа делать перерыв 10 мин. через каждый час,
В случае возникновения у работающего зрительного дискомфорта и других неблагоприятных ощущений целесообразно выполнять комплекс упражнений для глаз и туловища,
При появлении запаха гари немедленно прекратить работу, отключить питание ПК
и сообщить об этом преподавателю.
Не пытайтесь самостоятельно устранять неисправности в работе аппаратуры.
Вы отвечаете за сохранность рабочего места
План работы:
Входной контроль
Исследовательская работа:
заполнение таблицы
редактирование и форматирование данных
форматирование таблицы
решение задач оптимизации (максимум и минимум, среднее значение)
сортировка и фильтрация данных
Самостоятельная работа
Выходной контроль
Домашнее задание
Ход работы:
Входной контроль:
Ответьте устно на вопросы:
Какую роль выполняет сетка в рабочей области Excel?
Какие операции позволяет выполнять меню Формат/Ячейки.
Опишите алгоритм выделения диапазона ячеек.
Опишите правила задания формул в ячейках.
Будут ли выполнены расчеты, если в формулу введен адрес на русском языке?
Опишите алгоритм добавления строк и столбцов в таблицу.
Опишите алгоритм копирования данных с одного листа рабочей книги на другой.
Дайте определение понятиям «Сортировка данных», «Фильтрация данных»
Исследовательская работа:
Выполните запуск программы MS Excel.
Создайте новый документ «Анализ данных» в папке ПР №5
Добавьте новые листы и дайте заголовки: лист 1 - «Функции1», лист 2 - «Функции2»,
лист 3 - «Сортировка», лист 4 - «Фильтрация», лист 5 - «Самостоятельная работа»
(для добавления листа используйте контекстное меню, команду Добавить ).
Откройте файл Работа с таблицами и скопируйте следующий фрагмент этого файла на каждый лист документа «Анализ данных»:
Решение задач оптимизации с использованием мастера функций
Использование статистических функций:
Откройте лист Функции 1
Добавьте новую таблицу, используя изученные приемы форматирования:
Найдите максимальную выручку с продажи в рублях:
выделите ячейку С16,
запустите мастер функций (Вставка/Функция ),
в окне Категория выберите Статистические,
в списке Функция найдите МАКС и прочитайте ее характеристику, нажмите ОК,
появившееся диалоговое окно перенесите на пустое место листа,
выделите диапазон ячеек K6:K13 (выделенный диапазон автоматически отобразится в диалоговом окне мастера функций),
нажмите ОК,
ячейка С16 окажется заполненной, а в строке формул над таблицей отобразиться формула
Аналогично найдите значение максимальной выручки в долларах в ячейке D16
Используя функцию МИН (минимальное значение), заполните ячейки С17, D17
Выбрав функцию СРЗНАЧ (среднее значение), можно подсчитать среднее арифметическое значений:
выделите ячейку С18,
запустите мастер функций (Вставка/Функция/Статистические )
в списке Функция найдите СРЗНАЧ, прочитайте ее характеристику, нажмите ОК,
выделите диапазон ячеек K6:K13, нажмите ОК
Аналогично найдите значение в ячейке D18.
Для заполнения ячеек С20 и D20 подсчитаем количество моделей холодильной техники, имеющих количество проданных единиц свыше 2 (т.е.13 EMBED Equation.3 14153). Для этого:
выделите ячейку С20
запустите мастер функций (Вставка/Функция/Статистические )
в списке Функция найдите СЧЕТЕСЛИ, прочитайте ее характеристику, нажмите ОК
на экране появится диалоговое окно:
выделите диапазон ячеек I6:I13, он отразиться в строке Диапазон
в строке Критерий наберите выражение >=3
нажмите ОК
аналогично найдите значение в ячейке D20
Проверьте формулы во всех ячейках.
Cамостоятельно найдите общую выручку, используя категорию математических функций (Вставка/Функция/Математические функция СУММ)
Использование логических функций:
Откройте лист Функции 2
Удалите в таблице столбцы: Масса, Дата реализации, Курс доллара, Выручка доллары и строку 4
Дополните таблицу новой информацией:
Заполните столбец К по условию: если стоимость 1 единицы товара превышает 1000 рублей, то покупателю дается указанная в ячейке J2 текущая скидка, в других случаях скидка не дается. Для этого выполните следующее:
выделите ячейку К5
запустите мастер функций (Вставка/Функция/Логические )
в списке Функция найдите ЕСЛИ, прочитайте ее характеристику, нажмите ОК
на экране появится диалоговое окно:
в строке Лог_выражение наберите I5>1000
в строке Значение_если_истина наберите выражение для подсчета стоимости товара со скидкой I5-I5*J2 (для правильности вычислений при копировании этой формулы необходимо установить абсолютную адресацию у номера ячейки J2, тогда формула примет вид: I5-I5*J$2)
в строке Значение_если_ложь наберите фразу Скидки нет
нажмите ОК
используя маркер автозаполнения, протяните формулу в другие ячейки
проверьте правильность формул и правильность вычислений
Добавьте в таблицу еще один столбец Исследование спроса и, используя одновременно две функции: ЕСЛИ и И, заполните его значениями. Для этого в диалоговом окне функции ЕСЛИ введите следующие значения:
Т.е. в строке формул появится формула:
Примечание: при возникновении ошибок проверьте правильность адресации
Использование методов сортировки данных
Откройте лист Сортировка
Удалите в таблице столбцы Дата реализации, Стоимость 1 шт, Выручка руб. и строку 4
Отсортируйте данные в таблице модели холодильной техники в алфавитном порядке. Для этого:
- выделите диапазон ячеек В4:В12
- выберите на панели инструментов пиктограмму
- в появившемся диалоговом окне установите переключатель у команды Автоматически расширить выделенный диапазон
- нажать Сортировка (все записи будут расположены в алфавитном порядке)
Для сортировки в двух или нескольких диапазонах (по двум или нескольким столбцам) выполните следующие шаги:
выделите диапазон ячеек A4:К12
выберите меню Данные/Сортировка
установите в появившемся диалоговом окне параметры (см.рисунок)
нажмите ОК
Фильтрация (выборка) данных, удовлетворяющих
определенным условиям
Откройте лист Фильтрация
Удалите в таблице столбцы Дата реализации, Количество, Выручка, Курс доллара и строку 4
Выполните выборку данных, удовлетворяющих условию: вывести модели холодильников, у которых объем морозильной камеры – от 70 до 80, верхнее расположение и масса – менее 75 кг. Для этого:
- выделите диапазон ячеек А4:I12
- выберите команду меню Данные/Фильтр/Автофильтр. У каждого столбца таблицы появится значок фильтра
- нажмите ЛКМ на значок фильтра столбца Масса,в появившемся списке выберите значение Условие и в диалоговом окне задайте следующие параметры:
- нажмите ОК
- аналогично выберите фильтр столбца Расположение морозильной камеры и в появившемся списке выберите параметр Верхнее
- далее выберите фильтр столбца Морозильной камеры и окне Условие задайте значения меньше 80 И больше 70
- данная таблица изменит свой внешний вид и значки активных фильтров будут отмечены синим цветом:
- выделите полученную таблицу и скопируйте ее на этот же лист ниже
- повторно выберите команду меню Данные/Фильтр/Автофильтр. В это случае фильтр будет отменен и таблица примет начальный вид
3. Самостоятельная работа:
Откройте лист Самостоятельная работа и создайте на нем следующий документ, используя известные приемы форматирования:
А
В
С
D
E
F
G
H
I
J
1
Нижегородское оптово-розничное предприятие
2
П р о д и н в е ст
3
прайс-лист от 01.09.2004г.
4
без НДС
с учетом НДС (20%)
5
№ п/п
Наименование продукции
Поставщик
Вес, объем
Цена, руб.
Цена, долл.
Цена, руб.
Цена, долл.
6
1
Грибы маринованные
ООО "Агроинвест"
0,350 кг
28,50
0,95
34,20
1,14
7
2
Огурцы консервированные
ЧП Пахомов
3кг
43,00
1,43
51,60
1,72
8
3
Огурцы консервированные
ООО "РоссНи"
3 кг
46,00
1,53
55,20
1,84
9
4
Сок томатный
ООО "Агроинвест"
3л
34,00
1,13
40,80
1,36
10
5
Джем "Яблочный"
ЧП Черемухин
0,600 кг
14,00
0,47
16,80
0,56
11
6
Рис
ОАО "Макарна"
1кг
11,50
0,38
13,80
0,46
12
7
Рис
ОАО "Макарна"
0,500 кг
11,00
0,37
13,20
0,44
13
8
Крупа перловая
ОАО "Макарна"
0,500 кг
4,80
0,16
5,76
0,19
14
9
Томаты консервированные
ЧП Пахомов
3 кг
48,00
1,60
57,60
1,92
15
10
Солянка овощная
ООО "Агроинвест"
0,500 кг
13,00
0,43
15,60
0,52
16
11
Солянка овощная
ООО "Агроинвест"
0,650 кг
16,45
0,55
19,74
0,66
Добавьте в конце таблицы новые строки: максимальное и минимальное значения, среднее значение. Выполните соответствующие расчеты:
16
11
Солянка овощная
ООО "Агроинвест"
0,650 кг
16,45
0,55
19,74
0,66
17
максимальное значение
18
минимальное значение
19
среднее значение
Отсортируйте наименование продукции в алфавитном порядке, внутри каждого наименования товара - цену в руб. по возрастанию
Выберите из заданного списка только ту характеристику продукции, которая содержит наименование «Огурцы консервированные»
Результат сравните с образцом:
13 EMBED PBrush 1415
Скопируйте полученную таблицу ниже.
Постройте диаграмму по полученным данным
Чтобы вернуть в таблице все записи, снова щелкните на значок меню 13 EMBED PBrush 1415 и выберите строку (Все).
Выведите на экран список товаров, которые поставляет ООО «Агроинвест» и стоимость которых в руб. не превышает 30,00 руб.
Постройте диаграмму по полученным данным.
Домашнее задание:
Найти практическую задачу, решаемую средствами Excel на применение функций и формул, с использованием сортировки и фильтрации данных.
13PAGE 14415
13PAGE 14115