In Excel können Sie ganz einfach an das rechte / linke / obere / untere Ende des Bereichs springen, der die gerade ausgewählte Zelle mit Inhalt umgibt: Doppelklicken Sie auf den rechten / linken / oberen / unteren Rand der Markierung der Zelle.
Schwupps – schon landen Sie in der gewählten Richtung in der letzten Zelle vor der nächsten leeren Zelle.
Und nun noch (falls Sie aus Österreich sind) einen angenehmen Feiertag, allen anderen eine schöne Woche.
Wollten Sie schon einmal die Adresse der aktuellen Zelle herausfinden?
Die lautet =ADRESSE(ZEILE();SPALTE())
Und heraus kommt $A$1, wenn diese Formel in A1 steht.
=ADRESSE(ZEILE(C2);SPALTE(E7)) liefert dementsprechend $E$2. Völlig unabhängig vom tatsächlichen Aufenthaltsort der Formel. Klar?
Braucht man nicht oft, aber wenn doch, dann ist es schön, wie einfach das geht!
Ich hab noch ein Schmankerl für die Funktion ZEILE().
Damit können Sie Zeilen nummerieren (ich hab ja mal versprochen, es gibt mehr als einen Weg, und ich verschwieg Ihnen auf Dauer nichts :-)).
Das geht so:
Sie markieren die Zellen, die Sie nummerieren möchten. Dann schreiben Sie die Formel =ZEILE() bzw. =ZEILE()-5, falls Sie z.B in Zeile 6 mit der Nummer 1 anfangen wollen. Jetzt drücken Sie die Tastenkombination Strg+Shift+Enter. Fertig.
Die Tastenkombination hat dazu geführt, dass die Formel als Matrixformel für alle markierten Zellen eingegeben wird. Sie erkennen das an den geschwungenen Klammern, mit denen sie nun angezeigt wird, und die Sie nicht eingegeben haben.
Versuchen Sie sich doch nun an einer Spaltennummerierung!
Ein Beispiel für die Verwendung von ZEILE() hab ich Ihnen schon am Montag geliefert. Ein weiteres: =WENN(B1=ZEILE();„entspricht der Zeilennummer“;„ist ganz anders“) liefert Ihnen die Information, ob das, was in B1 (B2, B3, …) steht, der aktuellen Zeilennummer der Funktion entspricht.
Sagen wir’s besser ganz allgemein: Wann immer Sie in einer Formel eine Zahl benötigen, die von Zeile zu Zeile um 1 größer sein soll, ist ZELLE() Ihr Freund.
Und SPALTE()?
Haben Sie sich nicht schon ab und zu geärgert, dass Sie für jede Spalte den SVERWEIS neu schreiben mussten, wenn Sie einen Bereich aus mehreren Spalten füllen mussten? Weil ja das dritte Argument, die Spaltennummer, beim AutoAusfüllen nicht „mitrutscht“? Aha. Klingelt’s schon? Schauen Sie sich das Beispiel an:
Ist doch schön:
=SVERWEIS($A11;$B$2:$I$7;SPALTE()-1;FALSCH) wurde genau so in die Zelle F11 geschrieben, damit
Diese Formel lässt sich nun nach rechts ausfüllen und nach unten ausfüllen, ohne dass Sie noch etwas daran herumbasteln müssen.
Sagen Sie nur, das mach Ihnen keine Freude!
… SPALTE() geben. Tut es auch.
Die Funktion SPALTE() liefert Ihnen die Nummer der Spalte, in der Sie diese Funktion aufrufen. Steht also in der Zelle D17 die Formel =SPALTE(), dann lautet das Ergebnis … 4!
Wozu das gut sein soll?
Schauen Sie auch morgen wieder vorbei 🙂
Weiter geht’s mit dem dynamischen Listenaufbau. Neue Challenge: wie kriegen wir die seit einer Woche angestrebte Lösung, ohne ganz links eine störende Hilfsspalte einzufügen?
Die als „Gut“ nummerierten Daten in der Quellliste (ich hoffe, ich muss das Wort nicht mehr oft schreiben – sieht komisch aus) hatten wir per SVERWEIS in unsere dynamische Liste übertragen. Der Schlüssel für den SVERWEIS war eine aufsteigende Nummer, die wir der Einfachheit halber in eine Hilfsspalte eingefügt hatten. Sie erinnern sich?
Wie kriegen Sie das nur hin, wenn Sie eine Formel schreiben wollen, die in der Zelle A5 die Zahl 1 verwendet, in der Zelle A6 mit 2 arbeitet, in der Zelle A7 mit 3, … Schreiben? Nein. Stellen Sie sich nur vor, Sie müssten siebzehntausend Zellen befüllen. Das wollen Sie nicht tippen 🙂
Also: wir brauchen auch hier etwas Dynamisches. Ein Vorschlag (und es gibt viele andere Möglichkeiten):
Sie können statt dem Verweis auf die Zahl mit der Nummer als Suchkriterium mit
ZEILE() arbeiten. ZEILE() liefert nämlich die Nummer der Zeile, in der die Formel grad eben steht. D.h. in einer Formel, die in A5 steht, liefert ZEILE() 5, … Für uns bedeutet das: Den Verweis auf die Nummernspalte ersetzen wir einfach durch die neue Funktion, ergänzen – für unser Beispiel – noch ein -4, da unsere Formeln in Zeile 5 beginnen, wir allerdings mit 1 anfangen wollen zu zählen, und schon sind wir fertig. 
Hilfsspalten? Pah!
Gestern gab’s WENNFEHLER – die Funktion ist erst ein paar Jahre alt, sie existiert seit Excel 2007. Falls Sie noch eine ältere Version verwenden (müssen, außer Sie gehören zu den selten gewordenen Menschen, denen die neuen Versionen suspekt oder schlicht zu teuer sind), dann müssen Sie sich ein bisschen mehr verrenken, um Fehler als Ergebnis eines Ausdrucks auszublenden.
Konkret geht das dann so:
=WENN(ISTFEHLER(SVERWEIS(A5;$F$5:$H$14;2;FALSCH));„“;SVERWEIS(A5;$F$5:$H$14;2;FALSCH))
Schauen Sie genau hin: der Ausdruck SVERWEIS(A5;$F$5:$H$14;2;FALSCH) taucht zwei Mal auf. Ein Mal innerhalb der ISTFEHLER-Funktion, ein zweites Mal als sonst-Ausdruck der WENN-Funktion. Das ist der Ausdruck, der oft gut ausgewertet werden kann, manchmal aber einen Fehler bringt.
Mit ISTFEHLER(Ausdruck) wird überprüft, ob in diesem konkreten Fall kein vernünftiges Ergebnis geliefert werden kann. Und der ISTFEHLER, dessen Ergebnis WAHR oder FALSCH, also ein Wahrheitswert sein kann, wird wiederum als Bedingung für die WENN-Funktion verwendet. Gibt es also einen Fehler, so wird er dann-Ausdruck zurückgegeben, sonst (wenn alles gut gegangen ist) das, was der Ausdruck liefert.
Probieren Sie’s an einer Formel aus, die in einer Ihrer Listen manchmal einen Fehlerwert zurückgibt und meistens gut geht.
Ach ja: das Ganze klappt nur für korrekte Ausdrücke, die halt einen Fehlerwert liefern, weil z.B. etwas nicht gefunden wurde, jemand Texte statt zahlen liefert, … Die Formeln selbst dürfen natürlich nicht fehlerhaft gebaut sein.
Ich hab’s gewusst: die Liste, die wir gestern dynamisch erzeugt haben, ist gut geworden, aber Ihnen nicht gut genug. Die #NV-Einträge, die wir bekommen, wenn die Funktion SVERWEIS keinen passenden Eintrag in ihrer Quellliste findet, gefallen Ihnen nicht.
Dann tun wir sie eben weg. Aber nicht einfach mit der Entf-Taste, das würde ja die ganze Dynamik zerstören. Wir blenden sie nur aus, dann sind sie zwar unsichtbar, aber die Formeln sind immer noch lebendig. Dazu hängen wir der Funktion SVERWEIS von gestern ein Tarnmäntelchen um. Es heißt WENNFEHLER und wurde 2007 erstmals genäht. (Sie arbeiten mit einer älteren Version? Wir reden morgen weiter.)
=WENNFEHLER(SVERWEIS(A5;$F$5:$H$14;2;FALSCH);„“)
Das bedeutet: Wenn das erste Argument der WENNFEHLER-Funktion (hier das Gesamtpaket SVERWEIS(A5;$F$5:$H$14;2;FALSCH), das wir gestern erzeugt haben) einen Fehler zurückgibt (also in unserem Fall nichts Passendes gefunden wird), dann wird das zweite Argument zurückgegeben (hier „“, also eine leere Zeichenfolge; Sie können natürlich auch beliebige andere Ausdrücke verwenden); gibt es keinen Fehler, so wird einfach das geliefert, was im ersten Argument herauskommt.
Oder anders ausgedrückt: Im Fehlerfall wird nicht der Fehler, sondern das zweite Argument zurückgegeben. Sonst das, was man ohnehin berechnen wollte.
Jawohl! So sieht das gut aus!
Unser „wir-bauen-uns-eine-dynamische-Liste“-Beispiel gedeiht. Heute füllen wir die Fälle, die wir gestern in unserer Quellliste identifiziert haben, in unsere Ergebnisliste ein.
Dazu machen wir uns eine Hilfsspalte. (Sie wollen keine Hilfsspalte? Dann bleiben Sie dran. Wir lösen das auch noch auf einem anderen Weg.) In die Hilfsspalte schreiben wir fortlaufende Zahlen, beginnend bei 1. Händisch, oder – viel besser! – Sie schreiben 1, dann 2, dann markieren Sie beide Zahlen und ziehen die Markierung am AutoAusfüllen-Kreuzerl nach unten, bis zur maximal möglichen Zahl an Einträgen.
Erledigt. Jetzt geht’s ans Daten-Abschreiben. Natürlich nicht von Hand, wo denken Sie hin? Wir verwenden die soeben erstellten Zahlen als Schlüsselargument für einen Verweis auf unsere Quellliste. Mit der Formel =SVERWEIS(A5;$F$5:$H$14;2;FALSCH) lassen wir uns den ersten Wert aus der zweiten Spalte im Bereich F5:H14 liefern, bei dem im Bereich F5:F14 1 (der Inhalt von A5) steht (denn die Funktion SVERWEIS sucht immer in der ersten Spalte des Suchbereichs). Das FALSCH sorgt dafür, dass wir bei 4, 5, … den hübschen Fehlerwert #NV (für nicht vorhanden) bekommen. Damit wissen wir: Drei Treffer gab’s, dann war Schluss.
Noch einmal die Funktion SVERWEIS zum Mitschreiben:
Wir haben etwas vorbereitet – Daten aus einer Liste sollen dynamisch eine andere Liste generieren.
Dazu müssen wir nun die „Guten“ identifizieren und nummerieren – damit wir später den ersten, zweiten, dritten, … Treffer in unsere dynamische Liste übernehmen können.
Die „Guten“ in der Originaltabelle sind die, bei denen der Wert in der Spalte D dem Wert des ausgewählten Bundeslands entspricht (die Zelle A1, die wir im vorigen Beitrag sel_Country genannt haben). Die Formel, die uns diese Treffer nummeriert, geben wir in eine Hilfsspalte ein, die links von den auszuwählenden Listenspalten sein muss:
Sie lautet =ZÄHLENWENN($D$5:D5;sel_Country), und bewirkt, dass das erste Vorkommen von OÖ (wurde im Auswahlfeld sel_Country selektiert) mit 1 nummeriert wird, das zweite mit 2, das dritte mit 3. Alle Einträge davor sind 0, alle danach sind zwar ebenfalls 3, aber das kümmert uns nicht – Sie sehen morgen, warum.
Heute ist das ZÄHLENWENN Gegenstand unserer Überlegungen. Hier zählt es Zelle für Zelle, wie oft in der Spalte D bisher schon OÖ stand. Das „bisher schon“ bewirke ich mit dem Zellbezug $D$5:D5. Damit wird in der Zelle E5 nur die Zelle D5 betrachtet, in der Zelle E6 dann schon D5:D6, in der Zelle E7 D5:D7, … Also: Der Beginn des Bereichs, in dem alle OÖ gezählt werden, ist immer derselbe, das Ende gleitet nach unten.
katharinakanns
Soprani Software 