Cuprins
Ieri la maraton 30 de funcții Excel în 30 de zile am numărat numărul de coloane din interval folosind funcția COLOANE (NUMBERCOLUMN), iar acum este timpul pentru ceva mai solicitat.
În a 13-a zi a maratonului, ne vom dedica studiului funcției TRANSPUNE (TRANSP). Cu această funcție, puteți roti datele transformând zonele verticale în zone orizontale și invers. Ai o astfel de nevoie? Puteți face acest lucru folosind o inserție specială? O pot face și alte funcții?
Deci, să trecem la informații și exemple despre funcție TRANSPUNE (TRANSP). Dacă aveți informații suplimentare sau exemple, vă rugăm să le împărtășiți în comentarii.
Funcția 13: TRANSPONERE
Funcţie TRANSPUNE (TRANSPOSE) returnează un interval orizontal de celule ca interval vertical sau invers.
Cum poate fi utilizată funcția TRANSPOSE?
Funcţie TRANSPUNE (TRANSP) poate modifica orientarea datelor, precum și poate lucra împreună cu alte funcții:
- Schimbați aspectul orizontal al datelor în vertical.
- Afișați cele mai bune salarii totale din ultimii ani.
Pentru a schimba orientarea datelor fără a crea legături către datele originale:
- Utilizare pastă specială (Pastă specială) > Transpune (Transpune).
Sintaxă TRANSPOSE (TRANSP)
Funcţie TRANSPUNE (TRANSPOSE) are următoarea sintaxă:
TRANSPOSE(array)
ТРАНСП(массив)
- mulțime (matrice) este matricea sau intervalul de celule care trebuie transpus.
Capcane TRANSPUSE (TRANSPONERE)
- Funcţie TRANSPUNE (TRANSPOSE) trebuie introdus ca formulă matrice, prin apăsare Ctrl + Shift + Enter.
- Intervalul care va rezulta din transformarea de catre functie TRANSPUNE (TRANSPOSE) trebuie să aibă același număr de rânduri și coloane precum intervalul original are coloane și, respectiv, rânduri.
Exemplul 1: Transformarea datelor orizontale în date verticale
Dacă datele sunt orizontale într-o foaie Excel, puteți aplica funcția TRANSPUNE (TRANSPONERE) pentru a le converti într-o poziție verticală, dar într-un loc diferit de pe foaie. De exemplu, în tabelul final de repere, o aranjare verticală ar fi mai convenabilă. Folosind funcția TRANSPUNE (TRANSPOSE), puteți face referire la datele orizontale originale fără a le schimba locația.
Pentru a transpune intervalul orizontal 2 × 4 în intervalul vertical 4 × 2:
- Selectați 8 celule unde doriți să plasați intervalul vertical rezultat. În exemplul nostru, acestea vor fi celulele B4:C7.
- Introduceți următoarea formulă și transformați-o într-o formulă matrice făcând clic Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Acoladele vor fi adăugate automat la începutul și la sfârșitul formulei pentru a indica faptul că a fost introdusă o formulă matrice.
În loc de TRANSPUNE (TRANSPOSE), Puteți utiliza o altă funcție pentru a transforma datele, de exemplu, INDEX (INDEX). Nu necesită să introduceți o formulă matrice și nu trebuie să selectați toate celulele din zona țintă atunci când creați formula.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Exemplul 2: Schimbați orientarea fără linkuri
Dacă doriți doar să schimbați orientarea datelor fără a păstra o referință la datele originale, puteți utiliza Paste Special:
- Selectați datele sursă și copiați-le.
- Selectați celula din stânga sus a zonei în care doriți să plasați rezultatul.
- În fila Avansat Acasă (Acasă) faceți clic pe meniul derulant de comandă lipi (Introduce).
- selecta Transpune (Transpune).
- Ștergeți datele originale (opțional).
Exemplul 3: Cel mai bun salariu total din ultimii ani
Funcţie TRANSPUNE (TRANSP) poate fi utilizat în combinație cu alte caracteristici, cum ar fi în această formulă uimitoare. A fost postat de Harlan Grove în Excel News Bloc într-o discuție despre calcularea celui mai bun salariu total din ultimii 5 ani (la rând!).
=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 названа Număr и в этом примере число 4 ведено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных стовлбх. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить сумтомарнѱ сумтарния ячеек
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получаетой веочает вечает вет!
