Slik slår du sammen flere Excel-ark til ett: alle alternativene

Hvis du ofte jobber med flere regneark, har du sannsynligvis kopiert og limt inn som en gal for å samle informasjon spredt over forskjellige faner. Å slå sammen flere Excel-ark til ett sparer ikke bare tid, men reduserer også feil, forenkler rapportering og lar deg analysere data mye enklere.

Den gode nyheten er at du ikke er begrenset til den typiske kopieringen og limingen. Excel og noen eksterne verktøy tilbyr flere metoder for å kombinere ark fra samme fil, fra forskjellige arbeidsbøker, med eller uten duplikater, og til og med basert på vanlige kolonner som en produktidentifikator. La oss se trinn for trinn på alle de praktiske alternativene og hva som er best å bruke i hvert tilfelle.

Hvilke alternativer har du for å kombinere flere Excel-ark?

Før du går i gang med trinnene, er det nyttig å ha en klar oversikt. Det finnes mange forskjellige tilnærminger til konsolidering av regneark : fra innebygde Excel-funksjoner (Consolidate, Power Query) til VBA-makroer eller eksterne webtjenester og automatiseringsløsninger som Coupler.io eller ASPOSE.

Den beste metoden vil avhenge av om tabellene dine har samme struktur, om de er i samme fil eller i forskjellige mapper, og hvor dyktig du er i Excel. Det er ikke det samme å kompilere numeriske totaler for en ledelsesrapport som det er å koble salgs- og finanstabeller ved hjelp av en felles kolonne for detaljert analyse.

Kombiner data fra flere ark ved hjelp av Excels konsolideringsfunksjon

En av de mest klassiske og minst kjente måtene å konsolidere informasjon på er å bruke Excels kommando Konsolider. Konsolider lar deg oppsummere numeriske data spredt over flere regneark eller arbeidsbøker og gruppere dem i et "hovedark" med totaler, gjennomsnitt, antall og så videre.

Tenk deg at du sporer utgifter per filial: ett ark per kontor, alle med en lignende utgiftstabell. Med konsolidering kan du lage et bedriftsregneark med bare noen få klikk som samler data fra alle lokasjoner og beregner totaler, gjennomsnitt, lagernivåer eller andre målinger du trenger.

Excel tilbyr to konsolideringsmetoder: etter posisjon og etter kategori . Valget avhenger av hvordan områdene dine er organisert på tvers av forskjellige regneark: om cellene er justert med samme koordinater eller om du bruker rad- og kolonneetiketter.

Konsolider etter posisjon: når alle arkene er "lagt ut" likt

Posisjonskonsolidering fungerer veldig bra når alle ark har samme tabelldesign : samme kolonner i samme rekkefølge, samme rader i samme sekvens og ingen blanke mellomrom i datalisten.

For at dette systemet skal fungere problemfritt, må du gjennomgå hvert kildeark og sørge for at dataområdene opptar nøyaktig de samme cellene i alle, uten tomme rader eller kolonner mellom. Det er som å legge flere transparenter over hverandre: Excel vil summere eller behandle dataene som faller inn i den samme «cellen».

Den generelle prosessen er denne:

• Åpne alle kildeark eller bøker med dataene du vil legge til eller oppsummere, og kontroller at tabellen er justert i hver enkelt.
• I arbeidsboken der du vil vise totalen, gå til målarket og velg den øverste venstre cellen hvor vil du at jeg skal boot den konsoliderte tabellen.
• I datafanen skriver du inn riktig verktøy (i spanske versjoner er det vanligvis Data > Konsolider).
• I Konsolider-vinduet velger du i boksen función Hvis du vil legge sammen, beregne gjennomsnittet, telle osv.
• For hvert kildeark velger du området som inneholder dataene som skal konsolideres. Excel vil legge til hver referanse i listen. av intervaller som utgjør konsolideringen.
• Når du har inkludert alle områdene, trykker du OK for å få Excel til å opprette den kombinerte tabellen med resultatene.

