Excel: як транспонувати таблицю
Шановна редакціє! Працюючи з Excel, мені часто доводиться перебудовувати таблицю, міняючи рядки і стовпці місцями. За допомогою формул це робити дуже незручно. Підкажіть, як можна вирішити проблему?
В. Суворова, м. Харків
Відповідає
Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Операція, в якій рядки і стовпці таблиці міняються місцями, називається транспонуванням. В Excel таку операцію можна виконати, як мінімум, двома способами. На жаль, ви не вказали версію Excel, яку використовуєте у своїй роботі. Тому дати докладну інструкцію по роботі з інтерфейсом я не можу. Та це, найімовірніше, й не потрібно. Операція нескладна, можна обійтися і без рисунків.
Спосіб 1. Транспонування даних через спеціальну вставку:
1) виділіть блок таблиці, яку хочете транспонувати;
2) скопіюйте дані до буфера обміну («
Ctrl+V» або «Ctrl+Ins»);3) поставте покажчик активної комірки на початок блока, де має знаходитися транспонована таблиця;
4) викличте меню «
Правка → Специальная вставка» (в Excel 2007 функцію спеціальної вставки можна викликати, натиснувши на маленький трикутник під іконкою «Вставка»). З’явиться вікно параметрів спеціальної вставки;5) у цьому вікні включіть прапорець «
Транспонировать»;6) натисніть на «
ОК».На листі з’явиться транспонована таблиця. Спробуйте змінити дані у вихідній таблиці. Зверніть увагу, що вміст транспонованої таблиці не змінився.
Важливо!
Транспонування таблиць через функцію спеціальної вставки не встановлює зв’язку між джерелом даних та результатом. Після спеціальної вставки обидві таблиці будуть автономними.У цьому й полягає основний недолік першого способу. Тому я вважаю за краще користуватися формулою-масивом. Ось як це зробити.
Спосіб 2. Транспонування даних через формулу-масив:
1) виділіть діапазон комірок на робочому листі для майбутньої транспонованої таблиці;
2) не знімаючи виділення (!), до першої комірки діапазону запишіть формулу «
=Трансп(блок)»;3) як параметр «
блок» уведіть діапазон вихідної таблиці. Зазначити діапазон можна, виділивши його безпосередньо на робочому листі;4) після того як формула готова, натисніть на «
Ctrl+Shift+Enter». Це важливо, оскільки нам потрібна не просто формула, а формула-масив.Усі дані з першої таблиці автоматично відображатимуться в новій, транспонованій таблиці. Відкоригуйте вихідні дані. Усі зміни з’являться у транспонованій таблиці.
Важливо!
Транспонування таблиць через функцію «=Трансп()» установлює зв’язок між джерелом даних і результатом.На завершення теми хочу зробити пару зауважень стосовно другого способу:
1. Вставити функцію «
=Трансп()» ви можете за допомогою Майстра функцій, вибирати адреси комірок можна безпосередньо на робочому листі. Але є один нюанс. Після того як у вікні роботи з Майстром формула готова, не натискуйте на кнопку «ОК», інакше ви отримаєте звичайну формулу, а вона для вирішення завдання не підходить. Не закриваючи вікно Майстра, натисніть на «Ctrl+Shift+Enter», і Excel упровадить на робочий лист формулу-масив.2. Діапазон комірок для транспонованої таблиці бажано вибрати з урахуванням розмірів вихідної таблиці. Наприклад, якщо вихідні дані займали 4 рядки та 3 колонки, то діапазон для транспонованої таблиці має займати 3 колонки та 4 рядки. Якщо цієї вимоги не дотримуватись, а діапазон зазначити «із запасом», нічого страшного не станеться. Просто в зайвих елементах транспонованої таблиці ви побачите значення «
#Н/Д». Це означає, що для відповідних комірок функція «=Трансп()» не виявила даних.Важливо!
Стерти значення «#Н/Д» звичайним способом (наприклад, клавішею «Del») не вдасться. Excel забороняє видаляти елементи формули-масиву.Щоб усунути це обмеження, виконайте такі дії:
1) виділіть транспоновану таблицю;
2) скопіюйте її до буфера обміну («
Ctrl+V» або «Ctrl+Ins»);3) не знімаючи виділення, викличте меню «
Правка → Специальная вставка» (в Excel 2007 це можна зробити, натиснувши на маленький трикутник під іконкою «Вставка»);4) у вікні параметрів спеціальної вставки включіть прапорець «
Значения»;5) натисніть на «
ОК».Тепер ділянка робочого листа з формулою-масивом перетворилася на звичайні значення. Ви зможете виділити непотрібні комірки і стерти їх звичайним способом (наприклад, клавішею «
Del»).От і все. Сподіваюся, що цей матеріал допоможе вирішити проблему.
Чекаю ваших листів, запитань, зауважень та пропозицій на
bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
