Convertir les unités de mesure avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Application Excel de conversion de mesures

Dans cette nouvelle formation Excel, nous proposons de bâtir une application de conversion des unités de mesure et plus précisément, pour les distances. Le principe est le suivant : L'utilisateur définit l'unité de départ avec une valeur numérique à convertir puis, définit une unité finale par le biais d'une case à cocher. Comme les calculs Excel sont dynamiques, le résultat de la conversion s'affiche instantanément dans la cellule dédiée. Nous proposons de relever ce défi sans code VBA.



Les fonctions de recherche et d'extraction Excel sont capables de faire la correspondance entre les unités choisies et leur équivalence dans une source de donnée.

Présentation de l'interface Excel
Le classeur source propose déjà la structure avec les contrôles de formulaire permettant de sélectionner visuellement des unités de mesure.

• Télécharger le classeur Excel convertir-distances.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur les boutons Activer la modification et Activer le contenu,

Ce classeur se compose de deux feuilles. Par défaut, vous affichez la feuille Convertisseur qui représente l'application visuelle exploitable par l'utilisateur. Vous notez la présence des cases à cocher qui sont en réalité des cases d'option. Elles ont la capacité de se décocher automatiquement pour que seule l'une d'entre elles reste active. Nous reviendrons sur ce point important. Lorsque l'une de ces cases est cochée, elle inscrit une valeur booléenne dans une cellule qui lui est liée. Et cette valeur est fondamentale pour que les formules Excel sachent retrouver l'unité choisie par l'utilisateur. C'est la raison pour laquelle une colonne adjacente rappelle chacune des unités dans le même ordre que celui des cases.

• En bas de la fenêtre Excel, cliquer sur l'onglet Unités pour activer sa feuille,

Vous notez la présence d'une petite source de données livrant toutes les correspondances entre les unités de mesure. En colonne B, vous notez l'inscription des unités de mesure sous forme de titres. Le constat est le même pour la ligne 5. Au centre du tableau figurent les conversions entre chaque unité. Ainsi pour connaître le facteur de conversion entre le cm et le pouce (Inch), il suffit de réaliser l'intersection entre la ligne 10 et la colonne I. En I10 donc, nous apprenons qu'1 cm équivaut à environ 0,39 Pouce (Inch). Ce tableau servira de recherche en fonction des deux unités de mesure livrées par l'utilisateur afin d'extraire la valeur de conversion à l'intersection des deux unités.



Et comme deux éléments sont nécessaires à l'extraction de la valeur de conversion, la fonction RechercheV ne pourra être employée. De ce fait, nous exploiterons de nouveau les fonctions Index et Equiv imbriquées.

Les cases d'option
Même si l'interface est déjà fonctionnelle, il est important de comprendre comment fonctionnent ces contrôles.

• Afficher de nouveau la feuille Convertisseur du classeur Excel,
• Cocher la première case cm de la rangée de gauche,
• Puis, cocher la case In de la rangée de droite,

Nous venons de choisir de convertir les Centimètres en Pouces.



Comme vous le remarquez, le fait de cliquer sur l'une des cases de la rangée de gauche, désélectionne la précédente case active. De ce fait, seule une unité de mesure de départ peut être définie. Il en va de même pour l'unité de mesure finale dans la rangée de droite. Et lorsqu'une de ces cases est cochée, la cellule qui lui est liée sur la droite, bascule de Faux à Vrai. Il s'agit d'une valeur booléenne indiquant l'état de la case. Il ne peut donc y avoir qu'un seul Vrai par rangée.

Pour que ces cases d'option interagissent ensemble, elles doivent être liées au même groupe. Nous proposons de le démontrer par l'exemple. Et pour ce faire, bien qu'aucun code VBA ne soit nécessaire dans cette application, nous avons besoin du ruban développeur. S'il n'est pas présent sur votre interface :

• Cliquer avec le bouton droit de la souris n'importe où sur le ruban Accueil,
• Dans le menu contextuel, choisir Personnaliser le ruban,
• Dans la liste de droite de la boîte de dialogue qui apparaît, cocher la case Développeur,
• Enfin, cliquer sur le bouton Ok de la boîte de dialogue,

Maintenant que le ruban Développeur est disponible, la section Contrôles propose des objets permettant de réaliser des interactions, comme les cases d'option.

