Contents [show]
Perumusan masalah
Mari kita lihat penyelesaian yang cantik untuk salah satu situasi yang sangat standard yang kebanyakan pengguna Excel hadapi lambat laun: anda perlu cepat dan automatik mengumpul data daripada sejumlah besar fail ke dalam satu jadual akhir.
Katakan kita mempunyai folder berikut, yang mengandungi beberapa fail dengan data dari bandar cawangan:
Bilangan fail tidak penting dan mungkin berubah pada masa hadapan. Setiap fail mempunyai helaian bernama Jualandi mana jadual data terletak:
Bilangan baris (pesanan) dalam jadual, sudah tentu, berbeza, tetapi set lajur adalah standard di mana-mana.
Tugas: untuk mengumpul data daripada semua fail ke dalam satu buku dengan pengemaskinian automatik seterusnya apabila menambah atau memadam fail bandar atau baris dalam jadual. Menurut jadual disatukan akhir, maka ia akan menjadi mungkin untuk membina sebarang laporan, jadual pangsi, data isihan penapis, dll. Perkara utama adalah untuk dapat mengumpul.
Kami memilih senjata
Untuk penyelesaiannya, kami memerlukan versi terkini Excel 2016 (fungsi yang diperlukan sudah terbina ke dalamnya secara lalai) atau versi sebelumnya Excel 2010-2013 dengan tambahan percuma dipasang Kuasa Kuasa daripada Microsoft (muat turun di sini). Power Query ialah alat yang sangat fleksibel dan sangat berkuasa untuk memuatkan data ke dalam Excel dari dunia luar, kemudian menanggalkan dan memprosesnya. Power Query menyokong hampir semua sumber data sedia ada – daripada fail teks ke SQL dan juga Facebook 🙂
Jika anda tidak mempunyai Excel 2013 atau 2016, maka anda tidak boleh membaca lebih lanjut (gurau sahaja). Dalam versi Excel yang lebih lama, tugas sedemikian hanya boleh dicapai dengan memprogramkan makro dalam Visual Basic (yang sangat sukar untuk pemula) atau dengan penyalinan manual yang membosankan (yang mengambil masa yang lama dan menjana ralat).
Langkah 1. Import satu fail sebagai sampel
Mula-mula, mari kita mengimport data daripada satu buku kerja sebagai contoh, supaya Excel "mengambil idea". Untuk melakukan ini, buat buku kerja kosong baharu dan…
- jika anda mempunyai Excel 2016, kemudian buka tab Tarikh dan kemudian Cipta Pertanyaan – Dari Fail – Dari Buku (Data — Pertanyaan Baharu- Daripada fail — Daripada Excel)
- jika anda mempunyai Excel 2010-2013 dengan tambahan Power Query dipasang, kemudian buka tab Kuasa Kuasa dan pilih padanya Dari fail – Dari buku (Dari fail — Daripada Excel)
Kemudian, dalam tetingkap yang terbuka, pergi ke folder kami dengan laporan dan pilih mana-mana fail bandar (tidak kira yang mana, kerana semuanya adalah tipikal). Selepas beberapa saat, tetingkap Navigator akan muncul, di mana anda perlu memilih helaian yang kami perlukan (Jualan) di sebelah kiri, dan kandungannya akan dipaparkan di sebelah kanan:
Jika anda klik pada butang di sudut kanan bawah tetingkap ini muat turun (Muatkan), maka jadual akan segera diimport ke helaian dalam bentuk asalnya. Untuk satu fail, ini bagus, tetapi kita perlu memuatkan banyak fail sedemikian, jadi kita akan pergi sedikit berbeza dan klik butang Pembetulan (Edit). Selepas itu, editor pertanyaan Power Query hendaklah dipaparkan dalam tetingkap berasingan dengan data kami daripada buku:
Ini adalah alat yang sangat berkuasa yang membolehkan anda "menyelesaikan" jadual ke paparan yang kami perlukan. Walaupun penerangan cetek semua fungsinya akan mengambil masa kira-kira seratus halaman, tetapi, jika sangat ringkas, menggunakan tetingkap ini anda boleh:
- tapis data yang tidak perlu, baris kosong, baris dengan ralat
- mengisih data mengikut satu atau lebih lajur
- menghilangkan pengulangan
- bahagikan teks melekit mengikut lajur (mengikut pembatas, bilangan aksara, dsb.)
- susun teks dalam susunan (buang ruang tambahan, huruf besar yang betul, dsb.)
- menukar jenis data dalam setiap cara yang mungkin (tukar nombor seperti teks kepada nombor biasa dan sebaliknya)
- alihkan (putar) jadual dan kembangkan jadual silang dua dimensi kepada yang rata
- tambah lajur tambahan pada jadual dan gunakan formula dan fungsi di dalamnya menggunakan bahasa M yang terbina dalam Power Query.
- ...
Sebagai contoh, mari tambah lajur dengan nama teks bulan pada jadual kami, supaya lebih mudah untuk membina laporan jadual pangsi kemudian. Untuk melakukan ini, klik kanan pada tajuk lajur tarikhdan pilih arahan Lajur pendua (Lajur Pendua), dan kemudian klik kanan pada pengepala lajur pendua yang muncul dan pilih Perintah Transform – Nama Bulan – Bulan:
Lajur baharu hendaklah dibentuk dengan nama teks bulan untuk setiap baris. Dengan mengklik dua kali pada tajuk lajur, anda boleh menamakan semula daripadanya Tarikh Salin kepada yang lebih selesa bulan, contohnya.
Jika dalam beberapa lajur program tidak mengenali jenis data dengan betul, maka anda boleh membantunya dengan mengklik pada ikon format di sebelah kiri setiap lajur:
Anda boleh mengecualikan baris dengan ralat atau baris kosong, serta pengurus atau pelanggan yang tidak perlu, menggunakan penapis mudah:
Selain itu, semua transformasi yang dilakukan ditetapkan pada panel kanan, di mana ia sentiasa boleh digulung ke belakang (silang) atau menukar parameternya (gear):
Ringan dan elegan, bukan?
Langkah 2. Mari tukar permintaan kita kepada fungsi
Untuk seterusnya mengulangi semua transformasi data yang dibuat untuk setiap buku yang diimport, kami perlu menukar permintaan yang kami buat kepada fungsi, yang kemudiannya akan digunakan, seterusnya, pada semua fail kami. Untuk melakukan ini sebenarnya sangat mudah.
Dalam Editor Pertanyaan, pergi ke tab Lihat dan klik butang Editor Lanjutan (Lihat — Editor Lanjutan). Tetingkap harus dibuka di mana semua tindakan kami sebelum ini akan ditulis dalam bentuk kod dalam bahasa M. Sila ambil perhatian bahawa laluan ke fail yang kami import untuk contoh adalah dikodkan dalam kod:
Sekarang mari kita buat beberapa pelarasan:
Maksudnya mudah: baris pertama (laluan fail)=> menukar prosedur kami menjadi fungsi dengan hujah laluan fail, dan di bawah kita menukar laluan tetap kepada nilai pembolehubah ini.
Semua. Klik pada Selesai dan harus melihat ini:
Jangan takut bahawa data telah hilang – sebenarnya, semuanya OK, semuanya sepatutnya kelihatan seperti ini 🙂 Kami telah berjaya mencipta fungsi tersuai kami, di mana keseluruhan algoritma untuk mengimport dan memproses data diingati tanpa terikat pada fail tertentu . Ia kekal untuk memberikan nama yang lebih mudah difahami (contohnya getData) dalam panel di sebelah kanan dalam medan Nama Pertama dan anda boleh menuai Laman Utama — Tutup dan muat turun (Laman Utama — Tutup dan Muatkan). Sila ambil perhatian bahawa laluan ke fail yang kami import untuk contoh adalah dikodkan dalam kod. Anda akan kembali ke tetingkap utama Microsoft Excel, tetapi panel dengan sambungan yang dibuat ke fungsi kami akan muncul di sebelah kanan:
Langkah 3. Mengumpul semua fail
Semua bahagian yang paling sukar di belakang, bahagian yang menyenangkan dan mudah kekal. Pergi ke tab Data – Buat Pertanyaan – Dari Fail – Dari Folder (Data — Pertanyaan Baharu — Daripada fail — Dari folder) atau, jika anda mempunyai Excel 2010-2013, sama seperti tab Kuasa Kuasa. Dalam tetingkap yang muncul, nyatakan folder di mana semua fail bandar sumber kami berada dan klik OK. Langkah seterusnya harus membuka tetingkap di mana semua fail Excel yang terdapat dalam folder ini (dan subfoldernya) dan butiran untuk setiap fail akan disenaraikan:
Klik Tukar (Edit) dan sekali lagi kita masuk ke tetingkap editor pertanyaan biasa.
Sekarang kita perlu menambah lajur lain pada jadual kami dengan fungsi yang kami buat, yang akan "menarik" data dari setiap fail. Untuk melakukan ini, pergi ke tab Tambah Lajur – Lajur Tersuai (Tambah Lajur — Tambah Lajur Tersuai) dan dalam tetingkap yang muncul, masukkan fungsi kami getData, menyatakan untuknya sebagai hujah laluan penuh untuk setiap fail:
Selepas mengklik OK lajur yang dibuat hendaklah ditambahkan pada jadual kami di sebelah kanan.
Sekarang mari kita padamkan semua lajur yang tidak perlu (seperti dalam Excel, menggunakan butang tetikus kanan – Batalkan), hanya meninggalkan lajur yang ditambah dan lajur dengan nama fail, kerana nama ini (lebih tepat lagi, bandar) akan berguna untuk mempunyai dalam jumlah data untuk setiap baris.
Dan kini "wow moment" - klik pada ikon dengan anak panahnya sendiri di penjuru kanan sebelah atas lajur yang ditambah dengan fungsi kami:
… nyahtanda Gunakan nama lajur asal sebagai awalan (Gunakan nama lajur asal sebagai awalan)dan klik OK. Dan fungsi kami akan memuatkan dan memproses data daripada setiap fail, mengikut algoritma yang direkodkan dan mengumpul segala-galanya dalam jadual biasa:
Untuk kecantikan yang lengkap, anda juga boleh mengalih keluar sambungan .xlsx daripada lajur pertama dengan nama fail – dengan penggantian standard dengan “tiada apa-apa” (klik kanan pada pengepala lajur – Pengganti) dan namakan semula lajur ini kepada City. Dan juga betulkan format data dalam lajur dengan tarikh.
Semua! Klik pada Laman Utama – Tutup dan Muatkan (Laman Utama — Tutup & Muatkan). Semua data yang dikumpul oleh pertanyaan untuk semua bandar akan dimuat naik ke helaian Excel semasa dalam format "jadual pintar":
Sambungan yang dibuat dan fungsi pemasangan kami tidak perlu disimpan secara berasingan dalam apa jua cara - ia disimpan bersama fail semasa dengan cara biasa.
Pada masa hadapan, dengan sebarang perubahan dalam folder (menambah atau mengalih keluar bandar) atau dalam fail (menukar bilangan baris), cukup untuk mengklik kanan terus pada jadual atau pada pertanyaan dalam panel kanan dan pilih perintah Kemas kini & Simpan (Segarkan semula) – Power Query akan "membina semula" semua data sekali lagi dalam beberapa saat.
PS
Pindaan. Selepas kemas kini Januari 2017, Power Query mempelajari cara mengumpul buku kerja Excel dengan sendirinya, iaitu tidak perlu membuat fungsi berasingan lagi – ia berlaku secara automatik. Oleh itu, langkah kedua dari artikel ini tidak lagi diperlukan dan keseluruhan proses menjadi lebih mudah:
- Pilih Cipta Permintaan – Dari Fail – Dari Folder – Pilih Folder – OK
- Selepas senarai fail muncul, tekan Tukar
- Dalam tetingkap Editor Pertanyaan, kembangkan lajur Binari dengan anak panah berganda dan pilih nama helaian untuk diambil daripada setiap fail
Dan itu sahaja! Lagu!
- Reka bentuk semula tab silang menjadi tab rata yang sesuai untuk membina jadual pangsi
- Membina carta gelembung animasi dalam Power View
- Makro untuk memasang helaian daripada fail Excel yang berbeza menjadi satu