Für unseren hausbau habe ich einen Zahlungsplan erstellt, der zeigt zu Wann eelche Beträge fällig werden.
Zusätzlich gbt es noch einen Soll/Ist Vergleich. Bezahlte Beträge werden grün hinterlegt.
Für den IST- Wert möchte ich nun alle Zellen aus einer Reihe zusammen addieren, die einen grünen Hintergrund haben.
Dazu habe ich im NamensManager eine neue Funktion (HintergrungFarbe) angellegt.
=ZELLE.ZUORDNEN(38;Zahlungsplan!D18)
nun wollte ich die Summe folgendermaßen bilden:
=SUMMEWENNS(E18:Q18;E18:Q18;HintergrundFarbe=36)
Leider funktioniert das so leider nicht...
Auf eine einzelne Zelle angewendet wird mit der Funktion HintergrundFarbe der richtige wert ausgegeben aber in der Formel scheint das so nicht zu gehen.
15.09.2023, 09:51 (Dieser Beitrag wurde zuletzt bearbeitet: 15.09.2023, 09:55 von Ego.)
Hallo s...,
A) tu dir das nicht an.
Anstelle den Zellen manuell eine Farbe zu geben, solltest du den Status "Betrag bezahlt" als Kennung in einer extra Spalte eintragen. Dann kannst du die Beträge mit diesem Status summieren und den Zeilen über bedingte Formatierung die Farbe geben.
C) Warum gehts nicht so wie du gedacht hast?
Die Funktion Zelle.Zuordnen benötigt als zweiten Parameter einen Bezug. Über die benannte Formel, hast du einen relativen Bezug zugeordnet. Wahrscheinlich links von der aktuellen Zelle.
Deine Formel "=SUMMEWENN..." prüft jetzt die Farbe der Zelle links von dieser Formelzelle. Wenn diese zufällig die Farbe 36 hat, werden alle Zellen addiert, die als WAHR erkannt werden, ansonsten alle Zellen, die als FALSCH erkannt werden (also 0).
Das ist wohl nicht beabsichtigt.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
15.09.2023, 10:21 (Dieser Beitrag wurde zuletzt bearbeitet: 15.09.2023, 10:22 von HKindler.)
Hi,
zur Ergänzung: SUMMEWENNS versteht als Kriterium nur einfache Vergleiche, die auch noch als Text übergeben werden müssen. ">=0"
"<"&Jahr(HEUTE())
10
wären z.B. solche Vergleiche (bei letzem wird einfach "=10" verwendet).
Es wird immer der komplette Bereich auf ein (festes) Kriterium angewendet.
Also wenn schon, dann brauchst du eine Hilfsspalte (bzw. in deinem Fall eine Hilfszeile), in der du den Farbwert ausgeben lässt.
Übrigens: Excel ist eher auf Auswertung von Spalten ausgelegt als auf Auswertung von Zeilen, sprich normalerweise würde man fortlaufende Zahlungen untereinander und nicht nebeneinander aufführen.
Aber lass dir sagen: Farben sind eine ganz schlechte Ausgangsbasis um damit zu rechnen. Erstens ist Excel nicht dafür ausgelegt und zweitens siehst du den Unterschied zwischen dieser Farbe (RGB 00CF00) und dieser Farbe (RGB 00C000)? Also ich traue es mir nicht zu die beiden sicher zu unterscheiden. Dabei sind sie bereits 16 Farbstufen auseinander!
In den Zeilen stehen die Werte für die einzelnen Kostenstellen (Gebühren, Kosten, Abschläge, Material Eigenleistung...)
Die Spalten sind nach Monaten aufgeteilt. Also in welchem Monat die Beträge fällig werden.
Nun können bspw. Kosten (Vermessungskosten) auch über meherer Monate verteilt fällig werden.
Es erfolgt eine Aufrechnung der Spalten (Fälligkeiten in dem Monat) und in Zeilen (gesamt gezahlt Kostenstelle). Daraud ergibt sich ein
Soll/Ist Vergleich und eine Fälligkeitsübersicht (Wichtig für die Planung von Festgeldern).
Für den IST-wert können nur bezahlte Kosten herangezogen werden. Daher möchte eine einzelne Zelle so kennzeichnen, das sie eine
"Quersumme" einfließt oder aber eben nicht. Zusätzliche Zeilen und Spalten zerstören die Übersicht.
Welche Möglichkeit der kenzeichnung ener Zelle gäbe es denn noch?
Als Alternative müsste ich eine zusätzliche Spalte hinter jedem Monat einfügen und diese dann ausblenden
ein Profi würde hier jeder "Verpflichtung" eine Zeile spendieren. Alles andere kommt in eine Spalte.
Code:
Datum Fälligkeit Kostenstelle Betrag Datum bezahlt
15.09.2023 Gebühren 123,45 15.09.2023
15.09.2023 Abschlag 987,65
Hier habe ich statt "bezahlt" das Datum angegeben. Ist informativer als nur "Ja/Nein". Wenn noch nicht bezahlt, bleibt die Zelle leer.
Bei Bedarf kann man noch weiter Spalten einfügen mit weiteren Details zum Vorgang.
So eine Tabelle lässt sich wunderbar z.B. Mit Summewenns() auswerten. Z.B. =SUMMEWENNS(C:C;D:D;"<>") für alle bisherigen Zahlungen oder =SUMMEWENNS(C:C;D:D;">"&DATUM(2023;1;1)) für alles, was dieses Jahr bezahlt wurde. Genauso einfach könnte man alle aktuell offenen Abschläge oder alle künftigen Gebühren ausrechnen.
Deine bisherige Tabelle müsste sich daraus ganz einfach in Form einer Pivot-Tabelle erstellen lassen.
wie kann ich dann sowas darstellen? Für den Energieberater habe ich 6.000 € eingeplant. Es kam eine KV mit Einzelpositionen und Eventualpositionen.
Die einzelpositionen würde ich gern in der Reihe nach Zahlung addieren und daraus dann die Summe ist. Somit habe ich dann auch einen Soll/Ist Vergleich.
nun ja, wenn du den "Energieberater" also Oberbegriff siehst, ist das einfach eine weitere Spalte und wird in jeder Zeile der betreffenden Kostenstellen mit aufgeführt.
Wenn du eine kleine Beispieldatei hast, kann ich dir das gerne anhand dieser zeigen.
15.09.2023, 14:56 (Dieser Beitrag wurde zuletzt bearbeitet: 15.09.2023, 14:57 von HKindler.)
Hi,
hier mal 2 Blätter ergänzt: "Daten" und "Pivot"
"Daten" dient zur Erfassung aller Daten, "Pivot" erzeugt eine Ausgabe ähnlich deiner Vorgabe.
Beachte:
"Daten"
Dies ist eine Strg-T-Tabelle. Diese erweitert sich automatisch, wenn neue Daten direkt unter letzten Zeile eingegeben werden. Ebenso kann man die Spalten erweitern.
Die Spalte "fällig" ist ein echtes Datum (mit Tag) auch wenn es nicht so aussieht.
"Pivot"
wenn man Daten verändert hat, muss man die Pivot händisch aktualisieren. Am einfachsten mit einem Rechtsklick-->Aktualiseren innerhalb der Tabelle oder über Daten-->"Abfragen und Verbindungen"-->"Alle aktualisieren"
Spiel mal ein wenig damit rum. Vor allem mit den Menüs "PivotTable-Analyse" und "Entwurf", die erscheinen, sobald man in die Tabelle klickt.
Übrigens habe ich mehr Zeit zum Tippen dieses Textes als zum Erstellen der Tabellen gebraucht...
Summe bilden wenn Hintergrundfarbe bestimmten wert hat
