Excel'de en düşük notu düşüp kalan değerlerin ortalamasını veya toplamını nasıl alabilirsiniz?
Excel'de bir not veya puan listesiyle çalışırken, öğrencinin nihai notunu hesaplamak için en düşük puanı veya en düşük n puanını hariç tutmanız gerekebilir ve ardından kalan değerlerin ortalamasını veya toplamını almanız gerekebilir. Bu, öğrencilerin en kötü performanslarını düşerek anormallikleri hesaba katmak veya adil olmayı sağlamak için izin verildiği eğitim ortamlarında yaygın bir gereksinimdir. Bu işlemi manuel olarak yapmak, özellikle büyük veri setleri veya sıkça yapılan hesaplama ayarlamaları durumunda can sıkıcı olabilir. Neyse ki, Excel bu işlemi gerçekleştirmek için formüllerden VBA ile otomasyona kadar çeşitli esnek yöntemler sunar.
En düşük notu düşüp formüllerle ortalama veya toplam alma
VBA Kodu - En düşük veya en düşük n notu düşüp toplamı veya ortalamayı otomatik olarak hesaplama
En düşük notu düşüp formüllerle ortalama veya toplam alma
Bir veri satırından veya listeden en düşük veya en düşük n değeri hariç tutup ardından kalan sayıların ortalamasını veya toplamını hesaplamak istiyorsanız, Excel'in yerleşik formülleri pratik bir yaklaşım sunar. Bu formül çözümleri, işlenecek orta düzeyde satır sayısına sahipseniz veya şeffaflık ve kolay ayarlama tercih ettiğinizde özellikle kullanışlıdır.
Aşağıda, toplama ve ortalamaya yönelik ayrı açıklamalar bulacaksınız; ayrıca kapsamlı talimatlar ve esnek uygulama için pratik ipuçları da bulunmaktadır.
Sayıları toplayın ancak en düşük veya en düşük N sayıları hariç tutun:
Her satır veya liste için en düşük değeri hariç tutarak toplamı hesaplamak için aşağıdaki yöntemi kullanın:
1. Toplam sonucunun (örneğin, verileriniz B2:H2 aralığında ise I2 hücresinde) ilk satır için görüneceği boş bir hücre seçin, ardından aşağıdaki formülü girin:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Onaylamak için Enter'a basın, ardından formülü diğer satırlara uygulamak için doldurma tanıtıcısını aşağı sürükleyin. Bu, her satırdaki tüm sayıları en düşük değer hariç tutarak toplayacaktır.
Referans için bir ekran görüntüsü aşağıda bulunmaktadır:
Notlar ve ipuçları:
- En düşük iki, üç veya daha fazla değeri hariç tutmak için formülü, ek SMALL sonuçları çıkararak genişletebilirsiniz. Örneğin:
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)- Bu formüllerde, B2:H2 toplamak istediğiniz aralıktır ve 1, 2, 3 vb. sayılar hariç tutulacak en küçük n sayılarını belirtir. Düşürmek istediğiniz en düşük not sayısına göre n'yi ayarlayın.
- n'yi değerlerin toplam sayısından büyük veya eşit ayarlamamaya dikkat edin; aksi takdirde hatalar veya istenmeyen sonuçlarla karşılaşabilirsiniz.
- Bu formüller satırlar için bağımsız olarak çalışır. Verileriniz satırlar yerine sütunlarda ise, aralıkları buna göre ayarlayın.
- Veri setinizde en düşük sayının birden fazla tekrarı varsa, SMALL(B2:H2,1) her referans için yalnızca bir kez düşecektir. Birden fazla tekrarı düşürmek için yukarıda gösterildiği gibi k sayılarını artırarak SMALL terimini tekrarlayın.
Sayıların ortalamasını alın ancak en düşük veya en düşük N sayıları hariç tutun:
En düşük veya en düşük n değerleri göz ardı edilerek ortalamayı hesaplamak için aşağıdaki formülleri kullanabilirsiniz. Bu hesaplama, özellikle düşük performanslı aykırı değerlerin ortalamaya dahil edilmediği notlandırma düzenlerinde çok kullanışlıdır.
1. Ortalama sonucu için bir hücre seçin (örneğin, puanlarınız B2:H2 aralığında ise J2) ve formülü girin:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Enter'a bastıktan sonra, gerekirse formülü aşağı sürükleyerek ek satırların ortalamasını alın; her seferinde o satır için aralıktaki en düşük puan hariç tutulur.
Notlar ve önemli uyarılar:
- Birden fazla en düşük puanı düşerek ortalamayı almak için formülü, ek SMALL terimleri çıkararak ve böleni buna göre azaltarak genişletin:
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)- Yine, B2:H2 ortalama alınacak aralıktır ve n hesaplamaya dahil edilmeyecek en düşük değerlerin sayısını temsil eder.
- Aralıktaki mevcut değerlerden daha fazla sayı çıkarmaya çalışırsanız, formüller yetersiz değerler olduğuna dair bir #SAYI! hatası döndürür. Her zaman n'nin sayıların sayısından küçük olduğundan emin olun.
- Sonuçları etkileyebileceği için, hariç tutmadan önce en düşük değerlerin kritik olmadığından veya hesaplamanız için gerekli olmadığından emin olmak için kontrol etmeniz önerilir.
- Çok büyük veri setleri veya dinamik olarak en düşük n değerlerini düşürme durumunda, otomatik veya dizi çözümü düşünün.
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.
VBA Kodu - En düşük veya en düşük n notu düşüp toplamı veya ortalamayı otomatik olarak hesaplama
Büyük veya sık değişen veri setleriyle ilgilenirken veya en düşük n notları düşürmeyi ve birçok satırda toplamları veya ortalamaları hesaplamayı otomatikleştirmeniz gerektiğinde, VBA kullanmak tekrarlayan işlemleri büyük ölçüde basitleştirebilir. Bir VBA makrosuyla, veri aralığını ve kaç en düşük notun hariç tutulacağını belirtebilir ve kod, seçilen tüm satırları tek bir adımda verimli bir şekilde işleyebilir.
Bu otomatik yaklaşım, birden fazla sınıfın sayfalarını yöneten öğretmenler veya manuel formül girişini ve potansiyel hataları en aza indirmek isteyen herkes için özellikle kullanışlıdır. Aşağıdaki çözüm, hem hariç tutma sayısını hem de hedef fonksiyonu (toplam veya ortalama) kolayca ayarlamanıza olanak tanır.
Başlamadan önce, çalışan makrolar geri alınamadığı için çalışma kitabınızı kaydedin.
1. Geliştirici > Visual Basic'e tıklayın. Microsoft Visual Basic for Applications penceresinde Ekle > Modül'e tıklayın ve ardından aşağıdaki kodu girin:
Sub DropLowestNandCalculate()
Dim WorkRng As Range
Dim OutputRng As Range
Dim n As Integer
Dim FuncType As String
Dim i As Integer, j As Integer, k As Integer
Dim Arr() As Variant, TempArr() As Double
Dim RowSum As Double
Dim RowCount As Integer
Dim MinIdx() As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
For i = 1 To WorkRng.Rows.Count
Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
RowCount = UBound(Arr)
ReDim TempArr(1 To RowCount)
For j = 1 To RowCount
TempArr(j) = Arr(j)
Next j
' Mark n lowest values as used by setting to very high number
For k = 1 To n
Dim MinVal As Double, MinPos As Integer
MinVal = Application.WorksheetFunction.Min(TempArr)
For j = 1 To RowCount
If TempArr(j) = MinVal Then
TempArr(j) = 1E+308
Exit For
End If
Next j
Next k
RowSum = 0
Dim ValidCount As Integer
ValidCount = 0
For j = 1 To RowCount
If TempArr(j) <> 1E+308 Then
RowSum = RowSum + Arr(j)
ValidCount = ValidCount + 1
End If
Next j
If UCase(FuncType) = "AVG" Then
If ValidCount = 0 Then
OutputRng.Cells(i, 1).Value = "N/A"
Else
OutputRng.Cells(i, 1).Value = RowSum / ValidCount
End If
Else
OutputRng.Cells(i, 1).Value = RowSum
End If
Next i
End Sub2. Kodu ekledikten sonra 
düğmesine tıklayın veya F5 tuşuna basın.
3. Açılan talimatları takip edin:
- İşlemek istediğiniz puan aralığını seçin (her öğrencinin puanlarının bir satırda olduğundan emin olun).
- Çıktı aralığının sol üst hücresini seçin (çıktı, satır sayısına göre aşağı doğru doldurulacaktır).
- Düşürmek istediğiniz en düşük puan sayısını girin (örneğin, her satırda yalnızca en düşük notu hariç tutmak için 1).
- Düşürülen notlar hariç toplam almak için TOPLA yazın veya düşürülen notlar hariç yeniden hesaplanmış ortalama almak için ORT yazın.
Makro, belirtilen puan alanındaki her satırı işler ve seçilen toplamı veya ortalamayı çıktı aralığına yerleştirir. Bir satırdaki tüm notlar düşürülürse, sonuç hata oluşmasını önlemek için N/A olarak işaretlenir.
- Girdi aralığının veri yapısıyla (her satırda bir öğrencinin notları) eşleştiğinden emin olun.
- Sayısal olmayan hücreler (örneğin, boş veya metin) varsayılan olarak göz ardı edilecektir.
- Bu VBA kodu, sınıflar boyunca tekrarlayan not hesaplamalarını büyük ölçüde hızlandırır ve düşürülecek not sayısının esnek ayarlanmasını destekler.
- Bu tür işlemler sık sık gerçekleştiriyorsanız, bu makroyu çalışma sayfanızdaki bir düğmeyle ilişkilendirmek isteyebilirsiniz, böylece daha hızlı erişim sağlanır.
Yanlış çıktılar veya hatalar gibi sorunlarla karşılaşırsanız, aralıkların doğru bir şekilde belirtildiğinden ve "n"nin her satır için mevcut notların toplam sayısından büyük veya eşit olmadığından emin olun.
Hem en yüksek hem de en düşük puanları düşürme veya satırlar yerine sütunlar işleme gibi benzer otomasyon gereksinimleri için VBA kod mantığına küçük değişiklikler yapılabilir.
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