Excel - обработка данных в неудобном формате

Question

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

> Account_Details         Account_Name 
> account # 1             client name 
> product 1 name          product 1 value          
> product 2 name          product 2 value           
> product 3 name          product 3 value    
> account # 2             client name
> product 3               product 3 value
> product 2 name          product 2 value
> account # 3             client name
> product 6 name          product 6 value

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

Account Number       Product 1     Product 2    Product 3...                
account # 1            1234.1        346.2        10154.36
account # 2            0             556.6        6154.63
account # 3            123.04        905.24       101.16
.
.
.

Оригинальные заголовки «Сведения об учетной записи» и «Имя учетной записи» на самом деле являются просто местозаполнителями и не нужны. Любая помощь будет оценена.

Благодарю.

Answer 1

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

Затем создайте сводную таблицу для данных и настройте поля следующим образом:

Если вы хотите, чтобы имя учетной записи вместо номера учетной записи, измените формулу в D4 чтобы B4 вместо A4 .

Answer 2

Вы можете решить эту проблему с помощью регулярных выражений и преобразования в TSV.

Если ваши данные отформатированы не так, как в вашем примере, это не сработает.

s/> account # (\d+) +(.+)\n> product 1 name +(.+)\n> product 2 name +(.+)\n> product 3 name +(.+)\n/account # \1\t\2\t\3\t\4\t\5\r/

комментарии:

s/                       # We are replacing text
> account # (\d+)        # We are capturing the account number as \1
 +                       # Ignoring all spaces afterwords
(.+)\n                   # Capture the client name as \2 and look for a new line
> product 1 name +(.+)\n # Capture the product 1 value as \3
> product 2 name +(.+)\n # Capture the product 1 value as \4
> product 3 name +(.+)\n # Capture the product 1 value as \5
/                        # Done searching, start replacing
account # \1\t           # Write out the account number then a tab
\2\t\3\t\4\t\5           # Write out the client name and product values seperated by tabs
\r/                      # Write a new line for the next bit of data and terminate

Который превратит ваши данные в:

account # 1    client name    product 1 value    product 2 value    product 3 value
account # 2    client name    product 1 value    product 2 value    product 3 value

Разделены вкладками, которые можно легко импортировать в Excel.

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