Зміст заняття
  1. Опис заняття та відеоверсія
  2. Розв'язання:
  3. 1. Основна частина розв'язання
  4. 2. Упорядкування обчислень або замість висновку
  5. Завантажити файли

Заняття 3 з 3

Транспонування діапазону з змінною кількістю елементів

У Excel дуже просто транспонувати звичайний діапазон або таблицю, але складніше, коли записи містять різну кількість елементів.

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

Відеоверсія

Відео · YouTube

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

В 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, а при протягуванні формули вниз це значення має зсуватися ровно на стільки рядків, скільки елементів без помилок було в попередньому записі підсумкової таблиці, тобто значення попередньої клітинки плюс кількість елементів попереднього запису.

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

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

Через циклічність у обчисленнях краще зробити перерахунок формул

2. Упорядкування обчислень або замість висновку

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

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

Здавалося б, проміжні обчислення можна сховати в основну формулу, але через циклічні обчислення ми робили перерахунок лише для одного стовпця; якби помістити обчислення стовпців з помилками в основну формулу, то такий перерахунок довелося б робити для кожної клітинки формули.

Більше того, досвідом встановлено, що розрахунок кількості помилок необхідно робити праворуч від самої підсумкової таблиці: якщо помістити стовпець з розрахунком помилок зліва, то підрахунок буде неправильним. Ймовірно, це пов'язано з особливостями обчислень в Excel.

Підрахунок буде некоректним лише через розташування формул на аркуші

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

Завантажити файли

Посилання для завантаження робочого файлу.

Також цей файл розміщено на форумі підтримки, де можна обговорити матеріал.