транспонирование больших наборов данных с заданной структурой с использованием формулы

Question

Я пытаюсь найти способ транспонирования больших наборов данных (структура показана ниже) определенным образом и задаюсь вопросом, как реализовать его в Excel, используя формулу, чтобы избежать трудоемкой и подверженной ошибкам транспонированной операции копирования-вставки!

Я хочу сделать преобразование данных на sheet A на отдельном листе (B). По сути, у меня есть три измерения нескольких функций разных групп. Количество групп может варьироваться, но измерения всегда будут в трех экземплярах. Спасибо за любую помощь, которую вы можете оказать

Answer 1

Вы можете немного поиграть с формулами INDEX , COLUMN и ROW и некоторой арифметикой. INDEX будет искать значение ячейки для вас, учитывая номер строки и столбца. Для простой транспонирования вам просто нужно COLUMN текущей ячейки в качестве строки и ROW текущей ячейки в качестве столбца. Однако ваша операция немного сложнее, чем простая перестановка, и поэтому нам нужна некоторая арифметика. С некоторой пробой и ошибкой я пришел к формуле ниже.

При условии, что лист A содержит данные в верхнем левом углу (т. Е. B2 содержит значение 5), эта формула на листе B будет работать:

=INDEX('Sheet A'!$1:$1048576, MOD(COLUMN()+1,3)+3*ROW()-4, 1+(COLUMN()+1)/3)

Номера столбцов начинаются с 1, поэтому MOD(COLUMN()+1,3) дает 0 для столбцов группы n , 1 для B и 2 для C. Часть 3*ROW() соответствует трем строкам для каждого Группа n на листе A. 1+(COLUMN()+1)/3 отображает все столбцы Cat 1 на листе B в столбец 2 на листе A, Cat 2 в столбец 3 и т.д.