Переместить таблицу в стиле матрицы в 3 столбца в Excel

У меня есть таблица в стиле матрицы в excel, где B1: Z1 – заголовки столбцов, а A2: A99 – заголовки строк. Я хотел бы преобразовать эту таблицу в таблицу с тремя столбцами (заголовок столбца, заголовок строки, значение ячейки). Неважно, в каком порядке находится новая таблица.

ABCDABCABC 1 H1 H2 H3 1 H1 R1 V1 1 H1 R1 V1 2 R1 V1 V2 V3 => 2 H1 R2 V4 or 2 H2 R1 V2 3 R2 V4 V5 V6 3 H1 R3 V7 3 H3 R1 V3 4 R3 V7 V8 V9 4 H2 R1 V2 4 H1 R2 V4 5 H2 R2 V5 5 H2 R2 V5 6 H2 R3 V8 6 H3 R2 V6 7 H3 R1 V3 7 H1 R3 V7 8 H3 R2 V6 8 H2 R3 V8 9 H3 R3 V9 9 H3 R3 V8 

Я играл с функцией OFFSET для создания всей таблицы, но мне кажется, что требуется сочетание TRANSPOSE и V / HLOOKUP.

благодаря

РЕДАКТИРОВАТЬ

Мне удалось найти правильные формулы. Если данные в Sheet1, как в моем примере выше, формулы идут в Sheet2:

 [A1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"") [B1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"") [C1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"") 

Формулы ограничены B1: Z1 для заголовков и A2: A99 для строк (при необходимости их можно увеличить до максимума). Формула COUNTA () возвращает количество ячеек, которые действительно имеют значения, что ограничивает количество строк, возвращаемых в строки заголовков *. В противном случае формулы могли бы продолжать бесконечность из-за функции MOD.

2 Solutions collect form web for “Переместить таблицу в стиле матрицы в 3 столбца в Excel”

Мне нравится этот вопрос 🙂

Попробуйте эти функции. Вставьте их в h0, r0, v0 и перетащите их вниз

  hrv 0 (a) (b) (c) 1 | | | | | | 2 \ / \ / \ / 3 vvv 

где:
H = метка заголовка
R = ярлык строки
V = метка значения

(A) = OFFSET ($ A $ 1, INT (H2 / 99) + 1, MOD (H2,99) +1,1,1)
(B) = OFFSET ($ A $ 1, INT (H2 / 99) +1,0,1,1)
(C) = OFFSET ($ A $ 1,0, MOD (H2,99) +1,1,)

Где H2 – столбец со значениями: 0,1,2,3 …

Несколько недель назад я увидел немного более обобщенную версию решения, предоставленного @Dick Kusleika в StackOverflow: Преобразование строки с столбцами данных в столбец с несколькими строками в Excel 2007 . Кажется, что у него нет ссылок на отдельные строки (вместо этого используется ROW() ), но при этом требуется, чтобы количество столбцов было жестко закодировано.

  • Как получить или скопировать фоновое изображение в Excel?
  • Excel - отобразить текст, отличный от фактического значения?
  • Сравнить ячейки и совпадающие цвета
  • Автообновление столбца в Excel
  • Какой код пользовательского формата отображал бы проценты, как если бы он был несколько на 1000
  • Поверните одну строку в несколько строк в Excel
  • Сделать ссылку в двух таблицах Excel, чтобы автоматизировать вычисление
  • Как отделить список, разделенный запятой, на два столбца в Excel?
  • Курсор и выбор невидимы, когда фокус теряется
  • Excel: группировка и сортировка строк
  • Как легко изменить порядок строк в excel с помощью перетаскивания или перемещения или перемещения вниз?
  • Autofill превосходит с почасовой разбивкой в ​​течение длительного времени
  • Interesting Posts

    Должен ли я удалить группу «Все» из моих общих папок?

    Автоматическое распределение полосы пропускания на основе приложений в Windows?

    Могут работать внутренние ноутбуки с максимальной температурой? Варианты охлаждения?

    Что является хорошей альтернативой iDVD OneStep DVD для копирования видео с видеокамеры на DVD (в Mac OS X)?

    Не удается получить доступ к ресурсам LAN при подключении через беспроводную сеть

    Переключение материнской платы без новой установки?

    Установка пакета (xsane) без зависимостей

    Для чего используется домен «safebrowsing-cache.google.com»?

    Команда перед каждой командой bash

    Существует ли версия ОС Windows, которая позволит загрузочному диску превышать 2 ТБ?

    Окна не могут запустить ошибку 0x490

    Radeon HD 3670 на ноутбуке Dell XPS16. Возможны ли 3 монитора?

    Что такое Stereo Mix, который предполагается использовать в Windows?

    Как получить подсветку синтаксиса в TextMate 2

    Как безопасно установить дистрибутив GNU / Linux на ноутбуке с поддержкой UEFI Samsung

    Давайте будем гением компьютера.