Moderne Excel ga endelig mening for meg etter at jeg lærte hvordan matriser og områder er forskjellige

Nesten alle som har skrevet inn data i et Excel-regneark har en grov idé om hva et område er. Det er ett av de konseptene du ikke trenger å studere, siden du plukker det opp naturlig mens du bruker programvaren. Matriser, derimot, har en tendens til å være litt vanskeligere å forstå.

Selv om disse to ofte kan se like ut på et regneark, lagrer og håndterer Excel dem forskjellig. Å forstå denne forskjellen kan hjelpe deg med å unngå vanlige Excel-feil som #VALUE! og #SPILL! og gjøre det enklere å utføre flere beregninger mer effektivt.

Arrayer vs. områder

De kan inneholde de samme dataene, men de er ikke det samme

Et område er en samling av en eller flere verdier som fysisk er plassert i celler på et regnearknett. Fordi områder eksisterer på selve arket, refererer du til dem ved hjelp av celleadresser, som C3 eller C1.

Områder i Excel kan inneholde verdier du har skrevet inn manuelt, men de kan også være i form av en spesiell type område kjent som et spredt område. Når en dynamisk matriseformel returnerer flere verdier, spres disse resultatene automatisk ut på regnearket, og det opprettes et spredt område. I motsetning til et standardområde, hvor du kan redigere hver celle uavhengig, kan et spredt område kun redigeres fra øverste venstre celle. Du kan imidlertid referere til hele det spredte området ved å legge til # symbolet til adressen til øverste venstre celle. For eksempel, hvis et spredt område starter i N2, kan du referere til alle verdiene ved å bruke N2#.

Et array, derimot, er en liste eller tabell med data som Excel lagrer i minnet i stedet for som verdier knyttet til spesifikke celleadresser. Under beregning holder Excel arrays i RAM, noe som gjør det mulig å overføre data mellom funksjoner uten å skrive mellomresultater tilbake til regnearket. Ta for eksempel denne formelen:

=COUNT(UNIQUE(A1))

UNIQUE-funksjonen produserer først et array i minnet, og deretter evaluerer COUNT dette arrayet. Regnearket viser bare det endelige resultatet, mens den mellomliggende listen over unike verdier forblir skjult i Excels minne.

Et array blir først synlig på regnearket når det spres ut i celler. På det tidspunktet tar outputen formen av et område. For eksempel, ved å skrive =SEQUENCE(10) i en celle, opprettes et array med tallene fra 1 til 10 i minnet, som deretter spres ut i cellene nedenfor. Når det er spredt, blir disse verdiene et spredt område. Før spredning, eller selv når arrayen ganske enkelt overføres mellom funksjoner, forblir den et array i minnet. I praksis, mens Excel behandler data gjennom funksjoner som LET, UNIQUE, og andre dynamiske arrayformler, fortsetter det å behandle disse dataene som et array snarere enn et regnearkområde.

Du kan også opprette matrise-konstanter direkte i Excel-formler ved å sette verdier i krøllparenteser. Dette lar deg levere flere verdier som en enkelt matrise uten å referere til celler. For eksempel kan du definere numeriske matriser som {1, 2, 3, 4, 5} eller {6, 2, 10, 9, 3}, og tekstmatriser som {"a", "a"}, forutsatt at funksjonen støtter matrise-inndata.

For eksempel, mens noen betingede funksjoner er strenge når det gjelder å kreve celleområder, aksepterer SUMPRODUCT både regnearkområder og hardkodede matriser. I formelen nedenfor evaluerer Excel verdiene i A1:A5 mot kriteriet

=SUMPRODUCT((A1:A5

Du kan også observere hvordan Excel konverterer områder til matriser under beregninger. Hvis du velger en områdereferanse som A1 i formellinjen og trykker F9, evaluerer Excel referansen og erstatter den med en hardkodet matrise omsluttet av krøllparenteser. Dette gjør det mulig å se de eksakte verdiene Excel behandler i minnet på det tidspunktet.

Hvorfor forskjellen er viktig i formlene dine

Å vite hvor Excel lagrer data hjelper deg med å velge riktig tilnærming

Visse Excel-funksjoner, inkludert SUMIF, COUNTIF og RANK, er programmert til å fungere med fysiske områder. Hvis du gir en av disse funksjonene en minnebasert matrise i stedet for en områdereferanse, vil Excel returnere en #VALUE!-feil.

For eksempel kan ikke SUMIF behandle arrayet som opprettes av SEQUENCE i følgende formel:

=SUMIF(SEQUENCE(10), ">5")

Men hvis du skriver inn =SEQUENCE(10) i celle A1, vil Excel fylle resultatene i A1. Du kan deretter bruke denne formelen:

=SUMIF(A1#, ">5")

Denne versjonen fungerer fordi A1# refererer til et fysisk område på regnearket i stedet for et minnebasert array.

Moderne funksjoner som FILTER, UNIQUE, SORT og LET fungerer annerledes. De kan sende matriser direkte mellom funksjoner uten å lagre mellomliggende resultater på regnearket. Dette gjør komplekse formler mer effektive fordi Excel kan utføre beregningene helt i minnet til det er klart til å returnere et endelig resultat.

Vurder denne formelen:

=LET(HighSales, FILTER(B2:B100, I2:I100 > 9000), UniqueCountries, UNIQUE(HighSales), SORT(UniqueCountries))

LET-funksjonen oppretter variabler som kun eksisterer innenfor formelen. Først undersøker FILTER kolonne I og returnerer landene i kolonne B som er knyttet til salg høyere enn 9 000. Excel lagrer den listen som et array i minnet og tildeler den til HighSales. Deretter fjerner UNIQUE dupliserte oppføringer fra det arrayet og lagrer resultatet som UniqueCountries. Til slutt arrangerer SORT listen alfabetisk og skriver det ferdige resultatet ut på regnearket.

Hvis jeg gjorde dette på den gamle måten, måtte jeg ha flere hjelpekolonner for å lagre hvert mellomtrinn. Med dynamiske matriser lager Excel de midlertidige matrisene i minnet, sender dem fra FILTER til UNIQUE til SORT, og skriver kun det endelige resultatet til regnearket. Formelen forblir selvstendig, og arbeidsboken forblir renere og enklere å håndtere.

Forskjellen mellom matriser og områder er også viktig fordi noen Excel-funksjoner fortsatt ikke støtter dynamiske matriser. Datavalidering i Excel, for eksempel, krever et fysisk område. Du kan ikke plassere en matriseformel direkte i valideringskildeboksen. I stedet må du skrive inn formelen i en celle i regnearket, la den fylle ut området, og deretter referere til det utfylte området ved å bruke # -operatoren. Hvis formelen din for eksempel begynner i P1, vil du bruke =P1# som valideringskilde.

Excel-tabeller har en lignende begrensning. Fordi tabeller er utformet rundt uavhengige rader og kolonner, støttes ikke spredte matriseformler innenfor tabelstruktur. Enhver dynamisk matriseformel må plasseres utenfor tabellen.

Til slutt trenger dynamiske matriser plass til å utvide seg. Hvis en formel skal spre seg over tjue celler, må disse cellene være tomme. Hvis eksisterende data blokkerer spredningsområdet, returnerer Excel en #SPILL!-feil. Tradisjonelle områder har ikke dette kravet fordi de bare opptar cellene du eksplisitt har definert. Som et resultat trenger du bare å bekymre deg for å lage plass når en formel er utformet for å spre seg.

Moderne Excel fungerer bedre når du vet hvor dataene dine ligger

Et område har et hjem på regnearkets rutenett, mens en matrise lever i Excels minne til du gir den et sted å lande. Å forstå den distinksjonen gjør moderne Excel mye enklere å arbeide med. Du kan bygge mer robuste, automatiserte regneark og nest funksjoner som LET, FILTER og UNIQUE uten å fylle regnearket med mellomliggende beregninger.