Vorformulierte Formeln benutzen

Das Tabellenkalkulationsprogramm

Moderator: Moderatoren

paljass
******
Beiträge: 792
Registriert: Do, 25.10.2012 07:25

Re: Vorformulierte Formeln benutzen

Beitrag von paljass »

Hi,
@HeritzP
hast du das hier
Ich bin absoluter Neuling,
auch gelesen? Ich denke, da ist so eine Inlinematrix nicht unbedingt angebracht.

Gruß
paljass
EDV ist wie U-Boot fahren - machst du ein Fenster auf, fangen die Probleme an.
OOMaBo
Beiträge: 7
Registriert: Mi, 18.07.2018 16:09

Re: Vorformulierte Formeln benutzen

Beitrag von OOMaBo »

Hallo,

vielen dank für die schnellen Antworten und Fragen.

Ich habe mal versucht ein simples Beispiel zu kontruieren:
Beispiel.ods
(13.71 KiB) 93-mal heruntergeladen
Im Kern wird s darum gehen, dass für jede "Warengruppe" also im Beispiel unterteilt in Artikel 1-10, 11-100, 101-1000 usw. abhängig von vielen Faktoren (einkaufsrabatt, wettbewerbspreisen, effektiver Preis usw.) eine eigene Kalkulation bekommen sollen. Ich habe dies hier mal nur sehr vereinfacht dargestellt in Spalte "K".

Meine Eigentliche Tabelle ist ähnlich aufgebaut, nur ist die Formel zur Kalkulation fast eine ganze Seite lang im Writer(Schriftgröße 10), dementsprechend ineffektiv ist das ganze bei 30000 Artikeln auch, also Langsam und sehr Fehleranfällig.

Nun möchte ich für die jeweiligen Warengruppen, es sind 40 verschiedene, eine Formel vorformulieren (hier in spalte N) und abspeichern, diese dann mit einer kombination aus Wenn/SVERWEIS der tabelle entnehmen und somit die Rechnung vereinfachen und beschleunigen.

Sofern das geht...

Eine Frage noch am Rande:

in einer weiteren Mappe speichere ich separat Wettbewerbspreise ab und setze diese mit in meiner Kalkulation ein.
Kann ich automatisch ein Datum hinterlegen lassen, damit ich ggf. ebenfalls mit einer funktion nur den neueren Preis heranziehe, bzw. preise ab eines bestimmten datums nicht mehr berücksichtige?


Ich hoffe, dass ich mit meiner einfachen Ausdrucksweise annähernd beschreiben kann, woran ich gerade sehr zu knabbern habe...
echo
*******
Beiträge: 1005
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo »

Hallo
ich würde eine aufsteigend sortierte Liste mit den Stückzahlen erstellen wo daneben die Werte für die Formel stehen.
Diese lassen sich dann per SVerweis() abfragen. Hast du weitere Warengruppen bekommt jede "seine" Spalte mit Werten.
Es ändert sich nicht mehr die Formel sondern abhängig von der Stückzahl und Warengruppe nur noch die relevanten Werte.

Das geht übrigens genau so mit deinen Datum:
Du ermittelst mit =MAX() das letzte Datum, setzt MAX als Suchkriterium in SVerweis() ein und holst dir so den aktuellen Wert.
=SVERWEIS(MAX( Spalte vom Datum ); Matrix mit Datum links und Wert; Spalte vom Wert ; 0 )

LG Holger
EDIT: In der Liste hatte sich Text einschlichen.
LibereOffice scheint das wenig zu interessieren, OpenOffice schon, deshalb habe ich es nicht gemerkt sorry
Dateianhänge
Beispiel.ods
(16.09 KiB) 93-mal heruntergeladen
OOMaBo
Beiträge: 7
Registriert: Mi, 18.07.2018 16:09

Re: Vorformulierte Formeln benutzen

Beitrag von OOMaBo »

