Данные Excel в строках и столбцах для переформатирования

Question

У меня есть данные в Excel в шахматном порядке. В крайнем левом столбце указан идентификатор клиента, а сведения о нем находятся в разных строках в разных столбцах. Мне нужно переставить данные в одну строку для каждого клиента. Это в том же формате для всех других клиентов.

CustomerID FirstName LastName Education Training TrainingYear City State Zip
1          John       C       Masters
1                                      SAP
1                                                2017
1                                                             LA
1                                                                   CA
1                                                                        91433
2          Max        G       Bachelors
2                                       Oracle
2                                               2015
2                                                             NR
2                                                                  CA
2                                                                        91234

Answer 1

Использовать этот

=INDIRECT(CONCATENATE("A", ROW()*6-10))

Изменение "A" на соответствующий столбец (B для имени, C для фамилии, D для образования и т.д.) И изменение «-10» на соответствующий столбец (-10 для образования, -9 для обучения, -8 для обучения года , ..., -5 для Zip)

---

Для тренировок

=INDIRECT(CONCATENATE("E", ROW()*6-9))

Для колонки Zip

=INDIRECT(CONCATENATE("H", ROW()*6-6))

---

Кредиты для fixer1234 комментарий.

---

Этот интуитивно понятный метод НЕ работает (или, по крайней мере, не так удобен, потому что автозаполнение не распознает простой шаблон из 6 шагов):

CustomerID | Firstname | Lastname | Education | Training | TrainingYear | City | State | Zip
----------------------------------------------------------------------------------------------
=A2        | =B2       | =C2      | =D2       | =E3      | =F4          | =G5  | =H6   | =I7
=A8        | =B8       | =C8      | =D8       | =E9      | =F10         | =G11 | =H12  | =I13

Answer 2

Это можно сделать с помощью некоторых формул, которые вычисляют, какие ячейки использовать на основе строки, как это делает ответ Ади Нугрохо. На самом деле вы можете сделать еще один шаг и использовать единую формулу для всех ячеек.

1: Раствор с одной формулой

Вот один пример (есть несколько подходов, которые можно использовать). Скопируйте заголовки столбцов на другой лист. Затем поместите эту формулу в A2:

=OFFSET(Sheet1!$A$2,ROW()*6-12+(COLUMN()>4)*(COLUMN()-4),COLUMN()-1)

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

Объяснение:

Функция OFFSET привязывает вашу новую таблицу к первой ячейке исходных данных, а затем определяет каждую ячейку как смещение оттуда.

ROW()*6-12 преобразует исходный шаблон из шести строк в один интервал строки.

(COLUMN()>4)*(COLUMN()-4) увеличивает ссылку на строку для столбцов E и далее. Первые скобки содержат логический тест на то, находитесь ли вы за пределами 4-го столбца, возвращая 1 если истина, и 0 если ложь. Это умножается на номер столбца, превышающий 4, чтобы отразить шаблон данных (столбец 5 отбрасывает одну строку).

COLUMN()-1 устанавливает фактический столбец в смещение (столбец 1 вашего результата равен 0 смещению от столбца 1 данных).

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

---

2: Нет мозговых клеток Требуется решение

Как альтернатива, есть действительно простое решение, которое не требует каких-либо необычных функций.

Начните с ваших данных в Sheet1:

• Скопируйте заголовки столбцов на новый лист.

• Для первой строки введите ссылку на первую ячейку (в A2) на данные в листе 1:

  = Лист1!A2

Скопируйте его в B2, C2 и D2. Затем скопируйте его в E3, F4, G5, H6 и I7. Это автоматически укажет клетки в нужное место. Затем перетащите (переместите) ячейки в столбцах E - I до строки 2, и формулы будут сохранены. Итак, строка 2 будет содержать:

A2: =sheet1!A2
B2: =sheet1!B2
C2: =sheet1!C2
D2: =sheet1!D2
E2: =sheet1!E3
F2: =sheet1!F4
G2: =sheet1!G5
H2: =sheet1!H6
I2: =sheet1!I7

• Выберите значения строки 2 и скопируйте их вниз на страницу для столько строк, сколько у вас есть данных на листе 1. Это будет выглядеть так:

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

Мы удаляем лишние строки еще одним шагом.

• Выделите всю таблицу и включите фильтрацию.
• Фильтруйте по столбцу B и выберите, чтобы показывать только пустые ячейки (или ноль, если это то, что вы получили). Фильтр будет скрывать строки с вашими значениями и выглядеть так (или как строки с нулями, за исключением столбца A); т.е. только лишние строки:

• Выберите все видимые строки данных и удалите строки через контекстное меню:

• Теперь отключите фильтрацию, и вы получите свой результат:

Answer 3

Мне нужно переставить данные в одну строку для каждого клиента. Это в том же формате для всех других клиентов.

Если вам удобно работать с макросами. Вот простой вложенный цикл:

Sub moveAndCleanup()
    Call move
    Call cleanup
End Sub

Sub move()
    Dim row, col, i, j As Integer
    row = 2
    col = 4
    j = 6
        Do While Cells(row, 1).Value <> ""
            For i = 1 To 5
                Cells(row, col + i).Value = Cells(row + i, col + i).Value
            Next i
            row = row + j
        Loop
End Sub

Sub cleanup()
    Dim row, col, i, j As Integer
    row = 2
    col = 4
    j = 6
        Do While Cells(row, 1).Value <> ""
            For i = 1 To 5
                Cells(row + i, col + i).Value = ""
                Cells(row + i, 1).Value = ""
            Next i
            row = row + j
        Loop
End Sub

Но не забудьте скопировать свои данные или сначала скопировать книгу, потому что вы НЕ МОЖЕТЕ ОТМЕНИТЬ Макрос.