Konten
Rumusan masalah
Mari kita lihat solusi yang bagus untuk salah satu situasi yang sangat standar yang dihadapi sebagian besar pengguna Excel cepat atau lambat: Anda perlu mengumpulkan data dengan cepat dan otomatis dari sejumlah besar file ke dalam satu tabel final.
Misalkan kita memiliki folder berikut, yang berisi beberapa file dengan data dari kota cabang:
Jumlah file tidak masalah dan dapat berubah di masa mendatang. Setiap file memiliki lembar bernama Penjualandi mana tabel data berada:
Jumlah baris (urutan) dalam tabel, tentu saja, berbeda, tetapi set kolom standar di mana-mana.
Tugas: mengumpulkan data dari semua file ke dalam satu buku dengan pembaruan otomatis berikutnya saat menambahkan atau menghapus file kota atau baris dalam tabel. Menurut tabel konsolidasi akhir, maka dimungkinkan untuk membuat laporan, tabel pivot, data pengurutan filter, dll. Yang utama adalah dapat mengumpulkan.
Kami memilih senjata
Untuk solusinya, kita memerlukan versi terbaru Excel 2016 (fungsi yang diperlukan sudah ada di dalamnya secara default) atau versi Excel 2010-2013 sebelumnya dengan add-in gratis yang diinstal Kueri Daya dari Microsoft (unduh di sini). Power Query adalah alat yang sangat fleksibel dan sangat kuat untuk memuat data ke Excel dari dunia luar, lalu menghapus dan memprosesnya. Power Query mendukung hampir semua sumber data yang ada – dari file teks hingga SQL dan bahkan Facebook
Jika Anda tidak memiliki Excel 2013 atau 2016, maka Anda tidak dapat membaca lebih lanjut (bercanda). Di versi Excel yang lebih lama, tugas seperti itu hanya dapat diselesaikan dengan memprogram makro dalam Visual Basic (yang sangat sulit bagi pemula) atau dengan menyalin manual secara monoton (yang membutuhkan waktu lama dan menghasilkan kesalahan).
Langkah 1. Impor satu file sebagai sampel
Pertama, mari impor data dari satu buku kerja sebagai contoh, sehingga Excel "mengambil ide". Untuk melakukan ini, buat buku kerja kosong baru dan…
- jika Anda memiliki Excel 2016, lalu buka tab Data lalu Buat Kueri – Dari File – Dari Buku (Data — Kueri Baru- Dari file — Dari Excel)
- jika Anda memiliki Excel 2010-2013 dengan add-in Power Query terinstal, lalu buka tab Kueri Daya dan pilih di atasnya Dari file – Dari buku (Dari file — Dari Excel)
Kemudian, di jendela yang terbuka, buka folder kami dengan laporan dan pilih salah satu file kota (tidak masalah yang mana, karena semuanya khas). Setelah beberapa detik, jendela Navigator akan muncul, di mana Anda perlu memilih lembar yang kami butuhkan (Penjualan) di sisi kiri, dan isinya akan ditampilkan di sisi kanan:
Jika Anda mengklik tombol di sudut kanan bawah jendela ini Unduh (Beban), maka tabel akan langsung diimport ke sheet dalam bentuk aslinya. Untuk satu file, ini bagus, tetapi kita perlu memuat banyak file seperti itu, jadi kita akan sedikit berbeda dan klik tombol Koreksi (Ubah). Setelah itu, editor kueri Power Query harus ditampilkan di jendela terpisah dengan data kami dari buku:
Ini adalah alat yang sangat kuat yang memungkinkan Anda untuk "menyelesaikan" tabel ke tampilan yang kita butuhkan. Bahkan deskripsi dangkal dari semua fungsinya akan memakan waktu sekitar seratus halaman, tetapi, jika sangat singkat, dengan menggunakan jendela ini Anda dapat:
- menyaring data yang tidak perlu, baris kosong, baris dengan kesalahan
- urutkan data menurut satu atau lebih kolom
- singkirkan pengulangan
- bagi teks tempel dengan kolom (berdasarkan pembatas, jumlah karakter, dll.)
- susun teks secara berurutan (hapus spasi tambahan, perbaiki huruf besar/kecil, dll.)
- mengonversi tipe data dengan segala cara yang memungkinkan (mengubah angka seperti teks menjadi angka normal dan sebaliknya)
- mengubah posisi (memutar) tabel dan memperluas tabel silang dua dimensi menjadi tabel datar
- tambahkan kolom tambahan ke tabel dan gunakan rumus serta fungsi di dalamnya menggunakan bahasa M yang ada di dalam Power Query.
- ...
Sebagai contoh, mari kita tambahkan kolom dengan teks nama bulan ke tabel kita, sehingga nantinya akan lebih mudah untuk membuat laporan tabel pivot. Untuk melakukan ini, klik kanan pada judul kolom tanggaldan pilih perintah Kolom duplikat (Kolom Duplikat), lalu klik kanan pada header kolom duplikat yang muncul dan pilih Commands Transform – Bulan – Nama Bulan:
Kolom baru harus dibentuk dengan nama teks bulan untuk setiap baris. Dengan mengklik dua kali pada judul kolom, Anda dapat mengganti namanya dari Salin Tanggal ke yang lebih nyaman Bulanmisalnya.
Jika di beberapa kolom program tidak mengenali tipe data dengan benar, maka Anda dapat membantunya dengan mengklik ikon format di sisi kiri setiap kolom:
Anda dapat mengecualikan baris dengan kesalahan atau baris kosong, serta manajer atau pelanggan yang tidak perlu, menggunakan filter sederhana:
Selain itu, semua transformasi yang dilakukan diperbaiki di panel kanan, di mana mereka selalu dapat digulung kembali (silang) atau mengubah parameternya (roda gigi):
Ringan dan elegan bukan?
Langkah 2. Mari kita ubah permintaan kita menjadi sebuah fungsi
Untuk selanjutnya mengulangi semua transformasi data yang dibuat untuk setiap buku yang diimpor, kita perlu mengonversi permintaan yang kita buat menjadi sebuah fungsi, yang kemudian akan diterapkan, pada gilirannya, ke semua file kita. Untuk melakukan ini sebenarnya sangat sederhana.
Di Editor Kueri, buka tab Lihat dan klik tombol Editor Tingkat Lanjut (Lihat — Editor Lanjutan). Sebuah jendela akan terbuka di mana semua tindakan kita sebelumnya akan ditulis dalam bentuk kode dalam bahasa M. Harap perhatikan bahwa jalur ke file yang kami impor untuk contoh di-hardcode dalam kode:
Sekarang mari kita buat beberapa penyesuaian:
Arti mereka sederhana: baris pertama (jalur berkas)=> mengubah prosedur kita menjadi fungsi dengan argumen jalur file, dan di bawah ini kita mengubah jalur tetap ke nilai variabel ini.
Semua. Klik Finish dan harus melihat ini:
Jangan takut datanya hilang – sebenarnya, semuanya baik-baik saja, semuanya akan terlihat seperti ini Kami telah berhasil membuat fungsi kustom kami, di mana seluruh algoritma untuk mengimpor dan memproses data diingat tanpa terikat ke file tertentu . Tetap memberinya nama yang lebih mudah dipahami (misalnya dapatkanData) di panel di sebelah kanan di lapangan Nama Depan dan kamu bisa menuai Beranda — Tutup dan unduh (Beranda — Tutup dan Muat). Harap dicatat bahwa jalur ke file yang kami impor untuk contoh dikodekan dalam kode. Anda akan kembali ke jendela utama Microsoft Excel, tetapi panel dengan koneksi yang dibuat ke fungsi kami akan muncul di sebelah kanan:
Langkah 3. Mengumpulkan semua file
Semua bagian tersulit ada di belakang, bagian yang menyenangkan dan mudah tetap ada. Pergi ke tab Data – Buat Kueri – Dari File – Dari Folder (Data — Kueri Baru — Dari file — Dari folder) atau, jika Anda memiliki Excel 2010-2013, mirip dengan tab Kueri Daya. Di jendela yang muncul, tentukan folder tempat semua file kota sumber kami berada dan klik OK. Langkah selanjutnya akan membuka jendela di mana semua file Excel yang ditemukan di folder ini (dan subfoldernya) dan detail untuk masing-masingnya akan dicantumkan:
Klik Perubahan (Ubah) dan sekali lagi kita masuk ke jendela editor kueri yang sudah dikenal.
Sekarang kita perlu menambahkan kolom lain ke tabel kita dengan fungsi yang kita buat, yang akan "menarik" data dari setiap file. Untuk melakukan ini, buka tab Tambahkan Kolom – Kolom Kustom (Tambahkan Kolom — Tambahkan Kolom Kustom) dan di jendela yang muncul, masukkan fungsi kami dapatkanData, menetapkannya sebagai argumen path lengkap ke setiap file:
Setelah mengklik OK kolom yang dibuat harus ditambahkan ke tabel kita di sebelah kanan.
Sekarang mari kita hapus semua kolom yang tidak perlu (seperti di Excel, menggunakan tombol kanan mouse – Remove), hanya menyisakan kolom yang ditambahkan dan kolom dengan nama file, karena nama ini (lebih tepatnya, kota) akan berguna untuk memiliki total data untuk setiap baris.
Dan sekarang "momen wow" – klik ikon dengan panahnya sendiri di sudut kanan atas kolom yang ditambahkan dengan fungsi kami:
… hapus centang Gunakan nama kolom asli sebagai awalan (Gunakan nama kolom asli sebagai awalan)dan klik OK. Dan fungsi kami akan memuat dan memproses data dari setiap file, mengikuti algoritme yang direkam dan mengumpulkan semuanya dalam tabel umum:
Untuk keindahan lengkap, Anda juga dapat menghapus ekstensi .xlsx dari kolom pertama dengan nama file – dengan penggantian standar dengan “tidak ada” (klik kanan pada tajuk kolom – Pengganti) dan ganti nama kolom ini menjadi Kota. Dan juga perbaiki format data pada kolom dengan tanggal.
Semua! Klik Beranda – Tutup dan Muat (Beranda — Tutup & Muat). Semua data yang dikumpulkan oleh kueri untuk semua kota akan diunggah ke lembar Excel saat ini dalam format "tabel pintar":
Koneksi yang dibuat dan fungsi perakitan kami tidak perlu disimpan secara terpisah dengan cara apa pun – mereka disimpan bersama dengan file saat ini dengan cara biasa.
Di masa mendatang, dengan perubahan apa pun dalam folder (menambah atau menghapus kota) atau dalam file (mengubah jumlah baris), cukup klik kanan langsung pada tabel atau pada kueri di panel kanan dan pilih memerintah Perbarui & Simpan (Menyegarkan) – Power Query akan “membangun kembali” semua data lagi dalam beberapa detik.
PS
Amandemen. Setelah pembaruan Januari 2017, Power Query mempelajari cara mengumpulkan buku kerja Excel dengan sendirinya, yaitu tidak perlu lagi membuat fungsi terpisah – ini terjadi secara otomatis. Dengan demikian, langkah kedua dari artikel ini tidak lagi diperlukan dan seluruh proses menjadi lebih sederhana:
- Pilih Buat Permintaan – Dari File – Dari Folder – Pilih Folder – OK
- Setelah daftar file muncul, tekan Perubahan
- Di jendela Editor Kueri, perluas kolom Biner dengan panah ganda dan pilih nama lembar yang akan diambil dari setiap file
Dan itu saja! Lagu!
- Desain ulang tab silang menjadi datar yang cocok untuk membuat tabel pivot
- Membangun bagan gelembung animasi di Power View
- Makro untuk merakit lembar dari file Excel yang berbeda menjadi satu