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

367
user5305792

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

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

enter image description here

1

1 ответ на вопрос

1
Glorfindel

Вы можете поиграть немного с 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)

enter image description here

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

Это сработало идеально. Спасибо за это, это действительно полезно и адаптируется и экономит много копий и вставок. user5305792 7 лет назад 0

Похожие вопросы