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

Понятие списка

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

В Excel термин «список» относится к таблице, которая обладает следующими свойствами:

· Поле – это единица информации, например, название фирмы, адрес. Каждый столбец списка – это поле.

· Имя поля – уникальное имя, присвоенное каждому полю списка (столбцу таблицы).

· Значение поля – единичный элемент данных в поле (отдельная ячейка).

· Запись – набор взаимосвязанных значений полей. Каждая строка списка – это запись.

По существу список является базой данных, отсюда и соответствующая терминология. Пример списка представлен в табл.1.

Таблица 1. Список данных по мониторам

Фирма	Марка	Размер экрана	Тип	Цена	Поступил	Получено, шт.	Продано, шт.	Остаток, шт.	Сумма продаж
LG	L1515S		жк	$433	15.01.04
Benq	FP757		жк	$520	15.01.04
Samsung	152V		жк	$400	10.02.04
Samsung	753DFX		элт	$182	25.02.04
Sony	G-520		жк	$917	25.02.04
LG	710ВН		элт	$174	18.03.04
Benq	FP567		жк	$424	18.03.04
LG	710PН		элт	$190	17.04.04
Benq	FP767		жк	$564	17.04.04
Samsung	172S		жк	$550	27.04.04
Benq	FP731		жк	$482	27.04.04
Samsung	172V		жк	$509	17.05.04
Samsung	757DFХ		элт	$222	21.06.04
Benq	FP547		жк	$402	21.06.04
Samsung	755DFX		элт	$198	10.07.04
Samsung	172T		жк	$600	20.08.04
LG	F700B		элт	$190	20.08.04
LG	F700P		элт	$222	24.08.04

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

 

1. Старайтесь располагать на листе только один список, поскольку некоторые команды, например, фильтрация, работают в определенный момент только с одним списком.

 

2. Если на листе имеются данные, не относящиеся к списку, оставляйте, по крайней мере, одну пустую строку или один пустой столбец между этими данными и списком. Это поможет Excel автоматически распознавать список.

 

3. Имена полей должны располагаться в первой строке списка.

 

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

Чтобы работать со списком, достаточно выделить одну его ячейку и выбрать в меню Данные нужную команду. Excel автоматически выделит весь список.

Задание 1

1. Начиная с ячейки А1 введите список, представленный в табл.1. Формат для ячеек поля цена установите денежный (уберите значок $), денежная единица USD, десятичных знаков после десятичной точки - 0. Значения в столбце Остаток вычисляются как разность между полученным количеством мониторов и проданным, а в столбце Сумма продаж как произведение цены на проданное количество (и измените формат).

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

Сортировка данных списка выполняется командой меню Данные/ Сортировка. Данные можно сортировать по алфавиту, по числу или по дате. Порядок сортировки может быть возрастающим (от А до Я, от 1 до 9) или убывающим (от Я до А, от 9 до 1).

Когда список сортируется, Excel переставляет строки в соответствии с упорядоченным содержимым выбранного столбца. Имя этого столбца выбирается из списка Сортировать по.

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

Задание 2

Выполните сортировку списка сначала по полю Фирма, затем по Размеру экрана, и в последнюю очередь по Цене.

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

Задание 3

Отсортируйте список по полю Поступил в возрастающем порядке.

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

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

Автофильтр

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

 

Задача. Вывести на экран данные по мониторам фирмы LG.

1. Скопируйте список на новый лист.

2. Щелкните по кнопке со стрелкой в столбце Фирма.

3. Из списка критериев фильтрации выберите LG.

Удаление фильтра из списка:

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

· Чтобы удалить фильтры для всех столбцов списка, выполните команду меню Данные/Фильтр/Очистить.

· Чтобы удалить автофильтр из списка, еще раз выполните команду меню Данные/Фильтр.

Задача. Подсчитать на какую сумму продано жидкокристаллических мониторов фирмы Samsung.

1. Скопируйте исходный список на новый лист.

2. Самостоятельно вычислите в столбце J сумму от продаж каждого монитора.

3. Для поля Фирма установите критерий фильтрации Samsung, а для поля Тип – ЖК.

4. Выделите ячейку J20 и щелкните по кнопке Автосумма. Обратите внимание, что вместо функции СУММА для отфильтрованного списка используется функция ПРОМЕЖУТОЧНЫЕ ИТОГИ.

Задача. Вывести на экран данные по мониторам в ценовом диапазоне от 150$ до 200$.

 

1. Скопируйте список на новый лист.

