Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Formeln in Excel





Frage

Hallo, ich bin bereits total verzweifelt ... In Tabellenblatt 1 gebe ich per Formel an: Wenn in Tabelle 2 in Spalte A der Begriff X erschient und in Spalte B der Begriff Y dann gibt mir den dazugehörigen wert in Spalte C an. Bsp: Spalte A B C X b 2 X c 4 X y 5 Gesucht ist der Wert 5. Das Problem: Die Reihenfolge der Zeilen kann sich ändern, deshalb suche ich auch in der Spalte nach dem Wert. Nun versuche ich mich seit 5 Stunden mit WENN, SVERWEIS, UND, ISTFEHLER und sonstigen Funktion und komme nicht drauf. Kann mir jemand helfen? Viele Grüße Yvonne

Antwort 1 von JoeKe

Hallo Yvonne,

versuch es mal so:

=WENN(SVERWEIS("X";Tabelle2!A1:C3;2;WAHR)="y";SVERWEIS("X";Tabelle2!A1:C3;3;WAHR);"")

Gruß

JöKe

Antwort 2 von CaroS

Hallo JöKe,

bei diesem Versuch kann aber einiges schiefgehen, denn es ist nicht gewährleistet, dass die beiden SVERWEISe in den gleichen Zeilen "arbeiten". Während also der erste SVERWEIS (vielleicht - mal sehen) in der 3. Zeile das passende Y findet und dem WENN ein WAHR liefert, holt sich der zweite SVERWEIS seinen Wert ohne große Umwege gleich aus der 1. Zeile, also die 2.

Weitere Probleme: die Mehrdeutigkeit (X) in der 1. Spalte und die Sortierung. (Excel-Hilfe: Wenn Bereich_Verweis WAHR ist, müssen die Werte in der ersten Spalte von Matrix in aufsteigender Reihenfolge angeordnet werden ... ; andernfalls gibt SVERWEIS möglicherweise nicht den richtigen Wert zurück.)

Ich setz mich mal ran und versuche, was besseres zu finden.

Gruß an alle,
CaroS

Antwort 3 von Chilindrina

Hallo JöKe,

bei mir funktioniert das nicht, er gibt mir leider nur leere Zellen. Hat es bei Dir geklappt?
Und, woher weiß der "Dann"-Teil der Funktion welchen der 4 untereinanderstehenden X-Werte er nehmen muss?

Grüße
Yvonne

Antwort 4 von CaroS

Hallo!

Als erstes gibt´s da auf jeden Fall eine einfache und übersichtliche Lösung mit einer Hilfsspalte. Die müsste in Tabelle 2 vor Spalte C eingefügt werden, (heißt dadurch selber C) und bekommt eine Formel wie z. B. (in C1): =A1&"|"&B1. Die Zahlen stehen dann in Spalte D.

In Tabelle 1 steht dann =SVERWEIS("X|Y"; Tabelle2!C1:D100; 2; FALSCH).
Die 5 wird gefunden, falls der Suchbegriff "X|Y" in der Hilfsspalte eindeutig ist. Sind mehrere Werte "X|Y" in der Hilfsspalte vorhanden, wird der erste Treffer ausgewertet.

Gruß,
CaroS

Antwort 5 von Chilindrina

Hallo CaroS,
super Lösung! Und es funktioniert!
Aus Aktualisierungsgründen (einfaches drüberkopieren eines neuen Datensatzes in Tabelle 2) kann ich aber leider keine zusätzliche Spalte einfügen.
Gibt es eine eine Lösung one dass man zusätzliche Spalten einfügen muss?
Grüße
Yvonne

Antwort 6 von Chilindrina

Hallo!

Ich habe es gefunden und möchte Euch natürlich teilhaben lassen :-)

Um die 5 zu erhalten wäre die Formel in Tabelle 1:
=VERWEIS(2;1/(Tabelle2!A1:A4Tabelle2!C1:C4)

*Freude*

Viele Grüße
Yvonne

Antwort 7 von CaroS

Hallo Chilindrina,

ich hatte gerade zwei längere Telefonate, da hast Du zwischendurch das Problem selber gelöst. Auf die Frage in AW5 gibt es zwei Antworten: 1. Bei meiner Lösung in AW4 mit Hilfsspalte und normalem SVERWEIS wird die Hilfsspalte unbedingt benötigt, der einzig mögliche Kompromiss wäre aus meiner Sicht, sie links von Spalte A einzufügen. Damit wird sie selbst zu A, A zu B, B zu C und die ehemalige Zahlenspalte C wird zu D. Auf diese Weise bleiben zumindest Deine ehemaligen Spalten A:C zusammenhängende unmittelbare Nachbarn, nur eben im Bereich B:D.

2. ... ohne dass man zusätzliche Spalten einfügen muss - da war ich gerade noch am Überlegen und Probieren, bis das Telefon klingelte. Also erstmal nur vielleicht. Aber Du hast ja inzwischen eine Lösung mit dem VERWEIS gefunden, das heißt wohl, dass Du nun nicht weiter suchst (bzw. nicht mehr suchen musst).

Leider konnte ich Deine Lösung aber noch nicht nachvollziehen, weil da beim Kopieren auf jeden Fall was verloren gegangen und/oder verfälscht worden ist. Außerdem gibt es gerade vom VERWEIS dummerweise zwei verschieden Versionen.

Kannst Du das nicht schnell noch einmal kopieren und ein V für Vektorversion (oder ein M für Matrixversion) dazuschreiben? Ich würde mir gern mal ansehen, wie das Problem der zwei Suchkriterien gelöst ist.

Gruß,
CaroS

Antwort 8 von Chilindrina

Hallo CaroS,

Sorry, vor lauter Glücksgefühlen habe ich da wohl nicht aufgepasst.

So müsste es nun stimmen:

=VERWEIS(2;1/(´Tabelle2´!A1:A5´Tabelle2´!C1:C5)

Hoffe das hilft! :-)

Ob V oder M weiß ich leider nicht. Wo ist das ersichtlich??

Viele Grüße,
Yvonne

Antwort 9 von Chilindrina

Irgendwie klappt das nicht mit dem hochladen... im Textfeld stimmt es. Seltsam...

Ich tippe es mal händisch ein:

=VERWEIS(2;1/(´Tabelle2´!A1:A4´Tabelle2´!C1:C4)

Mal kucken ob es nun funktioniert...

Antwort 10 von Chilindrina

Immer noch nicht....
Nun die Kurzversion ohne Verweise auf das andere Tabellenblatt:
=Verweis(2;1/(A1:A4C1:C4)

Antwort 11 von Chilindrina

Der mag da was nicht...
Wie wollen wir es lösen?

Antwort 12 von CaroS

Hallo Chilindrina,

weiß auch nicht, was Deinem PC fehlen könnte.

DIe Frage, ob Vektor- oder Matrixversion lässt sich leicht mit dem Formelassistenten beantworten, d. h. erst in das Wort =VERWEIS (in der Formelzeile) reinklicken, danach auf fx links daneben klicken und dann muss man sich entscheiden. Die Vektorversion hat normalerweise 3 Parameter, die Matrixversion nur 2, nämlich ein Suchkriterium und eine Matrix.

Vektorversion: VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)

? ? ? ? ? ? ? =VERWEIS( 2;     1 / (´Tabelle2´!A1:A4´Tabelle2´!C1:C4) ? ? ? ? ? ? ?

Matrixversion: VERWEIS(Suchkriterium;Matrix)

Aber was ich da oben sehe, sieht irgendwie gar nicht gut aus. Laut Deinen Beispieldaten müsstest Du doch nach x und y und nicht nach einer 2 suchen, um die 5 zu bekommen. Danach kommt als 2. Parameter "eins   geteilt durch runde Klammer auf Bereich1 <keine Lücke dazwischen> Bereich2 runde Klammer zu". Funktioniert das? Kann ich mir gar nicht vorstellen. Zum Schluss würde mindestens noch eine runde schließende Klammer fehlen, wenn hier überhaupt schon Schluss ist.

Kopierst Du das eigentlich aus der Formelzeile? Falls das jetzt nichts wird, muss ich mir eben selbst Gedaken machen, wie das mit einem Verweis gehen könnte.

Gruß,
CaroS

Antwort 13 von Chilindrina

Hallo CaroS,

Es handelt sich um die Vektorversion.

Vektorversion: VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)

Also nochmal die Formel:
=VERWEIS( 2;1/(´Tabelle2´!A1:A4 Tabelle2´!C1:C4)

Habe schon kopiert, eingetippt, alles versucht. In der Texteingabe stimmt die Formel und sobald ich auf "Absenden" gehe, erscheint der text mit einer falschen Formel.... Ob das an meinem PC liegt???

Grüße
Yvonne

Antwort 14 von Chilindrina

Das kann nicht wahr sein!!!!

Ich tippe mal die einzelnen untereinander, vielleicht geht es dann!

=Verweis(2;1/
(A1:A4&B1:B4
="X"
C1:C4

Vielleicht jetzt?

Antwort 15 von Chilindrina

nach dem "X" kommt noch ein
&"y")
und nach dem C1:C4 auch Klammer zu.
Nun passt es. Kommst Du klar?

Antwort 16 von CaroS

Hallo Chilindrina,

erstmal danke für die Mühe, die Du Dir gemacht hast. Das ist ja ein ganz erstaunliches und hartnäckiges Problem, sowas habe ich ja noch nie erlebt! Da kann man von weiter weg wohl auch kaum gute Ratschläge geben, außer den PC mal neu zu booten oder - und das hat mir wirklich schon öfter mal geholfen - den Text nicht direkt zu kopieren, sondern einen kleinen Umweg über einen primitiven Texteditor wie den Windows-Editor (Notepad.exe) zu machen.

In dem Falle also:
Formelzeile markieren und in Zwischenablage kopieren (Strg+C)
Editor öffnen und Inhalt der Zwischenablage in Editor einfügen (Strg+V)
kontrollieren, ob alles noch stimmt
alles neu markieren (Strg+A) und neu in Zwischenablage kopieren (Strg+C)
Inhalt der Zwischenablage in das Ziel (Forum-Textfeld) einfügen (Strg+V)
kontrollieren, ob immer noch alles noch stimmt

Nun habe ich natürlich versucht, die Stücken aus AW14 und AW15 zu einer ordentlichen Formel zusammenzusetzen und mit Deinen Beispieldaten in meinem Excel auszuprobieren. Es hat leider nicht geklappt, deshalb schreibe ich mal auf, was bei mir angekommen ist und nicht geklappt hat:

=VERWEIS(2; 1 / (A1:A4 & B1:B4 = "X" & "Y")C1:C4)

=VERWEIS(Suchkriterium; Suchvektor; Ergebnisvektor)

Wenn ich nun versuche, das mit der Syntax der Vektor-Verweis-Funktion in Überein-stimmung zu bringen, dann habe ich damit unlösbare Probleme. Es müssten sich ja theoretisch mindestens 2 (oder 3) Parameter zuordnen lassen, aber da schon mal nur ein einziges Semikolon vorhanden ist, können es eigentlich nur 2 Parameter sein.
• Die 2 ganz links sieht nicht gerade wie ein Suchkriterium aus, denn eigentlich soll ja nach "X" und "Y" gesucht werden.
• Und dann kommt das merkwürdigste: 1 / (irgendwas). Ich weiß nicht, was diese Schreibweise zu bedeuten hat, aber mit einer Division hat das bestimmt nichts zu tun.
• Was nun genau der zweite und der dritte Parameter - der Suchvektor und der Ergebnisvektor - sein soll, kann ich mir zwar denken, aber mein Excel kann nicht denken und kommt damit auch nicht klar.

Deshalb habe vor C1:C4 probeweise mal ein Semikolon eingefügt, das hat aber auch nicht geholfen.
=VERWEIS(2; 1 / (A1:A4 & B1:B4 = "X" & "Y"); C1:C4)
=VERWEIS(Suchkriterium; Suchvektor; Ergebnisvektor)

Ich weiß zwar nicht, was Du für ein Excel hast und was auf Deinem PC los ist, aber ein Grund aufzugeben ist das nicht, es gibt ja noch den Formelassistenten. Das ist das graue Fenster, in dem man zu einer Excelfunktion alle Parameter in einzelne Eingabefelder eingeben oder sich die Zuordnung anzeigen lassen kann. Nicht jeder arbeitet damit, deshalb noch mal der Aufruf des Formelassistenten: erst die Zelle mit der VERWEIS-Funktion markieren, dann in der Formelzeile in den Namen der Funktion =VERWEIS klicken, danach auf die kleine Schaltfläche f x links neben der Formelzeile klicken und in diesem Fall dann noch die Vektorversion auswählen. Dann erscheint (bei mir, bei Dir auch?) der sog. Formelassistent, der im oberen Teil ungefähr so aussieht:

+-VERWEIS-------------------------+
|  Suchkriterium _____________ [_]|
|     Suchvektor _____________ [_]|
| Ergebnisvektor _____________ [_]|
+---------------------------------+


Hier müsste man nun ablesen können, was genau Suchkriterium, Suchvektor und Ergebnisvektor ist. Kannst Du mir diese 3 Angaben bei Gelegenheit noch mal "abschreiben"? Und füge bitte nach dem & oder am besten zwischen allen Zeichen jeweils ein Leerzeichen ein, die kann ich ohne Probleme wieder rauslöschen. Es eilt nicht, aber es würde mich schon interessieren.

Ansonsten hoffe ich, dass es Dir und Deinem PC gut geht und dass die Formel auch richtig funktioniert.

Gruß,
CaroS

Antwort 17 von Chilindrina

Liebe CaroS,
genau die Formel (Deine letzte mit dem "probeweisen" Semikolon) ist die RICHITGE!!
Seltsam, dass es bei Dir nicht funktioniert. Bei mir stimmt es, auch die Ergebnisswerte sind korrekt. Hast Du Leerzeichen mit drin? Vielleicht liegt es daran, bei mir sind in der Formel keine Leerzeichen enthalten.

Versuch es doch nochmal. Hier trotzdem die Formelzeilen:

Suchkriterium = 2

Suchvektor = 1/(A1:A4

&

B1:B4="x"

&

"y")

Ergebnisvektor = C1:C4

Klappt es nun? Aus reiner Neugier, lass es mich bitte wissen.

Grüße
Yvonne

Antwort 18 von CaroS

Hallo Yvonne,

Du hast das Problem voll erfasst. Nein, nicht die Leerzeichen, auch nicht das Semikolon ist das Problem, sondern die Neugier. Man will einfach wissen, wie´s geht, jedenfalls geht es mir so.

Es gibt jetzt eine gute und eine schlechte Nachricht: Es funktioniert, aber ich weiß nicht warum. Bzw. warum es nicht schon früher funktioniert hat. Wenn ich mal für andere (oder mich selbst) eine Formel gut lesbar schreiben will, dann verwende ich jede Menge Leerzeichen, aber so, dass die Formel noch funktioniert. Wenn es dagegen kritisch ist, riskiere ich nichts und schmeiße ich alle Leerzeichen raus, so wie es Excel selber auch tut, denn Leerzeichen müssen ja nicht sein und könnten eben doch ... (manchmal, vielleicht, eventuell, man weiß ja nie ... ) eine Fehlerquelle sein.
Also daran lag es jedenfalls nicht.

Völlig unklar ist mir ja, dass das jetzt funktioniert, obwohl da:
1. immer noch alle diese merkwürdigen Dinge sind wie
- Suchriterium: 2
- Suchvektor: 1 / (zwei mit & zusammengesetzte Spaltenbereiche = zwei einzelne miteinander verkettete Zeichen, die fast wie ein Suchkriterium aussehen, und das alles in Klammern) und
2. schon beim Eingeben des Suchvektors in das Eingabefeld des Formelassisten rechts daneben das Zwischenergebnis {#DIV/0!;1;#DIV/0!; ... } angezeigt wird.
Ich glaube auch nicht, dass diese "illegalen Methoden" irgendwo dokumentiert sind, aber es scheint zu funktionieren.

Wie bist Du bloß auf sowas gekommen? Gut, ich ziehe die Frage zurück, das könnte noch komplizierter werden als es jetzt schon ist.

Eine kleine Erklärung noch, weshalb ich überall Leerzeichen dazwischen haben wollte. Als ich Dir vorhin meinen aktuellen Stand mitteilen wollte, ist mir was ähnliches passiert wie Dir, aber schon in der Vorschau, nämlich dass immer ein bestimmter Teil verschwunden ist. Es funktioniert sogar jetzt noch. Schreibt man nur
1/(A1:A9&B1:B9="x"&"y")
ist alles in Ordnung, setzt man aber ein Semikolon dahinter, dann bleibt nur 1/(A1:A9 übrig. Mit anderen Worten, & B 1 : B 9 = " x " & " y " ) ; fällt weg. Nachdem man sich genug darüber geärgert hat, fällt einem dann irgendwann ein, dass man mit & + Spe-zialcode + ; in HTML Sonderzeichen darstellen kann, wie z. B. Umlaute: ä = & auml ;
Das Verschwinden von & B 1 : B 9 = " x " & " y " ) ; wird wohl damit was zu tun haben, also mindestens ein Leerzeichen hinter jedes & !

Nochmal vielen Dank für Deine Mühe und für Deine Ausdauer, ich freue mich, dass es nun doch noch funktioniert hat und habe jetzt erstmal genug gelernt.

Gruß,
CaroS

Antwort 19 von nighty

hi CaroS :)

nur frauen sind neugierig männer wissensdurstig :)))

gruss nighty

Antwort 20 von CaroS

Hallo nighty,

sag bloß, Du bist ein Mann? Dann bist Du als Mann wohl wissensdurstig, wenn Du die Beiträge hier liest?
Ich verstehe schon, neugierig bist Du nicht, aber einer muss ja schließlich aufpassen, dass alles in Ordnung ist.        {:·)-¦-||¦¦¦¦

Gruß,
CaroS