Сортировка в Эксель – это встроенная функция, с помощью которой пользователь сможет расположить данные в столбцах на листе в удобном порядке для их последующего анализа.
Вы сможете отсортировать информацию в алфавитном порядке, по возрастанию или убыванию значений, по дате или по значкам, по цвету текста или ячейки. Именно об этом и пойдет речь в данной статье.
Здесь все достаточно просто. Для примера возьмем следующую таблицу. Сделаем в ней сортировку данных по столбцу С . Для этого выделяем его и на вкладке «Главная» кликаем на кнопочку «Сортировка и фильтр» . В следующем меню выберите или «… от минимального к максимальному» , или «… от максимального к минимальному» . Выберем второй вариант.
Теперь у нас данные в С размещены в порядке убывания.
У меня столбец С расположен между двумя другими, которые заполнены данными. В этом случае, Excel считает, что выделенный столбец – это часть таблицы (и считает правильно). В результате появилось следующее сообщение. Поскольку мне нужно сделать сортировку конкретно для Класса, выделяю маркером пункт «… в пределах указанного выделения» и нажимаю «Сортировка» .
Она делается по тому же принципу, как было описано выше. Выделяем нужный диапазон, и нажимаем кнопочку «Сортировка и фильтр» . В выпадающем меню пункты изменились. Выберите или от «А до Я» , или от «Я до А» .
Список имен в примере отсортирован по алфавиту.
Чтобы отсортировать даты в Эксель, сначала обратите внимание, какой формат установлен для тех ячеек, в которых они записаны. Выделите их и на вкладке «Главная» посмотрите на группу «Число» . Лучше всего подойдет или формат «Дата» , краткий или длинный, или «(все форматы)» – дата может быть записана различными способами: ДД.ММ.ГГГГ, ДД.МММ, МММ.ГГ.
Этот момент очень важен, так как в противном случае, даты могут быть отсортированы просто по возрастанию первых двух чисел, или по месяцам в алфавитном порядке.
После этого выделяем нужный диапазон ячеек и жмем на кнопочку «Сортировка и фильтр» . В меню можно выбрать или «от старых к новым» , или «от новых к старым» .
Этот способ можно использовать, когда в таблице Excel текст в ячейках или сами ячейки закрашены в различный цвет. Для примера возьмем столбец из чисел, закрашенных разными цветами. Отсортируем его, чтобы сначала шли числа, закрашенные в красный, затем зеленый и черный цвет.
Выделяем весь диапазон, кликаем на кнопочку «Сортировка и фильтр» и выбираем из меню «Настраиваемая…» .
В следующем окне, уберите галочку с поля , если Вы выделили их без верхней строки, которая является шапкой таблицы. Затем выбираем столбец, по которому будем сортировать, в примере это «I» . В разделе «Сортировка» из выпадающего списка выбираем «Цвет шрифта» . В разделе порядок выбираем «красный цвет» – «Сверху» . Это мы отсортировали числа красного цвета.
Теперь нужно, чтобы в столбце шли числа зеленого цвета. Нажмите на кнопочку «Добавить уровень» . Все настройки те же, только выберите «зеленый цвет» . Нажмите «ОК» .
Наш столбец отсортирован следующим образом.
Как видите, числа идут не по порядку. Давайте отсортируем числа в порядке возрастания. Выделяем столбец, нажимаем «Сортировка и фильтр» – «Настраиваемая …» . В открывшемся окне нажмите на кнопку «Добавить уровень» . Столбец остается «I» , в следующем поле выбираем по «Значению» , порядок «По возрастанию» . Нажмите «ОК» .
Теперь наш столбец отсортирован и по цвету текста и в порядке возрастания данных.
Аналогичным образом сортируются данные и по цвету ячейки, только в разделе «Сортировка» выбирайте из списка «Цвет ячейки» .
Если у Вас есть таблица, в которой нужно выполнить сортировку сразу по нескольким столбцам, делаем следующее. Выделяем весь диапазон ячеек таблицы вместе с шапкой. Кликаем по кнопочке «Сортировка и фильтр» и выбираем «Настраиваемая …» .
Давайте отсортируем класс в порядке возрастания, и таким же образом средний бал.
В окне сортировки ставим галочку в поле «Мои данные содержат заголовки» . В разделе «Столбец» выбираем из списка «Класс» , сортировка по «Значению» , а порядок «По возрастанию» .
Чтобы сделать все тоже самое по среднему балу, нажмите на кнопочку «Добавить уровень» . В разделе «Столбец» выбираем «Средн.бал» . Нажмите «ОК» .
Данные в таблице отсортированы.
Теперь в столбце «Имя» закрасим ячейки с мальчиками в синий цвет, ячейки с девочками в розовый. Чтобы не делать это для каждой ячейки в отдельности, прочтите статью, как выделить ячейки в Excel – в ней написано, как выделить несмежные ячейки.
Выполним сортировку этого столбца по цвету ячейки: сначала будут девочки, потом мальчики. Снова выделяем всю таблицу, жмем «Сортировка» – «Настраиваемая …» .
В открывшемся окне уже есть два уровня, которые мы сделали раньше. Эти уровни имеют приоритет – у первого самый большой, у второго меньше и так далее. То есть, если мы хотим, чтобы сначала выполнилась сортировка данных в таблице девочки/мальчики, затем по классу, а затем по среднему балу – нужно в таком порядке и расставить уровни.
Нажимаем на кнопку «Добавить уровень» . В разделе «Столбец» выбираем «Имя» , сортировка – «Цвет ячейки» , порядок – «розовый» , «Сверху» .
Теперь с помощью стрелочек перемещаем данную строку наверх списка. Нажмите «ОК» .
Таблица с отсортированными данными выглядит следующим образом.
Откройте в проводнике папку «Документы» и отсортируйте содержимое по дате изменения. Что у вас сверху – папки или файлы? Правильный ответ зависит от того, как вы открыли папку:)
Постоянные читатели уже догадались, что сегодня не обойдется без – интересной, но так толком и не прижившейся . Казалось бы, в Windows 10 библиотеки ушли на второй план, но на них очень много завязано.
Действительно, в проводнике папки Документы, Изображения и Музыка оккупировали Компьютер и панель быстрого запуска, а библиотеки скрылись из области навигации. Но на них полагается история файлов (как мы выяснили в предыдущей записи) и унаследованное из Windows 7 резервное копирование образов (со старыми граблями).
А главное – библиотеки присутствуют в стандартных диалогах , причем окно открывается именно в библиотеке , в том числе в магазинных приложениях.
В качестве источника фотографий предлагается добавить папку из библиотеки Документы в библиотеку Изображения
При этом Microsoft так и не удалось вживить библиотеки в оболочку, и сегодня я разберу очередную путаницу.
В зависимости от того, открываете вы папку из библиотеки или минуя ее, может варьироваться не только внешний вид , но и сортировка по дате. Проведите простой эксперимент (результат ниже верен при стандартных настройках проводника):
По адресной строке легко определить, открыта папка из библиотеки или напрямую (это я разбирал в викторине еще 5 лет назад).
Разница в сортировке по дате изменения между папками и библиотеками при стандартных настройках проводника
Имеет смысл привести настройки к единому виду, чтобы не путаться. Это полезно даже в случае, если вы не пользуетесь библиотеками намеренно, поскольку вы будете на них натыкаться в диалоговых окнах классических и магазинных приложений.
Вопрос лишь в том, какой способ отображения результатов вам милее!
Уверен, что опытные пользователи часто используют первый вариант – с файлами вверху. Сортируя по дате, мы все-таки чаще ищем файл в известной папке, и нет смысла крутить колесо мыши лишний раз. Но вариант с папками сверху имеет право на жизнь, поэтому я к нему вернусь, когда разберусь с библиотеками.
Можно долго ломать голову, если не знать про одну особенность библиотек. Когда вы открываете папку из библиотеки, наряду с группировкой и сортировкой появляется возможность упорядочивания файлов, о которой я рассказывал в классической серии пенальти «Проводник vs. TC».
Интересно, что в Windows 7 упорядочивание было вынесено на специальную библиотечную панель вверху окна проводника. Но Windows 8 он сильно обновился , и упорядочивание осталось только в контекстном меню.
Щелкните ПКМ на свободном месте в папке. А вот и причина того, что в библиотеках папки всегда находятся сверху – по ним ведется упорядочивание!
Контекстное меню фона папки, открытой из библиотеки
Переключите упорядочивание на Имя и отсортируйте папку по дате изменения. Теперь недавние файлы сверху, что и требовалось доказать!
Сейчас будет интереснее! Допустим, вы скачали в папку Movies седьмой сезон любимого сериала, все эпизоды которого легли во вложенную папку. В корне Movies у вас много отдельных файлов — фильмы, торрент-файлы и т.д. Обычная сортировка по дате тут не помогает, потому что папки оседают внизу — под файлами.
Обычная сортировка папки по дате в папке
Сортировка по имени поднимет папки вверх, но свежая папка затеряется в их списке, и все равно придется искать ее глазами.
Секретное решение заложено в проводник со времен Windows Vista.
Сверху отобразятся папки, отсортированные по дате, и свежая папка будет в самом верху!
Индикатор сортировки в проводнике всегда указывает на основной столбец, а дополнительные критерии никак не обозначены
Проводник запоминает комбинированное состояние сортировки, но его можно сбросить – сначала удерживая Ctrl , щелкните по тому же столбцу, а потом отсортируйте по дате изменения как обычно.
Сортировка по дате – это самая распространенная постановка вопроса, но трюк с Shift работает с любыми столбцами, причем вы можете применить одновременно несколько критериев сортировки!
Чтобы продемонстрировать это, я сгенерировал полтора десятка файлов двух типов с разными размерами и датами создания, а для наглядности сделал GIF с четырьмя состояниями сортировки.
По порядку фреймов:
Работает! :)
Дочитав статью до этого места, некоторые из вас, думаю, уже проверили, работает ли прием с Shift в любимом файловом менеджере. Total Commander так тоже умеет! Причем у него реализация более наглядная.
У Total Commander в столбцах появляются стрелки и порядковый номер сортировки, а основной столбец выделен цветом
Думаю, это должно работать и в других двухпанельных менеджерах – напишите в комментариях!
Есть пара моментов, которые нужно учитывать.
Обычно, этот подзаголовок я использую для рассказа об эволюции той или иной фичи, но сегодня я вас развлеку историей о нелегком пути этой статьи на свет.
Вопрос про библиотеки мне задал в почте читатель Евгений К . Порывшись в блоге, я выяснил, что конкретного решения не публиковал, а на новую отдельную статью оно не тянуло. Я скинул ответ почтой и сделал себе пометку написать о нем в соцсетях . Но мысль о том, что я уже писал на эту тему крепко засела в голове.
Я прошерстил OneNote и нашел упоминание о сортировке с Shift ! Оно оказалось в набросках к опубликованной четыре (!) года назад статье 14 способов использования мыши в связке с клавиатурой для ускорения работы . Очевидно, я тогда решил, что трюк заслуживает отдельного материала в продолжение темы (и даже сделал пометку ToDo:)
Наброски к записи 2012 года в архиве OneNote
Теперь я доставил вам и пятнадцатый способ:) Лучше поздно, чем никогда!
Мы уже столько раз обсуждали файловые менеджеры и проводник в частности, что трудно придумать новую тему для дискуссии. В комментариях:
Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.
Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.
Существует два способа открыть меню сортировки:
Часто используемые методы сортировки представлены одной кнопкой на панели задач:
Сортировка таблицы по отдельному столбцу:
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.
Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.
Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:
Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».
В открывшемся окне вводим необходимые параметры:
Здесь можно выбрать порядок представления разных по цвету ячеек.
По такому же принципу сортируются данные по шрифту.
Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.
Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.
По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:
Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.
Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.
Например, нужно расположить в случайном порядке набор неких чисел.
Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.
Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.
Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.
Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.
Для динамической сортировки текстовых значений понадобятся формулы массива.
Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.
Сортировка данных в Excel это очень полезная функция, но пользоваться ней следует с осторожностью. Если большая таблица содержит сложные формулы и функции, то операцию сортировки лучше выполнять на копии этой таблицы.
Во-первых, в формулах и функциях может нарушиться адресность в ссылках и тогда результаты их вычислений будут ошибочны. Во-вторых, после многократных сортировок можно перетасовать данные таблицы так, что уже сложно будет вернуться к изначальному ее виду. В третьих, если таблица содержит объединенные ячейки, то следует их аккуратно разъединить, так как для сортировки такой формат является не приемлемым.
Какими средствами располагает Excel для сортировки данных? Чтобы дать полный ответ на этот вопрос рассмотрим его на конкретных примерах.
Подготовка таблицы для правильной и безопасной сортировки данных:
Теперь наша таблица не содержит формул, а только результаты их вычисления. Так же разъединены объединенные ячейки. Осталось убрать лишний текст в заголовках и таблица готова для безопасной сортировки.
Чтобы отсортировать всю таблицу относительно одного столбца выполните следующее:
Данные отсортированные по всей таблице относительно столбца «Чистая прибыль».
Теперь отсортируем только один столбец без привязки к другим столбцам и целой таблицы:
Столбец отсортирован независимо от других столбцов таблицы.
При копировании таблицы на отдельный лист мы переносим только ее значения с помощью специальной вставки. Но возможности сортировки позволяют нам сортировать не только по значениям, а даже по цветам шрифта или цветам ячеек. Поэтому нам нужно еще переносить и форматы данных. Для этого:
Теперь копия таблицы содержит значения и форматы. Выполним сортировку по цветам:
Сверху у нас теперь наихудшие показатели по чистой прибыли, которые имеют наихудшие показатели.
Примечание. Дальше можно выделить в этой таблице диапазон A4:F12 и повторно выполнить второй пункт этого раздела, только указать розовый сверху. Таким образом в первую очередь пойдут ячейки с цветом, а после обычные.
В Access существует несколько способов сортировки данных, отобранных посредством запроса. Быстро выполнить сортировку в окне запроса позволяют команда Сортировка из меню Записи, а также кнопки По возрастанию и По убыванию панели инструментов. Для этого следует включить в запрос поля таблицы, по которым будут рассортированы записи, и определить способ сортировки - по возрастанию или по убыванию. Данные можно упорядочить по алфавиту, а также по убыванию или возрастанию. При алфавитно-цифровой сортировке по возрастанию данные сортируются в таком порядке: сначала - элементы, начинающиеся со знаков пунктуации или специальных символов, затем - элементы, начинающиеся с цифр, а затем - элементы, которые начинаются с букв.
Сортировка данных в таблице по содержимому только одного столбца не всегда приводит к желаемым результатам, поэтому иногда требуется осуществлять сортировку по содержимому нескольких полей.
Примечание: Чтобы ускорить сортировку, следует размещать рядом поля, данные которых необходимо рассортировать.
Через окно запроса в исходную таблицу можно вставить новые записи, как при заполнении таблицы. Добавляемые или изменяемые данные помещаются в таблицу, на основе которой создан запрос. В результате ввода новых данных в таблицу может нарушиться порядок следования данных в запросе. Для восстановления порядка необходимо повторить вызов запроса и сортировку данных.
Выполнить сортировку данных можно и в окне конструктора запросов. Для этого необходимо нажать кнопку Открыть на вкладке Запросы окна базы данных. В окне выбора таблиц отметить имя таблицы, в которой следует выполнить сортировку данных, после чего нажать кнопки Добавить и Закрыть. Отметить в списке все имена полей, дважды щелкнув на заголовке списка, и переместить его в QBE-область. Для нужного поля задать в строке Сортировка способ сортировки. Сохранить запрос.
До сих пор мы рассматривали запросы, выполняющие отбор полей таблицы. При создании запроса можно задать дополнительные критерии, вследствие чего он будет осуществлять отбор только нужных сведений в каждом поле.Для формирования такого запроса необходимо в QBE-области ввести значение в ячейку, расположенную на пересечении строки Условия отбора и колонки с нужным именем.
Критерии, устанавливаемые в QBE-области, необходимо заключать в кавычки. Если Access идентифицирует введенные символы как критерий отбора, то заключает их в кавычки автоматически, а если нет, то сообщает о синтаксической ошибке. Программа Access не может распознать как критерий комбинацию символов и знаков подстановки.
Примечание: В запросах символы подстановки * и? применяются так же, как и во всех приложениях Microsoft Office 97. Символ звездочки заменяет любое количество букв или цифр, а знак вопроса - только один символ.
Все строки в QBE-области, расположенные под строкой Условия отбора , служат для задания критериев отбора. Таким образом, для одного поля можно определить два, три и более критериев отбора данных. По умолчанию все элементы критерия объединяются оператором ИЛИ . Это значит, что запрос выберет те записи, которые соответствуют хотя бы одному критерию.
Чтобы объединить несколько условий отбора оператором И , следует привести их в одной строке. Например, если необходимо выбрать записи из таблицы Зарплата , в которых указаны оклады от 310 до 1500, то в столбце Оклад надлежит ввести следующий критерий: Bet ween 310 and 1500 . Другой формой записи этого критерия является выражение > 310 And <1500 .
В результате будут отобраны только те записи, которые удовлетворяют обоим условиям, т.е. оклады, значения которых находятся в интервале от 310 до 1500.
Если необходимо отобрать несколько диапазонов значений, то критерий для каждого диапазона следует указать в отдельной строке.
Исключить группу данных из состава анализируемых запросом записей (например, оклад 400) позволяет следующий критерий: Not 400 . Другая форма записи этого критерия: <>400 . В этом случае можно не использовать кавычки.
ОператорыAnd иOr применяются как отдельно, так и совместно. Следует помнить, что условия, связанные операторомAnd , выполняются раньше условий, объединенных операторомOr .
