Сортировка в Эксель — это одна из базовых операций, позволяющая быстро структурировать данные для анализа.
Как же может использоваться сортировка данных в Excel?
Например, у вас есть список фамилий и вы хотите расположить их в алфавитном порядке:
Сортировка таблицы по фамилии
Или же у вас есть перечень товаров, который нужно упорядочить по цене от самого дешевого, до самого дорогого:
Сортировка товаров по цене
Для решения этих задач как раз и подойдет сортировка в Экселе. То есть сортировка данных в Экселе позволяет быстро упорядочить информацию в таблице, что помогает, как при поиске определенных данных, так и при анализе числовых значений.
Какими средствами располагает Excel для сортировки данных
Сортировка данных в Excel может производиться:
— в алфавитном порядке — от А к Я или от Я к А;
— по значениям — по убыванию или возрастанию;
— по дате и времени.
При этом сортировать данные в Эксель можно в одном или нескольких столбцах одновременно и, кроме этого, можно выполнять сортировку по спискам, которые создаются самим пользователем, а также по по цвету ячейки или текста.
Сортировка данных в MS Excel
Как сделать сортировку по алфавиту
Для сортировки по алфавиту необходимо сделать следующее — выделите любую ячейку (1, см. рис. ниже) того столбца, данные которого вы хотите отсортировать, затем на вкладке Главная щелкните по инструменту Сортировка и фильтр (2). Откроется меню, из которого нужно выбрать направление сортировки (3).
Сортировка по алфавиту
Если необходимо отсортировать данные от А до Я, то выбираем соответствующий инструмент — Сортировка от А до Я . Если же нужен обратный порядок сортировки, то — Сортировка от Я до А .
При этом обратите внимание на то, что сортировка данных осуществляется по всему диапазону, то есть и данные из соседних столбцов участвуют в упорядочивании. По сути при сортировке мы определяем столбец, который будет отвечать за порядок отображения всех данных электронной таблицы, затем все строки электронной таблицы будут упорядочены в соответствии с порядком значений сортируемого столбца. Особенно хорошо это видно, если заранее придать разное цветное форматирование каждой из строк электронной таблицы.
Сортировка по алфавиту
Как отсортировать по алфавиту столбец
Если же нужно отсортировать данные по алфавиту в отдельном столбце без привязки к информации из смежных столбцов, или же необходимо отсортировать данные из определенного диапазона, то достаточно выделить данный диапазон или отдельные ячейки и вызвать на выделении контекстное меню щелчком правой кнопки мыши. Затем из меню нужно выбрать Сортировка , а далее указать направление сортировки.
Сортировка данных в отдельном столбце
Будет изменен порядок значений только в выделенном диапазоне:
Сортировка в выделенном диапазоне ячеек
Как отсортировать таблицу по алфавиту
Если диапазон значений был превращен в таблицу, то сортировку данных можно производить еще проще. Достаточно щелкнуть по кнопке фильтра (кнопка со стрелкой вниз) в заголовке того столбца, данные которого необходимо отсортировать по алфавиту и выбрать из меню нужное направление сортировки.
Сортировка данных в умной таблице
Подробнее о сортировке данных по алфавиту в Экселе смотрите в видео:
Как убрать сортировку в Excel
Если результаты сортировки данных в Экселе вас не удовлетворяют и вы бы хотели отменить сортировку, то самым простым способом будет стандартная операция отмены последних действий. Просто нажимаем на соответствующую кнопку на панели быстрого доступа программы или используем сочетание клавиш Ctrl + Z .
Отмена сортировки
Само собой, такой способ возможно использовать лишь в том случае, если сортировка была произведена только что, или же если для вас неважны все последующие после сортировки операции и вы готовы их также отменить.
Если же после сортировки вы планируете изменять какие-то данные в таблице, то можно прибегнуть к хитрости, которая в дальнейшем позволит вам привести таблицу к первоначальному виду.
Создайте вспомогательный столбец, в который поместите порядковые номера строк вашей электронной таблицы. При этом важно, чтобы нумерация задавалась не формулой. Самый простой способ — это воспользоваться автозаполнением.
Создание нумерации автозаполнением
После того, как вспомогательный столбец создан, сделайте нужную сортировку, например, сортировку по фамилии в алфавитном порядке. Затем отредактируйте данные в таблице необходимым образом и в конце вновь проведите сортировку, но уже по вспомогательному столбцу с нумерацией.
Детально эта операция показана в видео:
Почему не сортируется список по алфавиту?
При сортировке данных по алфавиту в Экселе могут возникнуть проблемы. Например, список не сортируется или сортируется некорректно. В этом случае необходимо проверить формат всех (или «проблемных») ячеек сортируемого столбца. Формат должен быть одинаковым для всех его ячеек — Общий , Числовой или Текстовый .
Числовой формат данных
Например, если в столбце есть ячейки разных форматов, то при сортировке ячейки, содержащие числа в числовом формате (несмотря на их значения), окажутся выше ячеек с числами в текстовом формате.
Чтобы решить эту проблему нужно выделить весь столбец и для всех его ячеек указать один формат.
Изменение числового форматирования
Еще одной причиной может стать пробел в начале слова или числа. Особенно этот момент нужно проверить в случае импортирования данных из других программ. Более подробно об этом рассказано в видео:
Как в Excel отсортировать по убыванию (возрастанию)
С помощью Эксель можно сортировать данные по убыванию или возрастанию. Для этого нужно выделить любую ячейку столбца, который необходимо отсортировать по возрастанию или убыванию, и на вкладке Главная щелкнуть по кнопке Сортировка и фильтр . Появится меню, из которого нужно выбрать Сортировку по возрастанию , если нужно сортировать по возрастанию (сортировка от меньшего к большему) или же выбрать Сортировка по убыванию , для того, чтобы отсортировать числа по убыванию (сортировка от большего к меньшему).
Сортировка по возрастанию/убыванию
При сортировке чисел в Excel по возрастанию или убыванию, используются те же кнопки, что и при сортировке по алфавиту, изменяется лишь их название.
При сортировке по возрастанию/убыванию могут возникать проблемы, связанные с разным форматом ячеек в одном столбце. Подробнее о решении подобных проблем смотрите выше.
Сортировка в Excel по дате
Если в таблице с данными есть столбцы, содержащие информацию о дате или времени, то можно производить сортировку по ним. При этом важно, чтобы данные в ячейках столбца не только визуально выглядели, как дата или время, но и чтобы сам формат ячеек был соответствующим (1, см. рис. ниже).
Чтобы отсортировать в Excel по дате, необходимо выделить любую ячейку соответствующего столбца (2) и на вкладке Главная щелкнуть по кнопке Сортировка и фильтр . Из появившегося меню (2) нужно выбрать Сортировка от старых к новым , если нужно отсортировать от более ранних дат к более поздним или же произвести сортировку от более новых значений времени к более старым — Сортировка от новых к старым .
Сортировка по дате
Более подробно об этом в видео:
Как сортировать по цвету в Excel
Во-первых, стоит сказать, что в Эксель есть два способа сортировки по цвету — сортировка по цвету ячейки и сортировка по цвету текста. Оба варианта могут быть использованы.
Логично, что сортировка по цвету используется в том случае, если в таблице задано цветовое форматирование текста или заливка фона. Также сортировку по цвету можно применять, если таблица была отформатирована с помощью инструмента условного форматирования.
Чтобы отсортировать таблицу по цвету необходимо выделить любую ячейку диапазона (1), который требуется отсортировать и из меню Сортировка и фильтр (2) выбрать Настраиваемая сортировка (3). В открывшемся окне из выпадающего списка Сортировать по нужно указать столбец, который будет определять сортировку (4). Далее указать вариант сортировки (5)- Цвет ячейки или Цвет шрифта . И из выпадающего списка Порядок необходимо выбрать один из цветов (6). Эти цвета Эксель автоматически берет из таблицы, нам остается лишь указать нужный. В завершение необходимо указать направление сортировки (7), то есть сверху или снизу будут находиться ячейки с выбранным нами цветом.
Источник: dzen.ru
Динамическая сортировка таблицы в EXCEL
Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) — по текстовому столбцу.
Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов ; а второй — числовой Объем Продаж (см. файл примера ).
Задача1 (Сортировка таблицы по числовому столбцу)
Необходимо отсортировать строки таблицы по содержимому числового столбца (по Объему продаж). Предполагается, что пользователь постоянно заполняет строки таблицы, поэтому необходимо написать формулы с учетом добавляемых значений.
Для наглядности величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Также желтым выделены повторяющиеся значения.
Примечание : Задача сортировки отдельного столбца (списка) решена в статьях Сортированный список (ТЕКСТовые значения) и Сортированный список (ЧИСЛОвые значения) .
Решение1
Если числовой столбец гарантировано не содержит повторяющихся значений, то задача решается легко:
- Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью Сортированный список (ЧИСЛОвые значения) );
- Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.
Однако, в реальных задачах числовой столбец может содержать повторы, а так как функция ВПР() в случае наличия повторов всегда выбирает только первое значение сверху (см. статью Функция ВПР() в MS EXCEL ), то этот подход не годится (названия Фруктов будут выведены неправильно).
Поэтому механизм сортировки придется реализовывать по другому.
Создадим для удобства 2 Динамических диапазона Фрукты и Продажи , которые будут ссылаться на диапазоны ячеек, содержащие значения в соответствующих столбцах исходной таблицы. При добавлении новых строк в таблицу, границы этих динамических диапазонов будут автоматически расширяться.
В столбцах D и E разместим таблицу, которая будет динамически сортироваться,
В ячейке Е7 запишем зубодробительную формулу массива :
Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(. ))
В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)
Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.
Для наглядности, величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Как видно, сортировка работает.
Тестируем
Теперь добавим новую строку в исходную таблицу. В динамически сортируемых таблицах мы должны получить соответствующую сортировку.
1. В ячейку А15 исходной таблицы введите слово Морковь ; 2. В ячейку В15 введите Объем продаж Моркови = 25; 3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица; 4. В сортированной таблице новая строка будет отображена предпоследней.
Скорость вычислений формул
На «среднем» по производительности компьютере пересчет пары таких формул массива, расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа ( Формулы/ Вычисления/ Параметры вычисления ) и периодически нажимать клавишу F9 , либо отказаться от использования формул массива, заменив их столбцами с соответствующими формулами, либо вообще отказаться от динамической сортировки в пользу использования стандартных подходов (см. следующий раздел).
Альтернативные подходы к сортировке таблиц
Отсортируем строки исходной таблицы с помощью стандартного фильтра (выделите заголовки исходной таблицы и нажмите CTRL+SHIFT+L ). В выпадающем списке выберите требуемую сортировку.
Получим идентичный нашему вариант таблицы, но при добавлении в таблицу новых значений придется применять фильтр заново.
Также можно воспользоваться инструментом Сортировка ( Данные/ Сортировка и Фильтр/ Сортировка ). Для этого нужно выделить все значения исходной таблицы не включая заголовок, вызвать инструмент Сортировка, выбрать столбец, по которому требуется сортировать и вариант сортировки.
Получим идентичный нашему вариант таблицы, но при добавлении новых значений также придется применять фильтр заново.
При использовании Таблиц в формате EXCEL2007 мы также не получим динамической сортировки. Новое значение (Картофель) останется последним в исходной таблице (до принудительной сортировки таблицы через фильтр), не смотря на его значение продаж (200).
Какой вариант предпочтительней — как всегда — выбирать разработчику.
Еще одна формула массива (+дополнительный столбец). Задача1.1
Сортировку таблицы можно сделать с помощью другой, более простой формулы массива , но нам понадобится дополнительный (служебный) столбец D (см. файл примера лист Пример2 ):
В столбце F содержится отсортированный столбец В (объем продаж). Формула возвращает позицию значения объема продаж. Например, число 86 находится в 5-й строке таблицы.
Для повторов выражение ЕСЛИ(F8=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0) будет возвращать несколько значений: , т.е. число 74 находится в строках 2 и 8.
С помощью функции НАИБОЛЬШИЙ() сначала выводится 2, затем 8 (в разных строках).
Эта формула более наглядна, чем рассмотренная выше в начале статьи, но требует наличия дополнительного столбца.
Задача2 (Сортировка таблицы по текстовому столбцу)
Отсортируем строки таблицы по содержимому Текстового столбца (по Фруктам).
Примечание : Про сортировку списка текстовых значений можно прочитать в статье Сортированный список в MS EXCEL (ТЕКСТовые значения)
Как и в предыдущей задаче предположим, что в столбце, по которому ведется сортировка имеются повторы (названия Фруктов повторяются).
Для сортировки таблицы придется создать 2 служебных столбца (D и E).
Эта формула является аналогом ранга для текстовых значений (позиция значения относительно других значений списка). Текстовому значению, расположенному ниже по алфавиту, соответствует больший «ранг». Например, значению Яблоки соответствует максимальный «ранг» 7 (с учетом повторов).
В столбце E введем обычную формулу:
Эта формула учитывает повторы текстовых значений и корректирует «ранг». Теперь разным значениям Яблоки соответствуют разные «ранги» — 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):
Аналогичная формула выведет соответствующий объем продаж (столбец Н).
Задача 2.1 (Двухуровневая сортировка)
Теперь снова отсортируем исходную таблицу по Объему продаж. Но теперь для повторяющихся значений (в столбце А три значения 74), соответствующие значения выведем в алфавитном порядке.
Для этого воспользуемся результатами Задачи 1.1 и Задачи 2.
Подробности в файле примера на листе Задача2.
Источник: excel2.ru
Сортировка данных в Excel
Сортировать данные в Excel умеют даже новички. Трудно не заметить сразу три кнопки на ленте.
Но иногда спрашивают, как отсортировать данные в таблице Excel по столбцам, по цвету и т.п. Все эти задачи легко решаются в Excel.
Сортировка в Excel по нескольким столбцам
Сортировку строк в Excel можно осуществить по нескольким столбцам одновременно. В этом случае вначале происходит сортировка по первому указанному столбцу. Затем те строки, где ячейки в отсортированном столбце повторяются, сортируются по второму указанному столбцу и т.д. Таким образом, отчет о продажах можно упорядочить вначале по региону, затем в рамках каждого региона по менеджеру, затем в рамках одного региона и менеджера по группе товаров.
Выделив одну ячейку или всю таблицу, вызываем на ленте команду сортировки. Если нужно, ставим галочку Мои данные содержат заголовки и добавляем необходимое количество уровней.
Сортировка по собственному списку
Большинство пользователей знает, как отсортировать строки в Excel по алфавиту или возрастанию чисел. Но иногда нужно расставить строки в заданном порядке, который не обязательно соответствует алфавиту или возрастающей последовательности. Это может быть иерархия элементов, города и страны в удобной последовательности и т.п. В общем то, что имеет свою логику.
Самый простой (а, значит, достойный внимания) способ заключается в том, чтобы в столбце рядом проставить числа в соответствии с желаемыми порядком строк, а затем осуществить сортировку таблицы по этому столбцу.
Способ хорош, но требует создания дополнительного временного столбца. При частом повторении операции будет отнимать время. Если приходится сортировать одни и те же данные, то можно создать специальный список, на основе которого затем и произойдет сортировка. Это тот же список, который используется в автозаполнении ячеек.
Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки…
Здесь создаем вручную или импортируем список, отсортированный в нужном порядке.
Теперь в окне сортировки в поле Порядок нужно выбрать Настраиваемый список…
И в следующем окне указать нужный список.
Созданным списком можно пользоваться и в других файлах Excel.
Сортировка в Excel по цвету ячейки, шрифта, значку
В настройках сортировки также есть возможность использовать цвет ячейки, шрифта и значок (из условного форматирования). Если для форматирования отдельных ячеек использовать заливку (например, для указания проблемных товаров или клиентов), то затем их легко вывести вверх таблицы с помощью сортировки.
Сортировка по столбцам
Сортировать в Excel можно и по столбцам. Для этого выделяем таблицу вместе с названиями столбцов. Затем в окне сортировки вначале нажимаем Параметры и ставим переключатель на столбцы диапазона.
Последующие настройки обычные: задаем строку (!) и порядок. Единственное, теперь нельзя использовать названия строк (по аналогии с названием столбцов), будут только номера.
Сортировка промежуточных итогов
В Excel есть такой инструмент, как Промежуточные итоги. Вкратце, он нужен для того, чтобы автоматически создавать промежуточные итоги под группой однородных по некоторому признаку ячеек.
Это отличная функция, но как сделать, чтобы вверху были группы с наибольшей суммой? Ручной труд, конечно, в почете, но есть и другой вариант. Достаточно применить обычную сортировку к сгруппированным данным.
Внутреннее содержимое групп не изменится. Общий итог останется внизу.
Сортировка строк в случайном порядке
Интересный случай, когда нужно перемешать строки в случайном порядке. Создаем рядом столбец с функцией случайного числа СЛЧИС. Теперь сортируем таблицу по этому столбцу.
Случайные числа перемешают строки в хаотичном порядке. Не забудьте удалить лишний столбец.
Вот такие замечательные приемы. Если я что-то забыл, напишите, пожалуйста в комментариях.
Источник: statanaliz.info