Обработка и анализ данных в среде EXCEL

Министерство  образования и науки, молодежи и  спорта Украины

Севастопольский национальный технический университет

Факультет Экономики и менеджмента

Кафедра Учета и аудита 
 
 
 
 
 
 
 

КОНТРОЛЬНАЯ РАБОТА 
 

На тему: «Обработка и анализ данных в среде EXCEL» 

по дисциплине «Информатика» 
 
 
 
 
 

                                              Выполнил: студент  группы АУ-32уск 

                                              Гандзюк Ю.Ю._________________

                                              "_____"__________________2011 г.  

                                              Проверил: ст. преподаватель 

                                                         

                                              Лебедева Л.А._________________

                                    "_____"__________________2011г. 

                           
 
 
 
 
 
 
 
 

Севастополь

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. Вычислить значения функции на  заданном интервале с заданным  шагом   изменения аргумента.  Результаты оформить в виде  таблицы. Первый столбец таблицы  должен содержать значения аргумента  x,  второй столбец – значения  функции y. Для ввода значений x использовать метод автозаполнения, для вычисления значений y – встроенную  логическую функцию ЕСЛИ.

    3. Построить график функции. Сформировать  на графике:  заголовок,  названия  осей,  метки легенды.

    3.1. На графике разными маркерами  выделить максимальное и минимальное  значения функции.

    3.2. Метки на оси X представить  в формате с фиксированной  точкой (числовой формат), а на  оси Y – с плавающей точкой (экспоненциальный формат).

Решение

 
  1. Выбор варианта.

    I= (N mod 25) +1,

    где   N=78 - последние две цифры зачетной книжки,

      I=4 - остаток от деления 78 на 25.

    I=3+1=4

    Выбираем 4 вариант.

№ Функция Интервал изменения  аргумента Шаг изменения  аргумента
4    y =  
 
0.2
 
  1. Оформление  вычислений в таблицу.

     2.1. Вводим первое значение аргумента х=0 в ячейку A2.

     2.2. Вводим второе значение аргумента х=0,2 в ячейку A3.

     2.3. Выделяем ячейки как показано  на рис.1. Подводим указатель к  маркеру заполнения. Указатель превратится  в черный крестик. Нажимаем  кнопку мыши и перетаскиваем  маркер до появления значения  аргумента х=3.

     2.4. В ячейку B2 вводим формулу

                                                   =ЕСЛИ(A2<=1;КОРЕНЬ(SIN(A2));A2+2)

     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. Создать таблицу для заданной  предметной области в соответствии  с вариантом. Таблица должна  содержать 5-6 полей (2-3 числовых  поля, остальные - символьные, даты  и др.), 10-15 записей. Использовать  построенную таблицу при выполнении  последующих заданий.

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

     4. Выполнить выборку данных из  таблицы с использованием фильтров.

     4.1. Автофильтр. Использовать в качестве  условий: 1) точную форму (константу)  выбора; 2) текст выбора (шаблон); 3) формулу  выбора (условие).

     Задать  критерий типа "И" для поиска данных по значениям двух полей. Задать критерий типа "ИЛИ" для поиска данных в  отдельном столбце.

     4.2. Расширенный фильтр. Создать на  рабочем листе таблицу критериев.  Для числового поля задать  простое условие, сложное условие  для попадания значений числового  поля в диапазон (критерий И). Для  значений символьного поля реализовать  условие ИЛИ.

Решение

 
 
    1.  Выбираем  вариант:

      i=N mod 15=9, 

     где N=78 – две последние цифры зачетной книжки. Выбираем вариант №8: 
     

