Microsoft Excel: сортировка и фильтрация данных
20.11.2020Сортировка в Excel применяется для визуализации данных и их упорядочивания, что существенно облегчает восприятие информации, представленной в табличной форме. Необходимость в этой функции возникает при работе с бухгалтерскими ведомостями, списками складских запасов и строительными сметами.
Часто проблемой может стать вопрос расположения чисел от большего к меньшему или наоборот. На самом деле критерии для упорядочивания информации в Экселе бывают разными: дата, время, цвет ячеек или тип шрифта. Чаще всего при изучении приемов работы с электронными таблицами рассматриваются примеры, где представлены списки сотрудников или товаров, так как сортировать по алфавиту на практике приходится очень часто. Рассортировать в программе можно по двум различным параметрам.
Как отсортировать по алфавиту
Поскольку в большинстве таблиц числовые параметры привязаны к какому-либо тексту – фамилии или названию товара то удобно расположить по алфавиту. Рассмотрим особенности работы с этим инструментом в редакторе Еxcel на примере таблицы «Содержание питательных веществ во фруктах и овощах».
Для того чтобы отсортировать названия овощей и фруктов по алфавиту необходимо выделить первый столбец, кликнув мышью по его заголовку. Далее требуется перейти на панель инструментов «Редактирование» и открыть вкладку «Главная», на которой расположена специальная кнопка «Сортировка и фильтр». Для упорядочивания названий в первом столбце от А до Я или наоборот, достаточно выбрать соответствующую команду сортировки в выпадающем меню. Если в таблицу Еxcel будут добавлены данные о других продуктах сработает автоматическая сортировка.
Фильтр
Кроме того, в программе Microsoft Excel существует функция фильтра данных. Она позволяет оставить видимыми только те данные, которые вы посчитаете нужными, а остальные скрыть. При необходимости, скрытые данные всегда можно будет вернуть в видимый режим.
Чтобы воспользоваться данной функцией, становимся на любую ячейку в таблице (а желательно в шапке), опять жмем на кнопку «Сортировка и фильтр» в блоке инструментов «Редактирование». Но, на этот раз в появившемся меню выбираем пункт «Фильтр». Можно также вместо этих действий просто нажать сочетание клавиш Ctrl+Shift+L.
Как видим, в ячейках с наименованием всех столбцов появился значок в виде квадрата, в который вписан перевернутый вниз треугольник.
Кликаем по этому значку в том столбце, по данным которого мы собираемся проводить фильтрацию. В нашем случае, мы решили провести фильтрацию по имени. Например, нам нужно оставить данные только работника Николаева. Поэтому, снимаем галочки с имен всех остальных работников.
Когда процедура выполнена, жмем на кнопку «OK».
Как видим, в таблице остались только строки с именем работника Николаева.
Усложним задачу, и оставим в таблице только данные, которые относятся к Николаеву за III квартал 2020 года. Для этого, кликаем по значку в ячейке «Дата». В открывшемся списке, снимаем галочки с месяцев «Май», «Июнь» и «Октябрь», так как они не относятся к третьему кварталу, и жмем на кнопку «OK».
Как видим, остались только нужные нам данные.
Для того, чтобы удалить фильтр по конкретному столбцу, и показать скрытые данные, опять кликаем по значку, расположенному в ячейке с заглавием данного столбца. В раскрывшемся меню кликаем по пункту «Удалить фильтр с…».
Если же вы хотите сбросить фильтр в целом по таблице, тогда нужно нажать кнопку «Сортировка и фильтр» на ленте, и выбрать пункт «Очистить».
Если нужно полностью удалить фильтр, то, как и при его запуске, в этом же меню следует выбрать пункт «Фильтр», или набрать сочетание клавиш на клавиатуре Ctrl+Shift+L.
Кроме того, следует заметить, что после того, как мы включили функцию «Фильтр», то при нажатии на соответствующий значок в ячейках шапки таблицы, в появившемся меню становятся доступны функции сортировки, о которых мы говорили выше: «Сортировка от А до Я», «Сортировка от Я до А», и «Сортировка по цвету».
Урок: Как использовать автофильтр в Microsoft Excel
Как в Еxcel отсортировать по возрастанию значений
Простое распределение по возрастанию в программе Эксель осуществляется аналогичным образом, что и по алфавиту. После выделения нужного столбца в верхней части главного окна на панели задач «Главная» в разделе «редактирование» выбирается кнопка «Сортировка и фильтр», имеющая дополнительное меню. В открывшемся списке необходимо выбрать подходящий вариант. Когда данные в таблице Эксель нужно упорядочить от большего к меньшему, их следует отсортировать по убыванию, в противном случае выбирается пункт «Сортировка по возрастанию».
Если диапазон данных состоит из двух и более столбцов, при сортировке на экране должно появиться диалоговое окно для выбора дальнейших действий. Если пользователю необходимо отсортировать по возрастанию данные во всей таблице, то следует указать «автоматически расширить выделенный диапазон», во втором случае данные будут упорядочены только в выбранном столбце.
Многоуровневая сортировка
Пошаговые действия:
Если диапазоне данных имеются пустые столбцы или строкой, то Excel автоматически воспринимает их как границы сортируемого массива данных. В таком случае следует выделить все данные, подлежащие сортировке.
Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:
Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.
Как сделать сортировку по дате
В таблицах, где в хронологическом порядке отражаются определенные операции, их часто требуется отсортировать по дате проведения. Поскольку дата это сочетание целых чисел, представленных определенным образом, для правильного осуществления сортировки необходимо, чтобы был выбран соответствующий формат ячеек. Если формат указан неправильно, программа не сможет распознать значения дат, поэтому упорядочить их не получится.
Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий:
Сортировка по цвету ячейки и по шрифту
В случае если определенный диапазон табличных данных отформатирован с использованием шрифта различных цветов или заливки, пользователь может рассортировать строки по цвету, в который окрашена часть ячеек. Упорядочить данные можно также по набору значков, при создании которых применялось условное форматирование. В любом случае это можно сделать следующим образом:
Поскольку определенный порядок значков или цветов в редакторе не предусмотрен, его нужно создать самостоятельно. Для этого следует необходимо нажать кнопку «Добавить уровень» и затем повторить указанные шаги для каждого цвета или значка отдельно, исключая те, которые не нужно включать в сортировку,
Упорядочивание чисел
Сортировка чисел по возрастанию или убыванию – распространенная задача. Она помогает выстроить числовые параметры – количество товаров и клиентов, суммы продаж и доходов, и представить информацию в более наглядном виде. Сделаем в Excel сортировку этих данных.
Например, в таблице файла туристической компании хранится информация о стоимости проживания в отеле. Цена зависит от типа номера и предлагаемого питания. Турист заинтересован в поиске наиболее экономного размещения, поэтому нужно отсортировать данные по возрастанию цены.
Для этого выделяем столбец таблицы «Стоимость, руб.» и заходим на вкладку «Главная». В блоке «Редактирование» находится группа инструментов «Сортировка и фильтр». Выбираем из меню пункт «Сортировка по возрастанию».
Программа, обнаружив наличие связанных столбцов, уточняет, нужно ли выполнять сортировку в Excel только по выделенному полю, или же расширить диапазон.
Выбираем «Расширить», так как мы заинтересованы в упорядочивании полных записей таблицы, и нажимаем «Сортировка». Записи выстроены в порядке возрастания стоимости проживания.
Эта операция выполняется еще проще, если таблица отформатирована по стилю. В таком случае нужно вызвать контекстное меню столбца, щелкнув по выпадающему списку в заголовке графы. В меню находятся аналогичные команды, с помощью которых можно выполнить сортировку в Excel.
Для того чтобы числа были отсортированы верно, обратите внимание на корректность формата данных в столбце. Если значения внесены в таблицу как текст, а не число, выстраивание пройдет не в ожидаемом порядке. В таблице приведен пример сортировки чисел 10, 11, 100, 15, 110, 132 в зависимости от формата данных.
Сортировка в Excel по нескольким столбцам
Если возникла необходимость отсортировать данные в редакторе Эксель по двум или более столбцам, следует так же, как и в предыдущем случае выбрать диапазон данных и открыть окно «Настраиваемая сортировка». Далее в первой группе следует отметить заголовок столбца, данные в котором нужно упорядочить в первую очередь. Вторая группа остается без изменений, а в третьей необходимо указать желаемый тип сортировки.
Для указания критериев сортировки для второго столбца нужно добавить еще один уровень. В итоге количество уровней будет соответствовать числу столбцов, по которым необходимо отсортировать данные.
Сортировка строк
В стандартной корректировке данных в Excel изначально выставлен параметр по столбцам, но иногда нужно изменить таблицу поменяв строки.
Проходим стандартный путь – “Данные” – “Сортировка”. Выбираем меню “Настраиваемая”, “Параметры”. Для переключения варианта сортировки со столбцов на строки следует переместить галочку на “столбцы диапазона”.
Важно! В этом подпункте есть два переключателя: «строки диапазона» и «столбцы диапазона». На первый взгляд нужно выбрать первое, потому что там есть слова строки, но это не так. Следует выбрать второй пункт.
Таким образом можно отсортировать строки.
Динамическая сортировка таблицы в MS Excel
При выполнении некоторых задач в Excel, требуется установить автоматическую сортировку, которая предполагает наличие формул. В зависимости от типа данных в используемом диапазоне динамическая сортировка может быть задана тремя способами:
Программа Microsoft Excel: сортировка и фильтрация данных
в том столбце, к новым», или – «От старыхЭлементы списка списки для сортировки выберем отдельную небольшую Одним из инструментов,Столбец отсортирован независимо от если таблица содержит она не будет Следовательно, можно оперировать которые находятся в Стоит согласиться и в Excel была даты. Как правильно помощью этой функции,Существует два способа создания по данным которого «От новых к к новым» (и,диалогового окна
по другим характеристика, таблицу на листе
Простая сортировка данных
который позволяет сделать других столбцов таблицы. объединенные ячейки, то актуальной. Нужно сделать разными форматами. разных местах листа. нажать клавишу «Сохранить». комфортной. Для этого сортировать по датам, читайте в статье
«умной таблицы». Для мы собираемся проводить старым». наоборот).Настраиваемые списки которая не отсортировать Excel, чтобы отсортировать это, является сортировка.При копировании таблицы на следует их аккуратно так, чтобы значенияСделаем в учебной таблице Возможно использовать ссылки
При этом общий нужно перейти в смотрите в статье «Сортировка в Excel». того, чтобы воспользоваться фильтрацию. В нашемТаким же образом, вНо, как видим, при. в алфавитном порядке количество футболок, заказанных С помощью сортировки отдельный лист мы
разъединить, так как сортировались автоматически. Используем столбец «Итог» и на ячейки, расположенные
документ не потеряет меню «Данные», затем «Сортировка по дате Здесь рассмотрим, как первым из них, случае, мы решили
этом окне можно указанных видах сортировки
Рекомендации и примеры сортировки хорошо — например в определенные дни. Вы можете сформировать переносим только ее для сортировки такой формулы. «зальем» ячейки со в других рабочих смысл, и данные выбрать вкладку «Сортировка». в Excel».
Настраиваемая сортировка
настроить сортировку в выделяем всю область провести фильтрацию по настроить, при необходимости, по одному значению, данных по цвету высокий «,» СреднийВыделите диапазон ячеек, который
список контактной информации значения с помощью формат является неЕсть набор простых чисел, значениями разными оттенками. книгах или приложениях. остальных столбцов будут В поле «СортироватьПрограмма Microsoft Excel незаменима Excel с помощью таблицы, и, находясь имени. Например, нам и сортировку по данные, содержащие именаСортировка по дате
«и» Низкий — требуется отсортировать. В по фамилии, расположить специальной вставки. Но приемлемым. которые нужно отсортировать Выполним сортировку поПосле ввода формулы в соответствовать измененному.
по…» необходимо задать для работы с формул. Эта сортировка во вкладке «Главная», нужно оставить данные другим столбцам в одного и тогоДля удобства работы с или S, M, нашем примере мы содержимое таблицы в
- возможности сортировки позволяют
- Какими средствами располагает Excel
- по возрастанию.
- цвету:
ячейку ее можноСортировка в Excel может конкретные параметры и цифрами и таблицами. удобна тем, что кликаем по кнопке только работника Николаева. порядке приоритета. Когда
же человека, выстраиваются большим массивом данных L, XL. выделим диапазон A13:B17. алфавитном порядке или нам сортировать не для сортировки данных?Ставим курсор в соседнюю
Выделяем столбец – правая переносить или копировать быть произведена и нажать соответствующую кнопочку. Она позволяет с при изменении данных на ленте «Форматировать
Поэтому, снимаем галочки все настройки выполнены, внутри диапазона в в таблицах, ихНастраиваемые списки позволяют сортироватьОткройте вкладку же в порядке только по значениям, Чтобы дать полный ячейку и вводим кнопка мыши – в другое место. другим способом. Выделив На экране появится легкостью производить сложные в столбце, произойдет как таблицу». Данная
с имен всех жмем на кнопку произвольном порядке. постоянно нужно упорядочивать этого листа поДанные убывания. а даже по ответ на этот
формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно «Сортировка». При этом ячейка, конкретную ячейку, нужно окошко «Параметры сортировки», вычисления, строить графики автоматическая сортировка в
кнопка находится в остальных работников. «OK».А, что делать, если по определенному критерию. доставки (месяц) илина Ленте, затемПри сортировке данных в цветам шрифта или
вопрос рассмотрим его так. В качествеИз предложенного списка выбираем где располагалась формула, нажать на пиктограмму. где нужно указать
и диаграммы. Работая Excel новых данных. блоке инструментов «Стили».Когда процедура выполнена, жмемКак видим, теперь в мы хотим отсортировать
Кроме того, для по приоритету. нажмите команду
Excel в первую цветам ячеек. Поэтому на конкретных примерах. диапазона указываем весь «Сначала ячейки с становится свободной. Изменяются Но в этом порядок сортировки данных. с таблицами, которыеРассмотрим два способа
Фильтр
Далее, выбираем один из на кнопку «OK». нашей таблице все имена по алфавиту, выполнения конкретных целей,Например, чтобы отсортировать данныеСортировка очередь необходимо решить, нам нужно ещеПодготовка таблицы для правильной столбец. А в выделенным цветом».
только относительные ссылки, случае вопрос оПосле этого следует выбрать имеют вид списка, сортировки данных в понравившихся стилей, вКак видим, в таблице данные отсортированы, в но например, при иногда весь массив по дням недели. как применять сортировку: переносить и форматы и безопасной сортировки
качестве коэффициента –Соглашаемся «автоматически расширить диапазон». а абсолютные остаются расширении диапазона появляться собственный порядок сортировки Microsoft Excel дает
таблице Excel с открывшемся перечне. На остались только строки первую очередь, по совпадении имени сделать данных не нужен, или месяцам годаОткроется диалоговое окно ко всему листу данных. Для этого: данных: функцию СТРОКА соПрограмма отсортировала ячейки по
постоянными. не будет, он
и подтвердить выбор, возможность отбирать необходимые помощью формул. функционал таблицы выбор
с именем работника именам сотрудника, а так, чтобы данные а только отдельные с помощью встроенногоСортировка (таблице) или толькоВернемся к нашей исходнойВыделяем и копируем всю ссылкой на первую акцентам. Пользователь можетЧтобы переместить формулу, нужно расширяется автоматически. нажав ОК. значения и сортироватьПервый способ.
влиять не будет. Николаева.
затем, по датам располагались по дате? строки. Поэтому, чтобы настраиваемого списка, выполните. Выберите столбец, по к определенному диапазону таблице на Лист1 таблицу. ячейку. самостоятельно выбрать порядок навести курсор на
При работе с документамиДля того чтобы была столбцы. Данные можноСортировка вПосле этого, открывается диалоговоеУсложним задачу, и оставим выплат. Для этого, а
не путаться в следующие действия. которому необходимо выполнить ячеек. и снова полностьюНа другом чистом листеИзменим в исходном диапазоне сортировки цвета. Для уголок ячейки, чтобы
может потребоваться и осуществлена сортировка в упорядочить по возрастаниюExcel по возрастанию. окно, в котором в таблице толькоНо, это ещё не также для использования огромном количестве информации,Выделите столбцы, которые нужно сортировку. В данномСортировка листа (таблицы) систематизирует выделим ее, чтобы (например, Лист2)щелкаем правой цифру 7 на этого в списке
появилась двусторонняя стрелочка. многоуровневая сортировка в
Умная таблица
Excel, нужно поместить или убыванию согласноСортируем с помощью можно изменить координаты данные, которые относятся все возможности настраиваемой
некоторых других возможностей, рациональным решением будет выполнить сортировку. примере мы хотим все данные по скопировать. кнопкой мышки по 25 – «сортировка» возможностей инструмента выбираем Удерживая клавишу мышки, Excel. Чтобы воспользоваться курсор в определенную
тем значениям, которые функций «НАИМЕНЬШИЙ» или таблицы. Но, если к Николаеву за сортировки. При желании,
все в том упорядочить данные, иДля достижения наилучших результатов отсортировать данные по одному столбцу. ПриПравой кнопкой мышки щелкните ячейке A1. Из по возрастанию тоже «Настраиваемую сортировку». можно перетащить ее этой функцией, нужно ячейку и щелкнуть расположены в ячейках. «НАИБОЛЬШИЙ».
вы ранее выделили III квартал 2016 в этом окне же меню «Сортировка отфильтровать от других каждого столбца должен количеству заказов, поэтому применении сортировки к по ячейке A1 контекстного меню выбираем изменится.
В открывшемся окне вводим в нужное место. выделить ячейку в на соответствующей кнопочке Те же манипуляцииВ ячейке В5 пишем
область правильно, то года. Для этого, можно настроить сортировку и фильтр», нам результатов. Давайте выясним, содержать заголовки. выберем столбец листу связанная информация на копии таблицы
опцию: «Специальная вставка».Если необходимо сделать динамическую необходимые параметры:Автор: Ирина Демянчук списке и нажать сортировки: по возрастанию можно проделать и такую формулу. =НАИМЕНЬШИЙ(A:A;СТРОКА(A1))
больше ничего делать кликаем по значку
не по столбцам, нужно перейти в как производится сортировкаНа ленте, щелкнитеЗаказы в каждой строке на третьем листе В параметрах отмечаем сортировку по убыванию,Здесь можно выбрать порядокСортировка данных в Excel
на пункт «Сортировка»
lumpics.ru>
Как убрать сортировку в Excel
Для отмены единичной сортировки диапазона данных достаточно нажать кнопку «Отменить ввод» в левом углу экрана. Бывает так, что изменения в файле были сохранены и отменить действие невозможно. Как убрать сортировку в этом случае?
Если после сложных манипуляций с таблицей, ее нужно будет вернуть в первоначальный вид, перед выполнением сортировки следует специально создать дополнительный столбец, в котором будет отражена нумерация строк. После завершения сложного анализа числовых и текстовых данных, представленных в таблице, чтобы отменить все проделанные операции достаточно будет установить сортировку по созданному столбцу.