Преобразование многих столбцов в столбец заголовков и столбец всех данных в Excel с функциями листа

Question

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

Column1    Column2    Column3    Column4    Column5
Data1      Data1      Data3
Data2                 Data4

Таким образом, эти данные становятся

Column1    Data1
Column1    Data2
Column2    Data1
Column3    Data3
Column3    Data4

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

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

Не против добавления вспомогательных столбцов. На этой странице я уже на полпути:http://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html#a1

Answer 1

Так что ссылка, которую я нашел, каскадно перешла в решение, но потребовалось более 2 формул.

http://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html#a1

А вот ссылка на пример листа, который я создал:https://drive.google.com/file/d/0B4hGhOqw8ilUOVdkMGVLLVR2Uzg/view?usp=sharing

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

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

=OFFSET(FirstHeader,0,ROW()-CurrentRowNumber)&""

может получить заголовки вашего столбца в столбец для вас. Итак, теперь у вас есть:

ColumnHeaders
Column1
Column2
Column3

от:

Column1    Column2    Column3    BlankColumn    BlankColumn
Data1      Data1      Data3
Data2                 Data4

Теперь мне нужно было подсчитать количество элементов данных в каждом столбце. Что я получил от этого LittleMonstrosity:

=IF(B2<>"",SUMPRODUCT(--(INDIRECT(ADDRESS(FirstRowIndex,MATCH(B2,$A$1:$AA$1,0))&":"&ADDRESS(LastRowIndex,MATCH(B2,$A$1:$AA$1,0)))<>"")),"")

Где первый и последний индекс строки - это предопределенная граница для количества списков, которые я собираюсь принять из-за ограничений памяти.Аналогично рассуждению о разрешении только столбцов от A до AA. Может быть, я перенесу свой ограничивающий прямоугольник позже, чтобы сменить его из одного места.

Таким образом, используя это, я теперь получаю:

CountIndices   ColumnHeaders
2              Column1
1              Column2
2              Column3

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

=IFERROR(A2+LittleMonstrosity,"")

получить:

StartIndices   ColumnHeaders
1              Column1
3              Column2
4              Column3
6

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

=VLOOKUP(ROWS($B$2:$B2),DataRange,2)

Еще раз с этими границами, встроенными в DataRange. Скоро я даже сделаю эту динамику в разумных пределах.

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

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1
3              Column2          Column1
4              Column3          Column2
6                               Column3
                                Column3

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

=IF(M2<>"",INDEX(DataRange,COUNTIF($M$2:$M2,M2),MATCH(M2,HeaderRange,0)),"")

Теперь я наконец получаю:

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1   Data1
3              Column2          Column1   Data2
4              Column3          Column2   Data1
6                               Column3   Data3
                                Column3   Data4