Kombinirajte 4 lista - Excel savjeti

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

Zanimljivi članci...