Преобразование нескольких строк в один столбец с накоплением в Excel

У меня есть огромные данные о доле рынка различных брендов, которые выглядят так:

1111 2222 3333 4444
5555      7777 8888
9999 0001 0002
0004 0005 0006 0007

Какой код макроса можно использовать для получения вывода:

1111
2222
3333
4444
5555
<emptyCell>
7777
8888
9999
0001
0002
<emptyCell>
0004
0005
0006
0007

Также следует учитывать пустые ячейки.

Также есть ли возможность получить вывод на другом листе?


rows excel transpose vba
person Ankur Chandel    schedule 22.01.2013    source источник
comment
в чем разница между выходными и необработанными данными? Вы должны быть более конкретными в отношении того, как выглядят необработанные данные и как вы хотите, чтобы они выглядели после применения макроса.   -  person hoooman    schedule 22.01.2013
comment
@Ankur - вы должны показать, что вы уже пробовали, чтобы мы не дублировали уже проделанную работу. В идеале вы просто перебираете каждую строку, а затем переходите к следующей. Однако как узнать, что вы в конце ряда?   -  person LittleBobbyTables - Au Revoir    schedule 22.01.2013
comment
Могут ли быть 2 пустые ячейки рядом друг с другом?   -  person hoooman    schedule 22.01.2013
comment
Все строки имеют фиксированную ширину 4?   -  person hoooman    schedule 22.01.2013
comment
stackoverflow.com/questions/14298522/   -  person Hiten004    schedule 22.01.2013

Ответы (3)


arrow_upward
6
arrow_downward

Изменено на INDEX для версии с меньшей нагрузкой на процессор.

в строке 1 любого листа, в который вы хотите скопировать данные:

=INDEX($A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)

скопируйте это, и как только начнут появляться нули, вы окажетесь в конце. (Это единственная проблема - при этом пустые ячейки станут нулевыми. Если вы хотите сохранить и пробелы, вам нужно это:

=ЕСЛИ(ПУСТО(ИНДЕКС($A$1:$D$4,ЦЕЛОЕ((СТРОКА()-1)/4)+1,ОСТАТ(СТРОКА()-1,4)+1)),"",ИНДЕКС( $A$1:$D$4,INT((СТРОКА()-1)/4)+1,MOD(СТРОКА()-1,4)+1))

)

если вы начинаете не с первой строки, измените ROW() на ROW()-X, где X — количество строк вниз от верхнего края (т. е. 1 для строки 2, 2 для строки 3, 799 для строки 800)
Если есть другое количество столбцов, измените 4 на соответствующее число

person SeanC    schedule 22.01.2013
comment
Я собирался ответить так же, но отменил из-за вашего ответа: +1! Единственное изменение, которое я бы сделал: используйте INDEX вместо OFFSET - это даст тот же результат, но энергонезависимый и, следовательно, намного быстрее после первого запуска расчета!!! - person Peter Albert; 23.01.2013
comment
@PeterAlbert, хорошая мысль. Я исправил свою формулу как ИНДЕКС - person SeanC; 23.01.2013
comment
Я не вижу упоминания о том, что это формула массива, и ее нужно вводить с помощью метода Ctrl+Shift+Enter... я ошибаюсь? - person Code Jockey; 23.02.2016
comment
@CodeJockey, это не формула массива. Он использует вычисление текущей строки, чтобы выяснить, откуда взять данные. INDEX позволит вам указать позицию в массиве, как массив vba - person SeanC; 24.02.2016

arrow_upward
2
arrow_downward

Изменено из ответа SeanC (спасибо, приятель), чтобы превратить его в обобщенное использование, чтобы его могли использовать люди с другими размерами диапазона и начальными ячейками:

Замените «$RANGE$» ссылками на ваш диапазон. Замените «$CELL$» ссылкой на первую ячейку выходного столбца:

=INDEX( $RANGE$ ,INT((ROW()-ROW( $CELL$ ))/COLUMNS( $RANGE$ ))+1,MOD(ROW()-ROW( $CELL$ ),COLUMNS( $RANGE$ ))+1)

Перетащите это вниз. Конечно, убедитесь, что и $RANGE$, и $CELL$ зафиксированы знаками '$' как в строке, так и в столбце.

person Rob360    schedule 04.12.2015
comment
(ROW()-ROW($CELL$)) начинается с 0, а ROW() в первом столбце начинается с 1, поэтому должно быть (ROW()-ROW($CELL$) + 1) Редактировать: теперь я обратите внимание, что у него есть -1, который вы удалили, поэтому мой комментарий недействителен - person Jonathan Camilleri; 15.12.2015
comment
Очень хорошо! Я заменил $Range$ и $Cell$ именами диапазонов, теперь это хорошая служебная формула без необходимости настройки. - person Phil; 10.07.2017

arrow_upward
1
arrow_downward

Предполагая, что ваш диапазон A1: D4, вот макрос VBA, который может это сделать (просто поместив значение в столбец E).

Sub RangeToColumn()

Dim varray As Variant
Dim i As Long, j As Long, k As Long

Application.ScreenUpdating = False
k = 1

varray = Range("A1:D4").value
For i = 1 To UBound(varray, 1)
    For j = 1 To UBound(varray, 2)
        Cells(k, 5).value = varray(i, j)
        k = k + 1
    Next
Next

Application.ScreenUpdating = True

End Sub

Вы можете проявить фантазию и использовать объект словаря и перенести массив в столбец, но это более просто, и объект словаря не работает на Mac. Вы также можете использовать «Диапазон («E» & k)» вместо «Ячейки (k, 5)», но Cells() немного быстрее, поскольку не требует конкатенации.

Также обратите внимание, что если отключить обновление экрана, это будет работать намного быстрее.

person aevanko    schedule 24.01.2013