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


 

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

34922. Бюджетный дефицит 27 KB
  Наличие дефицита может быть следствием: кризисных явлений в экономике; неспособности правительства держать под контролем финансовую ситуацию в стране; чрезвычайных обстоятельств войны крупных стихийных бедствий; милитаризации экономики в мирное время; осуществления крупных централизованных вложений в развитие производства и осуществление структурной перестройки; увеличения темпов роста социальных расходов по сравнению с темпами роста валового внутреннего продукта и т. Несбалансированность имеет краткосрочный характер если несоответствие...
34923. ВВП и другие показатели дохода 32 KB
  ВВП и другие показатели дохода Основным макроэкономическим показателем рыночной экономики безусловно является ВВП. Следует заметить что в ВВП не включается стоимость промежуточных товаров и услуг которые необходимы для осуществления самого процесса производства поскольку они уже входят в стоимость товарной продукции. ВВП это внутренний продукт поскольку в его создании участвуют только резиденты данной страны т. Соответственно в первом случае мы имеем ВВП и ВНД а вычитая из них потребление основного капитала мы получаем чистый...
34924. Виды и причины возникновения инфляции. Кривая Филипса 34 KB
  Виды инфляции: Инфляция спроса порождается избытком совокупного спроса по сравнению с реальным объемом производства. Дефицит товара Инфляция предложения издержек рост цен вызван увеличением издержек производства в условиях недоиспользованных производственных ресурсов. Сбалансированная инфляция цены различных товаров остаются неизменными друг относительно друга. Несбалансированная инфляция цены различных товаров изменяются по отношению друг к другу в различных пропорциях.
34926. Возникновение, сущность, виды и функции денег 57 KB
  О наличии денег на хранении выдавался сертификат квитанция который удостоверял что деньги находятся у банкира на хранении и предъявитель сей бумаги получит определённую сумму. Со временем эти сертификаты стали иметь такую же силу как и реальные деньги. Так появились первые бумажные деньги возникшие из практики использования банковских сертификатов квитанций. Деньги специфический товар который является универсальным эквивалентом стоимости других товаров или услуг.
34927. Государственный бюджет 26 KB
  Деятельность государства по формированию рассмотрению утверждению исполнению бюджета а также составлению и утверждению отчёта об его исполнении формулировка касается бюджетов всех уровней входящих в бюджетную систему РФ называется бюджетный проце́сс. Если запланированные расходы бюджета превышают доходы бюджета то это называется бюджетный дефицит или дефицит бюджета. Когда при исполнении бюджета уровень дефицита бюджета превышает установленный при утверждении бюджета показатель или происходит значительное снижение ожидавшихся...
34928. Государственный долг. Экономический анализ государственного долга 28 KB
  Государственный долг возникает вследствие существования дефицита бюджета в определенные периоды времени. Когда расходы государства превышают его доходы, правительство вынужденно искать источники финансирования возникшего разрыва.
34929. Группы издержек производства 36.5 KB
  Внешние(явные, бухгалтерские) – денежные платежи, которые производит фирма за приобретенные ресурсы поставщику, обеспечивающие его таким доходом, чтобы он не направлял ресурсы в альтернативное производство, образует себестоимость
34930. Денежные теории 40 KB
  Фридмен реформировал количественную теорию денег основываясь на существующих разработках трансакционном варианте и. По мнению Фридмена деньги имеют значение для динамики цен и что важно именно количество денег а не процентные ставки влияют на состояние денежного рынка или условия выдачи кредитов. В монетаристском варианте количественной теории денег важное место отводится ожидаемым изменениям уровня цен как фактора действующего на размеры кассовых денежных резервов и других финансовых активов находящихся в распоряжении...