Sisu
Eile maratonis 30 Exceli funktsiooni 30 päevaga loendasime funktsiooni abil veergude arvu vahemikus Kolonnid (NUMBERCOLUMN) ja nüüd on aeg millegi nõudluse järele.
Maratoni 13. päeval pühendume funktsiooni uurimisele ÜLESANDMINE (TRANSP). Selle funktsiooniga saate oma andmeid pöörata, muutes vertikaalsed alad horisontaalseteks ja vastupidi. Kas teil on selline vajadus? Kas saate seda teha spetsiaalse vahetükiga? Kas teised funktsioonid saavad seda teha?
Niisiis, pöördume funktsiooni teabe ja näidete poole ÜLESANDMINE (TRANSP). Kui teil on lisateavet või näiteid, jagage neid kommentaarides.
Funktsioon 13: ÜLEKANDE
funktsioon ÜLESANDMINE (TRANSPOSE) tagastab horisontaalse lahtrivahemiku vertikaalse vahemikuna või vastupidi.
Kuidas saab funktsiooni TRANSPOSE kasutada?
funktsioon ÜLESANDMINE (TRANSP) saab muuta andmete orientatsiooni ja töötada koos muude funktsioonidega:
- Muutke andmete horisontaalne paigutus vertikaalseks.
- Näidake viimaste aastate parimat kogupalka.
Andmete orientatsiooni muutmiseks ilma algandmetega linke loomata tehke järgmist.
- Kasutama kleebi Special (Eriline pasta) > Üleminek (Transponeerida).
Süntaks TRANSPOSE (TRANSP)
funktsioon ÜLESANDMINE (TRANSPOSE) on järgmise süntaksiga:
TRANSPOSE(array)
ТРАНСП(массив)
- massiivi (massiiv) on ülekantavate lahtrite massiiv või vahemik.
Püünised TRANSPOSE (TRANSPOSE)
- funktsioon ÜLESANDMINE (TRANSPOSE) tuleb sisestada massiivivalemina, vajutades Ctrl + Tõstuklahv + Sisesta.
- Vahemik, mis tuleneb funktsiooni teisendusest ÜLESANDMINE (TRANSPOSE) peab sisaldama sama arvu ridu ja veerge, kui algses vahemikus on vastavalt veerge ja ridu.
Näide 1: Horisontaalsete andmete muutmine vertikaalandmeteks
Kui andmed on Exceli lehel horisontaalsed, saate funktsiooni rakendada ÜLESANDMINE (TRANSPOSE), et teisendada need vertikaalasendisse, kuid lehe teises kohas. Näiteks võrdlusaluste lõpptabelis oleks vertikaalne paigutus mugavam. Funktsiooni kasutamine ÜLESANDMINE (TRANSPOSE) saate viidata algsetele horisontaalandmetele ilma nende asukohta muutmata.
Horisontaalse vahemiku transponeerimiseks 2 × 4 vertikaalsesse vahemikku 4 × 2:
- Valige 8 lahtrit, kuhu soovite saadud vertikaalse vahemiku paigutada. Meie näites on need lahtrid B4:C7.
- Sisestage järgmine valem ja muutke see klõpsuga massiivivalemiks Ctrl + Tõstuklahv + Sisesta.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Valemi algusesse ja lõppu lisatakse automaatselt lokkis sulud, mis näitavad, et massiivi valem on sisestatud.
Asemel ÜLESANDMINE (TRANSPOSE), saate andmete teisendamiseks kasutada mõnda muud funktsiooni, näiteks INDEX (INDEKS). See ei nõua massiivivalemi sisestamist ja valemi loomisel ei pea valima kõiki sihtala lahtreid.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Näide 2: muutke orientatsiooni ilma linkideta
Kui soovite lihtsalt muuta oma andmete orientatsiooni ilma viidet algandmetele säilitamata, võite kasutada käsku Paste Special:
- Valige lähteandmed ja kopeerige need.
- Valige selle ala vasakpoolne ülemine lahter, kuhu soovite tulemuse paigutada.
- Vahekaardil Täpsemalt Avaleht (Kodu) klõpsake käsu rippmenüül pasta (Sisesta).
- valima Üleminek (Transponeerida).
- Kustutage algandmed (valikuline).
Näide 3: Parim kogupalk viimastel aastatel
funktsioon ÜLESANDMINE (TRANSP) saab kasutada koos teiste funktsioonidega, näiteks selle uimastatava valemiga. Selle postitas Harlan Grove Exceli uudisteblokis arutelus viimase 5 aasta (järjekorras!) parima kogupalga arvutamise üle.
=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 названа Number и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объётый.плом
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения дло показанке для этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!
