После долгих попыток я нашел кое-что, что работает, хотя он использует два рабочих столбца (которые, очевидно, будут скрыты до использования листа).
Я проверил в Excel 2007, который показывает 0 вместо пустой ячейки, когда index() или indirect() возвращает пустую ячейку: я мог бы удалить их с условным форматированием, но я оставил их, чтобы не затенять, как формулы работают:-
Я использовал ваши тестовые данные, поэтому формула, скопированная с D5 :
=INDEX($A$5:$B$8,MOD(ROW()-ROW($A$5),ROWS($A$5:$B$8))+1,INT((ROW()-ROW($A$5))/ROWS($A$5:$B$8))+1)
Столбец D теперь используется в качестве рабочего столбца, и создается другой рабочий столбец F , в котором F5 содержит:
=MATCH("*",$D$5:$D$12,0)+ROW()-1
и скопировал с F6:
=MATCH("*",INDIRECT(ADDRESS(F5+1,4,1)&":$D$12"),0)+F5
Важными моментами здесь являются:
- Подстановочные знаки можно использовать, когда третий параметр
MATCH() равен нулю (неупорядочено).
"*" соответствует чему угодно, но не 0 возвращенному INDEX() для пустой ячейки.
Наконец, H5 скопировал это:
=IFERROR(INDIRECT("$D"&F5),"")
Здесь IFERROR() используется для перехвата записей #N/A в столбце F после последней найденной записи.
Скрыв рабочие столбцы, вы получаете то, что хотели:-
Обратите внимание, что во избежание прерывания последовательности активных столбцов рабочие столбцы могут находиться за пределами области основного листа (например, Y и Z) или на отдельном листе, хотя последний еще больше усложнит формулы с ссылками на листы.
Кроме того, может быть возможно использовать только один рабочий столбец, но формулы будут чрезвычайно усложнены, что сделает поддержку очень сложной.
Также обратите внимание, что ваши формулы могут рассматриваться как излишне сложные, например, ROW($A$201) всегда равно 201, а ROWS($A$101:$F$200) всегда равно 100; в обоих случаях это происходит потому, что была использована абсолютная адресация - ROW() и ROWS() становятся полезными при копировании ячеек, использующих относительную адресацию.
