Contents [show]
Salah satu video yang paling banyak ditonton di saluran YouTube saya ialah video tentang Isian Kilat dalam Microsoft Excel. Intipati alat ini ialah jika anda perlu mengubah data sumber anda, maka anda hanya perlu mula menaip hasil yang anda ingin perolehi dalam lajur bersebelahan. Selepas beberapa sel yang ditaip secara manual (biasanya 2-3 sudah mencukupi), Excel akan "memahami" logik transformasi yang anda perlukan dan secara automatik meneruskan apa yang telah anda taip, menyelesaikan semua kerja membosankan untuk anda:
Intipati kecekapan. Butang ajaib "lakukan dengan betul" yang sangat kita semua suka, bukan?
Malah, terdapat analog alat sedemikian dalam Power Query - di sana ia dipanggil Lajur daripada contoh (Lajur daripada Contoh). Sebenarnya, ini adalah kecerdasan buatan kecil yang dibina dalam Power Query yang boleh belajar dengan cepat daripada data anda dan kemudian mengubahnya. Mari kita lihat dengan lebih dekat keupayaannya dalam beberapa senario praktikal untuk memahami di mana ia boleh berguna kepada kita dalam tugas sebenar.
Contoh 1. Menggam/memotong teks
Katakan kita mempunyai jadual "pintar" sedemikian dalam Excel dengan data tentang pekerja:
Muatkannya ke dalam Power Query dengan cara standard – dengan butang Daripada Jadual/Julat tab Tarikh (Data — Daripada Jadual/Julat).
Katakan kita perlu menambah lajur dengan nama akhir dan inisial untuk setiap pekerja (Ivanov SV untuk pekerja pertama, dsb.). Untuk menyelesaikan masalah ini, anda boleh menggunakan salah satu daripada dua kaedah:
- klik kanan pada tajuk lajur dengan data sumber dan pilih arahan Tambahkan lajur daripada contoh (Tambah lajur daripada contoh);
- pilih satu atau lebih lajur dengan data dan pada tab Menambah lajur memilih pasukan Lajur daripada contoh. Di sini, dalam senarai juntai bawah, anda boleh menentukan sama ada semua atau hanya lajur yang dipilih perlu dianalisis.
Kemudian semuanya mudah – dalam lajur yang muncul di sebelah kanan, kami mula memasukkan contoh hasil yang diingini, dan kecerdasan buatan yang terbina dalam Power Query cuba memahami logik transformasi kami dan meneruskan dengan sendirinya:
Dengan cara ini, anda boleh memasukkan pilihan yang betul dalam mana-mana sel lajur ini, iaitu tidak semestinya atas ke bawah dan berturut-turut. Selain itu, anda boleh menambah atau mengalih keluar lajur dengan mudah daripada analisis kemudian menggunakan kotak pilihan dalam bar tajuk.
Beri perhatian kepada formula di bahagian atas tetingkap – inilah yang dihasilkan oleh Power Query pintar untuk mendapatkan hasil yang kami perlukan. Ini, dengan cara ini, adalah perbezaan asas antara alat ini dan Isi segera dalam Excel. Pengisian segera berfungsi seperti "kotak hitam" - mereka tidak menunjukkan kepada kita logik transformasi, tetapi hanya memberikan hasil yang sudah siap dan kami menganggapnya begitu sahaja. Di sini semuanya telus dan anda sentiasa boleh memahami dengan jelas apa sebenarnya yang berlaku dengan data.
Jika anda melihat Power Query "menangkap idea", maka anda boleh menekan butang dengan selamat OK atau pintasan papan kekunci Ctrl+Masukkan – lajur tersuai dengan formula yang dicipta oleh Power Query akan dibuat. Ngomong-ngomong, ia kemudiannya boleh diedit dengan mudah sebagai lajur biasa yang dibuat secara manual (dengan arahan Menambah Lajur – Lajur Tersuai) dengan mengklik ikon gear di sebelah kanan nama langkah:
Contoh 2: Kes seperti dalam ayat
Jika anda klik kanan pada tajuk lajur dengan teks dan pilih arahan Transformasi (Transformasi), maka anda boleh melihat tiga arahan yang bertanggungjawab untuk menukar daftar:
Mudah dan sejuk, tetapi dalam senarai ini, sebagai contoh, saya secara peribadi sentiasa kekurangan satu lagi pilihan – kes seperti dalam ayat, apabila huruf besar (kapital) menjadi bukan huruf pertama dalam setiap perkataan, tetapi hanya huruf pertama dalam sel, dan selebihnya teks apabila Ini dipaparkan dalam huruf kecil (kecil).
Ciri yang hilang ini mudah dilaksanakan dengan kecerdasan buatan Lajur daripada contoh – hanya masukkan beberapa pilihan untuk Power Query untuk meneruskan dengan semangat yang sama:
Sebagai formula di sini, Power Query menggunakan sekumpulan fungsi Teks. Atas и Teks.Bawah, menukar teks kepada huruf besar dan kecil, masing-masing, dan fungsi Teks.Mula и Teks. Pertengahan – analog fungsi Excel LEFT dan PSTR, dapat mengekstrak subrentetan daripada teks dari kiri dan dari tengah.
Contoh 3. Permutasi perkataan
Kadangkala, apabila memproses data yang diterima, ia menjadi perlu untuk menyusun semula perkataan dalam sel dalam urutan tertentu. Sudah tentu, anda boleh membahagikan lajur kepada lajur perkataan yang berasingan dengan pemisah dan kemudian melekatkannya semula dalam susunan yang ditentukan (jangan lupa menambah ruang), tetapi dengan bantuan alat Lajur daripada contoh semuanya akan menjadi lebih mudah:
Contoh 4: Hanya nombor
Satu lagi tugas yang sangat penting ialah mengeluarkan hanya nombor (nombor) daripada kandungan sel. Seperti sebelum ini, selepas memuatkan data ke dalam Power Query, pergi ke tab Menambah lajur – Lajur daripada contoh dan isikan beberapa sel secara manual supaya program memahami apa sebenarnya yang ingin kita perolehi:
Bingo!
Sekali lagi, anda perlu melihat bahagian atas tetingkap untuk memastikan Query menjana formula dengan betul – dalam kes ini ia mengandungi fungsi Teks. Pilih, yang, seperti yang anda rasa, mengekstrak aksara yang diberikan daripada teks sumber mengikut senarai. Selepas itu, senarai ini, sudah tentu, boleh diedit dengan mudah dalam bar formula jika perlu.
Contoh 5: Teks sahaja
Begitu juga dengan contoh sebelumnya, anda boleh menarik keluar dan sebaliknya - hanya teks, memadam semua nombor, tanda baca, dsb.
Dalam kes ini, fungsi yang sudah bertentangan dalam makna digunakan – Text.Remove, yang mengalih keluar aksara daripada rentetan asal mengikut senarai yang diberikan.
Contoh 6: Mengekstrak data daripada bubur alfanumerik
Power Query juga boleh membantu dalam kes yang lebih sukar, apabila anda perlu mengekstrak maklumat berguna daripada bubur alfanumerik dalam sel, contohnya, dapatkan nombor akaun daripada perihalan tujuan pembayaran pada penyata bank:
Ambil perhatian bahawa formula penukaran yang dijana Power Query boleh menjadi agak rumit:
Untuk kemudahan membaca dan memahami, ia boleh ditukar kepada bentuk yang lebih waras menggunakan perkhidmatan dalam talian percuma. Pemformat Pertanyaan Kuasa:
Perkara yang sangat berguna - hormat kepada pencipta!
Contoh 7: Menukar tarikh
Alat Lajur daripada contoh boleh digunakan pada lajur tarikh atau masa tarikh juga. Apabila anda memasukkan digit pertama tarikh, Power Query akan memaparkan senarai semua pilihan penukaran yang mungkin:
Jadi anda boleh menukar tarikh asal dengan mudah kepada sebarang format eksotik, seperti "tahun-bulan-hari":
Contoh 8: Pengkategorian
Jika kita menggunakan alat tersebut Lajur daripada contoh kepada lajur dengan data berangka, ia berfungsi secara berbeza. Katakan kami mempunyai keputusan ujian pekerja dimuatkan ke dalam Power Query (skor bersyarat dalam julat 0-100) dan kami menggunakan penggredan bersyarat berikut:
- Sarjana – mereka yang mendapat markah lebih daripada 90
- Pakar – mendapat markah dari 70 hingga 90
- Pengguna - dari 30 hingga 70
- Pemula – mereka yang mendapat markah kurang daripada 30
Jika kami menambah lajur daripada contoh pada senarai dan mula mengatur penggredan ini secara manual, maka tidak lama lagi Power Query akan mengambil idea kami dan menambah lajur dengan formula, di mana pengendali bersarang antara satu sama lain if logik akan dilaksanakan, sangat serupa dengan apa yang kita perlukan:
Sekali lagi, anda tidak boleh menekan keadaan hingga akhir, tetapi klik pada OK dan kemudian betulkan nilai ambang yang sudah ada dalam formula - ia lebih pantas dengan cara ini:
kesimpulan
Sudah tentu alat Lajur daripada contoh bukanlah "pil ajaib" dan, lambat laun, akan ada situasi tidak standard atau terutamanya kes terabai "ladang kolektif" dalam data, apabila Power Query akan gagal dan tidak akan dapat menyelesaikan perkara yang kita mahu betul untuk kita. Walau bagaimanapun, sebagai alat bantu, ia sangat bagus. Selain itu, dengan mengkaji formula yang dihasilkannya, anda boleh mengembangkan pengetahuan anda tentang fungsi bahasa M, yang akan sentiasa berguna pada masa hadapan.
- Menghuraikan Teks dengan Ungkapan Biasa (RegExp) dalam Power Query
- Carian teks kabur dalam Power Query
- Isian Flash dalam Microsoft Excel