Berechnung_Bratwurstkosten_Köln

Hi zusammen!
Dies ist eine Fortsetzung von einer Kostenübersicht, bei der mir der liebe onur geholfen hat:)
Ausgangslage:
Ich habe eine Excel-Datei mit verschiedenen Tabellenblättern.
In dieser soll berechnet werden, wieviel Bratwürste, Bretzeln usw. in den deutschen Fußballstadien pro Jahr gegessen werden.
Die Tabellenblätter sind folgende Typen:
- Konsum im betrachteten Jahr (Konsum20, Konsum21, Konsum22)
- 1 Basisblatt (Name: "Grundlagen"), das den Binärcode (siehe Erläuterung unten) in seine Bestandteile zerlegt (mit der Formel "Teil ()")
- Preisblatt und Anzahlblatt pro Stadion (Berlin- und Berlin-#, München- und München-# usw.)
Was in den einzelnen Stadien im Jahr 2020 z.B. gegessen wird, ist zunächst mit Ja/Nein gegeben.
Mit onurs Lösung ist es möglich, dass er dieses in eine "binäre" Variable umwandelt.
Z.B. wird für Köln aus:
Bratwurst (nein), Nackensteack (ja). Bretzel (ja), Nachos (ja) und Erdnüsse (nein)
der binäre Code 01110 in Spalte G.
Diesen binären Code möchte ich nun dafür verwenden, die einzelnen Kosten für Bratwürste usw. in den Stadion zu berechnen.
Ziel:
Ziel ist nun ein Code, der Folgendes ausführt:
1) Im Konsumblatt "Konsum20" in Spalte G nach den binären Codes sortieren.
2) Eine Kopie des Blattes "Grundlagen" erstellen und die Zeilen, die den selben Binärcode haben, ab Zeile 3 einfügen.
Der Name des neuen Blattes soll der Name des Activesheet (hier also "Konsum20") sein, an den "_Binärcode" angehängt wird (z.B. "Konsum20_01110).
Dazu soll nach jeder eingefügten Zeile eine Leerzeile eingefügt werden.
3) Der Binärcode soll im neuen Blatt in Zelle A1 eingefügt werden. Dadurch belegt er die Bratwürste und Bretzeln usw. mit 0 oder 1.
4) Hat er z.B. das Stadion in Köln eingefügt, soll er in der zusätzlichen leeren Zeile z.B. für die Bratwurst folgende Berechnung ausführen:
Bratwurstpreis-Köln (aus Blatt "Köln-") * Bratwurstanzahl-Köln (aus Blatt "Köln-#) * Anteil des Binärcodes für die Bratwurst (ist für die Bratwurst immer B1).
Ich habe die Hoffnung, dass dies einfacher umzusetzen ist, als es zunächst aussieht, im Sinne von: "Suche nach Stadt, rechne an Stelle "Bratwurst": Stadt-Bratwurstpreis*Stadt-Anzahl*B1).
Ich habe eine Beispieltabelle gebaut, in der ich mit Textfeldern an einigen Stellen zum besseren Verständnis Teile der obigen Beschreibung eingefügt habe.
Die Tabellenblätter "Konsum20_01110, Konsum20_10000, Konsum20_11001" sind von mir händisch erstellt. Die sollen idealerweise vom Code erzeugt werden.
https://www.herber.de/bbs/user/152211.xlsx
Liebe Grüße und bitte Bescheid sagen, wenn ich noch etwas beisteuern kann!
(Z.B. Blatt kopieren und Name geben habe ich parat und könnte ich beisteuern? Wusste nicht, ob das hilft oder eher stört, weil vielleicht leichter, etwas "von scratch" zu bauen? Könnte da auf jeden Fall was dazu nachreichen, wenn das hilft!)
Henny

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi Yal :)
Ich wünschte, ich könnte ein paar Bilder hochladen, das würde mir das Erklären leichter machen.
Aber vielleicht kriege ich es auch so hin.
Anbei ein aktualisiertes Beispiel:
https://www.herber.de/bbs/user/152229.xlsm
In diesem Beispiel habe ich einen neuen Fall, "Dortmund" eingebaut.
Per Makrorecorder habe ich einmal aufgezeichnet, was das Ergebnis sein soll, das hilft vielleicht :)
Bitte die Bitte zum Aktualisieren, zu dem er einen auffordert, einfach mit dem Kreuz rechts oben abbrechen, keine Ahnung, was er da macht :-D
Zu meiner Datei:
1) Tabelleninhalt:
- Ich habe drei Tabellen mit dem Essenskonsum: Konsum20, Konsum 21 und Konsum22.
Betrachten tue ich gerade nur die Tabelle "Konsum20".
In diesen Tabellen steht nur, ob Bratwurst, Bretzel usw. in den Stadien gegessen wird oder nicht. Also Ja/Nein.
Je nach Ja/nein mache ich mit onurs Lösung eine Textkette in Spalte G, die das als "binäre Eingruppierung" einstuft.
Im Beispiel sind z.B. Hamburg, Köln und Stuttgart aktuell in der selben "Binärkategorie" 01110.
Berlin und München sind jeweils eine eigene Kategorie (10000 und 11001).
- Dazu hat jedes Stadion 2 Blätter mit Informationen bzgl. der Preise (z.B. Blatt "Köln-") und der konsumierten Menge ("Köln-#").
- Ein weiteres Tabellenblatt ist das Blatt "Grundlagen". Dies dient als Basis für die angestrebten Einzelblätter.
2) Ziel:
Das Ziel ist es, dass für jene Stadien aus "Konsum20" gesonderte Tabellenblätter erzeugt werden.
In diese neu erzeugten Tabellenblätter sollen alle Stadien rein, die die selbe binäre Kategorie haben.
Dazu soll das Blatt "Grundlagen" kopiert werden und folgendermaßen benannt werden:
"Activesheet.Name" (hier also Konsum20) + "_binär Code".
Also in diesem Fall "Konsum20_01110".
Der Binärcode soll dabei in die Zelle A1 kopiert werden (damit dann über die hinterlegte "Teil"-Formel in Zeile 1 immer mit 1 oder 0 gerechnet wird, s.u.)
Hier sollen dann alle Zeilen aus "Konsum20", die die selben Binärcode haben, ab Zeile 3 eingefügt werden, sowie darunter eine Leerzeile.
Als Beispiel habe ich das Tabellenblatt "Konsum20_01110" angelegt.
In dieser Leerzeile soll er nun die jeweiligen Kosten berechnen.
Diese Daten soll er sich aus den jeweiligen Stadientabellen (Blatt "Köln-" und Blatt "Köln-#") holen.
Sprich:
"Kopiere Zeile Köln von "Konsum20" nach "Konsum20_01110".
Füge Leerzeile ein.
Berechne unter "Bratwurst" (rot eingefärbte Zelle): Preis "Bratwurst" aus Köln- * Menge "Bratwurst" aus Köln-# * Binärfaktor Bratwurst (Zelle B1).
Das ist noch keine lineare Optimierung. Das Ziel ist schon, später eine Gesamtauswertung zu erreichen, das wollte ich aber auch erst einmal selber versuchen (so wie das Obige auch, ich kriege es aber nicht hin).
Stand jetzt "reicht" es, wenn man die Kosten der einzelnen Konsumgüter pro Stadion sieht.
LG und bitte melden, falls ich noch weiter was beisteuern kann bzw. etwas deutlicher beschreiben soll!
Ich hoffe, dass das Makrorecorder-Beispiel hilft :)
Henny

Sub Transform() Dim wQ As Worksheet 'Worksheet-Quelle Dim wZ As Worksheet 'Worksheet-Ziel Dim qZ As Integer 'z für Zeile, qZ für Quell-Zeile Dim zZ As Integer Dim zS As Integer 's für Spalte Dim boolArray As String For Each wQ In ThisWorkbook.Worksheets If LCase(Left(wQ.Name, 6)) = "konsum" And Len(wQ.Name) = 8 Then For qZ = 3 To wQ.Range("A9999").End(xlUp).Row boolArray = Array_lesen(wQ.Name, qZ) If boolArray "00000" Then Set wZ = Worksheet_auswählenOderHerstellen(wQ.Name & "_" & boolArray) zZ = wZ.Range("B99999").End(xlUp).Offset(1).Row wZ.Cells(zZ, 1).Resize(1, 6) = wQ.Cells(qZ, 1).Resize(1, 6).Value wZ.Cells(zZ + 1, 2).Resize(1, 5).FormulaR1C1 = "='" & wQ.Cells(qZ, 1) & "-'!R3C * " & "'" & wQ.Cells(qZ, 1) & "-#'!R3C" & CInt(Right(wQ.Name, 2)) - 18 & " * R1C" End If Next End If Next End Sub Private Function Worksheet_auswählenOderHerstellen(ByVal BlattName As String) As Worksheet Dim W As Worksheet Dim i On Error Resume Next Set W = ThisWorkbook.Worksheets(BlattName) If W Is Nothing Then ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count) Set W = Worksheets(Worksheets.Count) W.Name = BlattName W.Range("A1") = Right(BlattName, 5) For i = 2 To 6: W.Cells(1, i) = Mid(BlattName, 8 + i, 1): Next W.Range("A2:F2") = Split("Kulinarische Köstlichkeiten Bratwurst Nackensteack Bretzel Nachos Erdnüsse", " ") End If Set Worksheet_auswählenOderHerstellen = W End Function Private Function Array_lesen(ByVal BlattName As String, Zeile As Integer, Optional Länge As Integer = 4) As String Dim z As Range With Worksheets(BlattName) For Each z In .Cells(Zeile, 2).Resize(1, Länge + 1) Array_lesen = Array_lesen & IIf(LCase(z.Value) = "ja", "1", "0") Next End With End Function

Beliebteste Forumthreads (12 Monate)

19 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

19
Beiträge zum Forumthread

Beiträge zu diesem Forumthread