Как отсортировать данные в excel

Вариант 2: Настраиваемая сортировка

Более продвинутый вариант, позволяющий задать сразу несколько условий упорядочения, – это настраиваемая сортировка, выполняющаяся через соответствующее окно. Давайте разберем сортировку по двум разным параметрам:

1. Вы можете не выделять заранее столбец, если для него задано название. Вызовите то же меню редактирования, но на этот раз нажмите по «Настраиваемая сортировка».

2. Выберите вариант с расширением или сохранением диапазона, если предварительно выделили столбец.

3. Вы увидите первый уровень сортировки, который и стоит настроить с самого начала.

4. Выберите столбец для сортировки из первого выпадающего списка.

5. Далее укажите вариант сортировки. Это может быть значение, цвет шрифта или ячейки, знаки условного форматирования.

6. В завершение укажите, в каком порядке необходимо отсортировать данные.

7. Если затем нужно применить еще один вариант сортировки, добавьте уровень и настройте его должным образом.

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

Упорядочивание чисел

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

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

Для этого выделяем столбец таблицы «Стоимость, руб.» и заходим на вкладку «Главная». В блоке «Редактирование» находится группа инструментов «Сортировка и фильтр». Выбираем из меню пункт «Сортировка по возрастанию».

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

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

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

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

В таблице приведен пример сортировки чисел 10, 11, 100, 15, 110, 132 в зависимости от формата данных.

Работа с таблицами Excel

Сортировка данных по заданным критериям

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

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

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

Рис. 5.11. Результаты сортировки по фамилиям

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

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

Рис. 5.12. Диалоговое окно «Сортировка диапазона» с выбранным порядком сортировки по окладу и по фамилии

Сортировка по второму полю (по фамилии) будет производиться только для тех строк, у которых в столбце «Оклад» имеются одинаковые значения. В нашем случае один и тот же размер оклада имеют работники Карпова и Петров. В соответствии с условием второй сортировки в списке сначала будет идти фамилия Карпова, а затем Петров. Полученный в результате такой сортировки список представлен на рис. 5.13.

Рис. 5.13. Список отсортирован «Сначала» по критерию «Оклад» (по убыванию), а затем по критерию «Фамилия» (в алфавитном порядке по возрастанию)

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

Сортировать по дате Пример

Чтобы выполнить быстрый сортировать по дате в порядке возрастания — от старости до новейшего — для примера на изображении выше, будут выполняться следующие шаги:

1. Выделите диапазон сортируемых ячеек.
2. Нажми на Главная вкладку ленты.
3. Нажми на Сортировка и фильтр на ленте, чтобы открыть раскрывающийся список.
4. Нажмите на Пользовательская сортировка в раскрывающемся списке, чтобы Сортировать диалоговое окно.
5. Под колонка заголовок в диалоговом окне, выберите Заимствованные из раскрывающегося списка, чтобы сначала отсортировать данные по столбцу Имя.
6. Сортировать по параметр остается установленным для Ценности — поскольку сортировка основана на фактических данных в таблице.
7. Под Порядок сортировки заголовок, выберите От старых к новинкам в раскрывающемся списке, чтобы отсортировать даты
8. Нажмите Хорошо в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.

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

Хитрости сортировки

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

Случай 1. Сортировка по смыслу, а не по алфавиту

Представим весьма распространенную ситуацию: есть таблица, в которой присутствует столбец с названием месяца (январь, февраль, март. ) или дня недели (пт, вт, ср. ). При простой сортировке по данному столбцу Excel располагает элементы по-алфавиту (т.е. от А до Я):

А хотелось бы, конечно, получить на выходе привычную последовательность с января по декабрь или с понедельника по вторник. Такое можно легко реализовать с помощью особой сортировки по пользовательскому списку (custom list sorting).

Выделите таблицу и нажмите большую кнопку Сортировка на вкладке Данные (Data — Sort) . Откроется диалоговое окно, в котором нужно задать поле (столбец) сортировки и в последнем раскрывающемся списке выбрать тип сортировки Настраиваемый список (Custom List) :

После этого откроется следующее окно, в котором можно выбрать нужную нам последовательность месяцев или дней недели:

Если нужного списка (например, месяцев, но на английском) нет, то его можно ввести в правое поле, выбрав опцию Новый список (New List) :

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

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

должности (директор, замдиректора, руководитель департамента, начальник отдела. )
воинские звания (генерал, полковник, подполковник, майор. )
сертификации (TOEFL, ITIL, MCP, MVP

)
клиентов или товары по важности лично для вас (виски, текила, коньяк, вино, пиво, лимонад. )
и т.д.

Случай 2. Сортировка текста и чисел одновременно

Предположим, что в нашей таблице есть столбец с кодами различных запчастей и агрегатов для автомобилей (part number). Причем крупные детали в сборе (например, коробка передач, двигатель, рулевое управление) обозначаются чисто цифровым кодом, а мелкие детали, которые в них входят — кодом с добавлением уточняющего номера через, допустим, точку. Попытка отсортировать такой список обычным образом приведет к нежелательному результату, т.к. Excel сортирует отдельно числа (номера крупных агрегатов в сборе) и отдельно текст (номера мелких деталей с точками):

А хотелось бы, конечно, получить список, где после каждого крупного агрегата будут идти его детали:

Чтобы это реализовать, нужно временно добавить еще один столбец к нашей таблице, в котором превратить все коды в текст с помощью функции ТЕКСТ:

Если затем произвести сортировку по этому столбцу, то Excel спросит о том, как ему сортировать числа и текст:

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

голоса

Рейтинг статьи

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

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

• Первыми идут цифры от меньшего к большему;
• Затем идут специальные символы ($, %, * и т.д.);
• За ними следуют знаки препинания;
• Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).

Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:

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

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

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

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

Установка порядка первого столбца таблицы при выборе первого варианта:

Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.

Те же самые действия, но при выборе второго варианта:

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

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

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

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

Для примера будем использовать следующую таблицу:

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

Выделите диапазон нашей таблицы и кликните по значку «Сорировка». Появится следующее окно:

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

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

Следующий выпадающий список задает порядок: по возрастанию, по убыванию и т.п.

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

Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».

Рассмотрим получившийся результат:

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

Таким образом, можно задавать сортировку в Excel по значительному количеству столбцов.

Также в окне сортировки можно задавать различные параметры:

• Поставив флажок на поле «Мои данные содержать заголовки», программа учтет, что верхние ячейки диапазонов упорядочивать не нужно;
• Нажав кнопку «Параметры» Вы получите окно, в котором можно задать:

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

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

Способ 3: Формула сортировки

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

Шаг 1: Создание вспомогательной формулы

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

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

Теперь диапазон выделенных ячеек имеет собственное имя, отвечающее за его содержимое, — в нашем случае это фрукты. Если вводите несколько слов в названии, не ставьте пробел, а используйте вместо него нижнее подчеркивание: «(пример_текста)».

В новой клетке создадим формулу СЧЁТЕСЛИ, которая считает ячейки, удовлетворяющие условие. В качестве диапазона указывайте созданную только что группу, затем первую ячейку для сравнения. В итоге первоначальный вид формулы таков: =СЧЁТЕСЛИ(Фрукты;A1).

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

Растяните формулу, зажав край ячейки, до конца будущего списка для сортировки.

Переименуйте диапазон с числами в группу – это понадобится при составлении следующей формулы.

Шаг 2: Создание формулы сортировки

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

