Офисные прикладные программы MS Office 2007: Microsoft Excel

Фильтрация

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

Чтобы воспользоваться фильтром выполните ряд действий:

1. В правой части каждой ячейки с заголовками столбцов есть кнопка раскрывающегося списка (кнопка со стрелкой).
2. Щелкнуть на кнопке раскрывающегося списка того поля, по которому необходимо выполнить фильтрацию. Например, чтобы отобразить записи только одного отдела, щелкнуть на кнопке раскрывающегося списка поля Отдел.
3. Выбрать элемент раскрывающегося списка, в соответствии с кото­рым необходимо отфильтровать записи базы данных.

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

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

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

1. Щелкните на кнопке раскрывающегося списка числового поля, по которому необходимо выполнить фильтрацию. В нашем примере щелкните на кнопке раскрывающегося списка поля Цена расхода.
2. В открывшемся меню выберите элемент Числовые фильтры (рис.59). Откроется меню с операторами сравнения.

Рис.59. Числовой фильтр

3. Выберите нужный оператор сравнения. Для нашего примера вы­берите больше или равно. На экране появится диалоговое окно Пользовательский автофильтр (рис.60). В поле справа от оператора сравнения введите нужное значение, в нашем примере это 100.

4. Завершите выбор кнопкой ОК.

Excel отфильтрует таблицу по колонке Цена расхода, и покажет все товары, у которых Цена расхода будет равна или больше 100 руб.

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

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

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

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

В этой ячейке появится функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (109; [Сумма остатка] ) (в строке формул). В ней первым аргументом является номер математической или статистической операции (101 - вычисление среднего значения; 102 и 103 - подсчет количества чисел и непустых ячеек; 104 и 105 - вычисление максимума и минимума; 106 - произведение; 107- стандартное отклонение; 109 - сумма; 110 дисперсия), а вторым - интервал вычислений.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ находится в категории матема­тические и отличается тем, что она вычисляет значения только по видимым ячейкам (рис.58), а невидимые не учитывает. При изменении фильтрации, меняются и промежуточные итоги, тогда как обычная функция суммирования или произведения останется неизменной.

Изменим немного условие задачи, пусть необходимо посмотреть данные по ненулевым остаткам кондитерского отдела. Для этого в списке столбца Сумма остатка в пункте Числовые фильтры выберем необходимое Условие (больше, меньше, равно), либо Настраиваемый фильтр. В появившемся окне Пользовательский автофильтр (рис.60) в верхнем поле выбираем больше «0.00р.». Полученный результат представлен на рисунке (рис.62).

Для того чтобы посмотреть данные по ненулевым остаткам конди­терского и мясного отделов, в столбце Сумма остатка оставляем тот же самый фильтр, а в столбце Отдел выбираем Настраиваемый фильтр (рис.60). В верхнем поле выбираем равно Кондитерский, внизу равно Мясной, а в качестве логической функции задаем ИЛИ.

Полученный результат представлен на рисунке (рис.63).

Чтобы снова увидеть таблицу целиком, необходимо щелкнуть по стрелке в столбце, где применялась фильтрация (они показаны синими стрелками), выбрать в списке Все или Снять фильтр с «Отдел».