2. Для поля Цена выберем Числовые фильтры, затем из списка критериев выберем Между.
Математически наше условие записывается как 150 £ Цена £ 200 и состоит из двух простых условий Цена ³ 150 и Цена £ 200, которые должны выполняться одновременно. Поэтому в Excel для его формирования необходимо использовать логический оператор И:
Цена ³ 150 И Цена £ 200.

3. В верхней строке в левом поле выберите больше или равно, а в правом поле введите 150. В нижней строке в левом поле выберите меньше или равно, а в правом введите 200. Выберите оператор И и нажмите ОК.

Задача. Вывести на экран данные по самому дешевому монитору.

1. Скопируйте список на новый лист.

2. Для поля Цена из списка критериев выберем Первые 10.

3. В первом поле слева выберем 1, во втором – наименьших, в третьем – элементов списка.

Задание 4

Определите количество мониторов с размером экрана 17", проданных в августе.

Задание 5

Подсчитайте, на какую сумму продано мониторов фирм Benq и LG за летние месяцы.

Задание 6

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

Расширенный фильтр

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

Задача. Вывести список жидкокристаллических мониторов, из поступивших партий которых не продано ни одного монитора.

1. Скопируйте список на новый лист.

2. Сначала необходимо создать Диапазон условий, который должен содержать критерии фильтрации.

a) Задание критерия фильтрации требует точной копии имен столбцов исходного списка, которые в нем используются. Поэтому скопируем имена полей: из ячейки D1 в ячейку А23 и из ячейки H1 в В23.

b) В ячейки А24 и В24 введем операторы сравнения, знак равенства предполагается по умолчанию:

Тип	Продано, шт.
жк

Рис. 5. Диапазон условий

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

Фирма

Марка

Рис. 6. Заголовок таблицы для результатов фильтрации

Для этого следует скопировать в свободное место листа имена только тех полей списка, которые должны присутствовать в заголовке таблицы результатов. В нашем случае скопируем имена полей из ячеек А1:В1 в ячейки А26:В26.

4. Выделите любую ячейку в списке и выполните команду Данные/ Фильтр/Расширенный фильтр.
Задайте Исходный диапазон – $A$1:$J$19, Диапазон условий – $A$23:$A$24.
Установите переключатель Обработка в положение скопировать результаты в другое место. После этого можно заполнить поле Поместить результат в диапазон, указав в нем расположение заголовка выходной таблицы – $A$26:$B$26.

Правила для задания условий отбора расширенного фильтра:

1. Если условия отбора заданны в диапазоне условий в одной строке разных столбцов, то эти условия связаны логическим оператором И, то есть необходимо одновременное выполнение всех условий для отбора записей в таблицу результата.

2. Если условия поиска заданы в одном столбце сверху вниз, то они связаны логическим оператором ИЛИ, то есть требуется выполнение хотя бы одного из условий для отбора записей в таблицу результата.

3. Следующий диапазон условий задает условие отбора
(Фирма= LG И Цена <400) ИЛИ (Фирма= Benq И Цена <500).

Фирма	Цена
LG	<400
Benq	<500

Задание 7

Сформируйте таблицу, отображающую марки мониторов фирм Sony и Samsung, партии которых проданы полностью (т.е. остаток для них равен 0).

Задание 8

Сформируйте таблицу для заказчика, который хотел бы просмотреть все марки мониторов с размером экрана 17" фирм LG, Benq и Sony.

Задание 9

Выведите названия фирм, марок и типов для жидкокристаллических мониторов с ценой, не превышающей 500$, и мониторов с электронно-лучевой трубкой с ценой, не превышающей 200$.

Формирование итогов

Excel может автоматически обобщать данные, вычисляя промежуточные и общие итоги в списке с помощью команды меню Данные/Итоги. Промежуточные итоги вычисляются по группам записей, например, для каждой фирмы. Чтобы команда Итоги работала, список должен быть сначала отсортирован по столбцу, для которого требуются промежуточные итоги.

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

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

Задача. Подсчитать, в каком количестве и на какую сумму продано мониторов каждой фирмы.

1. Скопируйте список на новый лист.

2. Отсортируйте таблицу по полю Фирма.

3. Выполните команду Данные/Промежуточный итог.

4. Выберите столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

5. Выберите функцию Сумма, необходимую для подведения итогов, из списка Операция.

6. Выберите столбцы Продано, шт. и Сумма продаж, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по. Нажмите кнопку ОК.

7. Изучите полученный результат.

Задание 10

Определите общие суммы продаж мониторов по размерам экрана.

Задание 11

