Application Excel d'encryptage de textes

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


Sujets que vous pourriez aussi aimer :


Encrypter et décrypter du texte

Pour encoder ou décoder automatiquement des messages, nous allons exploiter les travaux aboutis lors de la formation précédente. Ils avaient notamment consisté à isoler toutes les lettres d'une phrase tapée.



Dans l'exemple finalisé illustré par la capture-ci-dessus, l'utilisateur tape un message dans une zone de saisie placée en haut de l'interface. A validation, toutes les lettres sont décomposées et encryptées. Le message codé est alors reconstitué et livré en bas de l'interface. Il est prêt à être diffusé en toute sécurité par messagerie. Les destinataires doivent bien sûr posséder l'application. Elle permet la transcription inverse en collant le message crypté dans la même zone de saisie. C'est alors le message d'origine, parfaitement intelligible qui est reconstruit en bas de l'interface.

Source et présentation de la problématique
Pour mener à bien cette étude, nous devons tout d'abord récupérer un fichier hébergeant une structure et des données.

• Télécharger le classeur encrypter-et-decrypter.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

La cellule B4 est fusionnée sur plusieurs colonnes. Elle attend la saisie de l'utilisateur. Il peut s'agir du message à encoder comme du message à décoder. La ligne 7 est fractionnée en plusieurs cases. Chacune d'elle doit recevoir une lettre du texte d'origine. Ces caractères doivent être encryptés ou décryptés en ligne 9, sur le même nombre de cases fort naturellement. La ligne 12 doit livrer le message converti par assemblage des lettres transformées en ligne 9.

Vous notez que ce classeur est doté d'une seconde feuille. Elle est nommée codes. La colonne B recense une grande partie des lettres et caractères habituels. La colonne C fournit leurs transcriptions encodées. Ce sont donc des recherches de chaque lettre isolée que nous allons devoir réaliser dans ce tableau à deux colonnes.

Le tour de force consiste à détecter automatiquement s'il s'agit d'un message à encrypter ou d'un message à décrypter. Et c'est une unique formule à répliquer sur la ligne 9 de la feuille Decouper, qui doit se charger de cette analyse. Comme la recherche peut être réalisée en première comme en deuxième colonne de la feuille codes, la fonction RechercheV est à proscrire.

Extraire chaque lettre d'un message
La première étape consiste donc à isoler dans l'ordre chaque caractère du message d'origine. Ce sont les cases de la ligne 7 qui doivent les héberger. Pour cela, comme nous l'avons démontré lors de la formation précédente, nous allons exploiter la fonction Excel Stxt :

=Stxt(Texte_à_découper; Position_de_départ; Longueur)

En premier argument, nous devons lui passer le message à tronçonner. En deuxième argument, nous devons lui indiquer la position de départ pour réaliser cette découpe. Cette position est variable. Elle progresse pour chaque lettre en même temps que le calcul doit être répliqué sur les cases situées sur la droite. La longueur de découpe est fixe. Nous souhaitons isoler les caractères un à un.

• Sélectionner la première case, soit la cellule B7,
• Taper le symbole égal (=) pour initier la formule,
• Saisir la fonction pour gérer les anomalies, suivie d'une parenthèse, soit : SiErreur(,
• Inscrire la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner le message à tronçonner en cliquant sur sa cellule B4,
• Enfoncer la touche F4 du clavier pour figer cette cellule, ce qui donne : $B$4,

En effet, le calcul doit être répliqué sur la droite pour extraire indépendamment chaque lettre. Mais chaque formule doit poursuivre le processus de découpe à partir du même message. Cette cellule ne doit donc pas suivre le déplacement imposé par la réplication.

• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Inscrire la fonction donnant l'indice de colonne, suivie d'une parenthèse, soit : Colonne(,
• Désigner une cellule de la première colonne, par exemple A1,

Au premier calcul, la position retournée vaudra 1. Mais avec la réplication sur la droite, A1 se transformant en A2 puis A3 etc.... La fonction Colonne fournira toutes les positions de départ incrémentées. C'est ainsi que nous extrairons chaque caractère dans l'ordre.

• Fermer la parenthèse de la fonction Colonne,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Saisir le chiffre 1 pour ne prélever qu'un seul caractère à chaque fois,
• Fermer la parenthèse de la fonction Stxt,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour garder la cellule vide en cas d'anomalie,

Ces erreurs peuvent se présenter lorsqu'aucun message n'est proposé à la transcription par exemple.

• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci CTRL + Entrée pour garder sa cellule active,
• Tirer la poignée du résultat à l'horizontale jusqu'en colonne AG,

Comme vous pouvez le voir, chaque caractère est effectivement extrait indépendamment. Si vous changez le message en cellule B4, les extractions s'actualisent parfaitement.

La formule que nous avons bâtie est très simple. Sa syntaxe est la suivante :

=SIERREUR(STXT($B$4; COLONNE(A1); 1); '')

Encoder oudécoder les caractères
Pour réaliser la recherche du caractère à encoder comme du caractère potentiellement à décoder, nous devons exploiter les fonctions Index et Equiv :

=Index(Tableau_de_recherche; Indice_de_colonne; Indice_de_ligne)
=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

La fonction Index retourne l'information située au croisement d'une ligne et d'une colonne. La colonne est connue. La ligne dépend de la position du caractère dans le tableau de la feuille codes. Pour la trouver, nous lui imbriquerons la fonction Equiv. En cherchant ce caractère dans la colonne dédiée, elle retournera sa position, donc l'indice de ligne manquant.

Si vous déployez la zone Nom en haut à gauche de la feuille, vous constatez que des intitulés sont attribués aux colonnes de cette feuille codes.



Le nom Code désigne l'intégralité du tableau. Les plages liste1 et liste2 pointent sur la première et la seconde colonne.

Pour réaliser l'encodage comme le décodage en une seule formule, nous suggérons d'exploiter la fonction SiErreur. L'astuce consiste à lui passer le calcul de recherche des caractères à encoder en premier paramètre. Si cette extraction génère une erreur, c'est son second argument qui est enclenché. Donc, en deuxième argument, nous lui passerons la formule pour chercher les caractères à décoder. Cependant, il reste un dernier cas. En bout de chaîne, plus aucun caractère n'étant trouvé, la double formule aboutira nécessairement sur une erreur. Donc, nous devons englober le tout dans une autre fonction SiErreur, capable de gérer les anomalies en second argument.

• Sur la feuille Decouper, sélectionner la cellule B9,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Saisir de nouveau la même fonction suivie d'une parenthèse, soit : SiErreur(,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau des caractères par son nom, soit : codes,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner la première lettre extraite en cliquant sur sa cellule B7,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la première colonne du tableau des caractères par son nom, soit : liste1,

En effet, dans ce premier cas, nous partons du principe que les caractères sont à encoder. Nous cherchons donc les lettres habituelles pour extraire le code associé, situé dans la seconde colonne.

• Taper un point-virgule suivi du chiffre zéro : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la fonction Index,
• Saisir le chiffre 2 pour désigner la colonne des caractères encryptés à retourner,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,

Si cette branche se déclenche, cela signifie que la recherche des caractères normaux a échoué. Nous en concluons donc qu'il s'agit d'un message à décoder. Le principe est identique. Cependant, la recherche de chaque caractère doit être réalisée en seconde colonne. La lettre décodée correspondante à rapatrier doit être désignée en première colonne.

• Adapter la syntaxe de la précédente extraction comme suit :

Index(codes; Equiv(B7; liste2; 0); 1)

• Fermer la parenthèse de cette seconde fonction SiErreur,
• Taper un point-virgule (;) pour passer dans le second argument de la première fonction,
• Inscrire deux guillemets ('') pour garder la cellule vide en cas d'anomalie de recherche,
• Fermer la parenthèse de la première fonction SiErreur,
• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Tirer la poignée de la cellule du résultat à l'horizontale jusqu'en cellule AG,

Comme vous pouvez le voir, l'encryptage de chaque lettre du message est réalisé avec succès. De plus, la double gestion organisée par la fonction SiErreur est précieuse. Tout d'abord, elle permet de stopper la recherche à la fin de l'énumération des lettres extraites par le précédent calcul. Ensuite et nous le verrons, elle permet la transcription dans les deux sens. La formule complète que nous avons bâtie est la suivante :

=SIERREUR(SIERREUR(INDEX(codes; EQUIV(B7; liste1; 0); 2); INDEX(codes; EQUIV(B7; liste2; 0); 1));'')

Message encrypté ou décrypté
Pour finaliser l'application, il nous reste à assembler les caractères transcrits dans la cellule B12, fusionnée sur de nombreuses colonnes. Cet assemblage doit se faire par concaténation. Nous proposons d'utiliser la fonction Excel Concatener :

=Concatener(Texte1; Texte2; ...; TexteN)

Toutes les cellules de la ligne 9 doivent être désignées tour à tour en argument de la fonction. Cette construction peut sembler fastidieuse. Néanmoins, une fois le calcul bâti, la transcription réversible est fonctionnelle quels que soient les messages saisis.

• En cellule B12, construire et valider la formule suivante :

=CONCATENER(B9; C9; D9; E9; F9; G9; H9; I9; J9; K9; L9; M9; N9; O9; P9; Q9; R9; S9; T9; U9; V9; W9; X9; Y9; Z9; AA9; AB9; AC9; AD9; AE9; AF9; AG9)



Le message encodé est livré et il est parfaitement incompréhensible. Si vous le copiez et ne collez que la valeur détachée de la formule en cellule B4, vous obtenez le déchiffrage. Voilà donc une application d'encryptage aboutie uniquement par les calculs Excel.