Excel łączy kilka arkuszy w jeden arkusz. Każdy arkusz może mieć codziennie inną liczbę rekordów, więc formuły nie są dobrym rozwiązaniem. Zamiast tego mało znane narzędzie o nazwie Power Query pozwoli Ci łatwo i szybko scalić dane.
Obejrzyj wideo
- Doug: Jak połączyć cztery arkusze, z których każdy ma inną liczbę rzędów?
- Użyj dodatku Power Query
- Sformatuj każdy arkusz jako tabelę za pomocą Ctrl + T
- Zmień nazwy tabel
- Dla każdej tabeli nowe zapytanie From Table. Dodaj niestandardową kolumnę dla regionu
- Zamiast Close & Load, wybierz Close & Load to… Only Create a Connection
- Użyj nowego zapytania, połącz zapytanie, dołącz. 3 lub więcej tabel. Wybierz tabele i dodaj
- Zamknij i załaduj, a dane pojawią się w nowym arkuszu
- Dla jednej tabeli z dodatkową kolumną: dane pokazują się tylko dla rekordów tego arkusza
- W przypadku jednej tabeli, w której kolumny były w złej kolejności: dodatek Power Query działał poprawnie!
- Łatwe do odświeżenia później
Transkrypcja generowana automatycznie
- Naucz się programu Excel z odcinka podcastu
- 2178 scalanie arkuszy roboczych dzisiaj
- pytanie przez YouTube od Douga ma to
- sytuacji mamy cztery arkusze, gdzie każdy
- arkusz to region z danymi sprzedaży i
- liczba rekordów zmienia się co miesiąc i
- w tej chwili Doug próbuje użyć formuł
- ale kiedy liczba wierszy to zmieniła
- staje się koszmarem, więc powiedziałem hej
- Doug, czy możemy użyć zapytania Power, jeśli masz
- Excel 2010 lub Excel 2013 jest darmowy
- pobrać z firmy Microsoft lub jest zbudowany
- w 2016 i Office 365 mówi tak, wszystko
- tak więc oto, co mamy
- cztery donosi o regionie centralnym na wschodzie
- region południowy i zachodni
- region i każdy ma inny
- liczba rekordów, jak tutaj na południu
- W regionie mamy 72 rekordy na wschodzie
- region 193 rekordy i to będzie
- zmienić w prawo
- za każdym razem, gdy uruchomimy ten raport
- mam teraz inną liczbę rekordów
- najpierw poczynił pewne założenia
- nie ma kolumny zwanej central i
- wtedy też będę całkowicie zły
- tutaj i wybierz region południowy, który chcę
- spróbuj to schrzanić, wezmę
- kolumna zysku wytnij i wklej, jak to zrobić
- odwróć je, a potem w porządku, więc my
- ma taki, w którym kolumny są odwrócone
- a potem kolejny, w którym będziemy
- dodaj dodatkową kolumnę procent zysku brutto
- więc to będzie zysk podzielony przez
- dochody w idealnym świecie to wszystko
- ukształtowane dokładnie tak samo, ale jak się nauczyłem
- Niedawno prowadziłem seminarium w
- Karolina Północna, jeśli nie są w porządku
- ktoś miał dobrze znaną sytuację
- w połowie roku wszystko się zmieniło
- i dodali nową kolumnę lub ruch
- kolumny wokół nas były naprawdę szczęśliwe
- zobacz, że power query był w stanie sobie poradzić
- z tym dobrze, więc bierzemy
- każdego z tych raportów i dostać się do
- oficjalny format tabeli jako tabela tzw
- to jest herbata kontrolna lub możesz użyć
- zakres nazwy dla mnie kontrolka t to
- łatwiejszy sposób i co oni tutaj robią
- czy nazywają ten stół pierwszym, którym zamierzam
- zmień jego nazwę na nazwę centralną i
- następnie przechodzimy do sterowania wschodniego T kliknij OK
- i teraz będzie się nazywał Wschodem, hej
- we wcześniejszym podcastie pokazałem, jak to zrobić
- były to cztery oddzielne pliki my
- mógł użyć power query tylko do
- łączyć pliki, ale to nie działa, kiedy
- to cztery oddzielne lub cztery arkusze
- w tej samej książce tak dobrze tam my
- idź i wtedy
- jak ta kontrola - to trochę uciążliwe
- założyłem to pierwszy raz, ale chłopcze
- będzie wspaniale
- za każdym razem, gdy będziesz musiał to później zaktualizować
- więc zamierzamy to zrobić
- aby wybrać ten pierwszy stół centralny
- region i jeśli jesteś w 2010 lub 2013 i
- pobrane zapytanie zasilania, zrobisz to
- będzie mieć własną kartę, ale za 16 cali
- Excel 2016 faktycznie się rozwija
- przekształcona, która jest drugą grupą w
- Office 365 przechodzi teraz transformację
- która jest pierwszą grupą, więc jesteśmy
- powiem, że zamierzają to stworzyć
- dane z tabeli lub zakresu w porządku 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
- mówiąc to pytanie dobrze, dziękuję
- za zatrzymanie się, do zobaczenia następnym razem
- za kolejną siatkę zarzuconą z
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2178.xlsm