Определите, на какую сумму продано мониторов каждой фирмы, а внутри каждой фирмы общие суммы продаж по типам.

Указание к выполнению. Сортировку данных необходимо выполнить по 2-м полям: сначала по фирме, затем по типу. Подведение итогов выполняется дважды – для каждого поля. Причем при подведении итогов по полю Тип необходимо снять флажок Заменить текущие итоги.

Сводные таблицы

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

Сводные таблицы создаются с помощью Мастера сводных таблиц, который вызывается командой меню Вставка/Сводная таблица.

 

Рис. 7. Сводная таблица

В сводной таблице заголовки строк (янв, фев и т.д.) берутся из поля строк, заголовки столбцов (Benq, LG и т.д.) – из поля столбцов; каждая пара заголовков (янв-Benq, янв-LG, янв-Samsung, фев-Benq и т.д.) определяет группу, по которой будут подводиться промежуточные итоги. Поле страниц используется для фильтрования данных, а поле данных определяет поле, над которым выполняются вычисления. Для построения конкретной сводной таблицы необходимо в макете сводной таблицы указать, какие поля исходного списка будут выступать в качестве полей строк, столбцов, страниц и данных сводной таблицы.

Готовую таблицу можно отредактировать с помощью Мастера сводных таблиц, панели инструментов Сводная таблица или контекстного меню. Можно:

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

- изменить функцию для обработки данных;

- использовать группирование для менее раздробленного представления данных, например, для просмотра данных по месяцам вместо просмотра по дням или для просмотра числовых элементов по сотням, а не по отдельности;

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

- изменить внешний вид таблицы.

Задача. Создать сводную таблицу для анализа поступлений мониторов по месяцам, фирмам и размеру экрана (рис. 7).

1. Выделите любую ячейку списка и выполните команду Вставка/ Сводная таблица. На 1-м шаге выберите в качестве источника данных список, а вид отчета – сводная таблица. Щелкните кнопку Далее.

2. На 2-м шаге задается диапазон исходных данных – $A$1:$J$19.

3. На 3-м шаге формируется структура сводной таблицы. Щелкните кнопку Макет и перетащите кнопку поля Поступил в область макета Название строк, кнопку поля Фирма – в область Названия столбцов, кнопку поля Размер экрана – в область Фильтр отчета, кнопку поля Получено, шт. – в область Значения. Обратите внимание, что по умолчанию данные суммируются. Нажмите кнопку ОК.
Задайте размещение сводной таблицы на новом листе и нажмите кнопку Готово.

4. Выполним группирование дат по месяцам. Для этого:

a) щелкните правой кнопкой мыши по полю Поступил или любому его значению, затем выполните командуконтекстного меню
Группа и Структура/Группировать;

b) В списке с шагом выберите Месяцы.

5. Созданная сводная таблица является интерактивной.
Используя кнопки со стрелками, получите разные срезы данных.

Задача. Подсчитать расходы на доставку, если доставка одного монитора обходится в 2$.

Для решения задачи достаточно создать в построенной сводной таблице вычисляемое поле с формулой = Получено, шт.*2, которая означает, что каждое значение в ячейке области данных (т.е. общее количество мониторов по группе) умножается на 2.

1. Щелкните правой кнопкой мыши по любой ячейке сводной таблицы и из контекстного меню выполните команду Формулы/Вычисляемое поле.

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

3. Для того, чтобы в таблице не отображались нули, выполните команду меню Сервис/Параметры/вкладка Вид и в разделе Параметры окна удалите флажок нулевые значения.

Задача. Показать поступления мониторов по месяцам и фирмам в процентах от общего количества поступивших мониторов.

1. Щелкните правой кнопкой мыши по любому заголовку Сумма по полю Получено, шт. или соответствующей ячейке в области данных и из контекстного меню выполните команду Параметры поля. Затем нажмитекнопку Дополнительно,из списка Дополнительные вычисления выберите Доля от общей суммы.

2. Заодно изменим имя поля данных. Для параметра Имя введите Получено от общего количества. Нажмите ОК.

Изменим структуру сводной таблицы.

1. Удалите поле данных Сумма по полю Расходы на доставку: нажмите стрелку на кнопке Данные и снимите соответствующий флажок.

2. Перетащите поле столбцов Фирма на кнопку Поступил.

3. Удалите поле строк Поступил. Для этого перетащите кнопку Поступил за пределы сводной таблицы.

4. Сделайте Размер экрана полем столбцов: перетащите кнопку Размер экрана в пустую ячейку справа от поля данных.

Задание 12

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