Vielen Dank für das Beispiel,

ich schaue mir das mal genauer an und versuche das ganze nachzuvollziehen und zu verstehen, es ist schonmal wesentlich weniger Code :)
OOMaBo
Beiträge: 7
Registriert: Mi, 18.07.2018 16:09

Re: Vorformulierte Formeln benutzen

Beitrag von OOMaBo »

Hallo,

ich muss tatsächlich mal nachfragen:

In Spalte L steht die Kalkulation mit dem Sverweis und den dazu benötigten kalkulationsdaten aus Spalte q-r, woher weiß die Formel, welcher Wert benötigt wird?

Zusätzlich ist in Spalte o der Vergleich zur Kalkulation, den kann ich nachvollziehen, allerdings sehe ich nicht wie der Vergleich in die Preiskalkulation Spalte L einbezogen wird.
echo
*******
Beiträge: 1005
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo »

Hallo
=Sverweis(Suchkriterium; Matrix ; Spalte ; Sortiert)
Sverweis() hat zwei Versionen:
Sortiert = 0 oder FALSCH
Sortiert= 1 oder WAHR

Ist sortiert auf 0 eingestellt sucht die Formel nach dem GENAUEN Wert in der Suchspalte

Ist sortiert auf 1 eingestellt sucht die Formel die Zeile mit dem letzten Wert der noch kleiner oder gleich dem Suchkriterium ist,
deshalb beginnt die Suchspalte mit Null und muss aufsteigend sortiert sein um die richtige Zeile zu finden.

Sehr schön erklärt (finde ich) auch wenn hier von Excel gesprochen wird:
https://www.online-excel.de/excel/singsel.php?f=9

Gruß Holger
OOMaBo
Beiträge: 7
Registriert: Mi, 18.07.2018 16:09

Re: Vorformulierte Formeln benutzen

Beitrag von OOMaBo »

Beispiel.ods
(20.87 KiB) 84-mal heruntergeladen
[/d]Also muss das Suchkriterium mit einer wenn Funktion verknüpft sein?

Der Verweis der beispieldatei gibt jeweil eine unterschiedliche Kalkulation pro warengruppe raus, diese muss nun noch in jeweils unterschiedliche rabattgruppen eingeteilt werden, Spalten in grauem Hintergrund.

Da müsste nach meinem Verständnis eine wenn,dann,wenn,dann,wenn,dann,wenn,dann,sonst rein und das im Prinzip für alle warengruppen, da diese jeweils unterschiedliche Eckdaten abfragen(wenn)
Mal ist es der Rabatt, mal die kalkulationsgrundlage, mal der Preis in Abhängigkeit der kalkulationsgrundlage.

Das Beispiel verstehe ich in soweit, nur mit dem Vergleich hapert es, da eben die verschiedenen rabattgruppen pro warengruppe rein müssten. Es wäre einfach, wären die in jeder warengruppe gleich.

Ich habe die beispieldatei mal erweitert, lade diese nachher mal hoch, dort habe ich die wahren Ergebnisse grün markiert.
echo
*******
Beiträge: 1005
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo »

hallo
wenn ich es richtig verstehe, so hasst du drei Kriterien.
1. die Warengruppen
2. jeweils die Stückzahl (Zeilen)
3. Je Wahrengruppe Rabattgruppen (Spalten)


Da könntest du für jede Warngruppe eine eigene Liste erstellen und dieser einen Namen vergeben, aus dem Beispiel:
Q2:R21 markieren und links oben im Namenfeld zB. WarenGr1 eintragen und mit RETURN bestätigen.
Siehe auch Tabelle >> benannte Bereiche
Das machst du 2-Spaltig für jede Warengruppe.

Wenn jetzt der Name der gesuchten WG in einer Zelle steht, (Beispiel N1) stellst du die Matrix von Sverweis auf den Namen so ein:
=G2/(I2+SVERWEIS(A2;INDIREKT($N$1);2;1))
N1 Stellt die gewünschte WG ein. (Name in N1 so schreiben wie vergeben)
In A2 Steht das Suchkriterium
Zeile 2 wird für die Rechnung zurück gegeben
OOMaBo
Beiträge: 7
Registriert: Mi, 18.07.2018 16:09

Re: Vorformulierte Formeln benutzen

Beitrag von OOMaBo »

Hallo,

schau mal bitte in meinen letzten Beitrag, dort habe ich die ergänzte Tabelle eingefügt.

Dort muss ich nun die „Wahrheit“ finden.

Kriterien sind:

Warengruppe
Einkaufspreis
Verschiedene Abstufungen den einkaufrabattes und die errechnete Kalkulationsgrundlage.

Für jede warengruppe ergibt sich so eine völlig unterschiedliche Herangehensweise, beschrieben in Spalte S (mein ergänztes Beispiel)

Die Lösung wird sicher machbar sein, nur sehe ich aktuell den Wald vor lauter Bäumen nicht und komme immerwieder zum selben Problem :(


Benötigt wird es, um preistabellen zu erstellen, die dann in das Warenwirtschaftssystem eingespielt werden.

Ich versuche mal den indirekten Verweis zu begreifen um da voran zu kommen ;)
echo
*******
Beiträge: 1005
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo »

Hallo
ich kann im Moment nicht so ganz folgen, daher Habe ich einfach Tabellenblatt 1 kopiert und einen Verweis eingefügt der
1. die Zeile abhängig vom Wert in Spalte A einstellt (Matrix U2:AB21) und
2. die Spalte abhängig von der Kalkulation einstellt (Suchzeile V1:AB1)
Wichtig sowohl für Spalt U wie auch für Zeile 1, das die Werte aufsteigend sortiert sind und das sie mit 0 beginnen damit alle Zwischenwerte gefunden werden können.
Dateianhänge
Beispiel2.ods
(35.89 KiB) 109-mal heruntergeladen
echo
*******
Beiträge: 1005
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo »

Hallo, schön das es funktioniert und danke füs Feedback
Weil es dazu passt, ein kleiner Nachtrag noch zu einer sehr oft verwendeten Kombination: Index() und Vergleich()

=SVERWEIS(A2;$U$2:$AB$21;VERGLEICH(I2;$V$1:$AB$1;1)+1;1)
In deiner Formel sucht Vergleich() nach der richtigen Spalte, der SVerweis() durchsucht Spalte U und stellt die richtige Zeile ein.
aber angenommen die Suchspalte vom SVerweis() ist nicht ganz links in der Tabelle oder steht sogar ganz wo anders (ZB. Liste für PullDownMenü) dann gibt es ein Problem die gesuchte Zeile einzustellen.

Lösung: Index:
Sverweis() möchte die Spalte eingestellt haben,
Index sucht gar nicht selbst und möchte sowohl die Zeile wie auch ggf. die Spalte eingestellt haben, um eine Zelle aus der Matrix anzuzeigen.
das Vergleich() das kann haben wir ja schon gesehen, also Vergleich doppelt, ein mal für die Zeile, ein mal für die Spalte.
=INDEX($V$2:$AB$21;VERGLEICH(A2;$U$2:$U$21;1);VERGLEICH(I2;$V$1:$AB$1;1))
Nur Daten ohne Suchzeile/Suchspalte : $V$2:$AB$21
Suchspalte: $U$2:$U$21
Suchzeile: $V$1:$AB$1
Der erste Vergleich sucht nach der Zeile, der zweite Vergleich nach der Spalte.
Beide Formeln SVerweis... und Index... liefern das gleiche Ergebnis, mit dem Unterschied, das die Suchspalte extra in der Formel steht. Die Suchspalte kann jetzt also beliebig eingestellt werden, und muss nicht zwingend ganz links in der Matrix stehen. :)
Gruß Holger
Antworten