SVERWEIS soll 2. Wert ziehen ODER wenn Bedingung erfüllt Wert ziehen der nicht wie in

[Isaaaaaabell]

Hallo 
Ich hoffe ich finde hier Hilfe. Sitze seit Tagen vor einem Excel-Problem & hab bisher keine Lösung gefunden. Folgendes:
Ich habe Ähnlichkeiten in der Form 0,977 berechnet. Diese Werte können in meiner Suchtabelle doppelt vorkommen. In der Ergebnistabelle möchte ich nun zu meinen Ähnlichkeiten die Menge und den Kundennamen hinzuspielen. Mit einem SVERWEIS würde ja, wenn die Ähnlichkeit 0,977 doppelt in meiner Tabelle vorkommt, bei beiden der Kundenname und die Menge die als erstes in der Tabelle auftaucht dazugespielt werden. Ich möchte jetzt allerdings, dass die zwei verschiedenen Kunden und Menge hinter den Ähnlichkeiten erscheinen.

Das Ergebnis soll dann sozusagen folgendermaßen aussehen:
Ähnlichkeiten Kundenname Menge
0,977 Müller 99
0,977 Schmitt 150

bisher habe ich es nur so hinbekommen dass hinter der Ähnlichkeit von 0,977 jedes mal Müller und Menge 99 steht.

Vielen Dank im Voraus für die Hilfe!
Liebe Grüße

 

[ms007]

Ich verstehe Dein Problem noch nicht so ganz:

Wenn doch zu der 0,9777 der Kunde Müller gefunden wird, muss doch in Deinen Tabellen die Zuordnung "Ähnlichkeit des Kunden" zu "Kunde" irgendwo vorhanden sein??

Schreib doch mal die Struktur Deiner Tabellen rein oder lade die relevanten Tabellen mit Musterdaten hoch.

 

[Ganzir]

Ich verstehe aus auch nicht so ganz, du berechnest in Tabelle A irgendwelche Ahnlickeiten und in Tabelle B stehen Kundennamen?

Nach welchem Suchkriterium sucht denn dein SVERWEIS um die Daten zusammen zu bringen?

 

[Knusperfloete]

2 Oktasekunden GOOGLE:

http://www.excelformeln.de/formeln.html?welcher=28

;-).

 

[OpamitKruecke]

Dazu ist der VERWEIS ungeeignet weil er immer den 1ten Wert zieht. Entweder man verwendet dazu zum Beispiel Index oder man muss Hilfsfunktionen einsetzen

 

[Isaaaaaabell]

A B C
1 Suchtabelle:
2 Ähnlichkeit Kunde Menge
3 0,977 Müller 99
4 0,853 Forster 350
5 0,974 George 15
6 0,956 Roos 85
7 0,977 Schmitt 150
8 0,657 Bauer 170
9 0,966 Haase 190
10
11
12 Ergebnistabelle
13 Ähnlichkeit Kunde Menge
14 0,977 Müller 99
15 0,977 Schmitt 150

Leider konnte ich die Datei nicht hochladen, da ständig eine Fehlermeldung kommt, aber ich denke so geht es auch. Hab die Zeilen und Spaltenüberscdhriften ergänzt.

Spalte A14 und A15 hab ich bereits über KGRÖSSTE Formeln mit Ähnlichkeiten füllen können
B12 und C14 bekomm ich mit SVERWEIS hin
Das Problem liegt in Spalte B15 und C15: Wenn ich hier einen SVERWEIS mach, bringt er mir wieder Müller und Menge 99.

Danke!

 

[Coldframe]

Hi,
hast du die Möglichkeit dir einen Eindeutigen "Key" zu basteln

z.b. wenn du in der Suchtabelle eine neue "A-Spalte" hinzufügst in der du zwei Zellen verkettest?

in B3 steht 0,997 und in C3 Müller
in A3 schreibst du "=B3&C3" Ergebnis ist dann "0,997Müller". Damit hättest du dann evtl. einen eindeutigen Key für den sverweis.

Das mal als Denkansatz.

 

[Chattermark()]

Kannst du da auf Datenbankfunktionen zurückgreifen?
Mit SVerweis sieht mir das arg kompliziert aus.

@Coldframe:
Dann muss man aber SVerweis beibringen, dass es mehrere relevante Zeilen gibt. Das verschiebt das Problem eigentlich nur.

 

[Isaaaaaabell]

Hallo Ganzir, über die Ähnlichkeiten wird der Name und die Menge hinzugespielt.

Habt ihr vielleicht eine Idee für so eine Hilfsfunktion? Das Beispiel mit dem Index in www.excelformeln.de hab ich mir bereits angeschaut. Leider bin ich damit nicht zurecht gekommen. Trotzdem danke für den Hinweis

Coldframe: die Idee mit dem eindeutigen Key hatte ich auch schon ansatzweise. Aber leider kann ich nur über die Ähnlichkeit suchen. Dieser Ansatz würde dann beispielsweise beider Menge funktionieren, wenn ich Ähnlichkeit & Kunde als eindeutigen Key nutze und dann dazu die Menge spiele. Aber wie bekomme ich den Kunden?

Danke für die vielen Hilfen bisher.
Leider klappt es bisher noch nicht.

Kann mir vll jemand speziell für meinen fall so eine Indexformel basteln. In diesem Gebiet bin ich ganz schlecht.

 

[Chattermark()]

Im Dunklen gestochert:

In Spalte A kommt die schon vorhandene kgrößte().
In Spalte B könnte bereich.verschieben(kgrößte();1;0)
In Spalte C könnte bereich.verschieben(kgrößte();2;0)

Die () müssen natürlich noch passend gefüllt werden.
Falls ich die Syntax richtig im Kopf und das Problem richtig verstanden habe...


Edit: So geht Bereich.verschieben():
http://office.microsoft.com/de-ch/excel-help/bereich-verschieben-funktion-HP010342739.aspx

 

[Isaaaaaabell]

Hallo,

Ich hab es endlich geschafft eine Beispieldatei anzuhängen.
Die Formel die ich bereits selbst hinbekommen hab, hab ich schonmal ergänzt.
Denke das Problem für den Rest dürfte anhand der Tabelle klar werden.

Allerdings meine Frage zu der Formel BEREICH.VERSCHIEBEN.
Wenn ich das richtig verstehe, wird in der Maximalwert beispielsweise gesucht und dann der Wert ausgeworfen der eine Zeile darunter steht. Hat jetzt nicht funktioniert. Da in meinem Fall der Extremwert auch ganz woanders in der Tabelle stehen kann.

Ich hoffe mit der Beispieltabelle klappt es jetzt & bedanke mich trotzdem schonmal für alle Hellferbisher...

 

[Chattermark()]

Ehm, ich merke gerade, dass kgrößte keine doppelten Werte ausgibt. Damit ist mein Post oben leider hinfällig.
*Weiterdenk*


Edit:
Warum kannst du nicht einfach die Anfangstabelle sortieren?
Das sieht dann so aus:

[TABLE="width: 240"]
[TR]
[TD]0,977[/TD]
[TD]Müller[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]0,977[/TD]
[TD]Schmitt[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]0,974[/TD]
[TD]George[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]0,966[/TD]
[TD]Haase[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]0,956[/TD]
[TD]Roos[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]0,853[/TD]
[TD]Forster[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]0,657[/TD]
[TD]Bauer[/TD]
[TD]170[/TD]
[/TR]
[/TABLE]

 

[Isaaaaaabell]

Das geht leider nicht, da die Ähnlichkeiten sich immer neu berechnen, je nachdem welche eingaben gemacht werden.
Also insgesamt ist das Modell sehr sehr komplex.
Ich bräuchte eine Lösung, wo gar nichts manuell sortiert werden muss, sondern alles automatisiert läuft. die Ähnlichkeiten sind auch nicht fest, die ändern sich ständig, und daraus sollen immer die zwei größen Ähnlichkeiten inklusive der Kundennamen und Mengen erscheinen.

 

