Die wichtigsten Excel-Begriffe einfach erklärt

Die Formel "SVERWEIS" ist eine Funktion in Microsoft Excel (und ähnlichen Tabellenkalkulationsprogrammen), die verwendet wird, um Daten in einer Tabelle zu suchen und zurückzugeben. Der Name "SVERWEIS" steht für "Senkrechte Suche", da die Funktion in einer Tabelle (auch Matrix genannt) vertikal nach einem bestimmten Wert (dem sogenannten Suchkriterium) sucht und dann einen Wert aus derselben Zeile in einer anderen Spalte (Spaltenindex) zurückgibt.

Die allgemeine Syntax der SVERWEIS-Funktion lautet:

=SVERWEIS(Suchkriterium, Matrix, Spaltenindex, [Bereich_vergleichen])

Suchkriterium:

Der Wert, den du in der Tabelle suchen möchtest.

MATRIX:

Der Bereich, in dem die Suche durchgeführt werden soll. Dieser Bereich sollte mindestens zwei Spalten enthalten: eine Spalte mit den Suchwerten und eine Spalte mit den zugehörigen Ergebniswerten.

Spaltenindex:

Die relative Position der Spalte im Suchbereich, die den Rückgabewert enthält. Die erste Spalte im Suchbereich hat den Index 1, die zweite den Index 2 usw.

Bereich_vergleichen (optional aber ein muss!): Dieser Parameter bestimmt, wie genau nach dem Suchwert gesucht wird. Es gibt drei Optionen:

• "WAHR" für eine ungefähre Übereinstimmung,
• "FALSCH" für eine exakte Übereinstimmung
• und "0" für eine exakte Übereinstimmung (ähnlich zu "FALSCH").

TIPP: Ich empfehle IMMER eine "0" oder "FALSCH" am Ende der Sverweis-Funktion zu setzen, auch wenn es sich um ein optionales Argument handelt. Sie werden feststellen, dass der Sverweis besser funktioniert, wenn eine 0 am Ende der Funktion steht.

Die SVERWEIS-Funktion ist besonders nützlich, wenn du Daten aus einer großen Tabelle extrahieren möchtest, ohne manuell nach den Werten suchen zu müssen. Sie wird oft in finanziellen Modellen, Inventarlisten, Verkaufsberichten und anderen Anwendungen verwendet, in denen Daten in tabellarischer Form organisiert sind.

Beispiel:

Angenommen, du hast eine Tabelle mit Produkten und ihren Preisen. Wenn du den Preis eines bestimmten Produkts anhand seines Namens finden möchtest, könntest du die SVERWEIS-Funktion verwenden, um den Preis zurückzugeben, indem du nach dem Produktnamen suchst und den Preis aus der entsprechenden Zeile zurückgibst.

Bitte beachte, dass die genaue Anwendung der SVERWEIS-Funktion je nach deinem spezifischen Anwendungsfall variieren kann.

Die Excel-Funktion "SUMMEWENN" wird verwendet, um die Summe der Zahlen in einem Bereich zu berechnen, der bestimmte Kriterien erfüllt. Mit dieser Funktion kannst du Zellen in einem Bereich auswählen und die Summe der zugehörigen Werte erhalten, basierend auf einem definierten Bedingungskriterium.

Die Syntax der SUMMEWENN-Funktion lautet:

=SUMMEWENN(Bereich, Suchkriterium, Summe_Bereich)

Hier sind die Parameter:

Bereich:

Der Bereich von Zellen, in dem nach dem Kriterium gesucht wird.

Suchkriterium:

Die Bedingung, die erfüllt sein muss, damit die entsprechenden Werte in den Zellen im Bereich zur Summe hinzugefügt werden.

Summe_Bereich :

Der Bereich von Zellen, dessen Werte summiert werden sollen. Wenn dieser Parameter ausgelassen wird, werden die Werte im Bereich selbst summiert.

Beispiel:

Angenommen, du hast eine Liste von Verkaufsdaten mit Produktnamen und den zugehörigen Umsätzen. Du möchtest die Gesamtsumme der Umsätze für ein bestimmtes Produkt berechnen. In diesem Fall könntest du die SUMMEWENN-Funktion verwenden, um die Umsätze für dieses Produkt zu summieren.

