Eclatement selon un code

Nous avons en colonne A des codes d’affectation comptable et en colonne B des montants à comptabiliser.

L’objectif est de récupérer, dans les colonnes D à F, les montants selon le code comptable présent dans le titre de la colonne, en D1:F1.

La formule miracle, à entrer sous forme de formule matricielle – donc avec la combinaison [Ctrl]-[Maj]-[Entrée] – en D2:D9 est la suivante :
=index($B$2:$B$7;petite.valeur(si(D1=$A$2:$A$7;ligne($A$2:$A$7)-1);ligne($1:$10)))

On reproduit ensuite le bloc D2:D9 dans les colonnes E et F pour obtenir le résultat ci-dessus.

Pour mieux comprendre cette formule, il faut savoir que la partie petite.valeur (si(D1=$A$2:$A$7;ligne($A$2:$A$7)-1);ligne($1:$10)) de la formule est évaluée de la façon suivante : {1;4;6;#NOMBRE!;#NOMBRE!;#NOMBRE!;#NOMBRE!;#NOMBRE!;
#NOMBRE!;#NOMBRE!}.

Remarque 1 – On pourrait masquer les messages d’erreur par un format conditionnel mettant en police blanche toutes les cellules évaluées comme erreur, à l’aide de la formule =esterreur(D2)…

Remarque 2 – On pourrait éviter ces erreurs sans format conditionnel, mais au prix d’une formule nettement plus lourde : =si(ligne()-1<=nb(petite.valeur(si(H1=$A$2:$A$7; ligne ($A$2:$A$7)-1);ligne($1:$10)));index($B$2:$B$7;petite.valeur(si(H1=$A$2:$A$7;ligne($A$2:$A$7)-1);ligne ($1:$10)));"")

Prévisions avec une courbe en S

Supposons que vous ayez prévu, pour une activité dans votre entreprise, un chiffre d’affaires de 100.000 € en 15 périodes, qui peuvent être des semaines, des mois, des trimestres…

Vous pensez que l’évolution du CA cumulé devrait grosse modo suivre une courbe en S avec une pente moyenne d’environ 45°. Quelles formules faut-il utiliser pour les prévisions ?

Nous voyons ci-dessous la courbe du CA cumulé en bleu, correspondant à l’axe gauche. Les chiffres individuels sont représentés dans l’histogramme rouge correspondant à l’axe droit.

Voici les formules utilisées :
En D2, recopiée vers le bas : =$A$2*(1-cos(radians(180*C2/$A$5)))/2
En E2 : =D2
En E3, recopiée vers le bas : =D3-somme(E$2:E2)

Remarque – Quand on utilise deux axes, comme dans cet exemple, il est souhaitable – pour optimiser la lisibilité du graphe – d’associer comme nous le faisons ici une couleur spécifique et un type de graphe spécifique à chaque axe.

Masquage des valeurs nulles

Parfois, on préfère masquer dans un tableau toutes les valeurs nulles. Nous nous intéressons aujourd’hui aux différentes façons de le faire.

Masquage des « 0 » de la feuille active

On peut masquer d’un coup tous les « 0 » de la feuille active :
■ avec Excel 2003, commande « Outils Options », onglet « Affichage », bloc
« Fenêtres », commande « Valeurs zéro » ;
■ avec Excel 2007, bouton « Office », bouton « Options Excel », commande
« Options avancées », puis le bloc « Afficher les options pour cette feuille de calcul », et enfin décocher la commande « Afficher un zéro pour les cellules qui ont une valeur nulle ».

Masquage des « 0 » à l’aide d’une macro

Une macro d’une ligne permet de faire la bascule entre l’affichage ou non des
« 0 » de la feuille active, ce qui est nettement plus rapide que la procédure précédente :

Sub Cache_0()
ActiveWindow.DisplayZeros = Not (ActiveWindow.DisplayZeros)
End Sub

Masquage des « 0 » avec un format numérique

Si l’on souhaite ne masquer les « 0 » que dans une zone précise d’une feuille, on peut passer par un format numérique. Par exemple avec le format 0;-0; pour des valeurs entières.

Masquage des « 0 » avec un format conditionnel

Toujours pour masquer les « 0 » dans une zone précise d’une feuille, on peut enfin – bien évidemment – utiliser un format conditionnel qui met la police en blanc quand la valeur est nulle.

Remarque – Avec le format conditionnel, les « 0 » apparaissent en inverse quand on sélectionne le bloc, ce qui n’est pas le cas avec le format numérique…

Une mise à jour améliorée

Reprenons le modèle présenté il y a quatre jours. Supposons que, pour des raisons esthétiques, on ne souhaite pas voir apparaître les mois qui n’ont pas encore été validés, comme dans l’image ci-dessous, dans les colonnes D:G. :

L’astuce consiste à attribuer au bloc D2:D13 le format conditionnel ci-dessus qui met la police en blanc quand les cellules E2 et F2 sont toutes les deux vides, et qui encadre la cellule dans le cas contraire.

Pour le bloc E2:F13, il suffit d’utiliser la seule seconde condition ci-dessus, qui ajoute un encadrement quand les cellules E2 et F2 ne sont pas toutes les deux vides.

Remarque 1 – On voit dans cet exemple une utilisation intéressante mais peu connue des formats conditionnels !

Remarque 2 – On utilise =nbcar(E2&F2)=0 comme test au lieu de =E2+F2=0 pour le cas où la recette du mois serait égale à la dépense du mois !

Une mise à jour mensuelle

Un lecteur m’a récemment demandé de l’aider pour une petite application dans laquelle il entre en A3:B3 le montant de recette et de dépense du mois (l’écart étant affiché dans la cellule fusionnée A5:B5) et il veut – en cliquant sur un bouton – entrer ces deux valeurs dans la première ligne de mois non occupée. Il souhaite aussi voir en A11:B11 les totaux de recettes et dépenses pour l’année, ainsi que le solde correspondant en A13:B13.

Nous voyons ci-dessus ce que cela donne et la macro de deux lignes que j’ai créée pour la mise à jour mensuelle. Le nom « Input » a été attribué au bloc A3:B3. L’image de la macro a été collée dans la feuille.

Remarque 1 – Si l’on a fait la bêtise de valider deux fois de suite le même mois, il suffit d’effacer dans le dernier mois les valeurs en colonnes E et F. La formule en G2 est =si(et(E2=0;F2=0);"";E2-F2).

Remarque 2 – Je continuer à considérer que la fusion de cellules (en A5:B5 et A13:B13) est une véritable horreur. Lisez à ce sujet un des premiers articles de ce blog, « Ne fusionnez plus jamais vos cellules ! », daté du 17 novembre 2005.

Où sont les cellules de validation ?

Parfois, surtout quand on hérite d’un modèle développé par d’autres, on souhaite identifier toutes les cellules munies d’un contrôle de validation.

Pour cela, il faut utiliser la commande « Atteindre » :

■ avec Excel 2003 ou Excel XP, activez la commande « Atteindre » du menu
« Edition » ;

■ avec Excel 2007, c’est plus indirect : activez la sous-commande « Atteindre » du bouton « Rechercher et sélectionner » dans le bloc « Edition » de l’onglet
« Accueil »…

■ avec toutes les versions d’Excel, utilisez directement le raccourci [Ctrl]-t.

Nous voyons ci-dessous le dialogue de la commande « Atteindre » puis le dialogue qui apparaît quand on clique dans le bouton « Cellules… » :

Pour utiliser le bouton « Identiques », il faut préalablement avoir activé une cellule comportant un contrôle de validation. La commande sélectionne alors, à l'intérieur de la feuille active, toutes les cellules ayant le même contrôle de validation.

Entourer les données non valides

Les commandes « Entourer les données non valides » et « Effacer les cercles de validation » de la commande « Validation des données » d’Excel 2007 – cf. article du 29 mars – peuvent paraître nouvelles aux utilisateurs d’Excel 2003 ou XP.

Elles étaient pourtant déjà disponibles avec ces versions, mais il fallait les trouver, bien cachées dans la barre d’outils « Audit de formules » :

Utilisation de la commande « Entourer les données non valides »

Reprenons l’exemple de statistiques de nos articles du 20 et du 25 mars. Mettons en place une validation de données pour le bloc D1:D16 exigeant que le résultat obtenu soit un nombre décimal compris entre 0 et 3. Activons alors la commande « Entourer les données non valides », et voici le résultat :

Remarque 1 – La commande « Effacer les cercles de validation » devrait plutôt s’appeler « Effacer les marques d’invalidité ». Il faut avoir beaucoup bu – ou bien agrandi les lignes – pour imaginer que ces marques sont circulaires.

Remarque 2 – Pour une fois que je peux tresser des lauriers à Excel 2007, je souligne le fait que d’avoir réuni les commandes « Entourer les données non valides » et « Effacer les cercles de validation » dans le même menu que
« Validation des données » est une excellente idée !

Le cap des 10.000 visites par mois

Pour la première fois depuis sa création en octobre 2005, le blog « Monsieur Excel » a passé ce mois-ci le cap des 10.000 visites par mois. Comme vous pouvez le voir dans le graphe ci-dessous tiré des statistiques de consultation, le nombre de visites par mois est régulier, et naturellement saisonnier. Il suit depuis un an une croissance proche de 25%.

Quand vous demandez à Google de trouver les références relatives à « blog Excel », le blog « Monsieur Excel » est, comme nous le voyons ci-dessus – et ce depuis plus d’un an – la première référence naturelle de Google.

La consultation du blog est gratuite.

Les personnes abonnées au blog reçoivent tous les deux mois un fichier Acrobat avec les textes des articles, et divers fichiers Excel illustrant la mise en œuvre de tous les conseils diffusés dans le blog. Les abonnés au blog ont donc accès, avec leur abonnement, à une véritable programmathèque Excel et peuvent ainsi économiser des jours et des jours de développement.