İçerik
Kaynak metinde anahtar sözcükleri aramak, verilerle çalışırken en sık yapılan görevlerden biridir. Aşağıdaki örneği kullanarak çözümüne birkaç şekilde bakalım:
Diyelim ki siz ve benim bir anahtar kelime listemiz – araba markalarının adları – ve her tür yedek parçadan oluşan büyük bir tablomuz var; araba markası. Görevimiz, belirli bir ayırıcı karakterle (örneğin, bir virgül) komşu hücrelerde algılanan tüm anahtar kelimeleri bulmak ve görüntülemektir.
Yöntem 1. Güç Sorgusu
Tabii ki, önce bir klavye kısayolu kullanarak tablolarımızı dinamik (“akıllı”) hale getiriyoruz. Ctrl+T veya komutlar Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir), onlara isimler verin (örneğin Pullarи Yedek parça) ve sekmede seçerek Power Query düzenleyicisine teker teker yükleyin Veriler – Tablodan/Aralıktan (Veri — Tablodan/Aralıktan). Power Query'nin ayrı bir eklenti olarak yüklendiği Excel 2010-2013'ün eski sürümlerine sahipseniz, sekmede istediğiniz düğme olacaktır. Güç Sorgu. Excel 365'in yepyeni bir sürümüne sahipseniz, düğme Tablodan/Aralıktan şimdi orada aradı yaprakları ile (Sayfadan).
Power Query'deki her bir tabloyu yükledikten sonra, komutla tekrar Excel'e dönüyoruz. Ana Sayfa — Kapat ve yükle — Kapat ve yükle… — Yalnızca bağlantı oluştur (Ana Sayfa — Kapat ve Yükle — Kapat ve Şuraya Yükle... — Yalnızca bağlantı oluştur).
Şimdi yinelenen bir istek oluşturalım Yedek parçaüzerine sağ tıklayıp seçerek Yinelenen istek (Yinelenen sorgu), ardından ortaya çıkan kopyalama isteğini şu şekilde yeniden adlandırın: Sonuçlar ve onunla çalışmaya devam edeceğiz.
Eylemlerin mantığı şudur:
- Gelişmiş sekmesinde Sütun ekleme bir takım seç Özel sütun (Sütun ekle — Özel sütun) ve formülü girin = Markalar. tıkladıktan sonra OK her hücrede anahtar kelimelerimizin - otomobil üreticisi markalarının listesini içeren iç içe geçmiş bir tablonun olacağı yeni bir sütun alacağız:
- Tüm iç içe geçmiş tabloları genişletmek için eklenen sütunun başlığındaki çift oklu düğmeyi kullanın. Aynı zamanda, yedek parça açıklamalarını içeren satırlar, marka sayısının katları ile çarpılacak ve olası tüm "yedek parça-marka" çift-kombinasyonlarını elde edeceğiz:
- Gelişmiş sekmesinde Sütun ekleme bir takım seç koşullu sütun (Koşullu sütun) ve kaynak metinde (parça açıklaması) bir anahtar kelimenin (marka) varlığını kontrol etmek için bir koşul belirleyin:
- Arama büyük/küçük harf duyarsız hale getirmek için üçüncü bağımsız değişkeni formül çubuğuna manuel olarak ekleyin Compare.OrdinalIgnoreCase oluşum kontrol işlevine Metin.İçerir (formül çubuğu görünmüyorsa, sekmede etkinleştirilebilir. Değerlendirme):
- Ortaya çıkan tabloyu filtreliyoruz, son sütunda yalnızca bir tane bırakıyoruz, yani eşleşenler ve gereksiz sütunu kaldırıyoruz olaylar.
- Aynı açıklamaları komutla gruplama Tarafından gruba göre çıkıntı Dönüşüm (Dönüştür — Gruplandırma ölçütü). Toplama işlemi olarak, Tüm satırlar (Tüm satırlar). Çıktıda, ihtiyacımız olan otomobil üreticilerinin markaları da dahil olmak üzere her bir yedek parçanın tüm ayrıntılarını içeren tablolar içeren bir sütun alıyoruz:
- Her parça için notları çıkarmak için sekmeye başka bir hesaplanmış sütun ekleyin Sütun Ekleme – Özel Sütun (Sütun ekle — Özel sütun) ve bir tablodan oluşan bir formül kullanın (bunlar sütunumuzda bulunur) - Detaylar) ve çıkarılan sütunun adı:
- Ortaya çıkan sütunun başlığındaki çift oklu düğmeye tıklıyoruz ve komutu seçiyoruz Değerleri çıkar (Değerleri ayıklayın)istediğiniz herhangi bir sınırlayıcı karaktere sahip pulların çıktısını almak için:
- Gereksiz bir sütunu kaldırma - Detaylar.
- Ortaya çıkan tabloya, açıklamalarda hiçbir markanın bulunmadığı, ondan kaybolan parçaları eklemek için, sorguyu birleştirme prosedürünü uyguluyoruz. Sonuç orijinal istek ile Yedek parça düğmesine tıklayın Birleştirmek çıkıntı Ana Sayfa (Ana Sayfa — Sorguları birleştirme). Bağlantı türü - Dış Birleştirme Sağ (Sağ dış birleştirme):
- Geriye kalan tek şey, ekstra sütunları kaldırmak ve kalanları yeniden adlandırmak-taşımaktır - ve görevimiz çözüldü:
Yöntem 2. Formüller
Excel 2016 veya sonraki bir sürüme sahipseniz, sorunumuz yeni işlevi kullanarak çok derli toplu ve zarif bir şekilde çözülebilir. KOMBİNE (METİN BİRLEŞİMİ):
Bu formülün mantığı basittir:
- işlev ARAMA (BULMAK) parçanın geçerli açıklamasında her bir markanın geçişini sırayla arar ve markanın bulunduğu simgenin seri numarasını veya #DEĞER! marka açıklamada yoksa.
- Ardından işlevi kullanarak IF (EĞER) и EOSHIBKA (HATA) hataları boş bir metin dizesi olan "" ile ve karakterlerin sıra numaralarını marka adlarının kendisiyle değiştiririz.
- Ortaya çıkan boş hücreler dizisi ve bulunan markalar, işlev kullanılarak belirli bir ayırıcı karakter aracılığıyla tek bir dizi halinde birleştirilir. KOMBİNE (METİN BİRLEŞİMİ).
Hızlandırma için Performans Karşılaştırması ve Power Query Query Buffering
Performans testi için başlangıç verisi olarak 100 adet yedek parça tanımlama tablosunu ele alalım. Üzerinde aşağıdaki sonuçları alıyoruz:
- Formüllerle yeniden hesaplama süresi (Yöntem 2) – 9 sn. formülü tüm sütuna kopyaladığınızda ve 2 sn. tekrarlandığında (muhtemelen tamponlama etkiler).
- Power Query sorgusunun güncelleme süresi (Yöntem 1) çok daha kötü – 110 saniye.
Tabii ki, çoğu şey belirli bir bilgisayarın donanımına ve Office'in yüklü sürümüne ve güncellemelere bağlıdır, ancak genel resim bence açık.
Bir Power Query sorgusunu hızlandırmak için arama tablosunu ara belleğe alalım Pullar, çünkü sorgu yürütme sürecinde değişmez ve sürekli olarak yeniden hesaplanması gerekmez (de facto Power Query'nin yaptığı gibi). Bunun için fonksiyonu kullanıyoruz Tablo.Arabellek yerleşik Power Query dilinden M.
Bunu yapmak için bir sorgu açın Sonuçlar ve sekmede Değerlendirme düğmesine basın Gelişmiş Düzenleyici (Görünüm — Gelişmiş Düzenleyici). Açılan pencerede yeni bir değişken içeren bir satır ekleyin Marky 2, otomobil üreticisi dizinimizin arabelleğe alınmış bir sürümü olacak ve bu yeni değişkeni daha sonra aşağıdaki sorgu komutunda kullanacak:
Böyle bir iyileştirmeden sonra, talebimizin güncelleme hızı neredeyse 7 kat artarak 15 saniyeye kadar çıkıyor. Çok farklı bir şey 🙂
- Power Query'de bulanık metin araması
- Formüllerle toplu metin değiştirme
- Power Query'de List.Accumulate işleviyle toplu metin değiştirme