1. В новой ячейке начните вводить =ПОИСКПОЗ(СТРОКА(A1). Эта формула отвечает за поиск позиции строки, из-за чего и следует указать аргумент “A1”.

Далее для простоты добавления именных диапазонов перейдите в “Формулы”, разверните меню “Определенные имена” и выберите “Использовать в формуле”.

Добавьте диапазон со вспомогательной формулой и укажите для него тип сопоставления “Точное совпадение” в выпадающем списке, который появится после добавления “;”.

Завершите создание формулы, обернув ее в функцию ИНДЕКС, которая будет работать с массивом названий.

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

Теперь вы получите корректно работающий динамический список, отсортированный по алфавиту.

Для упрощения понимания отдельно предоставляем полную формулу:

=(ИНДЕКС(Фрукты;ПОИСКПОЗ(СТРОКА(A1);номер_слова;0))), вам же останется только отредактировать ее под свои цели и растянуть на необходимый диапазон ячеек.

Шаг 3: Модернизации формулы для повторных названий

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

1. Откройте вспомогательную формулу и уберите знак “, чтобы СЧЁТЕСЛИ работал только при условии равенства.

Добавьте вторую часть – +СЧЁТЕСЛИ($A$1:A1;A1), позволяющую нормально записывать одинаковые слова в последовательном порядке.

Снова растяните формулу, чтобы она изменилась на всех ячейках.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.

Наши специалисты постараются ответить максимально быстро.

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

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

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

Возможности сортировки данных в Excel списком

Excel позволяет анализировать данные по разным параметрам. Элементом комплексного анализа является классификация. Для работы элемента информация должна быть представлена ​​в виде списка, отформатированного определенным образом.

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

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

В Excel вы можете сортировать списки:

• вверх;
• нисходящий;
• по заданным пользователем параметрам (дни недели, месяцы и т.д.).

Сортировка по возрастанию означает сортировку данных:

• от 0 до 9;
• от А до Я;
• от А до Я.

По убыванию — в обратном порядке.

Для вызова пользовательского порядка сортировки необходимо выполнить следующую последовательность: «данные» — «сортировка» — «порядок» — «пользовательский список». Появляется такое меню.

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

У нас есть обучающая таблица со списком сотрудников и зарплат:

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

Диапазон сортировки расширяется автоматически. Получается так:

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

Как сортировать по алфавиту в Excel?

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

«Сортировать по» — выберите название столбца, значения которого будут отсортированы по алфавиту. У нас есть имя. Сортировкой мы выбираем, на какие параметры будет ориентироваться инструмент. Может быть цвет ячейки, шрифт, значок ячейки. В нашем примере «значения» (разбор значений в ячейке). «Заказ»: от А до Я или от Я до А. Давайте сделаем прямой заказ. Нажмите «ОК.

Результат алфавитной сортировки:

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

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

Убедитесь, что столбец даты имеет правильный формат («Дата»). Выбираем столбец и сортируем даты от старых к новым (от новых к старым).

Выбирайте от старого к новому. Значения списка расположены в хронологическом порядке.

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

Сортируем данные по смыслу

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

Выберите новый список в меню Пользовательский список. Мы формируем иерархию любого объекта. Разделителями могут быть запятые или клавиша Enter».

Когда иерархические объекты введены, нажмите «Добавить».

А вот и результат сортировки обучающей таблицы по позициям:

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

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

Данные в таблице будут отсортированы по различным параметрам. В столбик, например, по возрастанию. В другом («тогда по…») — по алфавиту и т.д. В результате диапазон будет сформирован по заданной комплексной сортировке.

Уровни можно добавлять, удалять, копировать, обменивать с помощью кнопок «Вверх» — «Вниз».

Это самые простые способы сортировки данных в таблицах. Для этих целей можно использовать формулы. Для этих целей подходят встроенные инструменты «МАЛЕНЬКИЙ», «СТРОКА», СЧЁТЕСЛИ».

Настраиваемые списки

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

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

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

Как сделать сортировку листа (таблицы, списка) в Excel

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

1. Выделите ячейку в столбце, по которому необходимо выполнить сортировку. В нашем примере мы выделим ячейку C2.
2. Откройте вкладку Данные на Ленте, затем нажмите команду Сортировка от А до Я, чтобы отсортировать по возрастанию, или команду Сортировка от Я до А, чтобы отсортировать по убыванию. В нашем примере мы выберем команду Сортировка от А до Я.
3. Таблица будет отсортирована по выбранному столбцу, т.е. по фамилии.

При сортировке таблицы или списка в Excel необходимо, чтобы они были отделены от посторонних данных на листе как минимум одной строкой или столбцом. В противном случае в сортировке будут участвовать посторонние данные.

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

Выделите диапазон ячеек, который требуется отсортировать. В нашем примере мы выделим диапазон A13:B17.

Откройте вкладку Данные на Ленте, затем нажмите команду Сортировка.

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

Задайте порядок сортировки (по возрастанию или по убыванию). В нашем примере мы выберем По возрастанию.

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

Диапазон будет отсортирован по столбцу Заказы от меньшего в большему

Обратите внимание, что остальное содержимое листа сортировке не подвергается.

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