Функції зв’язку та транспонування в Excel є взаємовиключними. Це означає, що транспоновані клітинки не працюватимуть як посилання на вашому аркуші. Іншими словами, будь-які зміни, які ви вносите до вихідних клітинок, не відображаються в транспонованій копії.
Однак у ваших проектах часто потрібно як транспонувати, так і зв’язати клітинки/стовпці. Отже, чи є спосіб використовувати обидві функції? Звичайно, є, і ми представимо вам чотири різні методи, як це зробити.
Можна з упевненістю сказати, що ці прийоми є частиною середнього рівня знань Excel, але якщо ви дотримуєтеся кроків до T, не буде жодних проб і помилок, навіть якщо ви повний новачок.
Проблема вставки
Для цілей цієї статті припустимо, що ви хочете транспонувати стовпці на той самий аркуш. Так, що ти робиш? Виділіть стовпці, натисніть Ctrl + C (Cmd + C на Mac) і виберіть місце призначення. Потім клацніть параметри «Вставити», виберіть «Спеціальна вставка» та поставте галочку навпроти «Транспонувати».
Але як тільки ви поставите галочку, Вставити посилання стане сірим. На щастя, є деякі формули та прийоми, які допоможуть вам вирішити цю проблему. Давайте розглянемо їх.
TRANSPOSE – Формула масиву
Основна перевага цієї формули полягає в тому, що вам не потрібно вручну перетягувати клітинки. Однак він має певні мінуси. Наприклад, розмір змінити непросто, а це означає, що вам потрібно знову використовувати формулу, якщо зміниться діапазон вихідних клітинок.
Однак подібні проблеми стосуються інших формул масиву, і ця допоможе вам вирішити проблему транспонування посилання досить швидко.
Крок 1
Скопіюйте клітинки та клацніть верхню ліву клітинку в області, до якої потрібно вставити клітинки. Натисніть Ctrl + Alt + V, щоб відкрити вікно спеціальної вставки. Ви також можете зробити це з панелі інструментів Excel.
Крок 2
Відкривши вікно, поставте прапорець «Формати» у розділі «Вставити», виберіть «Транспонувати» в нижньому правому куті та натисніть «ОК». Ця дія транспонує лише форматування, а не значення, і це потрібно зробити з двох причин. По-перше, ви знаєте діапазон транспонованих клітинок. По-друге, ви зберігаєте оригінальний формат клітинок.
Крок 3
Необхідно виділити всю область вставки, і ви можете зробити це після вставки форматів. Тепер введіть =TRANSPOSE ("Початковий діапазон") і натисніть Ctrl + Shift + Enter.
Примітка: Важливо натиснути Enter разом із Ctrl і Shift. В іншому випадку програма не розпізнає команду правильно і автоматично створює фігурні дужки.
Посилання та транспонування – ручний метод
Так, Excel пов’язаний з автоматизацією та використанням функцій, які полегшують маніпулювання клітинками та стовпцями. Однак, якщо ви маєте справу з досить малим діапазоном комірок, ручне зв’язування та транспонування часто є найшвидшим рішенням. Правда, якщо ви недостатньо обережні, є можливість для помилок.
Крок 1
Виділіть клітинки та скопіюйте/вставте їх за допомогою параметра «Спеціальна вставка». Цього разу ви не ставите галочку навпроти Transpose і залишаєте параметри під Вставити за замовчуванням.
Крок 2
Натисніть кнопку Вставити посилання внизу ліворуч, і ваші дані будуть вставлені у вигляді посилань.
Крок 3
Тут настає важка частина. Вам потрібно вручну перетягнути клітинки в нову область. У той же час потрібно бути обережним з обміном рядків і стовпців.
Формула зміщення
Це один з найпотужніших інструментів для вставки клітинок, зв’язування та транспонування їх. Однак це може бути непросто, якщо ви новачок у Excel, тому ми постараємося зробити кроки максимально зрозумілими.
Крок 1
Потрібно підготувати цифри зліва і зверху. Наприклад, якщо є три рядки, ви використовуєте 0-2, а якщо є два стовпці, ви використовуєте 0-1. Метод – це загальна кількість рядків і стовпців мінус 1.
Крок 2
Далі потрібно знайти і визначити базову комірку. Ця клітинка повинна залишатися неушкодженою, коли ви копіюєте/вставляєте, і саме тому ви використовуєте спеціальні символи для комірки. Скажімо, базовою клітинкою є B2: вам потрібно буде вставити знак долара, щоб виділити цю клітинку. У формулі це має виглядати так: =ЗМІЩЕННЯ($B$2.
Крок 3
Тепер вам потрібно визначити відстань (у рядках) між базовою та цільовою комірками. Це число має збільшуватися, коли ви переміщуєте формулу вправо. З цієї причини перед стовпцем функції не повинно бути жодного знака долара. Замість цього перший рядок фіксується знаком долара.
Наприклад, якщо функція знаходиться в стовпці F, вона має виглядати так: =OFFSET($B$2, F$1.
Крок 4
Як і рядки, стовпці також мають збільшуватися після зв’язування та транспонування. Ви також використовуєте знак долара, щоб зафіксувати один стовпець, але дозволяєте рядкам збільшуватися. Щоб це було зрозуміло, краще звернутися до прикладу, який може виглядати так: =OFFSET($B$2, F$1, $E2).
Як Excel в Excel
Окрім зазначеного методу, існують також сторонні інструменти, які можна використовувати для зв’язування та транспонування. І якщо дані методи не дають задовільних результатів, можливо, краще використовувати один із таких інструментів.
Чи використовували ви одну з цих програм транспонування/зв’язування для виконання цієї операції в Excel? Ви були задоволені результатом? Поділіться своїм досвідом у розділі коментарів нижче.
