Как сделать фильтр по убыванию в эксель
Обновлено: 05.07.2024
В данном примере рассмотрим самые быстрые и эффективные методы сортировки данных в Excel по различным критериям: по возрастанию или убыванию чисел, по датам от старых коновым и от к новым к старым, по алфавиту от А до Я, а так же в обратно-алфавитном порядке.
Сортировка строк по столбцу в Excel
Ниже на рисунке представлена выборка информации из базы данных по сотрудникам фирмы:
Допустим нам необходимо отсортировать таблицу относительно столбца «Премия» по убыванию сумм. Для решения этой задачи воспользуемся самыми простыми (базовыми) инструментами сортировки в Excel.
Перейдите курсором клавиатуры на любую ячейку в столбце G (можно даже на заголовок столбца G1). А Потом выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».
В результате данные упорядочились и организовались в определенную структуру:
Как оказалось, наибольшую премию получил сотрудник «Евгений Пруткий».
Внимание! Список, который будет сортирован не должен содержать объединенных ячеек.
Примечание. Инструмент для базовой сортировки диапазонов доступен так же на закладке: «ДАННЫЕ»-«Сортировка по возрастанию/по убыванию».
И встроен в выпадающие меню заголовков таблиц в режиме автофильтра или умной таблицы:
Так же встроен в контекстное меню, которое появляется по нажатию правой кнопкой мышки:
Как убрать сортировку в Excel
Если таблицу необходимо будет привести в изначальный вид, тогда перед каждой сортировкой создавайте столбец нумерации строк. В данном примере – это столбец А (№п/п). Часто необходимо привести таблицу в изначальный вид после выполнения с ней многочисленных и сложных сортирований данных по разным столбцам, критериям или условиям. Тогда достаточно выполнить сортировку по столбцу с номерами строк, чтобы таблица приняла свой первоначальный вид.
Как в Excel сделать сортировку в столбце
Иногда нужно выполнить независимую сортировку только лишь внутри одного столбца таблицы без привязки к другим столбцам или их изменения. Например, столбец B «Имя». Такая задача решается так же просто:
- Выделите целый столбец B щелкнув левой мышкой по заголовку столбца листа.
- Выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».
- В появившемся диалоговом окне «Обнаруженные данные в неуказанного диапазона», выберите опцию: «сортировать в пределах указанного выделения». И нажмите на кнопку «Сортировка».
Обратите внимание на то, что значения ячеек в остальных столбцах остались без изменений:
Такой способ необходимо применять только как вспомогательный.
Сортировка по нескольким столбцам Excel
Как настроить сортировку в Excel? Допустим мы хотим сортировать сотрудников по дате рождения (от старых к новым) в соответствии с каждым городом (места рождения). Для этого:
- Сначала для данного примера лучше сбросить сортировку предыдущих примеров. Для этого отсортируйте таблицу по первому столбцу А (№п/п) – по возрастанию.
- Перейдите на любую ячейку исходной таблицы и выберите инструмент: «ГЛЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Настраиваемая сортировка». Появиться диалоговое окно в котором можно задать несколько условий для сортировки таблицы.
- В первом выпадающем списке выбираем заголовок столбца по которому будут сортированы данные в первую очередь. В данном примере – это «Место рождения». Второй выпадающий список оставим без изменений – «Значение», а в третьем указываем тип сортирования «От А до Я».
- Нажмите на кнопку «Добавить уровень» и в следующем условии указываем новые критерии. 1 – «Дата рождения», 2 – «Значение», 3 – «От старых к новым». И нажмите на кнопку ОК.
Таблица приобрела следующую структуру:
Сначала ее значения отсортированы относительно названий городов в алфавитном порядке. Далее относительного каждого города распределены даты рождения от старых к новым.
Если мы работаем только с фрагментом таблицы где нет заголовков столбцов, а только лишь диапазон ячеек с текущими значениями, как показано ниже на рисунке:
Тогда в диалоговом окне инструмента «Настраиваемая сортировка» следует отключить опцию «Мои данные имеют заголовки столбцов». В результате чего в качестве критериев для каждого первого выпадающего списка секции «Столбец» в каждом уровне, будут использованы заголовки столбцов рабочего листа Excel (Столбец А, Столбец B, C и т.д.). А при применении всех параметров в сортировку будут включены все строки и ячейки исходного диапазона (фрагмента таблицы), что является правильным результатом в данном случаи.
Примечание. Кнопка вызова инструмента для настраиваемой сортировки так же доступна на закладке «ДАННЫЕ»-«Сортировка».
Таблицы данных, которые мы получаем от разных сотрудников в большинстве случаев не упорядочены и работать с ними без организации – сложно. Возникает необходимость отсортировать: текстовые значение по алфавиту, числовые – по возрастанию, а даты от старых до новых. Сортировать вручную все столбцы по отдельности – это не наилучшее решение для данной задачи. Особенно если столбцов более тысячи. Поэтому лучше воспользоваться специальным встроенным инструментом Excel – настраиваемой сортировкой. Данный инструмент автоматическое сортирует данные одновременно по нескольким столбцам. Он настолько эластичен, что его можно применить практически для любой сортировки с любым уровнем сложности.
Сортировка данных – распространенный способ упорядочивания значений от большего к меньшему, по алфавиту или другим параметрам. В Excel это можно сделать несколькими методами – обращаясь к функциям или используя пункты в меню.
Вариант 1: Быстрая сортировка в Excel
Иногда требуется максимально быстро отсортировать данные в столбце по возрастанию чисел, убыванию или по алфавиту. Для этого идеально подойдут кнопки быстрой сортировки, предназначенные как раз для выполнения поставленной задачи.
Выделите зажатой левой кнопкой мыши столбец с данными и вызовите меню «Редактирование».
Откройте список «Сортировка и фильтр» и выберите подходящий для вас вариант упорядочения. С числами это по возрастанию или убыванию, а для текста – по алфавиту и в обратную сторону.
Если вы выделили не все ячейки в столбце, появится окно с предложением расширить диапазон. Сделайте это, если за пределами выделения тоже находятся значения, которые нужно сортировать. Укажите сортировку в пределах данного диапазона, если за ним находятся исключительно заголовки.
Изменения вступают в силу сразу же. Нажмите Ctrl + Z для отмены действия, если что-то выполнено неправильно.
Обратите внимание на то, что сортировка сбивается после внесения любых изменений в клетках. Придется снова обращаться к тем же самым кнопкам, упорядочив уже новый массив с данными.
Вариант 2: Настраиваемая сортировка
Более продвинутый вариант, позволяющий задать сразу несколько условий упорядочения, – это настраиваемая сортировка, выполняющаяся через соответствующее окно. Давайте разберем сортировку по двум разным параметрам:
Вы можете не выделять заранее столбец, если для него задано название. Вызовите то же меню редактирования, но на этот раз нажмите по «Настраиваемая сортировка».
Выберите вариант с расширением или сохранением диапазона, если предварительно выделили столбец.
Вы увидите первый уровень сортировки, который и стоит настроить с самого начала.
Выберите столбец для сортировки из первого выпадающего списка.
Далее укажите вариант сортировки. Это может быть значение, цвет шрифта или ячейки, знаки условного форматирования.
В завершение укажите, в каком порядке необходимо отсортировать данные.
Если затем нужно применить еще один вариант сортировки, добавьте уровень и настройте его должным образом.
После применения вернитесь к таблице и убедитесь в том, что действия произведены верно. Учитывайте, что в этом случае сортировка тоже собьется, если вы внесете изменения в любой ячейке.
Вариант 3: Функция НАИБОЛЬШИЙ или НАИМЕНЬШИЙ
Последний вариант – единственный динамический, то есть после внесения изменений в клетках сортировка автоматически перестроится и ее последовательность будет правильной. Как вы уже поняли, для этого понадобится использовать функцию НАИБОЛЬШИЙ или НАИМЕНЬШИЙ.
В пустом столбце выберите первую ячейку для сортировки (желательно, чтобы она находилась параллельно ячейке с существующего столбца). Вызовите одну из указанных функций, отталкиваясь от необходимого вам типа упорядочения.
Далее в скобках укажите (C:C;СТРОКА(C1)), поменяв буквы на номер столбца сортировки.
Растяните функцию, зажав левую кнопку мыши в правой нижней точке ячейки и потянув вниз. Вы увидите, что данные указанного столбца перенесены параллельно, но соблюдена сортировка по возрастанию или убыванию. При этом исходные данные остаются в таком же хаотичном порядке, как были и раньше.
Теперь в исходном столбце вы можете вносить любые изменения для значений, а параллельные данные в отсортированном столбце поменяются в зависимости от указанных данных, но порядок все равно будет правильным.
Используйте любой из вариантов сортировки по надобности, но учитывайте, что динамически меняется только способ с применением функции. Конечно, он потребует создания дополнительного столбца, но в некоторых случаях это является оптимальным решением. Только не забудьте дать название столбцу, чтобы все пользователи таблицы понимали, для чего он нужен на листе.
С помощью автофилеров или встроенных операторов сравнения, таких как "больше" и "10 лучших" в Excel вы можете показать нужные данные и скрыть остальные. После фильтрации данных в диапазоне ячеек или таблице вы можете повторно использовать фильтр, чтобы получить последние результаты, или очистить фильтр, чтобы повторно отфильтровать все данные.
Используйте фильтры, чтобы временно скрывать некоторые данные в таблице и видеть только те, которые вы хотите.
Фильтрация диапазона данных
Выберите любую ячейку в диапазоне данных.
Выберите Фильтр> данных.
Выберите стрелку в столбце .
Выберите Текстовые фильтры или Числовое фильтры, а затем выберите сравнение, например Между.
Введите условия фильтрации и нажмите кнопку ОК.
Фильтрация данных в таблице
При вводе данных в таблицу в заголовки ее столбцов автоматически добавляются элементы управления фильтрацией.
Выберите стрелку в столбца, который вы хотите отфильтровать.
Сберем (Выберите все) и выберем поля, которые вы хотите отобрать.
Стрелка загона столбца меняется на фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.
Статьи по теме
Отфильтрованные данные отображают только строки, которые соответствуют условия и скрывают строки, которые отображать не нужно. Фильтруя данные, вы можете копировать, находить, изменять, форматирование, диаграммы и печатать подмножество отфильтрованных данных, не меняя их оголовье и не перемещая.
Вы также можете отфильтровать несколько столбцов. Фильтры являются аддитивно, то есть каждый дополнительный фильтр основан на текущем фильтре и дополнительно уменьшает подмножество данных.
Примечание: При использовании диалогового окна Найти для поиска отфильтрованных данных поиск ведется только по отображаемой информации. данные, которые не отображаются, не поиск не ведется. Чтобы найти все данные, очистка всех фильтров.
Два типа фильтров
С помощью автофильтра можно создать два типа фильтров: по значению списка или по условиям. Каждый из этих типов фильтров является взаимоисключающими для каждого диапазона ячеек или таблицы столбцов. Например, можно отфильтровать значения по списку чисел или по критерию, но не по обоим критериям. можно фильтровать по значкам или по настраиваемой фильтрации, но не по обоим.
Повторное присвоение фильтра
Чтобы определить, применяется ли фильтр, обратите внимание на значок в заголовке столбца:
Стрелка вниз означает, что фильтрация включена, но не применена.
Если наведите курсор на заголовок столбца с включенной фильтрацией, но не примененной, на экране появляется подсказка "(Отображает все)".
Кнопка Фильтр означает, что применяется фильтр.
Когда вы наводите курсор на заголовок отфильтрованного столбца, на подсказке отображается примененный к этому столбец фильтр, например "Равно красному цвету ячейки" или "Больше 150".
При повторном его повторном анализе отображаются разные результаты по следующим причинам:
Данные были добавлены, изменены или удалены в диапазон ячеек или столбец таблицы.
значения, возвращаемые формулой, изменились, и лист был пересчитан.
Не смешивать типы данных
Для лучших результатов не смешивайте типы данных, такие как текст и число, число и дата в одном столбце, так как для каждого столбца доступна только одна команда фильтра. Если имеется сочетание типов данных, отображаемая команда является самым распространенным типом данных. Например, если столбец содержит три значения, сохраненные как число, и четыре значения как текст, отображается команда Текстовые фильтры.
Фильтрация данных в таблице
При вводе данных в таблицу в заголовки ее столбцов автоматически добавляются элементы управления фильтрацией.
Выделите данные, которые нужно отфильтровать. На вкладке Главная нажмите кнопку Форматировать как таблицу и выберите команду Форматировать как таблицу.
В диалоговом окне Создание таблицы можно выбрать, есть ли в таблице заглавные таблицы.
Выберите Таблица с заголовками, чтобы преобразовать верхнюю строку в заголовки таблицы. Данные в этой строке не будут фильтроваться.
Не выбирайте этот пункт, если вы хотите Excel в Интернете в таблицу под данными таблицы, которые можно переименовать.
Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтрации.
Фильтрация диапазона данных
Если вы не хотите форматирование данных в виде таблицы, вы также можете применить фильтры к диапазону данных.
Выделите данные, которые нужно отфильтровать. Для лучшего результата столбцы должны иметь заголовки.
На вкладке Данные выберите фильтр.
Параметры фильтрации для таблиц или диапазонов
Можно применить общий фильтр, выбрав пункт Фильтр, или настраиваемый фильтр, зависящий от типа данных. Например, при фильтрации чисел отображается пункт Числовые фильтры, для дат отображается пункт Фильтры по дате, а для текста — Текстовые фильтры. Применяя общий фильтр, вы можете выбрать для отображения нужные данные из списка существующих, как показано на рисунке:
Выбрав параметр Числовые фильтры вы можете применить один из перечисленных ниже настраиваемых фильтров.
В этом примере, чтобы отобрать регионы, в которых сумма продаж за март была меньше 6000, можно применить настраиваемый фильтр:
Вот как это сделать.
Щелкните стрелку фильтра в ячейке со словом "Март", выберите пункт Числовые фильтры и условие Меньше и введите значение 6000.
Excel в Интернете применяет фильтр и отображает только регионы с продажами ниже 6000 рублей.
Аналогичным образом можно применить фильтры по дате и текстовые фильтры.
Очистка фильтра из столбца
Удаление всех фильтров из таблицы или диапазона
Выберите любую ячейку в таблице или диапазоне и на вкладке Данные нажмите кнопку Фильтр.
При этом фильтры будут удаляться из всех столбцов таблицы или диапазона и вывести все данные.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и введите условия фильтра.
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Фильтрация по конкретному числу или диапазону чиселЩелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и введите условия фильтра.
В поле рядом с всплывающим меню введите число, которое хотите использовать.
В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Вместо фильтрации можно использовать условное форматирование, которое позволяет четко выделить верхние или нижние числовые значения среди данных.
Фильтрация по цвету шрифта, цвету ячеек или наборам значковДанные можно быстро фильтровать на основании визуальных условий, таких как цвет шрифта, цвет ячейки или наборы значков. Можно также фильтровать ячейки по наличию в них формата, примененных стилей или условного форматирования.
В диапазоне ячеек или столбце таблицы щелкните ячейку с определенным цветом, цветом шрифта или значком, по которому вы хотите выполнить фильтрацию.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр во всплывающем меню По цвету щелкните Цвет ячейки, Цвет шрифта или Значок ячейки и выберите цвет.
Это возможно только в случае, если фильтруемый столбец содержит пустую ячейку.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На панели инструментов Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В области (Выделить все) прокрутите список вниз и установите флажок (Пустые).
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На вкладке Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и во всплывающем меню выполните одно из указанных ниже действий.
Цель фильтрации диапазона
Строки с определенным текстом
Содержит или Равно.
Строки, не содержащие определенный текст
Не содержит или Не равно.
В поле рядом с всплывающим меню введите текст, которое хотите использовать.
В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.
Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий
Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На панели инструментов Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и во всплывающем меню выполните одно из указанных ниже действий.
Условие фильтрации
Начало строки текста
Начинается с.
Окончание строки текста
Заканчивается на.
Ячейки, которые содержат текст, но не начинаются с букв
Не начинаются с.
Ячейки, которые содержат текст, но не оканчиваются буквами
Не заканчиваются.
В поле рядом с всплывающим меню введите текст, которое хотите использовать.
В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.
Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий
Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий
Использование подстановочных знаков для фильтрацииПри создании условий можно использовать подстановочные знаки.
Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
На панели инструментов Данные щелкните Фильтр.
Щелкните стрелку в столбце с содержимым, которое вы хотите отфильтровать.
В разделе Фильтр щелкните Выберите один из вариантов и выберите вариант.
В текстовом поле введите свои условия, используя подстановочные знаки.
Например, чтобы в результате фильтрации найти оба слова "год" и "гид", введите г?д.
Выполните одно из указанных ниже действий.
Используемый знак
Чтобы найти
Пример: условию "стро?а" соответствуют результаты "строфа" и "строка"
Любое количество символов
Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"
Вопросительный знак или звездочка
Выполните одно из указанных ниже действий.
Удаление определенных условий фильтрации
Щелкните стрелку столбце, который содержит фильтр, и выберите очистить фильтр.
Удаление всех фильтров, примененных к диапазону или таблице
Выделите столбцы диапазона или таблицы, к которым применяются фильтры, а затем на вкладке Данные щелкните Фильтр.
Удаление или повторное применение стрелок фильтра в диапазоне или таблице
Выделите столбцы диапазона или таблицы, к которым применяются фильтры, а затем на вкладке Данные щелкните Фильтр.
При фильтрации данных появляются только данные, которые соответствуют вашим условиям. Данные, которые не соответствуют этому критерию, скрыты. После фильтрации данных можно копировать, находить, изменять, форматирование, диаграммы и печатать подмножество отфильтрованных данных.
Таблица с примененным фильтром "4 самых верхних элементов"
Фильтры забавляются. Это означает, что каждый дополнительный фильтр основан на текущем фильтре и дополнительно уменьшает подмножество данных. Сложные фильтры можно отфильтровать по одному значению, по одному формату или по одному критерию. Например, можно отфильтровать все числа больше 5, которые также ниже среднего. Но некоторые фильтры (десять верхних и нижних, выше и ниже среднего) основаны на исходном диапазоне ячеек. Например, при фильтрации десяти лучших значений вы увидите десять лучших значений всего списка, а не десять лучших значений подмножество последнего фильтра.
В Excel можно создавать фильтры трех типов: по значениям, по формату или по условиям. Но каждый из этих типов фильтров является взаимоисключающими. Например, можно отфильтровать значения по цвету ячейки или по списку чисел, но не по обоим. Вы можете фильтровать по значкам или по настраиваемой фильтрации, но не по обоим.
Фильтры скрывают лишние данные. Таким образом можно сосредоточиться на том, что вы хотите видеть. С другой стороны, при сортировке данных их порядок будет меняться. Дополнительные сведения о сортировке см. в списке сортировки данных.
При фильтрации рассмотрите следующие рекомендации:
В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Можно отфильтровать несколько столбцов. Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Примечание: При использовании поиска найти для поиска отфильтрованных данных поиск ведется только по отображаемой информации; данные, которые не отображаются, не поиск не ведется. Чтобы найти все данные, очистка всех фильтров.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Наверное, все пользователи, которые постоянно работают с Microsoft Excel, знают о такой полезной функции этой программы как фильтрация данных. Но не каждый в курсе, что существуют также и расширенные возможности у этого инструмента. Давайте рассмотрим, что умеет делать расширенный фильтр Microsoft Excel и как им пользоваться.
Использование расширенного фильтра в Excel
Шаг 1: Создание таблицы с условиями отбора
Чтобы установить расширенный фильтр, прежде всего требуется создать дополнительную таблицу с условиями отбора. Ее шапка в точности такая, как у основной, которую мы, собственно, и будем фильтровать. Для примера мы разместили дополнительную таблицу над основной и окрасили ее ячейки в оранжевый цвет. Хотя размещать ее можно в любом свободном месте и даже на другом листе.
Теперь вписываем в дополнительную таблицу сведения, которые нужно будет отфильтровать из основной таблицы. В нашем конкретном случае из списка выданной сотрудникам заработной платы мы решили выбрать данные по основному персоналу мужского пола за 25.07.2016.
Шаг 2: Запуск расширенного фильтра
Только после того как дополнительная таблица создана, можно переходить к запуску расширенного фильтра.
-
Переходим на вкладку «Данные» и на ленте в блоке инструментов «Сортировка и фильтр» жмем по «Дополнительно».
Таким образом, можно сделать вывод, что расширенный фильтр предоставляет больше возможностей, чем обычная фильтрация данных. Но нельзя не отметить, что работа с этим инструментом все-таки менее удобна, чем со стандартным фильтром.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: