Excel beißt nicht!

Schlagwörter: INDEX

Gib dem Zufall eine Chance! 10

25.07.2023 in Formeln und Funktionen / Statistik Stichworte: INDEX / SEQUENZ / SORTIEREN / SORTIERENNACH / ÜBERNEHMEN / ZUFALLSMATRIX Von: Martin Weiß

Zufallszahlen ohne Wiederholung generieren (und praktische Einsatzmöglichkeiten)

Die dynamischen Arrayfunktionen von Excel aus Microsoft 365 ermöglichen Vieles, was in der Vergangenheit nur umständlich oder auch gar nicht per Formel umsetzbar war.

Im heutigen Artikel werden ein paar dieser Funktionen kombiniert, um sehr elegant zufällige Zahlenreihen zu generieren. Ich möchte es aber nicht bei einem akademischen Selbstzweck belassen, sondern gleich noch ein paar Anregungen für einen möglichen Praxiseinsatz aufzeigen, die vielleicht auch für dich interessant sein könnten.

Ein kleines Geheimnis in der INDEX-Funktion 22

12.07.2022 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: INDEX / VERGLEICH Von: Martin Weiß

In der INDEX-Funktion schlummert mehr, als vielen Anwendern bekannt ist

Kürzlich hatte ich die Ehre und das Vergnügen, auf den Excel-Kompetenztagen in Fulda als Trainer aufzutreten (eine Veranstaltung, die ich jedem ambitionierten Excel-Anwender nur empfehlen kann). Im Kurs über die Verweisfunktionen war unter anderem die INDEX-Funktion ein Thema. Versierte Excel-Anwender nutzen diese Funktion in Kombination mit VERGLEICH gerne als den besseren SVERWEIS.

Deutlich weniger bekannt ist, dass INDEX in zwei Versionen auftritt. Als Matrixversion (das ist die „übliche“ Variante) und als Bezugsversion (das ist die eher unbekannte Variante). Letztere ermöglicht es, mit nur einer Formel gleich mehrere Bereiche auswerten zu können.

Wozu man das brauchen kann und wie das funktioniert, kannst du in diesem Artikel lesen.

Und so geht’s:

Suche über mehrere Spalten und Zeilen 6

22.03.2022 in Formeln und Funktionen Stichworte: AGGREGAT / INDEX / KKLEINSTE / SPALTE / ZEILE Von: Martin Weiß

Ein weiteres Einsatzgebiet für die Mauerblümchen-Funktion AGGREGAT

Manchmal steht man in Excel vor einer Aufgabe und denkt sich: Das ist doch ganze einfach! Aber auf den zweiten Blick stellt man fest: Mist, doch nicht so einfach…

Hast du zum Beispiel schon einmal versucht, nur mit Hilfe von Formeln einen bestimmten Wert in einer Tabelle zu finden? Also eine Suche, die sich sowohl über mehrere Spalten als auch über mehrere Zeilen erstreckt? Klingt eigentlich ganz einfach, oder?

Dann lass dich mal überraschen. Denn die Lösung führt über eine Funktion, die völlig zu Unrecht ein ziemliches Schattendasein fristet: AGGREGAT

Und so geht’s:

Liste nur mit eindeutigen Werten erstellen 125

26.10.2021 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: Dubletten / Duplikate / EINDEUTIG / FILTER / INDEX / VERGLEICH / ZÄHLENWENN Von: Martin Weiß

Mit Hilfe einer Matrixformel können aus einer Liste nur die eindeutigen Werte extrahiert werden (ohne Duplikate)

Vor einiger Zeit hatte ich in einem Artikel beschrieben, wie man in einer Liste mit Duplikaten die Anzahl der eindeutigen Werte bestimmt.

Der heutige Artikel geht einen Schritt weiter:
Ich möchte nicht nur wissen, wie viele eindeutigen Werte es gibt, sondern ich möchte diese gleich in einer eigenen Liste ausgeben. Also frei von Dubletten. Und zwar mit Hilfe von Excel-Formeln.

Und so geht’s:

Filter-Ausgabe für nicht zusammenhängende Spalten 28

13.04.2021 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: FILTER / INDEX / SEQUENZ Von: Martin Weiß

Mit einem Trick können bei der FILTER-Funktion beliebige Spalten ausgegeben werden.

Wenn du Excel aus Microsoft 365/Office 365 im Einsatz hast, dann kennst du vielleicht die dort verfügbare FILTER-Funktion. Falls nicht, empfehle ich dir, mal einen Blick in diesen Artikel zu werfen: https://www.tabellenexperte.de/eine-funktion-liefert-alle-treffer/ .

Denn damit kann man eine Tabelle sehr bequem nach einem beliebigen Kriterium filtern und die gefilterten Ergebnisse an anderer Stelle ausgeben lassen. Allerdings wirft die FILTER-Funktion immer sämtliche Spalten der Quelltabelle aus oder zumindest nur einen zusammenhängenden Spaltenbereich.

Was aber ist, wenn ich vielleicht nur ganz bestimmte Spalten in meiner Ausgabeliste benötige, die eben keinem zusammenhängenden Bereich entstammen?

Wie das geht, zeigt der heutige Artikel:

Bildauswahl per Dropdown-Liste

09.03.2021 in Formeln und Funktionen / Informationen / Nachschlagen und Verweisen Stichworte: Datenüberprüfung / INDEX / VERGLEICH Von: Martin Weiß

Sehr cool: Angezeigte Bilder dynamisch per Dropdown-Liste verändern!

Dropdown-Listen sind in Excel sehr beliebt, zumal sie das Leben des Anwenders sehr vereinfachen.

Heute zeige ich dir in diesem Zusammenhang einen Trick, mit dem Du ziemlich sicher deine Kollegen (oder deinen Chef) verblüffen wirst.

Wie lassen sich Bilder dynamisch verändern, abhängig von dem Wert, der aus einer Dropdown-Liste ausgewählt wird?

So geht unser kleiner Bildertrick:

Wie SVERWEIS, nur richtig! 14

22.12.2020 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: IDENTISCH / INDEX / VERGLEICH Von: Martin Weiß

SVERWEIS berücksichtigt keine Groß- und Kleinschreibung. Die hier vorgestellte Alternative schon!

Vor ein paar Wochen erreichte mich eine Leseranfrage zu einem interessanten Excel-Problem, dessen Existenz mir bis dahin gar nicht bewusst war (danke an dieser Stelle an Wilfried B.!)

Es ging darum, zu einer Abkürzung den dazu passenden Langtext aus einer Referenztabelle auszugeben. Das hört sich erst einmal nach einer klassischen SVERWEIS-Aufgabe an. Und jetzt kommt das große Aber….

Bei den Abkürzungen kommt es auf die exakte Schreibweise an. Das heißt, es muss zwischen Groß- und Kleinschreibung unterschieden werden. Und wie ich dann feststellen musste, kann der SVERWEIS damit leider nicht dienen.

Welche Formellösung dann doch zum gewünschten Ergebnis führt, das zeige ich dir in diesem Artikel.

Und so geht’s:

Eine Dateiliste in Excel erzeugen 40

18.03.2019 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: DATEIEN / INDEX / ZEILE Von: Martin Weiß

Mit einer alten Excel-4-Makrofunktion eine Dateiliste erstellen

Manchmal benötigt man in Excel eine Liste aller Dateien eines bestimmten Verzeichnisses. Leider bietet Excel dafür keine eingebaute Funktion. Trotzdem lässt sich diese Aufgabe auf unterschiedlichen Wegen lösen, zum Beispiel per Makroprogrammierung oder mit Hilfe von Power Query („Daten abrufen und Transformieren“).

Im heutigen Artikel zeige ich dir jedoch einen anderen Weg, bei dem wir doch nur ein paar Formeln verwenden werden.