Det er en veldig praktisk metode hvis du for eksempel trenger å få et numerisk sammendrag av flere periodiske ark (måneder, delegeringer, prosjekter) som deler en identisk struktur.

Konsolider etter kategori: når tagger styrer, ikke plassering

Den andre konsolideringsmetoden fungerer best når ikke alle ark har rader og kolonner i samme rekkefølge , men de deler de samme etikettene. Her bruker Excel rad- og kolonneoverskriftene til å bestemme hva som skal summeres med hva.

Denne tilnærmingen er ideell hvis tabellene dine er datalister uten hull, men med små forskjeller i rekkefølge, forutsatt at kategorinavnene er skrevet konsekvent . Hvis du bruker «Gjennomsnitt» i ett ark og «Prom.» i et annet, vil Excel behandle dem som separate felt og ikke kombinere dem.

Slik bruker du konsolidering etter kategori :

• Åpne alle kildeark som inneholder dataene som skal grupperes.
• På arket der du vil se sammendraget, velg igjen øverste venstre celle i utdataområdet.
• Tilgang igjen Data > Konsolider og velg funksjonen du ønsker (sum, gjennomsnitt osv.).
• Kryss av i boksene som angir hvor etikettene er: Øverste rad, Columna izquierda eller begge deler, avhengig av overskriftene dine.
• For hvert kildeark velger du dataområdet, inkludert radene eller kolonnene der etikettene er plassert, slik at Excel kan gjenkjenne kategoriene.
• Når alle områdene er lagt til, bekreft med OK for å generere den konsoliderte tabellen.

På denne måten kan du slå sammen numeriske data som deler kategorier, selv om de ikke er perfekt justert på tvers av alle ark, så lenge kolonne- og radnavnene er konsistente.

Kombiner flere Excel-ark manuelt fra selve programmet.

Hvis det du ønsker ikke så mye er å konsolidere verdier, men å ha flere ark som for øyeblikket er i separate filer i en enkelt arbeidsbok , gjør Excel det også relativt enkelt med alternativene for å flytte eller kopiere ark.

Den mest grunnleggende metoden innebærer å åpne de involverte filene, og fra arket du vil duplisere, bruke formateringsalternativet til å flytte eller kopiere det . Det er en enkel tilnærming som ikke krever formler eller automatisering, perfekt når du bare har med noen få ark å gjøre.

Trinnene er, i grove trekk, følgende:

• Åpne alle Excel-arkene du vil kombinere til én arbeidsbok.
• Plasser markøren på fanen på arket du vil kopiere. mot en annen bok.
• Gå til Hjem-fanen, og skriv inn alternativet i Celler-gruppen. Format, der kommandoen vises for flytte eller kopiere ark.
• I boksen som dukker opp, kan du velge Til hvilken bok skal arket sendes? (en som allerede er åpen eller en ny bok som skal lages) og i hvilken posisjon den skal plasseres.
• Sørg for å merke av i boksen Lag en kopi hvis du vil at det opprinnelige arket skal forbli i kildefilen.

Når arkene du vil kombinere er i samme arbeidsbok, kan du også velge flere faner samtidig ved å bruke Ctrl (for å velge individuelle ark) eller Shift (for sammenhengende områder) og gjenta flytte- eller kopieringsprosessen. På denne måten kan du kompilere forskjellige ark fra flere arbeidsbøker til én fil uten å miste originalene.

Slå sammen flere ark fra forskjellige filer ved hjelp av VBA-kode

Når dataene dine er spredt over mange filer og du ønsker å automatisere prosessen, er VBA (Visual Basic for Applications) en kraftig sveitsisk lommekniv . Med en makro kan du åpne en mappe full av Excel-arbeidsbøker og kopiere alle arkene til en hovedfil på få sekunder.

La oss ikke lure oss selv: bruk av makroer krever et visst nivå av kodekunnskap . Du trenger ikke å være en profesjonell programmerer, men du må vite hvordan du åpner Visual Basic-editoren, limer inn en makro og kjører den uten frykt.

Et typisk eksempel på en makro for å slå sammen ark fra flere filer gjør følgende: den åpner en filvalgboks, lar deg velge flere arbeidsbøker, åpner hver fil, går gjennom alle arkene og kopierer dem til slutten av hovedarbeidsboken, og når den er ferdig, lukker den kildearbeidsbøkene.

Arbeidsflyten ligner på følgende:

• Opprett en ny bok som skal fungere som en «hovedfil».
• Press ALT + F11 for å åpne Visual Basic Editor.
• Sett inn en modul Opprett en ny kopi og lim inn makrokoden som skal håndtere gjennomgang av bøkene og kopiering av arkene.
• Lagre filen som en makroaktivert arbeidsbok og Kjør makroen (F5) fra redaktøren.
• Når filutforskeren vises, velger du Excel-filene som inneholder arkene som skal slås sammen, og godtar.

Etter noen få øyeblikk vil du se at alle arkene fra de valgte filene har dukket opp i hovedarbeidsboken din , ordnet etter hverandre. Derfra kan du omorganisere, gi nytt navn eller rydde opp etter behov.

Et annet mye brukt alternativ er et VBA -skript som automatisk iterererer gjennom alle .xlsx-filene i en gitt mappe , åpner dem én etter én og kopierer arkene til hovedarbeidsboken. Dette er en fantastisk tilnærming når du gjentar operasjonen ofte og alltid lagrer rapportene i samme mappe.

Vise data fra flere ark i et hovedark ved hjelp av konsolideringsverktøyet

I tillegg til å kopiere hele ark, ønsker man ofte ett enkelt hovedark med oppsummerte data fra mange ark , uten å måtte manipulere hver fane manuelt. Det er her Excels konsolideringsfunksjon kommer inn i bildet igjen.

Tanken er å ha detaljarkene separate (for eksempel ett per måned eller ett per forretningsområde) og lage en tabell på hovedarket som gjenspeiler summen, gjennomsnittet eller antallet av alle . Dette hovedarket kan være grunnlaget for diagrammer, dashbord eller periodiske rapporter.

Avhengig av hvordan dataene dine er organisert, kan du velge konsolidering etter posisjon eller kategori , som vi så tidligere. Det viktigste er at kildeområdene er definert som rene lister (uten tomme rader eller kolonner imellom), og at etikettene og strukturene er konsistente på tvers av de forskjellige arkene.

Denne tilnærmingen er spesielt nyttig når du håndterer store mengder informasjon, og du bare trenger et sammendrag på hovedarket, ikke alle detaljene linje for linje . Likevel kan du alltid beholde de originale arkene med detaljene hvis du trenger å gå dypere inn i det senere.

Slå sammen flere ark uten duplikater: kombiner og rens

En annen veldig vanlig situasjon er når du slår sammen flere ark med lignende poster (for eksempel kundelister, produkter eller transaksjoner) og du ender opp med dupliserte rader etter å ha kombinert alt. Excel har ikke en magisk knapp som kombinerer ark og fjerner duplikater samtidig, men du kan kjede sammen to verktøy for å oppnå dette.

Ideen er klar: først kombinerer du arkene (med Kopier og Lim inn, med Power Query, med Coupler.io eller med metoden du foretrekker) for å ha alle dataene samlet i én tabell. Når dette er gjort, fortsetter du med å rydde i tabellen ved hjelp av alternativet Fjern duplikater i selve Excel.

Slik gjør du det fra båndet :

• Merk tabellen der du har de sammenslåtte dataene.
• Gå til fanen Data og i Dataverktøy-gruppen klikker du på fjerne duplikater.
• I vinduet som vises, merk av eller fjern merket. Dataene mine har overskrifter etter behov, slik at Excel kan skille mellom overskriftene på riktig måte.
• Velg hvilke kolonner du vil bruke for å oppdage dupliserte poster; hvis du velger alle kolonneneBare rader som samsvarer i alle feltene vil bli slettet.
• Godta, og Excel vil vise deg hvor mange duplikater som er fjernet og hvor mange unike verdier som er igjen.

