Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

S-W-Verweis, Index, gleiche Werte - letzten finden

S-W-Verweis, Index, gleiche Werte - letzten finden
29.04.2019 12:54:38
Kulo
Hallo Zusammmen,
in einer Tabelle erfasse ich die Wochentage für Avisierungen von Lieferungen.
Wenn ich feststelle, dass eine Lieferung 20 mal an einem Mittwoch und nur zwei mal an einem Donnerstag erfolgte, will ich davon ausgehen, dass im Normalfall die Lieferung am Mittwoch erfolgt.
Von dieser Berechnung konnte ich Excel überzeugen.
Wenn der Kunde aber noch nicht so oft dabei war und ich ihn zB. bisher zwei mal an einem Dienstag beliefert habe und zweimal an einem Donnerstag, dann möchte ich, dass mir der Donnerstag als Liefertag ausgegeben wird. Es wäre also nicht die erste Übereinstimmung der "2" (zwei Lieferungen) sondern die letzte.
Man müßte Excel davon überzeugen, die Daten in meiner Tabelle von der letzten zur ersten Spalte zu lesen.
Zum besseren Verständnis habe ich eine Beispieldatei angehängt.
https://www.herber.de/bbs/user/129469.xlsm
Könnte mir bitte jemand die Formel zurecht biegen?
Ich würde mich sehr freuen.
Viele Grüße
Kulo

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: warum eine xlsm-Datei? ...
29.04.2019 13:07:25
neopa
Hallo Kulo,
... wenn Du scheinbar nur eine Formel suchst? Warum dann nicht eine xlsx-Datei?
Gruß Werner
.. , - ...
AW: warum eine xlsm-Datei? ...
29.04.2019 13:23:08
Kulo
Hallo Werner,
ist mir beim Basteln der Beispieldatei aus Versehen passiert, als ich der Datei mit "Speichern unter" einen Neuen Namen gab. Hab die Dateiendung nicht geändert.
Suche wirrklich nur eine Formel.
https://www.herber.de/bbs/user/129478.xlsx
Gruß
Kulo
AW: dann ohne Hilfsspalte und allgemein ...
29.04.2019 13:43:34
neopa
Hallo Kulo,
... weil ich nicht weiß ob evtl. auch mehr als 2 Liefertage sein könnten.
In I7:
=WENN(MAX(D7:H7)=WENNFEHLER(KGRÖSSTE(D7:H7;2);0);INDEX($6:$6;AGGREGAT(14;6;SPALTE(D7:H7)
/(D7:H7=MAX(D7:H7));1));INDEX($D$6:$H$6;VERGLEICH(MAX(D7:H7);D7:H7;0)))

und nach unten kopieren
D.h. aber auch, wenn kein Liefertag angeben war, dann wird als Liefertag der Freitag ausgegeben.
Wenn dies nicht gewünscht ist, einfach Formel noch mit einem WENN() zusätzlich klammern.
Gruß Werner
.. , - ...
Anzeige
AW: dann ohne Hilfsspalte und allgemein ...
29.04.2019 20:40:38
Kulo
Hallo Werner,
vielen Dank für diese Formel.
Wow! Es funktioniert wie ich es wollte.
Aber ich muss zugeben, dass das bei weitem meinen Horizont übersteigt. Da muss ich noch jede Menge lernen.
Also vielen Dank nochmal und
eine schöne Woche.
Viele Grüße
Kulo
AW: danke, wünsche ich Dir auch owT
30.04.2019 09:06:24
neopa
Gruß Werner
.. , - ...
an Werner
30.04.2019 22:16:46
Kulo
Hallo Werner,
vielen Dank nochmal, dass du dich meinem Problem angenommen hast.
Wie ich schon geschrieben habe, ist die Formel für mich nicht zu verstehen geschweige denn nachzuvollziehen.
Ich hab also nochmals gegoogelt und bin auf folgende Seite gestoßen:
http://www.excelformeln.de/formeln.html?welcher=27
Mit dieser Info hab ich selbst mal etwas experimentiert und habe eine Formel "gefunden", die scheinbar auf das gleiche Ergebnis kommt, wie die von dir.
Leider kann ich nicht nachvollziehen, wie diese Formel funktioniert.
Ist es möglich, dass du mir kurz mal von Excel zu Deutsch übersetzt, was diese Formel macht?
Ich würde mich sehr freuen, würde aber auch verstehen, wenn diese Anfrage den Rahmen dieses Forums sprengt.
Wenn du magst, schau mal rein:
https://www.herber.de/bbs/user/129511.xlsx
Viele Grüße
Kulo
Anzeige
AW: VERWEIS() -Formel ist ausreichend ...
01.05.2019 08:19:19
neopa
Hallo Kulo,
... für die von Dir eingestellten Daten. Nimm die VERWEIS()-Formel!
Ich hatte zu kompliziert gedacht und in deshalb in meiner Antwort leider falsches angenommen (und dies dann auch noch dazu nicht korrekt umgesetzt), was zu einer unnötigen langen Formel führte.
Sorry.
Die Auswertung meiner aufgezeigten Formel funktioniert im übrigen auf gleichem Grundprinzip. Erzeugung von Fehlerwerten für die Bedingungsprüfungen und diese durch die Möglichkeiten der Funktion AGGREGAT(14;6;...) nicht zu berücksichtigen, um dadurch den korrekten Wert zu ermitteln.
Gruß Werner
.. , - ...
Anzeige
AW: VERWEIS() -Formel ist ausreichend ...
01.05.2019 13:26:03
Kulo
Hallo Werner,
danke für die Antwort.
Ich habe die Formel jetzt nochmal umgestellt:
=VERWEIS(2;1/($D7:$H7=MAX($D7:$H7));D$6:H$6)
Könntest du mir bitte noch erklären, was die erste "2" nach der Klammer bedeutet? Es funktioniert mit jeder Zahl >=1.
Bei der "Teilformel" nach dem ersten Semikolon werden wohl die tatsächlichen Werte erzeugt, in denen die Formel die Übereinstimmung finden soll um dann den entsprechenden Inhalt aus der soundsovielten Spalte in Zeile 6 zurück zu geben.
Aber wieso findet die Formel den letzten Wert? Schaut sie so lang in dem Suchvektor bis sie keine Übereinstimmung findet und geht dann ein zurück?
Würde mich sehr über eine Antwort freuen.
Bis dahin und noch einen schönen 1. Mai.
Viele Grüße
Kulo
Anzeige
AW: hierzu ...
01.05.2019 14:33:18
neopa
Hallo Kulo,
... das 2. Argument der VERWEIS() Formel ergibt durch die Division sowie den Bedingungsvergleich im Divisor eine Matrix aus Fehlerwerten und (evtl.) einer oder mehrere 1en.
Die Funktion VERWEIS() findet nun in dieser Matrix gemäß Funkt.definition "den größten Wert im Suchvektor, der kleiner oder gleich dem Wert von Suchkriterium ist" und was dort nicht explizit steht, ignoriert sie dabei dabei aller Fehlerwerte in der Matrix (die durch die Division von 1/FALSCH welches zu =1/0 gewandelt wird) entstehen. Die Position dieser letzten gefundenen 1 in der Matrix wird als Position im Ergebnisvektor gesucht und dessen Wert als Ergebnis von der Funktion ausgegeben.
Der/die Excelhelfer als erste diese Möglichkeit (Nutzung der Division von Bedingungsgleichungen mit Ignorierung deren Fehlerwerte) "entdeckt" haben (dies genau genommen für das Auffinden einer letzten nicht leeren Zelle mittels ""), haben dafür als Suchvektor die einstellige Zahl 2 genommen, was ja auch naheliegend ist.
Da ich einen kleinen Teil mit dazu beigetragen habe, diese bezeichnete Möglichkeit auch für andere Anwendungsfälle einzusetzen und diese dann auch oft erklärt habe, nutze ich in meinen entsprechenden Formeln die 9 als Suchkriterium. Andere nutzen z.B. die 5 oder auch die 41. Du könntest auch PI() oder ... nutzen. Vermeide jedoch die 1, auch wenn sie hier im Beispiel zum korrekten Ergebnis führt.
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
01.05.2019 15:35:55
Kulo
Hallo Werner,
in meinem Beispiel können in der Martrix ja nur Falsch oder Wahr stehen. Es wird ja geprüft, ob das Maximum der entsprechenden Zeile an der entsprechenden Position steht. Wenn ich dann immer "1" geteilt durch diesen Wert nehme, kommt dann bei Falsch "#DIV/0!" raus und wird ignoriert, bei "1" eben "1", da 1 geteilt durch 1 den Wert 1 ergibt.
Wenn dann die Funktion "den größten Wert im Suchvektor, der kleiner oder gleich dem Wert von Suchkriterium ist" ermittelt, ist in meinem Beispiel jeder vorhandene Wert Der letzte entsprechende Wert in der Matrix gibt dann die Position aus, welche im _ Ergebnisvektor (bei mir

($D$6:$H$6)
) "angefahren" werden muss. Also wenn eine "2" rauskommt, dann die zweite Spalte im Bereich

$D$6:$H$6
. Wäre dann Zelle "E6".
Ist dass soweit korrekt?
Es ist alles nicht so einfach... ;-)
Viele Grüße
Kulo
Anzeige
AW: korrekt; nur nicht E6 sondern Wert aus E6 owT
01.05.2019 16:06:39
neopa
Gruß Werner
.. , - ...
AW: korrekt; nur nicht E6 sondern Wert aus E6 owT
01.05.2019 17:20:22
Kulo
Hallo Werner,
vielen Dank, dass du dir nochmal Zeit genommen hast.
Es ist noch ein weiter Weg, aber jeder "cm" zählt. ;-)
Vielen, vielen Dank und noch einen schönen Tag.
Viele Grüße
Kulo
AW: wünsche ich Dir auch owT
01.05.2019 17:58:29
neopa
Gruß Werner
.. , - ...
AW: S-W-Verweis, Index, gleiche Werte - letzten finden
29.04.2019 13:10:18
Rainer
Hallo Kulo,
erstelle eine Hilfstabelle, welche du mit diesen Formeln füllst:
=D7+SPALTE()/1000000
(Beispiel für die erste Zelle)
Dann funktioniert deine Formel.
Die Kommastellen kannst du ausblenden, wenn sie dich stören.
Gruß, Rainer
AW: S-W-Verweis, Index, gleiche Werte - letzten finden
29.04.2019 13:28:37
Kulo
Hallo Rainer,
danke für den schnellen Tipp.
Mit so etwas hätte ich bestimmt nicht probiert.
Deine Lösung funktioniert.
Ich würde aber eine geänderte Formel ohne Hilfstabelle vorziehen.
Vielleicht weißt du oder jemand anderes noch eine andere Lösung?
Aber trotzdem vielen Dank!
Einen schönen Tag und
viele Grüße
Kulo
Anzeige

310 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige