Как распределить данные в формате «категория-значение» по категориям в Excel?

Question

[Chichi]

Я пытаюсь провести регрессионный анализ данных которые спарсил с одного сайта. Я смог получить данные с сайта только в таком неудобном формате:
Данные в 1-й колонке содержат информацию о типе данных, разделенные символом ':' .
Year:Storey:Area:Condition:Type:Name

Данные во 2-й колонке содержат саму информацию, соответствующую к типам данных с первой колонки , также разделенную символом ':'
2015:3:170:Renovated:TypeB:John

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


Пытаюсь перевести данные в следующий формат:


Пытался разбить данные на колонки с помощью разделителей, но проблема в том кол-во категорий в записи варьируется. Для 1 записи может быть 5 категорий(типов данных), а для другой 2 типа данных.

Как распределить данные такого в формата по категориям в Excel?

Comments

[h8nor]

Да, удобнее использовать VBA. У формул слишком ограниченный функционал.

Answer 1

[Антон Федорян]

Достаточно просто и с формулами. Вот что получилось:



Теперь по ячейкам:

Для Year:

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($D$1;A2;1)>0;ЕСЛИ((ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;":";"")))=0;ПСТР(B2;1;ДЛСТР(B2));ПСТР(B2;1;ПОИСК(":";B2;1)-1));"NULL");"NULL")

Для Storey:

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($E$1;A2;1)>0;ЕСЛИ(ДЛСТР(A2)=(ЕСЛИ(D2="NULL";0;5)+6);1;ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1))+1;ПОИСК(":";B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1))+1)-((ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1))+1)));"NULL");"NULL")

Для Area:

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($F$1;A2;1)>0;ЕСЛИ(ДЛСТР(A2)=(ЕСЛИ(D2="NULL";0;5)+ЕСЛИ(E2="NULL";0;7)+4);ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1))+1;ДЛСТР(B2)-(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)));ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1))+1;ПОИСК(":";B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1))+1)-((ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1))+1)));"NULL");"NULL")

Для Condition:

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($G$1;A2;1)>0;ЕСЛИ(ДЛСТР(A2)=(ЕСЛИ(D2="NULL";0;5)+ЕСЛИ(E2="NULL";0;7)+ЕСЛИ(F2="NULL";0;5)+9);ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1))+1;ДЛСТР(B2)-(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)));ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1))+1;    ПОИСК(":";B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1))+1)-((ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1))+1) ));"NULL");"NULL")

Для Type:

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($H$1;A2;1)>0;ЕСЛИ(ДЛСТР(A2)=(ЕСЛИ(D2="NULL";0;5)+ЕСЛИ(E2="NULL";0;7)+ЕСЛИ(F2="NULL";0;5)+ЕСЛИ(G2="NULL";0;10)+4);ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)+ЕСЛИ(G2="NULL";0;ДЛСТР(G2)+1))+1;ДЛСТР(B2)-(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)+ЕСЛИ(G2="NULL";0;ДЛСТР(G2)+1)));ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)+ЕСЛИ(G2="NULL";0;ДЛСТР(G2)+1))+1;    ПОИСК(":";B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)+ЕСЛИ(G2="NULL";0;ДЛСТР(G2)+1))+1)-((ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)+ЕСЛИ(G2="NULL";0;ДЛСТР(G2)+1))+1) ));"NULL");"NULL")

Для Name:

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($I$1;A2;1)>0;ПСТР(B2;(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)+ЕСЛИ(G2="NULL";0;ДЛСТР(G2)+1)+ЕСЛИ(H2="NULL";0;ДЛСТР(H2)+1)+1);ДЛСТР(B2)-(ЕСЛИ(D2="NULL";0;ДЛСТР(D2)+1)+ЕСЛИ(E2="NULL";0;ДЛСТР(E2)+1)+ЕСЛИ(F2="NULL";0;ДЛСТР(F2)+1)+ЕСЛИ(G2="NULL";0;ДЛСТР(G2)+1)+ЕСЛИ(H2="NULL";0;ДЛСТР(H2)+1)));"NULL");"NULL")

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