Med denne kombinasjonen av sammenslåing og opprydding ender du opp med én enkelt, ren tabell uten duplikater , perfekt for rapporter, pivottabeller eller eksport til andre systemer.

Slå sammen Excel-ark basert på en felles kolonne

I mange virkelige scenarier ønsker du ikke bare å stable poster, men også å koble komplementær informasjon spredt på tvers av forskjellige tabeller . For eksempel en finanstabell med beløp knyttet til en produkt-ID og en salgstabell med detaljer om det samme produktet; logisk nok vil du kombinere dem ved hjelp av den felles nøkkelen.

Det finnes to hovedmåter å gjøre dette på i et brukermiljø: å bruke et eksternt verktøy som Coupler.io , som automatiserer sammenføyningen, eller å bruke Power Query , som er integrert i moderne Excel og også tillater databaselignende sammenføyninger mellom tabeller.

Metode som bruker Coupler.io for å koble sammen ark med en kolonne

Coupler.io er en skybasert løsning som er utviklet for å automatisere rapporter og flytte data mellom ulike kilder . Blant funksjonene kan du importere flere Excel-regneark, kombinere dem og laste resultatet inn i et annet regneark eller til og med i analyseverktøy som BigQuery eller Looker Studio.

Hvis du for eksempel vil slå sammen et ark kalt «Finansistabell» og et annet kalt «Salgstabell» basert på produkt-ID- kolonnen , vil den typiske flyten være slik:

• Hos Coupler.io velger du begge deler opprinnelse så vel som destinasjon Microsoft Excel i den opprinnelige formen og fortsette.
• Du kobler til Microsoft-kontoen din, velger Excel-filen og velger arket som første kilde. Finanstabell.
• Du bruker alternativet med koble til en annen kilde for å legge til det andre arket, i dette tilfellet Salgstabell, fra samme fil eller fra en annen.
• I trinnet for kombinasjonskonfigurasjon velger du operasjonstype: Tilføy (for å stable tabeller med samme struktur) eller Bli med (for å matche data ved hjelp av en unik identifikatorkolonne).
• Du velger Bli med og du definerer «Produkt-ID» som nøkkelkolonnen i begge arkene.
• Du ser gjennom forhåndsvisningen av de sammenkoblede dataene og justerer kolonnene du vil beholde.

Deretter, i måldelen, velger du filen og Excel-arket der den sammenslåtte tabellen skal lagres. Du kan planlegge automatiske oppdateringer slik at Coupler.io oppdaterer dataene med jevne mellomrom uten at du må gjenta hele prosessen manuelt.

Koble sammen ark med Power Query basert på en felles kolonne

Hvis du foretrekker å holde deg innenfor Excels økosystem, er Power Query det perfekte verktøyet for å kombinere tabeller basert på samsvarende kolonner. Det er et gratis tillegg i Excel 2010 og 2013, og en innebygd funksjon som starter med Excel 2016.

La oss igjen anta at du har to tabeller: én i arket «Finanstabell» og en annen i arket «Salgstabell», begge med kolonnen «Produkt-ID». Målet er å opprette én enkelt tabell som kombinerer dataene fra begge basert på den identifikatoren.

Prosessen kan deles inn i flere trinn:

Trinn 1: Opprett Power Query-tilkoblingene

• Finn den første tabellen i Excel-arbeidsboken din (for eksempel i en celle på arket «Salgstabell»).
• I Data-fanen, i seksjonen Få og transformer, velg alternativet Fra tabell eller område for å laste inn tabellen i Power Query.
• I boksen som åpnes, merk av eller fjern merket for Tabellen min har overskrifter etter behov; i de fleste tilfeller vil du la denne boksen være avkrysset.
• Power Query-redigeringsprogrammet åpnes. Hvis du ikke vil laste inn tabellen i arbeidsboken ennå, velger du Lukk og last inn… og sjekk alternativet Bare opprett en forbindelse.
• Gjenta prosessen med det andre arket (for eksempel «Finanstabell») for å opprette den tilhørende tilkoblingen. I bokens spørrepanel vil du nå se begge koblingene..

