Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
972to976
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
972to976
972to976
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Formel Bedingung über mehrere Zellen

Formel Bedingung über mehrere Zellen
06.05.2008 14:51:38
Hugo
Hallo,
wie muss bitte die Formel für diese Bedingung lauten:
Wenn in Zellen I43 bis S43 irgendwo "w" steht UND in C43 steht "0", dann subtrahiere Zelle B5, ansonsten "0"
Danke nochmal.

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel Bedingung über mehrere Zellen
06.05.2008 14:58:00
Rudi
Hallo,
dann subtrahiere Zelle B5


wovon?
Gruß
Rudi

AW: Formel Bedingung über mehrere Zellen
06.05.2008 15:07:00
Hugo
Stimmt, müsste lauten:
dann weise Zelle B5 mit negativem Vorzeichen aus.

AW: Formel Bedingung über mehrere Zellen
06.05.2008 15:00:02
heikoS
Hallo Hugo,
das geht z.B. so:
Tabelle3

 CDEFGHIJKLMNOPQRS
430 -42   waskannhier

Formeln der Tabelle
ZelleFormel
E43=WENN(ISTZAHL(VERGLEICH("s";I43:S43;0))*(C43=0); -B5;0)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Klappt´s?
Gruß Heiko

Anzeige
AW: Formel Bedingung über mehrere Zellen
06.05.2008 15:18:00
Hugo
Hallo,
es funktioniert nur deswegen nicht, weil die "0" in C43 durch eine andere Wenn-Formel ausgegeben wird. Trage ich in C43 die "0" händisch ein funktioniert es.

"0" ist ja auch nicht 0 ...
06.05.2008 15:52:08
heikoS
Hallo Hugo,
dann laß Deine Wenn-Formel in c43 doch nicht den Text "0" sondern die Zahl 0 reinschreiben, dann klappt´s auch.
Guckst Du:
Tabelle3

 CDEFGHIJKLMNOPQRS
430 -42   waskannhier

Formeln der Tabelle
ZelleFormel
C43=WENN(ISTLEER(D43); 0;99)
E43=WENN(ISTZAHL(VERGLEICH("s";I43:S43;0))*(C43=0); -B5;0)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Und nu?
Gruß Heiko

Anzeige
...und dann gibt's ja auch noch -0 ! Das...
06.05.2008 17:58:00
Luc:-?
...kann bei bestimmten Formeln tatsächlich rauskommen,
auch ohne Dezimalen und mit Standardformatierung... ;-)
Gruß Luc :-?

AW: "0" ist ja auch nicht 0 ...
06.05.2008 19:11:41
Hugo
Wie setzt man denn zwei Bedingungen für den Vergleich und anschl. SVerweis, also in etwa:
=WENN(ISTZAHL(VERGLEICH(8 UND 9;I11:S11;0);SVERWEIS(I11;Tabelle2!$E$5:$F10;2);0)
Irgendwie fehlt auch ne rechte Klammer hier :-/
Danke!

Schreib die Fml mal richtig auf für nur...
06.05.2008 19:43:00
Luc:-?
...eine Bedingung, Hugo,
und liefere die andere extra dazu oder verwende in der Formel nur den Dummy "Bedingungen" und liefere beide Bedingungen extra. So macht die Fml keinerlei Sinn; sie entspricht auch nicht dem Niveau der bereits gegebenen Antworten. Du solltest eigentlich schon weiter sein! Anderenfalls hätte ich dir die xlFkt UND(bedingung1;bedingung2) zur Verbindung der beiden Bedingungen empfohlen. Aber lt deiner Vorgabe macht das keinen Sinn...
Gruß Luc :-?

Anzeige
AW: Schreib die Fml mal richtig auf für nur...
06.05.2008 20:07:00
Hugo
=WENN(ISTZAHL(VERGLEICH(UND(8;9);I11:S11;0));SVERWEIS(I11;Tabelle2!$E$5:$F10;2;0)
So mit UND Funktion, geht aber auch nicht.

AW: Schreib die Fml mal richtig auf für nur...
06.05.2008 21:40:03
Hugo
Merke gerade habe noch etwas zum Verweis vergessen.
Weil ich auch nicht auf die korrekte Formel komme schreib ich nochmal alles auf:
Wenn in Zellen I11 und M11 und O11 und Q11 irgendwo 8 oder 9 steht, dann verweise anhand des Wertes unter der Zelle wo die 8 oder 9 steht auf Tabelle2!$E$5:$F10;2);0)
Die 8 oder 9 kommt in den genannten Zellen nur 1 mal vor. Beide zusammen tauchen nicht auf.

