Konsolider regneark ved hjælp af dialogboksen Konsolider
For at få det ultimative inden for datakonsolidering, skal du bruge dialogboksen Konsolider. Denne metode er fleksibel og i nogle tilfælde, fungerer den ovenikøbet selvom regnearkene ikke er opstillet identisk. Denne teknik kan oprette konsolideringer, der er statiske (ingen linkformler) eller dynamiske (med linkformler). Funktionen Konsolider understøtter følgende metoder for konsolidering:
- Ved position: Denne metoder er kun præcis hvis regnearkene er sat op identisk.
- Ved kategori: Excel benytter række- og kolonneetiketter til at matche data i kilderegnearkene. Brug denne indstilling, hvis dataene er stillet op forskelligt i kilderegnearkene eller hvis nogle af regnearkene mangler rækker eller kolonner.
Figuren viser dialogboksen Konsolider, som vises når du vælger Data -> Dataværktøjer -> Konsolider.
Her følger en beskrivelse af de indstillinger der er i denne dialogboks:
- Listefeltet Funktion: Specificer konsolideringstypen. Sum er den mest brugte konsolideringsfunktion, men du kan vælge blandt ti andre muligheder.
- Reference: Specificer et område i kildefilen du vil konsolidere. Du kan indtaste områdereference manuelt eller bruge standard udpegningsteknikker (hvis projektmappen er åbnet). Navngivne områder er også accepteret. Efter du kan indtastet området i dette felt klikkes på Tilføj for at tilføje det til referencelisten. Hvis du konsoliderer efter position skal du ikke inkludere etiketterne i området; hvis du konsoliderer efter kategori skal du inkludere etiketterne i området.
- Alle referencer: Dette felt indeholder en liste over de referencer du har tilføjet med knappen Tilføj.
- Opret kæder til kildedata: Når du vælger denne indstilling vil Excel tilføje opsummeringsformler for hver etiket og oprette en oversigt. Hvis du fravælger dette indstilling, vil konsolideringen ikke bruge formler og en oversigt udelades.
- Knappen Gennemse: Klik for at se en dialogboks der giver dig mulighed for at vælge at åbne en projektmappe. Filnavnet indsættes i feltet Reference, men du skal tilføje områdereferencen. Du vil opdage at arbejdet er eget nemmere hvis alle projektmapper, der skal konsolideres, er åbne.
- Knappen Tilføj: Klik for at tilføje referencen i feltet Referencen til listen Alle referencer. Vær sikker på at du klikker på denne knap efter du har specificeret hvert område.
- Knappen Slet: Klik for at slette den valgte reference i listen Alle referencer.
Et eksempel på projektmappekonsolidering
Dette simple eksempel demonstrerer funktionen Data konsolidering. Figuren viser tre projektmapper med et enkelt regneark hvert, der skal konsolideres. Disse regneark indeholder tre måneders salgstal. Bemærk dog at de ikke gengiver de sammen produkter. Ud over dette er produkterne ikke vist i samme rækkefølge. Med andre ord er disse regneark er ikke opstillet identisk. Oprettelse af en manuel konsolidering vil være en tidskrævende opgave.
For at konsolidere disse informationer, starter vi med en ny projektmappe. Du behøver ikke åbne kildeprojektmapperne, men konsolideringen er nemmere hvis de er det. Følg disse trin for at konsolidere projektmapperne.
- Vælg Data -> Dataværktøjer -> Konsolidering. Dialogboksen Konsolider vises.
- Vælg den typen af opsummering for konsolideringen du ønsker fra listefeltet Funktion. Brug Sum i dette eksempel.
- Indtast referencerne til det første regneark du vil konsolidere. Hvis projektmappen er åbnet kan du udpege referencen. Hvis den ikke er åben klikke på knappen Gennemse for finde filen. Reference skal inkludere et område. Du kan bruge et område, der inkludere komplette kolonner som A:K. Dette område er større end det aktuelle område, der skal konsolideres, men ved at bruge dette område sikre det os at konsolidering også virker, hvis der tilføjes nye rækker og kolonner til kildefilen. Når referencen i boksen Alle referencer er korrekte, klikke du på Tilføj for at tilføje det til listen Alle referencer.
- Indtast referencen til det andet regneark. Du kan udpege området i projektmappen Afdeling 2, eller du kan ganske enkelt redigere den eksisterende reference ved at ændre Afdeling 1 til Afdeling 2, og så klikke Tilføj. Denne reference tilføjes til listen Alle referencer.
- Indtast referencen til det tredje regneark. Igen kan du redigere det eksisterende ved at ændre Afdeling2 til Afdeling 3 og klikke Tilføj. Den sidste reference tilføjes til listen Alle referencer.
- Da regnearkene ikke er sat op på samme måde, vælges afkrydsningsfelterne Venstre kolonne og Øverste række for at sikre at Excel sammenligner vores data ved at bruge etiketterne.
- Vælg afkrydsningsfeltet Opret kæder til kildedata for at få Excel til at opretter en oversigt med eksterne referencer.
- Klik OK for at begynde konsolideringen.
Excel opretter konsolideringen begyndende med den aktive celle. Bemærk at Excel opretter en oversigt, som er slået sammen for at vise subtotalerne for hvert produkt. Hvis du udvider oversigten (ved at klikke 2 eller symbolet for plustegnet i oversigten), kan du se detaljer. Hvis du undersøger det yderligere vil du opdage at hver detalje celle er en ekstern referenceformel, der bruger tilhørende celler i kildefilen. Derfor vil resultatet af konsolideringen automatisk opdateres når værdierne i en af projektmapperne ændres.
Figuren nedenfor viser resultatet af konsolideringen, og den efterfølgende figur viser de opsummerede informationer (med oversigten slået sammen for at skjule detaljerne).
Opdatere en konsolidering
Når du vælger indstillingen at oprette formler, vil de eksterne referencer i projektmapperne for konsolideringen kun blive oprettet med data, der er til stede på tidspunktet for konsolideringen. Hvis der derfor tilføjes nye rækker til en af de oprindelige projektmapper, skal konsolideringen laves igen. Heldigvis gemmes konsolideringens parametre i projektmappen, så det er ganske enkelt et spørgsmål om at køre konsolidering, hvis der er brug for det. Det er derfor det er en god ide at specificere hele kolonner eller inkludere ekstra kolonner (i trin 3 i den forgående sektion).
Excel husker på referencerne du indtastede i dialogboksen Konsolider og gemmer dem i projektmappen. Hvis du derfor til opdatere en konsolidering, skal du ikke genindtaste referencen. Du skal blot hente dialogboksen konsolidering frem igen, sikre dig at området er korrekt og klikke OK.
Mere om konsolidering
Excel er meget fleksibel hvad angår kilderne du kan konsolidere. Du kan konsolidere data fra følgende:
- Åbne projektmapper.
- Lukkede projektmapper. Du skal manuelt indtaste referencerne, men du kan bruge knappen gennemse for at få filnavndelen af referencen.
- Den samme projektmappe som du opretter din konsolidering i.
- Og selvfølgelig kan du blande og sammenligne enhver af de forgående valg i en enkelt konsolidering.
Hvis du foretrækker en konsolidering ved at sammenligne etiketter, skal du være opmærksom på at sammenligningen er præcis. For eksempel vil Jan ikke være det samme som Januar. Sammenligningen tager ikke højde for store og små bogstaver, så April matcher APRIL. Yderligere gælder der, at etiketterne kan være i en vilkårlig rækkefølge, og de behøver ikke være i sammen rækkefølge i alle kildeområderne.
Hvis du fravælger Opret en kæde til kildedata vil Excel generere en statisk konsolidering. (Der oprettes ikke formler.) Hvis der derfor er ændringer i dataene i kildeprojektmapperne, vil konsolideringen ikke opdateres automatisk. For at opdatere opsummereringsinformationerne skal du vælge Data -> Dataværktøjer -> konsolider igen.