Geçenlerde bir arkadaşım, verilen bir dizi kelimeden oluşan olası tüm ifadelerin oluşturulmasına yardım etme isteği ile bana yaklaştı. Bu tür sorunlar, çevrimiçi reklamcılık ve SEO tanıtımı için anahtar kelime ve kelime öbekleri listeleri derlerken, bir arama sorgusunda kelimelerin olası tüm permütasyonlarını gözden geçirmeniz gerektiğinde ortaya çıkabilir:
Matematikte bu işleme denir Kartezyen ürün. Resmi tanım şu şekildedir: A ve B kümelerinin Kartezyen çarpımı, ilk bileşeni A kümesine ve ikinci bileşeni B kümesine ait olan tüm çiftlerin kümesidir. Ayrıca, kümelerin öğeleri her ikisi de olabilir. sayılar ve metin.
İnsan diline çevrildiğinde, bunun anlamı, örneğin A kümesinde "beyaz" ve "kırmızı" ve B kümesinde "BMW" ve "Mercedes" kelimeleri varsa, o zaman bu iki kümenin Kartezyen çarpımından sonra çıktı almak, her iki listedeki kelimelerden oluşan tüm olası tümce çeşitlerinin kümesidir:
- beyaz bmw
- kırmızı bmw
- beyaz Mercedes
- kırmızı mercedes
… yani tam ihtiyacımız olan şey. Bu görevi Excel'de çözmenin birkaç yoluna bakalım.
Yöntem 1. Formüller
Formüllerle başlayalım. İlk veri olarak sırasıyla A, B ve C sütunlarında üç orijinal kelime listemiz olduğunu ve her listedeki öğe sayısının değişebileceğini varsayalım:
İlk olarak, indeksleri olan üç sütun yapalım, yani olası tüm kombinasyonlarda her listeden kelimelerin sıra sayıları. İlk birim satırı (E2:G2) manuel olarak girilecek ve geri kalanı için aşağıdaki formülü kullanacağız:
Buradaki mantık basittir: önceki üst hücredeki indeks zaten listenin sonuna ulaştıysa, yani fonksiyon tarafından hesaplanan listedeki eleman sayısına eşitse COUNT (SAY), sonra numaralandırmayı yeniden başlatıyoruz. Aksi takdirde, endeksi 1 artırırız. Formülü aşağı ve sağa kopyalayabilmeniz için aralıkların dolar işareti ($) ile akıllıca sabitlenmesine özellikle dikkat edin.
Artık her listeden ihtiyacımız olan kelimelerin sıra sayılarına sahip olduğumuza göre, fonksiyonu kullanarak kelimeleri kendileri çıkarabiliriz. INDEX (İNDEKS) üç ayrı sütuna:
Bu işlevle daha önce işinizde karşılaşmadıysanız, en azından çapraz olarak çalışmanızı şiddetle tavsiye ederim - birçok durumda yardımcı olur ve daha az (ve hatta daha fazla!) VPR (DÜŞEYARA).
Bundan sonra, yalnızca elde edilen parçaları birleştirme sembolünü (&) kullanarak satır satır yapıştırmak kalır:
… veya (Excel'in en son sürümüne sahipseniz) kullanışlı işlevle KOMBİNE (METİN BİRLEŞİMİ), belirtilen hücrelerin tüm içeriğini belirli bir ayırıcı karakter (boşluk) aracılığıyla yapıştırabilir:
Yöntem 2. Power Query ile
Power Query, iki ana görevi gerçekleştiren Microsoft Excel için güçlü bir eklentidir: 1. hemen hemen tüm harici kaynaklardan Excel'e veri yükleme ve 2. yüklenen tabloların her türlü dönüşümü. Power Query, Excel 2016-2019'da zaten yerleşiktir ve Excel 2010-2013 için ayrı bir eklenti olarak yüklenir (resmi Microsoft web sitesinden ücretsiz olarak indirebilirsiniz). Power Query'yi işinizde henüz kullanmaya başlamadıysanız, bunu düşünmenin zamanı gelmiştir, çünkü yukarıda açıklananlara benzer dönüşümler orada sadece birkaç hareketle kolayca ve doğal olarak yapılır.
Öncelikle Power Query'de kaynak listelerini ayrı sorgular olarak yükleyelim. Bunu yapmak için her tablo için aşağıdaki adımları uygulayın:
- Masaları bir düğme ile “akıllı” hale getirelim Tablo olarak biçimlendir çıkıntı Ana Sayfa (Ana Sayfa — Tablo Olarak Biçimlendir) veya klavye kısayolu Ctrl+T. Her tabloya otomatik olarak bir ad verilir Tablo1,2,3…, ancak istenirse sekmede değiştirilebilir Inşaatçı (Tasarım).
- Tablodaki aktif hücreyi ayarladıktan sonra düğmesine basın. Masadan (Tablodan) çıkıntı Veri (Tarih) veya sekmesinde Güç Sorgu (Excel 2010-2013 için ayrı bir eklenti olarak yüklediyseniz).
- Açılan sorgu düzenleyici penceresinde komutu seçin. Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… (Ana Sayfa — Kapat&Yükle — Kapat&Yükle..) ve sonra seçenek Sadece bir bağlantı oluşturun (Yalnızca bağlantı oluştur). Bu, yüklenen tabloyu bellekte bırakacak ve gelecekte erişilmesine izin verecektir.
Her şeyi doğru yaparsanız, sağ paneldeki çıktı, modda üç istek olmalıdır. Yalnızca bağlantı tablo adlarımızla:
Şimdi ilk sorguya sağ tıklayın ve komutu seçin Link (Referans)bunun güncellenebilir bir kopyasını oluşturmak ve ardından komut aracılığıyla verilere ek bir sütun eklemek için ž sütunu ekleme – Özel sütun (Sütun -ž Özel Sütun Ekle). Formül giriş penceresinde, yeni sütunun adını (örneğin Fragment2) ve son derece basit bir ifadeyi formül olarak girin:
=Tablo2
… yani, başka bir deyişle, ikinci sorgunun adı:
Tıkladıktan sonra OK her hücrede ikinci tablodan cümleler içeren iç içe bir tablo olacak yeni bir sütun göreceğiz (kelimenin yanındaki hücrenin arka planını tıklarsanız bu tabloların içeriğini görebilirsiniz) tablo):
Elde edilen sütunun başlığında çift oklu düğmeyi kullanarak ve işaretini kaldırarak bu iç içe tabloların tüm içeriğini genişletmeye devam eder. Önek olarak orijinal sütun adını kullan (Önek olarak orijinal sütun adını kullanın):
… ve ilk iki kümeden tüm olası eleman kombinasyonlarını elde ederiz:
Ayrıca, her şey benzer. Formülle başka bir hesaplanmış sütun ekleyin:
=Tablo3
… ve ardından iç içe geçmiş tabloları yeniden genişletin – ve şimdi sırasıyla üç kümeden sözcüklere izin vermek için olası tüm seçeneklere sahibiz:
Üç sütunu da soldan sağa seçmeye devam ediyor, Ctrlve komutları kullanarak boşluklarla ayrılmış içeriklerini birleştirin Sütunları birleştir (Sütunları Birleştir) sekmeden Dönüşüm (Dönüştür):
Ortaya çıkan sonuçlar, zaten bilinen komutla sayfaya geri yüklenebilir Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… (Ana Sayfa — Kapat&Yükle — Kapat&Yükle..):
İleride parçalı kaynak tablolarımızda bir şeyler değişirse, ortaya çıkan tabloya sağ tıklayıp komutu seçerek oluşturulan sorguyu güncellemek yeterli olacaktır. Güncelle ve Kaydet (Yenile) veya klavye kısayoluna basarak Ctrl+Ara Toplam+F5.
- Power Query, Power Pivot, Power Map ve Power BI nedir ve neden bir Excel kullanıcısına ihtiyaç duyarlar?
- Power Query'de Gantt Grafiği Oluşturma
- INDEX işlevini kullanmanın 5 yolu