Excel - WENN, DANN

[K-Rider]

Hallo zusammen,

ich habe ein Problem mit meiner Selfcontrolling-Tabelle (Tabelle 1).
Der Gedanke ist folgender:
Nach Auswahl eines bestimmten Models (über dropdown-Zelle C5) und händischer Eingabe in EUR der Optionen (E5) möchte ich nun, dass in Zelle S5 entweder der Wert 100 oder der Wert 0 steht.
Das Problem ist, dass jedes Modell eine andere Grenze hat (sieht Tabelle 2).

Zum Beispiel (Zahlen fiktiv):
Verkauft wurde ein Macan GTS mit 8.000,- EUR Optionen (Mehrausstattung). Ab 5.000,- EUR gibt es 10 EUR Provision.
Wenn ich also einen Macan GTS in Zelle C5 auswähle und 8.000,- EUR Optionen händisch eintrage, dann soll bei S5 eine 10 erscheinen.
Wenn ich nun einen 911 Carrera (C6) mit 18.000,- EUR Optionen (E6) eintrage, dann soll in S6 eine 0 stehen, da die Grenze erst bei 20.000,- EUR anfängt.

Wie kann ich das mit einbinden, dass die Tabelle automatisch erkennt, welches Modell ausgewählt wurde und wo die Grenze bei dem jeweiligen Modell liegt?

Tabelle 1 (AE's-Tabelle)

Tabelle 2 (Modell-Tabelle)

Ich würde mich sehr freuen, wenn mir jemand helfen kann. Stehe total auf dem Schlauch.

Beste Grüße
Daniel

 

[steve1da]

Hola,
man könnte das super mit einem Sverweis() abgleichen, wenn denn die Modellnamen immer gleich wären. "Macan GTS" ist halt nicht "Macan". Gleich die Modellnamen an so dass sie überall gleich sind und vergleiche dann mit einem Sverweis() den Euro-Wert mit Tabelle2.
Edit: alternativ kannst du, wenn die Modelle genau so in beiden Tabellen stehen, also immer ein Modell+Leerzeichen+Typ im ersten Blatt stehen und nur das Modell im zweiten Blatt steht, das Leerzeichen mit Suchen() finden, von dieser Zahl 1 abziehen und das als Anzahl_Zeichen in der Links() Funktion für den das Suchkriterium im Sverweis benutzen.
Gruß,
steve1da

 

[asdwe]

Du musst deinen SVERWEIS oder WENN/DANN mit TEIL bzw. LINKS (Für MACAN..., also nur die ersten x Zeichen) und einem UND bzw. ODER verschachteln. Dann sollte das ganz einfach sein.

 

[steve1da]

@asdwe zeig das mal bitte an einem Beispiel.

 

[Bonanca]

über dropdown-Zelle C5

1. Wenn der Wert in Spalte C per Dropdown ausgewählt wird, warum sind dann nicht exakt die gleichen Werte aus dem DropDown auch in Tabelle 2 hinterlegt?
2. Wenn die Werte, aus welchem Grund auch immer, unterschiedlich sein sollen, dann ist es schlechte Praxis, beide Spalten trotzdem mit "Modell" zu bezeichnen. Das ist nur unnötig verwirrend und überhaupt erst ein Grund, warum du vor einem solchen Problem stehst.

Wie kann ich das mit einbinden, dass die Tabelle automatisch erkennt, welches Modell ausgewählt wurde und wo die Grenze bei dem jeweiligen Modell liegt?

Da du DropDowns benutzt und damit nur endlich viele Eingabefelder zur Verfügung hast, wäre meine nächste Frage, wieviele Anfangszeichen du benötigst, um ein "Modell" (s.o.) eindeutig zu identifizieren.
Am einfachsten erscheint es mir augenscheinlich, fix auf die ersten 3 Zeichen zu vergleichen. Andere Möglichkeiten wären Übersetzungstabellen im Hintergrund, oder, was am einfachsten ist, du benutzt für Modell und Modell identische Werte.

Um die ersten 3 Zeichen zu verwenden, wäre eine Möglichkeit bspw:

= INDEX( Modell-Tabelle; MATCH( LEFT(C5; 3); LEFT(Modell-Tabelle-SpalteA; 3); 0); 2)

Das zieht dann den entsprechenden Wert ab und lässt dann einen Größenvergleich zu.
Die Befehle an deine Excel-Sprache anpassen überlasse ich dir.

@asdwe zeig das mal bitte an einem Beispiel.

Ich weiß nicht was an seiner Lösung unverständlicher sein soll als an deiner.

 

[steve1da]

@Bonanca Ich habe dem TE a) das gleich geraten wie du, nämlich die Gleichheit beider Listen der Modelle und b) aufgezeigt, das er bei Nichteinhalten von a) das Suchkriterium dennoch findbar zu machen. asdwe hat nur verschiedene Funktionen mit "bzw" hingeworfen und gesagt, dass es ganz einfach ist. Ich habe die konkreten Funktionen genannt, die gebraucht werden.
Ich wette, er bekommt das mit seinen genannten Funktionen nicht hin, wollte mich aber eines besseren belehren lassen.

Um die ersten 3 Zeichen zu verwenden, wäre eine Möglichkeit bspw:
Ausprobiert hast du das aber auch nicht, oder? Denn damit kannst du nicht einfach so die ersten 3 Zeichen von Spalte A abfragen. Aber auch hier lass ich mich gerne anhand eines Beispiels eines besseren belehren.
Bei den vorgestellten Bildchen enthält man den UPE mit:
=SVERWEIS(LINKS(C5;FINDEN(" ";C5)-1);Tabelle2!A:B;2;0)
Ich bin gespannt auf deine Lösung.

 

[Bonanca]

Ausprobiert hast du das aber auch nicht, oder? Denn damit kannst du nicht einfach so die ersten 3 Zeichen von Spalte A abfragen.

Wieso nicht?
Ich habe vorhin extra getestet, ob der LEFT Befehl auf ein Array anwendbar ist. Was soll daran nicht funktionieren?

Ich bin gespannt auf deine Lösung.

Die Lösung die da quasi zu 80% fertig steht meinst du?
Aber ich kann dir morgen gerne ein Bildchen anfügen, wenn dir das mehr zusagt.

 

[steve1da]

Die Lösung die da quasi zu 80% fertig steht meinst du?

Muss man denn hier alles vorlutschen? Ein bisschen Eigeninitiative kann man erwarten, oder?
Ich habe deine Formel in einem deutschen Excel ausprobiert, #BEZUG kommt als Ergebnis.

Aber ich kann dir morgen gerne ein Bildchen anfügen, wenn dir das mehr zusagt.

Gerne, wie gesagt, ich kann mich ja auch irren.

 

[Bonanca]

Muss man denn hier alles vorlutschen? Ein bisschen Eigeninitiative kann man erwarten, oder?

Ich freue mich auf morgen.

 

[steve1da]

Hier dann mal die 100% Lösung:
=WENN(E5>SVERWEIS(LINKS(C5;FINDEN(" ";C5)-1);Tabelle2!A:B;2;0);10;0)
Bin gespannt.

 

[K-Rider]

Danke für Eure Tipps!!!

Speziellen Dank geht an steve1da für die 100%ige Lösung meines Problems.
Ehrlicherweise hätten meine Excel-Fähigkeiten dafür nicht gereicht.

Viele Grüße
Daniel

 

[steve1da]

Danke für das Feedback

 

[Bonanca]

Ich habe deine Formel in einem deutschen Excel ausprobiert, #BEZUG kommt als Ergebnis.

Keine Ahnung was du falsch machst, aber die Formel funktioniert einwandfrei:

Das ganze lässt sich sogar analog als einzelnes Array schreiben:

Und das ganze ebenso auf Deutsch:

Soviel zu der Lösung.
So, und jetzt mal Tacheles:
Es ist absolut arrogant und anmaßend von dir, dich hinzustellen und zu sagen

Muss man denn hier alles vorlutschen? Ein bisschen Eigeninitiative kann man erwarten, oder?

während du selber alles vorgelutscht haben möchtest (oder was war dir an der Formel aus Post #5 zu ungenau?) und gleichzeitig überhaupt keine Eigeninitiative zeigst, was Fehlerursachenforschung und Fehlerbehebung angeht. Dir ist schon klar, dass, wenn Excel einen Fehler in einer Formel hat, du dir Schritt für Schritt anzeigen lassen kannst, wie Excel vorgegangen ist und damit exakt nachverfolgen kannst, an welcher Stelle der Fehler entsteht? Umso peinlicher wäre mir das an deiner Stelle jetzt, wo sich herausgestellt, dass es bereits am Kopieren einer Formel gescheitert ist.

Weil ich an dieser Stelle, wie bereits erwähnt, keine Eigeninitiative von dir erwarte und dir alles vorgelutscht werden muss: BSP:

Dein Verhalten hat an genau der Stelle gezeigt, dass du absolut keine Ahnung von Fehlersupport, Bugtickets oder Fehlerbeschreibungen im Allgemeinen hast.
Hättest du mir nämlich einfach mal direkt gesagt, was du da (falsches) in Excel eingegeben hast, hätte ich dir von Anfang an sagen können, dass es sich um einen Anwenderfehler ist. Deswegen gehört zu einer vernünftigen Fehlerbeschreibung immer eine möglichst exakte Beschreibung von Input+Output.
Und nein, ein

Ich habe deine Formel in einem deutschen Excel ausprobiert

ist offensichtlich nicht ausreichend, denn offensichtlich scheitert es bei dir ja selbst daran.


Also: Fang an dir öfter an die eigene Nase zu fassen und beherzige vllt mal deine eigenen Ratschläge (Eigeninitiative und so.). Du scheinst es nötiger zu haben als ich.

 

[steve1da]

I apologize!
Nennen wir es klassisches Missverständnis. Das "vorlutschen" war in Richtung des TE gemeint und auch da eher im allgemeinen. Heutzutage werden nur noch fertige Lösungen erwartet und Eigeninitiative kommt so gut wie gar nicht mehr. Ist jedenfalls mein Eindruck. Das "vorlutschen" war absolut nicht an dich gerichtet, ich hatte dein Zitat schlicht falsch verstanden.
Und ja, ich habe deine Formel falsch eingegeben, daher bekam ich stets #BEZUG. Ich sagte aber auch, dass ich mich irren kann ;-)
Deine Matrixformel funktioniert genau so wie meine Nicht-Matrixformel, ich habe mich geirrt und mich falsch ausgedrückt! Nochmal: sorry!

 

[K-Rider]

Hallo zusammen,

die Tabelle hat auf meinem Laptop zu Hause wunderbar funktioniert und tut dies immer noch.
Ich habe die Datei zu mir auf die Arbeit geschickt und mit Excel 2010 geöffnet.
Leider ändert Excel die Formeln in Tabelle 1 Spalte D automatisch in =_xlfn.XLOOKUP(C5; 'Modell-Tabelle'!$A$2:$A$108;'Modell-Tabelle'!B2:B108) und in der Zelle steht #NV.
Ändere ich jetzt das Modell z. B. in C6, ändert sich D6 in #NAME?.

Woran meint ihr könnte das liegen?
Doch sicher nicht, weil ich Microsoft 365 Excel auf dem Laptop habe und auf der Arbeit Excel 2010 installiert ist, oder etwa doch?
XLOOKUP ist doch das gleiche wie XVERWEIS.

Ich hoffe es hat jemand Zeit und Lust es mir "vorzulutschen" (mache nur Spaß)

Vielen Dank und beste Grüße

 

[steve1da]

XVerweis kommt doch hier in keiner der vorgeschlagenen Formeln vor.
Sverweis() oder Index() waren es hier.

 

[K-Rider]

aber wieso funktioniert es denn dann zu Hause an meinem Laptop und auf der Arbeit nicht?
Ich verstehe es nicht.
Und wieso wird es einfach in xlookup geändert?

VG

 

[Frightener]

Ist das denn beides die selbe Datei, oder hast du sie nur zu Hause "nachgebaut"?

BTW: Jetzt weiß ich endlich, was mein Verkäufer an den Extras verdient

 

[Bonanca]

Woran meint ihr könnte das liegen?

#NV ist bei LOOKUP üblicherweise der Fehler, der kommt, wenn der suchwert nicht in der Suchtabelle zu finden ist.

#NAME? deutet auf einen Fehler im Funktionsnamen hin, d.h. Excel erkennt deinen Befehl nicht.

Das ganze kannst du übrigens auch selber feststellen, wenn du auf das Warndreieck links neben einer ausgewählten Fehlerzelle klickst und dir dort die Berechnungsschritte anzeigen lässt.

Leider ändert Excel die Formeln in Tabelle 1 Spalte D automatisch in =_xlfn.XLOOKUP(C5; 'Modell-Tabelle'!$A$2:$A$108;'Modell-Tabelle'!B2:B108) und in der Zelle steht #NV.

Und wie sieht die Formel bei dir auf dem Laptop aus? Hast du die einfach Mal verglichen? Insbesondere für uns wäre die Formel auch Mal interessant zu wissen, weil du offensichtlich nicht die Lösung aus Post #10 1:1 übernommen hast.

Der erste Google-Suchvorschlag liefert übrigens https://support.microsoft.com/de-de...ngezeigt-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025
Andere gesagt: Excel 2010 scheint XLOOKUP nicht zu kennen.

Da XLOOKUP nichtmal in excel 2016 und 2019 existiert lehne ich mich mal ganz stark aus dem Fenster und behaupte einfach Mal, dass XLOOKUP in excel 2010 erst Recht nicht existiert.
https://support.microsoft.com/de-de/office/xverweis-funktion-b7fd680e-6d10-43e6-84f9-88eae8bf5929



Die Lösung:
Verwende einfach VLookup oder Index&Match, wie es dir in diesem Thread auch vorgeschlagen wurde, und nicht XLookup.

 

[K-Rider]

Ist das denn beides die selbe Datei, oder hast du sie nur zu Hause "nachgebaut"?

BTW: Jetzt weiß ich endlich, was mein Verkäufer an den Extras verdient

das ist die selbe Datei. Ich habe sie mir per E-Mail auf die Arbeit geschickt.

Haha
natürlich habe ich die Zahlen geändert. ;-) Will ja nicht, dass du weißt, wie sehr dein Verkäufer am Hungetuch nagt.

