Baru-baru ini, kami membahas penggunaan fungsi FILTER.XML untuk mengimpor data XML dari Internet – tugas utama yang sebenarnya dimaksudkan oleh fungsi ini. Namun, di sepanjang jalan, penggunaan lain yang tak terduga dan indah dari fungsi ini telah muncul – untuk membagi teks tempel dengan cepat ke dalam sel.
Katakanlah kita memiliki kolom data seperti ini:
Tentu saja, untuk kenyamanan, saya ingin membaginya menjadi kolom terpisah: nama perusahaan, kota, jalan, rumah. Anda dapat melakukan ini dengan berbagai cara:
- penggunaan Teks menurut kolom dari tab Data (Data — Teks ke kolom) dan pergi tiga langkah Pengurai teks. Tetapi jika data berubah besok, Anda harus mengulangi seluruh proses lagi.
- Muat data ini ke Power Query dan bagi di sana, lalu unggah kembali ke lembar, lalu perbarui kueri saat data berubah (yang sudah lebih mudah).
- Jika Anda perlu memperbarui dengan cepat, maka Anda dapat menulis beberapa rumus yang sangat kompleks untuk menemukan koma dan mengekstrak teks di antara keduanya.
Dan Anda dapat melakukannya dengan lebih elegan dan menggunakan fungsi FILTER.XML, tetapi apa hubungannya dengan itu?
Fungsi FILTER.XML menerima sebagai argumen awalnya sebuah kode XML — teks yang ditandai dengan tag dan atribut khusus, dan kemudian menguraikannya ke dalam komponennya, mengekstrak fragmen data yang kita butuhkan. Kode XML biasanya terlihat seperti ini:
Dalam XML, setiap elemen data harus diapit tag. Tag adalah beberapa teks (dalam contoh di atas adalah manajer, nama, laba) yang diapit oleh tanda kurung siku. Tag selalu berpasangan – pembukaan dan penutupan (dengan garis miring ditambahkan di awal).
Fungsi FILTER.XML dapat dengan mudah mengekstrak konten semua tag yang kita butuhkan, misalnya, nama semua manajer, dan (yang terpenting) menampilkan semuanya sekaligus dalam satu daftar. Jadi tugas kita adalah menambahkan tag ke teks sumber, mengubahnya menjadi kode XML yang sesuai untuk analisis selanjutnya oleh fungsi FILTER.XML.
Jika kita mengambil alamat pertama dari daftar kita sebagai contoh, maka kita perlu mengubahnya menjadi konstruksi ini:
Saya menelepon pembukaan dan penutupan global semua tag teks t, dan tag yang membingkai setiap elemen adalah s., tetapi Anda dapat menggunakan sebutan lain – tidak masalah.
Jika kami menghapus indentasi dan jeda baris dari kode ini - omong-omong, sepenuhnya opsional dan ditambahkan hanya untuk kejelasan, maka semua ini akan berubah menjadi satu baris:
Dan itu sudah dapat diperoleh dengan relatif mudah dari alamat sumber dengan mengganti koma di dalamnya dengan beberapa tag menggunakan fungsi PENGGANTI (PENGGANTI) dan menempel dengan simbol & di awal dan akhir tag pembuka dan penutup:
Untuk memperluas rentang yang dihasilkan secara horizontal, kami menggunakan fungsi standar TRANSP (MENGUBAH URUTAN), membungkus formula kami di dalamnya:
Fitur penting dari keseluruhan desain ini adalah bahwa dalam versi baru Office 2021 dan Office 365 dengan dukungan untuk array dinamis, tidak diperlukan gerakan khusus untuk input – cukup masukkan dan klik Enter – rumus itu sendiri menempati jumlah sel yang dibutuhkan dan semuanya bekerja dengan baik. Di versi sebelumnya, di mana belum ada array dinamis, Anda harus terlebih dahulu memilih jumlah sel kosong yang cukup sebelum memasukkan rumus (Anda bisa dengan margin), dan setelah membuat rumus, tekan pintasan keyboard Ctrl+perubahan+Enteruntuk memasukkannya sebagai rumus array.
Trik serupa dapat digunakan saat memisahkan teks yang disatukan menjadi satu sel melalui jeda baris:
Satu-satunya perbedaan dengan contoh sebelumnya adalah bahwa alih-alih koma, di sini kita mengganti karakter pemisah baris Alt + Enter yang tidak terlihat, yang dapat ditentukan dalam rumus menggunakan fungsi CHAR dengan kode 10.
- Seluk-beluk bekerja dengan jeda baris (Alt + Enter) di Excel
- Bagi teks dengan kolom di Excel
- Mengganti teks dengan SUBSTITUTE