5k Aufrufe
Gefragt in Tabellenkalkulation von jeremy Mitglied (677 Punkte)
Hallo Leute,

an dieser Stelle mal eine obligatorische Frage.
Ich bin gerade dabei eine Matrix für eine Equipmentliste zu erstellen. Dabei möchte ich gerne den Preis hinterlegen, was aber nicht höchste Priorität hat, sondern die Kummulierung der Zahlen steht an erster Stelle...

Jetzt habe ich mir gedacht ich kann ja für jedes Item trotzdem einen Preis hinterlegen, was mir in Zukunft die Arbeit erleichtern würde. Zum Verständnis, ich habe circa 50 verscheidene Sheets in denen immer die selben Items an selber Stelle vorkommen.
Mein Gedanke war, ich erstelle ein Sheet mit allen Items (circa 200) und aus allen anderen Sheets greife ich auf dieses "Mastersheet" zurück...

Nun ist es aber so, das ich dieses Sheet in unterschiedlichsten Ländern benötige, also bspw. in Deutschland, in Südafrika und augenblicklich in Brasilien.
Ich könnte ja auf der "Mastersheetseite" neben den Artikeln mehrere Spalten für unterschiedliche Währungen anlegen, auf die dann im besten Fall zurückgegriffen werden soll.

Jetzt kommt reine Theorie, noch keine Idee ob und wie das ausgeführt werden kann...

Auf dem ersten Blatt gibt es in Pull-Down-Menü mit unterschiedlichen Währungen. Dort klickt man einmal die benötigte Währung und alle Seiten greifen dann im "Mastersheet" auf die entsprechende Spalte neben den Items auf den Preis zu.
Falls das schon, wie auch immer gehen sollte, gibt es noch das Problem, das ich die Zellen ja jetzt eigentlich in bspw. Euro-Format formatieren würde. Dies müsste ja dann bei allen Zellen die so formatiert sind auch mit geändert werden...


Jetzt die Frage aller Fragen...

1. Geht so etwas oder zumindestens ein Teil davon ohne Makro ??

2. Wie würdet ihr an die Sache rangehen und wie würdet ihr vorschlagen, wie ich die Matrix aufbauen soll ??



Wie immer vielen Dank im Voraus für Eure Hilfe...


Jeremy

16 Antworten

0 Punkte
Beantwortet von saarbauer Profi (15.6k Punkte)
Hallo,

ich bin mir nicht ganz sicher, aber nach meiner Meinung müsste es mit einem Sverweis() machbar sein.

In Spalte a deine Items un in Zeile 1 deine Länder. In dem Pulldownmenü ist festzustellen welche Spalte für deine Währung genommen werden soll.

Für den Rest wäre eine Beispieltabelle nicht schlecht.

Gruß

Helmut
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Jeremy,

die Auswahl Items und Preis kannst du ja über Gültigkeit regeln. Wie viele Währungen hast du denn? Wenn es nur ein paar sind, kannst du das über eine Wenn-Abfrage regeln. Aber auch eine Hilfsspalte in deinem Mastersheet wäre möglich.

Ich habe dir mal eine kleine Beispieldatei erstellt: Beispiel Jeremy

Gruß

M.O.
0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Jeremy,

falls Du es so meinst, wie von @ M.O. dargestell, würde ich anstatt einer WENN-Abfrage die Funktion INDEX() in Verbindung mit VERGLEICH() einsetzen.

Das hat den Vorteil, dass bei mehr als zwei Währungen keine Formel geändert werden muss.

Beispiel

Gruß
Rainer
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo Leute,

ich bin nun soweit, das ich die ersten Seiten der Matrix fertig habe.

Ich erkläre mal meine zwei Baustellen:

1. Die Matrix ist das Sheet "EQ_Sheet". Als erstes benötige ich Hilfe für eine Formel. Dafür muss man sich das zweite Sheet "Mengengerüst 2010" anschauen. Als Beispiel ist in Zelle P48 eine Formel bei der Helmut mir geholfen hat. Es geschieht dort eine Abfrage und ein Vergleich. Ähnlich benötige ich es jetzt im Sheet "EQ_Sheet". Dafür erkläre ich kurz den Aufbau des Sheets:

Der erste Reiter "Overview" ist uninteressant, der zweite "Templates all" dient für mich als Vorlage, darauf hin verweise ich bei Begriffen und Bezeichnungen. Der dritte ist meine Vorlagen Matrix. Und der vierte ist ein Beispiel für circa 100 weitere Reiter die dann noch folgen werden.
Im vierten Reiter "BHZ L-1" ist ein Level in einem Stadion zu sehen. Die Equipment-Teile zieht er sich aus "Templates Items".

Nun soll folgendes passieren:

im Reiter "BHZ L-1" als Beispiel in Zelle H16 soll er überprüfen:

- was ist das Item ? (B16 - starter fork premium)
- was ist die Kategorie der Gäste ? (H3 = PRI-SUI)
- dann in Reiter "Templates Items" schauen,
Suchbereich R16:AK16, wo ist die Kategorie PRI-SUI, und dann die Ratio (in diesem Fall wäre es 3) multipliziert mit der Personenzahl aus Reiter "BHZ L-1" Zelle H12 (20 Pax/Personen).

Diese Formel möchte ich auf alle Zellen ziehen können.


2. Und jetzt noch die Frage, wo ich aber nicht weiß, ob es funtioniert, ohne Makro...
Ich hatte ja nach dem Währungswechsel angefragt. Im Reiter "BHZ L-1" ist als Beispiel in Spalte AA die Kostenrechnung hinterlegt. Dafür soll er sich den Preis nehmen, welcher im Reiter "Templates Items" hinterlegt (Spalte J - P) ist.

So und hier die Frage ob es geht...

Im Reiter "Templates Items habe ich in Zelle B6 ein Drop-Menü hinterlegt. Idee ist, das ich einmal am Anfang nur in diesem Blatt, in dieser Zelle festlege, in welcher Währung alle Seiten gerechnet werden sollen. Als Beispiel in der der Spalte AA würde er rechnen:

Spalte Z * Spalte AA

und beim Preis sol er den Preis aus Spalte J-P nehmen, der identisch ist mit der Währung aus Zelle B6.



So, das sind meine beiden Baustellen, es wäre toll, wenn ich wie immer mit Eurer Hilfe rechnen könnte.
Da es sich hierbei noch um ein Muster handelt, kann ich auch gerne noch Änderungen vornehmen, wenn dies die Formeln vereinfachen würde...



LG Jeremy


http://www.netuploaded.com/4ipnpum0vu2s/EQ-Sheet.xlsx.html


http://www.netuploaded.com/basvunr20kvl/Mengengeru%CC%88st_2010_-_Vers._2.6_-_Stand_17.06.2010_-_TEST.xls.html
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Jeremy,

zuerst ein Hinweis: in deinem Sheet "Templates Item" musst du bei den Preisen noch die Überschriften bei den Währungen anpassen. (Ich weiß ist ja noch Entwurf, nur damit beim Testen auch die Formeln funktionieren).

Für die Zelle H15 in deinem Sheet BHZ-L1 nimm folgende Formel:

=SVERWEIS(B16;'Templates Items'!$B$15:$AK$208;VERGLEICH(H3;'Templates Items'!$B$7:$AK$7;0);FALSCH)*H12

In AA16 schreibe diese Formel:

=RUNDEN(SVERWEIS(B16;'Templates Items'!$B$15:$P$208;VERGLEICH('Templates Items'!$B$6;'Templates Items'!$B$12:$P$12;0);FALSCH)*Z16;2)

Ich habe hier RUNDEN eingebaut, da die Preise dann automatisch auf 2 Nachkommastellen gerundet werden.

Gruß
M.O.
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo M.O.,

als erstes einmal vielen Dank für deine Hilfe !!!
Leider bin ich bei beiden Lösungen deinerseits nicht von Erfolg gekrönt wurden, habe alles nach deinen Anweisungen eingefügt und auch die Spaltenüberschrifzten geändert.

Du hast es doch sicher in meinem Muster auch ausprobiert..., lade mir doch mal deine Version hoch, vielleicht funktioniert es ja da...


Vielen Dank


Jeremy
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo M.O.

sorry, da habe ich die Pferde zu früh scheu gemacht, ich habe einfach noch ein paar "Sperr $" einfügen, so funktioniert schonmal die erste Formel fast korrekt...

Auch die zweite läuft schon, aber noch mit kleinen Mängeln.

Also auf diesem Wege schonmal danke für deine tolle Arbeit, vielleicht kannst du mir beim Rest auch nochmal zur Hand gehen...

1. Formel

Habe ich so angepasst, das ich sie über alle Felder ziehen kann. Einziges Problem was ich habe, ist wenn in die Formel ein leeres Feld kommt, dann wird das Ergebnis automatisch #NV
Hier müsste in die Formel noch integriert werden, dass er nur rechnen soll, wenn in Spalte B auch etwas steht.

2. Formel

Ich habe mal einen Testaluf gemacht. die Rechnung stimmt.
Im Augenblick macht er es so, das er den Europreis sucht und dann
mit dem Wechselkurs umrechnet. Alles korrekt. Für mich wäre es besser, wenn er den Preis aus der ganzen ganzen Tabelle h´nehmen würde. Als Beispiel: In Zeile 23 ist Dessert Spoon premium, der Euro Preis ist 0,10 Euro, der Dollarpreis ist in der Zelle daneben 0,13 $.
So hätte ich die Möglichkeit auch einzelne Preise zu ändern, die nicht 1:1 dem Wechselkurs entsprechen...
Aber prinzipiell funktioniert es einwandfrei !!!


Letzte Frage:

kann ich in Spalte AA irgendwie ohne Makro formatieren, das er mir die entsprechende Währung mit anzeigt ???
Sicher nicht, abe reine Frage ist es wert... :-)


Danke Jeremy
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Jeremy,

das mit den teilweise fehlenden $ ist mir beim zweiten Durchlesen auch aufgefallen, aber ich habe mir gedacht, dass du schon darauf kommst :-).

Hier die geänderte Formel, damit kein #NV angezeigt wird:

ab Excel 2007:
=WENNFEHLER(SVERWEIS($B16;'Templates Items'!$B$15:$AK$208;VERGLEICH(H$3;'Templates Items'!$B$7:$AK$7;0);FALSCH)*H$12;"")

bis Excel 2003:
=WENN(ISTNV(SVERWEIS($B16;'Templates Items'!$B$15:$AK$208;VERGLEICH(H$3;'Templates Items'!$B$7:$AK$7;0);FALSCH)*H$12);"";SVERWEIS($B16;'Templates Items'!$B$15:$AK$208;VERGLEICH(H$3;'Templates Items'!$B$7:$AK$7;0);FALSCH)*H$12)

Wenn du mit der Spalte AA nicht mehr rechnen willst, dann schreibe diese Formel in AA
=RUNDEN(SVERWEIS(B16;'Templates Items'!$B$15:$P$208;VERGLEICH('Templates Items'!$B$6;'Templates Items'!$B$12:$P$12;0);FALSCH)*Z16;2) & " " & 'Templates Items'!$B$6

Ansonsten schreibe in Spalte AB einfach
='Templates Items'!$B$6

Ansonsten müsste die Spalte per Makro formatiert werden.

Gruß

M.O.
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Jeremy,

noch mal ich :-).

Deine Frage zur 2. Formel verstehe ich nicht ganz.

Mit VERGLEICH wird die Spalte in deiner Tabelle "Template Items" gesucht, die deiner in B6 eingestellten Währung entspricht. Mit der SVERWEIS-Formel wird dann der Preis zurück gegeben, der dort z.B. bei der Zeile starter fork - premium steht.

Hast du als Währung also z.B. Dollar gewählt und änderst den Dollarpreis per Hand, dann wird dieser geänderte Dollarwert für die Berechnung der Kosten herangezogen.

Gruß

M.O.
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo M.O.

entschuldige die späte Antwort, aber hier in Rio sind alle durch den Karneval völlig durchgedreht und das Leben hier spielt verrückt...
Normales Arbeiten ist im Augenblick nicht möglich...

Ich habe deine beiden Formeln jetzt probiert und sie sind fast perfekt.

Die erste funktioniert reibungslos, ohne irgendwelche Komplikationen.
Die zweite funktinert vom Sinn her absolut korrekt, alle meine Wünsche wurden perfekt implementiert.
Für das alles schoin einmal einen ganz herzlichen und aufrichtigen Dank, vielen, vielen Dank !!!

Nur habe ich bei der zweiten Formel (die Währungsgeschichte) das gleiche Problem wie am Anfang bei der ersten Formel. Überall wo im Reiter "Templates Items" nichts steht, da macht er mir in der Spalte AA im Reiter "BHZ L-1" dieses #NV hin.

Ist es möglich auch diese Formel, wie die erste umzustellen ???


VIelen Dank und LG aus Rio

Jeremy
...