67692

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

Курсовая

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

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

Русский

2014-09-13

2.43 MB

66 чел.

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


 

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

29029. Учёт глубины сезонного промерзания грунтов при выборе глубины заложения фундаментов зданий и сооружений 20.5 KB
  Учёт глубины сезонного промерзания грунтов при выборе глубины заложения фундаментов зданий и сооружений. Глубина заложения фундамента из условия промерзания грунтов назначается в зависимости от их вида состояния начальной влажности и уровня подземных вод в период промерзания. Как непучинистые рассматриваются также пески мелкие и пылеватые с любой влажностью а также супеси твёрдой консистенции если уровень подземных вод во время промерзания находится от спланированной отметки земли на глубине равной расчётной глубине промерзания плюс 2 м...
29030. Определение глубины заложения фундаментов с учётом конструктивных особенностей сооружения, включая глубину прокладки подземных коммуникаций, наличие и глубину заложения соседних фундаментов 31.5 KB
  Определение глубины заложения фундаментов с учётом конструктивных особенностей сооружения включая глубину прокладки подземных коммуникаций наличие и глубину заложения соседних фундаментов. Основными конструктивными особенностями возводимого сооружения влияющими на глубину заложения его фундамента являются: наличие и размеры подвальных помещений приямков или фундаментов под оборудование; глубина заложения фундаментов примыкающих сооружений; наличие и глубина прокладки подземных коммуникаций. В зданиях с подвалом или полуподвалом а также...
29031. Определение размеров подошвы центрально нагруженных фундаментов мелкого заложения 63.5 KB
  Реактивное давление грунта по подошве жёсткого центрально нагруженного фундамента принимается равномерно распределённым интенсивностью: 1 где NoII расчётная вертикальная нагрузка на уровне обреза фундамента; GfII и GgII расчётные значения веса фундамента и грунта на его уступах см.1; А площадь подошвы фундамента. Площадь подошвы фундамента при его расчёте по второму предельному состоянию по деформациям определяется из условия: pII ≤ R 2 где R расчётное сопротивление грунта основания. Поскольку обе части неравенства 2...
29032. Определение размеров подошвы внецентренно нагруженных фундаментов мелкого заложения. Эпюры давлений под подошвой фундамента. Порядок расчёта 33 KB
  Эпюры давлений под подошвой фундамента. При расчёте давление по подошве внецентренно нагруженного фундамента принимают изменяющимся по линейному закону а его краевые значения при действии момента сил относительно одной из главных осей определяют как для случая внецентренного сжатия по формуле: 1 Подстановкой значений А=l·b W=b2l 6 и M=NII·e формула 1 приводится к виду 2 2 где NII суммарная вертикальная нагрузка на основание включая вес фундамента и грунта на его уступах; A площадь подошвы фундамента; е эксцентриситет...
29033. Гидроизоляция фундаментов. Защита подвальных помещений от сырости и подтопления подземными водами 42 KB
  Гидроизоляция фундаментов. Гидроизоляция предназначается для обеспечения водонепроницаемости сооружений антифильтрационная гидроизоляция а также защиты от коррозии и разрушения материалов фундаментов и подземных конструкций от агрессивных подземных вод антикоррозионная гидроизоляция. Гидроизоляция от сырости и грунтовых вод подвальных и заглубленных помещений является значительно более сложной выбор такой гидроизоляции зависит от гидрогеологических условий строительной площадки уровня подземных вод их агрессивности особенностей...
29034. Расчёт фундаментов по второй группе предельных состояний. Определение конечной осадки фундаментов мелкого заложения методом послойного суммирования 34 KB
  Расчёт оснований фундаментов по второй группе предельных состояний по деформациям производится исходя из условия: s ≤ su 1 где s конечная стабилизированная осадка фундамента определённая расчётом; su предельное значение осадки устанавливаемое соответствующими нормативными документами или требованиями проекта. После определения размеров подошвы фундамента и проверки условия pII ≤ R где рII среднее давление на основание по подошве фундамента a R расчётное сопротивление грунта ось фундамента совмещают с литологической колонкой...
29035. Расчёт фундаментов по второй группе предельных состояний. Определение конечной осадки фундаментов мелкого заложения методом эквивалентного слоя 31.5 KB
  Расчёт фундаментов по второй группе предельных состояний по деформациям заключается в выполнении условия s ≤ sw 1 где s конечная стабилизированная осадка фундамента определённая расчётом; sw предельное значение осадки устанавливаемое соответствующими нормативными документами или требованиями проекта. Конечная стабилизированная осадка фундамента может быть определена методом эквивалентного слоя. Осадка с учётом жёсткости и формы подошвы фундамента в случае однородного основания определяется по формуле: s=p0hэmv 2 где p0 ...
29036. Определение расчётного сопротивления грунтов основания по таблицам СНиП 23 KB
  Тип песчаного грунта пески гравелистые крупные средней крупности и т. Плотность сложения песчаного грунта плотный средней плотности рыхлый. Устанавливается по таблице в зависимости от типа песчаного грунта и его коэффициента пористости: 1 где γ удельный вес грунта; γs удельный вес твердых частиц; w влажность грунта. Степень влажности песчаного грунта Sr маловлажный влажный насыщенный водой: 2 где γs удельный вес воды.
29037. Условия применения свайных фундаментов. Конструктивные решения. Виды свайных фундаментов в зависимости от расположения свай в плане 32 KB
  Условия применения свайных фундаментов. Виды свайных фундаментов в зависимости от расположения свай в плане. В этих условиях чаще всего прибегают к устройству фундаментов из свай. Группы или ряды свай объединённые поверху распределительной плитой или балкой образуют свайный фундамент.