Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1196to1200
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
Inhaltsverzeichnis

"doppelter SVERWEIS"?

"doppelter SVERWEIS"?
Frank
Hallo liebes Forum,
ich habe im Anhang eine Datei hinterlegt in der zwei Datenblätter existieren. Im Datenblatt "Auswertung" sind gelben Zellen (Eingabe) und grüne Zellen (Ausgabe). Mit Hilfe einer geeigneten Formel sollen nun die Zahlen die in den grünen Zellen stehen aus den Blatt "Daten" herausgesucht werden. Die Werte können exakt die selben sein und wenn dem nicht so ist, sollen die Werte aus der nächsten (i+1) Zeile verwendet werden. Wenn die Möglichkeit 1 eintritt, sollen die Werte der Spalte B und C herangezogen werden. Wenn die Möglichkeit 2 eintritt, sollen die Werte der Spalte A und B herangezogen werden. Nun ist es ja so, dass ich mit SVERWEIS auf Grundlage einer Zahl, einen Wert aus der selben Zeile heraussuchen kann. Hier ist das Ergebnis aber von zwei Zahlen abhängig. Zur Zeit suche ich die geeigneten Zahlen "von Hand" heraus. Gibt es irgendeine Anweisung/Befehl, mit dessen Hilfe ich auf Grundlage von zwei Zahlen suchen kann?
Bsp.:
Datenblatt "Auswertung" Zelle B3 und C3 sind die Zahlen die ich im Datenblatt "Daten" in Spalte B und C (da Möglichkeit 1 ansonsten Spalte A und B) nach den Zahlen, werde in Zeile 13 fündig und stelle den Bezug her. Geht das irgendwie mit einer Formel?
https://www.herber.de/bbs/user/73253.xls
Vielen Dank für eure Unterstützung und viele Grüße,
Frank

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
Bahnhof
27.01.2011 08:25:29
Klaus
Hallo Frank,
ich habe leider nicht verstanden, was du wo unter welchen Bedingungen heraussuchen willst. Bitte nochmal für Kinder erklären.
Aber ein SVERWEIS mit zwei Suchspaltenbedingungen geht so:
http://www.excelformeln.de/formeln.html?welcher=30
Vielleicht hilft das ja schon.
Grüße,
Klaus M.vdT.
AW: Bahnhof
27.01.2011 09:49:06
Frank
Hallo Klaus M.vdT.,
vielen Dank für deine Antwort. Ich habe gerade eine dieser Formel probiert. Leider erscheint "Name". Ich verstehe die Formel auch irgendwie nicht. Ist es eventuell möglich die verwendete VERWEIS Formel zu erklären. Wieso ist mein Suchkriterium 2 und wieso wird der Suchvektor 1/x geteilt? Da würde ich ja nie drauf kommen.
Ich habe jetzt nur noch eine Zeile in der Datei. Ich möchte, dass die Zahl die in "Auswertung" E16 steht mit Hilfe einer Formel in E3 automatisch in den Spalten B und C von "Daten", mit den Suchkriterien aus B3 und C3 von "Auswertung" herausgesucht wird. Ich hofee es ist jetzt verständlicher.
https://www.herber.de/bbs/user/73270.xls
Vielen Dank und viele Grüße,
Frank
Anzeige
AW: Bahnhof
27.01.2011 10:03:35
Klaus
Hi Frank,
die korrekte Anpassung der Excelformeln-Formel auf deine Tabelle lautet:
=VERWEIS(2;1/(Daten!B1:B99&Daten!C1:C99=B3&C3);Daten!D1:D99)
Das "wenn" für die Möglichkeiten 1 oder 2 bekommst du dann selbst hin, ja?
Grüße,
Klaus M.vdT.
Vielen Dank
27.01.2011 10:22:23
Frank
Hallo Klaus M.vdT.,
das ist toll. DANKE schön. Aber mir würde es für die Zukunft helfen, wenn mir erklärt werden würde, was da genau geschieht (in meinem vorigen Beitrag habe ich gefragt)?
"Wieso ist mein Suchkriterium 2 und wieso wird der Suchvektor 1/x geteilt?"
Wäre schön, wenn du mir das erklären könntest. Nur so die Formel verwenden möchte ich nicht, ich möchte es auch gern verstehen :-) und dazu lernen.
Vielen Dank und viele Grüße
Frank
Anzeige
Erklärungsversuch
27.01.2011 10:33:58
Klaus
Hallo Frank,
schau dir die Ergebnisse der Bereich mal im Formeleditor (F9) an. Das Suchkriterium "2" könnte auch "99" oder "7335559" sein ... hautsache größer als 1.
Die Funktion "Verweis" sucht bei nicht-Übereinstimmung den nächsten passenden Wert. Die Kombinierte Matrix aus Daten!B1:B99&Daten!C1:C99 mit der Prüfung =B3&C3 ergibt Warheitswerte. Wenn die gesuchte Kombination an 3ter Stelle steht, ergibt Daten!B1:B99&Daten!C1:C99=B3&C3 die Matrix FALSCH;FALSCH;WAHR;FALSCH;FALSCH;FALSCH;[...].
Wahrheitswerte werden boolean als 1 oder 0 dargestellt. WAHR = 1, FALSCH = 0. Dadurch ergibt sich eine Neue Matrix: 0;0;1;0;0;0;[...].
Jetzt teilen wir 1 durch jeden Wert dieser Matrix. Durch Null teilen geht nicht! Darum ergeben sich Fehlerwerte. Durch 1 teilen geht jedoch sehr wohl! Dir wird gerade bestimmt klar, worauf das heraus läuft ...
1/Matrix ergibt #DIV0;#DIV0;1;#DIV0;#DIV0;#DIV0;[...]
In dieser Matrix suchen wir jetzt die Zahl 2 (oder 99 oder 7335559).
Der Verweis findet an dritter Stelle eine Zahl 1. Alle anderen Stellen sind #DIV0 Fehler .... demach denkt der Verweis, die beste Näherung an das gesuchte steht an Position 3. (Da die 1 näher an 7335559 ist als #DIV0 - da ALLES näher an 1 ist als an #DIV0)
Aus dem Ergebnisbereich Daten!D1:D99 wird nun die dritte Position als richtiges Ergebniss heraus gepickt.
Abgefahren, oder? Wilkommen in der wunderbaren Welt der booleanschen Logik :-)
Grüße,
Klaus M.vdT.
P.S.: all fame to www.excelformeln.de , die Idee stammt (leider) nicht von mir.
Anzeige
AW: Erklärungsversuch
27.01.2011 10:55:07
Frank
Vielen vielen Dank, dass ist wirklich abgefahren und gleichzeitig genial muss ich sagen. Ich habe mir die Formel in der Formelauswertung angesehen. Wenn ich in der Bearbeitungsleiste F9 drücke, erhalte ich sofort das Ergebnis.
Ich habe den Teil noch nicht ganz verstanden "Wenn die gesuchte Kombination an 3ter Stelle steht" , da in er Formelauswertung an der dritten Stelle kein WAHR oder eine 1 angegeben wird.
Was ist wenn ich anstatt -1 -1,05 eintrage, dann erhalte ich NV#? Wäre es auch denkbar, dass die Formel den nächstnäheren Wert nimmt? Es kann ja auch vorkommen, dass beide Zahlen die eingetragen werden nicht exakt in der Matrix wieder zu finden sind.
Am genailsten wäre ja, wenn dieser Fall eintritt, man den nächstkleineren und nächstgrößeren Wert heraussucht und dann das Ergebnis linear herausinterpoliert, ist soetwas möglich?
Ich habe schon vor langer Zeit eine Anfrage gestellt (bei Spotlight), wo sich dann die Profis gegenseitig mit Ihren Lösungen "geärgert" haben :-). Alles musste in einer Zelle stehen...das waren Formeln. Ich denke, dass sich hier für die Lösung meiner Frage auch so eine lange Formel ergeben würde. Meinst du das geht?
Vielen Dank und viele Grüße
Frank
Anzeige
AW: Erklärungsversuch
27.01.2011 11:21:41
Klaus
Hi Frank,
das geht sicherlich alles. Ich kann mir vorstellen, dass ich das mit Hilfsspalten oder VBA-udF lösen könnte.
Aber da gibts bessere Spezis für ... eventuell eine überarbeitete Frage als neues Thema reinstellen.
In einer einzelnen Formel ohne Hilfsspalten und VBA? Wenn ich jetzt sage, "dass geht nicht", dann kommt bestimmt einer der Spezis daher und postet eine 5-Zeilen-Matrixformel :-) Darum sag ich nur: "ich kanns nicht".
Im Formeleditor kannst du Teile der Formel (zB: Daten!D1:D99) mit der Maus markieren. F9 wertet jetzt nur das markierte Ergebnis aus. Bei 100-Zeilen-Matritzen ist das aber nicht lesbar, darum evt. die Bereiche verkleinern.
Anzeige
AW: Erklärungsversuch
27.01.2011 11:50:22
Frank
Hi Klaus M.vdT.,
danke für deine Antwort. Wegen mir können auch Hilfsspalten usw. erzeugt werden, hauptsache es kommt am Ende der richtige Wert heraus. Das wollte ich nur so berichten wie es damals war :-). Ich hatte das Problem damals auch über zich Hilfspalten gelöst und dann habe ich im Forum mein Problem geschildert und bekam so eine "Hammerformel", die genau das selbe konnte - ist schon ein wenig deprimierend (seufz). Ich werde eine neues Thema eröffnen oder kennst du jemanden den ich da konkret anschreiben könnte?
Also wenn du noch Ideen hättest, dann wäre ich dir sehr dankbar.
Vielen Dank und viele Grüße
Frank
Anzeige
AW: Erklärungsversuch
27.01.2011 14:51:17
Klaus
Hi Frank,
sag nochmal ganz genau was du willst. Soll ERST in der linken Spalte gesucht werden, und NUR wenn dort zB "-1" steht wird in der ZWEITEN Spalte weiter gesucht, zB "25" ?
Wo willst du dich annähern? Welcher Suchbegriff ist näher am Ziel -1 ; 25
  • -1,0001 ; 26

  • -1 ; 27

  • -1; 100000000000000

  • -100000000; 25

  • Vergiss nicht: ich / wir haben keine Ahnung, was du bezwecken willst (deine Spaltenüberschriften sind auch nicht aussagekräftig). Alles was dir gerade selbstverständlich vorkommt bitte nochmal akribisch erklären, dann wirds auch was mit der Lösung!
    (Ist die Reihenfolge im Blatt DATEN fix oder kann da uU. mit "sortieren" gearbeitet werden?)
    Grüße,
    Klaus M.vdT.
    Anzeige
    AW: Erklärungsversuch
    28.01.2011 08:42:11
    Frank
    Guten morgen Klaus M.vdT.,
    vielen Dank für deine Bemühungen. Meine Daten sind fest. Zur Erklärung, die Daten unter "Daten" Spalte A-C stellen Dehnungsbereiche von Beton dar.
    Okay ich versuche es nich genauer zu erklären: Wir befinden uns jetzt auf dem Blatt Daten:
    Im Bereich B3:B38 gelten diese Zahlen als Interpolationskriterium, da diese veränderlich und die Zahlen in C3:C38 fest sind (alle haben den Wert 25).
    Im Bereich C39:C155 gelten diese Zahlen als Interpolationskriterium, da diese veränderlich und die Zahlen in B39:B155 fest sind (alle haben den Wert -3,5).
    Ich habe die Bereiche farbig (blau) markiert. Es würde also reichen, wenn wir nur ein Suchkriterium haben, da die anderen Werte dann eh fest sind (wie oben erklärt).
    Das heißt, wenn die Zahl nicht exakt bspw. -1, sondern -1,036 enspricht, müsste die Formel
    B14 und B15 finden, da der eine kleiner und der andere größer als -1,036 ist und die dazugehörigen Werte aus Spalte D, so das sich für dieses Beispiel ein interpoliertes Ergebnis von
    -0,16217785145888 in Zelle E3 Baltt "Auswertung" ergibt. Ich wäre wirklich an einer Lösung interessiert und hoffe ich konnte es jetzt besser erklären.
    https://www.herber.de/bbs/user/73291.xls
    Vielen Dank und viele Grüße,
    Frank
    Anzeige

    300 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige