QUBA – Formel-Referenz: alle Funktionen mit Aufbau und Einsatzzweck

Alle klausurrelevanten Tabellenblattfunktionen auf einen Blick — Syntax wie sie aufs Papier gehört (deutsche Namen, Semikolon als Trenner, eckige Klammern = optionale Argumente, werden nicht mitgeschrieben). Spalte „Nachschlagen": unter diesem Begriff steht die gelöste Aufgabe im Stichwortverzeichnis.

Verweisen und Nachschlagen

Funktion	Aufbau	Was sie tut	Wann benutzen	✚ Stärke / − Schwäche	Nachschlagen
XVERWEIS	`=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])`	Sucht einen Wert und gibt den zugehörigen aus einer anderen Spalte/Zeile zurück	Der Standard für jedes Nachschlagen; Vergleichsmodus −1 = nächstkleinerer Wert für Intervalle (Notenschlüssel)	✚ ersetzt SVERWEIS und INDEX/VERGLEICH, Default = exakt · − erst seit 2020 — Altversionen kennen ihn nicht	XVERWEIS
SVERWEIS	`=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])`	Alter senkrechter Verweis, Suchspalte muss links stehen	Nur wenn die Klausur ihn explizit verlangt (Altbestand); sonst XVERWEIS	✚ überall bekannt und kompatibel · − Suchspalte muss links stehen; Spaltenindex bricht beim Einfügen	SVERWEIS/XVERWEIS
INDEX + VERGLEICH	`=INDEX(Matrix; VERGLEICH(Kriterium; Suchvektor; 0))`	VERGLEICH liefert die Position, INDEX holt den Wert an der Position	Wenn Position und Wert getrennt gebraucht werden; sonst durch XVERWEIS abgelöst	✚ kann links der Suchspalte zurückgeben · − zwei Funktionen, sperrig	INDEX/VERGLEICH
XVERGLEICH	`=XVERGLEICH(Suchkriterium; Suchmatrix; [Vergleichsmodus]; [Suchmodus])`	Liefert die Position eines Treffers	Nur wenn die Position selbst das Ergebnis ist	✚ liefert die Position direkt · − seltener Anwendungsfall	XVERGLEICH
INDIREKT	`=INDIREKT(Text; [A1])`	Macht aus Text einen echten Bezug	Zero-Touch-Modelle, umschaltbare Tabellen-/Spaltenbezüge; volatil — sparsam	✚ macht Modelle Zero-Touch-umschaltbar · − volatil, Textverarbeitung, Quellmappe muss offen sein	INDIREKT
PIVOTDATENZUORDNEN	`=PIVOTDATENZUORDNEN(Datenfeld; Pivot-Bezug; Feld1; Element1; …)`	Layoutstabiler Zugriff auf einen Pivot-Wert	Berichte, die aus einer Pivot lesen — übersteht jeden Umbau des Layouts	✚ übersteht jeden Pivot-Umbau · − braucht eine Pivot; Auto-Erzeugung setzt harte Konstanten	PIVOTDATENZUORDNEN

Bedingtes Rechnen und Aggregieren

