Инструкционная карта к практической работе Exce

ИНСТРУКЦИОННАЯ КАРТА

НА ВЫПОЛНЕНИЕ ПРАКТИЧЕСКОЙ  РАБОТЫ 

По дисциплине: Информационные технологии в профессиональной деятельности

Специальность:

Тема: Обработка данных средствами электронных таблиц  Microsoft Excel

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

Приобретаемые умения и  навыки: проводить анализ массива данных и его обработку (сортировка, фильтрация данных, использование функций); составлять план проведения поэтапного моделирования; моделировать ситуационные задачи

Используемые средства: ПЭВМ, ОС Windows,  MS Excel, ИК, опыт преподавателя, опыт студента

Норма времени: 4 часа

Техника безопасности: ЗАПРЕЩАЕТСЯ: :  Трогать разъемы соединительных кабелей, :  Включать и выключать аппаратуру без указания преподавателя, :  Прикасаться к экрану и тыльной стороне монитора, :  Класть дискеты, книги, тетради, ручки и  т.п. на клавиатуру и монитор.

При длительной работе за ПК необходимо соблюдать следующие санитарные правила: :  При продолжительности работы 1,5-2 часа делать перерыв 10 мин. через каждый час, :  В случае возникновения у работающего зрительного дискомфорта и других неблагоприятных ощущений целесообразно выполнять комплекс упражнений для глаз и туловища, При появлении запаха гари немедленно прекратить работу, отключить питание ПК и сообщить об этом преподавателю. Не пытайтесь самостоятельно устранять неисправности в работе аппаратуры. Вы отвечаете за сохранность рабочего места

План работы: Входной контроль Исследовательская работа: -          заполнение таблицы -          редактирование и форматирование данных -          форматирование таблицы -          решение задач оптимизации (максимум и минимум, среднее значение) -          сортировка  и фильтрация данных Самостоятельная работа Выходной контроль Домашнее задание

Ход работы: Входной контроль: Ответьте устно на вопросы: Какую роль выполняет сетка в рабочей области Excel? Какие операции позволяет выполнять меню Формат/Ячейки.  Опишите алгоритм выделения диапазона ячеек. Опишите правила задания формул в ячейках. Будут ли выполнены расчеты, если в формулу введен адрес на русском языке? Опишите алгоритм добавления строк и столбцов в таблицу. Опишите алгоритм копирования данных с одного листа рабочей книги на другой. Дайте определение понятиям «Сортировка данных», «Фильтрация данных»

2. Исследовательская работа:

1.        Выполните запуск программы MS Excel.

2.        Создайте новый документ  «Анализ данных»  в папке ПР №5

3.        Добавьте новые листы и дайте заголовки: лист 1 -  «Функции1», лист 2 - «Функции2»,

         лист 3 - «Сортировка», лист 4 -  «Фильтрация», лист 5 -  «Самостоятельная работа»

      (для добавления листа используйте контекстное меню, команду Добавить ).

4.        Откройте файл Работа с таблицами и скопируйте следующий фрагмент этого файла на каждый лист документа «Анализ данных»:

 

&    Решение задач оптимизации с использованием мастера функций

Использование статистических функций:

1.        Откройте лист Функции 1  

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

3.        Найдите максимальную выручку с продажи в рублях:

-    выделите ячейку С16,

-    запустите мастер функций (Вставка/Функция ),

-    в окне Категория выберите Статистические,

-    в списке Функция найдите МАКС и прочитайте ее характеристику, нажмите ОК,

-    появившееся диалоговое окно перенесите на пустое место листа,

-    выделите диапазон ячеек K6:K13 (выделенный диапазон автоматически отобразится в диалоговом окне мастера функций),

-    нажмите ОК,

-    ячейка С16 окажется заполненной, а в строке формул над таблицей отобразиться формула

4.        Аналогично найдите значение максимальной выручки в долларах в ячейке D16

5.        Используя функцию МИН (минимальное значение), заполните ячейки С17, D17

6.        Выбрав функцию СРЗНАЧ (среднее значение), можно подсчитать среднее арифметическое  значений:

-    выделите ячейку С18,

-    запустите мастер функций (Вставка/Функция/Статистические )

-    в списке Функция найдите СРЗНАЧ, прочитайте ее характеристику, нажмите ОК,

-    выделите диапазон ячеек K6:K13, нажмите ОК

7.        Аналогично найдите значение в ячейке D18.

8.        Для заполнения ячеек С20 и D20 подсчитаем количество моделей холодильной техники, имеющих количество проданных единиц свыше 2 (т.е.3). Для этого:

-          выделите ячейку С20

-          запустите мастер функций (Вставка/Функция/Статистические )

-          в списке Функция найдите СЧЕТЕСЛИ, прочитайте ее характеристику, нажмите ОК

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

-          выделите диапазон ячеек I6:I13, он отразиться в строке Диапазон

-          в строке Критерий наберите выражение    >=3

-          нажмите ОК