[Chattermark()]

Das ist ein guter Punkt :/
Hier ist ein Thread, der diskutiert, wie man automatisch sortieren kann.
http://www.mrexcel.com/forum/excel-questions/54295-auto-sort-list-values-excel.html

Man kann auch die Bereichsnamen dynamisch vergeben, so dass die Anzahl der Zeilen keine Rolle spielt. Das ist dann aber knapp über meiner Gewichtsklasse.

 

[Knusperfloete]

Mal ne ketzerische Frage:

Hat das hier:

https://www.computerbase.de/forum/t...iehen-der-nicht-wie-in.1334566/#post-15515125

jemand wahr genommen????

Da steht doch die Lösung...

 

[Isaaaaaabell]

ja den Link hab ich mir angeschaut.
Aber bin nicht mit klar gekommen für meinen Fall.
Hab nie eine Lösung rausbekommen.

Kannst du mir für diesen Fall helfen?

 

[Chattermark()]

Mit einem Kriterium. Das kann man variieren, und mehrfach ausführen, aber nur, wenn die Anzahl der Kriterien Fälle im Voraus bekannt ist.
Wenn die Anzahl der je Suchwert herauskommenden Werte UND die Anzahl der Suchwerte an sich wie hier dynamisch unbekannt ist, kann man die Formel nicht untereinanderschreiben.

 

[TNM]

Das Problem in diesem Fall ist ja auch nicht der SVERWEIS, sondern die KGRÖSSTE. In deinem Beispiel gibt die bei den 2 höchsten Werten immer als Wert 1 aus und ignoriert den 2.ten Wert, da sie bei identischen Werten nur den ersten ausgibt.

Damit kannst du anhand der KGRÖSSTE nicht weitere Kriterien ermitteln. Darum benötigst du eine Hilfsspalte die den Wert eindeutig ermittelt (in deinem Beispiel eine Kombination aus Wert + Name).

Mit der Hilfsspalte kannst du dann eindeutig sortieren, da gibts viele Wege.

Das Beispiel von Knusperfloete ist eines davon.

 

[Isaaaaaabell]

Ok mir ist klar wie ich mit SVERWEIS dann anhand der eindeutigen Identifikation (Ähnlichkeit + Name) mit die Werte ziehen kann.
Aber wie komm ich von der Info Ähnlichkeit = 0,977 auf die Kombination Ähnlichkeit+Name.

Ihr müsst euch das so vorstellen, auf einer Eingabemaske gibt der Nutzer bestimmte Maße ein und erhält dann zu diesen Maßen die Kundennamen zu den Artikeln die diesem am ähnlichsten sind sowie die Menge. Aber wenn der Nutzer jetzt zwei Ähnlichkeiten mit 0,977 erhält, kann er die Kombination Ähnlichkeit + Name nicht machen, da ihm ja genau die Information Name fehlt - zumindest von dem zweiten wert - der erste wird ja mit SVERWEIS gefunden.

Ich bedanke mich für alle Ideen und Hilfsversuche.
Das ist alles sehr komplex und ich hoffe ich stell mich nicht zu blöd an.

 

[TNM]

Ich hab dir eine Lösung gebastelt OHNE Hilfsspalte, hier die Formel:

=INDEX(B$3:B$9;VERGLEICH(KGRÖSSTE(A$3:A$9+ZEILE($3:$9)%%%;ZEILE(A1));A$3:A$9+ZEILE($3:$9)%%%;0))

Da du so damit nix anfangen kannst hab ich dir ein xls-Sheet in den Anhang gepackt:

xls-Datei

HINWEIS WICHTIG:

Das ist eine Matrixformel, d.h. mit STRG+SHIFT+ENTER abschließen, also nach der Eingabe der Formel (oder Änderung) mit der Tastenkombination abschließen anstatt "nur" ENTER, sonst gibts ein #NV.