Funktion	Aufbau	Was sie tut	Wann benutzen	✚ Stärke / − Schwäche	Nachschlagen
SUMMEWENNS	`=SUMMEWENNS(Summenbereich; Kriterienbereich1; Kriterium1; …)`	Summiert nur Zeilen, die alle Bedingungen erfüllen	Mehrfachbedingte Summen, Zeitintervalle per `">="&Zelle`-Verkettung	✚ schnell, lesbar, beliebig viele Kriterien · − nur UND-Logik	SUMMEWENNS
ZÄHLENWENNS / MITTELWERTWENNS	analog SUMMEWENNS	Zählen bzw. Mitteln unter Bedingungen	Gleiche Logik wie SUMMEWENNS	✚ gleiche Logik wie SUMMEWENNS · − nur UND-Logik	mehrfachbedingte Summe
SUMMENPRODUKT	`=SUMMENPRODUKT(Matrix1; [Matrix2]; …)`	Skalarprodukt: paarweise multiplizieren, dann summieren	Menge × Preis = Umsatz in einem Schritt; Bedingungen als (Krit1)*(Krit2)-Faktoren	✚ König für Kaufleute; Solver-Zielfunktionen · − als Bedingungs-Summe zweckentfremdet unleserlich	SUMMENPRODUKT
TEILERGEBNIS	`=TEILERGEBNIS(Funktion; Bezug1; …)`	Aggregiert unter Ausschluss gefilterter Zeilen (Codes 101–111 auch manuell ausgeblendete)	Summe/Anzahl über gefilterte Bereiche — 109 = Summe nur Sichtbares	✚ respektiert Filter automatisch · − Funktionscodes muss man kennen	TEILERGEBNIS
AGGREGAT	`=AGGREGAT(Funktion; Optionen; Matrix; [k])`	TEILERGEBNIS-Nachfolger mit 19 Funktionen und Ignorier-Optionen	Wenn zusätzlich Fehlerwerte ignoriert oder Quantile/Median gebraucht werden	✚ 19 Funktionen + Ignorier-Optionen · − setzt sich in der Praxis nicht durch (zu kompliziert)	AGGREGAT
SUMME	`=SUMME(Bereich1; …)`	Addiert alles im Bereich	Immer, wenn keine Bedingung nötig ist	✚ unschlagbar simpel · − keine Bedingungen	—

Dynamische Matrizen (erzeugen)

Funktion	Aufbau	Was sie tut	Wann benutzen	✚ Stärke / − Schwäche	Nachschlagen
FILTER	`=FILTER(Matrix; Einschließen; [wenn_leer])`	Gibt nur Zeilen zurück, die die Bedingung erfüllen	Lebende Teilmengen; UND über (K1)*(K2); wenn_leer gegen #KALK!	✚ lebende Teilmenge, wächst mit · − ohne wenn_leer #KALK! bei null Treffern	FILTER
EINDEUTIG	`=EINDEUTIG(Matrix)`	Entfernt Duplikate	Dynamische Dropdown-Quellen, Namenslisten	✚ dublettenfrei ohne Hilfskonstruktion · − nur M365/2021	EINDEUTIG
SORTIEREN	`=SORTIEREN(Matrix; [Spaltenindex]; [Reihenfolge])`	Sortiert eine Matrix (−1 = absteigend)	Als äußere Funktion um FILTER/EINDEUTIG	✚ sortiert als Formel · − Sortierindex zählt innerhalb der Matrix	SORTIEREN
SEQUENZ	`=SEQUENZ(Zeilen; [Spalten]; [Anfang]; [Schritt])`	Erzeugt eine Zahlenfolge aus dem Nichts	Mitwachsende Achsen (Planjahre) — erzeugend, nicht weiterverarbeitend!	✚ mitwachsende Achsen aus dem Nichts · − erzeugend — Falle bei Weiterverarbeitungs-Fragen	SEQUENZ
ZUFALLSMATRIX	`=ZUFALLSMATRIX([Zeilen]; [Spalten]; [Min]; [Max]; [Ganzzahl])`	Matrix aus Zufallszahlen	Testdaten; volatil	✚ Testdaten in einem Wurf · − volatil	Zufallszahlen
GRUPPIERENNACH	`=GRUPPIERENNACH(Zeilenfelder; Werte; Funktion)`	Eindimensionale Aggregation als Formel	Pivot-Ersatz für eine Dimension, sprachlich lesbar	✚ liest sich wie ein Satz · − sehr neu, nur aktuelles M365	GRUPPIERENNACH
PIVOTMIT	`=PIVOTMIT(Zeilenfelder; Spaltenfelder; Werte; Funktion)`	Kreuztabelle als lebende Formel	Pivot-Ersatz zweidimensional; mit INDIREKT flexibilisierbar	✚ lebende Kreuztabelle, INDIREKT-flexibilisierbar · − keine Datenschnitte/Datumsgruppierung	Kreuztabelle

Dynamische Matrizen (weiterverarbeiten)

Funktion	Aufbau	Was sie tut	Wann benutzen	✚ Stärke / − Schwäche	Nachschlagen
SPALTENWAHL / ZEILENWAHL	`=SPALTENWAHL(Matrix; Nr1; …)`	Greift Spalten bzw. Zeilen aus einer Matrix heraus (−1 = letzte)	Ausgabespalten reduzieren; Top-5 nach SORTIEREN	✚ gezielter Zuschnitt, negativ = von hinten · − Nummern-Reihenfolge bestimmt Ausgabe	SPALTENWAHL
VSTAPELN / HSTAPELN	`=VSTAPELN(Matrix1; Matrix2; …)`	Stapelt Matrizen vertikal/horizontal	Strukturgleiche Quartals-/Filialtabellen konsolidieren	✚ konsolidiert strukturgleiche Tabellen · − stapelt auch Kopfzeilen mit	VSTAPELN
MTRANS	`=MTRANS(Matrix)`	Transponiert als lebende Formel	Zeilen↔︎Spalten tauschen ohne Kopie	✚ lebende Transposition · − Quelle löschen = #BEZUG!	MTRANS
ÜBERNEHMEN / WEGLASSEN	`=ÜBERNEHMEN(Matrix; Zeilen; [Spalten])`	Nimmt/entfernt Zeilen vom Anfang (negativ: vom Ende)	Kopf- oder Fußzeilen einer Matrix abschneiden	✚ Kopf-/Fußzeilen elegant abschneiden · − Richtungen leicht verwechselt	Weiterverarbeitung
# (Spill-Operator)	`=B5#`	Bezug auf die ganze übergelaufene Matrix	Dropdown-Quellen, Weiterrechnen auf dynamischen Ergebnissen	✚ ein Zeichen, ganzer dynamischer Bereich · − nur auf echte Spill-Matrizen	Spill-Operator

Finanzfunktionen

Funktion	Aufbau	Was sie tut	Wann benutzen	✚ Stärke / − Schwäche	Nachschlagen
NBW	`=NBW(Zins; Wert1; …) − A0`	Barwert der Folgezahlungen; Anschaffung separat abziehen	Kapitalwert bei gleichen Periodenabständen	✚ einfachste Kapitalwert-Funktion · − zinst alles ab — Anschaffung separat; feste Perioden	NBW
XKAPITALWERT	`=XKAPITALWERT(Zins; Werte; Zeitpunkte)`	Kapitalwert taggenau mit echten Datumswerten	Unregelmäßige Zahlungstermine	✚ taggenau bei krummen Terminen · − Datumspflege nötig	XKAPITALWERT
IKV	`=IKV(Werte; [Schätzwert])`	Interner Zinsfuß aus einer Zahlungsreihe	Rendite einer Investition; braucht nur die Reihe	✚ braucht nur die Zahlungsreihe · − mehrdeutig bei Vorzeichenwechseln; Wiederanlage zum IZF unterstellt	IKV
XINTZINSFUSS	`=XINTZINSFUSS(Werte; Daten; [Schätzwert])`	Interner Zinsfuß taggenau	Wie IKV bei unregelmäßigen Terminen	✚ IZF taggenau · − gleiche Mehrdeutigkeit	XINTZINSFUSS
QIKV	`=QIKV(Werte; Investitionszins; Reinvestitionszins)`	Modifizierter interner Zinsfuß (getrennte Zinssätze)	Wenn Wiederanlage realistisch anders verzinst; Fehler = #DIV/0!	✚ realistische getrennte Wiederanlage · − zwei Zinssätze nötig; #DIV/0! ohne beide Vorzeichen	QIKV
RMZ / ZW / BW / ZZR / ZINS	`=RMZ(Zins; Zzr; Bw; [Zw]; [F])`	Die Rentenfamilie: Rate, Endwert, Barwert, Laufzeit, Zins	Annuitätendarlehen, Sparpläne; F=1 vorschüssig	✚ deckt die ganze Rentenrechnung ab · − Vorzeichenlogik ist die Stolperfalle	Annuitätenfunktionen
ZSATZINVEST / ZW2ZINS	`=ZSATZINVEST(Zzr; Bw; Zw)`	Durchschnittliche Wachstumsrate zwischen zwei Werten	CAGR ohne Potenzformel	✚ CAGR ohne Potenzformel · − nur Anfangs-/Endwert — glättet alles weg	CAGR
BRTEILJAHRE	`=BRTEILJAHRE(Anfang; Ende; [Basis])`	Jahresbruchteil zwischen zwei Daten (Basis = Zinsusance)	Taggenaue Periodenzählung; Basis 0=30/360, 1=act/act, 4=30E/360	✚ Zinsusancen sauber abgebildet · − Basis-Codes nachschlagen	BRTEILJAHRE

Statistik und Prognose

Funktion	Aufbau	Was sie tut	Wann benutzen	✚ Stärke / − Schwäche	Nachschlagen
PROGNOSE.LINEAR	`=PROGNOSE.LINEAR(x; Y_Werte; X_Werte)`	Linearer Regressionswert an Stelle x (früher SCHÄTZER)	Trend-Fortschreibung; vorher Streudiagramm + Bestimmtheitsmaß	✚ einfach und erklärbar · − nur linear; Stabilitätshypothese	PROGNOSE.LINEAR
PROGNOSE.ETS	`=PROGNOSE.ETS(Zieldatum; Werte; Zeitachse; [Saisonalität]; …)`	Exponentielle Dreifachglättung (Holt-Winters)	Saisonale Zeitreihen; Konfidenzintervall vor Weitergabe löschen	✚ kann Saisonalität (Holt-Winters) · − Blackbox; Konfidenzintervall wird fehlgedeutet	PROGNOSE.ETS
TREND	`=TREND(Y_Werte; [X_Werte]; [Neue_X])`	Regressionswerte für viele Stellen auf einmal	Ganze Prognosespalte in einem Wurf	✚ ganze Prognosespalte auf einmal · − gleiche Grenzen wie PROGNOSE.LINEAR	TREND
RGP	`=RGP(Y_Werte; [X_Werte])`	Regressionsparameter (Steigung + Achsenabschnitt) als Matrix	Wenn die Koeffizienten selbst gebraucht werden	✚ liefert die Koeffizienten selbst · − Matrixausgabe ungewohnt	RGP
STEIGUNG / ACHSENABSCHNITT	`=STEIGUNG(Y; X)`	Kostenspaltung: Steigung = variable Stückkosten, Achsenabschnitt = Fixkosten	Lineare Kostenfunktion aus Datenpaaren	✚ Kostenspaltung in zwei Funktionen · − setzt Linearität voraus	Kostenspaltung
BESTIMMTHEITSMASS	`=BESTIMMTHEITSMASS(Y; X)`	R²: 0 = Wolke, 1 = Gerade	Vor jeder Prognose; Faustregel ≥ 60–65 % brauchbar	✚ Prognose-Legitimität in einer Zahl · − hohes R² beweist keine Kausalität	Bestimmtheitsmaß
KORREL	`=KORREL(Matrix1; Matrix2)`	Korrelationskoeffizient −1…+1	Zusammenhang zweier Reihen prüfen	✚ Zusammenhang sofort sichtbar · − keine Kausalität; nur linear	KORREL
SUMMEXMY2	`=SUMMEXMY2(Matrix_x; Matrix_y)`	Summe der quadrierten Abweichungen	Prognosegüte vergleichen — quadriert, damit sich Fehler nicht aufheben	✚ Fehler heben sich nicht auf · − bestraft Ausreißer überproportional	SUMMEXMY2
HÄUFIGKEIT	`=HÄUFIGKEIT(Daten; Klassen)`	Klassenbesetzung (eine Ausgabezelle mehr als Klassen)	Verteilung prüfen, bevor der Mittelwert berichtet wird	✚ Verteilungsform vor dem Mittelwert · − eine Ausgabezelle mehr als Klassen — Eingabefalle	HÄUFIGKEIT
MITTELWERT / MEDIAN / MODUS.EINF	`=MEDIAN(Bereich)`	Lage-Kennzahlen	Median statt Mittelwert bei Ausreißern/bimodal	✚ Median robust gegen Ausreißer · − Mittelwert bei schiefer Verteilung irreführend	Median
STABW.S / STABW.N	`=STABW.S(Bereich)`	Streuung Stichprobe (n−1) bzw. Grundgesamtheit (n)	.S bei Stichprobe, .N wenn alle Daten vorliegen	✚ Streuung zur Lage dazu · − S/N verwechselt = falsche Grundgesamtheit	STABW

Text, Datum, Sonstiges

Funktion	Aufbau	Was sie tut	Wann benutzen	✚ Stärke / − Schwäche	Nachschlagen
& / TEXTKETTE / TEXTVERKETTEN	`=A1&" "&B1` · `=TEXTVERKETTEN(" "; WAHR; Bereich)`	Texte zusammenfügen (TEXTVERKETTEN mit Trenner + Leer-Überspringen)	Namenslisten, dynamische Kriterien `">="&B1`	✚ dynamische Kriterien, Namenslisten · − Zahlen werden zu Text	Textverkettung
TEXTTEILEN	`=TEXTTEILEN(Text; Spaltentrenner)`	Zerlegt Text an Trennzeichen in eine Matrix	Einspaltige Importe aufspalten (Formel-Weg)	✚ Import-Zerlegung per Formel · − nur M365	Text in Spalten
GLÄTTEN / ZEICHEN(10) / RÖMISCH	`=GLÄTTEN(Text)`	Leerzeichen bereinigen · Zeilenumbruch als Zeichen · römische Zahlen	Datenputzen; ZEICHEN(10) für Umbrüche in Formeln	✚ Datenputzen ohne Handarbeit · − GLÄTTEN kennt keine geschützten Leerzeichen	GLÄTTEN
WECHSELN	`=WECHSELN(Text; Alt; Neu; [n])`	Ersetzt Textteile per Formel	Normalisierung, wenn Suchen&Ersetzen nicht reicht	✚ Normalisierung als Formel · − verschachtelt unleserlich	WECHSELN
HEUTE / JETZT	`=HEUTE()`	Aktuelles Datum (JETZT mit Uhrzeit); volatil	Rollierende Zeitachsen `JAHR(HEUTE())`	✚ rollierende Zeitachsen · − volatil — Rechenlast, nicht reproduzierbar	HEUTE
WENNFEHLER	`=WENNFEHLER(Formel; Ersatzwert)`	Fängt Fehlerwerte ab	Um funktionierende Formeln wickeln; sichtbarer Platzhalter statt ""	✚ robuste Anwender-Modelle · − verdeckt echte Fehler (Handlungsauftrag weg)	WENNFEHLER
FORMELTEXT	`=FORMELTEXT(Bezug)`	Zeigt die Formel einer Zelle als Text	Dokumentation/Spickzettel im Blatt	✚ Dokumentation im Blatt · − zeigt nur, rechnet nichts	FORMELTEXT
LET	`=LET(Name1; Wert1; …; Berechnung)`	Zwischenwerte benennen und wiederverwenden	Lange Formeln lesbar + nur einmal gerechnet	✚ einmal rechnen, mehrfach nutzen · − nur M365; Syntax ungewohnt	LET
LAMBDA	`=LAMBDA([Par1]; …; Berechnung)`	Eigene Funktion ohne VBA definieren	Wiederkehrende Logik, im Namens-Manager benannt	✚ eigene Funktionen ohne VBA-Risiko · − Logik versteckt im Namens-Manager	LAMBDA
ZUFALLSZAHL / ZUFALLSBEREICH	`=ZUFALLSBEREICH(Min; Max)`	Zufallswerte (volatil)	Simulation, Testdaten	✚ Simulation ohne Add-In · − volatil, nicht reproduzierbar	Zufallszahlen

Merkregeln zum Schluss: Drei Arten von Geldgrößen — Zeitpunkt → XVERWEIS, Zeitraum → SUMMEWENNS, Barwert → XKAPITALWERT. Volatile Funktionen (JETZT, HEUTE, ZUFALLS*, INDIREKT, BEREICH.VERSCHIEBEN) sparsam einsetzen. Jede Formel in der Klausur Bestandteil für Bestandteil erklären — das ist die QUBA-Regel, die Punkte bringt.