58 Aufrufe
Gefragt in Tabellenkalkulation von
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

8 Antworten

0 Punkte
Beantwortet von flupo Profi (15.1k Punkte)
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
0 Punkte
Beantwortet von
Hallo Flupo,

Danke für deine Antwort.

Wie ginge letzteres denn?

VG
0 Punkte
Beantwortet von m-o Profi (17.5k Punkte)

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.

0 Punkte
Beantwortet von flupo Profi (15.1k Punkte)

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

0 Punkte
Beantwortet von
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!
0 Punkte
Beantwortet von flupo Profi (15.1k Punkte)

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

0 Punkte
Beantwortet von
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
0 Punkte
Beantwortet von

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
...