Hallo, wahrscheinlich gibts für diesen Fall sogar schon ne Lösung im Forum. Nur weiß ich grad nicht mal nach was für Suchbegriffen ich danach suchen soll.
Ich habe folgendes vor:
In einer Zeile habe ich mehrere Euro-Beträge in nicht zusammenhängenden und nicht gleichmäßig verteilten Spalten welche ich addieren möchte. Das alleine würde ich sogar noch hinbekommen. Nun möchte ich aber außerdem noch die Hintergrundfarbe einiger anderer Zellen in derselben Zeile als Bedingungen hinterlegen.
Zum Verständnis nochmal anders erklärt:
Ich möchte zB. E6, L6 und R6 addieren. ABER nur wenn zB die Zellen F6, M6 und S6 alle NICHT blau sind. Sollte eine dieser Zellen blau sein, dann wird nichts addiert und stattdessen 0 angezeigt.
(bisher habe ich nur geschafft, diese blaue Hintergrundfarbe als Zahlenwert 23 anzeigen zu lassen...)
Falls es also schon eine Lösung gibt, bin ich für Hinweise dankbar. Ansonsten noch dankbarer für Lösungsvorschläge/-versuche.
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen." Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • dimo
vielen Dank schonmal! Das ist grundsätzlich genau das was ich gesucht habe.
Leider ist mir durch diesen Lösungsansatz gerade ein Denkfehler meinerseits aufgefallen:
Sofern in der Zeile KEINE blau-farbige Zelle vorkommt, sollen zwar alle Beträge addiert werden, allerdings nur wenn die Betrags-Zellen selbst auch farbig (zB rot oder gelb) sind. Also alle Betragszellen in der Zeile die nicht farbig markiert sind fallen aus der Addition raus....
Gibts für dieses Problem auch noch einen Platz im o.g. Ansatz?
Wie werden den die Farben vergeben, über bedingte Formatierung?
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen." Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Morrn, Dimo; dein (korrigiertes) Anliegen ist nicht ganz klar, weil du immer zum Bsp verwendest. Meine Schlussfolgerung lautet deshalb so: Es sollen alle Zellen einer Zeile summiert werden, die eine Farbe haben, die von der des normalen Zellhintergrunds abweicht, aber nur, wenn keine dieser Zellen die Farbe Blau hat. Wie ich feststellen konnte, steht der Index 23 für eine Art Meerblau (RGB:0.102.204), also kein reines Blau, denn das hat den Index 5 (oder 32). Hierzu eine Anmerkung: 1. Es ist ab Xl12/2007 grundsätzlich ungünstig, mit Farbindizes zu arbeiten, denn auch optisch deutlich unterscheidbare Farb(tönung)en können den gleichen Index haben. Außerdem können einige Grundfarben auch 2 Indizes besitzen (s. oben am Bsp Blau ). 2. Die uralte XLM-Funktion (XLM = Excel4Makro) ZELLE.ZUORDNEN kann nur in einer benannten Formel verwendet werden und gibt mit ihrem Argument1=38 oder 63 stets auch nur den Index einer durch Direkt-Formatierung aufgebrachten Zellfarbe wieder (Bedingt-Formatierung gab's damals noch nicht). Ihre Verwendung erfordert definitiv die Speicherung einer Datei ab Xl12/2007 als .xlsm/.xlsb. 3. Ab Xl14/2010 ist auch das Feststellen der durch eine Bedingt-Formatierung verursachten Zellfarbe relativ einfach und kann sogar durch eine in einer Formel verwendbare UDF (UserDefinedFunction → benutzerdefinierte/eigenprogrammierte Funktion) bestimmt werden, obwohl MS das zu verhindern trachtet (haben aber eine Lücke übersehen). Damit ist auch dieser Tatbestand für die anschld gezeigte Formel irrelevant. Programme in einer Mappe erfordern normalerweise ebenfalls eine Speicherung als .xlsm/.xlsb, nicht aber, wenn die verwendeten Programme in einem sog AddIn (zwecks allgemeiner Verwendung) stationiert werden, welches dann für diese Mappe aktiviert werden muss. Natürlich muss das dann auch bei jedem Nutzer der Mappe erfolgen. Falls meine oben genannte Schlussfolgerung vollinhaltlich zutreffen sollte, würde folgende singulare (einzellige) Matrixformel (Eingabehinweise in der Xl-Hilfe beachten!) dein Problem lösen können (ich gehe hierbei vom Bereich E6:R6 aus, er kann aber auch größer gewählt werden): {=WENN(SUMME(--(TxEval("CellColor("&ADRESSE(ZEILE(A6);SPALTE(E:R);4)&")")<>HEXINDEZ("CC6600")))=SPALTEN(E6:R6);SUMME(NoErrRange(E6:R6;1;TxEval("CellColor("&ADRESSE(ZEILE(A6);SPALTE(E:R);4)&")")<>HEXINDEZ("FFFFFF")));0)} Die 3 verwendeten UDFs sind im Herber-Archiv enthalten, wobei NoErrRange einen diskreten (unzusammenhängenden) Bereich ohne Fehlerwerte, ausgeblendete Zellen und Zellen, die die ggf im 3.Argument angegebene Bedingung nicht erfüllen, erzeugt. Das 2.Argument=1 macht die UDF und damit die ganze Formel volatil, was bei Format-Auswertungen auch erforderlich ist. Zwecks Aktualisierung muss dann nur die Taste [F9] gedrückt oder in eine (leere) Zelle (doppel-)geklickt werden. CellColor ermittelt die tatsächlich gezeigte Zellfarbe (über das ab Xl14/2010 als Zelleigenschaft eingeführte DisplayFormat-Objekt). Da das direkt per UDF in einer Zellformel nicht möglich ist (das Objekt wurde von MS hierfür gesperrt, so dass nur #WERT! erscheinen würde), muss das über eine Formeltext-Auswertung erfolgen (ähnlich wie per XLM-Funktion AUSWERTEN in benannter Formel, aber in einer Zellformel, wobei der auszuwertende Text zwingend US-Original-Formel-Notation aufweisen muss). Links zu den UDFs: CellColor & TxEval : http://www.herber.de/cgi-bin/callthread....98#1477400 NoErrRange : http://www.herber.de/forum/archiv/1508to...tm#1508215 Gruß, Castor
Hallo, danke für Eure Antworten und Entschuldigung wenn das Problem noch nicht eindeutig rüber kam.
Ich habe mal eine Datei angehängt in der ein paar Zeilen der Tabelle ersichtlich sind. Es geht darum, die Beträge in gelben und roten Zellen zu addieren wenn in der Zeile keine blaue Zelle vorkommt. (das trifft in der Datei in den Zeilen 7 und 9 zu).
Bei allen Zeilen mit einer blauen Zelle am Ende der farbigen Balken soll Null als Betragssumme angezeigt werden
Bei den Farben bin ich davon ausgegangen, dass es bei nur drei verschiedenen Farben hoffentlich nicht so große Probleme mit den Indizes gibt. Falls doch, könnte ich die Farben auch ändern oder mir über bedingte Formatierung oder ähnliches Gedanken machen.
Vielleicht kann man aber mit der Datei jetzt das Problem schon so lösen.
Auch hier wieder ein Fall von einem Index für mehrere Farben. Dein „Blau“ war nicht RGB(0, 102, 204), sondern RGB(0, 112, 192). Beide Farben haben den Index 23! Habe die usprüngliche Formel also entsprechend anpassen müssen (auch an die andere Startzeile). Die Spalten mit den Farbindizes und die benannte Formel mit XLM-Funktion habe ich entfernt, weil sie in meiner Version nicht benötigt werden. Ob die relevanten Zellen nun rot, gelb oder grün gefärbt werden, spielt keine Rolle (auch der Farbton nicht), sie dürfen nur nicht weiß oder ungefärbt sein. Schon eine einzelne blaue Zelle (nur genau der o.g. Farbwert!) hingegen verhindert die Summenbildung in der Zeile. Sobald dein Xl die 3 verwendeten UDFs kennt, funktioniert die Formel auch, nicht eher. Damit Xl die UDFs kennt, musst du ihre unter den zuvor angegebenen Links zu findenden Programme in ein anzulegendes allgemeines Modul (kein Klassenmodul!) der Mappe kopieren. Alternativ kannst du auch eine ansonsten leere Mappe verwenden und diese dann als AddIn speichern (entsprechende Eigenschaft der Mappe zuvor im VBE-Eigenschaftenfenster – bei Auswahl der Mappe im VBE-Projekte-Fenster – einstellen!). Das AddIn muss dann nur noch unter Xl aktiviert werden und steht dir dann mit den UDFs immer z.V., sonst kennt die UDFs nur deine ggw Mappe. Viel Erfolg, Castor
Folgende(r) 1 Nutzer sagt Danke an Castor für diesen Beitrag:1 Nutzer sagt Danke an Castor für diesen Beitrag 28 • dimo
die ganze Geschichte mit diesen UDFs und VBE usw. war bisher völliges Neuland für mich. Hatte gedacht/gehofft, dass man das einfach irgendwie in eine "klassische" Excel-Formel schreiben kann á la SUMMEWENNS oder sowas. War wohl nicht ganz so einfach.
Nach der Anregung hier und ein bisschen einlesen hat es nun aber tatsächlich geklappt! Möchte zwar nicht behaupten dass ich alles verstanden habe was da so passiert. Aber es geht.
lngZ = Cells(Rows.Count, 2).End(xlUp).Row For i = 5 To lngZ For j = 2 To 20 Step 2 If Cells(i, j).Interior.ColorIndex <> 23 Then If Cells(i, j).Interior.ColorIndex <> -4142 Then dblsum = dblsum + Cells(i, j).Value End If Else dblsum = 0 Exit For End If Next j Cells(i, 22) = dblsum dblsum = 0 Next i End Sub
@Castor
wieso sieht die eingestellte Tabelle so komisch aus? Ist das ein Bild in einer Tabelle?
Nein, Atilla, das ist eine Tabelle in einer Tabelle. Ich habe mein ursprünglich „normal“ aussehendes Tool auf Vorschlag eines anderen Antworters mal so geändert, damit man die Ränder nicht mitauswählen muss, wenn man sie in eine Xl-Tabelle kopieren will. Leider haben die Browser ihren „eigenen Kopf“ und synchronisieren die Spaltenbreiten und Zeilenhöhen der äußeren und inneren Tabelle nicht generell. Je nach Inhalt kann es also zu Abweichungen kommen, die sich erst am Tabellenende wieder ausgleichen. Die hiesige Forumssoftware mischt dann auch noch mit, wodurch die Zeilen 3 und 4 so schmal geworden sind. Es ist mir bis dato nicht gelungen, dieses Verhalten wirksam einzudämmen, weshalb ich das wahrscheinlich wieder abschaffen werde, sobald es mir gelungen ist, ein Kopieren (nur des inneren Teils) auf Klick zu installieren. Ich hatte in einer neueren Version (inkl Original-Zellrahmen) schon mal ein Rückschreiben nach Xl (ohne diesen Rahmen) drin, aber das unterstützen die meisten Browser nicht (wegen ActiveX-Komponente) und direkt aus dem Forum ging's auch mit dem IE nicht.
Ja, Dimo, dann ist's ja gut und du hast tatsächlich Neuland erfolgreich beschritten. Glückwunsch!
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
