Datum

Titel

20.04.2024 00:49:19

Excel Sheet in einen Excel Kalender einfügen

20.04.2024 00:08:11

In Ordner nur das Speichern von Dateien erlauben

19.04.2024 21:26:15

mehrfache Texte finden

19.04.2024 19:41:34

Bei VBA Ausführung Anzeige einfrieren

Du findest keine passende Antwort auf Deine Frage?

Stelle Deine Frage in unserem Live-Forum

Archiv - Navigation

1484to1488

Aktuelles Verzeichnis

Verzeichnis Index

Übersicht Verzeichnisse

Vorheriger Thread

Rückwärts Blättern

Nächster Thread

Vorwärts blättern

HERBERS

Excel-Forum (Archiv)

20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Rückwärts Blättern

Aktuelles Verzeichnis

Übersicht Verzeichnisse

Vorwärts blättern

Inhaltsverzeichnis

Zählenwenn mit Aggregat

Forumthread: Zählenwenn mit Aggregat
19 Beiträge zu diesem Forumthread
Anzeige
302 Threads zu ähnlichen Themen
VBA Beispiele zu verwandten Themen

Top 100 Forumthreads

04.04.2016 01:02:12

Sepp

Hallo zusammen,
ich möchte in der Spalte B eine Bestimmte Anzahl ermitteln.
Alle ausgeblendete Zeilen dürfen nicht mitgezählt werden.
Gesucht wird die Anzahl 13:
Mein Versuch:
=Zählenwenn(AGGREGAT(3;5;B:B);"13")
Leider ohne Erfolg :-(
Habt ihr eine Idee?
Danke
Sepp

19
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Zählenwenn mit Aggregat

04.04.2016 02:57:52

Werner
AW: Zählenwenn mit Aggregat

04.04.2016 04:18:55

Sepp
?

04.04.2016 04:22:18

Sepp
AW: ?

04.04.2016 04:48:08

Werner
AW: "Grenzen" der AGGREGAT() - Funktionaltät ...

04.04.2016 08:10:48

...
AW: Zählenwenn mit Hilfsspalte

04.04.2016 09:26:04

Daniel
AW: zählen mit ZÄHLENWENNS() und ZÄHLENWENN() ...

04.04.2016 10:12:58

...
Danke an alle. Summenprodukt funktioniert;-)

04.04.2016 11:58:24

Sepp
AW: Gerne u. Danke für die Rückmeldung. o.w.T.

04.04.2016 12:00:05

Werner
Leider ist das noch etwas komplizierter, ...

04.04.2016 15:26:41

Luc:-?
AW: bei diesen Funktionen bist Du nachsichtig?...

04.04.2016 17:00:33

...
Sowas Blödes, jetzt hab' ich die Datei schon ...

04.04.2016 20:03:02

Luc:-?
AW: nicht ablenken ;-) ... sieh mal ...

05.04.2016 08:46:17

...
Die 'Ablenkung' berücksichtigt deine Bemerkung ...

06.04.2016 00:39:05

Luc:-?
AW: und wie sieht Dein Fazit aus? owT

06.04.2016 08:20:22

...
Fazit

06.04.2016 14:49:27

Luc:-?
AW: ich meinte ein Fazit für die Nutzer ...

06.04.2016 18:58:20

...
NutzerFazit

07.04.2016 02:48:19

Luc:-?
AW: volle Unterstützung zu Deinem Resümee owT

07.04.2016 09:32:10

...

AW: Zählenwenn mit Aggregat

04.04.2016 02:57:52

Werner

Hallo Sepp,
ohne Aggregat
=SUMMENPRODUKT(TEILERGEBNIS(103;INDIREKT("B"&ZEILE(1:100)))*(B1:B100=13))
überwacht wird der Bereich B1:B100
Gruß Werner

AW: Zählenwenn mit Aggregat

04.04.2016 04:18:55

Sepp

Hallo Werner,
da kommt 0 raus, obwohl ich 5 habe.
Ich schick dir Abend mal die Datei
Danke
SEPP

04.04.2016 04:22:18

Sepp

Hallo Werner,
ich weiß woran das liegt.
ich hab eine Formel drin die die 13 ausrechnet: B90=TEIL(C90;8;2)
Kann nur daran liegen oder?
Danke
SEPP

AW: ?

04.04.2016 04:48:08

Werner

Hallo Sepp,
setz mal in der Formel die 13 in Gänsefüsschen "13"
Ich vermute du hast keine echten Zahlen in Spalte B sondern Textzahlen. Deine Teil-Formel gibt nämlich Text aus.
Gruß Werner

AW: "Grenzen" der AGGREGAT() - Funktionaltät ...

04.04.2016 08:10:48

...

Hallo Sepp,
... AGGREGAT() ist für Deine (berechtigte) Anforderung (momentan leider) nicht programmiert. Damit mit der Funktion auch ausgefilterte und oder ausgeblendete Zellenwerte in ihrer Zählfunktionalität (1. Argument: 2 oder 3) in Kombination mit zusätzlicher Bedingungen müsste sie zumindest dafür auch Matrixversionsfähig sein. Dies ist aber nicht der Fall. Warum das so ist? Da musst Du MS fragen ;-)
Demzufolge kann hier nur eine Matrixfunktion(alität)sformel mit einer Funktionskombination auf Basis von TEILERGEBNIS() wie sie mein Namensvetter bereits aufgezeigt hat zum gewünschten Ergebnis führen.
Allerdings, so wie Du schreibst kann in Deiner auszuwertenden Spalte B sowohl "echte" Zahlenwerte 13, als auch Textwerte 13 stehen. Wenn beide gleichzeitig ausgewertet werden soll, dann muss die Formel noch eine kleine Ergänzung (nach stehend fett markiert) erhalten:
=SUMMENPRODUKT(TEILERGEBNIS(103;INDIREKT("B"&ZEILE(B1:B100)))*(B1:B100&""="13"))
Gruß Werner
.. , - ...

AW: Zählenwenn mit Hilfsspalte

04.04.2016 09:26:04

Daniel

Hi
am einfachsten ist, wenn du eine Hilfsspalte einfügst mit der Formel (Formel für Zeile 1 in Hilfsspalte)
=Teilergebnis(3;B1) die Formel musst du dann bis zum Tabellenende runter ziehen.
das Formelergebnis ist 1, wenn die Zeile eingeblendet ist (und in der Spalte B ein Wert steht) und immer 0, wenn die Zeile ausgeblendet ist.
dann kannst du diese Hilfsspalte (hier mal angenommen, es sei die Spalte C) über das ZählenWenns mit in die Berechnung aufnehmen:

=ZählenWenns(B:B;13;C:C;1)

du musst hier zwar eine Hilfsspalte einsetzen, dass hat aber den Vorteil dass du für die weitere Berechnung keine Matrixformeln benötigts und mit ZählenWenns arbeiten kannst, welches nicht nur schneller ist, sondern es auch ohne Performanceeinbussen erlaubt, ganze Spalten als Zellbezug zu verwenden.
dass mit Anführungszeichen um die 13 wurde ja schon angesprochen.
mit "13" suchst du nach dem TEXT aus den Ziffern "1" und "3", mit 13 suchst du nach der Zahl 13 und das ist für die ZählenWenn(s)-Funktion nicht das gleiche.
Gruß Daniel

AW: zählen mit ZÄHLENWENNS() und ZÄHLENWENN() ...

04.04.2016 10:12:58

...

Hallo Daniel,
... in Deiner Aussage: mit "13" suchst du nach dem TEXT aus den Ziffern "1" und "3", mit 13 suchst du nach der Zahl 13 und das ist für die ZählenWenn(s)-Funktion nicht das gleiche. ist unzutreffend.
Sowohl ZÄHLENWENN() als auch ZÄHLENWENNS() können eben (ohne "Klimmzüge") nicht unterscheiden zwischen einem echten Zahlenwert und einer Textzeichenkette die multipliziert mit 1 den gleichen Zahlenwert ergibt, wie das z.B: SUMMENPRODUKT() kann.
Gruß Werner
.. , - ...

Danke an alle. Summenprodukt funktioniert;-)

04.04.2016 11:58:24

Sepp

AW: Gerne u. Danke für die Rückmeldung. o.w.T.

04.04.2016 12:00:05

Werner

Leider ist das noch etwas komplizierter, ...

04.04.2016 15:26:41

Luc:-?

…Werner,
weil ja nicht nur der VglsOperator = (auch bei Fehlen), sondern auch , <, > und ihre Kombinationen mit = vorkommen können. Bei letzteren wird die Zahl nach dem Operator offensichtlich immer in eine echte umgewandelt und alle Texte (auch TextZahlen) bleiben unberücksichtigt. Nur bei = (oder seinem Fehlen) ist das etwas anders, weil die Fkt hier wohl standardmäßig dem TextVgl (nicht dem Byte-Vgl!) den Vorrang gibt. X ist also =x, nur LeerText<LeerZeichen und der Unterschied bei Zahl=TextZahl irrelevant wie in dieser Abbildung am Bsp von ZÄHLENWENN gezeigt wird:
Userbild

Ob das nun ein Bug ist oder eine PrioritätsEntscheidung, vermag ich nicht zu sagen, nur soviel, dass der Pgmmierer eine Entscheidung treffen muss, ob Zahlen im Kriterium, die natürlich primär als Text vorliegen, generell in echte Zahlen umzuwandeln sind oder aber nicht. Insofern dürfte es sich hierbei um einen Kompromiss handeln, wobei abhängig vom Typ des Bezugswerts und dem KriteriumsOperator entschieden wird, ob das Kriterium in eine echte Zahl gewandelt wird, falls das möglich ist.
Gruß, Luc :-?
Besser informiert mit …

AW: bei diesen Funktionen bist Du nachsichtig?...

04.04.2016 17:00:33

...

Hallo Luc,
... mit den Funktionsweisen der Funktionsgruppe AGGREGAT() bist Du härter mit den Programmieren zu Gericht gegangen ;-)
Eine Ergebnis-Abhängigkeit vom Typ des Bezugswerts und dem KriteriumsOperatoras als Kompromiss zu bezeichnen, darf man doch als großzügige Nachsicht werten?!? Zumindest hätten mE die Hilfeschreiber auf die spez. Problematik hinweisen sollen/müssen !?! Vor allem unter dem Gesichtspunkt, dass ja nicht nur die ZÄHLEN... Funktionen sondern scheinbar alle ...WENN(s)-Funktionen derartig betroffen sind und das Funktionen sind, die gerade auch Einsteiger gern und viel nutzen und die "kleinen Details", die wir hier diskutieren wohl kaum gleich überblicken und werten können.
In Deiner Aufstellung fehlt übrigens u.a. auch noch die Kombinationen mit der Funktion &. Da gibt es nämlich eine weitere "Überraschung". Ergänze bezogen auf Deine Tabelle z.B. u.a. folgende Formeln:


=ZÄHLENWENN(A1:A13;"

Wie begründest Du / man denn nun diese Ergebnisse?
Gruß Werner
.. , - ...

Sowas Blödes, jetzt hab' ich die Datei schon ...

04.04.2016 20:03:02

Luc:-?

…verworfen, Werner;
&¹ hatte ich nur mit = (bzw ohne Operator) probiert, was genauso lief (auch mit Apostroph). Auch mit deinen Fmln ist es nicht anders, das & hat nichts zu sagen², die Operation wird vor der Übergabe an die Fkt ausgeführt, nicht in der Fkt. Das ist eigentlich immer so. Will man an der Stelle als Pgmmierer eingreifen, muss man den FmlText sichten, was die Sache verkompliziert.
Wichtig ist hier aber das Apostroph (nicht wie ein TextPräfix behandelt!) in der 3.Fml. Dadurch wird nur mit Texten vgln, von denen 3 eindeutig < sind als '13 (in A8 steht bei mir nur ein TextPräfix, was LeerText bewirkt). Das '13 auch als < gezählt wird, muss mit dem TextVgl zu tun haben bzw dem ' vor 13 geschuldet sein, denn das ist bei =A2<"'13" auch so (das TextPräfix in A2 wird eher nicht berücksichtigt).
Was meine angebliche Nachsicht bei dieser älteren (bis ganz „neuen“) FkttGruppe betrifft, so bezieht die sich nur auf das Kriterium, denn das ist relativ kompliziert zu handhaben³, nicht aber auf die generelle Konstruktion dieser Fktt, die von der ursprünglichen Xl-Praxis deutlich abweicht. Als Arg1 nur ZellBereiche zuzulassen, um ein paar Befehle einzusparen, ist einfach nur engstirnig und PgmmiererBequemlichkeit. TEILERGEBNIS und AGGREGAT haben ja wenigstens noch einen nachvollziehbaren Grund für ihre Beschränkung auf ZellBereiche, der im Falle von AGGREGAT allerdings durch die abweichende Handhabung⁴ von Arg1≥14 konterkariert wird.
¹ Keine Funktion, sondern ein Operator wie +, -, *, /, ^.
² Gilt natürlich nicht für Adressen als/im Text, die wdn nicht evaluiert, deshalb dann 5 (<"A2") als Ergebnis!
³ Ich habe auch schon UDFs mit analogen Konstrukten geschrieben, weshalb ich das „nachfühlen“ kann.
⁴ Da hat sich wohl einer von wohl mehreren Autoren nicht an die Vorgaben gehalten! Was das dann mit BedingtFormatRegeln zu tun haben könnte/sollte, ist mir schleierhaft.
Luc :-?

AW: nicht ablenken ;-) ... sieh mal ...

05.04.2016 08:46:17

...

Hallo Luc,
... hier im thread geht es mir um die ...WENNS/s()-Funktionen, am Beispiel von ZÄHLENWENN():

	A	B	C	D	E
1	13	WAHR
2	13	FALSCH	2	2	4
3	14	WAHR
4		FALSCH
5	12	WAHR
6	12	FALSCH
7		FALSCH
8		FALSCH
9	0	WAHR
10	0	FALSCH
11	x	FALSCH
12	X	FALSCH
13	Y	FALSCH

Formeln der Tabelle

Zelle	Formel
B1	=ISTZAHL(A1)
B2	=ISTZAHL(A2)
C2	=ZÄHLENWENN($A1:$A13;"<"&"13")
D2	=ZÄHLENWENN($A1:$A13;"<"&A2)
E2	=ZÄHLENWENN($A1:$A13;"<"&"'13")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Die 'Ablenkung' berücksichtigt deine Bemerkung ...

06.04.2016 00:39:05

Luc:-?

…bzgl meiner „Nachsicht“, Werner;
ansonsten habe ich den Tatbestand geschildert. Hinzuzusetzen wäre ggf noch, dass der einfache Vgl nicht so fktioniert wie in diesen Fktt, die offensichtlich bestimmte Werte vom Vgl ausschließen, je nachdem welchen DatenTyp die haben, wie nachflgd zu sehen ist:

	A	B	C	D	E	F	G	H
1	13	FALSCH	2	2	WAHR	WAHR	FALSCH	FALSCH
2	13	WAHR	2	5	WAHR	FALSCH	FALSCH	FALSCH
3	14	FALSCH	4	4	WAHR	WAHR	FALSCH	FALSCH
4	14	WAHR	4	4	FALSCH	FALSCH	FALSCH	WAHR
5	12	FALSCH	Formeln		WAHR	WAHR	WAHR	FALSCH
6	12	WAHR	C1:=ZÄHLENWENN(A1:A13;"<"&"13")		WAHR	WAHR	FALSCH	FALSCH
7		FALSCH	C2:=ZÄHLENWENN(A1:A13;"<"&A2)		WAHR	WAHR	WAHR	FALSCH
8		WAHR	C3:=ZÄHLENWENN(A1:A13;"<"&"'13")		WAHR	WAHR	FALSCH	FALSCH
9	0	FALSCH	C4:=ZÄHLENWENN(A1:A13;">"&"'13")		WAHR	WAHR	WAHR	FALSCH
10	0	WAHR	D1:=ZÄHLENWENN(A1:A13;"<13")		WAHR	WAHR	FALSCH	FALSCH
11	x	WAHR	D2:=ZÄHLENWENN(A1:A13;"<A2")		FALSCH	FALSCH	FALSCH	WAHR
12	X	WAHR	D3:=ZÄHLENWENN(A1:A13;"<'13")		FALSCH	FALSCH	FALSCH	WAHR
13	Y	WAHR	D4:=ZÄHLENWENN(A1:A13;">'13")		FALSCH	FALSCH	FALSCH	WAHR
14	B1[:B13]:=ISTTEXT(A1)		E1[:E13]:=A1<"'13"		F1[:F13]:=A1<"13"	G1[:G13]:=A1<13		H1[:H13]:=A1>"13"

