Zahlen aufteilen und nach Häufigkeit prüfen

Question

Hallo miteinander

Kann mir jemand bei folgendem Problem helfen

Ich habe eine Tabelle mit Überschriften

[list] div. Spaltmasse"18,22,34,52,6,27,29..." / Unterlagen1-Stk / Unterlagen2-Stk / Unterlagen3-Stk / mögl. Unterlagen"1,2,3,5,8,10,12,15,20" / Anzahl der einz.Unterlagen[/list].

In der Spalte Spaltmassen sind die Dicken angegeben die ich füllen muss.
Unter mögl. Unterlagen steht welche Dicken ich zur Verfügung habe.
Jetzt möchte ich in der Spalte "Anzahl der einz. Unterlagen" eine Abfrage machen die wie folgt aussehen soll.

Beispiel : Die Formel sollte nun das erste Spaltmass "18" mit den "möglichen Unterlagen vergleichen und eine Zählung machen.
Also erste mögliche Unterlage die in 18 reinpasst wäre 15. Rest 3. Danach passt einmal die 3 in die 3.
Das Ergebnis würde jetzt heissen ich brauch eine 15er Unterlage und eine 3 Unterlage.
usw. usw.. Bis schlussendlich die Gesamtanzahl aller Unterlagen feststehen.
Die Liste "mögliche Unterlagen" kann auch an einem anderen Ort stehen. Sie müssen nicht unmittelbar dahinter stehen.
Ich hoffe jemand kann mir dabei helfen.
Danke im voraus.
blauermerlin

Answer 1

Die Summenproduktformel lässt sich auch einkürzen auf:

=SUMMENPRODUKT(($B$3:$B$10=J3)*($C$3:$C$10)+($D$3:$D$10=J3)*($E$3:$E$10)+($F$3:$F$10=J3)*($G$3:$G$10)+($H$3:$H$10=J3)*($I$3:$I$10))

Answer 2

Hallo Mr. K. ,

Von wegen böse sein, im Gegenteil ich freue mich, dass es Dir gelungen ist, das Problem auf die Reihe zu kriegen.

Das ist ja der Sinn und Zweck, dass wir voneinander was lernen.

schönen Tag noch

Gruß
Paul1

Answer 3

Das ist ja der Sinn und Zweck, dass wir voneinander was lernen.

Auch ich habe in diesem Forum schon einiges dazu gelernt. Immer wenn ich denke, ich würde mich auskennen, kommt irgendjemand mit einer gänzlich anderen Lösung daher, auf die ich im Leben nicht gekommen wäre. Deshalb auch von meiner Seite an dieser Stelle herzlichen Dank an alle Helfer für Ihre tägliche Mühe.

Mr. K.

Answer 4

Hallo

Ich danke Euch für die Lösung des Beispiels 1.
Jetzt muss ich die Formeln noch ein wenig analysieren damit ich auch kapiere wie diese genau Funktioniert. Ich will ja nicht nur Abschreiben. :-) gelle.

Vieleicht hat auch jemand eine Lösung für Beispiel 2.

Vielen Dank
Joschi.b

Answer 5

Hallo Joschi,

zur Erklärung, SVERWEIS sucht standardmäßig nach dem ersten Treffer der kleiner oder gleich Suchkriterium ist. Diesen Wert braucht man dann nur noch vom Ausgangswert abzuziehen und mit dem Rest weiterzurechnen (wie in Antwort 10 dargestellt). Die 1 als vierten Parameter von SVERWEIS kannst du dabei auch weglassen. Mit Wennfehler umgehst du zurückgegebene Fehlerwerte, wenn der Suchwert zu klein wird.

zu Beispiel2 hast du in diesem Thread bisher noch gar nichts geschrieben. Auch hier solltest du zunächst Spalte B sortieren, damit SVERWEIS richtig arbeitet. Warum du die Zelle so komisch verbunden hast, kann ich nicht nachvollziehen. jedoch gilt auch hier z.B. für Zelle D8 =SVERWEIS(C5;$B$8:$B$12;1) und für E8 =SVERWEIS(C5-D8;$B$8:$B$12;1) usw. Da du hier mehrere Versionen mit immer kleineren Werten rechnest solltest du dann Zeile für Zeile die Suchmatrix B8:B12 verringern. In Zeile 9 also nur noch B8:B11 usw. Zur Berechnung der Stk-Zahl kannst du hier mit Zählenwenn arbeiten. z.B. in C8 =Zählenwenn($D$8:$I$11;B8), dann runterziehen.

Mr. K.

Answer 6

Hallo

Danke schonmal.

Es ist so das die Darstellung D8-I17 eigentlich eine Grafische Dartellung sein soll. Es soll optisch Darstellen welche Stäbe in ein 6m Stab reinpassen.
Also wenn jemand in einer Werkstatt einen 6m Stab auf die Säge legt, sieht er sofort mit welchen er anfangen muss.
Würde er z.b zuerst die kleineren nehmen, ginge die bestellte Anzahl dann nicht mehr auf.

Deshalb würde ich gern die Spalte B nicht sortieren, da dies aus einer Stabkarte rausgeschrieben wird.
Man könnte diese aber auf einem anderen Tabellenblatt vorher sortieren lassen.

Die Formel oder Programm soll eben eine Abfrage machen die vom längsten zum kleinsten Stab geht.

Ich hoffe es das ich es ein wenig erklären konnte.


Joschi.b

Answer 7

Dann beziehe dich doch auf ein anderes Blatt oder einen anderen Bereich, der nicht Teil der Tabelle ist. z.B. Zelle D8 =SVERWEIS(C5;Tabelle2!$A$1:$A$5;1) Die aufsteigende Sortierung ist für SVERWEIS hier von essentieller Wichtigkeit, sonst klappts nicht. Wenn du die Werte per Formel nach Tabelle2 ziehen willst könnte die Formel in A1 von Tabelle2 lauten: =KKLEINSTE(Tabelle1!$B$8:$B$12;ZEILE(A1)). Dann runterziehen. Damit werden die Werte aus Spalte B in Tabelle2 automatisch sortiert.

Gruß Mr. K.

Answer 8

Hallo

Danke für die Antwort.
Ich hab nicht ganz Verstanden wie ich die Spalte -C mit der Stückzahl zusammen mit der Spalte -B sortieren soll.
Will ja die Stk mit den Längen nicht durcheinander bringen
Müsste ich die Formel C8 =Zählenwenn($D$8:$I$11;B8) nicht auch in die Tabelle 2 reinschreiben?

Joschi.b

Answer 9

Ich hatte das Beispiel so verstanden, dass du die 6000 in der Tabelle rechts anhand der Daten in Spalte B aufteilen willst und in Spalte C dann lediglich berechnest wie oft die Zahl von Spalte B in der Tabelle rechts vorkommt. Für die Berechnung der Stückzahlen mit Zählenwenn ist die Sortierung irrelevant und kann durchaus in C bleiben. In der Tabelle rechts hast du keine Felder für Stückzahlen hinterlegt, stattdessen setzt du manche Daten mehrfach an. Also ist für die Berechnung der Daten Spalte C nicht nötig und wird erst am Ende berechnet.

Falls ich damit falsch liege, erkläre doch bitte nochmal, wie du Spalte C in die Datenberechnung einbindest.

Mr. K.

Answer 10

Hallo zusammen,

habe mich schon einmal mit was Ähnlichen beschäftigt und bin kläglich gescheitert.


https://supportnet.de/t/2450715


Gruß
Paul1