Nehmen wir an, eine unserer Aufgaben besteht darin, Informationen darüber einzugeben, ob der Kunde im aktuellen Monat eine Bestellung aufgegeben hat. Anschließend müssen anhand der erhaltenen Informationen die Zellen entsprechend der Bedingung farblich hervorgehoben werden: Welcher der Kunden hat in den letzten 3 Monaten keine einzige Bestellung aufgegeben? Für solche Kunden müssen Sie das Angebot erneut senden.
Dies ist natürlich eine Aufgabe für Excel. Das Programm soll solche Kontrahenten automatisch finden und entsprechend farblich hervorheben. Für diese Bedingungen verwenden wir die bedingte Formatierung.
Füllen Sie Zellen automatisch mit Datumsangaben
Bereiten wir zunächst die Struktur zum Ausfüllen des Registers vor. Betrachten wir zunächst bedingt ein vorgefertigtes Beispiel eines automatisierten Registers, das in der folgenden Abbildung dargestellt ist:
Der Benutzer muss lediglich angeben, ob der Kunde im aktuellen Monat eine Bestellung aufgegeben hat, dann muss der Textwert „Bestellung“ in die entsprechende Zelle eingegeben werden. Die Hauptbedingung für die Hervorhebung: Wenn die Gegenpartei 3 Monate lang keine einzige Bestellung aufgegeben hat, wird ihre Nummer automatisch rot hervorgehoben.
Diese vorgestellte Lösung soll einige Arbeitsabläufe automatisieren und die visuelle Datenanalyse vereinfachen.
Füllen Sie Zellen automatisch mit aktuellen Daten
Für das Register mit Kundennummern erstellen wir zunächst Spaltenüberschriften mit grüner Farbe und aktuellen Monaten, in denen automatisch Zeiträume angezeigt werden. Geben Sie dazu in Zelle B1 die folgende Formel ein:
Wie funktioniert die Formel zur automatischen Generierung ausgehender Monate?
In der Abbildung gibt die Formel die verstrichene Zeit ab dem Datum der Erstellung des Artikels zurück: 17.09.2017. Das erste Argument in der Funktion DATA enthält eine Formel, die dank der Funktionen YEAR und TODAY immer das aktuelle Jahr am heutigen Datum zurückgibt. Das zweite Argument gibt die Monatsnummer (-1) an. Eine negative Zahl bedeutet, dass wir daran interessiert sind, welcher Monat in der Vergangenheit war. Beispielbedingungen für das zweite Argument mit einem Wert:
- 1 – bedeutet den ersten Monat (Januar) des im ersten Argument angegebenen Jahres;
- 0 ist 1 Monat her;
- -1 sind 2 Monate. zurück vom Beginn des laufenden Jahres (d. h.: 01.10.2016).
Das letzte Argument ist die Zahl des Tages des im zweiten Argument angegebenen Monats. Infolgedessen sammelt die DATE-Funktion alle Parameter in einem Wert und die Formel gibt das entsprechende Datum zurück.
Wie Sie sehen, verwendet die DATE-Funktion nun den Wert aus Zelle B1 und erhöht die Monatszahl um 1 relativ zur vorherigen Zelle. Als Ergebnis erhalten wir 1 – das Datum des nächsten Monats.
Kopieren Sie nun diese Formel aus Zelle C1 in die restlichen Spaltenüberschriften im Bereich D1:L1.
Markieren Sie den Zellbereich B1:L1 und wählen Sie das Werkzeug: „HOME“ – „Zellen“ – „Zellen formatieren“ oder drücken Sie einfach STRG+1. Wählen Sie im angezeigten Dialogfeld auf der Registerkarte „Zahl“ im Abschnitt „Zahlenformate:“ die Option „(alle Formate)“. Geben Sie im Feld „Typ:“ den Wert ein: MMM.YY (erforderliche Großbuchstaben). Dadurch erhalten wir eine verkürzte Anzeige der Datumswerte in den Registerköpfen, was die visuelle Analyse vereinfacht und durch bessere Lesbarkeit komfortabler macht.
Beachten Sie! Wenn der Monat Januar (D1) kommt, ändert die Formel das Datum automatisch auf das nächste Jahr.
So färben Sie eine Spalte in Excel basierend auf der Bedingung
Jetzt müssen Sie die Zellen markieren, die sich berühren aktueller Monat. Dadurch können wir leicht die Spalte finden, in die wir die aktuellen Daten für diesen Monat eingeben müssen. Dafür:
Die Spalte unter der entsprechenden Registerüberschrift wird gemäß unseren Bedingungen automatisch grün hervorgehoben:
Wie funktioniert die Formel zum Hervorheben einer Spaltenfarbe nach Bedingung?
Da wir vor der Erstellung der bedingten Formatierungsregel den gesamten Tabellenbereich zur Eingabe von Falldaten abgedeckt haben, ist die Formatierung für jede Zelle in diesem Bereich B2:L15 aktiv. Der gemischte Verweis in der Formel B$1 (absolute Adresse nur für Zeilen, relative Adresse für Spalten) bedeutet, dass sich die Formel immer auf die erste Zeile jeder Spalte bezieht.
Automatische Hervorhebung einer Spalte basierend auf dem aktuellen Monat
Die Hauptbedingung für das Füllen von Zellen mit Farbe: Wenn der Bereich B1:L1 dasselbe Datum wie den ersten Tag des aktuellen Monats enthält, ändern die Zellen in der gesamten Spalte sofort ihre Farbe in die in der bedingten Formatierung angegebene Farbe.
Beachten Sie! In den Bedingungen dieser Formel wird das letzte Argument der DATE-Funktion auf 1 gesetzt, genau wie in den Formeln zum Definieren von Datumsangaben für Registerspaltenüberschriften.
In unserem Fall ist dies die grüne Füllung der Zellen. Wenn wir im nächsten Monat unser Register öffnen, wird die entsprechende Spalte unabhängig vom aktuellen Tag grün hervorgehoben.
Der tabellarische Teil ist formatiert. Jetzt füllen wir ihn mit dem Textwert „Reihenfolge“ in einer gemischten Reihenfolge der Kunden für den aktuellen und den vorherigen Monat.
So markieren Sie Zellen basierend auf einer Bedingung rot
Jetzt müssen wir die Zellen mit der Anzahl der Kunden, die seit 3 Monaten keine einzige Bestellung aufgegeben haben, rot markieren. Dafür:
Kundennummern werden rot hervorgehoben, wenn ihre Zeile in den letzten drei Zellen für den aktuellen Monat (einschließlich) nicht den Wert „Bestellung“ enthält.
Analyse der Formel zum Hervorheben von Zellen nach Farbe:
Befassen wir uns zunächst mit dem mittleren Teil unserer Formel. Die OFFSET-Funktion gibt einen Bereichsreferenzversatz vom Basisbereich um eine angegebene Anzahl von Zeilen und Spalten zurück. Der zurückgegebene Verweis kann eine einzelne Zelle oder ein ganzer Zellbereich sein. Optional können Sie die Anzahl der zurückzugebenden Zeilen und Spalten definieren. In unserem Beispiel gibt die Funktion einen Verweis auf einen Zellbereich für die letzten 3 Monate zurück.
Der wichtige Teil für unsere Farbhervorhebungsbedingung liegt im ersten Argument der OFFSET-Funktion. Es bestimmt, ab welchem Monat der Offset beginnt. IN in diesem Beispiel– Dies ist Zelle D2, d. h. der Jahresanfang ist Januar. Für die übrigen Zellen in der Spalte entspricht die Zeilennummer der Basiszelle natürlich der Zeilennummer, in der sie sich befindet. Die nächsten beiden Argumente der OFFSET-Funktion bestimmen, für wie viele Zeilen und Spalten der Offset ausgeführt werden soll. Da wir Berechnungen für jeden Client in derselben Zeile durchführen, geben wir den Offset-Wert für Zeilen als –¬ 0 an.
Gleichzeitig verwenden wir zur Berechnung des Wertes des dritten Arguments (Spaltenoffset) die verschachtelte Formel MONTH(TODAY()), die entsprechend den Bedingungen die Nummer des aktuellen Monats im aktuellen Jahr zurückgibt. Von der durch die Formel berechneten Monatszahl subtrahieren wir die Zahl 4, d. h. im Fall von November erhalten wir einen Offset von 8 Spalten. Und zum Beispiel für Juni - nur 2 Spalten.
Die letzten beiden Argumente der OFFSET-Funktion geben die Höhe (in Anzahl der Zeilen) und die Breite (in Anzahl der Spalten) des zurückgegebenen Bereichs an. In unserem Beispiel handelt es sich um einen Bereich von Zellen mit einer Höhe von 1 Zeile und einer Breite von 4 Spalten. Dieser Bereich umfasst die Spalten der letzten 3 Monate und des aktuellen.
Die erste Funktion in der COUNTIF-Formel testet mithilfe der OFFSET-Funktion, wie oft der Textwert „order“ im zurückgegebenen Bereich erscheint. Wenn die Funktion den Wert 0 zurückgibt, bedeutet dies, dass seit 3 Monaten keine Bestellungen von einem Kunden mit dieser Nummer eingegangen sind. Und gemäß unseren Bedingungen wird die Zelle mit der Nummer dieses Kunden mit einer roten Füllfarbe hervorgehoben.
Wenn wir Kundendaten erfassen möchten, ist Excel hierfür ideal. Die Anzahl der bestellten Produkte sowie die Transaktionsdaten können Sie ganz einfach in den entsprechenden Kategorien erfassen. Mit zunehmender Datenmenge tritt das Problem allmählich auf.
Wenn es so viele sind, verbringen wir mehrere Minuten damit, nach einer bestimmten Registerposition zu suchen und die eingegebenen Informationen zu analysieren. In diesem Fall lohnt es sich, der Registertabelle Mechanismen hinzuzufügen, um einige Arbeitsabläufe des Benutzers zu automatisieren. Das haben wir getan.
Nehmen wir an, Sie haben einen Bereich von Zellen mit unterschiedlichen Hintergrundfarben wie Rot, Grün, Blau usw., aber jetzt müssen Sie zählen, wie viele Zellen in diesem Bereich eine bestimmte Hintergrundfarbe haben, und die farbigen Zellen mit derselben spezifischen Farbe summieren. In Excel gibt es keine direkte Formel zur Berechnung der Summe und Anzahl der Farbzellen. Hier stelle ich Ihnen verschiedene Möglichkeiten zur Lösung dieses Problems vor.
Zählen, summieren und mitteln Sie farbige Zellen in Excel mit einem Klick
Zählen und Summieren farbiger Zellen durch Filter und Zusammenfassungen
Nehmen wir an, wir haben eine Obstverkaufstabelle wie im Screenshot unten gezeigt und zählen oder summieren die farbigen Zellen in der Spalte „Menge“. In dieser Situation können wir die Spalte „Betrag“ nach Farbe filtern und dann die gefilterten farbigen Zellen mithilfe der ZWISCHENSUMME-Funktion in Excel einfach zählen oder summieren. 
1 , Wählen Sie leere Zellen aus, um die ZWISCHENSUMME-Funktion aufzurufen.
- Geben Sie die Formel ein, um alle Zellen mit derselben Hintergrundfarbe zu zählen = GESAMT (102, E2: E20);
- Geben Sie die Formel ein, um alle Zellen mit derselben Hintergrundfarbe hinzuzufügen = GESAMT (109, E2: E20);
Aufmerksamkeit: In beiden Formeln ist E2:E20 die Betragsspalte mit den farbigen Zellen und Sie können sie nach Bedarf ändern.
2
. Wählen Sie den Tabellentitel aus und klicken Sie Datum> Filter. Siehe Screenshot: 
3
. Klicken Sie auf das Filtersymbol in der Kopfzelle der Spalte „Betrag“ und klicken Sie auf Nach Farbe filtern und Sie zählen die angegebene Farbe der Reihe nach. Siehe Screenshot: 
Nach dem Filtern zählen und summieren beide ZWISCHENSUMME-Formeln automatisch alle gefilterten Farbzellen in der Spalte „Menge“. Siehe Screenshot: 
Aufmerksamkeit: Diese Methode erfordert, dass sich die farbigen Zellen, die Sie zählen oder summieren, in derselben Spalte befinden.
Zählen oder Summieren farbiger Zellen mit der Funktion GET.CELL
Bei dieser Methode erstellen wir mit der Funktion GET.CELL einen benannten Bereich, rufen den Farbcode der Zellen ab und zählen oder summieren dann einfach anhand des Farbcodes in Excel. Bitte gehen Sie wie folgt vor:
1
. Klicken Formeln > Namen festlegen. Siehe Screenshot: 
2
. Gehen Sie im Dialogfeld „Neuer Name“ wie folgt vor:
(1) Geben Sie im Feld „Name“ einen Namen ein;
(2) Geben Sie die Formel ein =GET.CELL(38,Sheet4!$E2) im Gilt für ( Aufmerksamkeit: in der Formel, 38
bedeutet, den Zellcode zurückzugeben, und Blatt4! $E2 ist die erste Zelle in der Spalte „Betrag“ mit Ausnahme der Spaltenüberschrift, die basierend auf den Tabellendaten geändert werden muss.)
(3) Klicken Sie OK Taste. 
3
Fügen Sie nun eine neue Farbspalte direkt zur Originaltabelle hinzu. Geben Sie als Nächstes die Formel ein = NumColor und ziehen Sie den AutoFill-Griff, um die Formel auf andere Zellen in der Spalte „Farbe“ anzuwenden. Siehe Screenshot:
Aufmerksamkeit: In der Formel NumColor Dies ist der benannte Bereich, den wir in den ersten beiden Schritten angegeben haben. Sie müssen ihn in den von Ihnen angegebenen Namen ändern. 
Jetzt wird der Farbcode jeder Zelle in der Spalte „Menge“ in der Spalte „Farbe“ zurückgegeben. Siehe Screenshot: 
4
Kopieren Sie die Füllfarbe, geben Sie sie im leeren Bereich des aktiven Arbeitsblatts an und geben Sie daneben die Formeln ein, wie im Screenshot unten gezeigt:
A. Um Zellen nach Farbe zu zählen, geben Sie bitte die Formel ein =COUNTIF($F$2:$F$20, NumColor);
B. Um Zellen nach Farbe zu summieren, geben Sie bitte eine Formel ein =SUMIF($F$2:$F$20,NumColor,$E$2:$E$20).
Aufmerksamkeit: In beiden Formeln 2 $F$: 20 $F$ Spaltenfarbe, NumColor der angegebene benannte Bereich, 2 $E$: 20 $E$ Dies ist die Betragsspalte und Sie können sie nach Bedarf ändern.
Sie werden nun sehen, dass die Zellen in der Spalte „Menge“ gezählt und anhand der Füllfarben summiert werden. 
Zählen und summieren Sie Zellen basierend auf einer bestimmten Füllfarbe mithilfe einer benutzerdefinierten Funktion
Unter der Annahme, dass die farbigen Zellen in einem Bereich verstreut sind, wie im Screenshot unten gezeigt, können beide oben genannten Methoden die farbigen Zellen nicht zählen oder summieren. Hier führt diese Methode VBA ein, um das Problem zu lösen. 
1 ,Halten ALT+F11 und öffnet sich Microsoft Visual Basic für Anwendungen Fenster.
2 . Klicken Einfügen >Modul, und fügen Sie den folgenden Code in das Modulfenster ein.
VBA: Zellen abhängig von der Hintergrundfarbe zählen und summieren:
Funktion ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex Wenn SUM = True, dann für jede rCell in rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function
3
Speichern Sie dann den Code und wenden Sie die folgende Formel an:
A. Zählen Sie die farbigen Zellen: = Farbfunktion (A, B, C, FALSE)
B. Summieren Sie die farbigen Zellen: = Farbfunktion(A, B, C, TRUE),
Hinweis: in den obigen Formeln A ist eine Zelle mit einer bestimmten Hintergrundfarbe, die Sie berechnen, zählen und summieren möchten, und VOR UNSERER ZEITRECHNUNG der Zellbereich, in dem Sie die Menge und den Betrag berechnen möchten.
4
Machen Sie zum Beispiel den folgenden Screenshot und geben Sie die Formel ein = Farbfunktion(A1, A1: D11, FALSE) gelbe Zellen zählen. Und verwenden Sie die Formel = Farbfunktion(A1, A1: D11, TRUE) Fassen Sie die gelben Zellen zusammen. Siehe Screenshot: 
5
Wenn Sie weitere farbige Zellen zählen und summieren möchten, wiederholen Sie Schritt 4. Dann erhalten Sie folgende Ergebnisse: 
Zählen und summieren Sie Zellen basierend auf einer bestimmten Füllfarbe mithilfe der Kutools-Funktionen
Kutools für Excel unterstützt auch einige nützliche Funktionen, die Excel-Benutzern helfen, spezielle Berechnungen durchzuführen, wie z. B. das Zählen nach Zellenhintergrundfarbe, das Summieren nach Schriftfarbe usw.
1
. Wählen Sie die leere Zelle aus, in der Sie die Zählergebnisse platzieren, und klicken Sie Kutools > Kutools-Funktionen > > COUNTBYCELLCOLOR. Siehe Screenshot: 
2
Geben Sie im Dialogfeld „Funktionsargumente“ den Bereich an, in dem Sie die farbigen Zellen zählen möchten Referenz Wählen Sie eine Zelle aus, die mit der angegebenen Hintergrundfarbe gefüllt ist Farbindex_nr und drücke OK Taste. Siehe Screenshot: 
Anmerkungen:
(1) Sie können auch die angegebene Kutools-Funktion eingeben =COUNTBYCELLCOLOR($A$1:$E$20,G2) direkt in eine leere Zelle oder Formelleiste, um die Berechnungsergebnisse zu erhalten;
(2) Klicken Sie Kutools > Kutools-Funktionen > Statistisch und mathematisch > SUMBYCELLCOLOR oder Typ =SUMBYCELLCOLOR($A$1:$E$20,G2) direkt in eine leere Zelle, um Zellen basierend auf einer bestimmten Hintergrundfarbe zu summieren.
Anwenden COUNTBYCELLCOLOR Und SUMBYCELLCOLOR Funktionen für jede Hintergrundfarbe separat und Sie erhalten die Ergebnisse wie im folgenden Screenshot gezeigt: 
Kutools-Funktionen enthält eine Reihe integrierter Funktionen, die Excel-Benutzern das Berechnen erleichtern, darunter Anzahl/Summe/Durchschnitt sichtbarer Zellen, Anzahl/Summe nach Zellenfarbe, Anzahl/Summe nach Schriftfarbe, Zeichen zählen, Anzahl nach Fettschrift usw. Kostenlose Testphase!
Zählen und summieren Sie Zellen basierend auf einer bestimmten Füllfarbe mit Kutools für Excel
Wenn Sie die obige benutzerdefinierte Funktion verwenden, müssen Sie die Formel einzeln eingeben. Wenn viele verschiedene Farben vorhanden sind, ist diese Methode mühsam und zeitaufwändig. Aber wenn ja Kutools für Excel Autor Nach Farbe Mit dem Dienstprogramm können Sie schnell einen Bericht über farbige Zellen erstellen. Sie können nicht nur farbige Zellen zählen und summieren, sondern auch Durchschnitts-, Maximal- und Mindestwerte Farbspektrum.
1
. Wählen Sie den Bereich aus, den Sie verwenden möchten, und klicken Sie Kutools Plus > Nach Farbe, siehe Screenshot: 
2
. Und in Nach Farbe Gehen Sie bitte wie im folgenden Screenshot vor:
(1) Auswählen Standardformatierung aus Farbmethode Dropdown-Liste;
(2) Auswählen Hintergrund aus Konto Typ Dropdown-Liste.
(3) Klicken Sie auf die Schaltfläche „Bericht generieren“. 
Aufmerksamkeit: Um farbige Zellen anhand einer bestimmten bedingten Formatierungsfarbe zu zählen und zu summieren, wählen Sie diese Option aus Bedingte Formatierung aus Farbmethode Dropdown-Liste im Dialogfeld oben oder wählen Sie aus Standard- und bedingte Formatierung aus der Dropdown-Liste, um alle mit der angegebenen Farbe gefüllten Zellen zu zählen.
Sie erhalten nun eine neue Statistik-Arbeitsmappe. Siehe Screenshot: 
Bei der Arbeit mit Tabellen sind die darin angezeigten Werte von vorrangiger Bedeutung. Ein wichtiger Bestandteil ist aber auch das Design. Einige Benutzer halten dies für einen Nebenfaktor und schenken ihm keine große Aufmerksamkeit. Aber vergebens, denn ein schön gestalteter Tisch ist eine wichtige Voraussetzung für eine bessere Wahrnehmung und ein besseres Verständnis durch den Nutzer. Dabei spielt die Datenvisualisierung eine besonders wichtige Rolle. Sie können beispielsweise Visualisierungstools verwenden, um Tabellenzellen basierend auf ihrem Inhalt einzufärben. Lassen Sie uns herausfinden, wie dies in Excel erfolgen kann.
Natürlich ist es immer schön, eine gut gestaltete Tabelle zu haben, in der die Zellen je nach Inhalt unterschiedlich gefärbt sind. Aber besonders relevant diese Möglichkeit für große Tabellen mit großen Datenmengen. In diesem Fall erleichtert das Füllen der Zellen mit Farbe den Benutzern die Navigation in dieser riesigen Informationsmenge erheblich, da sie sozusagen bereits strukturiert ist.
Sie können versuchen, die Blattelemente von Hand einzufärben. Wenn die Tabelle jedoch groß ist, wird dies viel Zeit in Anspruch nehmen. Darüber hinaus kann in einem solchen Datenfeld der menschliche Faktor eine Rolle spielen und es werden Fehler gemacht. Ganz zu schweigen davon, dass die Tabelle dynamisch sein kann und sich die darin enthaltenen Daten regelmäßig und massiv ändern. In diesem Fall wird eine manuelle Änderung der Farbe unrealistisch.
Aber es gibt einen Ausweg. Auf Zellen, die dynamische (sich ändernde) Werte enthalten, wird die bedingte Formatierung angewendet, und für statistische Daten können Sie das Tool verwenden "Suchen und Ersetzen".
Methode 1: Bedingte Formatierung
Mithilfe der bedingten Formatierung können Sie bestimmte Wertegrenzen festlegen, ab denen Zellen in der einen oder anderen Farbe eingefärbt werden. Die Einfärbung erfolgt automatisch. Wenn der Zellenwert aufgrund einer Änderung über den Rand hinausgeht, wird dieses Blattelement automatisch neu gezeichnet.
Sehen wir uns anhand eines konkreten Beispiels an, wie diese Methode funktioniert. Wir verfügen über eine Tabelle mit Unternehmenseinkommen, in der die Daten monatlich aufgeschlüsselt sind. Wir müssen die Elemente, bei denen das Einkommen geringer ist, in verschiedenen Farben hervorheben 400000 Rubel, ab 400000 Vor 500000 Rubel und mehr 500000 Rubel
- Wir markieren die Spalte, die Informationen zum Einkommen des Unternehmens enthält. Wechseln Sie dann zur Registerkarte "Heim". Klicken Sie auf die Schaltfläche "Bedingte Formatierung", das sich auf dem Menüband in der Toolbox befindet „Stile“. Wählen Sie in der sich öffnenden Liste das Element aus „Regelverwaltung…“.
- Das Fenster zur Verwaltung bedingter Formatierungsregeln wird geöffnet. Auf dem Feld „Formatierungsregeln anzeigen für“ Wert muss eingestellt werden „Aktuelles Fragment“. Standardmäßig sollte dort genau das angegeben werden, aber überprüfen Sie für alle Fälle die Einstellungen und ändern Sie sie bei Unstimmigkeiten gemäß den oben genannten Empfehlungen. Klicken Sie anschließend auf die Schaltfläche „Eine Regel erstellen…“.
- Das Fenster zum Erstellen einer Formatierungsregel öffnet sich. Wählen Sie in der Liste der Regeltypen eine Position aus . Im Regelbeschreibungsblock im ersten Feld sollte sich der Schalter in der Position befinden "Werte". Stellen Sie im zweiten Feld den Schalter auf Position "Weniger". Im dritten Feld geben wir den Wert an; Blattelemente, die einen niedrigeren Wert enthalten, werden in einer bestimmten Farbe bemalt. In unserem Fall wird dieser Wert sein 400000 . Klicken Sie anschließend auf die Schaltfläche "Format…".
- Das Fenster „Zellen formatieren“ wird geöffnet. Wechsel zur Registerkarte "Füllen". Wählen Sie die Füllfarbe aus, mit der Sie Zellen hervorheben möchten, die einen Wert kleiner als enthalten 400000 . Klicken Sie anschließend auf die Schaltfläche "OK" am unteren Rand des Fensters.
- Wir kehren zum Fenster zum Erstellen einer Formatierungsregel zurück und klicken dort ebenfalls auf die Schaltfläche "OK".
- Nach dieser Aktion werden wir erneut weitergeleitet Manager für bedingte Formatierungsregeln. Wie Sie sehen, wurde bereits eine Regel hinzugefügt, wir müssen jedoch noch zwei weitere hinzufügen. Also drücken wir erneut den Knopf „Eine Regel erstellen…“.
- Und wieder befinden wir uns im Fenster zur Regelerstellung. Weiter zum Abschnitt „Nur Zellen formatieren, die enthalten“. Im ersten Feld dieses Abschnitts belassen wir den Parameter „Zellenwert“, und im zweiten stellen wir den Schalter auf Position "Zwischen". Im dritten Feld müssen Sie den Anfangswert des Bereichs angeben, in dem die Blattelemente formatiert werden. In unserem Fall diese Nummer 400000 . Im vierten geben wir den Endwert dieses Bereichs an. Es wird sich darauf belaufen 500000 . Klicken Sie anschließend auf die Schaltfläche "Format…".
- Wechseln Sie im Formatierungsfenster erneut zur Registerkarte "Füllen", aber dieses Mal wählen wir eine andere Farbe und klicken dann auf die Schaltfläche "OK".
- Klicken Sie nach der Rückkehr zum Regelerstellungsfenster ebenfalls auf die Schaltfläche "OK".
- Wie wir sehen, in Regelmanager Wir haben bereits zwei Regeln erstellt. Es bleibt also noch die dritte zu schaffen. Klicken Sie auf die Schaltfläche "Regel erstellen".
- Gehen Sie im Regelerstellungsfenster erneut zum Abschnitt „Nur Zellen formatieren, die enthalten“. Im ersten Feld belassen wir die Option „Zellenwert“. Stellen Sie im zweiten Feld den Schalter auf Polizei "Mehr". Im dritten Feld geben wir die Nummer ein 500000 . Klicken Sie dann wie in den vorherigen Fällen auf die Schaltfläche "Format…".
- Im Fenster „Zellenformat“ Gehen Sie erneut zur Registerkarte "Füllen". Dieses Mal wählen wir eine Farbe, die sich von den beiden vorherigen Fällen unterscheidet. Klicken Sie auf die Schaltfläche "OK".
- Klicken Sie im Regelerstellungsfenster erneut auf die Schaltfläche "OK".
- Öffnet Regelmanager. Wie Sie sehen, wurden alle drei Regeln erstellt, klicken Sie also auf die Schaltfläche "OK".
- Jetzt werden Tabellenelemente gemäß den in den Einstellungen für bedingte Formatierung angegebenen Bedingungen und Grenzen eingefärbt.
- Wenn wir den Inhalt einer der Zellen ändern und dabei über die Grenzen einer der angegebenen Regeln hinausgehen, ändert sich automatisch die Farbe dieses Blattelements.
Sie können die bedingte Formatierung auch auf etwas andere Weise verwenden, um Arbeitsblattelemente einzufärben.
Methode 2: Verwenden des Such- und Auswahlwerkzeugs
Wenn die Tabelle statische Daten enthält, die sich im Laufe der Zeit nicht ändern sollen, können Sie mit einem Tool die Farbe der Zellen basierend auf ihrem aufgerufenen Inhalt ändern „Suchen und auswählen“. Mit diesem Tool können Sie die angegebenen Werte finden und die Farbe in diesen Zellen ändern vom Benutzer benötigt. Bitte beachten Sie jedoch, dass sich bei einer Änderung des Inhalts in den Blattelementen die Farbe nicht automatisch ändert, sondern gleich bleibt. Um die Farbe auf die aktuelle zu ändern, müssen Sie den Vorgang erneut wiederholen. Daher ist diese Methode für Tabellen mit dynamischem Inhalt nicht optimal.
Sehen wir uns anhand eines konkreten Beispiels an, wie das funktioniert. Für dieses Beispiel verwenden wir die gleiche Tabelle der Unternehmenseinkommen.
- Wählen Sie die Spalte mit den Daten aus, die farblich formatiert werden sollen. Gehen Sie dann zur Registerkarte "Heim" und drücken Sie die Taste „Suchen und auswählen“, das sich auf dem Menüband in der Toolbox befindet "Bearbeitung". Klicken Sie in der sich öffnenden Liste auf das Element "Finden".
- Fenster öffnet sich "Suchen und Ersetzen" in der Registerkarte "Finden". Lassen Sie uns zunächst die Werte bis zu ermitteln 400000
Rubel Da wir keine einzige Zelle haben, die einen Wert kleiner als enthält 300000
Rubel, dann müssen wir tatsächlich alle Elemente auswählen, die Zahlen im Bereich von enthalten 300000
Vor 400000
. Leider ist es nicht möglich, diesen Bereich direkt anzugeben, wie es bei der Anwendung der bedingten Formatierung der Fall ist diese Methode es ist verboten.
Aber es besteht die Möglichkeit, die Dinge ein wenig anders zu machen, was zum gleichen Ergebnis führt. Sie können das folgende Muster in der Suchleiste angeben "3?????". Ein Fragezeichen bedeutet ein beliebiges Zeichen. Das Programm sucht also nach allen sechsstelligen Zahlen, die mit der Ziffer beginnen "3". Das heißt, die Suchergebnisse enthalten Werte im Bereich 300000 – 400000 , das ist es, was wir brauchen. Wenn die Tabelle kleinere Zahlen hätte 300000 oder weniger 200000 , dann müsste für jeden Bereich von einhunderttausend die Suche separat durchgeführt werden.
Geben Sie den Ausdruck ein "3?????" auf dem Feld "Finden" und drücken Sie die Taste „Finde alles».
- Anschließend werden unten im Fenster Suchergebnisse geöffnet. Klicken Sie mit der linken Maustaste auf eine davon. Dann geben wir die Tastenkombination ein Strg+A. Anschließend werden alle Suchergebnisse ausgewählt und gleichzeitig die Elemente in der Spalte ausgewählt, auf die diese Ergebnisse verweisen.
- Nachdem die Elemente in der Spalte ausgewählt wurden, schließen Sie das Fenster nicht überstürzt "Suchen und Ersetzen". Während in der Registerkarte "Heim" Zu dem, zu dem wir zuvor gewechselt sind, wechseln Sie in der Multifunktionsleiste zum Werkzeugblock "Schriftart". Klicken Sie auf das Dreieck rechts neben der Schaltfläche "Füllfarbe". Es öffnet sich eine Auswahl verschiedener Füllfarben. Wir wählen die Farbe aus, die wir auf Blattelemente anwenden möchten, die Werte kleiner als enthalten 400000 Rubel
- Wie Sie sehen können, enthalten alle Zellen der Spalte Werte kleiner als 400000 Rubel, hervorgehoben in der ausgewählten Farbe.
- Jetzt müssen wir die Elemente einfärben, die Werte im Bereich von enthalten 400000 Vor 500000 Rubel Dieser Bereich umfasst Zahlen, die dem Muster entsprechen „4??????“. Geben Sie es in das Suchfeld ein und klicken Sie auf die Schaltfläche "Finde alle", nachdem wir zuvor die benötigte Spalte ausgewählt haben.
- Ähnlich wie beim vorherigen Mal Suchergebnisse Wir wählen das gesamte erhaltene Ergebnis durch Drücken einer Hotkey-Kombination aus STRG+A. Wechseln Sie anschließend zum Symbol zur Auswahl der Füllfarbe. Wir klicken darauf und klicken auf das Symbol des benötigten Farbtons, der die Elemente des Blattes einfärbt, wobei die Werte im Bereich von liegen 400000 Vor 500000 .
- Wie wir sehen können, sind nach dieser Aktion alle Tabellenelemente mit Daten im Intervall s 400000 Von 500000 mit der ausgewählten Farbe hervorgehoben.
- Jetzt müssen wir nur noch das letzte Werteintervall auswählen – mehr 500000 . Auch hier haben wir Glück, denn alle Zahlen sind größer 500000 liegen im Bereich von 500000 Vor 600000 . Daher geben wir im Suchfeld den Ausdruck ein „5?????“ und drücken Sie die Taste "Finde alle". Wenn es Werte größer als gäbe 600000 , dann müssten wir zusätzlich nach dem Ausdruck suchen „6?????“ usw.
- Markieren Sie die Suchergebnisse erneut mit der Kombination Strg+A. Wählen Sie als Nächstes mithilfe der Schaltfläche im Menüband eine neue Farbe aus, um das darüber liegende Intervall auszufüllen 500000 nach der gleichen Analogie wie zuvor.
- Wie Sie sehen, werden nach dieser Aktion alle Elemente der Spalte entsprechend dem darin eingegebenen Zahlenwert ausgefüllt. Jetzt können Sie das Suchfenster schließen, indem Sie auf die Standardschaltfläche zum Schließen in der oberen rechten Ecke des Fensters klicken, da unser Problem als gelöst betrachtet werden kann.
- Wenn wir jedoch die Zahl durch eine andere ersetzen, die über die für eine bestimmte Farbe festgelegten Grenzen hinausgeht, ändert sich die Farbe nicht, wie es bei der vorherigen Methode der Fall war. Dies weist darauf hin, dass diese Option nur in den Tabellen zuverlässig funktioniert, in denen sich die Daten nicht ändern.
Wie Sie sehen, gibt es zwei Möglichkeiten, Zellen abhängig von den darin enthaltenen numerischen Werten einzufärben: mithilfe der bedingten Formatierung und mithilfe der "Suchen und Ersetzen". Die erste Methode ist fortschrittlicher, da Sie damit die Bedingungen, unter denen Blattelemente hervorgehoben werden, klarer definieren können. Darüber hinaus ändert sich bei der bedingten Formatierung die Farbe eines Elements automatisch, wenn sich der Inhalt darin ändert, was mit der zweiten Methode nicht möglich ist. Das Füllen von Zellen erfolgt jedoch je nach Wert mithilfe des Tools "Suchen und Ersetzen" Es kann auch verwendet werden, jedoch nur in statischen Tabellen.
IN ausführliches Video Lektion. Heute möchten wir diesen Artikel etwas erweitern, um ein spezifischeres Problem zu lösen. Nehmen wir an, Sie müssen die Anzahl der Zellen abhängig von der Farbe der Zellen oder der Farbe des Textes zählen.
Ab Excel 2007 verfügt das Programm über eine integrierte Möglichkeit, Zellen nach Farbe zu sortieren. So können wir die benötigte Farbe filtern, die angezeigten Zellen auswählen und die Gesamtzahl der Zellen visuell sehen. Aber was ist, wenn wir dies oft tun müssen und gleichzeitig alles gezählt und mithilfe von Formeln neu berechnet werden muss?
Für diese Zwecke müssen Sie ein sehr einfaches Makro verwenden, oder besser gesagt eine benutzerdefinierte Funktion, nennen wir es FarbNom, ermöglicht es uns, den numerischen Code für die Füllfarbe abzurufen und dann mithilfe dieses Codes die Gesamtzahl der Zellen mithilfe der im Artikel beschriebenen Techniken zu zählen
Also lasst uns anfangen. Gehen Sie dazu zum Visual Basic-Editor:
V Excel 2003 Drücken Sie Service, Weiter Makro und dann Visual Basic-Editor.
V Excel 2007, 2010 und 2013 es wird anders gemacht. Gehen Sie zum Abschnitt Entwickler, dann auswählen Visual Basic
Aufmerksamkeit! Symbolleistenbereich Entwickler In Excel 2007 ist es standardmäßig verfügbar, in Excel 2010 und 2013 muss es jedoch aktiviert werden. Dies ist besonders nützlich für Benutzer, die häufig mit Makros arbeiten. Um die Entwicklersymbolleiste in Excel 2010 oder 2013 zu aktivieren, müssen Sie ausführen Datei | Optionen | Passen Sie das Menüband an Danach müssen Sie auf der rechten Seite das Kontrollkästchen neben der Aufschrift aktivieren Entwickler
Nachdem der Editor geöffnet wurde Visual Basic, Fügen Sie ein leeres Modul ein. Wählen Sie dazu das Menü aus Einfügen und weiter Modul
Öffentliche Funktion ColorNom (Zelle als Bereich)
ColorNom = Cell.Font.ColorIndex
Funktion beenden
Wichtig! Wenn Sie die bedingte Formatierung verwenden, können Sie mit dieser Funktion die Zellfarbnummer nicht finden. Darüber hinaus berechnet Excel beim Ändern der Farbe einer Zelle die Werte nicht neu; Sie müssen dies manuell tun, indem Sie Strg+Alt+F9 drücken, sonst werden die Änderungen beim erneuten Öffnen übernommen Diese Datei. Dies liegt daran, dass Excel das Ändern der Farbe einer Zelle nicht als Bearbeitung der Formel betrachtet. Wenn dies in diesem Zusammenhang von entscheidender Bedeutung ist, können Sie die Formel selbst ändern, indem Sie einfach eine Funktion hinzufügen, die ständig neu berechnet wird und keinen Einfluss auf die Bestimmung der Zellenfarbe hat. Geben Sie beispielsweise die Funktion zur Ermittlung des aktuellen Datums multipliziert mit Null an.
In unserem Fall sieht die Funktion so aus.
=ColorNom (A1)+Today()*0
Ein Beispiel für das Zählen der Anzahl der Werte anhand der Zellenfüllfarbe in Excel
Betrachten Sie das obige Beispiel mit einer Liste von Früchten. Wir haben den Zellcode definiert und ihn neben jeder Zelle angezeigt.
So sehen die Argumente dieser Funktion aus
COUNTIF(Bereich, Kriterium)
Schreiben wir die Formel:
COUNTIF($B$1:$B$8 ;E2 )
Wir haben den Bereich mit einem Dollarzeichen angegeben, damit er festgelegt ist und die Formel erweitert werden kann. Wir haben nur drei Kriterien und diese sind in unserer Hilfstabelle angegeben. Erweitern wir die Formel und ermitteln die Anzahl der Zellen nach Farbe.
Beispieldatei herunterladen: (Datei mit Makrounterstützung)
Hallo, liebe Leser. Haben Sie schon einmal mit großen Datenmengen in einer Tabelle gearbeitet? Wissen Sie, es wird viel bequemer sein, mit ihnen zu arbeiten, wenn Sie wissen, wie man mehrere davon auswählt Excel-Zellen verschiedene Farben unter bestimmten Bedingungen. Möchten Sie wissen, wie es geht? In diesem Tutorial werden wir dafür sorgen, dass sich die Farbe der Zelle je nach ändert Excel-Werte, und färben Sie auch alle Zellen mithilfe der Suche ein.
Die Füllfarbe ändert sich mit dem Wert
Als Beispiel werden wir üben, dass eine Zelle in einer bestimmten Tabelle unter einer bestimmten Bedingung ihre Farbe ändert. Ja, nicht einer, sondern alle mit einem Wert im Bereich von 60 bis 90. Dazu verwenden wir die Funktion „Bedingte Formatierung“.
Wählen Sie zunächst den Datenbereich aus, den wir formatieren möchten.
Suchen Sie als Nächstes auf der Registerkarte „Startseite“ die Schaltfläche „Bedingte Formatierung“ und wählen Sie „Regel erstellen“ aus der Liste aus. 
Das Fenster „Formatierungsregeln erstellen“ hat sich geöffnet. Wählen Sie in diesem Fenster den Regeltyp aus: „Nur Zellen formatieren, die Folgendes enthalten.“
Gehen Sie als Nächstes zum Abschnitt „Regelbeschreibung ändern“, wo Sie die Bedingungen angeben müssen, unter denen die Befüllung durchgeführt wird. In diesem Abschnitt können Sie verschiedene Bedingungen festlegen, unter denen es sich ändern soll.
In unserem Fall ist es notwendig, Folgendes einzugeben: „Zellenwerte“ und „zwischen“. Wir geben auch einen Bereich an, in dem bei einem Wert zwischen 60 und 90 eine Füllung angewendet wird. Schauen Sie sich den Screenshot an, wie ich es gemacht habe. 
Natürlich müssen Sie bei der Arbeit mit Ihrer Tabelle möglicherweise ganz andere Bedingungen ausfüllen, die Sie angeben, aber jetzt trainieren wir gerade.
Wenn Sie es ausgefüllt haben, beeilen Sie sich nicht, auf die Schaltfläche „OK“ zu klicken. Zuerst müssen Sie wie im Screenshot auf die Schaltfläche „Formatieren“ klicken und zu den Fülleinstellungen gehen.
Okay, wie Sie sehen können, hat sich das Fenster „Zelle formatieren“ geöffnet. Hier müssen Sie zur Registerkarte „Ausfüllen“ gehen, dort die gewünschte auswählen und in diesem und im vorherigen Fenster auf „OK“ klicken. Ich habe mich für eine grüne Füllung entschieden. 
Schauen Sie sich Ihr Ergebnis an. Ich denke, es ist dir gelungen. Es ist mir auf jeden Fall gelungen. Schauen Sie sich den Screenshot an: 
Färben wir eine Zelle mit einer bestimmten Farbe, wenn sie mit etwas übereinstimmt
Kehren wir zu unserer Tabelle in ihrer ursprünglichen Form zurück. Und jetzt ändern wir die Farbe dort, wo die Zahl 40 enthalten ist, in Rot und dort, wo die Zahl 50 enthalten ist, in Gelb. Natürlich können Sie in diesem Fall die erste Methode verwenden, aber wir möchten mehr über die Excel-Funktionen erfahren.
Dieses Mal verwenden wir die Funktion „Suchen und Ersetzen“.
Wählen Sie den Abschnitt der Tabelle aus, in dem wir Änderungen vornehmen möchten. Wenn es sich um das gesamte Blatt handelt, macht es keinen Sinn, es auszuwählen.
Jetzt ist es an der Zeit, das Suchfenster zu öffnen. Klicken Sie auf der Registerkarte „Startseite“ im Abschnitt „Bearbeiten“ auf die Schaltfläche „Suchen und Auswählen“. 
Sie können auch die Tastenkombinationen STRG + F verwenden
Im Feld „Suchen“ geben wir an, wonach wir suchen. In diesem Fall schreiben wir „40“ und klicken dann auf die Schaltfläche „Alle suchen“.
Nachdem die Suchergebnisse unten angezeigt wurden, wählen Sie eines davon aus und drücken Sie STRG + A, um alle auf einmal auszuwählen. Klicken Sie dann auf „Schließen“, um das Fenster „Suchen und Ersetzen“ zu entfernen. 
Wenn wir alles ausgewählt haben, was die Zahl 40 enthält, wählen Sie auf der Registerkarte „Startseite“ im Abschnitt „Schriftart“ die Farbe der Zelle aus. Unseres ist rot. Und wie Sie auf Ihrem Bildschirm und in meinem Screenshot sehen können, wurden sie rot. 
Jetzt müssen die gleichen Schritte befolgt werden, um diejenigen einzufärben, bei denen die Zahl 50 angegeben ist. Ich denke, jetzt verstehen Sie, wie das geht.
Warst du erfolgreich? Und schau, was mit mir passiert ist. 
Das ist alles. Danke Freunde. Abonnieren, kommentieren, der Gruppe beitreten, teilen soziale Netzwerke und bleiben Sie über neue Artikel auf dem Laufenden. Und vergessen Sie auch nicht, andere Artikel auf dieser Website zu lesen.