Articles

Excel ne fait pas de publipostage

Vous avez raison: Excel ne fait pas de publipostage. Mais lorsqu’on a des documents similaires à produire, l’emploi d’un publipostage permet de traiter un très grand nombre de documents en seulement quelques minutes. Pour cela, Excel est votre meilleur allié. Et de loin!!

La famille Office

Bien sûr, la famille Office est la première à laquelle on pensera pour ce genre de travail. Que ce soit Word, Publisher ou Outlook (Oui, oui! On peut faire du publipostage pour envoyer des courriels personnalisés), tous ces logiciels faisans partie de la même famille, ils permettent de personnaliser un modèle de base et ainsi d’automatiser la production de documents.

Adobe

La famille Adobe permet aussi de fusionner des documents pour en faire des versions uniques et personnalisées. Encore une fois, on peut automatiser une tâche répétitive et ainsi accélérer la production des documents de même type.

Les fonctions Excel

Pour rendre le travail aussi efficace et rapide que possible, il est bon de connaître quelques fonctions Excel qui élimineront le besoin de faire des ajustements manuels.

1. MAJUSCULE
   1. Convertira tous les caractères d’une cellule en lettres majuscules
2. MINUSCULE
   1. Convertira tous les caractères d’une cellule en lettres minuscules
3. NOMPROPRE
   1. Convertira les mots qui sont dans une cellule en mettant la première lettre majuscule et les suivantes en minucules
4. CHERCHE
   1. Permet de trouver la position d’un caractère spécifique ou d’une chaîne de caractères à l’intérieur d’une cellule
   2. Combinée avec les fonctions GAUCHE et DROITE, permet de tronquer le contenu d’une cellule à partir d’un caractère spécifique
5. CONCATENER
   1. Permet de combiner du texte et/ou le contenu d’une ou plusieurs cellules
6. SUBSTITUE
   1. Permet de rechercher une chaîne de caractère dans une cellule et de remplacer celle-ci par une nouvelle chaîne
   2. Combinée avec la fonction SI, permet de changer des formulations complètes à l’intérieur d’un texte

Possibilités infinies

Lorsqu’on commence à explorer l’univers des fonctions texte dans Excel, on se rend rapidement compte que le publipostage prend une toute autre dimension. Les possibilités pour automatiser la création de documents deviennent presque infinies!

Les formules matricielles sont des formules qui emploient une matrice de nombre pour retourner leur résultat. Nous utilisons, sans trop le savoir, plusieurs formules matricielles. La fonction SOMME.SI est un bon exemple. Il y a une opération de validation pour chaque cellule et ce sont uniquement les cellules qui respectent la condition qui seront additionnées. La fonction SOMMEPROD est un autre exemple de fonction matricielle.

Le hic, c’est qu’il n’existe pas de forme matricielle pour toutes les fonctions. Les fonctions MAX.SI ou encore GRANDE.VALEUR.SI n’existent pas. Pourtant, il se peut que vous vouliez trouver la plus grande valeur ou la Nième plus grande valeur d’une série qui respecte une condition. C’est là qu’il est important de connaitre comment transformer une fonction ordinaire en fonction matricielle.

Le fichier que vous pouvez télécharger en cliquant sur le lien ci-dessous contient une série de 1500 nombres de 1 à 10 000.

Fichier exemple formule matricielle

À côté de chaque nombre on retrouve une lettre (A, B, C ou D). Il est facile d’extraire la plus grande valeur de cette série avec la fonction MAX. On peut aussi extraire la Nième plus grande valeur avec la fonction GRANDE.VALEUR. Mais si on veut connaitre la 3e plus grande valeur qui est associée à la lettre D, on devra créer une nouvelle colonne, y mettre un test logique (SI) pour vérifier si c’est bien la lettre D, ensuite appliquer un filtre pour garder seulement les lignes qui passent le test et finalement trouver la valeur MAX.

Tout ça peut être fait en une seule opération avec l’aide de la fonction GRANDE.VALEUR qui utilisera une fonction SI en mode matriciel. En faisant cela, on fera comme si on avait ajouté le test logique sur chacune des 1500 lignes.

