Выбрать из списка нужные данные можно при помощи фильтрации, то есть путем сокрытия всех строк списка, кроме тех, которые удовлетворяют заданным критериям. Чтобы воспользоваться функцией фильтрации, необходимо установить табличный курсор на одну из ячеек заголовка списка (в нашей таблице это диапазон А1:U11) и вызвать команду Данные/Фильтр/Автофильтр . После ее активизации в правом нижнем углу каждой ячейки заголовка появится маленький квадратик со стрелкой раскрывающегося списка.
Рассмотрим приемы работы с автофильтром на следующем примере. Давайте определим, сколько представителей сильного пола работает на предприятии. Нажмите кнопку фильтрации, расположенную в ячейке с заголовком Пол, и в открывшемся списке выберите букву М (мужчина). В строке состояния появится сообщение Фильтр: отбор (рис. 4.20). Все строки, которые не удовлетворяют заданному критерию, будут скрыты. Стрелка на кнопке списка окрасится в синий цвет, указывая на то, что для данного поля включен автофильтр.
Рис. 4.20. Использование автофильтра для отбора записей по признаку "М" (мужчина)
Если же требуется уточнить, сколько среди этих мужчин начальников, нажмите также кнопку автофильтра в ячейке Должность и выберите в соответствующем ей списке слово Начальник. В строке состояния появится сообщение о том, сколько строк удовлетворяет заданному критерию: Найдено записей: 2 из 10 (то есть ответ будет дан сразу же). Результат изображен на рис. 4.21.
Чтобы отменить фильтрацию по определенному столбцу, достаточно открыть список автофильтра в этом столбце и выбрать пункт Все. Однако если функция фильтрации задана для нескольких столбцов, вам придется повторить эту операцию несколько раз. В этом случае лучше воспользоваться командой Данные/Фильтр/Отобразить все .
Рис. 4.21.
Рабочий лист после фильтрации списка сотрудников по критерию "мужчина - начальник"
Функция фильтрации будет работать как следует, если вы будете внимательными при занесении данных. В частности, нужно следить за тем, чтобы в начале и в конце текстовых данных не было лишних пробелов. На экране они не заметны, но могут привести к ошибочным результатам, а на их выявление тратится много времени.
При фильтрации выполняется отбор данных, точно отвечающих заданному критерию. Поэтому, если вместо слова "Начальник" с столбце встречается слово "Начальник_", то есть с пробелом в конце, Excel воспринимает эти значения как разные. Чтобы избавиться от несоответствий такого рода, скопируйте в буфер обмена ячейку со словом "Начальник", активизируйте фильтр для выборки по признаку "Начальник_" и замените неправильные значения содержимым буфера.
ОБРАБОТКА ДАННЫХ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ
Представление электронной таблицы в виде списка
Список или база данных – это один из способов организации данных на рабочем листе. Он создается как помеченный ряд, состоящий из строк с однотипными данными. Например, перечень сотрудников некоторого отдела, в котором столбцы имеют следующие имена: ФИО, Всего начислено, Всего удержано, Сумма к выдаче.
Данные, организованные в список, называются базой данных . При этом строки таблицы – записи базы данных, а столбцы – поля записей. Чтобы превратить таблицу Excel в список, необходимо присвоить столбцам имена, которые будут использоваться как имена полей записей. При создании базы данных необходимо выполнять следующие правила:
1. На одном рабочем листе не следует размещать более одного списка, т.к. некоторые операции, например фильтрация, работают в определенный момент только с одним списком.
2. Следует отделять список данных от других данных рабочего листа хотя бы одним незаполненным столбцом или одной незаполненной строкой. Это поможет автоматически выделить список при выполнении фильтрации или при сортировке данных.
3. В заголовках столбцов списка данных не следует объединять ячейки.
4. Имена столбцов должны располагаться в первой строке списка, т.к. Excel всегда первую строку рассматривает как заголовки столбцов.
5. Для того чтобы отделить заголовки столбцов от основных данных можно использовать шрифт, выравнивание, формат, отличные от тех, которыми отражены основные данные таблицы. Нельзя оставлять пустую строку между заголовками столбцов и основными данными, не рекомендуется для отделения использовать пунктирную линию.
Сортировка данных в таблице
Сортировка данных в Excel имеет положительные и отрицательные стороны. Положительные – универсальность операции и удобный пользовательский интерфейс. Отрицательные: легкость, с которой таблица может быть превращена в набор беспорядочных данных.
Для проведения сортировки необходимо выделить ячейку таблицы и выбрать команду Данные, Сортировка. Excel автоматически выделит весь список. Если в первой строке находятся имена полей, то они не будут включены в сортировку. Надо иметь в виду, что итоговая строка исходного списка также будет включена в сортировку, поэтому иногда бывает целесообразнее вручную выделить диапазон данных для сортировки.
В трех полях ввода окна Сортировка можно задать ключи, по которым данные будут сортироваться. Можно также задать параметры: установить порядок сортировки по первому ключу (обычный или определяемый пользователем), а также направление сортировки (по возрастанию или по убыванию).
Для быстрой сортировки можно воспользоваться кнопками «Сортировка по возрастанию» и «Сортировка по убыванию» стандартной панели инструментов (в этом случае ключом сортировки является столбец с текущей ячейкой).
Если в результате сортировки вы не добились ожидаемого результата, отмените ее. Для этого щелкните на кнопке «Отменить» стандартной панели инструментов.
Фильтрация данных в списке
Фильтр представляет собой конструкцию, предназначенную для отбора тех строк таблицы, которые удовлетворяют заданному условию, и временного скрытия остальных строк. Основой фильтра является список, который содержит условия отбора строк. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью Автофильтра. Более сложные запросы к базе данных можно реализовать с помощью команды Расширенный фильтр.
Автофильтр
С помощью функции Автофильтр можно вывести на экран только определенные записи. Для этого необходимо сначала выделить список обязательно с заголовками полей.
Кнопки, раскрывающие список фильтра, создаются в первой строке диапазона. Если в столбце встречаются незаполненные ячейки, список дополняется категориями «пустые» и «непустые». В любом случае верхняя строка диапазона не рассматривается как объект для фильтрации и в список не включается. Даже если часть строк не попала в выделение, принцип фильтрации будет распространяться и на них, включая итоговую строку. После того, как диапазон выделен выбираем команду Данные/ Фильтр / Автофильтр .
После этой команды Excel помещает раскрывающиеся списки непосредственно в имена столбцов списка. Элемент столбца, который выделен в раскрывающемся списке, называют критерием фильтра. Можно продолжить фильтрацию с помощью критерия из другого столбца.
Чтобы удалить критерий фильтрации для отдельного столбца, надо выбрать параметр «Все» в раскрывающемся списке. Чтобы отобразить все скрытые в списке строки, надо выбрать Данные/ Фильтр/Отобразить все .
Пользователь может сформировать условие отбора строк таблицы для каждого столбца. Пользовательские критерии отбора записей могут состоять из одного или двух логических выражений, соединяемых операторами И (ИЛИ). Использование оператора И предполагает, что условие будут выполнено, если одновременно будут выполнены обе его части. Использование оператора ИЛИ допускает выполнение хотя бы одной части условия.
Чтобы задать пользовательский критерий надо выбрать параметр Условие в раскрывающемся списке, а затем, в появившемся окне «Пользовательский автофильтр» задать необходимые критерии: Показывать только те строки, значения которых… – указать нужные критерии.
Для удаления Автофильтра необходимо повторно выбрать команду Данные/Фильтр/Автофильтр.
Первые 10. Этот метод имеет смысл только для полей с числовыми данными, в том числе и с датами. Чтобы воспользоваться этим методом, нужно выбрать в списке вариант «Первые 10…». В появившемся диалоговом окне следует указать, сколько наибольших или наименьших элементов следует отображать. Например, в БД имеется поле «Оклад» для хранения размера оклада сотрудника. Организация имеет возможность оказать материальную помощь 5 сотрудникам с наименьшим окладом. Для решения этой задачи можно воспользоваться методом отбор «Первые 10…» для поля «Оклад»: задать количество отображаемых записей с наименьшим значением.
Сложная фильтрация (расширенный фильтр)
Для фильтрации списка по сложному критерию, а также для получения части исходного списка по нескольким заданным столбцам используется команда Расширенный фильтр меню Данные. Ее отличие от команды Автофильтр состоит в том, что кроме перечисленных возможностей, отфильтрованные записи можно перенести в другое место рабочего листа Excel, не испортив исходный список. В расширенном фильтре критерий фильтрации создается как таблица, которая может храниться в любом месте рабочей книги, или даже в другом файле. К таблице-критерию применимы все операции обработки данных Excel.
Расширенный фильтр позволяет:
1. сразу копировать отфильтрованные записи в другое место рабочего листа.
2. сохраняет критерий отбора для дальнейшего использования (это полезно, когда в список вносятся новые данные, и нужно периодически извлекать из него информацию в соответствии с критерием).
3. показывать в отфильтрованных записях не все столбцы таблицы, а только указанные
4. объединять оператором ИЛИ условия для разных столбцов
5. для одного столбца объединять операторами И, ИЛИ более двух условий.
6. создавать вычисляемые критерии.
Чтобы использовать команду Расширенный фильтр, надо сначала создать таблицу критериев, которую желательно разместить на том же рабочем листе, что и исходный список, но так, чтобы она не была скрыта во время фильтрации. Для формирования таблицы критериев необходимо для обеспечения идентичности текстовых полей скопировать имена полей списка в ту часть рабочего листа, где будет располагаться таблица критериев. При этом число строк в этой таблице определяется только количеством критериев поиска. Однако включение пустых строк в таблицу критериев недопустимо, поскольку в этом случае будут найдены все записи списка.
Кроме таблицы критериев, для команды Расширенный фильтр надо определить вид выходного документа. Это означает, что следует скопировать в свободное место рабочего листа имена тех полей списка, которые определяют вид выходного документа. Количество строк в выходном документе Excel определит сам.
Таким образом, для выполнения команды Расширенный фильтр следует:
· сформировать в свободном месте рабочего листа таблицу критериев
· сформировать шапку выходного документа
· выделить область списка в исходном документе
В области Обработка надо указать, будет ли фильтрация выполняться на месте или выходные данные будут перенесены в другую область рабочего листа. Если выбран режим по умолчанию «фильтровать список на месте», то Excel скроет все строки исходного списка, не удовлетворяющие заданным критериям. Если установлен переключатель «Только уникальные записи», то повторяющиеся строки исходного списка не будут показаны в области выходных данных.
Задание условий с использованием логической операции ИЛИ:
Чтобы задать условия в диапазоне критериев логической операцией ИЛИ, нужно эти условия расположить на разных строках: например:
1) Отобразить записи о менеджерах с фамилией «Кислов» или о менеджерах, продающих «Хлеб»:
2) Получить информацию о клиентах, чьи фамилии начинаются на букву В и Т:
Задание условий с использованием логической операции И:
Чтобы задать условия в диапазоне критериев логической операцией И, нужно эти условия расположить на одной строке: например:
1) Найти информацию о менеджерах с фамилией «Петрова», продавших товар более чем на 50 единиц:
2) Найти информацию о товарах, цена которых больше 30 и меньше 80 рублей.
КРАТКАЯ СПРАВКА
Общие сведения
Фильтрация данных в списке - это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные, Фильтр. Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные, Форма.Автофильтрация
Команда Данные, Фильтр, Автофильтр для каждого столбца строит список значений, и который используется для задания условий фильтрации (рис. 3.36). В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком возможных критериев выбора. Рис. 3.36. Список с автофильтромПо отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:- все - выбираются все записи без ограничений;
- первые 10 - данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис.3.37) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
- значения - будут выбраны только те записи, которые в данном столбце содержат указанное значение;
- условие - выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис.3.3 8).
- оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;
- значение, которое может выбираться из списка или содержать шаблонные символы *,?.
Расширенный фильтр
Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:- критерий сравнения;
- вычисляемый критерий.
- состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;
- имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;
- ниже имен столбцов располагаются критерии сравнения типа:
- точного значения;
- значения, формируемого с помощью операторов отношения;
- шаблона значения, включающего символы * и (или) ?.
| Номер группы | Код предмета | Оценка |
| 133 | п1 | 4 |
| 133 | п1 | 5 |
| Номер группы | Оценка1 |
| 133 | =ИЛИ(G2<=CP3HAЧ($G$2:$G$I7);G2=5) |
- присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;
- установить курсор в ячейку ввода;
- вызвать Мастер функций - команда Вставка, Функция, выбор категории -Логические и выбор функции - ИЛИ;
- ввод параметров функции ИЛИ:
Фильтрация с помощью формы данных
ППП Excel 97 позволяет работать с отдельными записями списка с помощью экранной формы (рис.3,40). Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка,При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей - названия столбцов списка.Для просмотра записей используется полоса прокрутки либо кнопки <Назад> или <Далее>, выводится индикатор номера записи. При просмотре записей возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи с помощью кнопки <Вернуть> можно отменить.Для создания новой записи нажимаете) кнопка <Добавить>, выполняется заполнение пустых полей экранной формы; для переходи между полями формы используются курсор мыши либо клавиша <ТаЬ>. При повторном нажатии кнопки <Добавить> сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка <Удалить>, Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.С помощью экранной формы задаются критерии сравнения. Для этого нажимаете» кнопка <Критерии>, форма очищается для т да условий поиска в полях формы с помои кнопки <0чистить>, а название кнопки
ЗАДАНИЕ 1
Выберите данные из списка по критерию отбора, используя Автофильтр.- Проведите подготовительную работу - переименуйте новый лист на Автофильтр (см рис, 3.35).
- Выберите из списка данные, используя критерий:
- для преподавателя - а1 выбрать сведения о сдаче экзамена на положительную оценку,
- вид занятий - л.
- Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.
- Отмените результат автофильтрации.
- Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.
ТЕХНОЛОГИЯ РАБОТЫ
- Проведите подготовительную работу:
- переименуйте ЛистЗ -Автофильтр
- выделите блок ячеек исходного списка, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Автофильтр.
- Выберите из списка данные, используя критерий - для преподавателя - а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий - л. Для этого:
- Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные, Фильтр, Автофильтр.
ЗАДАНИЕ 2
Выберите данные из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию. Для этого:1.Проведите подготовительную работу - переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных {см. рис.3.35).2. Скопируйте имена полей списка в другую область на том же листе.3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов! студентами группы 133 по предмету п1 на оценки 4 или 5.4.Произведите фильтрацию записей на том же листе.5.Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.6.Сформируйте в области условий отбора Вычисляемый критерий - для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий - л; результат отбора поместите на новый рабочий лист.7.Произведите фильтрацию записей на новом листе.8.Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.ТЕХНОЛОГИЯ РАБОТЫ
1. Проведите подготовительную работу:- переименуйте Лист4 - Расширенный фильтр
- Расширенный фильтр.
- в столбец Номер группы - точное значение - 133;
- в столбец Код предмета - точное значения - п1;
- в столбец Оценка -условие->3
- выполните команду Данные, Фильтр, Расширенный фильтр;
- в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например;
- нажмите кнопку <ОК>.
- в столбец Вид занятия введите точное значения - букву л;
- переименуйте в области критерия столбец Оценка, например, на имя Оценка 2:
- в столбец Оценка 1 введитевычисляемый критерий, например, вида
- установите курсор в область списка (базы данных);
- выполните команду Данные, Фильтр, Расширенный фильтр;
- в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например:
- нажмите кнопку <ОК>.
ЗАДАНИЕ 3
Используя Форму,
выберите данные
из списка.
1.
Проведите подготовительную
работу - переименуйте
новый лист на
Форма
и скопируйте
на него исходную
базу данных (см.
рис.3,35).2.
Просмотрите
записи списка
с помощью формы
данных, добавьте
новые.3.
Сформируйтеусловие
отбора с помощью
формы данных
- для преподавателя
выбрать сведения
о сдаче студентами
экзамена на положительную
оценку, вид занятий
- л.4. Просмотрите
отобранные записи.5. Сформируйте
собственные
условия отбора
записей и просмотрите
их,
ТЕХНОЛОГИЯ РАБОТЫ
1. Проведите подготовительную работу:- переименуйте Лист5 - Форма;
- выделите блок ячеек исходного списка, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Форма;
- установите курсор в область списка и выполните команду Данные, Форма,
- нажмите кнопку <Критерии>, название которой поменяется на <Правка>;
- в пустых строках имен полей списка введите критерии:
- в строку Таб № препод. введите а1
- в строку Вид занятия введите л;
- в строку Оценка введите условие > 2
Фильтрация данных в списке - это выбор данных по заданному критерию, т.е. это операция, которая позволяет выделить нужные данные среди имеющихся.
С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью простой команды - «Автофильтрация». Более сложные запросы к базе данных можно реализовать при помощи команды «Расширенный фильтр».
Автофильтрация
Для того, чтобы произвести автофильтрацию, необходимо изначально скопировать на новый лист «Автофильтрация» исходную базу данных с листа «Подсчет данных по формулам». Затем установим курсор в область списка и выполним команду «Данные» - «Фильтр» - «Автофильтр». По этой команде Excel помещает раскрывающиеся списки непосредственно в имена столбцов списка. Щелкнув по стрелке, можно ознакомиться со списком возможных критериев выбора. Если кнопка была использована для назначения фильтра, то стрелка окрашивается в синий цвет. Существуют следующие варианты списка критериев:
· «Все» - выбираются все записи;
· «Первые 10» - в диалоговом окне «Наложение условия по списку» выбрать определенное кол-во наименьших или наибольших элементов списка, которое необходимо отобразить;
· «Значения» - будут выбраны только те записи, которые в данном столбце создают указанное значение;
· «Условие» - выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский автофильтр»;
· «Пустые» - предъявляются строки, не содержащие данные в колонке;
· «Непустые» - предъявляются только те записи, которые содержат непустые строки в колонке.
В данном случае необходимо сформировать следующие условия для проведения операции «Автофильтрация»: для поля «Льготы» нужно задать значение «Ветеран или Инвалид», а для поля «Количество членов семьи» нужно задать условие - «Больше или равно 3»». В соответствии с тем, что одновременно установлены фильтры в двух столбцах, то фильтрация записей будет выполнена по двум условиям одновременно, то есть в итоге будут отобрана льготы Ветеран и Инвалид, количество членов семьи которых больше или равно 3. В итоге были найдены квартиросъемщики, которые удовлетворяют вышеизложенным условиям. Данный результат представлен на рисунке Таблица 4 «Автофильтрация».
Расширенный фильтр
Фильтрация с помощью расширенного фильтра осуществляется с помощью команды: «Данные» - «Фильтр» - «Расширенный фильтр».
Для использования команды «Расширенный фильтр», надо сначала создать таблицу критериев, которую далее разместим на том же рабочем листе «Расширенный фильтр», что и исходная таблица «Подсчет данных по формулам», но так, чтобы не скрывать лист во время фильтрации.
В «Расширенном фильтре» также как и в «Автофильтре» существует несколько вариантов видов критерия, такие как:
Критерий сравнения включает операции следующего типа:
· точного значения;
· значения, формируемого с помощью операторов отношения;
· шаблона значения, включающего символы или
Множественный критерий - критерий, формируемый в нескольких столбцах.
· Если критерии указываются в каждом столбце на одной строке, то они считаются связанными условием И.
· Если критерии записаны в нескольких строках, то они считаются связанными условием ИЛИ.
Вычисляемый критерий - представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение «ИСТИНА» или «ЛОЖЬ».
Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.
Для этой цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не подходящие по условию. Автофильтр выполняет простейшие операции. У расширенного фильтра гораздо больше возможностей.
Автофильтр и расширенный фильтр в Excel
Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.
Если отформатировать диапазон данных как таблицу или объявить списком, то автоматический фильтр будет добавлен сразу.
Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:
Сразу видим результат:
Особенности работы инструмента:
- Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
- Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
- Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.
У расширенного фильтра гораздо больше возможностей:
- Можно задать столько условий для фильтрации, сколько нужно.
- Критерии выбора данных – на виду.
- С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.
Как сделать расширенный фильтр в Excel
Готовый пример – как использовать расширенный фильтр в Excel:
В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».
Как пользоваться расширенным фильтром в Excel
Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.
Заполняем меню расширенного фильтра:
Получаем таблицу с отобранными по заданному критерию строками:
Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.
Заполняем параметры фильтрации. Нажимаем ОК.
Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.
Применим инструмент «Расширенный фильтр»:
Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.
Основные правила:
- Результат формулы – это критерий отбора.
- Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
- Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
- Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.
Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.
Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).
В таблице остались только те строки, где значения в столбце «Количество» выше среднего.
Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».
Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.