Summe in Abhängigkeit von leeren Feldern.

Bild

Betrifft: Summe in Abhängigkeit von leeren Feldern.
von: Andi
Geschrieben am: 28.09.2015 14:09:35

Hallo Community,
ich habe folgendes Problem:
Ich habe eine Tabelle deren Datensätze aus jeweils 4 Zeilen bestehen, die sich immer wieder widerholen. Ich möchte nun die Summe folgendermaßen bilden: Wenn A4 leer dann A3, wenn A3 leer dann A2. Das gleich gilt dann auch für Wenn A6 leer dann A5, Wenn A5 leer dann A4.
Da ich eine Vielzahl von Werten mit diesem Aufbau habe, kann ich es nicht mit einer einfach wenn dann Funktion lösen.
Ein vereinfachtes Beispiel ist im Anhang.
Schon mal vielen Dank für eure Hilfe.
https://www.herber.de/bbs/user/100442.xlsx

Bild

Betrifft: AW: Summe in Abhängigkeit von leeren Feldern.
von: Matthias L
Geschrieben am: 28.09.2015 15:10:47
Hallo
Warum kannst Du das bei je 4 Zeilen nicht mit Wenn-Dann lösen?
In Deinem Bsp. sind immer 3 Zeilen! Nur soviel zur Genauigkeit der Angaben.
Einfach Hilfsspalten benutzen.

 ABCDEFG
1Wert31212  
2Werta1,53  Summe 114,5
3Wertb 6  Summe 216
4Wertc  1,56  
5Werta24    
6Wertb3     
7Wertc 535  
8Wert…55    
9Wert…      
10Wert…10 105  

Formeln der Tabelle
ZelleFormel
G2=SUMME(D2:D10)
G3=SUMME(E2:E10)
D4=WENN(B4<>"";B4;WENN(B3<>"";B3;B2))
E4=WENN(C4<>"";C4;WENN(C3<>"";C3;C2))
D7=WENN(B7<>"";B7;WENN(B6<>"";B6;B5))
E7=WENN(C7<>"";C7;WENN(C6<>"";C6;C5))
D10=WENN(B10<>"";B10;WENN(B9<>"";B9;B8))
E10=WENN(C10<>"";C10;WENN(C9<>"";C9;C8))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Matthias

Bild

Betrifft: AW: Summe in Abhängigkeit von leeren Feldern.
von: fcs
Geschrieben am: 28.09.2015 15:15:53
Hallo Andi,
ein Makro oder auch eine entsprechende benutzdefinierte Function sehen etwa wie folgt aus.
Gruß
Franz

'Code in einem allgemeinen Modul
Sub SummeSpezial()
  Dim dblSumme() As Double
  Dim Zeile As Long, Zeile2 As Long
  Dim Spalte As Long
  
  Const Spa1 = 2 '1. Spalte, die summiert werden soll
  Const spa2 = 6 'letzte Spalte, die summiert werden soll
  Const AnzZeilenDS = 3  'anzahl Zeilen pro Datensatz
  
  ReDim dblSumme(Spa1 To spa2)
  With ActiveSheet
  For Zeile = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row Step AnzZeilenDS
    For Spalte = Spa1 To spa2
    For Zeile2 = Zeile + AnzZeilenDS - 1 To Zeile Step -1
      If Not IsEmpty(.Cells(Zeile2, Spalte)) Then
        dblSumme(Spalte) = dblSumme(Spalte) + .Cells(Zeile2, Spalte)
        Exit For
      End If
    Next Zeile2
    Next Spalte
  Next
  
  'Summenwerte ausgeben
  Zeile = 2
  For Spalte = Spa1 To spa2
    .Cells(Zeile, 9) = "Summe " & .Cells(1, Spalte).Text  'Spalte I
    .Cells(Zeile, 10) = dblSumme(Spalte)                  'Spalte J
    Zeile = Zeile + 1
  Next Spalte
  End With
End Sub
Function fncSummeSpezial(WerteBereich As Range, Optional AnzZeilenDS As Long = 3)
  ' WerteBereich = Zellbereich in einer Spalte mit den Werten
  ' AnzZeilenDS  = Anzzahl Zeilen pro Datensatz
  Dim dblSumme As Double
  Dim Zeile As Long, Zeile2 As Long
  
  With WerteBereich
    For Zeile = 1 To WerteBereich.Rows.Count Step AnzZeilenDS
      For Zeile2 = Zeile + AnzZeilenDS - 1 To Zeile Step -1
        If Not IsEmpty(.Cells(Zeile2, 1)) Then
          dblSumme = dblSumme + .Cells(Zeile2, 1)
          Exit For
        End If
      Next Zeile2
    Next
  End With
  'Summenwerte ausgeben
  fncSummeSpezial = dblSumme
End Function


Bild

Betrifft: AW: wenn keine 0-Werte zu berücksichtigen sind ...
von: ... neopa C
Geschrieben am: 28.09.2015 17:27:06
Hallo Andi,
... dann würde auch eine Formel reichen.
In B1: =SUMMENPRODUKT(B2:B97*((REST(ZEILE(B2:B97);3)=1)+(REST(ZEILE(B2:B97);3)=0)*(B3:B98*(REST(ZEILE(B2:B97);3)=1)=0)+(REST(ZEILE(B2:B97);3)=2)*(B3:B98*(REST(ZEILE(B3:B98);3)=0)=0)*(B4:B99*(REST(ZEILE(B4:B99);3)=1)=0)))
und Formel nach rechts kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: Abgesehen mal davon, dass im Blatt die Summen ...
von: Luc:-?
Geschrieben am: 28.09.2015 22:56:39
…untereinander stehen, Werner,
hättest du viell noch anmerken sollen, dass deine Fml zwingend 1 leeren (ZellBereiche 3 Zeilen weiter fassen!) nach dem letzten 3er-Block erfordert, sonst könnten Werte fehlen. Ansonsten fktt sie gut. ;-)
Gruß, Luc :-?

Bild

Betrifft: AW: Ergebnisse oberhalb der Spaltenwerte ...
von: ... neopa C
Geschrieben am: 29.09.2015 08:28:04
Hallo Luc,
... lassen sich eben so leichter ermitteln, weil sie da "natürlicher" zugeordnet sind.
Wenn die Ergebnisse wirklich in einem anderem Bereich und untereinander angeordnet werden sollen, muss die Formel noch angepasst werden. Dann sollte sich Andi noch mal melden. Auch hierfür findet sich sicherlich eine Lösung.
Der Hinweis auf notwendige Leerzeilen (zwei nicht drei) nach dem letzten Block ist richtig, hab ich allerdings als normal angesehen und ist im Beispiel auch gewährleistet.
Gruß Werner
.. , - ...

Bild

Betrifft: Ja, ich hielt das auch nur für eine eher ...
von: Luc:-?
Geschrieben am: 29.09.2015 16:17:33
…zufällige Positionierung, Werner,
denn eigentlich sollten „Spalten“-Summen ja auch optisch ihren Spalten zugeordnet sein. Das umzukehren, bedeutet Zusatzaufwand mit deutlicher Fml-Verlängerung.
2 Zusatzzeilen ist richtig, aber da ein Block 3 hat, schien mir diese Angabe sinnvoller, falls danach noch eine andere Tabelle folgen soll.
Hatte das auch versucht, auf der Basis von VERWEIS, der immer den letzten nicht-leeren Wert findet (wichtig, falls 0 auch ein zuläs­si­ger Wert wäre). Leider ließ sich eine (hilfs­zellen­freie) Summe dann entweder nur durch separate Angabe aller Blöcke oder unter Zuhilfe­nahme meiner Auswertungs-UDFs realisieren. Letztere Fml ist nach bisherigem Stand auch nicht kürzer oder eleganter als deine. Ich habe nun auch nicht alle meine UDFs so gut im Kopf, dass mir spontan eine bessere Variante einfallen würde. Auch fürchte ich, dass die dann auf solche UDFs hinaus­liefe, die entweder nur ich oder höchstens noch 2(!) weitere AWer besitzen/kennen, weshalb ich mich (noch) nicht weiter damit befasst habe.
Aber ich hatte von dir eigentlich eine AGGREGAT-Lösung erwartet… ;-)
Gruß, Luc :-?

Bild

Betrifft: AW: warten wir mal ab ...
von: ... neopa C
Geschrieben am: 29.09.2015 19:52:27
Hallo Luc,
... was Andi so meint.
Gruß Werner
.. , - ...

Bild

Betrifft: Da können wir wohl lange abwarten... :-[ owT
von: Luc:-?
Geschrieben am: 03.10.2015 16:13:01
:-?

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Summe in Abhängigkeit von leeren Feldern."