Excels OFFSET-funksjon vil redusere beregningen av arbeidsboken. Det er et bedre alternativ: en uvanlig syntaks av INDEX.
Dette er et nisjetips. Det er en utrolig fleksibel funksjon kalt OFFSET. Det er fleksibelt fordi det kan peke på et utvalg av annen størrelse som beregnes på farten. I bildet nedenfor, hvis noen endrer rullegardinlisten # Qtrs i H1 fra 3 til 4, vil det fjerde argumentet for OFFSET sørge for at området utvides til å omfatte fire kolonner.
Regnearkguruer hater OFFSET fordi det er en ustabil funksjon. Hvis du går til en helt ubeslektet celle og skriver inn et tall, beregnes alle OFFSET-funksjonene. Selv om cellen ikke har noe med H1 eller B2 å gjøre. Som oftest er Excel veldig nøye med å bare kaste bort tid på å beregne cellene som trenger å beregne. Men når du har introdusert OFFSET, begynner alle OFFSET-cellene, pluss alt downline fra OFFSET, å beregne etter hver endring i regnearket.
Jeg dømte 2013 ModelOff-finalen i New York City da noen av vennene mine fra Australia påpekte en bisarr løsning. I formelen nedenfor er det et kolon før INDEX-funksjonen. Normalt vil INDEX-funksjonen vist nedenfor returnere 1403 fra celle D2. Men når du setter et kolon på hver side av INDEX-funksjonen, begynner det å returnere celleadressen D2 i stedet for innholdet i D2. Dette er vilt at det fungerer.
Hvorfor betyr dette noe? INDEX er ikke flyktig. Du får all den fleksible godheten til OFFSET uten tidssugende omberegninger om og om igjen.
Jeg lærte først dette tipset fra Dan Mayoh på Fintega. Takk til Access Analytic for å foreslå denne funksjonen.
Se på video
Videoutskrift
Lær Excel fra podcast, episode 2048 -: INDEX erstatter en flyktig OFFSET!
Hei, jeg podcaster alle tipsene mine fra denne boken, klikk på "i" øverst til høyre for å komme til spillelisten!
OK, OFFSET er en fantastisk funksjon! OFFSET lar oss spesifisere en celle øverst til venstre i hjørnet, og deretter bruke variabler for å definere derfra hvor mange rader ned, hvor mange rader over, og deretter definere en form, ok. Så her, hvis jeg vil legge sammen, eller gjøre et gjennomsnitt på, la oss si 3/4, vil denne formelen her se på formelen. OFFSET sier at vi starter fra B2, starter fra Q1, vi går ned 0, over 0, formen vil være 1 rad høy, og den vil være H1, med andre ord 3 celler bred, ok. Så i dette tilfellet er alt vi virkelig gjør å endre hvor mange celler vi legger sammen, vi starter alltid tilbake i B2, eller B3 eller B4 når jeg kopierer ned, og deretter bestemmer det hvor mange celler bredt. OK, OFFSET er denne kule tingen, den gjør alle slags fantastiske funksjoner, men her er bryet, det er ustabilt!Noe som betyr at selv om noe ikke er i beregningskjeden, Excel, tar det seg tid å beregne det på nytt, noe som kommer til å bremse ting, altså.
Denne fantastiske versjonen av INDEX, riktig, normalt hvis jeg vil be om INDEKS for disse 4 cellene, 3, kommer den til å returnere tallet 1403. Men når jeg setter et kolon enten før eller etter INDEX, skjer det noe helt annet, vi tar en titt på denne formelen her. Så indeks over de 4 cellene, hvilken vil jeg ha, jeg vil ha den tredje, men du ser at det er en: akkurat der. Så vi kommer alltid til å gå fra B2: E2, og jeg vil vise deg om vi går til formler, evaluerer formel, ok, så akkurat her kommer det til å beregne tallet 3. Og her, INDEKSEN med: neste til det, i stedet for å fortelle oss 1403, som er hvordan INDEX normalt ville beregne, kommer det til å returnere $ D2, og så vil GJENNOMSNITTET gjøre gjennomsnittet av de 3. Helt fantastisk, måten dette fungerer på, og den ekstra fordelen det er ikke ustabilt, ok,og dette kan til og med brukes til å erstatte en utrolig kompleks OFFSET.
Så her med denne OFFSET er vi basert på disse verdiene. Hvis jeg velger Q2 og Central, OK, bruker disse formlene MATCH og COUNTIF for å finne ut hvor mange rader ned, hvor mange kolonner over, hvor høye, hvor brede. Og så bruker OFFSET her alle disse verdiene for å finne ut medianen. Vi vil bare gjøre en test for å forsikre oss om at den fungerer, så = MEDIAN i det blå området der, bedre være 71, ok, og vi velger noe annet her, Q3 og West, så. Trykk på F2, jeg skal bare dra dette over og endre størrelsen på det for å skrive om formelen, trykk Enter, og det fungerer med OFFSET.
Vel her, ved hjelp av et INDEKS, har jeg faktisk et kolon i midten med et INDEKS på venstre side og et INDEKS på høyre side, se denne tingen bli beregnet i Evaluer formel. Så det begynner, vil evaluere, evaluere, og akkurat her er INDEX i ferd med å gjøre det om til en celleadresse. Så H16 er den første, West, Q3, og over på høyre side vil evaluere, par mer, og deretter høyre henne det endres til I20. Så den kule, kule ikke-flyktige å erstatte en OFFSET ved hjelp av INDEX-funksjonen. OK, dette tipset og mange flere i denne boken, klikk på "i" øverst til høyre for å kjøpe boken.
OK oppsummering: OFFSET, fantastisk, fleksibel funksjon, når du mestrer, kan du gjøre alle slags ting. Pek på en variabel øverst til venstre celle, pek på et område som har en variabel form, men den er flyktig, og så beregner de ved hver beregning. Excel gjør vanligvis en smart beregning, eller den beregner cellene som må beregnes på nytt, men med OFFSET blir det alltid beregnet. I stedet for OFFSET kan du bruke INDEX: eller: INDEX eller til og med INDEX: INDEX, når som helst INDEX har et kolon ved siden av, vil den returnere en celleadresse i stedet for verdien på den cellen. Og fordelen er at INDEX ikke er ustabil!
OK, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2048.xlsm
