Переместить таблицу в стиле матрицы в 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.

  • Excel: как внутренне хранить числовое значение в ячейке, но сделать ее по-разному (например, алфавитные имена)?
  • Разделить информацию
  • Как я могу отобразить URL-адрес в качестве изображения в ячейке excel?
  • Прекратите открывать новую книгу при запуске Excel
  • Измените язык нумерации контекста Excel, изменив язык Windows
  • Формула Excel для выведения котировок
  • Гистограмма Excel
  • Как сохранить историю изменений данных в одной книге или листе excel в другой книге или листе
  • 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() ), но при этом требуется, чтобы количество столбцов было жестко закодировано.

    Interesting Posts

    Как извлечь полный список типов расширений внутри каталога?

    Очень высокий шум, когда компьютер делает что-то интенсивное?

    Как создать zip / tgz в Linux, чтобы у Windows были правильные имена файлов?

    Как обмениваться Интернетом в Windows 7 с помощью двух сетевых карт?

    Клавиши Alt и AltGr случайным образом перестают работать с Windows 8.1

    Запуск нескольких версий Firefox

    Может ли dd-wrt или помидор отслеживать использование GB за период оплаты за устройство?

    Настройка насыщенности графики Intel исчезает при использовании HDMI

    Разделение нетбука без DVD-привода с использованием gparted

    Как отключить дескриптор перетаскивания на рабочем столе?

    Плагин для отображения привязок на HTML-странице?

    Есть ли способ сосредоточиться на результатах поиска Chrome?

    Как вы вносите изменения в шаблон Word?

    SSH с авторизованными ключами к системе Ubuntu с зашифрованным homedir?

    Каковы недостатки использования UEFI-загрузки?

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