зміст
Вчора на марафоні 30 функцій Excel за 30 днів ми підрахували кількість стовпців у діапазоні за допомогою функції КОЛОНИ (NUMBERCOLUMN), а тепер настав час чогось більш затребуваного.
13-й день марафону ми присвятимо вивченню функції ТРАНЗОЗУВАТИ (ТРАНСПОРТ). За допомогою цієї функції ви можете обертати дані, перетворюючи вертикальні області на горизонтальні та навпаки. У вас є така потреба? Чи можна це зробити за допомогою спеціальної вставки? Чи можуть це зробити інші функції?
Отже, звернемося до інформації та прикладів по функції ТРАНЗОЗУВАТИ (ТРАНСПОРТ). Якщо у вас є додаткова інформація чи приклади, поділіться ними в коментарях.
Функція 13: ТРАНСПОНУВАННЯ
функція ТРАНЗОЗУВАТИ (TRANSPOSE) повертає горизонтальний діапазон клітинок як вертикальний діапазон або навпаки.
Як можна використовувати функцію TRANSPOSE?
функція ТРАНЗОЗУВАТИ (TRANSP) може змінювати орієнтацію даних, а також працювати в поєднанні з іншими функціями:
- Змініть горизонтальне розташування даних на вертикальне.
- Покажіть найкращу загальну заробітну плату за останні роки.
Щоб змінити орієнтацію даних без створення посилань на вихідні дані:
- Скористайтесь паста Спец (Спеціальна паста) > Транспонувати (Транспонувати).
Синтаксис TRANSPOSE (TRANSP)
функція ТРАНЗОЗУВАТИ (TRANSPOSE) має такий синтаксис:
TRANSPOSE(array)
ТРАНСП(массив)
- масив (масив) — це масив або діапазон клітинок, які потрібно транспонувати.
Пастки TRANSPOSE (ТРАНСПОЗУВАННЯ)
- функція ТРАНЗОЗУВАТИ (TRANSPOSE) потрібно ввести як формулу масиву, натиснувши Ctrl + Shift + Enter.
- Діапазон, який буде результатом перетворення функцією ТРАНЗОЗУВАТИ (TRANSPOSE) має мати таку саму кількість рядків і стовпців, як початковий діапазон має стовпці та рядки відповідно.
Приклад 1: Перетворення горизонтальних даних на вертикальні
Якщо дані горизонтальні на аркуші Excel, ви можете застосувати функцію ТРАНЗОЗУВАТИ (TRANSPOSE), щоб перетворити їх у вертикальне положення, але в інше місце на аркуші. Наприклад, у підсумковій таблиці бенчмарків зручніше було б вертикальне розташування. Використання функції ТРАНЗОЗУВАТИ (TRANSPOSE), ви можете посилатися на оригінальні горизонтальні дані, не змінюючи їх розташування.
Щоб транспонувати горизонтальний діапазон 2 × 4 у вертикальний діапазон 4 × 2:
- Виділіть 8 комірок, де ви хочете розмістити отриманий вертикальний діапазон. У нашому прикладі це будуть комірки B4:C7.
- Введіть наступну формулу та перетворіть її на формулу масиву, клацнувши Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Фігурні дужки будуть автоматично додані на початку та в кінці формули, щоб вказати, що було введено формулу масиву.
Замість ТРАНЗОЗУВАТИ (TRANSPOSE), Ви можете використовувати іншу функцію для перетворення даних, наприклад, ІНДЕКС (ІНДЕКС). Вам не потрібно вводити формулу масиву, і вам не потрібно виділяти всі клітинки в цільовій області під час створення формули.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Приклад 2: зміна орієнтації без посилань
Якщо ви просто хочете змінити орієнтацію своїх даних, не зберігаючи посилання на вихідні дані, ви можете скористатися спеціальною вставкою:
- Виберіть вихідні дані та скопіюйте їх.
- Виберіть верхню ліву комірку області, куди потрібно розмістити результат.
- На вкладці Додатково Головна (Додому) клацніть спадне меню команд вставляти (Вставка).
- Виберіть Транспонувати (Транспонувати).
- Видалити вихідні дані (необов’язково).
Приклад 3: Найкраща загальна зарплата за минулі роки
функція ТРАНЗОЗУВАТИ (TRANSP) можна використовувати в поєднанні з іншими функціями, наприклад, у цій приголомшливій формулі. Його опублікував Харлан Гроув у блоці новин Excel під час обговорення розрахунку найкращої загальної заробітної плати за останні 5 років (поспіль!).
=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Як можна поняти по фігурним скобкам у рядку формули – це формула масиву. Ячейка A5 названа Номер і в цьому прикладі число 4 введено, як значення для кількості років.
Формула перевіряє діапазони, щоб побачити достатньо чи в них послідовних столбців. Результати перевірки (1 або 0) множаться на значення ячеек, щоб отримати сумарний об'єм заробітної плати.
Для перевірки результатів на рисунку нижче в рядку під значеннями зарплати показані суммарні значення для кожної стартової ячейки, при цьому максимальне значення виділено жовтим. Це більш довгий шлях до того ж результату, що попередня формула масиву отримує в одній ячейці!
