Автофильтр Excel 2007
 | Продолжая разговор про обработку данных в Excel 2007, мы познакомимся с чрезвычайно полезным «бухгалтерским» инструментом — фильтрацией данных. Этот инструмент в работе бухгалтера занимает особое место. С одной стороны, он представляет собой очень гибкое средство для поиска данных в больших таблицах. С другой — с помощью фильтров можно локализовать область обработки данных в MS Excel, используя для этого самые разные ограничения. А это как раз те задачи, с которыми каждый бухгалтер сталкивается практически ежедневно. Возвращаясь к вопросу о фильтрации данных в Excel 2007, скажу: эти инструменты в новой версии стали намного лучше. Что бы ни говорили скептики, а Excel 2007 в плане функциональных возможностей существенно продвинулся вперед. Какие новинки появились в этой программе в плане фильтрации данных и чем это поможет бухгалтеру — нам и предстоит разобраться. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Знакомство с инструментами фильтрации в Excel 2007 мы начнем с определения: вначале вспомним, для чего используют фильтры и какие они бывают. Затем посмотрим, как работа с фильтрами реализована в ленточном интерфейсе Excel. И разумеется, отдельно остановимся на тех возможностях, которые появились в новой версии программы. Итак, приступим.
Что такое фильтрация данных
Фильтры используют для того, чтобы выбрать из базы данных набор записей по некоторому критерию. В отфильтрованной базе доступны только те строки, которые удовлетворяют заданному условию, остальные остаются «за кадром». В отличие от сортировки, фильтр не меняет порядок записей в базе
. Отфильтрованные строки можно редактировать, удалять, копировать, форматировать, выводить на печать, создавать на их основе диаграммы и т. п.В Excel 2007 работа с фильтром получила определенное развитие. Теперь при фильтрации данных можно учитывать их формат. При обработке фильтром дат и времени появилась возможность выполнить их группировку. Кроме того, Excel 2007 предоставляет в распоряжение бухгалтера удобный механизм работы с так называемыми динамическими фильтрами, когда условие может меняться в зависимости от конкретной ситуации. Все эти преимущества, безусловно, будут полезны в работе бухгалтера, и разобраться с ними нужно не торопясь.
Инструменты фильтрации в Excel 2007
Как и в предыдущей версии программы, в Excel 2007 есть два инструмента фильтрации данных — автоматический и расширенный фильтры. Их отличие заключается главным образом в способе задания условий для отбора записей. При работе с автофильтром не нужно видоизменять исходную таблицу. Условия для отбора записей задают в специальных диалоговых окнах MS Excel. Чтобы применить расширенный фильтр, в исходной таблице придется создать специальную область, записать в нее условия фильтрации и только потом использовать эти условия для отбора данных. Каждый способ имеет свои достоинства и недостатки. Автофильтр удобен в работе. Но диапазон возможных условий фильтрации у него ограничен. Расширенный фильтр предоставляет больше возможностей для отбора записей. Зато для его применения нужно выполнить определенные манипуляции с исходной таблицей. Мы начнем знакомство с фильтрацией данных в Excel 2007 с автофильтра, так как этот инструмент более простой и чаще применяется на практике.
Автофильтр
Основная задача автофильтра — выбрать из базы данных записи по заданному множеству условий. Эти условия можно задать для одного конкретного столбца или для нескольких колонок одновременно. Если в работе используется много условий фильтрации, все они дополняют друг друга. То есть действие каждого нового фильтра накладывается на работу предыдущего, все больше и больше ограничивая подмножество отфильтрованных данных.
В Excel 2007 есть три способа фильтрации данных: по значениям списка, по формату ячеек и по условиям. Все эти способы взаимно исключают друг друга, если вы работаете с одним диапазоном данных или одним столбцом таблицы. Например, автофильтром можно отобрать записи, указав диапазон возможных значений или цвет ячеек в конкретной колонке. Но выбрать из этой колонки данные с учетом и цвета, и значения одновременно в Excel 2007 нельзя.
Для работы с автофильтром Excel 2007 мы воспользуемся хорошо знакомым реестром контрагентов, фрагмент которого показан на рис. 1. Он содержит семь полей «
N», «Дата», «НаимПредпр», «ИНН», «ОбщСум», «СумБезНДС», «НДС». Область данных в реестре начинается со второй строки рабочего листа. В базе контрагентов нет пустых строк, поэтому диапазон данных Excel сможет распознать автоматически. Работу с автофильтром мы начнем с отбора текстовых значений из базы данных.
Ф
ильтрация текстаНаша первая задача при работе с автофильтром — отобрать из базы данных контрагентов по их наименованию (в реестре это поле «
НаимПредпр»). Делаем так:1) ставим активную ячейку в область базы данных;
2) на ленте меню «
Главная» находим группу «Редактирование»;3) щелкаем левой кнопкой на иконке «
Сортировка и фильтр» (рис. 2). Откроется меню, изображенное на рис. 3;
4) из этого меню выбираем пункт «
Фильтр». В каждой колонке строки заголовков появились значки выбора. Они похожи на кнопки выпадающего списка (рис. 1). С их помощью можно формировать условия для отбора записей из базы данных. Щелчок мышкой на любом значке раскрывает список условий. Этот список будет построен с учетом типа данных в ячейках соответствующей колонки;5) щелкаем на значке выбора в колонке «
НаимПредпр». Раскроется меню, изображенное на рис. 4 на с. 17. В этом меню нас интересует пункт «Текстовые фильтры»;
6) переводим указатель мыши на строку «
Текстовые фильтры». Раскроется меню, изображенное на рис. 5 на с. 17. В нем есть семь пунктов. Первые шесть — это предопределенные условия для выбора записей. Последний пункт с названием «Настраиваемый фильтр…» позволяет построить пользовательское условие для фильтрации записей. Допустим, мы решили отобрать записи из базы по условию равенства — нас интересует информация по фирме «Колорит»;
7) из меню на рис. 5 выбираем пункт «
равно…». Появится окно, изображенное на рис. 6 на с. 18. В левой части этого окна находится список для определения типа операции. Сейчас там установлены поле «НаимПредпр» и операция «равно». В правой части окна находится список для выбора конкретного значения;
8) щелкаем левой кнопкой на этом списке. Появится перечень всех предприятий, которые зарегистрированы в реестре;
9) выбираем фирму «
ООО "Колорит"»;10) нажимаем на кнопку «
ОК». База данных примет вид, как на рис. 7 со с. 18. Все верно. На экране остались только записи, относящиеся к фирме «ООО "Колорит"».
Совет
Для включения или отключения автофильтра используйте комбинацию «Ctrl+Shift+L».Обратите внимание, что в результирующую выборку автофильтр включил записи с контрагентами «ООО "Колорит"» и «Ооо "Колорит"». Вывод: при работе автофильтра регистр символов роли не играет.
Совет
Если при работе с автофильтром значки выбора мешают читать текст заголовков (перекрывают их), примените к заголовкам выравнивание влево.Обратиться к автофильтру можно и по-другому. Например, выбрать пункт «Данные» главного меню, найти группу иконок «Сортировка и фильтр» и воспользоваться значком «Фильтр».
Отменим фильтр, чтобы показать всю базу данных целиком. Для этого делаем так:
1) щелкаем на значке выбора в колонке «НаимПредпр». Раскроется меню, изображенное на рис. 4;
2) в этом меню выбираем пункт «Снять фильтр с "НаимПредпр"». Реестр снова виден в полном объеме.
Важно!
В списке автофильтра (рис. 4) Excel 2007 может показать до 10000 элементов. Это существенное усовершенствование инструмента «Автофильтр» по сравнению с предыдущими версиями программы. Те, кому приходилось работать с большими базами данных, наверняка сталкивались с ситуацией, когда в меню автофильтра Excel 2003 мог показать не более 1000 элементов. Во многих случаях для бухгалтера этого явно недостаточно.И еще одна приятная новинка. Обратите внимание на специальный значок в нижнем правом углу окна, изображенного на рис. 4. Это маркер захвата. Он позволяет изменить размер окна автофильтра и тем самым показать большую или меньшую часть списка его элементов. И что немаловажно, размер этого окна не ограничен размерами окна самой программы Excel. Это очень удобно. Например, вы можете увеличить ширину окна со списком автофильтра, чтобы полностью видеть его элементы. В предыдущих версиях программы эта ширина была фиксированной и определялась по размеру соответствующей колонки рабочего листа. Или такой случай. Вы корректируете две таблицы и хотите постоянно держать их перед глазами. Экран у вас большой, вы удобно разместили на нем два окна Excel и начали работу. В какой-то момент вам понадобился автофильтр. Работая с Excel 2007, вы можете вызвать меню автофильтра и увеличить его до любого нужного размера, несмотря на то что сам Excel при этом занимает всего лишь половину экрана. В предыдущей версии программы такая возможность была недоступна. Размер списка элементов автофильтра был ограничен как по ширине, так и по высоте.
Теперь вернемся к окну на рис. 5. В его верхней части видим шесть условий для фильтрации текстовых данных. Названия соответствующих пунктов говорят сами за себя. Думаю, подробные комментарии здесь излишни. Например, мы решили отобрать из базы данных все общества с ограниченной ответственностью (в нашем случае это «ООО "Колорит"» и «ООО "ЕКСС"»). Тогда можно воспользоваться пунктом «Начинается с…». Окно параметров в данном случае в точности повторяет изображенное на рис. 6. Только в области типа операции будет стоять «начинается с…», а в поле значения мы должны ввести текст «ООО».
Совет
Обратите внимание на пункт «содержит…» в составе меню на рис. 5. Он позволяет отобрать записи, в которых текстовое поле (т. е. строка) содержит указанное значение в виде подстроки. Представьте, что в нашем реестре нужно отобрать все предприятия, в названии которых есть элемент «ООО». Но часть этих названий выглядит как «ООО "Колорит"», а часть записана как «"Колорит", ООО». В этом случае условие «содержит…» со значением «ООО» решит проблему в считанные секунды. При работе с текстовыми данными выбор по признаку вхождения — одна из самых полезных возможностей.Последним пунктом в меню на рис. 5 расположен «Настраиваемый фильтр». Здесь сосредоточены самые гибкие возможности для отбора записей. Щелкаем на нем левой кнопкой мыши. Появится уже знакомое окно, изображенное на рис. 6. Разница видна только в одном. Когда мы выбирали разделы меню с конкретной операцией, Excel сразу установил ее в списке «Показать только те строки, значения которых:». При работе с настраиваемым автофильтром все условия нужно указать вручную. Посмотрим, какие возможности может предложить диалог на рис. 6.
В левой части окна находится список возможных условий. Он насчитывает 12 элементов: «равно», «не равно», «больше», «больше или равно », «меньше», «меньше или равно », «начинается с», «не начинается с», «заканчивается на», «не заканчивается на», «содержит», «не содержит». Справа расположено поле для выбора конкретного значения. Это поле можно заполнить из списка или напечатать с клавиатуры.
В окне «Пользовательский автофильтр» (рис. 6) можно ввести максимум два условия выбора записей, объединив их между собой операциями «И»/«ИЛИ». Когда включен переключатель «И», Excel покажет записи, удовлетворяющие обоим условиям. При включенном переключателе «ИЛИ» будут показаны записи, удовлетворяющие одному из условий. Например, чтобы выбрать из базы все предприятия, в названии которых есть элемент «ЧП» или слово «фирма», нужно сделать так:
1) открыть окно «Пользовательский автофильтр»;
2) в верхнее поле с типом операции указать «содержит»;
3) в качестве значения для этого условия ввести «ЧП»;
4) во втором поле с типом операции тоже указать «содержит»;
5) в качестве значения для этого условия ввести «фирма»;
6) переключатель логической связи между операциями поставить в положение «ИЛИ»;
7) нажать на «ОК». На экране останутся данные по контрагентам «ЧП "Матадор"», «Фирма "КХК"» и «Фирма "Эталон"».
Возможность использовать логические операции «И»/«ИЛИ» в сочетании со стандартным набором условий автофильтра позволяет построить достаточно сложные правила отбора записей из базы данных.
Работа с окном «Пользовательский автофильтр» (рис. 6) — не единственный способ выбрать записи из длинного списка. Вернемся к окну на рис. 4. В нижней части этого окна находится список всех предприятий, которые присутствуют в реестре. Состав этого списка Excel взял из колонки, по которой мы делали фильтрацию данных (напомню, что это был столбец «НаимПредпр»).
Слева от названий расположены поля с галочками. По умолчанию они включены. Это означает, что все записи базы данных будут видны на экране, т. е. фильтр отключен. Делаем так:
1) щелчком мыши убираем все галочки, оставляем ее только возле названия «ООО "Колорит"». На экране останутся записи, относящиеся к фирме «ООО "Колорит"». То есть в данном случае Excel применил к базе данных условие фильтрации по признаку равенства. Он выбрал записи, у которых содержимое ячеек в колонке «НаимПредпр» совпадает с указанным значением;
2) снова вызываем меню автофильтра. Ставим галочку возле строки «Фирма "Эталон"». Теперь на экране видны записи о предприятиях «ООО "Колорит"» и «Фирма "Эталон"»;
Важно!
Нескольких условий в меню автофильтра взаимодействуют между собой по логике «ИЛИ».3) щелкаем на значке выбора в колонке «НаимПредпр», возвращаемся в меню работы с автофильтром;
4) ставим галочку возле пункта «(Выделить все)». В базе данных стали видны все записи.
Важно!
Выбор пункта «(Выделить все)» не отменяет действие автофильтра. Значки выбора в заголовках таблицы видны, а это означает, что фильтр по-прежнему активен.
Фильтрация чисел
Работа автофильтра в Excel 2007 зависит от типа данных, к которым он применяется. Вернемся к базе данных на рис. 1 и отфильтруем ее по колонке «
ОбщСум». Допустим, мы хотим выбрать все поступления, сумма которых находится в диапазоне от 1000 до 5000 грн. Делаем так.1) ставим активную ячейку в область базы данных;
2) в группе «
Редактирование» главного меню щелкаем на иконке «Сортировка и фильтр» (рис. 2). Откроется меню, как на рис. 3;3) из этого меню выбираем пункт «
Фильтр»;4) щелкаем на значке выбора в колонке «
ОбщСум». Раскроется меню, изображенное на рис. 8. Нас интересует пункт «Числовые фильтры»;
5) ставим указатель мыши на этот пункт. Появится меню, как на рис. 9;
6) из этого меню выбираем «
Между». Появится окно настройки фильтра, изображенное на рис. 10; 
7) в качестве значения «
больше или равно» указываем «1000». В поле «меньше или равно» вводим значение «5000»;8) нажимаем на «
ОК». Фрагмент отфильтрованной базы данных показан на рис. 11. В этом фрагменте все значения поля «ОбщСум» находятся в указанном диапазоне. 
Большинство пунктов автофильтра для числовых данных (рис. 9) не отличаются от аналогичного меню для работы с текстом. Среди специфических возможностей стоит обратить внимание на пункты «
между», «Первые 10…», «Выше среднего» и «Ниже среднего». Фильтром по условию «между» мы только что воспользовались. Пункт «Первые 10…» позволяет оставить на экране десять наибольших или десять наименьших элементов базы данных.Для условия «
Выше среднего» Excel определит среднеарифметическое значение по данным ключевой колонки и оставит на экране только те записи, которые превышают этот порог. Для варианта «Ниже среднего» Excel оставит записи, находящиеся ниже среднеарифметического порога.Совет
Для правильной работы автофильтра не смешивайте в одной колонке данные разного типа (текст, числа, даты и т. д.). В автофильтре все равно будет представлена только одна команда для работы с одним конкретным типом данных. Эту команду Excel выберет по преобладающим элементам. То есть по таким элементам, которых в колонке большинство.Речь идет, например, о такой ситуации. У вас есть колонка с датами. Часть из них представлена в формате «даты», а остальные — в виде текста. Если текстовых строк в колонке больше, то Excel предложит весь столбец обрабатывать текстовым автофильтром и все даты в нем сравнивать как текст. Разумеется, при этом могут возникнуть определенные проблемы.
Отбор значений даты и времени
Фильтр работы с датой и временем в Excel 2007 существенно отличается от более ранних версий этой программы. Теперь при работе с датами появилась возможность указывать диапазоны в месяцах, кварталах, годах
. И что не менее важно, формировать динамические условия с учетом текущей даты. Делаем так:1) открываем реестр, изображенный на рис. 1;
2) ставим указатель активной ячейки внутрь области данных;
3) щелчком на иконке «
Сортировка и фильтр» включаем автофильтр;4) щелкаем на значке выбора в колонке «
Дата»;5) в появившемся меню становимся на пункт «
Фильтр по дате». Раскроется окно, как на рис. 12. В нем представлены основные возможности по фильтрации дат. 
Меню состоит из 22 элементов. Все они разбиты на группы. Пункт «
равно…» позволяет выбрать записи по точному соответствию указанной дате.Пункты меню «
До…», «После…» и «Между…» открывают окно пользовательского автофильтра. В нем сразу будет установлена операция по выбору записей, которые датированы ранее указанной даты, позже указанной даты или в пределах заданного диапазона дат. Соответственно в этом окне нужно ввести одно или два значения даты.Далее в меню расположены три раздела: «
Завтра», «Сегодня», «Вчера». Они позволяют определить условие отбора записей с учетом текущей даты. Например, мы указали вариант «Вчера». Тогда сегодня, «10/01/2010», под условие фильтра попадут все записи, датированные от «09/01/2010». Открыв файл базы данных завтра, «11/01/2010», среди отобранных записей окажутся те, у которых значение поля «Дата» будет равно «10/01/2010». Это и есть динамическое условие для фильтрации дат.Ниже в меню расположены похожие условия, но для работы с неделями, а именно: «
На следующей неделе», «На этой неделе», «На прошлой неделе». Разумеется, есть возможность выбрать записи по условиям «В следующем месяце», «В этом месяце», «В прошлом месяце» и т. д.Совет
При работе с датами обратите внимание на возможность отфильтровать данные по кварталам. В меню на рис. 12 это пункты «В следующем квартале», «В этом квартале», «В прошлом квартале». Учитывая, что встроенной функции пересчета даты в номер квартала в Excel нет, такая возможность может быть очень полезной.
Поиск минимума, максимума и пустых ячеек
С помощью автофильтра удобно анализировать большие массивы чисел. Например, находить в них минимальное, максимальное, среднее значения и т. п. Вот как это сделать.
Пример 1. В базе реестра (рис. 1) я хочу найти самое крупное поступление (т. е. запись с максимальным значением в поле «
ОбщСум»). Делаем так:1) включаем автофильтр;
2) щелкаем на значке выбора в поле «
ОбщСум»;3) в меню автофильтра (рис. 8) выбираем «
Числовые фильтры», затем «Первые 10…». Появится окно, как на рис. 13; 
4) в списке слева выбираем вариант «
наибольших». Количество чисел устанавливаем равным «1». В списке справа оставляем значение «элементов списка».Нажимаем «
ОК». Excel оставит запись о поступлении от «ЧП "Матадор"» за «22.11.2009» на сумму 30769,2 грн. Это максимальное поступление.Указав в списке слева «
наименьших», легко обнаружить самые мелкие поступления. Кроме того, в данном варианте работы автофильтра можно гибко регулировать максимальное число отфильтрованных записей. Его можно ввести как конкретное значение или как процент от общего числа записей в базе данных (рис. 13).Пример 2. Проанализировать базу данных и найти в ней все записи с незаполненным полем «
Дата». Делаем так:1) включаем автофильтр;
2) щелкаем на значке выбора в поле «
Дата»;3) в меню автофильтра снимаем флажок «
(Выделить все)»;4) в этом же меню включаем флажок «
(Пустые)»;5) нажимаем на «
ОК». На экране остались только те записи, у которых не заполнено поле «Дата».Важно!
Флажок «Пустые» доступен только при условии, что в диапазоне ячеек или столбце есть хотя бы одна пустая ячейка.Автофильтр удобно применять для поиска данных, когда заранее неизвестно точное написание искомого элемента.
Пример 3. Я хочу найти в реестре предприятие, название которого похоже на «
Матадор». Точного значения я указать не могу, но тот факт, что в названии есть «Мата», помню наверняка. Делаем так:1) включаем автофильтр;
2) щелкаем на значке выбора в поле «
НаимПредпр»;3) в меню автофильтра выбираем «
Текстовые фильтры», затем «Содержит»;4) в качестве строки поиска вводим «
Мата»;5) нажимаем на «
ОК». На экране остались только записи о фирме «ЧП "Матадор"».В качестве условий сравнения при отборе текстовых строк в Excel 2007 можно использовать подстановочные знаки. Перечень таких знаков вместе с их описанием приведен в табл. 1.
Подстановочные знаки для отбора текстовых полей
Таблица 1
| Подстановочные знаки | Действия |
| 1 | 2 |
| «?» (знак вопроса) | Любой одиночный символ. Пример: условию «бар?н» соответствуют результаты «барин» и «баран» |
| «*» (звездочка) | Любое количество знаков. Пример: условию «*Excel» соответствуют результаты «Бухгалтер и Excel» и «Ms Excel» |
| «~» (тильда), за которой следует «?», «*» или «~» | Означает вопросительный знак, звездочку или тильду. Пример: условию «Calc или Excel~?» соответствует результат «Calc или Excel?» |
Пример 4
. Чтобы решить ту же задачу с помощью подстановочных знаков, делаем так:1) включаем автофильтр;
2) раскрываем список фильтрации для поля «
НаимПредпр»;3) в меню автофильтра выбираем «
Текстовые фильтры», затем «равно…»;4) в качестве строки поиска вводим «
*Мата*»;5) нажимаем «
ОК». На экране остались только записи о фирме «ЧП "Матадор"».Поиск данных с помощью автофильтра очень удобен при работе с большими таблицами. Он позволяет увидеть найденные значения не по одному, а все сразу. Недостаток этого метода в том, что он работает только со структурированными массивами, т. е. с базами данных. К таблице произвольной формы этот метод поиска неприменим.
Фильтр по нескольким колонкам
На практике часто приходится накладывать фильтр на несколько колонок базы данных. Посмотрим, как это работает в Excel 2007.
Пример 5. Выберем из базы реестра (рис. 1) записи о поступлениях за период с «
01/11/2009» по «10/11/2009», которые превышают 5000 грн. Делаем так:1) включаем автофильтр;
2) раскрываем список фильтрации для поля «
Дата»;3) в меню автофильтра выбираем «
Фильтры по дате», затем «между…». Появится окно «Пользовательский фильтр» , в нем есть два поля для дат;4) напротив условия «
после или равно» вводим значение «01/11/2009»;5) напротив условия «
до или равно» вводим значение по «10/01/2009»;6) нажимаем «
ОК». Теперь на экране видны только записи за первую декаду ноября 2009 года;7) щелкаем на значке выбора в поле «
ОбщСум»;8) в меню автофильтра (рис. 8) выбираем «
Числовые фильтры», затем «больше…». Появится окно, как на рис. 13;9) в списке слева уже стоит нужная нам операция («
больше…»). В поле справа вводим значение «5000»;10) нажимаем на «
ОК». Видимая часть базы данных сократилась до пяти строк. Все они относятся к первой декаде ноября, и сумма в колонке «ОбщСум» превышает 5000 грн.Важно!
Несколько условий фильтра для разных колонок базы данных взаимодействуют по условиям «И».И последнее. Не все условия фильтрации взаимодействую одинаково
. Попробуйте, например, такой вариант:1) выберите записи из базы реестра за период с «
01/11/2009» по «10/11/2009»;2) в колонке «
ОбщСум» установите фильтр «Первые 10…» и выберите с его помощью пять максимальных поступлений.Вполне возможно, что отфильтрованная база окажется пустой. На данных нашего примера я получил именно такой результат. Причина проста. Excel выбирает максимальные поступления со всех значений базы данных! Работу фильтра по датам он при этом не учитывает. Оказалось, что пять максимальных поступлений были вне заданного временно
го интервала, что и привело к такому результату.
Фильтр по форматам
В программе Excel 2007 появилась возможность указывать в качестве условий фильтрации параметры форматирования ячеек. Если вы привыкли использовать цвет для выделения смысловых фрагментов в базе данных, такая возможность окажется очень полезной.
Пример 6. В базе данных реестра я отметил желтым фоном записи о контрагентах, по которым есть дебиторская задолженность. Теперь я хочу выбрать эти записи с помощью автофильтра и посмотреть на них. Делаем так:
1) включаем автофильтр;
2) раскрываем список фильтрации для любого поля. Например, для колонки «
ОбщСум»;3) в меню автофильтра становимся на строку «
Фильтр по цвету». Появится меню, как на рис. 14. В верхней части этого меню предлагается выбрать способ заливки ячеек, которые мы хотим выбрать. В меню всего два варианта: «Нет заливки» и желтый цвет. Если бы я использовал много цветов для оформления фона ячеек, все они были бы видны в этом меню. Вторая группа параметров меню на рис. 14 относится к цвету шрифта. Эта возможность доступна потому, что в поле «ОбщСум» есть отрицательные значения. А формат ячеек в этой колонке установлен с параметром «отрицательные красным»; 
4) щелкаем левой кнопкой на желтом прямоугольнике в группе «
Фильтр по цвету ячейки». На экране остались только записи, отмеченные желтым фоном.Важно!
В Excel 2007 можно отфильтровать записи по цвету ячеек , по цвету шрифта или по набору значков, созданных с помощью условного форматирования.
Фильтр по образцу
В Excel 2007 таблицу можно быстро отфильтровать «по образцу», используя для этого характеристики активной ячейки. Чтобы воспользоваться такой возможностью, нужно задействовать контекстное меню правой кнопки мыши.
Пример 7
. Из базы данных реестра (рис. 1) я хочу выбрать записи за «20/11/2009». Делаем так:1) в колонке «
Дата» находим значение «20/11/2009»;2) щелкаем на соответствующей ячейке правой кнопкой мыши. Появится контекстное меню, как на рис. 15;
3) в этом меню ставим указатель мыши на пункт «
Фильтр». Раскроется дополнительное меню (рис. 15);4) в нем выбираем вариант «
Фильтр по значению выделенной ячейки». На экране останутся записи за «20/11/2009».Через контекстное меню можно отфильтровать также записи по цвету фона выделенной ячейки, цвету шрифта и по значку условного форматирования.
Автофильтр и статусная строка
Автофильтр удобно использовать совместно со статусной строкой. На мой взгляд, это незаменимый помощник бухгалтера при анализе больших таблиц. Он чрезвычайно полезен, когда требуется быстро получить контрольное значение по набору данных, при этом никаких расчетов в таблице делать не нужно.
Вы, конечно, помните, что в строке состояния отображается сумма чисел из выделенного блока ячеек. Кроме того, воспользовавшись правой кнопкой мыши, здесь можно показать и другие значения — среднее арифметическое, количество чисел, максимум, минимум и т. д. Информация в строке состояния выводится с учетом действия фильтра. Если вы захотели просуммировать фрагмент базы данных, советую сделать так:
1) включить автофильтр;
2) отобрать часть записей из базы данных;
3) щелчком мыши выделить колонку, значения в которой нужно просуммировать;
4) в строке состояния прочитать результат.
Автофильтр и сортировка
Может, в это трудно поверить, но сортировка данных работает с учетом действия автофильтра. Чтобы убедиться в этом, сделайте так:
1) откройте базу данных. Например, как показано на рис. 1;
2) с помощью автофильтра выберите данные за определенный период времени. Я отобрал часть записей реестра с «
01/11/2009» по «15/11/2009»;3) не снимая автофильтра, отсортируйте базу по полю «
ОбщСум»;4) выделите все отфильтрованные строки, присвойте им другой цвет фона (например, светло-желтый);
5) отключите автофильтр и проанализируйте полученный результат. База реестра будет отсортирована фрагментарно
. Все строки, помеченные светло-желтым цветом, в базе будут расположены по возрастанию поля «ОбщСум». Остальные записи в базе не поменяют свой порядок.Важно!
При включенном автофильтре сортировка действует только на «видимые» строки базы данных. Остальная часть базы остается неизменной.
Как работать с автофильтром без ошибок
И в завершение хочу дать несколько советов относительно работы с автофильтром. Думаю, это поможет избежать различного рода ошибок, которые иногда трудно заметить и вовремя предупредить.
1. В пределах одной колонки базы заносите однотипную информацию. Не смешивайте в одном столбце текст, числа, даты и т. д. Автофильтр может «запутаться», неправильно отобрать записи, и вы получите ошибочный результат. Поскольку автофильтр обычно применяют к большим таблицам, заметить такую ошибку бывает очень трудно.
2. Следите, чтобы в базе данных не было пустых строк
. Это общее требование при работе с инструментами обработки данных. Подобно сортировке, перед наложением автофильтра Excel анализирует структуру рабочего листа. Он пытается автоматически найти базу данных, выяснить ее координаты и затем наложить на нее фильтр. Если в базе есть пустые строки, Excel обязательно ошибется и применит автофильтр не ко всей базе, а только к ее фрагменту.3. Если вы не уверены, что в базе нет пустых строк, указывайте область для автофильтра вручную
.4. Помните, что автофильтр включает отображение скрытых строк на рабочем листе
. Это нужно учитывать, если пользуетесь информацией из строки состояния MS Excel. В этой строке Excel показывает результат обработки только видимых ячеек рабочего листа. Если вы по каким-то причинам скрыли часть строк таблицы, то при суммировании данных, при определении среднего и т. п. эти строки учитываться не будут. Но как только вы применили автофильтр, скрытые ячейки перейдут в разряд «явных» и сумма изменится.Вот и все, что я хотел сказать относительно работы с автофильтром. Надеюсь, что материал этой статьи будет вам полезен и поможет продуктивно пользоваться этим замечательным бухгалтерским инструментом в вашей работе. А мы в следующий раз посмотрим, что нам предложит расширенный фильтр Excel 2007, и познакомимся с режимом подведения итогов.
Жду ваших писем, вопросов и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
