67692

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

Курсовая

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

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

Русский

2014-09-13

2.43 MB

55 чел.

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


 

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

32550. КТО СОЗДАЕТ ЭЛЕКТРОННЫЕ СРЕДСТВА ОБРАЗОВАТЕЛЬНОГО НАЗНАЧЕНИЯ. РЕКОМЕНДАЦИИ ПО ПРОЕКТИРОВАНИЮ УЧЕБНЫХ ЦЕЛЕЙ. КАКИЕ МЕТОДЫ ОБУЧЕНИЯ ИСПОЛЬЗОВАТЬ 151.5 KB
  Типовой состав разработчиков программного средства Выделяются четыре базовые категории: авторы учебного материала; компьютерные методисты; системотехники КСО; специалисты по реализации КСО. В создании конкретного КСО участвуют как правило один компьютерный методист и один системотехник КСО. Компьютерный методист это специалист владеющий компьютерной дидактикой и ориентирующийся в ПО которая рассматривается в КСО. В круг его задач входят формирование структуры КСО выбор психологопедагогической стратегии и проработка используемых...
32551. Контакторыи. Коммутация силовых цепей электродвигателей 281.61 KB
  По роду коммутируемого тока контакторы делят на контакторы постоянного и переменного тока. Как правило род тока в цепи управления которая питает электромагнитный привод совпадает с родом тока главной коммутируемой цепи. Однако известны случаи когда катушки контакторов переменного тока получают питание от цепи постоянного тока. Конструктивная схема контактора постоянного тока показана на рис.
32552. Электромагнитные муфты 341.13 KB
  24 показана схема муфты серии ЭТМ с магнитопроводящими фрикционными дисками. Другой зажим катушки подключают к источнику питания постоянного тока через корпус муфты. Электромагнитная контактная дисковая муфта При включении муфты магнитный поток Ф созданный током протекающим по виткам катушки проходит через корпус пакет внутренних 6 и наружных 4 дисков и замыкается через якорь 5.
32553. Устройства обработки информации 19.92 KB
  Рычажный контактный узел с шарнирным закреплением работающий с эффектом притирания и перекатывания контактов что способствует их лучшему самоочищению и уменьшению переходного сопротивления поэтому они часто используются в мощных коммутационных устройствах например контакторах. Жидкометаллические контакты основными достоинствами которых являются малое переходное сопротивление отсутствие необходимости в контактном нажатии отсутствие эффектов пригорания и залипания контактов возможность работы при высоком давлении температуре...
32554. Реле времени (таймеры) 13.93 KB
  По способу задержки виду замедлителя: электромагнитное замедление до 10 сек; механическое замедление: пневматические и моторные от 3 до 30 мин; электронное замедление: конденсаторные и счётноимпульсные десятки сек; программнореализуемые любые задержки времени. При работе систем защиты и автоматики часто требуется создать выдержки времени между срабатыванием двух или нескольких аппаратов а также при возникновении необходимости производить операции в определённой временной последовательности автоматическое...
32555. Электромагнитные реле времени 190.42 KB
  Реле времени с электромагнитным замедлением При отключении обмотки реле 1 рис. В результате магнитный поток в сердечнике реле убывает медленно якорь 5 остается в притянутом положении и контакты реле 4 размыкаются с выдержкой времени в несколько секунд. Такие реле времени не отличаются стабильностью но находят широкое применение благодаря простоте и дешевизне.
32556. Реле времени КТ 88.94 KB
  28 приведен пример использования реле времени КТ в схеме управления циклом движения суппорта которая обеспечивает его рабочий ход р. задержку времени на концевике SQ2 и холостой ход х. Рабочий ход суппорта обеспечивается контактором КМ1 холостой ход контактором КМ2 а выдержка времени выстоя реле временем КТ.
32557. Современные технические устройства переработки информации 15.07 KB
  Примерами таких типовых устройств могут служить: Триггеры элементарные ячейки памяти предназначенные для хранения одного бита информации логического 0 или 1; Счетчики устройства для выполнения функций счета и задержек времени; Преобразователи кодов устройство для автоматического изменения по заданному алгоритму соответствия между входными и выходными кодами без изменения их смыслового содержания другими словами это схемы для перевода одного многоразрядного кода в другой; Регистры устройства для приёма хранения и...
32558. Промышленные программируемые логические контроллеры (ПЛК) 15.9 KB
  Они впервые появились в конце шестидесятых годов в автомобильной промышленности США в результате слияния трех направлений техники: Релейноконтактная и бесконтактная электроавтоматика основа ПЛК; Цикловое программное управление принцип управления ПЛК; Микропроцессорная техника элементная база ПЛК. Первоначально производством ПЛК занимались компьютерные фирмы DEC Modicon Entrekin Computers но позже к их разработке подключились и электротехнические фирмы Generl Elektric llen Brdley ISSC которые выпускали...