Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1072to1076
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
Inhaltsverzeichnis

Eigene Funktion -> Range -> langsam

Eigene Funktion -> Range -> langsam
08.05.2009 14:46:14
Boris
Hallo,
ich habe mir eine eigene Funktion zusammengebastelt, die mir als Ergebnis einen Bezug liefert, mit dem anschließend in Excel gerechnet werden soll. Die beispielhafte untenstehende Funktion liefert das Ergebnis RR!G15:G59738 als Range. Setze ich dies nun z.B. in die Formel =INDEX(RA();1;1) ein, wird der Wert korrekt berechnet. Kopiert man allerdings diese Formel in 20.000 Zellen, braucht Excel Jahre für die Kalkulation. Im Vergleich dazu braucht Excel bei einer direkten Referenzierung "=INDEX(RR!G15:G59738;1;1)" nur einen Bruchteil einer Sekunde.
Mache ich etwas falsch bzw. woran liegt das?
Gruß, Boris
Public Function RA() As Range Set RA = Range("RR!G15:G59738") End Function


17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Eigene Funktion -> Range -> langsam
08.05.2009 14:52:32
Ramses
Hallo
Das macht ja auch irgendwie keinen Sinn, 20'000 mal diese Funktion aufzurufen, weil EXCEL nun auch 20'000 mal die SET-Anweisung im Arbeitsspeicher verwalten muss nur um 20'000 mal den gleichen Wert anzuzeigen.
Im Gegensatz zur direkten Formel, wird dieser Zellbezug, zumdindest wie in diesem Beispiel, beim kopieren nämlich auch noch nach unten angepasst.
WAS willst du denn erreichen ?
Gruss Rainer
AW: Eigene Funktion -> Range -> langsam
08.05.2009 15:14:16
Boris
Ja klar macht das keinen Sinn, ist ja auch ein vereinfachtes Beispiel:))
Erreichen will ich folgendes: Ich habe diverse Blätter mit Tabellen, die sich laufend zeilen- und spaltenweise ändern, d.h. es können auch Spalten hinzugefügt werden, wodurch sich die Tabellenstruktur ändert. Diese Blätter werden in andere Dateien kopiert (nur Werte), wo dann Formeln (Index, Summenprodukt usw.) darauf zugreifen. Bei direkter Referenzierung müssen dann alle Bezüge innerhalb der Formeln manuell angepasst werden, da sich die Struktur geändert haben kann und die Werte kopiert wurden (oder anders: dadurch passen sich die Formeln nicht automatisch an).
Meine Funktion ermittelt nun also die Range, die für eine Spaltenüberschrift gilt. Übergabeparameter sind also Sheet und Spaltenüberschrift. Es ist also egal, wo in welcher Spalte eine Überschrift steht, da diese automatisch über die Funktion gefunden wird.
Kann man eine Range noch anders zurückgeben? Muss ich denn den Set-Befehl verwenden?
Oder gibt es gar eine andere Lösung zu meinem Problem?
Gruß, Boris
Anzeige
AW: Eigene Funktion -> Range -> langsam
08.05.2009 17:16:48
Ramses
Hallo
Du sollst keine "vereinfachten" Beispiele zu Fragen senden, die du nicht lösen kannst.
Sondern die Fragestellung genau definieren. Sonst reden wir stundenlang im Kreis.
Also definiere bitte deine Aufgabe klar.
Alternativ, bzw. grundsätzlich, würde ich in diesem Fall OHNE VBA arbeiten, sondern nur mit den EXCEL Funktionen.
Die sind auf jeden Fall schneller.
In deinem Fall kannst du auf eine externe Tabelle die entsprechenden Daten schreiben, oder auch berechnen lassen.
Tabelle1

 ABCDE
11 TabelleTabelle1 
21 Summier SpalteA 
31 Ergebnis3 
4     

Formeln der Tabelle
ZelleFormel
D3=SUMME(INDIREKT(D1&"!"&D2&"1:"&D2&"5"))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruss Rainer
Anzeige
Vorschlag
08.05.2009 15:17:40
Tino
Hallo,
arbeite doch anstatt mit einer VBA Funktion mit Namen.
Beispiel:
 A
515

Formeln der Tabelle
ZelleFormel
A5=INDEX(RA;1;1)
Namen in Formeln
ZelleNameBezieht sich auf
A5RA=INDIREKT("RR!G15:G59738")
Namen verstehen

Gruß Tino
Anzeige
AW: Vorschlag
08.05.2009 15:43:17
Boris
Hallo Tino,
guter Vorschlag, eine Lösung mit Namen habe ich schon. Hier gibt es allerdings ein anderes Problem: Die Verwendung von Namen erfordert die Verwendung von INDIREKT oder auch BEREICH.VERSCHIEBEN (für die dynamische Anpassung). Dies sind (anscheinend) volatile Funktionen (laut einem Beitrag, den ich gegoogelt habe), deren Berechnung nicht nur ausgelöst wird, wenn in der Formel referenzierte Zellen geändert werden, sondern auch bei einer Vielzahl anderer Aktionen, z.B. Drücken der Entfernen-Taste auf einer leeren Zelle.
Dies hat eine extreme Verlangsamung zur Folge (bei sehr häufigem Verwenden von volatilen Funktionen), was ich auf jeden Fall bestätigen kann und dies ist auch der Grund, warum ich nach einer anderen Lösung gesucht habe (jedes mal bei einer Berechnung 30 Sekunden zu warten nervt irgendwann:))...
Hm, und jetzt?
Anzeige
kann ich jetzt so nicht bestätigen,
08.05.2009 15:58:16
Tino
Hallo,
habe es eben mal mit über eine Million Zellen getestet unter xl2007.
Das einfügen der Formel in diese Zellen dauert 2 bis 3 Sekunden,
eine Neuberechnung bestimmt nicht einmal eine Sekunde.
Aber wie gesagt, bei mir xl2007.
Ich lass die Frage mal offen.
Gruß Tino
AW: kann ich jetzt so nicht bestätigen,
08.05.2009 16:20:34
Boris
Vergleiche aber mal Deinen Vorschlag mit definiertem Namen RA = INDIRECT("RR!$A$15:$A$65536") und INDEX(RA;1;1) mit einer direkten Referenzierung INDEX(RR!$A$15:$A$65536;1;1).
Wenn du nun (bei entsprechend vielen Formeln) bei Deiner Lösung auf eine leere Zelle gehst und entfernen drückst, wirst Du sehen, dass eine Kalkulation ausgelöst wird. Bei direkter Referenzierung passiert dies dagegen nicht.
Verwendet man jetzt noch Summenprodukt-Formeln mit mehreren Bedingungen, merkt man den Unterschied spürbar...
Anzeige
Frage offen.
08.05.2009 16:41:41
Tino
Hallo,
habe ich doch gemacht "Namen RA = INDIRECT("RR!A1:A1048576") und INDEX(RA;Zeile();1)"
Ergebnis siehe oben.
Summenprodukt ist klar, ist eine Matrixformel die bei großen Zellbereichen lang braucht.
Mehr kann ich dazu nicht sagen.
Gruß Tino
AW: Frage offen.
08.05.2009 17:05:52
Boris
Das hilft mir jetzt aber auch nicht weiter:)
Wie bereits erwähnt: ich habe eine Lösung, die mit Namen in Kombination mit INDIREKT arbeitet, jedoch auf Dauer zu langsam ist. Testweise habe ich mal alle Berechnungen direkt referenziert, und dies hat zu einer erheblichen Beschleunigung geführt. Dies ist jedoch aufgrund der fehlenden Flexibilität auch keine Lösung.
Eine andere Idee wäre, die Namensvergabe und deren Referenzen über VBA zu steuern. Leider kenne ich mich zu wenig mit VBA aus.
Wie setze ich folgendes um? :
In Zeile 1 stehen die Spaltenüberschriften, in Zeile 2 stehen (dynamische) Referenzen als Text (z.B. RR!A10:A2000). Der Makrorekorder liefert mir folgenden Code zum Erstellen von Namen: ActiveWorkbook.Names.Add Name:="ID", RefersToR1C1:="=RR!R10C1:R2000C1"
D.h. also: wenn man in Zeile 1 in einer leeren Zelle eine Überschrift eingibt, soll ein Name erstellt werden, schematisch: ActiveWorkbook.Names.Add Name:="CELL (1, X). VALUE", RefersToR1C1:="=CELL (2, X).VALUE"
Wenn sich der Bezug in Zeile 2 ändert, z.B. weil Zeilen hinzugefügt wurden, müssen alle Namen angepasst werden.
Wenn man eine Zelle in Zeile 1 löscht, muss der Name gelöscht werden.
Wenn man eine Zelle in Zeile 1 ändern, muss der vorherige Name gelöscht und ein neuer erstellt werden.
Geht das? Schwierigkeiten werden wahrscheinlich Löschen und Änderungen bereiten, da man ja wissen muss, welchen Wert die Zelle vor der Änderung hatte, um den Namen anzusprechen, oder? Kann man per VBA auch ermitteln, welche Namen sich auf eine bestimmte Spalte beziehen?
Fragen über Fragen....
Anzeige
Frage offen. keine Lösung.
08.05.2009 18:14:51
Tino
Hallo,
ich kann Dir keine Lösung anbieten.
Du musst dass Häkchen bei Frage offen immer setzen,
damit andere die sich besser mit dem Formelzeugs auskennen als ich sich auch beteiligen.
Gruß Tino
Namen mit INDIRECT ?
09.05.2009 21:03:37
Daniel
ist doch wohl nicht notwendig.
anstelle von dem hier:
RA =INDIRECT("RR!A1:A1048576")
kann man doch auch gleich das hier definieren:
RA =RR!$A$1:$A$1048576
definieren (geht zumindest bis Excelversion 2003)
damit dürften dann die Namen in den Formeln deutlich schneller bearbeitet werden.
für Namensbereiche, die über BEREICH.VERSCHIEBEN(...ANZAHL2()) dynamisch gehalten werden sollen, kann man auch ein kleines Makro schreiben, daß die Namen bei bedarf aktualisiert und an den geänderten Zellbereich anpasst.
ich schreibe solche Makros normalerweise in das Change-Event des Tabellenblatts, in dem sich der Name befindet. so habe ich immer einen akutellen Namen und die vorzüge einer direkten Addressierung (schnellere Bearbeitung)
Gruß, Daniel
Anzeige
ich bin von Ausgangsfrage ausgegangen. oT.
09.05.2009 22:50:57
Ausgangsfrage
AW: Namen mit INDIRECT ?
11.05.2009 11:11:59
Boris
Hallo Daniel,
den Einsatz von Namen, die per VBA gesteuert werden, halte ich für eine sinnvolle Lösung. Damit kann ich auch das Verwenden von Indirekt und Bereich.Verschieben vermeiden. Allerdings weiß ich nicht genau, wie ich das am besten umsetze, vielleicht kannst Du mir hier helfen...
Hier mein erstes Gedankenkonstrukt: In Zeile 1 stehen die Spaltenüberschriften, in Zeile 2 stehen (dynamische) Referenzen als Text (z.B. RR!A10:A2000). Folgende Funktionen müssen gewährleistet werden:
- Wenn man in Zeile 1 in einer leeren Zelle eine Überschrift eingibt, soll ein Name erstellt werden, mit RefersTo = Inhalt der darunter liegenden Zelle.
- Wenn sich der Bezug in Zeile 2 ändert, z.B. weil Zeilen hinzugefügt wurden, müssen alle Namen angepasst werden.
- Wenn man eine Zelle in Zeile 1 löscht, muss der Name gelöscht werden.
- Wenn man eine Zelle in Zeile 1 ändert, muss der vorherige Name gelöscht und ein neuer erstellt werden.
- Das Einfügen und Löschen ganzer Spalten muss auch funktioneren.
Habe bisher folgende Lösung, die aber noch nicht sehr ausgereift ist:
In ein Worksheet, in dem die Namen automatisch angepasst werden, steht folgendes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row  1 And Target.Row  2 Then Exit Sub
Call NamenUpdaten
End Sub


Sub NamenUpdaten()
Dim Name As String
Dim Bezug As String
Dim rng As Range
Dim n As Name
For Each n In ThisWorkbook.Names
If Split(ThisWorkbook.Names.Item(n.Name).RefersTo, "!")(0) = "=" & CStr(ActiveSheet.Name) Then n.Delete
Next
For Each rng In ActiveSheet.Rows(1).SpecialCells(xlCellTypeConstants)
Name = ActiveSheet.Name & "." & rng.Value
Bezug = rng.Offset(1, 0).Value
ActiveWorkbook.Names.Add Name:=Name, RefersTo:="=" & Bezug
Next
End Sub


Blöd ist, dass ich ActiveSheet verwende. Ich weiß allerdings nicht, wie ich mit Hilfe des Targets das entsprechende Sheet ermittle...?
Mein Makro löscht also alle Namen, die sich auf das entsprechende Blatt beziehen und erstellt für jeden Wert in Zeile 1 die Namen neu. Nicht allzu elegant. Kann man das auch Spaltenweise lösen? Das Einfügen und Löschen von Spalten könnte hier Probleme bereiten.
Ein andere Sache, die nicht funktioniert ist: Wenn sich die Werte der Zeile 2 verändern, z.B. weil man eine Zeile eingefügt hat, wird das WorkSheet_Change-Ereignis nicht ausgelöst, da in Zeile 2 Formeln stehen. Sollte ich also parallel ein WorkSheet_Calculate-Ereignis einbauen?
So viele Fragen....
Gruß, Boris
PS: Was muss ich hier im Editor tun, um nach eingefügtem Code wieder normal weiterschreiben zu können?

Anzeige
AW: Lad mal ne Beispieldatei mit deinen
11.05.2009 16:33:02
Daniel
Daten hoch.
Gruß, Daniel
AW: Lad mal ne Beispieldatei mit deinen
13.05.2009 10:22:37
Boris
Hallo Daniel,
hier ist eine rudimentäre Beispieldatei, in der noch keine automatische Namensvergabe stattfindet. Es gibt ein Datensheet KK, in dem sich eine Tabelle befindet. Das Auswertungssheet fragt die KK-Daten ab (hier z.B. per SUMPRODUCT). Die Namen habe ich manuell vergeben. Meine bisherigen Versuche habe ich jetzt absichtlich weggelassen. Vielleicht können wir uns der Lösung gemeinsam annähern.
https://www.herber.de/bbs/user/61783.xls
Wenn du Fragen hast, bitte einfach melden...
Gruß, Boris
Anzeige
AW: Lad mal ne Beispieldatei mit deinen
13.05.2009 18:59:16
Daniel
HI
das ist jetzt eigentlich nicht so schwer, im prinzip hab ich das Makro schon mal geschrieben, allerdings hattest du das so was gesagt, als würde der Addressbereich des Namens in den Zellen stehen.

Private Sub Worksheet_Deactivate()
Dim nme As Name
Dim Zelle As Range
'alle Namen löschen, die mit der Sheetbezeichnung (KK) anfangen
For Each nme In ThisWorkbook.Names
If nme.Name Like Me.Name & "*" Then nme.Delete
Next
'Namen neu einfügen
For Each Zelle In Range(Me.Cells(14, 1), Me.Cells(14, Me.Columns.Count).End(xlToLeft))
ThisWorkbook.Names.Add Name:=Me.Name & "." & Zelle.Value, _
RefersTo:=Range(Zelle.Offset(1, 0), Zelle.End(xlDown))
Next
End Sub


das Makro gehört ins Modul des Tabellenblatts "KK" und läuft, wenn das Tabellenblatt verlassen wird.
die Tabelle muss lückenlos ohne Leerspalten aufgebaut sein und Tabellenblattname und Zellenüberschrift müssen zusammen einen gültigen Namen ergeben.
Sicherheitsabfragen auf diese Punkte habe ich nicht drin.
Gruß, daniel

AW: Lad mal ne Beispieldatei mit deinen
27.05.2009 11:12:19
Boris
Hallo Daniel,
war etwas zu sehr beschäftigt. Probiere gerade einige Sachen in der Art deines Beispiels. Mal schauen, ob ich das so wie ich es wünsche hinbekomme... bei Bedarf mache ich dann nochmal ein neues Thema auf.
Vielen Dank,
Boris

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige