Pivot-Tabellen und SVERWEIS

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

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

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

4. Pivot-Tabellen und SVERWEIS Layout sichern Petra Schröder (Ref. 05)

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

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

7. Merke: Pivot-Tabellen und SVERWEIS 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)

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

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

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

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

12. Pivot-Tabellen und SVERWEIS Liste  Exportieren  Tabellenkalkulation Petra Schröder (Ref. 05)

13. Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05)

14. Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05)

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

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

17. Pivot-Tabellen und SVERWEIS Die Tabelle heisst Finanzpositionen.xls und enthält unter anderem die Spalten Ebene2 und Ebene3 und das Tabellenblatt FiPos für Auswertungen Petra Schröder (Ref. 05)

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

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

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

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

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

23. Pivot-Tabellen und SVERWEIS 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; Petra Schröder (Ref. 05)

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

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

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

27. Pivot-Tabellen und SVERWEIS Petra Schröder (Ref. 05)

28. 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 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. Petra Schröder (Ref. 05)

29. Pivot-Tabellen und SVERWEIS Haben Sie noch Fragen? Petra Schröder (Ref. 05)

30. Pivot-Tabellen und SVERWEIS Vielen Dank für Ihre Aufmerksamkeit Petra Schröder (Ref. 05)