Excel 2016: Datenanalyse

Einzelwerte mit der SVERWEIS-Funktion finden

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Einen bestimmten Wert aus einer Liste zu ermitteln, ist ziemlich einfach, wenn Sie die SVERWEIS-Funktion einsetzen. Das funktioniert allerdings nur dann, wenn das Suchkriterium in der ersten Spalte der Liste steht.

Transkript

Sicherlich werden Sie in diesen Listen häufig zusammenfassende Funktionen brauchen, also beispielsweise den Mittelwert der Zeit oder die Anzahl der Teilnehmer aus einem Bundesland oder so was. Aber Sie können mit Excel auch gezielt einzelne Werte heraussuchen lassen, beispielsweise um eine Urkunde zu schreiben. Die würde ich natürlich im wirklichen Leben auf einem anderen Blatt schreiben, vielleicht sogar mit einem Wert "Serienberief", aber es geht auch hier und hat den Vorteil, dass Sie im gleichen Programm bleiben und nicht Querverbindungen brauchen. Also werde ich hier oben eine Urkunde simulieren. Ich würde das natürlich auf dem zweiten Blatt machen wegen der optischen Gestaltung, aber hier ist es jetzt schneller und einfacher zu sehen. Und es muss auch nicht in der ersten Spalte sein, obwohl ich mich gleich auf die Startnummer beziehe und zwar jetzt erst mal völlig beliebig, also ich will was über den Teilnehmer Nummer 5 wissen und das heißt nicht, dass das hier stehen muss. Das ist eine völlig beliebige Zelle. Die werde ich mal ein wenig farbig hinterlegen, dass Sie sehen, das ist diejenige, die sozusagen alles vorgibt. Und ich möchte jetzt wissen, wie heißt der Teilnehmer Nummer 5. Und aus Platzgründen werde ich das hier reinschreiben, weil das da einfach eine sehr schmale Spalte ist, auch das ist völlig frei. Die Funktion, die ich jetzt brauche, ist genau genommen eine von beiden. Es gibt einen WVERWEIS und einen SVERWEIS, einen waagrechten Verweis und einen senkrechten Verweis. Den W-Verweis habe ich in den letzten [] wirklich noch nie benutzt. Ich benutze immer den senkrechten Verweis, weil der nämlich Listen in senkrechten untereinander stehenden Zeilen und nicht in Spalten sehen will, das ist eigentlich der Normalfall der Liste, also eine SVERWEIS-Funktion, senkrechter Verweis, die ich mit Tab auswähle. Und ich möchte zuerst wissen, was soll ich denn suchen. Das Suchkriterium ist das, was ich hier gelb markiert habe und danach, nach dem Semikolon wo drin soll ich suchen. Die Matrix ist typischerweise so eine Liste. Viele gehen jetzt hin und markieren diese Liste. Würden jetzt reinklicken und das irgendwie markieren, das kann man ein bisschen einfacher machen. Wenn Sie Strg+Umschalt einmal rechts, einmal runter klicken, dann sehen Sie hier schon, es handelt sich hier um eine Layout-Tabelle, den eigenen Namen hat, in diesem Fall sogar einen Bereichsnamen, der erkannt wird. Ansonsten stündet also was wie A7-I140. Das geht auch. Im Zweifelsfall würde ich Bereichsnamen bevorzugen. Da dieses eine Layout-Tabelle ist, ist das viel einfacher. Ich muss nur wissen, wie sie heißt. Das habe ich mir vorher schon angeguckt. Bei mir fangen alle Layout-Tabellen mit "tbl" irgendwas an, und damit habe ich sie jetzt schon. "tblMarathon" wird mir vorgeschlagen. Es steht jetzt hier ein bisschen tiefer darunter, also mit der Tab-Taste kann ich das auswählen, und dann muss ich ihm noch erklären, dass ich gerne die Daten hätte. Das ist nicht ganz intuitiv. Da muss man jetzt eckige Klammer aufmachen und kriegt die eigentlichen Inhalte und zwar nur die Daten ohne Kopfzeile, also diese SVERWEIS-Listen haben nur Daten, Doppelklick, und dann kann ich die eckige Klammer wieder zumachen. Man sieht das hier so ein bisschen einer Markierung. Es ist der richtige Bereich und wird vor allem erweitert, wenn ich an diesem Winkel ziehe. Dann nach dem Semikolon muss ich jetzt sagen, die wievielte Spalte ich sehen will. Die Zeile habe ich sozusagen schon, nämlich die mit der Nummer 5, die wir jetzt gerade nicht mehr sehen hier. Und jetzt muss ich sagen, in dieser Zeile mit der Nummer 5 hätte ich gern eins, zwei, drei, die dritte Spalte. Da steht der Nachname. Ich wäre theoretisch fertig, aber denn es ist unklar, was ich mache, wenn es die 5 nicht gibt, nämlich den nächstbesten Treffer oder sage ich dann, es gibt gar nichts. Dann kommt dieses Doppelkreuz NV. Ich würde jetzt wirklich nur Treffer finden, also nach dem Semikolon sage ich, das ist historisch bedingt ein merkwürdiges Argument, das heißt falsch, wenn ich den Treffer haben will, also es muss eine genaue Übereinstimmung sein, Klammer zu und RETURN. Sie sehen schon, zu Nummer 5 wird mir jetzt zurückgegeben "Pfleger". Wenn ich eine andere Nummer schreibe, es ist eine Funktion, die sofort berechnet wird, dann kriege ich sofort den richtigen Wert. Also die SVERWEIS-Funktion ist enorm praktisch um anhand einer Kennziffer, die typischerweise in der ersten Spalte steht und sortiert ist, diese Werte ermitteln kann. Und ich könnte jetzt entweder in einer zweiten Zelle den Vornamen anzeigen oder, das finde ich persönlich viel praktischer, in der gleichen Zelle. Ich kopiere das gerade mit Strg+C, verkettet mit einem Stückchen Text, was aus Komma und Leerzeichen besteht und in Gänsefüßchen sein muss, wiederum verkettet mit erst mal dem gleichen reinkopiert, aber nicht die Spalte 3, sondern die Spalte 2, nämlich den Vornamen, und nach dem RETURN sehen Sie, das ist ohne Bedenken auch zusammenzusetzen. Ich kann mir hier an dieser Stelle sehr bequem die komplette Adresse ermitteln, wenn ich zum Beispiel so was wie einen Brief in Excel erzeugen will. Dann wird das gleich die Adresse, ansonsten steht hier drüber Urkunde "Wir gratulieren", dann kommt dieser Eintrag und zu seiner Teilnahme am Marathonlauf oder irgendwie so was in der Richtung. Das geht auch mit Umbruch. Dafür muss ich hier mal ein klein bisschen Platz schaffen, und zwar möchte ich dazu den Ort nennen. Die Postleitzahl fehlt hier zwar, aber das soll egal sein. Also ich kann wiederum verkettet mit dem gleichen, wenn Sie das Gefühl haben, da ist gerade was verschwunden, wenn ich das reinkopiert habe. Dann mache ich die Edit-Zeile hier ein bisschen größer, also das Gleiche reinkopiert, aber eins, zwei, drei, vier, die Spalte mit dem Ort hätte ich gern, und da ist die vier. Mit dem RETURN sehen Sie, der Ort klebt so ein bisschen dran, er ist drin, aber noch nicht richtig. Da muss also noch ein Umbruch dazwischen. Ein Umbruch muss innerhalb von Gänsefüßchen stehen, Gänsefüßchen auf, und er ist in Excel mit Alt+Return zu machen. Sie sehen hier schon Gänsefüßchen zu, und dann natürlich [unverständlich] zum Verketten. Fängt gut an, endet aber noch nicht gut. Wenn ich das mit RETURN bestätige, dann sieht das so ein bisschen enttäuschend genau so aus wie vohrer. Hier oben sehen Sie schon Alt+Return, der Umbruch ist schon drin, aber die Zelle erlaubt es noch nicht. Ich kann entweder hier oben drauf klicken, oder wenn Sie das an dieser Stelle einmal offiziell sehen wollen mit "Zellen formatieren", das ist das, was bei Ausrichtung hier Zeilenumbruch heißt. Nach dem "OK" besitzt diese Zelle sozusagen die Erlaubnis, den Zeilenumbruch darzustellen. Der war schon drin, aber Sie hatten den nicht dargestellt. Das sind immer zwei Schritte, wo man ein bisschen überrascht guckt, wenn man den ersten richtig macht und der zweite noch fehlt. Sie können also hier sehr schön, sehr einfach anhand dieser Nummer die komplette Adresse, wenn es die Postleitzahl gäbe, zusammenstellen. Und das alles basierend auf der Nummer, ich nehme nun mal die vorderen Nummern, weil an der Stelle einfach die Kontrolle leichter ist, also hier ist es jetzt Rene Witte aus Daasdorf am Berge. Und es geht auch mehrstufig, denn diese ist eine spezielle Layout-Tabelle, die hat immer so ein paar Vorteile. Aber vielleicht möchten Sie normale Listen nehmen. Deswegen habe ich hier schon eine zweite Liste vorbereitet, und das ist eigentlich eher der Normalfall, dass diese Nachschlagelisten, auf die der SVERWEIS zugreift, auf einem anderen Blatt stehen. Die ist geringfügig anders organisiert. Sie hat in der ersten Spalte die wesentlichen Elemente, die es nachzugucken gilt, und in der zweiten sage ich mal die Formulierung dazu. Die sind sortiert. Es sind nicht einfache Nummern, sondern sind Zeiten. Und Sie haben auch Lücken. Sie sind nicht wie eben eins, zwei, drei lückenlos sozusagen, sondern sie haben ausdrücklich Lücken. Je nachdem in welcher Zeit derjenige gelaufen ist, will ich das bewerten. Also ich mache das mal in der Zelle daneben. Ich schreibe hier einen SVERWEIS. Der basiert -- Das kann man nicht noch mal kopieren. Ich habe das noch in der Zwischenablage mit Strg+V. Basiert immer noch auf diesen hier, will aber zwei, vier, sechs, acht, neun die Zeit wissen, also hier eine 9. Basiert immer noch auf A2, und nach dem RETURN sehen Sie hier die Zeit, zwar noch nicht hübsch, aber schon die richtige Zahl. Und ausgehen von dieser Zahl möchte ich jetzt hier einen SVERWEIS machen, der sich auf diese Beurteilung bezieht. Damit ich nicht hier mühsam markieren muss, gibt es bereits einen Bereichsnamen, also ich hatte hier markiert und mit RETURN bestätigt. Dann wird dieser Bereichsname darauf angewendet. Das wird mir gleich helfen. Und jetzt muss ich nun mal wirklich neu schreiben. SVERWEIS. Das Suchkriterium ist jetzt diese Uhrzeit oder besser gesagt diese Laufzeit. Semikolon. Jetzt brauche ich die andere Liste, und weil den Bereichsnamen hat, kann ich mit F3 einfach die Liste aller Bereichsnamen anbieten und ich nenne solche SVERWEIS nach Guckbereich immer Liste+irgendwas, also hier ListeBeurteilungen. Dort hätte ich gern die zweite Spalte. Semikolon, und dieses sozusagen sehr seltene Mal ist das letzte Argument wirklich mal wahr, denn diese Zeit werden Sie dort nicht finden. Dann nimmt Excel die nächst passende, und nach dem Klammer zu, RETURN sehen Sie, dass das hier entsprechend bewertet wird. Ich kann mal wieder hier wechseln. Nehmen wir eine Fünf, werde es noch besser. Wo haben wir mal, in den sie ganz so gut ist, die zwei. Dann sehen Sie, das wechselt immer passend dazu. Dieses ist also sogar ein SVERWEIS, der auf dem Ergebnis von einem anderen SVERWEIS basiert. Falls Sie Spaß dran haben, [] das sogar eingeschachtelt, also statt E2 würden Sie das reinkopieren, was hier diese ganze Formel ausmacht. Da steht also =SVERWEIS( und so weiter, Klammer zu, und dann kommt ;ListeBeurteilungen, wenn man das ein bisschen kompakt haben möchte. So funktioniert er aber auch wunderbar. Diese Zelle kann hier irgendwo außerhalb liegen. Der SVERWEIS ist also perfekt geeignet, um aus einer solchen Liste anhand einer Kennzahl, meistens ohne ID die Daten zu ermitteln und bei Bedarf zusammenzusetzen.

Excel 2016: Datenanalyse

Werten Sie Ihre Listen, Tabellen und Datenbanken mit Hilfe von Microsoft Excel 2016 nach allen Regeln der Kunst aus.

3 Std. 6 min (38 Videos)
Derzeit sind keine Feedbacks vorhanden...

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!