Office: Funktion ZÄHLENWENN: enthält eine Zelle einen Text einer Zelle aus einem Arrays ?

Hallo,

ich versuche mal, mein Problem zu schildern, indem ich das Obst-Beispiel der Excel-Hilfe verwende.

Ich habe in mehreren Zellen den ZÄHLENWENN-Bereich, z.B.:
C1 = "Bananen"
C2 = "Äpfel"
C3 = "Pfirsiche"

in der Spalte A habe ich die Kriterien:
A10 = "Äpfel"
A11 = "Pfirsiche gelbfleischig"
A12 = "Birnen"
A13 ...

in der Spalte B den Preis pro Frucht.

Nun soll in der Spalte C der Preis der Frucht erscheinen, wenn sie dem Bereich C1:C3 zugeordnet werden kann.

Das soll aber auch bei Teilstrings geschehen, z.B. A11 enthält den Text von C3.

Mit dieser Formel werden nur gleiche Zellen "akzeptiert":
=WENN(ZÄHLENWENN($C$1:$C$3;A10)>0;B10;"")

Nun kann man wohl bei den Kriterien Platzhalter verwenden ...
(wie hier beschrieben http://www.excelformeln.de/formeln.html?welcher=41
Beispiel =ZÄHLENWENN(A:A;"*Text")
)
... aber ich brauche das ja beim Bereich (im Beispiel A:A) - und da funktioniert es nicht: hier mit meinen Bezügen
=ZÄHLENWENN("*"&C1:C3&"*";A10)


Meine Notlösung ist es, die Vorgabewerte fest abzufragen:
=WENN(ODER(
ISTZAHL(FINDEN("Bananen";A10))
ISTZAHL(FINDEN("Äpfel";A10))
ISTZAHL(FINDEN("Pfirsiche";A10))
);B10;"")

Das ist aber unhandlich, wenn ein neuer Wert dazu kommt, z.B. C4 = "Pflaumen".

Das Ganze geht dann weiter mit Gemüse in Spalte D, Exoten in Spalte E, o.ä.

Könnt ihr mir da weiterhelfen ?
Schon mal Danke !

LG Pidray

:)

 

Hi,
meinst du das vielleicht ungefähr so?

	A	B	C
1		1	Pfirsiche
2		22	Äpfel
3		333	Bananen
4		4444	Kirschen
5
6
7
8
9
10	Pfirsiche gelbfleischig	1
11	Äpfel	22
12	Birnen	fehlt
13	Kochbananen	333

Zelle	Formel
B10	{=WENN(MAX(--ISTZAHL(SUCHEN(C:C;A10))); INDEX(B:B;MIN(WENN(ISTZAHL(SUCHEN(C:C;A10));ZEILE(C:C)))); "fehlt")}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen! Excel Jeanie HTML 4

An den Kochbananen erkennt man, dass (wg. SUCHEN statt FINDEN) Großschreibung keine Rolle spielt.

 

Danke für die schnelle Antwort !

Ich habe mal ein Beispiel angehängt, wie ich es meine ...
Dabei habe ich am Anfang noch zwei Zeilen eingefügt für die Spaltensumme.
In die neuen Zeilen 3 bis 9 sollen die Werte rein, welche in die Gruppensumme eingerechnet werden sollen:

im angehängten Bild:
grün = gruppenbestimmende Werte
blau = Gruppensumme
schwarz = variable Werte
rot = Felder, für die ich die Formel suche

Danke !

 

Hi,
damit du siehst, wie unpraktisch Grafiken für Excel sind, hier meine Antwort - auch mal als Grafik...

Geschickter ist es, Tabellen, Formeln usw. hier mit einem Tool wie z. B. der Jeanie darzustellen.
(siehe meinen vorigen Post)

 

aber verstehen tue ich es nicht :-(

=WENN(SUMME((C$3:C$6"")*ISTZAHL(SUCHEN(C$3:C$6;$A10)));$B10;"")

was macht der rote Teil ? Filtert der "leere" Vorgabenzellen raus, damit kein falscher Suchtreffer gemeldet wird ?

. . .

hab die kleinen grauen Zellen noch mal aufgeweckt und habs einzeln probiert:
=WENN(SUMME((C$3"")*ISTZAHL(SUCHEN(C$3;$A10)));$B10;"")

und dann das ganze wieder in die Matrix gesetzt - es funzt super !
und ich versteh es nun auch.

Meine eigenen Versuche habe ich immer abgebrochen, weil ich hierbei augenscheinlich kein richtiges Ergebnis erhielt: =ISTZAHL(SUCHEN(C$3:C$6;$A10))
Es kam immer "FALSCH" raus ... es fiel/fällt mir schwer, "in der Matrix" zu denken.


Tausend Dank für Deine Hilfe - und Jeanie schau ich mir auch mal an !

 

Hallo,

ich meine, dass ich eigentlich genau das machen möchte, was Erich G. in seinem ersten Post darstellt (siehe Excel-Anhang):

Wenn ein gewisser Range einen bestimmten Text enthält, soll ein bestimmter Wert in meiner Zelle angezeigt werden (mein Excel verdeutlicht, was gemeint ist).

Ich kann die Formel von Erich leider nicht genau nachvollziehen.

Ich habe es auch versucht mit
=IF((ISNUMBER(SEARCH("CH";$A$2:$A$10)));$B$2:$B$10*wink.gif*
wobei A2:A10 der Suchbereich und B2:B10 der Wertebereich.
Man könnte in dieser Formel evtl. auch mit IF(AND(ISNUMBER... die zweite notwendige Bedingung anwenden, aber das hat nicht geklappt.

Kann mir jemand auf die Sprünge helfen wie sich das lösen lässt?

Vielen Dank!
Karin

 

Moin Karin,

meinst Du es so?
Sheet2

	D	E	F	G
2		Apfel	Birne	Pfirsich
3	AT	1	4	7
4	CH	2	5	8
5	DE	3	6	9

Zelle	Formel
E3	=SUMMENPRODUKT(((ISTZAHL(FINDEN(E;$A:$A)))*(ISTZAHL(FINDEN($D3;$A:$A))))*1;$B:$B)

Sheet2

	A	B
1	Name	Wert
2	Apfelkuchen, AT	1
3	Apfelkuchen, CH	2
4	Apfelkuchen, DE	3
5	Birnenkompott, AT	4
6	Birnenkompott, CH	5
7	Birnenkompott, DE	6
8	Pfirsich, gelb, AT	7
9	Pfirsich, gelb, CH	8
10	Pfirsich, gelb, DE	9

Excel Tabellen im Web darstellen >>

 

Hallo Dirk,

genau so war's gemeint. Vielen Dank!

Nun möchte ich das aber gerne auch noch verstehen, denn auf die Verwendung vom Summenprodukt wäre ich nun wirklich nicht gekommen. Ich war wohl auch etwas konzentriert auf ein Möglichkeit von "WENN" und habe damit ewig lange in Kombination mit anderem herumprobiert.

Kannst du mir deshalb noch die folgenden Fragen beantworten?
1. Summenprodukt in diesem Zusammenhang: Ich kenne die Formel nur bzgl. Multiplikation von Matrizenelementen. Was bewirkt sie hier?
2. Verbindung der ISNUMBER mit einem *: Wann wird "AND" benutzt, wann *?
3. Was bewirkt das *1?

Wäre super, wenn ich hier noch was dazulernen könnte! Danke für die Geduld!

Karin

 

Hi Karin,
das geht jetzt an deiner aktuellen Frage etwas vorbei, aber vielleicht hilft dir diese Variante auch beim Verstehen der Formeln:

	A	B	C	D	E	F	G
1	Name	Wert
2	Apfelkuchen, AT	1			Apfel	Birne	Pfirsich
3	Apfelkuchen, CH	Test		AT	1	4	7
4	Apfelkuchen, DE	3		CH	0	0	8
5	Birnenkompott, AT	4		DE	3	11	9
6	Birnenkompott, DE	5
7	Birnenkompott, DE	6		AT	1	4	7
8	Pfirsich, gelb, AT	7		CH	Test	<>	8
9	Pfirsich, gelb, CH	8		DE	3	5	9
10	Pfirsich, gelb, DE	9

Zelle	Formel
E3	=SUMMENPRODUKT(((ISTZAHL(FINDEN(E;$A:$A)))*(ISTZAHL(FINDEN($D3;$A:$A))))*1;$B:$B)
E7	{=WENN(MAX(ISTZAHL(FINDEN($D7;$A:$A))*ISTZAHL(FINDEN(E;$A:$A))); INDEX($B:$B;MIN(WENN(ISTZAHL(FINDEN($D3;$A:$A))*ISTZAHL(FINDEN(E;$A:$A));ZEILE(A:A))));"<>")}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

 

Moin Karin,

zu1)
ich bin nicht so der Erklärbär, daher verweise ich mal auf folgende Seite Summenprodukt - Eine Funktion der besonderen Art hier findest Du eine sehr gute Beschreibung.
zu2)
* bedeutet und, + bedeutet oder
in diesem Fall, erst wenn BEIDE Prüfungen zutreffen, dann ...
zu3)
die Multiplikation mit 1 brauchtst du in diesem Fall nicht, habe die Formel noch mal um ein paar Klammern und der *1 gekürzt.

Sheet2

	E
3	1

Zelle	Formel
E3	=SUMMENPRODUKT((ISTZAHL(FINDEN(E;$A:$A))*ISTZAHL(FINDEN($D3;$A:$A)));$B:$B)

Excel Tabellen im Web darstellen >>

 

Hallo Dirk und Erich,

danke, nun habe ich einen fast vollständigen Überblick! Der Link zur Verwendung des Summenprodukts ist super.
Der Link zusammen mit Erich's Nachsatz "Dirks Formel addiert die Werte eines Treffers (5+6=11), meine Formel nimmt den Wert des 1. Treffers." sind Gold wert, da es in meinem Fall dazu kommen könnte, dass ich in Spalte A für DE einmal Birnenkompott und einmal Birnenkuchen habe. Dann möchte ich aber nicht, dass die Treffer addiert werden. Muss mir das noch etwas genauer anschauen, aber mit euren Formeln habe ich jetzt ja die Grundlagen.

Nun wäre ein Apfelkuchen grad gar nicht so schlecht! ;-)

Karin

 

Moin Karin,

... zum Frühstück könnte mir gefallen *biggrin.gif*