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

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

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

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

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

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

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

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

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

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

Решение:

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

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

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

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

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

Другими словами, размер диапазона, который необходимо передать функции ВПР будет зависеть от вычисления этой самой функции ВПР. В 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:

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

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

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

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

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

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