Преобразуйте матрицу в таблицу из трех столбцов ( "обратный поворот", "разворот", "сгладить", "нормализовать" )

Мне нужно преобразовать матрицу 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

Ответы

Ответ 1

Для "обратного поворота", "непривита" или "сглаживания":

  • Для Excel 2003: активируйте любую ячейку в сводной таблице и выберите Data - PivotTable и PivotChart Report:

    SO20541905 first example

Для более поздних версий обращайтесь к Мастере с помощью Alt + D, P.

В Excel для Mac 2011 это ⌘ + Alt + P (Смотрите здесь).

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

    SO20541905 second example

  2. В "Шаг 2a из 3" выберите "Я создам поля страницы" и нажмите Next.

    SO20541905 third example

  3. В "Шаге 2b из 3" укажите диапазон сводной таблицы в поле Диапазон (A1: E5 для данных примера) и нажмите Add, затем Next.

    SO20541905 fourth example

  4. В "Шаг 3 из 3" выберите местоположение для сводной таблицы (существующий лист должен служить, поскольку PT требуется только временно):

    SO20541905 fifth example

  5. Нажмите Finish, чтобы создать сводную таблицу:

    SO20541905 sixth example

  6. Разверните (т.е. дважды щелкните) на пересечении грандиозных итогов (здесь Cell V7 или 7):

    SO20541905 seventh example

  7. Теперь PT можно удалить.

  8. Полученная таблица может быть преобразована в обычный массив ячеек, выбрав Таблица в Быстром меню (щелкните правой кнопкой мыши в таблице) и Преобразовать в диапазон.

Есть видео по тому же вопросу в Запустите Excel, который я считаю отличным качеством.

Ответ 2

Другим способом неавторизованных данных без использования VBA является PowerQuery, бесплатная надстройка для Excel 2010 и выше, доступная здесь: http://www.microsoft.com/en-us/download/details.aspx?id=39379

Установите и активируйте надстройку Power Query. Затем выполните следующие действия:

Добавьте метку столбца к источнику данных и переведите его в таблицу Excel через Insert > Table или Ctrl - T.

enter image description here

Выберите любую ячейку в таблице, а на ленте Power Query нажмите "Из таблицы".

enter image description here

Это откроет таблицу в окне редактора запросов энергии.

enter image description here

Нажмите заголовок столбца первого столбца, чтобы выбрать его. Затем на ленте Трансформация выберите раскрывающийся список Unpivot Columns и выберите Unpivot других столбцов.

Для версий Power Query, которые не имеют команды Unpivot other columns, выберите все столбцы, кроме первого (с помощью Shift-щелчка на заголовках столбцов) и используйте Unpivot.

enter image description here

В результате получается плоская таблица. Нажмите Закрыть и Загрузить на ленте Home, и данные будут загружены на новый лист Excel.

enter image description here

Теперь к хорошей части. Добавьте некоторые данные в исходную таблицу, например

enter image description here

Щелкните по листу с помощью таблицы результатов Power Query, а на ленте данных нажмите Обновить все. Вы увидите что-то вроде:

enter image description here

Power Query - это не просто одноразовое преобразование. Он повторяется и может быть связан с динамически изменяющимися данными.