Транспонирование столбца в MS 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).

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.7 (3 оценок)

Комментарии

Олег

Уважаемый CREATOR, подскажи, пожалуйста, как проще всего действовать... Имеется 5 одинаковых таблиц по виду (боковик, шапка, формат, формулы, ссылки), но с разными данными и есть 6-я таблица точно такая же, но она должна агрегировать данные с предыдущих пяти таблиц по каждой отдельно взятой ячейке. Как лучше всего объединить данные?

Creator

Все зависит, от того как нужно агрегировать: сумма? 

Можете также посмотреть статью http://excel2.ru/articles/primenenie-instrumenta-ms-excel-konsolidaciya