Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05...

30
Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder www.finanzcontrolling.uni- bremen.de 218-60202 umbuchen@uni- bremen.de

Transcript of Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05...

Page 1: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Pivot-Tabellen aus SAPund SVERWEIS

Referat 05Finanzcontrolling

Petra Schröder

www.finanzcontrolling.uni-bremen.de

218-60202 [email protected]

Page 2: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Ü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

Page 3: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Spalten ausblenden, sodass am Ende nur noch diese Spalten bleiben

Finanzposition 8100000 per Filter ausschließen

Werttyp-Text, HHM-Budat, Zahl. Bud..., Finanzpos. Finanzstelle, Fonds, Text

Page 4: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Layout sichern

Page 5: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Einnahmen (J) Bericht Fonds 31010079 Jahre 2003 bis 2012 die Perioden 1 bis 16 ausführen

Wechseln in die Einzelpostensicht für die Ausgaben

Notieren: Betrag der Ausgaben

Was fällt auf?

Page 6: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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.

Page 7: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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.

Page 8: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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.

Page 9: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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.

Page 10: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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.

Page 11: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Übung:

Einnahmen (J) Bericht

Fonds: 31010079 bis 31010082 Jahre: 2003 bis 2012, bis Periode 16

Ausgaben Einzelposten aufrufen

Page 12: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Liste Exportieren Tabellenkalkulation

Page 13: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Page 14: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Page 15: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Reduzieren Sie die Tabelle auf diese Felder und speichern Sie die Tabelle auf dem Desktop

Page 16: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

SVERWEIS

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

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.

Page 17: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Die Tabelle heisstFinanzpositionen.xls

und enthältunter anderem die Spalten Ebene2 und Ebene3

und das TabellenblattFiPos für Auswertungen

Page 18: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

In unserem Beispiel wollen wir über die Finanzposition zuordnen.Unsere Verweistabelle ist das Tabellenblatt „FiPos für Auswertungen“.

Ergänzen Sie Ihre Grunddaten-Tabelle um die Spalten Ebene2 und Ebene3

Page 19: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Ö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.

Page 20: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Tabellenblatt auswählen Rechte Maustaste

Verschieben/kopieren Zieltabelle auswählen

(ans Ende stellen) Kopie erstellen anhaken

Page 21: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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;

Page 22: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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).

Zu beachten ist:

Page 23: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Wechseln Sie in das Tabellenblatt FiPos für Auswertungen und markieren die gesamte Tabelle. Die Formel wird automatisch 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;

Page 24: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Das letzte Kriterium in der Formel istFalsch die exakte Übereinstimmung wird geprüftoder 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.

Page 25: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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

Die fertige Formel für die Spalte Ebene2 lautet:

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.

Page 26: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Ändern Sie die Datenquelle in Ihrer Pivot-Tabelle

Ein Ergebnis könnte sein:

Page 27: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

Page 28: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Pivot-Tabellen und SVERWEIS

Petra Schröder (Ref. 05)

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 fest stehen und nicht verschoben werden können.

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.

Page 29: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Petra Schröder (Ref. 05)

Haben Sie noch Fragen?

Pivot-Tabellen und SVERWEIS

Page 30: Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05) Pivot-Tabellen aus SAP und SVERWEIS Referat 05 Finanzcontrolling Petra Schröder .

Petra Schröder (Ref. 05)

Vielen Dank für Ihre

Aufmerksamkeit

Pivot-Tabellen und SVERWEIS