Как сложить столбцы в один длинный

[zarl]

Добрый день!
Задача следующая:
Есть столбцы с данными (в примере - A:D)
Количество столбцов обычно - 6-10.
Длина - вплоть до тысяч строк.
Хочется написать одну формулу, которую, если прокрутить в чистом столбце, собрала бы подряд без пробелов данные из всех столбцов (в примере - вид готового столбца в F).
Буду пользоваться часто в разных книгах, поэтому хочется иметь готовую формулу "в кармане". Сейчас же приходиться копипастом заниматься, чтобы собрать этот суммирующий столбец.
С удовольствием выслушаю идеи по изящному решению.

[Михаил С.]

Вам именно формулу, или можно макрос?

[zarl]

Лучше бы формулу конечно, но если она слишком громоздская получится, то можно и макрос.

[Михаил С.]

Формулой, с дополнительными ячейками

[_Boroda_]

что-то в этом роде

Код Выделить Развернуть

=ИНДЕКС($A$1:$D$13;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ($A$1:$D$13;СТРОКА($A$1:$D$13)+1000000*СТОЛБЕЦ($A$1:$D$13));СТРОКА());1000000);ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ($A$1:$D$13;СТРОКА($A$1:$D$13)+1000000*СТОЛБЕЦ($A$1:$D$13));СТРОКА())/1000000))
или

Код Выделить Развернуть

=СМЕЩ($A$1;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ($A$1:$D$13;СТРОКА($A$1:$D$13)+1000000*СТОЛБЕЦ($A$1:$D$13));СТРОКА());1000000)-1;ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ($A$1:$D$13;СТРОКА($A$1:$D$13)+1000000*СТОЛБЕЦ($A$1:$D$13));СТРОКА())/1000000)-1)
Только потом сразу преобразуйте ее в значения, а то повиснет все

[zarl]

То, что нужно!
Спасибо, Михаил, спасибо Борода!

[ZORRO2005]

_Boroda_,
Здорово ты обыграл смещение по строкам:

Код Выделить Развернуть

ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ($A$1:$D$13;СТРОКА($A$1:$D$13)+1000000*СТОЛБЕЦ($A$1:$D$13));СТРОКА());1000000)
Я голову ломал 3 часа, так и не придумал!
Со смещением по столбцам можно еще такой вариант:

Код Выделить Развернуть

=СУММ(Ч(СТРОКА()>СЧЁТЕСЛИ(СМЕЩ($A$1;;;ЧСТРОК($A$1:$D$13);СТОЛБЕЦ($A$1:$D$13));"<>0")))

Друзья, объясните, почему

Код Выделить Развернуть

=СЧЁТЕСЛИ(СМЕЩ($A$1;;СТОЛБЕЦ(A1:D1)-1;ЧСТРОК(A1:D13));"<>0")
выдает неправильный результат {13;13;13;13} , а

Код Выделить Развернуть

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($A$1;;СТОЛБЕЦ(A1:D1)-1;ЧСТРОК(A1:D13)))
считает как надо {13;6;4;9}

[MCH]

Код Выделить Развернуть

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($A$1;;СТОЛБЕЦ(A1:D1)-1;ЧСТРОК(A1:D13)))
считает как надо {13;6;4;9}

Сергей, массив {13;6;4;9} можно еще так получить:

Код Выделить Развернуть

=МУМНОЖ(ТРАНСП(СТРОКА($A$1:$D$13))^0;ЗНАК($A$1:$D$13))
а что с ним дальше делать пока не знаю

[MCH]

макросом:

Код Выделить Развернуть

Sub www()
Dim x, i&
For Each x In Range("A1:D13").Value
    If x <> "" Then i = i + 1: Cells(i, "O") = x
Next x
End Sub

или так (будет работать быстрее на больших массивах):

Код Выделить Развернуть

Sub www2()
Dim x, i&, arr()
For Each x In Range("A1:D13").Value
    If x <> "" Then
        i = i + 1
        ReDim Preserve arr(1 To i)
        arr(i) = x
    End If
Next x
Range("P1").Resize(UBound(arr)) = Application.Transpose(arr)
End Sub