Monsieur Excel
Pour tout savoir faire sur Excel !

18 octobre 2018

Modèle de facturation (g)

Nous sommes prêts à présent pour terminer la création des formules. Commençons par effacer les cellules prévues pour les totaux en G32:G34.

Nous réalisons que, si nous faisons la somme des totaux actuels en G27:G31, cela nous donnera une erreur, ce qui n’est ni agréable ni informatif. Modifions donc la formule de G27 avec :
=sierreur(F27*D27;"")

Mettons-nous à présent en G32 et utilisons S pour obtenir la somme de G27:G31. Et cela ne marche pas ! Excel s’arrête dès qu’il rencontre la chaîne de texte vide provoquée par le sierreur(). Il faut toujours faire attention avec le S car il ne fait pas toujours par défaut la somme à laquelle on s’attend…

Une astuce pour le S

Pour être sûr que la somme concerne bien les cellules voulues, l’astuce consiste à sélectionner préalablement les cellules à additionner et la cellule appelée à afficher le résultat : nous sélectionnons donc le bloc G27:G32 puis nous cliquons dans le S : la bonne formule apparaît alors aussitôt en G32 !

Calcul de la TVA

Pour la TVA, en G33, nous pourrions utiliser une formule telle que : =G32*droite(D33;5), vu que D33 contient le texte « Taux TVA 20,0% ». Cela marche très bien actuellement, mais pourrait se révéler erroné par la suite si l’on entrait par exemple un taux de 20,05%. Il est donc bien plus raisonnable d’entrer 20% en F33 puis de formater la cellule en "Taux TVA : "0%. Il suffira alors de cadrer la cellule à gauche pour qu’elle ressemble à du texte…

Le total TTC

Enfin, pour le total TTC en G34, nous constatons une fois de plus qu’un simple S ne donne pas le bon résultat. Il convient donc de sélectionner d’abord G32:G34 puis de faire S pour obtenir le résultat attendu.

Nous voyons ci-dessous le résultat obtenu avec toutes les bonnes cellules :



09 octobre 2018

Modèle de facturation (f)

Pour commence, reprenons l’idée de l’index(equiv()) proposée pour la cellule B29 dans l’article précédent et appliquons-la aux cellules D27 et D28, afin d’en garder une trace. Cela nous donne en D27 la formule suivante :
=index($D$5:$D$17;equiv(A27;$A$5:$A$17;0))

Et nous allons maintenant vous proposer un nouveau challenge : essayez d’entrer en B29 une formule qui fonctionne bien entendu en B29 mais aussi telle que l’on puisse la copier en D29 et qu’elle fonctionne aussitôt sans que l’on aie à changer quoi que ce soit. Essayez donc de résoudre ce problème avant de lire la suite…

Une formule plus universelle

L’idée de faire une formule qui fonctionne dans les deux colonnes est un bon exemple de ce que l’on peut faire quand on est un bon développeur Excel. Quand on utiliser ainsi une formule pour deux colonnes, on va deux fois plus vite et – contrairement à ce que conseille la sagesse populaire – avec deux fois plus de sécurité. En effet, une formule représente un risque d’erreur alors que deux formules représentent deux risques d’erreur. Donc, dans ce genre de situation, avec Excel, on va en même temps deux fois plus vite et deux fois plus sûrement !

Pour résoudre ce problème, il faut utiliser à bon escient la touche [Ctrl] lors de la création de la formule, pour mettre les « $ » là où il le faut. Il faut aussi, pour atteindre notre objectif, connaître la fonction colonne().

Dans une formule, colonne() renvoie le numéro de la colonne active. Mais on peut aussi utiliser une adresse en argument : colonne(D5) donnera ainsi un résultat de 4, le numéro de colonne de D5. Si l’argument de colonne() correspond à un bloc de cellules, le résultat de la fonction sera le numéro de la première colonne du bloc.

Voici donc la formule attendue en B29 : =recherchev($A29;$A$5:$D$17;colonne();faux).

Nous pouvons à présent copier cette formule jusqu’en B31, puis la coller aussi en D29:D31. Il ne reste plus qu’à donner la même couleur de fond à D29:D31 et à D29:D31 pour montrer que ces formules sont les mêmes.

Pour G27, nous avons la simple formule =F27*D27 qu’il suffit de recopier jusqu’à la ligne 31.

Cela fonctionne aussi parfaitement. Voici donc une photo de notre écran après ces opérations :




02 octobre 2018

Modèle de facturation (e)

Nous nous attaquons à présent à la partie inférieure du modèle, la facturation. Pour construire la formule adéquate en B27, nous aimerions avoir dans notre modèle le haut de la base (lignes 4 et 5 au minimum), le bas de la base (lignes 16 à 18) et la partie intéressante de la facture (lignes 25 à 34).

Certes, avec la commande Fractionner de l’onglet Affichage, on peut afficher deux blocs horizontaux disjoints, mais pas trois blocs ! Comment faire ?

L’astuce est simple, encore faut-il y penser… Il suffit de masquer temporairement les lignes 20 à 24 qui, pour le moment, n’ont aucune utilité pour nous. Nous les réafficherons quand la facture sera prête. Cela peut sembler simple mais je vous assure que, lors de mes formations, rares sont les personnes qui trouvent cette solution…

Une première formule pour la cellule B27

La formule la plus simple est la suivante : =recherche(A27;$A$5:$B$17).

De nous jours, peu d’utilisateurs connaissent encore le simple recherche() et l’on me propose en général un recherchev() ou même parfois un index(equiv()).

Recopions vers le bas. Nous constatons alors que B28 et B30 affichent une désignation alors que le code à leur gauche n’existe pas dans la base. Il convient donc de tester la validité du code, ce qui ne peut se faire qu’avec un recherchev(…;faux) ou un index(equiv(…;0)).

Une formule améliorée pour la cellule B27

Prenons la première solution, qui est plus simple : =recherchev(A27;$A$5:$B$17;2;faux).

Le contrôle de la validité du code fonctionne bien à présent et nous pouvons construire une formule similaire pour la colonne D.

Pour changer, prenons à présent en B29 la seconde solution proposée plus haut :
=index($B$5:$B$17;equiv(A29;$A$5:$A$17;0))

Cela fonctionne aussi parfaitement. Voici donc une photo de notre écran après ces opérations :