Фильтрация данных в списке

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ

ФАОУ ВО «Севастопольский государственный университет»

Кафедра информационных технологий и компьютерных систем

 

Методические указания

К выполнению лабораторной работы

«Обработка данных в Excel. Сортировка, фильтры, итоги»

для студентов гуманитарных специальностей очной формы обучения

 

Севастополь

2017

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

При создании списка необходимо выполнять следующие правила:

1. На одном листе не следует помещать более одного списка;

2. Следует отделять список от других данных на листе, хотя бы одной пустой строкой и пустым столбцом;

3. Для имен столбцов следует использовать шрифт, тип данных, выравнивание, формат, рамку отличные от тех, которые используются для данных списка.

4. Чтобы отделить имена столбцов от данных следует разместить рамку по нижнему краю ячеек первой строки.

 

 

Практическое задание 1.

 На рабочем листе Excel создать список следующего вида:

 

Текущая Дата	Заемщик	Кол-во обращений	Дата возврата	Сумма долга	Пеня	Итого к возврату
…	…		…	…	…	…

 

Список заполнить заполнить 15 записями. Правила заполнения полей следующие:

«Текущая дата» - дата выполнения работы в формате ДД.ММ.ГГ;

«Заемщик» - произвольная информация;

«Количество обращений» - произвольная информация;

«Дата возврата» - произвольная дата в формате ДД.ММ.ГГ;

«Сумма долга» - произвольная информация;

«Пеня» - вычислить по формуле

0, если «Текущая дата» < «Дата возврата»,

Пеня =   

(«Текущая дата» - «Дата возврата») * «Сумма долга»*2%, если «Текущая дата» > «Дата возврата»

«Итого к возврату» - вычислить по формуле «Сумма долга»+ «Пеня».

Проверить работу списка для трех различных текущих дат, разместив каждую из таблиц на отдельном листе.

Сортировка данных

 

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

Чтобы отсортировать весь список необходимо выделить одну ячейку в списке и выбрать в меню Данные – Сортировка.

Для быстрой сортировки на панели инструментов «Стандартная» находятся кнопки

 

Ключевым полем для сортировки в этом случае является столбец с выделенной ячейкой.

 

Практическое задание 2.

Отсортировать полученные списки:

- первый в алфавитном порядке имен Заемщиков;

- второй в порядке убывания сумм возврата;

- третий в хронологическом порядке дат возврата.

 

Фильтрация данных в списке

 

С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Для этих целей в меню Данные – Фильтр есть две команды Автофильтр и Расширенный фильтр.

Автофильтр. По этой команде Excel помещает раскрывающиеся списки непосредственно в заголовки столбцов (там появляется кнопка  ). Щелкнув по этой кнопке можно получить список всех уникальных элементов столбца. Если в этом списке выделить некоторое значение, то из исходного списка будут удалены все строки, кроме тех, которые содержат выделенное значение.

 

  Практическое задание 3.

В первом списке с помощью автофильтра оставьте строки, содержащие информацию по одному из заемщиков.

 

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

Чтобы удалить ранее введенный критерий, надо выбрать критерий ВСЕ.

Чтобы быстро восстановить исходный список надо выполнить команду Данные – Фильтр – Показать все.

С помощью автофильтра можно устанавливать и более сложные критерии (пользовательские). Для этого выбираем критерий «Условие», в результате чего появится диалоговое окно, примерный вид которого представлен на рисунке ниже.

 

 

 

 

Левые окна задают операцию отношения (<,>,=, … для чисел, «содержит», «не содержит»… для текстов). Можно использовать символы «?» и «*» вместо любого символа или группы символов соответственно.

 

Практическое задание 4.

В одной из таблиц отобрать записи содержащие информацию:

- обо всех заемщиках, дата возврата долга которых истекает менее, чем через неделю;

- все записи по должникам с суммой долга более 100гр.;

- все записи по должникам с сумой долга более 200гр. или с суммой к возврату более 200гр.

 

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

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

Обычно для этого копируют имена полей списка на свободное пространство. Количество строк в таблице определяется только количеством критериев поиска. Например, для реализации критерия из задания 4.2 необходимо было бы создать таблицу из двух строк:

Сумма долга

>100

Если критерий составной, то применяется следующее правило: столбцы в таблице критериев соединяются союзом «И», а строки – союзом «ИЛИ». Например, если Вас интересуют должники с суммой долга от 100 до 200гр или с сумой к возврату более 200гр. то таблица критериев будет выглядеть так:

 

Сумма долга	Сумма долга	Итого к возврату
>100	<200
		>200

 

 

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

 

  Практическое задание 5.

Выполнить на своих таблицах примеры, приведенные в задании 4, используя расширенный фильтр.

 

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

Для записи такого критерия нужна формула. К формулам в таблице критериев расширенного фильтра предъявляются следующие требования:

1) формула должна выводить логическое значение ЛОЖЬ или ИСТИНА. После выполнения поиска на экран выводятся только те строки, для которых результатом будет ИСТИНА;

2) формула должна ссылаться хотя бы на один столбец (первое значение в столбце) в списке;

3) в таблице критериев заголовок над формулой не выполняется.

 

Примерный вид таблицы критериев для данного примера будет:

=Е4>СРЗНАЧ($E$4:$E$18)

, где Е4:Е18 данные по суммам к возврату исходного списка. Обратите внимание, что ячейка для названия столбца пустая и что использована абсолютная адресация ячеек (знак $).

        

 

Практическое задание 6.

Используя вычислимые критерии расширенного фильтра получить информацию:

- о всех должниках, у кого сумма к возврату выше среднего значения по всем должникам(решение см. выше);

- о должнике с самым большой суммой к возврату(используйте функцию МАХ);

- о должнике, просрочившим возврат на максимальное число дней;

- о должниках, у кого сумма пени превышает сумму долга.