Excels OFFSET-funktion nedsætter beregningen af din projektmappe. Der er et bedre alternativ: en usædvanlig syntaks af INDEX.
Dette er et nichetip. Der er en utrolig fleksibel funktion kaldet OFFSET. Det er fleksibelt, fordi det kan pege på et interval i en anden størrelse, der beregnes on-the-fly. I billedet nedenfor, hvis nogen ændrer rullemenuen # Qtrs i H1 fra 3 til 4, vil det fjerde argument i OFFSET sikre, at området udvides til at omfatte fire kolonner.
Regnearkguruer hader OFFSET, fordi det er en ustabil funktion. Hvis du går til en helt uafhængig celle og indtaster et tal, beregnes alle OFFSET-funktionerne. Selvom den celle ikke har noget at gøre med H1 eller B2. Det meste af tiden er Excel meget forsigtig med kun at spilde tid på at beregne de celler, der skal beregnes. Men når du først har introduceret OFFSET, begynder alle OFFSET-celler plus alt downline fra OFFSET at beregne efter hver ændring i regnearket.
Jeg bedømte 2013 ModelOff-finalen i New York City, da et par af mine venner fra Australien påpegede en bizar løsning. I nedenstående formel er der et kolon før INDEX-funktionen. Normalt vil INDEX-funktionen vist nedenfor returnere 1403 fra celle D2. Men når du sætter et kolon på begge sider af INDEX-funktionen, begynder det at returnere celleadressen D2 i stedet for indholdet af D2. Dette er vildt, at det fungerer.
Hvorfor betyder dette noget? INDEX er ikke flygtigt. Du får al den fleksible godhed af OFFSET uden tidssugende genberegninger igen og igen.
Jeg lærte først dette tip fra Dan Mayoh på Fintega. Tak til Access Analytic for at foreslå denne funktion.
Se video
Videoudskrift
Lær Excel fra podcast, afsnit 2048 -: INDEX erstatter en flygtig OFFSET!
Hej, jeg podcaster alle mine tip fra denne bog, klik på det "i" øverst til højre for at komme til afspilningslisten!
Okay, OFFSET er en fantastisk funktion! OFFSET giver os mulighed for at specificere en øverste venstre hjørne-celle og derefter bruge variabler til at definere derfra hvor mange rækker ned, hvor mange rækker derover og derefter definere en form, okay. Så her, hvis jeg vil tilføje eller lave et gennemsnit på, lad os sige 3/4, vil denne formel lige her se på formlen. OFFSET siger, at vi starter fra B2, starter fra Q1, vi går ned 0, over 0, formen vil være 1 række høj, og den vil være H1, med andre ord 3 celler bred, okay. Så i dette tilfælde alt, hvad vi virkelig laver, er at ændre, hvor mange celler vi tilføjer, vi starter altid tilbage i B2 eller B3 eller B4, når jeg kopierer ned, og derefter beslutter det, hvor mange celler bredt. Okay, OFFSET er denne seje ting, den udfører alle mulige fantastiske funktioner, men her er besværet, det er flygtigt!Hvilket betyder, at selvom noget ikke er i beregningskæden, Excel, tager det sig tid til at genberegne det, hvilket vil bremse tingene, okay.
Denne fantastiske version af INDEX, rigtigt, normalt hvis jeg beder om INDEX for disse 4 celler, 3, vil den returnere tallet 1403. Men når jeg lægger et kolon enten før eller efter INDEX, sker der noget meget andet, vi ser på denne formel her. Så indeks over de 4 celler, hvilken vil jeg have, jeg vil have den 3., men du kan se, at der er et: lige der. Så vi går altid fra B2: E2, og jeg viser dig, om vi går til formler, evaluerer formel, okay, så lige her beregner det antallet 3. Og her, INDEX med: næste til det, i stedet for at fortælle os 1403, hvilket er, hvordan INDEX normalt ville beregne, vil det returnere $ D2, og så GEMIDDEL vil gøre gennemsnittet af disse 3. Helt fantastisk, den måde, dette fungerer på, og den ekstra fordel, det er ikke ustabilt, okay,og dette kan endda bruges til at erstatte en utrolig kompleks OFFSET.
Så her med denne OFFSET er vi baseret på disse værdier. Hvis jeg vælger Q2 og Central, okay, bruger disse formler MATCH og COUNTIF til at finde ud af, hvor mange rækker ned, hvor mange kolonner over, hvor høje, hvor brede. Og så bruger OFFSET her alle disse værdier til at finde ud af medianen. Vi laver bare en test for at sikre os, at det fungerer, så = MEDIAN i det blå område derovre, bedre være 71, okay, og vi vælger noget andet her, Q3 og West, så. Tryk på F2, jeg skal bare trække dette over og ændre størrelsen på det for at omskrive den formel, tryk på Enter, og det fungerer med OFFSET.
Nå her, ved hjælp af en INDEX, har jeg faktisk et kolon i midten med et INDEX på venstre side og et INDEX på højre side, se denne ting blive beregnet i Evaluer formel. Så det starter, vil evaluere, evaluere og lige her er INDEX ved at gøre det til en celleadresse. Så H16 er den første, vest, Q3, og over på højre side vil evaluere, par mere, og derefter højre hende det ændres til I20. Så den seje, kølige, ikke-flygtige at erstatte en OFFSET ved hjælp af INDEX-funktionen. Okay, dette tip og mange flere i denne bog, klik på det "i" i øverste højre hjørne for at købe bogen.
Okay opsummering: OFFSET, fantastisk, fleksibel funktion, når du mestrer, kan du gøre alle mulige ting. Peg på en variabel øverst til venstre celle, peg på et interval, der har en variabel form, men den er flygtig, og så beregner de ved hver beregning. Excel foretager normalt en smart beregning, eller det genberegner cellerne, der skal beregnes, men med OFFSET bliver det altid beregnet. I stedet for OFFSET kan du bruge INDEX: eller: INDEX eller endda INDEX: INDEX, når som helst INDEX har et kolon ud for det, returnerer det en celleadresse i stedet for værdien for den celle. Og fordelen er, at INDEX ikke er ustabil!
OK, jeg vil gerne takke dig for at komme forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2048.xlsm