Das ist leider noch nicht viel klarer, aber ich...
07.05.2008 02:25:00
Luc:-?
...interpretiere das jetzt mal wie folgt, Hugo...
Wenn in Zellen I11 und M11 und O11 und Q11 irgendwo 8 oder 9 steht, dann verweise anhand des Wertes unter der Zelle wo die 8 oder 9 steht auf Tabelle2!$E$5:$F10;2);0)
...als...
Wenn in Zellen I11, M11, O11 oder Q11 8 oder 9 steht, dann verweise anhand des Wertes unterhalb der jeweiligen Zelle, in der die 8 bzw 9 steht (also in I12, M12, O12 oder Q12) auf Tabelle2!$E$5:$F10 mit Arg3=2 und Arg4=0. Und sonst? Meine Formel gibt dann Leer zurück. Kannst du ja b.Bed ändern! Die Fml steht im Folgd in A11: =WENN(Vorauswahl=0;"";WENN(INDEX({1.0.0.0.5.0.7.0.9};Vorauswahl)=0;"";SVERWEIS(INDEX(I12:Q12;Vorauswahl);Tabelle2!$E$5:$F10;2;0)))
Vorauswahl ist der Name einer benannten Fml (Menü Einfügen - Namen - Definieren, dabei muss die 1.Zelle mit einer Fml, in der Vorauswahl auftritt, ausgewählt sein):
=WENN(ISTNV(VERGLEICH(8;$I11:$Q11;0));WENN(ISTNV(VERGLEICH(9;$I11:$Q11;0));0;VERGLEICH(9;$I11:$Q11;0) );VERGLEICH(8;$I11:$Q11;0))
Du kannst natürlich auch Vorauswahl in der Zellfml durch die zugehörige Fml ersetzen (brauchst dann keinen Namen zu definieren), aber das macht die Fml nur unnötig lang und unübersichtlich.
{1.0.0.0.5.0.7.0.9} in der Zellfml ist eine sog Matrixkonstante; d.h., die {} müssen mitgeschrieben wdn. Ansonsten brauchst du hier keinerlei Matrixfml - immer vorausgesetzt...
1. du willst nur die Werte in den angegebenen 4 einzelnen Zellen der Zeile 11 mit 8 bzw 9 vgl,
2. diese in Gänze 8 oder 9 sein können und zwar stets nur eine davon - also nicht noch andere Inhalte in dieser Zelle,
3. der Wert, der dann in Spalte E der Tab2 gesucht/vgl wdn soll, stets in der Zelle direkt unterhalb der gefundenen Zelle in Tab1 steht und zwar auch nur 1x.
Die Fktt VERGLEICH und SVERWEIS gehen hier stets vom 1.Auftreten des Vglwertes aus. Ansonsten wird der Fehlerwert #NV geliefert, den ich bei Vorauswahl abgefangen habe. Wenn der Vglwert dann nicht in Tab2!E5:E10 vorkommt, wird aber dieser Fehlerwert geliefert. Müsstest du dann b.Bed selber abfangen.
Falls meine Interpretation nicht mit deinen Intentionen übereinstimmen sollte, müsstest du dich mal exakter ausdrücken. Dafür hast du ja nun ein Bsp... ;-)
Gruß Luc :-?
PS: UND ist hier sinnlos und von dir auch falsch gebraucht!

