Транспонирование столбцов

У меня есть файл CSV, который выглядит так:

name, a v1, 1 name, b v1, 1 v2, 5 name, c v1, 4 v2, 6 name, d v2, 8 v3, asdf 

Используя Excel или LibreOffice, как я могу преобразовать его в это:

 name, v1, v2, v3 a, 1, , b, 1, 5, c, 4, 6, d, , 8, asdf 

Я искал ключевое слово транспонирование, но, похоже, это не правильное ключевое слово для поиска – все руководства показывают только, как выполнить полный транспонирование, чего я не хочу.

Я также нашел этот вопрос, но он принимает очень чистые данные и должен быть настроен вручную для количества столбцов: Конкретный Transpose в Excel

Я не знаю количество столбцов раньше времени, и некоторые из ячеек могут отсутствовать.

Мой подход, основанный на формулах, со следующим расположением данных:

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

Столбцы A и B импортируются из файла CSV.

Формула в хелперной колонке C :

 =IF(A2="name",B2,C1) 

Формулы в F1 и F2 учитывают уникальные записи в столбцах A и C , они не нужны для создания выходной таблицы (обе формулы массива подтверждены с помощью Ctrl + Shift + Enter ):

 =SUM(1/COUNTIF(C2:C12,C2:C12)) =SUM(1/COUNTIF(A2:A12,A2:A12)) 

Заголовки строк – уникальные значения из столбца C ( E6 , подтвержденные с помощью Ctrl + Shift + Enter и перетаскиваемые)

 =IFERROR(INDEX($C$2:$C$12, MATCH(0, COUNTIF($E$5:E5, $C$2:$C$12), 0)),"") 

Заголовки Colummn – уникальные значения из столбца A ( F5 , подтвержденные с помощью Ctrl + Shift + Enter и перетаскиваемые вправо)

 =IFERROR(INDEX($A$2:$A$12, MATCH(0, COUNTIF($E$5:E5, $A$2:$A$12), 0)),"") 

Сочетание двух столбцов INDEX / MATCH для заполнения выходной таблицы ( F6 , подтвержденной Ctrl + Shift + Enter и перетаскиваемой вправо и вниз):

 =IFERROR(INDEX($B$2:$B$12,MATCH(F$5&$E6,$A$2:$A$12&$C$2:$C$12,0)),"") 

Вам необходимо выполнить два действия для достижения своей цели:

1. Консолидация ваших данных

Переместите разные типы данных в отдельные столбцы. У вас будет три новых столбца с формулами:

  • Имя: =IF(A2="name",B2,C1)
  • V: =IF(A2="name","",A2)
  • Значение: =IF(A2="name","",B2)

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

2. Создать сводную таблицу

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

  • ответ от Rober Ilbrink на Stackoverflow с VBA
  • еще одно решение от clearandsimply.com снова с VBA

Я воспринял вашу просьбу буквально. Поскольку вы хотели использовать Excel для «преобразования» CSV-файла, выглядящего как ваш первый пример, похожим на ваш второй пример, я использовал Excel VBA для:

  • Откройте исходный файл CSV
  • Прочитайте его по строкам и создайте объект, состоящий из элементов каждого name
  • Вставьте строку метки в массив результатов
  • Восстановите отдельные линии, состоящие из Name и V's
  • Запишите строки в новый файл CSV с запросом форматирования.

Если вы откроете CSV-файлы в «Блокноте», вы увидите результаты.

Обязательно укажите ссылку на Windows Scripting Runtime для доступа к FileSystemObject.


 Option Explicit 'SET REFERENCE to Windows Scripting Runtime ' Under Tools/References in the main menu above Sub ChangeCSV() Dim FSO As FileSystemObject, F As File, TS As TextStream Dim FN As String Dim V As Variant Dim S As String Dim vRes() As Variant Dim cI As cItem, colI As Collection Dim bFirstName As Boolean Dim I As Long, J As Long bFirstName = True 'Get File FN = Application.GetOpenFilename("CSV Files (*.csv), *.csv") 'Read file into textstream Set FSO = New FileSystemObject Set TS = FSO.OpenTextFile(FN, ForReading) Set colI = New Collection Do Until TS.AtEndOfStream S = TS.ReadLine 'Remove leading and trailing quote marks, if present 'If Left(S, 1) = """" Then S = Mid(S, 2) 'If Right(S, 1) = """" Then S = Left(S, Len(S) - 1) If Not S = "" Then 'skip empty lines V = Split(S, ",") If Trim(V(0)) = "name" Then If bFirstName = False Then colI.Add cI 'don't add if first name bFirstName = False Set cI = New cItem End If CallByName cI, CStr(Trim(V(0))), VbLet, Trim(V(1)) End If Loop colI.Add cI 'Compile results ReDim vRes(0 To colI.Count, 1 To 4) vRes(0, 1) = "Name" vRes(0, 2) = "V1" vRes(0, 3) = "V2" vRes(0, 4) = "V3" For I = 1 To colI.Count With colI(I) vRes(I, 1) = .Name vRes(I, 2) = .V1 vRes(I, 3) = .V2 vRes(I, 4) = .V3 End With Next I 'Note different file name Set TS = FSO.CreateTextFile(Left(FN, Len(FN) - 4) & "MOD.csv", True) For I = 0 To UBound(vRes) V = Empty ReDim V(1 To 4) For J = 1 To 4 V(J) = vRes(I, J) Next J TS.WriteLine Join(V, ",") Next I TS.Close Set FSO = Nothing End Sub 

Давайте будем гением компьютера.