Einzelwerte auf Höchstwert verteilen

[Bud]

Hallöchen,

ich frage mich ob und wenn wie ich via Excel folgendes bewerkstelligen kann:

Gegeben sind versch. Zahlenwerte in einer Spalte. Nun soll herausgefunden werden wie diese sich am optimalsten auf einen gewissen Höchstwert verteilen ließen.

Genauer gesagt geht es um Speichermengen im GB Bereich, welche auf theoretisch beliebig viele 1 TB Medien untergebracht werden sollen.
Es soll die Frage geklärt werden: Wie viele 1 TB Medien benötige ich, wenn die einzelnen GB Daten optimal addiert und verteilt werden?

Danke im Voraus,
Martin.

P.s.
Mein Excel-Wissen beschränkt sich auf Funktionen sowie das Aufzeichnen von Makros.

[Ego]

Hallo Bud,

wenn ich deine Anfrage richtig verstanden habe entspricht sie (da eindimensional) einem Zuschnitt von Rohrstücken/Brettern aus Rohren/Leisten vorgegebenen Längen (auch wenn es sich komisch "anfühlt").

Für eine optimale Zuordnung der Dateien müste man eigentlich alle möglichen Kombinationen ausprobieren. Das kann aber auf einem einfachen Rechner (da die Anzahl der möglichen Kombinationen exponentiell zu der Anzahl der Dateien steigt) schon bei einigen hundert Dateien Jahrhunderte dauern.
Wieviel Dateien sollen denn im Normalfall aufgeteilt werden?

In der Anlage eine Datei für die Aufteilung von Zuschnitten und hier einige Bemerkungen

A) mm werden zu GB

B) Benutzung
1. In den Zellen B13:C27 werden die Anzahl und Grössen der Speichermedien eingetragen.
2. In den Zellen H8: AB9 werden die Anzahl und Grössen der Dateien eingetragen.
3. Über die fünf Button werden die Makros der Datei gestartet.

C) Funktionalitäten der Makros


1. "Aufteilung übernehmen"
Hiermit werden die im Arbeitsblatt "'Aufteilung" über Formeln ermittelten Aufteilungen zusammengefasst. Für dieses Verfahren ist eine Sortierung der Dateien im Bereich H8:AB9 nach Grösse sinnvoll.
2. "optimieren"
Dieses Programm ermittelt im Code die gleiche Aufteilung wie 1, versucht dann aber in einer vorgegebenen Zeit ("Zeit ohne Verbesserung") durch andere Kombinationen die Anzahl der notwendigen Speichermedien zu verringern.
3. "verteilen"
Dies ist ein anderes Verfahren und eventuell sinnvoller wenn viele gleichgrosse Dateien vorhanden sind.

4. "verbessern optimiert"
Dieses Programm nimmt aus den Ergebnissen der Funktionen 1-3 nach vorgegebenen Regeln eine Teilmenge ("Verbesserung") an Dateien und versucht  in der vorgegebenen Zeit ("Zeit ohne Verbesserung") für diese Teilmenge eine verbesserte Aufteilung zu finden.
5. "verbessern zufällig"
Dieses Programm nimmt aus den Ergebnissen der Funktionen 1-3 zufällig eine Teilmenge ("Verbesserung") an Dateien und versucht  in der vorgegebenen Zeit ("Zeit ohne Verbesserung") für diese Teilmenge eine verbesserte Aufteilung zu finden.


D) Gruppieren
Bei Zuschnitten von Rohren und Leisten werden häufig Stücke gleicher Länge benötigt. Ich kann mir vorstellen, dass deine Dateien alle unterschiedlich gross sind.
Ich nehme an, dass eine Zusammenfassung von Dateien ähnlicher Grösse zu Gruppen (Maximum der einzelnen Dateigrössen) zwar im Einzelfall verhindert, dass eine Datei noch in eine Speicherlücke passt, durch die Reduzierung der möglichen Kombinationen die Rechenzeit für die Suche nach einem besseren Ergebnis aber sehr stark verkürzt.

[Bud]

Hey helmut,

danke für die schnelle Antwort und die Erläuterungen dazu.
So ganz leuchtet mir die Übertragung von Speichermengen auf Rohre und Bretter
in der Excel Tabelle aber nicht ein, bzw. wie ich das übertragen soll.


Ich habe mal in B13 1000mm eingetragen und eine Anzahl von 100 ( als beispielhafte Anzahl die zur Verfügung stehen?)
Eine H-Spalte konnte ich dann in der zweiten Tabelle leider nicht finden (H8:AB9), in die ich dann die Einzelwerte eingetragen hätte.
Da stehe ich grad ein Stück weit auf dem Schlauch.

Tut mir leid, falls dein Aufwand mir zu helfen nicht gefruchtet hat.


Nochmal die Ausgangssituation:

Ich habe ca. 100 Werte/Dateien. Diese haben eine Größe zwischen ca. 60GB und 700GB.
Diese müssen zusammenhängend bleiben / die Einzelwerte dürfen nicht aufgeteilt werden.

Die Speichermedien sind jeweils 1TB also 1000 GB groß und liegen theoretisch in beliebiger Anzahl vor.

Ein Rest darf in meinem Fall nur für die Speichermedien welche dann nicht zur Gänze ausgefüllt werden, nicht aber für die Einzelwerte/Dateien entstehen.

(Wie viele 1 TB Medien benötige ich mindestens, wenn die Einzelwerte optimal verteilt sind?)

Also ja, da müssten schon sehr viele Kombinationen durch gespielt werden.


Grüße,
Bud

[Ego]

Hallo Bud,

ich habe die Datei einmal auf deine Bezeichnungen umgeschrieben und hier einige Erklärungen:

A) Erklärung der aktuellen Daten

1. Eingaben
Zellen H8:AA9
Insgesamt habe ich 100 Dateien, davon 5 zwischen 676 und 700 GB; 5 zwischen 651 und 675 GB;...;8 zwischen 476 und 500 GB;...
Zellen B13:B14
Ich habe 70 Platten a 1024 GB zur Verfügung

2 Ergebnisse gesamt
Zellen D13 und G5
Ich benötige insgesamt 47 Platten
Zellen AD5:AF5
Die 47 Platten haben 48.128 GB und ich nutze davon nicht 1.863 GB. Das sind 4%


3. Ergebnisse einzeln
Zellen F12:AA26
Ich bespiele:
5 Platten der Grösse 1 TB mit jeweils einer Datei zwischen 676 und 700 GB und einer Datei zwischen 276 ud 311 GB
5 Platten der Grösse 1 TB mit jeweils einer Datei zwischen 651 und 675 GB und einer Datei zwischen 326 ud 334 GB
.
.
1 Platten der Grösse 1 TB mit einer Datei zwischen 70 und 250 GB.

B) Funktionen
Die obigen Ergebnisse stammen aus der einfachen Aufteilung.
Über den Button "optimieren" wird keine Verbesserung erreicht.
Über den Button "verteilen" erhalte ich ein schlechteres Ergebnis (51 Platten mit 5.959 GB Rest), dass ich aber über den Button "verbessern optimiert" wieder auf 47 Platten und einem Rest von 1.863 GB verbessern kann.

C) Unterschiedlich grosse Speichermedien
Wenn ich dem Programm zusätzlich (in B14:C14) 30 Platten a 1,5 TB zur Verfügung stelle, behält die Aufteilung zwar die gleichen Werte, aber über "verteilen" erhalte ich eine bessere Lösung mit 15 Platten a 1 TB und 21 Platten a 1,5 TB und 1.351 GB Rest.
Auch über den Button "optimieren" erhalte ich das Ergebnis von 1.351 GB Restspeicher, aber diesmal mit 45 Platten a 1 TB und 1 Platte a 1,5 TB.

D) Erweiterung
Zur Zeit ist "nur" die Eingabe von 21 verschiedenen Dateilängen möglich. Das ist erweiterbar, aber meines Erachtens nur sinnvoll, wenn die Anzahl der Dateien nicht viel mehr als 100 ist.


ps zu

Also ja, da müssten schon sehr viele Kombinationen durch gespielt werden.

Bei 100 Dateien habe ich ca. 9,3E+157 Permutationen. Das sind mehr als Atome im Universum und ist unmöglich "durchzuspielen". Du must dich also mit einer nicht unbedingt optimalen Lösung abfinden.