Вчора в марафоні 30 функцій Excel за 30 днів ми вважали кількість стовпців в діапазоні, використовуючи функцію COLUMNS (ЧІСЛСТОЛБ). а тепер прийшов час для чогось більш затребуваного.
13-й день марафону ми присвятимо дослідженню функції TRANSPOSE (ТРАНСП). За допомогою цієї функції Ви можете повертати свої дані, перетворюючи вертикальні області в горизонтальні і навпаки. У Вас виникає така необхідність? Чи зможете зробити це, використовуючи спеціальну вставку? Чи зможуть це зробити інші функції?
Функція 13: TRANSPOSE (ТРАНСП)
Функція TRANSPOSE (ТРАНСП) повертає горизонтальний діапазон комірок як вертикальний або навпаки.
Як можна використовувати функцію TRANSPOSE (ТРАНСП)?
Функція TRANSPOSE (ТРАНСП) може змінювати орієнтацію даних, а також працювати спільно з іншими функціями:
- Змінити горизонтальне розташування даних на вертикальне.
- Показати кращий загальний обсяг заробітної плати за останні роки.
Щоб змінити орієнтацію даних, не створюючи посилань на вихідні дані:
- Використовуйте Paste Special (Спеціальна вставка)> Transpose (Транспонувати).
Синтаксис TRANSPOSE (ТРАНСП)
Функція TRANSPOSE (ТРАНСП) має наступний синтаксис:
- array (масив) - це масив або діапазон комірок, які потрібно транспонувати.
Пастки TRANSPOSE (ТРАНСП)
- Функція TRANSPOSE (ТРАНСП) повинна бути введена як формула масиву, натисканням Ctrl + Shift + Enter.
- Діапазон, який вийде в результаті перетворення функцією TRANSPOSE (ТРАНСП), повинен мати таку саму кількість рядків і стовпців, скільки стовпців і рядків відповідно має вихідний діапазон.
Приклад 1: Перетворюємо горизонтальні дані в вертикальні
Якщо на аркуші Excel дані розташовані горизонтально, Ви можете застосувати функцію TRANSPOSE (ТРАНСП), щоб перетворити їх у вертикальне положення, але вже в іншому місці листа. Наприклад, в підсумковій таблиці контрольних показників вертикальне розташування буде більш зручним. Використовуючи функцію TRANSPOSE (ТРАНСП), Ви можете послатися на вихідні горизонтальні дані, не змінюючи їх розташування.
Щоб транспонувати горизонтальний діапазон 2х4 в вертикальний діапазон 4х2:
- Виділіть 8 осередків, куди Ви хочете розмістити отриманий вертикальний діапазон. У нашому прикладі це будуть осередки B4: C7.
- Введіть наступну формулу і перетворите її в формулу масиву натисканням Ctrl + Shift + Enter.
Автоматично будуть додані фігурні дужки на початку і в кінці формули, щоб показати, що введена формула масиву.
Замість TRANSPOSE (ТРАНСП), Ви можете використовувати іншу функцію для перетворення даних, наприклад, INDEX (ЗМІСТ). Вона не потребує введення формули масиву, і Вам не доведеться виділяти всі осередки кінцевої області, при створенні формули.
Приклад 2: Змінюємо орієнтацію без посилань
Якщо Ви просто хочете змінити орієнтацію Ваших даних без збереження посилання на початкові дані, Ви можете використовувати спеціальну вставку:
- Виберіть вихідні дані і скопіюйте їх.
- Виберіть верхнє ліве вічко області, куди необхідно помістити результат.
- На вкладці Home (Головна) натисніть на меню, що випадає команди Paste (Вставити).
- Виберіть Transpose (Транспонувати).
- Видаліть вихідні дані (за бажанням).
Приклад 3: Кращий загальний обсяг заробітної плати за минулі роки
= MAX (MMULT (A8: J8, - (ABS (TRANSPOSE (COLUMN (A8: J8)) - COLUMN (OFFSET (A8: J8,0,0,1, COLUMNS (A8: J8) -Number + 1)) - (Number-1) / 2)
Як можна зрозуміти з фігурним дужках в рядку формул - це формула масиву. Осередок A5 названа Number і в цьому прикладі число 4 введено, як значення для кількості років.
Формула перевіряє діапазони, щоб побачити чи достатньо в них послідовних стовпців. Результати перевірки (1 або 0) множаться на значення осередків, щоб отримати сумарний обсяг заробітної плати.
Для перевірки результату на малюнку нижче в рядку під значеннями зарплат показані сумарні значення для кожної стартовою осередки, при цьому максимальне значення виділено жовтим. Це довший шлях до того ж результату, що попередня формула масиву отримує в одній комірці!
