Excel Denksportaufgabe

[Endorhal]

Hallo ComputerBase Community,

ich wende mich heute mit einer kleinen Denkaufgabe an euch, bei der ihr mich hoffentlich unterstützen könnt. Ich arbeite an einer Excel, die mir bei einer Preiskalkulation helfen soll. Dabei verwende ich in erster Linie den guten alten SVERWEIS, der aber auch an seine Grenzen stößt (oder ich weiß nicht, wie ich ihn besser einsetzen kann).

Die angehängte Excel hat drei Reiter: Einkauf, Produktion und Konfig. In Einkauf sind die Zutaten und die zugehörigen Preise gelistet. Produktion stellt die Rezepte zur Verfügung. Hier möchte ich am Ende in Spalte C die Herstellungskosten haben, für deren Berechnung ich nach einer möglichst simplen Formel suche. In Konfig sind Gruppen definiert, da es mehrere Sorten Käse bzw. Brot gibt.

Was ich erreichen möchte ist, dass der Minimalpreis ausgegeben wird. Wenn also Brot einmal verwendet wird, soll die im Konfig Reiter definierte Liste Brot im Reiter Einkauf durchgegangen werden. Alle Objekte der Liste Brot (Vollkornbrot, Mischbrot und Weißbrot) sollen auf ihren Preis hin verglichen und es soll mit dem Mindestpreis gerechnet werden. Für Käsebrot erwarte ich im Reiter Produktion Feld C3 als Ausgabe "1" (entspricht einmal Weißbrot à 0,5 + einmal Schmelzkäse ebenfalls 0,5).

Wie kriege ich das möglichst elegant gelöst? Ich hoffe ich habe mein Problem verständlich erläutert, und dass mir jemand den notwendigen Hinweis zur Lösung geben kann.

Gruß
Endorhal

 

[ella_one]

Warum nur werden immer wieder Excel Lösungen angestrebt, welche sich mit Access und etwas VBA Programmierung viel elegenter lösen lassen?
Versuch es doch mal mit Access.

 

[abcddcba]

Wie kriege ich das möglichst elegant gelöst?

Hilft dir nicht weiter, aber jeder Informatiker wird dir hier "mit einer Datenbank" antworten.

Trotzdem viel Erfolg, gibt hier bestimmt paar Excel User, die dir helfen.

 

[poolk]

Wieso die Story mit dem Mindestpreis? In dieser Konstellation ist IMMER Weissbrot am günstigsten...

 

[Endorhal]

Wieso die Story mit dem Mindestpreis? In dieser Konstellation ist IMMER Weissbrot am günstigsten...

Weil die angehängte Datei lediglich eine stark vereinfachte Fassung der tatsächlichen Tabellen enthält, um sich nicht mit unnötigen Details zu befassen, die mit der eigentlichen Lösung nichts zu tun haben.

 

[Scientist]

Warum nur werden immer wieder Excel Lösungen angestrebt, welche sich mit Access und etwas VBA Programmierung viel elegenter lösen lassen?
Versuch es doch mal mit Access.

Weil die wenigsten Ottos mit Access je in Beruehrung kamen und Excel hatte jeder schon mal offen und ein bisschen herumgetippt. In Excel kommt man auch gut ohne VBA und damit erweiterten Programmierkenntnissen zurecht.

Zum Problem:

• Kategorien um Produktpreise erweitern
• Sverweis() auf jede Kategorie mit Minimum(Preis) fuettern

 

[Endorhal]

• Kategorien um Produktpreise erweitern

Meinst du im Konfig Tabellenblatt? Das wäre dann fast ein Clon der Tabelle vom Einkauf Tabellenblatt nur erweitert um Kategorienamen und ohne die Zeilen, die keiner Kategorie angehören.

• Sverweis() auf jede Kategorie mit Minimum(Preis) fuettern

SVERWEIS() gibt ja immer nur einen Wert (den ersten der gefunden wird) aus. Daher weiß ich nicht, wie ich das mit dem Minimum verknüpfen kann. Hast du dafür mal ein Beispiel?

 

[Scientist]

Meinst du im Konfig Tabellenblatt? Das wäre dann fast ein Clon der Tabelle vom Einkauf Tabellenblatt nur erweitert um Kategorienamen und ohne die Zeilen, die keiner Kategorie angehören.

Ja, so sind die Daten aber in einer Tabelle verknuepft und koennen mit SVerweis() bestimmt werden.
Andersherum (also Einkauf erweitern) macht es jedoch schwieriger, weil dann ein Sverweis mit zwei Kriterien benoetigt wird.

SVERWEIS() gibt ja immer nur einen Wert (den ersten der gefunden wird) aus. Daher weiß ich nicht, wie ich das mit dem Minimum verknüpfen kann. Hast du dafür mal ein Beispiel?

Minimum Preis einer Kategorie bestimmen (Min()) und diesen Wert dann in der jeweiligen Kategorie mit SVerweis() suchen.

 

[Endorhal]

Ja, so sind die Daten aber in einer Tabelle verknuepft und koennen mit SVerweis() bestimmt werden.
Andersherum (also Einkauf erweitern) macht es jedoch schwieriger, weil dann ein Sverweis mit zwei Kriterien benoetigt wird.


Minimum Preis einer Kategorie bestimmen (Min()) und diesen Wert dann in der jeweiligen Kategorie mit SVerweis() suchen.

Ok, also muss ich den Zwischenschritt das Minimum der Kategorien auszurechnen scheinbar wirklich händisch erledigen. Ich hatte gehofft es über den Namens Manager elegant mit Bezeichnungen für die Kategorien und dann direkt über einen verschachtelten SVERWEIS lösen zu können.

In jedem Fall danke für die Bestätigung!

 

[Scientist]

Okay, beim Aufbau der aktuellen Liste ist das nicht nur mit Min() erledigt.
Entweder aendert man den Aufbau (Jede Kategorie erhaelt ihre eigene Spalte bzw. Tabelle)
oder man loesst die verbundenen Zellen auf und schreibt in jede Zeile die Kategorie und nutzt Minwenns()
oder man bestimmt erst die Bereiche der jeweiligen Kategorien, um dann ueber indirekt() den Bereich fuer Min() zu basteln. Hier gibt es aber diverse Loesungen.

 

[Jirko]

Du hast es dir auf jeden Fall schwer gemacht, weil du Konfig und Einkauf trennst. Leg das zusammen und verzichte auf verbundene Zellen, wenn du es leicht mit Formeln durchsuchen willst.

Ein weiteres Problem ist, dass du in der Tabelle "Produktion" bei den Zutaten mal den Namen des Materials und mal den der Kategorie nutzt. Das macht die Verformelung auch nicht unbedingt leichter. Ich habe daher die Kategorie wie das Material genannt, wenn sie nur dieses eine Material enthält.

Wenn du es umsortierst und aufbaust wie in meiner Tabelle, kann man es recht leicht über "MINWENNS" durchsuchen und mit der Anzahl multiplizieren. Ich hab dir auch schon dynamische Bereiche im Namens-Manager angelegt, die auf bei einer Erweiterung der Einkaufs-Tabelle noch funktionieren.

 

[Endorhal]

Danke für die Mühe. Mit meinem Excel (Office 2016) kann ich die Datei nicht öffnen und in OpenOffice kommt was wirres raus. Mit welcher Version hast du's bearbeitet? Office 365? MINWENNS() gibt es in Office 2016 scheinbar auch gar nicht. Ich versuche gerade an ein Office 2019 zu kommen und es damit zu probieren.

Mal schauen, ob ich Erfolg habe.

 

[Jirko]

Also öffnen können solltest du die Datei ganz normal, ist einfach nur .xlsx - also Standard-Excelformat. Wenn dein Excel die Funktion nicht kennt, kommt tatsächlich an der Stelle ein Fehler in der Formel. Allerdings gibt es MINWENNS imho seit Office 2016 (siehe hier).

Erstellt habe ich die Datei mit einem aktuellen Office 365.