Excel: как превратить два вертикальных измерения в вертикальное и горизонтальное

Question

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

  col1 col2  col3                        table1
---------------
01| 1   A  value1A
02| 1   B  value1B
03| 1   Z  value1Z
04| 2   Z  value2Z
05| 3   A  value3A
06| 3   B  value3B
07| 3   Z  value3Z
08| 4   A  value4A
09| 4   B  value4B
10| 4   Z  value4Z
11| 5   Z  value5Z

И я хотел бы получить что-то вроде этого:

      A       B       Z                  table2
 -------------------------
1| value1A value1B value1Z
2|                 value2Z
3| value3A value3B value3Z
4| value4A value4B value4Z
5|                 value5Z

Где формула в ячейке A1 из таблицы2 вычисляет значение A1 через значения, расположенные в ячейках A1 и B1 из таблицы1, и где второй столбец в таблице1 иногда заполнен только одним значением (в этом случае всегда Z). Если не заполнить одно значение, это всегда будет 3 значения (A, B и Z).

Пока что я не нашел ничего удобного.

РЕДАКТИРОВАТЬ: как упомянуто в комментариях ниже, вы также должны знать, что value1Z на самом деле является суммой value1A и value1B.

РЕДАКТИРОВАТЬ 2: Я бы предпочел не использовать сводную таблицу. В моем очень конкретном случае это не очень подходит.

Answer 1

Попробуйте этот короткий макрос:

Sub MAIN2()
    Dim r As Range, v, numb, letr
    Dim i As Long, j As Long

    Columns(1).SpecialCells(2).Copy Cells(2, 4)
    Columns(4).RemoveDuplicates Columns:=1, Header:=xlNo
    Columns(2).SpecialCells(2).Copy Cells(2, 5)
    Columns(5).RemoveDuplicates Columns:=1, Header:=xlNo

    Set r = Columns(5).SpecialCells(2)
    r.Copy
    Cells(1, 5).PasteSpecial Transpose:=True
    r.Clear

    For Each r In Columns(3).SpecialCells(2)
        v = r.Value
        numb = r.Offset(0, -2).Value
        lett = r.Offset(0, -1).Value

        For i = 2 To Rows.Count
            If Cells(i, 4) = numb Then Exit For
        Next i
        For j = 5 To Columns.Count
            If Cells(1, j) = lett Then Exit For
        Next j

        Cells(i, j) = v
    Next r

End Sub

Макросы очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить макрос:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать макрос из Excel:

1. ALT-F8
2. Выберите макрос
3. Нажмите RUN

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Макросы должны быть включены, чтобы это работало!

Answer 2

Вы можете просто использовать сводную таблицу.

Изменить: вот рабочий пример. Не уверены, почему это не соответствует вашим требованиям? https://excel.solutions/wp-content/uploads/2017/11/Guillaume.xlsx

Вы можете легко изменить порядок столбцов в сводных таблицах путем ручной настройки или с помощью пользовательских списков.