Office: (Office 365) Baugruppen

Guten Morgen Zusammen

Ich bin dran, Baugruppen in einer Kalkulation zusammenzuzählen.

Gesucht wird Formel in Spalte N und Spalte O.
Pos. 1 = Einfaches Teil ohne Baugruppe, in Preis eff. dann abgebildet.
Pos. 2 = Baugruppe A; per Auswahl wird die erste Linie als Hauptteil (entsprechende Anzahl Baugruppe) definiert. Darunterliegend die einzelnen dazugehörigen Bauteile mit entsprechenden Stückzahlen (Gelb).

Gleiches passiert mit Gruppe B und C.

In der Theorie sind so alle Zeilen entweder ein einfaches Teil, oder eine Baugruppe.

Spalte M ist zur Kontrolle, alle Einzelpreise.
Spalte N zählt die Baugruppe effektiv zusammen.
Spalte O die effektiven Preise.

Siehe oben, die Spalte C und D sind klappbar :)

Danke für euere Hilfe!

Grüsse

 

Hallo,

ich habe dir in der Arbeitsblattkopie "Tabelle2 (2)"

1) im Bereich C4:C13 (Gruppe) die neuen Kontrollkästchen (Excel365) eingefügt: Im angehakten Modus steht in der betreffenden Zelle WAHR, im nicht-angehakten Modus steht in der Zelle FALSCH.

2) in der Zelle L4 folgende Formel eingefügt:
=LET(d; $B$4:$F$13; idx; SPALTENWAHL(d; 1); grp; SPALTENWAHL(d; 2); seg; SPALTENWAHL(d; 3); stk; SPALTENWAHL(d; 4); prs; SPALTENWAHL(d; 5); BauGrp; NACHZEILE(d; LAMBDA(zl; WENN(INDEX(zl; 2); LET(id; INDEX(zl; 1); sg; INDEX(zl; 3); st; WENN(idx = id; 1; stk); stg; FILTER(st; seg = sg; ""); prg; FILTER(prs; seg = sg; ""); SUMMENPRODUKT(stg; prg)); 0))); EffPr; NACHZEILE(d; LAMBDA(zl; WENN(INDEX(zl; 2); LET(Id; INDEX(zl; 1); INDEX(BauGrp; Id)); WENN(INDEX(zl; 3) = ""; INDEX(zl; 5); 0)))); HSTAPELN(prs; BauGrp; EffPr))
Diese dyn. Array-Formel berechnet alle Werte im Zellbereich L4:N13 (umrandeter Bereich). Ferner habe ich die Zellen so formatiert, dass 0-Beträge nicht angezeigt werden.
In etwas strukturierterer Form und damit leichter lesbar sieht obige Formel so aus:

Datei liegt bei:

 

Wahnsinn. Wie kann man das nur so zusammenstellen...!!?

Ich implementiere das mal in meine Tabelle und teste das mal durch. Danke vielmals
Diese neuen Kontrollkästchen von EXCEL 365 kannte ich nicht!

Gruss Marc

 

Guten Morgen
Laut meiner Vorlage waren ist dies nicht das effektive Design rsp. habe ich noch eine zusätzliche Spalte. Ich versuchte die Formel anzupassen. Wie es aussieht, funktioniert dann die Formel nicht mehr. In meinem Fall, Tabelle 3, ist die 4. Spalte neu.

Die Spalte der Einzelpreise, Spalte 6, ist nur 1mal vorhanden. Nicht wie im ersten Beispiel 2mal.
Könntest du mir das nochmal anpassen?

Auch die LET Funktion war mir neu. Hab mich da reingelesen, jedoch noch nicht alles verständlich bis jetzt

Gruss Marc

 

Hallo,

in der beiliegenden Datei habe ich dir in den Namensmanager 2 benutzerdef. Funktionen eingefügt:

1) Die erste Funktion habe ich dir ins Arbeitsblatt "Tabelle2 (2)" in Zelle L3 eingefügt:
=BauGrpTab2Abr(B4:F13)

2) Die zweite Funktion habe ich dir ins Arbeitsblatt "Tabelle3" in Zelle G2 eingefügt:
=BauGrpTab3Abr(A3:F12)

