Un planning annuel vite fait bien fait avec Calc et Excel

mercredi 14 février 2018, par ID

L’idée : concevoir un planning annuel fiable qui permette de calculer des durées, mette en relief automatiquement les week-ends et puisse être exploité soit en utilisant des sous-totaux, soit, mieux, avec tables pilotes ou tables croisées dynamiques. Et, comme ce tableau a été fait sous les tableurs des suites LibreOffice (Calc) et MS Office (Excel), cela nous donne aussi une bonne opportunité de comparer les deux ergonomies.

 Pour commencer : les types de données

Réfléchir préalablement au type de données et à leur organisation dans un tableau est le point de départ qui en permet une bonne conception et une bonne utilisation.

Ici on a besoin de dix colonnes :

  • des mois pour faire des analyses mensuelles, une colonne au format date n’indiquant que les mois, colonne A ;
  • des jours en toutes lettres pour plus de lisibilité et pour les mises en forme conditionnelles qui mettront en évidence les samedis et les dimanches, une colonne de texte, colonne B ;
  • des dates, une colonne au format date, colonne C ;
  • des heures de début et de fin en matinées donc 2 colonnes au format heure, colonnes D et E ;
  • du nombre d’heures de la matinée, donc une colonne au format heure qui calcule la différence entre les deux heures précédentes, colonne F ;
  • des colonnes équivalentes pour l’après-midi, donc 3 colonnes au format heure, colonnes G, H et I ;
  • d’une dernière colonne au format heure qui va additionner les heures du matin et celles de l’après-midi, colonne J.
Un tableau fonctionnel
Le résultat que l’on veut obtenir avec une mise en évidence des fins de semaines.

 Dates, jours et mois : mise en forme et ajout des données

Saisir le premier jour de l’année dans la colonne C (celle des dates). Si on veut un autre format que celui par défaut, clic droit et Formater les cellules (Calc) ou Format de cellules (Excel). Sélectionner les cellules de la colonne de C2 à C366.

🔧 Astuce : pour aller plus vite, saisir les bornes de la sélection dans la zone de nom de la barre de formule sous cette forme : C2:C366.

Aller sur Remplir (menu Feuille, LibreOffice) ou Recopier (Excel), cliquer sur Série.

Série dans LibreOffice et Excel
À gauche LibreOffice, à droite Excel.

Dans la boite de dialogue, cocher Date (LibreOffice) ou Chronologie (Excel), puis Jour. Indiquer le pas, ici 1 et la dernière valeur sous cette forme : 31/12/2018.

Les paramètres de la série de données.
Dans LibreOffice, à gauche, on peut repréciser au besoin la première valeur.

Pour le jour, saisir le nom du jour et tirer pour avoir la semaine sans qu’il soit nécessaire de saisir les autres jours, on utilise ainsi la fonctionnalité de liste utilisateur (ou personnalisée). Cette année 2018 commence un lundi, mais ça fonctionne tout aussi bien avec un autre jour. Sélectionner les cellules concernées, ici B2:B366, aller comme précédemment sur les fonctionnalités de remplissage de série (ou recopie) et cocher Remplir automatique (LibreOffice) ou Recopie incrémentée (Excel).

Pour le mois, on formate, pour commencer, la première cellule, ici A2, au format date pour commencer puis on personnalise en indiquant ce format-ci : MMMM. Ainsi on a les noms des mois en toutes lettres et rien que cela. Indiquer que cette cellule est égale à la cellule C2, donc saisir =C2. Sélectionner l’étendue comme précédemment, aller dans les fonctionnalités de remplissage et sélectionner Vers le bas ou En bas. De cette façon, d’une part on est sûr que les mois correspondent aux jours et, d’autre part, on peut analyser le tableau en fonction des mois.

Évidemment, le format du mois peut faire l’objet d’un style pour plus d’efficacité.

On a notre calendrier !

 Les heures : formats et calculs

Les heures posent des difficultés supplémentaires par rapport aux dates, en effet, selon le choix du format de base, elles seront calculées au format journalier, le compte se remettra à zéro systématiquement passées vingt-quatre heures, ou en continu de façon à pouvoir avoir un décompte supérieur à celui du nombre d’heures de la journée. Ici on va, pour simplifier et n’avoir qu’un format, opter pour la seconde solution. On peut, d’ailleurs, en faire des styles dans l’un ou l’autre tableur.

Commencer par saisir une heure pour avoir une idée du rendu en cours de conception du format.

⁠① Choisir la catégorie Heure ;

choisir le format 376613:37:46 (LibreOffice) ou 37:30:55 (Excel) ;

dans LibreOffice, en dessous, dans la Description du format, le personnaliser de façon à obtenir [HH]" h "MM ;

dans Excel, personnaliser le nombre de façon à avoir [HH]" h "MM ou [H]" h "MM comme précédemment pour LibreOffice.

Personnalisation de l’heure
À gauche, formatage de l’heure dans LibreOffice, à droite dans Excel.

Les calculs des heures du matin, en colonne F et de l’après-midi, colonne I sont des banales soustractions. Celui du nombre d’heures totales de la journée, colonne J, une simple addition.

Dans les trois cas, rentrer le calcul sur la première cellule concernée, sélectionner, au plus simple et plus efficace, en entrant les bornes dans la zone de nom comme précédemment. Puis, Remplir les cellules vers le bas (LibreOffice) ou les Recopier en bas (Excel).

Notre planning est presque prêt et peut déjà servir.

 Formatage conditionnel pour les fins de semaine

Le formatage conditionnel se présente et se fait de façon très différente dans les deux tableurs. Cette fois-ci, les deux tableurs sont traités séparément.

La condition est la même dans les deux cas : on veut que la ligne change de couleur, ici passe au rouge, quand il y a « dimanche » dans la colonne B ou devienne jaune quand il y a « samedi ».

On saisit les expressions « dimanche » et « samedi » dans une autre feuille, cela facilitera la maintenance du planning, on peut même les nommer si on veut, et, évidemment, masquer la feuille plus tard. Ici, dimanche est dans la cellule A1 de la feuille 2 et samedi dans la cellule A2.

 LibreOffice

Les formats conditionnels, dans LibreOffice, sont basés sur des styles, ce qui, soit dit en passant, en facilite la maintenance. On commencera donc par créer un style « samedi » et un style « dimanche ».

Afficher le Volet latéral si ce n’est déjà fait. Cliquer sur l’icône avec un « plus » en haut à droite pour créer un nouveau style que l’on va appeler « dimanche ».

Création d’un style
Lui donner un nom explicite en français normal.

Aller dans le panneau Styles, si ce n’est déjà fait, clic-droit sur le style et Modifier, lui donner une couleur d’arrière-plan, ici rouge. On pourra toujours modifier le style par la suite en passant par ce même volet.

Définition d’une couleur d’arrière-plan
La cellule dotée du style aura un fond rouge.

Dans la lancée, refaire un style pour le samedi. Dans l’exemple, le fond est jaune. On peut passer à la définition du format conditionnel.

Sélectionner la plage concernée (ici A2:J366).

Aller dans Format > Formatage conditionnel > Nouveau.

Formatage conditionnel dans Calc
Création d’un nouveau format conditionnel.

Choisir La formule est dans la liste en dessous du numéro de la condition, indiquer : $B2=$Feuille2$a$1, on peut aussi sélectionner les cellules plutôt que de les saisir, mais il faut taper le signe égal. Indiquer le style dimanche dans Appliquer le style.

Condition 1
Paramètres de la 1re condition dans Calc.

👓 À savoir : en ne mettant pas de $ devant le numéro de la cellule, on indique au tableur de s’adapter au numéro de ligne tout en gardant la référence à la colonne B.

Refaire pareil pour samedi en choisissant le style « kivabien ».

Voilà, c’est fini !

 Excel

Aller dans Mise en forme conditionnelle > Nouvelle règle.

Choix du format conditionnel dans Excel
Cliquer sur Nouvelle règle pour obtenir le résultat escompté.

Dans la boite de dialogue Nouvelle règle de mise en forme, choisir Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué et indiquer $B2=Feuil2!$A$1, on peut, évidemment, sélectionner les cellules plutôt que d’en saisir les références. Excel rajoute le signe égal devant l’expression. Appuyer sur le bouton Format pour le paramétrer. On ne peut pas utiliser de styles.

Paramètres de la première condition
Dans Excel, après avoir indiqué la condition, il faut paramétrer le format individuellement.

👓 À savoir : en ne mettant pas de $ devant le numéro de la cellule, on indique au tableur de s’adapter au numéro de ligne tout en gardant la référence à la colonne B.

Refaire pareil pour samedi.

Voilà, c’est fini !