Yaqinda biz XML ma'lumotlarini Internetdan import qilish uchun FILTER.XML funktsiyasidan foydalanishni muhokama qildik - bu funktsiya aslida mo'ljallangan asosiy vazifa. Shu bilan birga, bu funktsiyadan yana bir kutilmagan va chiroyli foydalanish paydo bo'ldi - yopishqoq matnni hujayralarga tezda ajratish uchun.
Aytaylik, bizda shunday ma'lumotlar ustuni bor:
Albatta, qulaylik uchun men uni alohida ustunlarga bo'lishni xohlayman: kompaniya nomi, shahar, ko'cha, uy. Buni turli yo'llar bilan qilishingiz mumkin:
- foydalanish Ustunlar bo'yicha matn tabdan ma'lumotlar (Ma'lumotlar - ustunlarga matn) va uch qadamga boring Matnni tahlil qilish. Ammo ertaga ma'lumotlar o'zgarsa, butun jarayonni yana takrorlashingiz kerak bo'ladi.
- Ushbu ma'lumotlarni Power Query-ga yuklang va u erga bo'ling, so'ngra uni yana varaqqa yuklang va ma'lumotlar o'zgarganda so'rovni yangilang (bu allaqachon osonroq).
- Agar siz tezda yangilashingiz kerak bo'lsa, vergullarni topish va ular orasidagi matnni chiqarish uchun juda murakkab formulalarni yozishingiz mumkin.
Va siz buni yanada nafisroq qilishingiz va FILTER.XML funksiyasidan foydalanishingiz mumkin, lekin bunga nima aloqasi bor?
FILTER.XML funksiyasi dastlabki argument sifatida XML kodini oladi - maxsus teglar va atributlar bilan belgilangan matn, so'ngra uni tarkibiy qismlarga ajratadi va bizga kerak bo'lgan ma'lumotlar fragmentlarini chiqaradi. XML kodi odatda shunday ko'rinadi:
XML-da har bir ma'lumot elementi teglarga o'ralgan bo'lishi kerak. Teg - bu burchakli qavslar ichiga olingan ba'zi matn (yuqoridagi misolda menejer, ism, foyda). Teglar har doim juft bo'ladi - ochilish va yopilish (boshiga slash qo'shilgan).
FILTER.XML funksiyasi bizga kerak bo'lgan barcha teglar tarkibini, masalan, barcha menejerlarning nomlarini osongina ajratib olishi va (eng muhimi) ularni bir vaqtning o'zida bitta ro'yxatda ko'rsatishi mumkin. Shunday qilib, bizning vazifamiz dastlabki matnga teglar qo'shish, uni FILTER.XML funksiyasi orqali keyingi tahlil qilish uchun mos XML kodiga aylantirishdir.
Agar biz ro'yxatimizning birinchi manzilini misol sifatida olsak, uni ushbu qurilishga aylantirishimiz kerak bo'ladi:
Men global ochilish va barcha matn tegini yopishni chaqirdim t, va har bir elementni ramkalash teglari s., lekin siz boshqa har qanday belgilardan foydalanishingiz mumkin - bu muhim emas.
Agar biz ushbu koddan chekinishlar va satr tanaffuslarini olib tashlasak - to'liq, aytmoqchi, ixtiyoriy va faqat aniqlik uchun qo'shilgan bo'lsa, bularning barchasi chiziqqa aylanadi:
Va uni allaqachon manba manzilidan vergullarni bir nechta teglar bilan almashtirish orqali nisbatan oson olish mumkin. funksiyasidan foydalanish Substitut (O'RNISHA) va belgi bilan yopishtirish & ochilish va yopish teglarining boshida va oxirida:
Olingan diapazonni gorizontal ravishda kengaytirish uchun biz standart funktsiyadan foydalanamiz TRANSP (TRANSPOZE), formulamizni unga o'rash:
Ushbu butun dizaynning muhim xususiyati shundaki, Office 2021 va Office 365 ning dinamik massivlarni qo'llab-quvvatlaydigan yangi versiyalarida kiritish uchun maxsus imo-ishoralar talab qilinmaydi - shunchaki kiriting va ustiga bosing. kiriting - formulaning o'zi kerakli hujayralar sonini egallaydi va hamma narsa portlash bilan ishlaydi. Oldingi versiyalarda, hali dinamik massivlar bo'lmaganida, formulani kiritishdan oldin (siz chegara bilan mumkin) etarli miqdordagi bo'sh katakchalarni tanlashingiz kerak va formulani yaratgandan so'ng, klaviatura yorlig'ini bosing. Ctrl+smena+kiritingmassiv formulasi sifatida kiritish uchun.
Xuddi shunday hiyla-nayrangni bir-biriga yopishtirilgan matnni satrlar oralig'i orqali bitta katakka ajratishda foydalanish mumkin:
Oldingi misoldan yagona farq shundaki, bu erda vergul o'rniga biz ko'rinmas Alt + Enter qatorni ajratish belgisini almashtiramiz, uni formulada CHAR funksiyasi yordamida 10 kodi bilan ko'rsatish mumkin.
- Excelda qator uzilishlari (Alt + Enter) bilan ishlashning nozik tomonlari
- Excelda matnni ustunlarga bo'ling
- Matnni SUBSTITUTE bilan almashtirish