Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1400to1404
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

EXCEL (S-VERWEIS, Mehrfach, WENN)

EXCEL (S-VERWEIS, Mehrfach, WENN)
07.01.2015 14:53:14
Manuel
Hallo an alle,
meine Excelkenntnisse sind begrenzt und nun stehe ich von einer Aufgabe die ich leider nicht lösen kann, da ich die einzelnen Formeln nicht zusammenhängend definieren und dokumentieren kann.
Im Anhang findet ihr eine Excelliste (Testversion) mit allen relevanten Infos.
Excelliste kann leider nicht als Anhang angehängt werden...
In der Tabelle 1 werden alle Daten manuell (später dann mit einem Drop down Feld) eingegeben.
Die Suchkriterien sind hier folgende:
Operator
TML/DOOR
Hafenplatz
PLZ
Containertype inkl. Gewicht
Leer
Relation
Nun soll in der zweiten Tabelle o.a. 7 Kriterien überprüft und übereinstimmen um mir dann den jeweilige Preis und die Offertnummer in Tabelle 1 automatisch zu generieren.
(...also bis dahin kann noch der S-Verweis + INDEX (Vergleich..) angewendet werden..soviel habe hier bereits herausgefunden.)
Nun wird das ganze interessant...es soll noch dazu der jeweilige Preis (z.B. Suchkriterium ist 20' bis 8 to) auch noch übereinstimmen...im Beispiel wäre das 317 €.
Wird hier dann noch 20' bis 16,5 to gesucht soll dann natürlich der Preis von dieser Kategorie in Tabelle 1 erscheinen.
ZUM Verständnis: 20' = 20'FUß Container / 40' = 40'FUß Container
LÖSUNG:
Ich brauche bitte eine Formel (die Formel soll bitte auch für mich zum Verständnis erklärt werden, damit ich diese dann in mein Kalkulationstool einbauen kann), wo
A) die Offertnummer automatisch (wenn alle 7 Kriterien übereinstimmen) in Tabelle 1 ausgeworfen wird.
b) der jeweilige Preis (wenn alle 7 Kriterien übereinstimmen) aus der jeweiligen Gewichtsspalte in Tabelle 1 übernommen wird.
Ich hoffe, meine Problemstellung ist verständlich.
Vielen Dank
Liebe Grüße
Manuel

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Musterdatei
07.01.2015 14:56:30
Klaus
Hallo Manuel,
Im Prinzip geht das mit einer Hilfsspalte, Verketten und Summewenn, oder ohne Hilfsspalte mit einer kilometerlangen Matrixformel. Oder per VBA-UDF, mit VBA geht alles :-)
Aber eine Formel mit 7 oder mehr Variablen entwerfe und erkläre ich dir nicht am Reißbrett. Stell bitte eine Musterdatei rein, am besten mit ein paar "händisch" eingetragenen Musterlösungen.
Grüße,
Klaus M.vdT.

Meine Version: Musterdatei
07.01.2015 15:19:21
Klaus
Hi Manuel,
hier meine Version mit Hilfsspalte, Mini-Erklärung in der Datei:
https://www.herber.de/bbs/user/94815.xls
Neopas Version habe ich nicht getestet, sie scheint mir aber besser (da keine Hilfsspalten) - aber auch schwerer Nachzuvollziehen für dich, oder?
Grüße,
Klaus M.vdT.

Anzeige
AW: Meine Version: Musterdatei
07.01.2015 15:27:14
Manuel
Hi Klaus,
sind schon mal ganz gut aus - vorab vielen lieben Dank.
lg
Manuel

mit VERWEIS() ...
07.01.2015 15:17:32
neopa
Hallo Manuel,
... in B12: =INDEX(Tabelle2!A:U;VERWEIS(9;1/(Tabelle2!A1:A9=B3)/(Tabelle2!E1:E9=B4)/(Tabelle2!C1:C9=B5)/(Tabelle2!D1:D9=B6)/(Tabelle2!F1:F9=B8)/(Tabelle2!B1:B9=B9);ZEILE(A:A));VERGLEICH(B7;Tabelle2!1:1;))
in B13: =VERWEIS(9;1/(Tabelle2!A1:A9=B3)/(Tabelle2!E1:E9=B4)/(Tabelle2!C1:C9=B5)/(Tabelle2!D1:D9=B6)/(Tabelle2!F1:F9=B8)/(Tabelle2!B1:B9=B9);Tabelle2!Y:Y)
Gruß Werner
.. , - ...

Anzeige
AW: mit VERWEIS() ...
07.01.2015 15:28:46
Manuel
Hallo Werner,
vielen Dank für deine Info.
lg
Manuel

