Посмотрите на этот пример с одним запасным столбцом и тремя формулами массива.
Диапазон A1:B100
заполнен случайными строками, с пустыми ячейками, диапазон C1:C100
- просто некоторые числа (также могут быть строки).
Диапазон H1:H100
используется в качестве вспомогательного столбца, который фактически отфильтровывает и накапливает номера строк с непустыми ячейками в A
и B
H1
: формула массива для поиска номера первой строки с непустыми ячейками в столбцах A
и B
:
=MATCH(1,SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))),0)
H2
: еще одна формула массива для поиска всех остальных номеров строк с непустыми ячейками в столбцах A
и B
:
=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",MATCH(1,SIGN(LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))),0)+H1)
Формула довольно длинная, поэтому на всякий случай, вот она, отформатированная, чтобы показать структуру:
IF(
ROW()>
SUM(
SIGN(
LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))
)
)
,""
,MATCH(1,
SIGN(
LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))
*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))
)
,0
)+H1
)
Введите последний тип формулы массива в D1
:
=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",INDEX($A$1:$C$100,$H1,COLUMN()-COLUMN($D$1)+1))
скопируйте ячейку в E1
, F1
; выберите и скопируйте диапазон D1:F1
; выберите диапазон D2:F100
и вставьте скопированные ячейки.
Формулы могут быть намного короче за счет большего количества вспомогательных столбцов.
Обязательное примечание: чтобы ввести формулу массива, нажмите Ctrl+Shift+Enter вместо просто Enter для скалярной формулы).