Функція транспонування в Excel

Час від часу перед користувачем Excel може виникати завдання перетворити ряд даних із горизонтальною структурою на вертикальну. Цей процес називається транспозицією. Це слово є новим для більшості людей, тому що при звичайній роботі з ПК вам не потрібно використовувати цю операцію. Однак ті, кому доводиться працювати з великими обсягами даних, повинні знати, як це робити. Сьогодні ми докладніше поговоримо про те, як це зробити, з якою функцією, а також детально розглянемо деякі інші методи.

Функція TRANSPOSE – транспонування діапазонів клітинок в Excel

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

Синтаксис функції

Синтаксис цієї функції неймовірно простий: TRANSPOSE(масив). Тобто нам потрібно використовувати лише один аргумент, який є набором даних, який потрібно перетворити на горизонтальний або вертикальний вигляд, залежно від того, яким він був спочатку.

Транспонування вертикальних діапазонів клітинок (стовпців)

Припустимо, у нас є стовпець із діапазоном B2:B6. Вони можуть містити як готові значення, так і формули, які повертають результати в ці комірки. Для нас це не так важливо, транспозиція можлива в обох випадках. Після застосування цієї функції довжина рядка буде такою самою, як початкова довжина стовпця діапазону.

Послідовність дій для використання цієї формули така:

1. Виберіть лінію. У нашому випадку він має довжину п'ять клітинок.
2. Після цього перемістіть курсор на рядок формул і введіть туди формулу =TRANSP(B2:B6).
3. Натисніть комбінацію клавіш Ctrl + Shift + Enter.

Природно, що у вашому випадку потрібно вказати діапазон, характерний для вашої таблиці.

Транспонування горизонтальних діапазонів клітинок (рядків)

В принципі, механізм дії практично такий же, як і в попередньому пункті. Припустимо, у нас є рядок із початковою та кінцевою координатами B10:F10. Він також може містити як безпосередньо значення, так і формули. Давайте зробимо з нього стовпець, який матиме ті ж розміри, що й вихідний рядок. Послідовність дій така:

1. Виберіть цей стовпець мишкою. Ви також можете використовувати клавіші на клавіатурі Ctrl і стрілку вниз, клацнувши верхню клітинку цього стовпця.
2. Після цього записуємо формулу =TRANSP(B10:F10) у рядку формул.
3. Записуємо його у вигляді формули масиву за допомогою комбінації клавіш Ctrl + Shift + Enter.

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

Іншим можливим варіантом транспонування є використання функції «Спеціальна вставка». Це вже не оператор, який можна використовувати у формулах, але це також один із популярних методів перетворення стовпців у рядки та навпаки.

Ця опція знаходиться на вкладці Головна. Для доступу до нього потрібно знайти групу «Буфер обміну», а там знайти кнопку «Вставити». Після цього відкрийте меню, яке знаходиться під цією опцією, і виберіть пункт «Транспонування». Перед цим вам потрібно вибрати діапазон, який ви хочете виділити. В результаті отримаємо такий же діапазон, тільки дзеркально відображений навпаки.

3 способи транспонування таблиці в Excel

Але насправді існує набагато більше способів перетворити стовпці в рядки і навпаки. Давайте опишемо 3 методи, за допомогою яких ми можемо транспонувати таблицю в Excel. Два з них ми обговорили вище, але наведемо ще кілька прикладів, щоб ви отримали краще уявлення про uXNUMXbuXNUMXbпроведення цієї процедури.

Спосіб 1: Спеціальна вставка

Цей спосіб найпростіший. Досить натиснути пару кнопок, і користувач отримує транспонований варіант таблиці. Для більшої наочності наведемо невеликий приклад. Припустімо, що у нас є таблиця, яка містить інформацію про те, скільки товарів зараз є на складі, а також про їх загальну вартість. Сама таблиця виглядає так.

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

Наше завдання — зробити так, щоб інформація, яка міститься в таблиці, була розташована горизонтально. Тобто, щоб стовпці стали рядками. Послідовність дій в нашому випадку буде наступною:

1. Виберіть діапазон даних, які нам потрібно обертати. Після цього ми копіюємо ці дані.
2. Розмістіть курсор у будь-якому місці аркуша. Потім натисніть праву кнопку миші і відкрийте контекстне меню.
3. Потім натисніть кнопку «Спеціальна вставка».

Після виконання цих кроків вам потрібно натиснути кнопку «Транспонувати». Навпаки, поставте галочку біля цього пункту. Інші налаштування не змінюємо, а потім натискаємо на кнопку «ОК».

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

Майже ті самі дії потрібно виконати для транспонування значень, а не формул. У цьому випадку також потрібно скористатися меню Спеціальна вставка, але перед цим вибрати діапазон даних, що містить значення. Бачимо, що вікно Спеціальна вставка можна викликати двома способами: через спеціальне меню на стрічці або контекстне меню.

Спосіб 2. Функція TRANSP в Excel

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

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

1. Спочатку нам потрібно вибрати діапазон даних, який буде використовуватися для транспонування таблиці. Просто потрібно виділити область навпаки. Наприклад, у цьому прикладі ми маємо 4 стовпці та 6 рядків. Тому необхідно вибрати область з протилежними характеристиками: 6 стовпців і 4 рядки. На малюнку це дуже добре видно.
2. Після цього відразу починаємо заповнювати цю комірку. Важливо випадково не зняти виділення. Тому формулу необхідно вказувати безпосередньо в рядку формул.
3. Далі натисніть комбінацію клавіш Ctrl + Shift + Enter. Пам’ятайте, що це формула масиву, оскільки ми працюємо відразу з великим набором даних, які будуть перенесені в інший великий набір комірок.

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

Ми бачимо, що формула не була перенесена в нову таблицю. Також було втрачено форматування. Поето

Все це доведеться робити вручну. Крім того, пам'ятайте, що ця таблиця пов'язана з оригінальною. Тому, як тільки деяка інформація змінюється у вихідному діапазоні, ці коригування автоматично вносяться до транспонованої таблиці.

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

зведена таблиця

Це принципово новий спосіб, який дозволяє не тільки транспонувати таблицю, але і виконувати величезну кількість дій. Щоправда, механізм транспозиції буде дещо іншим, ніж у попередніх методах. Послідовність дій така:

1. Зробимо зведену таблицю. Для цього потрібно вибрати таблицю, яку нам потрібно транспонувати. Після цього переходимо до пункту «Вставити» і шукаємо там «Зведену таблицю». З’явиться діалогове вікно, як на цьому знімку екрана.
2. Тут ви можете перепризначити діапазон, з якого він буде складатися, а також зробити ряд інших налаштувань. Тепер нас цікавить в першу чергу місце зведеної таблиці – на новому аркуші.
3. Після цього буде автоматично створено макет зведеної таблиці. У ньому необхідно відзначити ті предмети, які ми будемо використовувати, а потім їх необхідно перемістити в потрібне місце. У нашому випадку нам потрібно перемістити елемент «Продукт» в «Назви стовпців», а «Ціна за штуку» в «Значення».
4. Після цього зведена таблиця буде остаточно створена. Додатковим бонусом є автоматичний розрахунок кінцевої вартості.
5. Ви також можете змінити інші налаштування. Наприклад, зніміть позначку з пункту «Ціна за штуку» і відзначте пункт «Загальна вартість». В результаті у нас вийде таблиця з інформацією про те, скільки коштують продукти. Цей метод транспозиції набагато функціональніший за інші. Опишемо деякі переваги зведених таблиць:

1. автоматизація. За допомогою зведених таблиць можна автоматично підсумовувати дані, а також довільно змінювати положення колонок і колонок. Для цього не потрібно виконувати жодних додаткових дій.
2. Інтерактивність. Користувач може змінювати структуру інформації стільки разів, скільки йому потрібно для виконання своїх завдань. Наприклад, ви можете змінювати порядок стовпців, а також групувати дані довільним чином. Це можна робити стільки разів, скільки потрібно користувачеві. І займає це буквально менше хвилини.
3. Легко форматувати дані. Розташувати зведену таблицю так, як хоче людина, дуже легко. Для цього достатньо зробити кілька кліків мишкою.
4. Отримання значень. Переважна кількість формул, які використовуються для створення звітів, знаходяться в прямому доступі людини і легко інтегруються в зведену таблицю. Це такі дані, як підсумовування, отримання середнього арифметичного, визначення кількості комірок, множення, знаходження найбільшого і найменшого значень у вказаній вибірці.
5. Можливість створення зведених діаграм. Якщо зведені таблиці перераховуються, пов’язані з ними діаграми автоматично оновлюються. Можна створити скільки завгодно діаграм. Всі вони можуть бути змінені для конкретного завдання і вони не будуть пов'язані між собою.
6. Можливість фільтрації даних.
7. Можна побудувати зведену таблицю на основі більш ніж одного набору вихідної інформації. Тому їх функціональність стане ще більшою.

Правда, при використанні зведених таблиць необхідно враховувати такі обмеження:

1. Не всю інформацію можна використовувати для створення зведених таблиць. Перш ніж їх можна буде використовувати для цієї мети, клітини повинні бути нормалізовані. Простими словами, зробіть це правильно. Обов'язкові вимоги: наявність рядка заголовка, заповненість усіх рядків, однаковість форматів даних.
2. Дані оновлюються напівавтоматично. Щоб отримати нову інформацію у зведеній таблиці, необхідно натиснути на спеціальну кнопку.
3. Зведені таблиці займають багато місця. Це може призвести до деяких збоїв у роботі комп’ютера. Крім того, через це файл буде важко надіслати електронною поштою.

Також після створення зведеної таблиці користувач не має можливості додавати нову інформацію.