einfacher ...
07.01.2015 15:38:10
neopa
Hallo Manuel,
... in B12: =INDEX(Tabelle2!A:U;VERGLEICH(B13;Tabelle2!Y:Y;);VERGLEICH(B7;Tabelle2!1:1;))
und in B13 wie bereits angegeben.
Gruß Werner
.. , - ...

AW: einfacher ...
07.01.2015 15:57:07
Manuel
Super - Danke Werner

Hut ab, Werner!
07.01.2015 16:08:09
Klaus
Hallo Werner,
ich hätte nicht gedacht dass das ohne Matrix und Hilfsspalte geht. Die Frank-Kabel Formel mit mehrfachem Suchkriterium, da währ ich ja nie drauf gekommen. Respekt! Den Preis dann einfach über die eindeutige Offertnummer zu ermitteln ist die Sahnehaube.
Manuel, bitte vergiss meine laienhafte Lösung und übernimm Werners Version! Wenn du noch die Erklärung dazu brauchst, kannst du hier das Formelprinzip nachlesen:
http://www.excelformeln.de/formeln.html?welcher=48
Grüße,
Klaus M.vdT.

Anzeige
AW: Hut ab, Werner!
07.01.2015 16:55:34
Manuel
Super - Vielen lieben Dank...=)
lg
Manuel

wäre aber nicht nötig, denn ...
08.01.2015 09:24:33
neopa
Hallo Klaus,
... dieser erweiterte Formelansatz ist mittlerweile schon viele Jahre und relativ oft von mir in Anwendung. An dieser spez. "Weiterentwicklung" der "Frank Kabel" Formel hatte ich gemeinsam mit FP (Franz Pölt) gewirkt. Deren Wirkungsweise hatte ich seit dem auch schon mehrfach Nachfragenden (versucht) zu erklären (meist in Online-Excel). Ich hatte diese Wirkungsweise dann auch deshalb so verinnerlicht, dass ich mir dann schon vor Jahren erlaubt habe, die 2 durch die 9 - mein Erkennungsmerkmal ;-) zu ersetzen.
Für evtl. Nachleser des Threads muss ich noch anmerken, dass diese Art von VERWEIS()-Formel trotzdem ein MATRIXFormel ist. Diese bedarf nur keines {}-Eingabeabschlusses, wirkt aber derartig.
Gruß Werner
.. , - ...

Anzeige
AW: EXCEL (S-VERWEIS, Mehrfach, WENN)
07.01.2015 14:58:14
Jack_d
Hallo Manuel,
ohne eine Mustermappe versuch ich es gar nicht erst, aber wenn ich deine Frage richtig verstehe, könntest du mit
-DBsumme()
-Summenprodukt()
deine Ergebnisse erzielen.
Denn letztlich geht es ja darum aus einer Großen Tabelle Werte zu extrahieren.
Alternativ kann man aber auch eine Pivot dafür "missbrauchen" indem du mit 7 Berichtsfiltern deine Ergebnisse reduzierst auf eben das eine Ergebnis!
Grüße

AW: EXCEL (S-VERWEIS, Mehrfach, WENN)
07.01.2015 15:04:40
Jack_d
Hallo Manuel,
kannst du mir den Parameter "Containertype" erklären?
zudem die dazugehörigen Parameter in deiner Tabelle (Die es ehrlich gesagt nicht leicht macht, da sie eine 3. Informationsebene in die 2 darunterliegenden Projeziert)

Anzeige
AW: EXCEL (S-VERWEIS, Mehrfach, WENN)
07.01.2015 15:07:02
Manuel
Hallo,
Containertype bezieht sich auf die Daten wo die Preise stehen (20' bis 8 to...etx.) auf Tabelle 2

AW: EXCEL (S-VERWEIS, Mehrfach, WENN)
07.01.2015 15:11:14
Jack_d
Hallo
Das da Preise stehen, kann man erahnen, mehr aber auch nicht, da sie Einheitenlos sind.
Tatsächlich würde mich aber interessieren was sich hinter den kürzeln 20'leer usw. verbirgt, um damit eventuell einen algorythmus anzustossen
Grüße

AW: EXCEL (S-VERWEIS, Mehrfach, WENN)
07.01.2015 15:12:59
Manuel
Hallo,
20'leer ist lediglich eine Bezeichnung und bezieht sich auf einen Container.
Sprich soviel kostet ein Transport von einem leeren Container von z.B. Terminal Linz nach Hamburg...
lg

Anzeige
AW: EXCEL (S-VERWEIS, Mehrfach, WENN)
07.01.2015 15:16:31
Manuel
hier noch die ganze neue Datei....https://www.herber.de/bbs/user/94813.xls

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige