Сортировка данных в Excel весьма полезный инструмент, улучшающий восприятие представленной информации. Прежде, чем перейти к разбору интерфейса, разберем порядок сортировки приложения:
- Первыми идут цифры от меньшего к большему;
- Затем идут специальные символы ($, %, * и т.д.);
- За ними следуют знаки препинания;
- Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).
Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:
Сортировка по возрастанию (от А до Я), по убыванию (от Я до А) и значок сортировки для задания условий по нескольким столбцам одновременно.
Рассмотрим все пункты подробнее.
Для того чтобы в эксель отсортировать диапазон ячеек, предварительно выделите его, затем кликните по необходимому значку. Учтите, что самая верхняя ячейка в выделенном диапазоне будет воспринята как шапка таблицы, т.е. отсортирована не будет и останется на прежнем месте. Также, если рядом с выделенным диапазоном имеются другие заполненные ячейки, то программой будет выведено следующее окно:
Если Вами будет выбран первый вариант, то строки таблицы сохранят свою целостность. Если второй, то данные вне диапазона сортировки останутся нетронутыми. На рисунках приведены результаты до и после сортировки для двух вариантов.
Исходное состояние:
Установка порядка первого столбца таблицы при выборе первого варианта:
Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.
Те же самые действия, но при выборе второго варианта:
Сортировка также произведена по первому столбцу, за исключением того, что второй столбец не сместился относительно отсортированных ячеек.
Выбирайте сразу необходимый диапазон для сортировки, чтобы Excel не предлагал Вам автоматически его расширить. Столбец, с которого Вы начнете выделение диапазона, будет являться столбцом для условий сортировки.
Если возникла необходимость задать порядок по нескольким столбцам, то в этом поможет значок «Сортировка», который уже упоминался. Выделите диапазон, который необходимо упорядочить. В данном случае неважно, с какого столбца он начнет выделяться, т.к. условия и столбцы будут задаваться вручную. Для примера будем использовать следующую таблицу:
Мы специально будем использовать такие данные, чтобы наглядно отобразить, как Excel осуществляет сортировку.
Выделите диапазон нашей таблицы и кликните по значку "Сорировка". Появится следующее окно:
Изначально первый уровень сортировки уже добавлен. В выпадающем списке выберите нужный столбец. В качестве его названия служит верхняя («шапка»).
В следующем выпадающем списке можно выбрать, по каким параметрам сортировать диапазон. Например, если Вы использовали заливку для ячеек или меняли цвет шрифта, то возможно задать порядок по данным параметрам.
Следующий выпадающий список задает порядок: по возрастанию, по убыванию и т.п.
Для добавления второго и последующих столбцов для сортировки воспользуйтесь кнопкой «Добавить уровень» и задайте для него параметры.
Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».
Рассмотрим получившийся результат:
Сначала сортировка прошла по первому столбцу (первый уровень), выделив данные в определенные поддиапазоны (в красной рамке). Далее происходит сортировка второго уровня, но данные сортируются только в пределах определенных первым уровнем поддиапазонов. Отсортировав второй уровень, вновь определяются поддиапазоны (в синей рамке). Затем сортировка происходит по третьему уровню по такому же алгоритму.
Таким образом, можно задавать сортировку в Excel по значительному количеству столбцов.
Также в окне сортировки можно задавать различные параметры:
- Поставив флажок на поле «Мои данные содержать заголовки», программа учтет, что верхние ячейки диапазонов упорядочивать не нужно;
- Нажав кнопку «Параметры» Вы получите окно, в котором можно задать:
- Возможность учета регистра (заглавные символы имеют больший порядковый номер по сравнению с прописными);
- Выбрать сортировку по строкам или по столбцам.
В этой статье я покажу Вам, как в 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), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК .
Вот так выглядит наш список домашних дел, упорядоченных по важности:
Научимся ранжировать числовые данные в Excel
с помощью стандартной сортировки, а также функции РАНГ и ее частных случаях (РАНГ.РВ и РАНГ.СР), которые помогут в автоматизации сортировки.
Задача ранжирования числовых данных постоянно возникает в работе с целью поиска наибольших или наименьших значений в списке.
В Excel с этой задачей можно справиться 2 способами: стандартным инструментом сортировки
и с помощью функций
.
Для примера возьмем простую таблицу со списком числовых значений, в которой в дальнейшем и будем ранжировать данные:
Сортировка данных
Начнем с самого простого и доступного варианта — сортировки.
Мы уже частично разбирали как можно структурировать данные с помощью .
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная
-> Редактирование
-> Сортировка и фильтр
, а далее указать по какому критерию нужно произвести сортировку.
В данном случае выберем Сортировка по убыванию , где значения будут расположены от большего к меньшему:
Минусом данного способа является изменение структуры исходных данных, так как в процессе сортирования данных строки и столбцы могут меняться местами, что в некоторых случаях неудобно или невозможно сделать.
Также к важным недостаткам этого варианта можно отнести отсутствие возможности автоматизировать сортировку. Поэтому каждый раз при изменении данных сортировку придется делать еще раз.
В качестве решения данной проблемы рассмотрим другой способ ранжирования, который впрочем можно рассматривать и отдельно от решения этой задачи.
Ранжирование данных
При отсутствии возможности изменения структуры документа мы можем создать дополнительный ряд данных, где будут содержаться порядковые номера исходных данных.
Получить эти порядковые номера нам поможет функция РАНГ
(а также РАНГ.РВ
и РАНГ.СР
).
Функция РАНГ в Excel
Синтаксис и описание функции:
- Число (обязательный аргумент) — число для которого вычисляется ранг;
- Ссылка (обязательный аргумент) — массив или ссылка на массив чисел;
- Порядок
(необязательный аргумент)
— способ упорядочения.
Если аргумент равен 0 или не указан, то значение 1 присваивается максимальному элементу в списке (условно говоря, сортируем по убыванию), в ином случае значение 1 присваивается минимальному элементу (сортируем по возрастанию).
Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР , а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.
Функции РАНГ.РВ и РАНГ.СР в Excel
Синтаксис и описание функций:
РАНГ.РВ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.
Аргументы у всех трех функций одинаковые, т.е. кардинально они почти не отличаются, есть небольшие различие в деталях.
На примере исходной таблицы посмотрим как работает с данными каждая из функций:
Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.
В случае с РАНГ.РВ
равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки
и Мультиварки
соответствует одинаковое значение элемента — 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг — 3.
Для РАНГ.СР
для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами — 3,5.
На этом различия между ними заканчиваются, поэтому в зависимости от ваших задач можно использовать ту или иную функцию.
Если нужно отсортировать значения по возрастанию, то в качестве аргумента Порядок
нужно указать значение 1:
Автоматическая сортировка
Немного усложним задачу и представим, что нам в дальнейшем нужно составить отсортированную таблицу, которая бы автоматически обновлялась при изменении данных в исходной таблице.
Например, это можно сделать с помощью функции ВПР, или комбинации и , однако в случае наличия одинаковых значений в списке мы не сможем корректно подтянуть данные и получим ошибку:
В этом случае можно воспользоваться простым приемом в виде небольшой хитрости.
Добавим к каждому значению исходной таблицы не совпадающие случайные числа близкие к нулю, к примеру, я для этих целей использую функции или , поделенные на заведомо большую величину.
Этот шаг позволит нам получить различные числа в исходных данных, избежать совпадения рангов и ошибки при подтягивании данных:
Теперь для всех элементов таблицы (даже изначально совпадающих) определен свой индивидуальный ранг отличный от остальных, поэтому ошибок при автоматическом ранжировании данных удастся избежать.
Спасибо за внимание!
Если у вас остались вопросы - пишите в комментариях.
Работа с таблицами во многих случаях требует анализа содержащихся в них данных. Excel предоставляет мощные средства для обработки информации, возможность построения статистических отчетов и выборки данных по условию. Но иногда достаточно просто произвести сортировку элементов, чтобы решить поставленную задачу.
Упорядочивание чисел
Сортировка чисел по возрастанию или убыванию - распространенная задача. Она помогает выстроить числовые параметры - количество товаров и клиентов, суммы продаж и доходов, и представить информацию в более наглядном виде. Сделаем в Excel сортировку этих данных.
Например, в таблице файла туристической компании хранится информация о стоимости проживания в отеле. Цена зависит от типа номера и предлагаемого питания. Турист заинтересован в поиске наиболее экономного размещения, поэтому нужно отсортировать данные по возрастанию цены.
Для этого выделяем столбец таблицы «Стоимость, руб.» и заходим на вкладку «Главная». В блоке «Редактирование» находится группа инструментов «Сортировка и фильтр». Выбираем из меню пункт «Сортировка по возрастанию».
Программа, обнаружив наличие связанных столбцов, уточняет, нужно ли выполнять сортировку в Excel только по выделенному полю, или же расширить диапазон.
Выбираем «Расширить», так как мы заинтересованы в упорядочивании полных записей таблицы, и нажимаем «Сортировка». Записи выстроены в порядке возрастания стоимости проживания.
Эта операция выполняется еще проще, если таблица отформатирована по стилю. В таком случае нужно вызвать контекстное меню столбца, щелкнув по выпадающему списку в заголовке графы. В меню находятся аналогичные команды, с помощью которых можно выполнить сортировку в Excel.
Для того чтобы числа были отсортированы верно, обратите внимание на корректность формата данных в столбце. Если значения внесены в таблицу как текст, а не число, выстраивание пройдет не в ожидаемом порядке. В таблице приведен пример сортировки чисел 10, 11, 100, 15, 110, 132 в зависимости от формата данных.
Упорядочивание текста
В Excel сортировка столбцов, содержащих текст, выполняется так же, как и операция с числами. Произведем эту команду в колонке уже известной нам таблицы цен на комнаты в отеле «Питание». Выделяем столбец кликом по его заголовку и открываем панель инструментов «Редактирование» вкладки «Главная». Теперь в выпадающем меню вместо команд сортировки по возрастанию и убыванию расположены команды сортировки от А до Я и наоборот.
Выполняем построение ячеек от А до Я, опять же указывая расширение диапазона операции.
При упорядочивании строковых данных убедитесь в том, что значения не содержат начальных пробелов, так как в таком случае желаемый порядок ячеек будет нарушен. Эта проблема случается, если информация выгружается в файл из другого источника: базы данных, стороннего приложения или другого файла.
Для форматированных таблиц сортировку в Excel выполнить еще проще. Зайдя в контекстное меню заголовка колонки, вы также увидите команды построения ячеек в порядке от А до Я и наоборот.
Упорядочивание дат
Все указанные выше операции, применимые для чисел и строковой информации, используются и для выстраивания дат и времени. Отличие состоит только в наименовании команды, для такого типа данных она называется «Сортировка от старых к новым» и наоборот. Ниже показан пример.
Для дат действует то же правило, что и для чисел: чтобы упорядочивание прошло корректно, убедитесь в том, что сортируемая информация представлена именно в формате даты, а не в текстовом.
Настраиваемые списки
Но не всегда есть возможность выстроить необходимый список штатными средствами, бывает необходимость выполнить сортировку в Excel по полям, к которым не подходит ни один из описанных выше методов. К примеру, в списке дел на неделю мы хотим выстроить задачи по дням недели.
Чтобы выполнить такую сортировку, программа предоставляет возможность использования настраиваемых списков. В MS Excel есть ряд предустановленных списков, одним из которых и является перечень дней недели. Чтобы их посмотреть, зайдите на вкладку «Данные» и найдите панель инструментов сортировки и фильтрации Excel. По кнопке «Сортировка» открывается окно выполнения операции по нескольким столбцам.
В поле «Порядок» выбираем элемент «Настраиваемый список». Из перечня выбираем дни недели и нажимаем ОК.
Упорядочивание по цвету и значкам
Еще одной удобной опцией выстраивания элементов ячейки по порядку является возможность их сортировки по цвету. При этом может использоваться цвет как заливки, так и шрифта текста. В таблице дел обозначим рабочие задачи оранжевым цветом, семейные дела - голубым, праздники - бежево-желтым. И выстроим их по типу.
Для этого заходим в настраиваемую сортировку. Кроме описанного выше способа, можно воспользоваться меню панели «Редактирование» вкладки «Главная». Выбираем из списка команд «Сортировка и фильтр» элемент «Настраиваемая сортировка».
Выбираем поле «Задача», тип «Цвет ячейки» и устанавливаем оранжевый цвет сверху. Чтобы домашние дела находились внизу списка, добавляем еще один уровень и для тех же параметров устанавливаем голубой цвет снизу.
Этот способ позволяет упорядочивать элементы таблицы и по значкам. Например, мы можем пометить отдельные задачи восклицательным знаком как важные и выводить их в начале списка.
Выше мы описали, как в Excel сделать сортировку данных разных типов, используя цвет ячеек и шрифта текста и пометки в строках. Надеемся, эта информация поможет вам в работе с программой.
Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.
Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.
Сортировка по одному критерию
Пошаговые действия:
- В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
- На вкладке Данные найти группу команд .
Отметим, что буквы на этой кнопке указывают только на направление сортировки, а вид кнопки остается один и тот же и при текстовых, и при числовых данных.
Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка , а далее - требуемый вариант сортировки.
Пошаговые действия:
- Выделить одну ячейку из сортируемого массива данных.
Если диапазоне данных имеются пустые столбцы или строкой, то Excel автоматически воспринимает их как границы сортируемого массива данных. В таком случае следует выделить все данные, подлежащие сортировке.
- На вкладке Данные найти группу команд и на ней выбрать команду Сортировка .
- Последовательно задать уровни сортировки (определяемые именем столбца).
Нажимая на стрелку возле трех полей (Столбец , Сортировка , Порядок ) необходимо выбрать:
- Имя столбца для сортировки.
- Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
- Порядок сортировки (по убыванию или по возрастанию).
Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.
Сортировка по форматированию
Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.
Пошаговый порядок действий:
- Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
- На вкладке Данные выбрать группу , а затем выбрать команду Сортировка .
- В поле Столбец укажите столбец по которому будет проводиться сортировка.
- В поле Сортировка из всплывающего меню выбрать критерий сортировки: цвет ячейки , цвет шрифта или значок ячейки .
- Поле Порядок содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором - размещение ячеек, отсортированных по данному критерию (строку Сверху или Снизу ).
- При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень .
Можно также воспользоваться командой «Копировать уровень» , заменив в поле «Порядок» прежнее значение на новое.
- После выбора сортировки нажать кнопку ОК .
