Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Wenn Bedingung erfüllt Leerzeilen füllen

Wenn Bedingung erfüllt Leerzeilen füllen
28.03.2021 18:13:47
Mani
Guten Abend ,
ich stehe schon wieder vor einem Problem und komme nicht weiter.
Ich habe eine Tabelle wo in Zeile 1 fortlaufend ein Datum steht.
Ich bräuchte eine Möglichkeit das Datum zu prüfen ob es ein Wochentag ist.
Wenn ja sollen mir darunter alle Leerzeilen mit einer Null gefüllt werden bis zur letzten Beschriebenen Zeile.
Wenn das Datum ein Samstag oder Sonntag ist soll nichts passieren.
Habt ihr eine Idee oder eine Lösung ?
Ich selber kriege das leider nicht hin.
https://www.herber.de/bbs/user/145172.xlsx
Vielen Dank
beste Grüße
euer Mani

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Wenn Bedingung erfüllt Leerzeilen füllen
28.03.2021 18:16:29
Hajo_Zi
Hallo Mani,
jedes Datum hat beinen Wochentag.
ich vermute das is nur mit VBA realisierbar.
Dein Vorhaben ist nicht möglich. Da eine XLSX Datei kein Makro enthält.
Bild
Homepage

AW: Wenn Bedingung erfüllt Leerzeilen füllen
28.03.2021 18:45:07
Mani
Hallo Hajo,
https://www.herber.de/bbs/user/145174.xlsm
Format habe ich geändert
Vielen Dank
Gruß
Mani

Anzeige
AW: Wenn Bedingung erfüllt Leerzeilen füllen
28.03.2021 18:55:08
Hajo_Zi
Hallo Mani,
mein Beitrag muss man nicht komplett lesen.
Option Explicit
Sub Makro3()
Range("D2:O4").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "0"
End Sub
Gruß Hajo

AW: Wenn Bedingung erfüllt Leerzeilen füllen
28.03.2021 18:17:56
Herbert_Grom
Hallo Mani,
die Datumseinträge in Zeile 1 hören doch sicher nicht beim 12.3. auf, oder?
Und wie viele Zeilen nach unten wird deine Originaldatei gehen?
Servus

AW: Wenn Bedingung erfüllt Leerzeilen füllen
28.03.2021 18:42:53
Mani
Hallo,
das Datum geht bis zum Ende des Jahres in der Originaldatei.
Nach unten gehen die Zeilen bis 480.
Vielen Dank

Anzeige
AW: Wenn Bedingung erfüllt Leerzeilen füllen
29.03.2021 09:55:21
Herbert_Grom
Hallo Mani,
aufgrund des Hinweises von Günther, habe ich dir auch noch in das Sheet und den Code die Feiertagsabfrage eingebaut. Probiers mal!
https://www.herber.de/bbs/user/145180.xlsm
Servus

AW: Wenn Bedingung erfüllt Leerzeilen füllen
29.03.2021 11:45:17
Daniel
Hi
das geht auch ohne Schleife:
nur mit berücksichtigung der Wochendenden:
Sub test()
With Range("C1").CurrentRegion
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(Weekday(R1C,2)>5,"""",0)"
.Formula = .Value
End With
End Sub

mit Berücksichtigung der Feiertage mit deiner Vorgabe:
Sub test()
With Range("C1").CurrentRegion
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(OR(WEEKDAY(R1C,2)>5,COUNTIF(Rng_Feiertage,R1C)),"""",0))"
.Formula = .Value
End With
End Sub
das ganze kann man auch ohne Makro von Hand einfach ausführen.
1. ganze Tabelle markieren (am schnellsten so: linke obere Zelle markieren und STRG+A drücken
2. über Inhalte auswählen (F3 oder START - BEARBEITEN - SUCHEN UND AUSWÄHLEN - INHALTE...) die Selektion auf die Leerzellen einschänken
3. die Formel in eine der selektieren Zellen eingeben (Spalte ggf anpassen, je nachdem wo die Eingabezelle liegt):
=WENN(ODER(WOCHENTAG(D$1;2)&gt5;ZÄHLENWENN(Rng_Feiertage;D$1));"";0)
und eingabe mit STRG+ENTER abschließen
4. nochmal alle Zellen markieren und über Inhalte auswählen, die Zellen mit Formeln + Text selektieren.
5. diese Zellen leeren
6. ein weiteres mal alle Zellen auswählen, kopieren und als Wert einfügen.
der manuelle Ablauf muss gegenüber dem Makro etwas erweitert werden, weil im Makro mit .Formula = .Value die Texte "" in echte Leerzellen gewandelt werden.
beim Manuellen Inhalte Einfügen - Wert passiert das nicht, da bleiben diese Zellen Text, sehen aber aus wie Leerzellen.
Das kann manchmal probleme bereiten.
Daher dieses vorgehen, damit die Leerzellen an den Wochenenden auch Leerzellen bleiben.
sollten die zu beginn vorhandenen Zahlen auch vollständig oder Teilweise aus Formeln bestehen, so sollte man dieses Verfahren nicht anwenden, da hierbei dann auch diese Formeln in Festwerte gewandelt werden.
Gruß Daniel

Anzeige
AW: Wenn Bedingung erfüllt Leerzeilen füllen
29.03.2021 15:41:32
Mani
Vielen Dank -
der Mani

AW: Wenn Bedingung erfüllt Leerzeilen füllen
29.03.2021 15:45:19
Herbert_Grom
Hallo Mani,
"vielen Dank" ist prima, aber funzt es auch so wie du es haben wolltest?
Servus

AW: ist möglich mit einer kleinen Formel, doch ...
28.03.2021 18:57:58
neopa
Hallo Mani,
... wozu brauchst Du überhaupt eine 0 in diesen Zellen. Echte Leerzellen werden doch wie eine 0 auch so in Excel schon gewertet.
Die Formellösung sähe wie folgt aus:
Markiere den gesamten Bereich D2: XX## Betätige [F5] und aktiviere da "Inhalte" und danach die Option "Leerzellen".
Dann gib in der Eingabezeile folgende Formel bezogen auf Deine Beispieldatei folgende Formel ein: =WENN(REST(E$1;7)&lt2;"";0) und betätige [Strg]+[Enter]
Achtung: Anstelle E$1 müsstest Du die Spalten-Zelladresse in der Formel eingeben, die in Zeile 2 die erste Leerzelle hat.
Gruß Werner
.. , - ...

Anzeige
AW: ist möglich mit einer kleinen Formel, doch ...
28.03.2021 19:35:56
Mani
Vielen Dank ihr beiden...
Und wieder bin ich Dank euch einen Schritt weiter.
Besten Dank
Viele Grüße
der Mani

AW: ist möglich mit einer kleinen Formel, doch ...
28.03.2021 22:42:27
Günther
Moin,
eine xlsm-Datei (wie die von Herbert) öffne ich nicht aus Datenhygienischen Gründen (nur *.xlsx). Darum habe ich deine durchaus korrekte erste Datei genommen und das Geschwätz vom "Großen Zampano" wieder einmal nur bedauert.
Aber wenn ich lese, dass das Datum bis zum Jahresende geht da frage ich mich, welches Ergebnis du beispielsweise am Freitag, 02.04.2021 und Montag, 05.04.2021 erwartest …
Und ach ja, ich finde es schon allein aus Gründen der Optik (und auch der Datengenauigkeit) besser, wenn eine leere Zelle wirklich leer ist und weder "" (also einen Leerstring) noch eine 0 enthält. - Und deine angestrebte Lösung ist mit etwas Aufwand durchaus über Daten | Abrufen und transformieren zu realisieren. (Hier dürfen sich die anderen PQ-Spezis gerne einmal "austoben" ☺ Mein gesetztes Zeitlimit für die (kostenlose) Foren-Hilfe hatte ich überschritten.) Hier aber noch einige Stichworte, die mich zum Ziel brachten:
• Dummy-Füllwerte
• Entpivotieren
• Abgleich zum Wochentag
• Pivotieren
… und natürlich einige kleinere Zwischenschritte.
 
Gruß
Günther  |  mein Excel-Blog

Anzeige
AW: da bräuchte es weder VBA, Formel oder PQ ---
29.03.2021 09:04:35
neopa
Hallo Günther,
... wenn in die vorhandenen Leerzellen keine 0en eingesetzt werden sollen, worauf ich ja auch schon hingewiesen hatte.
Gruß Werner
.. , - ...

mögliches Mißverständnis
29.03.2021 11:57:28
Daniel
"Echte Leerzellen werden doch wie eine 0 auch so in Excel schon gewertet."
stimmt so nicht ganz. Das trifft nur für Einzelwertberechnungen zu
Für AuswerteFunktionen wie Mittelwert oder Produkt macht es schon einen Unterschied, ob es eine echte Leerzelle ist oder die Zelle eine 0 enthält:
Mittelwert(2;0) = 1
Mittelwert(2;Leer) = 2
wenn hier im Nachgang bspw der der durchschnittliche Wert pro Arbeitstag ermittelt werden soll, kann es also durchaus sinnvoll sein, die Leerzellen der Arbeitstage mit 0 zu füllen und die Wochenenden leer zu lassen.
Gruß Daniel

Anzeige
AW: kann sein aber auch nicht ...
29.03.2021 12:50:36
neopa
Hallo Daniel,
... deshalb hatte ich den TE diesbzgl. nachgefragt.
Auch eine MITTELWERT()-Bildung kann zwar mit aber auch ohne Berücksichtigung von 0-Werten gewünscht sein.
Das kann nur der TE wissen und entscheiden.
Gruß Werner
.. , - ...

AW: kann sein aber auch nicht ...
29.03.2021 14:52:31
Daniel
auf was beziehst du dein "kann sein aber auch nicht..."?
meine Hauptaussage war, dass Funktionen wie Mittelwert eben doch unterscheiden, ob eine Zelle 0 enthält oder leer ist.
darum gings zunächst einmal, weil du ja behauptet hast, dass dass keinen Unterschied macht.

AW: genau auf Deine Aussage bezog sich meine owT
29.03.2021 15:05:05
neopa
Gruß Werner
.. , - ...

AW: genau auf Deine Aussage bezog sich meine owT
29.03.2021 15:09:45
Daniel
dann ist dein "kann sein oder auch nicht..." bullshit.
sorry,

Anzeige
AW: dann hast Du es wohl nicht verstanden, ...
29.03.2021 15:22:49
neopa
Hallo Daniel,
... obwohl ich meine Aussage: "kann sein aber auch nicht ..." im entsprechenden Beitrag bezogen auf Deine Aussage: "... wenn hier im Nachgang bspw der der durchschnittliche Wert pro Arbeitstag ermittelt werden soll, kann es also durchaus sinnvoll sein, die Leerzellen der Arbeitstage mit 0 zu füllen ..." durch meine mit: "Auch eine MITTELWERT()-Bildung kann zwar mit aber auch ohne Berücksichtigung von 0-Werten gewünscht sein.
Das kann nur der TE wissen und entscheiden."
näher erläutert hatte und einleitend im Text sogar darauf verwies: "... ... deshalb hatte ich den TE diesbzgl. nachgefragt.".
Gruß Werner
.. , - ...

Anzeige
AW: dann hast Du es wohl nicht verstanden, ...
29.03.2021 16:21:33
Daniel
Das habe ich dann wirklich falsch verstanden.
Ich bei eigentlich davon ausgegangen, das ich mit "wenn" und "beispielsweise" eigentlich schon genügend darauf hingewiesen habe, dass es sich bei dieser Aussage nur um eine hypothetische Annahme handelt, so dass dein "kann sein kann nicht sein " eigentlich nicht mehr notwendig ist.
Wichtiger war mir darauf hinzuweisen, dass LeerZelle = 0 eben nicht immer gilt (deine Aussage) sondern nur in bestimmten Fällen.
Gruß Daniel

353 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige