Фильтрация данных с использованием расширенного фильтра

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

Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню Данные/Фильтр/Расширенный фильтр. Важной особенностью этого режима является то, что до выполнения самой команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора (интервал критериев). Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Обычно, для создания диапазона условий вначале копируют в отдельное, место (на другом или на том же самом рабочем листе – как правило, выше исходной таблицы) строку с заголовками столбцов, затем в расположенные ниже строки вводят критерии выборки по отдельным столбцам. Между значениями условий отбора и таблицей должна находиться, как минимум, одна пустая строка (рис.4.4). Если критерии отбора (условия фильтрации) вводятся в одной строке для разных столбцов, то они считаются связанными условием «И». Если критерии отбора записываются в разных строках, то они считаются связанными условием «ИЛИ».

Рис. 4.4. Пример списка данных с диапазоном условий отбора.

После формирования диапазона условий с критериями выборки записей необходимо устанавить курсор внутри таблицы, и выбрать команду Данные/Фильтр/Расширенный фильтр и в диалоговом окне Расширенный фильтр (рис. 4.5) указать диапазон ячеек таблицы, адрес сформированного заранее диапазона условий. Записи можно фильтровать на месте или одновременно с выполнением фильтрации копировать в указанную область на текущем рабочем листе. Чтобы скопировать отфильтрованные строки в другую область листа, следует установить переключатель Скопировать результаты в другое место, перейти в поле Поместить результат в диапазон и указать верхнюю левую ячейку области вставки отобранных данных. Ссылка на диапазон условий отбора должна включать только заголовки столбцов и строки с критериями, т. е. не содержать пустых строк.

Рис. 4.5. Вид диалогового окна Расширенный фильтр.

Промежуточные итоги

Автоматическое подведение итогов – это удобный способ быстрого обобщения и анализа данных в электронной таблице. Microsoft Excel может автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов Microsoft Excel изменяет разметку списка, что позволяет отображать и скрывать строки каждого промежуточного итога. Например, на рисунке 4.6 показан список, в котором вычислены промежуточные итоги для поля Город получателя. В данном случае для каждого города вычислена средняя стоимость доставки и в структуру таблицы автоматически добавлены строки с промежуточными итогами, а в конце списка приводится общий итог – средняя стоимость доставки по всем заказам.

Рис. 4.6. Фрагмент списка с промежуточными итогами

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

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

Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении данных в списке.

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

· Сумма (Сумма чисел);

· Количество (Количество данных. Эта операция используется по умолчанию для подведения итогов по нечисловым полям);

· Среднее (Среднее чисел);

· Максимум (Максимальное число);

· Минимум (Минимальное число);

· Произведение (Произведение чисел);

· Количество чисел (Количество данных, являющихся числами);

· Смещенное отклонение (Смещенная оценка стандартного отклонения генеральной совокупности по выборке данных);

· Несмещенное отклонение (Несмещенная оценка стандартного отклонения для генеральной совокупности, где выборка является подмножеством генеральной совокупности);

· Смещенная дисперсия (Смещенная оценка дисперсии генеральной совокупности по выборке данных);

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

Итак, для автоматического подведения итогов следует выполнить следующую последовательность действий:

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

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

· выбрать команду меню Данные/Итоги;

· в диалоговом окне Промежуточные итоги (рис.4.7) из списка При каждом изменении в: выбрать столбец, содержащий группы, по которым необходимо подвести итоги. Это должен быть тот столбец, по которому проводилась сортировка списка;

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

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

Рис. 4.7. Диалоговое окно. Промежуточные итоги.

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