Расширенный фильтр в excel

Использование расширенного фильтра в Excel

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

Шаг 1: Создание таблицы с условиями отбора

Чтобы установить расширенный фильтр, прежде всего требуется создать дополнительную таблицу с условиями отбора. Ее шапка в точности такая, как у основной, которую мы, собственно, и будем фильтровать. Для примера мы разместили дополнительную таблицу над основной и окрасили ее ячейки в оранжевый цвет. Хотя размещать ее можно в любом свободном месте и даже на другом листе.

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

Шаг 2: Запуск расширенного фильтра

Только после того как дополнительная таблица создана, можно переходить к запуску расширенного фильтра.

1. Переходим на вкладку «Данные» и на ленте в блоке инструментов «Сортировка и фильтр» жмем по «Дополнительно».

Открывается окно расширенного фильтра. Как видим, существует два режима использования этого инструмента: «Фильтровать список на месте» и «Скопировать результаты в другое место». В первом случае фильтрация будет производиться прямо в исходной таблице, а во втором — отдельно в диапазоне ячеек, которые вы укажете сами.

В поле «Исходный диапазон» нужно указать диапазон ячеек исходной таблицы. Это можно сделать вручную, вбив координаты с клавиатуры, либо выделив нужный диапазон ячеек с помощью мышки. В поле «Диапазон условий» нужно аналогичным образом вписать диапазон шапки дополнительной таблицы и той строки, которая содержит условия. При этом следует обратить внимание, чтобы в данный диапазон не попали пустые строки, иначе ничего не получится. По завершении настроек нажмите «OK».

В исходной таблице остались только те значения, которые мы решили отфильтровать.

Если был выбран вариант с выводом результата в другое место, в поле «Поместить результат в диапазон» следует указать диапазон ячеек, в которые будут выводиться отфильтрованные данные. Можно указать и одну ячейку. В этом случае она станет верхней левой ячейкой новой таблицы. Подтверждаем выбор кнопкой «OK».

После этого действия исходная таблица осталась без изменений, а отфильтрованные данные выводятся в отдельную таблицу.

Чтобы сбросить фильтр при использовании построения списка на месте, на ленте в блоке инструментов «Сортировка и фильтр» щелкните по кнопке «Очистить».

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Как применить

Теперь давайте рассмотрим, как использовать расширенный фильтр в Excel. Для начала, нужно создать диапазон условий – делается это путем копирования всех заголовков столбцов, в другое место листа.

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

Условия задаем следующим образом. Оставим, для примера, всех девочек, которые учатся в 9 классе. Заполняем нужные ячейки в диапазоне условий. Затем выделяем любую ячейку в основной таблице, переходим на вкладку «Данные» и нажимаем в группе «Сортировка и фильтр» на кнопку «Дополнительно» .

Откроется диалоговое окно «Расширенный фильтр» . В нем выберите маркером, где отобразить результат, в этой же таблице или сделать ее в другом месте. В качестве «Исходного диапазона» выбираем наши ячейки А6:Е31 . «Диапазон условий» – это наши заголовки с условиями А1:Е2 . Нажмите «ОК» .

Очень важно правильно задать диапазон условий. В примере это А1:Е2

Если нужно будет добавить еще одно условие, он станет А1:Е3 , и так далее. В противном случае ничего работать не будет.

В диапазоне условий, данные для столбцов, которые введены в одну строку, воспринимаются как логическое «И» . Данные на разных строках воспринимаются как логическое «ИЛИ» . В примере, мы оставили всех девочек, и из них выбрали тех, кто учится в 9 классе. Если во второй строке записать «девочка» – «>170» , то будут отобраны из таблицы еще и девочки, рост которых больше 170 см. При этом они могут учиться в других классах – это логическое «ИЛИ» .

Расширенный фильтр и немного магии

У подавляющего большинства пользователей Excel при слове «фильтрация данных» в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :

Такой фильтр — штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой «доработкой напильником» (по традиции).

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы — это будет диапазон с условиями (выделен для наглядности желтым):

Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский «Ашан» в III квартале, то условия будут выглядеть так:

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data — Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

Обратите внимание, что диапазон условий нельзя выделять «с запасом», т.е. нельзя выделять лишние пустые желтые строки, т.к

пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка — как просьба вывести все данные без разбора.

Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:

Добавляем макрос

«Ну и где же тут удобство?» — спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но «все меняется, когда приходят они » — макросы!

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот такой код:

Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

Так все гораздо лучше, правда?

Реализация сложных запросов

Теперь, когда все фильтруется «на лету», можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

Отбор по столбцу с текстовыми значениями

Если столбец содержит текстовые значения, то меню фильтра будет иметь следующий вид.

Сначала отобразим только те строки, которые содержат в столбце Товар слово Гвозди ( Регистр букв не имеет значения). Сделаем это 2-мя способами.

Способ 1. Нажмем стрелку раскрытия фильтра. Затем нажмем на значение (Выделить все) – снимутся все галочки. Затем установите галочку напротив значения гвозди .

Нажмите ОК. В итоге отобразятся только те строки, которые содержат в столбце Товар значение Гвозди (т.е. строки со значениями Лучшие Гвозди или Гвозди 10 мм отобраны не будут). Понять, что применен фильтр очень просто: стрелка раскрытия фильтра изменит свой вид (на ней появится маленькая воронка), а номера отобранных строк станут синими. Если подвести курсор к стрелке раскрытия фильтра столбца, в котором используется фильтрация, отобразится всплывающая подсказка с фильтром, который используется в данном столбце, например, Товар : «Равно Гвозди». В строке состояния (внизу листа) отображается информация о примененном фильтре: «Найдено записей: 13 из 76».

Снять примененный фильтр можно несколькими способами:

• Нажмите стрелку раскрытия фильтра. Выберите пункт Снять фильтр с “Товар” или;
• Нажмите стрелку раскрытия фильтра, затем нажмите на значение (Выделить все) или;
• Выберите команду Очистить ( Данные/ Сортировка и фильтр/ Очистить ) или;
• Выберите команду Очистить , но в другом меню ( Главная/ Редактирование/ Сортировка и фильтр/ Очистить ) или;
• Нажмите сочетание клавиш CTRL+SHIFT+L (должна быть выделена любая ячейка таблицы). При этом фильтр будет выключен.

Способ 2. Нажмем стрелку раскрытия фильтра. Выберем Текстовый фильтр Равно.. . Появится диалоговое окно, введем значение гвозди или выберем его из выпадающего списка.

В результате получим тот же результат.

Примечание: Если в столбце содержится и текстовые значения и числа (пример “неправильной” таблицы), то MS EXCEL будет отображать меню Числовые фильтры только в том случае, если количество чисел в столбце больше чем текстовых значений. В противном случае будут отображено меню Текстовые фильтры .

Теперь используя Способ 2 рассмотрим настройку других текстовых фильтров. Например, установим фильтр для отбора строк, которые содержат в столбце Товар значения начинающиеся со слова Гвозди . В этом случае будут строки со значениями Гвозди и Гвозди 10 мм.

Для этого нажмем стрелку раскрытия фильтра. Выберем Текстовый фильтр Начинается с.. .

Введем значение Гвозди, в результате получим:

Аналогично можно настроить фильтр для отображения строк, у которых в столбце Товар значения заканчиваются на >;>содержат или не содержат определенные значения.

Более того, в диалоговом окне Настраиваемый фильтр возможно использование Подстановочных знаков , которые могут сделать настройку фильтра очень гибкой. Но, как правило, сложные критерии фильтра не используются, т.к. их невозможно сохранить, в отличие от Расширенного фильтра . Единственное, что можно сделать – это использовать отмену последнего действия ( CTRL+Z ), который запоминает настройки фильтра. Например, применив последовательно 3 условия фильтрации можно используя комбинации CTRL+Z и CTRL+Y отменять и заново применять условия фильтрации (не выполняйте при этом никаких других действий кроме настройки фильтра!).

СОВЕТ : Т.к. условия отбора записей (настройки автофильтра) невозможно сохранить, то чтобы сравнить условия фильтрации одной и той же таблицы в разных случаях, скопируйте лист с исходной таблицей, затем примените нужные фильтры для оригинала таблицы и ее копии. Для сравнения результатов фильтрации используйте 2 окна (для каждой из таблиц). Для этого создайте новое окно ( Вид/ Окно/ Новое окно ), перейдите в новый лист, затем выберите требуемый вид отображения окон ( Вид/ Окно/ Упорядочить все/ Рядом ).

Использование фильтра

Применим «Числовой…» к столбцу «Цена» . Кликаем на кнопку в верхней ячейке и выбираем соответствующий пункт из меню. Из выпадающего списка можно выбрать условие, которое нужно применить к данным. Например, отобразим все товары, цена которых ниже «25» . Выбираем «меньше».

В соответствующем поле вписываем нужное значение. Для фильтрации можно применять несколько условий, используя логическое «И» и «ИЛИ» . При использовании «И» – должны соблюдаться оба условия, при использовании «ИЛИ» – одно из заданных. Например, можно задать: «меньше» – «25» – «И» – «больше» – «55» . Таким образом, мы исключим товары, цена которых находится в диапазоне от 25 до 55.

В примере у меня получилось так. Здесь отображены все данные с «Ценой» ниже 25.

«Текстовый фильтр» в таблице примера, можно применить к столбику «Название продукта» . Нажимаем на кнопочку со стрелкой вверху и выбираем из меню одноименный пункт. В открывшемся выпадающем списке, для примера используем «начинается с» .

Оставим в таблице продукты, которые начинаются с «ка» . В следующем окне, в поле пишем: «ка*» . Нажимаем «ОК» .

«*» в слове, заменяет последовательность знаков. Например, если задать условие «содержит» – «с*л» , останутся слова: стол, стул, сокол и так далее. «?» заменит любой знак. Например, «б?тон» – батон, бутон, бетон. Если нужно оставить слова, состоящие из 5 букв, напишите «. » .

Вот так я оставила нужные «Названия продуктов» .

По цвету ячейки

Фильтр можно настроить по цвету текста или по цвету ячейки.

Сделаем «Фильтр по цвету» ячейки для столбика «Название продукта» . Кликаем по кнопочке со стрелкой и выбираем из меню одноименный пункт. Выберем красный цвет.

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

По цвету текста

Такой вариант применим к столбцу «Категория» . Оставим только фрукты. Снова выбираем красный цвет.

Теперь в используемом примере отображены только фрукты красного цвета.

Фильтры в Эксель помогут Вам в работе с большими таблицами. Основные моменты, как его сделать и как с ним работать, мы рассмотрели. Подбирайте необходимые условия и оставляйте в таблице интересующие данные.

Как пользоваться расширенным фильтром в Excel

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» — значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».

Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.

Автофильтр

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные найдите группу Сортировка и фильтр .
3. Щелкнуть по кнопке Фильтр .

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

1. Выбрать условие фильтрации.

Варианты фильтрации данных

• Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
• Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
• Можно воспользоваться строкой быстрого поиска
• Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… , используя который можно задать одновременно два условия отбора, связанные отношением И – одновременное выполнение 2 условий, ИЛИ – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

Чтобы снять фильтр только с одного столбца, достаточно щелкнуть по кнопке со стрелочкой в первой строке и в контекстном меню выбрать строку: Удалить фильтр из столбца.

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор .

1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

1. Выделить срез.
2. На ленте вкладки Параметры выбрать группу Стили срезов , содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Как пользоваться фильтром в Эксель

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

заключается в том, что Excel оставит только те строки таблицы, которые в ЭТОМ (с настроенным фильтром) столбце содержат ячейку с выбранным значением. Прочие строки будут скрыты.

Чтобы убрать фильтрацию (не удаляя фильтр!) просто отметьте галочками все пункты. Такой же эффект будет при удалении фильтра совсем — таблица вновь примет первоначальный вид.

Пользовательская сортировка данных

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

В таком случае стоит обратить внимание на настраиваемую сортировку в Excel

1. Открываем меню «Сортировка и фильтр» и выбираем пункт «Настраиваемая сортировка» или «Пользовательская сортировка» (название зависит от версии программы).

3

1. На экране появится окно сортировки. В основной части расположена строка из трех интерактивных элементов выбора. Первый определяет столбец, по которому будет оптимизирована вся таблица. Далее выбирается принцип сортировки – по значению, цвету ячеек, цвету шрифта или значкам. В разделе «Порядок» нужно установить способ сортировки данных. Если выбран столбец с текстом, в последнем списке будут варианты для текстового формата, то же и с другими форматами ячеек.

4

1. Усложним упорядочивание данных, нажав кнопку «Добавить уровень». Под первой строкой появится такая же вторая. С ее помощью можно выбрать дополнительные условия сортировки. Заполняем его по тому же принципу, но в этот раз выберем другой формат ячеек.

Обратите внимание на пометку «Затем по». Она показывает, что приоритетными для сортировки таблицы являются условия первой строки

1. Если все поля заполнены правильно, нажимаем «ОК».

5

Настройки можно изменить, выбрав одну из ячеек рассортированного диапазона и открыв окно пользовательской/настраиваемой сортировки.

Текстовый фильтр в Эксель

Рассмотрим, как отфильтровать в Excel ячейки с определенным текстом. Простейший способ – это, по аналогии с прошлым примером, ввести нужный текст (или его часть) в поиске.

Однако, можно и более гибко настраивать отбор. Если в окне фильтра нажать «Текстовые фильтры», то в контекстном меню появится выбор способа сопоставления: равно, не равно, начинается с, заканчивается на, содержит, не содержит.

Например, нужно выбрать людей, которых не зовут Богдан. Выберем вариант «не содержит» и запишем ему критерий « Богдан ». Поставим пробелы перед именем и после. Иначе, например, Богданов Егор Егорович тоже попадет под фильтр, хотя его не зовут Богданом:

Как поставить

Excel — мощная программа из пакета Microsoft, созданная для работы с таблицами. В ней удобно вести большой учет множества данных. И регулярно у пользователей возникает потребность быстро находить в файлах с тысячами данных те, которые отвечают определенному параметру. Для этого придется поставить фильтр в эксель-таблице.

Для начала работы необходимо выбрать одну, любую, ячейку внутри таблицы, открыть вкладку «Данные».

Затем нажать кнопку «Фильтр».

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

В таблице

Пошаговая инструкция: как поставить фильтр в эксель-таблице.

1. Нажать на значок в заголовке столбца.

Перед пользователем появится выпадающее окошко с перечислением всех значений в этом столбце.

2. Убрать лишние галочки, стоящие у параметров, которые пользователя не интересуют. Галочки останутся только у тех параметров, по которым необходимо провести поиск. После чего нажать «Ок».

3. Просмотреть результат — останутся только строки, соответствующие заданному параметру.

В диапазоне

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

Например, в рассматриваемом файле столбцы В и С имеют числовой отсев.

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

Например, в случае с числовым поиском этот порядок действий выглядит так:

1. Выбираем вид отсева.

2. Выбираем необходимый способ формирования диапазона, например, «больше» означает, что в результате появятся все значения, превышающие цифру, которую пользователь введет.

3. Ввести цифру, которая и станет границей для отсева, — выведены будут все значения больше нее.

4. Нажать «Ок» и оценить результат. В выбранном столбце останутся только значения, превышающие установленную границу.

Как пользоваться расширенным фильтром в Excel?

Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» — «Сортировка и фильтр» — «Очистить».

Найдем с помощью инструмента «Расширенный фильтр» информацию по значениям, которые содержат слово «Набор».

В таблицу условий внесем критерии. Например, такие:

Программа в данном случае будет искать всю информацию по товарам, в названии которых есть слово «Набор».

Для поиска точного значения можно использовать знак «=». Внесем в таблицу условий следующие критерии:

Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: =»=Набор обл.6 кл.»

После использования «Расширенного фильтра»:

Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.

В табличку условий введем критерии отбора: =»=Набор обл.6 кл.» (в столбец «Название») и =» ””;МАКС($A$1:A1)+1)’ class=’formula’>

Рядом с выпадающим списком ячейку введем следующую формулу: МАКС($B$1:$O$1);»»;ГПР(СТОЛБЕЦ(A:A);$B$1:$O$33;ПОИСКПОЗ($A$12;$A$4:$A$9;)+3))’ class=’formula’> Ее задача – выбирать из таблицы те значения, которые соответствуют определенному товару

Таким образом, с помощью инструмента «Выпадающий список» и встроенных функций Excel отбирает данные в строках по определенному критерию.

Срезы

Программа Microsoft Excel позволяет прикрепить к таблицам интерактивные элементы для сортировки и фильтрации – срезы. После выхода версии 2013-о года появилась возможность подключать срезы к обычным таблицам, а не только к сводным отчетам. Разберемся, как создать и настроить эти опции.

Создание срезов

1. Кликаем по одной из ячеек таблицы – на панели инструментов появится вкладка «Конструктор», которую нужно открыть.

1. На вкладке следует найти кнопку «Вставить срез» – кликаем по ней и переходим к новому открывшемуся окну.

22

1. Отмечаем галочками все поля, которые следует добавить в срез, и кликаем по кнопке «ОК».

23

Срезы выглядят, как диалоговые окна со списками кнопок. Названия пунктов зависят от того, какие элементы таблицы были выбраны при создании среза. Чтобы отфильтровать данные, нужно кликнуть по кнопке в одном из списков. Фильтрация по нескольким диапазонам данных возможна, если нажать кнопки в нескольких срезах.

Форматирование срезов

Редактирование внешнего вида срезов и их взаимодействия с другими элементами возможно с помощью специальных инструментов. Попробуем изменить цветовую схему.

1. Открываем вкладку «Параметры» и находим раздел «Стили срезов». В нем находятся темы для срезов разных цветов. Выбираем любую из них – цвет не повлияет на эффективность работы элемента. Сразу после клика по стилю срез приобретет указанные цвета.

24

1. Также возможно изменить положение срезов на экране. Воспользуемся кнопками «Переместить вперед» и «Переместить назад» в разделе «Упорядочить». Необходимо выбрать один из срезов и нажать кнопку на панели инструментов. Теперь при перемещении по экрану срез будет оказываться поверх всех срезов или попадет под них.
2. Удаление срезов – несложная операция. Выберите лишнее окно и нажмите клавишу «Delete» на клавиатуре. Срез исчезнет с экрана и перестанет влиять на фильтрацию данных в таблице.