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

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

421-fff5c6cf4ca1543a72b64bf5dff0d8ef.png

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

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

Как сортировать данные в электронной таблице Excel (короткое видео)

Этот скринкаст охватывает несколько методов сортировки ваших данных. Просмотрите этот трехминутный видеоролик, чтобы быстро изучить эти профессиональные техники работы Excel. Мы начнем с простой сортировки и перейдем к более продвинутым методам, чтобы вы всегда смогли отсортировать данные так, как вам нужно.

Читайте дальше пошаговое руководство по сортировке данных в электронных таблицах Excel с использованием простых и передовых методов.

Примеры данных (бесплатная загрузка рабочей книги Excel)

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

Синтаксис сортировки

Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:

WithExpression.Sort

    .SortFields.Clear

    .SortFields.Add Key,SortOn,Order,DataOption

    .SetRange[Range]

    .Header=[xlGuess,xlYes,xlNo]

    .MatchCase=[True,False]

    .Orientation=[xlTopToBottom,xlLeftToRight]

    .Apply

EndWith

Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:

WithExpression.Sort

    .SortFields.Clear

    .SortFields.Add Key

    .SetRange[Range]

    .Apply

EndWith

Expression – выражение, возвращающее объект Worksheet, например:

ActiveSheet

Worksheets(«Лист1»)

ActiveWorkbook.Worksheets(«Лист1»)

Workbooks(«Книга1.xlsm»).Worksheets(«Лист1»)

Что можно сортировать?

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

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

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

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

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

Поскольку в большинстве таблиц числовые параметры привязаны к какому-либо тексту – фамилии или названию товара то удобно расположить по алфавиту. Рассмотрим особенности работы с этим инструментом в редакторе Еxcel на примере таблицы «Содержание питательных веществ во фруктах и овощах».

Для того чтобы отсортировать названия овощей и фруктов по алфавиту необходимо выделить первый столбец, кликнув мышью по его заголовку. Далее требуется перейти на панель инструментов «Редактирование» и открыть вкладку «Главная», на которой расположена специальная кнопка «Сортировка и фильтр». Для упорядочивания названий в первом столбце от А до Я или наоборот, достаточно выбрать соответствующую команду сортировки в выпадающем меню. Если в таблицу Еxcel будут добавлены данные о других продуктах сработает автоматическая сортировка.

Сортировка по одному критерию

  1. В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
  2. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].

44-079f4fb55b755f6f198bee97d7c95390.png

  1. Выбрать нужную кнопку: 508-7134f8f5aced525d1c11d229063305e7.pngсортировка по возрастанию или 181-75c168b671d4ce827fca23907d85f114.pngсортировка по убыванию.

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

Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.

Несколько ячеек

Для сортировки в Excel по нескольким столбцам или ячейкам стоит воспользоваться пунктом “Настраиваемый список” и выбрать меню “Добавить уровень”. Тут следует указать для каждого столбца необходимый параметр редактирования. Именно в таком случае информация в таблице изменится для каждого столбца отдельно.

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

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

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

Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.

  1. Открываем меню «Настраиваемая сортировка». Назначаем первый критерий. filtr-sortirovka5-11.png
  2. Нажимаем кнопку «Добавить уровень». filtr-sortirovka5-12.png
  3. Появляются окошки для введения данных следующего условия сортировки. Заполняем их.

filtr-sortirovka5-13.png

Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.

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

%D1%81%D0%BE%D1%80%D1%82%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B0-%D1%81%D1%82%D1%80%D0%BE%D0%BA.jpegОбычно сортировка данных в Экселе производится по столбцам, но иногда возникает необходимость упорядочить данные в строках, то есть не вертикально, а горизонтально.

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

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

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

%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D0%B0.jpg

Наши действия будут следующими.

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

%D1%81%D0%BE%D1%80%D1%82%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B0-%D0%BF%D0%BE-%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%B0%D0%BC.jpg

На вкладке Главная щелкаем по Сортировка и фильтр, а далее Настраиваемая сортировка (2). В появившемся окне нажимаем на кнопку Параметры (3) и в следующем диалоговом окне указываем, что сортировка будет производиться по столбцам (4).

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

%D1%81%D0%BE%D1%80%D1%82%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B0-%D0%BF%D0%BE-%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%B0%D0%BC-1.jpg

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

Нажимаем ОК и получаем результат.

%D1%81%D0%BE%D1%80%D1%82%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B0-%D0%BF%D0%BE-%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%B0%D0%BC-2.jpg

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

Если нужно отсортировать строки в Excel по алфавиту, то делается это точно таким же способом. Например, чтобы расставить ноутбуки в алфавитном порядке, нам нужно будет упорядочить тот же самый диапазон по второй строке, но при этом в разделе Порядок нужно выбрать От А до Я.

Источник: pcsecrets.ru

Тестируем

Теперь добавим новую строку в исходную таблицу. В динамически сортируемых таблицах мы должны получить соответствующую сортировку.

1. В ячейку А15 исходной таблицы введите слово Морковь ; 2. В ячейку В15 введите Объем продаж Моркови = 25; 3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица; 4. В сортированной таблице новая строка будет отображена предпоследней.

По цвету

Форматирование данных по дате или по цвету мало чем отличается друг от друга. Требуется выделить таблицу, зайти в меню “Сортировка” и выбрать “Параметры”. В подразделе следует выбрать параметр “Сортировка по цвету”. После этого в соответствующем окне нужно выставить оттенок цвета с которого будет начинаться сортировка (т.е. он будет находится вверху или внизу таблицы). Редактор после подтверждения действия выдаст отредактированную таблицу, в которой указанный цвет будет расположен вверху или внизу.параметры сортировки

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

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

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

Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий:

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

Пользовательская сортировка в Excel — Microsoft Excel для начинающих

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

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

Создание пользовательской сортировки в Excel

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

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

    Как сделать сортировку в excel 2013?

  2. Откройте вкладку Данные, затем нажмите команду Сортировка.
  3. Откроется диалоговое окно Сортировка. Выберите столбец, по которому Вы хотите сортировать таблицу. В данном случае мы выберем сортировку по размеру футболок. Затем в поле Порядок выберите пункт Настраиваемый список.

    Как сделать сортировку в excel 2013?

  4. Появится диалоговое окно Списки. Выберите НОВЫЙ СПИСОК в разделе Списки.
  5. Введите размеры футболок в поле Элементы списка в требуемом порядке. В нашем примере мы хотим отсортировать размеры от меньшего к большему, поэтому введем по очереди: Small, Medium, Large и X-Large, нажимая клавишу Enter после каждого элемента.

    Как сделать сортировку в excel 2013?

  6. Щелкните Добавить, чтобы сохранить новый порядок сортировки. Список будет добавлен в раздел Списки. Убедитесь, что выбран именно он, и нажмите OK.

    Как сделать сортировку в excel 2013?

  7. Диалоговое окно Списки закроется. Нажмите OK в диалоговом окне Сортировка для того, чтобы выполнить пользовательскую сортировку.

    Как сделать сортировку в excel 2013?

  8. Таблица Excel будет отсортирована в требуемом порядке, в нашем случае – по размеру футболок от меньшего к большему.

    Как сделать сортировку в excel 2013?

Сортировка в Excel по формату ячейки

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

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

    Как сделать сортировку в excel 2013?

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

    Как сделать сортировку в excel 2013?

  4. В поле Порядок выберите цвет для сортировки. В нашем случае мы выберем светло-красный цвет.
  5. Нажмите OK. Таблица теперь отсортирована по цвету, а ячейки светло-красного цвета располагаются наверху. Такой порядок позволяет нам четко видеть неоплаченные заказы.

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/polzovatelskaja-sortirovka-v-excel-114.html

Динамическая сортировка таблицы в MS Excel

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

  1. Есть набор простых чисел, которые нужно отсортировать по возрастанию.
  2. Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
  3. Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.

Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.

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

  1. Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
  2. Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
  3. В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
  4. Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон «фрукты» еще одно значение «помело» и проверим:

Скачать формулы сортировки данных в Excel

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

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

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

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

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

Итоги.

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

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

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, отзывы и замечания пишите в комментариях.

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

Как убрать сортировку в Excel

Для отмены единичной сортировки диапазона данных достаточно нажать кнопку «Отменить ввод» в левом углу экрана. Бывает так, что изменения в файле были сохранены и отменить действие невозможно. Как убрать сортировку в этом случае?

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

Задача 2.1 (Двухуровневая сортировка)

Теперь снова отсортируем исходную таблицу по Объему продаж. Но теперь для повторяющихся значений (в столбце А три значения 74), соответствующие значения выведем в алфавитном порядке.

Для этого воспользуемся результатами Задачи 1.1 и Задачи 2.

sort-4.png

Подробности в файле примера на листе Задача2.

Используем горизонтальную сортировку с помощью Power Query

Для совершения каких либо манипуляций в Power Query данные необходимо предварительно загрузить. На вкладке данные, в зависимости от типа источника данных пользуемся соответствующей командой. В нашем, простом случае, – это “Из таблицы/диапазона”.

H_pic4.png

В самом окне редактора Power Query переходим на вкладку “Добавление столбца”, команда “Настраиваемый столбец”. В диалоговом окне настройки столбца вводим функцию List.Sort(), в качестве аргументов передаем столбцы. По умолчанию функция сортируем по возрастанию, поэтому этот опциональный параметр нужно обязательно указывать, если сортировка нужна в убывающем порядке.

H_pic5.png

а вот формула для убывающего порядка

Поскольку результатом получается элемент типа список, нужно определиться с дальнейшими действиями сортировки, если нужно получить столбец со значениями, выбираем опцию “Развернуть в новые строки”, однако, если идет сортировка для большого количества данных, лучше воспользоваться опцией “Извлечь значения…”. При извлечении значений все они извлекаются в одну ячейку с определенным разделителем (ну или без). Если потом возникнет необходимость разнести значения по ячейкам сделать это можно несколькими способами, мы их рассмотрели в данном материале, однако, лучшим вариантом будет воспользоваться командой “Разделить столбец” вкладки “Главная” в самом Power Query.

Источник: msoffice-prowork.com

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...