Excel'de Ağırlıklı Ortalama Hesaplama: TOPLA.ÇARPIM Formülü

TOPLA.ÇARPIM (İngilizce SUMPRODUCT) ve TOPLA (İngilizce SUM) ile yazılan =TOPLA.ÇARPIM(B2:B5;C2:C5)/TOPLA(C2:C5) bir ağırlıklı ortalama hesaplar: B'deki her puan C'deki ağırlığıyla çarpılır, çarpımlar toplanır ve toplam, ağırlıkların toplamına bölünür. Tablodaki formülleri de bu şekilde, Türkçe adlarla ve noktalı virgülle yazabilirsiniz.

FormüllerSıfırla

F2fx

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

	A	B	C	D	E	F
1	Part	Score	Weight		Average	Result
2	Homework	85	20%		Weighted	81.2
3	Quizzes	78	30%		Plain AVERAGE	80.75
4	Midterm	72	20%
5	Final	88	30%

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =TOPLA.ÇARPIM(B2:B5;C2:C5)/TOPLA(C2:C5)

Ağırlıklı not 81.2'dir, düz bir ORTALAMA ise 80.75 verir, çünkü %20 değerindeki ödevi %30 değerindeki final kadar sayar. Finalin puanını değiştirin, ağırlıklı not ödevdeki aynı değişiklikten daha çok oynar.

Excel'de bir AĞIRLIKLI.ORTALAMA fonksiyonu yoktur, bu yüzden TOPLA'ya bölünen TOPLA.ÇARPIM standart formüldür. Google E-Tablolar'da AVERAGE.WEIGHTED(B2:B5,C2:C5) vardır.

Ağırlıklı ortalama formülü nasıl çalışır

TOPLA.ÇARPIM iki aralığı satır satır çarpar ve sonuçları toplar. Bir yardımcı sütunla açık yazıldığında bu, bir çarpımlar sütunu ve onların TOPLA'sıdır:

FormüllerSıfırla

D6fx

=SUM(D2:D5)

	A	B	C	D
1	Part	Score	Weight	Score x weight
2	Homework	85	20%	17.0
3	Quizzes	78	30%	23.4
4	Midterm	72	20%	14.4
5	Final	88	30%	26.4
6	Total		100%	81.2
7	Weighted average			81.2

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =TOPLA(D2:D5)

Her bölüm puanı çarpı ağırlığı kadar katkıda bulunur: 85 × %20, 17.0; 78 × %30, 23.4 eder ve böyle devam eder. Toplamları 81.2'dir. Ağırlıkların toplamı %100 olduğu için C6'ya bölmek burada hiçbir şeyi değiştirmez, ama toplam %100 olmadığında formülü doğru tutan budur.

Toplamı %100 olmayan ağırlıklar

Ağırlıkların yüzde olması gerekmez. Bir not ortalaması kredi saatleriyle, bir ortalama fiyat miktarla ağırlıklandırılır. Ağırlıkların TOPLA'sına bölmek her toplamı karşılar.

FormüllerSıfırla

F2fx

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

	A	B	C	D	E	F
1	Course	Grade points	Credits		Average	Result
2	Math	4	4		Weighted GPA	3.51
3	History	3	3		Plain average	3.48
4	Biology	3.7	4		Total credits	14
5	Art	2.7	2
6	Lab	4	1

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =TOPLA.ÇARPIM(B2:B6;C2:C6)/TOPLA(C2:C6)

Bölme olmasaydı formül not puanları çarpı kredilerin toplamını, burada 49,2'yi döndürürdü, bir not ortalamasını değil. Bölmeyle F2, 14 krediyle ağırlıklandırılmış not ortalamasını gösterir. Dört kredilik dersler ortalamayı kendi notlarına doğru çeker, bir kredilik laboratuvar ise onu neredeyse kıpırdatmaz: B6'yı 2 yapın ve F2'nin F3'e kıyasla ne kadar az değiştiğini izleyin.

