Table des matières
De nombreux processus commerciaux (et même des entreprises entières) dans cette vie impliquent l'exécution de commandes par un nombre limité d'interprètes dans un délai donné. Dans de tels cas, la planification se produit, comme on dit, "à partir du calendrier" et il est souvent nécessaire de transférer les événements qui y sont planifiés (commandes, réunions, livraisons) vers Microsoft Excel - pour une analyse plus approfondie par des formules, des tableaux croisés dynamiques, des graphiques, etc.
Bien sûr, je voudrais mettre en œuvre un tel transfert non pas par une copie stupide (ce qui n'est tout simplement pas difficile), mais avec une mise à jour automatique des données afin qu'à l'avenir, toutes les modifications apportées au calendrier et les nouvelles commandes à la volée soient affichées dans Exceller. Vous pouvez implémenter une telle importation en quelques minutes à l'aide du complément Power Query intégré à Microsoft Excel, à partir de la version 2016 (pour Excel 2010-2013, il peut être téléchargé à partir du site Web de Microsoft et installé séparément du lien) .
Supposons que nous utilisions le calendrier Google gratuit pour la planification, dans lequel j'ai, pour plus de commodité, créé un calendrier séparé (le bouton avec un signe plus dans le coin inférieur droit à côté de Autres calendriers) avec le titre Activités principales. Ici, nous saisissons toutes les commandes qui doivent être complétées et livrées aux clients à leurs adresses :
En double-cliquant sur n'importe quelle commande, vous pouvez afficher ou modifier ses détails :
Noter que:
- Le nom de l'événement est managerqui remplit cet ordre (Elena) et Numéro de commande
- Indiqué propos page de livraison.
- La note contient (sur des lignes séparées, mais dans n'importe quel ordre) les paramètres de la commande : type de paiement, montant, nom du client, etc. au format Paramètre=Valeur.
Pour plus de clarté, les commandes de chaque gestionnaire sont surlignées dans leur propre couleur, bien que cela ne soit pas nécessaire.
Étape 1. Obtenir un lien vers Google Agenda
Nous devons d'abord obtenir un lien Web vers notre calendrier de commandes. Pour cela, cliquez sur le bouton avec trois points Les options de calendrier fonctionnent à côté du nom du calendrier et sélectionnez la commande Paramètres et partage:
Dans la fenêtre qui s'ouvre, vous pouvez, si vous le souhaitez, rendre le calendrier public ou en ouvrir l'accès à des utilisateurs individuels. Nous avons également besoin d'un lien pour un accès privé au calendrier au format iCal :
Étape 2. Charger les données du calendrier dans Power Query
Ouvrez maintenant Excel et sur l'onglet Données (si vous avez Excel 2010-2013, alors sur l'onglet Requête d'alimentation) choisissez une commande À partir d'Internet (Données — À partir d'Internet). Collez ensuite le chemin copié dans le calendrier et cliquez sur OK.
L'iCal Power Query ne reconnaît pas le format, mais il est facile de vous aider. Essentiellement, iCal est un fichier texte brut avec deux-points comme délimiteur, et à l'intérieur il ressemble à ceci :
Vous pouvez donc simplement cliquer avec le bouton droit sur l'icône du fichier téléchargé et sélectionner le format qui a le sens le plus proche CSV – et nos données sur toutes les commandes seront chargées dans l'éditeur de requête Power Query et divisées en deux colonnes par deux-points :
Si vous regardez attentivement, vous pouvez clairement voir que :
- Les informations sur chaque événement (commande) sont regroupées dans un bloc commençant par le mot BEGIN et se terminant par END.
- Les dates et heures de début et de fin sont stockées dans des chaînes étiquetées DTSTART et DTEND.
- L'adresse de livraison est LOCATION.
- Bon de commande – Champ DESCRIPTION.
- Nom de l'événement (nom du responsable et numéro de commande) — champ RÉSUMÉ.
Il reste à extraire ces informations utiles et à les transformer en un tableau pratique.
Étape 3. Convertir en vue normale
Pour ce faire, effectuez la chaîne d'actions suivante :
- Supprimons les 7 premières lignes dont nous n'avons pas besoin avant la première commande BEGIN Accueil — Supprimer les lignes — Supprimer les lignes du haut (Accueil - Supprimer les lignes - Supprimer les lignes supérieures).
- Filtrer par colonne Column1 lignes contenant les champs dont nous avons besoin : DTSTART, DTEND, DESCRIPTION, LOCATION et SUMMARY.
- Dans l'onglet Avancé Ajout d'une colonne '; '; ; Colonne d'index (Colonne Ajouter — Colonne Index)pour ajouter une colonne de numéro de ligne à nos données.
- Juste là sur l'onglet. Ajout d'une colonne choisir une équipe Colonne conditionnelle (Ajouter une colonne — Colonne conditionnelle) et au début de chaque bloc (commande) on affiche la valeur de l'index :
- Remplissez les cellules vides dans la colonne résultante Blocken faisant un clic droit sur son titre et en sélectionnant la commande Remplissez (Remplissez).
- Supprimer la colonne inutile Sommaire.
- Sélectionnez une colonne Column1 et effectuer une convolution des données de la colonne Column2 en utilisant la commande Transformer - Pivot Colonne (Transformer - Pivot colonne). Assurez-vous de sélectionner dans les options Ne pas agréger (Ne pas agréger)afin qu'aucune fonction mathématique ne soit appliquée aux données :
- Dans le tableau bidimensionnel (croisé) résultant, effacez les barres obliques inverses dans la colonne d'adresse (clic droit sur l'en-tête de colonne - Remplacement des valeurs) et supprimer la colonne inutile Block.
- Pour transformer le contenu des colonnes DTSTART и DTENDRE dans une date-heure complète, en les mettant en surbrillance, sélectionnez dans l'onglet Transformer – Dater – Exécuter l'analyse (Transformer — Date — Analyser). Puis on corrige le code dans la barre de formule en remplaçant la fonction Date.Du on DateHeure.Depour ne pas perdre les valeurs temporelles :
- Ensuite, en faisant un clic droit sur l'en-tête, nous divisons la colonne DESCRIPTION avec paramètres de commande par séparateur – symbole n, mais en même temps, dans les paramètres, on sélectionnera la division en lignes, et non en colonnes :
- Encore une fois, nous divisons la colonne résultante en deux colonnes distinctes - le paramètre et la valeur, mais par le signe égal.
- Sélection d'une colonne DESCRIPTIF.1 effectuer la convolution, comme nous l'avons fait précédemment, avec la commande Transformer - Pivot Colonne (Transformer - Pivot colonne). La colonne de valeur dans ce cas sera la colonne avec les valeurs de paramètre - DESCRIPTIF.2 Assurez-vous de sélectionner une fonction dans les paramètres Ne pas agréger (Ne pas agréger):
- Il reste à définir les formats pour toutes les colonnes et à les renommer comme vous le souhaitez. Et vous pouvez télécharger les résultats vers Excel avec la commande Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger vers…)
Et voici notre liste de commandes chargées dans Excel depuis Google Calendar :
À l'avenir, lors de la modification ou de l'ajout de nouvelles commandes au calendrier, il suffira de mettre à jour notre demande avec la commande Données – Tout actualiser (Données — Actualiser tout).
- Calendrier d'usine dans Excel mis à jour à partir d'Internet via Power Query
- Transformer une colonne en tableau
- Créer une base de données dans Excel