=SUMMEWENN(A2:A10, "Apfel", B2:B10)

Hierbei wird im Bereich A2:A10 nach dem Kriterium "Apfel" gesucht, und die zugehörigen Umsätze aus dem Bereich B2:B10 werden summiert.

Die SUMMEWENN-Funktion ist besonders nützlich, wenn du aggregierte Daten basierend auf bestimmten Kriterien ermitteln möchtest. Sie kann in vielen Szenarien verwendet werden, beispielsweise zur Analyse von Verkaufsdaten, zur Erfassung von Projektzeiten oder zur Berechnung von Finanzkennzahlen.

Bitte beachte, dass es auch eine erweiterte Version dieser Funktion namens "SUMMEWENNS" gibt, mit der du mehrere Bedingungen kombinieren kannst, um die Summe basierend auf komplexeren Kriterien zu berechnen. Ich empfehle aber anstatt der Formel SUMMEWENNS bei 3 und mehr Kriterien Pivottabellen oder PowerQuery zu benutzen, was ich in diesen Beitrag erklärt habe.

Eine Pivottabelle ist ein leistungsstarkes Werkzeug in Tabellenkalkulationsprogrammen wie Microsoft Excel, Google Sheets oder ähnlichen Anwendungen. Sie ermöglicht es, große Mengen von Daten auf eine übersichtliche und interaktive Weise zu analysieren und darzustellen. Mit Pivottabellen können komplexe Datensätze schnell zusammengefasst, gefiltert und aggregiert (bedingte Summen gebildet) werden, um Einblicke in die Informationen zu gewinnen.

Hier ist eine Schritt-für-Schritt-Erklärung, wie Pivottabellen funktionieren:

Datenquelle vorbereiten: Du benötigst zunächst eine Tabelle mit Daten in einem Tabellenkalkulationsprogramm. Jede Zeile in der Tabelle stellt einen Datensatz dar, und die Spalten enthalten verschiedene Datenfelder wie z.B. Datum, Produkt, Umsatz usw.

Pivottabelle erstellen: Wähle die gesamte Datenmenge aus und gehe zu dem Menü oder der Registerkarte, die für Pivottabellen steht (normalerweise heißt sie "Einfügen" oder "Daten" und dann "Pivottabelle"). Wähle die Option "Pivottabelle erstellen".

Feldzuweisungen: In der Pivottabellen-Erstellungsoption kannst du die Datenfelder aus deiner Quelltabelle den verschiedenen Bereichen der Pivottabelle zuweisen:

Zeilenfelder: Hier kannst du die Kategorie wählen, nach der du die Daten aufschlüsseln möchtest. Zum Beispiel könnten das Produktkategorien oder Zeitperioden sein.

Spaltenfelder: Hier kannst du zusätzliche Kategorien für horizontale Aufschlüsselungen hinzufügen.

Werte: Dies sind die numerischen Werte, die du analysieren möchtest, wie z.B. Umsatz oder Mengen.

Filterfelder: Du kannst hier Filter festlegen, um die Daten weiter einzuschränken.

Aggregation: In den Werten-Bereichen der Pivottabelle kannst du angeben, wie die Daten aggregiert werden sollen. Du kannst Summen, Durchschnitte, Maxima, Minima usw. auswählen, je nachdem, welche Art von Analyse du durchführst.

Interaktion: Nachdem du die Pivottabelle erstellt hast, kannst du interaktiv damit arbeiten. Du kannst Zeilen und Spalten ziehen und ablegen, um die Daten neu anzuordnen, Filter anwenden, um bestimmte Daten zu sehen, und sogar Diagramme erstellen, um die Ergebnisse visuell darzustellen.

Aktualisierung: Wenn sich deine Quelldaten ändern, kannst du die Pivottabelle aktualisieren, um die Änderungen widerzuspiegeln. Dies stellt sicher, dass du immer aktuelle Einblicke in deine Daten erhältst.

Insgesamt ermöglichen Pivottabellen eine schnelle und flexible Analyse von großen Datenmengen, ohne dass du komplizierte Formeln oder manuelle Sortierungen verwenden musst. Sie sind besonders nützlich, um Trends, Muster und Zusammenhänge in deinen Daten zu entdecken und zu visualisieren.

Ein Pivot-Datenschnitt (auch als Pivot-Tabelle-Datenschnitt oder PivotTable-Slicer bezeichnet) ist ein interaktives Filterwerkzeug, das in Pivot-Tabellen verwendet wird, um Daten auf eine benutzerfreundliche Weise zu filtern und zu analysieren. Es bietet eine visuelle Möglichkeit, Daten in Pivot-Tabellen zu filtern, ohne die eigentliche Tabelle zu ändern. Pivot-Datenschnitte sind besonders nützlich, um die Datenanalyse und -visualisierung flexibler zu gestalten und tiefergehende Einblicke in deine Daten zu erhalten.

Hier sind die Hauptmerkmale und Funktionen eines Pivot-Datenschnitts:

Interaktive Filterung:

Ein Pivot-Datenschnitt bietet eine visuelle Oberfläche mit Schaltflächen oder Auswahllisten, die es ermöglichen, Daten in der Pivot-Tabelle auf einfache Weise zu filtern. Benutzer können die Elemente im Datenschnitt auswählen, um die Daten in Echtzeit neu zu arrangieren und anzuzeigen.

Einfache Bedienung:

Pivot-Datenschnitte sind intuitiv und leicht zu bedienen. Du klickst einfach auf die gewünschten Filteroptionen im Datenschnitt, um die darunterliegenden Daten in der Pivot-Tabelle entsprechend anzupassen.

Mehrfachauswahl:

Ein Datenschnitt ermöglicht oft die Auswahl mehrerer Filteroptionen gleichzeitig. Dies ist hilfreich, wenn du Daten für mehrere Kategorien oder Zeiträume anzeigen möchtest.

Visuelle Darstellung (Hauptvorteil!):

Ein Pivot-Datenschnitt kann als horizontale oder vertikale Schaltflächenreihe oder als Dropdown-Liste angezeigt werden, je nach deinen Präferenzen. Die visuelle Darstellung macht es einfach, die verfügbaren Filteroptionen zu erkennen.

Synchronisierung:

Wenn du mehrere Pivot-Datenschnitte in derselben Pivot-Tabelle hast, werden die Auswahlen in den Schnitten synchronisiert. Das bedeutet, dass die Filterung, die in einem Datenschnitt angewendet wird, sich auf die anderen Schnitte auswirkt.

Anpassung:

Du kannst das Erscheinungsbild eines Pivot-Datenschnitts anpassen, indem du Farben, Schriftarten und Layouts änderst. Dies hilft, den Datenschnitt besser an das Gesamtdesign deiner Arbeitsmappe anzupassen.

Dynamische Aktualisierung:

Wenn die zugrunde liegenden Daten in der Pivot-Tabelle aktualisiert werden, passt sich der Datenschnitt automatisch an, um die neuen Daten widerzuspiegeln.

Ein Pivot-Datenschnitt ist besonders nützlich, wenn du mit umfangreichen Datenmengen arbeitest und eine schnelle und interaktive Möglichkeit benötigst, die Daten z.B. in einem Dashboard in verschiedenen Dimensionen zu analysieren. Es hilft dir, verschiedene Filterkriterien auszuprobieren, ohne die Pivot-Tabelle jedes Mal neu zu bearbeiten, und ermöglicht dir so, in deinen Daten tiefer zu graben, Muster zu entdecken und Einblicke zu gewinnen.

Power Query ist ein leistungsstarkes Datentransformations- und Abfrage-Werkzeug von Microsoft, das in Anwendungen wie Excel, Power BI und anderen Microsoft-Produkten integriert ist. Es ermöglicht Benutzern, Daten aus verschiedenen Quellen zu importieren, zu transformieren und zu modellieren, um sie für Analysen, Berichte und Visualisierungen vorzubereiten. Power Query hilft dabei, den Prozess der Datenbereinigung, -transformation und -integration zu automatisieren, was besonders bei der Arbeit mit großen und komplexen Datensätzen sehr hilfreich ist.

In diesem Beitrag findest Du ein klassisches Anwendungsbeispiel aus dem Rechnungswesen und Controlling

Hier sind einige Schlüsselkonzepte und Funktionen von Power Query:

Datenimport: Power Query ermöglicht den Import von Daten aus einer Vielzahl von Quellen, darunter Datenbanken, Tabellenkalkulationen, Textdateien, Webdienste und mehr. Es kann auch Verbindungen zu Online-Datenquellen herstellen und regelmäßige Aktualisierungen einrichten.

Datentransformation: Mit Power Query können Daten transformiert werden, indem sie bereinigt, strukturiert und formatiert werden. Du kannst Spalten hinzufügen oder entfernen, Werte ersetzen oder entfernen, Daten sortieren, gruppieren und filtern.

Verknüpfung von Daten: Power Query ermöglicht es, Daten aus verschiedenen Quellen zu verknüpfen und zu kombinieren, um umfassendere Datensätze zu erstellen. Dies ist besonders nützlich, wenn Daten aus unterschiedlichen Quellen miteinander in Beziehung stehen sollen.

Erweiterte Transformationen: Power Query bietet erweiterte Funktionen, um komplexe Transformationen durchzuführen. Du kannst benutzerdefinierte Funktionen schreiben, komplexe Berechnungen durchführen, Pivot-Operationen anwenden und vieles mehr.

Datentyp-Erkennung: Power Query versucht automatisch, den Datentyp jeder Spalte zu erkennen, kann aber auch manuelle Anpassungen zulassen, um sicherzustellen, dass die Daten korrekt interpretiert werden.

Schritte und Abfrageeditor: Power Query zeichnet alle durchgeführten Schritte auf und zeigt sie im Abfrageeditor an. Hier kannst du Schritte bearbeiten, neu anordnen, hinzufügen oder entfernen, um die Datentransformationen nach deinen Anforderungen anzupassen.

Automatisierung und Skripting: Power Query ermöglicht es, Abfragen zu automatisieren und in Skripten zu speichern. Dies ist besonders nützlich, wenn du wiederholende Transformationen auf mehreren Datensätzen durchführen möchtest.

Ladeoptionen: Nach der Transformation kannst du die Daten in ein Tabellenformat in Excel oder Power BI laden oder für weitere Analysen und Visualisierungen verwenden.

Power Query bietet eine benutzerfreundliche Oberfläche, die sowohl für Anfänger als auch für fortgeschrittene Benutzer geeignet ist. Es erleichtert die Arbeit mit Daten erheblich, indem es den manuellen Aufwand für Bereinigung, Transformation und Verknüpfung minimiert.

Bei Power Pivot handelt es sich um eines der drei Power Tools (PowerQuery, PowerPivot und PowerView) von Microsoft Excel, das es ermöglicht, leistungsstarke Datenmodelle zu erstellen und diese zu visualisieren. Es wird auch als Datenbank-Funktion von Microsoft Excel bezeichnet und ist die Excel Alternative zu Microsoft Access. Es ermöglicht Benutzern, umfangreiche Datenmodelle zu erstellen, die aus verschiedenen Datenquellen stammen können, diese zu kombinieren und komplexe Berechnungen, Analysen und Visualisierungen durchzuführen.

Hier sind einige der Hauptmerkmale und -funktionen von Power Pivot:

Datenmodellierung:

Power Pivot ermöglicht es, Daten aus verschiedenen Quellen wie Excel-Tabellen, SQL-Datenbanken, Textdateien und anderen Datenquellen zu importieren und in einem einzigen Datenmodell zu kombinieren. Du kannst Beziehungen zwischen den Tabellen erstellen, um die Daten miteinander zu verknüpfen. Das gleiche kann auch PowerQuery, doch mit PowerPivot erhälst Du zusätzlich die Möglichkeit das Datenmodell visuell z.B nach dem Sternmodell darzustellen, was in PowerQuery nicht möglich ist.

Spalten- und Tabellenberechnungen:

Du kannst eigene Berechnungsspalten und -tabellen erstellen, indem du DAX (Data Analysis Expressions) verwendest. DAX ist eine Formelsprache, die speziell für die Datenanalyse entwickelt wurde und leistungsstarke Berechnungen ermöglicht.

Hierarchien und Aggregationen:

Mit Power Pivot kannst du Hierarchien erstellen, um Daten auf verschiedenen Ebenen zu aggregieren. Dies ist besonders nützlich, wenn du beispielsweise Umsätze nach Jahr, Quartal, Monat und Tag analysieren möchtest.

Schnelle Leistung:

Power Pivot nutzt In-Memory-Technologie, um Daten im Arbeitsspeicher zu speichern und schnellen Zugriff auf die Informationen zu ermöglichen. Dadurch können auch große Datensätze über eine Million Datenzeilen effizient verarbeitet werden.

Integration mit Power Query:

Power Pivot lässt sich nahtlos mit Power Query integrieren, einem Tool zur Datenbereinigung und -transformation. Du kannst Daten aus verschiedenen Quellen importieren, transformieren und dann in Power Pivot verwenden.

Das Sternmodell ist ein Konzept aus der Datenmodellierung und wird häufig in Zusammenhang mit Datenbanken und Business Intelligence-Tools wie Power Pivot verwendet.

Bei Power Pivot handelt es sich um eines der drei Power Tools (PowerQuery, PowerPivot und PowerView) von Microsoft Excel, das es ermöglicht, leistungsstarke Datenmodelle zu erstellen und sternförmig zu visualisieren. Genau von dieser sternförmigen Anordnung der Dimensionstabellen um die Faktentabelle hat das Sternmodell seinen Namen.

Im Beitrag "Wie Sie mithilfe der Power Methode und dem Stern-Schema Ihr Excel-Berichtswesen automatisieren" erfahren Sie wie genau das Sternmodell anhand eines Praxisbeispiels aussieht. 

Faktentabelle (Fact Table):

Die Faktentabelle ist der zentrale Bestandteil des Sternmodells. Sie enthält mehrere numerische Daten, die analysiert werden sollen, wie beispielsweise Umsätze, Mengen, Verkaufszahlen usw. Jede Zeile in der Faktentabelle repräsentiert einen einzelnen Eintrag oder eine Transaktion.

Beispiel: Angenommen, du möchtest den Umsatz eines Unternehmens analysieren. Die Faktentabelle könnte Transaktionen mit den Spalten "Datum", "Produkt-ID", "Menge" und "Umsatz" enthalten.

Dimensionstabellen (Dimension Tables):

Die Dimensionstabellen enthalten beschreibende Informationen, die mit den Einträgen in der Faktentabelle in Beziehung stehen. Diese Informationen werden verwendet, um die numerischen Daten in der Faktentabelle zu kontextualisieren und zu filtern. Jede Dimensionstabelle enthält eine eindeutige Liste von Werten und optional weitere Attribute.

Beispiel: Für das oben genannte Beispiel könnten Dimensionstabellen "Produkte" (mit Produktinformationen wie Name, Kategorie usw.) und "Datum" (mit Datumsattributen wie Jahr, Quartal, Monat) erstellt werden.

Beziehung zwischen Faktentabelle und Dimensionstabellen:

Die Dimensionstabellen sind über Beziehungen mit der Faktentabelle verbunden. Diese Beziehungen ermöglichen es, die numerischen Daten in der Faktentabelle basierend auf den beschreibenden Informationen in den Dimensionstabellen zu analysieren. Beispielsweise könntest du den Umsatz nach Produktkategorie oder nach Datum aufschlüsseln.

Die Vorteile des Sternmodells sind eine effiziente Datenanalyse, da Informationen in kleinere, spezialisierte Tabellen aufgeteilt werden, die einfacher zu verarbeiten sind. Zudem wird die Datenredundanz minimiert, da Informationen nicht mehrfach in der Faktentabelle vorkommen.

Zusammenfassung:

Das Sternmodell ist eine Methode zur Datenmodellierung, die in Power Pivot und anderen Business Intelligence-Tools verwendet wird. Es hilft dabei, komplexe Datenanalysen zu ermöglichen, indem es Daten in Faktentabellen (numerische Daten) und Dimensionstabellen (beschreibende Informationen) organisiert und Beziehungen zwischen ihnen herstellt. Dies erleichtert die Erstellung von aussagekräftigen Berichten und Dashboards.

Dieser Beitrag wurde mit Hilfe von eigenen Praxis Erfahrungen und ChatGPT erstellt Quelle: (ChatGPT, persönliche Kommunikation, 17. August 2023).