67692

Управление списками в MS Excel: создание списка «Автомобили», сортировка, отбор, анализ данных

Курсовая

Информатика, кибернетика и программирование

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

Русский

2014-09-13

2.43 MB

50 чел.

7

Министерство образования и науки Российской Федерации

Федеральное государственное бюджетное

образовательное учреждение

высшего профессионального образования

«Воронежская государственная лесотехническая академия»

Кафедра вычислительной техники и информационных систем

Курсовая работа

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

на тему: «Управление списками в MS Excel:

создание списка «Автомобили»,

сортировка, отбор, анализ данных»

Выполнил: студент гр.ИС2-121-ОБ

Ковалев А.Н.

Проверил: к.т.н.,доцент

Скворцова Т.В.

Воронеж

2012

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ ..................................................................................................................................3

РАЗДЕЛ 1 АНАЛИЗ ПОСТАНОВКИ ЗАДАЧ.......................................................7

1.1 Ввод данных в список...................................................................................................7

РАЗДЕЛ 2 ОТБОР ДАННЫХ.........................................................................................8

2.1 Поиск с помощью автофильтра………………………………………….…….…..8

2.1.1 Поиск с помощью расширенного автофильтра……………………….……….9

РАЗДЕЛ 3 АНАЛИЗ ДАННЫХ.....................................................................................11

3.1 Автоматическое подведение итогов……………………………………………...…..11

3.2 Консолидация данных………………………………………………………...…...…..15

3.3 сводная таблица…………………………………………………………………….…..16

СПИСОК ЛИТЕРАТУРЫ……………………………………………………………………….19

ВВЕДЕНИЕ

Работа со списками в MS Excel

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

  1.  сортировка – выстраивание данных в нужном порядке;
  2.  отбор данных – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями);
  3.  анализ данных – обработка различными средствами информации, находящейся в списке или в отфильтрованных данных.

Сортировка данных

Команда Данные | Сортировка открывает окно, в котором задаются ключи сортировки (столбцы или строки) и порядок сортировки (рис. 1).

       Рис. 1. Окно Сортировка

Для сортировки данных в каком-либо заданном порядке (по дням недели, месяцам и т.д.) вызовите команду Данные | Сортировка и кнопкой Параметры вызовите диалоговое окно Параметры сортировки (рис. 2).

Рис. 2. Окно Параметры сортировки

Отбор данных

Для поиска и фильтрации данных в MS Excel существуют 3 средства:

  1.  форма данных;
  2.  автофильтр;
  3.  расширенный фильтр.

Поиск с помощью формы данных

Форма данных представляет собой средство для поиска и редактирования записей, которые удовлетворяют простому или множественному критерию сравнения.

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

Поиск с помощью формы данных производится следующим образом:

  1.  Поместите указатель ячейки в любое место внутри списка.
  2.  Выберите команду Данные | Форма, затем нажмите кнопку Критерии (рис. 3).
  3.  В открывшемся окне введите критерии поиска в необходимых полях. Для перехода к записи, удовлетворяющей критерию, следует нажать кнопку Далее или Назад.

Рис. 3. Поиск с помощью формы данных

Поиск с помощью автофильтра

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

  1.  установку необходимых значений полей для поиска точного соответствия (рис. 4);
  2.  применение пользовательского автофильтра Условие – позволяет осуществить поиск близкого соответствия на основе критериев И/ИЛИ (рис. 5);

использование команды Первые 10 (рис. 6) для отбора некоторого количества наибольших или наименьших элементов списка

Рис. 4. Поиск с помощью автофильтра по точному соответствию

Рис. 5. Поиск с помощью пользовательского автофильтра

Рис. 6. Поиск с помощью команды Первые 10

Поиск с помощью автофильтра производится в следующем порядке:

  1.  Установите указатель ячейки в список данных.
  2.  Выполните команду Данные | Фильтр | Автофильтр. Возле каждого поля строки заголовка появятся раскрывающиеся списки в виде кнопки с треугольником.
  3.  Перейдите к необходимому полю.
  4.  Выберите необходимый критерий поиска или воспользуйтесь пользовательским автофильтром Условие.
  5.  Для включения в критерий другого поля возвратитесь к пункту 1.