Anzeige
Leider machen diese Fmln noch nicht ganz...
07.05.2008 11:52:00
Luc:-?
...das, was ich beschrieben habe, Hugo.
Deshalb hier noch mal eine Neufassung mit 2 benannten Formeln...
A11: =WENN(Wahlindex=0;"";SVERWEIS(INDEX(I12:Q12;Wahlindex);Tabelle2!$E$5:$F10;2;0))
Wahlindex =INDEX({0.1.0.0.0.5.0.7.0.9};1+ABRUNDEN(Vorauswahl;0))+INDEX({0.1.0.0.0.5.0.7.0.9};1+REST(10*Vorauswahl;10))
Vorauswahl =--(WENN(ISTNV(VERGLEICH(8;$I11:$Q11;0));0;VERGLEICH(8;$I11:$Q11;0))&","&WENN(ISTNV(VERGLEICH(9;$I11:$Q11;0));0;VERGLEICH(9;$I11:$Q11;0)))
Jetzt wdn auch alle 8 u. 9 in den nicht signifikanten "Zwischenzellen" ignoriert.
Gruß Luc :-?

Anzeige
AW: Leider machen diese Fmln noch nicht ganz...
07.05.2008 12:28:02
Hugo
Hallo lieber Luc, danke für die Mühe.
Bin nun etwas überfordert das alles zu verstehen.
1. Es sind jetzt 3 Formeln? Wenn dem so ist, wie bekomme ich die alle in eine Zelle?
2. Matrixkonstante: Die Formel muss "nach unten" kopierbar sein, ich meine damit z.Bsp. von A11 nach A20, usw.
3. Ich versuche mich deutlicher auszudrücken:
A11 = Wenn in Zellen I11, M11, O11 oder Q11 8 oder 9 steht, dann verweise anhand des Wertes Z unterhalb der jeweiligen Zelle, in der die 8 bzw 9 steht (also in I12, M12, O12 oder Q12) auf Tabelle2!$E$5:$F10, dort den 1.ten Wert rechts, also in Spalte F neben dem verwiesenen Wert Z , ANSONSTEN (wenn in Zellen I11, M11, O11 oder Q11 keine 8 oder 9 steht) schreibe in A11 eine 0.

Anzeige
AW: Leider machen diese Fmln noch nicht ganz...
07.05.2008 13:36:00
Hugo
Ich poste gleich mal einen Screenshot was ich genau meine :)

AW: Leider machen diese Fmln noch nicht ganz...
07.05.2008 18:09:34
Hugo
Ist das nicht machbar?

Na, dann habe ich ja (fast) alles richtig...
07.05.2008 18:25:00
Luc:-?
...verstanden, Hugo,
nur - die Formeln reagieren immer noch auf 8 und 9 in nicht relevanten Zellen (zwischen den von dir genannten). Deshalb habe ich das noch mal neu gefasst (mit SUMMENPRODUKT). Dabei habe ich dem Formelteil, der sich wiederholt, einen Namen gegeben. Du weißt doch, dass man Zellbereichen einen Namen geben kann! Dieses Feature ist so aufgebaut, dass der einzutragende Bereich quasi eine Formel ist, denn die betreffende Eintragszeile im Namensassistenten beginnt mit =. Statt eines Zellbereichs gibt man nach dem = eine Formel ein. Diese darf nur nicht länger als 255 Zeichen sein - die Tabellennamen, die generell automatisch ergänzt wdn - bei Fernbezügen auch ganze Pfade -, mitgerechnet.
In der Zellformel steht dann nur noch der Name dieser Formel statt des quasi "ausgelagerten" Formelteils. Auf diese Weise kann man Formeln kürzer und übersichtlicher gestalten.
Die benannte Fml wird von XL zuerst ausgerechnet und das Ergebnis anstelle des Formelnamens in die Zellformel eingesetzt.
Du kannst natürlich auch darauf verzichten und die FmlNamen durch den von ihnen repräsentierten FmlTeil ersetzen...
ZellFml: =WENN(Wahlindex=0;0;SVERWEIS(INDEX(I11:Q11;Wahlindex);Tabelle2!E$5:F$10;2;0))
benFml: Wahlindex=SUMMENPRODUKT({1.0.0.0.1.0.1.0.1};(SPALTE(I10:Q10)-8);(I10:Q10=8)+(I10:Q10=9))
Das Einzige, was nicht mit deinen Screenshots übereinstimmte, war die Adressierung der Vgls- u. Auswahlzellen. Aber da hattest du dich wirklich missverständlich ausgedrückt.
Wichtig ist, dass du beim Eintragen der benannten Fml unbedingt die Zelle auswählst, in deren Fml dann ihr FmlName verwendet wird. Wenn du dann die 1.Zellformel in A11 eingetragen hast, kannst du sie kopieren und auch in die anderen Zellen einfügen. Die Formeln (auch die benannte) passen sich entsprechend an.
Die Matrixkonstante habe ich auch verändert. Sie stellt ein Feld mit genausoviel Spalten wie der Vglsbereich dar. Nur sind hier schon Werte eingetragen (du bräuchtest sonst Hilfszellen) - alle relevanten "Spalten" haben eine 1, die anderen sind hier 0 gesetzt. Das Ganze wird mit den Spaltennr ab 1 und der Addition der Vgl auf 8 bzw 9 multipliziert. So kann Wahlindex stets nur 0 oder die lfdNr einer relevanten Spalte, also 1 für I10, 5 für M10, 7 für O10, 9 für Q10, liefern. 0 wird abgefangen und eine Zahl >0 als Index für die Auswahl der Vglszahl aus der Folgezeile (hier Zeile 11) benutzt. Die wird dann von SVERWEIS zur Auswahl des Wertes aus Tab2 benutzt.
Jetzt weißt du wie die Formeln fkt. Wenn noch nicht alles klar ist, empfehle ich dringend, in der xlHilfe nachzulesen.
Gruß Luc :-?
PS: Falls du mal mit OOcalc arbeiten solltest - das kennt bei aller Ähnlichkeit mit XL keine Matrixkonstanten. Die würden dort weggelassen oder durch NV() ersetzt.
Übrigens kannst du Bilder und Dateien auch hier hochladen, was den Vorteil hat, dass man die dann auch noch nach Jahren sehen kann! Steht doch oben ganz deutlich da: Hier geht es zum File-Upload

Anzeige
AW: Na, dann habe ich ja (fast) alles richtig...
07.05.2008 20:56:00
Hugo
Hallo Luc,
habe nun folgendes gemacht:
1. A11 wurde zu C11 und in den Formeln abgeändert ( -8 wurde in der Matrix zu -6)
2. Habe Zelle C11 markiert, dann im Namensassistent die benFml eingegeben
Userbild
3. In Zelle C11 die Zellfml einkopiert, sowie auch in C15 & C19.
Userbild
Tabelle2 ist unverändert.
Irgendwas stimmt noch nicht.

-8 in -6 zu ändern war KEINE gute Idee,...
08.05.2008 00:34:00
Luc:-?
...Hugo,
denn diese Angabe hatte nichts mit dem Standort der Zellformel, aber alles mit der ersten Vglszelle (I10) zu tun. SPALTE(I10) ergibt den Wert 9 (9.Spalte). Hier wird aber 1 benötigt → 9-8=1. Deshalb hatte ich auch lfdNr geschrieben! Bei deiner Änderung wird für I10 3 zurückgegeben. Daraufhin wird der Wert aus K11 für den SVERWEIS benutzt. Da K11 aber leer zu sein scheint, entsteht hier der Fehler #BEZUG! Anderenfalls wäre 1 zurückgegeben worden, da schon I10=8 ist. Daraus ergibt sich dann der Suchwert in I11=6. Damit hättest du in C11 als Ergebnis bspw den Wert aus Tabelle2!F10 erhalten, falls Tabelle2!E10=6 ist!
Außerdem kommt in den Formeln kein Bezug auf A11 vor! Das wäre dann iterativ und du würdest bei xlStandardeinstellung eine entsprechende Fehlermeldung bekommen. Ich habe die Formeln so abgefasst, dass du die Zellfml in eine beliebige Zelle setzen kannst, wenn du den Hinweis bzgl der benannten Fml einhältst. Anderenfalls müssten hier die Spalten absolut (mit vorangestelltem $) angegeben wdn. Dann würde es reichen, wenn beim Eintragen der benannten Fml eine beliebige Zelle der gleichen Zeile ausgewählt ist. Aber das hattest du ja wohl richtig gemacht, denn in der 2.Datengruppe wird so der Wert 13 ausgewählt und der kommt offensichtlich in E5:E10 nicht vor, so dass hier der Fehlerwert #NV zurückgegeben wird.
Jedenfalls ist es dir gelungen, das genau durchkonstruierte Fmlsystem ziemlich zu verwirren... ;->
Gruß Luc ;-?
PS: Soviel erkläre ich nur selten, weil es meist nicht nötig ist...

