Фильтрация

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

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

1. В правой части каждой ячейки с заголовками столбцов есть кнопка раскрывающегося списка (кнопка со стрелкой).

2. Щелкните на кнопке раскрывающегося списка того поля, по которому необходимо выполнить фильтрацию. Например, чтобы отобразить записи только одного отдела, щелкните на кнопке раскрывающегося списка поля Отдел.

3. Выберите элемент раскрывающегося списка, в соответствии с которым необходимо отфильтровать записи базы данных.

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

Рис.56. Таблица после фильтрации по столбцу Отдел

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

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

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

2. В открывшемся меню выберите элемент Числовые фильтры (рис.57). Откроется меню с операторами сравнения.

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

4. Щелкните на кнопке ОК.

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

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

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

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

Рис.59. Вычисление суммы остатка по Кондитерскому отделу

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

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

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

В верхнем поле выбираем больше «0.00р.». Полученный результат представлен на рисунке (рис.60).

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

Рис.60. Данные по ненулевым остаткам кондитерского отдела

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

Рис.61. Данные по отделам Кондитерский и Мясной

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