3) Weiters ist noch eine kleine Funktion
IstGruppe = LAMBDA(Gr; WENN(ISTTEXT(gr);gr="þ";gr))
enthalten, die dazu da ist, die Werte der Spalte "Gruppe" zuverlässig in logische Werte umzuwandeln, falls dort die Zeichen "þ" (für angehakt) bzw. "o" (für nicht angehakt) enthalten sein sollten.

Die Unterschiede der Funktonen von Punkt 1) bzw. Punkt 2) wirst du ja selber herausfinden können.

 

Hallo Anton

Die Formel funktioniert perfekt.
Nun ziehe ich das Segment (A, B, C, etc.) von einer anderem Mappe via Formel rüber. Wenn die Zelle, wie in meinem Beispiel Leer ist, funktioniert sie. Wenn die Formel drin ist, berechnet mir die Funktion es nicht.
Wie kann ich das umgehen trotz Formel?

Gruss Marc

 

Habe eine Lösung gefunden
=WENN(ISTLEER(XVERWEIS(ZELLE;ZELLE;FALSCH));"";XVERWEIS(ZELLE;ZELLE;WAHR))

Danke Anton für deine Hilfe!

 

Hallo,

was dein Posting #6 betrifft: Da verstehe ich das Problem nicht.
Es kommt nur darauf an, dass im Zellbereich Tabelle3!C3:C12 (Segment-Spalte) in jeder Zelle ein Text enthalten ist: Zeilen mit gleichen Texten werden gruppiert und der Gruppenpreis in der Zeile mit dem Gruppenhäkchen ausgegeben. Falls kein Gruppenhäkchen vorliegt, wird Gruppenpreis 0 ermittelt in der Gruppenpreis-Spalte, in der EffPreis-Spalte erscheint dann der Einzelpreis.

was dein Posting #7 betrifft: Diese Formel verstehe ich nicht wirklich, außer dass sie WAHR liefert. Aber da sie gemäß deinen Angaben funktioniert, müsste sie folglich ok sein.

 

Hallo Exl121150,

der Baugruppenpreis für Segmenz 'C' kann so nicht stimmen, denn nacg Adam riese kommt da 80 € raus ( 2x10 + 6*5 + 3*10 = 80). Meine PQ-Lösung bringt hier das korrekte Ergebnis. Solange die GroupBy()-Funktion nicht Allgemeingut in Excel 365 ist, bleibt Deine Lösung doch wohl eher Krampf als Kampf, denn die Fehlerkontrolle ist doch herausfordernd und zeitintensiv.

Gruß von Luschi
aus klein-Paris

 

Hallo,

ich weiß, dass bei dieser Art der Anzahlen/Preis-Rechnung 80 herauskommen muss. Aber genau das war nicht die Vorgabe der Excel-Datei von Posting #1.

Geht man von den Vorgaben dieser Datei aus, darf man den Preis, der in einer Gruppenzeile steht, nur 1x in den Gruppenpreis hineinnehmen, während die Mengenangabe der Gruppenzeile dazu dient, den Totalpreis zu ermitteln. Es steht in der Baugruppenpreis-Spalte 70 und nicht 80. Ohne diese Merkwürdigkeit wäre auch meine Funktion einfacher ausgefallen.
Die Vorgaben dieser Datei seien im Folgenden nochmals eingeblendet.

Ich habe jetzt im Arbeitsblatt "Tabelle4" eine weitere Kopie eingefügt, allerdings jetzt in Zelle G2 mit dem Funktionsaufruf =BauGrpTab4Abr(A3:F12). Hier scheint jetzt in der Baugruppenpreis-Spalte 80 auf und in der Total-Spalte 160.

Oder was immer @marc_wind sich vorgestellt hat, dass es richtig sei. - Ich weiß es einfach nicht. Ich hielt mich an seine Vorgabe.
Auch ich weiß, dass es im Betakanal von Excel365 neue(re) Funktionen gibt, aber das steht hier nicht zur Diskussion.

 

Guten Morgen Zusammen

Im Anhang meine Datei. Hier die zwei Beispiele: Bsp. Anton und Bsp. Marc_Wind.
Was mir nicht angezeigt wird ist die Zelle Gelb bei der Formel von Anton. Dies wäre ein einzelnes Bauteil - das ist normalerweise der Fall. Grün meine gewurstelte Formel.
Ich ziehe die Informationen "Gruppe" und "Segment" von der Einzelteilkalk rüber, dies veranschaulicht in den jeweiligen 2. Mappe der Beispiele.

Luschi hat recht. Dies ist mir nicht aufgefallen, dass es dort 70 ergibt. Richtig wäre 80. Sry Anton.
Das "Hauptteil" der Baugruppe ist jeweils die Anzahl der Baugruppen, welche dann addiert wird. Weil die Baugruppe geht jeweils von 1 Hauptteil aus. Ich habe die Zusammenstellung der Gruppen und des Segments irgendwie flexibel halten müssen, da dies auch ein einfaches Bauteil sein kann.

Ich habe die Formel von Anton mehrfach geprüft und soweit hats gepasst

Gruss Marc

 

Hallo,

du hast im Arbeitsblatt "Bsp Anton_1" in Zelle C6 eine echte Leerzelle (=Zelle ohne jeden Inhalt). Durch deine XVERWEIS-Funktion gelangt jedoch in die Zelle C7 von Arbeitsblatt "Bsp Anton" ein 0-Wert, den du durch die benutzerdefinierte Formatierung "0;-0;;@" wie eine echte Leerzelle erscheinen lässt, obwohl natürlich weiterhin eine 0 in der Zelle enthalten ist.

Ich habe deswegen die entsprechende Formelzeile geändert
von WENN(INDEX(zl; 3) = ""; INDEX(zl; 6); 0)
auf WENN((INDEX(zl; 3) = "")+(INDEX(zl; 3) = 0); INDEX(zl; 6); 0),
sodass jetzt alle Spezialfälle (echte Leerzelle / Zelle mit Leerzeichenkette / Zelle mit 0-Wert) gleich behandelt werden, sodass dieses Pseudo-Leerzellen-Problem nicht mehr auftritt.
Nachfolgend die Formel noch einmal wie sie im Arbeitsblatt "Bsp Anton" im Funktionsaufruf =BauGrpTab4Abr(A7:F16) der Zelle G6 verwendet wird:
=LAMBDA(d; LET(idx; SPALTENWAHL(d; 1); grp; IstGruppe(SPALTENWAHL(d; 2)); seg; SPALTENWAHL(d; 3); bez; SPALTENWAHL(d; 4); stk; SPALTENWAHL(d; 5); prs; SPALTENWAHL(d; 6); BauGrp; NACHZEILE(d; LAMBDA(zl; WENN(IstGruppe(INDEX(zl; 2)); LET(id; INDEX(zl; 1); sg; INDEX(zl; 3); stg; FILTER(stk; seg = sg; ""); prg; FILTER(prs; seg = sg; ""); SUMMENPRODUKT(stg; prg)); 0))); EffPr; NACHZEILE(d; LAMBDA(zl; WENN(IstGruppe(INDEX(zl; 2)); LET(Id; INDEX(zl; 1); INDEX(BauGrp; Id)); WENN((INDEX(zl; 3) = "") + (INDEX(zl; 3) = 0); INDEX(zl; 6); 0)))); VSTAPELN({"Baugruppenpreise"."Preise eff"."Stk."."Total"}; HSTAPELN(BauGrp; EffPr; stk; EffPr * stk))))
In strukturierter Form sieht sie so aus:

Datei liegt bei:

 

Hallo Anton
Danke für deine Anpassung.
Hast du das falsche Excel angehängt? In deinem Anhang habe ich noch in G6 die Formel: =BauGrpTab3Abr(A7:F16) drin.

Gruss Marc

 

Hallo,

entschuldige meinen Irrtum! Vielleicht hatte ich die neue Version der Datei nicht abgespeichert.
In der beiliegenden Datei sollte jetzt im Arbeitsblatt "Bsp Anton" in Zelle G6 der korrekte Aufruf enthalten sein:
=BauGrpTab4Abr(A7:F16)

 

Hallo Anton
Danke für dein Feedback.
Die Formel funktioniert nun genau wie sie soll! Herzlichen Dank

Gruss Marc