Сортировка данных по убыванию и возрастанию. Сортировка данных в Excel по строкам и столбцам с помощью формул Сортировка в excel
Рассмотрев возможности простой сортировки в предыдущей статье, решил написать . Например, как сортировать в Excel по столбцам? Сортировка столбцов таблицы в несколько условий и даже по заданным параметрам — не такая уж сложная задача. Подробно об этом можно прочитать в нашей статье.
Начнем с самого интересного, на мой взгляд. Как сортировать в Excel по столбцам, т.е. чтобы значения выстроились в нужном порядке по столбцам т.е. заголовок в первом столбце (а по умолчанию Excel всегда сортирует по строкам). Все как всегда просто, если знаешь как.
Выделяем таблицу. Главная — блок Редактирование — Сортировка — Настраиваемая сортировка — Параметры
В открывшемся окне (картинка в заголовке) выбираем столбцы диапазона. Все, теперь сортировка будет проходить по строкам.
Так же возможно задать дополнительный параметр — учитывать регистр (т.е. учитывать большие и малые буквы). Что очень удобно, если нужно найти, например неверно написанные имена или фамилии.
Настраиваемый список или заданные параметры сортировки
Часто необходимо сортировать данные не по алфавиту или от большего к меньшему, а, например, по дням недели (или по заданным вами параметрам — по филиалам, например). Для этого в столбце Порядок (рисунок 2), выбираем настраиваемый список.
Открывается новое окно.
Как видите, здесь уже заданы дни недели, месяца и прочее. А вам нужно добавить филиалы, например, МСК, СПБ, РНД, НСБ и так далее. Заносим все что нужно в окно Элементы списка: и жмем кнопку Добавить. Разделять можно как пробелом, так и запятой. А такой пользовательский список будет доступен и в других файлах Excel. Т.е. вы можете единоразово задать свою значимость объектов для сортировки.
Сортировка. Горячие клавиши
К сожалению простого сочетания клавиш не предусмотрено. НО! Всегда есть выход для Excel 2007 и выше — управление лентой команд. нажмите Alt. Появятся буквы, обозначающие команды.
Предположим, что мы подготовили отчет по продажам и финансовым результатам по каналам продаж и менеджерам следующего вида:
Глаза разбегаются, правда? Для того, чтобы было легче читать эту таблицу, определимся с тем, какая информация для нас существенна, а какая нет.
Сразу отметим 2 важных момента.
1. Как правило, таблицы такого рода создаются с помощью формул. До проведения сортировки нужно обязательно убрать все формулы из всех ячеек , иначе провести операцию просто не удастся, а если и удастся, то все значения изменятся, т.к. формулы будут ссылаться на совершенно другие ячейки, чем должны.
2. Если в дальнейшем нам понадобится убрать сортировку и вернуться к прежнему виду таблицы, следует позаботиться об этом на данном этапе. Пронумеруем строки первоначальной таблицы на случай, если захотим вернуться к ней, и тогда у нас будет возможность отсортировать строки по нумерации.
Как убрать формулы из всех ячеек листа
Для того, чтобы одновременно убрать все формулы нужно:
1. Выделить всю область листа, для чего необходимо нажать левой клавишей мыши на самый верхний левый угол серого поля:
2. Скопировать выделенную область (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Копировать»)
3. Воспользоваться специальной вставкой (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Специальная вставка» -> «значения»)
Ну, а теперь можно удалить или скрыть лишние столбцы, не боясь изменения отчета.
Прежде чем приступить непосредственно к сортировке, нужно проверить, чтобы сортируемая таблица не содержала объединенных ячеек в шапке или другом месте . В таком случае Excel будет ругаться и писать: «Для этого требуется, чтобы ячейки имели одинаковый размер».
Если Вы не намерены снимать объединение ячеек шапки и менять таблицу, можно перенести сортируемый столбик в левый крайний ряд и отсортировать его с помощью кнопочек сортировки.
Например, мы хотим отсортировать таблицу по убыванию выручки. Выделяем сортируемые строки по серому полю без шапки и нажимаем на «Сортировку по убыванию»:
ВНИМАНИЕ: Это касается только объединенных ячеек в шапке . Если внутри таблицы есть объединенные ячейки, Excel не будет сортировать такие строки. Придется снимать объединение.
Если же таблица не содержит объединенных ячеек, можно провести сортировку непосредственно внутри таблицы без переноса сортируемого столбца.
Для этого выделяем всю область таблицы вместе с шапкой, находим в главном меню «Данные» и выбираем «Сортировку»:
В выпавшем окне Excel предложит сделать сортировку по всем показателям, перечисленным в шапке. Мы выберем «Чистую прибыль»:
Если же мы совместим данный вид сортировки с сортировкой через автофильтр, мы сможем отсортировать данные по отделам, менеджерам и клиентам. Добавим столбец для сортировки и отсортируем данные по менеджерам:
Как видим, в результате у нас получился удобный отчет, который очень наглядно демонстрирует, кто из менеджеров приносит больше всего прибыли, а кто работает с нерентабельными клиентами.
Сортировка данных в Excel весьма полезный инструмент, улучшающий восприятие представленной информации. Прежде, чем перейти к разбору интерфейса, разберем порядок сортировки приложения:
- Первыми идут цифры от меньшего к большему;
- Затем идут специальные символы ($, %, * и т.д.);
- За ними следуют знаки препинания;
- Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).
Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:
Сортировка по возрастанию (от А до Я), по убыванию (от Я до А) и значок сортировки для задания условий по нескольким столбцам одновременно.
Рассмотрим все пункты подробнее.
Для того чтобы в эксель отсортировать диапазон ячеек, предварительно выделите его, затем кликните по необходимому значку. Учтите, что самая верхняя ячейка в выделенном диапазоне будет воспринята как шапка таблицы, т.е. отсортирована не будет и останется на прежнем месте. Также, если рядом с выделенным диапазоном имеются другие заполненные ячейки, то программой будет выведено следующее окно:
Если Вами будет выбран первый вариант, то строки таблицы сохранят свою целостность. Если второй, то данные вне диапазона сортировки останутся нетронутыми. На рисунках приведены результаты до и после сортировки для двух вариантов.
Исходное состояние:
Установка порядка первого столбца таблицы при выборе первого варианта:
Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.
Те же самые действия, но при выборе второго варианта:
Сортировка также произведена по первому столбцу, за исключением того, что второй столбец не сместился относительно отсортированных ячеек.
Выбирайте сразу необходимый диапазон для сортировки, чтобы Excel не предлагал Вам автоматически его расширить. Столбец, с которого Вы начнете выделение диапазона, будет являться столбцом для условий сортировки.
Если возникла необходимость задать порядок по нескольким столбцам, то в этом поможет значок «Сортировка», который уже упоминался. Выделите диапазон, который необходимо упорядочить. В данном случае неважно, с какого столбца он начнет выделяться, т.к. условия и столбцы будут задаваться вручную. Для примера будем использовать следующую таблицу:
Мы специально будем использовать такие данные, чтобы наглядно отобразить, как Excel осуществляет сортировку.
Выделите диапазон нашей таблицы и кликните по значку "Сорировка". Появится следующее окно:
Изначально первый уровень сортировки уже добавлен. В выпадающем списке выберите нужный столбец. В качестве его названия служит верхняя («шапка»).
В следующем выпадающем списке можно выбрать, по каким параметрам сортировать диапазон. Например, если Вы использовали заливку для ячеек или меняли цвет шрифта, то возможно задать порядок по данным параметрам.
Следующий выпадающий список задает порядок: по возрастанию, по убыванию и т.п.
Для добавления второго и последующих столбцов для сортировки воспользуйтесь кнопкой «Добавить уровень» и задайте для него параметры.
Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».
Рассмотрим получившийся результат:
Сначала сортировка прошла по первому столбцу (первый уровень), выделив данные в определенные поддиапазоны (в красной рамке). Далее происходит сортировка второго уровня, но данные сортируются только в пределах определенных первым уровнем поддиапазонов. Отсортировав второй уровень, вновь определяются поддиапазоны (в синей рамке). Затем сортировка происходит по третьему уровню по такому же алгоритму.
Таким образом, можно задавать сортировку в Excel по значительному количеству столбцов.
Также в окне сортировки можно задавать различные параметры.
Вы можете отсортировать список в алфавитном, числовом или хронологическом порядке не более чем по трем полям. Для каждого столбца можно задать возрастающий и убывающий порядок сортировки.
Возрастающий порядок сортировки:
Числа будут отсортированы в порядке от наименьшего отрицательного до наибольшего положительного числа.
Значения даты и времени будут отсортированы в порядке от наиболее раннего до наиболее позднего значения.
Текст будет отсортирован по алфавиту. При этом сначала будут расположены заданные в качестве текста числовые значения.
При сортировке в возрастающем порядке логических значений сначала будет отображено значение ЛОЖЬ, а затем – значение ИСТИНА.
Значения ошибки будут отсортированы в том порядке, в котором они были обнаружены (с точки зрения сортировки все они равны).
Пустые ячейки будут отображены в конце отсортированного списка.
Убывающий порядок сортировки:
В случае убывающего порядка сортировки Excel сортирует записи в обратном описанному выше порядке. Только пустые ячейки по-прежнему будут отображены в конце списка.
Пользовательский порядок сортировки:
Вместо возрастающего и убывающего порядка сортировки Вы можете применять также пользовательский порядок сортировки, определяемый заданным пользовательским списком.
Сортировка списка
Для сортировки списка поместите указатель ячейки внутри списка и выполните команду Данные – Сортировка.
Excel автоматически выделит список и выведет на экран диалоговое окно “Сортировка диапазона” в котором нужно указать параметры сортировки.
Вы можете задать сортировку не более чем по трем полям списка и указать для каждого поля свой порядок сортировки.
Excel автоматически распознает имена полей, если формат ячеек, содержащих имена, отличается от формата ячеек с данными.
Диалоговое окно “Сортировка диапазона”.
Если выполненное программой выделение диапазона не совсем корректно, установите переключатель внизу диалогового окна в нужное положение (Идентифицировать поля по “подписям (первая строка диапазона)” или же “обозначениям столбцов листа”).
Заданные в диалоговом окне “Сортировка” диапазона и “Параметры сортировки” параметры будут сохранены и отображены в диалоговом окне при следующем его открытии.
Вы можете также задать сортировку столбцов диапазона вместо строк. Для этого нажмите в диалоговом окне “Сортировка диапазона” кнопку “Параметры” и в диалоговом окне “Параметры сортировки” в группе “Сортировать” установите переключатель в положение “Столбцы диапазона”.
26. Фильтрация данных в Excel.
Фильтрация данных в списке позволяет отобразить только те записи, которые соответствуют заданным условиям.
Автофильтр
При применении автофильтра имена полей будут дополнены раскрывающимися списками, из которых можно выбрать нужное значение поля или задать пользовательский критерий.
Вставка автофильтра
1. Поместите указатель ячейки внутри списка.
2. В подменю Данные – Фильтр выберите команду “Автофильтр”. Рядом с именами полей будут отображены кнопки со стрелками, нажав которые, можно открыть список.
3. Откройте список для поля, значение которого хотите использовать в качестве фильтра (критерия отбора). В списке будут приведены значения ячеек выбранного поля.
4. Выберите из списка нужный элемент. На экране будут отображены только те записи, которые соответствуют заданному фильтру.
5. Выберите при необходимости из списка другого поля нужный элемент. На экране будут отображены только те записи, которые соответствуют всем заданным условиям фильтрации (условия отдельных полей объединяются с помощью логической операции “И”).
Кнопки открытия автофильтров полей, для которых заданы условия фильтрации, окрашены синим цветом.
Если перед выполнением команды “Автофильтр” Вы выделили один или несколько столбцов, то раскрывающиеся списки будут добавлены только соответствующим полям.
Чтобы снова отобразить на экране все записи списка, выполните команду “Отобразить все” из подменю Данные – Фильтр.
Критерий фильтрации для отдельного поля можно убрать, выбрав в списке автофильтра этого поля элемент “Все”.
Чтобы деактивировать функцию автофильтра (удалить раскрывающиеся списки), выберите повторно команду Данные – Фильтр – Автофильтр.
Применение пользовательского автофильтра
Применяя пользовательский автофильтр, Вы можете соединить с помощью логических операторов отдельные условия отбора записей.
Вставьте в список автофильтр, выбрав команду Данные – Фильтр – Автофильтр.
Откройте список автофильтра для нужного поля и выберите в нем элемент (Условие).
В открывшемся диалоговом окне “Пользовательский автофильтр” (Рис. 6.3.27.) укажите первый критерий.
Выберите логический оператор, объединяющий первый и второй критерии.
Диалоговое окно “Пользовательский автофильтр”.
Вы можете задать для отдельного поля в пользовательском автофильтре один или два критерия. В последнем случае их можно объединить логическим оператором “И” либо “ИЛИ”.
Задайте второй критерий.
Нажмите кнопку “OK”. Excel отфильтрует записи в соответствии с указанными критериями.
Расширенный фильтр
Для задания сложных условий фильтрации данных списка Excel предоставляет в помощь пользователю так называемый расширенный фильтр.
Диапазон критериев
Критерии можно задать в любом свободном месте рабочего листа. В диапазоне критериев Вы можете вводить и сочетать два типа критериев:
Простые критерии: программа сравнит содержимое полей с заданным критерием (аналогично применению автофильтра).
Вычисленные критерии: в этом случае можно указывать также возвращаемые формулой значения, которые не представлены в списке.
При указании простых критериев сначала в диапазоне критериев следует указать имена полей, причем можно ввести только те имена полей, для которых задаются критерии.
В строке (строках), расположенной непосредственно под строкой с именами полей, следует задать критерии. При задании критериев Excel не различает регистр. Вы можете использовать при задании критериев символы подстановки? и *.
Все критерии, заданные в одной строке, должны выполняться одновременно (соответствует логическому оператору “И”). Чтобы задать соединение критериев оператором “ИЛИ”, укажите критерии в различных строках.
Применение расширенного фильтра
После создания диапазона критериев можно запустить расширенный фильтр и подвергнуть данные списка фильтрации.
1. Поместите указатель ячейки внутри списка. В этом случае Excel автоматически распознает диапазон списка и представит ссылку на диапазон в диалоговом окне.
2. Выполните команду Данные – Фильтр – Расширенный фильтр. Поместите курсор ввода в поле “Диапазон условий” и выделите соответствующий диапазон в рабочем листе.
3. Закройте диалоговое окно нажатием кнопки “ОК”. На экране теперь будут отображены записи, удовлетворяющие заданным критериям.
Вы можете применить в рабочем листе только один расширенный фильтр.
Если в результате применения расширенного фильтра не должны быть отображены повторяющиеся записи, в диалоговом окне “Расширенный фильтр” установите флажок параметра “Только уникальные записи”.
Чтобы после фильтрации задать отображение в рабочем листе всех записей списка, выполните команду Данные – Фильтр – Отобразить все.
Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.
Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.
Порядок сортировки в Excel
Существует два способа открыть меню сортировки:
Часто используемые методы сортировки представлены одной кнопкой на панели задач:
Сортировка таблицы по отдельному столбцу:
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.
Сортировка по цвету ячейки и по шрифту
Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.
Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:
Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».
В открывшемся окне вводим необходимые параметры:
Здесь можно выбрать порядок представления разных по цвету ячеек.
По такому же принципу сортируются данные по шрифту.
Сортировка в Excel по нескольким столбцам
Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.
Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.
Сортировка строк в Excel
По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:
Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.
Случайная сортировка в Excel
Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.
Например, нужно расположить в случайном порядке набор неких чисел.
Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.
Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.
Динамическая сортировка таблицы в MS Excel
Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.
Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.
Для динамической сортировки текстовых значений понадобятся формулы массива.
Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.
