Сортировка строк по значению в excel

Динамическая сортировка таблицы в MS EXCEL

Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) — по текстовому столбцу.

Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов; а второй — числовой Объем Продаж (см. файл примера ).

Задача1 (Сортировка таблицы по числовому столбцу)

Необходимо отсортировать строки таблицы по содержимому числового столбца (по Объему продаж). Предполагается, что пользователь постоянно заполняет строки таблицы, поэтому необходимо написать формулы с учетом добавляемых значений.

Для наглядности величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Также желтым выделены повторяющиеся значения.

Примечание: Задача сортировки отдельного столбца (списка) решена в статьях Сортированный список (ТЕКСТовые значения) и Сортированный список (ЧИСЛОвые значения).

Если числовой столбец гарантировано не содержит повторяющихся значений, то задача решается легко:

Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью Сортированный список (ЧИСЛОвые значения));
Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.

Однако, в реальных задачах числовой столбец может содержать повторы, а так как функция ВПР() в случае наличия повторов всегда выбирает только первое значение сверху (см. статью Функция ВПР() в MS EXCEL), то этот подход не годится (названия Фруктов будут выведены неправильно).

Поэтому механизм сортировки придется реализовывать по другому.

Создадим для удобства 2 Динамических диапазона Фрукты и Продажи, которые будут ссылаться на диапазоны ячеек, содержащие значения в соответствующих столбцах исходной таблицы. При добавлении новых строк в таблицу, границы этих динамических диапазонов будут автоматически расширяться.

В столбцах D и E разместим таблицу, которая будет динамически сортироваться,

В ячейке Е7 запишем зубодробительную формулу массива:

=ИНДЕКС(Продажи;
ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(
—(СЧЁТЕСЛИ(Продажи;» Похожие задачи

Сортировка данных в Excel

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.

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

Сортировка по одному критерию

В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].

Выбрать нужную кнопку: – сортировка по возрастанию или сортировка по убыванию.

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

Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.

Многоуровневая сортировка

Выделить одну ячейку из сортируемого массива данных.

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

На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
Последовательно задать уровни сортировки (определяемые именем столбца).

Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:

Имя столбца для сортировки.
Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
Порядок сортировки (по убыванию или по возрастанию).

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

Сортировка по форматированию

Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.

Пошаговый порядок действий:

Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.

Читать еще: Как в таблице excel закрепить строку

Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.

После выбора сортировки нажать кнопку ОК.

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

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

Думаю, всем известно, как выполнить сортировку по столбцу в алфавитном порядке или по возрастанию / убыванию. Это делается одним нажатием кнопки А-Я (A-Z) и Я-А (Z-A) в разделе Редактирование (Editing) на вкладке Главная (Home) либо в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data):

Однако, сортировка в Excel имеет гораздо больше настраиваемых параметров и режимов работы, которые не так очевидны, но могут оказаться очень удобны:

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

Я покажу Вам, как в Excel сортировать данные по двум или более столбцам. Работа инструмента показана на примере Excel 2010 – именно эта версия установлена на моём компьютере. Если Вы работаете в другой версии приложения, никаких затруднений возникнуть не должно, поскольку сортировка в Excel 2007 и Excel 2013 работает практически так же. Разницу можно заметить только в расцветке диалоговых окон и форме кнопок. Итак, приступим…

Кликните мышью по кнопке Сортировка (Sort) на вкладке Данные (Data) или по кнопке Настраиваемая сортировка (Custom sort) на вкладке Главная (Home), чтобы открыть диалоговое окно Сортировка (Sort).
В появившемся диалоговом окне, нажимая кнопку Добавить уровень (Add Level), установите нужное количество столбцов, по которым будет выполняться сортировка.
В выпадающих списках Сортировать по (Sort by) и Затем по (Then by) выберите столбцы, по которым нужно произвести сортировку данных. Например, Вы планируете, как провести выходные, и просматриваете список отелей, предложенный туристическим агентством. Вы хотите отсортировать этот список сначала по географическому признаку (столбец Region), затем по системе питания (столбец Board basis) и в конце концов по цене (столбец Price), как это показано на рисунке ниже:
Жмите ОК и смотрите, что получилось:
- В первую очередь отсортированы значения в столбце Region – в алфавитном порядке.
- Затем упорядочены значения в столбце Board basis так, что отели, предлагающие сервис “Всё включено” (AI) – сгруппированы в начале списка.
- И, наконец, по стоимости в столбце Price данные расположены в порядке возрастания.

Сортировать данные по нескольким столбцам в Excel оказалось совсем не сложно, правда? Однако, в диалоговом окне Сортировка (Sort) кроется значительно больше возможностей. Далее в этой статье я покажу, как сортировать по строке, а не по столбцу, и как упорядочить данные на листе в алфавитном порядке по заголовкам столбцов. Вы также научитесь выполнять сортировку данных нестандартными способами, когда сортировка в алфавитном порядке или по значению чисел не применима.

Сортировка данных в Excel по заголовкам строк и столбцов

Я полагаю, что в 90% случаев сортировка данных в Excel выполняется по значению в одном или нескольких столбцах. Однако, иногда встречаются не такие простые наборы данных, которые нужно упорядочить по строке (горизонтально), то есть изменить порядок столбцов слева направо, основываясь на заголовках столбцов или на значениях в определённой строке.

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

Нам нужно отсортировать этот список фотокамер по наиболее важным для нас параметрам. Для примера первым делом выполним сортировку по названию модели:

Выбираем диапазон данных, которые нужно сортировать. Если нам нужно, чтобы в результате сортировки изменился порядок всех столбцов, то достаточно выделить любую ячейку внутри диапазона. Но в случае с нашим набором данных такой способ не допустим, так как в столбце A перечисляются характеристики камер, и нам нужно, чтобы он остался на своём месте. Следовательно, выделяем диапазон, начиная с ячейки B1:
На вкладке Данные (Data) нажимаем кнопку Сортировка (Sort), чтобы открыть одноимённое диалоговое окно. Обратите внимание на параметр Мои данные содержат заголовки (My data has headers) в верхнем правом углу диалогового окна. Если в Ваших данных нет заголовков, то галочки там быть не должно. В нашей же таблице заголовки присутствуют, поэтому мы оставляем эту галочку и нажимаем кнопку Параметры (Options).
В открывшемся диалоговом окне Параметры сортировки (Sort Options) в разделе Сортировать (Orientation) выбираем вариант Столбцы диапазона (Sort left to right) и жмём ОК.
Следующий шаг – в диалоговом окне Сортировка (Sort) под заголовком Строка (Row) в выпадающем списке Сортировать по (Sort by) выбираем строку, по значениям которой будет выполнена сортировка. В нашем примере мы выбираем строку 1, в которой записаны названия фотокамер. В выпадающем списке под заголовком Сортировка (Sort on) должно быть выбрано Значения (Values), а под заголовком Порядок (Order) установим От А до Я (A to Z).

В результате сортировки у Вас должно получиться что-то вроде этого:

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

Наша задача – повторить описанные выше шаги 1 – 3. Затем на шаге 4 вместо строки 1 выбираем строку 4, в которой указаны розничные цены (Retail Price). В результате сортировки таблица будет выглядеть вот так:

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

Надеюсь, теперь стало ясно, как работает сортировка по строке в Excel. Но что если наши данные должны быть упорядочены не по алфавиту и не по возрастанию / убыванию?

Сортировка в произвольном порядке (по настраиваемому списку)

Если нужно упорядочить данные в каком-то особом порядке (не по алфавиту), то можно воспользоваться встроенными в Excel настраиваемыми списками или создать свой собственный. При помощи встроенных настраиваемых списков Вы можете сортировать, к примеру, дни недели или месяцы в году. Microsoft Excel предлагает два типа таких готовых списков – с сокращёнными и с полными названиями.

Предположим, у нас есть список еженедельных дел по дому, и мы хотим упорядочить их по дню недели или по важности.

Начинаем с того, что выделяем данные, которые нужно сортировать, и открываем диалоговое окно Сортировка (Sort), точно так же, как в предыдущих примерах – Данные >Сортировка (Data > Sort).
В поле Сортировать по (Sort by) выбираем столбец, по которому нужно выполнить сортировку. Мы хотим упорядочить наши задачи по дням недели, то есть нас интересует столбец Day. Затем в выпадающем списке под заголовком Порядок (Order) выбираем вариант Настраиваемый список (Custom list), как показано на снимке экрана ниже:
В диалоговом окне Списки (Custom Lists) в одноимённом поле выбираем нужный список. В нашем столбце Day указаны сокращённые наименования дней недели – кликаем по соответствующему варианту списка и жмём ОК.

Готово! Теперь домашние дела упорядочены по дням недели:

Замечание: Если Вы планируете вносить изменения в эти данные, помните о том, что добавленные новые или изменённые существующие данные не будут отсортированы автоматически. Чтобы повторить сортировку, нажмите кнопку Повторить (Reapply) в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data).

Как видите, сортировка данных в Excel по настраиваемому списку – задача вовсе не сложная. Ещё один приём, которому мы должны научиться – сортировка данных по собственному настраиваемому списку.

Сортировка данных по собственному настраиваемому списку

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

Повторите шаги 1 и 2 из предыдущего примера. Когда откроется диалоговое окно Списки (Custom Lists), в одноимённом столбце слева нажмите НОВЫЙ СПИСОК (NEW LIST) и заполните нужными значениями поле Элементы списка (List entries). Внимательно введите элементы Вашего списка именно в том порядке, в котором они должны быть расположены в результате сортировки.

Нажмите Добавить (Add), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК.

Вот так выглядит наш список домашних дел, упорядоченных по важности:

Подсказка: Для создания длинных настраиваемых списков удобнее и быстрее импортировать их из существующего диапазона. Об этом подробно рассказано в статье Создание настраиваемого списка из имеющегося листа Excel.

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

И вот, наконец, наш список домашних дел упорядочен в наивысшей степени логично, сначала по дням недели, затем по важности

Сортировка строк в Excel

Обычно сортировка данных в Экселе производится по столбцам, но иногда возникает необходимость упорядочить данные в строках, то есть не вертикально, а горизонтально.

Как же сделать сортировку строк в Excel?

Для этого в программе уже заложены инструменты и давайте разберем эту задачу на конкретном примере.

Например, у нас есть сводная таблица с характеристиками разных ноутбуков и есть необходимость упорядочить их в порядке возрастания объема оперативной памяти.

Наши действия будут следующими.

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

На вкладке Главная щелкаем по Сортировка и фильтр, а далее Настраиваемая сортировка (2). В появившемся окне нажимаем на кнопку Параметры (3) и в следующем диалоговом окне указываем, что сортировка будет производиться по столбцам (4).

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

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

Нажимаем ОК и получаем результат.

Обратите внимание, что сортировке подверглись не только данные из строки Оперативная память. По факту данные из этой строки являлись критерием сортировки и в выбранном нами диапазоне столбцы были упорядочены в соответствии с объемом оперативной памяти.

Если нужно отсортировать строки в Excel по алфавиту, то делается это точно таким же способом. Например, чтобы расставить ноутбуки в алфавитном порядке, нам нужно будет упорядочить тот же самый диапазон по второй строке, но при этом в разделе Порядок нужно выбрать От А до Я.

Сортировка данных в Excel по строкам и столбцам с помощью формул

Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.

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

Порядок сортировки в Excel

Существует два способа открыть меню сортировки:

Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.
Открыть вкладку «Данные» — диалоговое окно «Сортировка».

Часто используемые методы сортировки представлены одной кнопкой на панели задач:

Сортировка таблицы по отдельному столбцу:

Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.

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

Сортировка по цвету ячейки и по шрифту

Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.

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

Выделяем столбец – правая кнопка мыши – «Сортировка».
Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
Соглашаемся «автоматически расширить диапазон».

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

В открывшемся окне вводим необходимые параметры:

Здесь можно выбрать порядок представления разных по цвету ячеек.

По такому же принципу сортируются данные по шрифту.

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

Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.

Открываем меню «Настраиваемая сортировка». Назначаем первый критерий.
Нажимаем кнопку «Добавить уровень».
Появляются окошки для введения данных следующего условия сортировки. Заполняем их.

Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.

Сортировка строк в Excel

По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:

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

Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.

Случайная сортировка в Excel

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

Например, нужно расположить в случайном порядке набор неких чисел.

Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.

Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.

Динамическая сортировка таблицы в MS Excel

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

Есть набор простых чисел, которые нужно отсортировать по возрастанию.
Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.

Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.

Для динамической сортировки текстовых значений понадобятся формулы массива.

Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон «фрукты» еще одно значение «помело» и проверим: