Сортировка данных в Excel это очень полезная функция, но пользоваться ней следует с осторожностью. Если большая таблица содержит сложные формулы и функции, то операцию сортировки лучше выполнять на копии этой таблицы.
Во-первых, в формулах и функциях может нарушиться адресность в ссылках и тогда результаты их вычислений будут ошибочны. Во-вторых, после многократных сортировок можно перетасовать данные таблицы так, что уже сложно будет вернуться к изначальному ее виду. В третьих, если таблица содержит объединенные ячейки, то следует их аккуратно разъединить, так как для сортировки такой формат является не приемлемым.
Какими средствами располагает Excel для сортировки данных? Чтобы дать полный ответ на этот вопрос рассмотрим его на конкретных примерах.
Подготовка таблицы для правильной и безопасной сортировки данных:
Теперь наша таблица не содержит формул, а только результаты их вычисления. Так же разъединены объединенные ячейки. Осталось убрать лишний текст в заголовках и таблица готова для безопасной сортировки.
Чтобы отсортировать всю таблицу относительно одного столбца выполните следующее:
Данные отсортированные по всей таблице относительно столбца «Чистая прибыль».
Теперь отсортируем только один столбец без привязки к другим столбцам и целой таблицы:
Столбец отсортирован независимо от других столбцов таблицы.
При копировании таблицы на отдельный лист мы переносим только ее значения с помощью специальной вставки. Но возможности сортировки позволяют нам сортировать не только по значениям, а даже по цветам шрифта или цветам ячеек. Поэтому нам нужно еще переносить и форматы данных. Для этого:
Теперь копия таблицы содержит значения и форматы. Выполним сортировку по цветам:
Сверху у нас теперь наихудшие показатели по чистой прибыли, которые имеют наихудшие показатели.
Примечание. Дальше можно выделить в этой таблице диапазон A4:F12 и повторно выполнить второй пункт этого раздела, только указать розовый сверху. Таким образом в первую очередь пойдут ячейки с цветом, а после обычные.
Сортировка данных в Excel – это очень полезный инструмент, который позволяет улучшать восприятие информации, особенно при больших объемах. В данном уроке мы научимся применять сортировку, узнаем основные команды, а также познакомимся с типами сортировки в Excel.
При добавлении данных в Excel очень важно грамотно организовать информацию на рабочем листе. Одним из инструментов, который позволяет сделать это, является сортировка. С помощью сортировки Вы можете сформировать список контактной информации по фамилии, расположить содержимое таблицы в алфавитном порядке или же в порядке убывания.
При сортировке данных в Excel в первую очередь необходимо решить, как применять сортировку: ко всему листу (таблице) или только к определенному диапазону ячеек.
В следующем примере мы отсортируем форму заказа футболок по Фамилиям (Столбец С) и расположим их в алфавитном порядке.
При сортировке таблицы или списка в Excel необходимо, чтобы они были отделены от посторонних данных на листе как минимум одной строкой или столбцом. В противном случае в сортировке будут участвовать посторонние данные.
В следующем примере мы выберем отдельную небольшую таблицу на листе Excel, чтобы отсортировать количество футболок, заказанных в определенные дни.
Если сортировка в Excel выполняется неправильно, то в первую очередь проверьте верно ли введены значения. Даже небольшая опечатка может привести к проблемам при сортировке больших таблиц. В следующем примере мы забыли поставить дефис в ячейке A18, что привело к неточной сортировке.
Сортировка в Эксель – это встроенная функция, с помощью которой пользователь сможет расположить данные в столбцах на листе в удобном порядке для их последующего анализа.
Вы сможете отсортировать информацию в алфавитном порядке, по возрастанию или убыванию значений, по дате или по значкам, по цвету текста или ячейки. Именно об этом и пойдет речь в данной статье.
Здесь все достаточно просто. Для примера возьмем следующую таблицу. Сделаем в ней сортировку данных по столбцу С . Для этого выделяем его и на вкладке «Главная» кликаем на кнопочку «Сортировка и фильтр» . В следующем меню выберите или «… от минимального к максимальному» , или «… от максимального к минимальному» . Выберем второй вариант.
Теперь у нас данные в С размещены в порядке убывания.
У меня столбец С расположен между двумя другими, которые заполнены данными. В этом случае, Excel считает, что выделенный столбец – это часть таблицы (и считает правильно). В результате появилось следующее сообщение. Поскольку мне нужно сделать сортировку конкретно для Класса, выделяю маркером пункт «… в пределах указанного выделения» и нажимаю «Сортировка» .
Она делается по тому же принципу, как было описано выше. Выделяем нужный диапазон, и нажимаем кнопочку «Сортировка и фильтр» . В выпадающем меню пункты изменились. Выберите или от «А до Я» , или от «Я до А» .
Список имен в примере отсортирован по алфавиту.
Чтобы отсортировать даты в Эксель, сначала обратите внимание, какой формат установлен для тех ячеек, в которых они записаны. Выделите их и на вкладке «Главная» посмотрите на группу «Число» . Лучше всего подойдет или формат «Дата» , краткий или длинный, или «(все форматы)» – дата может быть записана различными способами: ДД.ММ.ГГГГ, ДД.МММ, МММ.ГГ.
Этот момент очень важен, так как в противном случае, даты могут быть отсортированы просто по возрастанию первых двух чисел, или по месяцам в алфавитном порядке.
После этого выделяем нужный диапазон ячеек и жмем на кнопочку «Сортировка и фильтр» . В меню можно выбрать или «от старых к новым» , или «от новых к старым» .
Этот способ можно использовать, когда в таблице Excel текст в ячейках или сами ячейки закрашены в различный цвет. Для примера возьмем столбец из чисел, закрашенных разными цветами. Отсортируем его, чтобы сначала шли числа, закрашенные в красный, затем зеленый и черный цвет.
Выделяем весь диапазон, кликаем на кнопочку «Сортировка и фильтр» и выбираем из меню «Настраиваемая…» .
В следующем окне, уберите галочку с поля , если Вы выделили их без верхней строки, которая является шапкой таблицы. Затем выбираем столбец, по которому будем сортировать, в примере это «I» . В разделе «Сортировка» из выпадающего списка выбираем «Цвет шрифта» . В разделе порядок выбираем «красный цвет» – «Сверху» . Это мы отсортировали числа красного цвета.
Теперь нужно, чтобы в столбце шли числа зеленого цвета. Нажмите на кнопочку «Добавить уровень» . Все настройки те же, только выберите «зеленый цвет» . Нажмите «ОК» .
Наш столбец отсортирован следующим образом.
Как видите, числа идут не по порядку. Давайте отсортируем числа в порядке возрастания. Выделяем столбец, нажимаем «Сортировка и фильтр» – «Настраиваемая …» . В открывшемся окне нажмите на кнопку «Добавить уровень» . Столбец остается «I» , в следующем поле выбираем по «Значению» , порядок «По возрастанию» . Нажмите «ОК» .
Теперь наш столбец отсортирован и по цвету текста и в порядке возрастания данных.
Аналогичным образом сортируются данные и по цвету ячейки, только в разделе «Сортировка» выбирайте из списка «Цвет ячейки» .
Если у Вас есть таблица, в которой нужно выполнить сортировку сразу по нескольким столбцам, делаем следующее. Выделяем весь диапазон ячеек таблицы вместе с шапкой. Кликаем по кнопочке «Сортировка и фильтр» и выбираем «Настраиваемая …» .
Давайте отсортируем класс в порядке возрастания, и таким же образом средний бал.
В окне сортировки ставим галочку в поле «Мои данные содержат заголовки» . В разделе «Столбец» выбираем из списка «Класс» , сортировка по «Значению» , а порядок «По возрастанию» .
Чтобы сделать все тоже самое по среднему балу, нажмите на кнопочку «Добавить уровень» . В разделе «Столбец» выбираем «Средн.бал» . Нажмите «ОК» .
Данные в таблице отсортированы.
Теперь в столбце «Имя» закрасим ячейки с мальчиками в синий цвет, ячейки с девочками в розовый. Чтобы не делать это для каждой ячейки в отдельности, прочтите статью, как выделить ячейки в Excel – в ней написано, как выделить несмежные ячейки.
Выполним сортировку этого столбца по цвету ячейки: сначала будут девочки, потом мальчики. Снова выделяем всю таблицу, жмем «Сортировка» – «Настраиваемая …» .
В открывшемся окне уже есть два уровня, которые мы сделали раньше. Эти уровни имеют приоритет – у первого самый большой, у второго меньше и так далее. То есть, если мы хотим, чтобы сначала выполнилась сортировка данных в таблице девочки/мальчики, затем по классу, а затем по среднему балу – нужно в таком порядке и расставить уровни.
Нажимаем на кнопку «Добавить уровень» . В разделе «Столбец» выбираем «Имя» , сортировка – «Цвет ячейки» , порядок – «розовый» , «Сверху» .
Теперь с помощью стрелочек перемещаем данную строку наверх списка. Нажмите «ОК» .
Таблица с отсортированными данными выглядит следующим образом.
Сортировка в Excel — это встроенная функция анализа данных. С помощью нее можно выставить фамилии в алфавитном порядке, отсортировать средний балл абитуриентов по возрастанию или убыванию, задать порядок строк в зависимости от цвета или значка и т.д. Также с помощью этой функции можно быстро придать таблице удобный вид, что позволит пользователю быстрее находить необходимую информацию, анализировать ее и принимать решения.
Excel умеет сортировать данные по тексту (в алфавитном порядке или наоборот), числам (по возрастанию или по убыванию), по дате и времени (от новых к старым, и наоборот). Сортировать можно как по одному столбцу, так и одновременно по нескольким. Например, сначала можно отсортировать всех клиентов в алфавитном порядке, а затем — по общей сумме их покупок. Кроме того, Эксель может сортировать по настраиваемым спискам или по формату (цвет ячейки, цвет текста и т.д.). Обычно сортировка применяется только по колонкам, но есть возможность применять эту функцию и к строкам.
Все указанные настройки этой опции сохраняются вместе с книгой Excel, что позволяет повторно сортировать информацию при открытии книги (если необходимо).
Сортировку в Экселе условно можно разделить на простую и сложную. Простой считается сортировка по возрастанию или по убыванию.
Существует 2 основных типа сортировки — по возрастанию и по убыванию
Итак, перед тем как начать, необходимо открыть Excel и заполнить какой-то информацией. К примеру, можно заполнить 10 ячеек цифрами от 1 до 10. Теперь нужно выделить весь столбец (в данном случае — все 10 ячеек) и выбрать в панели меню пункты «Данные — Сортировка». Откроется новое окно, в котором надо указать, как отсортировать информацию, по возрастанию или по убыванию. Можно, например, выбрать пункт «по убыванию» и нажать кнопку «ОК». Теперь числа будут идти от 10 и до 1. Можно снова открыть окно сортировки и выбрать «по возрастанию» — числа будут идти от 1 до 10. Также эта процедура может выполняться одновременно по 3 колонкам. Хотя такую сортировку лучше выполнять .
Для примера можно создать таблицу, в которой будет храниться информация о товаре на складе. Таблица будет состоять из 3 колонок: наименование, цвет, количество. Товары нужно написать так, чтобы одной и той же категории было несколько. К примеру, ботинки мужские черные (3 модели), ботинки мужские красные (2 модели), туфли женские белые (4 модели) и т.д. Количество может быть любое.
Итак, чтобы включить автофильтр, необходимо выделить весь лист и в панели меню выбрать пункты «Данные — Фильтр — Автофильтр». В ячейках с названиями столбцов (наименование, количество и пр.) должен появиться небольшой значок, при нажатии на который откроется выпадающий список. Наличие такой стрелочки означает, что автофильтр включен правильно. В этом списке можно отсортировать данные по убыванию или по возрастанию, указать, чтобы в таблице отображались только первые 10 наименований (в данном примере эта опция не сработает) или чтобы показывало какой-то определенный товар (например, ботинки мужские). Также можно выбрать пункт «Условие» и указать, например, чтобы программа отобразила все товары, количество которых меньше или равно 10.
Если стрелочка автофильтра окрашена в синий цвет, это означает, что данная колонка уже была отсортирована.
Допустим, что у пользователя есть таблица, в которой присутствует столбец с названиями месяцев года. И когда необходимо отсортировать его, к примеру, по возрастанию, получается примерно так: август, апрель, декабрь и т.д. А хотелось бы, чтобы сортировка происходила в привычной последовательности, т.е. январь, февраль, март и т.д. Сделать это можно с помощью особой настройки по пользовательскому листу.
Для этого необходимо выделить всю таблицу, открыть окно сортировки и в поле «Порядок» указать пункт «Настраиваемый список». Откроется новое окно, где можно выбрать нужную последовательность месяцев в году. Если такого списка нет (например, название месяцев на английском), то его можно создать самому, выбрав опцию «Новый список».
Отсортировать данные совсем не трудно. Зато в результате можно получить удобную таблицу или отчет для быстрого просмотра необходимой информации и принятия решения.
Научимся ранжировать числовые данные в Excel
с помощью стандартной сортировки, а также функции РАНГ и ее частных случаях (РАНГ.РВ и РАНГ.СР), которые помогут в автоматизации сортировки.
Задача ранжирования числовых данных постоянно возникает в работе с целью поиска наибольших или наименьших значений в списке.
В Excel с этой задачей можно справиться 2 способами: стандартным инструментом сортировки
и с помощью функций
.
Для примера возьмем простую таблицу со списком числовых значений, в которой в дальнейшем и будем ранжировать данные:
Начнем с самого простого и доступного варианта — сортировки.
Мы уже частично разбирали как можно структурировать данные с помощью .
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная
-> Редактирование
-> Сортировка и фильтр
, а далее указать по какому критерию нужно произвести сортировку.
В данном случае выберем Сортировка по убыванию , где значения будут расположены от большего к меньшему:
Минусом данного способа является изменение структуры исходных данных, так как в процессе сортирования данных строки и столбцы могут меняться местами, что в некоторых случаях неудобно или невозможно сделать.
Также к важным недостаткам этого варианта можно отнести отсутствие возможности автоматизировать сортировку. Поэтому каждый раз при изменении данных сортировку придется делать еще раз.
В качестве решения данной проблемы рассмотрим другой способ ранжирования, который впрочем можно рассматривать и отдельно от решения этой задачи.
При отсутствии возможности изменения структуры документа мы можем создать дополнительный ряд данных, где будут содержаться порядковые номера исходных данных.
Получить эти порядковые номера нам поможет функция РАНГ
(а также РАНГ.РВ
и РАНГ.СР
).
Синтаксис и описание функции:
Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР , а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.
Синтаксис и описание функций:
РАНГ.РВ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.
Аргументы у всех трех функций одинаковые, т.е. кардинально они почти не отличаются, есть небольшие различие в деталях.
На примере исходной таблицы посмотрим как работает с данными каждая из функций:
Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.
В случае с РАНГ.РВ
равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки
и Мультиварки
соответствует одинаковое значение элемента — 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг — 3.
Для РАНГ.СР
для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами — 3,5.
На этом различия между ними заканчиваются, поэтому в зависимости от ваших задач можно использовать ту или иную функцию.
Если нужно отсортировать значения по возрастанию, то в качестве аргумента Порядок
нужно указать значение 1:
Немного усложним задачу и представим, что нам в дальнейшем нужно составить отсортированную таблицу, которая бы автоматически обновлялась при изменении данных в исходной таблице.
Например, это можно сделать с помощью функции ВПР, или комбинации и , однако в случае наличия одинаковых значений в списке мы не сможем корректно подтянуть данные и получим ошибку:
В этом случае можно воспользоваться простым приемом в виде небольшой хитрости.
Добавим к каждому значению исходной таблицы не совпадающие случайные числа близкие к нулю, к примеру, я для этих целей использую функции или , поделенные на заведомо большую величину.
Этот шаг позволит нам получить различные числа в исходных данных, избежать совпадения рангов и ошибки при подтягивании данных:
Теперь для всех элементов таблицы (даже изначально совпадающих) определен свой индивидуальный ранг отличный от остальных, поэтому ошибок при автоматическом ранжировании данных удастся избежать.
Спасибо за внимание!
Если у вас остались вопросы - пишите в комментариях.
