Excel 2016–Projektliste auswerten

Das Vorgehen funktioniert ab Excel 2016 und möglicherweise auch in älteren Versionen, sofern das Tool “PowerPivot” dort zu Verfügung steht. Allerdings unterscheiden sich die Menünamen und die Befehle erheblich. Ich beschreibe das nur für die Excel-2016-Version!

14.06.2019_0000Die Projekt-Liste wird aus einem separaten Programm gezogen und jede Woche in Excel neu übertragen. Dabei bleiben Dateiname und –pfad identisch, aber die Daten werden jedes Mal überschrieben.

Sie besteht aus den Spalten Projekt-Nummer, Rechnungsdatum und Betrag. Die Projekt-Nummer setzt sich aus einer fünfstelligen Mandanten-Nummer und einer fortlaufenden Projekt-Nummer zusammen.

Grün markiert ist immer die Projekt-Nummer; zu jedem Mandanten kann es mehrere Projekte geben.

14.06.2019_0001

Daraus soll eine Auswertung entstehen, die die Rechnungsbeträge pro Mandant (also nicht pro Projekt!) auflistet.

Die Mandantennummer kann aus der Projektnummer recht einfach ermittelt werden. Die Funktion dazu lautet für die Projektnummer in Zelle A2:

=LINKS(A2;5)

LINKS erwartet zuerst den Wert oder die Zelle, dann die Anzahl der Zeichen, die von links ermittelt werden sollen. Da die Mandantennummer immer fünfstellig ist, lautet das zweite Argument bei mir 5. Später werden wir den englischen Funktionsnamen benötigen, er heißt schlicht und einfach LEFT.

Aber: ich kann die Ursprungstabelle nicht mit einer Zusatzspalte versehen. Das heißt, ich kann schon, aber ich will nicht. Die Ursprungstabelle kommt ja jede Woche neu und ich müsste dann jede Woche neu die Mandanten-Nummer ermitteln. Das ist zwar nicht so schrecklich viel Arbeit, aber ich will es ganz bequem haben. Deswegen arbeite ich anders.

Schritt 1: Die Exporttabelle aus dem anderen Programm muss gespeichert werden – später immer unter dem gleichen Pfad und Namen ablegen. Meine heißt “Mandantenliste_export.xlsx”. Sie ist geschlossen.

14.06.2019_0002Schritt 2: Erstellen Sie eine neue leere Excel-Tabelle und klicken Sie auf “PowerPivot | Datenmodell Verwalten”. Im nächsten Bild klicken Sie auf “Start | Externe Daten abrufen: Aus anderen Quellen”, rollen Sie an das Ende der Liste und wählen Sie “Excel-Datei”.

14.06.2019_0003Klicken Sie auf “Weiter”, wählen Sie im folgenden Dialog mit Klick auf “Durchsuchen” die Excel-Datei aus.

Machen Sie ein Häkchen bei “Erste Zeile als Spaltenüberschrift verwenden” und klicken Sie auf “Weiter”.

Es werden Ihnen anschließend die Tabellen dieser Excel-Arbeitsmappe angezeigt. Aktivieren Sie das Blatt, auf dem die Daten sind und klicken Sie auf “Fertig stellen”.

Sie sehen als Erfolgsmeldung, wie viele Zeilen übertragen wurden. Mit Klick auf “Schließen” werden Ihnen die Daten angezeigt. Damit werden wir später noch arbeiten, weil wir eine Spalte für die Mandanten-Nummer stellen müssen. Aber vorerst lassen wir es bei der Übergabe der Daten und probieren erst einmal aus, dass die Verbindung auch wirklich vorhanden ist.

14.06.2019_0004Schritt 3: Klicken Sie auf “Start | PivotTable” und im nächsten Schritt am besten auf “Vorhandenes Arbeitsblatt”.

 

14.06.2019_0005Erstellen Sie eine ganz einfache Pivot-Tabelle für die Projektnummer und den Betrag. Klappen Sie die Felder unter “Tabelle 1” (oder welchen Namen Sie auch immer vergeben haben) auf und setzen Sie ein Häkchen für die Projekt-Nummer und den Betrag.

Ich weiß, das ist noch lange nicht das, was wir wollen. Sie haben jetzt bloß eine normale Pivot, die Sie auch in der Ursprungstabelle hätten erstellen können. Glauben Sie mir, es wird noch spannender!

Speichern Sie diese Excel-Arbeitsmappe, meine heißt “Mandantenliste_Auswertung.xlsx”. In ihr befindet sich jetzt eine Verbindung zu der Export-Datei sowie die zugehörige Auswertung. Probeweise schließen Sie diese Arbeitsmappe, öffnen Sie die Export-Datei und hängen Sie unten ein neues Projekt an. Speichern, zumachen, die Auswertung öffnen und auf “PivotTable-Analyse | Aktualisieren” klicken. Das neue Projekt muss jetzt in der Pivot erscheinen.

Fazit: Jede Woche kann die exportierte Excel-Arbeitsmappe unter dem gleichen Namen im gleichen Ordner abgelegt werden. Dann wird die Auswertungsdatei geöffnet und aktualisiert.

Und jetzt der letzte Schritt. Sozusagen der Clou!

Schritt 4: Öffnen Sie die Auswertungsdatei. Klicken Sie auf “PowerPivot | Verwalten”. Sie landen wieder in der Ansicht mit den übertragenen Daten. Setzen Sie den Cursor in die erste leere Zelle unter “Spalte hinzufügen” und klicken Sie in die Bearbeitungszeile.

14.06.2019_0006Tippen Sie ein = und dahinter LEFT(Pro

Es muss jetzt der Spaltenname “Projekt-Nr.” in eckigen Klammern vorgeschlagen werden. Übernehmen Sie ihn mit doppeltem Mausklick oder mit der Tab-Taste.

Tippen Sie ein Semikolon und die Ziffer 5.

Die Formel sieht jetzt so aus:

14.06.2019_0008=left([Projekt-Nr’#];5)

Mit ENTER wird die Berechnung nach einigen Sekunden auf alle Datensätze angewandt. Benennen Sie die Spalte z. B. mit “Mandant”.

Achten Sie darauf, dass innerhalb der Datentabelle in PowerPivot immer englische Funktionsnamen verwendet werden müssen. Allerdings dürfen wir aktuell das Semikolon verwenden – in älteren Versionen musste man in PowerPivot ein Komma einfügen.

14.06.2019_0009

Schließen Sie die Datenansicht (einfach auf das Kreuzchen oben rechts klicken) und kehren Sie zurück zu Ihrer “normalen” Pivot-Tabelle. Sie müssen jetzt dort das Feld “Mandant” zusätzlich haben.

In der Pivot kann jetzt die Projekt-Nummer deaktiviert und dafür “Mandant” aktiviert werden”. Nun sieht das Ergebnis schon fast wie gewünscht aus: die Rechnungsbeträge werden nach Mandanten zusammengefasst. Speichern an dieser Stelle wäre nicht schlecht.

Kosmetik zum Schluss: Aktivieren Sie noch das Feld “Rechnungsdatum”, es wird automatisch als Spaltenbeschriftung eingefügt. Klicken Sie in der Pivot-Tabelle ein Datumsfeld mit rechter Maustaste an, wählen Sie “Gruppieren” und markieren dann “Monate”.

Speichern, schließen.

Ab sofort geht es so:

  • Die neue wöchentliche Exporttabelle wird unter gleichem Namen im gleichen Ordner gespeichert und gar nicht angerührt.
  • Öffnen Sie die Auswertungsdatei, klicken Sie auf auf “PivotTable-Analyse | Aktualisieren”.
  • Alle Daten werden sofort mit der Mandantennummer versehen und ausgewertet.

Die Export- und die Auswertungsdatei können Sie sich zum Ausprobieren herunterladen. Sie müssen aber in der Auswertung den Pfad anpassen: Nach dem Öffnen auf “PowerPivot | Verwalten” klicken, dann auf “Start | Vorhandene Verbindungen”, markieren Sie die Datei “Mandantenliste_Export” und klicken Sie auf “Bearbeiten”.

Mandantenliste_export

Mandantenlist_Auswertung

 

Werbeanzeigen

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s