Обработка и анализ данных в среде EXCEL
Министерство образования и науки, молодежи и спорта Украины
Севастопольский национальный технический университет
Факультет Экономики и менеджмента
Кафедра
Учета и аудита
КОНТРОЛЬНАЯ
РАБОТА
На тему:
«Обработка и анализ данных в среде
EXCEL»
по дисциплине
«Информатика»
"_
Севастополь
2011
Оглавление
Задание №1 3
Цель работы 3
Постановка задачи 3
Решение 3
Задание №2 3
Цель работы 3
Постановка задачи 3
Решение 3
Задание №3 3
Цель работы 3
Постановка задачи 3
Решение 3
Выводы по работе 3
Задание №1
Цель работы
Цель работы - приобрести навыки
работы с таблицами, освоить
технологию создания и
Постановка задачи
1. Выбрать вариант I= (N mod 25) +1, где N - последние две цифры зачетной книжки, а I - остаток от деления N на 25.
2.
Вычислить значения функции на
заданном интервале с заданным
шагом изменения аргумента.
Результаты оформить в виде
таблицы. Первый столбец
3.
Построить график функции.
3.1.
На графике разными маркерами
выделить максимальное и
3.2. Метки на оси X представить в формате с фиксированной точкой (числовой формат), а на оси Y – с плавающей точкой (экспоненциальный формат).
Решение
- Выбор варианта.
I= (N mod 25) +1,
где N=78 - последние две цифры зачетной книжки,
I=4 - остаток от деления 78 на 25.
I=3+1=4
Выбираем 4 вариант.
| № | Функция | Интервал изменения аргумента | Шаг изменения аргумента |
| 4 | y = | |
0.2 |
- Оформление вычислений в таблицу.
2.1. Вводим первое значение аргумента х=0 в ячейку A2.
2.2. Вводим второе значение аргумента х=0,2 в ячейку A3.
2.3. Выделяем ячейки как показано на рис.1. Подводим указатель к маркеру заполнения. Указатель превратится в черный крестик. Нажимаем кнопку мыши и перетаскиваем маркер до появления значения аргумента х=3.
2.4. В ячейку B2 вводим формулу
2.5. Методом автозаполнения (см. п. 2.3) протягиваем формулу до ячейки B12 (Рисунок 3).
Первый столбец таблицы теперь содержит значения аргумента x, второй столбец – значения функции y.
3. Построим график
функции. Вызываем мастер диаграмм,
выбираем тип диаграммы (график), выставляем
диапазон. Для этого выделяем таблицу
данных и вставляем в строку диапазон.
Добавляем легенду и выбираем место, где
разместим диаграмму (на имеющемся листе).
Нажимаем кнопку «готово». (Рисунок 4).
Рисунок 4.
Нажимаем правой кнопкой мышки на диаграмму и выбираем параметры диаграммы. Выставляем название диаграммы, название осей.
Выставляем минимальное и максимальное значение: выделяем точки min и max значений нажатием кнопок мышки в последовательности правая-левая-правая и выбираем формат точки данных. Изменяем цвет и формат маркера.
Рисунок 5.
Двойным
щелчком на осях координат вызываем
диалоговые окна настройки. Метки на
оси X представляем в формате с
фиксированной точкой (числовой формат),
а на оси Y – с плавающей точкой
(экспоненциальный формат) (Рисунок 5).
Готовый график функции показан на Рисунке 6.
Задание №2
Цель работы
Цель – изучить принципы выполнения операций сортировки данных (строк / столбцов) и поиска записей с использованием фильтров.
Постановка задачи
1. Выбрать вариант i=N mod 14, где N – две последние цифры зачетки.
2.
Создать таблицу для заданной
предметной области в
3.
Выполнить сортировку строк по
значениям одного и нескольких
полей. Выполнить сортировку
4.
Выполнить выборку данных из
таблицы с использованием
4.1. Автофильтр. Использовать в качестве условий: 1) точную форму (константу) выбора; 2) текст выбора (шаблон); 3) формулу выбора (условие).
Задать критерий типа "И" для поиска данных по значениям двух полей. Задать критерий типа "ИЛИ" для поиска данных в отдельном столбце.
4.2.
Расширенный фильтр. Создать на
рабочем листе таблицу
Решение
- Выбираем вариант:
i=N
mod 15=9,
где N=78
– две последние цифры зачетной книжки.
Выбираем вариант №8:
| Номер варианта | Предметная область |
| 8 | авиакасса |
- Создаем таблицу для заданной предметной области (Таблица 1).
Таблица 1.
| дата | пункт отправления | пункт назначения | класс поездки | цена, USD | количество | сумма |
| 04.ноя | Киев | Днепропетровск | эконом | 72,9 | 1 | 72,9 |
| 04.ноя | Киев | Донецк | эконом | 102,9 | 2 | 205,8 |
| 06.ноя | Киев | Львов | бизнес | 226,9 | 1 | 226,9 |
| 09.ноя | Киев | Львов | эконом | 114,9 | 1 | 114,9 |
| 04.ноя | Киев | Москва | эконом | 85 | 4 | 340 |
| 06.ноя | Киев | Москва | эконом | 115 | 1 | 115 |
| 07.ноя | Киев | Москва | бизнес | 400 | 2 | 800 |
| 07.ноя | Киев | Одесса | бизнес | 216,9 | 2 | 433,8 |
| 06.ноя | Киев | Самара | эконом | 210 | 1 | 210 |
| 05.ноя | Киев | Симферополь | эконом | 92,1 | 2 | 184,2 |
| 09.ноя | Киев | Тбилиси | эконом | 170 | 1 | 170 |
| 04.ноя | Киев | Харьков | бизнес | 210,9 | 1 | 210,9 |
- Для выполнения задания созданную таблицу копируем четыре раза.
На
первой таблице для сортировки всех
данных таблицы в алфавитном порядке
(по колонке «Пункт назначения») жмем
на выпадающий список в ячейке C3 (Рисунок
7). Выбираем пункт «по возрастанию», после
чего программа автоматически сортирует
все строки таблицы по названию пункта
назначения в алфавитном порядке (Риунок
8).
Рисунок
7.
Рисунок 8.
Во
второй скопированной таблице
Рисунок 10.
В третьей скопированной таблице выполняем сортировку по значениям двух полей. Аналогично предыдущему случаю, сортируем данные таблицы по значению столбца «дата отправления», выбрав в меню сортировки «по возрастанию». В этой же таблице включаем сортировку по полю «цена», применив числовой фильтр «от минимума к максимуму» (Рисунок 11).
Рисунок 11.
Результат
сортировки по двум столбцам представлен
на рисунке 12.
Рисунок 12.
В
четвертой таблице производим сортировку
столбцов по значениям заголовков.
Для этого выделяем всю таблицу,
и выбираем команду Данные – Сортировка.
В диалоговом окне «Параметры» указываем
сортировать столбцы диапазона.
Далее в окне «Сортировка» указываем
сортировать по «Строка 1» (значения
заголовков, как указано в задании).
Сортировку производим по значениям, порядок
сортировки «по возрастанию» (Рисунок
13).
Рисунок
13.
Результат сортировки столбцов представлен на рисунке 14.
Рисунок 14.
- Производим выборку данных из таблицы с использованием фильтров.
4.1.1.
Отобразим в таблице данные
только по количеству требующихся
билетов, используя автофильтр. Используем
в качестве условий точную форму (константу)
выбора. Задаем критерий типа "ИЛИ"
для поиска данных в столбце «количество»
(Рисунок 15).
Рисунок 15.
Результат
представлен на Рисунке 16.
Рисунок 16
- Отобразим в таблице все рейсы, с пунктом отправления Киев, используя текст выбора (шаблон).
- Отобразим в таблице все рейсы, цена билетов на которые находится в пределах от 84$ до 200$, используя формулу выбора (условие). Задаем критерий типа "И" для поиска данных в столбце «цена» (Рисунок 18) Результат представим на рисунке 19.
Рисунок 18.
Рисунок
19.
4.2.
Расширенный фильтр. Создаём на
рабочем листе таблицу
Рисунок 20.
Рисунок 21.
Задание №3
Цель работы
Цель - приобрести навыки применения функций просмотра и ссылок в решении экономических задач, обработки списков в режиме подбора параметров, создания сводных таблиц.
Постановка задачи
1. Выбрать вариант.
2. Перенести на рабочий лист таблицу 3.1. Добавить в таблицу пустые столбцы с заголовками «Коэф_A», «Коэф_В», «Зарплата». Присвоить таблице имя «Сотрудники»
3.
Создать на этом же рабочем
листе таблицу, содержащую
4.
В свободную ячейку рабочего
листа занести значение
5.
Заполнить поля «Коэф_A», «
6.
Вычислить Фонд заработной
7.
Построить сводную таблицу. На
основании сводной таблицы
Решение
- Выбираем вариант:
i=N
mod 15=9,
где N=78 – две последние цифры зачетной книжки. Выбираем вариант №3.
- Переносим на рабочий лист таблицу 3.1. Добавляем в таблицу пустые столбцы с заголовками «Коэф_A», «Коэф_В», «Зарплата». Присваиваем таблице имя «Сотрудники».
- Создаем на этом же рабочем листе таблицу, содержащую поля: «Должность», «Коэф_А», «Коэф_В». Заполняем таблицу в соответствии с вариантом №3 (таблица 3.2 методических указаний). В поле «Должность» вводим значения: Кассир, Продавец, Ст. продавец. Присваиваем таблице имя «Коэффициенты».
| Должность | Коэф_A | Коэф_В |
| Кассир | 1,75 | 70 |
| Продавец | 2,1 | 85 |
| Ст. продавец | 2,8 | 90 |
- В свободную ячейку рабочего листа заносим значение минимальной заработной платы в соответствии с вариантом №3 (таблица 3.2 методических указаний). Присваиваем ячейке имя «Мин_зарплата».
| Мин. зарплата |
| 420 |
- Заполняем поля «Коэф_A», «Коэф_В», «Зарплата» таблицы «Сотрудники». Поле «Коэф_A» заполняем с помощью функции ВПР, введя в ячейку D3 формулу
Затем протягиваем указанную формулу до конца столбца 4. Поле «Коэф_В» также заполняем с помощью функции ВПР, введя в ячейку E3 формулу
Затем протягиваем указанную формулу до конца столбца 5. Зарплату сотрудников (столбец 6) вычисляем, введя в ячейку F3 формулу
Затем
протягиваем указанную формулу
до конца столбца 6. Готовая таблица
приведена на рисунке 23.
Рисунок
23.
- Вычисляем Фонд заработной платы, используя для поля «Зарплата» таблицы сотрудники итоговую функцию СУММ.3
=СУММ(F3:F17)
Запускаем режим «Подбор
Рисунок 24.
Рисунок
25.
В качестве изменяемой ячейки
выбрать ячейку с именем «Мин_
Рисунок 26.
- Построим сводную таблицу. Выделим таблицу «Сотрудники» и вызовем команду Данные – Сводная таблица (Рисунок 27).
Рисунок 27.
На основании сводной таблицы
определим среднюю заработную
плату сотрудников по магазинам
и общую зарплату по должностям. В диалоговом
окне «Список полей сводной таблицы» выбираем
для добавления в отчет поля «№ магазина»
и «Зарплата» (Рисунок 28).
Рисунок 28.
Далее
вызываем контекстное меню ячейки столбца
«Суммарная зарплата», выбираем команду
Итоги по – Среднее (Рисунок 29).
Рисунок 29.
Получаем отчет о средней заработной плате сотрудников по магазинам (Рисунок 30).
Рисунок 30.
Аналогичным
образом составляем отчет об общей
заработной плате сотрудников по
должностям (Рисунок 31).
Рисунок
31.
Выводы
по работе
- По результатам выполнения задания №1 приобретены навыки работы с таблицами, освоена технология создания и форматирования диаграмм.
- По результатам выполнения задания №2 изучены принципы выполнения операций сортировки данных (строк / столбцов) и поиска записей с использованием фильтров.
- По результатам выполнения задания №3 приобретены навыки применения функций просмотра и ссылок в решении экономических задач, обработки списков в режиме подбора параметров, создания сводных таблиц.