Mit dem letzten Spaltenwert weiterrechnen in Excel?

 - (Microsoft, Excel, Office)

6 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

Mach doch einfach die Berechnung des Gesamtsaldos auch in die Wochenendspalten rein. Du hast doch sicherlich z.B. in K7 die Formel =K6+J7 oder so drin. Das kannst du einfach nach unten ausfüllen. Da, wo in der Spalte J nichts drinsteht (oder besser wäre, wenn immer eine 0 drinstehen würde), da addiert er dann einfach nichts zum Gesamtsaldo.

Die Textfarbe kannst du dann ja einfach in der gleichen Farbe machen, wie die Hintergrundfarbe der Zellen.

Die Farben ändern sich von Jahr zu Jahr automatisch, per bedingter Formatierung.

Heißt, wenn ich deinen Lösungsansatz verfolge ist dieser Tag im Jahr 2021 der Mittwoch/Donnerstag und ich habe eine gelbe Schrift.

Ziel ist es, dass der USER nur noch das Kalenderjahr eingibt und der Rest komplett automatisch funktioniert und das ist der der letzte Punkt, der mir noch fehlt.

0
@Muhtant

Die Schriftfarbe kannst du dann doch auch per bedingte Formatierung anpassen. Das kannst du sogar in der selben Regel einstellen, in der du auch die Hintergrundfarbe einstellst.

0
@daCypher

Mhhh...das könnte klappen 😊

Dann referenziere ich auf Spalte D.

Das werden aber viele bedingte Formatierungen, da ja auch Feiertage, Urlaub, Krankheit, HomeOffice, Gleittag und Samstag/Sonntag eingefärbt werden...

Das ist sehr aufwändig.

Gibt es denn keine Funktion wie =ENDWERT(K:K)+J13

???

0
@Muhtant

Nein, eine Endwert-Funktion gibt es leider nicht.

Die anderen Lösungen, die hier genannt werden, gehen davon aus, dass in den Wochenendzeilen keine Formeln drin sind. Das heißt aber auch, dass du die Formeln bei jedem Jahreswechsel wieder in die passenden Zellen reinkopieren musst. Dann kannst du bei den Montagen auch gleich die Referenz von drei Zeilen weiter oben, anstatt nur von einer Zeile weiter oben nehmen.

Falls du doch meinen Weg weiter gehen willst: Die bereits eingestellten Regeln für bedingte Formatierung kannst du über "Bedingte Formatierung" > "Regeln verwalten" einstellen. Da kannst du dann z.B. auch auswählen, dass du die Regeln für das gesamte Blatt sehen willst und musst im Prinzip nur die gleiche Textfarbe einstellen, wie die Hintergrundfarbe in der gleichen Regel. Du brauchst also keine neuen Regeln erstellen.

0
@daCypher

Die Idee ist grundsätzlich nicht schlecht, aber der Folgeaufwand nicht gerechtfertigt, wenn ich jedes Jahr neue Formeln eingeben muss.

Auch geht natürlich die Übersicht bzgl. der bedingten Formatierungen flöten, wenn ich da so viele Dinger reinhaue. Da entstehen unweigerlich Fehler bzw. gegenseitige Störungen...leider.

Trotzdem Danke.

0
@Muhtant

Ich versteh es grade nicht. Die bedingten Formatierungen hast du doch sowieso schon drin. Wie gesagt, wegen der Textfarbe brauchst du keine neuen bedingten Formatierungen anlegen. Nur bei der Formatierung halt hinterlegen, dass zusätzlich zur Hintergrundfarbe auch die Textfarbe geändert werden soll.

Wenn du allerdings die Formeln aus den Wochenendzellen rausnimmst, damit sich Excel den letzten Wert in der Spalte holt, dann musst du wirklich jedes Jahr die Formeln korrigieren.

Aber gut. Wie du's im Endeffekt machst, ist ja deine Sache. Ich find es halt einfacher als Bedingte Formatierung die Formel =ODER(WOCHENTAG(K6) = 7;WOCHENTAG(K6) = 1) einzustellen und als Format halt Hintergrund und Text Gelb und die Formatierung dann für die ganze Zeile runterzuziehen.

0
@daCypher

Achso oder meinst du weil die bestehenden bedingten Formatierungen die ganze Zeile einfärben und durch meinen Vorschlag auch das Datum und der Tag verschwinden würden? Stimmt, dann müsstest du die bedingten Formatierungen so hinziehen, dass sie erst ab Spalte E oder sogar erst ab Spalte J gelten.

1
@daCypher

Die Matrixformel hat mir einen Fehler ausgespuckt. Ich habe das jetzt alles über "wennfehler(Formel();0)", "Oder"/"Wenn"-Funktionen und weitere bedingte Formatierungen gelöst.

Hat zwar etwas gedauert mit der richtigen Reihenfolge, aber jetzt ist alles einwandfrei.

Danke schön.

0

in K13 als {Matrixformel, Eingabe mit Strg+Shift+Enter ohne geschweifte Klammern. Tipp: rechte Strg+Shift-Taste verwenden, geht dann mit einer Hand und merkt sich auch leichter}:

=INDEX(K$1:K12;MAX((K$1:K12<>"")*ZEILE(K$1:K12)))+J13

Überbrückt Dir beliebige Lücken, wenn zB der Hl Abend auf Mi fällt, so dass es erst am Mo danach wieder Weitergeht, oder wenn Urlaub keinen Eintrag ergibt (sofern das so ist).

Diese Formel darf dann aber nicht in den freien Tagen stehen bzw muss an Feiertagen wieder gelöscht werden.

Insofern musst Du doch von Jahr zu Jahr nachkorrigieren.

Oder Du bettest die Formel in eine mehrfache Wenn-Bedingung ein, die Sa-/Sonntage [=Rest(Datumsbezug;7)<2] und Feiertage (zB aus einem Listenverweis), ausserdem sonstige nicht zu zählende Tage als "" kennzeichnet, da ja nur die Bedingung <>"" den nächstoberen Wert zur Berechnung verwendet. Kann recht länglich werden...

Die Rest-Formel ist der Wochentag-Formulierung vorzuziehen, weil sie GebietsSchema-unabhängig immer Sa+So liefert und <2 würde auch Datum+Zeitangaben , also Sa+So bis 23:59:59 einbeziehen.

In Ländern, wo am Fr oder ggf.am Sabbat+sonstwann nicht gearbeitet wird, muss man das dann etwas komplexer verformeln. Ebenso für Friseure oder GaststättenPersonal mit festen Ruhetagen.

Die Matrixformel hat mir einen Fehler ausgespuckt. Ich habe das jetzt alles über "wennfehler(Formel();0)", "Oder"/"Wenn"-Funktionen und weitere bedingte Formatierungen gelöst.

Hat zwar etwas gedauert mit der richtigen Reihenfolge, aber jetzt ist alles einwandfrei.

Danke schön.

1
@Muhtant

Kann sein, dass wenn in K1 oder sonstwo in der Liste ein Text steckt, dass dann Max oder +Jn Fehler liefern in diesem Fall bei K2 beginnen (aber nicht Index!), also

=INDEX(K:K;MAX((K$2:K12<>"")*ZEILE(K$2:K12)))+J13

Die Spanne $2:12 muss in Max und Zeile gleich sein, Indey muss weiterhin mit 1 als oberster Zelle beginnen (weil Zeile ausgespuckt wird als Zählargument für Index. Als Matrix wird nur Max()*Zeile() berechnet, das kannst Du aber m.W. nicht steuern.

0

Frag doch Spalte D, den Tag, mit ab:

WENN(D13="Mo";K10+J13;WENN(ODER(D13="Sa";D13="So");"";K12+J13))

(ungetestet)

Danke, du hast mir die zündende Idee geliefert.

Ging auch auf diesem Weg:

=WENN(J13="";""WENN(D13="Mo";K10+J13;K12+J13))
1
@Muhtant

Allerdings, was ist, wenn Montag Feiertag ist?

0

Der Ansatz war wirklich gut, aber bei Tagen die dazwischen frei sind, funktioniert es leider nicht.

Habe es mit einer Matrixfunktion versucht die den letzten Spaltenwert angibt, aber hier gibt es dann einen Zirkelfehler, wenn ich mit diesem Wert nachfolgende Werte berechnen will.

{=Index(K:K;Max(IstZahl(K6:K371)*Zeile(K6:K371)))}
0
@Muhtant

Das erscheint mir zu kompliziert. Dann würde ich eher so vorgehen, dass ich in einer Hilfsspalte alle kontinuierlich summieren würde, den Wert der Hilfsspalte nur in K 1:1 anzeigen würde, wenn der tägliche Saldo größer als 0 ist und dann die Hilfsspalte ausblenden.

0
@Suboptimierer

Die Matrixformel hat mir einen Fehler ausgespuckt. Ich habe das jetzt alles über "wennfehler(Formel();0)", "Oder"/"Wenn"-Funktionen und weitere bedingte Formatierungen gelöst.

Hat zwar etwas gedauert mit der richtigen Reihenfolge, aber jetzt ist alles einwandfrei.

Danke schön.

1

Ich habe etwas ähnliches gestrickt.
Gelöst habe ich es, indem ich einfach Sa+So+Feiertage den Saldo weiter berechnet habe mit einer Arbeitszeit von 0 Stunden.

So oder so: Um eine solche Übersicht flexibel zu halten solltest Du noch eine Spalte mit der "Sollzeit" jeden Tages einfügen.

Ich möchte ja aber nicht, das am Wochenende oder den Feiertagen etwas drin steht...soll ja bewusst leer bleiben.

Die Sollzeit wird direkt über die Stammdaten in einer Zelle vorgegeben.

0
@Muhtant

Wieso so kompliziert?
Dann manch halt eine bedingte Formatierung, die prüft ob Wochenende ist und stelle damit die Textfarbe auf die Hintergrundfarbe ein. Schon ist die Zelle "leer".

0
@mchawk777

Aber für die fortlaufenden Zellen, hätte ich dann schon überall was drin stehen.

Die Rechnung startet aber erst, wenn in "Gehen" was drin steht. Bei Sa So entfällt das.

Feiertage die per SVerweis einbezogen werden lassen sich so nicht mit mitformatieren...

Wie du siehst, ist es nicht ganz sooo trivial.

0
@Muhtant

"leer bleiben": muss es tatsächlich leer sein oder reicht es, wenn es leer erscheint?

Dann ginge es über eine bedingte Formatierung mit der Bedingung J="". (kann man ja zusätzlich zur Jahres-Schriftfarbe setzen und die Schriftfarbe an die Wochenend-Farbe knüpfen. (Frühere Versionen haben sowas dann trotzdem im Ausdruck schwarz gedruckt, weiß nicht. ob das noch so ist, und Schriftfarbe transparent gabs nicht)

Ansonsten wirst Du nämlich das Problem unterschiedlicher Feiertagsgruooen/Kombinationen mit Wochenenden nicht los bzw nur sehr aufwändig.

Und wenn Du die Zellen der Spalte K sperrst, sieht ohnehin kein Schwein, was tatsächlich unsichtbar formatiert drinsteht

1
@Iamiam

Mit einer J="" - Formel könnte es ein Problem mit dem "durchschleifen" des Saldos geben. Deshalb war mein Tip auch einfach die Schriftfarbe an die Hintergrundfarbe anzupassen und schon erscheint die Zelle "leer".

Ist m. E. einfacher als die Dinge nur zu kompizieren, nur wein die Zelle wirklich leer sein - und nicht nur leer erscheinen soll.

0

Hallo,

hier ein Ansatz für dich, um den Wert des letzten Eintrages der Range einer Spalte auszulesen.

http://www.excelformeln.de/formeln.html?welcher=48

LG, Chris

Für dich konkret also so:

{=WENN(J13="";"";INDEX(K$6:K12;MAX((K$6:K12<>"")*ZEILE($1:7)))+J13)}

Eingabe erfolgt ohne {}, Formel muss mit STRG + Umschalt + Enter bestätigt werden.

0
@Funfroc

Probiere ich Montag mal aus...habe unten eine ähnliche Matrixfunktion benutzt, aber da gab es Zirkelfehler

0
@Muhtant

Die Matrixformel hat mir einen Fehler ausgespuckt. Ich habe das jetzt alles über "wennfehler(Formel();0)", "Oder"/"Wenn"-Funktionen und weitere bedingte Formatierungen gelöst.

Hat zwar etwas gedauert mit der richtigen Reihenfolge, aber jetzt ist alles einwandfrei.

Danke schön.

0
@Muhtant

Hallo,

die Formel ist eigentlich getestet. Welchen Fehler hast denn bekommen?

LG, Chris

0
@Funfroc

#Wert

Kann aber an den ganzen Verweisen etc. liegen, die dahinter stehen.

0

Was möchtest Du wissen?