Поиск с помощью расширенного фильтра

Расширенный фильтр позволяет одновременно или по отдельности применять операции И,ИЛИ и составлять вычисляемые критерии.

Анализ данных

MS Excel предоставляет широкие возможности для проведения анализа данных, находящихся в списке. К средствам анализа относятся:

  1.  обработка списка с помощью различных формул и функций;
  2.  построение диаграмм и использование карт MS Excel;
  3.  проверка данных рабочих листов и рабочих книг на наличие ошибок;
  4.  структуризация рабочих листов;
  5.  автоматическое подведение итогов;
  6.  консолидация данных;
  7.  сводные таблицы;
  8.  специальные средства анализа выборочных записей и данных – подбор параметра, поиск решения, сценарии, пакет анализа и др.

Автоматическое подведение итогов

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

  1.  Подготовить список данных и оставить в нем указатель ячейки. Определиться с тем, какие нужны итоги (простые, сложные или с использованием мастера частичных сумм).
  2.  Провести сортировку по необходимому полю или полям командой Данные | Сортировка.
  3.  Подвести итоги командой Данные | Итоги.

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

На рабочих листах  подготавливаю данные в соответствии со следующей структурой строки заголовка:

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

  1.  Открываю список, подлежащий фильтрации (R1C1: R14C9, строка заголовка – в диапазоне R1C1: R1C9).
  2.  Формирую диапазон критериев для расширенного фильтра в соответствии с рис. 7.(Нужно определить белые TOYOTA, год выпуска которых больше 2005, но меньше либо равен 2011. Отсортировать полученные данные по убыванию года выпуска автомобилей). Для этого в диапазоне R15C10: R16C13 ввожу необходимые условия:

Рис. 8. Диапазон критериев для расширенного фильтра

  1.  Выполняю команду Данные | Фильтр | Расширенный фильтр. И заполняю диалоговое окно Расширенный фильтр как показано на рис. 9

Рис. 9. Окно Расширенный фильтр

  1.  Сортируем полученные данные по убыванию года выпуска автомобилей. Полученные данные показаны на Рис. 11

Рис. 10. Сортировка полученных данных

Рис. 11. Полученные данные

  1.  Формирую диапазон критериев для расширенного фильтра в соответствии с рис. 7.(Нужно определить черные BMW, пробег  которых меньше либо равен 50000км, а цена-больше либо равна 50000 у.е. Отсортировать полученные данные по возрастанию цены).  Для этого в диапазоне R15C10: R16C13 ввожу необходимые условия:

Рис. 12. Диапазон критериев для расширенного фильтра

  1.  Сортирую данные по возрастанию цены. Итоговый результат на Рис. 13

Рис. 13. Итоговый результат

Используя расширенный автофильтр, мне нужно вывести все записи удовлетворяющие заданным критериям: 1)Определить синие или зеленые BMW или ПЕЖО, в цифрах номера которых содержится «2» или «4», а так же – год выпуска которых совпадает с годом приобретения. 2)Определить белые или черные автомобили, год выпуска которых больше 2010 года, пробег которых меньше либо равен максимальному значению пробегов для  всех ПЕЖО.

1)Чтобы выполнить здание 1  я создал две ячейки R1C10 и R2C10.R1C10 я озаглавил как условие, а в ячейку R2C10  я вписал  формулу для вывода заданных критерий через расширенный фильтр. Формула выглядит так: =И(ИЛИ(RC[-9]="BMW";RC[-9]="ПЕЖО");ИЛИ(RC[-4]="синий";RC[-4]="зеленый");ИЛИ(RC[-6]=RC[-5]);ИЛИ(ПРАВСИМВ(RC[-8];1)="2";ПРАВСИМВ(RC[-8];1)="4")). Затем выполнил команду Данные | Фильтр | Расширенный фильтр. Действие продемонстрированно  на Рис.14.

Полученный результат показан на Рис.15.

Рис. 14.Выполнение команды расширенный фильтр

Рис. 15 Данные к задаче про синие или зеленые BMW или ПЕЖО

2)  Для того, чтобы выполнить задание 2 рассчитаем сначало  максимальное значение пробегов для всех ПЕЖО, потому что пробег всех машин будет сравниваться с максимальным пробегом машины марки ПЕЖО. Для этого заполним две ячейки R1C11 и R2C11. В ячейку R1C11 вписываем название: макс.пробегов для всех ПЕЖО, а в ячейку R2C11 следовательно формулу для вычисления максимального значение пробегов. Предварительно используя автофильтр выведем все машины только марки ПЕЖО. Рис. 16

.

Рис. 16 Фильтрация ПЕЖО

Формула из ячейки R2C11 выглядит так: =МАКС(RC[-3]:R[5]C[-3]). Затем в автофильтре выбираем все автомашины, чтоб был показан полный список машин. А в ячейку R2C11 вводим формулу для определения белых или черных автомашин, год выпуска которых больше 2010г, пробег которых меньше либо равен максимальному значению пробегов для всех ПЕЖО. Формула выглядит так: =И(ИЛИ(RC[-5]="белый";RC[-5]="черный");ИЛИ(RC[-7]>=2010;RC[-4]<=R7C10)). Далее выполняем команду Данные | Фильтр | Расширенный фильтр и вводим наши данные. Полученный  результат показан на Рис. 17

Рис. 17 Данные к задаче про белые или черные автомобили

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

  1.  Средняя, максимальная и минимальная цены определенной марки машин с учетом конкретного года приобретения.
  2.  Суммарный и средний пробег определенной марки машин с учетом конкретного года выпуска.

Чтобы выполнить 1 задание, нужно  провести сортировку по необходимому полю или полям командой Данные | Сортировка. В данном случай мне нужно провести сортировку по полю марка автомашин и по полю год приобретения. Для этого провожу сортировку сначала - по полю марка автомашин, затем –по  году приобретения как показано на Рис. 18

Рис. 18 Сортировка списка

Следующим пунктом Применяем команду Данные | Итоги. В окне Промежуточные итоги устанавливаем  параметры для получения итогов по полю Марка автомашин

в соответствии с Рис.19  И в окне Промежуточные итоги устанавливаем параметры для получения итогов по полю Год приобретения в соответствии с Рис.20

Рис. 19 Окно Промежуточные итоги для получения итогов по полю Марка автомашин

Рис. 20. Окно Промежуточные итоги для получения итогов по полю Дата продажи

Чтобы определить по средней, максимальной и минимальной цене определенной марки при выполнении промежуточных итогов по полю Марка машины в  поле Операция поэтапно  устанавливаем  параметр: среднее, максимум и минимум ка показано на Рис.21 Итоговый результат показан на Рис.22.

Рис.21 Определение по средней, максимальной и минимальной цене

Рис.22  Итоговый результат

Чтобы выполнить 2 задание, нужно  провести те же действия, что и в 1 задании, только вместо года приобретения будет год выпуска. А для того, чтобы определить суммарный и средний пробег настраиваем промежуточные итоги, как показано на Рис. 23. Конечный результат на Рис.24

Рис.23 Промежуточные итоги для выполнения задания 2

Рис.24 Конечный результат

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

Для того, чтобы выполнить консолидацию оставим данные, которые нам необходимо вывести и  разделим наш список на три части как показано на Рис.25

Рис.25  Таблицы для консолидации

  1.  Затем выбираем команду Данные | Консолидация. Заполняем окно Консолидация в соответствии с данными рис. 26. Объединенные данные представлены на рис. 27.

Рис.26 Консолидация

Рис.27 Объединенные данные

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

  1.  Фамилии объединить по первой буквы алфавита(А,Б,В и т.д.);
  2.  Год выпуска объединиться с интервалов м 5 лет.

Перед выполнением задания добавим дополнительное поле в ячейку R1C10 , в котором цена из у.е будет переведена на российские рубли. Курс доллара составляет 30,7696. Вводим формулу для перевода: =RC[-2]*30,7696. Затем выбираем команду создать сводную таблицу и появившемся окне выбираем данные для анализа как показано на Рис. 28

Рис.28 данные для сводной таблицы

В появившемся списке полей сводной таблицы выбираем необходимые данные: марка машины, год выпуска, пробег км, цена у.е. и цена в руб. Рис.29

Рис.29 Список полей сводной таблицы

Чтобы цена была не суммарной, а средней жмем  в списке полей сводной таблицы на сумму по полю цена у.е и выбираем параметры полей значений. В появившемся окне в разделе операции выбираем  среднее и следом нажимаем на числовой формат, чтобы  формат ячеек задать денежный и жмем ОК. Повторяем такую операцию и для цены в руб.

Рис.30 Параметры полей значений

В сводной таблице щелкаем правой кнопкой мыши на год выпуска и жмем группировать. В появившемся окне настраиваем как на Рис. 31. Отформатированные данные представлены на Рис.32

Рис.31 Группировка  года выпуска интервалом в 5 лет

Рис.32 Отформатированные данные

Список литературы

  1.  Конспект лекции

  1.  Информационные технологии: Учеб. для вузов / Б. Я. Советов, В. В. Цехановский. — М.: Высш. шк. , 2003.

  1.  http://office.microsoft.com/ru-ru/excel-help/HA010370218.aspx -Приступая к работе с Excel 2010


 

А также другие работы, которые могут Вас заинтересовать

64931. Источниковедение Кыргызстана (с древности до конца XIX в.) 132.5 KB
  После установления военно-политического господства монголов в Центральной Азии империя Чингизхана была разделена между его сыновьями. Главная орда Угедея третьего сына Чингизхана находилась в Тарбагатае на берегах Эмиля и Кабука. Следует отметить что в Средней Азии дольше чем в других завоеванных монголами странах сохранилась система государственного...
64932. ХАНСКАЯ ВЛАСТЬ И УЛУСНАЯ СИСТЕМА УПРАВЛЕНИЯ В ГОСУДАРСТВЕ ЧАГАТАИДОВ 99 KB
  После установления военно-политического господства монголов в Центральной Азии империя Чингизхана была разделена между его сыновьями. Главная орда Угедеятретьего сына Чингизхана – находилась в Тарбагатае на берегах Эмиля и Кабука. Следует отметить что в Средней Азии дольше чем в других завоеванных монголами странах сохранилась система государственного устройства...
64933. «МОНГОЛЫ» И ЧИНГИСХАН 70.5 KB
  Предлагаемая статья является не столько результатом сколько проектом исследования направленного на изучение начального этапа этно культуро и политогенеза общности сформированной Чингисханом и известной в истории под именем монголы.
64934. Как улус Джучи стал Золотой Ордой 38 KB
  ТМ уже публиковал заметки о том когда и как родилось одно из названий этой державы Золотая Орда см. Согласно Рашид ад Дину автору Сборника летописей Джами аттаварих главному источнику по истории Монгольской империи орда ставка Джучи была в пределах Иртыша.
64936. Тайна могилы Чингисхана 57 KB
  Речь идет о Могиле Чингисхана о тайне его захоронения. Листая древние манускрипты Жизнь Чингисхана послужила основой для двух монгольских хроник из которых до нас дошли две редакции видимо составленные вскоре после смерти великого хана писал исследователь...
64937. Тактика и стратегия монгольской армии в правление Чингиcхана 84 KB
  Передовой отряд силою в две-три сотни предшествовавший армии на расстоянии двух переходов и такие же боковые отряды исполняли задачи не только охранения марша и разведки противника но также и хозяйственной разведки они давали знать где подножный корм и водопой лучше.
64938. К вопросу о численности и этническом составе монгольской армии в 1-й четверти XIII века 57.5 KB
  Видимо они руководили соединениями численностью менее 10 тысяч воинов. После этого согласно Тайной истории монголов на реке Халха Тэмуджин произвел пересчет и реорганизацию армии сформировал личную тысячу тургаутов назначил новых офицеров в том числе 6 черби...