Vorformulierte Formeln benutzen

Das Tabellenkalkulationsprogramm

Moderator: Moderatoren

Gast

Vorformulierte Formeln benutzen

Beitrag von Gast » Mi, 18.07.2018 16:29

Hallo,

ich habe folgendes Vor:

ich möchte Funktionen/Formeln vorformulieren und a1,a2 usw. insgesamt 13 verschiedene kalkulationsgrundlagen.

Dann möchte ich eine wenn/sverweis Funktion starten so dass bei Erfüllung bestimmter Voraussetzungen die jeweils richtige vorgefertigte Kalkulation übernommen wird. Sozusagen in den formeltext eingefügt und Mitberechnet wird, leider zeigt es mir die Formel entweder nur als Textergebis an oder ein error.

Beispiel:

=wenn(c2<100;a1;wenn(c2<500;a2;... usw.

Dabei zeigt es mir aber nur den Text an und berechnet nichts.

Formatiere ich spalte a als Zahl, berechnet mir a1, a2 usw. einen Wert (100,13€), der auch wieder nur in c2 angezeigt wird, aber die vorgefertigte formel wird nicht in die Berechnung eingwfügt.

Ich bin absoluter Neuling, verzeiht mir bitte dieses vielleicht sinnlose Anliegen, ich freu mich über jeden erdenklichen Hinweis.

HeritzP
*****
Beiträge: 284
Registriert: So, 22.12.2013 11:23

Re: Vorformulierte Formeln benutzen

Beitrag von HeritzP » Mi, 18.07.2018 16:45

Hallo,

versuche die Funktion WAHL() zu verwenden.
Wahl&Formel1.ods
kleine Änderung
(7.92 KiB) 30-mal heruntergeladen

Gruß HeritzP
Zuletzt geändert von HeritzP am Mi, 18.07.2018 17:39, insgesamt 1-mal geändert.

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

Re: Vorformulierte Formeln benutzen

Beitrag von paljass » Mi, 18.07.2018 17:00

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.

HeritzP
*****
Beiträge: 284
Registriert: So, 22.12.2013 11:23

Re: Vorformulierte Formeln benutzen

Beitrag von HeritzP » Mi, 18.07.2018 17:05

Hallo,

er darf auch nachfragen.
Eine Beispieldatei würde dann auch helfen.

Gruß HeritzP

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

Re: Vorformulierte Formeln benutzen

Beitrag von OOMaBo » Mi, 18.07.2018 22:50

Hallo,

vielen dank für die schnellen Antworten und Fragen.

Ich habe mal versucht ein simples Beispiel zu kontruieren:
Beispiel.ods
(13.71 KiB) 26-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: 750
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo » Mi, 18.07.2018 23:42

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) 26-mal heruntergeladen

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

Re: Vorformulierte Formeln benutzen

Beitrag von OOMaBo » Do, 19.07.2018 07:48

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 » Do, 19.07.2018 09:26

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: 750
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo » Do, 19.07.2018 09:42

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 » Do, 19.07.2018 10:28

Beispiel.ods
(20.87 KiB) 20-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: 750
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo » Do, 19.07.2018 10:55

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 » Do, 19.07.2018 11:45

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: 750
Registriert: Fr, 14.11.2008 15:27
Wohnort: BRA - Nds

Re: Vorformulierte Formeln benutzen

Beitrag von echo » Do, 19.07.2018 16:52

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) 53-mal heruntergeladen

Gast

Re: Vorformulierte Formeln benutzen

Beitrag von Gast » Do, 19.07.2018 18:47

Danke! Auf dem Handy sehe ich leider deine Änderungen nicht, ich schaue mir das heute Abend daheim nochmal an.

Ich Google auch mal nach Literatur, die mir als Anfänger so einfach wie nur möglich excel, Funktionen, Makro, und vba erklärt... :)

Gast

Re: Vorformulierte Formeln benutzen

Beitrag von Gast » Do, 19.07.2018 21:02

@echo:

Daaaaaaankeeeee!

Du hast es so geschrieben, dass ich es verstehen und nachvollziehen kann! Ich habe gerade richtig etwas gelernt, was ich zwar bisher in teilen verwendet habe, aber noch nie in der Kombination!

Ich war immer nur stiller gastleser im Forum und habe mir da mein bischen Wissen angeeignet, das hier war phänomenal!

Danke an alle Mitwirkenden, ich kann nun auch die anderen Erklärungen etwas bis ganz verstehen!

Antworten