Ergänzung (10. März 2023)

#NV ist bei LOOKUP üblicherweise der Fehler, der kommt, wenn der suchwert nicht in der Suchtabelle zu finden ist.

#NAME? deutet auf einen Fehler im Funktionsnamen hin, d.h. Excel erkennt deinen Befehl nicht.

Das ganze kannst du übrigens auch selber feststellen, wenn du auf das Warndreieck links neben einer ausgewählten Fehlerzelle klickst und dir dort die Berechnungsschritte anzeigen lässt.



Und wie sieht die Formel bei dir auf dem Laptop aus? Hast du die einfach Mal verglichen? Insbesondere für uns wäre die Formel auch Mal interessant zu wissen, weil du offensichtlich nicht die Lösung aus Post #10 1:1 übernommen hast.

Der erste Google-Suchvorschlag liefert übrigens https://support.microsoft.com/de-de/office/problem-vor-einer-formel-wird-das-präfix-xlfn-angezeigt-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025
Andere gesagt: Excel 2010 scheint XLOOKUP nicht zu kennen.

Da XLOOKUP nichtmal in excel 2016 und 2019 existiert lehne ich mich mal ganz stark aus dem Fenster und behaupte einfach Mal, dass XLOOKUP in excel 2010 erst Recht nicht existiert.
https://support.microsoft.com/de-de/office/xverweis-funktion-b7fd680e-6d10-43e6-84f9-88eae8bf5929



Die Lösung:
Verwende einfach VLookup oder Index&Match, wie es dir in diesem Thread auch vorgeschlagen wurde, und nicht XLookup.

Vielen Dank für die Erklärung und Tipps wo man das selbst feststellen kann.

Ich habe mich zu undeutlich ausgedrückt. Zum weiteren Verständnis:
bei der XLOOKUP-Formel geht nicht um diesen Optionsbonus (ob 0 oder 10), sondern darum, dass Excel keine UPE mehr zuweisen kann, wenn ich in der Dropdownliste das Modell auswähle.

Ziel ist es, dass ich das Modell auswähle in Spalte C (über Dropdownliste) und mir in Spalte D (UPE netto) angezeigt wird.

Dazu habe ich in der Registerkarte "Modell-Tabelle" eine einfache Modellübersicht gebastelt, wo hinter jedem Modell die aktuelle UPE steht. (Also Spalte A stehen die Modelle und in Spalte B stehen die dazugehörigen Preise [UPE]).

Das kuriose ist, dass ich die Datei verschickt habe und wenn ich Sie auf der Arbeit öffne, =_xlfn.XLOOKUP... anzeigt und nicht wie zu Hause auf dem Laptop, =XVERWEIS...
Zu Hause funktionieren die Formeln in Spalte D (=XVERWEIS(C5; 'Tabelle 2'!$A$2:$A$108;'Tabelle 2'!$B$2:$B$108).
Das verstehe ich einfach nicht