630 Aufrufe
Gefragt in Windows 7 von milli24 Einsteiger_in (86 Punkte)
Habe noch ein ähnliches Phänomen bei etwas umfangreicherer Formel Summenprodukt. Es wird immer einer zu wenig gezählt. Hier mal die Formel:

 Worksheets("TabStat").Range("b2").FormulaLocal = "=SUMMENPRODUKT(((ArbTab!$C$2:$C$" & letztezeile & "=""Herr"")+(ArbTab!$C$2:$C$" & letztezeile & _
  "=""Frau""))/ZÄHLENWENN(ArbTab!$B$2:$B$" & letztezeile & ";ArbTab!$B$2:$B$" & letztezeile & "))"

Hier bin ich vollends mit meinem Latein am Ende, und bräuchte Unterstützung. Danke im Voraus, und Gruß milli

22 Antworten

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Milli,

nur mit deinem VBA-Code-Schnippsel und ohne die Tabelle zu kennen wird es schwer dir zu helfen. Stimmt der Bereich? Was willst du denn mit der Division durch ZÄHLENWENN erreichen? Wenn du nur die Datensätze mit Herr und Frau zählen willst, kannst du auch einfach zwei entsprechende Zählenwenn-Formel addieren.

Lass doch mal zu Testzwecken die Datensätze mit Herr und Frau zählen. Stimmen die Ergebnisse mit deiner Summewenn-Formel, dann gibt es wohl einen Fehler ein einem deiner Datensätze (Leerzeichen, nichtdruckbare Zeichen etc.).

Gruß

M.O.
0 Punkte
Beantwortet von milli24 Einsteiger_in (86 Punkte)
Bearbeitet von milli24
Danke, es werden die Datensätze gezählt wobei es vorkommen kann das für eine Person in zwei Zeilen unterschiedliche Daten gespeichert sind. Also doppelte als 1 zählen. Ich muss noch anmerken das in einer kleine Testdatei das nicht vorkommt, bei ca. 250 Zeilen tritt das Problem auf.

Bei einzelnen Datensätze "Frau" oder M.... zählen ist Formel korrekt. Schreibweise usw. habe ich geprüft. Zeige mal die einfachere Formel für die Summe wo aber auch um 1 zu wenig gezählt wird. Fehler in Datensätzen, wie würde man "nichtdruckbare Zeichen" heraus filtern können.

Gruß Milli

Worksheets("TabStat").Range("b2") = Worksheets("TabStat").Range("b3") + Worksheets("TabStat").Range("b4")
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Milli,

schau dir mal das hier: Anzahl unterschiedlicher Einträge in mehreren Spalten

Natürlich könntest du das Zählen auch per VBA machen.

Nichtdruckbare Zeichen, Leerzeichen am Anfang etc. kannst mit GLÄTTEN entfernen.

Gruß

M.O.

0 Punkte
Beantwortet von milli24 Einsteiger_in (86 Punkte)
Bearbeitet von milli24
Hab die Tabelle mal in DROPBOX hochgeladen. Könnte man sich dort mal ansehen. Ich glaube aber man muss Kontaktdaten , Berechtigung austauschen. Gruß
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Milli,

du kannst die Beispieldatei auch hier im Forum hochladen: Eine kleine Anleitung findest du hier.

Gruß

M.O.

0 Punkte
Beantwortet von milli24 Einsteiger_in (86 Punkte)

Danke für den Tipp, ich hoffe das Hochladen hat geklappt. Wie bereit geschildert, die Summe passt nicht, wenn die Anzahl Zeilen sehr groß 250 ist. Einträge zum xten Mal kontrolliert. Warum ein Muss "+1" um den richtigen wert zu erhalten?

Gruß Milli

http://supportnet.de/forum/?qa=blob&qa_blobid=3809507223692594411

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Milli,

da in deiner Beispieldatei ja nur 20 Datensätze vorkommen konnte ich den von dir genannten Fehler nicht feststellen.

Ich habe aber mal die Summenprodukt-Formel aus meinem Link angepasst und in deine Tabelle gesetzt:

=SUMMENPRODUKT((VERGLEICH(ArbTab!D1:D300&"x"&ArbTab!E1:E300;ArbTab!D1:D300&"x"&ArbTab!E1:E300;0)=ZEILE(1:300))*(ArbTab!D1:D300<>"")*(ArbTab!E1:E300<>""))-1

(die -1 muss hier dazu, da die Überschrift mitgezählt wird).

