Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 17:19:09
Formel mit Index-Funktion
24.04.2024 16:19:42
SummeWenn / SummeWenns mit Bedingung wenn kleiner als dann
24.04.2024 14:48:50
"Eingabefeld" zwischen zwei Zeilen geht nicht weg.
24.04.2024 14:40:10
Zeilenumbrüche in Textbox zählen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1316to1320
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1316to1320
1316to1320
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summewenns Funktion optimieren

Summewenns Funktion optimieren
18.06.2013 12:11:34
Dominic
Hi,
ich habe eine sehr umfangreiche Excel Tabelle mit unzähligen Summewenns Funktionen aufgebaut. Diese funktioniert soweit einwandfrei, allerdings würde ich diese gerne weiter optimieren bzw. von der Performance her deutlich verbessern.
Mitunter braucht meine Tabelle bei einer Datenaktualisierung gute 2-3 Minuten!
Mit Datenbank Funktionen die ich davor verwendet habe war es sogar noch langsamer.
Hat einer ne Idee wie ich die folgende Formel "performanceschonender" umgestalten kann?
=WENN(ODER($B56="";$B$5=1);"";WENN($B$5=2;SUMMEWENNS(Datenbasis!$M:$M;Datenbasis!$O:$O; BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE($A56););Datenbasis!$H:$H;$B$10; Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8);WENN($B$5=3;SUMMEWENNS(Datenbasis!$L:$L; Datenbasis!$O:$O;BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE($A56););Datenbasis!$H:$H; $B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8);WENN($B$5=4;SUMMEWENNS(Datenbasis!$R:$R; Datenbasis!$O:$O;BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE(A56););Datenbasis!$H:$H;$B$10; Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8);WENN($B$5=5;SUMMEWENNS(Datenbasis!$S:$S; Datenbasis!$O:$O;BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE($A56););Datenbasis!$H:$H; $B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8))))))

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summewenns Funktion optimieren
18.06.2013 12:27:46
Klaus
Hi,
du solltest
BEREICH.VERSCHIEBEN
tauschen gegen das deutlich schnellere
INDEX
Die Formel wird dadurch höchstwarscheinlich noch länger, aber performanter.
In die Detailanalyse deines Formelmonsters steige ich jetzt aber nicht ein ;-) etwas Lesestoff für dich:
Hier
http://www.online-excel.de/excel/singsel.php?f=180
erklärt Peter Haserodt, wie man ein BEREICH.VERSCHIEBEN durch Zuhilfenahme von INDEX():INDEX() ersetzen kann. Warum das hilft, steht unter anderem hier
http://www.online-excel.de/excel/singsel.php?f=171
beschrieben.
Grüße,
Klaus M.vdT.

Anzeige
AW: Summewenns Funktion optimieren
18.06.2013 13:11:57
Dominic
Hi Klaus,
ach so, "Bereich.Verschieben" bremst meine Tabelle so aus. Hmm, könntest du mir ggf. helfen mit der Anpassung meiner Formel?
Die folgende Formel steht in Zelle C56:
=WENN(ODER($B56="";$B$5=1);"";WENN($B$5=2;SUMMEWENNS(Datenbasis!$M:$M;Datenbasis!$O:$O;$Y3; Datenbasis!$H:$H;$B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8);WENN($B$5=3; SUMMEWENNS(Datenbasis!$L:$L;Datenbasis!$O:$O;Datensammlungen!$Y3;Datenbasis!$H:$H;$B$10; Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8);WENN($B$5=4;SUMMEWENNS(Datenbasis!$R:$R; Datenbasis!$O:$O;Datensammlungen!$Y3;Datenbasis!$H:$H;$B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F; $E$8);WENN($B$5=5;SUMMEWENNS(Datenbasis!$S:$S;Datenbasis!$O:$O;Datensammlungen!$Y3;Datenbasis!$H:$H; $B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8))))))
In C57, C58, C59, c60 und C61 stehen andere Formeln / Werte die Excel mir errechnen soll.
Nun möchte ich, die Zellen C56 bis C61 markieren und bis zum Tabellenende Zeile 2029 ziehen,
das funktioniert bei mir mit allen anderen Formeln die z. B. in C57, C58, C59, c60 und C61 stehen einwandfrei nur in Zelle C62 steht auf einmal die Formel:
=WENN(ODER($B62="";$B$5=1);"";WENN($B$5=2;SUMMEWENNS(Datenbasis!$M:$M;Datenbasis!$O:$O;$Y9; Datenbasis!$H:$H;$B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8);WENN($B$5=3; SUMMEWENNS(Datenbasis!$L:$L;Datenbasis!$O:$O;Datensammlungen!$Y9;Datenbasis!$H:$H;$B$10; Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8);WENN($B$5=4;SUMMEWENNS(Datenbasis!$R:$R; Datenbasis!$O:$O;Datensammlungen!$Y9;Datenbasis!$H:$H;$B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F; $E$8);WENN($B$5=5;SUMMEWENNS(Datenbasis!$S:$S;Datenbasis!$O:$O;Datensammlungen!$Y9;Datenbasis!$H:$H; $B$10;Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8))))))
Der Vergleich mit der Formel davor zeigt, dass hier statt Y4, leider Y9 aus dem Tabellenblatt Datensammlungen "benutzt" wird. Wie kann ich das entsprechend abändern mit deiner Index Funktion, dass er immer nur in 1er Schritten weiterzählt?
Vielen Dank im Voraus!

Anzeige
AW: Summewenns Funktion optimieren
18.06.2013 13:29:49
Klaus
Hi Dominic,
was ist denn mit der Verkürzung, die Rudi vorgeschlagen hat? Vielleicht löst die das Problem schon.
Falls nicht, währ ich über eine Musterdatei sehr dankbar. Ich kann die von dir angezeigte Formel auch nicht einfach kopieren, da mein Excel auf englisch läuft.
Grüße,
Klaus M.vdT.

AW: Summewenns Funktion optimieren
18.06.2013 14:12:44
Dominic
Hi Klaus,
okay vielen Dank dennoch, die Formel von Rudi funktioniert. Ich durchschau diese zwar momentan noch nicht, aber das wird hoffentlich noch kommen ;)
Vg

AW: Summewenns Funktion optimieren
18.06.2013 12:33:42
Rudi
Hallo,
die Bremse ist eher Bereich.Verschieben.
Benutze INDEX().
Außerdem geht's imho viel kürzer.
=WENN(ODER($B56="";$B$5=1);"";SUMMEWENNS(INDEX(Datenbasis!$A:$M;;WAHL($B$5;13;12;18;19)); Datenbasis!$O:$O; INDEX(Datensammlungen!$Y:$Y;ZEILE()-ZEILE(A53));Datenbasis!$H:$H;$B$10; Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8))
Gruß
Rudi

Anzeige
Korrektur
18.06.2013 13:37:15
Rudi
Hallo,
C56: =WENN(ODER($B56="";$B$5=1);"";SUMMEWENNS(INDEX(Datenbasis!$A:$S;;WAHL($B$5;;13;12;18;19)); Datenbasis!$O:$O; INDEX(Datensammlungen!$Y:$Y;ZEILE(A6)/6+2);Datenbasis!$H:$H;$B$10; Datenbasis!$G:$G;C$26;Datenbasis!$F:$F;$E$8))
Gruß
Rudi

AW: Korrektur
18.06.2013 14:11:34
Dominic
Hi Rudi,
wow, es funktioniert. Vielen Dank. Ich kann die Formel allerdings noch nicht ganz nachvollziehen - oder es ist grad einfach zu warm ;-) )
Könntest du mir diese vielleicht noch ein wenig erklären?
Mir ist gerade auch noch ein Fehler in einer meiner anderen Zellen aufgefallen die ebenfalls die "Bereich.Verschieben" Funktion beinhaltet. Könntest du mir hierbei ebenfalls einen alternativen Vorschlag auf Basis der Index Funktion durchgeben?
Zelle C57 beinhaltet diese Formel:
=WENN(UND($B56"";$B$10=2011);SUMMEWENNS(Datensammlungen!$F$2:$F$1614;Datensammlungen!$A$2:$A$1614; BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE($A57););Datensammlungen!$E$2:$E$1614;$E$8); WENN(UND($B56"";$B$10=2012);SUMMEWENNS(Datensammlungen!$G$2:$G$1614;Datensammlungen!$A$2:$A$1614; BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE($A57););Datensammlungen!$E$2:$E$1614;$E$8); WENN(UND($B56"";$B$10=2013);SUMMEWENNS(Datensammlungen!$H$2:$H$1614;Datensammlungen!$A$2:$A$1614; BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE($A57););Datensammlungen!$E$2:$E$1614;$E$8);"")) )*SUMMEWENNS(Datensammlungen!$W$3:$W$50;Datensammlungen!$V$3:$V$50;C$26;Datensammlungen!$U$3:$U$50; $B$10)
Ohne Bereich.Verschieben sollte diese so aussehen (übersichtlicher):
=WENN(UND($B56"";$B$10=2011);SUMMEWENNS(Datensammlungen!$F$2:$F$1614;Datensammlungen!$A$2:$A$1614; Datensammlungen!$Y3;Datensammlungen!$E$2:$E$1614;$E$8);WENN(UND($B56"";$B$10=2012); SUMMEWENNS(Datensammlungen!$G$2:$G$1614;Datensammlungen!$A$2:$A$1614;Datensammlungen!$Y3; Datensammlungen!$E$2:$E$1614;$E$8);WENN(UND($B56"";$B$10=2013); SUMMEWENNS(Datensammlungen!$H$2:$H$1614;Datensammlungen!$A$2:$A$1614;Datensammlungen!$Y3; Datensammlungen!$E$2:$E$1614;$E$8);"")))*SUMMEWENNS(Datensammlungen!$W$3:$W$50; Datensammlungen!$V$3:$V$50;C$26;Datensammlungen!$U$3:$U$50;$B$10)
Auch hier gilt das gleiche wie für die Formel in Zelle C56, heißt wenn ich die Zellen C56 bis C61 markiere und bis Zeile C2029 kopiere soll in
Zelle C63 folgende Formel auftauchen:
=WENN(UND($B56"";$B$10=2011);SUMMEWENNS(Datensammlungen!$F$2:$F$1614;Datensammlungen!$A$2:$A$1614; Datensammlungen!$Y4;Datensammlungen!$E$2:$E$1614;$E$8);WENN(UND($B56"";$B$10=2012); SUMMEWENNS(Datensammlungen!$G$2:$G$1614;Datensammlungen!$A$2:$A$1614;Datensammlungen!$Y4; Datensammlungen!$E$2:$E$1614;$E$8);WENN(UND($B56"";$B$10=2013); SUMMEWENNS(Datensammlungen!$H$2:$H$1614;Datensammlungen!$A$2:$A$1614;Datensammlungen!$Y4; Datensammlungen!$E$2:$E$1614;$E$8);"")))*SUMMEWENNS(Datensammlungen!$W$3:$W$50; Datensammlungen!$V$3:$V$50;C$26;Datensammlungen!$U$3:$U$50;$B$10)
Also beim Y3, wieder eine "Steigerung" auf Y4.
Wie bekomm ich das mit der Index Funktion hin? Da ich deine oben genannte Formel noch nicht nachvollziehen kann, weiß ich aktuell nicht wie ich diese Formel umändere.
Vielen Dank im Voraus!

Anzeige
AW: Korrektur
18.06.2013 14:32:56
Rudi
Hallo,
Ich kann die Formel allerdings noch nicht ganz nachvollziehen - oder es ist grad einfach zu warm ;-) )
hab ich vorhin was von nem Profi gelesen?
Das ist doch einfach. Und warm ist es hier auch.
Du kannst doch nicht einfach Bereich.verschieben durch nichts ersetzen.
Dafür und auch für die korrekte Ermittlung von Y ist INDEX(Datensammlungen!$Y:$Y;ZEILE(A6)/6+2) zuständig.
C57: =WENN($B56"";SUMMEWENNS(INDEX(Datensammlungen!$F$2:$H$1614;;$B$5-2010);Datensammlungen!$A$2:$A$1614; INDEX(Datensammlungen!$Y:$Y;ZEILE(A6)/6+2);Datensammlungen!$E$2:$E$1614;$E$8)*SUMMEWENNS(Datensammlungen!$W$3:$W$50; Datensammlungen!$V$3:$V$50;C$26;Datensammlungen!$U$3:$U$50;$B$10);"")
Gruß
Rudi

Anzeige
AW: Korrektur
18.06.2013 14:37:57
Dominic
Hi Rudi,
vielen Dank für deine Unterstützung.
Ich habe "Bereich.Verschieben" nicht durch nichts ersetzt, ich wollte dir nur die Formel bzw. die Bezüge einfacher darstellen. Dachte es wäre für dich einfacher "Y3" bzw. "Y4" zu sehen als "BEREICH.VERSCHIEBEN(Datensammlungen!$Y3;ZEILE()-ZEILE($A57);)"
Ich werde deine Formel gleich ausprobieren. :)
Vg

AW: Korrektur
18.06.2013 15:10:45
Dominic
Hi Rudi,
irgendwo ist da noch der Wurm drin im folgenden Bereich:
"$B$5-2010)"
Kannst du mir hier bitte nochmal helfen?

AW: Korrektur
18.06.2013 15:14:46
Rudi
Hallo,
muss auch $B$10 heißen.
Gruß
Rudi

AW: Korrektur
18.06.2013 15:55:13
Dominic
Ach Mensch, jetzt versteh ich deine Formel. Mir war nicht ganz klar, dass die Zahlen bei WAHL für die Spalten M, L, usw. stehen.
Das ist mir nun klar. Echt genial diese Methode.
Aber eines versteh ich nach wie vor nicht ganz, was hat es mit der "Zeile(A6)/6+2" auf sich?
Ich versteh da nicht den Zusammenhang mit der Spalte Y bzw. wie Excel den Bezug korrekt setzt / bzw. errechnet, dass in Zelle C56: Y3 steht, in Zelle C62: Y4 und in Zelle C68: Y5
Wo wird hier angegeben, dass Excel in der Spalte Y im Tabellenblatt "Datensammlungen" immer nur um den Wert 1 "steigen" soll?

Anzeige
AW: Korrektur
18.06.2013 16:21:03
Rudi
Hallo,
in 56: Zeile(A6)/6+2 = 6/6+2 = 3 Index(Y:Y;3)= Y3
in 62: Zeile(A12)/6+2 = 12/6+2 = 4 Index(Y:Y;4)= Y4
in 68: Zeile(A18)/6+2 = 18/6+2 = 5 Index(Y:Y;5)= Y5
etc.
Jetzt klar?
Gruß
Rudi

302 Forumthreads zu ähnlichen Themen

Zellen kopieren wenn Ziel nicht leer
Tom

Guten Morgen!
Ich suche mittels VBA folgende Möglichkeit
Suche Zeile für Zeile (Beginnend ab Zeile 4) die Spalte H ab ob etwas drinn steht.
Kopiere den Inhalt in die gleiche Zeile, aber in die Spalte P
Wichtig! Ist die ZIEL-Zelle bereits befüllt (es steht also etwas drin), dann...
18.08.2008 11:18:24
Thread-ID: 1002534
4 Beiträge
Anzeige
Summewenns
cfrei75

Hallo ich habe folgende Formel: =SUMMEWENNS(C4:CT4;C3:CT3;">6:00";C3:CT3;" Diese funktioniert einwandfrei, möchte aber anstatt die 6:00 und 21:00 händisch reinzuschreiben, dass diese von einem Feld übernommen wird, da diese variabel sind.
Wenn ich aber die Formel wie folgt schreibe =SUMM...
21.10.2008 10:57:16
Thread-ID: 1017894
5 Beiträge
Zählen nichtleerer Zellen
Stefan

Hallo zusammen,
ich erbitte erneut Eure Hilfe. Es geht darum Zellen mit Inhalt einer Liste der Fuhrparkverwaltung zu zählen. In Spalte C:J habe ich jeweils die verschiedenen Fahrzeuge. In den Zeilen 7:372 trage ich dann ein, wer welches Fahrzeug belegt hat. Geht eine Fahrt über mehrere Tage...
04.11.2008 08:20:00
Thread-ID: 1021461
3 Beiträge
Anzeige
wenn Spalte F nicht leer, ganze Zeile markieren
maxx_1985

Hallo mit'mander
Ich hab ein kleines VBA Probelem. Naemlich das fogende:
Wenn Zelle in Spalte F NICHT leer, dann die ganze Zeile markieren (einfach markieren, keine Farbe und nichts).
Bin gerade dabei mit dem Recorder was zu basteln (da ich noch kein vba kann) und bin an ganau diese...
19.11.2008 18:37:00
Thread-ID: 1026165
11 Beiträge
gibt immer den wert 0 aus..?
pat270881

hello,
ich habe aus einem anderen Posting folgende Codezeile gesehen:
Dim DBS As New ADODB.Recordset
Do Until ActiveCell.Value = ""
DBS.AddNew
DBS!GebDatum = ActiveCell.Value
DBS!GebOrt = ActiveCell.Offset(0, 1).Value
...
Was sagt dieses Rufzeichen "!" nach DBS g...
30.12.2008 23:17:17
Thread-ID: 1036066
6 Beiträge
summenprodukt mit 2 wenn's geht nicht?
Silvi


Moin zusammen,
muß eine ziemlich komplexe Abfrage machen und habe mir untenstehende ellenlange Formel ausgedacht, die leider nicht funktionert... :o( Hat von Euch jemand eine Idee, wie man das eleganter lösen könnte (ohne Makro?). Vielleicht ist nur eine Klammer falsch? Ich komm nicht...
23.01.2009 10:30:18
Thread-ID: 1042851
8 Beiträge
SUMMEWENN- Teilstring des Suchkriteriums
Joachim

Guten Morgen,
im Suchbereich befinden sich Texte. Ich brauch aber die Summe derer, die einem Teilstring des Textes entsprechen. Beispiel:
Suchbereich: Summenbereich
2005_SIM_EG 1520
2004_SIM_EG 2588
2003_SIM_EG 3444
2005_SIM_FW 1220
SUMMEWENN(Suchbe...
03.02.2009 08:16:09
Thread-ID: 1046502
8 Beiträge
Summewenn-nach Tages Datum addieren
Becker

Hallo zusammen,
Ich habe eine Tabelle in der ich nach Datum addieren möchte.
Ab A3 habe ich Datum der Benutzerdefieniert ist (Format; ###.#00".01.2009") bis A800
Ab E3 bis E800 sind Geldbeiträge in Euro (Format; Währung)
In Spalte K habe ich ein Kalender so aufgebaut;
-K3 ha...
10.02.2009 16:54:00
Thread-ID: 1049215
3 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
3
2
4
2
6
10
5
7
7
2
2
4
9
1
5
15
4
8
9
2
2
2
6
6
2