14
Zellen vergleichen mit Index....ich kriegs nicht hin.....
Hallo,
ich sitz schon seit Stunden und bekomms nicht hin..... Liegt wohl auch daran, dass ich nicht gerade ein Excel Freak bin...... Habs schon mit einer Index Variante probiert, habe aber immer Fehlermeldungen bekommen.
Also zum Problem:
Ich habe eine Zelle mit einem bestimmten Wert. Dieser soll mit verschiedenen Werten einer Spalte A verglichen werden. Neben den Werten aus Spalte A stehen ebenfalls Werte aus Spalte B. Gibt es jetzt eine Übereinstimmung zwischen einem Wert aus Spalte A und meinem Bezugswert aus der einzelnen Zelle, soll der entsprechende Wert aus Spalte B als Ergebnis präsentiert werden.Übersteigt der Bezugswert alle vorhanden Werte aus Spalte A, so soll der letzte Wert aus Spalte B präsentiert werden.
War das verständlich??
Ich hoffe
Danke für eure Hilfe...
ich sitz schon seit Stunden und bekomms nicht hin..... Liegt wohl auch daran, dass ich nicht gerade ein Excel Freak bin...... Habs schon mit einer Index Variante probiert, habe aber immer Fehlermeldungen bekommen.
Also zum Problem:
Ich habe eine Zelle mit einem bestimmten Wert. Dieser soll mit verschiedenen Werten einer Spalte A verglichen werden. Neben den Werten aus Spalte A stehen ebenfalls Werte aus Spalte B. Gibt es jetzt eine Übereinstimmung zwischen einem Wert aus Spalte A und meinem Bezugswert aus der einzelnen Zelle, soll der entsprechende Wert aus Spalte B als Ergebnis präsentiert werden.Übersteigt der Bezugswert alle vorhanden Werte aus Spalte A, so soll der letzte Wert aus Spalte B präsentiert werden.
War das verständlich??
Ich hoffe
Danke für eure Hilfe...
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 117858
Url: https://administrator.de/contentid/117858
Printed on: April 23, 2024 at 09:04 o'clock
14 Comments
Latest comment
Hallo OleSchulze!
Das sollte gehen: "=SVERWEIS(C1;A:B;2;WAHR)"
D1 = Formel
C1 = Eingabewert (> Suchwert A = Letzter Wert B)
A = Suchwert
B = Rückgabewert
Alternativ in der Art:
=WENN(ISTLEER(C1);"Bitte einen Wert in C1 Eingeben";SVERWEIS(C1;A:B;2;WAHR))
Gruß Dieter
Das sollte gehen: "=SVERWEIS(C1;A:B;2;WAHR)"
D1 = Formel
C1 = Eingabewert (> Suchwert A = Letzter Wert B)
A = Suchwert
B = Rückgabewert
Alternativ in der Art:
=WENN(ISTLEER(C1);"Bitte einen Wert in C1 Eingeben";SVERWEIS(C1;A:B;2;WAHR))
Gruß Dieter
Hallo OleSchulze,
ob's verständlich war?
Ich bin mir nicht sicher, aber so habe ich die Aufgabe verstanden und mit Excel 2007 umgesetzt:
Formel für Zelle B2:
=WENN(B1>MAX(A6:A10);B10;INDEX(B6:B10;VERGLEICH(B1;A6:A10;1)))
Was Du nicht bedacht hast:
Was soll denn passieren wenn die Eingabe zwar nicht größer als die Zahlen im Bereich "Zahl" (A6:A10) ist, aber wenn keine Übereinstimmung gefunden wird, beispielsweite die 15 als Eingabe?
Gruß Frank
ob's verständlich war?
Ich bin mir nicht sicher, aber so habe ich die Aufgabe verstanden und mit Excel 2007 umgesetzt:
| A | B | |
| 1 | Eingabe: | 20 |
| 2 | Ausgabe: | b |
| 3 | ||
| 4 | Zahl | Text |
| 5 | 10 | a |
| 6 | 20 | b |
| 7 | 30 | c |
| 8 | 40 | d |
| 9 | 50 | e |
Formel für Zelle B2:
=WENN(B1>MAX(A6:A10);B10;INDEX(B6:B10;VERGLEICH(B1;A6:A10;1)))
Was Du nicht bedacht hast:
Was soll denn passieren wenn die Eingabe zwar nicht größer als die Zahlen im Bereich "Zahl" (A6:A10) ist, aber wenn keine Übereinstimmung gefunden wird, beispielsweite die 15 als Eingabe?
Gruß Frank
Moin Pedant,
vierten dritten Parameter der Funktion VERGLEICH().
Dieser Parameter ("Vergleichstyp") bewirkt, dass
Wenn es nicht auf eine dieser Arten gefunden werden kann, knallt es halt ("#NV").
Grüße
Biber
Was soll denn passieren wenn die Eingabe zwar nicht größer als die Zahlen im Bereich "Zahl" (A6:A10) ist, aber wenn keine Übereinstimmung gefunden wird, beispielsweite die 15 als Eingabe?
das hast Du schon als Regel vorgegeben durch den Dieser Parameter ("Vergleichstyp") bewirkt, dass
- wenn =0 -> VERGLEICH den ersten Wert zurückgibt , der gleich Suchkriterium ist. Die Elemente der Suchmatrix dürfen in beliebiger Reihenfolge angeordnet sein.
- wenn -1, gibt VERGLEICH den kleinsten Wert zurück, der größer gleich Suchkriterium ist
- wenn =1 (wie bei Dir) dann der erste Größer-oder-Gleichwert bei einer aufsteigenden Liste
Wenn es nicht auf eine dieser Arten gefunden werden kann, knallt es halt ("#NV").
Grüße
Biber
Danke für die schnellen Antworten.
Hab mal nen Screenshot gemacht. Rot ist mein Bezugswert. Grün ist die Spalte mit den Vergleichswerten und in das grüne Feld oben rechts soll der Parallellwerte aus Spalte B eingetragen werde.
Habs damit versucht: =INDEX(A40:A52;KKLEINSTE(WENN(B40:B52=C40:C52;ZEILE(40:52));1))
Hab mal nen Screenshot gemacht. Rot ist mein Bezugswert. Grün ist die Spalte mit den Vergleichswerten und in das grüne Feld oben rechts soll der Parallellwerte aus Spalte B eingetragen werde.
Habs damit versucht: =INDEX(A40:A52;KKLEINSTE(WENN(B40:B52=C40:C52;ZEILE(40:52));1))
Hallo Biber,
danke für die Erklärung zum Parameter ("Vergleichstyp").
Ich hatte zwar auf die Schnelle einen ausgewählt, aber die Alternativen und die Auswirkungen nicht verstanden.
Meine erste Formel ist übrigens im Bereich etwas verrutscht.
Statt der Zeilen 6 bis 10 müssen es die Zeilen 5 bis 9 sein und statt der Zelle B10 die Zelle B9.
Korrigierte Formel für Zelle B2:
=WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;1)))
Jetzt könnte man nach einen exakte Übereinstimmung suchen (Parameter 0 statt 1):
=WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;0)))
und eventuelle Fehler abfangen:
=WENNFEHLER(WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;0)));"keine Übereinstimmung")
Also wenn es keine exakte Übereinstimmung gibt,
dann kommt statt des Excel-Fehlers "#NV" der Text "keine Übereinstimmung".
Gruß Frank
danke für die Erklärung zum Parameter ("Vergleichstyp").
Ich hatte zwar auf die Schnelle einen ausgewählt, aber die Alternativen und die Auswirkungen nicht verstanden.
Meine erste Formel ist übrigens im Bereich etwas verrutscht.
Statt der Zeilen 6 bis 10 müssen es die Zeilen 5 bis 9 sein und statt der Zelle B10 die Zelle B9.
Korrigierte Formel für Zelle B2:
=WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;1)))
Jetzt könnte man nach einen exakte Übereinstimmung suchen (Parameter 0 statt 1):
=WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;0)))
und eventuelle Fehler abfangen:
=WENNFEHLER(WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;0)));"keine Übereinstimmung")
Also wenn es keine exakte Übereinstimmung gibt,
dann kommt statt des Excel-Fehlers "#NV" der Text "keine Übereinstimmung".
Gruß Frank
Moin Pedant,
ebenfalls danke.
ich musste auch bei mir korrigieren: Vergleichstyp ist der dritte und nicht der vierte Parameter.
Und noch eine Nachfrage.
Ich gehöre eher zu den nicht so Technikbesessenen und habe noch kein Excel 2007.
Da scheint eine neue Funktion WENNFEHLER() hinzugekommen zu sein?
Denn mit meinem Excel 2002/2003 muss ich noch mit Wenn() +IstFehler() etwas unleserlicher schachteln.
Grüße
Biber
ebenfalls danke.
ich musste auch bei mir korrigieren: Vergleichstyp ist der dritte und nicht der vierte Parameter.
Und noch eine Nachfrage.
Ich gehöre eher zu den nicht so Technikbesessenen und habe noch kein Excel 2007.
Da scheint eine neue Funktion WENNFEHLER() hinzugekommen zu sein?
Denn mit meinem Excel 2002/2003 muss ich noch mit Wenn() +IstFehler() etwas unleserlicher schachteln.
Grüße
Biber
Hallo OleSchulze,
das hat ja offensichtlich nicht funktioniert.
Bereiche vergleichen mit WENN(B40:B52=C40:C52;dann,sonst) funktioniert nicht.
Hast Du den Ansatz von didi1954 oder meine Formel mal ausprobiert?
@Biber,
ja, ich glaube die Funktion "WENNFEHLER()" ist neu.
Es kamen übrigens einige neue Funktionen hinzu und durchaus hilfreiche.
Gruß Frank
Habs damit versucht:
=INDEX(A40:A52;KKLEINSTE(WENN(B40:B52=C40:C52;ZEILE(40:52));1))
=INDEX(A40:A52;KKLEINSTE(WENN(B40:B52=C40:C52;ZEILE(40:52));1))
das hat ja offensichtlich nicht funktioniert.
Bereiche vergleichen mit WENN(B40:B52=C40:C52;dann,sonst) funktioniert nicht.
Hast Du den Ansatz von didi1954 oder meine Formel mal ausprobiert?
@Biber,
ja, ich glaube die Funktion "WENNFEHLER()" ist neu.
Es kamen übrigens einige neue Funktionen hinzu und durchaus hilfreiche.
Gruß Frank
Hallo OleSchulze,
ich habe die Formel jetzt an die Tabelle aus Deinem Screenshot angepasst:
=WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0)))
und mit Fehlerbehandlung für Excel 2003 sieht sie so aus:
=WENN(ISTFEHLER(WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0))));"keine Übereinstimmung";WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0))))
Die Formel ist in F38 einzugeben.
Warum Du zwei rote Zellen (F36 und C40) hast ist mir allerdings nicht klar.
Ich habe F38 genommen und C40 komplett ignoriert.
Meine Formen nimmt den Wert aus F36, sucht ihn im Bereich A40:A52.
Wird eine exakte Übereinstimmung gefunden, dann wird der Wert angezeigt, der rechts daneben in Spalte B steht.
Ist der Vorgabewert aus F36 größer als alle Werte im Bereich A40:A52, dann wird der Wert aus B52 genommen, der der letzte Wert aus Spalte B ist.
Ist der Vorgabewert nicht größer und wird keine exakte Übereinstimmung gefunden, dann gibt es einen Fehler.
Dieser Fehler kann mit der Fehlerbehandlung abgefangen und durch einen Ausgabewert ersetzt werden.
In meinem Beispiel kommt dann der feste Text "keine Übereinstimmung", der allerdings auch durch anderen Text oder eine weitere Formel ersetzt werden kann.
Hat's was genutzt?
Gruß Frank
ich habe die Formel jetzt an die Tabelle aus Deinem Screenshot angepasst:
=WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0)))
und mit Fehlerbehandlung für Excel 2003 sieht sie so aus:
=WENN(ISTFEHLER(WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0))));"keine Übereinstimmung";WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0))))
Die Formel ist in F38 einzugeben.
Warum Du zwei rote Zellen (F36 und C40) hast ist mir allerdings nicht klar.
Ich habe F38 genommen und C40 komplett ignoriert.
Meine Formen nimmt den Wert aus F36, sucht ihn im Bereich A40:A52.
Wird eine exakte Übereinstimmung gefunden, dann wird der Wert angezeigt, der rechts daneben in Spalte B steht.
Ist der Vorgabewert aus F36 größer als alle Werte im Bereich A40:A52, dann wird der Wert aus B52 genommen, der der letzte Wert aus Spalte B ist.
Ist der Vorgabewert nicht größer und wird keine exakte Übereinstimmung gefunden, dann gibt es einen Fehler.
Dieser Fehler kann mit der Fehlerbehandlung abgefangen und durch einen Ausgabewert ersetzt werden.
In meinem Beispiel kommt dann der feste Text "keine Übereinstimmung", der allerdings auch durch anderen Text oder eine weitere Formel ersetzt werden kann.
Hat's was genutzt?
Gruß Frank
@Frank
Danke, bin gerade auf der Arbeit und kann es erst später eingeben.
Du hast recht, zweite rote Zelle ist nur eine Kopie und somit unwichtig.
Habe gestern doch noch etwas gefunden. Super kurz und funktioniert rechts gut gibt nur ein kleines Problem mit Rundungsfehlern. Weiß die Formel jetzt nicht mehr auswendig, gebe sienachher mal durch.
danke erst mal
greetz
OS
Danke, bin gerade auf der Arbeit und kann es erst später eingeben.
Du hast recht, zweite rote Zelle ist nur eine Kopie und somit unwichtig.
Habe gestern doch noch etwas gefunden. Super kurz und funktioniert rechts gut gibt nur ein kleines Problem mit Rundungsfehlern. Weiß die Formel jetzt nicht mehr auswendig, gebe sienachher mal durch.
danke erst mal
greetz
OS
Hallo Frank,
das wäre ja genau das was ich gesucht habe.!!!!
Hab´s eingefügt bekomme aber immer= Keine Übereinstimmung) als Fehlermeldung
Habe Office 2003. Woran kann es liegen??? Zellpositionen stimmen überein. Das Ergebnis in F36 habe ich ohne Dezimalstellen berechnen lassen. Der Wert der herauskommt ist definitiv in Spalte A40/52 enthalten .....
thx
OS
das wäre ja genau das was ich gesucht habe.!!!!
Hab´s eingefügt bekomme aber immer= Keine Übereinstimmung) als Fehlermeldung
Habe Office 2003. Woran kann es liegen??? Zellpositionen stimmen überein. Das Ergebnis in F36 habe ich ohne Dezimalstellen berechnen lassen. Der Wert der herauskommt ist definitiv in Spalte A40/52 enthalten .....
thx
OS
Hallo OleSchulze,
um systematisch an die Sache ranzugehen, solltest Du Dir zunächst Deine Formel aus F36 sichern und statt der Formel in F36 feste Zahlen eingeben, das um sicherzustellen, ob die Formel aus F38 funktioniert, also Übereinstimmungen findet.
Wenn Du soweit bist, dann kannst Du Deine Formel wieder in F36 einsetzen.
Es macht einen Unterschied, ob keine Dezimalstellen zu sehen sind oder ob wirklich keine da sind.
Um sicherzugehen, dass Du tatsächlich ohne Dezimalstellen berechnen lässt, kannst Du das Ergebnis der Formel noch auf 0 Stellen runden lassen.
=RUNDEN(Deine Formel,;0)
Da die Vergleichswerte im Bereich A40:A52 aber bis zu einer Nachkommastelle haben, halte ich eine Berechnung ganz ohne Nachkommastellen für unzweckmäßig.
Ein Runden auf 1 Nachkommastelle erscheint mir hier angemessener.
=RUNDEN(Deine Formel,;1)
Damit das klar ist:
Hier werden Zahlen verglichen und keine Zeichenfolgen (Text), deswegen gilt:
2,0 = 2 = 2,0000
und 2,0 <> 2,00001
egal wieviele Stellen in den Tabellenzellen dargestellt werden.
Gruß Frank
Das Ergebnis in F36 habe ich ohne Dezimalstellen berechnen lassen.
Der Wert der herauskommt ist definitiv in Spalte A40/52
Der Wert der herauskommt ist definitiv in Spalte A40/52
um systematisch an die Sache ranzugehen, solltest Du Dir zunächst Deine Formel aus F36 sichern und statt der Formel in F36 feste Zahlen eingeben, das um sicherzustellen, ob die Formel aus F38 funktioniert, also Übereinstimmungen findet.
Wenn Du soweit bist, dann kannst Du Deine Formel wieder in F36 einsetzen.
Es macht einen Unterschied, ob keine Dezimalstellen zu sehen sind oder ob wirklich keine da sind.
Um sicherzugehen, dass Du tatsächlich ohne Dezimalstellen berechnen lässt, kannst Du das Ergebnis der Formel noch auf 0 Stellen runden lassen.
=RUNDEN(Deine Formel,;0)
Da die Vergleichswerte im Bereich A40:A52 aber bis zu einer Nachkommastelle haben, halte ich eine Berechnung ganz ohne Nachkommastellen für unzweckmäßig.
Ein Runden auf 1 Nachkommastelle erscheint mir hier angemessener.
=RUNDEN(Deine Formel,;1)
Damit das klar ist:
Hier werden Zahlen verglichen und keine Zeichenfolgen (Text), deswegen gilt:
2,0 = 2 = 2,0000
und 2,0 <> 2,00001
egal wieviele Stellen in den Tabellenzellen dargestellt werden.
Gruß Frank
hallo,
Hallo Frank,
schau mal hier: www.bikeboersepeine.de/test.xls
Kaum ein Problem gelöst, ist das nächste da.
Die Formel hat irgendwie ein Problem mit meinen Werten aus F34- F39.
Werte in F34 sollen in F35 nur berechnet werden (und zwar als positive Zahl) wenn sie negativ sind dh. wenn sie die Bezugssumme von 150 unterschreiten. Des Weiteren dient F34 als Bezugswert zu F37. Diese beiden ergeben dann F39 als Prozentwert. Welcher dann in deine Formel einfließt......
Puh.......
Hoffe du kannst mir helfen...
thx
Hallo Frank,
schau mal hier: www.bikeboersepeine.de/test.xls
Kaum ein Problem gelöst, ist das nächste da.
Die Formel hat irgendwie ein Problem mit meinen Werten aus F34- F39.
Werte in F34 sollen in F35 nur berechnet werden (und zwar als positive Zahl) wenn sie negativ sind dh. wenn sie die Bezugssumme von 150 unterschreiten. Des Weiteren dient F34 als Bezugswert zu F37. Diese beiden ergeben dann F39 als Prozentwert. Welcher dann in deine Formel einfließt......
Puh.......
Hoffe du kannst mir helfen...
thx
Hab´s gelöst, mit =VERWEIS(F39;A39:A52;B39:B52).
funktioniert einwandfrei mit größeren und mit kleineren Zahlen als in A39-A52.
Danke noch mal.....
an alle
thx + greetz
OS
funktioniert einwandfrei mit größeren und mit kleineren Zahlen als in A39-A52.
Danke noch mal.....
an alle
thx + greetz
OS
Moin OleSchulze,
auch wenn es mittlerweile vielleicht obsolet ist....
Deine per Link bereitgestellte Excel-Tabelle hat in der Formel in Zelle F9 (von wo an die ganze Folgeberechnung in die Grütze geht) in der dort 2x enthaltenen Funktion VERGLEICH als dritten Parameter "Vergleichstyp" den Wert 0 (exakter Vergleich) statt 1 (nimm das erste kleiner/gleich Suchkriterium-Feld einer sortierten Liste). Ersetze diese beiden Parameter durch 1 wie oben beschrieben, dann klappt doch alles.
Was sich in Deiner test.xls nicht flicken lässt ist die marode Nebenrechnungsformel Formel in F15.
Da steht "=RUNDEN(#BEZUG!+F12;2)" ... tja. Kann Biber erraten nix.
Dieser Kommentar hat keinerlei Bezug zu Deinen Sätzen "Werte in F34 sollen in F35 nur berechnet werden.." ff.
Da weiß ich im Moment gar nicht, in welchem Tabellenblatt Du gedanklich bist.
Grüße
Biber
auch wenn es mittlerweile vielleicht obsolet ist....
Deine per Link bereitgestellte Excel-Tabelle hat in der Formel in Zelle F9 (von wo an die ganze Folgeberechnung in die Grütze geht) in der dort 2x enthaltenen Funktion VERGLEICH als dritten Parameter "Vergleichstyp" den Wert 0 (exakter Vergleich) statt 1 (nimm das erste kleiner/gleich Suchkriterium-Feld einer sortierten Liste). Ersetze diese beiden Parameter durch 1 wie oben beschrieben, dann klappt doch alles.
Was sich in Deiner test.xls nicht flicken lässt ist die marode Nebenrechnungsformel Formel in F15.
Da steht "=RUNDEN(#BEZUG!+F12;2)" ... tja. Kann Biber erraten nix.
Dieser Kommentar hat keinerlei Bezug zu Deinen Sätzen "Werte in F34 sollen in F35 nur berechnet werden.." ff.
Da weiß ich im Moment gar nicht, in welchem Tabellenblatt Du gedanklich bist.
Grüße
Biber