Pour réussir cela, on devra utiliser une fonction SI pour l’argument Matrice de la fonction GRANDE.VALEUR. L’argument K (qui est le rang recherché; 1er, 2e, 3e, etc.) sera un chiffre normal. Ensuite… Roulement de tambour… On appuie sur Ctrl et Maj en même temps que la touche Entrée pour finaliser la fonction. La combinaison de touches avisera Excel qu’il doit effectuer la validation de la fonction SI de façon matricielle avant de retourner la Nième plus grande valeur.

En regardant dans la barre de formules, on peut voir qu’il y a une accolade «{» au début et une autre «}» à la fin. Ce symbole signifie que la fonction travaille en mode matriciel. On ne peut ajouter les accolades manuellement à une fonction. Il faut utiliser la combinaison de touches mentionnée précédemment pour que ça fonctionne.

Comme pour toute chose, la pratique vous permettra de devenir habile avec ce genre de fonction. Les chances sont fortes pour que votre premier essai ne soit pas un succès mais avec un peu de patience et quelques essais-erreurs, vous maîtriserez un nouvel aspect d’Excel. Bon plaisir!

Les fonctions de recherche

Mes prochains messages traiteront de certaines fonctions associées à des recherches et références. Nous avons vu, il y a déjà longtemps, la fonction RECHERCHEV. C’est essentiel de connaitre et utiliser cette fonction pour un très grand nombre d’usages.

INDEX

La fonction présentée aujourd’hui s’appelle INDEX. Elle permet de retourner la valeur située au croisement d’une ligne et d’une colonne, à l’intérieur d’une matrice.

Les arguments

Nous allons étudier le modèle matrice;no_ligne;no_colonne.

Matrice

La matrice est l’ensemble des cellules utilisées lors de la référence. Vous pouvez utiliser un Tableau Excel, une plage nommée ou encore choisir des cellules manuellement. Il doit par contre s’agir d’un rectangle, puisqu’on donnera le croisement d’une ligne et d’une colonne à Excel.

No_lig

Cet argument parle par lui-même… Il s’agit du numéro de ligne, à l’intérieur de la matrice, où se trouve la donnée recherchée.

No_col

Tout comme son cousin le numéro de lignes, le numéro de colonne dira à Excel dans quelle colonne rechercher l’information à retourner. Notez que, même si on parle de colonne, il faut mettre un numéro et non pas la lettre de la colonne. De plus, il s’agit du numéro de colonne de la matrice et non pas de la feuille Excel.

2 ou 3 arguments

On peut utiliser cette fonction avec une matrice à une seule ligne ou colonne. Dans ce cas, il ne sera pas nécessaire de préciser l’un ou l’autre des arguments No_lig ou No_col puisqu’il n’y en aura qu’une seule.

Utilisation

La fonction INDEX est utile lorsqu’on puisse l’interaction entre plusieurs tableaux afin de maximiser l’automatisation de certains calculs ou résultats. Il se peut que vous ne voyez pas d’utilité maintenant mais un jour, vous vous direz : « Il me semble que j’ai déjà vu une fonction qui pourrait faire ça… »

On va faire ça avec Excel…

J’aimerais vous partager un cas rencontré récemment. Dans la grande majorité des entreprises, on utilise Excel pour de multiples usages. Chacun y va de son mieux avec les connaissances qu’il possède. Lorsqu’on rencontre un nouveau besoin et qu’il n’existe pas de logiciel car le besoin est trop spécifique à l’entreprise, on utilise Excel.

Habituellement c’est efficace, i.-e. ça permet d’atteindre le résultat voulu. Mais l’efficience est requise dans le monde du travail. Pour ceux qui ont des doutes sur la différence entre l’un et l’autre

• Efficace permet d’atteindre le résultat voulu
• Efficient est le meilleur moyen (plus facile, rapide et performant) d’atteindre le résultat en question

Débordements

Mon cas met en vedette un gestionnaire qui avait des débordements saisonniers. Chaque fois que la haute saison arrivait, les dossiers s’empilaient, les clients s’impatientaient, les erreurs se multipliaient et le temps supplémentaire rendait la période extrêmement pénible. L’envie de quitter pour trouver mieux ailleurs revenait à chaque fois.

