Wie kann es einrichten das sich die Matrix von einem Sverweis automatisch vergrößert wenn man neue Daten in die Tabelle einträgt?
ich möchte es so einrichten, dass die Matrix erweitert wird wenn man neue Daten in die Spalten B und C eingibt.
(bitte mit Erklärung)
5 Antworten
Eine andere Möglichkeit wäre, den Bereich im SVERWIES dynamisch zu gestalten.
Dazu muss man erst mal die letzte Zeile ermitteln - dazu darf, wenn es ohne VBA sein soll, unter eine der Spalten nie etwas anderes stehen denn diese Funktion ermittelt die letzte gefüllte Zelle einer Spalte:
=Vergleich("";B:B;-1)
Das Ergebis kann man dann für einen dynamischen Adressraum in der SVERWEIS-Funktion verwenden:
=SVERWEIS(F2;INDIREKT("$B$3:" & ADDRESSE(VERGLEICH("";B:B;-1);3;1));2;0)
- Vergleich gibt die die letzte gefüllte Zelle in Spalte B - in Deinem Beispiel "8"
- Adresse macht aus der Zeile ("8"), aus der Spalte (numerisch, hier "3" => C) und der Option 1 die Adresse $C$8
- "$B$3:"& setzt das zu dem Text "$B$3:$C$8" zusammen
- "Indirekt" macht aus dem Text wieder einen Bezugswert (siehe unten)
Du wirst nicht drum herum kommen, eine Spalte über einen größeren Bereich zu "überwachen", wenn Du das ohne späteres einfügen von Zeilen innerhalb eines Bereiches o. ä. umsetzen willst.
Mein Tipp dabei wäre, nicht B:B zu verwenden, sondern sich Gedanken zu machen, wie lang die Tabelle wohl werden wird.
Das erwartete Ergebnis mit 5 mutliplizieren, auf volle 1000 aufrunden, dann steht da wahrscheinlich sowas wie "=Vergleich("";$B$3:$B$12000;-1) --> das beschleunigt die Formel (merkst Du zugegebenermaßen nur, wenn Du noch andere Formeln wie "Zählenwenns" und so weiter verwendest, dann summieren sich Millisekunden zu Minuten.... )
(Indirekt:
Wenn du in zelle A1 reinschreibst "B1", und dann dann in A2 schreibst "=A1", dann erhälst Du "B1" als Ergebnis. Wenn Du "=indirekt(A1)" schreibst, bekommst Du den Zellwert von B1 angezeigt.)
gruß
xthen
Also ich jedenfalls hab auch aus dieser AW wieder was gelernt (nämlich dass ich mit =Vergleich("";B:B;0) die erste freie Zelle einer Spalte ermitteln kann (da darf übrigens durchaus noch was drunter stehen, Vergleich() nimmt nur die erste Übereinstimmung, nur oberhalb darf keine weitere freie Zelle auftauchen). Obwohl naheliegend, bin ich auf diese Idee noch nicht gekommen!
Ich jedenfalls bin dankbar für solche Anregungen, und wenn der Frager nichts damit anfangen kann, dann siehe den Kommentar in meiner Antwort...
Das würde dann funktionieren, wenn Du den Bereich in eine (Layout)Tabelle umwandelst. Markiere den Bereich und gehe über Einfügen > Tabelle. Vergib anschließend unter "Entwurf" einen Tabellennamen (korrekterweise schreibt man mit tbl vorangestellt, also tblEingabe).
Nun kannst Du den SVERWEIS wie gewohnt anlegen und der Bereich wird automatisch erweitert. Gib die Funktion nicht über die Tastatur ein sondern teilweise mit der Maus, dann erkennst Du den Unterschied zu vorher recht gut!
noch Fragen oder ich war nicht deutlich genug? Melde Dich
Danke für die ausführliche und schnelle Antwort. Ich habe es eben ausprobiert und es funktioniert wie gewünscht.
Warum Fragst du nicht gleich die komplette Spalte an? =INDEX(C:C;VERGLEICH(F2;B:B;0))
Oder mit dem unsäglichen und von mir so gehassten SVERWEIS
=SVERWEIS(F2;B:C;2;0)
Bereiche erweitern kannst du mit
=BEREICH.VERSCHIEBEN(B3;;Anzahl2(C:C)-1;2)
Also im Falle deines SVERWEISES
=SVERWEIS(F2;BEREICH.VERSCHIEBEN(B3;;Anzahl2(C:C)-1;2);2;0)
PS: Bereiche Erweitern kannst du mit
=BEREICH.VERSCHIEBEN(B3;;Anzahl2(C:C)-1;2)
Also im Falle deines SVERWEISES
=SVERWEIS(F2;BEREICH.VERSCHIEBEN(B3;;Anzahl2(C:C)-1;2);2;0)
Du musst beim festlegen der Matrix nicht den definierten Bereich auswählen, sondern die ganzen Spalten. Dazu klickst du mit der Maus in den Dpaltenkopf B bis C
andere Möglichkeit:
Füge eine/mehrere Spalte/n zwischen B und C ein und kopiere dann die jetzige Spalte C wieder in das jetzt neue C. Lass in Zukunft immer eine Leerspalte am Ende, dann kannst du beliebig oft erweitern und musst dann nicht mehr zurückkopieren.
Sind in Spalte C Formelbezüge -egal, ob zum kreieren der Werte oder zum Weiterrechnen (ausser dem S-Verweis natürlich), dann musst Du die Werte zurückschieben aber ohne die erste und die letzte Zeile, deren Formeln musst Du individuell rüberbringen
Ich mach das gleiche auch mit Zeilen und zwar von Vornherein, die letzte Zeile hat bei mir meist den Endwert
10^99 oder -bei gemischten Daten - ZZZZ.
Für jeden neuen Datensatz wird eine Zeile eingefügt.
mit index(..;Vergleich(...)) kann man sich diese Prozedur sparen, da ist es nämlich egal, die Spaltenadresse passt sich an, wenn Du eine gesamte Spalte verschiebst und da muss die Leitspalte auch nicht links stehen, Du beziehst dich da immer nur auf (ggf Teilstücke) einzelner Spalten. (Beim SVerweis passt sich ja das 3 Argument =Spaltenversatz nicht selbsttätig an.
Beispiel: anstatt =SVerweis(F3;B2:C8;2;Falsch) schreibst du
=index(C2:C8;Vergleich(F2;B2:B8;Falsch)) mit demselben Ergebnis (und anstatt FALSCH tuts auch 0)
Ich würde Dir übrigens raten, solche Bereiche je nach Kontext $ zu setzen, also
=index(C$2:C$8;Vergleich(F2;$B$2:$B$8;Falsch))
Das kannst Du dann nach rechts kopieren und C&2:C$8 passt sich in der Kopie an zu D$2:D$8 ($B bleibt dagegen $B). Auch nicht komplizierter, aber viel flexibler! (hat sich an den Schulen anscheinend noch nicht herumgesprochen, warum auch immer! Dabei ist es schon sehr alt, der SVerweis wäre vollkommen überflüssig!))
Vergleich liefert die n-te Zelle, mit index wird dieses n als n-te Zelle in der Findespalte ausgelesen und Du siehst an der adresse sofort, woher die Werte kommen! (Bei breiten Tabellen weiß man nämlich kaum noch, ob zB 12 K,L oder M bedeutet (es ist L) und im Bereich ab AA wirds dann echt schwierig, das noch zuzuordnen (ich hatte im Beruf Tabellen bis ca AF, wovon aber für mich nur wenige Spalten relevant waren, ausserdem Tausende von Zeilen. Dummerweise kannte ich das Index/Vergleich damals auch noch nicht...)
was soll denn der umständliche Kram? Natürlich ist es eine "andere Möglichkeit"
Vielleicht ist es für große Datenmengen hilfreich, aber hast Du Dir auch nur eine Sekunde den Datenbereich angesehen?
Es ist ja schön, dass Du Dich so gut auskennen magst, aber begreift hier als Antwortende doch mal bitte endlich, dass Menschen die Fragen stellen nicht so tief in der Materie sind. Wer mit Deiner Antwort etwas anfangen kann, der hätte diese Frage niemals stellen müssen.
Ich gebe Dir einen gut- und ernstgemeinten Rat: nicht mit Wissen trommeln, sondern zweckmäßig antworten!
ich versuche meine Antworten so zu gestalten, dass der Fragende sich weiterentwickeln kann. Mag sein, dass ich mich manchmal in deren Fähigkeiten verschätze, für den Fall haben sie ja Deine Antwort...
Was den kleinen Datenbereich angeht, hast Du recht, aber meist sind das Schulungsbeispiele, die nur das Prinzip nahebringen sollen. Die Praxis später schaut anders aus! (und die Leute stehen dann oft hilflos da!)
Ich kenne mich in Excel recht gut aus (25 Jahre Erfahrung, auch beruflich), aber diese Beschreibung ist für mich kaum nachvollziehbar. Ein Anfänger ist damit wahrscheinlich mehr als überfordert.
mag sein, dass meine didaktischen Fähigkeiten nicht so gut sind wie mich die Zahl der Hilfreichsten Antworten vermuten ließ, kann auch sein, dass ich im Lauf der Zeit vieles leichter einschätze als früher, aber es ist ja hier jeder frei zu entscheiden, ob er willens ist, eine Antwort nachzuvollziehen oder ob er sie ignoriert.
Ich weiß also nicht, warum mich hier manche Leute so angiften müssen (Dein Beitrag ist ja noch gemäßigt im Vergleich zum anderen...)
Und da ich -übrigens leider erst in diesem Forum- gelernt habe, um wieviel besser die Kombi
=Index(...;Vergleich(...)) ist, propagiere ich das eben!
auch an Dich die Frage: was soll denn der umständliche Kram? Natürlich ist es eine "andere Möglichkeit"
Es ist ja schön, dass Du Dich so gut auskennen magst, aber begreift hier als Antwortende doch mal bitte endlich, dass Menschen die Fragen stellen nicht so tief in der Materie sind. Wer mit Deiner Antwort etwas anfangen kann, der hätte diese Frage niemals stellen müssen.
Ich gebe Dir einen gut- und ernstgemeinten Rat: nicht mit Wissen trommeln, sondern zweckmäßig antworten!