Excel'de 3 sütunda ortak değerleri nasıl bulurum?
Excel'de verilerle çalışmak genellikle ortak veya yinelenen girdileri belirlemek için listeleri karşılaştırmayı içerir. İki sütunu karşılaştırarak ortak değerleri bulmak sık yapılan bir görev olsa da, bazen hangi değerlerin aynı anda üç ayrı sütunda göründüğünü belirlemeniz gerekebilir. Örneğin, anket verilerini birleştirirken, satış kayıtlarını birleştirirken veya birden fazla listedeki yinelenen girdileri analiz ederken, aşağıdaki ekran görüntüsünde gösterildiği gibi, tüm üç sütunda bulunan öğeler kümesini doğru bir şekilde çıkarmak önemlidir. Bu makale, formüller tercih edip etmediğinizden bağımsız olarak Excel'deki bu sorunu çözmek için birkaç pratik yöntem sunar ve size üç sütun arasında ortak değerleri verimli ve güvenilir bir şekilde belirlemenizi sağlar.
Dizi formülleri ile 3 sütunda ortak değerleri bulun
Üç sütunda bulunan değerleri çıkarmak için VBA makrosu
Dizi formülleri ile 3 sütunda ortak değerleri bulun
Üç sütun arasında ortak değerleri bulmak ve çıkarmak için seçili tüm aralıklarda görünen öğeleri aramak üzere tasarlanmış dizi formüllerini kullanabilirsiniz. Bu, ek Excel eklentilerine veya harici araçlara güvenmek istemediğiniz veri setlerinde özellikle yararlıdır.
İlk ortak değeri görüntülemek istediğiniz boş bir hücreye şu dizi formülünü girin:
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))Bu dizi formülünü nasıl kullanılır:
- Formülü seçtiğiniz hücreye girdikten sonra, Enter'a değil Shift + Ctrl + Enter tuşlarına basın. Excel, formülü dizi formülü olduğunu belirtmek için süslü parantez içine alacaktır.
- Boş hücreler görünene kadar formülü sütun boyunca aşağı sürükleyin. Bu, üç sütun tarafından paylaşılan tüm değerleri listeleyecektir ve boş hücreler artık eşleşme olmadığını gösterecektir.
Notlar ve parametre açıklamaları:
- Farklı bir dizi formülü tercih ederseniz, bu formül de üç sütunda bulunan tüm benzersiz değerleri döndürür:
Yine hatırlatmak gerekirse, formülü yazdıktan veya yapıştırdıktan sonra Shift + Ctrl + Enter tuşlarına basın.=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0)) - Bu formüllerde:
- A2:A10, B2:B8, C2:C9, karşılaştırmak istediğiniz üç sütundaki veri aralıklarıdır.
- E1, formülünüzün başladığı hücrenin hemen üzerindeki hücreye atıfta bulunur (hariç tutma mantığı için). Hücre referanslarını gerçek veri aralıklarınızla ve sonuçların görüneceği konumu eşleştirmek için ayarlayın.
- Bu yöntemler orta büyüklükteki veri setleri için iyi çalışır, ancak dizi formüllerinin hesaplama talepleri nedeniyle çok büyük hacimlerde yavaşlayabilir.
- Ortak sonuçlara veya formül hatalarına yol açabileceğinden, kaynak aralıkları işin ortasında değiştirmekten kaçının.
- Sonuçta boş satırlar varsa, bu tüm ortak değerlerin çıkarıldığını ve kalan hücrelerde artık başka kesişim olmadığını gösterir.
Kutools AI ile Excel Sihirini Keşfedin
- Akıllı Yürütme: Hücre işlemleri gerçekleştirin, verileri analiz edin ve grafikler oluşturun—tümü basit komutlarla sürülür.
- Özel Formüller: İş akışlarınızı hızlandırmak için özel formüller oluşturun.
- VBA Kodlama: VBA kodunu kolayca yazın ve uygulayın.
- Formül Yorumlama: Karmaşık formülleri kolayca anlayın.
- Metin Çevirisi: Elektronik tablolarınız içindeki dil engellerini aşın.
Üç sütunda bulunan değerleri çıkarmak için VBA makrosu
Karmaşık formüller girmeyi veya kopyalamayı gerektirmeyen otomatik bir yaklaşımı tercih ediyorsanız, Excel VBA'yı kullanarak verilerinizde döngü oluşturabilir ve yalnızca her üç sütunda da bulunan değerleri çıkarabilirsiniz. Bu yöntem, özellikle çok büyük veri setleri veya dinamik aralıklarla çalışırken faydalıdır çünkü VBA, tekrarlayan görevlerle ve özel kriterlerle uğraşmakta daha etkilidir.
1. Geliştirici > Visual Basic'e tıklayarak VBA düzenleyicisini açın (eğer Geliştirici sekmesi görünür değilse, Dosya > Seçenekler > Şeridi Özelleştir üzerinden etkinleştirebilirsiniz).
2. VBA düzenleyicisinde Ekle > Modül'e tıklayarak yeni bir modül oluşturun. Ardından aşağıdaki kodu modül penceresine yapıştırın:
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub3. VBA penceresinde, modül seçili iken F5'e basın veya Çalıştır (▶) düğmesine tıklayın. Karşılaştırmak istediğiniz üç sütun aralığını sırayla seçmeniz istenecektir. Her istem sırasında uygun hücreleri fareyle vurgulayın.
4. Makro, seçimlerinizi işleyecek ve mevcut veri setinizin sağındaki bir sonraki boş sütuna, ilk satırdan başlayarak üç sütunda da bulunan tüm değerleri çıkaracaktır.
Bu yöntem, büyük veya dinamik veri setleriyle çalışırken etkilidir ve sözlük mantığını çoğaltarak dört veya daha fazla sütuna kolayca genişletilebilir. Makroları çalıştırmadan önce çalışma kitabınızı kaydetmeyi unutmayın, çünkü geri dönmek isterseniz kaydedilmemiş değişiklikler geri alınamaz.
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin