Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zählen eindeutiger Werte mit Bedingung

Zählen eindeutiger Werte mit Bedingung
05.11.2021 13:51:56
Nelinax
Hi Zusammen,
ich war vorhin schonmal hier und hatte nach einer Summenbildung gefragt. Das hat auch prima funktioniert danke nochmal an Michael & steve1da
Jetzt würde ich allerdings gerne noch meine Anzahl der Zahlen in Spalte C zählen und die doppelten Werte dabei nur einmal berücksichtigen.
- Anzahl der Zahlen die mit 4 oder 7 beginnen
- Anzahl der Zahlen die mit 9 beginnen 8
Ich bin im Forum schon über die erweiterte Filter Funktion gestoßen allerdings lade ich oft neue Werte in meine Tabelle und würde das ungern händisch lösen..
Gibt es hierfür eine relativ unkomplizierte Lösung ?
Liebe Grüße & schönes Wochenende
Nelinax

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zählen eindeutiger Werte mit Bedingung
05.11.2021 14:16:21
Michael
Danke für die Blumen ;-)
Mit zwei Hilfsspalten würde ich das so machen, wie in der Datei hier:
https://www.herber.de/bbs/user/148964.xlsx
Viele Grüße
Michael
AW: Zählen eindeutiger Werte mit Bedingung
05.11.2021 16:42:11
Lenzmacher
Ich versuche immer, solche speziellen Sachen bei großen Listen als Datenbank zu behandeln und diese Abfragen mit SQL durchzuführen.
hört sich aber komplizierter an als es ist. hier mal der für deine Tabelle genutzte Code

Sub SQL()
Dim WB As Workbook
Set WB = Application.ThisWorkbook
Dim WS As Worksheet
Set WS = WB.Worksheets("Tabelle1")
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set Conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Dim ConnStr As String
ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WB.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Conn.Open ConnStr
With WS
ZAHL1 = .Cells(2, 5)
ZAHL2 = .Cells(3, 5)
ZAHL3 = .Cells(4, 5)
ZAHL4 = .Cells(5, 5)
sSql00 = "Select distinct count(Zahlen) as ANZAHL  "
sSql01 = ", (Select count(Zahlen) FROM [Tabelle1$] where left(Zahlen,1) = " & ZAHL1 & ") as ZAHLEN1"
sSql02 = ", (Select count(Zahlen) FROM [Tabelle1$] where left(Zahlen,1) = " & ZAHL2 & ") as ZAHLEN2"
sSql03 = ", (Select count(Zahlen) FROM [Tabelle1$] where left(Zahlen,1) = " & ZAHL3 & ") as ZAHLEN3"
sSql04 = ", (Select count(Zahlen) FROM [Tabelle1$] where left(Zahlen,1) = " & ZAHL4 & ") as ZAHLEN4"
sSql05 = " from [Tabelle1$] "
sSql = sSql00 & sSql01 & sSql02 & sSql03 & sSql04 & sSql05
rs.Open sSql, Conn
If rs.EOF = False Then
.Cells(2, 6) = rs.Fields("ZAHLEN1")
.Cells(3, 6) = rs.Fields("ZAHLEN2")
.Cells(4, 6) = rs.Fields("ZAHLEN3")
.Cells(5, 6) = rs.Fields("ZAHLEN4")
.Cells(6, 6) = rs.Fields("ANZAHL")
End If
rs.Close
sSql = " Select distinct Zahlen from [Tabelle1$]"
rs.Open sSql, Conn
ANZAHL = rs.GetRows()
.Cells(7, 6) = UBound(ANZAHL, 2)
rs.Close
End With
Conn.Close
End Sub
im ersten Teil bilde ich die Verbindung zur Excel-Tabelle als "Datenbank"
im 2. Teil suche ich nach den Zahlen, die von dir in E2-E5 vorgegeben wurden. diese sind änderbar....
im letzten Teil zähle ich die eindeutigen Datensätze.
Voraussetzung ist, dass die Datei gespeichert ist.
Eine Summenbildung wäre auch machbar, wenn die Verbindung einmal steht, braucht man auch keine Hilfsspalten mehr.
FG
Lenzi
Anzeige
Wenn schon über VBA, dann als UDF
05.11.2021 19:24:49
Yal
UDF, User Defined Formula.
_ Visual Basic öffnen (Alt+F11)
_ Modul öffnen (Alt+e, m)
_ folgende Code copy-pasten

Public Function NachMusterZählen(Target As Range, Optional Muster = "*", Optional Distinct = True) As Long
Dim Dic, Z, E
Set Dic = CreateObject("Scripting.Dictionary")
For Each Z In Target.Cells
If Z.Value Like Muster Then
E = 0
E = Dic(Z.Value)
Dic(Z.Value) = E + 1
End If
Next
If Distinct Then
NachMusterZählen = Dic.Count
Else
For Each Z In Dic.Items()
NachMusterZählen = NachMusterZählen + Z
Next
End If
End Function
Dann im Blatt
= NachMusterZählen("A1:A100"; "9*") --> mehrfach kommende Element nur einmal gezählt
oder
= NachMusterZählen("A1:A100"; "9*"; FALSCH) --> jede mehrfach kommende Element zählt
Achtung: Case sensitive. Wenn nicht erwunscht dann

Muster = LCase(Muster)
If LCase(Z.Value) Like Muster Then
VG
Yal
Anzeige
AW: hier würde auch eine einzige Formel reichen ..
06.11.2021 07:52:56
neopa
Hallo Nelinax,
... folgende Formel nach unten kopieren:
Arbeitsblatt mit dem Namen 'MFF_211105_1'
 CDE
1ZahlenZahl beginnt mitAnzahl ohne Doppelte
21476
317208
4180
524497
6244  
7244  
8261  
9261  
10261  
11277  
12277  
13277  
14300  
15300  
16300  
17301  

ZelleFormel
E2=SUMMENPRODUKT((VERGLEICH(C$2:C$9999&"";C$2:C$9999&"";0)=ZEILE(C$2:C$9999)-1)*(LINKS(C$2:C$9999;1)=D2&""))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: noch eine einfache(re) Lösungsmöglichkeit ...
06.11.2021 08:13:56
neopa
Hallo Nelinax,
... ganz ohne Formeln und ohne VBA mit nur ca. ein Dutzend Mausklicks.
Die Funktionen unter Abrufen und Transformieren unter dem Menü Daten ermöglichen das so:
- eine Zahlenzelle aktivieren
- Fkt.: "aus Tabelle" unter Menü Daten aktivieren
- "ok" bestätigen
- dann im Power Query Editor Fkt. Duplikate entfernen aktivieren
- Fkt. "Spalte nach Position teilen" und da "so weit links wie möglich" und 1 "Anzahl"
- Datentyp, ändern auf ganze Zahl
- überflüssige Spalte entfernen
- Fkt. "Gruppieren nach" aktivieren mit Vorgang "Zeilen nach"
- Fkt. "Sortieren" für Zahlenspalte aktivieren
- Fkt. Datei Speichern in Tabellenblatt
Bei Bedarf in dessen Ergebnistabelle nur die gewünschten filtern.
Gruß Werner
.. , - ...
Anzeige
AW: hier würde auch eine einzige Formel reichen ..
08.11.2021 08:48:43
Michael
Hallo Werner,
sowas hab ich schon geahnt ;-)
Aber warum funktioniert LINKS(C$2:C$9999;1) bei Dir und bei mir nicht:
=ZÄHLENWENNS(links(C$2:C$9999;1);E2)
('tschuldige Nelinax, ist natürlich Dein Thread, aber vielleicht drängt sich Dir eh eine ähnlich Frage auf...)
Danke
Michael
AW: die ZÄHLEN- bzw. SUMMEWENNs()-Funktionen ...
08.11.2021 09:39:49
neopa
Hallo Michael,
... erlauben für Kriterienbereiche keine derartige Teilformeln sondern lediglich "normale" Zellbereichsfestlegungen bzw.auch Formeldefinitionen die mittels ausschließlich INDEX() oder INDIREKT() den auszuwertenden Bereich als Matrix festlegen. Deshalb dafür Auswertung mit SUMMENPRODUKT() was ich nutze oder alternativ als klassische Matrixformel mit {SUMME(WENN(....))}.
Gruß Werner
.. , - ...
Anzeige
AW: Besten Dank!
08.11.2021 09:55:41
Michael
Bislang was nur so'n Gefühl...nun kann ich mir weitere Experimente sparen.
AW: gerne owT
08.11.2021 13:00:58
neopa
Gruß Werner
.. , - ...
AW: Zählen eindeutiger Werte mit Bedingung
08.11.2021 08:09:09
Nelinax
Guten Morgen euch allen,
ich hab mir am Wochenende etwas Zeit genommen und die Lösungen auf meine Frage angeschaut.
Ich bin immer wieder überrascht wie schnell, freundlich und hilfreich reagiert wird. Da sinkt die Verzweiflung im Studium recht schnell :)
Danke Michael, Lenzi, Yal und Werner!
Liebe Grüße :)
AW: bitteschön owT
08.11.2021 09:40:14
neopa
Gruß Werner
.. , - ...

336 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige