Baru-baru ini, kami membincangkan penggunaan fungsi FILTER.XML untuk mengimport data XML daripada Internet – tugas utama yang sebenarnya, bertujuan untuk fungsi ini. Walau bagaimanapun, sepanjang perjalanan, satu lagi penggunaan fungsi ini yang tidak dijangka dan indah telah muncul - untuk membahagikan teks melekit ke dalam sel dengan cepat.
Katakan kita mempunyai lajur data seperti ini:
Sudah tentu, untuk kemudahan, saya ingin membahagikannya kepada lajur berasingan: nama syarikat, bandar, jalan, rumah. Anda boleh melakukan ini dalam banyak cara yang berbeza:
- Penggunaan Teks mengikut lajur daripada tab Tarikh (Data — Teks ke lajur) dan pergi tiga langkah Penghurai teks. Tetapi jika data berubah esok, anda perlu mengulangi keseluruhan proses sekali lagi.
- Muatkan data ini ke dalam Power Query dan bahagikannya di sana, kemudian muat naik kembali ke helaian, dan kemudian kemas kini pertanyaan apabila data berubah (yang sudah lebih mudah).
- Jika anda perlu mengemas kini dengan cepat, maka anda boleh menulis beberapa formula yang sangat kompleks untuk mencari koma dan mengekstrak teks di antaranya.
Dan anda boleh melakukannya dengan lebih elegan dan menggunakan fungsi FILTER.XML, tetapi apakah kaitannya dengannya?
Fungsi FILTER.XML menerima kod XML sebagai hujah awalnya — teks yang ditandakan dengan tag dan atribut khas, dan kemudian menghuraikannya ke dalam komponennya, mengekstrak serpihan data yang kami perlukan. Kod XML biasanya kelihatan seperti ini:
Dalam XML, setiap elemen data mesti disertakan dalam teg. Teg ialah beberapa teks (dalam contoh di atas ialah pengurus, nama, keuntungan) yang disertakan dalam kurungan sudut. Teg sentiasa datang secara berpasangan – pembukaan dan penutup (dengan garis miring ditambah pada permulaan).
Fungsi FILTER.XML boleh mengekstrak kandungan semua teg yang kami perlukan dengan mudah, sebagai contoh, nama semua pengurus dan (paling penting) memaparkan semuanya sekali gus dalam satu senarai. Jadi tugas kami adalah untuk menambah teg pada teks sumber, mengubahnya menjadi kod XML yang sesuai untuk analisis seterusnya oleh fungsi FILTER.XML.
Jika kita mengambil alamat pertama daripada senarai kita sebagai contoh, maka kita perlu mengubahnya menjadi pembinaan ini:
Saya memanggil semua teg teks pembukaan dan penutupan global t, dan tag yang membingkai setiap elemen ialah s., tetapi anda boleh menggunakan sebarang sebutan lain – tidak mengapa.
Jika kami mengalih keluar inden dan pemisah baris daripada kod ini – sepenuhnya, dengan cara ini, pilihan dan ditambah hanya untuk kejelasan, maka semua ini akan bertukar menjadi satu baris:
Dan ia sudah boleh didapati dengan mudah daripada alamat sumber dengan menggantikan koma di dalamnya dengan beberapa tag menggunakan fungsi tersebut PENGGANTI (GANTIAN) dan melekat dengan simbol & pada permulaan dan akhir tag pembukaan dan penutup:
Untuk mengembangkan julat yang terhasil secara mendatar, kami menggunakan fungsi standard TRANSP (PINDAHKAN), membungkus formula kami di dalamnya:
Ciri penting bagi keseluruhan reka bentuk ini ialah dalam versi baharu Office 2021 dan Office 365 dengan sokongan untuk tatasusunan dinamik, tiada gerak isyarat khas diperlukan untuk input – cuma masukkan dan klik pada Masukkan – formula itu sendiri menduduki bilangan sel yang diperlukan dan semuanya berfungsi dengan cepat. Dalam versi sebelumnya, yang belum ada tatasusunan dinamik, anda perlu terlebih dahulu memilih bilangan sel kosong yang mencukupi sebelum memasukkan formula (anda boleh dengan margin), dan selepas mencipta formula, tekan pintasan papan kekunci Ctrl+Beralih+Masukkanuntuk memasukkannya sebagai formula tatasusunan.
Helah yang serupa boleh digunakan apabila memisahkan teks yang dilekat bersama ke dalam satu sel melalui pemisah baris:
Satu-satunya perbezaan dengan contoh sebelumnya ialah bukannya koma, di sini kita menggantikan watak pemisah baris Alt + Enter yang tidak kelihatan, yang boleh ditentukan dalam formula menggunakan fungsi CHAR dengan kod 10.
- Kehalusan bekerja dengan pemisah baris (Alt + Enter) dalam Excel
- Bahagikan teks dengan lajur dalam Excel
- Menggantikan teks dengan SUBSTITUTE