Indhold
- Brug af OFFSET-funktionen i en formel i Excel 2007 og Excel 2010
- Hvornår skal man bruge positive og negative tal til rækker og kolonner i OFFSET-formler
- Brug af OFFSET med COUNTA i en formel til at oprette et dynamisk interval i Excel 2007 og Excel 2010
- Dynamiske områder i Excel
- Og endelig....
Robbie skriver for det meste om Skyrim, men kaster også lejlighedsvis lys over de underlige Microsoft-applikationer som Excel og Outlook.
I denne artikel skal vi se på noget, der er ret cool og noget, jeg har haft brug for i mine egne Excel-regneark i nogen tid.
Forestil dig, hvis du vil, et regneark, hvor du registrerer fakturaer, salg, webhits osv. Og derefter SUM dem til at give dig selv et løbende total. Hver gang du tilføjer ekstra rækker til dit regneark, skal du opdatere din SUM formel for at give mulighed for de ekstra varer.
Nå ikke længere! Velkommen til det dynamiske område ved hjælp af OFFSET og COUNTA funktioner. Brug af denne funktion, hver gang der tilføjes rækker (eller slettes) og også hver gang der indsættes kopierede celler, SUM formel opdateres automatisk og dynamisk for at afspejle dette.
Brug af OFFSET-funktionen i en formel i Excel 2007 og Excel 2010
Det OFFSET -funktion giver dig mulighed for at bede Excel om at fortælle dig indholdet af en celle (eller et celleområde) et bestemt antal rækker og kolonner væk fra en referencecelle (eller celler).
Hvis du bruger OFFSET alene returnerer den værdien af den celle, du henviste til den. Vi starter med det som et eksempel:
= OFFSET (B3,1,1,1,1)
Formlen består af følgende dele:
- Den celle, du beder Excel om at starte fra (i dette tilfælde B3) (kendt af Excel som Reference)
- Den næste del af formlen er antallet af Rækker (som kan være enten op eller ned) fra referencecellen til den celle, du ser på. Vi er interesserede i celle C4, så denne værdi er 1, da den er en række ned fra celle B3.
- Dernæst skal vi vide, hvor mange Kolonner fra start til reference celle. Dette er igen 1, da det er en kolonne fra B3 til C4
- Det fjerde element i formlen er Højde. Dette er antallet af rækker, som de returnerede data skal være.
- Endelig har vi det Bredde eller antallet af kolonner, som dataene skal være.
Bemærk:Rækker og Kolonner kan være et negativt eller positivt tal. Se nedenstående tabel for at forstå, hvornår der skal bruges positive eller negative værdier.
Hvornår skal man bruge positive og negative tal til rækker og kolonner i OFFSET-formler
| Række | Kolonne |
Positiv | Nedenfor reference | Ret til henvisning |
Negativ | Ovenstående reference | Venstre reference |
Bemærk:Højde og Bredde skal begge være positive. Hvis de ikke er angivet, bruger Excel referencens højde og bredde.
Bemærk: Hvis du får en #VALUE, har du typisk bedt Excel om at vise noget, det ikke kan vises.
For at illustrere dette:
Hvis jeg bruger følgende formel:
= OFFSET (B3,1,1,2,1)
Jeg beder Excel om at vise resultaterne af en celle i to celler, som den ikke kan gøre, så det returnerer en fejl.
Bemærk: Hvis du får en #REF-fejl, er du gået ud for kanten af dit regneark.
Denne formel resulterer i en #REF-fejl, da der ikke er sådan en række fire rækker over B3.
= OFFSET (B3, -4,2,1,1)
Brug af OFFSET med COUNTA i en formel til at oprette et dynamisk interval i Excel 2007 og Excel 2010
Nu hvor vi forstår OFFSET funktion og hvordan man bruger den i en formel, kommer vi til den virkelig interessante del af denne artikel; vi får lavet et dynamisk interval ved hjælp af det ved siden af COUNTA. Det kan vi så SUM værdierne i det dynamiske område og denne formel genberegnes automatisk, hvis celler tilføjes, fjernes eller deres indhold ændres. Selvfølgelig, SUM er bare et eksempel, som jeg valgte til denne artikel, du kunne bruge GENNEMSNIT, MAKS eller enhver anden funktion, som du følte var passende til dine behov sammen med OFFSET og COUNTA funktioner.
Lad os først se på hvad COUNTA gør for os i dette eksempel:
Det tæller simpelthen antallet af celler, der ikke er tomme mellem start- og slutcellen (i dette tilfælde er der 29 ikke-tomme celler mellem celle C3 og celle C32).
= COUNTA (C3: C32)
Nu inkluderer vi COUNTA formel ovenfor som en del af vores OFFSET formel for at skabe et dynamisk område. Den færdige formel er:
= SUM (OFFSET (C3,0,0, COUNTA (C3: C32), 1))
Vi begynder med at se på OFFSET sektion med formlen:
OFFSET (C3,0,0, COUNTA (C3: C32), 1)
- Vi starter ved celle C3 som vores Reference
- Antallet af Rækker vi beder Excel om at flytte fra startcellen til C3 er 0
- Ligeledes antallet af Kolonner er også 0 (jeg kan godt lide at tilføje og fjerne kolonner til mine data, så at holde det på 0 betyder, at formlen aldrig går i stykker)
- Nu kommer den dynamiske del af hele formlen. For Højde, vi fortæller Excel, at højden er resultatet af COUNTA-formlen, der i øjeblikket er 29. Hvis vi tilføjer eller fjerner rækker, vil dette automatisk justeres!
- Endelig rådgiver vi Excel om Bredde, som i dette tilfælde er 1 kolonne.
Nu hvor vi forstår OFFSET sektion af formlen tilføjer vi SUM funktion til det for at fuldføre den overordnede formel, der giver os:
= SUM (OFFSET (C3,0,0, COUNTA (C3: C32), 1))
Hvis vi nu Indsæt en ekstra tom række vil du bemærke, at det samlede salg falder med 18 (hvilket er værdien i celle C32, som er den sidste række) fra 1.085 i figuren ovenfor til 1.067 i nedenstående figur.
Jeg tilføjer nu en værdi i den tomme række, og det samlede salg er nu korrekt og inkluderer værdien i C32.
Bemærk: Indsættelse af en tom række i dit dynamiske område fører til de resultater, der vises i din SUM formel er forkert.
Bemærk: Skal du tilføje yderligere rækker, f.eks. Ved Indsættelse af kopierede celler Excel vil simpelthen øge COUNTA del af formlen, der gør den dynamisk.
Som et eksempel tilføjede jeg fire rækker til mit regneark, og Excel opdaterede min formel automatisk!
= SUM (OFFSET (C3,0,0, COUNTA (C3: C36), 1))
Dynamiske områder i Excel
Evnen til at have dynamiske områder i dine Excel-regneark, der indeholder data såsom salg, fakturaer, webhits osv., Som derefter kan tilføjes eller beregnes i gennemsnit, er faktisk en meget nyttig ting. Dette betyder, at du ikke længere behøver at opdatere dine formler manuelt, når du tilføjer eller sletter dine data.
For at opnå dette:
- Vi startede med at bruge OFFSET funktion i formlen. Denne funktion giver dig mulighed for at starte med en referencecelle (eller celler) og bede Excel om at vise indholdet af et andet område et specifikt antal rækker og kolonner fra referencecellen
- Dernæst tilføjede vi COUNTA funktion til at levere Højde af området til OFFSET. Når celler tilføjes eller fjernes fra området, bliver COUNTA sektion af formlen opdateres automatisk.
- Ved at tilføje SUM funktion til denne formel, kan vi nu tilføje de celler, som Excel har vist via OFFSET.
Når alle disse funktioner føjes til en formel, summerer den nu dynamisk en række celler og opdateres automatisk, når data tilføjes eller slettes fra området.Dette for mig er skønheden og kraften i Excel, det faktum, at du kan tilføje to funktioner sammen og skabe noget, der ikke kun er meget nyttigt, men også mere end summen af dets dele.
Jeg håber, at du har fundet denne artikel nyttig og informativ og brugbar OFFSET vil transformere dine regneark på samme måde som det har forvandlet mine. Mange tak for læsningen, er du velkommen til at efterlade eventuelle kommentarer, du måtte have nedenfor.
Og endelig....
Denne artikel er nøjagtig og sand efter bedste forfatterens viden. Indholdet er kun til informations- eller underholdningsformål og erstatter ikke personlig rådgivning eller professionel rådgivning i forretningsmæssige, økonomiske, juridiske eller tekniske forhold.
