Pivot-Tabellen und SVERWEIS

Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling

www.finanzcontrolling.uni-bremen.de

Petra Schröder

  218-60202 [email protected]

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Übung: Belegjournal aufrufen (Einzelposten – Obligo und Ist – Alle Buchungen)       Fonds 31010079 Jahre 2003 bis 2012 die Perioden 1 bis 16 maximale Trefferzahl und Haken bei „Nur Variantenfelder“ rausnehmen ausführen Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Spalten ausblenden, sodass am Ende nur noch diese Spalten bleiben Werttyp-Text, HHM-Budat, Zahl. Bud..., Finanzpos. Finanzstelle, Fonds, Text Finanzposition 8100000 per Filter ausschließen Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Layout sichern Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

     Einnahmen (J) Bericht Fonds 31010079 Jahre 2003 bis 2012 die Perioden 1 bis 16 ausführen  Notieren: Betrag der Ausgaben  Wechseln in die Einzelpostensicht für die Ausgaben

Was fällt auf?

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Es sind nur wenige Spalten zu sehen.

Die Gesamtsumme weicht vom Einnahmen (J) - Bericht ab.

Warum?

In dem voreingestellten Layout wurde ein Filter gesetzt und die Spalten eingeschränkt.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Merke: Gundsätzlich ist beim Abspeichern eines Layouts der Haken bei Voreinstellungen gesetzt. Man muss ihn meistens manuell rausnehmen.

Speichert man ein Layout als Voreinstellung, aber nicht Benutzerspezifisch ab, werden die Einstellungen des gesamten Mandanten 150 (Land Bremen, Bremerhaven, Hochschulen) geändert.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Beim „Absprung“ in die Einzelposten aus dem Einnahmen (J)-, dem Einnahmen (G) oder aus einem anderen Summen-Bericht wird das voreingestellte Layout angezeigt.

Achtung:

Wenn Filter gesetzt wurden kann das Ergebnis in der Einzelpostenliste verfälscht werden.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

SAP bietet die Möglichkeit, Einzelposten listen als Pivot-Tabelle zu exportieren.

Dabei ist zu beachten, dass die Pivot Tabelle, die man aus SAP exportiert, eine vorgegebene Formatierung und Gliederung hat.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Einige Spalten, wie z. B. die Anlagen nummer oder das Erfassungsdatum können beim „Absprung“ in die Einzelposten im Einnahmen (J) und (G) Bericht nur eingeblendet werden, wenn sie auch im voreingestellten Layout eingeblendet sind.

Filter, Sortierungen, Summen und Zwischensummen werden dabei nicht berücksichtigt.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Übung:  Einnahmen (J) Bericht   Fonds: 31010079 bis 31010082 Jahre: 2003 bis 2012, bis Periode 16  Ausgaben Einzelposten aufrufen Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Liste  Exportieren  Tabellenkalkulation Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Reduzieren Sie die Tabelle auf diese Felder und speichern Sie die Tabelle auf dem Desktop Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS SVERWEIS

Bei der Tabelle, die aus SAP exportiert wird, fehlt die Zuordnung nach Hierarchien. z. B. Investiv, Konsumtiv, Personal, ...

Dafür verknüpfen wir unsere Tabelle mit einer anderen Tabelle bzw. zwei Tabellenblätter in einer Tabelle.

Der Befehl ist

SVERWEIS

und bedeutet, dass bestimmte Daten aus einer Verweistabelle übernommen werden, ohne die jeweiligen Daten einzeln kopieren zu müssen.

Petra Schröder (Ref. 05)

Die Tabelle heisst Finanzpositionen.xls

und enthält unter anderem die Spalten Ebene2 und Ebene3 und das Tabellenblatt FiPos für Auswertungen

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Ergänzen Sie Ihre Grunddaten-Tabelle um die Spalten  Ebene2 und  Ebene3 In unserem Beispiel wollen wir über die Finanzposition zuordnen.

Unsere Verweistabelle ist das Tabellenblatt „FiPos für Auswertungen“.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Öffnen Sie die Tabelle „Finanzpositionen.xls“ Kopieren Sie das Tabellenblatt „FiPos für Auswertungen“ in ihre Tabelle.

ACHTUNG:

Dafür müssen beide Tabellen geöffnet sein.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Tabellenblatt auswählen  Verschieben/kopieren  (ans Ende stellen)   Rechte Maustaste Zieltabelle auswählen  Kopie erstellen anhaken Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Gehen Sie in die Zelle, in der das Ergebnis angezeigt werden soll. (Erste Zelle der Spalte Ebene2) Eingeben:

=SVERWEIS(

Wählen Sie die Zelle, in der der Bezug, hier die Finanzposition, steht.

=SVERWEIS(

G2;

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS Zu beachten ist:

Die Formate der Zellen, die miteinander verknüpft werden sollen, müssen identisch sein. In diesem Beispiel ist das leider nicht so.

Die Daten, die Sie aus SAP bekommen sind als Text formatiert, die in dem Tabellenblatt FiPos für Auswertungen, als Zahl. Lösung: Sie müssen die Spalte markieren und den Text in eine Zahl umwandeln (Office 2007).

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Wechseln Sie in das Tabellenblatt FiPos Auswertungen und markieren die Tabelle. Die Formel wird automatisch für gesamte ergänzt, das Semikolon muss gesetzt werden.

=SVERWEIS(G2;

‘FiPos für Auswertungen‘!A1:E543;

Geben Sie die Zahl der Spalte an, in der die Info steht, die übernommen werden soll.

Hier die Spalte 4 , da wir zunächst eine grobe Zuordnung zu Personal, Konsumtiv, Investiv ... haben möchten.

=SVERWEIS(G2;‘FiPos für Auswertungen‘!A1:E543;

4;

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Das letzte Kriterium in der Formel ist Falsch  die exakte Übereinstimmung wird geprüft oder Wahr  die ungefähre Übereinstimmung wird geprüft. Im Zweifel sollte man FALSCH wählen.

=SVERWEIS(G2;‘FiPos für Auswertungen‘!A1:E543;4;

FALSCH)

Um den

Bereich

in der Verweistabelle

konstant

zu halten, muss man ihn

fixieren

. Das erreicht man, indem man in der Formel auf die Zellenangabe geht und die

F4-Taste

drückt. Dann erscheinen sowohl vor der Spalte als auch vor der Zelle jeweils Dollarzeichen.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Die fertige Formel für die Spalte Ebene2 lautet:

=SVERWEIS(G2;'FiPos für Auswertungen'!$A$1:$E$543;4;FALSCH)

Die Formel bis zum Ende der Spalte kopieren.

Die Formel für die Spalte Ebene3 lautet:

=SVERWEIS(G2;'FiPos für Auswertungen'!$A$1:$E$543;5;FALSCH)

Die Formel bis zum Ende der Spalte kopieren.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Ändern Sie die Datenquelle in Ihrer Pivot-Tabelle Ein Ergebnis könnte sein: Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Ein festes Suchkriterium muss vorhanden sein, z. B.

Finanzposition, welches in der Verweis tabelle in der ersten Spalte steht, da man nur die Informationen der Spalten rechts von dieser Bezugszeile (= Suchkriterium) abrufen kann.

Dollar-Zeichen in der Formel geben an, dass bestimmte Kriterien verschoben werden fest können.

stehen und nicht z. B. bei „$A2“ = Der Wert „A“ ist fest definiert, der Wert „2“ kann verschoben werden zu 1,3,4… Mit

F4

kann das Dollarzeichen gesetzt werden.

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Haben Sie noch Fragen?

Petra Schröder (Ref. 05)

Pivot-Tabellen und SVERWEIS

Vielen Dank für Ihre Aufmerksamkeit

Petra Schröder (Ref. 05)