1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как автоматически отсортировать столбец по значению в Excel?

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

Сортировка данных в Excel — это инструмент для простого представления информации.

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

Порядок сортировки в Excel

Меню сортировки можно вызвать двумя способами:

  1. Щелкните правой кнопкой мыши на таблице. Выберите «Сортировка» и метод.
  2. Откройте диалоговое окно «Данные» — вкладка «Сортировка».

На панели задач есть кнопка, которая обеспечивает доступ к часто используемым способам сортировки:

Сортировка таблицы по одной колонке:

  1. Чтобы программа правильно выполнила задание, выберите нужный столбец в диапазоне данных.
  2. Затем действуйте в соответствии с поставленной задачей. Если вы хотите выполнить простую сортировку по возрастанию/убыванию (алфавитная или иная), просто нажмите соответствующую кнопку на панели задач. Если диапазон содержит более одного столбца, Excel открывает диалоговое окно типа: Чтобы значения строк были одинаковыми, выберите действие «автоматически расширять выбранный диапазон». В противном случае сортироваться будет только выбранный столбец — структура таблицы будет нарушена.

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

Сортировка по цвету ячейки и по шрифту

Excel предоставляет пользователю множество вариантов форматирования. Поэтому можно работать с разными форматами.

Назначьте столбец «Итого» в таблице обучения и придайте ячейкам разные оттенки значений. Отсортируем по цвету:

  1. Выберите столбец — щелкните правой кнопкой мыши — «Сортировать».
  2. Выберите «Ячейки, цвет которых выбран первым» из списка.
  3. Примите «Расширить диапазон автоматически».

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

В открывшемся окне введите нужные параметры:

Здесь можно выбрать порядок отображения ячеек разных цветов.

Тот же принцип применяется для сортировки данных по шрифту.

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

Как установить вторичный порядок сортировки в Excel? Чтобы решить эту проблему, необходимо задать некоторые условия сортировки.

  1. Откройте меню «Настроенная сортировка». Назначьте первый критерий.
  2. Нажмите на кнопку «Добавить уровень».
  3. Появляются окна для ввода следующего условия сортировки. Заполните их.
Читайте так же:
Как вернуться к предыдущей версии сохранения в Excel?

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

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

По умолчанию данные сортируются по столбцам. Как сортировать по строкам в Excel :

  1. В диалоговом окне «Пользовательская сортировка» нажмите на кнопку «Параметры».
  2. Выберите «Колонки диапазона» в открывшемся меню. Нажмите кнопку OK. В окне «Сортировка» появятся поля для заполнения условий по строкам.

Несколько параметров используются для сортировки таблицы Excel.

Случайная сортировка в Excel

Встроенные параметры сортировки не позволяют произвольно располагать данные в столбце. Эта задача может быть выполнена функцией SLCHIS.

Например, вы хотите расположить набор чисел случайным образом.

Поместите курсор в соседнюю ячейку (слева направо, не имеет значения). В строке формул введите SLCHIS(). Нажмите Enter. Когда мы применим формулу ко всему столбцу, будут сгенерированы случайные числа.

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

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

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

  1. Имеется набор простых чисел, которые нужно отсортировать в порядке возрастания.
  2. Поместите курсор в соседнюю ячейку и введите формулу: = LEAST(A:A;LINE(A1))). Именно так. В качестве диапазона мы указываем весь столбец. А в качестве коэффициента мы используем функцию STRING со ссылкой на первую ячейку.
  3. Измените число 7 на 25 в начальном диапазоне — «сортировка» вверх также изменится.

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

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

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

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

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

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

Читайте так же:
Как в Excel разделить доллары и центы на две колонки?

Упорядочивание чисел

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

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

Для этого выделите столбец таблицы «Стоимость, руб.» и перейдите на вкладку «Главная». Среди инструментов в блоке «Редактирование» есть «Сортировка и фильтр». Выберите «Сортировка по возрастанию».

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

Поскольку мы хотим упорядочить все записи таблицы, мы нажимаем «Развернуть» и «Сортировать». По мере увеличения стоимости жизни записи располагаются в порядке возрастания.

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

Для того чтобы цифры были отсортированы правильно, необходимо следить за тем, чтобы данные в столбце имели правильный формат. Если значения в таблице введены в виде текста, а не чисел, сортировка не будет происходить в ожидаемом порядке. В таблице приведен пример сортировки чисел 10, 11, 100, 15, 110, 132 в зависимости от формата данных.

Числовой форматТекстовый формат
1010
11100
1511
100110
110132
13215

Упорядочивание текста

В Excel сортировка столбцов, содержащих текст, выполняется так же, как и операция с числами. В нашей таблице цен на проживание в гостинице уже есть столбец «Питание». Поэтому давайте применим сортировку к этому столбцу. Щелкнув на его заголовке, выделите столбец и откройте панель инструментов Редактирование на вкладке Главная. В выпадающем меню теперь вместо команд сортировки по возрастанию и убыванию есть команды сортировки от A до Z и наоборот.

Постройте ячейки от A до Z, снова задавая диапазон операции.

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

Читайте так же:
Как быстро вставить пробел между каждой строкой в ​​Excel?

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

Упорядочивание дат

Помимо всех вышеперечисленных операций, относящихся к числам и строкам, выравниваются также даты и время. Что касается названия команды, то для такого рода данных это «Сортировка от старого к новому» и наоборот. Пример показан ниже.

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

Настраиваемые списки

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

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

Выберите «Пользовательский список» в поле «Порядок». После выбора дней недели нажмите OK.

Упорядочивание по цвету и значкам

Элементы ячеек также можно сортировать по цвету для удобства их расположения. Можно использовать цвет заливки и шрифта. В таблице дел давайте обозначим рабочие задачи оранжевым цветом, семейные дела — синим, а праздники — бежевым и желтым. И выстроим их по типу.

Для этого перейдите к настраиваемой сортировке. Помимо описанных выше способов, вы можете воспользоваться меню раздела «Редактирование» вкладки «Главная». Выберите опцию «Пользовательская сортировка и фильтр» из списка команд «Сортировка и фильтр».

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

Также можно организовать элементы таблицы на основе значков. Например, первая задача в списке может быть отмечена восклицательным знаком как важная.

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

Читайте так же:
Два самых простых способа создать динамический диапазон в диаграмме Excel

Чтобы пересчитать формулы, Worksheet_Change не будет отвечать.

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

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

При пересчете формул будет происходить сортировка. Чтобы пересчет происходил даже при ручном изменении данных, в произвольную ячейку необходимо добавить волатильную функцию (volatile — автоматически пересчитывается при редактировании листа). Например:

Недостатком данного решения является то, что сортировка включается при любом изменении, внесенном в лист.

Если пересчет формул происходит нечасто, можно использовать параллельно с Worksheet_Change событие активации листа — Worksheet_Activate

Как сделать сортировку в Excel по возрастанию и по убыванию

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

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

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

Какие инструменты есть в Excel для сортировки данных? Рассмотрим конкретные примеры, чтобы дать полный ответ на этот вопрос.

Составление таблицы для безопасной и надлежащей сортировки данных:

  1. Выделяем и копируем всю таблицу. Продажи по товарам.
  2. На другом чистом листе (например, Лист2)щелкаем правой кнопкой мышки по ячейке A1. Из контекстного меню выбираем опцию: «Специальная вставка». В параметрах отмечаем «значения» и нажимаем ОК.

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

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

  1. Выделите столбцы листа, которые охватывает исходная таблица. Выделение.
  2. Выберите инструмент на закладке: «Данные»-«Сортировка». Инструмент.
  3. В появившимся окне укажите параметры сортировки. В первую очередь поставьте галочку напротив: «Мои данные содержат заголовки столбцов», а потом указываем следующие параметры: «Столбец» – Чистая прибыль; «Сортировка» – Значения; «Порядок» – По убыванию. И нажмите ОК.
Читайте так же:
Как вставить данные в альтернативные пустые строки в Excel?

Данные упорядочены в таблице по отношению к столбцу «Чистая прибыль».

Как в Excel сделать сортировку в столбце

Теперь давайте отсортируем только один столбец без привязки к другим столбцам или всей таблице:

  1. Выделите диапазон значений столбца который следует отсортировать, например «Расход» (в данном случаи это диапазон E1:E11). Расход.
  2. Щелкните правой кнопкой мышки по выделенному столбцу. В контекстном меню выберите опцию «Сортировка»-«от минимального к максимальному» От минимального к максимальному.
  3. Появится диалоговое окно «Обнаруженные данные вне указанного диапазона». По умолчанию там активна опция «автоматически расширять выделенный диапазон». Программа пытается охватить все столбцы и выполнить сортировку как в предыдущем примере. Но в этот раз выберите опцию «сортировать в пределах указанного диапазона». И нажмите ОК.

Колонка сортируется независимо от других колонок в таблице.

Расход результат.

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

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

  1. Вернемся к нашей исходной таблице на Лист1 и снова полностью выделим ее, чтобы скопировать. Лист1.
  2. Правой кнопкой мышки щелкните по ячейке A1 на копии таблицы на третьем листе (Лист3) и выберите опцию «Специальная вставка»-«значения».
  3. Повторно делаем щелчок правой кнопкой мышки по ячейе A1 на листе 3 и повторно выберем «Специальная вставка» только на этот раз указываем «форматы». Так мы получим таблицу без формул но со значениями и форматами Специальная вставка.Форматы.
  4. Разъедините все объединенные ячейки (если такие присутствуют).

Значения и форматы были добавлены в копию таблицы. Давайте отсортируем по цвету:

  1. Выделите таблицу и выберите инструмент «Данные»-«Сортировка».
  2. В параметрах сортировки снова отметьте «Мои данные содержат заголовки столбцов» и укажите: «Столбец» — Чистая прибыль; «Сортировка» — Цвет ячеек; «Порядок» — Красный, выше. И нажмите OK.

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

Результат3.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector