рефераты конспекты курсовые дипломные лекции шпоры

Реферат Курсовая Конспект

Лабораторная работа 5. Сортировка и фильтрация данных

Лабораторная работа 5. Сортировка и фильтрация данных - Методические Указания, раздел Компьютеры, Excel   Цель Работы: Познакомиться Со Способами Сортировки И Фильтрац...

 

Цель работы: познакомиться со способами сортировки и фильтрации данных в Excel, научиться создавать сводные таблицы, изучить способы консолидации данных.

 

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

Существует ряд ограничений, накладываемых на структуру базы данных:

- первый ряд базы данных должен содержать неповторяющиеся имена полей и располагаться в одной строке;

- для имен полей следует использовать шрифт, тип данных, формат, рамку, отличные от тех, которые используются для данных в записях;

- таблицу следует отделить от других данных рабочего листа пустым столбцом и пустой строкой;

- информация по полям должна быть однородной, т.е. только цифры или только текст.

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

Задание 1. Поместить на листе Excel таблицу 5.1. Используя данные этой таблицы, создать на этом же листе новую (рабочую) таблицу с относительными величинами, разделив все параметры на соответствующий параметр Земли. Не забудьте убрать размерность величин в заголовках новой таблицы!

Для проведения дальнейших операций с полученной таблицей необходимо, чтобы в ее ячейках находились числовые значения, а не формулы, их вычисляющие[8]. Замена производится с помощью опции Специальная вставка (рис. 4.3).

В полученной таблице, используя Данные | Сортировка (рис. 5.1) или «горячие клавиши» Сортировка по возрастанию и Сортировка по убыванию на панели Стандартная:

1) отсортировать данные в порядке убывания количества спутников;

2) отсортировать данные в алфавитном порядке названий планет;

3) отсортировать данные в порядке возрастания массы.

Разместить результаты сортировок на различных листах рабочей книги.

 

Таблица 5.1 – Планеты Солнечной системы

 

Планета Период обращения, земной год Расстояние от Солнца, млн. км Диаметр, тыс. км Масса, Тт Количество спутников
Меркурий 0,241 4,9 0,32×109
Венера 0,615 12,1 4,86×109
Земля 12,8 6,0×109
Марс 1,881 6,8 6,1×108
Юпитер 11,86 142,6 19,07×1011
Сатурн 29,46 120,2 57,09×1010
Уран 84,01 49,0 87,24×109
Нептун 164,8 50,2 10,34×1010
Плутон 247,7 2,8 0,1×109

 

Примечание. Прежде чем начинать работу с базой данных, необходимо «встать в таблицу», т.е. щелкнуть мышью по какой-либо ее ячейке (но не выделять отдельные столбцы!).

Задание 2. Поиск записей, удовлетворяющих каким-либо критериям, можно проводить с помощью средства Excel Форма данных. Форма данных позволяет просматривать найденные записи по одной. Для этого необходимо выбрать команду Данные | Форма (рис. 5.2) и нажать на кнопку Критерии.

 

Рис. 5.1 - Диалоговое окно Сортировка

 

Выбрав нужное поле, введите соответствующее условие поиска, используя знаки =, >, <, >=, <=. Нажмите кнопку Далее (или Назад), чтобы просмотреть все записи, удовлетворяющие заданным условиям поиска. При введении критерия можно пользоваться символами подстановки, которые вводятся вместо букв и символов: ? – заменяет один символ, * – заменяет группу символов. Например, если в поле Планета ввести М*, то будут найдены все планеты, начинающиеся на букву М.

Рис. 5.2 - Диалоговое окно Форма

С помощью Формы данных найти планеты, имеющие не менее 14 спутников и находящиеся на расстоянии от Солнца не далее, чем на 10 земных расстояний. Соответствующие формы, в которых появятся результаты поиска, взять в буфер с помощью клавиш Alt+PrintScreen и затем разместить на листе Excel.

 

Задание 3. Более удобное средство Excel для фильтрации списков – Автофильтр. В отличие от Формы он позволяет видеть сразу все отфильтрованные записи. Вызов Автофильтра происходит при выполнении последовательности действий (Данные | Фильтр | Автофильтр). При этом в ячейках, где располагаются заголовки, появляются кнопки. При нажатии на них появляется меню с условиями отбора автофильтра.

Все – задает все строки.

 
 

Первые 10 – определяет строки с максимальным или минимальным значением ячеек текущего столбца. Эта строка открывает диалоговое окно Наложение условия по списку (рис. 5.3).

 

Условие – выбирает строки, удовлетворяющие одному или двум условиям. Эта строка меню открывает диалоговое окно Пользовательский автофильтр (рис. 5.4).

В левом поле этого окна под надписью Показать только те строки, значения которых, выбирается необходимый оператор сравнения (равно, меньше, больше и т.п.), а в правом поле задается значение сравнения. Нижние поля используются для второго условия отбора, соединяющегося с первым знаками логических операций И или ИЛИ.

 
 

С помощью Автофильтраосуществить:

1) поиск планет, имеющих диаметр менее 4-х диаметров Земли и период обращения более 80 земных лет;

2) поиск планет, находящихся от Солнца на расстоянии не менее 0,5 расстояния от Земли, имеющих массу от одной до 100 масс Земли и не более 2-х спутников;

3) поиск трех планет, имеющих самый большой диаметр.

Разместить результаты фильтрации на различных листах рабочей книги.

 

Задание 4.В отличие от Автофильтра Расширенный фильтр(рис. 5.5)позволяет проводить фильтрацию не только по месту расположения таблицы, но и выносить результат в другое место листа.

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

Первый диапазон – это исходная таблица (область базы данных). Пусть, например, она располагается в ячейках А1:F10. Эта область указывается в поле Исходный диапазон(рис. 5.5).

Второй диапазон – это область, где задаются критерии фильтрации. Его формируют из заголовков столбцов таблицы (т.е. имен полей базы данных), по которым ведется отбор, и строк, где в соответствующих ячейках записываются условия фильтрации. Эта область указывается в поле Диапазон условий.

 

 

 

Рис. 5.5 - Диалоговое окно Расширенный фильтр

 

Если все условия отбора разместить в одной строке сразу под заголовками, то этим самым реализуется их взаимодействие по схеме «И» (требуется одновременное их выполнение):

 

Заголовок 1 Заголовок 2 Заголовок 3
>=5 <110 =2

 

Если по одному полю необходимо задать два условия отбора («двойное неравенство»), то они располагаются рядом, а в следующей строке записываются ограничение снизу и ограничение сверху:

 

Заголовок 1 Заголовок 1
>=5 <=10

 

Для соединения условий отбора по схеме «ИЛИ» необходимо каждое из них разместить в отдельной строке:

 

Заголовок 1 Заголовок 2 Заголовок 3
>=5    
  <110  
    =2

 

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

 

Заголовок 1 Заголовок 2
=5 >110
=8 <=1000

 

Третий диапазон нужен, если результат фильтрации собираются расположить не на месте исходного диапазона, а в другом месте. Для этого достаточно указать в поле Поместить результат в диапазон адрес только одной ячейки. Она станет левой верхней ячейкой диапазона результатов.

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

Опция Только уникальные записи позволяет исключить из таблицы все повторяющиеся строки, оставив только уникальные.

Чтобы восстановить таблицу после работы Автофильтра или Расширенного фильтра, следует выполнить следующие действия: Данные | Фильтр | Отобразить все.

СпомощьюРасширенного фильтраосуществить:

1) поиск планет с периодом обращения от 10 до 100 земных лет и количеством спутников не более 15;

2) поиск планет, у которых либо диаметр не менее 4-х земных, либо масса более 100 земных масс;

3) поиск среди планет без спутников той, которая находится от Солнца на расстоянии менее половины земного, а среди планет с не менее чем 14-ю спутниками той, которая находится от Солнца не ближе, чем 10 земных расстояний.

Результаты поместить на одном листе в последовательности: исходная таблица, условия, результат, условия, результат и т.д.


– Конец работы –

Эта тема принадлежит разделу:

Excel

Предисловие... Методические указания...

Если Вам нужно дополнительный материал на эту тему, или Вы не нашли то, что искали, рекомендуем воспользоваться поиском по нашей базе работ: Лабораторная работа 5. Сортировка и фильтрация данных

Что будем делать с полученным материалом:

Если этот материал оказался полезным ля Вас, Вы можете сохранить его на свою страничку в социальных сетях:

Все темы данного раздела:

Методические указания
  Лабораторные работы рекомендуется выполнять в том порядке, в котором они следуют, т.е. от «простого к сложному». Первые две работы знакомят с элементарными правилами заполн

Лабораторная работа 1. Изучение основ Excel. Заполнение таблиц
  Цель работы: научиться заносить информацию (числа, текст, формулы) в ячейки листа Excel, освоить приемы построения и форматирования таблиц.   Электронная табл

Не забывайте, адреса ячеек набираются только латинским шрифтом!
Двойной щелчок левой клавишей мыши на ячейке с введенными данными осуществляет переход в режим редактирования данных. При этом указатель приобретает вид вертикальной линии. Переход

Лабораторная работа 2. Построение диаграмм и графиков функций
  Цель работы: научиться табулировать функции одного и двух переменных, строить графики и поверхности, освоить работу с функцией Excel ЕСЛИ и логическими функциями И, ИЛИ. &n

Лабораторная работа 3. Трендовый анализ
  Цель работы: освоить элементы трендового анализа временного ряда, познакомиться со статистическими функциями Excel.   Excel имеет специальный аппарат для граф

Лабораторная работа 4. Численное решение уравнений
Цель работы: освоить приемы решения нелинейных уравнений, систем линейных и нелинейных уравнений в среде Excel, познакомиться с возможностями сервисных программ Подбор параметра и Поиск решения.

Лабораторная работа 6. Сводные таблицы
  Цель работы: познакомиться со способами подведения промежуточных итогов в базах данных Excel.   Сводная таблица – это еще один инструмент Excel для обработки

Лабораторная работа 6. Сводные таблицы
  Цель работы: познакомиться со способами подведения промежуточных итогов в базах данных Excel.   Сводная таблица – это еще один инструмент Excel для обработки

Лабораторная работа 8. Создание простых макросов
  Цель работы: используя MacroRecoder, записать простой макрос и обеспечить его вызов с помощью объекта управления Кнопка.   Вы - заведующий больницей. Вам пред

Лабораторная работа 9. Статистический анализ данных
  Цель работы: освоить технологии бизнес-анализа данных в Excel, используя функции пакета Анализ данных и некоторые статистические функции   Для того чтобы прин

Лабораторная работа 10. Финансовые расчеты
  Цель работы: с помощью встроенных функций Excel научиться решать задачи финансовой математики, познакомиться с возможностями Диспетчера сценариев.   Финансовы

Лабораторная работа 11. Моделирование развития финансовой пирамиды
  Цель работы: освоить приемы решения обыкновенных дифференциальных уравнений с помощью Excel, провести их параметрические исследования и познакомиться с функциями ВПР, СМЕЩ, ПОИСКПОЗ

Лабораторная работа 12. Задачи оптимизации в экономике
  Цель работы: с помощью сервисной программы Excel Поиск решения научиться решать экономические оптимизационные задачи и проводить анализ решения типа «что-если».  

КОММЕНТАРИИ
к выполнению лабораторных работ в среде Excel с анализом наиболее часто совершаемых ошибок[22]   Лабораторная работа №1 (INFOLAB1) Не испол

Хотите получать на электронную почту самые свежие новости?
Education Insider Sample
Подпишитесь на Нашу рассылку
Наша политика приватности обеспечивает 100% безопасность и анонимность Ваших E-Mail
Реклама
Соответствующий теме материал
  • Похожее
  • Популярное
  • Облако тегов
  • Здесь
  • Временно
  • Пусто
Теги