Материал: 5442
26
можно использовать для задания условий, и приведены примеры условий для текстовых значений.
Таблица 2 – Операторы, используемые для задания условий
Оператор или |
Тип сравнения |
|
условие |
||
|
||
|
|
|
= |
Равно |
|
|
|
|
>, < |
Больше, меньше |
|
|
|
|
>=, <= |
Больше или равно, меньше или равно |
|
|
|
|
<> |
Неравно |
|
|
|
|
>С |
Все слова, которые начинаются с букв от Т до Я |
|
|
|
|
С |
Все слова, которые начинаются с буквы С |
|
|
|
|
<>С* |
Все слова, кроме тех, которые начинаются с буквы С |
|
|
|
При задании условий можно использовать так называемые вычисляемые условия или вычисляемые критерии. Предположим, что нам нужно получить информацию о том, какого материала выдано на сумму, большую среднего значения по полю Сумма. Среднее значение можно было бы добавить в список и в условии сделать на него ссылку. Но лучше использовать вычисляемый критерий.
Вычисляемый критерий – это формула, вычисляющая новое значение для списка. Формула должна ссылаться на ячейки с данными, расположенными в первой строке списка, которая находится сразу же под строкой заголовков. Ссылки на ячейки списка в формуле должны быть относительными (в примере на рисунке 16 ячейка F7). Формула вводится во вторую строку и в новое поле диапазона условий, причём новое поле может не иметь заголовка, либо заголовок не должен совпадать с названиями полей списка, а пояснять вводимое условие (в примере заголовок Больше среднего). Вычисляемые критерии можно использовать вместе с обычными условиями. Например, на рисунке 16 для определения, какого наименования материала, за исключением цемента, выдано на сумму, меньшую среднего значения, в поле Наименование товара записано условие <>Цемент (не равно цемент), а формула для вычисляемого критерия поля Больше среднего имеет вид: =F7<СРЗНАЧ($F$7:$F$314).
27
Рисунок 16 – Задание условий для Расширенного фильтра
Формула =F7<СРЗНАЧ($F$7:$F$314) в диапазоне условий возвращает значение ЛОЖЬ, так как ссылается на первую строку с данными в списке. На самом деле при фильтрации записей эта формула применяется к каждой строке списка. После применения расширенного фильтра на экране будут отображены только те записи, для которых данная формула вычисляет значение ИСТИНА
Результат фильтрации списка можно скопировать в другую область рабочего листа, на котором находится исходный список, или на отдельный лист текущей книги.
Для выполнения расширенного фильтра, выполнить команду Данные – Фильтр – Расширенный фильтр. Появится диалоговое окно Расширенный фильтр (рисунок 17), в котором задать диапазон списка и диапазон условий в полях Исходный диапазон и Диапазон условий. Если выбрать переключатель Скопировать результат в другое место, то отобранные строки будут скопиро-
ваны в ту область, которая указана в поле Поместить результат в диапазон.
28
Рисунок 17 – Диалоговое окно Расширенный фильтр
Если список фильтровался на месте, то для отмены фильтрации приме-
нить команду Отобразить все из меню Данные – Фильтр.
Промежуточные итоги
ВExcel имеется полезное средство, с помощью которого можно быстро подвести промежуточные итоги. Предположим, требуется вычислить суммарное количество каждого наименования товара, отпущенного со склада. Для быстрого суммирования данных в списке Excel предлагает процедуру вставки автоматически рассчитанных промежуточных и общих итогов.
Для этого необходимо выполнить следующие действия.
Вначале список необходимо подготовить к подведению промежуточных итогов Список должен быть отсортирован по тому полю, по которому будут подводиться итоги, чтобы сгруппировать строки, по которым нужно подвести итоги. В нашем примере необходимо выполнить сортировку по полю Наименование товара (сортировать строки можно по возрастанию, по убыванию или применить особый порядок сортировки).
После того как список отсортирован, щёлкнуть на любой ячейке списка и выбрать команду Данные – Итоги. Откроется диалоговое окно Промежуточные итоги (рисунок 18).
Вдиалоговом окне Промежуточные итоги можно определить следующие параметры:
При каждом изменении в:. В этом раскрывающемся списке необходимо выбрать то поле, по которому выполнялась сортировка списка (в нашем примере это поле Наименование товара).
29
Рисунок 18 – Диалоговое окно Промежуточные итоги
Операция. Этот раскрывающийся список содержит 11 операций или, точнее, функций для вычисления итогов. По умолчанию в этом поле указана функция Сумма, т.е. при подведении итогов данные суммируются.
Добавить итоги по. Этот список содержит названия полей списка. Установить флажок возле тех полей, по которым необходимо подвести итоги. (В нашем примере необходимо установить флажок рядом с полем Сумма.)
Заменить текущие итоги. Установка этого флажка необходима, чтобы любые существующие итоги заменялись новыми.
Конец страницы между группами. Если этот флажок установлен, Excel
вставляет символ конца страницы после подведения каждого промежуточного итога.
Итоги под данными. Если этот флажок установлен, итоги размещаются под данными. В противном случае итоги будут помещены над данными.
Убрать все. Щёлкните на этой кнопке, если необходимо удалить все формулы итогов из списка.
30
После задания всех необходимых параметров щёлкнуть на кнопке ОК. Excel вставит в список строки с формулами и заодно создаст структуру списка (рисунок 19). Все формулы, вставленные в список, используют функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Рисунок 19 – Структура списка с промежуточными итогами в режиме формул
При добавлении в список промежуточных итогов разметка списка изменяется так, что становится видна его структура. Для отображения только промежу-
точных и общих итогов использовать кнопки 
слева от номеров строк.
Кнопки 
и 
позволяют показать или скрыть строки данных для итогов. Таким образом можно создать итоговый отчёт, скрыв подробности и отобразив только итоги (рисунок 20).