Fonction CHERCHE
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.
- 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.
- 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.
- 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!
Fonction MAINTENANT
Possiblement la fonction la plus simple à utiliser avec sa soeur AUJOURDHUI, la fonction MAINTENANT est fort pratique lorsqu’on veut saisir un événement dans le temps. Elle retourne la valeur précise associée au moment présent, selon le modèle de temps Excel.
Rappelons-nous que l’unité de temps dans Excel est le jour. Les heures sont des subdivisions d’un jour (à raison d’un vingt-quatrième 1/24) et les minutes des subdivision d’heures (donc un soixantième d’un vingt-quatrième [1/24]/60). Les secondes poursuivent le modèle en divisant une autre fois par 60. Chaque seconde qui passe représente donc 0,000011574 jour.
| Jour | 1 |
| heure | 0,041666667 |
| minute | 0,000694444 |
| seconde | 0,000011574 |
En partant de ce principe, la fonction MAINTENANT donne le nombre exact qui correspond à la date (42705 pour le premier décembre) en plus d’y ajouter les décimales qui permettent de compter les heures, les minutes et les secondes. Le temps peut ainsi être calculé selon des équations purement mathématiques.
Lorsqu’on intègre la fonction MAINTENANT à l’intérieur d’une macro, on peut facilement garder une trace du moment précis où est survenu un événement dans Excel. Si vous n’y voyez pas encore d’utilité, voici quelques exemples où ça peut s’avérer utile:
- Feuille de temps qui fonctionne comme une carte de punch et qui calcule automatiquement le total des heures chaque jour
- Feuille de suivi des tâches journalières pour mesurer le temps passé à exécuter chacune d’entre elles
- Suivi des cédules d’entretien; par exemple chaque fois qu’un entretien est fait dans une salle de bain
- Suivi des entrées de clients dans un commerce; chaque fois qu’un nouveau client entre, on appuie sur un bouton et le moment est enregistré
Bien sûr il y a une infinité de possibilités. Chaque fois qu’une statistique de temps pourrait s’avérer intéressante, la fonction MAINTENANT devient utile.
Pour l’utiliser, il suffit d’inscrire «=maintenant()» dans une cellule [ne pas mettre les guillemets]. Elle se mettra automatiquement à jour à chaque nouvel événement. Pour garder la trace, il faudra créer la macro qui va chercher la valeur de MAINTENANT et qui la copie ailleurs. Très simple pour quiconque sait faire des macros de base.
Comme la fonction est trop simple pour avoir besoin de pratique, j’ai préparé un fichier exemple au lieu d’un fichier d’exercice. Il s’agit d’une feuille de temps qui utilise MAINTENANT et quelques macros pour saisir l’information dans un tableau. Faites un essai, c’est amusant. Notez que les valeurs du tableaux s’arrondissent par tranches de 15 minutes. Vous pouvez changer cette valeur dans la cellule G3. Il vous faudra aussi activer les macros pour que ça fonctionne. N’ayez crainte, le fichier est sans danger pour votre ordinateur.
Fonctions ET & OU
Deux nouvelles fonctions ce matin: ET & OU. Ne pas confondre avec les ingrédients d’une recette dans laquelle il y a acide ascorbique, dimethylposiloxane, glutamate monosodique et/ou polydimethylpolysiloxane (Croyez-moi ce sont de réels ingrédients utilisés dans l’alimentation!). Il s’agit plutôt de deux fonctions cousines qui permettent de pousser plus loin la fonction SI.
Il faut avant tout comprendre que la fonction SI est limitée à un seul test logique. Du moins, elle ne peut en vérifier qu’un seul à la fois. On peut en imbriquer plusieurs et ainsi ajouter des étages, mais sans ses deux cousines, on ne pourra pas vérifier plusieurs critères en simultané. Pour rendre la dernière phrase plus simple à comprendre, on ne peut pas vérifier SI Sophie a les yeux bleus et les cheveux blonds. Pour faire une telle vérification, il faudra imbriquer la fonction ET à l’intérieur de la fonction SI.
Commençons par définir ce qu’est imbriquer des fonctions. On parlera d’imbrication lorsqu’une fonction est utilisée comme argument dans une autre fonction. Lorsque vient le temps de remplir les champs arguments d’une fonction, on peut y mettre des nombres, du texte, des cellules référence et… une autre fonction. Pour vérifier SI Sophie a les yeux bleus ET les cheveux blonds, il faudra donc utiliser la fonction ET à l’intérieur de la fonction SI.
Fonction ET
La fonction ET sert à vérifier que tous les test logiques (ou toutes les affirmations dans mon vocabulaire à moi), sont vrais. Si et seulement si ils sont tous vrais, la fonction retournera la valeur VRAI. Dès qu’une des affirmations sera fausse, le résultat de la fonction ET sera faux. Dans mon exemple, l’affirmation 1 est que Sophie a les yeux bleus et l’affirmation 2 est que Sophie a les cheveux blonds. Si les deux affirmations sont vraies, alors la fonction ET retournera le résultat VRAI. Si Sophie a les yeux bleus et les cheveux bruns, le résultat sera FAUX.
Fonction OU
La fonction OU permet de vérifier qu’une ou l’autre des conditions spécifiées est vraie. Dès qu’une des conditions est vrai, le résultat sera VRAI.
On peut vérifier jusqu’à 255 affirmations dans une même fonction. On peut aussi combiner des fonction ET & OU. Comme le résultat que ces fonctions retournent est toujours VRAI ou FAUX, on devra utiliser une fonction SI pour faire afficher autre chose. La formule prendra alors la forme SI(fonction ET ou OU;valeur si vrai;valeur si faux). Le premier argument de la fonction sera la fonction ET ou la fonction OU. Celle-ci retournera directement la valeur VRAI ou FAUX à la fonction SI qui, à son tour, retournera ce que vous aurez déterminé comme étant la valeur si vrai ou la valeur si faux selon le cas.
Comme pour les articles précédents, j’ai préparé un fichier qui permet de mettre le tout en application. Vous pouvez le télécharger en suivant le lien ci-dessous.
Fonction CHOISIR
Dans la série des articles sur les fonctions, je vous présente aujourd’hui la fonction CHOISIR. Lors des formations que je donne, je rencontre souvent des gens qui ont découvert la fonction SI et qui utilisent cette fonction dans toutes les situations. Il est vrai qu’on peut faire beaucoup de choses avec SI mais c’est un peu comme utiliser le micro-ondes pour tout faire dans une maison. On peut faire chauffer de l’eau, faire sécher un chandail, on peut sûrement faire cuire le spaghetti dedans et je parie que quelqu’un a déjà tenté d’y faire des toasts.
La fonction CHOISIR remplacera certainement la fonction SI pour certaines situations simples que vous rencontrez. Voici comment elle fonctionne: on lui donne une liste de résultats à retourner et on lui donne le numéro du résultat voulu. Ça sonne un peu étrange dit comme ça. L’exemple ci-dessous devrait être plus clair.
Disons qu’on a un tableau qui contient une liste de clients avec un numéro de 1 à 6 qui représente la catégorie du client.
1 = homme moins de 30 ans, 2 = femme moins de 30 ans,3 = homme 30-45 ans, 4 = femme 30-45 ans, etc.
À la fin de l’année, on veut envoyer un cadeau adapté aux hommes et aux femmes et aussi à leur âge. La fonction CHOISIR sera tout indiqué. Dans cet exemple, le premier argument de la fonction (le numéro du résultat) sera le numéro associé à chaque client. Les arguments suivants seront les différents cadeaux possibles. Tellement simple!
Le fichier ci-dessous présente l’exemple mentionné avec une colonne pour vous permettre d’essayer la fonction.
Connaitre plus de fonctions pour aller plus vite
Plus on a de connaissances, plus on peut faire de choses. Ça va de soi. Les connaissances permettent d’en faire plus, plus vite et habituellement mieux.
Cela s’applique aussi à Excel. Plus on sait de choses, plus on connait les possibilités, plus on connait de fonctions et de méthodes pour travailler avec, plus on devient efficace. Encore une fois, ça va de soi.
C’est pour cette raison que je vais publier tout plein d’articles qui présentent des fonctions au cours des prochains jours. Si vous visitez régulièrement cette page, vous devriez apprendre l’existence de plusieurs fonctions qui sont méconnues. Certaines ne vous intéresseront probablement pas mais d’autres vous permettront d’accélérer votre travail et ainsi d’en faire plus, plus vite et mieux 😉
NB.SI
La première que je vous présente est NB.SI. Elle permet de compter le nombre de cellules qui rencontrent un critère. Supposons que vous avez une liste de 200 personnes avec leur ville de résidence, la population de la ville et la date de naissance. Vous voulez savoir combien de gens habitent dans chacune des villes. Il suffit de créer un nouveau tableau qui liste les villes dans la première colonne et de mettre la fonction NB.SI dans la colonne voisine.
Le premier argument de cette fonction est la plage de cellules dans laquelle se trouvent les cellules à évaluer en fonction du critère. Dans le cas présent, il s’agit de la colonne qui affiche la ville de résidence des gens. Le 2e argument est le critère d’évaluation. Dans notre cas, il s’agit de la cellule voisine puisque nous sommes dans un tableau qui liste les villes. Une fois les deux arguments définis, on appuie sur la touche « Entrée » et le tour est joué.
Idéalement, on a converti la liste en Tableau Excel au préalable. De cette façon, on n’aura qu’à créer une seule fonction et tout le reste se fera de façon automatique. Tout simplement magique!!
On pourra ensuite trouver tout plein d’information grâce à cette fonction. Si le coeur vous en dit, j’ai préparé un fichier avec une liste de gens et 4 petites questions pour pratiquer l’utilisation de cette fonction. Vous pouvez télécharger le fichier en suivant le lien ci-dessous
La Validation des données
Validation de données
Dans un article précédent, j’ai parlé des Tableaux Excel et de quelques avantages qu’ils procurent. Cette méthode de travail permet de filtrer et de faire une analyse de base simplement et rapidement. J’ai aussi effleuré les Tableaux croisés dynamiques qui permettent un niveau d’analyse plus poussé lorsque c’est requis.
Ces merveilleux outils fonctionnent à merveille lorsque les données ont été entrées correctement et de façon uniforme. S’il y a des variations dans le modèle de saisie, les chances sont grandes pour que les résultats d’analyse deviennent boiteux. Cela vous place devant un choix : soit vous « nettoyez » les données en corrigeant les variantes qui nuisent, soit vous éliminez celles-ci de votre analyse en vous concentrant sur celles qui sont entrées correctement.
L’option un requiert un investissement de temps que vous n’avez probablement pas. L’option deux donnera des résultats biaisés. Dans tous les cas, vous serez biaisé mais sans le premier « i ».
La solution à ce problème passe par la commande Validation des données. Avec cette commande, vous pourrez définir les paramètres pour chaque colonne et vous assurer de la conformité des données qui y seront saisies.
Il suffit de sélectionner les cellules qui doivent être Validées et de cliquer sur Validation des données dans l’onglet du ruban Données. Vous pourrez ensuite choisir le type de données à entrer dans les cellules en question ou bien définir une liste précise de laquelle il ne sera pas possible de déroger.
En plus d’empêcher la saisie de données non conformes, il sera possible de laisser une directive pour guider l’utilisateur. Vous pourrez faire afficher un message qui s’affichera dès que la cellule sera sélectionnée. Vous pourrez aussi définir un message d’erreur avec des directives supplémentaires.
Avec la Validation des données, plus besoin de faire le nettoyage de vos tableaux de données avant de les utiliser. Plutôt intéressant n’est-ce pas !?!?
Mise en forme conditionnelle
Une personne de mon entourage a récemment eu à apporter des ajustements de salaire à plusieurs centaines de personnes. Pour certains, il s’agissait d’une augmentation et donc d’une bonne nouvelle. Pour les moins chanceux, l’exercice causait une baisse de revenus.
Comment mettre en évidence les variations négatives pour faire une double vérification? La mise en forme conditionnelle serait une option toute indiquée.
Pour ceux qui n’utilisent pas encore cet outil, son fonctionnement est vraiment très simple. Il suffit d’appliquer un test logique et de dire à Excel comment la cellule doit être formatée lorsque la condition est remplie.
Imaginons que l’ancien salaire est dans la colonne « K » et que le nouveau salaire s’affiche dans la colonne « L » pour tous les employés. On ajoute une colonne « M » qui calcule la variation entre « K » et « L »; si la variation est positive, on fait afficher la cellule en vert, si la variation est neutre, en jaune et si la variation est négative, on fait afficher la cellule en rouge.
Pour faire cela, il suffira de créer 3 règles avec 3 couleurs différentes et de les appliquer aux bonnes cellules. C’est l’histoire de 5 minutes et ça permet de voir clairement ce qui se passe.
En plus, on pourra trier ou filtrer les valeurs en fonction des couleurs et ainsi se concentrer sur un groupe en particulier.
La commande de mise en forme conditionnelle se trouve dans l’onglet du ruban Accueil. Attention de ne pas vous faire prendre : il est possible d’assigner la mise en forme conditionnelle à la fin de l’exercice en choisissant Gérer les règles mais c’est tellement plus simple de sélectionner les cellules avant de créer les règles. Je vous recommande fortement de procéder ainsi. Surtout pour les premières fois.
Sélectionnez les cellules qui doivent changer d’apparence, cliquez sur la commande Mise en forme conditionnelle, choisissez Règles de mise en surbrillance des cellules, et choisissez Supérieur à. Attribuez la valeur zéro et choisissez Remplissage vert avec texte vert foncé.
Ensuite, répéter l’exercice mais avec l’option Égal à, attribuez encore la valeur zéro et choisissez Remplissage jaune avec texte jaune foncé. Une autre fois en choisissant Inférieur à, toujours valeur à zéro et Remplissage rouge avec texte rouge foncé. Voilà! Vos cellules sont maintenant colorées selon la variation.
Bien sûr, vous n’êtes pas obligé de faire colorer toutes les cellules. Vous pourriez choisir de colorer seulement les baisses de salaire pour faire la double vérification et savoir qui aura la mine basse en recevant la nouvelle. Vous pourrez en profiter pour envoyer quelque chose de positif en parallèle pour aider à faire passer la pilule.
Imprimer un tableau sur plusieurs pages
Lorsqu’on travaille avec un tableau qui à quelques centaines de lignes, il est facile de voir à quoi correspondent les données de chaque colonne puisque les entêtes restent visibles.
Si vous devez imprimer ce document pour vous y référer dans un contexte où vous n’aurez pas votre écran sous les yeux, c’est un peu moins évident. Surtout si les données peuvent être confondues d’une colonne à l’autre.
Pour faciliter la consultation papier d’un document semblable, j’ai deux recommandations à proposer :
Mettre sous forme de Tableau Excel
Pour plusieurs c’est un réflexe. Pour ceux qui ne le feraient pas de façon systématique, il est grand temps de commencer cette bonne habitude. Les Tableaux Excel possèdent tout plein de caractéristiques avantageuses pour vous aider à être plus productif. Pourquoi s’en passer?
Une fois que vos données seront mises sous forme de Tableau Excel, dans les Outils de Tableau vous pourrez cocher Colonnes à bandes et vous assurer que Lignes à bandes n’est pas coché. Vos colonnes seront ainsi colorées et les données d’une même colonne seront plus évidentes.
Imprimer les titres
Rendu à la page 7, vos colonnes seront toujours clairement identifiées mais comment vous souvenir du titre de la colonne? Bien sûr vous pouvez insérer une ligne qui répétera les entêtes de colonne à intervalles réguliers dans votre document. J’espère seulement que vous ne fausserez pas vos données en procédant ainsi.
La solution toute simple réside dans l’onglet du ruban Mise en page. À cet endroit, vous pouvez cliquer sur Imprimer les titres et indiquer à Excel la ligne à répéter en haut de chaque page imprimée.
Comme vous pourrez le voir en faisant un essai, le même principe s’applique pour la colonne qui contient l’information référence. Si votre tableau est large et doit être imprimé sur plus d’une page en largeur, vous n’avez qu’à indiquer quelle colonne doit être répétée.
Plutôt pratique non? Plus besoin de répéter manuellement, d’inscrire à la main ou de faire du bricolage… Quel bel outil !!!
Les «faux nombres» dans Excel
On l’a tous déjà vécu: une série de nombres dont le format n’est pas adéquat et qui ne peuvent être utilisés pour faire des opérations dans Excel. Lorsque vous regardez les cellules, vous pouvez voir les nombres mais dès que vous tentez de faire une addition ou une multiplication vous obtenez #VALEUR! Il est plus que probable que les nombres soient inscrits d’une façon incompréhensible par Excel.
S’il s’agit d’une liste qui ne sera travaillée qu’une seule fois, vous utiliserez probablement la commande Remplacer (Ctrl + H) et vous pourrez transformer les «faux nombres» en vrais nombres en seulement quelques clics. S’il s’agit d’une liste que vous utilisez sur base régulière, vous pourrez peut être créer une Macro qui pourra convertir le tout et vous permettre de continuer votre travail. Un peu complexe pour ceux qui n’osent pas s’aventurer dans le monde mystérieux des macros.
Il existe une autre solution pour résoudre cette fâcheuse situation : une combinaison de fonctions. Par exemple, si vous avez une série de nombres qui représentent des montants d’argent et qui sont entrés de la façon suivante: 39.99 $
Excel ne reconnaît pas le nombre pour deux raisons:
- Utilisation du point au lieu de la virgule comme séparateur décimal
- Ajout du symbole «$» à la fin, précédé d’un espace
La recette que je vous propose va comme suit:
- Compter le nombre de caractères dans la cellule avec la fonction NBCAR
- Remplacer les deux derniers caractères (l’espace et le symbole $) par rien avec la fonction REMPLACER, en utilisant le résultat de NBCAR moins un comme 2e argument
- Utiliser la fonction VALEURNOMBRE avec le résultat des fonctions précédentes pour que Excel remplace le séparateur décimal.
Si on utilise le tout dans un Tableau Excel, on n’aura qu’à entrer une formule ressemblant à ceci: =VALEURNOMBRE(REMPLACER([@Montant];NBCAR([@Montant])-1;2;»»);».»)
Dans une colonne et coller les «faux nombres» dans la colonne Montant. Plus besoin de faire d»autres manipulations pour le futur.
Si la formule vous semble complexe à regarder, je vous invite à créer un Tableau Excel à trois colonnes. Nommez vos colonnes comme suite: Montant, Montant converti, Multiplié.
Entrez quelques nombres au format mentionné plus haut (Ex: 33.78 $) dans la première colonne
Copiez-collez la formule dans la 2e colonne
Faites une multiplication simple dans la 3e colonne pour vérifier que les nombres convertis sont reconnus par Excel.
Il ne vous reste ensuite qu’à cliquer sur une cellule de la 2e colonne et sur la touche Fx pour analyser les fonctions, les arguments et mieux comprendre en quoi consiste la formule.
Comment faire des macro commandes
Comprendre ce qu’est une Macro
Une macro est une procédure, c’est-à-dire un ensemble d’instructions qui exécutent une tâche spécifique ou renvoient un résultat. Intégré à même Excel il y a une interface de programmation VBA qui permet de créer des fonctions personnalisées et utilisables comme si elles étaient intégrées dans le logiciel. Lorsqu’on enregistre une macro, on crée des lignes de programmation dans VBA. Une fois que la programmation est complétée, on peut utiliser le sous-programme pour exécuter les tâches voulues. En d’autres termes, une macro commande est un petit robot à qui on enseigne une série d’instruction à exécuter lorsqu’on lui en donne l’ordre.
Procédure
Lorsque vient le temps de créer une macro, il faut:
- Déterminer exactement les actions à exécuter
- Lister celles-ci dans l’ordre exact où elles doivent être exécutées
- Vérifier les étapes en faisant un essai sans enregistrer
- Une fois qu’on est certain des étapes et de l’ordre, appuyer sur le bouton d’enregistrement de macro (coin inférieur gauche de l’écran)
- Exécuter la série d’actions
- Cliquer sur le bouton d’arrêt (même bouton que pour lancer l’enregistrement)
Pas plus compliqué que ça. En fait… Oui, c’est un peu plus compliqué que ça car il y a la notion macro relative et absolue et il y a l’édition de la macro dans VBA. Il faut aussi lancer la macro, idéalement lui attribuer une raccourci clavier ou encore l’intégrer au ruban, déterminer si elle doit être enregistrée dans le classeur de macro personnelles ou dans le fichier lui-même… Malgré tout, juste en appliquant cette procédure correctement, vous serez en mesure de créer des macros de base et d’automatiser les tâches redondantes. Pour le reste, une formation avec Monsieur Excel serait la solution idéale 😉
Un détail important: après avoir exécuté une macro, on ne peut faire un retour en arrière (undo). Cela veut donc dire qu’il est important de vérifier que la macro est bien au point avant de l’utiliser sur des données importantes.
Bon succès avec vos futures macros!!