Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Summewenns mit Lücken im Kriterienbereich

Summewenns mit Lücken im Kriterienbereich
15.09.2016 15:10:34
Peter
Hallo zusammen,
ich möchte gerne bedingt summieren. Leider bekomme ich die Daten mit Lücken im Kriterienbereich. Ist es trotzdem möglich zu summieren? Anbei mein Beispiel; ich denke, daraus wird mein Problem deutlich.
https://www.herber.de/bbs/user/108198.xlsx
Danke für eure Hilfe!
Gruß,
Peter

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summewenns mit Lücken im Kriterienbereich
15.09.2016 15:38:46
Fennek
Hallo,
da es sehr einfach ist, die Lücken manuell zu schließen, macht es wenig Sinn einen relativ aufwändigen Makro zu schreiben, der das Auffüllen unsichtbar durchführt.
Markiere im Beispiel von B6:C18, drück die Taste F5, Inhalte, leere Zellen.
schreibe in die erste Markierung "=" und klicke mit der Maus eine Zelle höher, Schließ mit "strg-enter" ab.
mfg
AW: Summewenns mit Lücken im Kriterienbereich
15.09.2016 15:50:47
Peter
Hallo,
danke, das ist eine gute Möglichkeit.
Gibt es nicht trotzdem eine Formellösung?
Gruß,
Peter
AW: Summewenns mit Lücken im Kriterienbereich
15.09.2016 15:50:48
UweD
Hallo
mit dem folgenden Makro werden die Lücken geschlossen.
Sub Fehlende_Werte_in_Spalte_auffüllen()
    Dim RR, Z
    With ActiveSheet
        RR = .Cells.SpecialCells(xlCellTypeLastCell).Row 'Letzte Zeile des gesamten Blattes 
        For Each Z In .Range("A1:A" & RR).SpecialCells(xlCellTypeBlanks)
            If Z.Row > 1 Then
                Z.Value = Cells(Z.Row - 1, Z.Column)
            End If
        Next Z
        For Each Z In .Range("B1:B" & RR).SpecialCells(xlCellTypeBlanks)
            If Z.Row > 1 Then
                Z.Value = Cells(Z.Row - 1, Z.Column)
            End If
        Next Z
    End With
End Sub

Gruß UweD
Anzeige
AW: Summewenns mit Lücken im Kriterienbereich
15.09.2016 15:54:13
Peter
Hallo,
danke, das ist eine gute Möglichkeit. Läuft zwar noczh nicht ganz sauber, aber das könnte man ja noch anpassen.
Gibt es nicht trotzdem eine Formellösung?
Gruß,
Peter
AW: Summewenns mit Lücken im Kriterienbereich
15.09.2016 16:01:06
Daniel
Hi
naja, für eine Einzelspalte (summe für "a" in Spalte A) könne man so eine Formel schreiben:
=Summe(Index(D:D;Vergleich(B:B;"a";0):Index(D:D;Vergleich(B:B;"b";0)-1))
dh du brauchst für die summenbildung nicht nur die Startzeile von "a", sondern auch die Startzeile von "b", damit du weißt wie weit die Summe geht.
wenn du noch die zweite Kriterienspalte einbauen willst, wird's sehr kompliziert.
bis du die Formel geschrieben hat, hast du die Werte 3x von Hand in den Zellen ergänzt.
gruß Daniel
Anzeige
AW: Summewenns mit Lücken im Kriterienbereich
15.09.2016 16:04:35
Peter
Hallo Daniel,
danke für den Tipp. Das ist ein gute Ansatz, den werde ich mal weiter "ausbauen".
Viele Grüße,
Peter
Damit wird Peter wohl wenig Freude haben, ...
15.09.2016 16:19:19
Luc:-?
…Daniel;
1. eine Klammer fehlt,
2. in VERGLEICH sind Arg1 und 2 vertauscht!
Gruß, Luc :-?
AW: Damit wird Peter wohl wenig Freude haben, ...
15.09.2016 16:25:51
Daniel
naja, ich denke mal das Peter klug genug ist, das Prinzip zu verstehen und die Formel dann selber schreibt und nicht nur guttenbergt.
ausserdem wird er spätesten beim hinzufügend er zweiten Bedingung erkennen, dass das Auffüllen der Tabelle mit den Daten die bessere Lösung ist.
Gruß Daniel
D.Letztere ist unbestritten, d.Andere - naja…! :-]
15.09.2016 17:23:11
Luc:-?
Luc :-?
ersteres kannst du an seiner Reaktion erkennen.
15.09.2016 18:17:19
Daniel
Gruß Daniel
Die sieht aber sehr nach SelbstVersuch aus! ;-]
15.09.2016 23:51:28
Luc:-?
Werde ich ihm mal abnehmen! Ist aber absolut unökonomisch und passt nur aufs Bsp!
Luc :-?
Anzeige
AW: Die sieht aber sehr nach SelbstVersuch aus! ;-]
16.09.2016 00:16:38
Daniel
auf was soll es denn sonst passen, wenn nicht auf das Beispiel?
Gruß Daniel
Na, auf andere C-Struktur zB! Die wäre der ...
16.09.2016 00:50:04
Luc:-?
…Lückenfüll-Fml nämlich egal, komplexen Fmln aber nicht unbedingt. Solltest du doch wissen, Daniel!
Ansonsten kannst du dich ja gern durch mein Monster beißen… :-]
Luc :-?
AW: Summewenns mit Lücken im Kriterienbereich
15.09.2016 15:54:40
Daniel
HI
das Makro kann man auch noch kompakter schreiben:
with Range("A:B")
.SpecialCells(xlcelltypeblanks).FormulaR1C1 = "=R[-1]C"
.Formula = .Value
End with
Gruß Daniel
Das ist auch mit Fml nicht schwer, aber dann ...
15.09.2016 16:13:29
Luc:-?
…doch auch irgendwie manuell, Peter; ;-)
wenn's um den Neuaufbau aller Daten geht, so wie es in deinem Bsp den Anschein hat, ist deine 1.Frage zu verneinen und die 2. kann mit folgd Fml für den GesamtBereich F6:H18 beantwortet wdn:
F6[:H18]:=WENN(B6="";F5;B6)
Fml in F6 eintragen und dann nach rechts und unten ziehen!
Man könnte die Ergänzung aber auch im OriginalBereich durchführen. Das wäre mit Fml aber etwas komplizierter und erforderte auch manuelle (bzw pgmmierte) Ersetzungen.
Gruß, Luc :-?
Besser informiert mit …
Anzeige
Aber falls du diesen kleinen Aufwand scheuen ...
15.09.2016 23:59:04
Luc:-?
…solltest, Peter,
wird's ausgesprochen heftig, wenn du VBA in jedweder Form vermeiden wolltest. Eine mögliche Fml, die aber (besonders bzgl Spalte C) nur auf die gezeigte Struktur passt, wäre folgende 1231 Zeichen umfas­sende singulare (1zellige) MatrixFml:
K6[:K9]: {=SUMME(WENN(J6=RECHTS(INDEX(WAHL(SPALTE(A$1:C$1);WECHSELN(1^ZEILE(B$6:B$18);1;"a");WENN(ZEILE(B$6:B$18)-5<VERGLEICH("b";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"b"));WENN(ZEILE(B$6:B$18)-5<VERGLEICH("c";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"c")));;1)&INDEX(WAHL(SPALTE(A$1:C$1);WECHSELN(1^ZEILE(B$6:B$18);1;"a");WENN(ZEILE(B$6:B$18)-5<VERGLEICH("b";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"b"));WENN(ZEILE(B$6:B$18)-5<VERGLEICH("c";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"c")));;2)&INDEX(WAHL(SPALTE(A$1:C$1);WECHSELN(1^ZEILE(B$6:B$18);1;"a");WENN(ZEILE(B$6:B$18)-5<VERGLEICH("b";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"b"));WENN(ZEILE(B$6:B$18)-5<VERGLEICH("c";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"c")));;3))&RECHTS(INDEX(WAHL(SPALTE(A$1:B$1);WECHSELN(1^ZEILE(C$6:C$18);1;"1");WENN(ZEILE(C$6:C$18)-5<VERGLEICH(2;C$6:C$18;0);"";WENN(ZEILE(B$6:B$18)-5<VERGLEICH("b";B$6:B$18;0);WECHSELN(1^ZEILE(C$6:C$18);1;"2");WECHSELN(1^ZEILE(C$6:C$18);1;"1"))));;1)&INDEX(WAHL(SPALTE(A$1:B$1);WECHSELN(1^ZEILE(C$6:C$18);1;"1");WENN(ZEILE(C$6:C$18)-5<VERGLEICH(2;C$6:C$18;0);"";WENN(ZEILE(B$6:B$18)-5<VERGLEICH("b";B$6:B$18;0);WECHSELN(1^ZEILE(C$6:C$18);1;"2");WECHSELN(1^ZEILE(C$6:C$18);1;"1"))));;2));D$6:D$18))}
Viel Spaß! Morrn, Luc :-?
Anzeige
Aber es geht auch etwas kürzer mit 448 Zeichen:
16.09.2016 03:05:50
Luc:-?
K6[:K9]: {=SUMME((J6=RECHTS(WECHSELN(1^ZEILE(B$6:B$18);1;"a")&WENN(ZEILE(B$6:B$18)-5<VERGLEICH("b";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"b"))&WENN(ZEILE(B$6:B$18)-5<VERGLEICH("c";B$6:B$18;0);"";WECHSELN(1^ZEILE(B$6:B$18);1;"c")))&RECHTS(WECHSELN(1^ZEILE(C$6:C$18);1;"1")&WENN(ZEILE(C$6:C$18)-5<VERGLEICH(2;C$6:C$18;0);"";WENN(ZEILE(B$6:B$18)-5<VERGLEICH("b";B$6:B$18;0);WECHSELN(1^ZEILE(C$6:C$18);1;"2");WECHSELN(1^ZEILE(C$6:C$18);1;"1")))))*D$6:D$18)}
Diese Fml ist aber ebenfalls auf die BspStruktur von Spalte C ausgerichtet. StrukturÄnderungen würden die Fml höchstwahrscheinlich noch mehr komplizieren.
Luc :-?
Anzeige
AW: Summewenns mit Lücken im Kriterienbereich
16.09.2016 10:19:41
Daniel
Hi
mit Originären Exclformlen wird es aufwendig das zu lösen, wenn du die Lückenzellen nicht auffüllen willst.
Das Auffüllen der Lücken geht einfach und hätte viele Vorteile, die Liste wird dadurch:
- sortierbar
- filterbar
- per Formel oder Pivot-Tabelle auswertbar.
Wenn du die Optik erhalten willst, kannst du ja nach dem einfügen der Formel die schriftfarbe der Zellen mit formel gleich der Hintergrundfarbe setzen.
wenn du das partout nicht tun willst, ist die wahrscheinlich einfachste Lösung eine kleine selbstgeschribene Funktion zur Auswertung.
Schau dir mal das Beispiel an. Die Funktion wird parametrisiert wie SummeWenns, dh als erster Parameter die Summenspalte, danach immer Abwechselnd die Bedingungsspalte und die Bedingung.
der Code ist erstmal einfach und kurz gehalten, bspw ist nur der einfache Vergleich mit "=" implementiert.
https://www.herber.de/bbs/user/108207.xlsm
Gruß Daniel
Anzeige
Ja, die UDF scheint hier flexibel genug zu sein, …
16.09.2016 13:00:24
Luc:-?
…Daniel;
ich hatte inzwischen an eine StrukturmusterVerarbeitung gedacht, aber jetzt nicht wirklich Zeit für so etwas. Meine regulären MonsterFmln waren eher als Abschreckung gedacht… ;-]
Gruß, Luc :-?
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen