Monsieur Excel
Pour tout savoir faire sur Excel !

13 novembre 2018

La zone de critères

Pour répondre à la question posée à la fin de l’article précédent, il faut utiliser ce que l’on appelle une zone de critères. Avant l’arrivée des tableaux croisés dynamiques, tous les bons utilisateurs d’Excel les connaissaient. Comme je le constate souvent chez mes clients, ce n’est plus le cas aujourd’hui.

Une zone de critères comporte au minimum deux cellules voisines dans la même colonne. La première porte le nom d’un champ de la base et la seconde une condition, par exemple respectivement Age au-dessus et >27 en dessous.

Une zone de critères peut comporter plus de deux lignes et plus d’une colonne. Dans ce cas, chaque ligne correspond à un OU (au sens Booléen du terme, et chaque colonne à un ET.

La copie d’écran ci-dessous montre la solution au problème posé à la fin de l’article précédent : lister à la fois les garçons de 27 ans et les filles de 26 ans. Nous avons inséré quelques lignes pour loger la zone de critères. Nous constatons qu’il y a 6 gagnants, 4 garçons et 2 filles.


Un critère calculé

On peut aller plus loin encore en utilisant des critères calculés. Un critère calculé est un critère où le nom du champ à tester est tout sauf celui d’un nom de champ existant : tout sauf Sexe, Prénom, Nom, Age, Salaire ou Pays.

La condition indiquée sous le nom du champ doit faire référence à la première fiche de la base, donc à la première ligne placée sous les noms des champs.

Entrons par exemple Test en F1 et =ou(et(A6="m";D6=27);et(A6="f";D6=26)) en F2. Si nous refiltrons la base en identifiant F1:F2 comme zone de critères, nous obtenons exactement le même résultat !

Remarque 1 – Comme vous pourrez le constater en F2, la cellule affiche le résultat VRAI car il se trouve que notre condition est vraie pour la ligne 6.

Remarque 2 – Dans la formule reproduite ci-dessus, ainsi qu’en C2 et C3, nous avons utilisé une minuscule pour identifier le sexe. Ce n’est pas gênant : à l’instar des fonctions Recherche, Index et Equiv, la capitalisation des lettres n’est pas prise en compte dans les zones de critères. Les lettres accentuées, en revanche, sont significatives !

Pour télécharger le fichier original :

08 novembre 2018

Une base Excel, à l’ancienne…

Les utilisateurs d’Excel ont tellement l’habitude à ce jour d'utiliser des tableaux croisés dynamiques qu’ils en ont oublié ce qu’était une base dans Excel et ce que l’on pouvait faire avec.

Tout d’abord, il faut savoir que, dans une base Excel, les noms des champs sont toujours dans la première ligne. Chaque ligne suivante liste les données correspondant à un enregistrement.  Il n’est donc pas possible d’utiliser une base transposée où les noms des champs seraient listés dans la première colonne.

Une base Excel n’est en fait pas une base de données. Techniquement, on devrait plutôt parler de table. Une véritable base de données met en fait en relation plusieurs tables.

Position de la base

Une base peut être placée n’importe où dans une feuille de calcul. Ceci dit, il est absolument déconseillé que, sur l’une quelconque de ses quatre bordures, elle soit en contact avec des cellules non vides. Je me permets d’insister sur ce point car j’ai souvent vu chez mes clients des cas où cette règle fondamentale n’était pas respectée.

Pour le vérifier, c’est simple. Cliquez dans une cellule quelconque de la base puis faites [Ctrl]-a. Après cela, si seule votre base est sélectionnée, tout va bien. Sinon, vous avez du nettoyage à faire…

Si je vous dis cela, c’est parce qu’Excel, dans son travail avec les bases, effectue ce [Ctrl]-a sans rien vous demander.

Cliquez en dehors de la base, par exemple en H5, et utilisez la commande Filtrer du ruban Données. Cela ne marche pas, et un message vous dit pourquoi. Cliquez à présent dans une quelconque des cellules de la base, et vous obtenez dans la ligne 1 les filtres, comme nous le voyons dans la copie d’écran.

Excel reconnaît aussitôt si la colonne est de nature textuelle ou numérique, comme nous pouvons le constater dans la partie droite de la copie d’écran.

Les filtres sont cumulatifs

Si nous filtrons dans la colonne Sexe pour ne garder que les garçons, puis dans la colonne Age pour ne garder que les 27, nous obtenons au final quatre personnes, les garçons de 27 ans. Autrement dit, chaque nouveau filtre se cumule avec les filtres antérieurs.

Comment faites-vous, sans passer par un tableau croisé dynamique, pour sélectionner à la fois les garçons de 27 ans et les filles de 26 ans ? Si vous séchez, rassurez-vous, nous répondrons à cette question dans le prochain article.



31 octobre 2018

Able2Extract Professional 12

Aujourd’hui, nous testons un logiciel de conversion de fichier Acrobat (*.pdf) vers Excel : Able2Extract Professional 12. Pour bien apprécier à quel point ce logiciel est convivial, je l’ai testé sans lire le mode d’emploi. J’ai ouvert le fichier des comptes d’une start-up dans laquelle j’avais investi.

La partie gauche de l’écran affiche la liste des pages avec une image en réduction de chacune d’entre elles. On peut sélectionner une ou plusieurs pages à convertir, ainsi que telle ou telle partie d’une page. On peut aussi choisir de réaliser la conversion dans divers formats : Excel, CSV, Word, Powerpoint, Publisher, HTML, AutoCAD ou Image.

Pour ce test, j’ai sélectionné une partie d’une page intermédiaire. Vous pouvez voir, dans la partie supérieure de la copie d’écran ci-dessous, ce à quoi ressemblait le haut de ma sélection à convertir.                                                                                                                                           
Résultat de la conversion

La conversion s’est effectuée sans le moindre problème et on peut en voir le résultat dans les colonnes A à F du fichier Excel.  On constate aussitôt que le résultat est excellent, seuls les encadrements ayant disparu.


Il peut y avoir un problème pour la récupération des nombres, les nombres étant en fait au format texte et le séparateur des milliers n’étant pas reconnu comme tel.

A titre indicatif, nous avons recopié la colonne F en H pour vous montrer comment résoudre ce problème. Il faut pour cela deux étapes : dans la première, nous avons remplacé le « . » dans la colonne par rien du tout ; dans la seconde, nous avons attribué un format monétaire à la colonne H. Et tout est alors réglé !

En guise de conclusion

Notre test a été tout à fait concluant, la récupération dans Excel s’est effectuée sans problème, sous la seule réserve d’avoir éventuellement à faire une petite retouche, comme nous venons de le faire.

Pour voir le produit et le télécharger :  https://www.investintech.com/prod_downloadsa2e.htm

25 octobre 2018

Modèle de facturation (h)

Il ne nous reste plus qu’à fignoler la présentation de notre modèle pour qu’il soit prêt à être utilisé par d’autres. Pour commencer, rétablissons les lignes 20 à 25 qui étaient masquées : pour cela, sélectionnons le bloc de lignes 19:26 et faisons un double clic sur leur séparateur de lignes.

Décoration en série

Pour décorer tous nos tableaux d’un coup, sélectionnons tour à tour – en maintenant la touche [Ctrl] enfoncée – les blocs suivants : B1:C2, G1:G2, A4:I4, A5:I17, A26:G26, A27:G31 et A27:G31. Puis, pour les encadrer avec un trait bleu épais autour et un trait rouge dedans, utilisons la commande BordureAutres bordures du bloc Police. Il n’y a plus qu’à valider pour obtenir le résultat ci-dessous :


Fignolage définitif

Nous constatons (cf. la copie d’écran ci-dessus) qu’il faudrait enlever la barre rouge à gauche de C26:C31 et de E26:E34. Pour cela, sélectionnons ces deux blocs – en maintenant la touche [Ctrl] enfoncée – et appelons la commande BordureAutres bordures du bloc Police puis cliquons dans l’image une fois sur la bordure rouge à gauche pour éliminer cette bordure, puis validons.

Il ne reste plus qu’à centrer D27:D31 et F27:F31 pour améliorer encore un peu le look du tableau et nous sommes quasiment finis.

La seule chose qui puisse encore être améliorée est la présentation des lignes 32 à 34. J’en connais qui sélectionneraient D32:F32 puis fusionneraient, puis feraient de même avec les deux lignes en dessous. Il faut absolument éviter cela : la fusion de cellules est la pire fonctionnalité jamais inventée pour Excel. En effet, dans ce cas, on perd l’indépendance des cellules qui est une qualité fondamentale des tableurs. A la place, utilisez donc la commande Alignement et, dans le menu déroulant Horizontal, la commande « Centré sur plusieurs colonnes ».

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 :