Saturs
Vakar maratonā 30 Excel funkcijas 30 dienās mēs saskaitījām kolonnu skaitu diapazonā, izmantojot funkciju COLUMNS (NUMBERCOLUMN), un tagad ir pienācis laiks kaut kam pieprasītākam.
Maratona 13. dienā veltīsimies funkcijas izpētei TRANSPOSĒT (TRANSP). Izmantojot šo funkciju, varat pagriezt savus datus, pārvēršot vertikālos apgabalus horizontālos un otrādi. Vai jums ir tāda vajadzība? Vai to var izdarīt, izmantojot īpašu ieliktni? Vai citas funkcijas to var izdarīt?
Tātad, pievērsīsimies informācijai un piemēriem par funkciju TRANSPOSĒT (TRANSP). Ja jums ir papildu informācija vai piemēri, lūdzu, kopīgojiet tos komentāros.
13. funkcija: TRANSPONĒŠANA
funkcija TRANSPOSĒT (TRANSPOSE) atgriež horizontālu šūnu diapazonu kā vertikālu diapazonu vai otrādi.
Kā var izmantot funkciju TRANSPOSE?
funkcija TRANSPOSĒT (TRANSP) var mainīt datu orientāciju, kā arī darboties kopā ar citām funkcijām:
- Mainiet datu horizontālo izkārtojumu uz vertikālo.
- Parādiet labāko kopējo algu pēdējo gadu laikā.
Lai mainītu datu orientāciju, neveidojot saites uz sākotnējiem datiem:
- lietošana paste Special (Īpašā pasta) > Transpose (Transponēt).
Sintakse TRANSPOSE (TRANSP)
funkcija TRANSPOSĒT (TRANSPOSE) ir šāda sintakse:
TRANSPOSE(array)
ТРАНСП(массив)
- masīvs (masīvs) ir transponējamo šūnu masīvs vai diapazons.
Slazdi TRANSPOSE (TRANSPOSE)
- funkcija TRANSPOSĒT (TRANSPOSE) jāievada kā masīva formula, nospiežot Ctrl + Shift + Enter.
- Diapazons, kas tiks iegūts funkcijas pārveidošanas rezultātā TRANSPOSĒT (TRANSPOSE) ir jābūt tādam pašam rindu un kolonnu skaitam, cik sākotnējā diapazonā ir attiecīgi kolonnas un rindas.
1. piemērs. Horizontālo datu pārvēršana vertikālos datos
Ja Excel lapā dati ir horizontāli, varat lietot funkciju TRANSPOSĒT (TRANSPOZE), lai pārvērstu tos vertikālā stāvoklī, bet citā lapas vietā. Piemēram, galīgajā etalonu tabulā ērtāks būtu vertikālais izvietojums. Izmantojot funkciju TRANSPOSĒT (TRANSPOZE), varat atsaukties uz sākotnējiem horizontālajiem datiem, nemainot to atrašanās vietu.
Lai transponētu horizontālo diapazonu 2 × 4 vertikālajā diapazonā 4 × 2:
- Atlasiet 8 šūnas, kurās vēlaties novietot iegūto vertikālo diapazonu. Mūsu piemērā tās būs šūnas B4:C7.
- Ievadiet tālāk norādīto formulu un pārveidojiet to par masīva formulu, noklikšķinot Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Formulas sākumā un beigās automātiski tiks pievienotas cirtainas iekavas, lai norādītu, ka ir ievadīta masīva formula.
Tā vietā, lai TRANSPOSĒT (TRANSPOSE), varat izmantot citu funkciju, lai pārveidotu datus, piemēram, INDEX (INDEKSS). Tam nav jāievada masīva formula, un, veidojot formulu, nav jāatlasa visas šūnas mērķa apgabalā.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
2. piemērs. Mainiet orientāciju bez saitēm
Ja vēlaties tikai mainīt savu datu orientāciju, nesaglabājot atsauci uz sākotnējiem datiem, varat izmantot Īpašā ielīmēšana:
- Atlasiet avota datus un kopējiet tos.
- Atlasiet apgabala augšējo kreiso šūnu, kurā vēlaties ievietot rezultātu.
- Cilnē Papildu Sākumlapa (Sākums) noklikšķiniet uz komandu nolaižamās izvēlnes makaroni (Ievietot).
- atlasīt Transpose (Transponēt).
- Dzēsiet sākotnējos datus (pēc izvēles).
3. piemērs: labākā kopējā alga pēdējos gados
funkcija TRANSPOSĒT (TRANSP) var izmantot kopā ar citām funkcijām, piemēram, šajā satriecošajā formulā. To ievietoja Harlan Grove programmā Excel News Bloc diskusijā par labākās kopējās algas aprēķināšanu pēdējo 5 gadu laikā (pēc kārtas!).
=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 названа Skaits и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объётной.плом
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для показаны, суммарные значения дло этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!
