Сортировка и фильтрация данных в Excel

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

 

Обычная (простая) сортировка

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

Быстрая организация данных в таблице возможна благодаря набору инструментов «Сортировка и фильтр». Эта кнопка находится на вкладке «Главная» справа от нее. Параметры сортировки соответствуют формату выбранных ячеек.

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

• вы также можете сортировать числа, но параметры для этого появляются только после того, как вы выберете ячейку в числовом формате. Щелкните одну из этих ячеек и откройте «Сортировка и фильтр». В меню появятся новые функции — «Асцендент» и наоборот. Для дат подготовлены варианты «от старого к новому» и обратная сортировка.

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

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

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

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

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

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

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

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

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

Как настроить фильтр в таблице

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

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

Внимание! Вы также можете переключиться на фильтр через вкладку «Данные». На ней есть большая кнопка «Фильтр» с воронкой.

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

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

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

восемь

Как убрать фильтр в таблице

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

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

девять

Другой метод предполагает использование панели инструментов. Откройте меню «Сортировка и фильтр» на главной вкладке и найдите пункт «Удалить». Он активен, когда диапазон ячеек отфильтрован. Щелкните этот элемент, и все настройки фильтра будут сброшены.

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

Как создать «умную таблицу»

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

• Выделите всю таблицу курсором.
• В основной вкладке выбираем пункт «Форматировать как таблицу.
• На экране появится меню стилей. Вы должны выбрать любой — выбор не повлияет на результат.

• Откроется окно выбора координат. Поскольку таблица была изначально выбрана, вы можете пропустить этот шаг и нажать OK».

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

Есть еще один метод создания умной таблицы:

• После выбора диапазона ячеек перейдите на вкладку «Вставка» и нажмите кнопку «Таблица».

• Проверяем правильность координат в появившемся окне и нажимаем «ОК».

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

Как сделать фильтр в Excel по столбцам

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

Сортировка по нескольким столбцам в Excel

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

1. Выберите ячейку таблицы для сортировки.
2. Найдите кнопку «Сортировка» на вкладке «Данные» и щелкните ее.

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

Щелкните по кнопке «Параметры». В появившемся окне выберите пункт «Столбцы таблицы», затем нажмите кнопку «ОК».

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

Важно! Количество уровней сортировки ограничено только количеством столбцов или строк в таблице.

Автофильтр

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

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

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

ветры

Рассмотрим вариант Custom Filter. С его помощью пользователи могут самостоятельно установить нужные параметры фильтра.

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

21 год

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

Стоит обратить внимание на записи И/ИЛИ в окне настроек автофильтра. Они сами решают, как настройки будут применяться вместе или частично.

Срезы

Microsoft Excel позволяет прикреплять к таблицам интерактивные элементы для сортировки и фильтрации — срезы. После выпуска версии 2013-o появилась возможность привязать срезы к обычным таблицам, а не только к сводным отчетам. Давайте узнаем, как создавать и настраивать эти параметры.

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

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

Примечание! Если ваша версия Microsoft Excel более ранняя, чем 2013, вы не сможете создать срез для обычной таблицы. Эта функция применима только к отчетам в формате сводной таблицы.

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

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

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

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

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

Откройте вкладку «Параметры» и найдите раздел «Стили слайсера». Содержит темы для разного цвета кроя. Выбираем один из них — цвет не повлияет на работоспособность элемента. Сразу после нажатия на стиль секция приобретет указанные цвета.

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

Удаление ломтиков — простая операция. Выделите ненужное окно и нажмите на клавиатуре клавишу «Удалить». Срез исчезает с экрана и перестает влиять на фильтрацию данных в таблице.

Заключение

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