[Excel, VBA] Aufsummieren von Zahlen ohne ihre Duplikate
Gepostet am: Jun 14, 2011 11:18:3 AM
Ausgangslage ist eine Liste von Zahlen, in der bestimmte Zahlen doppelt vorkommen:
Und es soll nur jede Zahl einmal in die Summe einbezogen werden: (Für das konkrete Beispiel also folgendes:)
Aber wie geht das schneller? Mithilfe einer Matrixformel: {=SUMME(WENN(ISTZAHL(Bereich);1/ZÄHLENWENN(Bereich;Bereich)*Bereich))}
Achtung: mit StrG + Umschalt + Enter abschließen!
Da die Formel aber nicht wirklich leicht zu merken ist, analysiere ich mal die Formel:
1. Mit {=ZählenWenn(Bereich;Bereich)} wird ermittelt, wie oft die jeweilige Zahl vorkommt
2. mit {=1/Zählenwenn(Bereich;Bereich)} wird sozusagen jede Zahl gewichtet, je nach Anzahl der Vorkommen
3. mit {=1/Zählenwenn(Bereich:Bereich)*Bereich)} wird die gewichtete Zahl mit ihrem Wert multipliziert.
Zur Verdeutlichung:
Am Beispiel der Zahl 7 möchte ich das ganze noch mal erklären.
Die Zahl kommt 3 mal vor (=ZÄHLENWENN(Bereich;7)). Das bedeutet, jede dieser Zahlen hat eine Gewichtung von 1/3 =0,3333 (=1/ZÄHLENWENN(Bereich;7)). Das heißt jedes der 3 mal vorkommenden Zahl 7 trägt zur Summe 2,33333 bei (=1/ZÄHLENWENN(Bereich;7)*7).
Das Problem dabei ist, dass man sich die Formel nicht merkt und die Herleitung doch etwas Denkarbeit erfordert. Daher habe ich auch eine VBA Lösung entwickelt.
SummeOhneDuplikate
Public Function SummeOhneDuplikate(myRange As range) As Double Dim AddressRange As String AddressRange = myRange.Address SummeOhneDuplikate = Application.Evaluate("=SUM(IF(ISNUMBER(" & AddressRange & "),1/COUNTIF(" & AddressRange & "," & AddressRange & ")*" & AddressRange & "))")End Function