Мне нужно преобразовать матрицу Excel FIRST в таблицу LATER:
ПЕРВЫЙ:
P1 P2 P3 P4
F1 X
F2 X X
F3 X X
F4 X X
ПОЗЖЕ:
F P VALUE
F1 P1 X
F1 P2
F1 P3
F1 P4
F2 P1 X
F2 P2 X
F2 P3
F2 P4
F3 P1
F3 P2 X
F3 P3
F3 P4 X
F4 P1
F4 P2 X
F4 P3 X
F4 P4
Для Excel 2003: активируйте любую ячейку в сводной таблице и выберите Данные - Сводная таблица и Отчет сводной диаграммы:
Для более поздних версий откройте Мастер с помощью Alt + D, P.
В Excel для Mac 2011 это ⌘ + Alt + P (См. здесь).
Выберите Несколько диапазонов консолидации и нажмите Далее.
На «Шаге 2а из 3» выберите Я создам поля страницы и нажмите Далее.
На «Шаге 2b из 3» укажите диапазон сводной таблицы в поле Диапазон (A1: E5 для данных примера) и нажмите Добавить, затем Далее .
На «Шаге 3 из 3» выберите место для сводной таблицы (должен использоваться существующий лист, так как PT требуется только временно):
Нажмите Finish, чтобы создать сводную таблицу:
Разверните (т.е. дважды щелкните) на пересечении общих итогов (здесь ячейка V7 или 7):
Теперь ПТ можно удалить.
На ту же тему есть видео на Запустите Excel, которое я считаю отличным качеством.
Другой способ развернуть данные без использования VBA - использовать PowerQuery, бесплатную надстройку для Excel 2010 и более поздних версий, доступную здесь: http://www.microsoft.com/en-us/download/details.aspx?id=39379
Установите и активируйте надстройку Power Query. Затем выполните следующие действия:
Добавьте метку столбца к источнику данных и превратите его в таблицу Excel с помощью Insert> Table или Ctrl - T.
Выберите любую ячейку в таблице и на ленте Power Query нажмите «Из таблицы».
Это откроет таблицу в окне редактора Power Query.
Щелкните заголовок первого столбца, чтобы выбрать его. Затем на ленте Преобразование нажмите раскрывающееся меню Отменить сводку столбцов и выберите Отменить сводку других столбцов.
Для версий Power Query, в которых нет команды Отменить развертку других столбцов, выберите все столбцы, кроме первого (с помощью щелчка по заголовкам столбцов, удерживая нажатой клавишу «Shift»), и используйте команду Развернуть команда.
В результате получился плоский стол. Нажмите Закрыть и загрузить на главной ленте, и данные будут загружены на новый лист Excel.
Теперь о хорошем. Добавьте данные в исходную таблицу, например
Щелкните лист с таблицей результатов Power Query и на ленте данных нажмите Обновить все. Вы увидите что-то вроде:
Power Query - это не просто одноразовое преобразование. Он воспроизводится и может быть связан с динамически изменяющимися данными.
Все решения на данный момент включают VBA, PowerQuery и т. Д., Которые хороши, но являются «разовыми» событиями. Чтобы сделать его более динамичным, рассмотрите возможность использования INDEX (MATCH (...)). Это позволит динамически обновлять таблицу.
Добавление функции LET и динамических массивов позволяет использовать это решение, отличное от VBA.
=LET(data,B2:E5,
dataRows,ROWS(data),
dataCols,COLUMNS(data),
rowHeaders,OFFSET(data,0,-1,dataRows,1),
colHeaders,OFFSET(data,-1,0,1,dataCols),
dataIndex,SEQUENCE(dataRows*dataCols),
rowIndex,MOD(dataIndex-1,dataRows)+1,
colIndex,INT((dataIndex-1)/dataRows)+1,
dataColumn, IF(INDEX(data,rowIndex,colIndex)="","",INDEX(data,rowIndex,colIndex)),
unfiltered, CHOOSE({1,2,3},INDEX(rowHeaders,rowIndex),INDEX(colHeaders,colIndex), dataColumn),
filtered, FILTER(unfiltered, dataColumn<>""),
unfiltered)
Это покажет все элементы, включая те, с пустыми данными. Чтобы исключить пробелы, измените последний параметр на отфильтрованный.
Еще одно, что нужно добавить в BoK. Для этого требуется Excel 365. Он отменяет поворот B1: E5 на A1: A5.
=LET( unPivMatrix, B1:E5,
byMatrix, A1:A5,
upC, COLUMNS( unPivMatrix ),
byC, COLUMNS( byMatrix ),
dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
dmxSeq, SEQUENCE( dmxR ) + 1,
upCells, dmxR * upC,
upSeq, SEQUENCE( upCells,, 0 ),
upHdr, INDEX( INDEX( unPivMatrix, 1, ), 1, SEQUENCE( upC ) ),
upBody, INDEX( unPivMatrix, dmxSeq, SEQUENCE( 1, upC ) ),
byBody, INDEX( byMatrix, dmxSeq, SEQUENCE( 1, byC ) ),
attr, INDEX( upHdr, MOD( upSeq, upC ) + 1 ),
mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
demux, IFERROR( INDEX(
IFERROR( INDEX( byBody,
IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
SEQUENCE( 1, byC + 1 ) ),
attr ),
upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
mux ),
FILTER(demux, mux<>"")
)
NB: byMatrix может быть диапазоном с несколькими столбцами, и он будет копировать значения строк столбцов. например у вас может быть byMatrix из A1: C5 и unPivMatrix из D1: H5, и он будет реплицировать значения столбца A2: C5 (игнорируя A1).