Dabei ist aber aufgefallen, dass hierbei ein Datensatz zuviel angezeigt wird. Im Gegensatz zu deiner Formel werden hier die zusammengesetzten Vor- und Nachnamen verglichen und ohne doppelte gezählt.

Bei der Fehlersuche ist mir aufgefallen, dass bei dem Nachnamen lfd. Nr. 3 (Zeile 5) nach dem Namen noch einige Leerzeichen vorhanden sind.

Das folgende Makro glättet die Daten in den Spalten D und E:

Sub glaetten()

Dim lngZeile As Long

For lngZeile = 2 To 300
 Cells(lngZeile, 4) = Trim(Cells(lngZeile, 4).Value)
 Cells(lngZeile, 5) = Trim(Cells(lngZeile, 5).Value)
Next lngZeile

End Sub

Du kannst es ja mal über deine große Tabelle laufen lassen und ggf. auch mal die oben gepostete Summenproduktformel testen.

Hier noch die angepassten Summenproduktformeln für die Zählung von Herr und Frau:

=SUMMENPRODUKT((VERGLEICH(ArbTab!D1:D300&"x"&ArbTab!E1:E300;ArbTab!D1:D300&"x"&ArbTab!E1:E300;0)=ZEILE(1:300))*(ArbTab!D1:D300<>"")*(ArbTab!E1:E300<>"")*(ArbTab!C1:C300="Herr"))

=SUMMENPRODUKT((VERGLEICH(ArbTab!D1:D300&"x"&ArbTab!E1:E300;ArbTab!D1:D300&"x"&ArbTab!E1:E300;0)=ZEILE(1:300))*(ArbTab!D1:D300<>"")*(ArbTab!E1:E300<>"")*(ArbTab!C1:C300="Frau"))

Gruß

M.O.

0 Punkte
Beantwortet von milli24 Einsteiger_in (86 Punkte)
Bearbeitet von milli24
Hallo M.O. Danke für die Hilfsbereitschaft. Habe die Formeln mal eingebaut. Bei "Herr" u. "Frau" ist das Ergebnis korrekt. Meine Formel jetzt:
Worksheets("TabStat").Range("b3").FormulaLocal = "=SUMMENPRODUKT((VERGLEICH(ArbTab!D1:D300&""x""&ArbTab!E1:E300;ArbTab!D1:D300&""x""&ArbTab!E1:E300;0)=ZEILE(1:300))*(ArbTab!D1:D300<>"")*(ArbTab!E1:E300<>"")*(ArbTab!C1:C300=""Herr""))"
Bei der Summe "Beider" rechnet er Menge 1 zu viel. Meine Formel:
Worksheets("TabStat").Range("b2").FormulaLocal = "=SUMMENPRODUKT((VERGLEICH(ArbTab!D1:D300&""x""&ArbTab!E1:E300;ArbTab!D1:D300&""x""&ArbTab!E1:E300;0)=ZEILE(1:300))*(ArbTab!D1:D300<>"")*(ArbTab!E1:E300<>""))" -1"

Muss es vielleicht Minus 2 sein, denn das würde hinkommen??   Und die Methode lässt beliebig viele Zeiel befüllen?? Gruß Milli
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Milli,

die Formel lässt sich beliebig erweitern. Du musst nur darauf achten, dass die Bereiche in allen Formelteilen gleich sind.

Wenn die Formeln für Herr und Frau korrekt sind, dann muss bei der Formel für alle noch irgendwo eine Zeile sein, in der nicht Herr oder Frau steht, die aber bei der Formel für alle noch mitgerechnet wird. Eventuell gibt eine 2-zeilige Überschrift (dann musst du natürlich 2 abziehen) oder es gibt noch eine andere Anrede als Herr oder Frau oder es steht noch irgendetwas in den Spalten D und E unter der eigentlichen Tabelle?

Ich würde versuchen den Grund fü die Abweichung zu finden, denn es könnte bei weiteren Auswertungen wieder zu Abweichungen und Fehlern kommen.

Gruß

M.O.
0 Punkte
Beantwortet von milli24 Einsteiger_in (86 Punkte)
Guten Tag in die Runde. Habe meine Hausaufgaben gemacht, und händisch den Fehler (Text) beseitigen können. In dem Zusammenhang hätte ich aber noch eine Frage. Die Daten in Spalte "C" (Herr und Frau) sind ja immer gleich. Kann man in der Userform bei der Eingabe neuer Datensätze direkt prüfen lassen ob die Schreibweise korrekt ist. Dies würde Fehler bei der Eingabe vermeiden, da es ja nur 2 Möglichkeiten gibt? Gruß Milli
...