Ağırlıklarınız toplamı tam olarak %100 olan yüzdelerse tek başına =TOPLA.ÇARPIM(B2:B5;C2:C5) aynı sonucu verir. Yine de /TOPLA(...) kısmını tutun: bir ağırlık değiştirilip toplam %105 olduğu gün, onsuz formül yanlış olur ve sayfada bunu söyleyen hiçbir şey olmaz.

Koşullu ağırlıklı ortalama

Yalnızca bazı satırları ağırlıklandırmak için TOPLA.ÇARPIM'ın içinde bir koşulla çarpın ve eşleşen ağırlıkları ETOPLA ile toplayın. Aşağıda bölge başına ortalama fiyat, satılan miktarla ağırlıklandırılmıştır.

FormüllerSıfırla

G2fx

=SUMPRODUCT((A2:A7=F2)*C2:C7*D2:D7)/SUMIF(A2:A7,F2,D2:D7)

	A	B	C	D	E	F	G
1	Region	Product	Price	Qty		Region	Average price
2	North	Apple	$1.20	100		North	$1.45
3	South	Pear	$1.50	40		South	$1.36
4	North	Pear	$1.50	60
5	South	Apple	$1.20	120
6	North	Plum	$2.00	40
7	South	Plum	$2.00	20

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =TOPLA.ÇARPIM((A2:A7=F2)*C2:C7*D2:D7)/ETOPLA(A2:A7;F2;D2:D7)

North 100 elma, 60 armut ve 40 erik sattı, bu yüzden ortalama fiyatı $1.45'tir; üç fiyatın düz ortalamasından daha çok elma fiyatına yakındır. (A2:A7=F2) koşulu North satırlarında 1, diğer yerlerde 0'dır; böylece diğer satırlar paya hiçbir şey eklemez, ETOPLA da paydada yalnızca North miktarlarını toplar. Türkçe Excel'de G2 =TOPLA.ÇARPIM((A2:A7=F2)*C2:C7*D2:D7)/ETOPLA(A2:A7;F2;D2:D7) olarak yazılır.

Alıştırma: ağırlıklı ortalama fiyat

FormüllerSıfırla

F2fx

	A	B	C	D	E	F
1	Batch	Price	Qty		Average	Result
2	Jan	$4.20	100		Weighted price
3	Feb	$4.50	40
4	Mar	$3.90	250
5	Apr	$4.80	10
6	May	$4.10	120

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.

Sıra sende: Aynı ürünü farklı fiyatlarla beş partide aldınız. Her partinin miktarıyla ağırlıklandırılmış birim başına ortalama fiyatı hesaplayın. Formülü F2'ye yazın.

Kontrol etCevabı göster

Yanlış ağırlıklı ortalama veren hatalar

• Çarpımların ORTALAMA'sı. Bir puan × ağırlık sütunu üzerinde =ORTALAMA(D2:D5) ağırlıklara değil satır sayısına böler ve küçük, anlamsız bir sayı verir. Çarpımların TOPLA'sını ağırlıkların TOPLA'sına bölün.
• Ağırlıklar yerine sayıya bölmek. =TOPLA.ÇARPIM(B2:B6;C2:C6)/BAĞ_DEĞ_SAY(B2:B6) yalnızca her ağırlık 1 olduğunda doğrudur.
• Hizalanmayan aralıklar. =TOPLA.ÇARPIM(B2:B6;C3:C7) her değeri bir sonraki satırın ağırlığıyla eşleştirir. İki aralık da aynı satırlarda başlayıp bitmelidir; farklı boyutlar #VALUE! (Türkçe Excel'de #DEĞER!) döndürür.
• Boş bir ağırlık. Boş bir ağırlık 0 sayılır, bu yüzden o satır sessizce dışarıda kalır. Eksik bir ağırlık hesaplamayı durdurmalıysa önce =BOŞLUKSAY(C2:C6) ile denetleyin.
• Ortalamaların ortalamasını almak. 70 (10 öğrenci) ve 90 (30 öğrenci) olan iki sınıf ortalamasının ortalaması 80 değildir. Onları sınıf büyüklükleriyle ağırlıklandırın, sonuç 85 olur; EĞERORTALAMA sayfasında koşullarla aynı tuzak var.