Транспонирование столбца в EXCEL в таблице с повторами

Пусть имеется таблица наименований обуви. Каждое наименование обуви (столбец №1) повторяется столько раз, сколько у него имеется различных размеров (столбец №2) . Иногда требуется «перевернуть» не всю таблицу, а только один столбец №2. Т.е. теперь у нас будет столбец наименований обуви (уже без повторов), а по горизонтали - список размеров. Транспонирование произведем формулами.

Пусть имеется исходная таблица наименований обуви с размерами.

Требуется для каждого наименования обуви вывести горизонтально список размеров.

Сделаем такое преобразование таблицы с помощью формул, что позволит в дальнейшем при вводе новых наименований и размеров иметь актуальную транспонированную таблицу.

Для упрощения формул наложим ограничения на исходную таблицу: количество размеров одного наименования не может быть больше 10 (определяет количество столбцов с размерами в транспонированной таблице); таблица отсортирована сначала по наименованию, затем по размеру (чтобы размеры в транспонированной таблице шли по порядку).

Примечание : О сортировке таблицы средствами EXCEL читайте статью Автофильтр .

Решение

С помощью идей из статьи Отбор уникальных значений сформируем в новой транспорнированной таблице перечень уникальных наименований (у нас будет их 2).

Для этого создадим Динамический диапазон Наименования , чтобы при добавлении новых данных в исходную таблицу, транспонированная таблица автоматически обновлялась.

Формула для Динамического диапазона: =СМЕЩ(Лист1!$A$6;;;СЧЁТЗ(Лист1!$A$6:$A$19)).

Затем создадим перечень уникальных наименований в столбце D с помощью формулы массива :

=ЕСЛИОШИБКА(ИНДЕКС(Наименования;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$5:D5;Наименования);0));"")

Формулу введите в ячейку D6 . Не забудьте после ввода формулы массива нажать CTRL+SHIFT+ENTER. Протяните формулу вниз с помощью Маркера заполнения на 2 ячейки ниже.

В столбце Е подсчитаем количество размеров каждого наименования: =СЧЁТЕСЛИ(Наименования;D6)

Примечание : Не забудьте, что Наименования в вышеприведенной формуле - это Имя созданного ранее Вами Динамического диапазона .

В строке 4 (см. файл примера ) создайте горизонтальный массив порядковых чисел от 1 до 10.

Теперь самое интересное: запишем в ячейке F6 формулу для транспонирования столбца В в горизонтальный массив .

=ЕСЛИ($E6>=F$4;СМЕЩ($B$6;ПОИСКПОЗ($D6;Наименования;0)+F$4-2;;1);"")

Формулу протяните на 9 ячеек вправо и на 2 строки ниже. Обратите внимание на использование смешанных ссылок в формуле. При копировании формулы вправо ссылка $E6 не изменяется, т.к. зафиксирован столбец (зато F$4 изменяется на G$4 , затем на H$4 и т.д.), а при копировании вниз наоборот ссылка F$4 не изменяется, т.к. зафиксирована строка (зато $E6 изменяется на $E7 , затем на $E8 и т.д.)

Тестируем

Дополним исходную таблицу одним наименованием обуви Кроссовки дет. с 3-мя размерами (12, 13, 14).

Различные наименования обуви отделены красными горизонтальными линиями с помощью Условного форматирования .

Транспонированная таблица обновится автоматически.