Excel

Excel-Tricks aus dem täglichen Leben eines Einkäufers – Teil 3 von 3

Teilergebnis, XVERWEIS, Power Pivot

Nachdem wir in vorangegangenen Blogs bereits unter anderem die Themen Tastenkombinationen, Überwachungsfenster und Makros aufgegriffen haben, ist dieses nun der vorerst letzte Teil unserer Blog-Serie zum Thema Excel.

Teilergebnis

Kennen Sie das? Sie haben eine Tabelle erstellt und über alle Zeilen eine Formel (ANZAHL2, SUMME oder der gleichen) gebildet. Nun filtern Sie die Tabelle nach einem Kriterium. Ergebnis: Die Formel passt sich nicht an und bezieht nach wie vor alle Zeilen in die Berechnung mit ein, unabhängig davon, ob diese aufgrund der Filterung ein- oder ausgeblendet sind.

Die Lösung an dieser Stelle lautet Teilergebnis: =TEILERGEBNIS(Funktion; Bereich). Die Funktion des Teilergebnisses, also die Formel, welche dem Teilergebnis zu Grunde liegt, wird mittels einer Ziffer bestimmt, wobei u.a. die 2 für ANZAHL2 steht, die 9 für SUMME – Excel zeigt Ihnen die mögliche Auswahl beim Erstellen der Formel an. Nun passt sich die Berechnung automatisch bei Filterung der Tabelle auf die gefilterten Werte an.

XVERWEIS

Die Funktion SVERWEIS wird immer dann verwendet, wenn man mehrere Excel-Tabellen miteinander vergleichen oder zusammenführen will. Dabei steht das S für senkrecht, so dass die Suche bzw. der Abgleich auf Spalten-Basis erfolgt. Das Gegenstück auf Zeilenbasis hierzu ist der WVERWEIS für die Suche / den Abgleich auf Zeilenebene.

Neu und derzeit im Rollout für Office 365 ist der XVERWEIS, welcher das Potenzial hat, zukünftig SVERWEIS, WVERWEIS und INDEX/VERGLEICH-Funktionen abzulösen. Er bietet gleich mehrere Vorteile gegenüber den alten Formeln:

  • Mit nur einer Formel können senkrechte und waagerechte Suche vereint werden.
  • Man kann sowohl nach rechts als auch nach links (bzw. sowohl nach oben als auch nach unten) suchen.
  • Ein Abzählen des Ausgabebereichs entfällt.

Wir wollen an dieser Stelle nur die Grundformel in Kürze vorstellen. Online finden Sie hierzu viele detaillierte Tutorials mit Beispielen zu den verschiedensten Anwendungsmöglichkeiten.

=XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;[wenn_nicht_gefunden];[Vergleichsmodus];
[Suchmodus])

Suchkriterium:        Nach welchem Wert soll gesucht werden?

Suchmatrix:              Spalte / Zeile, in der nach dem Wert gesucht werden soll

Rückgabematrix:    Spalte / Zeile, aus der der Rückgabewert verwendet werden soll

wenn_nicht_gefunden (optional) Welcher Wert soll angezeigt werden, wenn das Suchkriterium nicht gefunden wird (anstelle eines Fehlers)

Vergleichsmodus: (optional) Auswahl „exakte Übereinstimmung“, „exakte Übereinstimmung oder nächst größerer / kleinerer Wert“, „Platzhalterzeichenübereinstimmung“

Suchmodus:             (optional) Auswahl der Suchrichtung (vom ersten zum letzten oder anders herum mit oder ohne Berücksichtigung einer Sortierung)

Nachteil: Nach derzeitigem Stand wird der XVERWEIS zunächst nur für Office 365 nutzbar sein und keinen Rollout in Office 2019 (oder älteren Versionen) erfahren. Leider ist er auch nicht rückwärtskompatibel, so dass es zu Problemen kommen kann, wenn Dateien unter Nutzung der Formel zwischen Organisationen mit und ohne 365-Abonnement geteilt werden sollen.

Power Pivot

Arbeiten Sie bereits mit Pivot-Tabellen in Excel? Hierbei handelt es sich um eine spezielle Art von Tabelle mit der Möglichkeit zur Darstellung und Auswertung von Daten nach unterschiedlichen Aufrissen und Sichtwinkeln, ohne dabei Änderungen an den Ausgangsdaten selbst vornehmen zu müssen. So lassen sich große Datenmengen auf überschaubare Ansichten verdichten. Sollten Sie bisher nicht mit Pivots arbeiten, so sollten Sie sich mit diesem Thema eindeutig näher beschäftigen, um einen besseren Überblick über Ihre Daten zu erhalten.

Aber kennen Sie auch Power Pivot? Hierbei handelt es sich um eine Funktion in Excel, die seit Version 2010 zunächst als Add-in zur Verfügung stand und seit Version 2013 automatisch installiert wird. Im Grunde umfasst Power Pivot dabei die Eigenschaften einer „normale“ Pivot-Tabelle, bietet darüber hinaus aber weitere Funktionen und Vorteile:

  • Zusammenführung mehrerer Datenquellen (von unterschiedlichen Datentypen) zu einem verknüpftem Datenmodell
  • Keine Beschränkung der Datensatz-Anzahl
  • Hohe Schnelligkeit von Auswertungen
  • Erstellung individuell berechneter Felder
  • Nutzung von Hierarchien

Eine unserer liebsten Funktionen: Eine normale Pivot-Tabelle ermittelt über die Wertfeld-Funktion „Anzahl“ die Anzahl der Datensätze mit Inhalten, ist jedoch nicht in der Lage, die Quantität unterschiedlicher Inhalte zu benennen. Dies gelingt mittels Power Pivot über die Weltfeld-Funktion „Diskrete Anzahl“ mühelos.

Wir hoffen, dass Sie Gefallen an unserer kleinen Excel-Blog-Serie hatten und dass wir Ihnen vielleicht den ein oder anderen Trick in der Anwendung der Software näherbringen konnten.

 

Jessica Murawski

Consultant, ADCONIA GmbH