Microsoft Excel

Herbers Excel/VBA-Archiv

Formel verkürzen --> zu lange Speicherzeit

Betrifft: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 13:10:19

Hallo

Meine Formel funktioniert gut, aber da die Formel sehr lang ist und ich sie in viele Zeilen kopiert habe, dauert das Speichern einer Tabelle 15 Minuten! Ich will aber 30 solche Tabellen anfertigen, das heisst ich würde dann immer mehrere Stunden warten bis das Ding gesichert ist! Das kann nicht sein.

Deshalb, kann mir jemand helfen, wie ich diese Formel in Zelle I15 verkürzen kann? Vielleicht die L$L durch einen Range L:L ersetzen, so dass ich dann nicht jede der 11 Zellen einzeln in Spalte L ansteuern muss? Das würde die Formel erheblich verkürzen, nur weiss ich nicht wie das umsetzen. Besten Dank für jeden Tipp!

=WENN(L$1="";0;SUMMEWENN(INDIREKT("'"&L$1&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$1&"'!$R$1:$R$1869"))) +WENN(L$2="";0;SUMMEWENN(INDIREKT("'"&L$2&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$2&"'!$R$1:$R$1869"))) +WENN(L$3="";0;SUMMEWENN(INDIREKT("'"&L$3&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$3&"'!$R$1:$R$1869"))) +WENN(L$4="";0;SUMMEWENN(INDIREKT("'"&L$4&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$4&"'!$R$1:$R$1869"))) +WENN(L$5="";0;SUMMEWENN(INDIREKT("'"&L$5&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$5&"'!$R$1:$R$1869"))) +WENN(L$6="";0;SUMMEWENN(INDIREKT("'"&L$6&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$6&"'!$R$1:$R$1869"))) +WENN(L$7="";0;SUMMEWENN(INDIREKT("'"&L$7&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$7&"'!$R$1:$R$1869"))) +WENN(L$8="";0;SUMMEWENN(INDIREKT("'"&L$8&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$8&"'!$R$1:$R$1869"))) +WENN(L$9="";0;SUMMEWENN(INDIREKT("'"&L$9&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$9&"'!$R$1:$R$1869"))) +WENN(L$10="";0;SUMMEWENN(INDIREKT("'"&L$10&"'!$G$1:$G$1869");B15; INDIREKT("'"&L$10&"'!$R$1:$R$1869") ))+WENN(L$11="";0;SUMMEWENN(INDIREKT("'"&L$11&"'!$G$1:$G$1869"); B15; INDIREKT("'"&L$11&"'!$R$1:$R$1869")))

http://www.xlupload.de/download.php?file=416KS-Budget.xls

Gruss Albin

  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Franc
Geschrieben am: 13.08.2008 14:47:14

Also so selten wie die Formel da drin vorkommt, kann es nicht daran liegen.
Bei mir dauert das speichern auch nur 1 sek und ich konnte bei einem ersten Überfliegen nichts ungewöhnliches entdecken.

Speicherst du die Datei eventuell auf einem Netzlaufwerk was diese große Verzögerung verursacht?


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 14:52:53

Ja ich speichere diese Datei auf einem Netzlaufwerk, das funktioniert aber normalerweise schnell.

Das Problem ist, dass diese Formel in 3 Spalten und in 1500 Zellen drin ist.

Die Datei ist stark verkleinert, dass ich sie überhaupt uploaden konnte.

Gruss Albin


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Franc
Geschrieben am: 13.08.2008 14:59:08

hm du könntest die original Datei zippen ^^
Wie groß ist denn das original in MB und ist die größe allein durch die Formeln bedingt? (also hast jetzt nur die Formeln gelöscht, damit sie nur noch 290 KB klein ist?)


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 15:43:53

ich hab massig Tabellen und auch Zeilen gelöscht. Hab die Datei mal gezippt, ist immer noch 4,5 MB gross


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: David
Geschrieben am: 13.08.2008 16:00:13

Hi.

Du kannst Teile deiner Formel "auslagern", indem du dafür Namen definierst, hier für die INDIREKT-Ausdrücke.
Ich habe dir mal eine Formel zusammengebastelt, als Beispiel und zum Verdeutlichen des Prinzips.

https://www.herber.de/bbs/user/54582.xls

Ich bin der Meinung, dass das Ganze auch einfacher (Matrixformel?) zu realisieren ist, die entscheidende Erleuchtung hat sich aber leider noch nicht eingestellt.

Gruß

David


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 16:07:23

sieht schon viel kürzer aus, nur wie kopiere ich diese Formel dann in die anderen Spalten ohne den Bezug zur richtigen Quellspalte von der Quelltabelle zu verlieren?


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: David
Geschrieben am: 13.08.2008 16:19:08

Von oben nach unten funzt das kopieren ohne weitere Anpassungen. Die Formel rechts neben meinem Beispiel musst du dann derart anpassen, dass du jeweils den Ausdruck

INDIREKT("'"&L$1&"'!$G$1:$G$1869")

ersetzt durch

L1K

(die Zahlen dann natürlich durchnummerieren)

Anschließend kannst du dann auch diese Formel von oben nach unten kopieren.

Gruß

David


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 16:33:44

1. wo sagst du dem excel, was L1K bedeutet?

2. wo sagst du dem excel, ob er zahlen in der quellspalte p, q oder r holen soll?

danke!
Gruss Albin


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: David
Geschrieben am: 13.08.2008 16:40:44

1. Menü Einfügen - Namen - Namen definieren
2. in der Formel

Du musst doch einfach nur deine Formel als Vorlage nehmen. Beispiel:

=WENN(L$1="";0;SUMMEWENN(INDIREKT("'"&L$1&"'!$G$1:$G$1869");B15;
INDIREKT("'"&L$1&"'!$q$1:$q$1869")))/istmonat*istjahr+WENN(L$2="";0;
SUMMEWENN(INDIREKT("'"&L$2&"'!$G$1:$G$1869");B15;INDIREKT("'"&L$2&"'!$q$1:$q$1869")))
/istmonat*istjahr+WENN(L$3="";0;SUMMEWENN(INDIREKT("'"&L$3&"'!$G$1:$G$1869");
B15;INDIREKT("'"&L$3&"'!$q$1:$q$1869")))/istmonat*istjahr



Den fett markierten Teil fügst du wie oben beschrieben, als Namen "L1K" (oder wie auch immer) ein, indem du beim Verweis für diesen Namen =INDIREKT("'"&L$1&"'!$G$1:$G$1869") eingibst. In deiner Formel ersetzt du dann diesen String durch den eben definierten Namen. Voila!
Kleine Falle noch in deinem Fall: Du hast bei &L$1 zwar die "1" festgemacht, dass "L" aber nicht, dort immer noch ein $ davor setzen.

Für die anderen halt ebenso, bis du die 10 oder mehr Teile einer Formel zusammengebaut hast. Diese kannst du dann einfach nach unten kopieren.

Gruß

David


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 16:58:30

1. das mit dem Namen definieren wusste ich nicht, toll!

2. ich meine nicht "INDIREKT("'"&L$1&"'!$G$1:$G$1869"", sondern !$q$1:$q$1869"

müsste in nachstehendem Beispiel nicht überall L1W stehen? Denn mein Total ist nur aus L1 und L2 bestehend, L3 fehlt.
=WENN(L$1="";0;SUMMEWENN(L1K;B15;L1W))+WENN(L$2="";0;SUMMEWENN(L2K;B15;L1W))+WENN(L$3="";0; SUMMEWENN(L3K;B15;L1W))

Hab wieder einiges gelernt, danke!

Gruss Albin


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: David
Geschrieben am: 13.08.2008 17:20:17

K habe ich für "Kriterium" gewählt und W für Wert. In der Summewenn-Formel fragst du vorn eine Suchspalte ab (L1K) und bildest dann die Summe über die Wert-Spalte (L1W). Meine Beispielformel hatte zumindest bei mir den richtigen Wert geliefert. Prüfe, ob du wirklich die exakte Syntax in den Namen übernommen hast, insbesondere die $-Zeichen.
Du kannst die Formel auch Schritt für Schritt prüfen, indem du im Menü Extras - Formelüberwachung - Formelauswertung aktivierst und dich in dem Fenster durch die Formel klickst. Dann siehts du evtl., an welcher Stelle es hakt.

Ansonsten kann ich mich nur den anderen anschließen, dass die Struktur deiner Tabelle recht "unglücklich" ist, um es mal vorsichtig zu formulieren.

Gruß

David


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Daniel
Geschrieben am: 13.08.2008 15:33:33

Hi
ich glaube nicht, daß die Formellänge das Problem ist.
SummeWenn ist halt nunmal einfach eine langsame Formel, und wenn die mehrfach hintereinander mit einem grossen Suchbereich vorkommt, dann dauerts halt, da kann man nichts machen.
außer sich vielleicht überlegen, wie die Daten anders aufgebaut werden könnten, so daß man SummeWenn vermeiden kann. Oder die Daten in den einzelnen Tabellen müssten so umsortiert werden, daß die relevanten Zeilen in einem kleinen Zellbereich stehen, so daß du für SummeWenn ne kleiner Matrix angeben kannst.

aber um zu testen, ob meine Vermutung richtig ist, könntest du ja mal folgendes Machen:
- vor dem Speichern unter EXTRAS-OPTIONEN-BERECHNUG auf "manuell" umstellen und das häkchen bei "vor dem Speichern neu berechnen" entfernen.

Gruß, Daniel


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 15:38:48

danke. Dein Tipp "vor dem Speichern unter EXTRAS-OPTIONEN-BERECHNUG auf "manuell" umstellen" hab ich vorher schon mal probiert, mit mittlerem Erfolg.

Ich dachte halt, vielleicht könnte man die Formel insofern verkürzen, dass man statt 11mal dieselbe Formel für die Zellen in Spalte L braucht, nur eine Formel macht, die gleich die ganze Spalte L miteinschliesst (L:L). Weisst du was ich meine?

Gruss Albin


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Daniel
Geschrieben am: 13.08.2008 15:51:19

Hi

ich glaube, auf die Frage hatte ich dir schon was geantwortet.
wobei das nicht die Berechnungszeit verkürzt, weil die SummeWenns ja ausgeführt werden müssen.

wenn du hier was erreichen willst, dann müsste man sich grundsätzlich mal über den Aufbau deiner Tabellen Gedanken machen, um die Werte u.U. mit effektiveren Methoden auszuwerten.

Gruß, Daniel


  

Betrifft: AW: Formel verkürzen --> zu lange Speicherzeit von: Albin
Geschrieben am: 13.08.2008 16:00:21

Hi

Ja die SummeWenns müssten trotzdem ausgeführt werden, aber es wären 11mal weniger SummeWenns.

Mittlerweilen dauerts schon nicht mehr so lange wie zuvor, für den Moment gehts so, langfristig muss ich mir da was überlegen.

Ich hab die Tabelle übernommen von einem Kollegen, werde sie nächstes Jahr wohl neu aufbauen.

Gruss und besten Dank
Albin


 

Beiträge aus den Excel-Beispielen zum Thema "Formel verkürzen --> zu lange Speicherzeit"