Microsoft Excel

Herbers Excel/VBA-Archiv

Summe bei Farben


Betrifft: Summe bei Farben
von: Berni
Geschrieben am: 05.12.2017 12:21:31

Mahlzeit Excellenzen,

ich wollte verschiedene Zahlen summieren, die rot eingefärbt sind. Wenn diese in einer Spalte z.B. stehen, dann kann ich das ja mit Namensdefinition in der Nebenspalte und dem Kriterium =Zelle.zuordnen(63;indirekt("ZS(-!)";))runterkopieren und dann die Summewenn-Formel anwenden.
Gibt es da nicht eine einfachere Lösung, wenn ich die Daten in mehreren Spalten nebeneinander stehen habe? Oder brauche ich da für jede Spalte eine eigene Hilfsspalte?

Vielleicht habt ihr da einen Trick auf Lager.

Danke und
Schöne Grüße
Berni

  

Betrifft: AW: Summe bei Farben
von: SF
Geschrieben am: 05.12.2017 12:34:39

Hola,

werden die Zahlen willkürlich gefärbt oder gibt es dahinter eine Logik die man mit der bedingten Formatierung erreichen könnte?

Gruß,
steve1da


  

Betrifft: AW: Summe bei Farben
von: Berni
Geschrieben am: 05.12.2017 12:41:59

Hallo Steve,

Eben, das ist das Problem. Wenn ich die Zellen über eine Bedingung einfärbe, dann genügt mir die Summewennformel auch ohne Farbe.
Die Zellen sind aber nach unterschiedlichen Kriterien eingefärbt.

Schöne Grüße
Berni


  

Betrifft: AW: Summe bei Farben
von: SF
Geschrieben am: 05.12.2017 12:43:56

Hola,

Die Zellen sind aber nach unterschiedlichen Kriterien eingefärbt.

und nach welchen?

Gruß,
steve1da


  

Betrifft: AW: Summe bei Farben
von: Berni
Geschrieben am: 05.12.2017 13:38:39

Die Spalten haben unterschiedliche Grenzwerte. Nur diese sollen summiert werden, die diese Grenzwerte überschritten haben.
Leg Dir einmal ein Beispiel bei.

http://www.herber.de/bbs/user/118107.xlsx

LG Berni


  

Betrifft: AW: Summe bei Farben
von: SF
Geschrieben am: 05.12.2017 13:44:30

Hola,

und was soll jetzt summiert werden? Die Zahlen pro Spalte, oder pro Zeile?
Also B13, B14, B31 oder C2, G2, H2?

Gruß,
steve1da


  

Betrifft: AW: Summe bei Farben
von: Berni
Geschrieben am: 05.12.2017 15:17:02

Da die Grenzwerte in der Zeile 1 stehen und nach diesen sich die bedingte Formatierung richtet, sollten die eingefärbten Spalten summiert werden.

Entschuldige die Unklarheiten.

Danke
LG Berni


  

Betrifft: AW: Summe bei Farben
von: SF
Geschrieben am: 05.12.2017 15:19:05

Hola,

dann:

=SUMMENPRODUKT((B2:B36>B1)*(B2:B36))

Gruß,
steve1da


  

Betrifft: AW: Summe bei Farben
von: Berni
Geschrieben am: 05.12.2017 15:49:51

Herzlichen Dank Steve,

Funktioniert prächtig

Danke nochmals
LG Berni


  

Betrifft: AW: Summe bei Farben
von: SF
Geschrieben am: 05.12.2017 15:54:16

Hola,

geht natürlich auch mit Summewenn():

=SUMMEWENN(B2:B36;">"&B1;B2:B36)

Gruß,
steve1da


  

Betrifft: Die BedFmt-Regel-Fmln sind ziemlich konfus, ...
von: Luc:-?
Geschrieben am: 05.12.2017 18:21:50

…Berni,
so dass sich auf den 1.Blick Steves einfache Lösung nicht unbedingt anbietet. Die solltest du dann aber auch nach der Basis­An­nahme von Steves Fml vereinheitlichen!
Ansonsten hätte ich noch 4 Bemerkungen:
1. Mit ZELLE.ZUORDNEN kann man keine per BedingtFormatierung erzeugten Farben ermitteln (höchstens ZahlenFormate → .Text)!
2. Ab Xl14/2010 steht die objektbildende Range-Eigenschaft .DisplayFormat dafür zV. Diese hat nahezu die gleichen Eigenschaften wie ihr MutterObjekt .Range. Man kann sie aber regulär nur mittels SubProzedur auswerten. Mit einer UDF (eigene Fkt) in einer Zell­Fml ist das nicht direkt möglich (Xl verhindert das absichtlich → MS-Sperre!), sondern nur per XML-Fkt AUSWERTEN oder einer auf der vbFkt Evaluate basierenden UDF wie TxEval, die einen Fml-Text mit einer solchen UDF auswertet (Umgehung dieser Sperre).
3. Es ist idR günstiger, die Formate zu ermitteln und mit einer Vorgabe zu vgln als eine spezielle UDF nur für diesen einen Zweck zu schreiben, denn Xl bringt sowohl Vgls- als auch Summierungsmöglichkeit bereits mit. Es müsste also nur die FormatErmittlung ergänzt wdn.
4. Wären die Bedingungen für die Färbung tatsächlich so verschiedenartig wie von dir ursprünglich behauptet, wäre das eine Mög­lich­keit, so etwas zu machen.
Mit singularen MatrixFmln:
Zeilensumme J2[:J36]: {=SUMME(MTRANS(TxEval(WECHSELN("CellColor(#)";"#";ADRESSE(ZEILE(2:2);SPALTE(B:H))))=255)*B2:H2)}
Spaltensumme B38[:H38]: {=SUMME(MTRANS(TxEval(WECHSELN("CellColor(#)";"#";ADRESSE(ZEILE(2:36);SPALTE(B:B))))=255)*B2:B36)}
Gesamtsumme J38: {=SUMME(MTRANS(TxEval(WECHSELN("CellColor(#)";"#";ADRESSE(ZEILE(2:36);SPALTE(B:H))))=255)*B2:H36)}
Alternativ wäre auch die Bildung eines diskontinuierlichen Bereichs aus diesen Zellen möglich. Die Summierung seiner Werte erfolgt dann ebenfalls mit singularen MatrixFmln:
Zeilensumme K2[:K36]: {=SUMME(NoErrRange(B2:H2;;TxEval(WECHSELN("CellColor(#)";"#";ADRESSE(ZEILE(2:2);SPALTE(B:H))))=255))}
Spaltensumme B39[:H39]: {=SUMME(NoErrRange(B2:B36;;TxEval(WECHSELN("CellColor(#)";"#";ADRESSE(ZEILE(2:36);SPALTE(B:B))))=255))}
Gesamtsumme K39: {=SUMME(NoErrRange(B2:H36;;MTRANS(TxEval(WECHSELN("CellColor(#)";"#";ADRESSE(ZEILE(2:36);SPALTE(B:H))))=255)))}
Die Spaltensummen sind in beiden Fällen die gleichen wie mit Steves Fmln.
Feedback nicht unerwünscht! Gruß, Luc :-?

„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …