Table des matières
Calendrier de production, c'est-à-dire une liste de dates, où tous les jours ouvrables et jours fériés officiels sont marqués en conséquence - une chose absolument nécessaire pour tout utilisateur de Microsoft Excel. En pratique, vous ne pourrez plus vous en passer :
- dans les calculs comptables (salaire, ancienneté, congés…)
- en logistique – pour la détermination correcte des délais de livraison, en tenant compte des week-ends et des jours fériés (rappelez-vous le classique « viens après les vacances ? »)
- dans la gestion de projet – pour l'estimation correcte des délais, en tenant compte, encore une fois, des jours ouvrés-chômés
- toute utilisation de fonctions telles que JOURNEE DE TRAVAIL (JOURNÉE DE TRAVAIL) or TRAVAILLEURS PURS (JOURS OUVRABLES), car ils nécessitent une liste de jours fériés comme argument
- lors de l'utilisation des fonctions Time Intelligence (telles que TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) dans Power Pivot et Power BI
- … etc. etc. – beaucoup d'exemples.
C'est plus facile pour ceux qui travaillent dans des systèmes ERP d'entreprise tels que 1C ou SAP, car le calendrier de production y est intégré. Mais qu'en est-il des utilisateurs d'Excel ?
Vous pouvez, bien sûr, conserver un tel calendrier manuellement. Mais ensuite, vous devrez le mettre à jour au moins une fois par an (voire plus souvent, comme dans le "jolly" 2020), en saisissant soigneusement tous les week-ends, transferts et jours non ouvrables inventés par notre gouvernement. Et puis répétez cette procédure chaque année suivante. Ennui.
Que diriez-vous de devenir un peu fou et de créer un calendrier d'usine «perpétuel» dans Excel? Un qui se met à jour, prend des données sur Internet et génère toujours une liste à jour des jours non ouvrables pour une utilisation ultérieure dans tous les calculs ? Tentant?
Faire cela, en fait, n'est pas du tout difficile.
La source de données
La principale question est de savoir où obtenir les données ? A la recherche d'une source appropriée, je suis passé par plusieurs options:
- Les décrets originaux sont publiés sur le site Internet du gouvernement au format PDF (ici, l'un d'entre eux, par exemple) et disparaissent immédiatement – aucune information utile ne peut en être extraite.
- Une option tentante, à première vue, semblait être le « Portail de données ouvertes de la Fédération », où se trouve un ensemble de données correspondant, mais, après un examen plus approfondi, tout s'est avéré triste. Le site est terriblement peu pratique pour l'importation dans Excel, le support technique ne répond pas (auto-isolé ?), et les données elles-mêmes y sont obsolètes depuis longtemps – le calendrier de production pour 2020 a été mis à jour pour la dernière fois en novembre 2019 (honte !) et , bien sûr, ne contient pas notre « coronavirus » et le week-end de « vote » de 2020, par exemple.
Désabusé par les sources officielles, j'ai commencé à creuser des sources non officielles. Il y en a beaucoup sur Internet, mais la plupart d'entre eux, encore une fois, sont totalement inadaptés à l'importation dans Excel et donnent un calendrier de production sous la forme de belles images. Mais ce n'est pas à nous de l'accrocher au mur, non ?
Et au cours de la recherche, une chose merveilleuse a été découverte accidentellement - le site http://xmlcalendar.ru/
Sans « fioritures » inutiles, un site simple, léger et rapide, taillé pour une tâche – donner à chacun un calendrier de production pour l'année souhaitée au format XML. Excellent!
Si, tout à coup, vous n'êtes pas au courant, alors XML est un format texte dont le contenu est balisé avec des
Au cas où, j'ai contacté les auteurs du site et ils m'ont confirmé que le site existe depuis 7 ans, les données dessus sont constamment mises à jour (ils ont même une branche sur github pour cela) et ils ne vont pas le fermer. Et cela ne me dérange pas du tout que vous et moi chargions des données à partir de celui-ci pour l'un de nos projets et calculs dans Excel. Est libre. C'est bon de savoir qu'il y a encore des gens comme ça ! Respect!
Il reste à charger ces données dans Excel à l'aide du complément Power Query (pour les versions d'Excel 2010-2013, il peut être téléchargé gratuitement sur le site Web de Microsoft, et dans les versions d'Excel 2016 et plus récentes, il est déjà intégré par défaut ).
La logique des actions sera la suivante :
- Nous faisons une demande de téléchargement de données à partir du site pour une année
- Transformer notre requête en fonction
- Nous appliquons cette fonction à la liste de toutes les années disponibles, à partir de 2013 et jusqu'à l'année en cours – et nous obtenons un calendrier de production « perpétuel » avec mise à jour automatique. Voilà!
Étape 1. Importer un calendrier pour un an
Tout d'abord, chargez le calendrier de production pour une année quelconque, par exemple pour 2020. Pour ce faire, dans Excel, accédez à l'onglet Données (ou Requête d'alimentationsi vous l'avez installé en tant que module complémentaire séparé) et sélectionnez À partir d'Internet (À partir du Web). Dans la fenêtre qui s'ouvre, collez le lien vers l'année correspondante, copié depuis le site :
Après avoir cliqué sur OK une fenêtre d'aperçu apparaît, dans laquelle vous devez cliquer sur le bouton Convertir des données (Transformer les données) or Pour modifier les données (Modifier les données) et nous arriverons à la fenêtre de l'éditeur de requête Power Query, où nous continuerons à travailler avec les données :
Immédiatement, vous pouvez supprimer en toute sécurité dans le panneau de droite Paramètres de demande (Paramètres de la requête) étape type modifié (type modifié) Nous n'avons pas besoin de lui.
Le tableau dans la colonne des jours fériés contient les codes et les descriptions des jours chômés - vous pouvez voir son contenu en le "tombant" deux fois en cliquant sur le mot vert lampe de table:
Pour revenir en arrière, vous devrez supprimer dans le panneau de droite toutes les étapes qui sont apparues jusqu'à Identifier (La source).
Le deuxième tableau, accessible de la même manière, contient exactement ce dont nous avons besoin - les dates de tous les jours non ouvrables :
Il reste à traiter cette plaque, à savoir :
1. Filtrer uniquement les dates de vacances (c'est-à-dire celles) par la deuxième colonne Attribut :t
2. Supprimer toutes les colonnes sauf la première - en cliquant avec le bouton droit sur l'en-tête de la première colonne et en sélectionnant la commande Supprimer les autres colonnes (Supprimer les autres colonnes):
3. Diviser la première colonne par point séparément pour le mois et le jour avec la commande Fractionner la colonne - Par délimiteur languette De La Carrosserie (Transformer — Fractionner la colonne — Par délimiteur):
4. Et enfin créer une colonne calculée avec des dates normales. Pour cela, dans l'onglet Ajout d'une colonne cliquez sur le bouton Colonne personnalisée (Ajouter une colonne - Colonne personnalisée) et entrez la formule suivante dans la fenêtre qui apparaît :
=#daté(2020, [# »Attribut : d.1 ″], [# »Attribut : d.2 ″])
Ici, l'opérateur #date a trois arguments : année, mois et jour, respectivement. Après avoir cliqué sur OK nous obtenons la colonne requise avec les dates de week-end normales et supprimons les colonnes restantes comme à l'étape 2
Étape 2. Transformer la requête en fonction
Notre prochaine tâche consiste à convertir la requête créée pour 2020 en une fonction universelle pour n'importe quelle année (le numéro de l'année sera son argument). Pour ce faire, nous procédons comme suit :
1. Développer (s'il n'est pas déjà développé) le panneau Messages (Requêtes) à gauche dans la fenêtre Power Query :
2. Après avoir converti la requête en fonction, la possibilité de voir les étapes qui composent la requête et de les modifier facilement disparaît malheureusement. Par conséquent, il est logique de faire une copie de notre demande et de gambader déjà avec elle, et de laisser l'original en réserve. Pour ce faire, faites un clic droit dans le volet de gauche sur notre demande de calendrier et sélectionnez la commande Dupliquer.
Un nouveau clic droit sur la copie résultante de calendar(2) sélectionnera la commande renommer (Renommer) et entrez un nouveau nom - que ce soit, par exemple, fxAnnée:
3. Nous ouvrons le code source de la requête dans le langage interne de Power Query (il est succinctement appelé « M ») à l'aide de la commande Éditeur avancé languette Avis(Affichage — Éditeur avancé) et y apporter de petits changements pour transformer notre demande en une fonction pour n'importe quelle année.
Il était la suivante:
Après:
Si vous êtes intéressé par les détails, alors ici:
- (année en nombre)=> – nous déclarons que notre fonction aura un argument numérique – une variable an
- Coller la variable an vers le lien Web à l'étape Identifier. Étant donné que Power Query ne vous permet pas de coller des chiffres et du texte, nous convertissons le numéro de l'année en texte à la volée à l'aide de la fonction Number.ToText
- Nous remplaçons la variable année par 2020 dans l'avant-dernière étape #"Objet personnalisé ajouté«, où nous avons formé la date à partir des fragments.
Après avoir cliqué sur Finition notre requête devient une fonction :
Étape 3. Importer des calendriers pour toutes les années
La dernière chose qui reste est de faire la dernière requête principale, qui téléchargera les données pour toutes les années disponibles et ajoutera toutes les dates de vacances reçues dans une table. Pour ça:
1. Nous cliquons dans le panneau de requête de gauche dans un espace vide gris avec le bouton droit de la souris et sélectionnons séquentiellement Nouvelle requête – Autres sources – Requête vide (Nouvelle requête — À partir d'autres sources — Requête vide):
2. Nous devons générer une liste de toutes les années pour lesquelles nous demanderons des calendriers, c'est-à-dire 2013, 2014… 2020. Pour cela, dans la barre de formule de la requête vide qui s'affiche, entrez la commande :
Structure:
={NombreA..NombreB}
… dans Power Query génère une liste d'entiers de A à B. Par exemple, l'expression
={1..5}
… produirait une liste de 1,2,3,4,5.
Eh bien, pour ne pas être lié de manière rigide à 2020, nous utilisons la fonction DateTime.LocalNow() – analogue de la fonction Excel AUJOURD'HUI (AUJOURD'HUI) dans Power Query – et en extraire, à son tour, l'année en cours par la fonction Date.Année.
3. L'ensemble d'années qui en résulte, bien qu'il semble tout à fait adéquat, n'est pas une table pour Power Query, mais un objet spécial - liste (Liste). Mais la convertir en table n'est pas un problème : il suffit de cliquer sur le bouton À la table (À la table) dans le coin supérieur gauche :
4. Ligne d'arrivée! Application de la fonction que nous avons créée précédemment fxAnnée à la liste d'années qui en résulte. Pour cela, dans l'onglet Ajout d'une colonne appuie sur le bouton Appeler la fonction personnalisée (Ajouter une colonne - Appeler une fonction personnalisée) et définissez son seul argument - la colonne Column1 au cours des années:
Après avoir cliqué sur OK notre fonction fxAnnée l'import fonctionnera à tour de rôle pour chaque année et nous obtiendrons une colonne où chaque cellule contiendra un tableau avec les dates des jours chômés (le contenu du tableau est bien visible si vous cliquez dans le fond de la cellule à côté de le mot lampe de table):
Il reste à dérouler le contenu des tableaux imbriqués en cliquant sur l'icône à doubles flèches en entête de colonne Dates (cocher Utiliser le nom de la colonne d'origine comme préfixe il peut être retiré):
… et après avoir cliqué sur OK nous obtenons ce que nous voulions - une liste de tous les jours fériés de 2013 à l'année en cours :
La première colonne, déjà inutile, peut être supprimée, et pour la seconde, définissez le type de données données (Date) dans la liste déroulante de l'en-tête de colonne :
La requête elle-même peut être renommée quelque chose de plus significatif que Demande1 puis uploadez les résultats sur la feuille sous forme de tableau « intelligent » dynamique à l'aide de la commande fermer et télécharger languette Accueil (Accueil — Fermer et charger):
Vous pouvez mettre à jour le calendrier créé à l'avenir en cliquant avec le bouton droit sur la table ou en interrogeant dans le volet de droite via la commande Mettre à jour et enregistrer. Ou utilisez le bouton Rafraîchir tout languette Données (Date — Actualiser tout) ou raccourci clavier Ctrl+autre+F5.
C'est tout.
Désormais, vous n'aurez plus jamais besoin de perdre du temps et de réfléchir à la recherche et à la mise à jour de la liste des jours fériés - vous disposez désormais d'un calendrier de production "perpétuel". En tout cas, tant que les auteurs du site http://xmlcalendar.ru/ soutiennent leur progéniture, ce qui, je l'espère, le sera pour très, très longtemps (encore merci à eux !).
- Importer le taux de bitcoin vers Excel à partir d'Internet via Power Query
- Recherche du jour ouvrable suivant à l'aide de la fonction WORKDAY
- Trouver l'intersection d'intervalles de dates