Farben Nummern zuordnen und zusammenrechnen

Question

Guten Morgen,

ich habe folgendes Problem und hoffe, hier auf Profis zu treffen...:)

Ich habe eine Excel- Tabelle vor mir liegen, in die eine Mietübersicht verschiedener Autos eingepflegt ist. D.h. dass z.B. in der Spalte links Auto 1, Auto 2, ... stehen und daneben- über verschiedene Reiter sortiert- die Monate inklusive jedem einzelnen Tag. Die Tage, an denen die Autos noch zur Verfügung stehen, sind grün markeirt und die vermieteten rot. (wird alles händisch gemacht)

Jetzt möchte ich ans Ende der Tabelle zwei weitere Spalten einfügen, in denen ich einmal die "Soll- Vermietung" habe, welche ich bereits eingetragen hab, weil die ja bei voller auslastung leicht zu errechnen ist.

In die andere Spalte möchte ich die "Ist- Vermietung" einpflegen, welche sich an die Farben anpassen soll. Wenn Excel jetzt also sieht: aha, 12 Kästchen sind in dieser Zeile von Auto 1 fot markiert, beträgt die "Ist- Vermietung" beispielsweise 1200€ (bei 100€ pro Tag). Und wenn jetzt jemand zwei weitere Kästchen rot markiert, weil das Auto übers Wochenende vermietet wurde, soll sich die Tabelle hinten automatisch auf 1400€ erhöhen.

Ich hoffe, ihr versteht, was ich brauche und könnt helfen!

Vielen Dank schonmal und viele Grüße,

Chiara

Answer 1

Ich würde das über Inhalte in den Zellen lösen und die Formatierung (Farbe) über bedingte Formatierung machen.

Eine direkte Auswertung der Zellfarbe ist (meines Wissens) nur mit Formeln nicht möglich. Wenn aber in einer Zelle z.B. ein x steht, kann man daraus die Farbe erzeugen und man kann die Zellen zählen und damit weiter rechnen.

Frag nach, wenn du mehr wissen willst.

Gruß Flupo

Answer 2

Hallo Flupo,

Danke für deine Antwort.

Wie ginge letzteres denn?

VG

Answer 3

Hallo,

du kannst mit z.B. mittels einer Hilfspalte die Farbe bestimmen und dann zählen (Anleitungen gibt es z.B. hier oder hier). Ob das bei deinem Tabellenbau mit Hilfsspalten oder -zeilen geht, kann ich natürlich nicht sagen. Auch eine Lösung per VBA ist möglich (z.B. laut dieser Anleitung).

Der Einfachheit halber würde ich aber wie von Flupo vorgeschlagen das mit einem x und mit bedingter Formatierung  machen:

Markiere deine Zeile(n) und wähle im Menüband Start "Bedingte Formatierung".-> neue Regel -> nur Zellen formatieren die enthalten -> Zellwert gleich x -> Formatierung: Hintergrundfarbe rot -> mit OK bestätigen.

Dann noch einmal Bedingte Formatierung -> Regeln verwalten -> neue Regel - nur Zellen formatieren die enthalten -> Zellwert ungleich x --> Formatierung Hintergrundfarbe grün - mit OK bestätigen.

Dann machst du bei jeden Tag an dem ein Fahrzeug vermietet ist ein x und die Zelle wird rot gefärbt. Diese x kannst du dann ohne Probleme mit Zählenwenn zählen.

Gruß

M.O.

Answer 4

Kleines Beispiel mit zwei Autos und drei Tagen:

Die Datumsspalten sind standardmäßig grün eingefärbt. Wenn man ein x eingibt werden sie über die bedingte Formatierung rot. 

Die Regel dazu:

In der Soll-Spalte wird die die Anzahl der Datumsspalten mit dem jeweiligen Mietpreis multipliziert.

In der Ist-Spalte wird die Anzahl der Zellen mit einem x gezählt und ebenfalls mit dem Mietpreis multipliziert.

Formel: =ZÄHLENWENN(B3:D3;"=x")*F8

Gruß Flupo

Answer 5

Huhu,

also erstmal vielen, vielen Dank für eure Hiilfe! So machen wir es jetzt auch. (mit x)

Eine Frage habe ich jetzt noch... Wie kriege ich es denn jetzt hin, dass alle x- Zellen automatisch mit dem Mietpreis mulipliziert werden? Und wo und wie muss ich den Mietpreis festlegen, damit das Programm es sich merkt?

Vielen Dank schon mal im Voraus!

Answer 6

Die Formel für Auto1 hatte ich ja oben schon aufgeschrieben. Die verrechnet alle x mit dem Mietpreis1 der hier in F8 steht.

 =ZÄHLENWENN(B3:D3;"=x") ist die Formel, die alle x im Bereich B3:D3 zählt.

Ob du hier den Mietpreis direkt als Zahlenwert multiplizierst oder den separat nochmal irgendwo stehen hast, hängt von deinen Anforderungen ab. Die Mietpreise für die Autos könnten ja auch auf einem anderen Tabellenblatt gepflegt werden. Sowas hat den Vorteil, dass man bei Änderungen nicht die ganze Arbeitsmappe durchsuchen muss, wo der Wert verwendet wird. Man ändert die eine Stelle und alle Berechnungen verwenden den neuen Wert.

Ich mache sowas auch immer gern noch etwas transparenter indem ich für wichtige Zellen Namen vergebe und die dann in den Formeln verwende. Für das Beispiel oben könnte man z.B. die Zellen, in denen die Mietpreise stehen (die Zahlenwerte, nicht die Beschriftung) in "Mietpreis1" (F8) und "Mietpreis2" (F9) benennen. Solange die Namen eindeutig vergeben werden, ist es dann für die Verwendung in den Formeln auch egal, wo die Werte in der Arbeitsmappe stehen. die Formeln werden also einfacher "lesbar" weil man nicht erst gucken muss, was denn in F8 steht.

Als Formel für die Ist-Vermietung von Auto1 würde sich dann daraus ergeben:

 =ZÄHLENWENN(B3:D3;"=x")*Mietpreis1

Der blaue Bereich ist der, den du in deiner Mappe anpassen musst um den Bereich festzulegen, in dem die x gezählt werden sollen.

Gruß Flupo

Answer 7

Hallo Community

Oder eine Benutzerdefinierte Funktion!

Addiert Summen dessen Zellen einen farbigen Hintergrund besitzen nach dem jeweiligen Farbindex

Einzufügen!

Alt+F11/Einfügen/Allgemeines Modul

Funktion in das Allgemeine Modul einfügen

Die Funktion ist dann unter den Funktionen bei Benutzerdefiniert erreichbar!

Syntax z.b.

=FIndexAdd(A2:A5;3)

=FIndexAdd(Bereich;Farbindex)

Function FIndexAdd(Zellen As Range, FarbIndex As Long) As Long
    Dim Zelle As Range
    For Each Zelle In Zellen
        If Cells(Zelle.Row, Zelle.Column).Interior.ColorIndex = FarbIndex Then
            FIndexAdd = FIndexAdd + Cells(Zelle.Row, Zelle.Column)
        End If
    Next Zelle
End Function

Gruß Michael

Answer 8

Hallo Community

Oder RGB orientiert!

Syntax z.b.

=RGBwerteAddieren(A1:A6;255;0;0)

=RGBwerteAddieren(Bereich;RotWert;GrünWert;BlauWert)

Function RGBwerteAddieren(Zellen As Range, Rrgb As Integer, Grgb As Integer, Brgb As Integer) As Double
    Dim Rot As Long, Gr?n As Long, Blau As Long, Wert As Long
    For Each Zelle In Zellen
        Wert = Zelle.Interior.Color
        Rot = Wert Mod 256
        Wert = (Wert - Rot) / 256
        Gr?n = Wert Mod 256
        Wert = (Wert - Gr?n) / 256
        Blau = Wert Mod 256
        If Rrgb = Rot And Grgb = Gr?n And Brgb = Blau Then RGBwerteAddieren = RGBwerteAddieren + Cells(Zelle.Row, Zelle.Column)
    Next Zelle
End Function

Übersicht!

Index             RGB               Name

1 0 0 0 Schwarz 2 255 255 255 Weiß 3 255 0 0 Rot 4 0 255 0 Hellgrün 5 0 0 255 Blau 6 255 255 0 Gelb 7 255 0 255 Rosa 8 0 255 255 Türkis 9 128 0 0 Dunkelrot 10 0 128 0 Grün 11 0 0 128 Dunkelblau 12 128 128 0 Dunkelgelb 13 128 0 128 Violett 14 0 128 128 Seegrün 15 192 192 192 Grau 16 128 128 128 Grau 17 153 153 255 Immergrün 18 153 51 102 Pflaume 19 255 255 204 Elfenbein 20 204 255 255 helles Türkis 21 102 0 102 Dunkelpurpur 22 255 128 128 Koralle 23 0 102 204 Meeresblau 24 204 204 255 Eisblau 25 0 0 128 Dunkelblau 26 255 0 255 Rosa 27 255 255 0 Gelb 28 0 255 255 Türkis 29 128 0 128 Violett 30 128 0 0 Dunkelrot 31 0 128 128 Seegrün 32 0 0 255 Blau 33 0 204 255 Himmelblau 34 204 255 255 helles Türkis 35 204 255 204 Pastellgrün 36 255 255 153 Hellgelb 37 153 204 255 Blaßblau 38 255 153 204 Hellrosa 39 204 153 255 Lavendel 40 255 204 153 Gelbbraun 41 51 102 255 Hellblau 42 51 204 204 Aquablau 43 153 204 0 Gelbgrün 44 255 204 0 Gold 45 255 153 0 helles Orange 46 255 102 0 Orange 47 102 102 153 Blaugrau 48 150 150 150 Grau 40
Gruß Michael