Новости:

Из правил форума: Тема должна отражать суть вопроса, топики типа "help please" будут удаляться!

Главное меню

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

Автор zarl, 20.04.2012, 12:07

« назад - далее »

zarl

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

Михаил С.

Вам именно формулу, или можно макрос?
Отдельное спасибо можно на QiWi-кошелек 909-771-53-87 или ЯД 41001136675053

zarl

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

Михаил С.

#3
Формулой, с дополнительными ячейками
Отдельное спасибо можно на QiWi-кошелек 909-771-53-87 или ЯД 41001136675053

_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)
Только потом сразу преобразуйте ее в значения, а то повиснет все
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

zarl

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

ZORRO2005

#6
_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}
Яндекс-деньги: 410011658492153

MCH

Цитата: ZORRO2005 от 20.04.2012, 17:22
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(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