Дізнайтеся, як транспонувати дані в Excel і швидко змінювати орієнтацію стовпців і рядків на аркуші.
Транспонування даних в Excel означає перемикання або обертання даних з горизонтального масиву на вертикальний або з вертикального масиву на горизонтальний. Простіше кажучи, функція TRANSPOSE змінює рядки на стовпці і стовпці на рядки.
У Excel можна транспонувати дані двома різними способами, використовуючи функцію TRANSPOSE та спеціальний метод вставки. У цій статті ми покажемо, як змінити значення рядка на значення стовпця та значення стовпця на значення рядка в Excel.
Використання Функція TRANSPOSE
Функція TRANSPOSE дозволяє перемикати орієнтацію заданого діапазону комірок (масиву) з вертикальної на горизонтальну або навпаки. Оскільки це формула масиву, функція повинна бути введена в діапазон, який має таку ж кількість рядків і стовпців, що й джерело.
Синтаксис функції транспонування такий:
= Транспонування (масив)У наступному зразку електронної таблиці ми збираємося транспонувати таблицю, яка містить обсяги експорту тварин за округами.
По-перше, ви повинні знати, скільки рядків і стовпців містить ваша початкова таблиця. Потім виберіть точну кількість клітинок як вихідний діапазон комірок, але в іншому напрямку.
Наприклад, у нас є 5 рядків і 3 стовпці, тому нам потрібно вибрати 3 рядки і 5 стовпців клітинок для транспонованих даних. У нашому випадку ми маємо 5 рядків і 4 стовпці, тому потрібно вибрати 4 рядки і 5 стовпців.
Виберіть діапазон пустих клітинок і введіть формулу: =ТРАНСПОЗУВАННЯ(A1:D5), але ще не натискайте клавішу «Enter»! На цьому етапі ваш Excel буде виглядати так:
Тепер натисніть «Ctrl + Shift + Enter». Після натискання комбінації клавіш дані транспонуються.
Пам’ятайте, що формулу масиву завжди потрібно закінчувати натисканням «CTRL + SHIFT + ENTER». Коли ви натискаєте комбінацію клавіш, вона поміщає набір фігурних дужок навколо формули в рядку формул, щоб результат розглядався як масив даних, а не як значення однієї клітинки.
Функція Excel Transpose копіює лише дані, а не форматування вихідних даних. Як бачимо, форматування вихідного заголовка не копіюється. Крім того, транспонування є динамічною функцією, коли ваше значення в вихідних даних буде змінено, воно буде відображено в транспонованих даних, але не навпаки.
Як транспонувати дані в Excel без нулів
Якщо ви транспонуєте таблицю або діапазон клітинок з однією або кількома порожніми клітинками, ці клітинки матимуть нульові значення під час транспонування, як показано на малюнку нижче:
Щоб скопіювати порожні клітинки під час транспонування, скористайтеся функцією IF у формулі TRANSPOSE, щоб перевірити, чи є клітинка порожньою чи ні. Якщо клітинка порожня/порожня, вона повертає порожній рядок (“”) до транспонованої таблиці.
Якщо у вашій таблиці є порожні клітинки, введіть формулу: =TRANSPOSE(IF(A1:D5="","",A1:D5)) у вибраних клітинках.
Потім натисніть комбінацію клавіш «Ctrl + Shift + Enter». Тепер порожній простір у комірці D5 копіюється в транспоновану клітинку E11.
Як транспонувати в Excel за допомогою Спеціальний метод вставки
Іншим способом транспонування даних в Excel є використання спеціального методу вставки. Але транспонування даних за допомогою методу Paste Special має недоліки і плюси. Ця функція дозволяє транспонувати дані з оригінальним форматуванням вмісту, але транспоновані дані будуть статичними.
На відміну від функції TRANSPOSE, якщо ви зміните значення в вихідних даних, воно не відобразиться в транспонованому вмісті при використанні спеціального методу вставки.
Спочатку виберіть діапазон, який потрібно транспонувати, а потім клацніть правою кнопкою миші та виберіть «Копіювати» або натисніть «CTRL + C», щоб скопіювати таблицю.
Далі виділіть діапазон комірок, який відповідає точному розміру вихідної таблиці, як і для попереднього методу.
Потім клацніть правою кнопкою миші вибрані клітинки та натисніть «Спеціальна вставка».
У діалоговому вікні «Спеціальна вставка» встановіть прапорець «Транспонувати» та натисніть «ОК».
Тепер таблиця буде транспонована (скопійована) у вибрані клітинки.
У цьому методі вам не доведеться турбуватися про те, що чорні клітинки стануть нулями в транспонованій таблиці, оскільки порожні клітинки також будуть скопійовані до вибраних клітинок.