Фильтрация данных (Power Query)

Применяется к

Excel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

В Power Query можно включать или исключать строки на основе значения столбца. Отфильтрованный столбец содержит маленький значок фильтра ( Значок примененного фильтра ) в заголовке столбца. Чтобы удалить фильтр по столбцу, щелкните рядом со столбцом и выберите команду Очистить фильтр.

Фильтрация с помощью автофильтра

С помощью функции автофильтра можно искать, отображать и скрывать значения, а также легко задавать условия фильтра. По умолчанию отображаются только первые 1000 уникальных значений. Если в сообщении говорится, что список фильтров может быть неполным, выберите "Загрузить еще". В зависимости от объема данных это сообщение может отображаться несколько раз.

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
рядом со столбцом, который вы хотите отфильтровать.
Снимите флажок (Выделить все), чтобы снять выделение со всех столбцов.
Установите флажок для проверки значений столбца, по которым нужно выполнить фильтрацию, а затем нажмите кнопку "ОК".

Выделение столбца

Фильтрация с помощью текстовых фильтров

Фильтрацию по определенному текстовому значению можно выполнить с помощью подменю "Текстовые фильтры ".

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
рядом со столбцом, содержащим текстовое значение, по которому требуется выполнить фильтрацию.
Выберите "Текстовые фильтры" и выберите тип на равенство с именем "Равно", "Д или ес не равно", "начинается с", "не начинается с", "заканчивается с", "не заканчивается", "содержит с" и "не содержит".
В диалоговом окне "Фильтрация строк" выполните указанные ниже действия.
- Ввод или обновление двух операторов и значений в простом режиме .
- В расширенном режиме введите или обновите более двух предложений, сравнений, столбцов, операторов и значений.
Нажмите кнопку ОК.

Фильтрация с помощью числовых фильтров

Можно отфильтровать по числовому значению с помощью подменю "Числовые фильтры ".

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
для столбца с числом, по которому требуется выполнить фильтрацию, со стрелкой вниз.
Нажмите кнопку " Числовые фильтры" и выберите тип на равенство с именем "Равно","Не равно", "Больше", "Больше или равно", "Меньше", "Меньше или равно", " Между".
В диалоговом окне "Фильтрация строк" выполните указанные ниже действия.
- Ввод или обновление двух операторов и значений в простом режиме .
- В расширенном режиме введите или обновите более двух предложений, сравнений, столбцов, операторов и значений.
Нажмите кнопку ОК.

Фильтрация с помощью фильтров даты и времени

Вы можете фильтровать по значению даты и времени с помощью подменю "Фильтры даты и времени ".

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
для столбца со значением даты и времени, по которому требуется выполнить фильтрацию, со стрелкой вниз.
Выберите "Фильтры даты и времени" и выберите тип равенства: "Равно", "До", "После", "Между", "В следующем", "В предыдущем", "Ранний", "Последний", "Не самый ранний", "Не новейший" и "Пользовательский фильтр".

Совет Для упрощения использования стандартных фильтров выберите значения "Год", "Квартал", "Месяц", "Неделя", "День", "Час", "Минуты" и "Секунды". Эти команды работают сразу.
В диалоговом окне "Фильтрация строки" выполните следующие действия.
- Ввод или обновление двух операторов и значений в простом режиме .
- В расширенном режиме введите или обновите более двух предложений, сравнений, столбцов, операторов и значений.
Нажмите кнопку ОК.

Фильтрация нескольких столбцов

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

В следующем примере в строке формул функция Table.SelectRows возвращает запрос, отфильтрованный по состоянию и году.

Результат фильтра

Фильтрация по пустым значениям или пустым значениям

Пустое или пустое значение, которое используется, если в ячейке ничего нет. Существует два способа удаления пустых и пустых значений.

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

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
рядом со столбцом, который вы хотите отфильтровать.
Снимите флажок (Выделить все), чтобы отменить выделение всех столбцов.
Выберите "Удалить пустые" и нажмите "ОК".

Этот метод проверяет каждое значение в столбце с помощью следующей формулы (для столбца "Имя"):

Table.SelectRows(#"Changed Type", each ([Name] <> null and [Name] <> ""))

Использование команды "Удалить пустые строки"

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите "Главная">Удалить строки>Удалить пустые строки.

Чтобы очистить этот фильтр, удалите соответствующее действие из раздела "Примененные шаги " в параметрах запроса.

Этот метод проверяет всю строку как запись с помощью следующей формулы:

Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

Фильтр по положению строки

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

Примечание

Если указать диапазон или шаблон, первой строкой данных в таблице будет нулевая строка (0), а не строка первая (1). Вы можете создать столбец индекса для отображения позиций строк перед указанием строк. Дополнительные сведения см. в статье Добавление столбца индекса.

Сохранение верхних строк

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите «Домашняя>страница сохранения строк»,>«Сохранить верхние строки».
В диалоговом окне "Сохранение верхних строк" введите число в поле "Число строк".
Нажмите кнопку ОК.

Сохранение нижних строк

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите домашнюю>строку,> чтобысохранить нижние строки.
В диалоговом окне " Сохранение нижних строк" введите число в поле "Число строк".
Нажмите кнопку ОК.

Сохранение диапазона строк

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

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите "Домашняя>страница Сохранить строки",>"Сохранить диапазон строк".
В диалоговом окне " Сохранить диапазон строк" введите числа в полях "Первая строка " и "Число строк". Чтобы использовать пример, введите шесть в качестве первой строки, а семь — как количество строк.
Нажмите кнопку ОК.

Удаление верхних строк

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите "Главная",>"Удалить строки",>"Удалить верхние строки".
В диалоговом окне " Удаление верхних строк" введите число в поле "Число строк".
Нажмите кнопку ОК.

Удаление нижних строк

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите "Главная",>"Удалить строки",>"Удалить нижние строки".
В диалоговом окне " Удаление нижних строк" введите число в поле "Число строк".
Нажмите кнопку ОК.

Фильтрация путем удаления альтернативных строк

Можно фильтровать по дополнительным строкам и даже определять альтернативные строки. Например, в таблице после каждой строки данных отображается строка примечаний. Требуется сохранить нечетные строки (1, 3, 5 и т. д.), но удалить четные строки (2, 4, 6 и т. д.).

Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Выберите "Главная",>"Удалить строки>", "Удалить альтернативные строки".
В диалоговом окне "Удаление альтернативных строк" введите следующие данные:
- Первая строка для удаления Начните считать в этой строке. Если ввести "2", первая строка сохраняется, а вторая удаляется.
- Количество удаляемых строк Определите начало шаблона. Если ввести 1, одна строка будет удалена за раз.
- Количество строк для сохранения Определите конец шаблона. Если вы введете 1, продолжите шаблон со следующей строкой, которая является третьей строкой.
Нажмите кнопку ОК.