Une intervention de 5h

Au lieu rester passif et de «misérabiliser» sa vie pendant 8 semaines, le gestionnaire en question m’a passé un coup de fil. Il a fait appel à mes services pour améliorer son outil et automatiser certaines manipulations. Il aura fallu investir 5h pour

• Analyser et comprendre le flot d’information
• Réfléchir sur les améliorations possibles
• Expliquer au gestionnaire ce que je propose (pour fins de validation)
• Modifier le document en question
• Présenter la nouvelle version et donner une mini formation
• Procéder aux ajustements requis (il y a toujours des ajustements mineurs)

Résultat après ces 5h

• Diminution du risque d’erreurs
• Gain de temps approximatif de 15 minutes par utilisation de l’outil
• Gain de temps lors des mises à jour de données (environ 15 minutes)
• Augmentation de la productivité

Résultat

Grâce aux améliorations, le traitement des dossiers peut se faire à l’intérieur des heures normales de travail, les clients attendent moins pour obtenir réponse et il y a moins d’erreurs. Mais le plus important est que le gestionnaire est libéré de la pression qui lui donnait envie de quitter son emploi. Pour l’employeur autant que pour l’employé, c’est un gain appréciable. Difficile à chiffrer mais j’ai entendu dire que le remplacement d’un gestionnaire coûte tout près d’une année de salaire à l’entreprise. Et pour l’employé, tout lâcher et recommencer ailleurs n’est pas de tout repos.

Le comparatif

Si vous vous reconnaissez dans les lignes précédentes, demandez-vous et demandez à votre employeur combien coûtent les erreurs dues à la surcharge de travail, les clients insatisfaits à cause des délais et surtout combien coûterait un remplacement suite à un congé maladie (la mention d’un départ sonne un peu trop comme une menace selon moi). Le comparatif VS un investissement dans un outil plus efficient est habituellement assez éloquent.

Vous êtes-vous déjà senti désemparé en face d’un employé de banque? Vous savez, le moment où il pose tout un tas de questions pour savoir si votre prêt sera accordé… Calculer le paiement vous même avec Excel et la fonction VPM permet de réduire une bonne partie de l’inconfort. Il vous suffit de connaitre le taux d’intérêt (habituellement disponible sur le site web de l’institution financière), d’entrer les bons paramètres et le tour est joué. Prenons quelques minutes pour jeter un oeil aux arguments de la fonction.

Arguments de la fonction VPM

Taux

Taux est le taux d’intérêt du prêt par période. Par exemple, utilisez 6%/4 pour des paiements trimestriels à 6% APR. C’est l’explication donnée dans la boîte d’arguments… En plus clair, il faut prendre le taux annuel et le diviser par le nombre de paiement qui seront effectués durant une année. Quand on parle d’une hypothèque à 2,79% ce taux est toujours divisé selon le nombre de période de paiements. Si le paiement est fait à chaque semaine, on divisera le taux par 52, dans un calcul quinzomadaire on le divisera par 26 et s’il s’agit d’un versement mensuel ce sera par 12.

Npm

Npm est le nombre total de versements pour rembourser le prêt. Il faut multiplier le nombre d’années par le nombre de paiements par année.

Va

Va est la valeur actuelle, c’est-à-dire la valeur présente du total des remboursements futurs. Disons qu’au niveau explication, les gens de Microsoft n’ont pas fait fort… La valeur actuelle est votre avoir au moment de débuter le prêt. Vous devez le montant emprunté. Vous êtes dans le rouge!! Il s’agit donc du montant de l’emprunt en négatif. Pour un emprunt de 200 000$ il faut mettre -200 000$.

Vc

Vc est la valeur future ou valeur capitalisée, c’est-à-dire un montant que vous voulez obtenir après le dernier paiement, 0 (zéro) si omis. Encore une fois, ça pourrait être plus clair. Si vous voulez que les paiements s’arrêtent à un certain moment, vous pouvez mettre le montant que vous devrez après la série de paiements. Disons que vous toucherez un héritage de 5000$ dans 3 ans. Vous voulez emprunter 25 000$ tout de suite et faire des paiements pendant 3 ans pour qu’il reste 5000$ à verser à ce moment. Mettez la valeur 5000 à l’argument Vc.

