Inhalte
Viele Geschäftsprozesse (und sogar ganze Unternehmen) in diesem Leben beinhalten die Erfüllung von Aufträgen durch eine begrenzte Anzahl von Ausführenden innerhalb einer bestimmten Frist. Die Planung erfolgt in solchen Fällen, wie man so schön sagt, „aus dem Kalender“ und oft besteht die Notwendigkeit, die darin geplanten Ereignisse (Bestellungen, Besprechungen, Lieferungen) nach Microsoft Excel zu übertragen – zur weiteren Analyse durch Formeln, Pivot-Tabellen, Charts, usw.
Natürlich möchte ich eine solche Übertragung nicht durch dummes Kopieren (was halt nicht schwer ist) realisieren, sondern mit automatischer Aktualisierung der Daten, damit in Zukunft alle Änderungen am Kalender und neue Bestellungen on the fly angezeigt würden Excel. Sie können einen solchen Import in wenigen Minuten mit dem in Microsoft Excel integrierten Power Query-Add-In implementieren, beginnend mit der Version 2016 (für Excel 2010-2013 kann es von der Microsoft-Website heruntergeladen und über den Link separat installiert werden). .
Angenommen, wir verwenden den kostenlosen Google-Kalender für die Planung, in dem ich der Einfachheit halber einen separaten Kalender erstellt habe (die Schaltfläche mit einem Pluszeichen in der unteren rechten Ecke neben Andere Kalender) mit dem Titel Arbeiten. Hier erfassen wir alle Bestellungen, die abgeschlossen und an die Kunden an deren Adressen geliefert werden müssen:
Durch Doppelklicken auf eine Bestellung können Sie ihre Details anzeigen oder bearbeiten:
Beachten Sie, dass:
- Der Name der Veranstaltung lautet Managerdie diesen Auftrag erfüllt (Elena) und Bestellnummer
- Angezeigt Adresse Lieferanten
- Der Vermerk enthält (in separaten Zeilen, aber in beliebiger Reihenfolge) die Auftragsparameter: Zahlungsart, Betrag, Kundenname etc. im Format Parameter=Wert.
Der Übersichtlichkeit halber sind die Aufträge jedes Managers in einer eigenen Farbe hervorgehoben, obwohl dies nicht notwendig ist.
Schritt 1. Holen Sie sich einen Link zu Google Kalender
Zuerst benötigen wir einen Weblink zu unserem Bestellkalender. Klicken Sie dazu auf die Schaltfläche mit den drei Punkten Kalenderoptionen funktionieren neben dem Namen des Kalenders und wählen Sie den Befehl aus Einstellungen und Freigabe:
In dem sich öffnenden Fenster können Sie den Kalender auf Wunsch öffentlich machen oder den Zugriff darauf für einzelne Benutzer freigeben. Außerdem benötigen wir einen Link für den privaten Zugriff auf den Kalender im iCal-Format:
Schritt 2. Laden Sie Daten aus dem Kalender in Power Query
Öffnen Sie nun Excel und auf der Registerkarte Datum (wenn Sie Excel 2010-2013 haben, dann auf der Registerkarte Power Query) Wählen Sie einen Befehl aus Aus dem Internet (Daten — aus dem Internet). Fügen Sie dann den kopierten Pfad in den Kalender ein und klicken Sie auf OK.
Die iCal Power Query erkennt das Format nicht, aber es ist einfach zu helfen. Im Wesentlichen ist iCal eine einfache Textdatei mit einem Doppelpunkt als Trennzeichen, und darin sieht es ungefähr so aus:
Sie können also einfach mit der rechten Maustaste auf das Symbol der heruntergeladenen Datei klicken und das Format auswählen, das der Bedeutung am nächsten kommt CSV – und unsere Daten über alle Bestellungen werden in den Power Query-Abfrageeditor geladen und per Doppelpunkt in zwei Spalten unterteilt:
Wenn Sie genau hinsehen, können Sie das deutlich erkennen:
- Informationen zu jedem Ereignis (Auftrag) sind in einem Block gruppiert, der mit dem Wort BEGIN beginnt und mit END endet.
- Die Start- und Enddaten werden in Zeichenfolgen mit der Bezeichnung DTSTART und DTEND gespeichert.
- Die Lieferadresse ist LOCATION.
- Bestellnotiz – Feld BESCHREIBUNG.
- Ereignisname (Name des Managers und Auftragsnummer) – Feld ZUSAMMENFASSUNG.
Es bleibt, diese nützlichen Informationen zu extrahieren und in eine praktische Tabelle umzuwandeln.
Schritt 3. Konvertieren Sie in die normale Ansicht
Führen Sie dazu die folgende Aktionskette aus:
- Lassen Sie uns vor dem ersten BEGIN-Befehl die obersten 7 Zeilen löschen, die wir nicht benötigen Start — Zeilen löschen — Obere Zeilen löschen (Start — Zeilen entfernen — Obere Zeilen entfernen).
- Nach Spalte filtern Column1 Zeilen mit den benötigten Feldern: DTSTART, DTEND, DESCRIPTION, LOCATION und SUMMARY.
- Auf der Registerkarte Erweitert Spalte hinzufügen wählen Indexspalte (Spalte hinzufügen – Indexspalte)um unseren Daten eine Zeilennummerspalte hinzuzufügen.
- Genau dort auf der Registerkarte. Spalte hinzufügen wähle ein Team Bedingte Spalte (Spalte hinzufügen – Bedingte Spalte) und am Anfang jedes Blocks (Bestellung) zeigen wir den Wert des Index an:
- Füllen Sie die leeren Zellen in der resultierenden Spalte aus Blockierenindem Sie mit der rechten Maustaste auf den Titel klicken und den Befehl auswählen Abfüllen (Abfüllen).
- Entfernen Sie unnötige Spalten Index.
- Wählen Sie eine Spalte aus Column1 und eine Faltung der Daten aus der Spalte durchzuführen Column2 mit dem Befehl Transformieren – Pivot-Spalte (Transformieren – Pivot-Spalte). Achten Sie darauf, in den Optionen auszuwählen Nicht aggregieren (nicht aggregieren)damit keine mathematische Funktion auf die Daten angewendet wird:
- Löschen Sie in der resultierenden zweidimensionalen (Kreuz-)Tabelle die Backslashes in der Adressspalte (Rechtsklick auf die Spaltenüberschrift – Werte ersetzen) und entfernen Sie die unnötige Spalte Blockieren.
- Um den Inhalt der Spalten zu drehen DTSTART и DTEND in einer vollständigen Datum-Uhrzeit, markieren Sie sie, wählen Sie auf der Registerkarte Transformieren – Datum – Analyse ausführen (Umwandeln – Datum – Analysieren). Dann korrigieren wir den Code in der Formelleiste, indem wir die Funktion ersetzen Stammen aus on DateTime.Fromum Zeitwerte nicht zu verlieren:
- Dann teilen wir die Spalte, indem wir mit der rechten Maustaste auf die Kopfzeile klicken BESCHREIBUNG mit Auftragsparametern durch Trennzeichen – Symbol n, aber gleichzeitig wählen wir in den Parametern die Aufteilung in Zeilen und nicht in Spalten:
- Wieder teilen wir die resultierende Spalte in zwei getrennte – den Parameter und den Wert, aber durch das Gleichheitszeichen.
- Spalte auswählen BESCHREIBUNG.1 Führen Sie die Faltung wie zuvor mit dem Befehl durch Transformieren – Pivot-Spalte (Transformieren – Pivot-Spalte). Die Wertspalte ist in diesem Fall die Spalte mit Parameterwerten − BESCHREIBUNG.2 Stellen Sie sicher, dass Sie eine Funktion in den Parametern auswählen Nicht aggregieren (nicht aggregieren):
- Es bleibt, die Formate für alle Spalten festzulegen und sie wie gewünscht umzubenennen. Und Sie können die Ergebnisse mit dem Befehl wieder in Excel hochladen Home — Schließen und laden — Schließen und laden in… (Home — Schließen&Laden — Schließen&Laden nach…)
Und hier ist unsere Liste der Bestellungen, die aus Google Kalender in Excel geladen wurden:
Künftig reicht es beim Ändern oder Hinzufügen neuer Bestellungen zum Kalender nur noch aus, unsere Anfrage mit dem Befehl zu aktualisieren Daten – Alle aktualisieren (Daten — Alle aktualisieren).
- Fabrikkalender in Excel, aktualisiert aus dem Internet über Power Query
- Umwandlung einer Spalte in eine Tabelle
- Erstellen Sie eine Datenbank in Excel