Konsolidér data i flere regneark

Hvis du vil opsummere og rapportere resultater fra separate regneark, kan du konsolidere data fra hvert ark i et hovedregneark. Arkene kan være i samme projektmappe som hovedregnearket eller i andre projektmapper. Når du konsoliderer data, samler du data, så du nemmere kan opdatere og aggregere efter behov.

Hvis du har et regneark med udgiftsrapporter for hvert af dine regionale kontorer, kan du f.eks bruge konsolideringen til at samle disse tal i et overordnet regneark over virksomhedens udgifter. Dette hovedregneark kan også indeholde virksomhedens samlede omsætning og salgsgennemsnit, aktuelle lagerbeholdninger og bedst sælgende produkter.

: Hvis du ofte konsoliderer data, kan det være en hjælp at oprette nye regneark ud fra en regnearksskabelon, der bruger et ensartet layout. For at få mere at vide om disse skabeloner skal du læse: Opret en skabelon. Dette er også et ideelt tidspunkt at konfigurere din skabelon med Excel-tabeller.

Der er to måder at konsolidere data på, enten efter placering eller kategori.

Konsolidering efter placering: Dataene i kildeområderne har samme rækkefølge og bruger de samme etiketter. Brug denne metode til at konsolidere data fra en række regneark, f.eks regneark til afdelingsbudgetter, der er oprettet fra samme skabelon.

Konsolidering efter kategori: Når dataene i kildeområderne ikke er arrangeret i samme rækkefølge, men bruger de samme etiketter. Brug denne metode til at konsolidere data fra en række regneark, som har forskellige layouts, men har de samme dataetiketter.

At konsolidere data efter kategori svarer til at oprette en pivottabel. Med en pivottabel kan du dog nemt omorganisere kategorierne. Overvej at oprette en pivottabel , hvis du har brug for mere fleksibel konsolidering efter kategori.

: Eksemplerne i denne artikel er oprettet i Excel 2016. Selvom din visning kan variere, hvis du bruger en anden version af Excel, er trinnene de samme.

Følg disse trin for at konsolidere flere regneark i et hovedregneark:

Hvis du ikke allerede har gjort det, skal du konfigurere dataene i hvert komponentark ved at gøre følgende:
- Sørg for, at hvert dataområde er i listeformat. Hver kolonne skal have et navn (overskrift) i den første række og indeholde lignende data. Der må ikke være tomme rækker eller kolonner nogen steder på listen.
- Placer hvert område i et separat regneark, men indtast ikke noget i hovedregnearket, hvor du planlægger at konsolidere dataene. Excel gør dette for dig.
- Sørg for, at hvert område har det samme layout.
I hovedregnearket skal du klikke på cellen øverst til venstre i det område, hvor de konsoliderede data skal vises.

: Hvis du vil undgå at overskrive eksisterende data i hovedregnearket, skal du sørge for, at der er tilstrækkeligt med celler til højre og under denne celle til de konsoliderede data.
Klik på Data>Konsolider (i gruppen Dataværktøjer ).
Klik på den summeringsfunktion, der skal bruges til at konsolidere dataene, i feltet Funktion. Standardfunktionen er SUM.

Her er et eksempel, hvor der er valgt tre regnearksområder:
Vælg dine data.

Klik derefter på knappen Skjul i feltet Reference for at formindske panelet og markere dataene i regnearket.

Klik på det regneark, der indeholder de data, du vil konsolidere, markér dataene, og klik derefter på knappen Udvid dialogboks i højre side for at gå tilbage til dialogboksen Konsolider.Hvis et regneark, der indeholder data, som du skal konsolidere, findes i en anden projektmappe, skal du klikke på Gennemse for at finde projektmappen. Når du har fundet og klikket på OK, angiver Excel filstien i feltet Reference og føjer et udråbstegn til den pågældende sti. Du kan derefter fortsætte med at vælge andre data.

Her er et eksempel, hvor tre regnearksområder er valgt:
Klik på Tilføj i pop op-vinduet Konsolider. Gentag dette for at tilføje alle de områder, du konsoliderer.
Automatiske kontra manuelle opdateringer: Hvis du vil have Excel til at opdatere din konsolideringstabel automatisk, når kildedataene ændres, skal du blot markere afkrydsningsfeltet Opret kæder til kildedata . Hvis dette felt ikke er markeret, kan du opdatere konsolideringen manuelt.
:
- Du kan ikke oprette links, når kilde- og destinationsområder er på samme ark.
- Hvis du vil ændre omfanget af et område – eller erstatte et område – skal du klikke på området i pop op-vinduet Konsolider og opdatere det ved hjælp af trinnene ovenfor. Dette vil oprette en ny områdereference, så du er nødt til at slette den forrige, før du konsoliderer igen. Du skal blot vælge den gamle reference og trykke på Delete-tasten.
Klik på OK, så genererer Excel konsolideringen for dig. Du kan også anvende formatering. Det er kun nødvendigt at formatere én gang, medmindre du kører konsolideringen igen.
- De etiketter, der ikke passer til etiketter i andre kildeområder, resulterer i separate rækker eller kolonner i konsolideringen.
- Sørg for, at de kategorier, du ikke vil konsolidere, har entydige navne, der kun vises i ét kildeområde.

Hvis de data, der skal konsolideres, findes i forskellige celler i forskellige regneark:

Angiv en formel med cellereferencer til de andre regneark, én for hvert separate regneark. Hvis du f.eks. vil konsolidere data fra regneark med navnet Salg (i celle B4), HR (i celle F5) og Marketing (i celle B9) i celle A2 i hovedregnearket, skal du angive følgende:

Formelreference for flere ark i Excel

: Sådan angiver du en cellereference – f.eks. Salg! B4 – I en formel uden at skrive skal du skrive formlen op til det sted, hvor du skal bruge referencen, klikke på regnearksfanen og derefter klikke på cellen. Excel udfylder arknavnet og celleadressen for dig. BEMÆRK! Formler i sådanne tilfælde kan være fejlbevarende, da det er meget nemt ved et uheld at markere den forkerte celle. Det kan også være svært at få øje på en fejl, når du har indtastet en kompleks formel.

Hvis de data, der skal konsolideres, findes i de samme celler i forskellige regneark:

Angiv en formel med en 3D-reference, der bruger en reference til et område med regnearksnavne. Hvis du f.eks. vil konsolidere data i cellerne A2 fra Salg til marketing, skal du angive følgende i celle E5 i hovedregnearket:

Referenceformel for 3D-ark i Excel