-          аналогично найдите значение в ячейке D20

9.        Проверьте формулы во всех ячейках.

10.      Cамостоятельно найдите общую выручку, используя категорию математических функций (Вставка/Функция/Математические  функция СУММ)

Использование логических функций:

1.    Откройте лист Функции 2  

2.    Удалите в таблице столбцы: Масса, Дата реализации, Курс доллара, Выручка доллары и строку 4

3.    Дополните таблицу новой информацией:

4.    Заполните столбец К по условию: если стоимость 1 единицы товара превышает 1000 рублей, то покупателю дается указанная в ячейке J2 текущая скидка, в других случаях скидка не дается. Для этого выполните следующее:

-          выделите ячейку К5

-          запустите мастер функций (Вставка/Функция/Логические )

-          в списке Функция найдите ЕСЛИ, прочитайте ее характеристику, нажмите ОК

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

-          в строке Лог_выражение наберите I5>1000

-          в строке  Значение_если_истина   наберите выражение для подсчета стоимости товара со скидкой  I5-I5*J2 (для правильности вычислений при копировании этой формулы необходимо установить абсолютную адресацию у номера ячейки J2, тогда формула примет вид: I5-I5*J$2)

-          в строке Значение_если_ложь наберите фразу Скидки нет   

-          нажмите ОК

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

-          проверьте правильность формул и правильность вычислений

5.    Добавьте в таблицу еще один столбец Исследование спроса и, используя одновременно две функции: ЕСЛИ  и  И, заполните его значениями. Для этого в диалоговом окне функции ЕСЛИ введите следующие значения:

Т.е. в строке формул появится формула:

Примечание: при возникновении ошибок проверьте правильность адресации

&    Использование методов сортировки данных

1.    Откройте лист Сортировка  

2.    Удалите в таблице столбцы  Дата реализации, Стоимость 1 шт, Выручка руб.  и строку 4

3.    Отсортируйте данные в таблице модели холодильной техники в алфавитном порядке. Для этого:

- выделите диапазон ячеек В4:В12

- выберите на панели инструментов пиктограмму    

- в появившемся диалоговом окне установите
переключатель у команды Автоматически
расширить выделенный диапазон

-  нажать Сортировка
(все записи будут расположены в алфавитном порядке)

4.    Для сортировки в двух или нескольких
диапазонах (по двум или нескольким столбцам) 
выполните следующие шаги:

-    выделите диапазон ячеек A4:К12

-    выберите меню Данные/Сортировка

-    установите в появившемся диалоговом окне
 параметры (см.рисунок)

-    нажмите ОК

&    Фильтрация  (выборка) данных, удовлетворяющих

определенным условиям

1.    Откройте лист Фильтрация  

2.    Удалите в таблице столбцы  Дата реализации, Количество, Выручка, Курс доллара  и строку 4

3.    Выполните выборку данных, удовлетворяющих условию: вывести модели холодильников, у которых объем морозильной камеры – от 70 до 80, верхнее расположение и  масса – менее 75 кг. Для этого:

- выделите диапазон ячеек А4:I12

- выберите команду меню Данные/Фильтр/Автофильтр. У каждого столбца таблицы появится значок фильтра

- нажмите ЛКМ на значок фильтра столбца Масса,в появившемся списке выберите значение Условие… и  в диалоговом окне задайте следующие параметры:

- нажмите ОК

- аналогично выберите фильтр столбца  Расположение морозильной камеры и в появившемся списке выберите параметр Верхнее

- далее выберите фильтр столбца  Морозильной камеры  и окне Условие… задайте значения  меньше 80 И больше 70

- данная таблица изменит свой внешний вид и значки активных фильтров будут отмечены синим цветом:

-  выделите полученную таблицу и скопируйте ее на этот же лист ниже

-  повторно выберите команду меню Данные/Фильтр/Автофильтр. В это случае фильтр будет отменен и таблица примет начальный вид

3. Самостоятельная работа:

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

	А	В		С	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

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

16		11	Солянка овощная	ООО "Агроинвест"	0,650 кг	16,45	0,55	19,74	0,66
17			максимальное значение
18			минимальное значение
19			среднее значение

3.         Отсортируйте наименование продукции в алфавитном порядке, внутри каждого наименования товара - цену в руб. по возрастанию

4.         Выберите  из заданного списка только ту характеристику продукции, которая содержит наименование «Огурцы консервированные»

5.         Результат сравните с образцом:

6.         Скопируйте полученную таблицу ниже.

7.         Постройте диаграмму по полученным данным

8.         Чтобы вернуть в таблице все записи, снова щелкните на значок меню  и выберите строку (Все).

9.         Выведите на экран список товаров, которые поставляет ООО «Агроинвест» и стоимость которых в руб. не превышает 30,00 руб.

10.      Постройте диаграмму по полученным данным.

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

Найти практическую задачу, решаемую средствами Excel на применение функций и формул, с использованием сортировки и фильтрации данных.