• Dans la section Contrôles du ruban Développeur, cliquer sur le bouton Insérer,
• Dans la section Contrôles ActiveX de la liste, cliquer sur le contrôle Case d'option,
• Puis, tracer un rectangle n'importe où sur la feuille pour le dessiner,

Comme l'illustre la capture ci-dessus, notre première case d'option apparaît. Le mode création est activé par défaut, comme l'indique le bouton Mode Création à gauche du bouton Insérer dans le ruban Développeur. Ce mode est nécessaire pour paramétrer les objets que nous insérons avant de les exploiter. Les contrôles de la section ActiveX correspondent aux objets de développement généralement utilisés avec le code VBA Excel. Ils permettent des personnalisations plus fines que ceux de la section Contrôles de formulaire.

• Insérer une nouvelle case d'option sous la précédente,
• Puis, cliquer sur le bouton Mode Création du ruban Développeur pour le désactiver,
• Cocher la première case d'option puis la seconde,

Comme vous le constatez, le fait de cocher l'une d'entre elles décoche l'autre. Ce principe est rendu possible grâce au groupe auquel elles sont rattachées. Ce groupe doit être le même pour les deux cases. Mais comme vous l'avez remarqué, dans notre application, les cases de la colonne de gauche fonctionnent ensemble indépendamment des cases de la colonne de droite qui, elles aussi sont liées entre elles. Cela signifie que pour reproduire ce principe, nous devons être en mesure de créer deux groupes de cases d'option.

• Cliquer de nouveau sur le bouton Mode Création pour le réactiver,
• Insérer deux nouvelles cases d'option,

Nous souhaitons que les deux premières interagissent ensemble indépendamment des deux dernières. Et bien entendu, les deux dernières doivent elles aussi interagir ensemble indépendamment des deux premières. Tout est une question de propriétés d'objet.

• Cliquer sur le bouton Propriétés de la section Contrôles du ruban Développeur,

Cette action a pour effet d'afficher le volet des propriétés du contrôle sélectionné sur la feuille Excel. Nous allons l'exploiter pour reproduire le processus qui a permis de construire l'interface de cette application de conversion. Et pour ce faire, nous devons intervenir sur quatre des propriétés pour chaque contrôle. Nous devons modifier le nom de l'objet, son texte, son groupe d'appartenance et définir la cellule qui lui est liée.

• Sélectionner la toute première case d'option de manière à la paramétrer,
• A l'aide du volet, régler sa propriété (Name) sur case1,
• Saisir le texte Case 1 dans sa propriété Caption,
• Taper le nom groupe1 dans sa propriété GroupName,
• Enfin, saisir la référence de cellule A2 dans sa propriété LinkedCell,

Nous devons rattacher la deuxième case d'option (OptionButton2 sur la figure) à cette première case. Vous l'avez compris, la propriété permettant de réunir ces contrôles dans un même groupe est la propriété GroupName. Leur nom de groupe doit être le même.

• Cliquer sur la deuxième case à cocher sur la feuille Excel afin de la sélectionner,
• Définir ses quatre propriétés comme suit :

(Name) : case2, Caption : Case 2, GroupName : groupe1 et LinkedCell : A3.

• De la même façon, régler les propriétés des cases 3 et 4, respectivement comme suit :

(Name) : case3, Caption : Case 3, GroupName : groupe2 et LinkedCell : A4.
(Name) : case4, Caption : Case 4, GroupName : groupe2 et LinkedCell : A5.

• Enregistrer les modifications (CTRL + S),
• Fermer le volet des propriétés en cliquant sur la croix située en haut à droite,
• Puis, cliquer sur le bouton Mode Création du ruban Développeur afin de le désactiver,
• Cliquer alors sur la case 1 puis sur la case 2,
• Faire de même en cliquant sur la case 3 puis sur la case 4,

Comme vous le constatez, le fait de cocher la case 1 décoche la case 2 et inversement sans influer sur les cases 3 et 4 qui appartiennent désormais à un autre groupe qui leur est propre. Ainsi, le fait de cliquer sur la case 3 décoche la case 4 et inversement sans influer sur les cases 1 et 2. Dans le même temps, les cellules liées A2, A3, A4 et A5 réagissent pour inscrire la valeur VRAI lorsque la case correspondante est cochée et FAUX dans le cas contraire.

Maintenant que nous avons compris le processus, nous savons comment a été bâtie l'interface de notre application Excel de conversion. Les cases de la colonne de gauche réagissent ensemble sans interférer avec celles de la colonne de droite qui, elles-mêmes interagissent ensemble.

Exploiter les actions des contrôles ActiveX par les formules
Il s'agit désormais et dans un premier temps, de connaître les unités choisies pour le départ et l'arrivée. Lorsque ces unités seront inscrites explicitement, nous pourrons alors les exploiter pour lancer la recherche et l'extraction du facteur de conversion correspondant, dans la feuille Unités. Mais avant cela, il convient de rétablir la feuille Convertisseur telle que nous l'avons trouvée.

• Cliquer sur le bouton Mode Création du ruban Développeur pour le réactiver,
• Sélectionner ensemble les quatre cases d'option (Touche CTRL),
• Puis les supprimer (Touche Suppr),
• Sélectionner les cellules A2 à A5 et supprimer leur contenu (Touche Suppr),
• Enregistrer les modifications (CTRL + S),

Les valeurs booléennes indiquant l'état des cases d'option de la première colonne sont inscrites en colonne C. Celles de la seconde colonne figurent en colonne G. A droite de chacune d'entre elles sont listées les unités correspondantes. En conséquence, une rechercheV de la valeur Vrai sur ces deux tableaux doit fournir en retour, le texte de l'unité de départ ainsi que le texte de l'unité finale. La syntaxe de la fonction RechercheV, comme le rappelle la formation sur l'extraction de données, est la suivante :

=recherchev(valeur_cherchee ;tableau_de_recherche ; num_colonne_retour ; faux)

La valeur cherchée n'est autre que la donnée booléenne Vrai. Cette recherche doit être réalisée deux fois : une première fois dans le tableau situé entre les colonnes C et D pour extraire l'unité de mesure d'origine, et une seconde fois sur le tableau situé entre les colonnes G et H pour extraire l'unité de mesure finale correspondant au choix cliqué. Le numéro de colonne à fournir dans les deux cas est le chiffre 2 puisque le texte de l'unité se trouve dans la deuxième colonne du tableau de recherche. Enfin, le dernier argument de la fonction RechercheV doit être réglé à Faux pour indiquer que la recherche doit être réalisée selon une correspondance exacte.

Nous pourrions réaliser l'extraction du facteur de conversion en J5, en fonction de ces deux unités avec une seule formule. Mais comme elle s'avère relativement complexe, nous proposons de la décomposer en extrayant d'une part l'unité de départ et d'autre part l'unité finale. Pour ce faire, nous choisissons deux cellules arbitraires intermédiaires, K4 et K5, que nous masquerons ensuite.

• En K4, taper la formule suivante :

=SIERREUR(RECHERCHEV(VRAI; C5:D13; 2; FAUX); '')

La fonction SiErreur est un garde-fou. Si aucune case n'est initialisée, la fonction RechercheV renvoie un message d'erreur. Dans ce cas, la fonction SiErreur indique de conserver vide la cellule du résultat (''). Dans le cas contraire, la recherche verticale pour l'extraction de l'unité de départ est lancée. Et nous indiquons d'effectuer la recherche de la valeur booléenne VRAI correspondant à la case cochée. Comme il s'agit d'une valeur booléenne et non d'un texte, nous ne l'encadrons pas de guillemets. Nous indiquons d'effectuer cette recherche sur la plage de cellules C5:D13, soit le tableau des valeurs correspondant aux cases de la première colonne. Lorsque la valeur VRAI est trouvée, nous demandons à la fonction de retourner la donnée correspondante située en deuxième colonne (Chiffre 2 du troisième argument), soit l'unité de mesure. Nous terminons par le booléen FAUX pour que cette recherche se fasse selon une correspondance exacte. Dans notre cas, la fonction retourne l'unité cm correspondant bien à la case cochée du même nom. Le principe est le même pour la seconde unité de mesure.

• En K5 taper la formule suivante :

=SIERREUR(RECHERCHEV(VRAI; G5:H13; 2; FAUX); '')



Comme l'illustre la figure ci-dessus, nous obtenons bien les unités de mesure correspondant aux cases qui ont été cochées par l'utilisateur.

Recherche croisée dans une source de données
Cette fois le problème se corse quelque peu. Nous devons être capable d'extraire en J5 le facteur de conversion selon les deux extractions précédentes, soit l'unité de départ et l'unité finale. Ce facteur de conversion se trouve dans le tableau de la feuille Unités, à l'intersection de l'unité de départ (ligne) et de l'unité finale (colonne).

