Namen für Bereich dynamisch festlegen

[Zahnerer]

Hallo Cracks,

ich habe ein Rechnungstool, welches ganz wunderbar funktioniert, nur wird es allmählich immer schwerfälliger. Ich vermute, dass es daran liegt, dass Calc massig Zeilen durchsucht, wo überhaupt nichts drinsteht:

z.B. habe ich eine Tabelle mit Adressen von Kunden, auf die ich an vielen Stellen des Dokuments mit sverweis() zugreife. Der Zellbereich (Matrix) ist in jedem sverweis() festgelegt auf $Adressen.$A$1:$H$32000. Jetzt stehen hier aber "nur" 250 Adressen drin, die ständig erweitert werden. Die erste Spalte enthält die Kundennummern. Jetzt kam mir die Idee, ob es möglich wäre, einen Namen für eine dynamischen Zellbereich zu erstellen, so wie folgt:

Code: Alles auswählen

indirekt("$Adressen.$A$1:$H$"&max($Adressen.$A$1:$A$32000))

Man könnte die Kunden auch zählen und dann die Endzeile der Matrix dynamisch erzeugen.

Diese Formel würde ich gerne unter Einfügen - Namen - Festlegen unter zugeordnet zu eintragen und einen Namen vergeben, z.B. Adressmatrix, so dass dann bei jedem verweis() stehen würde:

Code: Alles auswählen

sverweis(Kriterium;Adressmatrix;3;0)

Geht so etwas, mache ich etwas falsch oder kann mir sonst jemand helfen?


mit erwartungsvollen Grüßen, Zahnerer

[balu]

Hallo Zahnerer,

versuchs doch mal mit folgender Formel, für "Adressmatrix".

Code: Alles auswählen

INDIREKT("A1:H"&VERGLEICH("<>";$A$1:$A$32000;1))

Nur, ob das was wirklich bringt, kann ich dir nicht versprechen. Denn schließlich muss ja Calc erstmal die letzte Zelle finden, die "ungleich" Leer ist, und das ist ja schließlich auch mit einem gewissen Arbeits- und Zeitaufwand verbunden.

Würde mich sehr freuen, wenn Du dich dazu äußern würdest, ob Du damit wirklich eine Performence Steigerung erzielen konntest .

Hinweis!
Sollte mal zwischendrin, also z.B. in A195, nichts drin stehen, und die wirklich letzte benutzte Zelle A276 sein, so wird diese Position ausgewertet, und nicht die von A195.



@Sanne

Aber mit Teilauslagerung in einen Namen glaube ich nicht, dass es geht.

Wieso Teilauslagerung? Das was Zahnerer auslagern will, ist ein komplett eigenständiger Bereich.



Gruß
balu

[balu]

Hallo Sanne,

Darin ist der INDIREKT-String ein Teil und es soll ja nicht die SVERWEIS-Funktion in einen Namen, sondern nur die Bereichsdefinition. Daher Teilauslagerung.

Hat ich mir fast gedacht, das Du das meintest, war mir aber nicht 100pro sicher.
Und ja, man kann.

Kennst Du eine sehr gesunde und Vitaminreiche SVERWEIS?
Na dann mach mal folgendes.
Der Zelle D1 den Namen Banane zuweisen.
Der Formel

Code: Alles auswählen

INDIREKT("A1:B"&VERGLEICH("<>";$A$1:$A$32000;1))

den Namen Tomate zuweisen.
Zelle E1 den Namen Gurke, und F1 den Namen Apfel zuweisen.
Nun gibst Du in E1 eine 2, und in F1 eine 0 ein.
Als nächstes gibst Du in den Spalten A und B irgendwelche Werte ein. Und in D1 irgendeinen Wert der in der Spalte A gesucht werden soll. Und jetzt kommt das wichtigste, nämlich die eigentliche Vitamin-Formel.

=SVERWEIS(Banane;Tomate;Gurke;Apfel)

Guten Appetit

Hast Du übrigens mal versucht, Deine INDIREKT-Formel in einen Namen auszulagern?

Ja natürlich habe ich das vorher getestet. Auch wenn ich noch viel kompliziertere Formelkonstrukte mit Namen habe und anwende, so teste ich es dennoch noch einmal vorher durch.

Bei mir (2.3) ist das Ergebnis in einer normalen Zelle auch wieder eine Matrix und ich zweifle, ob sich diese Art Matrix-Formel in Namen speichern lassen

Es ist klar, das die INDIREKT-Formel in einer normalen Zelle kein verwertbares Ergebnis ergibt. Sie liefert sogar den Fehler: #WERT! Das ist ja auch logisch, denn was soll INDIREKT mit einem Zellbereich anfangen?
Geb doch einfach mal die Formel ohne INDIREKT ein, also nur

Code: Alles auswählen

="A1:B"&VERGLEICH("<>";$A$1:$A$32000;1)

Und was bekommst Du dann heraus, wenn z.B. in A33 der letzte Zelleintrag steht?

Richtig!

Einen Zellbereich. In diesem Falle
A1:B33

INDIREKT liefert also jetzt einen Zellbereich, den SVERWEIS auswerten kann.

Merke!
Mit INDIREKT alleine kann man nicht arbeiten. Man benötigt noch eine zusätzliche Funktion, die mit dem Ergebnis von INDIREKT weiterarbeitet. (zumindest in diesem Fall)


Gruß
balu

[Zahnerer]

Hallo Freunde,

vielen Dank für die Antworten!

Ich habe nun balu in meiner Tabelle zitiert und in einer Zelle den Bereich bestimmen lassen:

Code: Alles auswählen

="A1:B"&VERGLEICH("<>";$A$1:$A$32000;1)

die Zelle mit "Adressmatrix" benannt und in den vielen Sverweisen innerhalb der Tabelle den Bereich mit Namen angesprochen:

Code: Alles auswählen

WENN(SVERWEIS($I$14;INDIREKT(Adressmatrix);3;0)="";"";SVERWEIS($I$14;INDIREKT(Adressmatrix);3;0))

Ich meine auf den ersten Blick, dass die Datei schon schneller geladen wird. Gemessen habe ich es nicht. Ich meine aber, da der Bereich ja nur einmal mit VERGLEICH() errechnet wird und die Verweise dann nur den tatsächlichen Bereich durchsuchen müssen, dass eine Beschleunigung logisch wäre.

Vielen Dank nochmal!

Zahnerer