Thema: Nachschlagen und Verweisen

Automatische Sortierung (Teil 2) 43

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: INDEX / VERGLEICH / ZÄHLENWENN / ZEILEN Von:
Mit ein paar Excel-Funktionen und einem kleinen Trick lassen sich auch alphanumerische Listen automatisch sortiert ausgeben.
 

In der letzten Woche habe ich dir gezeigt, wie man eine unsortierte numerische Liste automatisch sortiert an einer anderen Stelle ausgeben kann. Was ist aber, wenn die zu sortierende Spalte keine Zahlen, sondern Texte enthält? Dann funktioniert die vorgestellte Weg leider nicht.

Aber es wäre ja gelacht, wenn es für dieses Problem nicht auch eine einfache Lösung gäbe. Mit einem kleinen Trick und dem geschickten Einsatz der folgenden Excel-Funktionen bekommen wir auch Texte automatisch sortiert:

  • INDEX
  • VERGLEICH
  • ZÄHLENWENN

Und so geht’s:

Weiterlesen »

Automatische Sortierung (Teil 1) 38

 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik  Stichworte: INDEX / KGRÖSSTE / KKLEINSTE / VERGLEICH / ZEILEN Von:
Das automatische Sortieren von Excel-Listen ist mit den Funktionen KKLEINSTE und KGRÖSSTE gar nicht so schwer
 

Ein Leser hatte kürzlich wegen einer automatischen Sortierung einer Tabelle angefragt: Datensätze werden (unsortiert) in einem Arbeitsblatt eingegeben und sollen in einem anderen Arbeitsblatt automatisch sortiert angezeigt werden.

Da dies auch für andere Excel-Anwender interessant sein könnte, nehme ich diese Inspiration (danke, Daniel!) zum Anlass für einen entsprechenden Blogartikel.

Je nach Art und Inhalt der zu sortierenden Spalte können unterschiedliche Methoden zum Einsatz kommen.

Heute sehen wir uns die automatische Sortierung von numerischen Spalten an und nutzen dabei vor allem die folgenden Funktionen:

  • KKLEINSTE
  • KGRÖSSTE

Außerdem zeige ich dir, welche Stolpersteine es dabei zu beachten gilt.

Und so geht’s:

Weiterlesen »

Bedingte Summen in Excel (Teil 3) 13

 in Datum und Uhrzeit / Formeln und Funktionen / Mathematik und Trigonometrie / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / MONAT / SUMME / VERGLEICH Von:
Maximale Flexibilität bei der Bildung von Summen in Excel: Sowohl Zeilen als auch Spalten können variabel sein!
 

Letzte Woche habe ich dir hier gezeigt, wie man mit der geschickten Kombination der Funktionen SUMME und BEREICH.VERSCHIEBEN in Excel Werte unter bestimmten Bedingungen aufsummieren kann.

Dabei war die zu summierende Zeile variabel, die Spalten hingegen fix. Heute gehen wir noch einen Schritt weiter und wollen zusätzlich auch die zu summierenden Spalten möglichst flexibel halten.

Und so geht’s:

Weiterlesen »

Bedingte Summen in Excel (Teil 2) 4

 in Formeln und Funktionen / Mathematik und Trigonometrie / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / SUMME / VERGLEICH Von:
Summenbildung unter bestimmten Bedingungen kann manchmal etwas vertrackt sein. Die Lösung gibt's hier.
 

Das Aufsummieren von Werten in Excel-Tabellen unter bestimmten Bedingungen ist manchmal nicht ganz so trivial, wie es auf den ersten Blick aussieht.

Im Artikel „Alle Kriterien erfüllt? Bedingte Summen in Excel“ habe ich schon verschiedene Möglichkeiten aufgezeigt.

Manchmal ist die Aufgabenstellung aber etwas anders, so dass die gezeigten Wegen möglicherweise nicht zum Ergebnis führen. Wie bildet man z.B. eine Summe über mehrere Spalten, wenn die jeweilige Zeile variabel ist, also von einem bestimmten Kriterium abhängt?

So geht’s:

Weiterlesen »