La fonction Excel Index que nous avons déjà abordée lors de plusieurs formations, permet d'extraire une valeur d'une base de données en fonction de son indice de ligne et de son indice de colonne. L'indice de colonne peut être trouvé par la recherche horizontale (RechercheH) de l'unité finale dans les deux premières lignes du tableau de la feuille Unités. L'indice de ligne peut être retourné par la recherche de l'unité de départ dans la première colonne de ce tableau, grâce à la fonction Excel Equiv.

• Cliquer sur la cellule J5 pour l'activer,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de gestion d'erreurs suivi d'une parenthèse, soit SIERREUR(,
• Taper ensuite le nom de la première fonction de recherche suivi d'une parenthèse, soit INDEX(,
• Cliquer sur l'onglet Unités en bas de la fenêtre Excel pour activer sa feuille,
• Désigner le tableau de recherche en le sélectionnant intégralement, soit la plage B5:K15,
• Taper un point-virgule (;) pour passer à l'argument de l'indice de ligne,
• Saisir le nom de la deuxième fonction de recherche suivi d'une parenthèse, soit EQUIV(,
• Cliquer sur l'onglet Convertisseur en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur la cellule K4 pour désigner l'unité extraite comme élément de recherche,
• Taper un point-virgule (;) pour passer à l'argument de la colonne de recherche,
• Cliquer sur l'onglet Unités en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la première colonne du tableau des unités, soit la plage B5:B15,
• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction Equiv,
• Saisir le chiffre 0 pour indiquer une correspondance exacte,
• Fermer la parenthèse de la fonction EQUIV afin de poursuivre la fonction Index,
• Taper un point-virgule pour passer à l'argument de l'indice de colonne de l'élément à extraire,
• Saisir le nom de fonction RechercheH suivi d'une parenthèse ouvrante, soit RechercheH(,
• Cliquer sur l'onglet Convertisseur en bas de la fenêtre Excel afin d'activer sa feuille,
• Sélectionner la cellule K5 de l'unité finale extraite comme élément à rechercher,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de la recherche horizontale,
• Cliquer sur l'onglet Unités en bas de la fenêtre Excel pour revenir sur sa feuille,
• Sélectionner les deux premières lignes du tableau des unités soit la plage B5:K6,
• Taper un point-virgule (;) pour passer à l'argument de l'indice de colonne à extraire,
• Saisir le chiffre 2 pour indiquer que les numéros se situent dans la seconde ligne,
• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction RechercheH,
• Saisir le booléen FAUX pour demander une correspondance exacte dans la recherche,
• Fermer deux parenthèses, une pour la RechercheH et une autre pour la fonction Index,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreurs de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver une cellule vierge en cas de problème,
• Fermer la parenthèse de la fonction SiErreur et valider le calcul par la touche Entrée,

La formule complète est la suivante :

=SIERREUR(INDEX(Unités!B5:K15; EQUIV(Convertisseur!K4; Unités!B5:B15; 0);RECHERCHEH(Convertisseur!K5; Unités!B5:K6; 2; FAUX));'')



Et nous obtenons l'équivalence entre l'unité de mesure de départ et l'unité finale. Dans notre cas, nous apprenons qu'1cm est égal à environ 0,39 Pouce. Dans cette formule nous avons imbriqué trois fonctions de recherche : Index, Equiv et RechercheH, elles-mêmes imbriquées dans la fonction SiErreur.

• Cocher cette fois la case m pour l'unité de départ et la case Ft pour l'unité finale,

Le facteur de conversion en cellule J5 se met instantanément à jour et nous apprenons qu'1 Mètre vaut environ 3,28 Pieds.

Le plus dur est fait puisqu'il ne reste plus qu'à multiplier la valeur tapée par l'utilisateur en E5, pour fournir le résultat de la conversion dans les cellules E10 et E12. En E10, nous souhaitons rappeler la valeur à convertir concaténée à son unité de mesure. La formule est donc très simple puisqu'il s'agit d'associer deux cellules mais si et seulement si l'utilisateur a bien saisi une valeur à convertir en E5. Donc nous proposons de commencer par tester ce critère grâce à la fonction Excel conditionnelle.

• Cliquer sur la cellule E10 pour la sélectionner,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit SI(,
• Cliquer sur la cellule à tester, soit E5,
• Taper le symbole = suivi de deux guillemets (='') pour vérifier si elle est vide,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir de nouveau deux guillemets ('') pour conserver la cellule vide dans ce cas,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Cliquer alors sur la cellule E5 pour l'intégrer dans le résultat,
• Enfoncer la touche 1 en haut à gauche du clavier pour le symbole de concaténation (&),
• Puis cliquer la cellule K4 correspondant à l'unité de mesure de départ extraite,
• Fermer la parenthèse et valider la formule par la touche Entrée,

La formule finalisée est la suivante :

=SI(E5=''; ''; E5&K4)

La cellule E10 reste vide puisqu'aucune valeur à convertir n'est encore saisie en E5.

• Taper le nombre 16 par exemple en E5,

A validation, vous constatez que la concaténation s'opère en E10 pour fournir le résultat 16inch. En E12, nous devons maintenant réaliser le calcul permettant de fournir la valeur convertie, selon le facteur extrait en J5 et, en le concaténant à l'unité finale en K5. Bien sûr, le principe reste le même. Le calcul ne doit être engagé que si une valeur numérique est détectée en E5.

• En E12, taper la formule suivante :

=SI(E5=''; ''; ARRONDI(E5*J5;2)&K5)

Pour commencer, le test reste le même que précédemment. Ensuite, si E5 n'est pas vide donc, nous arrondissons le résultat de la multiplication à deux décimales grâce à la fonction Excel Arrondi (Arrondi(valeur ; Nombre_decimales)). Cette multiplication est celle de la valeur à convertir en E5 par le facteur de conversion extrait en J5. Nous concaténons (&) ce résultat arrondi à l'unité finale extraite en K5. Nous obtenons le résultat de 1,33 Pied (Ft) pour les 16 Pouces (Inch) demandés.

• Cocher la case cm dans la colonne de gauche pour l'unité de départ,
• Cocher la case In pour l'unité finale dans la colonne de droite,

Comme vous le constatez, notre application est parfaitement dynamique puisque tous les résultats se mettent instantanément à jour. Il suffit à l'utilisateur de cliquer pour obtenir le renseignement.



Finalisation et protection de l'application
Pour que l'application puisse être livrée, les colonnes intermédiaires de calculs doivent être masquées. De même, il s'agit d'ajouter une protection en E5 afin de n'autoriser que la saisie de données numériques. En effet à ce stade, si vous tapez un texte à la place d'un nombre, le résultat de la conversion conduit logiquement à un message d'erreur, fort disgracieux au demeurant. Commençons par cette restriction.

• Cliquer sur la cellule E5 pour l'activer,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation de données,
• Dans la boîte de dialogue qui suit, dérouler la liste de la zone Autoriser,
• Dans la liste, choisir Décimal,
• Dérouler ensuite la liste déroulante de la zone Données,
• Dans les propositions, choisir le critère Supérieure à,
• Puis, taper le chiffre 0 dans la zone Minimum et valider par Ok,

Qui peut le plus peut le moins. Un nombre décimal accepte la saisie d'un nombre entier. En revanche, tout ce qui n'est pas numérique est désormais interdit.

Si vous tapez un texte en cellule E5, le message d'erreur illustré par la figure ci-dessous apparaît. Vous ne pourrez pas forcer le passage tant que vous n'aurez pas corrigé la saisie en valeur numérique. Nous avions déjà exploité ces outils de validation de données dans une formation Excel destinée à apprendre à créer des listes déroulantes dans les cellules de la feuille.

Nous pourrions pousser le vice en protégeant la feuille de manière à ce que seule la saisie en cellule E5 reste autorisée. Ainsi, toutes les formules Excel seraient protégées contre une mauvaise manipulation, comme nous l'apprend la formation sur la facturation Excel.



Il nous reste désormais à masquer les colonnes des cellules qui ont servi de relais pour les calculs finaux.

• En haut de la feuille Excel, cliquer ensemble sur les étiquettes de colonnes C, D, G, H et K grâce à la touche CTRL du clavier,
• Cliquer alors avec le bouton droit de la souris sur l'une des étiquettes sélectionnées,
• Dans le menu contextuel, choisir Masquer,

L'application est plus propre et parfaitement fonctionnelle. Nous pourrions la dériver pour fournir les conversions sur les unités de poids, de vitesses ou encore de températures qui ne sont pas les mêmes selon les pays.