Номер варианта Предметная  область
8 авиакасса
 
    1. Создаем таблицу  для заданной предметной области (Таблица 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
 
 
       
  1. Для выполнения задания созданную таблицу копируем четыре раза.

       На  первой таблице для сортировки всех данных таблицы в алфавитном порядке (по колонке «Пункт назначения») жмем на выпадающий список в ячейке C3 (Рисунок 7). Выбираем пункт «по возрастанию», после чего программа автоматически сортирует все строки таблицы по названию пункта назначения в алфавитном порядке (Риунок 8). 

Рисунок 7. 

 

Рисунок 8.

 

     Во  второй скопированной таблице сортируем  все данные по дате отправления. Для этого жмем на выпадающий список в ячейке A18. Выбираем пункт «по возрастанию» (Рисунок 10).

     

Рисунок 10.

     В третьей скопированной таблице  выполняем сортировку по значениям  двух полей. Аналогично предыдущему  случаю, сортируем данные  таблицы  по значению столбца «дата отправления», выбрав в меню сортировки «по возрастанию». В этой же таблице включаем сортировку по полю «цена», применив числовой фильтр «от минимума к максимуму» (Рисунок 11).

     

Рисунок 11.

     Результат сортировки по двум столбцам представлен  на рисунке 12. 

     

 

Рисунок 12.

     В четвертой таблице производим сортировку столбцов по значениям заголовков. Для этого выделяем всю таблицу, и выбираем команду Данные – Сортировка. В диалоговом окне «Параметры» указываем  сортировать столбцы диапазона. Далее в окне «Сортировка» указываем  сортировать по «Строка 1» (значения заголовков, как указано в задании). Сортировку производим по значениям, порядок  сортировки «по возрастанию» (Рисунок 13). 

 

Рисунок 13. 

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

 

     

Рисунок 14.

    1. Производим выборку данных из таблицы с использованием фильтров.
 

     4.1.1. Отобразим в таблице данные  только по количеству требующихся билетов, используя автофильтр. Используем в качестве условий точную форму (константу) выбора. Задаем критерий типа "ИЛИ" для поиска данных в столбце «количество» (Рисунок 15). 

     

Рисунок 15.

     Результат представлен на Рисунке 16. 

     

Рисунок 16

      1. Отобразим в таблице все рейсы, с пунктом отправления Киев, используя текст выбора (шаблон).
      2. Отобразим в таблице все рейсы, цена билетов на которые находится в пределах от 84$ до 200$, используя формулу выбора (условие). Задаем критерий типа "И" для поиска данных в столбце «цена» (Рисунок 18) Результат представим на рисунке 19.
 

     

Рисунок 18.

     

 

Рисунок 19. 

     4.2. Расширенный фильтр. Создаём на  рабочем листе таблицу критериев.  Задаем сложное условие для  попадания значений текстового поля «класс поездки» в диапазон  (критерий И). Для значений числового поля «количество» реализуем условие ИЛИ (Рисунок 20). Результат представлен на Рисунке 21. 

Рисунок 20.

 

Рисунок 21.

Задание №3

Цель  работы

 

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

Постановка  задачи

 
 

    1. Выбрать  вариант.

       2. Перенести на рабочий лист  таблицу 3.1. Добавить в таблицу  пустые столбцы с заголовками  «Коэф_A», «Коэф_В», «Зарплата». Присвоить  таблице имя «Сотрудники»

       3. Создать на этом же рабочем  листе таблицу, содержащую поля: «Должность», «Коэф_А», «Коэф_В». Заполнить таблицу в соответствии с вариантом задания (таблица 3.2). В поле «Должность» ввести значения: Кассир, Продавец, Ст. Продавец. Присвоить таблице имя «Коэффициенты».

       4. В свободную ячейку рабочего  листа занести значение минимальной  заработной платы в соответствии  с вариантом (таблица 3.2). Присвоить  ячейке имя «Мин_зарплата».

       5. Заполнить поля «Коэф_A», «Коэф_В», «Зарплата» таблицы «Сотрудники»: поля «Коэф_A» и «Коэф_В» заполнить  на основании таблицы «Коэффициенты»  с помощью функции ВПР, используя  в качестве искомого значения  должность сотрудника. Зарплата  сотрудника считается по формуле  Зарплата=Коэф_А*Мин_зарплата+Коэф_В.

       6. Вычислить Фонд заработной платы,  используя для поля «Зарплата»  таблицы сотрудники итоговую  функцию СУММ.  Используя режим  «Подбор параметра» определить  минимальную заработную плату  для заданного вариантом фонда  заработной платы. В качестве  изменяемой ячейки выбрать ячейку  с именем «Мин_зарплата».

       7. Построить сводную таблицу. На  основании сводной таблицы определить  среднюю заработную плату сотрудников  по магазинам, общую заработную  плату сотрудников по должностям.

Решение

 
     
  1. Выбираем  вариант:

     i=N mod 15=9, 

 где N=78 – две последние цифры зачетной книжки. Выбираем вариант №3.

  1. Переносим на рабочий лист таблицу 3.1. Добавляем в таблицу пустые столбцы с заголовками «Коэф_A», «Коэф_В», «Зарплата». Присваиваем таблице имя «Сотрудники».
  2. Создаем на этом же рабочем листе таблицу, содержащую поля: «Должность», «Коэф_А», «Коэф_В». Заполняем таблицу в соответствии с вариантом №3 (таблица 3.2 методических указаний). В поле «Должность» вводим значения: Кассир, Продавец, Ст. продавец. Присваиваем таблице имя «Коэффициенты».
Должность Коэф_A Коэф_В
Кассир 1,75 70
Продавец 2,1 85
Ст. продавец 2,8 90
 
       
  1. В свободную  ячейку рабочего листа заносим значение минимальной заработной платы в соответствии с вариантом  №3 (таблица 3.2 методических указаний). Присваиваем ячейке имя «Мин_зарплата».
 
Мин. зарплата
420
 
       
  1.  Заполняем  поля «Коэф_A», «Коэф_В», «Зарплата»  таблицы «Сотрудники». Поле «Коэф_A»  заполняем с помощью функции ВПР, введя в ячейку D3 формулу

                                             =ВПР(C3;коэффициент;2;ЛОЖЬ)

     Затем протягиваем указанную формулу  до конца столбца 4. Поле «Коэф_В» также  заполняем с помощью функции ВПР, введя в ячейку E3 формулу

                                             =ВПР(C3;коэффициент;3;ЛОЖЬ)

     Затем протягиваем указанную формулу  до конца столбца 5. Зарплату сотрудников (столбец 6) вычисляем, введя в ячейку F3 формулу

                                             =D3*$E$23+E3

     Затем протягиваем указанную формулу  до конца столбца 6. Готовая таблица  приведена на рисунке 23. 

     

Рисунок 23. 

       
  1. Вычисляем Фонд заработной платы, используя для  поля «Зарплата» таблицы сотрудники итоговую функцию СУММ.3
 

    =СУММ(F3:F17)

         Запускаем режим «Подбор параметра» (Рисунок 24), определяем  минимальную  заработную плату для заданного  вариантом фонда заработной платы (13 000).

Рисунок 24.

 

Рисунок 25. 

       В качестве изменяемой ячейки  выбрать ячейку с именем «Мин_зарплата».  Искомое значение Минимальной  зарплаты равно 357,598784  (Рисунок 26). 

Рисунок 26.

  1. Построим сводную таблицу. Выделим таблицу «Сотрудники» и вызовем команду Данные – Сводная таблица (Рисунок 27).
 

 

Рисунок 27.

         На основании сводной таблицы  определим среднюю заработную  плату сотрудников по магазинам и общую зарплату по должностям. В диалоговом окне «Список полей сводной таблицы» выбираем для добавления в отчет поля «№ магазина» и «Зарплата» (Рисунок 28). 

Рисунок 28.

     Далее вызываем контекстное меню ячейки столбца  «Суммарная зарплата», выбираем команду  Итоги по – Среднее (Рисунок 29). 

       

Рисунок 29.

     Получаем  отчет о средней заработной плате  сотрудников по магазинам (Рисунок 30).

Рисунок 30.

     Аналогичным образом составляем отчет об общей  заработной плате сотрудников по должностям (Рисунок 31). 

Рисунок 31. 
 
 
 
 

Выводы  по работе 
 

  1. По результатам  выполнения задания №1 приобретены  навыки работы с таблицами, освоена  технология создания и форматирования диаграмм.
  2. По результатам выполнения задания №2 изучены  принципы  выполнения  операций  сортировки    данных   (строк / столбцов)  и    поиска  записей  с использованием  фильтров.
  3. По результатам выполнения задания №3 приобретены навыки применения функций просмотра и ссылок в решении экономических задач, обработки списков в режиме подбора параметров, создания  сводных таблиц.
Обработка и анализ данных в среде EXCEL