Excel'de birden fazla koşul söz konusuysa medyan nasıl hesaplanır?
Excel’de bir veri kümesinin medyanını hesaplamak, veri analizi ve raporlama süreçlerinde sıklıkla karşılaşılan temel bir gereksinimdir. Basit bir aralık için medyanı bulmak, standart Excel işlevleriyle kolayca yapılabilir; ancak genellikle yalnızca belirli kriterleri karşılayan veriler üzerinden medyan değeri elde etme ihtiyacı ortaya çıkar—örneğin, büyük bir veri kümesi içinde belirli bir ürünün belirli bir tarihteki satış tutarlarının medyanını hesaplamak gibi. Bu tür koşullu ve karmaşık senaryoları geleneksel işlevlerle gerçekleştirmek zor olabilir. Bu eğitimde, Excel’de birden fazla koşula göre medyan hesaplamak için hem formül tabanlı pratik çözümleri hem de ileri düzey ihtiyaçlar için VBA ile otomasyonu detaylıca inceleyeceğiz.
- Birden fazla koşulu karşılayan değerlerin medyanını hesapla
- VBA Kodu - Birden fazla koşul ile medyan hesaplama
Birden fazla koşulu karşılayan değerlerin medyanını hesapla
Aşağıda gösterildiği gibi bir Veri Aralığınız olduğunu varsayalım ve göreviniz, iki kritere göre filtrelenen verilerin medyan değerini belirlemek olsun: örneğin, A sütununda "a" değeri olan ve C sütununda "2-Oca" tarihine sahip satırlar için B sütunundaki medyan değeri hesaplamak. Bu senaryo, özellikle satış raporlarında, sınıf sınav sonuçlarında ve birden fazla kategoriye göre filtreleme gerektiren diğer iş veya akademik veri analizlerinde sıkça karşılaşılan bir durumdur.
Çalışma sayfanızı Netlik açısından aşağıdaki gibi hazırlayın: Excel sayfanıza koşullarınızı girin ve aşağıda gösterilen görünüme benzer bir düzen oluşturun. Burada E sütunu, A sütunu için kriterleri içerirken; F ve sonraki sütunların 1. satırı ise C sütunundaki tarih kriterlerini temsil eder.
Birden fazla kriteri karşılayan medyanı hesaplamak için, koşullarınız temelinde filtrelenmiş bir değer listesi oluşturmak amacıyla MEDYAN ve EĞER işlevlerini kullanan bir dizi formülü kullanabilirsiniz. İşte nasıl yapacağınız:
1.Medyan sonucunun görünmesini istediğiniz F2 hücresine tıklayın ve aşağıdaki formülü girin:
=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))Bu formül, her satırda A sütunundaki değerin E2 hücresindeki koşulla ve C sütunundaki değerin F1 hücresindeki başlıkla eşleşip eşleşmediğini kontrol eder. Her iki koşul da sağlanıyorsa, ilgili satırdaki B sütunu değerini medyan hesaplamasına dahil eder.
2. Formülü girdikten sonra sadece Enter tuşuna basmak yerine Ctrl + Shift + Enter tuşlarına basın; çünkü bu bir dizi formülüdür. Excel, dizi formülünü göstermek için formülün çevresine otomatik olarak süslü parantezler { } ekleyecektir.
3.Farklı koşullar altında medyanlara ihtiyacınız olan diğer hücrelere formülü kopyalamak için F2 hücresinin sağ alt köşesindeki doldurma tutamacını sürükleyin, aşağıda gösterildiği gibi:
Parametre açıklamaları ve kullanım ipuçları: Formülde, $A$2:$A$12 ilk koşulu içeren aralıktır (örneğin ürün adları), $C$2:$C$12 ikinci koşulun aralığıdır (örneğin tarihler) ve $B$2:$B$12 medyanını almak istediğiniz sayısal değerleri içeren aralıktır. Bu aralıkları kendi çalışma sayfanıza göre ayarlayın. Formülü kopyalarken aralıkların kaymaması için her zaman mutlak başvuruları ($ sembolleri) kullanın.
Önlemler: Eğer hiçbir değer her iki koşulu da karşılamıyorsa, formül bir #SAYI! hatası döndürür. Karışıklığı önlemek için formülü, boş bir hücre veya özel bir mesaj döndürecek şekilde HATA.EĞER işlevinin içine yerleştirebilirsiniz:
=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")Medyan sütununuzda boş hücrelerin veya sayısal olmayan değerlerin olmadığından emin olun; aksi takdirde sonuçlar etkilenebilir.
Bu formül tabanlı yaklaşım, nispeten basit koşullar olduğunda (genellikle iki veya üç kritere kadar) uygundur. Kurulumu hızlıdır ve programlama bilgisi gerektirmez. Ancak, dinamik koşullarla karmaşık filtreleme veya daha büyük veri kümeleri söz konusu olduğunda dizi formüllerini sürdürmek veya düzenlemek zorlaşabilir.
VBA Kodu - Birden fazla koşul ile medyan hesaplama
Koşullu medyan hesaplamasını otomatikleştirmeniz gereken senaryolarda—örneğin çok sayıda koşulun, büyük veri kümelerinin veya sıkça değişen kriterlerin söz konusu olduğu durumlarda—VBA çözümü pratik bir alternatif sunar. VBA ile, istediğiniz sayıda koşula göre medyanı hesaplayabilen yeniden kullanılabilir bir makro oluşturabilirsiniz. VBA tabanlı çözümler, tekrarlayan analizleri kolaylaştırmak veya raporlama ve göstergeler için özelleştirilmiş Excel süreçleri geliştirmek istiyorsanız özellikle değerlidir.
Koşullu medyan hesaplaması için VBA kullanmak üzere aşağıdaki adımları izleyin:
1. Geliştirici Araçları > Visual Basic öğesine tıklayın. Yeni bir Microsoft Visual Basic for Applications penceresi açılacaktır. Ekle > Modül seçeneğine tıklayıp aşağıdaki kodu Modül'e yapıştırın:
Sub ConditionalMedian()
Dim DataRange As Range
Dim CriteriaRange1 As Range
Dim CriteriaRange2 As Range
Dim OutputRange As Range
Dim Criteria1 As Variant
Dim Criteria2 As Variant
Dim TempArr() As Double
Dim i As Long
Dim j As Long
Dim count As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
count = 0
For i = 1 To DataRange.Rows.count
If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
ReDim Preserve TempArr(count)
TempArr(count) = DataRange.Cells(i, 1).Value
count = count + 1
End If
Next i
If count = 0 Then
OutputRange.Value = "No match"
Else
Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
If count Mod 2 = 1 Then
OutputRange.Value = TempArr(count \ 2)
Else
OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
End If
End If
End Sub
Sub QuickSort(arr() As Double, first As Long, last As Long)
Dim i As Long
Dim j As Long
Dim pivot As Double
Dim temp As Double
i = first
j = last
pivot = arr((first + last) \ 2)
Do While i <= j
Do While arr(i) < pivot
i = i + 1
Loop
Do While arr(j) > pivot
j = j - 1
Loop
If i <= j Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
i = i + 1
j = j - 1
End If
Loop
If first < j Then
QuickSort arr, first, j
End If
If i < last Then
QuickSort arr, i, last
End If
End Sub2. Kodu çalıştırmak için 
düğmesine tıklayın (veya F5 tuşuna basın). Ardından gerekli aralıkları seçmeniz ve kriterlerinizi girmeniz istenecektir. İstemleri tamamladıktan sonra, tüm kriterleri karşılayan medyan değeri belirttiğiniz hedef hücreye yazılacaktır.
Bu makro, her çalıştırıldığında değer aralığını, kriter aralıklarını, kriter değerlerini ve sonucun nereye yazılacağını esnek bir şekilde belirlemenize olanak tanır. Ayrıca, gerekirse koda ek koşullar kolayca eklenebilir.
İpuçları ve sorun giderme: VBA çözümleri kullanırken tüm Aralık seçimlerinin uzunluklarının eşit olduğundan ve kriterlerin doğru veri türüyle ve biçimlendirmeye uygun olduğundan emin olun (örneğin metin mi, tarih mi?). Hiçbir değer kriterleri karşılamıyorsa çıktı olarak "Eşleşme yok." mesajı görüntülenir. En iyi kararlılık için makroyu çalıştırmadan önce çalışma kitabınızı mutlaka kaydedin ve istendiğinde her zaman makroları etkinleştirin. Bu VBA çözümü, makro güvenlik ayarlarına aşina kullanıcılar ile otomatik Excel iş akışlarında kullanım için idealdir.
Özetle, VBA yaklaşımı, yalnızca formüllerle zahmetli veya zor gerçekleştirilen karmaşık medyan hesaplamalarını otomatikleştirir; özellikle değişken koşullar, sık tekrarlanan hesaplamalar ve büyük veri kümeleriyle çalışırken idealdir.
İlgili Makaleler:
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayı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 okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (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— ayrı ayrı eklenti satın almaya göre tasarruf sağlar