Daha yakın zamanlarda, İnternet'ten XML verilerini içe aktarmak için FILTER.XML işlevinin kullanımını tartışmıştık - aslında bu işlevin asıl amacı bu işlevdir. Ancak yol boyunca, bu işlevin beklenmedik ve güzel bir kullanımı daha ortaya çıktı - yapışkan metni hücrelere hızla bölmek için.
Diyelim ki şöyle bir veri sütunumuz var:
Tabii ki kolaylık olması için ayrı sütunlara bölmek istiyorum: şirket adı, şehir, sokak, ev. Bunu bir çok farklı yolla yapabilirsiniz:
- kullanım Sütunlara göre metin sekmeden Veri (Veri — Sütunlara metin) ve üç adım git Metin ayrıştırıcı. Ancak yarın veriler değişirse, tüm süreci tekrarlamanız gerekecek.
- Bu verileri Power Query'ye yükleyin ve orada bölün, ardından tekrar sayfaya yükleyin ve ardından veriler değiştiğinde sorguyu güncelleyin (ki bu zaten daha kolay).
- Anında güncelleme yapmanız gerekiyorsa, virgül bulmak ve aralarındaki metni çıkarmak için çok karmaşık formüller yazabilirsiniz.
Ve bunu daha zarif bir şekilde yapabilir ve FILTER.XML işlevini kullanabilirsiniz, ancak bununla ne ilgisi var?
FILTER.XML işlevi, ilk bağımsız değişkeni olarak bir XML kodu - özel etiketler ve niteliklerle işaretlenmiş metin alır ve ardından ihtiyacımız olan veri parçalarını ayıklayarak onu bileşenlerine ayrıştırır. XML kodu genellikle şöyle görünür:
XML'de, her veri öğesi etiketler içine alınmalıdır. Etiket, köşeli parantez içine alınmış bir metindir (yukarıdaki örnekte yönetici, ad, kârdır). Etiketler her zaman çift olarak gelir - açılış ve kapanış (başına bir eğik çizgi eklenerek).
FILTER.XML işlevi, örneğin tüm yöneticilerin adları gibi ihtiyacımız olan tüm etiketlerin içeriğini kolayca çıkarabilir ve (en önemlisi) hepsini tek bir listede görüntüleyebilir. Bu nedenle görevimiz, kaynak metne etiketler eklemek, onu FILTER.XML işlevi tarafından sonraki analiz için uygun XML koduna dönüştürmektir.
Örnek olarak listemizden ilk adresi alırsak, onu şu yapıya çevirmemiz gerekecek:
Global açılış ve kapanış tüm metin etiketini aradım tve her bir öğeyi çerçeveleyen etiketler s., ancak diğer atamaları kullanabilirsiniz - önemli değil.
Bu koddan girintileri ve satır sonlarını kaldırırsak - bu arada, tamamen isteğe bağlı ve yalnızca netlik için eklendi, o zaman tüm bunlar bir satıra dönüşecek:
Ve içindeki virgülleri birkaç etiketle değiştirerek kaynak adresten nispeten kolayca elde edilebilir. işlevi kullanmak VEKİL (VEKİL) ve sembolü ile yapıştırma & açılış ve kapanış etiketlerinin başında ve sonunda:
Ortaya çıkan aralığı yatay olarak genişletmek için standart işlevi kullanıyoruz TAŞIMA (TRANSPOZ), formülümüzü içine sararak:
Tüm bu tasarımın önemli bir özelliği, dinamik dizileri destekleyen Office 2021 ve Office 365'in yeni sürümünde, giriş için özel bir hareket gerekmemesidir - sadece girin ve tıklayın Keşfet - formülün kendisi ihtiyaç duyduğu hücre sayısını kaplar ve her şey bir patlama ile çalışır. Henüz dinamik dizilerin olmadığı önceki sürümlerde, formülü girmeden önce yeterli sayıda boş hücre seçmeniz (bir kenar boşluğu ile yapabilirsiniz) ve formülü oluşturduktan sonra klavye kısayoluna basmanız gerekir. Ctrl+vardiya+Keşfetdizi formülü olarak girmek için.
Benzer bir numara, bir hücrede birbirine yapışmış metni bir satır sonu ile ayırırken kullanılabilir:
Önceki örnekten tek fark, burada virgül yerine, CHAR işlevi kullanılarak formülde belirtilebilen görünmez Alt + Enter satır sonu karakterini kod 10 ile değiştirmemizdir.
- Excel'de satır sonları (Alt + Enter) ile çalışmanın incelikleri
- Excel'de metni sütunlara bölme
- Metni SUBSTITUTE ile değiştirme