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.
Danke schon mal für Eure Hilfe :)
Liebe Grüße
sfo
https://www.ms-office-forum.net/forum/sh...p?t=360015
Hi,
für dein erstes Beispiel:
Code:
=SUMMENPRODUKT(($A$2:$A$15=$F$2)*($B$2:$B$15=F1)*($C$2:$C$15))
Hallo sfo,
wenn du auf die verbundenen Zellen verzichtest (und die Firmennamen immer gleich schreibst
), ginge es zB so:
Zitat:{
=INDEX($A$5:$G$8;VERGLEICH($D16;$A$5:$A$8;0);VERGLEICH(42;SPALTE($A$4:$G$4)/(($A$4:$G$4=F$15)*($A$3:$G$3<=$E16));1))
}
Achtung! Matrixformel. Die geschweiften Klammern werden nicht mit eingegeben, sondern jede Bearbeitung mit Strg+Shift+Enter abgeschlossen.
Hallo WillWissen,
leider bekomme ich auch mit der Formel einen Bezugsfehler
Aber vielen lieben Dank für deine Hilfe.
LG
sfo
Hallo Helmut,
das sieht sehr gut aus :) Wollte aus Gründen der Übersichtlichkeit die Menge jeweils nur 1 x schreiben, aber so geht es auch :)
Vielen Dank!!!
LG
sfo
Hi,
deinen Bezugfehler kann ich nicht nachvollziehen:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E | F |
| 1 | Menge | Format | Preis | | Format | 90 x 90 |
| 2 | 5.000 | 80 x 80 | 60,00 € | | Menge | 7.000 |
| 3 | 5.000 | 90 x 90 | 65,00 € | | Preis | 45 |
| 4 | 6.000 | 80 x 80 | 50,00 € | | ungefähre Übereinstimmung funktioniert gar nicht | |
| 5 | 6.000 | 90 x 90 | 55,00 € | | | |
| 6 | 7.000 | 80 x 80 | 40,00 € | | Format | 80 x 80 |
| 7 | 7.000 | 90 x 90 | 45,00 € | | Menge | 10.000 |
| 8 | 8.000 | 80 x 80 | 30,00 € | | Preis | 10 |
| 9 | 8.000 | 90 x 90 | 35,00 € | | Ab Menge 9.000 funktioniert die ungefähre Übereinstimmung nicht mehr | |
| 10 | 9.000 | 80 x 80 | 20,00 € | | | |
| 11 | 9.000 | 90 x 90 | 25,00 € | | | |
| 12 | 10.000 | 80 x 80 | 10,00 € | | | |
| 13 | 10.000 | 90 x 90 | 15,00 € | | | |
| 14 | 20.000 | 80 x 80 | 5,00 € | | | |
| 15 | 20.000 | 90 x 90 | 7,50 € | | | |
| Zelle | Formel |
| F3 | =SUMMENPRODUKT(($A$2:$A$15=$F$2)*($B$2:$B$15=F1)*($C$2:$C$15)) |
| F8 | =SUMMENPRODUKT(($A$2:$A$15=$F$7)*($B$2:$B$15=F6)*($C$2:$C$15)) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo Helmut,
danke nochmal für deine Hilfe.
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.
Beispieldatei nochmal anbei :)
VIelen Dank schon mal :)
Hallo sfo,
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.
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| I | J | K | L | M | N | O | P | Q | R | S |
| 3 | | 25 x 140 | 25 x 140 | | 30 x 105 | 30 x 105 | | 35 x 134 | 35 x 134 | | |
| 4 | | Firma A | Firma B | Abweichung | Fima A | Firma B | Abweichung | Firma A | Firma B | Abweichung | |
| 5 | 1000 | 100,00 € | 200,00 € | 50,00% | 150,00 € | 250,00 € | 40,00% | 220,00 € | 310,00 € | 29,03% | |
| 6 | 1500 | 600,00 € | 500,00 € | -20,00% | 650,00 € | 550,00 € | -18,18% | 350,00 € | 460,00 € | 23,91% | |
| 7 | 2000 | 400,00 € | 300,00 € | -33,33% | 450,00 € | 350,00 € | -28,57% | 420,00 € | 540,00 € | 22,22% | |
| 8 | 3000 | 1.500,00 € | 1.200,00 € | -25,00% | 1.600,00 € | 1.300,00 € | -23,08% | 1.700,00 € | 1.900,00 € | 10,53% | |
| 9 | | | | | | | | | | | |
| 10 | | | | | | | | | | | |
| 11 | | Format | Menge | Firma A | Firma B | | | | | | |
| 12 | | 25 x 140 | 1000 | 100,00 € | 200,00 € | | | | | | |
| 13 | | 25 x 140 | 1001 | 100,00 € | 200,00 € | | | | | | |
| 14 | | 35 x 134 | 1777 | 350,00 € | 460,00 € | | | | | | |
| 15 | | 48,3 x 201,1 | 2145 | | | | | | | | |
| 16 | | | | | | | | | | | |
| Zelle | Formel |
| L12 | =WENNFEHLER(AGGREGAT(14;6;INDEX($5:$9;;VERWEIS(9;1/($J$3:$Z$3=$J12)/($J$4:$Z$4=L$11);SPALTE($J1:$Z1)))/($I$5:$I$9<=MAX($K12;1000));1);"") |
| Zelle | Gültigkeitstyp | Operator | Wert1 | Wert2 |
| J12 | Liste | | =$A$5:$A$8 | |
| J13 | Liste | | =$A$5:$A$8 | |
| J14 | Liste | | =$A$5:$A$8 | |
| J15 | Liste | | =$A$5:$A$8 | |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo sfo,
in meinem letzten Lösungsvorschlag war die Funktion SPALTE und der Start das Indexbereiches ab Spalte A gar nicht notwendig.
Verkürzt sieht die Formel jetzt so aus:
Code:
{
=INDEX($B$5:$G$8;VERGLEICH($D16;$A$5:$A$8;0);VERGLEICH(42;1/(($B$4:$G$4=F$15)*($B$3:$G$3<=$E16));1))
}
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:
Code:
{
=INDEX($J$5:$R$8;VERGLEICH($J16;$I$5:$I$8;1);VERGLEICH(42;1/(($J$4:$R$4=K$15)*($J$3:$R$3=$I16));1))
}
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.
Hallo Helmut,
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