Создание диапазона условий
-
Скопируйте из списка заголовки фильтруемых столбцов в первую строку диапазона условий.
-
Введите в строки под заголовками условий требуемые критерии отбора.
-
Укажите ячейку в списке.
-
Выберите пункт меню Данные/ Фильтр/ Дополнительно.
-
В исходный диапазон автоматически ставятся координаты фильтруемого списка, если вы указали ячейку внутри списка в п.3.
-
Укажите координаты диапазона условий, содержащего строку заголовков и строки с критериями. В диапазон недопустимо включение лишних, пустых строк.
Примеры условий отбора расширенного фильтра
В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение.
Условия, записанные в одной строке, автоматически соединяются в одно логическое выражение союзом И (And). Условия, записанные в смежных строках, соединяются союзом ИЛИ (Or).
-
На ячейки одного столбца накладываются три или более условий отбора
Пример 5: Выбираются все строки с условием:
(материал= доска) или (материал=гвозди) или (материал=цемент)
материал |
доска |
гвозди |
цемент |
.
-
Условие отбора накладывается на ячейки двух или более столбцов
-
Пример 6: выбираются строки с условием:
(получил=иванов) и (дата>10/10/2000) и (дата<1/11/2000)
Получил |
дата |
дата |
иванов |
>10/10/2000 |
<1/11/2000 |
-
Пример 7: выбираются строки с условием:
(цена=максимальной цене в списке) или стоимость=максимальной стоимости в списке)
цена(у.е.) |
стоимость(у.е) |
=МАКС($D$3:$D$20) |
|
|
=МАКС($E$3:$E$20) |
-
В условии отбора используется возвращаемое формулой значение
Возвращаемое формулой значение можно использовать не только в условиях на равенство (как в предыдущем примере), но и с другими операторами сравнения.
В этом случае в качестве заголовка условия нужно ввести строку, которая не является заголовком столбца списка (например, 'критерий') или оставить заголовок условия незаполненным.
Используемая в условии формула должна ссылаться либо на заголовок столбца (например, 'цена(у.е.)' ), либо на соответствующее поле в первой записи(D3).
Пример 8: выбираются строки с условием:
значение в столбце 'цена(у.е.)' превышает среднее значение в ячейках $D$3:$D$20; заголовок условия критерий.
критерий |
='цена(у.е.)'>СРЗНАЧ($D$3:$D$20) |
Аналогичный результат получится при задании условия в виде:
|
=D3>СРЗНАЧ($D$3:$D$20) |
Подведение итогов
Для подведения итогов по всей базе данных, а также по отдельным группам данных можно использовать функцию Excel ПРОМЕЖУТОЧНЫЕ ИТОГИ (рис. 2.4.6), а также создание сводных таблиц.
Рис. 2.4.6. Промежуточные итоги
.
Подведение промежуточных итогов
Порядок работы:
-
Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог. (Например, чтобы просуммировать стоимость материала, отпущенного со склада одному человеку, нужно отсортировать список по полю [получил].)
-
Укажите ячейку в этом списке.
-
Выберите команду меню Данные/ Промежуточные итоги.
-
Выберите столбец При каждом изменении в, содержащий группы, по которым необходимо подвести итоги. Это должен быть тот столбец, по которому проводилась сортировка списка. (Например, получил.)
-
Выберите функцию, необходимую для подведения итогов, из списка Операция. (Например, сумма.)
-
Выберите столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по. (Например, стоимость.)
Детализацию данных можно регулировать с помощью кнопок структуры слева от таблицы.
Любая строка итогов может быть удалена из списка, это не повлияет на другие данные. Чтобы удалить все строки с итогами нужно выбрать меню Данные/ Итоги/ Убрать все.
Чтобы подвести итоги по вложенным группам (например, для каждого получателя подсчитать сумму количества и стоимости каждого материала), нужно сначала провести сортировку по всем необходимым столбцам (получил и материал), а затем два раза подвести итоги (сначала При каждом изменении в: получил, затем При каждом изменении в: материал)
Пример9: итоги по фамилиям получателей с суммированием стоимости, а также с суммированием количества и стоимости каждого материала для каждого получателя.
Таблица 2.4.3