Wenn’s auf den Einzelnen ankommt: Werte in Pivot-Tabellen ansprechen 37

 in Daten- und Analysetools / Nachschlagen und Verweisen  Stichworte: Pivot-Tabelle / PIVOTDATENZUORDNEN Von:
Mit der Funktion PIVOTDATENZUORDNEN kannst du gezielt Ergebnisse aus deiner Pivot-Tabelle herauspicken und weiterverarbeiten.
 

Wie man zu Analysezwecken seine Daten mit Hilfe von Pivot-Tabellen verdichtet, habe ich schon in den Artikeln Der beste Freund des Excel-Analysten: Pivot-Tabellen und Der Horror geht weiter: Pivot-Tabellen Teil 2 gezeigt.

Wie greift man jetzt aber gezielt auf einzelne Ergebnisse innerhalb einer Pivot-Tabelle zu, um diese woanders weiterzuverarbeiten? (Und warum sollte man so etwas überhaupt wollen?)

Hier bekommst du die Antworten darauf:

Weiterlesen »

Trans Am: Was ein amerikanischer Sportwagen und Excel gemeinsam haben 1

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Matrixformel / MTRANS / Transponieren Von:
Nicht für jeden Einsatzzweck geeignet, aber in bestimmten Fällen die einzige Lösung: Eine Funktion zum Vertauschen von Zeilen und Spalten.
 

Die etwas älteren und vor allem männlichen Leser werden sie noch kennen: die amerikanische Sportwagen-Legende Pontiac Firebird, auch Trans Am genannt.

Doch was hat der Flitzer, der unter anderem als K.I.T.T. aus der Serie „Knight Rider“ bekannt ist, mit Excel gemeinsam? Nun, es hat mit Kopieren und Einfügen zu tun, mit Matrix-Formeln und Arrays…

Lass dich überraschen!

Weiterlesen »

Das Ende der Verschachtelungen: Du hast die WAHL 2

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: WAHL Von:
Die WAHL-Funktion bietet oft einen eleganten Ausweg aus unübersichtlichen WENN-Verschachtelungen.
 

Wer kennt das nicht: Man erstellt eine Excel-Formel, in der man mehrere Bedingungen prüfen muss. Solange es sich nur um eine oder zwei Bedingungen handelt, bekommt man das mit ein paar verschachtelten WENN-Funktionen hin.

Spätestens jedoch bei der dritten Verschachtelung wird es aber unübersichtlich und man verstrickt sich hoffnungslos in einem Wust aus Klammern und Bedingungen. Eine andere Lösung muss her!

Etwas weniger bekannt als ihre „großen“ Brüder SVERWEIS, VERWEIS & Co. bietet die WAHL-Funktion oft einen eleganten Ausweg aus dem Verschachtelungs-Dilemma.

Und so geht’s:

Weiterlesen »

Das Beste kommt zum Schluß: Die letzte Zeile einer Excel-Tabelle 34

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: ANZAHL2 / INDEX / VERGLEICH Von:
Mit der simplen Kombination von ein paar Excel-Funktionen lässt sich der letzte Eintrag einer Tabelle finden - ganz ohne VBA.
 

Manchmal möchte man den jeweils letzten Eintrag seiner Excel-Tabelle zusätzlich noch an einer anderen Stelle ausgeben. Zum Beispiel ganz am Anfang einer sehr langen Tabelle, um den Eintrag jederzeit im Blick zu haben. Oder um ihn vielleicht in einem Formular ausgeben zu können.

Da die Tabelle aber ständig länger wird, sich das Ende als dynamisch nach unten verschiebt, kann man nicht einfach eine Verknüpfung zu einer festen Zeile erstellen, denn man kennt ja das Ende der Tabelle noch nicht.

Mit der geschickten Kombination einiger Excel-Standardfunktionen gibt es auch für dieses Problem ein Happy End. Und zwar ganz ohne VBA-Programmierung bemühen zu müssen.

Und so geht’s:

Weiterlesen »

Unikate gesucht: Listenabgleich in Excel 3

 in Formatierung und Layout / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Bedingte Formatierung / Matrixformel / SUMMENPRODUKT Von:
Mit der SUMMENPRODUKT-Funktion lassen sich in Excel Unikate in zwei unterschiedlichen Listen finden.
 

Die Verarbeitung von Listen gehört bekanntermaßen zu den Standardanwendungen von Excel. Wie man Dubletten in solchen Listen findet, habe ich im Artikel „Das doppelte Lottchen“ ja schon gezeigt.

Doch was macht man, wenn man stattdessen Unikate in zwei unterschiedlichen Listen identifizieren möchte? Sozusagen das schwarze Schaf in der weißen Herde? Den Osterhasen unter den Nikoläusen?

Eine geschickte Kombination der SUMMENPRODUKT-Funktion mit der bedingten Formatierung führt hier zum Ziel.

Und so geht’s:

Weiterlesen »

Ein echtes Dream-Team: INDEX und VERGLEICH 82

 in Allgemeine Excel-Tipps / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: INDEX / Matrixformel / MAX / SVERWEIS / VERGLEICH Von:
Mit der INDEX-Funktion lassen sich die Grenzen des SVERWEIS überwinden!
 

Eine bei vielen Anwendern oft verschmähte oder wenig verstandene Funktion führt völlig zu Unrecht ein ziemliches Schattendasein in der Excel-Welt: INDEX.

Dabei bietet diese unscheinbare Funktion das Potential, selbst die universelle SVERWEIS-Funktion blass aussehen zu lassen, wenn man ihr noch einen Partner zur Seite stellt.

Das neue Dream-Team heißt daher:

  • INDEX
  • VERGLEICH

Und so geht’s:

Weiterlesen »

Die Qual der Wahl: Mehrspaltige Dropdown-Listen 31

 in Formatierung und Layout / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Formulare / Steuerelemente / SVERWEIS Von:
Über einen kleinen Trick kannst Du mehrspaltige Dropdown-Listen erzeugen
 

Manchmal reicht ein einfaches Dropdown-Feld nicht, um zuverlässig den richtigen Wert auszuwählen. Nämlich dann, wenn z.B. in einer Namensliste Einträge mehrfach vorkommen. Welcher ist dann der Richtige?

Jetzt kommt er schon wieder mit seinen Dropdown-Listen daher!, wirst du vielleicht denken. Ich gebe zu, man könnte fast meinen, dass dies nach den beiden Artikeln hier und hier mein neues Steckenpferd ist. Nicht ganz.

Aber wer sich schon immer mal gefragt hat, wie man in einem Dropdown-Feld eine mehrspaltige Liste angezeigt bekommt, sollte sich diesen Artikel doch ansehen:

Und so geht’s:

Weiterlesen »

Hyperlinks und Schaltflächen: Einfaches Navigieren in Excel 25

 in Formatierung und Layout / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: HYPERLINK Von:
Mit der HYPERLINK()-Funktion dem Anwender einfache Navigationsmöglichkeiten bieten.
 

Die Welt des Internets hat sich auch in der Tabellenkalkulation breit gemacht. Mit sehr einfachen Mitteln ist es möglich, in Excel Hyperlinks zu erstellen, mit deren Hilfe man dem Benutzer eine Navigation innerhalb einer großen Tabelle anbieten kann.

Dabei lassen sich über die HYPERLINK()-Funktion einfache Textlinks erstellen. Wer es optisch etwas ansprechender möchte, kann natürlich auch eigene Schaltflächen in seine Excel-Tabelle einfügen und mit den gewünschten Hyperlinks versehen.

Und so geht’s:

Weiterlesen »

Dynamische Drop-Down-Listen (Teil 2) 50

 in Daten- und Analysetools / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: BEREICH.VERSCHIEBEN / Datenüberprüfung / Gültigkeit / Matrixformel Von:
Mit BEREICH.VERSCHIEBEN nicht nur die Drop-Down-Liste, sondern auch deren einzelne Elemente dynamisch anpassen.
 

Letzte Woche habe ich eine Möglichkeit gezeigt, wie man Drop-Down-Listen in Excel von Einträgen in anderen Zellen abhängig machen kann.

Die Auswahlliste verändert sich somit zwar dynamisch, die Listenelemente an sich sind aber in diesem Fall trotzdem statisch. Es gibt aber auch Anwendungsfälle, wo sich die einzelnen Elemente permanent verändern sollen und somit richtig dynamisch werden.

Als heutiges Beispiel dient mir ein kleines Haushaltsbuch, in dem ich meine Ausgaben erfassen möchte. Um mir die Eingabe zu erleichtern, sollen mir die jeweils letzten 5 verwendeten Ausgabebeschreibungen als Eingabehilfe in einer Drop-Down-Liste angeboten werden.

Und so geht’s:

Weiterlesen »

Dynamische Drop-Down-Listen (Teil 1)

 in Daten- und Analysetools / Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Datenüberprüfung / Gültigkeit / INDIREKT / Matrixformel Von:
Wie sich eine Drop-Down-Liste automatisch einer veränderten Umgebung anpasst.
 

Im Artikel „Eingabemöglichkeiten einschränken“ habe ich ja schon beschrieben, wie man eigene Drop-Down-Listen in Excel erstellt, um die Eingaben auf gültige Werte zu beschränken.

Im heutigen Artikel möchte ich noch einen Schritt weitergehen und die Drop-Down-Listen dynamisch gestalten. Und zwar dergestalt, dass die Eingabemöglichkeiten in einer Drop-Down-Liste vom zuvor gewählten Eintrag einer anderen Drop-Down-Liste abhängig sind.

Mein Beispiel ist wie immer sehr einfach gehalten: In meiner Tabelle wähle ich aus einer Drop-Down-Liste einen Automobilhersteller aus. Abhängig vom gewählten Hersteller möchte ich in der zweiten Liste nur noch Automodelle dieses Herstellers angeboten bekommen.

Und so geht’s:

Weiterlesen »

Neo lässt grüßen: Die Matrix in Excel 3

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Matrixformel / SUMMENPRODUKT Von:
Mit SUMMENPRODUKT() lassen sich Ergebnisse verdichtet darstellen.
 

Was will uns diese Blog-Überschrift sagen? Nun, es gibt in Excel sogenannte Matrix-Funktionen. Eine davon möchte ich heute vorstellen: SUMMENPRODUKT().

Diese mächtige Funktion ist leider bei vielen Anwendern nicht oder nur wenig bekannt. Das liegt vermutlich daran, dass sich der Nutzen und die Anwendungsmöglichkeiten nicht auf den ersten Blick erschließen.

Das möchte ich heute ändern: In folgenden Artikel werde ich dir zeigen, wie SUMMENPRODUKT() funktioniert und was man so alles damit anstellen kann. Aha-Erlebnisse sind garantiert!

Und so geht’s:

Weiterlesen »

Das Schweizer Taschenmesser in Excel: SVERWEIS! 18

 in Formeln und Funktionen / Nachschlagen und Verweisen  Stichworte: Matrixformel / SVERWEIS Von:
Listen abgleichen und Daten anreichern mit SVERWEIS ganz einfach.
 

Der heutige Beitrag behandelt eine mächtige und universell einsetzbare Funktion in Excel, die auch zu meinen absoluten Favoriten gehört: Den SVERWEIS!

Was ist der SVERWEIS? Ganz allgemein gesprochen kann man mit dieser Funktion einen bestimmten Wert in einer Liste nachschlagen. Damit lassen sich z.B. folgende Aufgabenstellungen lösen:

  • Prüfen, ob ein bestimmter einzelner Wert in einer Liste enthalten ist
  • Abgleichen zweier kompletter Listen gegeneinander
  • Anreichern von Daten in einer Liste mit den Inhalten aus einer anderen Liste

Hat man erst einmal die Funktionsweise verstanden, sind die Anwendungsmöglichkeiten fast so universell wie ein Schweizer Taschenmesser.

Der SVERWEIS durchsucht die erste Spalte einer Liste senkrecht von oben nach unten (daher das „S“ in SVERWEIS) und gibt bei einem Treffer den Wert aus einer Spalte rechts davon wieder.

Das war noch ein wenig zu abstrakt? Die folgenden Erklärungen und praktischen Beispiele verdeutlichen die Anwendung ganz bestimmt.

Weiterlesen »