Отразить значение фильтра в ячейке excel
marinanazarova, здравствуйте.
Самое простое - сделать кнопку с макросом. При этом, если ячейка с годом пустая - фильтр тоже обнуляется и показывает всё.
Sub Year_Filter()
With ThisWorkbook.Sheets(1)
Dim X As Integer
X = .Cells(1, 4).Value
If X <> 0 Then
.Range("$A$3:$I$8").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(0, "12/12/" & X)
Else
.Range("$A$3:$I$8").AutoFilter Field:=5
End If
End With
End Sub
marinanazarova, здравствуйте.
Самое простое - сделать кнопку с макросом. При этом, если ячейка с годом пустая - фильтр тоже обнуляется и показывает всё.
Sub Year_Filter()
With ThisWorkbook.Sheets(1)
Dim X As Integer
X = .Cells(1, 4).Value
If X <> 0 Then
.Range("$A$3:$I$8").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(0, "12/12/" & X)
Else
.Range("$A$3:$I$8").AutoFilter Field:=5
End If
End With
End Sub
Самое простое - сделать кнопку с макросом. При этом, если ячейка с годом пустая - фильтр тоже обнуляется и показывает всё.
Sub Year_Filter()
With ThisWorkbook.Sheets(1)
Dim X As Integer
X = .Cells(1, 4).Value
If X <> 0 Then
.Range("$A$3:$I$8").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(0, "12/12/" & X)
Else
.Range("$A$3:$I$8").AutoFilter Field:=5
End If
End With
End Sub
А если у меня будет несколько листов с одинаковыми таблицами, а последний будет лист итоговый? Просто не удобно будет по каждому листу бегать - нажимать кнопку. Спасибо
А если у меня будет несколько листов с одинаковыми таблицами, а последний будет лист итоговый? Просто не удобно будет по каждому листу бегать - нажимать кнопку. Спасибо marinanazarova
В моём Excel 2003 на панель инструментов можно вытащить кнопку "Автофильтр" (находится в категории "Данные", изображение на кнопке выглядит как воронка и рядышком символ "равно"), нажатие на которую как раз и фильтрует таблицу по выделенному значению.
где аналогичный инструмент находится в Вашем Excel 2007 - я не знаю, но, думаю, поискать такую кнопочку стоит и вытащить её на ПБД.
В моём Excel 2003 на панель инструментов можно вытащить кнопку "Автофильтр" (находится в категории "Данные", изображение на кнопке выглядит как воронка и рядышком символ "равно"), нажатие на которую как раз и фильтрует таблицу по выделенному значению.
где аналогичный инструмент находится в Вашем Excel 2007 - я не знаю, но, думаю, поискать такую кнопочку стоит и вытащить её на ПБД. ikki
А как расшифровывается эта аббревиатура? Приборная Б. Д. Автор - Rioran
Дата добавления - 11.06.2014 в 12:34
или Вы ещё не до конца определились с Вашей хотелкой?
или Вы ещё не до конца определились с Вашей хотелкой? ikki
или Вы ещё не до конца определились с Вашей хотелкой? Автор - ikki
Дата добавления - 11.06.2014 в 12:34
Вы хотите подсчитать какой-то показатель по нескольким листам? Уточните, какой именно за указанный год. Это будет адекватнее реализовать формулами
Вы хотите подсчитать какой-то показатель по нескольким листам? Уточните, какой именно за указанный год. Это будет адекватнее реализовать формулами Rioran
Вы хотите подсчитать какой-то показатель по нескольким листам? Уточните, какой именно за указанный год. Это будет адекватнее реализовать формулами Автор - Rioran
Дата добавления - 11.06.2014 в 12:37
Господа, меня устраивает первый предложенный вариант Rioran, но в процессе создался дополнительный вопрос, возможно ли реализовать данное действие на нескольких листах, и эту одну кнопку поместить только на последний лист. Это позволит мне анализировать на первом листе сколько потребуется ботинок в таком то году и таких то размеров, и на втором листе туфли. А на последнем листе заполниться общая таблица, где можно будет указать год, который поменяется в каждом листе (листов может быть много), где соответственно отфильтруется. Спасибо вам за понимание и помощь.
Господа, меня устраивает первый предложенный вариант Rioran, но в процессе создался дополнительный вопрос, возможно ли реализовать данное действие на нескольких листах, и эту одну кнопку поместить только на последний лист. Это позволит мне анализировать на первом листе сколько потребуется ботинок в таком то году и таких то размеров, и на втором листе туфли. А на последнем листе заполниться общая таблица, где можно будет указать год, который поменяется в каждом листе (листов может быть много), где соответственно отфильтруется. Спасибо вам за понимание и помощь. marinanazarova
marinanazarova, тогда Вам подойдёт этот вариант.
Принцип, в общем, тот же. Работает, если у всех целевых для фильтра листов в ячейке А3 стоит "обувь рабочая", а нужный год указан на листе с именем "Option". Можете в коде имя поменять по вкусу, если надо.
Код фильтрует любое количество листов в книге.
Sub Year_Filter()
With ThisWorkbook.Sheets("Option")
Dim X As Integer
Dim shtX As Worksheet
X = .Cells(1, 3).Value
For Each shtX In Worksheets
If shtX.Cells(3, 1).Value = "обувь рабочая" Then
If X <> 0 Then
shtX.Range("$A$3:$I$8").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(0, "12/12/" & X)
Else
shtX.Range("$A$3:$I$8").AutoFilter Field:=5
End If
End If
Next shtX
End With
End Sub
marinanazarova, тогда Вам подойдёт этот вариант.
Принцип, в общем, тот же. Работает, если у всех целевых для фильтра листов в ячейке А3 стоит "обувь рабочая", а нужный год указан на листе с именем "Option". Можете в коде имя поменять по вкусу, если надо.
Код фильтрует любое количество листов в книге.
Sub Year_Filter()
With ThisWorkbook.Sheets("Option")
Dim X As Integer
Dim shtX As Worksheet
X = .Cells(1, 3).Value
For Each shtX In Worksheets
If shtX.Cells(3, 1).Value = "обувь рабочая" Then
If X <> 0 Then
shtX.Range("$A$3:$I$8").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(0, "12/12/" & X)
Else
shtX.Range("$A$3:$I$8").AutoFilter Field:=5
End If
End If
Next shtX
End With
End Sub
Принцип, в общем, тот же. Работает, если у всех целевых для фильтра листов в ячейке А3 стоит "обувь рабочая", а нужный год указан на листе с именем "Option". Можете в коде имя поменять по вкусу, если надо.
Код фильтрует любое количество листов в книге.
Sub Year_Filter()
With ThisWorkbook.Sheets("Option")
Dim X As Integer
Dim shtX As Worksheet
X = .Cells(1, 3).Value
For Each shtX In Worksheets
If shtX.Cells(3, 1).Value = "обувь рабочая" Then
If X <> 0 Then
shtX.Range("$A$3:$I$8").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria2:=Array(0, "12/12/" & X)
Else
shtX.Range("$A$3:$I$8").AutoFilter Field:=5
End If
End If
Next shtX
Как отобразить / показать критерии автоматической фильтрации в Excel?
Автофильтр - полезная функция для пользователей Excel, иногда мы применяем автофильтр для фильтрации данных по нескольким критериям и отправки файла другим пользователям. В этой ситуации, если другие пользователи хотят знать ваш отфильтрованный критерий, как они могут это сделать?
Отображение / отображение критериев автоматической фильтрации на листе с помощью Kutools for Excel
Отображение / отображение критериев автоматической фильтрации на листе с функцией, определяемой пользователем
Предположим, у вас есть следующие данные, которые были отфильтрованы по некоторому критерию, см. Снимок экрана:
И теперь я могу создать определяемую пользователем функцию для отображения критерия фильтрации в ячейках, выполните следующие действия:
1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модули, и вставьте следующий макрос в Окно модуля:
3. Затем сохраните и закройте этот код, в пустой ячейке пустой строки, например ячейке A1, введите эту формулу = AutoFilter_Criteria (A4) (A4 является ячейкой заголовка отфильтрованных данных), см. снимок экрана:
4, Затем нажмите Enter ключ, а затем выберите ячейку A1, перетащите маркер заполнения в правые ячейки, которые должны содержать эту формулу, и все критерии были отображены в ячейках, см. снимок экрана:
Внимание: Если в столбце более двух критериев, эта функция, определяемая пользователем, не даст правильного результата.
Отображение / отображение критериев автоматической фильтрации на листе с кодом VBA
Здесь я также могу рассказать об интересном коде VBA, он может помочь вам отобразить критерии фильтра в ячейке книги.
1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модули, и вставьте следующий макрос в Окно модуля:
Код VBA: отображение критериев автоматической фильтрации на листе
3, Затем нажмите F5 нажмите клавишу для запуска этого кода, появится окно подсказки, позволяющее выбрать пустую ячейку для ввода критерия, см. снимок экрана:
4, Затем нажмите OK, все критерии были показаны в выбранной ячейке следующим образом:
Примечание:
С помощью этого кода VBA, если ваш критерий более двух в столбце, он не будет отображаться подробный критерий фильтра, а будет отображаться следующим образом:
Отображение / отображение критериев автоматической фильтрации на листе с помощью Kutools for Excel
Если у вас есть Kutools for Excel установлен, вы можете применить его Суперфильтр функция фильтрации данных, и в то же время вы можете просмотреть критерии фильтрации в любое время.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Кутулс Плюс > Суперфильтр для отображения Суперфильтр функция. Смотрите скриншот:
3. в Суперфильтр щелкните строку в группе фильтров, затем укажите необходимые критерии фильтрации в раскрывающихся списках, затем щелкните ОК, чтобы завершить настройку критериев. Смотрите скриншот:
4. Нажмите Фильтр, и выбранные данные были отфильтрованы по критериям, и в то же время вы можете просмотреть критерии фильтрации, отображаемые в Суперфильтр панель. Смотрите скриншот:
Примечание. Вы можете сохранить критерии фильтра в качестве сценария для следующего использования с утилитой Super Filter.
2. Если вы захотите использовать настройки этого фильтра в следующий раз, нажмите кнопку меню, затем выберите сценарий в подменю Открыть сценарий сохраненных настроек фильтра.
С помощью автофилеров или встроенных операторов сравнения, таких как "больше" и "10 лучших" в Excel вы можете показать нужные данные и скрыть остальные. После фильтрации данных в диапазоне ячеек или таблице вы можете повторно использовать фильтр, чтобы получить последние результаты, или очистить фильтр, чтобы повторно отфильтровать все данные.
Используйте фильтры, чтобы временно скрывать некоторые данные в таблице и видеть только те, которые вы хотите.
Фильтрация диапазона данных
Выберите любую ячейку в диапазоне данных.
Выберите Фильтр> данных.
Выберите стрелку в столбце .
Выберите Текстовые фильтры или Числовое фильтры, а затем выберите сравнение, например Между.
Введите условия фильтрации и нажмите кнопку ОК.
Фильтрация данных в таблице
При вводе данных в таблицу в заголовки ее столбцов автоматически добавляются элементы управления фильтрацией.
Выберите стрелку в столбца, который вы хотите отфильтровать.
Сберем (Выберите все) и выберем поля, которые вы хотите отобрать.
Стрелка загона столбца меняется на фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.
Статьи по теме
Отфильтрованные данные отображают только строки, которые соответствуют условия и скрывают строки, которые отображать не нужно. Фильтруя данные, вы можете копировать, находить, изменять, форматирование, диаграммы и печатать подмножество отфильтрованных данных, не меняя их оголовье и не перемещая.
Вы также можете отфильтровать несколько столбцов. Фильтры являются аддитивно, то есть каждый дополнительный фильтр основан на текущем фильтре и дополнительно уменьшает подмножество данных.
Примечание: При использовании диалогового окна Найти для поиска отфильтрованных данных поиск ведется только по отображаемой информации. данные, которые не отображаются, не поиск не ведется. Чтобы найти все данные, очистка всех фильтров.
Два типа фильтров
С помощью автофильтра можно создать два типа фильтров: по значению списка или по условиям. Каждый из этих типов фильтров является взаимоисключающими для каждого диапазона ячеек или таблицы столбцов. Например, можно отфильтровать значения по списку чисел или по критерию, но не по обоим критериям. можно фильтровать по значкам или по настраиваемой фильтрации, но не по обоим.
Повторное присвоение фильтра
Чтобы определить, применяется ли фильтр, обратите внимание на значок в заголовке столбца:
Стрелка вниз означает, что фильтрация включена, но не применена.
Если наведите курсор на заголовок столбца с включенной фильтрацией, но не примененной, на экране появляется подсказка "(Отображает все)".
Кнопка Фильтр означает, что применяется фильтр.
Когда вы наводите курсор на заголовок отфильтрованного столбца, на подсказке отображается примененный к этому столбец фильтр, например "Равно красному цвету ячейки" или "Больше 150".
При повторном его повторном анализе отображаются разные результаты по следующим причинам:
Данные были добавлены, изменены или удалены в диапазон ячеек или столбец таблицы.
значения, возвращаемые формулой, изменились, и лист был пересчитан.
Не смешивать типы данных
Для лучших результатов не смешивайте типы данных, такие как текст и число, число и дата в одном столбце, так как для каждого столбца доступна только одна команда фильтра. Если имеется сочетание типов данных, отображаемая команда является самым распространенным типом данных. Например, если столбец содержит три значения, сохраненные как число, и четыре значения как текст, отображается команда Текстовые фильтры.
Фильтрация данных в таблице
При вводе данных в таблицу в заголовки ее столбцов автоматически добавляются элементы управления фильтрацией.
Выделите данные, которые нужно отфильтровать. На вкладке Главная нажмите кнопку Форматировать как таблицу и выберите команду Форматировать как таблицу.
В диалоговом окне Создание таблицы можно выбрать, есть ли в таблице заглавные таблицы.
Выберите Таблица с заголовками, чтобы преобразовать верхнюю строку в заголовки таблицы. Данные в этой строке не будут фильтроваться.
Не выбирайте этот пункт, если вы хотите Excel в Интернете в таблицу под данными таблицы, которые можно переименовать.
Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтрации.
Фильтрация диапазона данных
Если вы не хотите форматирование данных в виде таблицы, вы также можете применить фильтры к диапазону данных.
Выделите данные, которые нужно отфильтровать. Для лучшего результата столбцы должны иметь заголовки.
На вкладке Данные выберите фильтр.
Параметры фильтрации для таблиц или диапазонов
Можно применить общий фильтр, выбрав пункт Фильтр, или настраиваемый фильтр, зависящий от типа данных. Например, при фильтрации чисел отображается пункт Числовые фильтры, для дат отображается пункт Фильтры по дате, а для текста — Текстовые фильтры. Применяя общий фильтр, вы можете выбрать для отображения нужные данные из списка существующих, как показано на рисунке:
Выбрав параметр Числовые фильтры вы можете применить один из перечисленных ниже настраиваемых фильтров.
В этом примере, чтобы отобрать регионы, в которых сумма продаж за март была меньше 6000, можно применить настраиваемый фильтр:
Вот как это сделать.
Щелкните стрелку фильтра в ячейке со словом "Март", выберите пункт Числовые фильтры и условие Меньше и введите значение 6000.
Excel в Интернете применяет фильтр и отображает только регионы с продажами ниже 6000 рублей.
Аналогичным образом можно применить фильтры по дате и текстовые фильтры.
Очистка фильтра из столбца
Удаление всех фильтров из таблицы или диапазона
Выберите любую ячейку в таблице или диапазоне и на вкладке Данные нажмите кнопку Фильтр.
При этом фильтры будут удаляться из всех столбцов таблицы или диапазона и вывести все данные.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и введите условия фильтра.
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и введите условия фильтра.
В поле рядом с всплывающим меню введите число, которое хотите использовать.
В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Вместо фильтрации можно использовать условное форматирование, которое позволяет четко выделить верхние или нижние числовые значения среди данных.
Данные можно быстро фильтровать на основании визуальных условий, таких как цвет шрифта, цвет ячейки или наборы значков. Можно также фильтровать ячейки по наличию в них формата, примененных стилей или условного форматирования.
В диапазоне ячеек или столбце таблицы щелкните ячейку с определенным цветом, цветом шрифта или значком, по которому вы хотите выполнить фильтрацию.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр во всплывающем меню По цвету щелкните Цвет ячейки, Цвет шрифта или Значок ячейки и выберите цвет.
Это возможно только в случае, если фильтруемый столбец содержит пустую ячейку.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На панели инструментов Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В области (Выделить все) прокрутите список вниз и установите флажок (Пустые).
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и во всплывающем меню выполните одно из указанных ниже действий.
Цель фильтрации диапазона
Строки с определенным текстом
Содержит или Равно.
Строки, не содержащие определенный текст
Не содержит или Не равно.
В поле рядом с всплывающим меню введите текст, которое хотите использовать.
В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.
Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий
Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На панели инструментов Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и во всплывающем меню выполните одно из указанных ниже действий.
Условие фильтрации
Начало строки текста
Начинается с.
Окончание строки текста
Заканчивается на.
Ячейки, которые содержат текст, но не начинаются с букв
Не начинаются с.
Ячейки, которые содержат текст, но не оканчиваются буквами
Не заканчиваются.
В поле рядом с всплывающим меню введите текст, которое хотите использовать.
В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.
Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий
Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий
При создании условий можно использовать подстановочные знаки.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На панели инструментов Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и выберите вариант.
В текстовом поле введите свои условия, используя подстановочные знаки.
Например, чтобы в результате фильтрации найти оба слова "год" и "гид", введите г?д.
Выполните одно из указанных ниже действий.
Используемый знак
Чтобы найти
Пример: условию "стро?а" соответствуют результаты "строфа" и "строка"
Любое количество символов
Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"
Вопросительный знак или звездочка
Например, там~? находит "там?"
Выполните одно из указанных ниже действий.
Удаление определенных условий фильтрации
Щелкните стрелку столбце, который содержит фильтр, и выберите очистить фильтр.
Удаление всех фильтров, примененных к диапазону или таблице
Выделите столбцы диапазона или таблицы, к которым применяются фильтры, а затем на вкладке Данные щелкните Фильтр.
Удаление или повторное применение стрелок фильтра в диапазоне или таблице
Выделите столбцы диапазона или таблицы, к которым применяются фильтры, а затем на вкладке Данные щелкните Фильтр.
При фильтрации данных появляются только данные, которые соответствуют вашим условиям. Данные, которые не соответствуют этому критерию, скрыты. После фильтрации данных можно копировать, находить, изменять, форматирование, диаграммы и печатать подмножество отфильтрованных данных.
Таблица с примененным фильтром "4 самых верхних элементов"
Фильтры забавляются. Это означает, что каждый дополнительный фильтр основан на текущем фильтре и дополнительно уменьшает подмножество данных. Сложные фильтры можно отфильтровать по одному значению, по одному формату или по одному критерию. Например, можно отфильтровать все числа больше 5, которые также ниже среднего. Но некоторые фильтры (десять верхних и нижних, выше и ниже среднего) основаны на исходном диапазоне ячеек. Например, при фильтрации десяти лучших значений вы увидите десять лучших значений всего списка, а не десять лучших значений подмножество последнего фильтра.
В Excel можно создавать фильтры трех типов: по значениям, по формату или по условиям. Но каждый из этих типов фильтров является взаимоисключающими. Например, можно отфильтровать значения по цвету ячейки или по списку чисел, но не по обоим. Вы можете фильтровать по значкам или по настраиваемой фильтрации, но не по обоим.
Фильтры скрывают лишние данные. Таким образом можно сосредоточиться на том, что вы хотите видеть. С другой стороны, при сортировке данных их порядок будет меняться. Дополнительные сведения о сортировке см. в списке сортировки данных.
При фильтрации рассмотрите следующие рекомендации:
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Можно отфильтровать несколько столбцов. Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Примечание: При использовании поиска найти для поиска отфильтрованных данных поиск ведется только по отображаемой информации; данные, которые не отображаются, не поиск не ведется. Чтобы найти все данные, очистка всех фильтров.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше
Функция ФИЛЬТР позволяет выполнять фильтрацию диапазона данных на основе условий, которые вы определяете.
Примечание: Эта функция в настоящее время доступна только Microsoft 365 подписчикам.
В следующем примере мы использовали формулу =ФИЛЬТР(A5:D20;C5:C20=H2;"")), чтобы вернуть все записи для Apple, как было выбрано в ячейке H2, и, если яблоки нет, возвращается пустая строка ("").
Функция ФИЛЬТР фильтрует массив с учетом массива логических значений (ИСТИНА/ЛОЖЬ).
=ФИЛЬТР(массив;включить;[если_пусто])
Массив или диапазон для фильтрации
Массив логических переменных с аналогичной высотой или шириной, что и массив.
Значение, возвращаемое, если все значения во включенном массиве пустые (фильтр не возвращает ничего)
Массивом может быть ряд значений, столбец со значениями или комбинация строк и столбцов значений. В приведенном выше примере массив для нашей формулы ФИЛЬТР представляет собой диапазон A5:D20.
Функция ФИЛЬТР возвращает массив, который будет переноситься на другие ячейки, если является конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете структурированные ссылки. Дополнительные сведения см. в статье о переносе массива.
Примеры
Функция ФИЛЬТР, используемая для возврата нескольких условий
В данном случае мы используем оператор умножения (*) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст "Яблоко" И находящихся в восточном регионе: =ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);"").
Функция ФИЛЬТР, используемая для возврата нескольких условий и сортировки
В данном случае мы используем предыдущую функцию ФИЛЬТР с функцией СОРТ для возврата всех значений в диапазоне массива (A5:D20), содержащих текст "Яблоко" И находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);"");4;-1)
В данном случае мы используем функцию ФИЛЬТР с оператором сложения (+) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст "Яблоко" ИЛИ находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)+(A5:A20=H2);""),4;-1).
Обратите внимание на то, что ни одна из функций не требует абсолютных ссылок, так как они находятся только в одной ячейке, а их результаты переносятся в соседние ячейки.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Для обработки части большого диапазона данных можно воспользоваться фильтрацией. При фильтрации остаются видимыми только те строки, которые удовлетворяют заданным условиям, а остальные скрываются до тех пор, пока не будет отменен фильтр.
В Excel предусмотрено три типа фильтров:
-
– для отбора записей по значению ячейки, по формату или в соответствии с простым критерием отбора. – интерактивные средства фильтрации данных в таблицах. – для фильтрации данных с помощью сложного критерия отбора.
Автофильтр
- Выделить одну ячейку из диапазона данных.
- На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
- Щелкнуть по кнопке Фильтр [Filter] .
- В верхней строке диапазона возле каждого столбца появились кнопки со стрелочками. В столбце, содержащем ячейку, по которой будет выполняться фильтрация, щелкнуть на кнопку со стрелкой. Раскроется список возможных вариантов фильтрации.
- Выбрать условие фильтрации.
- Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
- Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
- Можно воспользоваться строкой быстрого поиска
- Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:
- При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
- При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
- При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
- Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.
Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.
Отмена фильтрации
Для того чтобы отменить фильтрацию диапазона данных, достаточно повторно щелкнуть по кнопке Фильтр.
Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные
Срезы
Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.
Создание срезов
В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.
Для этого нужно выполнить следующие шаги:
-
Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].
Форматирование срезов
- Выделить срез.
- На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.
- Выбрать кнопку с подходящим стилем форматирования.
Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.
Расширенный фильтр
Расширенный фильтр предоставляет дополнительные возможности. Он позволяет объединить несколько условий, расположить результат в другой части листа или на другом листе и др.
Задание условий фильтрации
Вначале надо скопировать шапку таблицы. Построить таблицу условий отбора данных можно либо на активном листе, либо на другом. Предпочтительнее на другом листе, иначе после фильтрации эти условия или их часть могут быть скрыты.
Записать условия фильтрации. Условия, записанные в одной строке, выполняются одновременно (как условие « И »), а в разных строках - как условие выбора (« ИЛИ »). В качестве условия может быть совпадение значения, которое заносится в ячейку, или сравнение с заданным в ячейке значением с помощью знаков или > . Если один столбец должен удовлетворять двум условиям, его заголовок нужно повторить еще раз и записать в этом столбце второе условие.
Читайте также:
- Как открыть dds в фотошопе
- Автоматическое заполнение ячеек при выборе значения из раскрывающегося списка в excel vba
- Сканер hp laserjet m1120 mfp установить программу для сканирования
- Программа для прошивки люмия 640
- Какие действия может выполнять антивирусная программа с обнаруженным зараженным файлом