У меня есть столбец с данными, и нет определенного шаблона, что после 10 или 20 строк я должен повторять новую строку. Поэтому эти решения Index и Offset могут работать только в том случае, если формула может определить начало и конец каждой секции.
Чтобы понять это правильно, у меня есть такая колонка:
A
B
C
D
- (это разрывает раздел)
A
C
E
F
G
- (это разрывает еще одну строку)
B
C
F
G
- (снова разрыв)
Так что для меня было бы нормально, если бы каждый символ "-" начинал новую строку в таблице. Возможно ли сделать это в Excel или Access с помощью формулы или VBA?
Спасибо!
решение1
Использовать:
=FILTERXML("<Group><Element>"&SUBSTITUTE(TEXTJOIN("",FALSE,A1:A15),"-","</Element><Element>")&"</Element></Group>","/Group/Element")
Используется TEXTJOIN()для объединения всех значений в столбце (A1:A15 в формуле. Предполагается, что в качестве конечной ячейки не используется «-», и этого можно добиться, просто выбрав ячейку над такой последней записью в качестве конца диапазона, если всегда появляются конечные символы «-»). Ячейки не пропускаются, даже если они пустые, но если пустые ячейки необходимо пропустить, используйте TRUEдля второго параметра функции.
Затем вы собираетесь превратить TEXTJOIN()массив из одного элемента, разделенного на части символами "-", в ту же строку, но с "" вместо символов "-". Это нужно для того, чтобы сделать строку частично готовой к использованию в функции, которую FILTERXML()вы видите. Обратите внимание, что все замены являются внутренними по отношению к строке, или, другими словами, появляются только внутри нее, а не на каком-либо конце. Если вы хоть немного знакомы с HTML, вы знаете, что форма "</tag name>" закрывает тег, а "" открывает тег.
Затем вы добавляете открывающий тег в начало (добавляете его перед тем, как SUBSTITUTE()происходит ), а закрывающий тег добавляется к концу... Таким образом, у вас есть пара открывающего и закрывающего тегов вокруг каждой из желаемых групп, которые символы "-" используются для разделения.
Наконец, все эти индивидуально помеченные элементы (видите, откуда я взял название тега ""?) необходимо сгруппировать в одну группу (видите, откуда взялся тег?).
Теперь у вас есть строка HTML, которую FILTERXML()можно осмыслить и разбить на части. Она берет эту строку для первого параметра, а для второго параметра вы сообщаете ей, как теги связаны через то, что называется «Путь»… то есть бит «/Группа/Элемент» в конце.
Трудно понять, почему Excel установил эту необходимость, когда такая строка очевидно организована определенным образом, верно? Но это было предназначено для того, чтобы препарировать реальный живой HTML из живых веб-сайтов, а не разбирать "обыденные" строки, и в такой строке HTML могло быть много других тегов, и тогда это не было бы так очевидно! Интересно, однако, что это предполагает, что если вы создали строку с несколькими тегами, вы могли бы заставить Excel брать разные наборы из одной и той же строки, используя разные значения Path.
В любом случае, FILTERXML()любит выдавать свои результаты в вертикальном макете, строки в одном столбце. Я думаю, вы бы предпочли это[да, я вижу в комментариях, что вы явно просите строки], но если нет, если вам нужна горизонтальная компоновка, столбцы в одну строку, просто оберните все это в TRANSPOSE()функцию.
Еще одно интересное применение FILTERXML()заключается в том, что вы можете указать, какой именно элемент такой группы вам нужен, по номеру (1, 2, 3,... и т. д.) И последний по номеру (если он известен) ИЛИ по «last», что может быть удобно.
Он может выполнять более широкий спектр операций со строками, если у вас есть работающий способ вставки внутренних пар тегов () в нужных местах.