Konsolider data i flere regneark

Hvis du vil summere og rapportere resultater fra separate regneark, kan du konsolidere data fra hvert ark til et hovedregneark. Arkene kan være i samme arbeidsbok som hovedregnearket, eller i andre arbeidsbøker. Når du konsoliderer data, setter du sammen data slik at det blir enklere å oppdatere og aggregere etter behov.

Hvis du for eksempel har et regneark for utgifter for hvert avdelingskontor, kan du kanskje konsolidere alt dette i ett hovedregnearket for bedriften. Dette hovedregnearket kan også inneholde salgsresultater og gjennomsnitt, gjeldende lagerbeholdning og produktene som har solgt mest for hele bedriften.

: Hvis du ofte konsoliderer data, kan det hjelpe å opprette nye regneark fra en regnearkmal som bruker et konsekvent oppsett. Hvis du vil lære mer om maler, kan du se: Opprette en mal. Dette er også et ideelt tidspunkt å konfigurere malen med Excel-tabeller.

Det finnes to måter å konsolidere data på, enten etter posisjon eller kategori.

Konsolidering etter posisjon: Dataene i kildeområdene har samme rekkefølge og bruker de samme etikettene. Bruk denne metoden for å konsolidere data fra en rekke regneark, som budsjettark for avdelingen som ble opprettet fra samme mal.

Konsolidering etter kategori: Når dataene i kildeområdene ikke ordnes på samme måte, men bruker de samme etikettene. Bruk denne metoden for å konsolidere data fra en rekke regneark som har ulike oppsett, men som har de samme dataetikettene.

Konsolidering av data etter kategori er nesten det samme som å opprette en pivottabell. Med en pivottabell kan du imidlertid omorganisere kategoriene enkelt og greit. Vurder å opprette en pivottabell hvis du trenger mer fleksibel konsolidering etter kategori.

: Eksemplene i denne artikkelen ble opprettet med Excel 2016. Selv om visningen kan variere hvis du bruker en annen versjon av Excel, er fremgangsmåten den samme.

Følg disse trinnene for å konsolidere flere regneark til et hovedregneark:

Hvis du ikke allerede har gjort det, kan du konfigurere dataene i hvert ark med bestanddeler ved å gjøre følgende:
- Kontroller at hvert dataområde er i listeformat. Hver kolonne må ha en etikett (topptekst) i den første raden og inneholde lignende data. Det må ikke være noen tomme rader eller kolonner i listen.
- Plasser hvert område i et eget regneark, men ikke skriv inn noe i hovedregnearket der du planlegger å konsolidere dataene. Excel gjør dette for deg.
- Kontroller at hvert område har samme oppsett.
Klikk cellen øverst til venstre i området der du vil at de konsoliderte dataene skal vises, i hovedregnearket.

: Hvis du vil unngå å overskrive eksisterende data i hovedregnearket, må du sørge for at du lar nok celler være til høyre og under denne cellen for de konsoliderte dataene.
Klikk Data>Konsolider (i Dataverktøy-gruppen ).
Klikk på sammendragsfunksjon du vil bruke til å konsolidere dataene i Funksjon-boksen. Standardfunksjonen er SUMMER.

Her er et eksempel der tre regnearkområder er valgt:
Velg dataene.

Deretter klikker du Skjul-knappen i Referanse-boksen for å redusere panelet og merke dataene i regnearket.

Klikk på regnearket som inneholder dataene du vil konsolidere, merk dataene, og klikk deretter på Utvid dialogboks-knappen til høyre for å returnere til dialogboksen Konsolider.Hvis et regneark som inneholder data du må konsolidere, er i en annen arbeidsbok, klikker du Bla gjennom for å finne arbeidsboken. Når du har funnet og klikket OK, skriver Excel inn filbanen i Referanse-boksen og tilføyer et utropstegn til banen. Deretter kan du fortsette å velge andre data.

Her er et eksempel der tre regnearkområder er valgt:
Klikk Legg til i popup-vinduet Konsolider. Gjenta dette for å legge til alle områdene du konsoliderer.
Automatiske kontra manuelle oppdateringer: Hvis du vil at Excel skal oppdatere konsolideringstabellen automatisk når kildedataene endres, merker du av for Opprett koblinger til kildedata . Hvis det ikke er merket av for denne boksen, kan du oppdatere konsolideringen manuelt.
:
- Du kan ikke opprette koblinger når kilde- og destinasjonsområdene er i samme regneark.
- Hvis du må endre omfanget av et område, eller erstatte et område, klikker du området i hurtigmenyen Konsolider og oppdaterer det ved hjelp av fremgangsmåten ovenfor. Dette oppretter en ny områdereferanse, så du må slette den forrige før du konsoliderer på nytt. Bare velg den gamle referansen, og trykk delete-tasten.
Klikk OK, så genererer Excel konsolideringen for deg. Du kan eventuelt bruke formatering. Det er bare nødvendig å formatere én gang, med mindre du kjører konsolideringen på nytt.
- Eventuelle etiketter som ikke samsvarer med etikettene i de andre kildeområdene, resulterer i egne rader eller kolonner i konsolideringen.
- Kontroller at kategorier du ikke vil konsolidere, har unike etiketter som bare vises i ett kildeområde.

Hvis dataene som skal konsolideres, er i forskjellige celler i forskjellige regneark:

Skriv inn en formel med cellereferanser til de andre regnearkene, en for hvert separate regneark. Hvis du for eksempel vil konsolidere data fra regneark kalt Salg (i celle B4), Personaladministrasjon (i celle F5) og Markedsføring (i celle B9), i celle A2 i hovedregnearket, kan du angi følgende:

Formelreferanse for flere regneark i Excel

: Slik skriver du inn en cellereferanse, for eksempel Salg! B4 – skriv inn formelen opp til det punktet der du trenger referansen, i en formel uten å skrive inn formelen, og klikk deretter regnearkfanen, og klikk deretter cellen. Excel fullfører arknavnet og celleadressen for deg. OBS! Formler i slike tilfeller kan være utsatt for feil, siden det er svært enkelt å merke feil celle ved et uhell. Det kan også være vanskelig å oppdage en feil etter at du har angitt en kompleks formel.

Hvis dataene som skal konsolideres, er i de samme cellene i forskjellige regneark:

Skriv inn en formel med en 3D-referanse som bruker en referanse til et område med regnearknavn. Hvis du for eksempel vil konsolidere data i celle A2 fra Salg til og med Markedsføring, skriver du inn følgende i celle E5 i hovedregnearket:

Referanseformel for 3D-ark i Excel