Type

Le type fait référence au moment où vous ferez vos paiements; au début ou à la fin de chaque période. L’incidence est relativement faible mais sachez que les banques fonctionnent généralement avec un paiement à la fin de la période. Votre premier paiement est fait après 7, 14 ou 30 jours au lieu d’être fait au moment du prêt. Vous pouvez laisser cet argument vide pour faire vos calculs.

Exemple

Dans l’exemple ci-dessous, un emprunt de 258 000$ étalé sur 25 ans, avec des paiements hebdomadaires et un taux de 2,79% donnera des paiements de 275,71$ chaque semaine.

Voilà! Vous pouvez maintenant calculer n’importe quel emprunt et savoir à quoi ressemblera votre paiement. Votre prochaine visite à la banque sera moins intimidante.

Vous pouvez télécharger mon fichier exemple ci-dessous

Fonction VPM

Certaines fonctions disponibles dans Excel n’ont pas d’utilité pour le commun des mortels. La fonction BASE en est une. L’article que je propose ce matin se veut plus de la connaissance générale qu’un outil réel.

Nous avons appris les mathématiques en utilisant les nombres en base décimale. Grossièrement cela veut dire qu’on ajoute un caractère  lorsqu’on atteint 10 unités. Pour mieux l’illustrer, comptons ensemble; 1, 2, 3, 4, 5, 6, 7, 8, 9… Ensuite, on doit ajouter un deuxième caractère pour continuer de compter; 10, 11, 12, 13, 14, 15, 16, 17… Après, la dizaine va changer; 20, 21, 22, 23… Jusqu’à l’atteinte de la dernière combinaison à deux chiffres: 99.

Ça semble étrange de l’expliquer car nous avons toujours appris à compter ainsi. Vous devez avoir l’impression que je donne un cours à des enfants de première année. Mais on va complexifier les choses un peu. Comptons maintenant en base 5. Cela veut dire que nous ajouterons un caractère lorsqu’on atteindra 5 unités. Comptons ensemble; 1, 2, 3, 4… On arrive à la 5e unité. On obtient alors; 10, 11, 12, 13, 14… On doit ensuite majorer le caractère de gauche; 20, 21, 22, 23, 24… Et ainsi de suite jusqu’à 44 qui sera le dernier nombre avant d’ajouter un 3e caractère. Après 44 on passe à 100.

Notez qu’on ne peut employer le terme dizaine puisqu’il ne s’agit pas d’une dizaine mais bien d’une «cinquaine»

Certains diront que c’est trop compliqué. D’autres diront que c’est inutile. Je reconnais qu’il y a peu de chance que vous utilisiez cela dans la vie courante. Malgré tout, un peu de connaissance n’a jamais fait de mal à personne. Le fichier qui se trouve plus bas montre l’emploi de la fonction BASE à partir d’une série de nombre. Vous pourrez voir comment s’écrivent les nombres en base 2, 3, 4, 5, 6, 7, 8 et 9.

Pour ceux qui ont déjà entendu parler de «base hexadecimale», il s’agit d’une base qui comporte 16 caractères avant de passer à une nouvelle «seizaine». On ajoute alors des lettres de l’alphabet pour aller au delà du chiffre 9.

La dernière colonne du tableau montre la base la plus grande que Excel peut gérer: 36. Elle utilise tous les chiffres de 0 à 9 et toutes les lettres de a à z.

Fonction BASE

En ce début d’année 2017, je prends quelques instants pour vous souhaiter la santé, le plaisir, la réussite et l’efficience. Souhaiter l’efficience est plutôt non conventionnel, mais si on y pense bien l’efficacité permet de libérer du temps et avoir plus de temps c’est fort agréable. Pas bête n’est ce pas!?!?

Passons maintenant aux choses sérieuses: la fonction ENT se veut un raccourci simple et efficace pour plusieurs applications. Si je parle de raccourci c’est qu’on peut facilement se passer de cette fonction en combinant d’autres fonctions. Mais comme je viens de vous parler d’efficience, il me semble évident qu’utiliser la fonction ENT est à propos.

La fonction ENT arrondit un nombre à l’entier inférieur. Si on veut créer un tableau qui utilise les dates d’anniversaires d’un groupe de gens pour déterminer leur âge, on voudra éliminer les décimales. Quand on parle d’âge, on n’a pas 34 ans et 676 millièmes d’année… On a 34 ans!

Il serait possible d’utiliser la fonction ARRONDI.INF et de mettre zéro pour le nombre de décimales mais la fonction ENT est plus rapide, plus simple et plus efficiente dans un tel contexte. Le même scénario s’applique pour calculer l’ancienneté et plusieurs autres calculs de temps.

Cette fonction Excel n’a qu’un seul argument: la cellule qui contient le nombre à arrondir.

En espérant que ça pourra vous être utile.

À bientôt!

Si vous utilisez déjà les commandes «Rechercher» et «Remplacer» dans Excel ou dans d’autres programmes de la suite Office, vous savez à quel point elles peuvent être utiles. Au lieu de scruter à la loupe chaque cellule pour vérifier si elle contient l’élément recherché, il suffit de laisser Excel faire la recherche pour vous et ensuite d’effectuer le traitement voulu. Plus il y a de données, plus on apprécie cette fonctionnalité.

Par contre, si on veut identifier toutes les cellules qui contiennent une chaîne de caractère spécifique pour ensuite les filtrer, ce ne sera pas le bon outil. La fonction CHERCHE pourrait certainement vous donner un coup de main à atteindre votre objectif.

Voici comment ça fonctionne: la fonction CHERCHE identifie le numéro du caractère où se trouve un caractère ou une chaîne de caractères à l’intérieur d’une cellule. On peut donc savoir où se trouve le caractère spécifique. À quoi ça sert? Disons que nous avons une liste de pièces. Les pièces ont un code d’une longueur variable selon le modèle. Comment pourrait-on identifier les pièces qui sont des courroies? Si toutes les courroies ont un «A» comme caractère juste après un tiret (-), on pourra sûrement trouver une façon…

Dans cet exemple, la fonction CHERCHE permettrait d’identifier où le trouve le tiret. Ensuite, on pourrait utiliser la fonction STXT pour retourner seulement le premier caractère situé après le tiret. S’il s’agit d’une courroie, il y aura un «A» dans la cellule où on aura combiné ces deux fonctions.

Un autre exemple: à partir d’une liste de courriels, je veux extraire le nom de domaine pour chacune d’entre elles. Rien de plus facile! La fonction CHERCHE me permettra de trouver la position du «@» et ensuite en combinant la fonction NBCAR et DROITE, je pourrai extraire le domaine de façon automatique.

Maintenant qu’on a des utilisations concrètes de la fonction, voici comment l’utiliser.

1. Le premier argument est le ou les caractères à rechercher. On peut y mettre le contenu à recherche directement ou faire référence à une cellule, ce qui permettra de paramétrer l’élément recherché. Dans l’exemple des adresses courriel, il s’agit du «@», puisqu’il sépare toujours le nom du détenteur de l’adresse et le domaine.
2. Le second argument est la cellule qui contient le texte dans lequel on veut identifier la position du ou des caractères. Dans l’exemple des adresses courriels, il s’agit des adresses.
3. Un 3e argument facultatif permet de commencer à chercher à partir d’un numéro de caractère. Si vous avez une très longue chaîne de caractère dans votre cellule et que vous souhaitez que la recherche se fasse à partir du xième caractère, il faut simplement entrer le numéro du caractère de départ. Encore une fois, il peut s’avérer utile de faire référence à une cellule pour cette valeur car on pourra ensuite paramétrer facilement le caractère de départ.

L’explication peut semble un peu floue pour certains. Je dois admettre que ce n’est pas la fonction la plus facile à expliquer car on l’emploie rarement toute seule. Dans la grande majorité des cas, le résultat final implique l’utilisation d’autres fonctions.

Histoire de rendre la chose plus concrète, j’ai préparé un petit fichier d’exercice pour faire un essai. Amusez-vous!

Exercice CHERCHE