Monsieur Excel
Pour tout savoir faire sur Excel !

29 septembre 2013

Préparation de mailing

Remarque Chère lectrice, Cher lecteur, étant en vacances dans un endroit sans WiFi, je n'ai pas pu envoyer d'article durant ces quelques jours. Mais rassurez-vous, me voilà de retour :)


En lien avec Word, on peut utiliser Excel pour préparer un mailing, par exemple une lettre que l’on adressera à plusieurs personnes, personnalisée pour chacune de ces personnes.

Nous avons un tout petit exemple de fichier, ci-dessous, que nous allons utiliser pour faire un mailing à partir de Word.

Pour commencer, il faut tout d’abord attacher le fichier Excel au document Word. Pour cela, dans Word, nous activons l’onglet « Publipostage », la commande « Sélection des destinataires » puis « Utiliser la liste existante ». Ignorons le bouton « Nouvelle source » et allons chercher le fichier Excel contenant la base de données.

Cela marche tout de suite : on sélectionne la feuille contenant la base et le bouton « Modifier la liste des destinataires » s’active aussitôt. Ce bouton nous permet de consulter la base, de désactiver ou réactiver tel ou tel élément, et de réaliser diverses opérations : tris, filtres, recherches,…


La commande « Insérer un champ de fusion » vous permet d’insérer rapidement tel ou tel champ dans votre texte.

Avec la commande « Règles », vous insérez sans difficulté un « Si … Alors … Sinon » ou un « Sauter l’enregistrement si… », pour ne parler que de deux possibilités.

Le bloc « Aperçu des résultats » vous permet à tout moment de vérifier ce que cela donnera sur les enregistrements de votre base de données.

Enfin, la commande « Terminer et fusionner » lance le processus final. L’idéal est alors de disposer d’un driver avec une imprimante Acrobat : cela vous permet de bien voir le résultat sur un PDF sans gâcher de papier et de revenir faire des modifications si nécessaire.


21 septembre 2013

Améliorer l’isolement numérique

Dans le dernier post, nous avons découvert une formule matricielle qui nous a permis d’isoler la partie textuelle de la partie numérique, dans une cellule contenant un texte suivi d’une valeur. Nous vous rappelons la situation : il faut isoler en B1 la partie numérique de A1...


En fait, un lecteur de la solution proposée dans l'article précédent, John Chisholm, a trouvé une formule encore meilleure, car elle ne requiert pas de validation matricielle.

Sa formule pour B1 est la suivante : 
=recherche(99^99;--(0&stxt(A1;min(trouve({0.1.2.3.4.5.6.7.8.9};A1&1234567890));
ligne(indirect("1:"&nbcar(A1)+1)))))

trouve({0.1.2.3.4.5.6.7.8.9};A1&1234567890) s’évalue en {22.13.6.7.8.9.11.12.20.21}, ce qui nous donne les positions dans la chaîne (astucieusement enrichie des 10 chiffres) du premier 0, du premier 1, …

Le minimum de ce vecteur est la position du premier chiffre, donc 6.

0&stxt(A1;6;ligne(indirect("1:"&nbcar(A1)+1))) est évalué en {"02";"023";"0234";"02345";"02345,";"02345,6";"02345,67";"02345,67";"02345,67";
"02345,67";"02345,67";"02345,67";"02345,67"}

--({"02";"023";"0234";"02345";"02345,";"02345,6";"02345,67";"02345,67";"02345,67";
"02345,67";"02345,67";"02345,67";"02345,67"}) est évalué en {2;23;234;2345;2345;2345,6;2345,67;2345,67;2345,67;2345,67;2345,67;2345,67;
2345,67}

Et enfin, le recherche() renvoie la dernière valeur de ce vecteur…

Comme dans l’article précédent, on peut donc raccourcir encore cette formule en utilisant la fonction droite(), avec la formule :
=1*droite(A1;nbcar(A1)-min(trouve({0.1.2.3.4.5.6.7.8.9};A1&1234567890))+1)

Remarque – Il faudrait que Claude Duigou, qui nous a proposé dans un commentaire à l’article précédent une solution non matricielle à base de substitue() à répétition, nous dise comment sa solution se compare à celle-ci en temps de calcul…

16 septembre 2013

Isolement de partie numérique

Nous recevons un fichier CSV contenant une liste de textes (20 caractères au maximum) suivis d’une valeur, comme dans la colonne A dans la copie d’écran ci-dessous.

  
L’objectif est d’isoler les textes et les valeurs dans deux colonnes différentes.

La formule de B1, proposée par un internaute, était la suivante :
=stxt(A1;equiv(vrai;estnum(1*stxt(A1;ligne($1:$20);1));0);nbval(1*stxt(A1;ligne($1:$20);1)))

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Décortiquons cette formule :

stxt(A1;ligne($1:$20);1) est évalué en {"A";"v";"i";"o";"n";"2";"3";"4";"5";",";"6";"7";"";"";"";"";"";"";"";""}

ESTNUM(1*{"A";"v";"i";"o";"n";"2";"3";"4";"5";",";"6";"7";"";"";"";"";"";"";"";""}) est évalué en : {FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;FAUX;VRAI;VRAI;
FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX}

equiv(vrai;{FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;FAUX;VRAI;VRAI;
FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX};0) est évalué en 6, c’est-à-dire la position du premier chiffre.

Et enfin, nbval(1*stxt(A1;ligne($1:$20);1)) est évalué en 20, ce qui nous donne enfin =stxt(A1;6;20) avec le résultat espéré.

Remarque – Une fois de plus, nous constatons que les formules matricielles permettent d’obtenir en une formule unique un traitement algorithmique.

La dernière étape est un peu lourde, et l’on peut écrire la formule globale suivante, qui est plus courte :
=1*droite(A1;nbcar(A1)-equiv(vrai;estnum(1*stxt(A1;ligne($1:$20);1));0)+1)

La formule de C1 ne pose pas de problème : =gauche(A1;nbcar(A1)-nbcar(B1))

11 septembre 2013

Découvrez la fonction epurage()

Une fonction vraiment peu connue d’Excel est la fonction epurage().

Cette fonction sert à enlever du texte en argument tous les caractères dont le code ASCII est inférieur ou égal à 31.

Ces caractères peuvent provenir de fichiers provenant d’autres systèmes informatiques.

Dans l’exemple suivant, nous avons entré en B1 la formule ="Texte avec "&car(ligne()), et en E1 la formule ="Texte avec "&car(ligne()+20). En C1, nous avons la formule =epurage(B1), formule reproduite dans les blocs C1:C20 et F1:F20.

Tout cela marche bien et nous constatons en effet que c’est seulement à partir de E13 que notre épurage n’efface rien de visible.

06 septembre 2013

Comment planter Excel en beauté !

John Peltier, un MVP Excel bien connu, vient de trouver une façon originale de planter Excel !

Vous créez un graphe dans une feuille, en lui donnant un titre.

Vous déplacez ce graphe dans une nouvelle feuille graphique : clic droit dans le graphe, commande « Déplacer le graphe ». Voici le résultat obtenu :


Maintenant, il faut que vous respectiez exactement la procédure suivante :

Cliquez une seule fois dans le titre, pour l’activer
Tapez un nouveau titre dans la barre de formule
Sans valider ce nouveau titre, utilisez la flèche à gauche comme pour modifier ce texte…
Et là, Excel se plante !

Cela n’arrive apparemment pas avec Excel 2007, mais a été vérifié sur 2010, 2013 en 32 bits, et 2013 en 64 bits.
 
Remarque – Quand ensuite Excel s'est restauré, avec la récupération du fichier, ce problème avait disparu (avec mon Excel 2010) ???

En revanche, quand j'ai refait l'ensemble de l'opération sur un fichier nouveau, le problème est réapparu...

01 septembre 2013

Séquence graphe papillon – 6

Finalisation de l’axe des X

Il ne nous reste plus qu’à finaliser l’axe des X pour qu’il représente ce que l’on voyait dans le premier article sur ce thème.

Pour cela, nous ajoutons une série de données en colonnes H et I.

Nous copions le bloc H2:I9, nous activons le graphe et nous faisons un collage spécial en demandant « Nouvelle série » et « Abscisses dans la première colonne ». Nous obtenons alors le résultat visible ci-dessous.


Nous cliquons dans la nouvelle série sur la graphe et modifions son type pour prendre un graphe en XY (Nuage de points avec marqueurs uniquement). On obtient alors des points en ocre qui représentent un escalier descendant puis remontant.

Il ne reste plus qu’à effectuer les tâches suivantes…

a) Modifier l’axe des Y pour mettre son maximum à 10000.
Tous les points ocres précédents tombent sur l’axe des X…

b) Détruire l’axe des Y avec la touche [Del].

c) Sélectionner la série des points ocres et leur donner les options « Aucun marqueur » et « Aucun remplissage ».

d) Leur ajouter des étiquettes de données et les centrer.

e) Supprimer l’ancien axe des X et l’étiquette « Série 6 » dans la légende.

f) Modifier la taille de la police et le style de la légende..

Et voilà ! Vous avez enfin obtenu le résultat demandé au départ :


Avec de tels graphes, on peut comparer les performances de deux pays, de deux magasins, de deux choix d’investissement,…

Ou même de deux sexes, mais ce ne serait pas politiquement correct !