Litt avansert summering i Excel

[ilpostino]

Jeg har kanskje en litt sær problemstilling i norsk Excel 365. Jeg har en regnebok med oversikt over hvilke ansatte som har hvilke modeller av bærbare datamaskiner. Hvert avdelingskontor har hvert sitt regneark. I et eget regneark jeg jeg brukt formelen nedenfor for å hente alle unike instanser av modeller av datamaskiner. 

=SORTER(UNIK(VSTAKK(<referanse til tabellen i hvert regneark med klientmaskiner>)))

Det har gått fint for å hente ut alle unike modeller. Problemet mitt er når jeg prøver å summere antallet av hver modell på tvers av alle regnearkene. Jeg har prøvd varianter av formelen nedenfor uten hell. 

=ANTALL.HVIS(<referanse til tabellene med klientmaskiner;F3)

Overnevnte fungerer fint hvis jeg skal hente fra et regneark. Jeg har prøvd å bruke VSTAKK for å hente fra ale, men da får jeg feilmelding. Er SUMMER.HVIS.SETT et alternativ?

[NoBo]

ANTALL.HVIS ser ikke ut til å støtte å jobbe med flere ark samtidig. Sitter på mobilen så får ikke testet noe men ChatGPT foreslår det i sitatet. Alternativt bruke PowerPivot, PowerBI?
 

Sitat

I Microsoft Excel kan du bruke ANTALL.HVIS-funksjonen for å telle verdier basert på kriterier, men når du ønsker å telle fra flere ark (sheets) i en arbeidsbok, må du bruke en kombinasjon av funksjoner, da ANTALL.HVIS ikke direkte støtter flere ark samtidig. Her er noen løsninger:

 

1. Bruke ANTALL.HVIS med separate arkreferanser

 

Hvis du vet hvilke ark du vil telle fra, kan du legge sammen resultatene manuelt:

 

=ANTALL.HVIS(Ark1!A1:A100, "Kriterie") + ANTALL.HVIS(Ark2!A1:A100, "Kriterie") + ANTALL.HVIS(Ark3!A1:A100, "Kriterie")

 

Forklaring:

• Ark1!A1:A100 – Refererer til området i første ark.

• "Kriterie" – Dette er verdien du søker etter.

• + – Summerer resultatene fra de ulike arkene.

 

2. Bruke 3D-referanse med SUMPRODUKT og ANTALL.HVIS

 

Excel har begrenset støtte for 3D-referanser med betingede funksjoner, men du kan kombinere SUMPRODUKT og INDIREKTE for dynamiske løsninger:

 

=SUMMER(HVIS(INDIREKTE("'" & {"Ark1","Ark2","Ark3"} & "'!A1:A100")="Kriterie",1,0))

 

Forklaring:

• {"Ark1","Ark2","Ark3"} – Liste over arkene du vil telle fra.

• INDIREKTE("'" & {...} & "'!A1:A100") – Henter data fra områdene dynamisk.

• HVIS(...="Kriterie",1,0) – Sjekker hvor kriteriet stemmer og gir 1 hvis sant.

• SUMMER(...) – Summerer antall forekomster.

 

Merk: INDIREKTE fungerer ikke hvis arkene er lukket, og det kan påvirke ytelsen ved store datamengder.

 

3. Bruke en hjelpecelle for dynamisk arkreferanse

 

Hvis du har en liste over ark i en kolonne (f.eks. A1:A3 inneholder “Ark1”, “Ark2”, “Ark3”), kan du bruke følgende formel:

 

=SUMMER(HVIS(INDIREKTE("'" & A1:A3 & "'!A1:A100")="Kriterie",1,0))

 

Her kan du enkelt utvide listen ved å legge til flere ark i kolonnen.

 

4. Makro (VBA) for telling på tvers av ark

 

Hvis du ofte trenger å telle verdier på tvers av mange ark, kan du bruke en VBA-makro for å automatisere prosessen. Eksempel på VBA-kode:

 

Function TellFraFlereArk(rng As String, kriterie As String) As Double

    Dim ws As Worksheet

    Dim antall As Double

    antall = 0

    For Each ws In ThisWorkbook.Worksheets

        antall = antall + Application.WorksheetFunction.CountIf(ws.Range(rng), kriterie)

    Next ws

    TellFraFlereArk = antall

End Function

 

Bruk funksjonen i Excel som:

 

=TellFraFlereArk("A1:A100", "Kriterie")

 

Håper dette hjelper! Velg metoden som passer best til ditt behov.

 

[backtoback]

Mitt løsningforslag er å lage en Piviottabell på de stakkede dataene, da slipper du å bruke funksjonene SORTER() og UNIK().

Her er noen skjermdumper fra Excel i Microsoft 365 (jeg kunne ikke teste i Excel 2021 siden den ikke har VSTAKK()-funksjonen).

1. Lagde litt testdata

2. Datastakking og Pivottabell i Sheet4

• La til overskriftene "Ansatt" og "Modell" i Sheet4.
• Stakket dataene med denne formelen i celle A2: `=VSTACK(Sheet1!A2:B5,Sheet2!A2:B5,Sheet3!A2:B4)`.
• Merket området A1:B12 og satte inn en Pivottabell i celle E1.

 

I Pivottabellen "dro" jeg Modell til feltene "Rows" og "Σ Values".

3. Endret celle F1 fra "Sum of Modell" til "Count of Modell"

Høyreklikk på "Sum of Modell".

4. Ferdig resultat

 

Var det noe slikt du var ute etter?

Edit: Kolonne E = unike modeller, kolonne F = antall av hver modell.

Endret 5 timer siden av backtoback
typos

[ilpostino]

backtoback skrev (1 time siden):

Var det noe slikt du var ute etter?

Takk for veiledning. Skal teste i morgen. Jeg vet en kan gjøre veldig mye fancy shit i Pivot, men såvidt skrapet på overflaten.