☰

Excel - Как расположить столбцы в соответствии с хронологическими месяцами

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-office
Excel - Как расположить столбцы в соответствии с хронологическими месяцами

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

Я попытался проиллюстрировать свою проблему здесь:

Месяц и год были записаны в текстовом формате вместо формата даты (в форме mmm'yy – включая апостроф). Я попытался создать пользовательский формат для месяцев и попытался переупорядочить строки на этой основе, но пользовательский формат сортировки, похоже, неактивен. Я новичок в Excel, поэтому любая помощь будет оценена.

решение1

Секрет хорошей сортировки в том, что Excel должен знать, что это дата.

Вы можете преобразовать текст в ячейке C3 (например) в дату с помощью этой формулы в ячейке C4:

=DATE(2000+RIGHT(C3,2),XLOOKUP(LEFT(C3,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},SEQUENCE(1,12)),1)

введите описание изображения здесь

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

Мы используем DATEфункцию. Она принимает три аргумента (разделенных запятыми):

  1. Год
  2. Месяц
  3. День

Год — 2000+RIGHT(C3,2). Это просто добавление двух крайних правых символов из вашего текста к 2000, чтобы получить год. Если у вас есть даты до 2000, вам нужно будет скорректировать эту часть.

Месяц — XLOOKUP(LEFT(C3,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},SEQUENCE(1,12)). Это говорит «Найти первые три символа в ячейке C3 в этом списке месяцев. Вернуть значение из той же позиции (т. е. если Apr, то 4-я позиция в списке), из SEQUENCE(1,12)». Эта ПОСЛЕДОВАТЕЛЬНОСТЬ — это просто список чисел от 1 до 12. Поэтому мы преобразуем трехзначный месяц обратно в число, чтобы использовать его в функции ДАТА.

День — это всего лишь 1. Это может быть любое число, меньшее или равное 28, поскольку во всех месяцах как минимум столько дней.

Итак, для C3 год — 2000+21, месяц — Apr, т. е. 4-й месяц, то есть 4, а день — 1. Итак, у нас есть DATE(2021,4,1).

решение2

Вы также можете попробовать эту формулу в ячейке C4

=DATE(2000+RIGHT(C3,2),MONTH(1&LEFT(C3,3)),1)

решение3

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

Выберите «вспомогательную строку». Это может быть строка 4 или 999, или любое другое место, которое вы захотите.

В столбце C этой строки (например, в ячейке C4) введите

=DATEVALUE(SUBSTITUTE(C3, "'", " 20"))

Заменяет SUBSTITUTEапостроф пробелом и  20. Например, это изменится Apr'21на  Apr 2021.  DATEVALUEЗатем функция изменит это текстовое значение на соответствующее значение даты.

Затем — «очевидная» часть, о которой никто не упомянул — вы можете отсортировать данные по вспомогательной строке.

Если вы предпочитаете, вы можете использовать

=DATEVALUE(LEFT(C3,3) & " 20" & RIGHT(C3,2))

Если у вас есть данные, которые не относятся к 21 веку (т.е. год не 20гг), вам нужно будет добавить логику, чтобы определить номер столетия.

Связанный контент