Selles artiklis õpime, kuidas Excelis funktsiooni OFFSET kasutada.
Mis on OFFSET funktsioon?
Nihkefunktsioon on teabe otsimise funktsioon. Kui teil on tohutu tabel, mis sisaldab teatud andmeid ja soovite sealt andmeid hankida, on parim viis seda teha, kui sisestate selle töölehe või sama töövihiku mõne muu lehe tabeli nihkevalemi. Seega, kui olete nihkeotsingu tabeli loonud, peate lihtsalt sisestama andmed, näiteks „kuu”, ja saate selle kuu „müügi” või „tulu”.
Tabeli läbimine on veel üks võimalus, kuid kas kaaluksite seda võimalust, kui teil on andmelehed ja -lehed, mis kõik sisaldavad 1000 veeru andmetabelit? Siin ühendatakse nihutamine teiste funktsioonidega.
Pöördtabeli mõiste pärineb nihkega. Pöördtabel on üldine tabel ja sellest saadakse andmete ja graafikute konkreetne kombinatsioon vastavalt vajadusele. Ka dünaamilisi tabeleid saab luua samal viisil. Nende tabelite jaoks peate looma Exceli nihke valemi
OFFSET süntaks
| = OFFSET (viide, read, veerud, [kõrgus], [laius]) |
Ridad - palute Excelil teabe hankimiseks ridade arvu nihutada. Sel juhul peab see liikuma laua ümber ja jätma lihtsalt tühiku, mis on tähistatud komaga (,). Vastasel juhul anna veeru edasiliikumiseks väärtus 1 ja enne veeru teisaldamine väärtus -1.
Veerud - see on funktsiooni veergude arvu suurendamiseks. Väärtussüsteem on sama, mis reas.
Kõrgus - laua kõrgus, antud juhul A11.
Laius - laua laius, antud juhul D11.
Pärast nihkefunktsiooni seadistamist on vigade kõrvaldamiseks oluline katse käivitamine.
Võtame tabeli, mis sisaldab nime, e -posti aadressi, sünniaega ja vanust. Tabel algab lahtrist A1, mis sisaldab pealkirja „Nimi”. Andmed töötavad näiteks A11 kohta. Ja read kulgevad kuni D1. Kogu tabel kulgeb A1: D11. Soovite allalaadimissüsteemi, mis annab teile nime, kui sisestate e -posti ID, või e -posti ID, kui sisestate nime koos muude andmetega. Valemi koostamiseks rahuldage 5 argumenti.
Võrdluspunkt on lahter, millest otsimine peaks algama. Sel juhul peaks see olema A2. See on üldine standard.
Näide:
Näiteks kui annate viite B4 -le ja nihke reale 0 ning nihkeveerule 1, tagastatakse väärtus C4 -st. Segane? Toome veel mõned näited. Funktsiooni OFFSET viimane näide ütleb, kuidas dünaamiliselt summeerida.
Nihkefunktsioon väärtuse saamiseks lahtri paremalt ja vasakult
Meil on see tabel.
Kui ma tahan saada väärtuse kahest lahtrist otse B2 -ni (mis tähendab D2). Kirjutan selle OFFSET valemi:
| = Nihe (B2,0,2) |
Funktsioon OFFSET nihutab 2 lahtrit lahtrist B4 paremale ja tagastab selle väärtuse. Vaata pilti allpool
Kui ma tahan saada väärtust 1 lahtrist vasakule B2 -ni (mis tähendab A2). Kirjutan selle OFFSET valemi:
| = Nihe (B2,0, -1) |
Miinusmärk (-) näitab tagurpidi liikumise nihet.
Funktsioon OFFSET väärtuse saamiseks lahtri alt ja ülalt
Nii et jällegi ülaltoodud tabelis, kui ma tahan saada väärtust 2 -rakulisest sisust B3 -ni (mis tähendab B5). Kirjutan selle OFFSET valemi:
| = Nihe (B2,2,0) |
Kui ma tahan saada väärtust 1 lahtrist vasakule B2 -ni (mis tähendab A2). Kirjutan selle OFFSET valemi:
| = Nihe (B2, -2,0) |
- Pro näpunäide: Käsitlege NIHET graafiku osutajana, kus viide on lähtekoht ning rida ja veerg x ja y telg.
OFFSET Funktsioon Sum Range dünaamiliselt
Vaatame seda näidet.
Ülalolevas tabelis tabeli lõpus rida kokku. Aja jooksul lisate sellele tabelile ridu. Siis peate muutma valemi summa vahemikku. Siin saame dünaamiliselt summeerida funktsiooni OFFSET funktsiooni. Praegu on meil lahtris C11 = SUM (C2: C10). Asendage see alloleva valemiga.
| = SUMMA (C2: nihe (C11, -1,0)) |
See valem võtab lihtsalt esimese andmerida ja summeerib seejärel kogu rea kohal oleva vahemiku.
Nihe massiivi saamiseks
Funktsioonil OFFSET on kaks valikulist argumenti: [kõrgus] ja [laius]. Ärge võtke neid enesestmõistetavaks. Kui funktsioon OFFSET on ette nähtud [kõrgus] ja [laius], tagastab see kahemõõtmelise massiivi. Kui argumendina edastatakse ainult üks neist, naaseb see mõõtmete väärtuste massiivile.
Kui sisestate selle valemi suvalisse lahtrisse:
| = SUMMA (nihe (C1,1,0,9,3)) |
See summeerib väärtused vahemikus C2 kuni 9 rida allpool ja 3 veergu paremal.
Nihke (C1,1,0,9,3): See tähendab {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Kokkuvõtteks võib öelda, et nihutamine on Exceli väga kasulik funktsioon, mis aitab teil lahendada paljusid kägistatud teemasid. Andke mulle teada, kuidas te oma valemis funktsiooni OFFSET kasutate ja kus see teid kõige rohkem aitas.
Otsimisfunktsioon on sama mis nihutamine, kuid kasutab dünaamilise tabeli töötamiseks selliseid funktsioone nagu Match, Counta ja IF.
Nihke kasutamine Excelis nõuab oskusi ja häid teadmisi Exceli funktsioonidest ja nende koostoimimisest. Selle tõrjumine ilma veata on suurim takistus.
Siin on kõik tähelepanekud valemi kasutamise kohta.
Märkused:
- See valem töötab nii teksti kui ka numbritega.
- Vea vaba teabe saamiseks tuleb tasakaalustada viis argumenti. Kui kirjutate vale valemi, ilmneb viide Ref.
Loodetavasti mõistsite, mis on Exceli nihkefunktsioon ja kuidas seda Excelis kasutada. Siit leiate rohkem artikleid Exceli otsingu ja väärtuste sobitamise kohta, kasutades siin valemeid. Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile.
Funktsioon VLOOKUP Exceli hinde arvutamiseks : Klasside arvutamiseks ei ole IF ja IFS ainsad funktsioonid, mida saate kasutada. VLOOKUP on selliste tingimuslike arvutuste jaoks tõhusam ja dünaamilisem. Hinnete arvutamiseks VLOOKUPi abil saame kasutada seda valemit.
17 asja Exceli kohta VLOOKUP : VLOOKUP -i kasutatakse kõige sagedamini sobitatud väärtuste toomiseks, kuid VLOOKUP saab teha palju enamat. Siin on 17 asja VLOOKUPi kohta, mida peaksite tõhusaks kasutamiseks teadma.
Otsi üles esimene tekst loendist Excelis : Funktsioon VLOOKUP töötab metamärkidega hästi. Me saame seda kasutada Exceli antud loendist esimese tekstiväärtuse eraldamiseks. Siin on üldine valem.
OTSINGU kuupäev loendis viimase väärtusega : Viimast väärtust sisaldava kuupäeva toomiseks kasutame funktsiooni LOOKUP. See funktsioon otsib lahtrit, mis sisaldab vektori viimast väärtust, ja kasutab seejärel seda viidet kuupäeva tagastamiseks.
Kuidas Excelis uusimat hinda hankida : Igas ettevõttes on tavaline hindu värskendada ja iga ostu või müügi puhul tuleb kasutada uusimaid hindu. Viimase hinna hankimiseks Exceli loendist kasutame funktsiooni LOOKUP. Funktsioon LOOKUP toob viimase hinna.
Populaarsed artiklid:
50 Exceli otsetee tootlikkuse suurendamiseks : Saate oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Funktsiooni IF kasutamine Excelis : Exceli IF -lause kontrollib tingimust ja tagastab konkreetse väärtuse, kui tingimus on TRUE, või tagastab mõne muu väärtuse, kui FALSE.
Funktsiooni VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.