Dynamische Matrix in SVERWEIS
Marcel Peter
Wie kann ich mich dynamisch auf eine Matrix in einer anderen Datei
verknüpfen?
Soviel habe ich in der Zwischenzeit rausgefunden:
Mein SVerweis lautet:
=SVERWEIS(b5;e5;e4;FALSCH)
wobei:
Suchkriterium b5=Hans,
Matrix e5='N:\BUP\[TEST.XLS]Jahr'!$1:$65536
und Spaltenindex e4=9.
Das ganz läuft aufgrund der variablen Matrix nicht.
Ich habe folgendes via Indirekte Adressierung probiert:
e5=[TEST.XLS]Jahr
=SVERWEIS(b5;INDIREKT(e5&"!$1:$65536");e4;FALSCH)
Nun läuft es, unter der Bedingung, dass die Datei Test.xls geöffnet ist.
NB: INDIREKT("N:\BUP\"&e5&"!$1:$65536") geht leider nicht.
Wie kann ich mich dynamisch auf eine Matrix in einer anderen Datei (inkl.
Pfadangabe) verknüpfen?
Vielen Dank zum Voraus
Marcel
Alexander Wolff
////////////////////////////////////////////////////////
Möglichkeit ohne VBA ( von Frank Arendt-Theilen gepostet), wertet einen
Text als Formel aus:
Markiere Zelle B2, dann
EINFÜGEN | NAMEN | FESTLEGEN
Namen in der Arbeitsmappe: Ergebnis
Bezieht sich auf: =AUSWERTEN(A2)
Wenn Du in einer Zelle nun die Formel
=Ergebnis
einträgst, wird der Zelleninhalt der links daneben stehenden
Zelle als Formel ausgewertet.
--
Mit freundlichen Grüssen Thomas Ramel
////////////////////////////////////////////////////////
Das führt Dich (evtl. mit &) zum Ziel!
Alexander
Hans Hofmann
Hallo Marcel,
>
8<--------
>wobei:
>Suchkriterium b5=Hans,
>Matrix e5='N:\BUP\[TEST.XLS]Jahr'!$1:$65536
>und Spaltenindex e4=9.
>
>Das ganz läuft aufgrund der variablen Matrix nicht.
>
>Ich habe folgendes via Indirekte Adressierung probiert:
>e5=[TEST.XLS]Jahr
>=SVERWEIS(b5;INDIREKT(e5&"!$1:$65536");e4;FALSCH)
$1:$65536 so wird das nix.
War es das?
Gruß HW
--
__________________________________________________________
News suchen, lesen, schreiben mit http://newsgroups.web.de
Marcel Peter
$1:$65536 heisst einfach, dass Excel die ganze Matrix von erste bis zur
letzten Zeile und Spalte durchsucht, dies funktioniert bestens.
Das Problem liegt beim Pfad.
Trotzdem vielen Dank für deinen Hinweis.
Grüsse,
Marcel
Hans Hofmann schrieb in Nachricht <3cff57ba$1...@netnews.web.de>...
Daniel Schmitz
> wobei:
> Suchkriterium b5=Hans,
O.K.
> Matrix e5='N:\BUP\[TEST.XLS]Jahr'!$1:$65536
Mumpitz.... Was soll denn das für ein Bereich sein?? ;-)
> und Spaltenindex e4=9.
auch O.K.
> Das ganz läuft aufgrund der variablen Matrix nicht.
wo genau ist Deine Matrix variabel?? Du gibst sie doch
"nur" in ein anderes Feld ein....
> Ich habe folgendes via Indirekte Adressierung probiert:
> e5=[TEST.XLS]Jahr
> =SVERWEIS(b5;INDIREKT(e5&"!$1:$65536");e4;FALSCH)
Das grundsätzliche Problem, das ich hier sehe ist, dass bei Deinem
Versuch eine Matrix aus einer anderen Datei zu übernehmen (was
eigentlich kein Problem ist) Dein Weg nicht so trivial ist. Dadurch,
dass Du einen Pfad aus einer Zelle einlesen willst sieht Excel diesen
Wert als Text an und nicht als Pfadangabe zu einer anderen Excel
Datei. Deshalb musst Du wenn schon mit:
Indirekt(Indirekt(...))
arbeiten. Allerdings funktioniert Indirekt nicht mit geschlossenen Dateien
und nicht mit Pfadangaben. Frag mich nicht warum aber es scheint so
zu sein.
> Wie kann ich mich dynamisch auf eine Matrix in einer anderen Datei (inkl.
> Pfadangabe) verknüpfen?
Was verstehst Du unter dynamisch?
Vielleicht konnte oder kann ich wenigstens ein bisschen helfen.
Gruss
Daniel
Marcel Peter
Excel meldet, dass die Funktion =AUSWERTEN () nur in einem Diagramm, nicht
aber in einer Tabelle gültig ist.
Trotzdem ein herzliches Dankeschön für den Hinweis
Marcel
Alexander Wolff schrieb in Nachricht ...
Marcel Peter
Zur Matrix:
Wenn man nicht einen spezifischen Bereich, sondern die ganze Tabelle
durchsuchen möchte, so kann man dies auch so eingeben (Schaltfläche für
alles markieren). Excel schreibt dann $1:$65536. Es sieht nach Mumpiz aus,
aber es funktioniert prächtig.
zu dynamisch:
Wir haben ein Auswertetool (MIS) und rund 60 ähnliche Basistabellen (in
anderen Verzeichnissen). Nun will ich die Verknüpfung nicht statisch haben,
d.h. alle SVerweise haben einen festen Bezug auf zB Tabelle 27, sondern der
Bezug ist eben dynamisch. Ich gebe in eine Zelle den Dateinamen mit Pfad,
drücke F9 und die gewünschten Daten sind da.
Wir werden wohl weiterhin quick and dirty mit Ersetzen von Pfad/Dateiname
weiterarbeiten.
So long
Marcel
Daniel Schmitz schrieb in Nachricht ...
Alexander Wolff
Alexander
Thomas Ramel
Alexander Wolff schrieb:
>
> Irrtum! Auch im Zusammenhang mit FELDNAMEN - genau lesen!
Danke für die 'Ehrenrettung' ;-)
Ob die Auswertung mittels des Excel4Makros im Bereichnamen allerdings
das gewünschte Resultat bringt kann ich so auf die Schnelle nicht sagen.
Hast Du es schon mit Erfolg versucht? Das wäre dann nämlich in ert Tat
eine interessante Variante, da INDIREKT(), wie hier in einme Beitrag
schon geschrieben wurde, für geschlossene Arbeitsmappen nicht
funktionert.
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für Microsoft-Excel -
Frank Arendt-Theilen
folgende Beispielformel zur Orientierung:
=SVERWEIS(B5;INDIREKT("[Mappe6]Tabelle1!A1:C10");INDIREKT("E4"))
Anmerkung
Die Arbeitsmappe Mappe6 muss geöffnet sein. Einer Pfadangabe auf diese
Arbeitsmappe entfällt daher, bzw. darf nicht angegeben werden. Der
Tabellenblattname ist erforderlich. Die Bereichsangabe $1:$65536
umfaßt das gesamte Tabellenblatt, wobei durch die Definition des
SVerweises Spalte A das Suchkriterium enthalten müsste.
MfG Frank
_____________________________________________________
Frank Arendt-Theilen, Microsoft MVP für Excel, Hameln
eMail: Thei...@t-online.de, Homepage: http://www.xl-faq.de
Alexander Wolff
Ich hatte das mit der geschlossenen Datei gar nicht so beachtet, sondern
eher die dynamische Matrix interessant gefunden!
Vorläufiges Ergebnis (leider):
Formel =SVERWEIS (mit direkt geschr. Pfad) ok bei geschl Datei!
Auswertestring SVERWEIS (mit direkt geschr. Pfad*) ok bei offener Datei...
* oder auch "concatenated" mit &, da ja String
... aber nicht bei geschlossener! (Also hier wohl kein Vorteil ggü
=INDIREKT)
Allerdings ist der Fehlerwert im letzten (erfolglosen) Fall
#WERT!, wenn der Pfad nicht richtig geschrieben ist, und
#BEZUG!, wenn wenigstens dies der Fall ist.
(Getestet letztere Aussage mittels "Aus String mach Formel!")
Das deutet darauf hin, daß =AUSWERTEN wenigstens die Datei findet!
(bei offener Quelldatei ist die Angabe des (natürlich richtigen) Pfads
optional)
Stellt sich die Frage, inwieweit man =AUSWERTEN in der Namensdefinition noch
beeinflussen darf....
Alexander
Daniel Schmitz
nochmal kurz zu der Matrix.....
Es ist schon o.k., dass $1:$65536 die gesamte Arbeitsmappe
einschließt, allerdings wenn Du sowieso die Spalte 9, wie in Deinem
Fall, oder eine andere Spalte die nicht gerade 256 ist benutzen willst,
dann finde ich eine "richtige" Matrixangabe übersichtlicher und Du
brauchst Deinen Rechner nicht mit einer 256 x 65536 Matrix zu
quälen.
Das habe ich damit gemeint.
Zu dem Rest hast Du glaube ich inzwischen genug gehört.... :-)
Gruss
Daniel
Marcel Peter
In der Zelle zB. AD1 verknüpfe ich mich auf die Datei
='N:\BUP\[TEST.XLS]Jahr'!$A$1. Es erscheint der dort eingegebene Titel.
In der Zelle zB. AD2 gebe ich obigen Pfad und Dateiname als Text ein:
'='N:\BUP\[TEST.XLS]Jahr'!$A$1
Auf Zelle e5 kopple ich davon den Datei- und Blattname aus:
=TEIL(AD2;FINDEN("[";AD2);FINDEN("'";AD2;5)-FINDEN("[";AD2)).
Dann die Formel:
=SVERWEIS($b5;INDIREKT(e5&"!$1:$65536");e4;FALSCH)
Dann Doppelklick auf AD1 und alle meine SVerweise erhalten die gewünschten
Daten.
Will ich nun die Datei mit den Basisdaten wechseln, so muss ich nur die
Zellen AD1 und 2 anpassen.
Nochmals vielen Dank an alle Beteiligten.
Marcel
NB: Ich habe die Funktion =Auswerten() nochmals probiert. Fehlermeldung:
"Die verwendete Funktion ist in Microsoft Excel oder in diesem Blatt-Typ
ungültig. Die Funktion Datenreihe ist zum Beispiel in einem Diagramm gültig
.... "+ weitere 4 Zeilen Fehlermeldung.
Ich habe Excel 97 SR2, wahrscheinlich ist diese Funktion erst in Excel 2000
verfügbar.
Thomas Ramel
Marcel Peter schrieb:
>
> Hier die Lösung, die ich nun für meine Auswertungen zusammengestellt habe.
>
> In der Zelle zB. AD1 verknüpfe ich mich auf die Datei
> ='N:\BUP\[TEST.XLS]Jahr'!$A$1. Es erscheint der dort eingegebene Titel.
>
> In der Zelle zB. AD2 gebe ich obigen Pfad und Dateiname als Text ein:
> '='N:\BUP\[TEST.XLS]Jahr'!$A$1
>
> Auf Zelle e5 kopple ich davon den Datei- und Blattname aus:
> =TEIL(AD2;FINDEN("[";AD2);FINDEN("'";AD2;5)-FINDEN("[";AD2)).
>
> Dann die Formel:
> =SVERWEIS($b5;INDIREKT(e5&"!$1:$65536");e4;FALSCH)
>
> Dann Doppelklick auf AD1 und alle meine SVerweise erhalten die gewünschten
> Daten.
>
> Will ich nun die Datei mit den Basisdaten wechseln, so muss ich nur die
> Zellen AD1 und 2 anpassen.
Eine schöne Lösung :-)
> NB: Ich habe die Funktion =Auswerten() nochmals probiert. Fehlermeldung:
> "Die verwendete Funktion ist in Microsoft Excel oder in diesem Blatt-Typ
> ungültig. Die Funktion Datenreihe ist zum Beispiel in einem Diagramm gültig
> .... "+ weitere 4 Zeilen Fehlermeldung.
> Ich habe Excel 97 SR2, wahrscheinlich ist diese Funktion erst in Excel 2000
> verfügbar.
Die Funktion ist eine Excel4Funktion, es gibt sie also schon eine ganze
Weile.
Hast Du die Funktion in eine Tabellenzelle geschrieben? Da funktioniert
sie in der Tat nicht.
Wenn Du allerdings unter Einfügen --> Name --> Definieren die Funtion
unter 'Bezieht sich auf:' verwendest tut sie es sehr wohl (auch in
xl97). Sieh Dir den Beitrag von Alexander Wolff von gestern 14:20
nochmals an, er hat dort gut zitiert wie das Vorgehen ist.
Alexander Wolff
wäre ja toll, wenn es wirklich geht (XL2000). Es wäre das erstemal, daß es
jemand "gepackt" hat (habe in Herber's Archiv nachgeschaut, wo es auf Fragen
in dieser Group nie zu funktionierenden Lösungen kam).
Bei mir geht es leider nicht (vielleicht mache ich ja auch was falsch beim
Umsetzen auf meine Tabellen). #BEZUG!
> In der Zelle zB. AD1 verknüpfe ich mich auf die Datei
> ='N:\BUP\[TEST.XLS]Jahr'!$A$1. Es erscheint der dort eingegebene Titel.
Zellinhalt, meinst Du, oder? Oder hat es was mit dem Titel auf sich? (Die
Verknüpfung funktioniert natürlich auch bei mir).
> In der Zelle zB. AD2 gebe ich obigen Pfad und Dateiname als Text ein:
> '='N:\BUP\[TEST.XLS]Jahr'!$A$1
Hiermit endet dann aber auch der Sinn von AD1, oder? Warum nicht gleich hier
eintragen? Oder dient AD1 dazu, die Verknüpfung technisch erst aufzubauen
(bzw. als Zelle direkt zu halten?).
Wenn nicht: Die Formel AD1 steht ganz für sich allein. Nichts bezieht sich
später darauf. Warum muß ich sowohl AD1 und AD2 ändern? Das müßte sich doch
auch reduzieren lassen können.
> Auf Zelle e5 kopple ich davon den Datei- und Blattname aus:
> =TEIL(AD2;FINDEN("[";AD2);FINDEN("'";AD2;5)-FINDEN("[";AD2)).
>
> Dann die Formel:
> =SVERWEIS($b5;INDIREKT(e5&"!$1:$65536");e4;FALSCH)
>
> Dann Doppelklick auf AD1 und alle meine SVerweise erhalten die
> gewünschten Daten.
> Will ich nun die Datei mit den Basisdaten wechseln, so muss ich nur die
> Zellen AD1 und 2 anpassen.
Wenn es also bei Dir geht: Wie händelst Du den Verweis $1:$65536 allgemein?
SVERWEIS kann das Suchkriterium immer nur mit der ersten Spalte vergleichen.
Wie wäre es mit Bereichsnamen der Quelltabellen?
Weitere Frage: Was passiert bei Wechsel der Quelle? Wird sie im Fenster
"Vernüpfungen" gewechselt oder hinzugefügt? Bei letzterem: Wie räumt man
dies auf?
Gespannt auf Deine Antwort
Alexander
Alexander Wolff
Hans Herber schreibt in
http://www.herber.de/cgi-bin/callsmpl.pl?Datei=020798h.htm :
Frage: In den Kopfzeilen habe ich einen Arbeitsmappen-Namen (Beispiel:
"Test") stehen. In ausgewählten Zellen darunter sollen Bezüge auf die
jeweilige Arbeitsmappe hergestellt werden. Mit der =INDIREKTFunktion kann
dies nicht erreicht werden, da diese nicht auf geschlossene Arbeitsmappen
zugreifen kann. Gibt es eine Lösung?
(mit Lösung unter: VBA-Beispiele / Funktion / 020798 zu lesen, falls Link
nicht funktioniert)
Alexander
Marcel Peter
Auf Wunsch kann ich das Excel-Mustersheet (XL97 SR2) mailen.
Gruss, Marcel
Alexander Wolff schrieb in Nachricht ...
>Hallo Marcel,
>
>wäre ja toll, wenn es wirklich geht (XL2000). Es wäre das erstemal, daß es
>jemand "gepackt" hat (habe in Herber's Archiv nachgeschaut, wo es auf
Fragen
>in dieser Group nie zu funktionierenden Lösungen kam).
>
>Bei mir geht es leider nicht (vielleicht mache ich ja auch was falsch beim
>Umsetzen auf meine Tabellen). #BEZUG!
Die Tabelle ist nicht offen.
>> In der Zelle zB. AD1 verknüpfe ich mich auf die Datei
>> ='N:\BUP\[TEST.XLS]Jahr'!$A$1. Es erscheint der dort eingegebene Titel.
>
>Zellinhalt, meinst Du, oder? Oder hat es was mit dem Titel auf sich? (Die
>Verknüpfung funktioniert natürlich auch bei mir).
Zellinhalt. Der Titel im zu verknüpfenden File. Damit weiss ich immer, auf
welche der 60 Basisdateien ich mich derzeit verknüpft habe.
>> In der Zelle zB. AD2 gebe ich obigen Pfad und Dateiname als Text ein:
>> '='N:\BUP\[TEST.XLS]Jahr'!$A$1
>
>Hiermit endet dann aber auch der Sinn von AD1, oder? Warum nicht gleich
hier
>eintragen? Oder dient AD1 dazu, die Verknüpfung technisch erst aufzubauen
>(bzw. als Zelle direkt zu halten?).
>Wenn nicht: Die Formel AD1 steht ganz für sich allein. Nichts bezieht sich
>später darauf. Warum muß ich sowohl AD1 und AD2 ändern? Das müßte sich doch
>auch reduzieren lassen können.
AD1 brauche ich nur, um das gesuchte File mit Doppelklick zu öffnen. AD2 um
die dyn. Verknüpfung zu aktualisieren.
>
>> Auf Zelle e5 kopple ich davon den Datei- und Blattname aus:
>> =TEIL(AD2;FINDEN("[";AD2);FINDEN("'";AD2;5)-FINDEN("[";AD2)).
>>
>> Dann die Formel:
>> =SVERWEIS($b5;INDIREKT(e5&"!$1:$65536");e4;FALSCH)
>>
>> Dann Doppelklick auf AD1 und alle meine SVerweise erhalten die
>> gewünschten Daten.
>> Will ich nun die Datei mit den Basisdaten wechseln, so muss ich nur die
>> Zellen AD1 und 2 anpassen.
>
>Wenn es also bei Dir geht: Wie händelst Du den Verweis $1:$65536 allgemein?
>SVERWEIS kann das Suchkriterium immer nur mit der ersten Spalte
vergleichen.
>Wie wäre es mit Bereichsnamen der Quelltabellen?
Viel zu kompliziert.Alle Suchkriterien müssen in diesem Beispiel in Spalte A
stehen (=ID-Nrn.), ich blende sie anschliessend immer aus.
>Weitere Frage: Was passiert bei Wechsel der Quelle? Wird sie im Fenster
>"Vernüpfungen" gewechselt oder hinzugefügt? Bei letzterem: Wie räumt man
>dies auf?
Datei in AD1 manuell verknüpfen, Quelle schliessen, Text manuell in AD2
einfügen. Zum Aufräumen gibt es ein Makro.