Turinys
Vakar maratone 30 Excel funkcijų per 30 dienų suskaičiavome stulpelių skaičių diapazone naudodami funkciją KOLONOS (NUMBERCOLUMN), o dabar atėjo laikas kažkam paklausesniam.
13 maratono dieną skirsime funkcijos studijoms TRANSPOSUOTI (TRANSP). Naudodami šią funkciją galite pasukti duomenis, vertikalias sritis paversdami horizontaliomis ir atvirkščiai. Ar turite tokį poreikį? Ar galite tai padaryti naudodami specialų įdėklą? Ar kitos funkcijos gali tai padaryti?
Taigi, pereikime prie informacijos ir pavyzdžių apie funkciją TRANSPOSUOTI (TRANSP). Jei turite papildomos informacijos ar pavyzdžių, pasidalykite jais komentaruose.
13 funkcija: PERKELIMAS
Funkcija TRANSPOSUOTI (TRANSPOSE) grąžina horizontalų langelių diapazoną kaip vertikalų diapazoną arba atvirkščiai.
Kaip galima naudoti funkciją TRANSPOSE?
Funkcija TRANSPOSUOTI (TRANSP) gali pakeisti duomenų orientaciją, taip pat veikti kartu su kitomis funkcijomis:
- Pakeiskite horizontalų duomenų išdėstymą į vertikalų.
- Parodykite geriausią bendrą pastarųjų metų darbo užmokestį.
Norėdami pakeisti duomenų orientaciją nesukūrę nuorodų į pradinius duomenis:
- naudojimas paste Special (Speciali pasta) > Perkelti (Perkelti).
Sintaksė TRANSPOSE (TRANSP)
Funkcija TRANSPOSUOTI (TRANSPOSE) turi tokią sintaksę:
TRANSPOSE(array)
ТРАНСП(массив)
- masyvas (masyvas) yra ląstelių, kurias reikia perkelti, masyvas arba diapazonas.
Spąstai TRANSPOSE (TRANSPOSE)
- Funkcija TRANSPOSUOTI (TRANSPOSE) turi būti įvesta kaip masyvo formulė, paspausdami Ctrl + Shift + Enter.
- Diapazonas, kuris atsiras transformavus pagal funkciją TRANSPOSUOTI (TRANSPOSE) turi turėti tiek pat eilučių ir stulpelių, kiek pradiniame diapazone yra atitinkamai stulpelių ir eilučių.
1 pavyzdys: horizontalių duomenų pavertimas vertikaliais
Jei „Excel“ lape duomenys yra horizontaliai, galite taikyti funkciją TRANSPOSUOTI (TRANSPOSE), kad konvertuotumėte juos į vertikalią padėtį, bet kitoje lapo vietoje. Pavyzdžiui, galutinėje etalonų lentelėje vertikalus išdėstymas būtų patogesnis. Naudojant funkciją TRANSPOSUOTI (TRANSPOSE), galite nurodyti pradinius horizontalius duomenis nekeisdami jų vietos.
Norėdami perkelti horizontalųjį diapazoną 2 × 4 į vertikalų diapazoną 4 × 2:
- Pasirinkite 8 langelius, kuriuose norite įdėti gautą vertikalų diapazoną. Mūsų pavyzdyje tai bus langeliai B4:C7.
- Įveskite šią formulę ir paverskite ją masyvo formule spustelėdami Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Formulės pradžioje ir pabaigoje bus automatiškai pridėti garbanoti skliaustai, rodantys, kad įvesta masyvo formulė.
Vietoj TRANSPOSUOTI (TRANSPOSE), duomenims transformuoti galite naudoti kitą funkciją, pvz. RODYKLĖ (INDEKSAS). Tam nereikia įvesti masyvo formulės ir kurdami formulę neturite pasirinkti visų tikslinės srities langelių.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
2 pavyzdys: pakeiskite orientaciją be nuorodų
Jei tiesiog norite pakeisti savo duomenų orientaciją nepalikdami nuorodos į pradinius duomenis, galite naudoti specialų įklijavimą:
- Pasirinkite šaltinio duomenis ir nukopijuokite juos.
- Pasirinkite viršutinį kairįjį langelį srityje, kurioje norite įdėti rezultatą.
- Skirtuke Išplėstinė Pagrindinis (Pagrindinis) spustelėkite komandų išskleidžiamąjį meniu įklijuoti (Įdėti).
- pasirinkti Perkelti (Perkelti).
- Ištrinkite pradinius duomenis (neprivaloma).
3 pavyzdys: geriausias bendras atlyginimas per pastaruosius metus
Funkcija TRANSPOSUOTI (TRANSP) gali būti naudojamas kartu su kitomis funkcijomis, pavyzdžiui, šioje nuostabioje formulėje. Ją paskelbė Harlanas Grove'as „Excel News Bloc“ diskusijoje apie geriausio bendro darbo užmokesčio apskaičiavimą per pastaruosius 5 metus (iš eilės!).
=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 названа Skaičius и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объётый.плом зараплоек
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения длойпочестакения для е выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!