Und so geht’s:

Wie ein SVERWEIS von hinten: Den letzten Wert finden 24

04.03.2019 in Formeln und Funktionen / Nachschlagen und Verweisen / Statistik Stichworte: INDEX / VERGLEICH / ZÄHLENWENN Von: Martin Weiß

SVERWEIS findet immer den ersten Wert. Aber wie findet man den letzten?

Jeder Excel-Anwender, der mit der SVERWEIS-Funktion vertraut ist, weiß, dass man damit immer das erste Ergebnis zu einem gesuchten Wert finden kann. Wenn jedoch das letzte Vorkommen gesucht wird, hilft der SVERWEIS nicht weiter. Hier muss man zu ein paar Tricks greifen.

Aber auch das ist kein großes Hexenwerk. In meiner hier vorgestellten Lösung werden wir ein paar andere Funktionen miteinander kombinieren und so das gewünschte Ergebnis erzielen.

Und so geht’s:

Forecast-Diagramm mit Pfiff 8

09.01.2019 in Diagramme / Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: INDEX / ISTZAHL / MAX / MIN / VERGLEICH Von: Martin Weiß

Mit ein paar Tricks lässt sich ein schlichtes Liniendiagramm mit einer dynamischen Box aufpeppen.

Die Idee zum heutigen Artikel habe ich mir bei meinem geschätzten Excel-Kollegen Andreas Thehos „ausgeliehen“. Der hat auf den Excellent Days 2018 ein Umsatzdiagramm gezeigt, das er mit ein paar Tricks um einen dynamischen Kasten erweitert hat. Durch diesen coolen Effekt lassen sich die Istwerte von den Planwerten sehr eindrucksvoll abgrenzen.

Wie das aussieht und wie man so ein Diagramm erstellt, zeigt der folgende Beitrag.

Und so geht’s:

Das Dreamteam INDEX+VERGLEICH aufgebohrt (Teil 2) 35

03.04.2018 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: INDEX / VERGLEICH Von: Martin Weiß

INDEX + VERGLEICH nochmal aufgebohrt: Ein kleiner Trick ermöglicht mehrere Suchkriterien.

Der erste Beitrag dieses kleinen Zweiteilers endete mit einem Cliffhanger. Ich habe dir zuerst den Mund wässrig gemacht und dich dann auf den zweiten Teil vertröstet. Nicht séhr schön…
Auf der anderen Seite, so funktionieren eben echte Thriller. Was Dan Brown und Co können, klappt auch mit Excel 😉

Genug der Vorrede. Hier kommt die Auflösung zu der Frage, wie sich mehrere Suchkriterien in einer INDEX-VERGLEICH-Formel verarbeiten lassen.

Das Dreamteam aufgebohrt: INDEX + VERGLEICH mit mehreren Kriterien 60

19.03.2018 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: INDEX / VERGLEICH Von: Martin Weiß

SVERWEIS war gestern: INDEX + VERGLEICH bieten maximale Flexibilität

Dass die Kombination der beiden Funktionen INDEX und VERGLEICH einem SVERWEIS um Längen voraus ist, habe ich vor langer Zeit schon in diesem Artikel beschrieben.

Wenn du bisher um diese zwei Funktionen einen großen Bogen gemacht oder sie vielleicht noch gar nicht gekannt hast, empfehle ich dir, dich ein wenig mit ihnen zu beschäftigen. Es lohnt sich wirklich, denn zusammen sind sie fast unschlagbar!

Einen kniffeligen Anwendungsfall dafür zeige ich dir in den nächsten beiden Artikel. Es geht um die folgende Aufgabe:

Für einen kleinen Report sollen die benötigten Daten aus einer großen Rohdatentabelle per Formel ausgelesen werden. Die Schwierigkeit dabei ist, dass die jeder auszulesende Wert vier verschiedene Kriterien erfüllen muss.

