Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

Forumthread: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

22.08.2024 15:00:52

KlaHu65

Hallo zusammen,
ich suche für folgendes Problem eine Excel-Formel:
A1 kann jede beliebige Zahl sein und gibt die Anzahl der Zellen an, an die Excel den Wert 1 zuweisen soll. Das heißt die Summe der vergebenen "Einsen" entspricht immer der Zahl in A1. Dabei darf der Wert 1 nur in den Zellen der folgenden Bereiche vergeben werden: C1:G5; C8:G12; C15:G19; C22:G26; C29:G33. Beispiel: wenn A1=27 dann müssen alle 25 Zellen des Bereichs C1:G5 den Wert 1 aufweisen, die übriggebliebene 2 müsste auf C8 und D8 verteilt werden. Wäre die Zahl in A1 =25 spielt sich die Belegung nur im Bereich C1:G5 ab (....dies funktioniert schon mit meiner Lösung). Wäre A1=70 würde 25 Zellen im Bereich C1:G5 und 25 Zellen im Bereich C8:G12 und 20 Zellen im Bereich C15:G19 der Wert 1 zugewiesen. Kann man das mit einer Excelformel darstellen? Mein Ansatz funktioniert, allerdings nur wenn ich die Formel durchgehend kopiere. Es wäre aber wichtig das bestimmte Zellbereiche von der Belegung mit der 1 freibleiben. Kann mir jemand helfen??
Beispieldatei:
https://www.herber.de/bbs/user/171765.xlsx
Für Eure Hilfe wäre ich Euch sehr sehr dankbar!!
Liebe Grüße

Klaus

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

22.08.2024 16:01:22

Yal

Hallo Klaus,

mit Formel eher schwierig, weil es schnell zu einem Zirkelbezug führen kann.

Mit Makro relativ easy:

Sub Befüllen()

Dim i, Zelle



    With Worksheets("Tabelle1")

        .Range("C1:G31").ClearContents

        For Each Zelle In .Range("C1:G5,C8:G12,C15:G19,C22:G26,C29:G33")

            i = i + 1

            If i > .Range("A1") Then Exit For

            Zelle.Value = 1

        Next Zelle

    End With

End Sub

(es ist kein chinesisch: einfach laut und auf deutsch lesen: mit dem Blatt "Tabelle1", den Bereich C1:G31 vom Inhalt klarmachen. Mit jede Zelle von Bereich ..., Variable i um 1 erhöhen, wenn i größer als Wert in A1 dann aus der Schleife aussteigen. Sonst die Betrachtete Zelle übernimmt den Wert 1. Nächste Zelle)

Rechtsklick auf dem Reiter des Blattes, "Code anzeigen" auswählen, in dem gerade geöffnete VBA-Codepane den Code oben einfügen, schliessen.
In Excel Alt+F8 drücken und Prozedure "Befüllen" starten.

VG
Yal

AW: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

22.08.2024 16:54:42

BoskoBiati2

Hallo,

hier mal auf die Schnelle eine einfache Variante, mit drei Blöcken:

https://www.herber.de/bbs/user/171771.xlsx

Gruß

Edgar

Hier eine weitere...

22.08.2024 17:19:10

Case

Moin Klaus, :-)

... Alternative: ;-)

=WENN(UND((SPALTE()-SPALTE($C$1))+(ZEILE()-ZEILE($C$1)-GANZZAHL((ZEILE()-ZEILE($C$1))/7)*2)*5$A$1;REST(ZEILE()-ZEILE($C$1);7)=4);1;"")

Servus
Case

AW: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

23.08.2024 10:30:46

daniel

Hmm
Warum passt deine Beispieldatei nicht zur deiner Beschreibung?

Du kannst zwar nicht mit einer Formel lösen, aber mit einer Formel für jeden Zellblock.
Diese Formel kommt dann in die linke obere Zelle jedes Blocks

Hier für deine Beispieldatei, die Formel kommt in C1, die restlichen Zellen des Blocks müssen leer sein:

=LET(

x;C1:G31;

y;SEQUENZ(ZEILEN(x); SPALTEN(x);1);

WENN(y=$A$1;1;""))

Wenn du mehrere Blöcke hast, muss du denn Zellbereich für die Variable x entsprechend anpassen.
Ab dem Zeiten Block musst du die 1 im dritten Parameter von SEQUENZ auf die Summe der vorheriegen Blöcke+1 anheben.

Wenn man deiner Beschreibung folgt, müsste in C1 diese Formel:

=LET(

x;C1:G5;

y;SEQUENZ(ZEILEN(x); SPALTEN(x);1);

WENN(y=$A$1;1;""))

In c8 diese:

=LET(

x;C8:G12;

y;SEQUENZ(ZEILEN(x); SPALTEN(x);1);

WENN(y=$A$1;26;""))

In c15 diese:

=LET(

x;C15:G19;

y;SEQUENZ(ZEILEN(x); SPALTEN(x);1);

WENN(y=$A$1;51;""))

Usw.

Gruß Daniel

AW: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

23.08.2024 12:00:58

BoskoBiati2

Hi,

das Aufteilen in Blöcke geht auch mit einer Formel:

=LET(x;C1:G31;y;SEQUENZ(ZEILEN(x); SPALTEN(x);1);WENN((y=$A$1+KÜRZEN(ZEILE(1:31)/7;)*10)*(REST(ZEILE(1:31)-1;7)5);1;""))

Gruß

Edgar

AW: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

22.08.2024 17:05:26

BoskoBiati2

Hi,

hier eine Formel für den gesamten Bereich:

=WENN(REST(ZEILE()-1;7)>4;"";--($A$1-KÜRZEN((ZEILE()-1)/7;)*25>=(REST(ZEILE(C1)-1;7)*5+SPALTE(A1))))

Gruß

Edgar

Beliebteste Forumthreads (12 Monate)

Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

Forumthread: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

Forumthread: Verteilung des Wertes 1 auf bestimmte Anzahl von Zellen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread