Excel kombinira nekoliko radnih listova u jedan radni list. Svaki radni list može imati različit broj zapisa iz dana u dan, tako da formule nisu pravi put. Umjesto toga, malo poznati alat pod nazivom Power Query omogućit će vam jednostavno i brzo spajanje podataka.
Gledaj video
- Doug: Kako kombinirati četiri lista gdje svaki ima različit broj redaka?
- Koristite Power Query
- Oblikujte svaki radni list kao tablicu pomoću Ctrl + T
- Preimenujte tablice
- Za svaku tablicu novi upit Iz tablice. Dodajte prilagođeni stupac za Regija
- Umjesto Zatvori i učitaj, odaberite Zatvori i učitaj za … Samo izradi vezu
- Koristite novi upit, kombinirajte upit, dodajte. 3 ili više tablica. Odaberite Tablice i Dodaj
- Zatvori i učitaj i podaci se pojavljuju na novom radnom listu
- Za jednu tablicu s dodatnim stupcem: podaci se prikazuju samo za zapise tog lista
- Za jednu tablicu u kojoj su stupci bili u pogrešnom redoslijedu: Power Query je ispravno funkcionirao!
- Lako se kasnije osvježiti
Automatski generirani prijepis
- Naučite Excel iz epizode Podcast
- 2178 spajanje današnjih radnih listova
- pitanje putem YouTubea od Douga ima ovo
- situacija imamo četiri lista gdje svaki
- list je regija s podacima o prodaji i
- broj zapisa mijenja se mjesečno i
- trenutno Doug pokušava koristiti formule
- ali kad je to promijenio broj redaka
- postaje noćna mora, pa sam rekla hej
- Doug možemo li koristiti upit snage ako imate
- Excel 2010 ili Excel 2013 besplatan je
- preuzmite s Microsofta ili je izgrađen
- u 2016. i Office 365 kaže da sve
- točno, evo što imamo
- četiri izvješća središnja regija istok
- regija južna regija i zapad
- regija i svaka ima drugačiju
- broj zapisa kao ovdje na Jugu
- Regija imamo 72 zapisa na istoku
- regija 193 zapisa i ovo će
- promijeni udesno
- svaki put kad pokrenemo ovo izvješće
- sada imam drugačiji broj zapisa
- ovdje prvo napravio neke pretpostavke da
- ne postoji kolona koja se naziva središnja i
- tada ću također biti potpuno zao
- ovdje i uzmi Južnu regiju koju želim
- probaj zeznuti ja ću uzeti
- stupac dobiti izrežite ga i zalijepite kako treba
- preokrenuti ih i onda u redu pa mi
- imaju jedan gdje su stupci obrnuti
- a onda još jedan kamo ćemo
- dodajte dodatni stupac postotaka bruto dobiti
- pa će ovo biti dobit podijeljena sa
- prihod u idealnom svijetu to su sve
- oblikovana potpuno isto, ali kako sam naučila
- nedavno sam održavao seminar dolje u
- Sjeverna Karolina ako nisu sve u redu
- netko je imao situaciju dobro znate
- na pola godine stvari su se promijenile
- i dodali su novi stupac ili potez
- kolone oko nas bili smo zaista sretni
- vidi da je upit snage mogao riješiti
- s ovim sve u redu pa ćemo uzeti
- svako od ovih izvješća i napravite ga
- službeni format tablice kao stol
- to je kontrolni čaj ili biste mogli koristiti a
- raspon imena za mene kontrola t je
- lakši put i što rade ovdje
- da li će ovaj stol nazvati onim koji ću nazvati
- preimenovati ovo da se zove centralno i
- zatim idemo na Istočnu kontrolu T kliknite U redu
- i ovo će se sada zvati Istok, hej
- na ranijem podcastu pokazao sam kako ako
- to su bile četiri zasebne datoteke
- mogao upotrijebiti upit snage samo za
- kombinirati datoteke, ali to ne radi kad
- to su četiri odvojena ili četiri radna lista
- u istoj knjizi tako dobro tamo mi
- idi i onda
- poput ove kontrole-t pomalo zamorne
- postavi ovo prvi put, ali dječak
- bit će super
- svaki put kad ovo kasnije morate ažurirati
- pa ćemo ići
- odabrati ovaj prvi središnji stol
- i ako ste u 2010 ili 2013 i
- preuzeto napajanje upit ćete
- imat će svoju karticu, ali za 16 in
- Excel 2016 zapravo dobiva
- transformirana što je druga skupina u
- Office 365 sada se transformira
- koja je prva grupa i tako smo
- reći će da će ovo stvoriti
- podaci iz tablice ili raspona u redu i
- 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
- govoreći to pitanje dobro hvala
- za svratak vidimo se sljedeći put
- za još jedan neto ulog od
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Podcast2178.xlsm