Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Nicht zusammenhängende Zellen einer Zeile addieren mit Bedingungen
#1
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.

Dimo
Antworten Top
#2
Hallo Dimo,

da du die Formel für den Farbzahlenwert schon hast, so:

=wenn(und(Farbzahlenwert(F6)<>32;Farbzahlenwert(F6)<>32;Farbzahlenwert(S6)<>32);E6+L6+R6;0)
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.





[-] Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:
  • dimo
Antworten Top
#3
Hallo Ego,

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?

Danke
Gruß Dimo
Antworten Top
#4
Hallo Dimo,

Wenn der Farbzahlenwert einer nicht gefärbten Tabelle 0 ist, dann "E6+L6+R6" ersetzen durch:

"Wenn(Farbzahlenwert(E6)=0;E6;0)+Wenn(Farbzahlenwert(L6)=0;L6;0)+Wenn(Farbzahlenwert(R6)=0;R6;0)"


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.





Antworten Top
#5
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 normaler­weise 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 (Eingabe­hinweise 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 (unzusammen­hängenden) Bereich ohne Fehler­werte, 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 auszu­wertende 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
Antworten Top
#6
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.

Danke
Gruß Dimo


Angehängte Dateien
.xls   beispieldatei.xls (Größe: 33,5 KB / Downloads: 8)
Antworten Top
#7
So, Dimo,
dann schaun wir mal:
ABCDEFGHIJKL
1
1. Stufe2. Stufe3. Stufe4. Stufe5. Stufe6. Stufe7. Stufe8. Stufe9. Stufe10. StufeSumme
 BetragBetragBetragBetragBetragBetragBetragBetragBetragBetrag …der roten und gelben Zellen in der Zeile, wenn keine blaue Zelle in der Zeile vorhanden ist.
Position 11,00 €2,00 €4,00 €7,00 €10,00 €1,00 €1,00 €1,00 €1,00 €1,00 €- €
Position 21,00 €2,00 €7,00 €1,00 €1,00 €1,00 €1,00 €1,00 €1,00 €1,00 €- €
Position 31,00 €2,00 €5,00 €7,00 €12,00 €1,00 €1,00 €1,00 €1,00 €1,00 €15,00 €
Position 41,00 €2,00 €6,00 €8,00 €7,00 €10,00 €15,00 €22,00 €1,00 €1,00 €- €
Position 51,00 €2,00 €3,00 €5,00 €8,00 €1,00 €1,00 €1,00 €1,00 €1,00 €6,00 €
            
sg MxFml:L5[:L9]: {=WENN(SUMME(--(TxEval("CellColor("&ADRESSE(ZEILE(A5);SPALTE(B5:K5);4)&")")<>HEXINDEZ("C07000")))=SPALTEN(B5:K5);SUMME(NoErrRange(B5:K5;1;TxEval("CellColor("&ADRESSE(ZEILE(A5);SPALTE(B5:K5);4)&")")<>HEXINDEZ("FFFFFF")));0)}
2
3
4
5
6
7
8
9
10
11
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-Eigenschaften­fenster – 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:
  • dimo
Antworten Top
#8
Abend,

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.

Herzlichen Dank für die Hilfe und Gute Nacht.

Dimo
Antworten Top
#9
Hallo,

hier im Nachgang noch eine Variante:


Code:
Dim i As Long, j As Long
Dim dblsum As Double

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?
Gruß Atilla
Antworten Top
#10
Nein, Atilla,
das ist eine Tabelle in einer Tabelle. Wink
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!

Bis dann, Castor
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste