Durch Eingabe der Referenz „1“ in B11 wird ausgelöst, dass der SVERWEIS aus einem bestimmten Bereich Daten zurückliefert.
Mein Problem ist, dass ich ca. 50 verschiedene Referenzen haben (Eingabe 1-50 in B11), die jede in aus einem anderen Bereich zurückliefern soll.
Denn SVERWEIS auf den neuen Bereich anpassen, kann ich,
aber wie löse ich die 50 Referenzen auf intelligente Weise ?
Und wenn ich wirklich 50x WENN verschachteln muss, wäre ich dankbar für Tipps zur Syntax.
Durch Eingabe der Referenz „1“ in B11 wird ausgelöst, dass der
SVERWEIS aus einem bestimmten Bereich Daten zurückliefert.
Mein Problem ist, dass ich ca. 50 verschiedene Referenzen
haben (Eingabe 1-50 in B11), die jede in aus einem anderen
Bereich zurückliefern soll.
Denn SVERWEIS auf den neuen Bereich anpassen, kann ich,
aber wie löse ich die 50 Referenzen auf intelligente Weise ?
Hmmm, gibe es direkte Abhängigkeiten dieser Bereiche von einander?
Dann könntest Du ev. mit BEREICH.VERSCHIEBEN() die Bereiche anpassen in Abehängigkeit deiner Eingabe.
Ansonsten könntest Du allen Bereichen einen Bereichsnamen ‚verpassen‘, der mit einer Endziffer von 1-50 versehen („DeinName_1“, „DeinName_2“) ist und dann mit INDIREKT() derauf zugreifen.
Den Inhalt von INDIREKT() setze Du zusammen aus dem Namensteil und deiner Eingabe:
Wenn die 50 Bereiche, aus denen Du lesen willst, eine äquivalente und konstante Größe haben, dann könntest Du theoretisch komplett auf die WENN-Funktion verzichten und stattdessen eine Berechnung in folgender Art durchführen:
Ich unterstelle mal
Bereich 1 ist $A$1:blush:A$100
Bereich 2 ist $A$101:blush:A$200
Bereich 3 ist $A$201:blush:A$300
Die Berechnung der Bereiche würde dann zu folgender Formel führen:
vielen Dank für deinen schnellen Tipp, aber mein Problem scheint anders gelöst werden zu müssen- Der Ansatz mit den gleichen Bereichsgrößen ist schon richtig; ich erläutere mal näher:
Ich habe 50 Tabellen mit gleichem Aufbau, gleicher Größe, aber unterschiedlichem Inhalt. Jede Zeile ist von 1-100 nummeriert.
D11(=Zufallsbereich(1;100)bestimmt, aus welcher Zeile zurückgeliefert wird. In den Tabellen stehen mehrere Werte in Spalten nebeneinander, die ich durch die jeweilige SVERWEIS-Spaltenzahl zurückliefere.SO ! Und in B11 will ich nun einen Wert von 1-50 eingeben, der dann dem SVERWEIS sozusagen die Tábelle bestimmt, aus der mittels D11 zurückgeliefert werden soll. Also verbal: Wenn in B11 eine 1 steht, gucktst Du den Wert aus D11 in Tabelle1 nach, Wenn in B11 eine 2 steht, guckst Du den Wert aus D11 in Tabelle2 nach usw usw bis 50.
Entschuldige, dass ich es so umständlich erkläre, wäre aber super, wenn Du eine Lösung hättest…
Danke und Gruß
Andy
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hmmm, gibe es direkte Abhängigkeiten dieser Bereiche von
einander?
Dann könntest Du ev. mit BEREICH.VERSCHIEBEN() die Bereiche
anpassen in Abehängigkeit deiner Eingabe.
Ansonsten könntest Du allen Bereichen einen Bereichsnamen
‚verpassen‘, der mit einer Endziffer von 1-50 versehen
(„DeinName_1“, „DeinName_2“) ist und dann mit INDIREKT()
derauf zugreifen.
Den Inhalt von INDIREKT() setze Du zusammen aus dem Namensteil
und deiner Eingabe:
INDIREKT(„DeinName_“&B1")
Hi, Thomas,
auch Dir Danke für den schnellen Tipp, klappt aber nicht so richtig.
Vom Ansatz löst es nicht mein Problem, lies doch mal meine Antwort an Carsten (ich wollte nicht doppel-posten). Vielleicht hast Du aufgrund der detaillierten Problembeschreibung noch einen Vorschlag - wäre toll )
Bei dem INDIREKT sehe ich nicht, wo/wie ich die Spalte bestimme, aus der ein Wert zurückgeliefert wird. Habe es trotzdem probiert, erhalte aber #WERT. Vielleicht weil die Tabelle, der ich einen Namen gegeben habe, auf einem anderen Arbeitsblatt steht, als die auslösende Formel ?
Der Ansatz mit den gleichen Bereichsgrößen ist schon richtig; ich erläutere mal näher:
Ich habe 50 Tabellen mit gleichem Aufbau, gleicher Größe, aber
unterschiedlichem Inhalt. Jede Zeile ist von 1-100 nummeriert.
D11(=Zufallsbereich(1;100)bestimmt, aus welcher Zeile
zurückgeliefert wird. In den Tabellen stehen mehrere Werte in
Spalten nebeneinander, die ich durch die jeweilige
SVERWEIS-Spaltenzahl zurückliefere.SO ! Und in B11 will ich
nun einen Wert von 1-50 eingeben, der dann dem SVERWEIS
sozusagen die Tábelle bestimmt, aus der mittels D11
zurückgeliefert werden soll. Also verbal: Wenn in B11 eine 1
steht, gucktst Du den Wert aus D11 in Tabelle1 nach, Wenn in
B11 eine 2 steht, guckst Du den Wert aus D11 in Tabelle2 nach
usw usw bis 50.
Wenn Deine Tabellen folgende Namen haben:
Tabelle1
Tabelle2
Tabelle3
...
Tabelle50
dann kannst Du trotzdem auf die WENN-Funktion verzichten und Deine Formel mit folgendem Prinzip verwenden:
vielen Dank für deinen schnellen Tipp, aber mein Problem
scheint anders gelöst werden zu müssen- Der Ansatz mit den
gleichen Bereichsgrößen ist schon richtig; ich erläutere mal
näher:
Ich habe 50 Tabellen mit gleichem Aufbau, gleicher Größe, aber
unterschiedlichem Inhalt. Jede Zeile ist von 1-100 nummeriert.
D11(=Zufallsbereich(1;100)bestimmt, aus welcher Zeile
zurückgeliefert wird. In den Tabellen stehen mehrere Werte in
Spalten nebeneinander, die ich durch die jeweilige
SVERWEIS-Spaltenzahl zurückliefere.SO ! Und in B11 will ich
nun einen Wert von 1-50 eingeben, der dann dem SVERWEIS
sozusagen die Tábelle bestimmt, aus der mittels D11
zurückgeliefert werden soll. Also verbal: Wenn in B11 eine 1
steht, gucktst Du den Wert aus D11 in Tabelle1 nach, Wenn in
B11 eine 2 steht, guckst Du den Wert aus D11 in Tabelle2 nach
usw usw bis 50.
OK, dieses Szenario ist soweit klar - aber leider sagst Du nirgens wo genau die Bereiche sind, in denen Du deine Tabellen abgelegt hast.
Wenn diese Bereiche in einem immer gleich bleibenden Muster abgegelgt sind, könnte man deren Lage anhand deiner Eingabe berechnen lassen. Mangels Kenntnis können wir hier aber nicht mehr als prinzipielle Auskunft geben.
Mein Vorschlag war ja schon, jedem dieser Bereiche einen Namen zu vergeben, der sich immer durch die Endziffer unterschiedet - hast Du das schon getan?
Gib uns daher genaue(re) Angaben über die Lage deiner Tabellen, dann können wir auch klare und konkrete Hilfe leisten; dies vermutlich sogar in Form einer funktionierenden Formel.
danke für Deine Zeit und Tipps; habe es gleich ausprobiert und meinen Tabellen die Namen „Tabelle1“, „Tabelle2“ usw. gegeben.
Die Formel
=SVERWEIS($D$11;INDIREKT(VERKETTEN(„Tabelle“;$B$11;"!$CM$2:blush:CN$102"));2:wink:
liefert aber leider nur #BEZUG! zurück.
In D11 habe ich eine „1“ eingegeben, B11 liefert den Zufallswert „94“
Die „Tabelle1“ erstreckt sich über $CM$2:blush:CN$102, „Tabelle2“ über $CM$103:blush:CN$202. (Eigentlich nicht CN sondern DF, aber ich übe erstmal mit 2 Spalten) Beide haben eine Zeilennummerierung von 1-100 und sollten jetzt den Wert aus Zeile 94, Spalte 2 zurückgeben; je nach dem welche Tabelle in D11 gewählt wird.
Präzisere Angaben (siehe auch Antwort an Carsten):
Ja, ich habe (erstmal) 2 von 50 Tabellen Namen gegeben; „Tabelle1“ und „Tabelle2“. Alle Tabellen sind gleich aufgebaut:100 Zeilen und 20 Spalten (ich übe aber erstmal nur mit 2 Spalten.
Die Spaltenköpfe sind immer CM (Zahl von 1-100) bis DF (unterschiedliche Feldinhalte, Text und Zahlen). Im aktuellen Beispiel nur CM bis CN, bleiben wir der Einfachheit halber dabei.
Die Zeilen „wandern“: Tabelle1 von $CM$2 bis $CN$102, Tabelle2 von $CM$103 bis $CN$202, Tabelle3 von $CM$104 bis $CN$302 usw.
Immer 100 Zeilen dazu.
Ich stelle gerade eine ungewollte Unregelmäßigkeit fest: Der erste Bereich enthält in CM2:smiley:F2 die Spaltenüberschriften. Eigentlich ist der erste Tabellenbereich erst ab CM3.
Die Formel mit den Felder B11 und D11 sind auf „BEG1“, die auszulesenden Tabellen auf „_Matrix“.
danke für Deine Zeit und Tipps; habe es gleich ausprobiert und
meinen Tabellen die Namen „Tabelle1“, „Tabelle2“ usw. gegeben.
Die Formel
=SVERWEIS($D$11;INDIREKT(VERKETTEN(„Tabelle“;$B$11;"!$CM$2:blush:CN$102"));2:wink:
liefert aber leider nur #BEZUG! zurück.
In D11 habe ich eine „1“ eingegeben, B11 liefert den
Zufallswert „94“
Carsten hat die Formel für diese deine Aussage entwickelt:
***************************************************************
D11(=Zufallsbereich(1;100)bestimmt, aus welcher Zeile zurückgeliefert wird. In den Tabellen stehen mehrere Werte in Spalten nebeneinander, die ich durch die jeweilige SVERWEIS-Spaltenzahl zurückliefere.SO ! Und in B11 will ich nun einen Wert von 1-50 eingeben, der dann dem SVERWEIS sozusagen die Tábelle bestimmt, aus der mittels D11 zurückgeliefert werden soll. Also verbal: Wenn in B11 eine 1 steht, gucktst Du den Wert aus D11 in Tabelle1 nach, Wenn in B11 eine 2 steht, guckst Du den Wert aus D11 in Tabelle2 nach usw usw bis 50.
****************************************************************
Wenn du nun D11 mit B11 einfach vertauschst kommt klar ein Fehler weil du kein Blatt tabelle94 hast…
danke für Deine Zeit und Tipps; habe es gleich ausprobiert
und meinen Tabellen die Namen „Tabelle1“, „Tabelle2“ usw.
gegeben.
Die Formel
=SVERWEIS($D$11;INDIREKT(VERKETTEN(„Tabelle“;$B$11;"!$CM$2:blush:CN$102"));2:wink:
liefert aber leider nur #BEZUG! zurück.
In D11 habe ich eine „1“ eingegeben, B11 liefert den
Zufallswert „94“
Wie Reinhard schon anmerkte hast Du die beiden Zellen B11 und D11 vertauscht.
Aber auch der Inhalt von INDIREKT() ist nicht korrekt - wenn Du den Tabellen Namen vergeben hast, brauchst Du dort die Bereiche nicht noch einmal aufzuführen.
Nimm die folgende Formel, die funktioniert hier problemlos:
sorry, hab´s falsch hingeschrieben - die Aufregung…
Die Formel lautet richtig (copy+paste):
=SVERWEIS($B$11;INDIREKT(VERKETTEN(„Tabelle“;$D$11;"!$CM$2:blush:CN$102"));2:wink:
Und die liefert „#BEZUG!“
Hab dann deinen Tipp (von wegen keine Tabelle94) aufgenommen und D11 in B11 geändert:
=SVERWEIS($B$11;INDIREKT(VERKETTEN(„Tabelle“;$B$11;"!$CM$2:blush:CN$102"));2:wink:
Die liefert aber auch „#BEZUG!“
Was mache ich falsch ? Liegt es am Ausrufungszeichen vor dem Bereich ?Müsste da vielleicht das Arbeitsblatt stehen ?
Danke und Gruß
Andreas
Wenn du nun D11 mit B11 einfach vertauschst kommt klar ein
Fehler weil du kein Blatt tabelle94 hast…
aaahah !!! Diese Formel funktioniert, ich habe die Vertauschung korrigiert, also erst D11 und dann B11 (damit der Zufallswert nicht die Tabelle bestimmt), aber es klappt nur wenn in B11 eine 1 steht.
Wenn eine 2 steht, kommt #NV
Aber schon sehr nah dran…fieber…
Wenn Du noch mal drüber gucken magst…
Danke und Gruß
Andy
Wie Reinhard schon anmerkte hast Du die beiden Zellen B11 und
D11 vertauscht.
aaahah !!! Diese Formel funktioniert, ich habe die
Vertauschung korrigiert, also erst D11 und dann B11 (damit der
Zufallswert nicht die Tabelle bestimmt), aber es klappt nur
wenn in B11 eine 1 steht.
Wenn eine 2 steht, kommt #NV
#NV deutet in aller Regel an, dass der Suchbegriff (in meiner Formel der Wert in B11) nicht in der ersten Spalte der Suchmatrix enthalten ist - Du sagest doch es wären die Zahlen 1 bis 100?
=SVERWEIS($B$11;INDIREKT("Tabelle"&$D$11);2;0)
Aber schon sehr nah dran…fieber…
Wenn Du noch mal drüber gucken magst…
Mögen und wollen helfen hier leider nicht viel, denn Du zeigst (schon wieder) nicht die exakte Formel die Du verwendet hast…
Oder ist es so, dass der Wert in D11 (die Tabelle in der gesucht werden soll) nicht korrekt ist oder nicht alle Werte enthält?
Sorry, aber mit solchen Fragmenten Ursachenforschung zu betreiben gleicht schon fast dem Blick in die Glaskugel (meine ist notabene aus Schweizer Bergkristall gefertigt)
Hi Andy,
ich weiss nicht ob du schon wie von Thomas nehegelegt, die 50 Namen vergeben hast.
Mit dem nachfolgenden Makro geht es schneller und vor allem auch schneller wenn du mal die Bereiche ändern musst.
Alt+F11, Einfügen–Modul, nachfolgenden Code reinkopieren, Bereich $D$1:blush:F$300 anpassen.
Dann den Cursor beliebig irgednwo in den Code stellen und F5 drücken.
Danach kannste den Code drinlassen, oder durch Rechtsklick links auf den Modulnamen Modul1 das Modul entfernen.
Anwendung in Excel
=sverweis($b$11;indirekt(„Ä“&$d$11);2;0)
@Thomas Du vergibst den Namen „Tabelle1“ für einen Bereich? Das Excel da mitmacht wundert mich (ich mache das nicht mit, weils mich verwirrt *gg*).
Gruss
Reinhard
Sub NamenErzeugen()
Dim N
On Error Resume Next
For Each N In ThisWorkbook.Names
If N.Name Like "Ä\*" Then N.Delete
Next N
On Error GoTo 0
For N = 1 To Worksheets.Count
ThisWorkbook.Names.Add Name:="Ä" & N, RefersTo:="=" & Worksheets(N).Name & "!$D$1:blush:F$300"
Next N
'ThisWorkbook.Names.Add Name:="Ö", RefersTo:="=Auswerten(""sverweis(a1;Ä""&$a$1&"";3;0)"")"
End Sub
Die Formel lautet richtig (copy+paste):
=SVERWEIS($B$11;INDIREKT(VERKETTEN(„Tabelle“;$D$11;"!$CM$2:blush:CN$102"));2:wink:
Und die liefert „#BEZUG!“
Wie Thomas schon sagte, kommt #NV so wurde der Begriff nicht gefunden.
Kommt #Bezug, so wird es den Bereich Tabelle3!$CM$2:blush:CN$102 irgendwie nicht geben, wenn in D11 eine 3 steht, denn das erzeugt Indirekt.
Hab dann deinen Tipp (von wegen keine Tabelle94) aufgenommen
und D11 in B11 geändert:
=SVERWEIS($B$11;INDIREKT(VERKETTEN(„Tabelle“;$B$11;"!$CM$2:blush:CN$102"));2:wink:
Die liefert aber auch „#BEZUG!“
wo ist D geblieben? Gibt es ein Blatt was „tabelle“ & den Inhalt von B11 heisst?
Was mache ich falsch ? Liegt es am Ausrufungszeichen vor dem
Bereich ?Müsste da vielleicht das Arbeitsblatt stehen ?
Ich kann was übersehen, klar, aber ich entdecke keinen Fehler in der Formel.
ich weiss nicht ob du schon wie von Thomas nehegelegt, die 50
Namen vergeben hast.
Mit dem nachfolgenden Makro geht es schneller und vor allem
auch schneller wenn du mal die Bereiche ändern musst.
Da mir bisher noch ein wenig schleierhaft ist wie/wo die Bereiche liegen habe ich das erstmal aussen vor gelassen
@Thomas Du vergibst den Namen „Tabelle1“ für einen Bereich?
Bereich markieren und im Namensfeld links neben der Barbeitungszeile ‚Tabelle1‘ eintragen - Excel hat da nichts dagegen (ob es Sinn macht steht auf einem anderen Blatt).
Auch dass die einzelnen Tabellenblätter im Grossteil aller Fälle immer noch so heissen wie sie von Excel eingefügt wurden halte ich nicht für einen sonderlich weisen Umstand (kann daran aber nicht viel ändern).
Das Excel da mitmacht wundert mich (ich mache das nicht mit,
weils mich verwirrt *gg*).
Das hingegen wollte ich nun keineswegs - ich habe die Bezeichnungen von Andy übernommen um nicht noch mehr Querstömungen ins Thema rein zu bringen.
Wüsste man nämlich wo genau die Bereiche liegen und könnte man dafür sorgen, dass sie sich immer in gleichen Abständen zueinander befinden, könnte man mit einer (relativ) einfachen benannten Funktion das Ganze ‚erschlagen‘ und könnte auch auf die ganzen INDIREKT()-Geschichten verzichten.
In der Hoffnung die Aktion ‚Licht ins Dunkel bringe ein wenig Erhellung‘
#NV deutet in aller Regel an, dass der Suchbegriff (in meiner
Formel der Wert in B11) nicht in der ersten Spalte der
Suchmatrix enthalten ist - Du sagest doch es wären die Zahlen
1 bis 100?
Ehrlich, jede Zeile ist in der ersten Spalte mit 1-100 nummeriert.
Mögen und wollen helfen hier leider nicht viel, denn Du zeigst
(schon wieder) nicht die exakte Formel die Du verwendet
hast…
Sorry, aber das ist die Formel. Ich habe Sie aus der Zelle in das Posting kopiert.
Oder ist es so, dass der Wert in D11 (die Tabelle in der
gesucht werden soll) nicht korrekt ist oder nicht alle Werte
enthält?
Doch, eigentlich ja. D11 liefert einen Zufallswert 1-100 und siehe oben: jede Zeile in der Zieltabelle ist von 1-100 nummeriert.
Sorry, aber mit solchen Fragmenten Ursachenforschung zu
betreiben gleicht schon fast dem Blick in die Glaskugel (meine
ist notabene aus Schweizer Bergkristall gefertigt)
Sorry, Sorry, Sorry, will nicht mit Fragmenten nerven !! Ich beantworte alle Fragen nach bestem Wissen und Gewissen - bin waaahnsinnig an einer Lösung interessiert.
Ich habe Dir deswegen mit separater Mail einen Gastzugang auf die Datei in meinem GMX-Mediacenter geschickt; Vielleicht magst Du ja noch mal draufgucken…?
vielen Dank für Tipp und Code, aber das ist mir zu hoch - ich verstehe zu viel an dem Code nicht und müßte rückfragen bzw. bräuchte Schritt-für-Schritt-Info, was da passiert; Das würde wohl diesen Rahmen sprengen. Sorry, zu doof. (und ich dachte immer, ich bin in Excel ganz fit - Asche auf mein Haupt…)
Ich werde meine paar Tabellen von Hand benennen, aber wenn ich bzw. die geilen Experten dieses Forums mein Hauptproblem gelöst haben,
werde ich versuchen, Deinen Tipp zu begreifen - interessiert mich schon…
Erstmal Danke
Andy
Sub NamenErzeugen()
> Dim N
> On Error Resume Next
> For Each N In ThisWorkbook.Names
> If N.Name Like "Ä\*" Then N.Delete
> Next N
> On Error GoTo 0
> For N = 1 To Worksheets.Count
> ThisWorkbook.Names.Add Name:="Ä" & N, RefersTo:="=" &
> Worksheets(N).Name & "!$D$1:blush:F$300"
> Next N
> 'ThisWorkbook.Names.Add Name:="Ö",
> RefersTo:="=Auswerten(""sverweis(a1;Ä""&$a$1&"";3;0)"")"
> End Sub
Die Formel =SVERWEIS($D$11;INDIREKT(„Tabelle“&$B$11);3;0)
funktioniert nämlich suuuper - ich hatte „nur“ bei den Tabellennamen Mist gemacht, indem Tabelle2 einen anderen Bereich als Tabelle1 umfasste (war eine Spalte zuviel markiert).
Habe ich beim Namen für Tabelle3 erst gemerkt…Sorry !
Jetzt funktioniert es prima !! Geil !! Wenn Du wüsstest, was mir das für Nachschlagearbeiten in Zukunft abnehmen wird (alles privat und hobby) …Dir und allen Experten vielen Dank !!
)
