Как перенести данные в столбцах в строки?

Question

Как перенести данные в столбцах в строки?

Если данные:

Joe  
some city  
some state  
Bob  
no city  
no state  

и я использую Paste Special » Транспонировать, я получаю

Joe some city some state Bob no city no state  

Что я хочу это:

Joe some city some state  
Bob no city no state  

Я не знаю VBA.

Answer 1

Мы собираемся научить вас немного VBA этим утром, и вы будете чувствовать себя хорошо об этом! Наденьте свои ракетные ботинки и приготовьтесь к отплытию!

Этот макрос сделает это за вас, если он все в столбце A

Нажмите Alt + F11, чтобы вызвать редактор VBA. Щелкните правой кнопкой мыши на insert - module . Вставьте приведенный ниже код в модуль и перейдите к debug - compile project и нажмите F5, чтобы запустить его. Легко свежий.

Sub transposedelete()

Dim rownum As Long
Dim colnum As Long
Dim data, result
colnum = 1

Application.ScreenUpdating = False

'check if the data on the sheet start where the code expects it
If Range("a1") = "" Then Exit Sub


    ' define data range
    With Range("a1", Cells(Rows.Count, "a").End(xlUp)).Resize(, 2)
    data = .Value
    numrows = UBound(data)

    'loop it
    For rownum = 2 To numrows

    Range((Cells(rownum, colnum)), (Cells(rownum + 1, colnum))).Copy
    ' transpose
    Cells(rownum - 1, colnum + 1).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    'delete rows (this could be cleaner)
    Rows(rownum).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows(rownum).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp


    Next

    End With

    Application.ScreenUpdating = True

End Sub

Легко как 1, 2, 3:

1.

2.

3.

Видите, теперь это было не сложно и не страшно, не так ли? И теперь у вас есть трюк в рукаве! Учиться очень весело!

Answer 2

обновление: Excel 2007 и более поздние версии действительно имеют функцию от одного столбца до нескольких строк. Читайте об этом здесь. По той причине, что функция транспонирования не делает то, что вы хотите, см. Ниже:

Чтобы транспонирование работало так, как вы хотите, ваши исходные данные должны быть упорядочены следующим образом:

Joe        Bob
city1      city2
state1     state2

Транспозиция изменяет строки на столбцы и наоборот. То, как ваша информация отформатирована в вашем примере, это все один длинный столбец, и Excel изменяет его на один длинный ряд. Он не может знать, что вы хотите, чтобы колонка с шестью элементами рассматривалась как два блока по три элемента в каждом.

Answer 3

Этот ответ использует данные из Как преобразовать данные формата строки в формат столбца?, который был закрыт как дубликат этого вопроса.  Таким образом, этот ответ действительно отвечает на этот вопрос с тривиальными модификациями (т. Е. Игнорировать части этого ответа, которые говорят о метках данных.

---

Прежде всего, я буду считать , что ваши данные , что вы , кажется, говорил:

                                               

Создайте новый лист и введите заголовки столбцов, которые соответствуют вашим трем меткам данных:

                                               

(Вы можете сделать это, скопировав ячейки A1:A3 из Sheet1 и транспонировав их, но всего для трех, вероятно, проще просто ввести их вручную.)  Теперь обратите внимание, что каждый раз, когда вы перемещаете одну ячейку (т.е. один столбец) вправо на Sheet2 , это соответствует перемещению вниз на одну строку на Sheet1 .  И каждый раз, когда вы перемещаете одну строку вниз на Sheet2 , это соответствует перемещению вниз на три строки на Sheet1 .  Итак, предположим, что =ROW()*3 + COLUMN() может быть полезной формулой, и введите это в ячейки A2:C4:

                                               

Обратите внимание, что значения развиваются так, как мы хотим.  Но мы хотим Sheet2!A2 чтобы получить его значение от Sheet1!A1 , поэтому давайте вычтем 7 и используем результат в качестве смещения. Войти

=OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0)

в ячейки A2:C4:

                                               

это данные, которые вы хотите, но с прикрепленными метками данных.  Итак, чтобы это исправить, измените формулу на

=SUBSTITUTE(OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0), A$1 & ": ", "")

убрать метку (из строки 1 вне текущего столбца) из данных ячейки:

                                               

а затем вы можете копировать и вставлять значения.