Так і таке буває. Сьогодні вирішимо це питання.
Термін "UNPIVOT" може бути перекладений як "Розконсолідація", "Розагрегація".
це процес перетворення широкої таблиці, де дані розподілені по кількох стовпцях, у довгу таблицю з меншою кількістю стовпців. Це часто роблять для полегшення фільтрації або для подальшого об'єднання даних в іншому форматі.
Консолідована таблиця: Дані по продажам за кілька місяців для кожного регіону представлені в одному рядку.
Розконсолідована таблиця: Ті ж самі дані представлені так, що кожен запис показує продажі в одному регіоні за один місяць.
Маємо перехресну таблицю з реалізацією улюблених смаколиків по обласних центрах Аркуш Реалізація .
У неї така структура: Вертикально розташовані дані - Код, Назва смаколика, Виробник
Горизонтально назви Міст і на перехресті Міста із Назва Смаколика кількість проданих одиниць.
Задача перевести цю таблицю в вигляд вхідних даних з окремими елементами по кожному перехрестю.
Тож задача сподіваюсь зрозуміла, тому приступаємо до покрокового розбору.
УВАГА: варіант описаний нижче має обмеження по довжині (кількості) символів.
1й Крок - На аркуші unpivot у А2 вводимо формулу:
Таблиця Реалізація має:
24 елемента Міст
35 елементів Кодів (смаколиків),
тобто треба для кожного Кода повторити назву Міста 35 разів. Довжина таблиці 24*35=840 рядків. Що і робиться у клітинці А2.
Формула бере всі заголовки (тобто всі назви Міст) з рядка 'Реалізація'!D1:AA1, робить їх вертикальними, повторює кожен заголовок стільки разів, скільки є рядків (Кодів) у діапазоні 'Реалізація'!A3:A37. Потім цей повторюваний текст розбивається по нових рядках і зрештою збирається в один стовпчик.
ФОРМУЛА:
=ARRAYFORMULA(
FLATTEN(
SPLIT(
REPT(
TRANSPOSE('Реалізація'!D1:AA1) & CHAR(10);
COUNTA(
FLATTEN('Реалізація'!A3:A37)
)
);
CHAR(10)
)
)
)
Давайте розберемо цю формулу по частинах:
'Реалізація'!D1:AA1:
Це діапазон клітинок на аркуші з назвою "Реалізація", що йдуть горизонтально з клітинки D1 до AA1. У цьому діапазоні знаходяться назви Міст.
TRANSPOSE('Реалізація'!D1:AA1):
Ця функція трансформує рядок міст (Київ, Харків, Одеса, Дніпро, Львів ...) у вертикальний стовпець.
Результат виглядатиме так:
Київ
Харків
Одеса
Дніпро
Львів
...
&CHAR(10):
& використовується для об'єднання (конкатенації) даних.
CHAR(10) додає до кожного значення в списку "перенос рядка" (новий рядок), щоб розділити дані.
REPT(...;COUNTA(...)):
REPT (від слова "repeat" - повторити) повторює кожен елемент стільки разів, скільки потрібно.
COUNTA(FLATTEN('Реалізація'!A3:A37)):
FLATTEN('Реалізація'!A3:A37) перетворює діапазон 'Реалізація'!A3:A37 на плоский список, прибираючи порожні клітинки.
COUNTA(...) рахує кількість непорожніх значень у цьому діапазоні. У цьому випадку, це 35 (оскільки там є 35 значень Кодів).
Таким чином, REPT повторює кожен заголовок (Місто) стільки разів, скільки рядків (Кодів) у нашому діапазоні 'Реалізація'!A3:A37 '.
SPLIT(...; CHAR(10)):
SPLIT розбиває текст на частини, використовуючи символ переносу рядка (CHAR(10)) як роздільник.
FLATTEN(...):
FLATTEN бере діапазон (або набір даних), що складається з декількох рядків і стовпчиків, і перетворює його в один довгий стовпчик.
ARRAYFORMULA(...):
ARRAYFORMULA дозволяє обробляти масиви даних, тобто застосувати формулу відразу до всього діапазону даних, а не до окремих клітинок.
Перевіряємо простим способом, що вийшло, виділивши всі елементи на кожну позицію смаколика, маємо назву міста, 840 штук:
2й Крок Наступне, що маємо зробити для кожного Міста повторити весь набір Кодів від A3 до A37.
Тобто весь діапазон 'Реалізація'!A3:A37 повторити 24 - рази кількість Міст.
Для цього на аркуші unpivot у B2 вводимо наступну формулу:
У нас є список Кодів у клітинках 'Реалізація'!A3:A37.
Функція TEXTJOIN об'єднує всі ці назви в один рядок, розділяючи їх символом "♦".
Потім цей рядок повторюється REPT стільки разів COUNTA, скільки є Міст у діапазоні 'Реалізація'!D1:AA1.
Далі, результат ділиться назад на окремі значення за допомогою SPLIT, і перетворюється на один вертикальний список через FLATTEN.
Нижче буде деталізовано.
ФОРМУЛА:
=FLATTEN(
TRANSPOSE(
SPLIT(
REPT(
TEXTJOIN("♦"; TRUE; 'Реалізація'!A3:A37) & "♦";
COUNTA(
FLATTEN('Реалізація'!D1:AA1)
)
);
"♦"
)
)
)
TEXTJOIN("♦"; TRUE;'Реалізація'!A3:A37):
Ця частина формули об'єднує всі значення з діапазону 'Реалізація'!A3:A37 в один рядок, використовуючи символ "♦" як роздільник між значеннями.
TRUE означає, що порожні клітинки ігноруються.
REPT(TEXTJOIN(...) & "♦"; COUNTA(FLATTEN('Реалізація'!D1:AA1))):
REPT повторює цей рядок (створений на першому кроці) стільки разів, скільки непорожніх клітинок Міст у діапазоні 'Реалізація'!D1:AA1.
COUNTA(FLATTEN('Реалізація'!D1:AA1)) рахує кількість непорожніх клітинок в діапазоні 'Реалізація'!D1:AA1.
SPLIT(REPT(...); "♦"):
SPLIT розбиває текстовий рядок, створений на попередньому кроці, на окремі частини (значення), використовуючи "♦" як роздільник.
TRANSPOSE(SPLIT(...)):
TRANSPOSE перетворює рядок значень на стовпець або навпаки. Це потрібно для правильного розташування значень у списку.
FLATTEN(TRANSPOSE(...)):
FLATTEN зводить цей масив до одновимірного списку, тобто вирівнює всі значення в одному стовпці.
3й Крок тепер просто необхідно підтягнути дані Назва і Виробник - це робимо простою функцією.
Ця формула зручна для автоматичного заповнення даних на основі відповідності Кода.
НАЗВА С2:
=XLOOKUP($B2;'Реалізація'!$A$3:$A;'Реалізація'!$B$3:$B;"not found!!!";0)
Виробник D2:
=XLOOKUP($B2;'Реалізація'!$A$3:$A;'Реалізація'!$C$3:$C;"not found";0)
4й Крок - Отримати Штуки на Перехресті Міста і Кода.
Формула шукає конкретне значення у таблиці на аркуші "Реалізація" за допомогою координат рядка Код і стовпця Місто.
Ось її детальне пояснення:
=INDEX(
'Реалізація'!$D$3:$37;
MATCH(B2; 'Реалізація'!$A$3:$A$37; 0);
MATCH(A2; 'Реалізація'!$D$1:$1; 0)
)
INDEX('Реалізація'!$D$3:$D$37; ... ; ...):
Ця функція повертає значення з діапазону 'Реалізація'!$D$3:$D$37 на основі знайдених індексів рядка і стовпця.
Рядок і стовпець визначаються двома функціями MATCH, описаними нижче.
MATCH(B2; 'Реалізація'!$A$3:$A$37; 0):
Ця функція шукає значення Код, що знаходиться в комірці B2, в діапазоні 'Реалізація'!$A$3:$A$37.
Якщо збіг знайдено, MATCH повертає номер рядка, де це значення розташоване.
Аргумент "0" вказує на те, що необхідно знайти точну відповідність.
MATCH(A2; 'Реалізація'!$D$1:$1; 0):
Ця функція шукає значення Місто, що знаходиться в комірці A2, в діапазоні 'Реалізація'!$D$1:$1.
Якщо збіг знайдено, MATCH повертає номер стовпця, де це значення розташоване.
Знову ж таки, "0" означає, що ми шукаємо точну відповідність.
Нарешті отримали готову розгорнуту таблицю, з якою можна працювати для будь-яких інших задач.
Відео приклада на YouTube, Приклад за посиланням.
