Excel: как преобразовать список имя-значение повторяющейся структуры записи в таблицу / кросс-таблицу?

877
adolf garlic

«текст в столбцы» и сводная таблица не являются решениями!

источник данных

myval: value1 mydate: 11:11:2001 myname: bob diamond mynum: 5648 endmarker myval: value2 mydate: 10:10:2008 myname: jimmy knapp mynum: 6661 endmarker

в

myval mydate myname mynum value 1 11:11:2001 bob diamond 5648 value 2 10:10:2008 jimmy knapp 6661

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

 =IFERROR(LEFT(A1,FIND(":",A1,1)-1),"")  =IFERROR(RIGHT(A1,LEN(A1)-FIND(":",A1,1)),"")

Который красиво разделяется только на первый разделитель

Google раздражающе для «список в таблицу» или «список в кросс-таблицу» возвращает результаты с точностью до наоборот

РЕДАКТИРОВАТЬ : в каждой записи содержится около 200 полей, значения всегда отображаются в одном и том же порядке, но некоторые из них присутствуют не всегда, что означает, что их нельзя решить напрямую с помощью команды OFFSET, которая в противном случае работала бы

3
У вас есть имена полей, которые вы могли бы использовать для идентификации значений, но идентична ли каждая последовательность (все одинаковые поля в одном и том же порядке), чтобы вы могли надежно использовать относительный номер строки для присвоения значений, а не для декодирования того, что происходит в каждом столбце? Это фактические имена полей или просто заполнители для примера? Фактические данные - только четыре столбца? Простое решение: для значения в каждом столбце назначения: используйте функцию MID () с начальным символом для этого значения и рассчитайте соответствующую исходную строку численно. fixer1234 8 лет назад 0
Имена полей расположены в том же порядке, но, как я теперь понял после попытки решить эту проблему с помощью OFFSET, некоторые записи содержат не все поля, а их около 200. Я отредактирую Q, чтобы отразить это adolf garlic 8 лет назад 0
Таким образом, вы хотите получить около 200 столбцов? fixer1234 8 лет назад 0
Да, что означает, что список в таблицу в MSWord тоже не будет работать adolf garlic 8 лет назад 0
Один из подходов - создать 3 вспомогательных столбца: проанализировать имя и значение поля и добавить номер записи на основе конечных маркеров (в соответствии с целевым номером записи / строки). Для значения в каждом столбце выполните поиск заголовка столбца в массиве вспомогательных столбцов в пределах соответствующего номера записи. Другой подход: используйте Access. fixer1234 8 лет назад 0
Макрос ** VBA ** приемлем? Gary's Student 8 лет назад 0
Это выглядит все более и более, изобилующее 200-ю ветвью Case case: - / adolf garlic 8 лет назад 0

1 ответ на вопрос

2
Gary's Student

Давайте предположим, что ваш источник данных находится в столбце А из Лист1 :

enter image description here

Сначала поместите заголовки столбцов в Sheet2

Затем запустите этот короткий макрос:

Sub DataReOrganizer() Dim s1 As Worksheet, s2 As Worksheet Dim N As Long, i As Long, K As Long, v As String Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") N = s1.Cells(Rows.Count, "A").End(xlUp).Row K = 2  For i = 1 To N v = s1.Cells(i, "A").Text If v = "endmarker" Then K = K + 1 Else ary = Split(v, ": ") MsgBox ary(1) If ary(0) = "myval" Then s2.Cells(K, 1) = ary(1) If ary(0) = "mydate" Then s2.Cells(K, 2) = Chr(39) & ary(1) If ary(0) = "myname" Then s2.Cells(K, 3) = ary(1) If ary(0) = "mynum" Then s2.Cells(K, 4) = ary(1) End If Next i End Sub

произвести это в Sheet2 :

enter image description here

Chr (39) используется для сохранения вашего формата даты и предотвратить Excel от изменения его в то время.

Оно работает! Но некоторые дополнительные ошибки должны были быть найдены в форме странных скрытых символов. После удаления и исправления сценария (с использованием Excel) для создания других 196 нечетных столбцов он работал нормально. Спасибо adolf garlic 8 лет назад 0
Это действительно отвечает на вопрос, но теперь я понимаю, что одно из полей является многострочным и имеет только идентификатор в первой строке, который мне придется попытаться поймать. Я отправлю ответ, если и когда я выясню решение (не обрабатывая это приводит к строкам от 2 до * n * указанного поля) adolf garlic 8 лет назад 0

Похожие вопросы