Excel kombinerer flere regneark i et enkelt regneark. Hvert regneark kan ha et annet antall poster fra dag til dag, så formler er ikke veien å gå. I stedet vil et lite kjent verktøy kalt Power Query la deg slå sammen dataene enkelt og raskt.
Se på video
- Doug: Hvordan kombinere fire ark der hvert har forskjellige antall rader?
- Bruk Power Query
- Formater hvert regneark som en tabell med Ctrl + T.
- Gi bordene nytt navn
- For hver tabell, nytt spørsmål fra tabell. Legg til en tilpasset kolonne for Region
- I stedet for Lukk og last, velg Lukk og last til … Bare opprett en tilkobling
- Bruk ny spørring, kombiner spørring, legg til. 3 eller flere bord. Velg Tabeller og Legg til
- Lukk og last, og dataene vises på et nytt regneark
- For den ene tabellen med ekstra kolonne: dataene vises bare for arkets poster
- For den ene tabellen der kolonnene var i feil rekkefølge: Power Query fungerte riktig!
- Lett å oppdatere senere
Autogenerert transkripsjon
- Lær Excel fra Podcast Episode
- 2178 fusjonere for regneark i dag
- spørsmål via YouTube fra Doug har dette
- situasjonen har vi fire ark hvor hver
- arket er en region med salgsdata og
- antall poster endres månedlig og
- akkurat nå prøver Doug å bruke formler
- men da antall rader endret det
- blir et mareritt rett så jeg sa hei
- Doug kan vi bruke strømspørsmål hvis du har det
- Excel 2010 eller Excel 2013 er gratis
- laste ned fra Microsoft eller det er bygget
- inn i 2016 og office 365 sier det ja alt
- akkurat så her har vi det vi har
- fire rapporter sentralregionen øst
- region sør-regionen og vest
- region og hver har en annen
- antall poster som her i sør
- Region vi har 72 poster i øst
- region 193 poster og dette kommer til å
- endre rett
- hver gang vi kjører denne rapporten vil ha
- et annet antall poster nå har jeg
- gjorde noen antakelser her først at
- det er ikke en kolonne som heter sentral og
- da blir jeg også helt ond
- her og ta Sør-regionen jeg vil
- prøv å skru det opp, jeg skal ta
- profitt kolonne kutte den og lime den hvordan
- snu dem og så greit så vi
- ha en der kolonnene er omvendt
- og så en til der vi skal
- legg til en ekstra kolonne brutto fortjeneste prosent
- så dette blir fortjeneste delt på
- inntekter i en ideell verden dette er alle
- formet nøyaktig det samme, men som jeg lærte
- nylig holdt jeg på et seminar nede i
- North Carolina hvis de ikke er i orden
- noen hadde en situasjon, vel du vet
- halvveis i året endret ting seg
- og de la til en ny kolonne eller flytte
- spalter rundt var vi veldig glade for
- se at kraftforespørsel var i stand til å takle
- med dette i orden så vi skal ta
- hver av disse rapportene og gjøre det til
- et offisielt bordformat som tabell så
- det er kontrollte eller du kan bruke en
- Navneområde for meg kontroll t er
- enklere vei å gå og hva de gjør her
- kaller de dette bordet det skal jeg
- omdøpe dette for å bli kalt sentralt og
- så fortsetter vi til østkontrollen T klikker OK
- og dette blir kalt øst nå hei
- på en tidligere podcast viste jeg hvordan hvis
- dette har vært fire separate filer vi
- kunne ha brukt strømforespørsel bare til
- kombinere filer, men det fungerer ikke når
- de er fire separate eller fire regneark
- i samme bok så godt der vi
- gå og da
- som denne kontrollen-t litt kjedelig å
- satt opp dette første gang men gutt
- det blir fantastisk
- hver gang du må oppdatere dette senere
- så vi skal gjøre er at vi skal
- å velge dette første bordet sentralt
- region og hvis du er i 2010 eller 2013 og
- nedlastet strømforespørsel, du kommer til å bli det
- skal ha sin egen fane, men i 16 i
- Excel 2016 blir faktisk
- transformert som er den andre gruppen i
- office 365 blir nå transformert
- som er den første gruppen, og så er vi
- skal si at de skulle lage dette
- data fra en tabell eller rekkevidde ok og
- there is our data now we don't have a
- region field and the combined files
- would have added the region field so in
- this case I'm just gonna add a column a
- new custom call the headings gonna be
- region and this one is going to be what
- was this central right like that
- click OK alright now here's the
- important part when we're done this with
- this we're gonna go home not choose
- close and load we're gonna open the
- drop-down close and load to only create
- a connection click OK
- perfect we have our connection only now
- the next thing we have to do is repeat
- these steps for the next three regions
- and now that would be really a bit
- boring to you so let's just speed up the
- video to 10x for this
- alright there we are for connections set
- up now here's where we're going to do
- the magic I'm gonna insert a new blank
- worksheet and I'm gonna say get data
- combine queries and I want to append two
- queries from this workbook and I'm gonna
- say three or more tables and the
- available tables are Central through
- West click Add BAM click OK and then we
- can close and load and what we have here
- is we have a superset of all of the
- records in all of the tables all right
- and where we tried to screw it up where
- I purposely tried to screw up by
- reversing cost of goods sold and profit
- down in what was that that was Central
- East South in the South Region I'll just
- go check those right and it looks like
- yeah generally feels right they used the
- heading to figure it out because the
- profit is always higher than cost of
- goods sold and so that worked and then
- down here in the West where we added
- gross profit percent we actually get
- that data for the tables that had it and
- for the tables that didn't have it we
- just get null which is perfect alright
- now duck
- here's what you're gonna do so the next
- time that you have some more data and
- I'll just let's create some some extra
- records here we'll just add some ABC
- with a date of today and all retail and
- it's called Doug's new records and just
- some garbage out here let's just put in
- a hundred all the way across in the
- interest of time okay so now because
- this is a table the table automatically
- expands to the new records which is
- beautiful had they been named range I
- would have had a redefine that's why I
- really like the table instead of the
- name range but we come back here to the
- resulting workbook with 563 rows loaded
- and I click refresh
- and bam now I have 572 Rose loaded
- including let's see if we can find them
- in here
- Doug's new records right there at the
- end of the South Region
- isn't that just an awesome awesome way
- to go yes it definitely takes longer to
- set up the first day we're up to seven
- minutes already if I hadn't sped that up
- to 10x but once it's set up now life is
- gonna be super super easy from here on
- out way this is where I usually promote
- my own book but no this time let's talk
- about this awesome book Emma's for data
- monkey by Ken polls in Miguel Escobar
- everything I learned about power query I
- learned from this book look at the eye
- on the top right hand corner for more
- information about that book all right
- wrap up topics in this episode Doug how
- to combine four sheets where each sheet
- has a different number of rows we can
- use power query make sure to format each
- worksheet as a table with ctrl T or use
- named ranges but I prefer ctrl T rename
- the tables from each table choose new
- query from table add a custom column for
- a region and then instead of close and
- load choose close and load to only
- create a connection do that for all four
- queries and then new query combined
- query append choose three or more tables
- choose the tables and click Add
- now some older versions of power query
- you couldn't do three or more tables you
- have to do two and then do another query
- to add the third one and then do another
- query to add the fourth one either way
- it would be more hassle that way I'm
- glad that they added the three or more
- tables close and load this time close
- and load to the worksheet and and then
- later on if you add more data to any of
- the four tables just go back to your
- query and click refresh and you're good
- to go
- power query and amazing new feature from
- Microsoft I love it I thank Doug for
- sier det spørsmålet det vel takk
- for å stikke innom, vi sees neste gang
- for nok et nettstøp fra
Last ned fil
Last ned eksempelfilen her: Podcast2178.xlsm
