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

Teilergebnis + Zählenwenn verbinden

Teilergebnis + Zählenwenn verbinden
04.03.2003 11:43:34
Max
Hallo!

Folgendes Problem: Ich habe eine Anwesenheitsliste (monatlich) in der die Urlaube, Krankheitstage und die Anwesenheit, etc. von Mitarbeitern durch ein entsprechendes Kürzel (z.B. u = Urlaub) eingetragen werden.

Oberhalb der Liste zähle ich die Anzahl der Einträge für jeden Tag mit der Funktion Zählenwenn (z. B. =ZÄHLENWENN(I17:I53;"u")zusammen.

Das ganze möchte ich jetzt etwas aufbohren.

Es ist so, dass einige Mitarbeiter über besondere Skills verfügen und dass in Früh und Spätschicht gearbeitet wird. Um die betreffenden Mitarbeiter zu kennzeichnen, habe ich zwei Spalten vorangestellt, in die man ein Kürzel für Früh- Spätschicht oder für einen besonderen Skill eintragen kann. Das ganze wurde mit Autofilter versehen.

Wenn ich nun wissen möchte, wie viele Mitarbeiter aus der Spätschicht gerade Urlaub haben, kann ich nach Spätschicht filtern. Leider zeigt die Funktion Zählenwenn trotzdem die gesamte Anzahl der u-Einträge an, weil diese Funktion den Autofilter nicht berücksichtigt.

Die Funktion Teilergebnis würde das machen, nur kann ich damit nicht die Anzahl der u, k, etc. zusammenzählen.

Jetzt die Frage: Gibt es eine Möglichkeit, diese beiden Funktionen zu verbinden, oder eine eigene Funktion zu schreiben, die das leisten kann?

Das Problem brennt mir ziemlich unter den Nägeln. Wäre gut, wenn jemand Rat wüsste.




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

Betreff
Datum
Anwender
Anzeige
Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 12:00:54
Folker

Das Problem löst dir eine Pivottabelle.
Als Bereich alle Datenangeben und dann im Layout die Skills als Spalte und in Daten ziehen und Summe als Berechnung angeben.

Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 12:01:31
Klaus-Dieter

Hallo Max,

das geht mit der (Zell)funktion Teilergebnis. Du gibst in einer bekliebigen Zelle außerhalb des Filterbereichs Folgende Formel ein: =TEILERGEBNIS(3;B:B). B:B steht dabei für die Spalte deren Inhalte gezählt werden sollen. Da bei dieser Lösung auch die Überschriften mitgezählt werden muß die Formel noch erweitert werden: =TEILERGEBNIS(3;B:B)-1 dann sollte das funktionieren.

Gruß Klaus-Dieter
Klaus-Dieter's Excel und VBA Seite


Anzeige
Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 12:21:02
Max

Hi Klaus-Dieter!

Danke für die Antwort. Leider löst es mein Problem deshalb nicht, weil mir Teilergebnis alle Einträge zählt. Ich möchte aber beispielsweise nur die u´s zählen, oder die k´s, je nach Filterung. Nur bestimmte Buchstaben zählt man normalerweise mit der Funktion Zählenwenn. Aber die berücksichtigt den Autofilter nicht. Also bräuchte ich irgendwie eine Komintion aus beidem: Zählenwenn und Teilergebnis.

Grüße,

Max


Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 12:23:13
Boris

Hi Max,

so geht´s:

=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(1:1000)))*(B1:B1000="u"))

Bezieht sich auf Spalte B und gibt die Anzahl der "u" aus.

Gruß Boris

Anzeige
Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 12:59:49
Max

Hallo Boris,

das ist fantastisch, es funktioniert!!! Vielen Dank, das hilft undendlich. Da fällt mir ein - kann man diese Formel ggf. auch in VBA programmieren?

Grüße,

Max

Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 13:02:45
Boris

Hi Max,

zeichne es dir mit dem Makrorecorder auf.
Sieht dann so aus (Formel eingefügt in D1):
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(SUBTOTAL(3,INDIRECT(""B""&ROW(R:R[999])))*(RC[-2]:R[999]C[-2]=""u""))"

Gruß Boris


Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 13:49:01
Max

Hi Boris!

Again, super Tipp. Ich werde dich in mein Gebet einschließen!

Grüße,

Max

Re: Teilergebnis + Zählenwenn verbinden
04.03.2003 13:52:09
Max

Hi,

vielen Dank für den Tipp. Ich schaus mir mal an!

Grüße,

Max

Anzeige
Re: Teilergebnis + Zählenwenn verbinden
05.03.2003 15:36:37
Max

Hi Boris,

ich bins noch mal. Wäre es möglich, dass du mir die Formal mal ein wenig erklärst, ich kapier sie nicht ganz.

Z. B. Die Funktion Summenprodukt schaut doch normalerweise so aus: Summenprodukt(Matrix1;Matrix2;...). In deiner Formel ist sie aber so: summenprodukt(matrix1 * matrix2)?

Auch diese "B" in Anführungszeichen kapiere ich nicht, denn wenn ich beispielsweise ein "h" einsetze, dann funktioniert es genau so gut. (hab die formel-bezüge allerdings ein wenig geändert).

Wäre schön, wenn du mir noch mal helfen könntest.

Grüße,

Max

Re: Teilergebnis + Zählenwenn verbinden
05.03.2003 21:10:42
Boris

Hi Max,

das ist eigentlich ziemlich kompliziert zu erklären.
SUMMENPRODUKT ist eine der wenigen Formeln, die wie Matrix- (oder auch Array-Formeln) arbeiten.
Die Formel könnte als Array-Formel auch so aussehen (also mit den geschweiften Klammern):

{=SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(1:1000)))*(B1:B1000="u"))}

* bedeutet nix anderes als UND (+ ist übrigens ODER).

Vereinfacht gesagt, werden die sichtbaren Zeilen mit den sichtbaren Zeilen in Spalte B verglichen. Steht in Spalte B ein "u", dann ergibt (B1:B1000="u") für die jeweilige Zeile den Wert WAHR - und WAHR bedeutet - auch vereinfacht gesagt - 1.
Wenn also die mit Teilergebnis ermittelte Zeile (in Kombination mit INDIREKT) sichtbar ist UND in Spalte B ein "u" steht, dann hat man 2 WAHR-Werte, und das ist 1*1, also 1.
Und die Summe der Einsen (daher SUMMENPRODUKT) gibt dir die Anzahl der "u" aus der gefilterten Liste wieder.

Puh, hoffentlich hab ich dich jetzt nicht total verwirrt...

Gruß Boris

Anzeige
Re: Teilergebnis + Zählenwenn verbinden
06.03.2003 12:11:55
Max

Hi Boris,

danke für die Erklärung. Ich glaub ich habs nun einigermaßen kapiert.

Grüße,

Max

112 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige