Объединить все ячейки, которые соответствуют критериям (Excel)

Question

У меня есть электронная таблица, подобная следующей:

Ожидаемый результат - все ячейки, начиная с конкретного текста, объединенного в порядке их появления.

Форматирование, как на изображении, не является обязательным, но предпочтительным. Тем не менее, число (1, 2, 3) перед каждой частью довольно важно.

Я пытался использовать SUMIF и CONCAT для обозначения радужных таблиц и многошаговых процессов, но все они в итоге вышли из-под контроля, медленно и ненадежно.

Если возможно, я бы хотел сделать это без макросов /UDF, поскольку мой системный администратор не любит их, но если нет другого способа, который подходит.

Любая помощь приветствуется!

Answer 1

Я предлагаю решение, основанное на небольшом количестве кода VBA и функции TEXTJOIN в Excel 2016. Обратите внимание, что это будет работать в Excel 2016 и выше, но не будет работать на более низких версиях. В этом случае вам придется создать UDF для TEXTJOIN. Я имею в виду ваш тег Excel 2016.

В этом примере пример данных находится в ячейках E4:E15.

Нажмите ALT + F11, чтобы открыть VBA Editor. Вставьте модуль из меню вставки и вставьте в него следующий код.

Function GetString(a1 As String) As String

Dim array1 As Variant
Dim mystring As String

array1 = Split(a1, "|")

For i = LBound(array1) To UBound(array1)

    mystring = mystring & i + 1 & "." & array1(i) & " "

Next i

GetString = mystring

End Function

Сохраните файл Excel как.Рабочий лист Excel с поддержкой макросов XLSM.

Теперь, например, в F4 поместите следующую формулу и нажмите CTRL + SHIFT + ВВОД, чтобы создать формулу массива. Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива.

=GetString(TEXTJOIN("|",TRUE,IF(LEFT(E4:E14,3)="fob",RIGHT(E4:E14,LEN(E4:E14)-LEN("fob")),"")))

Теперь вы получаете свою строку, как пожелаете.

Ограничение - обратите внимание, что здесь я использую символ трубы "|" в качестве разделителя. Поэтому в редких случаях, если в самом тексте есть канал, результаты могут быть не такими, как ожидалось. В этом случае используйте один из тех символов, которые можно получить с помощью цифровой клавиатуры ALT +, например ALT + 789, например, §

---

В случае, если вы предпочитаете чистый подход к формуле Excel, я предлагаю следующее решение на основе вспомогательной колонки.

В этом примере пример данных находится в ячейках B3:B13. Теперь в C3 поместите следующую формулу и нажмите CTRL + SHIFT + ENTER, чтобы создать формулу массива. Теперь перетащите его вниз до предполагаемых рядов.

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(LEFT($B$3:$B$13,3)="fob",ROW($B$3:$B$13)-2,""),ROW(A1))),"") 

Обратите внимание на -2 здесь. Это важно, он устанавливает значение индекса, начиная с 1, на основе относительной строки, в которой начинается ваш список.

Эта формула должна создать заразительный список необходимых строк из вашего исходного столбца.

Теперь в D3 поместите следующую формулу и нажмите CTRL + SHIFT + ENTER на панели формул, чтобы создать формулу Array.

=TEXTJOIN(",",TRUE,IF(LEN(C3:C13)=0,"",ROW(C3:C13)-2&"."&RIGHT(C3:C13,LEN(C3:C13)-LEN("fob")))) 

Теперь вы должны получить нужную строку в D3. Смотрите скриншот ниже.