Supportnet Computer
Planet of Tech

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

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

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.

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
Zitat:
+2
deiner Tabelle anzupassen ist, ich habe mit einer Überschrift, die naürlich Überschrift bleibt hier gearbeitet. Für die übrigen Spalten
Zitat:
"Tabelle1!A"
wäre der Text entsprechend anzupassen.

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?

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

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.

=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?

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

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: