İçerik
YouTube kanalımda en çok görüntülenen videolardan biri Microsoft Excel'de Flash Fill hakkında bir video. Bu aracın özü, kaynak verilerinizi bir şekilde dönüştürmeniz gerekiyorsa, bitişik sütuna almak istediğiniz sonucu yazmaya başlamanız yeterlidir. Elle yazılan birkaç hücreden sonra (genellikle 2-3 yeterlidir), Excel ihtiyacınız olan dönüşümlerin mantığını "anlayacak" ve yazdıklarınıza otomatik olarak devam ederek sizin için tüm monoton işleri tamamlayacaktır:
Verimliliğin özü. Hepimizin çok sevdiği sihirli “doğru yap” düğmesi, değil mi?
Aslında, Power Query'de böyle bir aracın bir benzeri var - orada denir Örneklerden sütun (Örneklerden Sütun). Aslında bu, Power Query'de yerleşik olarak bulunan, verilerinizden hızlı bir şekilde öğrenebilen ve ardından bunları dönüştürebilen küçük bir yapay zekadır. Gerçek görevlerde bizim için nerede yararlı olabileceğini anlamak için birkaç pratik senaryodaki yeteneklerine daha yakından bakalım.
Örnek 1. Metni yapıştırma/kesme
Diyelim ki Excel'de çalışanlarla ilgili veriler içeren böyle bir "akıllı" tablomuz var:
Power Query'ye standart şekilde - düğmeyle yükleyin Tablodan/Aralıktan çıkıntı Veri (Veri — Tablodan/Aralıktan).
Her çalışan için soyadı ve adının baş harflerini içeren bir sütun eklememiz gerektiğini varsayalım (ilk çalışan için Ivanov SV, vb.). Bu sorunu çözmek için iki yöntemden birini kullanabilirsiniz:
- kaynak verilerle sütun başlığına sağ tıklayın ve komutu seçin Örneklerden sütun ekle (Örneklerden sütun ekleyin);
- veri içeren bir veya daha fazla sütun seçin ve sekmede Sütun ekleme bir takım seç Örneklerden sütun. Burada, açılır listede, tüm sütunların mı yoksa yalnızca seçili sütunların mı analiz edilmesi gerektiğini belirleyebilirsiniz.
O zaman her şey basit – sağda görünen sütuna, istenen sonuçların örneklerini girmeye başlıyoruz ve Power Query'de yerleşik yapay zeka, dönüşüm mantığımızı anlamaya ve kendi başına devam etmeye çalışıyor:
Bu arada, bu sütunun herhangi bir hücresine doğru seçenekleri girebilirsiniz, yani mutlaka yukarıdan aşağıya ve arka arkaya değil. Ayrıca, başlık çubuğundaki onay kutularını kullanarak daha sonra analize sütunları kolayca ekleyebilir veya kaldırabilirsiniz.
Pencerenin üst kısmındaki formüle dikkat edin; akıllı Power Query, ihtiyacımız olan sonuçları elde etmek için bunu oluşturur. Bu arada, bu araç ile bu araç arasındaki temel fark budur. Anında doldurma Excel'de. Anında doldurma bir “kara kutu” gibi çalışır – bize dönüşümlerin mantığını göstermezler, sadece hazır sonuçlar verirler ve biz de onları olduğu gibi kabul ederiz. Burada her şey şeffaftır ve verilerle tam olarak ne olduğunu her zaman kesinlikle net bir şekilde anlayabilirsiniz.
Power Query'nin "fikri yakaladığını" görürseniz, düğmeye güvenle basabilirsiniz. OK veya klavye kısayolu Ctrl+Keşfet – Power Query tarafından icat edilmiş bir formüle sahip özel bir sütun oluşturulacaktır. Bu arada, daha sonra manuel olarak oluşturulan normal bir sütun olarak kolayca düzenlenebilir (komutla Sütun Ekleme – Özel Sütun) adım adının sağındaki dişli simgesine tıklayarak:
Örnek 2: Cümlelerdeki gibi durum
Metin içeren sütun başlığına sağ tıklayıp komutu seçerseniz Dönüşüm (Dönüştür), ardından kaydı değiştirmekten sorumlu üç komut görebilirsiniz:
Kullanışlı ve havalı, ancak bu listede, örneğin, kişisel olarak her zaman bir seçeneğim daha yoktu - cümlelerde olduğu gibi, büyük harf kullanımı (büyük harf) her kelimenin ilk harfi değil, yalnızca hücredeki ilk harf olduğunda ve Bu, küçük (küçük) harflerle görüntülendiğinde metnin geri kalanı.
Bu eksik özelliğin yapay zeka ile uygulanması kolaydır. Örneklerden sütunlar – aynı ruhla devam etmek için Power Query için birkaç seçenek girin:
Buradaki formül olarak, Power Query bir dizi işlev kullanır Metin.Üst и Metin.Alt, metni sırasıyla büyük ve küçük harfe dönüştürme ve işlevler Metin.Başlat и Metin.Orta - Soldan ve ortadan metinden bir alt dize çıkarabilen Excel işlevlerinin SOL ve PSTR analogları.
Örnek 3. Kelimelerin permütasyonu
Bazen, alınan veriler işlenirken, hücrelerdeki kelimeleri belirli bir sıraya göre yeniden düzenlemek gerekli hale gelir. Tabii ki, sütunu ayırıcı ile ayrı word sütunlarına bölebilir ve ardından belirtilen sırayla (boşluk eklemeyi unutmayın) tekrar yapıştırabilirsiniz, ancak araç yardımıyla Örneklerden sütun her şey çok daha kolay olacak:
Örnek 4: Yalnızca sayılar
Bir diğer hayati görev ise hücrenin içeriğinden sadece sayıları (sayıları) çıkarmaktır. Daha önce olduğu gibi, verileri Power Query'ye yükledikten sonra sekmeye gidin Sütun ekleme – Örneklerden sütun ve programın tam olarak ne elde etmek istediğimizi anlaması için birkaç hücreyi manuel olarak doldurun:
Bingo!
Yine, Query'nin formülü doğru bir şekilde oluşturduğundan emin olmak için pencerenin üst kısmına bakmaya değer - bu durumda bir işlev içerir. Metin. Seçme, tahmin edebileceğiniz gibi, verilen karakterleri listeye göre kaynak metinden çıkarır. Daha sonra, bu liste, elbette, gerekirse formül çubuğunda kolayca düzenlenebilir.
Örnek 5: Yalnızca metin
Önceki örneğe benzer şekilde, dışarı çekebilir veya tam tersini yapabilirsiniz - yalnızca metin, tüm sayıları, noktalama işaretlerini vb. silerek.
Bu durumda, anlamı zaten zıt olan bir işlev kullanılır – belirli bir listeye göre orijinal dizeden karakterleri kaldıran Text.Remove.
Örnek 6: Alfanümerik bir yulaf lapasından veri çıkarma
Power Query, bir hücredeki alfasayısal yulaf lapasından yararlı bilgiler almanız gerektiğinde, örneğin bir banka ekstresindeki ödeme amacının açıklamasından hesap numarasını almanız gerektiğinde, daha zor durumlarda da yardımcı olabilir:
Power Query tarafından oluşturulan dönüştürme formülünün oldukça karmaşık olabileceğini unutmayın:
Okuma ve anlama kolaylığı için, ücretsiz bir çevrimiçi hizmet kullanılarak çok daha aklı başında bir forma dönüştürülebilir. Güçlü Sorgu Biçimlendiricisi:
Çok kullanışlı bir şey – yaratıcılara saygı!
Örnek 7: Tarihleri dönüştürme
araç Örneklerden sütun tarih veya tarih saat sütunlarına da uygulanabilir. Bir tarihin ilk rakamlarını girdiğinizde, Power Query tüm olası dönüştürme seçeneklerinin listesini yararlı bir şekilde görüntüler:
Böylece orijinal tarihi kolayca "yıl-ay-gün" gibi herhangi bir egzotik biçime dönüştürebilirsiniz:
Örnek 8: Kategorizasyon
aracı kullanırsak Örneklerden sütun sayısal veriler içeren bir sütuna farklı şekilde çalışır. Power Query'ye yüklenmiş çalışan testi sonuçlarımız olduğunu (0-100 aralığında koşullu puanlar) ve aşağıdaki koşullu derecelendirmeyi kullandığımızı varsayalım:
- Ustalar - 90'dan fazla puan alanlar
- Uzmanlar – 70 ile 90 arasında puan aldı
- Kullanıcılar – 30'dan 70'e
- Yeni Başlayanlar - 30'dan az puan alanlar
Örneklerden bir sütunu listeye ekler ve bu derecelendirmeleri manuel olarak düzenlemeye başlarsak, çok yakında Power Query fikrimizi alacak ve operatörlerin iç içe geçtiği bir formül içeren bir sütun ekleyecektir. if mantık, ihtiyacımız olana çok benzer şekilde uygulanacaktır:
Yine durumu sonuna kadar basamazsınız, ancak tıklayın OK ve ardından formülde zaten bulunan eşik değerlerini düzeltin – bu şekilde daha hızlıdır:
Sonuç
kesinlikle bir araç Örneklerden sütun "sihirli bir hap" değildir ve er ya da geç, Power Query'nin başarısız olacağı ve istediğimiz şeyi çözemeyeceği verilerde standart olmayan durumlar veya özellikle ihmal edilen bir "kolektif çiftlik" vakaları olacaktır. bizim için doğru. Ancak, yardımcı bir araç olarak çok iyidir. Ayrıca, onun oluşturduğu formülleri inceleyerek, gelecekte her zaman işinize yarayacak olan M dilinin işlevleri hakkındaki bilginizi genişletebilirsiniz.
- Power Query'de Metni Normal İfadelerle (RegExp) Ayrıştırma
- Power Query'de bulanık metin araması
- Microsoft Excel'de Hızlı Doldurma