☰

Как сгруппировать несколько столбцов дат|данных в один столбец дат слева?

tag-icon • tag-icon microsoft-excel sorting spreadsheet format
Как сгруппировать несколько столбцов дат|данных в один столбец дат слева?

У меня есть большой набор данных, который выглядит следующим образом:

этот

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

решение1

Если у вас НЕТ O365, я предлагаю решение VBA или Power Query.

Однако, если у вас Excel O365, вы можете попробовать следующее:

  • Создайте таблицу из ваших данных (имя моей таблицы — ) Table5; измените формулы так, чтобы они соответствовали имени вашей таблицы.
    • При этом заголовки будут автоматически помещены в пустые столбцы с именами, например, ColumnNгде Nнаходится число.

Учитывая, что мои результаты начинаются с A22 (см. ниже)

Формулы:

  • Отсортированный список уникальных дат из вашей исходной таблицы:

      A23: =SORT(UNIQUE(FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,INDEX(Table5,SEQUENCE(ROWS(Table5)),SEQUENCE(,COLUMNS(Table5)/2,1,2))) & "</s></t>","//s")))

  • Список корпораций:

      B22: =FILTER(Table5[#Headers],ISERR(FIND("Column",Table5[#Headers])))

  • Верните соответствующие значения

      B23: =LET(x,INDEX(Table5,SEQUENCE(ROWS(Table5)),SEQUENCE(,2,MATCH(B$22,Table5[#Headers],0)-1)),y,XLOOKUP($A23,INDEX(x,0,1),INDEX(x,0,2),""),y)

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

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

Пояснение формулы уникальных дат:

INDEX(Table5,SEQUENCE(ROWS(Table5)),SEQUENCE(,COLUMNS(Table5)/2,1,2))

вернет таблицу, содержащую только нечетные столбцы (столбцы с датами)

TEXTJOIN("</s><s>",TRUE,the_odd_column_table)

объединяет все записи в этих столбцах дат в одну строку с указанным разделителем

FILTERXML("<t><s>" & the_joined_string & "</s></t>","//s")))

создает XML, а затем возвращает массив узлов(это будут даты из исходной таблицы).

SORT(UNIQUE(the_dates_array))

делает очевидное.

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