Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1160to1164
1160to1164
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summation bis zur nächsten Leerzeile

Forumthread: Summation bis zur nächsten Leerzeile

Summation bis zur nächsten Leerzeile
Miguel
Hallo,
ich brauche dringend Hilfe für folgenden Sachverhalt, siehe auch Excelanhang.
https://www.herber.de/bbs/user/70238.xls
Lösung lautet:
Gerät1 = 3 ST
Gerät2 = 8 ST
Quelldaten:
Gerät 1
8 22.06.2010 1 ST
8 21.06.2010 2 ST
Gerät 2
8 22.06.2010 1 ST
8 21.06.2010 7 ST
Da jeden Tag pro Gerät eine neue Zeile generiert wird benötige ich eine dynamische Formel, die mir folgendes wiedergibt:
wenn Gerät1=Gerät1 dann gehe in Spalte C und summiere solange nach unten bis eine Leerzeile auftaucht.
Gerät2=Gerät2 dann springe in Spalte C und summiere solange nach unten bis eine Leerzeile auftaucht.
etc.
Anzeige
Summe nur mal bis zur Leerzelle
23.06.2010 13:03:12
WF
Hi,
wie Du das weiter generieren/automatisieren willst, ist mir nicht ganz klar.
Folgenden Arrayformel:
{=SUMME(C2:INDIREKT("C"&MIN(WENN(C2:C99="";ZEILE(2:99)))-1))}
Salut WF
AW: Summation bis zur nächsten Leerzeile
23.06.2010 13:22:12
fcs
Hallo Miguel,
der einfachste Weg wäre, per Makro das Gerät jeweils in der Spalte E einzutragen. Dann kann man mit der Funktion SUMMEWENN die Gesamtstückzahlen ermitteln. Gerät 1 8 22.06.2010 1 ST Gerät 1 Gerät 1 3 8 21.06.2010 2 ST Gerät 1 Gerät 2 8 Gerät 3 2 Gerät 2 8 21.06.2010 1 ST Gerät 2 Zelle Formula-Local 8 20.06.2010 7 ST Gerät 2 $H$2 =SUMMEWENN(E:E;G2;C:C) $H$3 =SUMMEWENN(E:E;G3;C:C) Gerät 3 $H$4 =SUMMEWENN(E:E;G4;C:C) 8 21.06.2010 1 ST Gerät 3 8 20.06.2010 1 ST Gerät 3
Gruß
Franz 
Sub Geraet_Spalte_E()
'Gerät in Spalte A jeweils in Spalte E eintragen
Dim wks As Worksheet, sGeraet As String, Zeile As Long
Set wks = ActiveSheet
With wks
Application.ScreenUpdating = False
.Columns(5).ClearContents
Zeile = 1 'Zeile mit 1. gerät
sGeraet = .Cells(Zeile, 1)
For Zeile = Zeile + 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
If .Cells(Zeile, 1)  "" Then
.Cells(Zeile, 5).Value = sGeraet
Else
Zeile = Zeile + 1
sGeraet = .Cells(Zeile, 1)
End If
Next
Application.ScreenUpdating = True
End With
End Sub

Anzeige
Summe(Fundstelle:nächste Leerzeile)
23.06.2010 13:23:33
Erich
Hi Miguel,
probier mal (von B14 nach unten kopieren):
 AB
14Gerät 13
15Gerät 28
16Gerät 32

Formeln der Tabelle
ZelleFormel
B14{=SUMME(BEREICH.VERSCHIEBEN(C$1;VERGLEICH(A14;A$1:A$12;0); ; MIN(WENN(BEREICH.VERSCHIEBEN(C$1;VERGLEICH(A14;A$1:A$12;0); ;9999)=""; ZEILE(BEREICH.VERSCHIEBEN(C$1;VERGLEICH(A14;A$1:A$12;0); ;9999)))) -VERGLEICH(A14;A$1:A$12;0)))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Vorschlag mit Hilfsspalte
23.06.2010 13:28:15
Erich
Hi,
so gehts etwas kürzer, mit Hilfszellen C14:C16:
 ABC
14Gerät 131
15Gerät 285
16Gerät 329

Formeln der Tabelle
ZelleFormel
B14{=SUMME(BEREICH.VERSCHIEBEN(C$1;C14;; MIN(WENN(BEREICH.VERSCHIEBEN(C$1;C14;;9999)=""; ZEILE(BEREICH.VERSCHIEBEN(C$1;C14;;9999)))) -C14))}
C14=VERGLEICH(A14;A$1:A$12;0)
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Hilfsspalte und SUMMEWENN - ohne VBA
23.06.2010 13:42:13
Erich
Hi,
noch eine Variante:
 DEF
1 Gerät 1Gerät 1
2STGerät 1Gerät 1
3STGerät 1Gerät 1
4   
5 Gerät 2Gerät 2
6STGerät 2Gerät 2
7STGerät 2Gerät 2
8   
9 Gerät 3Gerät 3
10STGerät 3Gerät 3
11STGerät 3Gerät 3
12   
13   
143  
158  
162  

Formeln der Tabelle
ZelleFormel
E1=A1
F1=A1
E2=WENN((1-ISTTEXT(A2))*(C2>0); E1;A2&"")
F2=WENN(ISTTEXT(A2)+ISTLEER(C2); A2&"";E1)
D14=SUMMEWENN($E$1:$E$13;A14;$C$1:$C$13)

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Hilfsspalte und SUMMEWENN - ohne VBA
23.06.2010 15:42:42
Miguel
Hallo,
vielen vielen Dank für die zahlreiche Rückantwort. Eine super Lösung war das Makro von fcs. So bekomme ich die Hilfsspalte fast automatisiert. Per Summenformel habe ich dann meine Zusammenfassung, die ich benötige.
Super noch mal vielen Dank.
Michael
und ohne BEREICH.VERSCHIEBEN() und ohne{} ...
23.06.2010 16:37:38
neopa
Hallo Erich,
... alternativ so (Formel nach unten kopieren):
 ABCDEF
1Gerät 1     
2822.06.20101STGerät 13
3821.06.20102STGerät 28
4    Gerät 32
5Gerät 2     
6821.06.20101ST  
7820.06.20107ST  
8      
9Gerät 3     
10821.06.20101ST  
11820.06.20101ST  
12      

Formeln der Tabelle
ZelleFormel
F2=WENN(E2="";"";SUMME(INDEX(C:C;VERGLEICH(E2;A:A;)):WENN(E3>"";INDEX(C:C;VERGLEICH(E3;A:A;)-1); INDEX(C:C;VERWEIS(9^9;C:C;ZEILE(A:A))))))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
schöner mit INDEX
23.06.2010 17:02:09
Erich
Hi Werner,
das mit dem INDEX habe ich nach wie vor nicht richtig drauf, BEREICH.VERSCHIEBEN habe ich eher verinnerlicht.
Das sieht man an an meinen Formeln. Vielleicht schaff ich es ja auch noch...
Deine Formel ist vieeeel schöner und klarer.
Sie setzt natürlich voraus, dass die Ergebnisse nicht unterhalb der Daten in den Spalten A:C stehen.
Aber auch das ist - wie auch bei den SUMMEWENN-Lösungen, siehe bei Franz - ohnehin viel geschickter.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
oT, "Blumen" für den Monatsersten ...
24.06.2010 08:59:03
neopa
Hallo Erich,
der Fragesteller im dortigen Thread hatte in die gleiche Fragestellung gestern etwa zur gleichen Zeit in Office-Lösung gestellt. Ich hab ihm dort offensichtlich (hab keine weitere Threadbenachrichtigung erhalten) als einziger geantwortet. Ich hatte dabei die Annahme getroffen, dass es sich bei "Jan.10" etc. evtl. um nicht echte Datumswerte handeln könnte und so eine Lösungsformel mit SUMMENPRODUKT() vorgeschlagen, wo jeder zweite Wert summiert wird, bis zu der Spalte deren Spaltenzahlwert kleiner MONAT(HEUTE())*2 ist.
Unter der sich nachträglich als richtig erwiesen Annahme, dass echte Excel-Datumswerte in des Fragestellers Tabellenüberschriften sind, ist Rainers Lösungsformel natürlich naheliegender naheliegend und auch gut. Deine Formel hat dazu das gewisse "Etwas" und ist durch Deine zusätzliche Erklärung "rund" und damit auch schön. Wobei in meinen Augen der Begriff "Schönheit" immer auch stark subjektiv belegt ist,
In diesem Zusammenhang will ich mich natürlich für Deine "Blumen" bedanken. Ich bin mir allerdings ziemlich sicher, dass ich nie so intensiv nach Alternativen für die Funktion OFFSET() gesucht hätte, wenn es nicht die Eindeutschung des Funktionnamens OFFSET() gegeben hätte. Und so bin ich eben seit einigen Monaten bekennender "INDEX()-Fan.
Gruß Werner
.. , - ...
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Summation bis zur nächsten Leerzeile in Excel

Schritt-für-Schritt-Anleitung

  1. Vorbereitung der Daten: Stelle sicher, dass deine Daten in einer Excel-Tabelle strukturiert sind, wobei die Geräte in einer Spalte stehen und die Werte in einer angrenzenden Spalte.

  2. Formel verwenden: Um die Summe bis zur nächsten leeren Zelle zu berechnen, kannst du die folgende Array-Formel verwenden:

    {=SUMME(C2:INDIREKT("C"&MIN(WENN(C2:C99="";ZEILE(2:99)))-1))}

    Diese Formel summiert die Werte in Spalte C, solange bis eine leere Zelle erreicht wird.

  3. Eingabe der Formel: Gib die Formel in die Zelle ein und schließe sie mit STRG+SHIFT+ENTER ab, um sicherzustellen, dass sie als Matrixformel eingegeben wird.

  4. Anpassung der Bereiche: Ändere die Zellreferenzen in der Formel entsprechend deiner Datenstruktur.

Häufige Fehler und Lösungen

  • Fehler: #WERT!: Dieser Fehler tritt auf, wenn die Formel nicht als Matrixformel eingegeben wurde. Stelle sicher, dass du STRG+SHIFT+ENTER verwendest.

  • Leere Zellen nicht berücksichtigt: Falls leere Zellen in der Summe auftauchen, überprüfe, ob die Daten korrekt formatiert sind und keine unsichtbaren Zeichen enthalten.

Alternative Methoden

  • SUMMEWENN-Funktion: Wenn du eine Hilfsspalte verwendest, kannst du die Funktion SUMMEWENN einsetzen, um die Werte für jedes Gerät zu summieren:

    =SUMMEWENN(E:E;G2;C:C)

    Hierbei wird in Spalte E nach dem Gerät gefiltert, und die entsprechenden Werte in Spalte C summiert.

  • VBA-Makros: Eine weitere Möglichkeit ist die Verwendung eines VBA-Makros zur automatischen Befüllung der Hilfsspalte:

    Sub Geraet_Spalte_E()
   Dim wks As Worksheet, sGeraet As String, Zeile As Long
   Set wks = ActiveSheet
   Application.ScreenUpdating = False
   .Columns(5).ClearContents
   Zeile = 1
   sGeraet = .Cells(Zeile, 1)
   For Zeile = Zeile + 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
       If .Cells(Zeile, 1) <> "" Then
           .Cells(Zeile, 5).Value = sGeraet
       Else
           Zeile = Zeile + 1
           sGeraet = .Cells(Zeile, 1)
       End If
   Next
   Application.ScreenUpdating = True
End Sub

Praktische Beispiele

  • Beispiel 1: Angenommen, du hast die Geräte in Spalte A und die zu summierenden Werte in Spalte C. Die Formel in Zelle B14 könnte wie folgt aussehen:

    {=SUMME(BEREICH.VERSCHIEBEN(C$1;VERGLEICH(A14;A$1:A$12;0);; MIN(WENN(BEREICH.VERSCHIEBEN(C$1;VERGLEICH(A14;A$1:A$12;0);9999)=""; ZEILE(BEREICH.VERSCHIEBEN(C$1;VERGLEICH(A14;A$1:A$12;0);9999))) -VERGLEICH(A14;A$1:A$12;0)))}

  • Beispiel 2: Bei der Verwendung von SUMMEWENN kannst du diese Formel für die Geräte verwenden:

    =SUMMEWENN(A:A; "Gerät 1"; C:C)

Tipps für Profis

  • Benutze die Excel-Tabelle: Wenn du Daten in einer Excel-Tabelle formatierst, kannst du strukturierte Verweise verwenden, die die Lesbarkeit und Wartbarkeit deiner Formeln verbessern.

  • Vermeide komplexe Formeln: Halte die Formeln so einfach wie möglich. Verwende Hilfsspalten, um die Berechnungen zu vereinfachen und die Fehleranfälligkeit zu reduzieren.

  • Testen und Validieren: Überprüfe die Ergebnisse deiner Formeln regelmäßig, insbesondere nach Änderungen an den Quelldaten.

FAQ: Häufige Fragen

1. Wie summiere ich in Excel bis zur nächsten leeren Zelle?
Du kannst die Array-Formel =SUMME(C2:INDIREKT("C"&MIN(WENN(C2:C99="";ZEILE(2:99)))-1)) verwenden, um die Werte bis zur nächsten Leerzeile zu summieren.

2. Kann ich auch ohne VBA arbeiten?
Ja, du kannst die Funktion SUMMEWENN oder Array-Formeln verwenden, um die Summation zu realisieren, ohne auf VBA zurückgreifen zu müssen.

3. Was ist der Unterschied zwischen SUMMEWENN und SUMME?
SUMME addiert einfach alle angegebenen Werte, während SUMMEWENN nur die Werte summiert, die bestimmten Kriterien entsprechen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige