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

Как в Excel сортировать числа через дефис?

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

Все операции выполняются в один момент времени, а затем ассоциируются с определенной датой. Данные в Excel преобразуются в целые числа. Число 01.01.1900 равно 1, а 02.01.1900 равно 2 и т.д. Таким образом, каждая дата имеет свое целое число. Определения года, месяца и дня — это просто обработка последовательных числовых значений в соответствующем формате. По этой причине даже самые простые операции с датами, выполняемые в Excel (например, сортировка), весьма проблематичны.

Сортировка в Excel по дате и месяцу

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

Даты транзакций.

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

  1. В ячейке A1 введите название столбца «№п/п», а ячейку A2 введите число 1. После чего наведите курсор мышки на маркер курсора клавиатуры расположенный в нижнем правом углу квадратика. В результате курсор изменит свой внешний вид с указательной стрелочки на крестик. Не отводя курсора с маркера нажмите на клавишу CTRL на клавиатуре в результате чего возле указателя-крестика появиться значок плюсик «+». Номер по порядку.
  2. Теперь одновременно удерживая клавишу CTRL на клавиатуре и левую клавишу мышки протяните маркер вдоль целого столбца таблицы (до ячейки A15).

В результате столбец автоматически будет содержать последовательность цифр от 1 до 14.

Читайте так же:
Как автоматически заполнять другие ячейки при выборе значений в раскрывающемся списке Excel?

Полезный совет! Excel предлагает несколько решений для большинства задач. В Excel правую кнопку мыши можно использовать для автоматического нормирования столбцов. Для этого достаточно установить курсор на маркер клавиатурного курсора (в ячейке A2), а затем перетащить маркер вдоль столбца, нажав только правую кнопку мыши. После того как вы отпустите правую кнопку мыши, автоматически появится контекстное меню, из которого нужно выбрать пункт «Заполнить». Аналогично первому способу, столбец будет автоматически заполнен последовательностью чисел.

Альернативное автозаполение.

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

  1. Заполните ячейки D1, E1, F1 заголовками: «Год», «Месяц», «День».
  2. В соответствии с каждым столбцом напишите соответствующие функции под заголовками и скопируйте их вдоль каждого столбца:
  • D1: =ГОД(B2);
  • E1: =МЕСЯЦ(B2);
  • F1: =ДЕНЬ(B2).

Результат должен быть таков:

Таблица с датами.

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

Предположим, мы хотим отсортировать даты транзакций по месяцам. В данном случае порядок дней и лет не имеет значения. Для этого просто перейдите к любой ячейке в столбце «Месяц (E)» и выберите инструмент: «ДАННЫЕ» — «Сортировка и фильтр» — «Сортировать по возрастанию».

Чтобы вернуть данные в таблице к форме по умолчанию, перейдите к любой ячейке в столбце «n/a» (A) и снова выберите инструмент «Сортировка по возрастанию».

Как сделать сортировку дат по нескольким условиям в Excel

После этого мы можем выполнить более сложную сортировку дат в соответствии с различными условиями. Задача заключается в следующем — транзакции должны быть отсортированы в следующем порядке:

  1. Годы в порядке возрастания.
  2. Месяцы в периодах конкретных лет — в порядке убывания.
  3. Дни в периодах конкретных месяцев — в порядке убывания.
Читайте так же:
Как автоматически скрыть столбцы по дате в Excel?

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

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

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

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

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

Алфавитно-цифровая сортировка в Excel

Можно ли отсортировать буквенно-цифровые символы в Excel по a-1, a-2, a-3. a-123 вместо a-1, a-10, a-100, a-11?

Сортировка от старого к новому или через A-Z определенно не даст мне желаемого результата. Ячейки не были отформатированы как числа, поэтому я попытался отформатировать их в числовом формате.

2 ответов

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

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

Предположим, что префикс «a — » никогда не меняется, записи находятся в столбце a, начиная со строки 2, и доступен столбец B. B2 будет выглядеть так: =value(mid(a2,3,len(a2)-2)), а затем скопируйте формулу по мере необходимости. Выделил оба столбца и отсортировал B.

Если там могут быть другие префиксы, например, B-, C- и т.д., аналогично разделите текстовую часть на другой столбец. Далее сортируйте текст по колонкам, при этом первая колонка должна быть отсортирована, а вторая — иметь номер колонки.

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

Чтобы объяснить строковые функции, начните с функции mid, которая извлекает строку символов из строки символов. mid(a2,3,len (a2)-2) рассматривает текст в a2, начиная с третьего символа (первая цифра, предполагая, что все префиксы «a -«), а затем берет количество символов, которое на два меньше, чем количество в исходном тексте (длина исходного текста минус символы «a»). Функция value преобразует строку символов в число вместо строки цифр.

The Result

Пользователь @fixer1234 прав, вероятно, вы хотите использовать строковые функции. Вот способ сделать это.

Шаг 1

В столбце «числа» выделите интервал, а затем через дефис напишите: do.

Data > Text to Columns > Delimited > Next > Other: — > Finish

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

Вы также можете использовать эту функцию для извлечения числа из колонки a:

Шаг 2

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

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

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

[ UPDATE] для определения самого длинного числа можно использовать следующую функцию (хотя мы пока не совсем понимаем зачем):

В качестве альтернативы вы можете просто ввести в ячейку следующую формулу (вы видите ее на рисунке выше, так как ячейка выделена оранжевым цветом), но вместо того, чтобы просто нажать клавишу ENTER для установки ячейки, нажмите комбинацию клавиш CTRL-SHIFT-ENTER . Таким образом, функция станет формулой массива, не требуя использования функций типа INDEX().

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

После нажатия CTRL-SHIFT-ENTER содержимое ячейки изменится на это, но ввод вручную ничего не даст:

Но если вы хотите сделать все правильно. Вам просто нужно определить, сколько цифр составляет самое большое число в списке: «1», конечно, имеет одну цифру, «10» — две цифры, «100» — три цифры и так далее.

Шаг 3

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

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