Мини-курс "5 чудес Excel"
Cross отчет в плоскую таблицу функцией СМЕЩ
Задача
Из вот такой исходной таблицы:
Нужно получить обычную плоскую таблицу:
Решение с помощью функции массива СМЕЩ
Реальный размер таблицы в десятки и сотни раз превосходит этот учебный пример, поэтому ручное копирование данных исключено.
Доверим эту работу функции СМЕЩ. В помощниках у неё будут порядковые номера строк и столбцов с данными, я добавила их и выделила для удобства красным:
Доверим эту работу функции СМЕЩ. В помощниках у неё будут порядковые номера строк и столбцов с данными, я добавила их и выделила для удобства красным:
Далее создаю служебный столбец с номерами строк. Так как в каждой строке столбца данных и все их нужно из горизонтали превратить в вертикаль, то и каждый номер строки указываю трижды.
А рядом, во втором служебном столбце, указываю номера столбцов с данными. То есть получаю нечто вроде списка координат с исходными данными. Похоже, правда? Номер строки такой, номер столбца такой, и так далее. Каждая пара координат уникальна.
Дальше дело техники. В качестве стартовой ячейки выбираю верхний левый угол ячейки и сдвигаю её к нужной ячейке функцией =СМЕЩ($F$1;A1;B1) - формула для ячейки С1
А рядом, во втором служебном столбце, указываю номера столбцов с данными. То есть получаю нечто вроде списка координат с исходными данными. Похоже, правда? Номер строки такой, номер столбца такой, и так далее. Каждая пара координат уникальна.
Дальше дело техники. В качестве стартовой ячейки выбираю верхний левый угол ячейки и сдвигаю её к нужной ячейке функцией =СМЕЩ($F$1;A1;B1) - формула для ячейки С1
Видео разбор этого примера смотрите по ссылке: https://www.instagram.com/tv/CAdDn7lnQXF/?utm_source=ig_web_copy_link
Желаю вам долгой и крепкой дружбы с формулами – со СМЕЩ, ДВССЫЛ, АДРЕС и другими волшебницами. Приходите учиться на мой онлайн курс Формулы массива – подробно разберём и принцип работы этих функций, и несколько головоломок с их участием решим.
Первый урок можно посмотреть бесплатно: https://excelforum.ru/courses/page6