Konten
Salah satu video yang paling banyak dilihat di channel youtube saya adalah video tentang Flash Fill di Microsoft Excel. Inti dari alat ini adalah jika Anda perlu mengubah data sumber Anda, maka Anda hanya perlu mulai mengetik hasil yang ingin Anda dapatkan di kolom yang berdekatan. Setelah beberapa sel yang diketik secara manual (biasanya 2-3 sudah cukup), Excel akan "memahami" logika transformasi yang Anda butuhkan dan secara otomatis melanjutkan apa yang telah Anda ketik, menyelesaikan semua pekerjaan monoton untuk Anda:
Intisari efisiensi. Tombol ajaib "lakukan dengan benar" yang sangat kita semua sukai, bukan?
Sebenarnya, ada analog dari alat semacam itu di Power Query – itulah yang disebut Kolom dari contoh (Kolom dari Contoh). Faktanya, ini adalah kecerdasan buatan kecil yang dibangun ke dalam Power Query yang dapat dengan cepat belajar dari data Anda dan kemudian mengubahnya. Mari kita lihat lebih dekat kemampuannya dalam beberapa skenario praktis untuk memahami di mana itu dapat berguna bagi kita dalam tugas-tugas nyata.
Contoh 1. Menempel/memotong teks
Katakanlah kita memiliki tabel "pintar" di Excel dengan data tentang karyawan:
Muat ke Power Query dengan cara standar – dengan tombol Dari Tabel/Rentang tab Data (Data — Dari Tabel/Rentang).
Misalkan kita perlu menambahkan kolom dengan nama belakang dan inisial untuk setiap karyawan (Ivanov SV untuk karyawan pertama, dll.). Untuk mengatasi masalah ini, Anda dapat menggunakan salah satu dari dua metode:
- klik kanan pada judul kolom dengan data sumber dan pilih perintah Tambahkan kolom dari contoh (Tambahkan kolom dari contoh);
- pilih satu atau lebih kolom dengan data dan pada tab Menambahkan kolom pilih tim Kolom dari contoh. Di sini, di daftar drop-down, Anda dapat menentukan apakah semua atau hanya kolom yang dipilih perlu dianalisis.
Kemudian semuanya sederhana – di kolom yang muncul di sebelah kanan, kami mulai memasukkan contoh hasil yang diinginkan, dan kecerdasan buatan yang dibangun ke dalam Power Query mencoba memahami logika transformasi kami dan melanjutkannya sendiri:
Omong-omong, Anda dapat memasukkan opsi yang benar di sel mana pun dari kolom ini, yaitu tidak harus dari atas ke bawah dan dalam satu baris. Selain itu, Anda dapat dengan mudah menambahkan atau menghapus kolom dari analisis nanti menggunakan kotak centang di bilah judul.
Perhatikan rumus di bagian atas jendela – inilah yang dibuat oleh Power Query cerdas untuk mendapatkan hasil yang kita butuhkan. Omong-omong, inilah perbedaan mendasar antara alat ini dan Isi instan di Excel. Pengisian instan bekerja seperti "kotak hitam" – mereka tidak menunjukkan logika transformasi, tetapi hanya memberikan hasil yang sudah jadi dan kami menerimanya begitu saja. Di sini semuanya transparan dan Anda selalu dapat dengan jelas memahami apa yang sebenarnya terjadi dengan data.
Jika Anda melihat bahwa Power Query "menangkap ide", maka Anda dapat dengan aman menekan tombol OK atau pintasan keyboard Ctrl+Enter – kolom kustom dengan rumus yang dibuat oleh Power Query akan dibuat. Omong-omong, nanti dapat dengan mudah diedit sebagai kolom biasa yang dibuat secara manual (dengan perintah Menambahkan Kolom – Kolom Kustom) dengan mengklik ikon roda gigi di sebelah kanan nama langkah:
Contoh 2: Kasus seperti dalam kalimat
Jika Anda klik kanan pada judul kolom dengan teks dan pilih perintah Transformasi (Mengubah), maka Anda dapat melihat tiga perintah yang bertanggung jawab untuk mengubah register:
Nyaman dan keren, tetapi dalam daftar ini, misalnya, saya pribadi selalu kekurangan satu opsi lagi – kasus seperti dalam kalimat, ketika kapitalisasi (kapital) tidak menjadi huruf pertama di setiap kata, tetapi hanya huruf pertama di sel, dan sisa teks saat Ini ditampilkan dalam huruf kecil (kecil).
Fitur yang hilang ini mudah diterapkan dengan kecerdasan buatan Kolom dari contoh – cukup masukkan beberapa opsi untuk Power Query untuk melanjutkan dengan semangat yang sama:
Sebagai rumus di sini, Power Query menggunakan banyak fungsi Teks.Atas и Teks.Bawah, mengonversi teks menjadi huruf besar dan kecil, masing-masing, dan fungsi Teks.Mulai и Teks. Pertengahan – analog dari fungsi Excel KIRI dan PSTR, dapat mengekstrak substring dari teks dari kiri dan dari tengah.
Contoh 3. Permutasi kata
Terkadang, saat memproses data yang diterima, perlu untuk mengatur ulang kata-kata dalam sel dalam urutan tertentu. Tentu saja, Anda dapat membagi kolom menjadi kolom kata terpisah dengan pemisah dan kemudian merekatkannya kembali dalam urutan yang ditentukan (jangan lupa untuk menambahkan spasi), tetapi dengan bantuan alat Kolom dari contoh semuanya akan jauh lebih mudah:
Contoh 4: Hanya angka
Tugas lain yang sangat vital adalah mengeluarkan hanya angka (angka) dari isi sel. Seperti sebelumnya, setelah memuat data ke Power Query, buka tab Menambahkan kolom – Kolom dari contoh dan isi beberapa sel secara manual sehingga program memahami apa yang sebenarnya ingin kita dapatkan:
Bingo!
Sekali lagi, ada baiknya melihat bagian atas jendela untuk memastikan Query menghasilkan rumus dengan benar – dalam hal ini berisi fungsi Teks. Pilih, yang, seperti yang Anda duga, mengekstrak karakter yang diberikan dari teks sumber sesuai dengan daftar. Selanjutnya, daftar ini, tentu saja, dapat dengan mudah diedit di bilah rumus jika perlu.
Contoh 5: Hanya teks
Sama seperti contoh sebelumnya, Anda dapat menarik keluar dan sebaliknya – hanya teks, menghapus semua angka, tanda baca, dll.
Dalam hal ini, fungsi yang sudah berlawanan makna digunakan – Text.Remove, yang menghapus karakter dari string asli sesuai dengan daftar yang diberikan.
Contoh 6: Mengekstrak data dari bubur alfanumerik
Power Query juga dapat membantu dalam kasus yang lebih sulit, saat Anda perlu mengekstrak informasi yang berguna dari bubur alfanumerik dalam sel, misalnya, dapatkan nomor akun dari deskripsi tujuan pembayaran pada laporan mutasi bank:
Perhatikan bahwa rumus konversi yang dihasilkan Power Query bisa sangat rumit:
Untuk kemudahan membaca dan pemahaman, itu dapat diubah menjadi bentuk yang jauh lebih waras menggunakan layanan online gratis. Pemformat kueri daya:
Hal yang sangat berguna – hormat kepada pencipta!
Contoh 7: Mengonversi tanggal
Alat Bantu Kolom dari contoh dapat diterapkan ke kolom tanggal atau datetime juga. Saat Anda memasukkan digit pertama tanggal, Power Query akan membantu menampilkan daftar semua opsi konversi yang mungkin:
Jadi Anda dapat dengan mudah mengonversi tanggal asli ke format eksotis apa pun, seperti "tahun-bulan-hari":
Contoh 8: Kategorisasi
Jika kita menggunakan alat Kolom dari contoh ke kolom dengan data numerik, cara kerjanya berbeda. Misalkan kita memiliki hasil tes karyawan yang dimuat ke Power Query (skor bersyarat dalam kisaran 0-100) dan kita menggunakan gradasi bersyarat berikut:
- Masters – mereka yang mencetak lebih dari 90
- Pakar – skor dari 70 hingga 90
- Pengguna – dari 30 hingga 70
- Pemula – mereka yang mendapat nilai kurang dari 30
Jika kita menambahkan kolom dari contoh ke daftar dan mulai mengatur gradasi ini secara manual, maka Power Query akan segera mengambil ide kita dan menambahkan kolom dengan rumus, di mana operator bersarang satu sama lain. if logika akan diimplementasikan, sangat mirip dengan yang kita butuhkan:
Sekali lagi, Anda tidak dapat menekan situasi sampai akhir, tetapi klik OK dan kemudian perbaiki nilai ambang batas yang sudah ada dalam rumus – lebih cepat dengan cara ini:
Kesimpulan
Pastinya alat Kolom dari contoh bukan "pil ajaib" dan, cepat atau lambat, akan ada situasi non-standar atau terutama kasus "pertanian kolektif" yang diabaikan dalam data, ketika Power Query akan gagal dan tidak akan dapat menyelesaikan apa yang kita inginkan benar untuk kita. Namun, sebagai alat bantu, itu sangat bagus. Plus, dengan mempelajari rumus yang dia buat, Anda dapat memperluas pengetahuan Anda tentang fungsi bahasa M, yang akan selalu berguna di masa mendatang.
- Mengurai Teks dengan Ekspresi Reguler (RegExp) di Power Query
- Pencarian teks kabur di Power Query
- Isi Flash di Microsoft Excel