09.05.2019, 13:40 (Dieser Beitrag wurde zuletzt bearbeitet: 09.05.2019, 14:17 von sfo.)
Hallo Zusammen,
ich habe folgendes Problem mit einer Kombi aus Index- und Vergleichfunktion (Beispieldatei anbei)
Sobald ich eine andere Menge als 1.000 (bei der Menge 1.000 funktioniert die Abfrage einwandfrei) in die Zelle E16 eintrage, erhalte ich einen Bezugsfehler in den Zellen F16 und G16. Kann mir jemand sagen, was an meiner Funktion falsch ist?
Außerdem wäre es schön, wenn die Funktion auch mit der ungefähren Übereinstimmung funktionieren würde. Also, wenn ich den Preis für 1.200 suche, mir auch der Preis für 1.000 ausgegeben wird.
Achtung! Matrixformel. Die geschweiften Klammern werden nicht mit eingegeben, sondern jede Bearbeitung mit Strg+Shift+Enter abgeschlossen.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen." Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • sfo
Wie müsste die Formel lauten, wenn ich die Tabelle transponiere und noch eine Spalte einfüge? Ich bekomme immer die Meldung, dass durch 0 dividiert wird.
ich bin zwar nicht Helmut, hab mich aber mal Deiner neuen Aufgabe angenommen.
Einge Deiner Angaben sind nicht eindeutig. Da Du aber von einer nun transponierter Tabelle schreibst, geh ich davon aus, dass Du nun die Daten in I3:R8 meinst!?!
Es fehlen in dieser Datentabelle jedoch Angaben für das Format: "48,3 x 201,1" und außerdem ist unklar, ob Du für eine beliebige Datenmenge wieder den Preis ermitteln willst, der der nächstkleineren Menge zugewiesen ist. Ich hab dieses nachfolgend zunächst angenommen. Eine andere Ergebnisermittlung ist natürlich auch möcglich, bedarf aber dann Deiner entsprechenden Angaben, wie diese erfolgen soll.
In P4 hattest Du übrigens auch "Fima A" anstelle "Firma A" geschrieben, was ich korrigiert habe.
Die Ergebnisliste hab ich der leichteren Übersicht hier im Beitrag halber gegenüber in Deiner Beispiedatei etwas verschoben. Meine dortige Formel in L12 ist nach unten und rechts ziehend kopieren. In Deiner Datei kannst Du diese Liste natürlich wieder an die von Dir gwünschte Stelle verschieben.
Ich nehme an, dass Werners Formel auch richtig ist. Wenn man aber meinen obigen Vorschlag auf die transformierten Daten umschreibt, ist die Formel so richtig:
Achtung! Matrixformel. Die geschweiften Klammern werden nicht mit eingegeben, sondern jede Bearbeitung mit Strg+Shift+Enter abgeschlossen.
Neben den geänderten Bezügen muste bei der Ermittlung der Zeile der dritte Parameter der Vergleichsfunktion auf 1 gesetzt werden und bei der Ermittlung der Spalte im zweiten Teil des Divisors das "<=" durch ein "=" ersetzt werden.
ps In dieser neuen Version ist anstelle der 42 jede andere Zahl >= 1 auch möglich.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen." Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • sfo
natürlich ergibt Deine Formellösung (im Normalfall) auch die gleiche Ergebnisse wie mein Vorschlag. In Deiner Formel könnte noch ein Klammerpaar eingespart werden, wenn die Bedingungsprüfung konsequent über Division vorgenommen wird. In den früheren Excelversionen war die MSO-Hilfe für VERGLEICH() auch noch besser/umfassender, wonach man auch das 3. Argument (die ;1) inklusive des vorangehenden Semikolons hätte einsparen können.
In meiner Formel hab ich darüber hinaus bewusst den Lösungsteil mit WENNFEHLER() geklammert und auch Mengen kleiner 1000 berücksichtigt. Beides kann in Deiner Formel analog noch eingebaut werden (für letzteres anstelle ... VERGLEICH($J16;... eben ... VERGLEICH(MAX($J16;1000);...)
Ich verzichte aber weiterhin wo möglich prinzipiell auf klassische Matrixformellösungen, u.a. deswegen weil eine solche den spez. Formelabschluss notwendig macht. Diese Lösungsform hat sich auch anderweitig mittlerweile bewährt.
Moin Werner! Lediglich ein paar allgemeine Anmerkungen: Mittlerweile verzichtet Excel auf den speziellen Abschluss per Strg+Shift+Enter (dies trifft nicht auf Nicht-Abo-Versionen zu)
Ist einerseits nur folgerichtig, denn schließlich ist selbst SUMME() eine Matrix-Formel, AGGREGAT() oder SUMMENPRODUKT() erst Recht. Andererseits ertappe ich mich ab und an dabei, dass ich den wichtigen Hinweis schlicht vergesse, weil ich nicht (mehr) daran denke.
Gruß Ralf
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
), ginge es zB so:
