Office - Word, Excel und Co. 9.753 Themen, 41.598 Beiträge

Verfolgung starten Optionen

Excel Summenbildung

Mick1 / 14 Antworten / Baumansicht Nickles

Hallo! Ich habe da ne "kleinere Prblematik" und hoffe wieder mal auf Hilfe. Also folgendes: normale Exceltabellenblatt ( Office 2013) A1 bis A13 normale Datumseingabe; in B1 bis B13 sind Geldbeträge, da aber unterschiedliche farbige Zellen ( rot, gelb grün, braun usw.) eingetragen. Nun möchte ich aber  z.B. in B14 nur die Summe der Zahlen/Beträge von B1 bis B13, die in der roten Zellen hinterlegt sind, in B15 die in den gelben Kästchen eingetragen sind usw. Wie macht man das? Vielen Dank schon mal imvoraus.

bei Antwort benachrichtigen
Edgar Rau Mick1 „Excel Summenbildung“
Optionen

Hallo Mick1,

das kannst Du nur mit VBA lösen, hier ein kleines Makro:

sub summe1()

Dim i As Integer
Dim summe1 As Single
    i=1
    For i = 1 To 13
    Cells(i, 2).Select
    If Selection.Interior.ColorIndex = 3 Then
    summe1 = summe1 + Cells(i, 2)
    End If
    Next
    Cells(14, 2).Select
    ActiveCell.FormulaR1C1 = summe1


End Sub

Dieses Makro summiert alle Beträge zwischen B1 und B13, wenn der Hintergrund rot (Interior.ColorIndex = 3) ist. Für alle weiteren Farben musst Du den Interior.ColorIndex ändern.

Du kannst dieses Makro erweitern oder für jede Farbe ein eigenes Makro schreiben. Diese/s Makro/s würde ich dann auf eine Schaltfläche legen.

Viel Erfolg

Edgar

Wer brauchen ohne zu gebraucht, braucht brauchen gar nicht zu gebrauchen
bei Antwort benachrichtigen
Mick1 Edgar Rau „Hallo Mick1, das kannst Du nur mit VBA lösen, hier ein kleines Makro: sub summe1 Dim i As Integer Dim summe1 As Single i 1 ...“
Optionen

High!

Vielen Dank für die Antwort. Hab jetzt schon ne ganze Weile daran umhergebastelt, krieg das aber nicht hin; muss auch gestehen, hatte noch nie was mit Makroprogrammierungzu tuen und raff das nicht. Gibt's vielleicht auch ne einfachere Lösung, was für "Dummis"?

Danke schon mal im voraus!

bei Antwort benachrichtigen
neanderix Edgar Rau „Hallo Mick1, das kannst Du nur mit VBA lösen, hier ein kleines Makro: sub summe1 Dim i As Integer Dim summe1 As Single i 1 ...“
Optionen

Hört die unart, alles vorher zu .Select en denn nie auf?!

sub summe1()

Dim i As Integer
Dim summe1 As Single
    i=1
    For i = 1 To 13
   If  Cells(i, 2).Interior.Colorindex = 3 Then
        summe1 = summe1 + Cells(i, 2)
    End If
    Next
    Cells(14, 2)= summe1


End Sub

Ob die neu gestaltete If Anweisung so funktioniert, kann ich jetzt und hier nicht mit letzter Bestimmheit sagen (ich meine aber: ja, denn auch "Cells" kennt "Interior".

Die "Formula" anweisung hinter dem letzten Cells ist überflüssig.

Übrigens lässt sich das noch etwas eleganter schreiben

sub summe2

Dim summe2 as Single

Dim tws As Worksheet

Dim zelle As Range

Set tws = ThisWorkbook.Worksheet("Tabelle1")

For Each zelle IN tws.Range("B1:B13")

 if zelle.interior.colorindex=3 Then

    summe2 =summe 2 + zelle

End If

Next zelle

tws.Range("A14")=summe2

End Sub

Zur Erklärung:

die zweite DIM Anweisung dimensioniert die Variable tws als Variable vom Typ "Worksheet" - sie nimmt also ein Worksheet auf.

Die zweite DIM Anweisung dimensioniert die Variable als eine vom Typ Range - sie kann also einen beliebigen Zellbereich aufnehmen.

Die Zeile:

Set tws = ThisWorkbook.Worksheet("Tabelle1")

weist der Variablen tws das in klammern hinter .Worksheet angegebenene Arbeitsblatt zu.

Ich habe hier Tabelle1 reingeschrieben, der name ist den tatsächlichen Gegebenheiten anzupassen.

Mit der nachstehenden For Each ... IN ... NEXT Schleife durchlaufen wir den in ""Range" espeicherten Zellbereich Zelle für Zelle.

Die letzte Anweisung weist der Zelle B14 den in summe2 enthaltenen Wert zu.

Vollständig würde diese Anweisung lauten:

tws.Range("A14").Value=summe2

.Value ist aber die Standardmethode der .Range-Methode, d.h. wenn nichts näher spezifiziert wird wird automatisch ".Value" ausgeführt - dieses Verhalten habe ich hier genutzt.

Ansonsten gilt:

Faustregel: in VBA nur dann "Activate"n und/oder "Select"en, wenn es unvermeidlich und absolut nötig ist - beide Methoden kosten unnötig Zeit - die sich bei solch einem kleinen Projekt nicht bemerkbar macht, bei größeren Tabellen aber sehrwohl, auch bei kleinen Routinen.

Aus meiner sicht ist die Nutzung dieser Methoden schlechter Programmierstil, weil sie in den allermeisten Fällen eben gerade nicht nötig sind - sie zeigen meist nur, dass sich der Programmierer mit den Objekten und deren Methoden nicht genügend auseinandergesetzt hat.

Computers are like airconditioners - they stop working properly when you open Windows Ich bin unschuldig, ich habe sie nicht gewählt!
bei Antwort benachrichtigen
Edgar Rau neanderix „Hört die unart, alles vorher zu .Select en denn nie auf?! sub summe1 Dim i As Integer Dim summe1 As Single i 1 For i 1 To ...“
Optionen

Hallo neanderix,

ich habe mein Makro ausgedacht und ausprobiert, es funktioniert tadellos.

Die "Formula" anweisung hinter dem letzten Cells ist überflüssig.

Diese Aussage verstehe ich nicht.

Sicherlich ist Dein Makro eleganter und auch schlüssig, jedoch für den Fragesteller zu kompliziert.

Es ist mir immer wieder ein Rätsel, warum einige Mitglieder nicht auf für sie einfache Fragen antworten, jedoch die Antworten anderer kritisieren.

Ich wünsche Dir noch einen schönen Abend

Gruß Edgar

Wer brauchen ohne zu gebraucht, braucht brauchen gar nicht zu gebrauchen
bei Antwort benachrichtigen
neanderix Edgar Rau „Hallo neanderix, ich habe mein Makro ausgedacht und ausprobiert, es funktioniert tadellos. Diese Aussage verstehe ich ...“
Optionen

Ich habe auch nicht bestreiten, dass dein VBA funktioniert. 

Was ist an meinsr Aussage, die Formula...  Anweisung sei überflüssig, nicht zu verstehen? 

. Formula dient dazu, einer Zelle per VBA eine Formel zuzuweisen. 

zim Zuweisen eines Wertes dient .Value. 

Value ist aber, sofern ich mich richtig erinnere, auch bei .Cells() 

Standardmethode

Computers are like airconditioners - they stop working properly when you open Windows Ich bin unschuldig, ich habe sie nicht gewählt!
bei Antwort benachrichtigen
joseph3 Mick1 „Excel Summenbildung“
Optionen

Hallo Mick1,

eine Lösung ohne VBA findest du hier: 
http://www.excelformeln.de/formeln.html?welcher=432

Beste Grüße
Joseph

Wer buckelt, eckt nie an und wer kriecht, kann nicht stolpern.
bei Antwort benachrichtigen
Edgar Rau joseph3 „Hallo Mick1, eine Lösung ohne VBA findest du hier: http://www.excelformeln.de/formeln.html?welcher 432 Beste Grüße Joseph“
Optionen

Hallo Joseph,

Stimmt, jedoch muss bei einer Änderung der Werte die Formel (=Farbe) neu eingegeben oder die F9-Taste zum Neuberechnen gedrückt werden. Leider erfolgt keine automatische Neuberechnung, aber vielleicht hast Du ja einen Tipp dazu.

Außerdem wird eine Hilfsspalte benötigt, die den Interior.ColorIndex ausliest.

Ich persönlich finde es zu kompliziert und umständlich.

Gruß Edgar

Wer brauchen ohne zu gebraucht, braucht brauchen gar nicht zu gebrauchen
bei Antwort benachrichtigen
hddiesel Mick1 „Excel Summenbildung“
Optionen

Hallo Mick,
es geht auch ohne anpassen der Farbnummern.
Hier einmal ein Beispiel, kannst du nach Wunsch anpassen.

Sub Summe_Hintergrund_Farbe()
Dim i As Integer
Dim Summe1 As Double, Summe2 As Double, Summe3 As Double, Summe4 As Double
Dim Farbe1 As Integer, Farbe2 As Integer, Farbe3 As Integer, Farbe4 As Integer

Farbe1 = Cells(1, "D").Interior.ColorIndex
Farbe2 = Cells(1, "E").Interior.ColorIndex
Farbe3 = Cells(1, "F").Interior.ColorIndex
Farbe4 = Cells(1, "G").Interior.ColorIndex

'Makrolauf, von der Zeile 1, bis zur letzten belegten Zeile
For i = 1 To ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

'Summieren, wenn der Wert der Zelle eine Zahl ist.
If IsNumeric(Cells(i, 2)) Then
If Cells(i, 2).Interior.ColorIndex = Farbe1 Then Summe1 = Summe1 + Cells(i, 2)
If Cells(i, 2).Interior.ColorIndex = Farbe2 Then Summe2 = Summe2 + Cells(i, 2)
If Cells(i, 2).Interior.ColorIndex = Farbe3 Then Summe3 = Summe3 + Cells(i, 2)
If Cells(i, 2).Interior.ColorIndex = Farbe4 Then Summe4 = Summe4 + Cells(i, 2)
End If

Next

Cells(1, "D") = Summe1
Cells(1, "E") = Summe2
Cells(1, "F") = Summe3
Cells(1, "G") = Summe4
End Sub

Windows 10 Pro 64-Bit, 22H2: MS Office Pro Plus 2016 32-Bit: Mein Motto: Leben und leben lassen: Gruss Karl
bei Antwort benachrichtigen
Mick1 hddiesel „Hallo Mick, es geht auch ohne anpassen der Farbnummern. Hier einmal ein Beispiel, kannst du nach Wunsch anpassen. Sub ...“
Optionen

Hallo und Gruß an den Rhein-Neckar-Kreis und vielen Dank für die Rückantwort.

Ich muss aber leider sagen - verstehe das Ganze überhaupt nicht; ich hatte noch nie was mit Macro's oder so was in der Art gemacht und bin da überfordert oder nicht blickig genug. Nicht, dass ich aufgeben will, aber wie gesagt - ich kann mit deinen Ausführungen, die sicher auch funktionieren und tadellos sind, im ersten Moment nix anfangen. Ich weiß nun nicht, ob ich das zuviel zumute, aber ne Schritt für Schrittanleitung oder ne fertige Ausgabe als Anhang? Jedenfalls würde ich mich sehr freuen und zur Entschuldigung nochmals - ich bin da Laie, für Profi's sicher pille palle, na ja - learning by doing.

Danke auf jeden Fall

bei Antwort benachrichtigen
hddiesel Mick1 „Hallo und Gruß an den Rhein-Neckar-Kreis und vielen Dank für die Rückantwort. Ich muss aber leider sagen - verstehe das ...“
Optionen

Hallo Mick,

erstelle eine Beispieldatei:
A1 bis A13 mit Datum
B1 bis B13 mit Beträgen

Den Hintergrund der Zelle D1, färbst du mit der gleichen Hintergundfarbe Rot,
wie die Roten Zellen in den Beträgen B1 bis B13
Den Hintergrund der Zelle E1, färbst du mit der gleichen Hintergundfarbe Gelb,
wie die Roten Zellen in den Beträgen B1 bis B13
Den Hintergrund der Zelle F1, färbst du mit der gleichen Hintergundfarbe Grün,
wie die Roten Zellen in den Beträgen B1 bis B13
Den Hintergrund der Zelle G1, färbst du mit der gleichen Hintergundfarbe Braun,
wie die Roten Zellen in den Beträgen B1 bis B13
Den Hintergrund der Zelle H1, färbst du z.B. nicht,
dann wird in dieser Zelle alles ohne Hintergrundfarbe, der Zellen B1 bis B13 summiert.

Jetzt ein Rechts- Klick, Unten auf den Blattreiter der Tabelle, z.B. der Tabelle1
Code anzeigen wählen
 Jetzt kopierst du den Code, nach

Hier der geänderte Code:
 
in das freie Rechte Makrofenster.
Den Makroeditor wieder schließen und wieder in die Tabelle mit den Summen wechseln.
Das geänderte Makro startet, wenn du in den Bereich der Zellen D1 bis H1 klickst.
Färbst du eine Zelle um, dann klicke anschließend wieder in den Bereich der Zellen D1 bis H1 und die Summen werden neu berechnet.

Da du keine Excelversion nicht angegeben hast, hoffe ich dass der Code bei dir läuft.
 Wenn nicht, nochmals melden und deine Excelversion angeben.

Der Grün angezeigte Text im Makroeditor, nach dem kopieren, ist Bemerkung und wird vom Makro nicht beachtet.
Hier der geänderte Code:

'Datum in A1 bis A13
'Betrag in B1 bis B13
'Den Hintergrund der Betrag Zellen färben
'
'Summen Zellen im Beispiel, D1, E1, F1, G1 und H1(diese Zelle ohne Hintergrundfarbe, oder eine weitere Hintergrundfarbe)
'Den Hintergrund, der Ergebnis- Zellen, mit den gewünschten Farben färben
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
Dim Summe1 As Double, Summe2 As Double, Summe3 As Double, Summe4 As Double, Summe5 As Double
Dim Farbe1 As Integer, Farbe2 As Integer, Farbe3 As Integer, Farbe4 As Integer, Farbe5 As Integer

If Intersect(Target, Range("D1:H5")) Is Nothing Then Exit Sub

'Farbe1 bis Farbe5, die Zellhindergrundfarben der Zellen D1 bis H1 zuweisen.
Farbe1 = Cells(1, "D").Interior.ColorIndex
Farbe2 = Cells(1, "E").Interior.ColorIndex
Farbe3 = Cells(1, "F").Interior.ColorIndex
Farbe4 = Cells(1, "G").Interior.ColorIndex
Farbe5 = Cells(1, "H").Interior.ColorIndex

'Makrolauf, von der Zeile 1, bis zur letzten belegten Zeile
For i = 1 To ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

'Summieren, wenn der Wert der Zelle eine Zahl ist.
If IsNumeric(Cells(i, 2)) Then
If Cells(i, 2).Interior.ColorIndex = Farbe1 Then Summe1 = Summe1 + Cells(i, 2)
If Cells(i, 2).Interior.ColorIndex = Farbe2 Then Summe2 = Summe2 + Cells(i, 2)
If Cells(i, 2).Interior.ColorIndex = Farbe3 Then Summe3 = Summe3 + Cells(i, 2)
If Cells(i, 2).Interior.ColorIndex = Farbe4 Then Summe4 = Summe4 + Cells(i, 2)
If Cells(i, 2).Interior.ColorIndex = Farbe5 Then Summe5 = Summe5 + Cells(i, 2)
End If

Next

'Die Summen in die Zellen D1 bis H1 schreiben.
Cells(1, "D") = Summe1
Cells(1, "E") = Summe2
Cells(1, "F") = Summe3
Cells(1, "G") = Summe4
Cells(1, "H") = Summe5

End Sub

NACHTRAG: Hast du eine Excelversion, größer Excel 2002,
dann speicher die Datei statt mit der Datei- Endung .xlsx,
mit der Datei- Endung .xlsm ab,
sonst wird der Makrocode nicht gespeichert,
denn die Datei- Endung .xlsx, ist nur für Dateien ohne Makros vorgesehen.

Dann viel Spass beim testen.

Windows 10 Pro 64-Bit, 22H2: MS Office Pro Plus 2016 32-Bit: Mein Motto: Leben und leben lassen: Gruss Karl
bei Antwort benachrichtigen
mawe2 hddiesel „Hallo Mick, erstelle eine Beispieldatei: A1 bis A13 mit Datum B1 bis B13 mit Beträgen Den Hintergrund der Zelle D1, ...“
Optionen
Hast du eine Excelversion, größer Excel 2002, dann speicher die Datei statt mit der Datei- Endung .xlsx, mit der Datei- Endung .xlsm ab,

Größer Excel 2003 müsste es heißen. Erst ab 2007 gab's xlsx/xlsm.

bei Antwort benachrichtigen
hddiesel mawe2 „Größer Excel 2003 müsste es heißen. Erst ab 2007 gab s xlsx/xlsm.“
Optionen

Hallo Mawe2,

Danke für die Info, zu xlsx/xlsm!

Windows 10 Pro 64-Bit, 22H2: MS Office Pro Plus 2016 32-Bit: Mein Motto: Leben und leben lassen: Gruss Karl
bei Antwort benachrichtigen
Borlander mawe2 „Größer Excel 2003 müsste es heißen. Erst ab 2007 gab s xlsx/xlsm.“
Optionen

Zur Info:

Die von mir verursachte Nebendiskussion habe ich in einen eigenen Thread ausgelagert.

bei Antwort benachrichtigen
hddiesel Mick1 „Hallo und Gruß an den Rhein-Neckar-Kreis und vielen Dank für die Rückantwort. Ich muss aber leider sagen - verstehe das ...“
Optionen

Hallo Mick,

sollen Werte in ausgeblendeten Zeilen, nicht in der Summe berechnet werden,

oder deine Tabelle wird gefiltert und die nicht sichtbaren Zeilen,  sollen nicht in der Summe berechnet werden,

dann die folgende Makrozeile:
If IsNumeric(Cells(i, 2)) Then

durch diese ersetzen:
If IsNumeric(Cells(i, 2)) And Cells(i, 2).Rows.Hidden = False Then

Windows 10 Pro 64-Bit, 22H2: MS Office Pro Plus 2016 32-Bit: Mein Motto: Leben und leben lassen: Gruss Karl
bei Antwort benachrichtigen