Supportnet / Forum / Tabellenkalkulation
sVerweis von unten nach oben
Frage
Hallo.
Ich möchte eine Tabelle auswerten, wobei ich eigentlich sVerweis benutzen würde. Allerdings liefert diese Funktion immer den ersten Wert, für den das Suchkriterium übereinstimmt.
Ich möchte aber einen anderen.
Dazu gäbe es zwei alternative Hilfsmittel.
Zum einen ist in der Zeile in der mein gewünschter Wert steht ein leeres Feld in einer Spalte, zum anderen ist es immer der unterste Wert, der mit dem Suchwert, der die entsprechende Spalte beschreibt.
Weil das doch etwas komplizierter ist, hier ein Beispiel:
......... A...................B................C
1....Äpfel.............€3,10...........1
2....Bananen......€2,60..........1
3....Birnen..........€1,60...........1
4....Äpfel.............€3,20...........1
5....Bananen ....€2,10............" "
6....Birnen..........€3,20...........1
7....Äpfel.............€1,90............" "
8....Birnen..........€3,30............" "
Wie komme ich jetzt an die "€2,10" der untersten Bananen? (B5)
sVerweis("Bananen";A1:C8;2) liefert mir "2,60" weil er in Zeile 2 schon fündig würde.
Die Tabelle selbst kann ich nicht ändern oder über Filter oder ähnliches benutzen, weil die Datei von Kollegen verwaltet wird. Ich kann nur lesen und in einer anderen Tabelle auswerten.
Kann ich von unten nach oben suchen? Oder kann ich irgendwie mit Spalte "C" verknüpfen und filtern?
Danke für eure Hilfe
Antwort 1 von rainberg
Hallo,
wenn ich dich richtig verstehe, willst du im konkreten Falle den niedrigsten Preis für Bananen ermitteln.
Das geht so:
=MIN(WENN(A1:A1000="Bananen";B1:B1000))
Formeleingabe mit der Tastenkombination Strg+Shift+Enter abschließen, da es sich um eine Matrixformel handelt.
Anstatt des Kriteriums "Bananen" in der Formel kannst du auch einen Zellbezug benutzen der das Kriterium enthält.
Gruß
Rainer
wenn ich dich richtig verstehe, willst du im konkreten Falle den niedrigsten Preis für Bananen ermitteln.
Das geht so:
=MIN(WENN(A1:A1000="Bananen";B1:B1000))
Formeleingabe mit der Tastenkombination Strg+Shift+Enter abschließen, da es sich um eine Matrixformel handelt.
Anstatt des Kriteriums "Bananen" in der Formel kannst du auch einen Zellbezug benutzen der das Kriterium enthält.
Gruß
Rainer
Antwort 2 von Aliba
Hi Tweety,
wenn es pro Eintrag in Spalte A auch wirklich nur eine leere Zelle in SPalte C gibt, und diese Zelle in SPalte C auch wirklich leer ist, dann:
=SUMMENPRODUKT((A1:A100="Bananen")*(C1:C100="")*B1:B100)
ACHTUNG in B1 bis B100 dürfen keine Texte, nur Werte vorkommen, ansonsten:
=SUMMENPRODUKT((A1:A100="Bananen")*(C1:C100="");B1:B100)
CU Aliba
wenn es pro Eintrag in Spalte A auch wirklich nur eine leere Zelle in SPalte C gibt, und diese Zelle in SPalte C auch wirklich leer ist, dann:
=SUMMENPRODUKT((A1:A100="Bananen")*(C1:C100="")*B1:B100)
ACHTUNG in B1 bis B100 dürfen keine Texte, nur Werte vorkommen, ansonsten:
=SUMMENPRODUKT((A1:A100="Bananen")*(C1:C100="");B1:B100)
CU Aliba
Antwort 3 von Tweety78
Leider hilft mir beides nicht weiter.
Mein Beispiel war natürlich nur zur Verdeutlichung. In meinem Fall geht es Text, der herausgesucht werden soll. Und es ist nicht immer der kleinste Wert, sondern in der Tabelle der unterste, der das Suchkriterium erfüllt.
In meinem konkreten Fall gibt es auch bis zu 500 "Bananenspalten", von denen bis zu 10 in Spalte C leer sind. Diese 10 sind dann aber alle in Spalte B identisch.
Mein Beispiel war natürlich nur zur Verdeutlichung. In meinem Fall geht es Text, der herausgesucht werden soll. Und es ist nicht immer der kleinste Wert, sondern in der Tabelle der unterste, der das Suchkriterium erfüllt.
In meinem konkreten Fall gibt es auch bis zu 500 "Bananenspalten", von denen bis zu 10 in Spalte C leer sind. Diese 10 sind dann aber alle in Spalte B identisch.
Antwort 4 von Saarbauer
Hallo,
ob es damit so funktioniert wie du dir das vorstellst oder ob es weiterhilft kann ich auch nicht sagen, aber wie wäre es die Tabelle des Kollegen einfach in einer Hilfstabelle umzudrehen ?
Seine erste Zeile deine letzte Zeile
seine Letzte Zeile deine Erste
Müsste hiermit machbar sein
=INDIREKT("Tabelle1!A"&(ZEILEN(Tabelle1!$A:$A)-ANZAHLLEEREZELLEN(Tabelle1!$A:$A)-ZEILE()+2))
wobei die letzte Angabe
Anschliessend könnest du mit dem Normalen SVerweis weiterarbeiten
Gruß
Helmut
ob es damit so funktioniert wie du dir das vorstellst oder ob es weiterhilft kann ich auch nicht sagen, aber wie wäre es die Tabelle des Kollegen einfach in einer Hilfstabelle umzudrehen ?
Seine erste Zeile deine letzte Zeile
seine Letzte Zeile deine Erste
Müsste hiermit machbar sein
=INDIREKT("Tabelle1!A"&(ZEILEN(Tabelle1!$A:$A)-ANZAHLLEEREZELLEN(Tabelle1!$A:$A)-ZEILE()+2))
wobei die letzte Angabe
Zitat:
+2
deiner Tabelle anzupassen ist, ich habe mit einer Überschrift, die naürlich Überschrift bleibt hier gearbeitet. Für die übrigen Spalten +2
Zitat:
"Tabelle1!A"
wäre der Text entsprechend anzupassen."Tabelle1!A"
Anschliessend könnest du mit dem Normalen SVerweis weiterarbeiten
Gruß
Helmut
Antwort 5 von Tweety78
Die Idee, die Tabelle umzudrehen hatte ich auch schon.
Nur leider geht das auch nicht so einfach.
Es gibt wiederholt Spalten, in denen die Zellen einiger Zeilen zu einer Zelle verbunden sind.
Gibt es denn nicht irgendeine Suchfunktion. der man sagen kann, wo sie anfangen soll zu suchen?
Nur leider geht das auch nicht so einfach.
Es gibt wiederholt Spalten, in denen die Zellen einiger Zeilen zu einer Zelle verbunden sind.
Gibt es denn nicht irgendeine Suchfunktion. der man sagen kann, wo sie anfangen soll zu suchen?
Antwort 6 von Aliba
Hi Tweety,
versuchs mal mit nachstehender Matrixformel
(ACHTUNG !! geschweifte Klammern nicht mit eingeben, sondern Formel normal erfassen und dann mit Strg+Shift+Enter abschliessen)
{=INDEX($B$1:$B$2000;MAX(WENN(($C$1:$C$2000="")*($A$1:$A$2000="Banane");ZEILE(1:2000))))}
CU Aliba
versuchs mal mit nachstehender Matrixformel
(ACHTUNG !! geschweifte Klammern nicht mit eingeben, sondern Formel normal erfassen und dann mit Strg+Shift+Enter abschliessen)
{=INDEX($B$1:$B$2000;MAX(WENN(($C$1:$C$2000="")*($A$1:$A$2000="Banane");ZEILE(1:2000))))}
CU Aliba
Antwort 7 von rainberg
Hallo Tweety,
du könntest auch auf die Hilfsspalte C verzichten und folgendermaßen vorgehen:
Schreibe in D1 folgende Matrixformel und kopiere sie nach unten bis die Fehlermeldung #ZAHL! erscheint.
Damit werden dir alle in Spalte A vorkommenden Obstsorten einzeln aufgelistet.
Schreibe in E1 folgende Matrixformel und kopiere sie bis ans Ende der Einträge in Spalte D.
Damit erhältst du den letzen Listeneintrag des betreffenden Obstes aus Spalte B.
Vergiss die Tastenkombination Strg+Shift+Enter bei der Formeleingabe nicht und passe die tatsächliche Zeilenzahl deiner Liste in den Formeln an.
Gruß
Rainer
du könntest auch auf die Hilfsspalte C verzichten und folgendermaßen vorgehen:
Schreibe in D1 folgende Matrixformel und kopiere sie nach unten bis die Fehlermeldung #ZAHL! erscheint.
=INDIREKT("A"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A$1;;;ZEILE($1:$2000));A$1:A$2000)=1;ZEILE($1:$2000));ZEILE(A1)))
Damit werden dir alle in Spalte A vorkommenden Obstsorten einzeln aufgelistet.
Schreibe in E1 folgende Matrixformel und kopiere sie bis ans Ende der Einträge in Spalte D.
=INDEX(B:B;KGRÖSSTE(WENN(A$1:A$2000=D1;ZEILE($1:$2000));1))
Damit erhältst du den letzen Listeneintrag des betreffenden Obstes aus Spalte B.
Vergiss die Tastenkombination Strg+Shift+Enter bei der Formeleingabe nicht und passe die tatsächliche Zeilenzahl deiner Liste in den Formeln an.
Gruß
Rainer
Antwort 8 von Tweety78
Hallo Rainer.
Wie erwähnt, kann ich die Tabelle selbst nicht editieren.
Aber ich werd es am Montag im Büro dennoch mal ausprobieren, in eine andere Tabelle auszulesen.
Aber dann müsste ich dennoch zig Tausend Zeilen benutzen.
Geht´s auch ohne?
Wie erwähnt, kann ich die Tabelle selbst nicht editieren.
Aber ich werd es am Montag im Büro dennoch mal ausprobieren, in eine andere Tabelle auszulesen.
Aber dann müsste ich dennoch zig Tausend Zeilen benutzen.
Geht´s auch ohne?
Antwort 9 von brumm
Hallo Tweety78,
probier mal diese Formel aus. Du mußt sie aber an Deine Tabelle anpassen.
={WENN(ISTFEHLER(INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!$C$2:$C$126=$D$3);ZEILE(Tabelle1!$2:$126));ZEILE(A1))));"";INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!$C$2:$C$126=$D$3);ZEILE(Tabelle1!$2:$126));ZEILE(A1))))}
(ACHTUNG !! geschweifte Klammern nicht mit eingeben, sondern Formel normal erfassen und dann mit Strg+Shift+Enter abschliessen)
Ich benutze diese Formel um in einem Raumplan die Mitarbeiter anzuzeigen, die in einem Büro sitzen.
Hier musst Du den Zähler erhöhen, um den zweiten, dritten, vierten usw. Wert anzeigen zu lassen.
ZEILE(A1))));"";INDEX..... = ZEILE(A2))));"";INDEX usw.
1.wert = A1, 2.wert = A2, 3.wert =A3 usw.
Das gleiche gilt auch hier:
ZEILE(A1))))} = ZEILE(A2))))} usw.
Gruss
brumm
probier mal diese Formel aus. Du mußt sie aber an Deine Tabelle anpassen.
={WENN(ISTFEHLER(INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!$C$2:$C$126=$D$3);ZEILE(Tabelle1!$2:$126));ZEILE(A1))));"";INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!$C$2:$C$126=$D$3);ZEILE(Tabelle1!$2:$126));ZEILE(A1))))}
(ACHTUNG !! geschweifte Klammern nicht mit eingeben, sondern Formel normal erfassen und dann mit Strg+Shift+Enter abschliessen)
Ich benutze diese Formel um in einem Raumplan die Mitarbeiter anzuzeigen, die in einem Büro sitzen.
Hier musst Du den Zähler erhöhen, um den zweiten, dritten, vierten usw. Wert anzeigen zu lassen.
ZEILE(A1))));"";INDEX..... = ZEILE(A2))));"";INDEX usw.
1.wert = A1, 2.wert = A2, 3.wert =A3 usw.
Das gleiche gilt auch hier:
ZEILE(A1))))} = ZEILE(A2))))} usw.
Gruss
brumm