Я предлагаю решение, при котором вам просто нужно оставить одну пустую ячейку в верхней части ваших данных, как показано в вашем примере.
Если у вас есть Office 365, вы можете легко использовать функцию TEXTJOIN. Однако большинство версий Excel не имеют этой встроенной функции, и вам может потребоваться написать код в VBA самостоятельно. Вы можете свободно найти версию VBA в Интернете, например, одна такая ссылка здесь.
Нажмите ALT + F11 для доступа к VBA Editor. Вставьте модуль и вставьте в него приведенный ниже код.
Function TEXTJOIN1(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN1 = Left(result, Len(result) - Len(delimiter))
End Function
Это создает пользовательскую пользовательскую функцию VBA с именем TEXTJOIN1. Он принимает 3 параметра: Разделитель, Игнорировать пустой флаг и диапазон.
В этом примере пример данных находится в ячейках B2:B14. B1 намеренно оставлено пустым.
Поместите следующую формулу в C2 и нажмите CTRL + SHIFT + ENTER на панели формул, чтобы создать формулу массива. Теперь формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива, и перетащить ее вниз по длине столбца.
=IF(ISBLANK(B2),TEXTJOIN1(",",FALSE,OFFSET(B2,-(ROW()-MAX(IF(ISBLANK($B$1:B1),ROW($B$1:B1),0))-1),0):OFFSET(B2,-1,0)),"")
Обновить:
Решение выше предполагает, что у вас есть только один пробел между наборами данных. Однако, если у вас их больше одного, и если вы хотите, чтобы формула все еще работала, используйте вместо нее следующую формулу массива.
=IF(AND(ISBLANK(B2),NOT(ISBLANK(OFFSET(B2,-1,0)))),TEXTJOIN1(",",TRUE,OFFSET(B2,-(ROW()-MAX(IF(ISBLANK($B$1:B1),ROW($B$1:B1),0))-1),0):OFFSET(B2,-1,0)),"")