Заняття 3 з 3
Транспонування діапазону з змінною кількістю елементів
У Excel дуже просто транспонувати звичайний діапазон або таблицю, але складніше, коли записи містять різну кількість елементів.
Опис заняття та відеоверсія
Відеоверсія
Текстова версія
В Excel дуже просто транспонувати, звичайний діапазон або таблицю. Перший спосіб, з використанням спеціальної вставки, підійде для одиничної операції. Якщо потрібно оновлення транспонованих даних, слід скористатися функцією ТРАНСП (TRANSPOSE).
Є й інші випадки, наприклад, перетворення вертикального діапазону в таблицю, коли вгорі списку формується заголовок, а нижче — елементи списку, однак жоден із зазначених варіантів не передбачає випадку, коли записи містять різну кількість елементів.
Наприклад, якщо в списку клієнтів в когось не вказано телефон. Так, можна встановити прочерк і залишити запис, і проблема з транспонуванням вирішувалася б тривіально, але якщо цей список формується зовнішнім додатком і такого варіанту немає — потрібен інший підхід.

Постановка завдання
Постановка завдання зводиться до перетворення вертикального діапазону в горизонтальний. Якщо в запису відсутній окремий елемент, то поле можна залишити порожнім, зробити прочерк або позначку про відсутність даних.

Використовувані функції та інструменти
У ході розв'язання поставленого завдання будуть використані функції, які умовно можна поділити на дві частини.
Основні, тобто безпосередньо такі, що відповідають за розв'язання завдання:
| Формула | Застосування в рамках даного завдання |
|---|---|
| ВПР (VLOOKUP) | Основа формули, використовується для співставлення заголовків таблиці та заголовків діапазонів вихідного списку |
| ДВССЫЛ (INDIRECT) | Використовується для передачі першої клітинки плаваючого діапазону у ВПР |
| СМЕЩ (OFFSET) | Використовується для визначення розміру плаваючого діапазону, для подальшої передачі даних у ВПР |
| СЧЁТЕСЛИ (COUNTIF) | Використовується для підрахунку помилок у кожному рядку підсумкової таблиці |
Допоміжні, тобто такі, без яких можна обійтися:
| Формула | Застосування в рамках даного завдання |
|---|---|
| ЕСЛИОШИБКА (IFERROR) | Використовується для надання естетичного вигляду таблиці, тобто заміни помилок на порожні значення клітинки. |
| СЧЁТЗ (COUNTA) | Використовується для підрахунку стовпців таблиці |
Розв'язання:
1. Основна частина розв'язання
На початку складаємо заголовок майбутньої таблиці, кількість стовпців відповідатиме запису з максимальною кількістю елементів. Одразу ж за допомогою функції СЧЁТЗ підрахуємо кількість записів.
Основою нашої майбутньої формули буде функція ВПР, яка шукатиме співпадіння в заголовку та вихідному діапазоні. Однак, оскільки функція ВПР здійснює пошук до першого співпадіння, нам потрібно буде в якості таблиці вибирати не повний діапазон, а його частини (записи на першому рисунку).
Тут з'являються перші складнощі. Перша полягає в тому, що записи можуть містити різну кількість елементів, а друга — в тому, що аргумент «таблиця» для функції ВПР буде «плаваючим», тобто різним для кожного рядка нової таблиці.
Для розв'язання другої складності стане в пригоді функція ДВССЫЛ (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, а при протягуванні формули вниз це значення має зсуватися ровно на стільки рядків, скільки елементів без помилок було в попередньому записі підсумкової таблиці, тобто значення попередньої клітинки плюс кількість елементів попереднього запису.

Тепер можна другу строку за допомогою автозаповнення розтягнути вниз. Однак через те, що у нас формули взаємозалежні, доведеться зробити перерахунок стовпця з розрахунком кількості елементів у записі. З одного боку, автоматизація виглядає слабко, з іншого — додавання нових записів коректно обробляється формулами.

2. Упорядкування обчислень або замість висновку
Цей розділ скоріше косметичний і дуже простий: якщо вас бентежать помилки в підсумковій таблиці, їх можна легко обробити за допомогою функції ЕСЛИОШИБКА. Проте краще робити це не в самій таблиці, оскільки перехоплення помилок є основою наших обчислень, а вивести результат в нове місце, заодно приховавши проміжні обчислення:
=ЕСЛИОШИБКА(Лист1!E1;"")
Здавалося б, проміжні обчислення можна сховати в основну формулу, але через циклічні обчислення ми робили перерахунок лише для одного стовпця; якби помістити обчислення стовпців з помилками в основну формулу, то такий перерахунок довелося б робити для кожної клітинки формули.
Більше того, досвідом встановлено, що розрахунок кількості помилок необхідно робити праворуч від самої підсумкової таблиці: якщо помістити стовпець з розрахунком помилок зліва, то підрахунок буде неправильним. Ймовірно, це пов'язано з особливостями обчислень в Excel.

У цьому матеріалі розглянуто загальний випадок, коли кількість записів може змінюватися в досить широких межах. Якщо розглядати випадок, коли в записі відсутній один або пара елементів, то перевірку на наявність/відсутність можна робити для конкретного стовпця і там проблем із перерахунком не повинно бути.
Завантажити файли
Посилання для завантаження робочого файлу.
Також цей файл розміщено на форумі підтримки, де можна обговорити матеріал.