Excel menggabungkan beberapa lembar kerja menjadi satu lembar kerja. Setiap lembar kerja mungkin memiliki jumlah rekaman yang berbeda dari hari ke hari, jadi rumus bukanlah cara yang tepat. Sebagai gantinya, alat yang kurang dikenal yang disebut Power Query akan memungkinkan Anda menggabungkan data dengan sederhana dan cepat.
Menonton video
- Doug: Bagaimana cara menggabungkan empat lembar yang masing-masing memiliki jumlah baris yang berbeda?
- Gunakan Power Query
- Format setiap lembar kerja sebagai Tabel dengan Ctrl + T
- Ubah nama tabel
- Untuk setiap tabel, kueri baru Dari Tabel. Tambahkan kolom kustom untuk Region
- Alih-alih Tutup & Muat, pilih Tutup & Muat ke… Hanya Buat Koneksi
- Gunakan Kueri Baru, Gabungkan Kueri, Tambahkan. 3 atau Lebih Tabel. Pilih Tabel dan Tambahkan
- Tutup dan Muat dan data muncul di lembar kerja baru
- Untuk satu tabel dengan kolom ekstra: data muncul hanya untuk catatan lembar itu
- Untuk satu tabel di mana kolom berada dalam urutan yang salah: Power Query bekerja dengan benar!
- Mudah disegarkan nanti
Transkrip yang Dibuat Otomatis
- Pelajari Excel dari Podcast Episode
- 2178 bergabung untuk lembar kerja hari ini
- pertanyaan melalui YouTube dari Doug memiliki ini
- situasi kami memiliki empat lembar di mana masing-masing
- sheet merupakan wilayah dengan data penjualan dan
- jumlah record berubah setiap bulan dan
- sekarang Doug mencoba menggunakan rumus
- tetapi ketika jumlah baris berubah itu
- menjadi mimpi buruk kan jadi saya bilang hei
- Doug bisa kita gunakan power query jika Anda punya
- Excel 2010 atau Excel 2013 gratis
- unduh dari Microsoft atau sudah terpasang
- hingga 2016 dan Office 365 dikatakan ya semua
- benar jadi inilah yang kita miliki
- empat melaporkan wilayah tengah di timur
- wilayah wilayah selatan dan barat
- wilayah dan masing-masing memiliki perbedaan
- jumlah rekor seperti di sini di Selatan
- Wilayah kami memiliki 72 catatan di timur
- wilayah 193 catatan dan ini akan
- ubah benar
- setiap kali kami menjalankan laporan ini akan ada
- jumlah record yang berbeda sekarang saya sudah
- membuat beberapa asumsi di sini dulu itu
- tidak ada kolom yang disebut pusat dan
- maka aku juga akan benar-benar jahat
- di sini dan ambil Wilayah Selatan yang saya inginkan
- mencoba dan mengacaukannya aku akan mengambil
- kolom profit potong dan paste caranya
- membalikkan itu dan kemudian baik-baik saja jadi kita
- memiliki satu tempat kolom dibalik
- dan satu lagi di mana kita akan pergi
- tambahkan kolom ekstra persentase laba kotor
- jadi ini akan menjadi keuntungan dibagi
- pendapatan di dunia yang ideal, ini semua
- berbentuk persis sama tetapi seperti yang saya pelajari
- baru-baru ini saya sedang melakukan seminar di
- North Carolina jika mereka tidak baik-baik saja
- seseorang memiliki situasi yang baik, Anda tahu
- setengah tahun hal-hal berubah
- dan mereka menambahkan kolom baru atau pindah
- kolom di sekitar kami sangat senang
- melihat bahwa power query mampu menangani
- dengan ini baik-baik saja jadi kami akan mengambil
- masing-masing laporan ini dan buatlah menjadi
- format tabel resmi seperti tabel jadi
- yang mengontrol teh atau Anda bisa menggunakan a
- rentang nama bagi saya kontrol t adalah
- cara yang lebih mudah untuk pergi dan apa yang mereka lakukan di sini
- apakah mereka menyebut meja ini sebagai yang akan saya lakukan
- ganti namanya menjadi central dan
- lalu kita lanjutkan ke kontrol Timur T klik OK
- dan ini akan disebut Timur sekarang, hei
- di podcast sebelumnya saya menunjukkan bagaimana jika
- ini telah menjadi empat file terpisah kami
- bisa saja menggunakan power query hanya untuk
- menggabungkan file tetapi itu tidak berfungsi saat
- mereka empat lembar kerja terpisah atau empat
- di buku yang sama begitu baik di sana kami
- pergi dan kemudian
- seperti kontrol-t ini agak membosankan
- mengatur ini pertama kali tapi nak
- akan ada yang luar biasa
- setiap kali Anda harus memperbaruinya nanti
- jadi kita akan lakukan adalah kita pergi
- untuk memilih pusat tabel pertama ini
- wilayah dan jika Anda berada di tahun 2010 atau 2013 dan
- download power query Anda akan mendapatkannya
- akan memiliki tab sendiri tetapi dalam 16 in
- Excel 2016 sebenarnya mendapatkan
- transformed yang merupakan grup kedua di
- Office 365 kini bertransformasi
- yang merupakan kelompok pertama dan jadi kami
- akan mengatakan mereka akan membuat ini
- data dari tabel atau rentang baik-baik saja dan
- 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
- mengatakan pertanyaan itu dengan baik terima kasih
- untuk mampir sampai jumpa di lain waktu
- untuk pemain jaring lain dari
Unduh berkas
Unduh file contoh di sini: Podcast2178.xlsm