Beim DirektVgl in Spalte F ergibt der Vgl >"'13" genau dasselbe, weshalb ich darauf verzichtet habe. Die Werte, die mE verwendet wdn, sind in gleicher Farbe markiert wie das Ergebnis und die zugehörige Fml-Darstellung.
Morrn, Luc :-?

AW: und wie sieht Dein Fazit aus? owT

06.04.2016 08:20:22

...

Gruß Werner
.. , - ...

Fazit

06.04.2016 14:49:27

Luc:-?

Naja, Werner,
ist natürlich etwas spekulativ:
Ich befürchte, dass sich der Pgmmierer beim Versuch, das Dilemma zwischen Kriteriumsangabe m/o Operator (quasi als TextZahl) und Text bzw Zahl im VglsBereich zu lösen, etwas in den vielen Möglichkeiten verheddert haben könnte, so dass seine Lösung im speziellen Fall auf Unverständnis stoßen kann. Wirklich zufriedenstellend kann man das wohl nur lösen, wenn man die KriteriumsAngabe aus dem FmlText isoliert und dann danach vorgeht, was die für eine Form hat. Dann könnte auch ein "<"&EchtZahl besser von "<Zahl" unterschieden wdn, was hier offensichtlich nicht erfolgt. Alternativ könnte natürlich auch das jeweils eingestellte TextPräfix, auch in einer solchen Angabe, zur TextErkennung genutzt und nicht nur als vorangestelltes (Text-)Zeichen berücksichtigt wdn, was hier der Fall zu sein scheint. Dann könnte man auch zwischen "=Zahl" (bzw "='Zahl") und nur EchtZahl (bzw "="&EchtZahl) unterscheiden.
Den Aufwand, der ggf auch Nachteile haben kann, hat der Pgmmierer wohl zu vermeiden gesucht… ;-]
Luc :-?

AW: ich meinte ein Fazit für die Nutzer ...

06.04.2016 18:58:20

...

Hallo Luc,
... siehe dazu auch noch mal meine Beispiellisting von gestern Morgen https://www.herber.de/forum/archiv/1484to1488/t1485722.htm und versuche es einen Nutzer dieser Funktionalität plausibel zu erklären!?!
Ich glaube kaum, dass ein solcher das dann wirklich nachvollziehen kann. Ist diese Problematik schon mal wo anders diskutiert wurden?
Gruß Werner
.. , - ...

NutzerFazit

07.04.2016 02:48:19

Luc:-?

Tja, Werner,
schwierig, aber im Prinzip so wie von dir erklärt.
1. Die Fktt gehen primär von ZahlenVgln aus, wobei im Falle von = auch TextZahlen im ZellBereich einbezogen wdn, was der Fehler im System zu sein scheint. Das muss man wohl nur wissen.
2. Eine besondere Notationsweise des Kriteriums für TextVgle bringt idR nichts, sondern führt eher zu falschen Ergebnissen, wie die Ergebnisse in C3:D3 belegen. Was hierbei wohl tatsächlich gezählt wird, sind die WAHR-EinzelErgebnisse von …
=WENN(B1;A1<"'13") ⇐{=SUMME(--WENN(B1:B13;A1:A13<"'13"))}
Dabei ist wg TextVgl 13<'13, da TextZiffern offensichtlich einen kleineren SortierWert als AlfaZeichen haben.
3. Resümee: Man sollte also stets auf TypenIntegrität des VglsBereichs achten, alfanumerische Texte nur mit alfanumerischen Texten (nicht TextZahlen!) und echte Zahlen mit Zahlen vgln, damit man keine unangenehmen Überraschungen erlebt. Ein reiner Vgl von TextZahlen mit einer Zahl ist nur per = möglich, da man den VglsZellBereich bei diesen Fktt ja nicht manipulieren kann, was ich schon des Öfteren kritisiert hatte. Somit muss man in solchen Fällen wiedermal HilfsZellen, die guten alten Xl-StammFktt oder das Schlupfloch in AGGREGAT nutzen. ;-]
Zur Diskussion: Nicht das ich wüsste, aber das muss ja nichts heißen, das INet ist weit in Raum & Zeit und vielsprachig…
Morrn, Luc :-?

AW: volle Unterstützung zu Deinem Resümee owT

07.04.2016 09:32:10

...

Gruß Werner
.. , - ...