Сортировка в Excel – основные сведения. Как сделать в Excel сортировку данных Как упорядочить строки по возрастанию в excel

15.02.2024

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

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

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

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

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

Примечание : Задача сортировки отдельного столбца (списка) решена в статьях и .

Решение1

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

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

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

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

ИНДЕКС(Продажи;
ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(
--(СЧЁТЕСЛИ(Продажи;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
СТРОКА()-СТРОКА($E$6));1)*1000;0)
)

Данная формула сортирует столбец Объем продаж (динамический диапазон Продажи ) по убыванию. Пропуски в исходной таблице не допускаются. Количество строк в исходной таблице должно быть меньше 1000.

Разберем формулу подробнее:

Формула СЧЁТЕСЛИ(Продажи;"<"&Продажи) возвращает массив {4:5:0:2:7:1:3:5}. Это означает, что число 64 (из ячейки B7 исходной таблицы, т.е. первое число из диапазона Продажи ) больше 4-х значений из того же диапазона; число 74 (из ячейки B8 исходной таблицы, т.е. второе число из диапазона Продажи ) больше 5-и значений из того же диапазона; следующее число 23 - самое маленькое (оно никого не больше) и т.д.
Теперь вышеуказанный массив целых чисел превратим в массив чисел с дробной частью, где в качестве дробной части будет содержаться номер позиции числа в массиве: {4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008}. Это реализовано выражением &","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6)) Именно в этой части формулы заложено ограничение о не более 1000 строк в исходной таблице (см. выше). При желании его можно легко изменить, но это бессмысленно (см. ниже раздел о скорости вычислений).
Функция НАИБОЛЬШИЙ() сортирует вышеуказанный массив.
Функция ОСТАТ() возвращает дробную часть числа, представляющую собой номера позиций/1000, например 0,005.
Функция ОКРУГЛ() , после умножения на 1000, округляет до целого и возвращает номер позиции. Теперь все номера позиций соответствуют числам столбца Объемы продаж, отсортированных по убыванию.
Функция ИНДЕКС() по номеру позиции возвращает соответствующее ему число.

Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(...))

В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)

Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.

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

Тестируем

Теперь добавим новую строку в исходную таблицу. В динамически сортируемых таблицах мы должны получить соответствующую сортировку.

1. В ячейку А15 исходной таблицы введите слово Морковь ;
2. В ячейку В15 введите Объем продаж Моркови = 25;
3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица;
4. В сортированной таблице новая строка будет отображена предпоследней.

Скорость вычислений формул

На "среднем" по производительности компьютере пересчет пары таких формул массива, расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа (Формулы/ Вычисления/ Параметры вычисления ) и периодически нажимать клавишу F9 , либо отказаться от использования формул массива, заменив их столбцами с соответствующими формулами, либо вообще отказаться от динамической сортировки в пользу использования стандартных подходов (см. следующий раздел).

Альтернативные подходы к сортировке таблиц

Отсортируем строки исходной таблицы с помощью стандартного фильтра (выделите заголовки исходной таблицы и нажмите CTRL+SHIFT+L ). В выпадающем списке выберите требуемую сортировку.

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

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

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

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

Для сортировки таблицы придется создать 2 служебных столбца (D и E).

=СЧЁТЕСЛИ($B$7:$B$14;"<"&$B$7:$B$14)+1

Эта формула является аналогом для текстовых значений (позиция значения относительно других значений списка). Текстовому значению, расположенному ниже по алфавиту, соответствует больший "ранг". Например, значению Яблоки соответствует максимальный "ранг" 7 (с учетом повторов).

В столбце E введем обычную формулу:

=СЧЁТЕСЛИ($D$6:D6;D7)+D7

Эта формула учитывает повторы текстовых значений и корректирует "ранг". Теперь разным значениям Яблоки соответствуют разные "ранги" - 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):

=ИНДЕКС($B$7:$B$14;ПОИСКПОЗ(СТРОКА()-СТРОКА($G$6);$E$7:$E$14;0))

Аналогичная формула выведет соответствующий объем продаж (столбец Н).

Задача 2.1 (Двухуровневая сортировка)

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

Для этого воспользуемся результатами Задачи 1.1 и Задачи 2.

Подробности в файле примера на листе Задача2.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Предположим, что мы подготовили отчет по продажам и финансовым результатам по каналам продаж и менеджерам следующего вида:

Глаза разбегаются, правда? Для того, чтобы было легче читать эту таблицу, определимся с тем, какая информация для нас существенна, а какая нет.

Сразу отметим 2 важных момента.

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

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

Как убрать формулы из всех ячеек листа

Для того, чтобы одновременно убрать все формулы нужно:

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

2. Скопировать выделенную область (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Копировать»)

3. Воспользоваться специальной вставкой (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Специальная вставка» -> «значения»)

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

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

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

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

ВНИМАНИЕ: Это касается только объединенных ячеек в шапке . Если внутри таблицы есть объединенные ячейки, Excel не будет сортировать такие строки. Придется снимать объединение.

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

Для этого выделяем всю область таблицы вместе с шапкой, находим в главном меню «Данные» и выбираем «Сортировку»:

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

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

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

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

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

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

Какими средствами располагает Excel для сортировки данных? Чтобы дать полный ответ на этот вопрос рассмотрим его на конкретных примерах.

Подготовка таблицы для правильной и безопасной сортировки данных:

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

Чтобы отсортировать всю таблицу относительно одного столбца выполните следующее:

Данные отсортированные по всей таблице относительно столбца «Чистая прибыль».

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

Теперь отсортируем только один столбец без привязки к другим столбцам и целой таблицы:

Столбец отсортирован независимо от других столбцов таблицы.

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

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

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

Выделите таблицу и выберите инструмент «Данные»-«Сортировка».
В параметрах сортировки снова отмечаем галочкой «Мои данные содержат заголовки столбцов» и указываем: «Столбец» – Чистая прибыль; «Сортировка» – Цвет ячейки; «Порядок» – красный, сверху. И нажмите ОК.

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

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

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

Несколько облегчает пользователю поиск интересующей информации.

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

Три главных алгоритма сортировки в Excel.

1. числовые значения, сортируются по принципу «от меньшего к большему» или наоборот.

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

3. Столбцы с ячейками, содержащими даты , сортируются по принципу «от более старых к более новым» или наоборот.

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

Продолжим работу с базой данных БД2 «Выпуск металлоконструкций участком №2», созданной в статье « ».

Рассматриваемая учебная база данных состоит всего из 6-и полей (столбцов) и 10-и записей (строк). Реальные базы данных обычно содержат более десятка полей и тысячи записей! Найти необходимую информацию в такой таблице очень не просто! Именно через призму такого понимания необходимо смотреть на последующие наши действия.

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

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

В производстве при грамотном построении и тщательном ведении баз данных применение результатов анализа позволяет удвоить, а иногда и утроить объемы выпуска продукции на тех же площадях, оборудовании и с тем же составом ИТР.

Простейшая сортировка.

Простейшая сортировка в Excel осуществляется с помощью кнопок «Сортировка по возрастанию» и «Сортировка по убыванию», расположенных на панели инструментов «Стандартная». (На рисунке ниже эти кнопки обведены красным эллипсом.)

Задача №1:

Определить: какое из выпущенных изделий самое тяжелое и какова его масса? Когда это изделие было изготовлено?

1. Открываем в MS Excel файл .

2. Активируем щелчком мыши ячейку E7 с заголовком столбца «Масса 1шт, т» (можно активировать любую ячейку в интересующем нас столбце).

3. Нажимаем кнопку «Сортировка по убыванию» на панели инструментов «Стандартная».

4. Считываем ответ на поставленный вопрос в самой верхней строке базы данных (строка №8). Самое тяжелое изделие в базе – Балка 045 из заказа № 2. Изготавливалась Балка 045 с 23-его по 25-е апреля 2014-ого года (смотри записи в строках Excel №8-10).

5. Вернуть базе данных вид, предшествовавший сортировке в Excel можно (если нужно), нажав кнопку «Отменить» той же панели инструментов «Стандартная». Или можно применить «Сортировку по возрастанию» для столбца «Дата» базы данных.

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

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

Задача №2:

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

1. Активируем щелчком мыши любую ячейку базы данных (например, ячейку C11).

2. Нажимаем кнопку главного меню «Данные» и выбираем «Сортировка…».

3. В выпавшем окне «Сортировка диапазона» выбираем из выпадающих списков значения так, как показано на снимке с экрана слева и нажимаем «ОК».

4. Задача №2 выполнена. Записи, во-первых, отсортированы по номерам заказов и, во-вторых, внутри каждого из заказов расположены по алфавиту имен изделий.

Итоги.

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

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

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, отзывы и замечания пишите в комментариях.