Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1768to1772
1768to1772
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Bedingte Formatierung - 0 ignorieren

Forumthread: Bedingte Formatierung - 0 ignorieren

Bedingte Formatierung - 0 ignorieren
09.07.2020 15:49:17
Thomas
Hallo,
folgendes Problem mit der bedingten Formatierung: Ich möchte gern, dass von einer Menge A (z. B. G3:G33) das oberste Drittel B der Teilmenge rot, das unterste (D) grün eingefärbt wird. Das mittlere Drittel C soll gelb formatiert oder unformatiert sein.
Die Formatierung soll für folgenden Bereich gelten, den ich wie folgt definiert habe:
=WENN(D3>0;B3;0)
weswegen alle Zeilen mit dem Wert "0" ignoriert werden sollen.
Ziel: Ich möchte folgende Werte ausgeben:
100 % = Summe(G3:G33)
-Top1/3 = Summe(Feld gelb/unformatiert + Feld grün)
-Top2/3 = Summe(Feld grün)
Letzteres geht wohl nur über ein Script. Aktuell scheitere ich jedoch noch daran, dass es mit die ganzen Nullwerte grün einfärbt, statt diese zu ignorieren.
Anzeige

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bedingte Formatierung - 0 ignorieren
09.07.2020 16:02:04
Thomas
Nachtrag: Ich habe es jetzt erst einmal so gelöst, dass alle Einträge, die eine 0 aufweisen, in einer Hilfszeile mit " - " überschrieben werden. So habe ich schon einmal die korrekte farbliche Auszeichnung.
Jetzt muss ich es nur noch schaffen, dass alle Zellen mit dem Farbcode X bei Summe() ignoriert werden. :)
Anzeige
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
09.07.2020 16:06:46
neopa
Gruß Werner
.. , - ...
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
09.07.2020 16:08:34
Thomas
Die würde ich gerne hochladen, aber der Upload akzeptiert nur "exotische" Formate, die mir Excel 2019 nicht generieren möchte. :(
AW: wenn Du die Datei zippst, sollte es gehen owT
09.07.2020 16:12:08
neopa
Gruß Werner
.. , - ...
Anzeige
AW: wenn Du die Datei zippst, sollte es gehen owT
09.07.2020 16:17:48
Thomas
Ich probiere es noch einmal mit der Original *.xlsx - es scheint auch so zu gehen. Ich dachte, ich muss eines der custom Formate nutzen. :D
https://www.herber.de/bbs/user/138912.xlsx
Danke übrigens noch einmal Werner!
AW: es sollte reichen ...
09.07.2020 16:33:16
neopa
Hallo Thomas,
... wenn Du Deine Formel in G3 von =WENN(D3&gt0;B3;0) zu =WENN(D3&gt0;B3;"") änderst und nach unten kopierst und hierfür Deine bed. Formatierung definierst. Natürlich musst Du dann in J3 auch ein WENN(G3="";""; einfügen.
Gruß Werner
.. , - ...
Anzeige
AW: es sollte reichen ...
09.07.2020 16:47:56
Thomas
Danke! Das funktioniert zwar, aber dann habe ich das Problem, dass mir z. B. in J4 #WERT! erscheint und ich somit keine Summe mehr ausgeben kann.
Und ich muss noch irgendwie die farbig hinterlegten Felder auslesen und berechnen. Die andere Formel, die Du mir gegeben hast funktioniert zwar super, aber ich habe vergessen, dass ich noch die Gewichtung (Menge) bei der Drittelung vergessen habe.
Anzeige
AW: auf Notwendigkeit der Formelanpassung in J ...
09.07.2020 16:58:01
neopa
Hallo Thomas,
... hatte ich hingewiesen und die Änderung auch aufgezeigt.
Farbig hinterlegten Felder auslesen und berechnen geht standardmäßig nicht. Um welche Zellwerte geht es denn? Wo und wie willst Du denn eine Gewichtung vornehmen?
Gruß Werner
.. , - ...
AW: auf Notwendigkeit der Formelanpassung in J ...
09.07.2020 17:02:13
Thomas
Ein ähnliches Problem wie in dem anderen Thread bzw. immer das gleiche bei wechselnder Menge an Daten:
Ich habe verschiedene Angebote und muss hier das
a) teuerste Drittel
b) günstigste Drittel
ausweisen.
Im Beispiel ginge das ja auch manuell bei nur drei Angeboten, aber in anderen Bereichen habe ich teils über 40 verschiedene. :D
Anzeige
AW: nachgefragt ...
09.07.2020 17:33:10
neopa
Hallo Thomas,
... Du möchtest die bed. Formatierung über den Vergleich des Gesamtpreise bezogen auf die "Drittel-"Mediane vornehmen?
Dann diese mE mit 14 als 1. Argument von AGGREGAT() ermitteln.
Also: =MEDIAN(INDEX(AGGREGAT(14;6;J$3:J$14;ZEILE(A$1:INDEX(A:A;AUFRUNDEN(1/3*ANZAHL(J$3:J$14);))));))
und so: =MEDIAN(INDEX(AGGREGAT(14;6;J$3:J$14;ZEILE(A$1:INDEX(A:A;2/3*ANZAHL(J$3:J$14))));))
(in der bedingten Formatierung bedarf es für den Vergleich mit dem jeweiligen Wert in Spalte J die INDEX()-Teilklammerung nicht.
Meinst Du das so?
Gruß Werner
.. , - ...
Anzeige
AW: nachgefragt ...
09.07.2020 17:47:32
Thomas
Danke Dir, aber irgendwie komme ich da glaube nicht weiter... z. B. komme ich so bei
- bei Formel 1 auf einen Wert von 0, obwohl bei 0.89,
- bei Formel 2 auf einen Wert von 7,55, obwohl bei 0,54.
Ich werde wohl den manuellen Weg gehen und einfach alles absteigend sortieren lassen, dann per bedingter Formatierung Dritteln und anschließend die Auswahl manuell machen.
Anzeige
AW: Formeln ermitteln andere Werte, außerdem ...
09.07.2020 19:55:43
neopa
Hallo Thomas,
... ist unklar, warum Du diese teilweise mit Bezug auf Spalte D und teilweise mit Bezug auf Spalte J ermittelst. Ich hatte nur Formeln für Spalte J angegeben.
Gruß Werner
.. , - ...
;
Anzeige
Anzeige

Infobox / Tutorial

Bedingte Formatierung: Nullwerte ignorieren

Schritt-für-Schritt-Anleitung

  1. Bedingte Formatierung einrichten:

    • Wähle den Bereich aus, den du formatieren möchtest (z. B. G3:G33).
    • Gehe zu Start > Bedingte Formatierung > Neue Regel.
    • Wähle Formel zur Ermittlung der zu formatierenden Zellen verwenden.

  2. Formeln für die Farbgebung:

    • Um das oberste Drittel rot zu formatieren, verwende die Formel: 
      =D3>MEDIAN(INDEX(AGGREGAT(14;6;G$3:G$33;ZEILE(A$1:INDEX(A:A;AUFRUNDEN(1/3*ANZAHL(G$3:G$33);))));))
    • Für das unterste Drittel grün: 
      =D3<MEDIAN(INDEX(AGGREGAT(14;6;G$3:G$33;ZEILE(A$1:INDEX(A:A;2/3*ANZAHL(G$3:G$33))));))
    • Das mittlere Drittel kannst du mit einer weiteren Regel unformatiert oder gelb färben.

  3. Nullwerte ignorieren:

    • Um sicherzustellen, dass die Nullwerte ignoriert werden, passe die Formel in der Hilfszeile an: 
      =WENN(D3>0;B3;"")
    • Diese Formel sorgt dafür, dass nur Werte größer als 0 weiterverarbeitet werden.

Häufige Fehler und Lösungen

  • Problem: Der Fehler #DIV/0! erscheint.

    • Lösung: Stelle sicher, dass du die Formel so anpasst, dass sie nur Werte über 0 berücksichtigt. Verwende die oben genannte WENN-Formel.

  • Problem: Die bedingte Formatierung ignoriert immer noch Nullwerte.

    • Lösung: Überprüfe die angewandte Regel und stelle sicher, dass sie korrekt auf die Hilfszeile verweist.

Alternative Methoden

Eine andere Möglichkeit, mit Nullwerten umzugehen, besteht darin, die AGGREGAT-Funktion zu verwenden. Diese Funktion kann Fehlerwerte ignorieren und ist besonders nützlich, wenn du mit größeren Datenmengen arbeitest. Beispiel:

=AGGREGAT(14;6;G$3:G$33)

Dies gibt dir die Medianwerte, ohne Nullwerte oder Fehler zu berücksichtigen.

Praktische Beispiele

Angenommen, du hast folgende Werte in G3:G33:

  • 0, 5, 10, 15, 0, 20

Wenn du die bedingte Formatierung wie beschrieben anwendest, wird der Bereich so gefärbt:

  • Rot: 15, 20
  • Grün: 5, 10
  • Gelb: keine Werte, da alle relevanten Werte farbig sind.

Tipps für Profis

  • Nutze die Datenüberprüfung, um sicherzustellen, dass nur gültige Werte eingegeben werden und Fehler von vornherein vermieden werden.
  • Erstelle eine Hilfsspalte, in der du die gefilterten Werte darstellst. So behältst du den Überblick und kannst einfacher mit den Daten arbeiten.
  • Experimentiere mit den Formatierungsregeln, um eine visuelle Darstellung zu erhalten, die dir hilft, wichtige Informationen auf einen Blick zu erkennen.

FAQ: Häufige Fragen

1. Wie kann ich in Excel 0 ignorieren?
Mit der WENN-Formel kannst du sicherstellen, dass Nullwerte nicht in Berechnungen oder der bedingten Formatierung berücksichtigt werden.

2. Was sind die besten Methoden, um mit Fehlern in Excel umzugehen?
Verwende Funktionen wie AGGREGAT oder WENNFEHLER, um Fehlerwerte zu ignorieren und die Berechnungen robust zu gestalten.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige