Excel'de 2 Sütundan Crossjoin (Tüm Kombinasyonlar) Oluşturun — Detaylı Rehber
Excel'de iki listeyle çalışırken—örneğin ürün adları ve bedenler, bölgeler ve satış temsilcileri ya da öğrenciler ve dersler—bu listelerin tüm olası kombinasyonlarını oluşturmanız gerekebilir. Bu işleme **Çapraz Birleştirme** (Crossjoin) veya **Kartezyen Çarpım** denir. Bu eğitimde, iş akışınız için en uygun yöntemi seçmenize yardımcı olmak üzere adım adım talimatlar, avantajlar, dezavantajlar ve gerçek dünya örnekleri yer alıyor.
Crossjoin nedir?
Crossjoin (Kartezyen çarpım olarak da bilinir), iki liste arasındaki tüm olası kombinasyonları oluşturan güçlü bir işlemdir. Excel’de bu işlem, Liste A’daki her öğeyi Liste B’deki her öğeyle eşleştirerek eksiksiz bir kombinasyon matrisi sunar.
Crossjoin'ler aşağıdaki gibi birçok gerçek dünya veri senaryosunda son derece yararlıdır:
Ürün Varyasyonları
Renkler × bedenler × stiller kombinasyonuyla eksiksiz bir ürün kataloğunuzu oluşturun!
Satış Analizi
Bölgeler × Satış Temsilcileri × Çeyrekler kombinasyonlarını oluşturun.
Planlama ve Zamanlama
Çalışanlar × vardiyalar ya da öğrenciler × dersler gibi tüm olası kombinasyonları oluşturun.
Test ve Simülasyonlar
Modelleme, tahminleme veya doğrulama amacıyla senaryo kombinasyonları oluşturun.
Örnek:
Şuna sahipseniz:
Çapraz birleştirme sonucu şöyle olacaktır:
Excel'de Crossjoin Gerçekleştirin
Excel, cross join oluşturmak için birden fazla yöntem sunar ve en uygun yaklaşım, kullandığınız Excel sürümüne, formüllerle veya araçlarla olan rahatlık düzeyinize ve veri kümenizin büyüklüğüne bağlıdır. Aşağıda, basit formüllerden Power Query ve VBA gibi gelişmiş araçlara kadar dört pratik ve verimli yöntemi bulacaksınız. Her yöntemin kendine özgü avantajları vardır; bu nedenle iş akışınıza, veri boyutunuza ve otomasyon ihtiyaçlarınıza en iyi şekilde uygun olanı seçebilirsiniz.
Yöntem 1: Formül kullanarak Cross Join (Excel 365)
1.Verilerinizi hazırlayın. İlk listenizi bir sütuna (örneğin, Ürünler için A2:A5) ve ikinci listenizi başka bir sütuna (örneğin, Renkler için C2:C5) yerleştirin.
2. Sonucu görüntülemek istediğiniz boş bir hücreye aşağıdaki formülü girin, ardından Enter tuşuna basın. Formül anında yayılır ve oluşturulan tüm kombinasyonları tek seferde döker. Ekran görüntüsüne bakın:
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 &, "|" &, TRANSPOSE(C2:C5)), "|", ",")
Bu formülün açıklaması:
- A2:A5 & "|" & TRANSPOSE(C2:C5): A sütunundaki her değeri, C sütunundaki her değerle eşleştirir.C.
- TEXTJOIN(",", TRUE, …): Tüm çiftleri virgülle ayrılmış tek bir metin dizesinde birleştirir.
- TEXTSPLIT(…, "|", ","): Metni tekrar iki sütunlu bir tabloya böler.
Kısaca, formül A|C çiftlerini oluşturur, bunları tek bir metin dizesinde birleştirir ve ardından tüm olası kombinasyonları elde etmek için yapılandırılmış iki sütunlu bir tabloya geri böler.
İpuçları:
Daha önce gösterilen formüle ek olarak, aynı sonucu elde etmek için aşağıdaki formülü de kullanabilirsiniz.
=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
LAMBDA(r,c,
IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
)
))
Avantajlar
- Tamamen dinamik
- Yardımcı sütunlara gerek yok
- Kaynak listeler değiştiğinde otomatik olarak güncellenir
Dezavantajlar
- Excel 365 gerektirir
- Formül başlangıç seviyesi kullanıcılar için karmaşık görünür
✨ Tüm kombinasyonları listele — Tüm olasılıkları tek tıkla oluşturun!
Tüm olası kombinasyonları elde etmek için karmaşık formüller mi yazıyorsunuz?Kutools for Excelile birden fazla sütunun veya değerin her kombinasyonunu anında listeleyebilirsiniz —formül gerekmez, Power Query gerekmez, sadece birkaç tıklama yeterlidir!
Yöntem 2: Power Query kullanarak Cross Join
Power Query, tüm modern Excel sürümlerinde çalışan, temiz ve kod gerektirmeyen bir yaklaşımdır. Formül yazmanıza gerek kalmadan, hızlı ve tıklamayla gerçekleştirilen adımlarla cross join oluşturmanızı sağlar. Aşağıdaki adım adım talimatları izleyin.
Adım 1: Her sütun verisi için tablolar oluşturun
1.İlk veri listesini seçin, Ekle > Tablo seçeneğine tıklayın; Tablo Oluştur iletişim kutusunda Tamam’a tıklayın. Böylece ilk tablonuzu oluşturmuş olacaksınız.
2.Tablo Tasarımı sekmesinden, daha sonra başvurmak üzere kolaylık olması için tabloya anlamlı bir isim verin.
3. Diğer sütun verilerini de aynı adımları tekrarlayarak bir tabloya dönüştürün ve ona da bir isim verin.
Adım 2: Tabloları İçe Aktarın ve Bağlantı Olarak Yükleyin
1.İlk tabloyu seçin ve Veri>Tablodan/Araklıktan'a tıklayın; ekran görüntüsüne bakın:
2. Açılan Power Query Düzenleyici penceresinde, Giriş sekmesinden Kapat ve Yükle > Kapat ve Yükle seçeneğine tıklayın.
3. Bir Veri Al iletişim kutusu açılacak; Yalnızca Bağlantı Oluştur seçeneğini işaretleyin ve Tamam'a tıklayın.
4. Sağ tarafta Sorgular ve Bağlantılar bölmesi görünecek ve bir sorgunun yalnızca bağlantı olarak eklendiğini gösterecektir.
5. İkinci tabloyu da yalnızca bağlantı olarak yüklemek için aynı adımları tekrarlayın; bu durumda ikinci sorgu da Sorgular ve Bağlantılar bölmesinde ilk sorgunun yanında listelenmiş olacaktır.
Adım 3: Başvuru Sorgusu ve Özel Sütun Oluşturun
1. Çapraz birleştirmede ilk tablo olarak kullanmak istediğiniz sorguya sağ tıklayın ve Başvuru'yu seçin.
2.Power Query Düzenleyicipenceresinde Sütun Eklesekmesine gidin ve Özel Sütun'a tıklayın; ekran görüntüsüne bakın:
3.Özel Sütun iletişim kutusunda, çapraz birleştirme için kullanmak istediğiniz diğer tablonun adını Özel Sütun formülü kutusuna yazın. Ardından Tamam düğmesine tıklayın.
Not:
Sorgu adınız boşluk içeriyorsa (örneğin Ürün Rengi), Özel Sütun formülü kutusuna yazarken #"Sorgu Adı" söz dizimini kullanmanız gerekir. Örneğin, "Ürün Rengi" için #"Ürün Rengi" yazmalısınız.
4. Yeni bir özel sütun görünecektir; içeriğini görüntülemek için Genişlet düğmesine tıklayın.
5. Genişletilen bölmede, birleştirmek istediğiniz sütunun işaretli olduğundan emin olun. Ardından Tamam'a tıklayın.
6. Artık iki tablodan oluşturulabilecek tüm kombinasyonları göreceksiniz.
Adım 4: Veriyi çalışma sayfasına yükleyin
Giriş sekmesine gidin, Kapat ve Yükle > Kapat ve Yükle seçeneğine tıklayın. Tüm kombinasyonları içeren tablo, yeni bir çalışma sayfasına yüklenecektir.
→
Avantajlar
- Büyük Verileri İşler: Binlerce satırda üstün performans sunar.
- Yeniden Kullanılabilir ve Yenilenebilir: Kaynak aralığı tablosuna ek veri ekledikten sonra sorguyu yenileyin; sonuçlar otomatik olarak güncellenir.
Dezavantajlar
- Biraz daha fazla adım gerektirir
- Temel Power Query bilgisi gerektirir
Yöntem 3: PivotTable kullanarak Cross Join
Bu yöntem dolaylı olsa da, formül yazmadan tüm kombinasyonları oluşturmanız gerektiğinde şaşırtıcı derecede etkilidir. Özellikle daha görsel ve tıklamaya dayalı bir yaklaşımı tercih edenler veya dinamik dizi formüllerini desteklemeyen Excel sürümleriyle çalışan kullanıcılar için son derece kullanışlıdır.
1.Veri listeleri için iki ayrı tablo oluşturun ve bunlara Yöntem 2, Adım 1'de belirtilen adımları izleyerek isim verin.
2. İlk sütun olarak kullanmak istediğiniz tabloyu seçin. Ardından Ekle sekmesine gidin ve PivotTable'a tıklayın; ekran görüntüsüne bakın:
3.Tablodan veya Aralıktan PivotTable Oluştur iletişim kutusunda Mevcut çalışma sayfası seçeneğini işaretleyin, PivotTable konumunu belirleyin, Bu verileri Veri Modeline ekle kutucuğunu işaretleyin ve ardından Tamam’a tıklayın.
4. Sağ tarafta PivotTable Alanları bölmesi göründüğünde, tablodaki sütun adını işaretleyin; bu sütun otomatik olarak Satırlar alanına eklenecektir.
5. Ardından Tümü sekmesine geçin, PivotTable Alanları bölmesinde çapraz birleştirmenizin ikinci sütunu olarak kullanmak istediğiniz tabloyu seçin ve sütun adını işaretleyerek Satırlar alanına ekleyin. Aşağıdaki ekran görüntüsünde gösterildiği gibi PivotTable’yi elde edeceksiniz:
6. PivotTable'ın herhangi bir hücresine tıklayın, Tasarım sekmesine gidin ve Rapor Düzeni > Tablo Formunda Göster seçeneğini belirleyin. Böylece PivotTable’ı tablo biçiminde görüntülemiş olacaksınız. Ekran görüntüsüne bakın:
7. Ardından, her satırda tüm öğelerin görüntülenmesini sağlamak için Rapor Düzeni > Tüm Öğe Etiketlerini Tekrarla seçeneğine tıklayın.
8. Son olarak, şunu tıklayın: Genel Toplamlar > Satırlar ve Sütunlar için Kapalı.
Artık ÖzetTablo, özet satır veya sütunlar olmadan tüm kombinasyonları temiz bir şekilde listelemektedir.
Avantajlar
- Formül veya Power Query gerekmez
- Çok kolay ve görseldir
- Hızlı analizler için uygundur
Dezavantajlar
- Dinamik değildir
- Manuel işlemler gerektirir
- Çıktı, orijinal verilerle bağlantılı değildir
Yöntem 4: Kullanıcı Tanımlı İşlev kullanarak Cross Join (Excel 365 / Excel 2021 ve sonrası)
İki liste arasındaki tüm olası kombinasyonları sık sık oluşturmanız gerekiyorsa, Kullanıcı Tanımlı İşlev (UDF) temiz, yeniden kullanılabilir ve son derece esnek bir çözüm sunar. Excel 365 veya Excel 2021 ile dökülen dizi çıktısı, sonuçların tamamen dinamik olmasını sağlar ve cross join işlemlerini otomatikleştirmek için güçlü ve pratik bir yol sunar.
1. VBA düzenleyicisini açmak için Alt + F11 tuşlarına basın.
2. Ardından, Ekle > Modül seçeneğine tıklayın ve aşağıdaki kodu boş modüle kopyalayıp yapıştırın.
Function CrossJoin(list1 As Range, list2 As Range)
'Updateby Extendoffice
Dim arr1, arr2, result()
Dim i As Long, j As Long, r As Long
arr1 = list1.Value
arr2 = list2.Value
ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
r = 1
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr2, 1)
result(r, 1) = arr1(i, 1)
result(r, 2) = arr2(j, 1)
r = r + 1
Next j
Next i
CrossJoin = result
End Function
3. Excel çalışma sayfasına dönün, aşağıdaki formülü girin ve Enter tuşuna basın; Excel tüm kombinasyonları otomatik olarak listeleyecektir.
=CrossJoin(A2:A5, C2:C5)
Sonuç
Özetle, Excel'de crossjoin yapmak, ihtiyaçlarınıza ve çalışma ortamınıza göre en uygun yöntemi seçmenizi sağlayan çok sayıda esnek ve verimli çözüm sunar:
- Formül, programlama gerektirmeden sonuçları hızlıca üretmek için Excel 365’teki dinamik dizi formüllerinden yararlanır ve yerel formülleri tercih eden hafif kullanıcılar için idealdir.
- Power Query, büyük veri kümelerini işleyebilen, temiz ve yeniden kullanılabilir bir süreç sunar; bu da onu veri temizliği ve otomatik raporlama için en uygun seçenek haline getirir.
- PivotTable yöntemi daha dolaylı olabilir; ancak tanıdık bir kitle için son derece etkili ve sezgiseldir.
- VBA Kullanıcı Tanımlı İşlevi, özelleştirme açısından en yüksek esnekliği sunar ve karmaşık makro kodlarıyla entegre çalışması gereken senaryolar için idealdir.
En İyi Office Üretkenlik Araçları
Kutools for Excel ile Excel becerilerinizi üst seviyeye taşıyın ve verimlilikten önce hiç tecrübe etmediğiniz kadar faydalanın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300'den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project’te sekmeli düzenleme ve okumayı etkinleştirin.
- Birden fazla belgeyi yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açın ve oluşturun.
- Günlük üretkenliğinizi %50 artırır ve size her gün yüzlerce fare tıklamasından tasarruf sağlar!
Tüm Kutools eklentileri — tek bir yükleyiciyle!
Kutools for Office paketi; Excel, Word, Outlook ve PowerPoint için eklentileriyle birlikte Office Tab Pro'yu da içerir ve Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi-bir-arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek yükleyici, tek lisans— birkaç dakikada kurulum (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— tek tek eklenti satın almaya göre tasarruf edin