Anzeige
Danke
08.05.2008 12:28:00
Hugo
Ja, so klappts schon besser ;)
Recht herzlichen Dank!

Bitte! Aber trotzdem noch eine zusätzliche...
08.05.2008 14:26:30
Luc:-?
Erklärung, Hugo,
damit dir wirklich klar wird, was da passiert...
Statt der bisher verwendeten benannten Fml kann auch...
Wahlindex=SUMMENPRODUKT({1.0.0.0.1.0.1.0.1};SPALTE($A:$I);($I10:$Q10=8)+($I10:$Q10=9))
...verwendet wdn, denn es kommt hier nur auf die Durchnummerierung der betroffenen Spalten an. Ich wollte dir nur einen optischen Bezug auf deinen Bereich bieten. In der Fml wird zu der betroffenen Tabellenzeile eine Spaltenrelevanzmaske (als Feld, die Matrixkonstante) mit der lfdNr der Spalte (SpaltenNrFeld) und dem spaltenweisen Ergebnis des Vgls (ebenfalls als Feld; Voraussetzung: 8 und 9 dürfen nicht gleichzeitig in relevanten Spalten auftreten, sonst ergibt sich durch die Addition eine Verdopplung der lfdNr der relevanten Spalte!) spaltenweise multipliziert (Produkte). Die Spaltenergebnisse wdn zum Schluss summiert (Produktsumme; die MS-Bezeichnung SUMMENPRODUKT ist eine Fehlübersetzung ins Deutsche, quasi ein Anglizismus!). Da alle nichtrelevanten Spalten stets 0 liefern, gibt das Ergebnis (unter der o.g. Voraussetzung!) die lfdNr der Spalte zurück, aus der der Vglswert mit Tab2 entnommen wdn muss.
Gruß Luc :-?
PS: Übrigens, was heißt ...schon besser ...? Gibt's noch Probleme? ;-)

Anzeige
AW: Bitte! Aber trotzdem noch eine zusätzliche...
08.05.2008 14:52:11
Hugo
Nein, damit nicht. Ich versuche zu verstehen. Wenn ich selbst eine mir scheinbar einfachere Formel selbst basteln möchte, hakt immer irgend etwas. Zum Mäusemelken.
https://www.herber.de/forum/messages/976079.html

Aha! Sieht ganz ähnlich aus! Passt die dortige...
08.05.2008 15:37:00
Luc:-?
...Antwort, Hugo?
Sonst hake ich mich da heute Abend ein!
Gruß Luc :-?
PS: Kann auch nichts schaden, wenn du dort zwecks Vgl einen Link auf diesen Thread setzt...

Ach Gott, ist ja gar kein neuer Thread! orT
08.05.2008 15:42:59
Luc:-?
xxxxx ?
:-?

NEUE Formel mit 2 Bedingungen über mehrere Zellen
08.05.2008 14:47:58
Hugo
Hallo,
trotz einiger Beispiele die mir hier schon geliefert wurden, schaffe ich es leider nicht eine neue Formel zu basteln. Zwei Stunden des Probierens (Grmpf) sind vergangen, jetzt frage ich eben nochmal zu diesem Thema.
Meine Formel lautet:
=WENN(ISTZAHL(VERGLEICH("9";I11:Q11;0))*(C110); SVERWEIS(C11;Tabelle2!G5:I10;3);0)
Das bringt mir aber immer 0.
Liegt es daran, dass der Wert in C11 auch durch eine Formel ausgewiesen wird?
Userbild
Tabelle2:
Userbild

Die Fortsetzung ist...
08.05.2008 22:38:54
Luc:-?
...hier zu finden!
Luc :-?

331 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige