Microsoft Excel

Herbers Excel/VBA-Archiv

Bedingter Farbwechsel bei Autofilter

Betrifft: Bedingter Farbwechsel bei Autofilter von: Undine
Geschrieben am: 18.09.2020 16:13:04

Guten Tag,


ich habe in einer Tabelle mit Ordnungsnummern einen bedingten Farbwechsel realisiert.


A

A

B

C

C


Dazu benutze ich in der bedingten Formatierung die Formel:

=REST(SUMMENPRODUKT(N($C$4:$C4<>$C$5:$C5));2)=0


Ich suche allerdings eine Möglichkeit, dies auch bei gesetzten Autofiltern in beliebigen Spalten der Tabelle zu ermöglichen. Ich habe hier im Forum eine Formel gefunden (https://www.herber.de/forum/archiv/1244to1248/1246609_Alternierende_Zeilenfarben_trotz_Autofilter.html):

=REST(SUMMENPRODUKT(N($A$1:$A1<>$A$2:$A2)*(TEILERGEBNIS(3;INDIREKT("A"&ZEILE($2:2)))));2)=0


und sie entsprechend angepasst:

=REST(SUMMENPRODUKT(N($C$4:$C4<>$C$5:$C5)*(TEILERGEBNIS(3;INDIREKT("C"&ZEILE($5:5)))));2)=0


Leider funktioniert sie nicht, sobald ich Autofilter nutze. Übersehe ich etwas? Gibt es alternative Formeln, die zum gewünschten Ergebnis führen?


Über Feedback würde ich mich sehr freuen.


VG

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Daniel
Geschrieben am: 18.09.2020 16:40:04

Hi

ich würde das mit einer Hilfsspalte machen:
in Spalte A stehen die Gruppenbezeichnungen.
in der nächsten Freien Spalte (hier Spalte B) dann diese Formel:
- in Zeile 1 der Spalte: der feste Wert WAHR oder FALSCH (ist egal, das legt nur fest mit welcher Farbe angefangen wird)
- ab Zeile 2 dann diese Formel: =Wenn(Oder(Teilergebnis(3;A2)=0;A1=A2;B1;Nicht(B1))
für die Bedingte Formatierung reicht dann als Formel einfach der Bezug auf die Zelle in dieser Spalte: =$B2

das hat dann zur Folge, dass bei ausgeblendeten Zeilen der Wert immer aus der darüberliegenden Zeile genommen wird, so dass ein Wechsel der Farbe nur bei sichtbaren Zeilen erfolgen kann.

Gruß Daniel

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Undine
Geschrieben am: 18.09.2020 16:54:16

Hallo Daniel,

danke für den Vorschlag. Bei der Formel gibt Excel mir eine Fehlermeldung aus, auch wenn ich hinten die Wenn-Formel mit einer Klammer schließe.

VG

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Daniel
Geschrieben am: 18.09.2020 16:58:33

sorry, da hab ich die schließende Klammer für das Oder vergessen.
aber bei Excel gut solltest du selber rausbekommen, wo die hin muss (nicht am Ende)
Gruß Daniel

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Undine
Geschrieben am: 18.09.2020 17:52:54

Hallo Daniel,

hab nun testen können, danke. Die Tabelle wird allerdings recht flexibel genutzt, d. h. es werden Zeilen gelöscht, hinzugefügt, geändert, neu sortiert. Das würde bedeuten, dass die Formel-Bezüge der Hilfsspalte dadurch immer wieder durcheinander gerieten.

Lässt sich deine Formel auch in die Formel der bedingten Formatierung einbinden, sodass ich ohne Hilfsspalte auskomme?

Die von mir oben erwähnte Formel aus dem Forum funktioniert übrigens mit Autofilter, aber ausschließlich mit Autofilter in der Spalte der Ordnungsnummern. Ließe sich das ggf. auf beliebige Spalten einer Tabelle z. B. C:AD erweitern?

VG

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Daniel
Geschrieben am: 18.09.2020 17:57:54

Hi
wenn zeilen gelöscht oder hinzugefügt werden, dann müssen Zellbezüge, die sich auf eine andere Zeile beziehen durch so ein Konstrukt ersetzt werden, dh in B2 dann für den Bezug auf B1: Index(B:B;Zeile()-1)

kann mann sich sich generell für Tabellenspielchen merken:
Zellbezüge gehen immer auf die gleiche Zeile in welche die Formel steht, oder auf die ganze Spalte.
dann gibt's kein Durcheinander oder Fehler beim Löschen oder Einfügen oder beim Sortieren.
und Rein Formal ist diese Bedingung durch den berechneten Zellbezug erfüllt.
Gruß Daniel

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Daniel
Geschrieben am: 18.09.2020 18:03:42

du kannst, wenn du die Zellbezüge über Index definierst, auch den ganzen Bereich als Ausgangsbasis festlegen und dann zusätzlich die Spaltennummer als Parameter angeben.
die Spaltennummer liest du über eine Zelle ein und dann kannst du damit festlegen, welche Spalte für den Farbwechsel zuständig sein soll.
allso dann auch für den Bezug auf A1 bzw A2: Index(A:XFD;Zeile();$A$1), bei Zeile() dann wieder mit und ohne -1 und in A1 gibst du die Spaltennummer ein, die die Gruppierung für die Färbung enthält.
Gruß Daniel

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Undine
Geschrieben am: 18.09.2020 18:25:07

Bis hierhin bin ich mitgekommen:
=WENN(ODER(TEILERGEBNIS(3;A2)=0;A1=A2);INDEX(B:B;ZEILE()-1);NICHT(INDEX(B:B;ZEILE()-1)))

Sortieren klappt. Lösche ich eine Zeile, werden die nachfolgenden Bezüge zerschossen.

Bei deinem zweiten Hinweis muss ich meine Excel-Kenntnisse offensichtlich nach unten korrigieren. Kann deine Anmerkungen leider nicht praktisch umsetzen.

VG

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Daniel
Geschrieben am: 18.09.2020 20:15:41

Ja, du hast das mit dem Index für einen Bezug auf die nächste Zeile ja auch nicht in allen Fällen umgesetzt.
Das musst du auch für die Bezüge auf Spalte A machen.

Gruß Daniel

Betrifft: AW: Bedingter Farbwechsel bei Autofilter
von: Undine
Geschrieben am: 18.09.2020 23:27:22

Okay.

Beiträge aus dem Excel-Forum zum Thema "Bedingter Farbwechsel bei Autofilter"