Элегантный способ импортировать повторяющиеся данные в MS-Excel

Question

Проблема: Текстовый файл, содержащий повторяющиеся патенты данных:

Object:1  
value1:abc  
value2:234  
value3:xyz  
value4:date  

Object:2  
value1:def  
value2:567   
value3:asd  
value4:date  

Object:3  
.  
.  

Так что.
Я хотел бы импортировать его в Excel как таблицу, которая выглядит примерно так:

Object  value1 Value2 Value3 Value4  
  1         abc      234   xyz    date  
  2         def      567   asd    date  
.  
.  

Я нашел грязное решение с помощью функции смещения, но у меня есть ощущение, что есть более элегантное решение?

Answer 1

Вот более надежный вариант, я не уверен, что он более элегантный.

1. разбить ваши данные на столбцы (данные - текст в столбцы)
2. добавить два вычисляемых столбца:
   • объект №: =IF(A2="Object",B2,C1)
   • ID: =A2&"_"&C2
3. создать скелет таблицы назначения с заголовками строк и столбцов
4. введите формулу и заполните и поперек =INDEX($B:$B,MATCH(G$1&"_"&$F2,$D:$D,0))

Ключевым моментом здесь является правильное использование абсолютных / относительных ссылок

Answer 2

Ответ Мате Юхаса очень убедительный. Если в ваших данных есть неточности, как, например, некоторые недостающие элементы, его решение все равно получит значения в нужном месте. Если ваши данные не являются нетронутыми, было бы неплохо использовать его решение.

Если вы знаете, что ваши данные чистые (ничего не пропало, все надежно следует шаблону), вы можете воспользоваться более простым подходом, который использует данные «как есть», не требует разделения данных или вспомогательных столбцов. Этот подход основан на прямых ссылках на расположение ячеек данных, поэтому данные должны следовать шаблону и быть там, где они должны быть.

Он основан на двух формулах, объединенных в одну. Первый извлекает значение из любой записи:

=MID(A1,FIND(":",A1)+1,LEN(A1))

Это ищет двоеточие и берет все справа от него.

Вторая формула связывает местоположение в вашей матрице результатов с ячейкой исходных данных:

=INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1)

INDIRECT создает ссылку на ячейку. Формула работает с группами из шести строк. Первая ячейка в матрице результатов (C2) указывает на первую ячейку данных (A1). Каждый столбец справа перемещается вниз по строке данных. Каждая строка в матрице результатов перемещается в следующую группу из шести строк.

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

• Замените $C$2:C2 начальным местоположением матрицы результатов, соблюдая $ s для абсолютной адресации.
• Замените "A" на столбец, в котором находятся ваши исходные данные.
• +1 в конце ROWS($C$2:C2)-1)*6+1 - номер строки первой строки данных. Мой пример начинается в строке 1.

Фактическая формула заменяет вторую формулу, которая дает местоположение ячейки данных, для A1 в первой формуле. A1 появляется три раза, поэтому формула становится немного длиннее:

=MID(INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1),FIND(":",INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1))+1,LEN(INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1)))

Вы можете сократить формулу, заменив последнюю из трех ссылок на A1. Последний, LEN(A1) - это просто предоставление функции MID количества символов для извлечения. Использование LEN(A1) гарантирует, что будет достаточно символов. Вы можете заменить это произвольным числом, которое больше любого значения, которое вы увидите (MID просто не хватает символов для извлечения). Так, например, если вы используете 99 , первая формула будет:

=MID(A1,FIND(":",A1)+1,99)

И объединенная формула будет:

=MID(INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1),FIND(":",INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1))+1,99)

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

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