Trinn 2: Slå sammen spørringer med en felles kolonne

• I Excel velger du en av spørringene og går til Data > Ny spørring (eller direkte fra spørrepanelet) > Kombiner spørringer > kombinere.
• I flettevinduet velger du «Finanstabell» som den første tabellen og «Salgstabell» som den andre.
• I hver tabell, Klikk på kolonnen «Produkt-ID» for å markere det som et samsvarende felt; du vil se at det er uthevet når valget er vellykket.
• La sammenføyningstypen være standard, eller juster den etter behov (f.eks. indre sammenføyning, venstre sammenføyning osv.).
• Godta slik at Power Query genererer en ny spørring med en ekstra kolonne som inneholder den relaterte «Salgstabellen» for hver rad.

Trinn 3: Utvid kolonnene i den kombinerte tabellen

• I Power Query-redigeringsprogrammet ser du en kolonne med navnet på den andre tabellen, som inneholder ordet «Tabell» i hver rad.
• Klikk på ikonet med de to pilene ved siden av navnet på kolonnen for å utvide den.
• I popup-boksen velger du hvilke kolonner fra «Salgstabellen» du vil legge til i kombinasjonen (for eksempel «Varer» og «Beløp ($)»).
• Hvis du ikke vil at det opprinnelige tabellnavnet skal vises som et prefiks i feltene, Fjern merket i boksen for å bruke det opprinnelige kolonnenavnet som prefiks og godta.

Trinn 4: Last inn den sammenslåtte tabellen i Excel

• Når tabellen nå er kombinert og renset i Power Query Editor, går du til Lukk og last inn….
• Velg om du vil laste inn dataene som tabell i et nytt ark eller på et eksisterende ark, avhengig av hvordan du vil organisere boken din.

Når du er ferdig, vil du ha en tabell i Excel med dataene fra begge arkene koblet sammen av den felles kolonnen , med alle feltene du valgte i utvidelsestrinnet.

Bruk spørringer og tilkoblinger til å koble sammen ark fra samme fil

Mange brukere oppdager nesten ved en tilfeldighet at Excels funksjon for spørringer og tilkoblinger lar dem enkelt kombinere flere ark i samme fil, forutsatt at de deler en felles struktur. Denne funksjonen er avhengig av Power Query, men grensesnittet er ganske brukervennlig.

Hvis du for eksempel har tre ark med de samme kolonnene (Dato – Element – ​​Verdi) og du vil liste alle postene i én tabell sortert etter dato , er denne tilnærmingen svært praktisk og fleksibel.

De typiske trinnene ville være:

• På Data-fanen velger du Ny spørring (eller «Hent data», avhengig av versjonen din) > Fra fil > Fra bok.
• Velg Excel-filen som inneholder arkene du vil kombinere; Det kan til og med være boken du jobber med..
• I nettleseren, merk av i boksen for Velg flere elementer og merk alle arkene du vil inkludere.
• Klikk på Transformer data for å åpne tabellene i Power Query Editor.
• Fjern kolonnene du ikke trenger i hver spørring, og Sørg for at alle tabellene har nøyaktig samme struktur (samme kolonner, samme datatype).
• Bruk alternativene på Hjem-menyen i Power Query til å legg til spørringeneslik at du til slutt har én enkelt spørring som inneholder radene fra alle arkene.
• Når den endelige spørringen er klar, trykk Lukk og last inn slik at den legges til i boken som en tabell på et nytt ark.

Derfra kan du sortere etter dato, bruke farger i henhold til datakilden, eller til og med legge til en kolonne som identifiserer hvilket ark hver post kommer fra , noe som er veldig nyttig for mer avansert analyse.