AW: Anzahl unterschiedlicher Zeilen
07.01.2008 14:16:58
fcs
Hallo Helena,
Die Lösung ohne VBA erfordert meines Wissens eine Hilfsspalte. In dieser Spalte wird berechnet, wie oft die Kombination der Werte in den 3 Spalten vorkommt. Geht super mit Funktion SUMMENPRODUKT und WAHRHEITS-Bedingungen für die 3 Spalten.
Formel in Spalte D entsprechend für alle Zeilen nach unten kopieren
=SUMMENPRODUKT((A2=$A$2:$A$1000)*(B2=$B$2:$B$1000)*(C2=$C$2:$C$1000))
Für die Berechnung der Anzahl der nichtdoppelten Einträge, die die Kriterien erfüllen kann man dann nochmals die Funktion SUMMENPRODUKT benutzen unter Verwendung der Hilfsspalte.
=SUMMENPRODUKT(($A$2:$A$1000=F3)*($C$2:$C$1000=G3)*($D$2:$D$1000=1))
Die Kriterien für Spalte A bzw. C stehen in Zellen F3 bzw. G3
Nachfolgende benutzerdefierte Funktion ermittelt ohne Hilfspalte die Anzahl. Die Hilfsspalte wird dabei in der Funktion als Feldvariable erzeugt.
In der Beispieldatei hab ich beide Lösungen eingebaut.
https://www.herber.de/bbs/user/48851.xls
Gruß
Franz
Public Function AnzahlNichtDoppelt(Spalte1 As Range, Spalte2 As Range, Spalte3 As Range, _
Kriterium1 As String, Kriterium3 As String, Optional GrossKlein As Boolean = False) As Long
' Ermittelt die Anzahl nicht doppelter Einträge, die die Kriterien erfüllen
'Spalte1 = Zellbereich mit dem 1. Kriterium
'Spalte2 = Zellbereich mit den Werten
'Spalte3 = Zellbereich mit dem 2. Kriterium
'Kriterium1 und Kriterium2 sind die Kriterien für Spalte1 und Spalte3, _
'GrossKlein = Option ob Groß-Kleinschreibung beachtet werden soll, Standard = False (Nein)
'Formelbeispiele:
' =AnzahlNichtDoppelt(A:A;B:B;C:C;"x";"S")
' =AnzahlNichtDoppelt(A2:A1000;B2:B1000;C2:C1000;"x";"S";WAHR)
'Prüfung ob die 3 Bereiche die gleiche Zeilenzahl haben
If Not (Spalte1.Rows.Count = Spalte2.Rows.Count And Spalte2.Rows.Count = Spalte3.Rows.Count) _
Then
MsgBox "Die 3 Spaltenbereiche müssen die gleiche Zeilenzahl haben!"
Exit Function
End If
Dim Anzahl As Long, Zeile As Long, Zeile2 As Long, wks As Worksheet, ZeileL As Long
Dim arrZusammen() As String, arrGezaehlt() As Boolean, strZusammen As String
Set wks = Spalte1.Parent
'Letzte Zeile mit Daten
With wks
ZeileL = .Cells(.Rows.Count, Spalte1.Column).End(xlUp).Row
End With
ReDim arrGezaehlt(1 To ZeileL)
ReDim arrZusammen(1 To ZeileL)
If GrossKlein = True Then
For Zeile = 1 To ZeileL
arrZusammen(Zeile) = Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1)
Next
For Zeile = 1 To ZeileL
If arrGezaehlt(Zeile) = False Then
arrGezaehlt(Zeile) = True
If Kriterium1 = Spalte1(Zeile, 1) And Kriterium3 = Spalte3(Zeile, 1) Then
strZusammen = Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1)
Anzahl = 1
For Zeile2 = Zeile + 1 To ZeileL
If strZusammen = Spalte1(Zeile2, 1) & Spalte2(Zeile2, 1) & Spalte3(Zeile2, 1) Then
arrGezaehlt(Zeile2) = True
Anzahl = Anzahl + 1
End If
Next
If Anzahl = 1 Then
AnzahlNichtDoppelt = AnzahlNichtDoppelt + 1
End If
End If
End If
Next
Else
For Zeile = 1 To ZeileL
arrZusammen(Zeile) = UCase(Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1))
Next
For Zeile = 1 To ZeileL
If arrGezaehlt(Zeile) = False Then
arrGezaehlt(Zeile) = True
If UCase(Kriterium1) = UCase(Spalte1(Zeile, 1)) _
And UCase(Kriterium3) = UCase(Spalte3(Zeile, 1)) Then
strZusammen = UCase(Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1))
Anzahl = 1
For Zeile2 = Zeile + 1 To ZeileL
If strZusammen = UCase(Spalte1(Zeile2, 1) & Spalte2(Zeile2, 1) & Spalte3(Zeile2, 1)) _
Then
arrGezaehlt(Zeile2) = True
Anzahl = Anzahl + 1
End If
Next
If Anzahl = 1 Then
AnzahlNichtDoppelt = AnzahlNichtDoppelt + 1
End If
End If
End If
Next
End If
End Function