Angenommen, Sie führen mehrere Projekte mit unterschiedlichen Budgets durch und möchten Ihre Kosten für jedes davon visualisieren. Das heißt, aus dieser Quelltabelle:
.. so etwas bekommen:
Mit anderen Worten, Sie müssen das Budget auf die Tage jedes Projekts verteilen und eine vereinfachte Version des Projekt-Gantt-Diagramms erhalten. Dies mit den Händen zu tun ist langwierig und langweilig, Makros sind schwierig, aber Power Query für Excel zeigt in einer solchen Situation seine ganze Kraft.
Power Query ist ein Add-on von Microsoft, das Daten aus fast jeder Quelle in Excel importieren und dann auf verschiedene Arten umwandeln kann. In Excel 2016 ist dieses Add-In bereits standardmäßig integriert, und für Excel 2010-2013 kann es von der Microsoft-Website heruntergeladen und dann auf Ihrem PC installiert werden.
Lassen Sie uns zunächst unsere ursprüngliche Tabelle in eine „intelligente“ Tabelle umwandeln, indem Sie den Befehl auswählen Als Tabelle formatieren Tab Startseite (Home — Als Tabelle formatieren) oder durch Drücken der Tastenkombination Ctrl+T :
Gehen Sie dann zur Registerkarte Datum (wenn Sie Excel 2016 haben) oder auf der Registerkarte Power Query (wenn Sie Excel 2010-2013 haben und Power Query als separates Add-In installiert haben) und klicken Sie auf die Schaltfläche Aus Tabelle/Bereich. :
Unsere intelligente Tabelle wird in den Power Query-Abfrageeditor geladen, wo der erste Schritt darin besteht, die Zahlenformate für jede Spalte mithilfe der Dropdowns im Tabellenkopf einzurichten:
Um das Budget pro Tag zu berechnen, müssen Sie die Dauer jedes Projekts berechnen. Wählen Sie dazu (Taste gedrückt halten Ctrl) Spalte zuerst Endziel, und dann Startseite und wähle ein Team Spalte hinzufügen – Datum – Tage subtrahieren (Spalte hinzufügen — Datum — Tage subtrahieren):
Die resultierenden Zahlen sind 1 weniger als nötig, da wir jedes Projekt am ersten Tag morgens beginnen und am letzten Tag abends beenden sollen. Wählen Sie daher die resultierende Spalte aus und fügen Sie ihr mit dem Befehl eine Einheit hinzu Transformieren – Standard – Hinzufügen (Umwandeln – Standard – Hinzufügen):
Lassen Sie uns nun eine Spalte hinzufügen, in der wir das Budget pro Tag berechnen. Dazu auf der Registerkarte In Spalte Ich spiele nicht Benutzerdefinierte Spalte (Benutzerdefinierte Spalte) Geben Sie im angezeigten Fenster den Namen des neuen Felds und die Berechnungsformel ein, indem Sie die Namen der Spalten aus der Liste verwenden:
Jetzt der subtilste Moment – wir erstellen eine weitere berechnete Spalte mit einer Liste von Daten von Anfang bis Ende mit einem Schritt von 1 Tag. Drücken Sie dazu erneut die Taste Benutzerdefinierte Spalte (Benutzerdefinierte Spalte) und verwenden Sie die integrierte Power Query-Sprache M, die aufgerufen wird Liste.Termine:
Diese Funktion hat drei Argumente:
- Startdatum – in unserem Fall wird es aus der Spalte übernommen Startseite
- die Anzahl der zu generierenden Termine – in unserem Fall ist dies die Anzahl der Tage für jedes Projekt, die wir weiter oben in der Spalte gezählt haben Subtraktion
- Zeitschritt – vom Design festgelegt #Dauer(1,0,0,0), was in der Sprache von M bedeutet – ein Tag, null Stunden, null Minuten, null Sekunden.
Nach dem Klicken auf OK erhalten wir eine Liste (List) von Daten, die mit dem Button im Tabellenkopf in neue Zeilen expandiert werden kann:
… und wir erhalten:
Jetzt müssen Sie nur noch die Tabelle reduzieren und die generierten Daten als Namen für die neuen Spalten verwenden. Dafür ist das Team verantwortlich. Detailspalte (Pivot-Spalte) Tab Konvertieren (Verwandeln):
Nach dem Klicken auf OK Wir erhalten ein Ergebnis, das dem gewünschten sehr nahe kommt:
Null ist in diesem Fall ein Analogon zu einer leeren Zelle in Excel.
Es bleibt, unnötige Spalten zu entfernen und die resultierende Tabelle neben den Originaldaten mit dem Befehl zu entladen Schließen und laden – Schließen und laden… (Schließen & Laden — Schließen & Laden nach…) Tab Startseite (Home):
Als Ergebnis erhalten wir:
Für mehr Schönheit können Sie das Erscheinungsbild der resultierenden intelligenten Tabellen auf der Registerkarte anpassen Bauherr (Design): Legen Sie einen einzelnen Farbstil fest, deaktivieren Sie Filterschaltflächen, aktivieren Sie Summen usw. Außerdem können Sie eine Tabelle mit Datumsangaben auswählen und die Hervorhebung von Zahlen dafür aktivieren, indem Sie die bedingte Formatierung auf der Registerkarte verwenden Startseite — Bedingte Formatierung — Farbskalen (Startseite — Bedingte Formatierung — Farbskalen):
Und das Beste daran ist, dass Sie in Zukunft sicher alte bearbeiten oder neue Projekte zur ursprünglichen Tabelle hinzufügen und dann die rechte Tabelle mit Datumsangaben mit der rechten Maustaste aktualisieren können – und Power Query wiederholt alle von uns durchgeführten Aktionen automatisch .
Voilà!
- Gantt-Diagramm in Excel mit bedingter Formatierung
- Projekt-Meilenstein-Kalender
- Generieren doppelter Zeilen mit Power Query