Das war ist dir noch zu abstrakt? Die folgenden Bilder machen es deutlicher.

Einen dynamischen Datenbereich ausgeben 32

27.11.2017 in Formeln und Funktionen Stichworte: INDEX / VERGLEICH Von: Martin Weiß

Durch geschickten Formeleinsatz lassen sich Datenbereiche dynamisch ausgeben

Beim Umgang mit größeren Datenmengen stellt sich oft das Problem, dass man nur einen bestimmten Teil der Daten für eine Auswertung benötigt. Excel bietet dazu verschiedene praktische Hilfsmittel, wie z.B. die Filterfunktionen.

Wenn die Quelltabelle Datumswerte enthält und ein bestimmter Datumsbereich dynamisch an einer anderen Stelle ausgegeben werden soll, muss man jedoch auf andere Lösungen zurückgreifen. Eine davon stelle ich heute vor.

Und so geht’s:

Alle Wege führen nach Rom… 14

18.09.2017 in Formeln und Funktionen / Logisch / Nachschlagen und Verweisen Stichworte: ERSTERWERT / INDEX / SVERWEIS / WAHL / WENN / WENNS Von: Martin Weiß

Sechs unterschiedliche Lösungen für ein Problem: In Excel führen viele Wege zum Ziel.

Wie so oft im Leben trifft der Spruch „Alle Wege führen nach Rom“ in ganz besonderer Weise auch auf Excel zu. Bei den allermeisten Problemen führt nämlich nicht nur ein Weg zur Lösung. Der Fantasie und Kreativität des Anwenders sind dabei nur selten Grenzen gesetzt.

Heute will ich anhand eines einfachen Beispiels demonstrieren, wie eine Lösung auf sechs unterschiedlichen Wegen erreicht werden kann. Dabei gibt es nicht „Richtig“ oder „Falsch“. Dieses Beispiel soll dich dazu inspirieren, auch einmal etwas um die Ecke zu denken, gewohntes Terrain zu verlassen und die für dich geeignete Variante zu finden.

Und los geht’s:

Excel-Tricks mit Steuerelementen (Teil 2) 23

30.05.2016 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: BEREICH.VERSCHIEBEN / Formulare / INDEX / Steuerelemente / VERGLEICH Von: Martin Weiß

Im Teil 2 der Artikelserie geht es um das Kombinationsfeld aus den ActiveX-Steuerelementen.

In der letzten Woche habe ich hier eine Möglichkeit vorgestellt, mit nur einem einzigen Dropdown-Feld eine ganze Liste in Excel zu befüllen.

Für den Einstieg ganz nett, aber noch nicht so richtig komfortabel.

Heute greife ich das Thema nochmal auf und zeige Dir, wie sich mit wenig Aufwand noch deutlich mehr aus den Steuerfeldern herausholen lässt.

Und so geht’s:

Besser als SVERWEIS: Alle Werte finden

28.03.2016 in Formeln und Funktionen / Nachschlagen und Verweisen Stichworte: INDEX / KGRÖSSTE / Matrixformel / WENNFEHLER / ZÄHLENWENN / ZEILE Von: Martin Weiß

Was der SVERWEIS nicht kann: Alle Treffer zu einem Suchkriterium liefern. So geht's trotzdem.

Der SVERWEIS ist mit Sicherheit eine der wichtigsten Funktionen, die dem versierten Excel-Anwender zur Verfügung stehen und sie gehört natürlich auch zu meinen Favoriten.

Die SVERWEIS-Funktion liefert bekanntermaßen zu einem Suchkriterium in der einen Tabelle den ersten gefundenen Treffer aus einer anderen Tabelle zurück. Was macht man aber, wenn der gesuchte Wert mehrmals vorkommt und man gerne alle Ergebnisse angezeigt bekommen möchte?

Hier ist der SVERWEIS leider hoffnungslos überfordert. Wie man so etwas trotzdem hinbekommt, zeigt der heutige Artikel.