Excel bedingte Formatierung Farbskala leere Zellen ignorieren beim Vergleich

[acidarchangel]

Moin zusammen,

ich stehe vor einem für mich, auch nach einiger Recherche, unlösbaren Problem mit der bedingten Formatierung in Excel.
Hier mal ein Beispiel (es geht eigentlich um eine Tabelle mit mehreren Hundert Zeilen und Spalten):

Wie bekomme ich es hin, dass Zellen mit "0" (hier B3) von der formatierung ausgeschlossen werden? Im Beispiel sollte B5 als niedrigster Wert größer 0 dann grün erscheinen.

EDIT: Klar könnte ich jede einzelne Zelle mit null manuell von der Formatierung ausschließen, aber da macht bei hunderten Zellen keinen Spaß

 

[cartridge_case]

Wie bekomme ich es hin, dass Zellen mit "0"

"-" ist doch nicht Null. Welche Formel steckt denn da schon hinter?

 

[sLy]

Indem du eine bedingte Formatierung (Für deine "0" oder "leeren" Werte) mit höherer Priorität erstellst
Oder du machst eine UND Bedingung in die bedingte Formatierung

EDIT: Bei der "Voreinstellung", die du anscheinend benutzt, kann man da keinen Mindestwert angeben?

 

[acidarchangel]

@cartridge_case Dahinter steckt nur eine Summenformel, und dads "-" erscheint durch die Verwendung als Buchhaltungszahlenformat.

@sLy Genau das ist mein Problem: Ich finde keinen Weg die vorgegebene 3-Farb-Skalen Formatierung mit einer UND Bedingung zu erstellen. Wo finde ich das?
Bei einer 2. Bedingung mit höherer Priorität werden die "0" Zellen zwar weiß markiert, aber dennoch mit in den Vergleich der der Farbskala eingeshlossen, was man am helleren Grün in B5 sieht (erstes Bild). Dieses müsste eigentlich so kräftig sein wie in B3 auf der rechten Seite.

 

[sLy]

https://support.microsoft.com/de-de...mationen-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

Schau da mal unter "Zellen mithilfe ... 3 Farben Skala..."
Und dann "Erweitert" (Punkt 7)
Da steht dass man das definieren kann. Aber habe selbst bisher nie damit gearbeitet

 

[Utensil1538]

Wenn in der Zelle der Wert 0 dargestellt wird, statt dem „-„ dann würde es funktionieren.
Oder eine vierte bedingte Formatierung erstellen, welche den Wert „-„ abbildet, müsste man probieren.

 

[sLy]

Das "Problem" was hier besteht: Das eben nicht "0", sondern "100" oder "1?" als niedrigster Wert definiert werden muss damit die Farbskala an diesem Punkt startet.

 

[Utensil1538]

Oder so, denn "-" ist eben auch ungleich 0. Dan muss aber 1 auch als =/< markiert sein, sofern 1 auch einen relevanten Wert in der Berechnung darstellt.

 

[acidarchangel]

Sorry, aber habt ihr eure Vorschläge selber mal getestet? Die Anzeige "-" ist lediglich durch die buchhalterische Formatierung vorhanden. Der Wert der Zelle ist "0"! Man kann damit sogar in Formeln rechnen und es auch in bedingten Formatierungen verwenden.

Auch die Variante, anstatt den "niedrigsten" Wert in der Bedingung die Zahl "1" anzugeben bringt keinen Unterschied. Das hab ich alles schon durch. Deswegen suche ich ja hier nach einer Lösung

 

[Utensil1538]

Das eine Zelle hellgrün statt dunkelgrün anzeigt, zeigt auch das in der bedingten Formatierung ein Fehler ist. Irgendwo ist der hellgrüne Wert definiert worden.

Ergänzung (30. März 2024)

Sorry, aber habt ihr eure Vorschläge selber mal getestet? Die Anzeige "-" ist lediglich durch die buchhalterische Formatierung vorhanden. Der Wert der Zelle ist "0"! Man kann damit sogar in Formeln rechnen und es auch in bedingten Formatierungen verwenden.

Auch die Variante, anstatt den "niedrigsten" Wert in der Bedingung die Zahl "1" anzugeben bringt keinen Unterschied. Das hab ich alles schon durch. Deswegen suche ich ja hier nach einer Lösung

Sorry, aber ja. Hier mal der Vergleich.
Alle Werte >0 sind grün, nur die Zelle mit 0 ist weiß. Funktioniert bei korrekter Formatierung.

 

[cartridge_case]

Hier mal der Vergleich.

Anscheinend ohne buchhalterische Formatierung?!

 

[Utensil1538]

Spielt doch überhaupt keine Geige, welche Formel dahinter steht. Wichtig ist der Zustand der Zelle.
Aber gerne auch in EUR als buchhalterische Formel.

 

[Palomino]

Ich denke mit einer Farbskala wird es so nicht funktionieren, da hier ein Farbverlauf hinterlegt ist. Selbst wenn Du für die kleineste Zahl das dunkle grün vorgibst, werden sich auch deine anderen Zellen farblich verändern.
Was du eher machen könntest wäre, für bestimmte Wertbereiche Farben zu definieren und diese dann über die bedingte Formatierung zuzuweisen.

 

[sLy]

Hier musst du halt den "Startpunkt" angeben (Zahl statt Niedrigster Wert)

Edit: OK Excel ist doof, die Nullwerte musst doch trotzdem über eine 2. Bedingungen formattieren
bzw. wird alles was unter/oberhalb der Grenze liegt wie der Maximalwert behandelt.

 

[acidarchangel]

Ich denke @Palomino hat Recht und es geht einfach nicht.

@sLy Genau das schrieb ich oben. Selbst wenn man dort (dein Screenshot) einen "Startwert" angibt werden die "0" Werte dennoch mit in den "Vergleich" aufgenommen und der niedrigste Wert erhält nicht den, wie gewünscht, intensivsten Grünton sondern einen etwas helleren. Und ohne zweite Regel werden auch die "0" Zellen grün, und dann in dem Grün, dass eigentlich für den kleinsten Wert >0 angegeben ist. Das verfälscht den Farbverlauf gerade wenn man viele Werte hast.

 

[Utensil1538]

@acidarchangel Und so wie ich es oben gezeigt habe geht es bei dir nicht?

 

[bughero]

Zellen mit "0" (hier B3) von der formatierung ausgeschlossen werden

Bei einer 3-Farben-Skala gar nicht. Werte kleiner als Minimum werden immer die Minimumfarbe haben; Werte größer als Maximum werden immer die Maximumfarbe haben.

Bei einer 2. Bedingung mit höherer Priorität werden die "0" Zellen zwar weiß markiert

Falls das für dich eine akzeptable Lösung wäre, nimm den niedrigsten Wert größer als Null als Minimum und (Minimum+Maximum)/2 als Mittelpunkt:

Minimum:
Typ: Formel
Wert: =MIN(FILTER($B$1:$B$5;$B$1:$B$5>0))

Mittelpunkt:
Typ: Formel
Wert: =(MIN(FILTER($B$1:$B$5;$B$1:$B$5>0))+MAX($B$1:$B$5))/2

 

[acidarchangel]

@Utensil1538 Bei deiner Variante gibt es keinen Farbverlauf von grün (niedrigster Betrag) nach rot (höchster Betrag).

@bughero Bingo!!! Du bist mein Held für heute! Mit der Minimum und Maximum Formel und der Kombi mit einer zweiten Bedingung für Zellen mit dem Wert 0 klappt es!
Danke euch! Ich wünsche euch ein schönes Osterwochenende! 🥚✌️

 

[Utensil1538]

@acidarchangel Das war auch nur als Bsp. gedacht um zu zeigen das es mit 0 geht. Natürlich nur mit festen Werten und nicht Farbverlaufsskala. Die anderen Farbwerte müsste man noch ergänzen.