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


 

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

21290. Діаграма станів 479.5 KB
  Діаграма станів Вступ Розглянута в попередній лекції діаграма класів є логічний модель статичного подання модельованої системи. Справа в тому що характеристика станів системи не залежить або слабко залежить від логічної структури зафіксованої в діаграмі класів. Тому при розгляді станів системи припадає на час відволіктися від особливостей її об'єктної структури і мислити зовсім іншими категоріями які утворюють динамічний контекст поведінки модельованої системи. Тому при побудові діаграм станів необхідно використовувати спеціальні...
21291. Діаграма діяльності 625.5 KB
  Діаграма діяльності Вступ При моделюванні поведінки проектованої або аналізованої системи виникає необхідність не тільки уявити процес зміни її станів але і деталізувати особливості алгоритмічної та логічної реалізації виконуваних системою операцій. Для моделювання процесу виконання операцій в мові UML використовуються так звані діаграми діяльності. Застосовувана в них графічного багато в чому схожа на нотацію діаграми станів оскільки на діаграмах діяльності також присутні позначення станів і переходів. Кожен стан на діаграмі діяльності...
21292. Діаграма послідовності 571.5 KB
  Іншими словами хоча повідомлення і має інформаційний зміст воно набуває додаткове властивість надавати направлений вплив на свого одержувача. Повідомлення зображуються у вигляді горизонтальних стрілок з ім'ям повідомлення і також утворюють порядок за часом свого виникнення. Іншими словами повідомлення розташовані на діаграмі послідовності вище ініціюються раніше тих що розташовані нижче. Графічне зображення актора рекурсії та рефлексивного повідомлення на діаграмі послідовності 2.
21293. Методологія обєктно-орієнтованого аналізу і проектування ПЗ. Мова UML 72.5 KB
  Мова UML Зіставлення і взаємозв'язок структурного та об'єктноорієнтованого підходів Граді Буч сформулював головне достоїнство об'єктноорієнтованого підходу ООП наступним чином: об'єктноорієнтовані системи більш відкриті і легше піддаються внесенню змін оскільки їх конструкція базується на стійких формах. Буч відзначив також ряд наступних переваг ООП: об'єктна декомпозиція дає можливість створювати програмні системи меншого розміру шляхом використання загальних механізмів що забезпечують необхідну економію виразних засобів. Системи...
21294. Структурний підхід до проектування інформаційних систем 477 KB
  Основними з цих принципів є наступні: принцип абстрагування полягає у виділенні істотних аспектів системи і відволікання від несуттєвих; принцип формалізації полягає в необхідності суворого методичного підходу до вирішення проблеми; принцип несуперечності полягає в обгрунтованості та узгодженості елементів; принцип структурування даних полягає в тому що дані повинні бути структуровані і ієрархічно організовані. Кожній групі засобів відповідають певні види моделей діаграм найбільш поширеними серед яких є наступні: SADT...
21295. Мета та завдання дисципліни 88.5 KB
  CASEтехнологія являє собою методологію проектування ІС а також набір інструментальних засобів що дозволяють в наочній формі моделювати предметну область аналізувати цю модель на всіх етапах розробки і супроводу ІС і розробляти програми відповідно до інформаційними потребами користувачів. Поняття моделі та моделювання Модель це об'єкт або опис об'єкта системи для заміщення однієї системи оригіналу іншою системою для кращого вивчення оригіналу або відтворення будьяких його властивостей. Слово модель лат. При моделюванні...
21296. Діаграма варіантів використання (use case diagram) 504 KB
  Діаграма варіантів використання use case diagram Вступ Візуальне моделювання в UML можна уявити як певний процес поуровневого спуску від найбільш обший і абстрактної концептуальної моделі вихідної системи до логічної а потім і до фізичної моделі відповідної програмної системи. Для досягнення цих цілей спочатку будується модель у формі так званої діаграми варіантів використання use case diagram яка описує функціональне призначення системи або іншими словами те що система буде робити в процесі свого функціонування. Діаграма...
21297. Життєвий цикл програмного забезпечення 1.58 MB
  Життєвий цикл програмного забезпечення Одним з базових понять методології проектування ІВ є поняття життєвого циклу її програмного забезпечення ЖЦ ПЗ. Структура ЖЦ ПЗ за стандартом ISO IEC базується на трьох групах процесів: основні процеси ЖЦ ПЗ придбання поставка розробка експлуатація супровід; допоміжні процеси які забезпечують виконання основних процесів документування управління конфігурацією атестація оцінка аудит рішення проблем; організаційні процеси управління проектами створення інфраструктури проекту...
21298. Моделювання за допомогою методу Баркера 243 KB
  З їх допомогою визначаються важливі для предметної області об'єкти сутності їх властивості атрибути і відношення один з одним зв'язки. Графічне зображення сутності Кожна сутність повинна мати унікальний ідентифікатор. Кожен екземпляр сутності повинен однозначно ідентифікуватися і відрізнятися від всіх інших примірників даного типу сутності. Одна і та ж інтерпретація не може застосовуватися до різних імен якщо тільки вони не є псевдонімами; володіє одним або декількома атрибутами які або належать сутності або успадковуються через...