Hallo Kristina,
interessante Aufgabe, allerdings hat sich mein Excel geweigert, über die mit der Formel ermittelten
Zelladresse den entsprechenden Wert aus Mappe2 zu holen. Insofern ist meine Lösung nicht perfekt.
Aber mit einem kleinen Kunstgriff kannst du alle benötigten Werte ermitteln.
Der Kunstgriff besteht darin, dass du die Spalten Gewicht und PLZKürzel komplett kopierst
(auch wenn in den Zeilen über bzw. unter der Tabelle noch andere Inhalte stehen sollten) und
in die Mappe2, die mit den Preisstaffeln einfügst (du kannst natürlich auch die Preisstaffel in die
Mappe1 kopieren). Oder überhaupt ein neues Tabelleblatt nehmen.
Ziel der Aktion, beide Tabellen sind in einem Tabellenblatt.
Annahme die Tabelle mit der Preisstaffel beginnt in E13:
E F G H usw
13 plzkurz 0 100 200
14 12 17,2 21,3 28,7
15 23 17,5 21,8 29,1
16 24 16,95 20,95 28,95
17 31 23,55 28,4 32,99
18 26 14,2 18,95 25,88
19 27 19,99 25,4 30,95
Die Spalte E mit den plzKürzeln muß nicht geordnet sein.
Die Zeile 13 mit den Gewichtsgrenzen ist
wahrscheinlich geordnet (macht Sinn), jedenfalls ich bin davon ausgegangen. Die Spaltenköpfe sollen nur
einen Wert enthalten (den unteren Wert der Gewichtsgruppe), keine Von-Bis-Angabe! Der untere Wert ist notwendig,
um auch die Gewichte unter der 1. Grenze (100) zu verarbeiten, da sonst ein Fehler ausgewiesen wird. Damit
hat die 1. Preissppalte die Überschrift 0.
Spalte A enthält die Gewichte, für die du den Preis ermitteln willst, also die Spalte A oder die ca. 21000 Zellen
mit den Gewichten in die Spalte A einfügen.
Spalte B bekommt die dazugehörenden plzKürzel. Also auch am besten die komplette Spalte einfügen.
Füge jetzt diese Formel
=INDIREKT(ADRESSE(ZEILE($E$13)+VERGLEICH(B5;$E$14:blush:E$23;0);SPALTE($E$13)+VERGLEICH(A5;$F$13:blush:H$13;1);4;1:wink:)
in Zeile 5 in eine freie Spalte ein. Nun die Formel soweit wie benötigt nach oben und unten ausfüllen.
Vor Einfügen der ermittelten Werte in die Mappe1 die Spalte markieren, kopieren, Inhalte einfügen, Werte,OK.
Fertig.
Hier ist übrigens die Formel, die direkt von Mappe1 aus Mappe2 den Wert in Mappe1 holen sollte. Einzusetzen
in Zeile14 (Falls sich jemand daran versuchen möchte)
=(ADRESSE(ZEILE([Mappe2.xls]Tabelle1!$E$13)+VERGLEICH(B14;[Mappe2.xls]Tabelle1!$E$14:blush:E$23;0);SPALTE([Mappe2.xls]Tabelle1!$E$13)+VERGLEICH(A14;[Mappe2.xls]Tabelle1!$F$13:blush:H$13;1);4;1:wink:)
Die Zelladresse hat sie perfekt geliefert, nachdem ich die Formel aber in INDIREKT verpacken wollte, um den Zellinhalt
zu holen, habe ich eine Fehlermeldung erhalten. Allerdings läßt sich nicht jeder Text mit INDIREKT in einen Bezug
umwandeln. Ob das der Grund war oder meine mangelnde Aufmerksamkeit - keine Ahnung.
Lösung zum Ermitteln der Adresse der gesuchten Zelle:
mit =VERGLEICH(B14;$E$14:blush:E$23;0) wird die Nummer der Zeile mit dem passenden plzKürzel im Vektor ermittelt
mit =VERGLEICH(A14;$F$13:blush:H$13;1) die Nummer der Spalte für das passende Gewicht innerhalb der Gewichtstaffel
der Rest ist einfach, in
=Adresse(Zeile; Spalte; Abs; A1; Tabellenname) die ermittelten Ausdrücke einsetzen:
für Zeile: die linke obere Ecke der Gewichtsstaffeltabelle (B13 Schnittpunkt von Spalten- und Zeilenköpfen)+
den mit VERGLEICH ermittelten Ausdruck für Zeilenanzahl
für Spalten entsprechend
Abs: 4 (Zeile und Spalte relativ)
A1: 1 oder WAHR für A1 Schreibweise
ergibt die Zelladresse
das Ganze in INDIREKT für den Zellinhalt einsetzen.
Klappt leider nur innerhalb eines Tabellenblattes (bei mir)
Ich freu mich über Tipps zur Komplettierung der Aufgabenstellung.
Liebe Grüße
Marion
. Wenn Du noch Hilfe brauchst oder die Variante von Alex lösen nutzen willst, müsstest Du schon mal einige Zeilen (aber bitte mit und aufführen, damit wir eine Chance haben, Dir die Formel in die Tastatur zu diktieren.