Центр обученияExcelПрактические занятия по ExcelТранспонирование диапазона с изменяемым количеством элементов

Транспонирование диапазона с изменяемым количеством элементов

Предыдущее занятиеКак сравнить два файла Excel V2

Описание занятия и ведо версия

Видеоверсия

Текстовая версия


В Excel очень просто транспонировать, обычный диапазон или таблицу. Первый способ, с использованием специальной вставки, подойдет для единичной операции. Если необходимо обновление транспонированных данных, следует воспользоваться функцией ТРАНСП (TRANSPOSE).

Есть и другие случаи, например, преобразования вертикального диапазона в таблицу, когда вверху списка формируется заголовок, а ниже элементы списка, однако, ни один из указанных вариантов не предусматривает случая, когда записи содержат разное количество элементов.

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

Исходный диапазон для транспонирования

Постановка задачи

Постановка задачи сводится в преобразовании вертикального диапазона в горизонтальный. Если у записи отдельный элемент отсутствует, то здесь можно оставить поле пустым, сделать прочерк, или пометку об отсутствии данных.

Результат после преобразования

Используемые функции и инструменты

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

Основные, т.е. непосредственно отвечающие за решение задачи:

Формула Применение в рамках данной задачи
ВПР (VLOOKUP) Основа формулы, используется для сопоставления заголовков таблицы и заголовков диапазонов исходного списка
ДВССЫЛ (INDIRECT) Используется для передачи первой ячейки плавающего диапазона в ВПР
СМЕЩ (OFFSET) Используется для определения размера плавающего диапазона, для последующей передачи данных в ВПР
СЧЁТЕСЛИ (COUNTIF) Используется для подсчеты ошибок в каждой строке итоговой таблицы

Вспомогательные, т.е., без которых можно обойтись:

Формула Применение в рамках данной задачи
ЕСЛИОШИБКА (IFERROR) Используется для придания эстетического вида таблице, т.е. замене ошибок на пустые значения ячейки.
СЧЁТЗ (COUNTA) Используется для подсчета столбцов таблицы

Решение:

  1. Основная часть решения
В начале составляем заголовок будущей таблицы, количество столбцов будет соответствовать записи с максимальным количеством элементов. Сразу же, с помощью функции СЧЁТЗ, подсчитаем количество записей.

Основой нашей будущей формулы, будет составлять функция ВПР, которая будет искать совпадения в заголовке и исходном диапазоне. Однако, поскольку функция ВПР осуществляет поиск до первого совпадения, нам нужно будет в качестве таблицы выбирать не полный диапазон, а части его (записи на первом рисунке).

Здесь появляются первые сложности. Первая заключается в том, что записи могут содержать разное количество элементов, а вторая заключается в том, что аргумент «таблица», для функции ВПР будет «плавающим», т.е. разным для каждой строки новой таблицы.

Для решения второй сложности пригодится функция ДВССЫЛ (INDIRECT), которая поможет определить первую ячейку каждого «плавающего» диапазона, а с помощью функции СМЕЩ (OFFSET) мы выберем размер каждого «плавающего диапазона».

Размер каждого диапазона зависит от количества элементов в каждой записи. И здесь появляется очень интересный нюанс, дело в том, что количество элементов в записи мы будем определять по формуле:

К-во элементов записи = максимальное к-во элементов – к-во ошибок #НД, возвращаемых функцией ВПР

Другими словами, размер диапазона, который необходимо передать функции ВПР будет зависеть от вычисления этой самой функции ВПР. В Excel такие вычисления называются «итеративными», их еще называют «циклическими», или «циклическими ошибками».

Само по себе «зацикливание» формулы, когда результат ее вычисления напрямую или косвенно зависит от нее самой, не является ошибкой, однако, в большинстве случаев, такое зацикливание случается именно из-за ошибки пользователя, поэтому по умолчанию в Excel отключены итеративные вычисления.

Для включения итеративных вычисления следует перейти в раздел «Формулы» параметров Excel.

Включение итеративных вычислений в Excel

Вместе с включением циклических вычислений задается и предельное число итераций (по умолчанию 100), чтобы программа не уходила в бесконечный цикл вычислений, если формула не будет иметь верхнего предела вычисления, например, формула:

A2 = A2+2

запросто «повесит» систему, если не ограничить количество циклов. В нашем случае 100 вычислений будет с явным избытком, уменьшим значение до 10.

Формула для подсчета ячеек итоговой таблицы с ошибками будет использовать функцию СЧЁТЕСЛИ:

 =СЧЁТЕСЛИ(E2:K2;НД())

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

=$D$1-СЧЁТЕСЛИ(E2:K2;НД())

Теперь можно записать итоговую формулу преобразования вертикального диапазона в таблицу:

=ВПР(E$1;СМЕЩ(ДВССЫЛ("A"&$D2);0;0;$M2;2);2;ЛОЖЬ)

А увидеть, что ячейки с формулами на листе взаимозависимые легче всего на листе Excel:

Формулы ссылаются друг на друга

Или воспользоваться инструментом отображения влияющих и зависимых ячеек:

Отображение зависимости формул на листе Excel

Т.е. для ячеек из диапазона E2:K2 ячейка M2 является одновременно и влияющей и зависимой, равно как и для ячейки M2 диапазон E2:K2 является и влияющим и зависимым.

Решение задачи на этом не закончилось дело в том, что для функции ВПР, в качестве левого верхнего края аргумента «таблица» мы указали ссылку на ячейку D2 со значением 1, а, растягивая формулу вниз это значение должно смещаться ровно на столько строк, сколько элементов без ошибок было в предыдущей записи итоговой таблице, т.е. значение предыдущей ячейки плюс количество элементов предыдущей записи.

Практически готовая формула для транспонирования

Теперь можно вторую строку с помощью автозаполнения растянуть вниз. Однако, из-за того, что у нас формулы взаимозависимы придется сделать пересчет столбца с расчетом количества элементов в записи. С одной стороны, автоматизация выглядит слабовато, с другой – добавление новых записей корректно обрабатывается формулами.

Из-за цикличности в вычислениях лучше сделать пересчет формул

  1. Приведение вычислений в порядок или вместо заключения
Данный раздел сугубо косметический и очень простой, если вас смущают ошибки в итоговой таблице, их можно легко обработать с помощью функции ЕСЛИОШИБКА.
Однако лучше это делать не в самой таблице, т.к. перехват ошибок является основой наших вычислений, а вывести результат в новое место, заодно скрыть промежуточные вычисления:

=ЕСЛИОШИБКА(Лист1!E1;"")

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

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

Подсчет будет идти некорректно только из-за расположения формул на листе

В данном материале был рассмотрен общий случай, когда количество записей может меняться в достаточно широких рамках. Если рассматривать случай, когда в записи будет отсутствовать один или пару элементов, то проверку на наличие/отсутствие можно делать на конкретный столбец и там проблем с пересчетом быть не должно.

Скачать файлы

Ссылка для скачивания рабочего файла.

Также данный файл размещен на форуме поддержки, где можно обсудить материал.

Предыдущее занятиеКак сравнить два файла Excel V2