EXCEL A MAITRISER ABSOLUMENT€¦ · Excel peut retourner une erreur dans de nombreuses situations. Heureusement, toutes les erreurs ne renvoient pas le même message. Voici la liste

LES 12 FONCTIONS

EXCEL A MAITRISER

ABSOLUMENT Ne perdez plus de temps avec Excel

Nous utilisons tous Word Excel PowerPoint Outlook ou OneNote Mais savez-vous en exploiter toutes les capaciteacutes

Dans ce livre je vous preacutesente les fonctionnaliteacutes drsquoExcel qui vous feront gagner du temps Votre renommeacutee sur lrsquoutilisation drsquoExcel ne fera que croicirctre

Les fonctionnaliteacutes suivantes vont vous permettre drsquoen savoir un peu plus sur le potentiel drsquoExcel Pour continuer agrave en apprendre plus encore rien de plus facile

httpsexceller-avec-la-bureautiquecom

Les 12 Fonctions Excel agrave Maicirctriser Absolument


2

TABLE DES MATIEgraveRES

Merci 3

Excel 4

Ndeg1 Donner le LA avec les SI() SICONDITIONS() et SIMULTIPLE() 4

Ndeg2 La SOMME() crsquoest bien SOMMESI() crsquoest mieux SOMMESIENS() afin drsquoecirctre au top 8

Ndeg3 Il y a aussi NB() qui peut poser ses conditions NBSI() et NBSIENS() 12

Ndeg4 Avant il y avait la RECHERCHEV() RECHERCHEH() maintenant il y a RECHERCHEX() 13

Ndeg5 Plus fort que RECHERCHEV() le couple INDEX() et EQUIV() 17

Ndeg6 Le summum avec EQUIVX() 18

Ndeg7 Les fonctions essentielles sur les textes 19

Ndeg8 Les erreurs cela arrivehellip Mais il faut savoir les geacuterer 20

Ndeg9 Les tableaux dynamiques (agrave ne pas confondre avec les tableaux croiseacutes dynamiques ) 23

Ndeg10 Ecirctes-vous UNIQUE() 23

Ndeg11 FILTRER() nrsquoa jamais eacuteteacute aussi facile 27

Ndeg12 Remplissage instantaneacute 27

Ndeg13 EN BONUS Ne plus avoir agrave utiliser laquo lrsquoadresse raquo drsquoune cellule dans les formules 28

Ndeg14 EN BONUS Utiliser les segments 30

Ndeg15 EN BONUS Utiliser la chronologie 32

Copyright 34



3

MERCI

Depuis de nombreuses anneacutees entre mes eacutetudes et mon travail tout comme vous jrsquoai utiliseacute les outils de Microsoft Word Excel PowerPoint Outlook puis OneNote A de nombreuses reprises je me suis eacutenerveacute agaceacute que telle ou telle fonctionnaliteacute ne fonctionne pas correctement

Avec le temps jrsquoai suivi les eacutevolutions de ces diffeacuterentes applications De plus au lieu de mrsquoarrecircter agrave lrsquoexclamation laquo Ccedila ne fonctionne pas raquo jrsquoai creuseacute jrsquoai appris et compris pour arriver aujourdrsquohui agrave parfaitement les maicirctriser

Ensuite au travail surtout mais aupregraves de mes proches eacutegalement jrsquoai noteacute que plus personne ne se formait agrave ces outils La connaissance la maicirctrise des applications de bureautique est devenue un preacuterequis pour tous les emplois lors de lrsquoembauche mais personne ne prend le temps de veacuterifier On pourrait plaisanter en se demandant si celui qui embauche en sait reacuteellement plushellip )

Crsquoest pour ces raisons que jrsquoai deacutecideacute de lancer ce blog Pour aider le plus de personnes possibles agrave reprendre le controcircle de ces applications omnipreacutesentes en entreprise et qui sont agrave lrsquoorigine de nombreuses pertes de temps et sources de stress

laquo Exceller avec la bureautique raquo aurait aussi pu se nommer laquo Zen avec la bureautique raquo Eacutecrivez-moi afin de partager les problegravemes auxquels vous faites face les difficulteacutes agrave trouver la bonne formation agrave lrsquoadresse charlesexceller-avec-la-bureautiquecom Je tenterai de vous reacutepondre agrave travers un article de blog ou vous renverrai vers des articles abordant le sujet

Dans un premier temps je vous propose de prendre de la hauteur sur les questions agrave vous poser

Un enseignement qui nenseigne pas agrave se poser des questions est mauvais Paul Valeacutery ndash Artiste eacutecrivain philosophe poegravete (1871 - 1945)

Ensuite je vous propose de suivre les diffeacuterents articles de mon blog pour comprendre et trouver votre chemin dans les meacuteandres des applications de Microsoft

Le veacuteritable enseignement nest point de te parler mais de te conduire Antoine De Saint-Exupeacutery ndash Artiste aviateur eacutecrivain (1900 - 1944)

Voici donc les 12 Fonctions Excel agrave Maicirctriser Absolument

PS Je nrsquoutilise que la derniegravere version drsquoExcel agrave disposition = Excel 365



4

EXCEL

Ndeg1 Donner le LA avec les SI() SICONDITIONS() et SIMULTIPLE()

La fonction SI() est lrsquoune des premiegraveres et parmi les plus utiliseacutees des fonctions drsquoExcel

Elle permet de tester une valeurune expression et de retourner soit VRAI soit FAUX

Cette fonction va deacuteterminer la valeur que prend la cellule A1 en fonction drsquoune condition Ce sera valeur_si_vrai ou valeur_si_faux

Lagrave ougrave cette fonction prend de lrsquoampleur crsquoest lorsque valeur_si_vrai et valeur_si_faux peuvent elles-mecircmes ecirctre conditionnelles Autrement dit on peut imbriquer les conditions



5

Un exemple avec les moyennes drsquoeacutelegraveves au baccalaureacuteat

La fonction SI() permet de choisir lrsquoune des deux valeurs

Peut-ecirctre souhaiteriez-vous deacuteterminer la bonne valeur de mention en fonction de la note Allons-y avec plusieurs fonctions SI() imbriqueacutees

La lecture de ces SI() imbriqueacutes devient difficile et surtout incertaine si cette chaicircne de SI() nrsquoest pas construite de maniegravere logique



6

=SI([Moyenne]lt12

Pas de mention

SI([Moyenne]lt14

Assez bien

SI([Moyenne]lt16

Mention bien

SI([Moyenne]lt18

Mention tregraves bien

Feacutelicitations du jury

)

)

)

)

Dans mon cas je me suis assureacute drsquoeacuteliminer toutes les notes infeacuterieures agrave 12 au deacutebut ainsi la moyenne sera neacutecessairement supeacuterieure ou eacutegale (En rouge)

Jrsquoai alors continueacute agrave exclure progressivement et tranche par tranche les notes supeacuterieures agrave 12 (en bleu)

Cela fonctionne mais serez-vous certain de ne pas vous tromper

1 Dans la lecture de cette fonction 2 Si vous eacutetiez ameneacute agrave modifier cette fonction

Heureusement Excel propose une autre option pour cette situation

La fonction SICONDITIONS() est lagrave pour cela

valeur_si_vrai

valeur_si_faux



7

Cette fois les conditions et le reacutesultat agrave afficher vont de pair Chaque condition est indeacutependante il faut donc preacuteciser les conditions complegravetes agrave respecter

Ci-dessous vous avez la fonction complegravete Chaque changement de couleur indique un nouveau couple laquo test_logique raquo laquo valeur_si_vrai raquo Pour plus de lisibiliteacute les valeur_si_vrai ont eacuteteacute mises en gras

=SICONDITIONS(

[Moyenne]lt12Pas de mention

ET([Moyenne]gt=12[Moyenne]lt14)Mention assez bien

ET([Moyenne]gt=14[Moyenne]lt16)Mention bien

ET([Moyenne]gt=16[Moyenne]lt18)Mention tregraves bien

[Moyenne]gt=18Feacutelicitations du jury)

Au passage vous noterez lrsquoutilisation de la fonction ET() qui garantit que deux critegraveres (ou plus) sont respecteacutes Elle retourne VRAI ou FAUX

Une autre fonction peut porter agrave confusion avec SICONDITIONS() il srsquoagit de SIMULTIPLE()

Dans notre cas cette fonction nrsquoaurait pas drsquoutiliteacute mais voyons comment elle fonctionne

Cette fonction fonctionne comme un aiguilleur (ou seacutelecteur) Vous preacutesentez une donneacutee (expression) et Excel la compare aux diffeacuterentes options disponibles avec en plus une valeur par deacutefaut si aucune option disponible

=SIMULTIPLE(A11Lundi2MardiAutre jour)

Cela permet drsquoeacutevaluer la cellule A1 si A1 vaut 1 alors on renvoie laquo Lundi raquo si A1 vaut 2 alors on renvoie laquo Mardi raquo et sinon laquo Autre jour raquo



8

Ndeg2 La SOMME() crsquoest bien SOMMESI() crsquoest mieux SOMMESIENS() afin drsquoecirctre au top

Autre fonction essentielle SOMME()Elle permet comme son nom lrsquoindique drsquoadditionner toutes les reacutefeacuterences passeacutees en paramegravetre

Voyons de plus pregraves

Cette fonction peut prendre jusqursquoagrave 255 arguments Lorsqursquoon eacutecrit la fonction SOMME(Nombre1Nombre2hellip) il peut y avoir 255 eacuteleacutements seacutepareacutes par des laquo raquo Chaque argument peut en revanche ecirctre une plage de cellule ou encore une formule



9

Cela donne par exemple

Et sihellip et si on ne souhaite additionner qursquoagrave certaines conditions SOMMESI() est faite pour vous

Cette fonction peut ecirctre utiliseacutee de deux maniegraveres

1 Vous ne souhaitez ajouter que les cellules respectant directement une condition Ex je nrsquoajoute que les cellules dont la valeur est supeacuterieure agrave 1000



10

2 La deuxiegraveme maniegravere drsquoutiliser cette fonction est drsquoappliquer la condition agrave une autre plage de valeurs Cette plage doit neacutecessairement avoir le mecircme nombre drsquoeacuteleacutements

Cela donne le reacutesultat ci-dessous Les zones surligneacutees en bleues ont eacuteteacute ajouteacutees afin de mieux visualiser les cellules qui sont reacuteellement additionneacutees

Apregraves cette utilisation deux questions peuvent vous venir agrave lrsquoesprit la premiegravere laquo Est-ce que le critegravere drsquoaddition peut ecirctre variable raquo et laquo Peut-on disposer de plusieurs critegraveres raquo Vous avez de la chance la reacuteponse agrave ces deux questions est laquo OUI raquo Voyez ce qui suit



11

Prenons le tableau ci-dessous

Avec lrsquoaffichage deacutetailleacutee de la formule ce sera plus clair

La plage de critegraveres et le critegravere vont toujours de pair La plage_somme repreacutesente les valeurs qui seront additionneacutees si les critegraveres sont respecteacutes Dans lrsquoexemple il faut que le pays soit eacutegal agrave B14 ET le produit agrave B15

Cette fonction peut avoir jusqursquoagrave 127 paires plage_critegraverecritegraveres

Attention cette fonction ne sait faire que des laquo ET raquo avec les critegraveres Crsquoest-agrave-dire que seuls les eacuteleacutements qui respectent lrsquoensemble des critegraveres seront additionneacutes



12

Ndeg3 Il y a aussi NB() qui peut poser ses conditions NBSI() et NBSIENS()

NB() est une fonction extrecircmement utile dans de tregraves nombreuses situations

En utilisation directe elle sert agrave compter le nombre de cellules contenant des nombres Agrave ne pas confondre avec NBVAL() qui deacutenombre le nombre de cellules non vides

NB(hellip) = nombre de cellules avec des nombres

NBVAL(hellip) = nombre de cellules qui ne sont pas vides

Drsquoune maniegravere assez proche de la fonction SOMMESI() NBSI() permet de ne compter que les eacuteleacutements qui respectent un critegravere

Exemple

Afin de gagner en souplesse je vous preacutesente ici une autre astuce lrsquoutilisation du symbole laquo amp raquo

Cela permet de combiner le contenu de deux cellules Ainsi je rends le critegravere de deacutenombrement variable La formule va donc compter le nombre drsquoeacuteleacutements qui sont infeacuterieurs agrave 1000

Dans la mecircme ligneacutee que SOMMESIENS() NBSIENS() permet de deacutenombrer les lignes qui respectent plusieurs critegraveres



13

Ici aussi Excel deacutecomptera uniquement les lignes qui respectent TOUS les critegraveres qui sont deacutefinis dans les paramegravetres

Jrsquoai mis en bleu la ligne qui respecte les deux critegraveres

Ndeg4 Avant il y avait la RECHERCHEV() RECHERCHEH() maintenant il y a RECHERCHEX()

La recherche verticale est une fonction assez mythique Nombre drsquoutilisateurs qui deacutebutent bloquent sur cette fonction Probablement car personne nrsquoen a expliqueacute le fonctionnement

Imaginez la situation suivante

Vous disposez drsquoune liste de produits avec leurs caracteacuteristiques dans une feuille et dans une autre feuille vous aimeriez reporter lrsquoune drsquoelles dans un second tableau



14

Pour lrsquoexemple je vais placer ces deux tableaux sur la mecircme feuille pour simplifier la lecture

Lrsquoobjectif ici est de reporter le nom de la reacutefeacuterence depuis la liste des produits vers le tableau des stocks

Crsquoest exactement le rocircle de la fonction RECHERCHEV() Pourquoi verticale Crsquoest parce qursquoExcel va lire iteacuterativement les lignes du tableau du haut vers le bas darr drsquoougrave le nom En bonus lorsque votre valeur a eacuteteacute trouveacutee vous pouvez deacuteterminer le ndeg de la colonne que vous souhaitez reporter (paramegravetre no_index_col)

Si vos donneacutees sont inscrites en colonnes alors votre recherche devra se faire de gauche agrave droite rarr Vous utiliserez RECHERCHEH() la recherche horizontale Tout simplement

Voyons tout de mecircme en deacutetails les paramegravetres de cette fonction

Le premier paramegravetre vous demande quelle est la valeur rechercheacutee Ici crsquoest la cellule contenant la reacutefeacuterence produit

Ensuite il faut renseigner la table_matrice crsquoest-agrave-dire la table dans laquelle la donneacutee rechercheacutee est censeacutee se trouver

ATTENTION La reacutefeacuterence rechercheacutee DOIT se trouver dans la premiegravere colonne de la matrice que vous indiquez



15

Si votre tableau ressemble agrave ceci

Vous DEVREZ reacutefeacuterencer le tableau agrave partir de la colonne B Ceci a une conseacutequence tregraves importante sur le paramegravetre suivant No_index_col ce dernier est forceacutement une valeur positive Il repreacutesente le numeacutero de la colonne ougrave se trouve la donneacutee agrave reacutecupeacuterer

Dans la table_matrice B3D5 si vous voulez le laquo Nom raquo ce sera la 2e colonne

Vous ne pourrez pas (avec cette fonction) retrouver le nom du fournisseur

Puis il faut bien veiller agrave FIXER les reacutefeacuterences de sa table_matrice Si vous copiez la formule dans la cellule du dessous Excel va eacutegalement deacuteplacer la table_matrice de recherche

Soit vous utilisez les TABLEAUX soit vous fixez les reacutefeacuterences en utilisant le caractegravere $

Enfin le dernier paramegravetre permet de faire des recherches approximatives Entre nous je le place toujours agrave FAUX afin drsquoavoir une correspondance exacte



16

Maintenant que vous maicirctrisez la fonction RECHERCHEV() et RECHERCHEH() voici venir RECHERCHEX()

Il y a tant de paramegravetres que jrsquoai fait un montage de deux captures drsquoeacutecran

Il faut noter que les tableaux de recherche renvoyeacutes sont soit mono-colonne soit mono-ligne (n lignes et 1 colonne) ou (1 ligne et n colonnes)

RECHERCHEX() ouvre tant de nouvelles possibiliteacutes Je vais vous en donner quelques-unes

1 Le tableau renvoyeacute peut se trouver nrsquoimporte ougrave (colonne avant ou apregraves la colonne de recherche mais aussi sur une autre feuille ) Il nrsquoest pas obligeacute de correspondre au tableau de recherche

2 On peut renseigner une valeur agrave renvoyer srsquoil nrsquoy a pas de correspondance (fixe ou calculeacutee) RECHERCHEV() RECHECHEH() et RECHERCHE() renvoie NA si aucun reacutesultat nrsquoest trouveacute

3 Deux compleacutements majeurs sur le mode de recherche a Le mode de correspondance

Celui-ci affine la maniegravere de trouver la meilleure correspondance (dont un mode laquo expression reacuteguliegravere raquo cela permet de rechercher laquo U22 raquo ougrave le point signifie nrsquoimporte quel caractegravere)

b Le mode de recherche Il offre la possibiliteacute drsquoimposer un tri croissant ou deacutecroissant

Ces possibiliteacutes vont rapidement faire oublier la fonctionnaliteacute RECHERCHEV() RECHERCHEH() ou RECHERCHE() Si vous devez en apprendre une crsquoest celle-ci RECHERCHEX()



17

Ndeg5 Plus fort que RECHERCHEV() le couple INDEX() et EQUIV()

Sur la fonction preacuteceacutedente je vous ai montreacute une des limitations de la fonction RECHERCHEV() qui est drsquoimposer que la colonne de recherche soit la premiegravere drsquoun tableau et que les reacutesultats soient agrave droite de celle-ci

INDEX() et EQUIV() sont comme une carte et une boussole Agrave elles deux elles vous permettent de trouver ce que vous recherchez

Illustrons ceci avec un exemple

Si nous deacutecomposons notre raisonnement ainsi

1 Nous recherchons le nom de la reacutefeacuterence laquo U1318 raquo Dans notre cas il srsquoagit de laquo Tabouret raquo

2 Pour cela je dois trouver U1318 dans la colonne reacutefeacuterence Il faudra raisonner ainsi de combien de lignes dois-je descendre depuis laquo Reacutefeacuterence raquo pour trouver laquo U1318 raquo

3 Je pourrais alors descendre drsquoautant dans la colonne laquo Nom raquo et jrsquoaurai le bon reacutesultat

INDEX() renvoie lrsquoeacuteleacutement demandeacute drsquoune plage (On veut renvoyer laquo Tabouret raquo mais on ne peut pas deviner ougrave il se trouve) Crsquoest dans cette plage que se trouve le reacutesultat

Pour cela EQUIV() permet de dire quelle est la position drsquoun eacuteleacutement dans une plage (cela deacutetermine de combien il faut descendre)

En combinant les deux cela donne

=INDEX($C$3$C$6EQUIV(F6 $B$3$B$6))

Je lis cette fonction ainsi

INDEX(laquo La valeur que je recherche se trouve ici raquo laquo Deacuteplace-toi de tant de lignescolonnes pour la trouver raquo)

EQUIV(laquo A quel rang se trouve cette valeur raquo laquo Dans cette seacuterie de valeurs raquo)

Les $ sont importants lorsque vous allez eacutetendre cette formule En effet vos plages de recherche ou de reacutesultats ne changeront pas la seule qui change crsquoest la valeur rechercheacutee (en vert dans la formule)

Un attribut suppleacutementaire peut ecirctre ajouteacute agrave INDEX() crsquoest le nombre de colonnes dont il faut se deacutecaler vers la droite avant de renvoyer une valeur Il faut eacutetendre la zone de recherche (en bleu dans la formule) et indiquer le rang de la colonne

=INDEX($A$3$C$6EQUIV(F6 $B$3$B$6)1)



18

Ndeg6 Le summum avec EQUIVX()

Cette fonction reacutecente drsquoExcel vient apporter encore plus de souplesse pour trouver un eacuteleacutement dans une liste

Drsquoune maniegravere similaire agrave RECHERCHEX() vue preacuteceacutedemment EQUIVX() vient ajouter les deux mecircme attributs

1 Mode_correspondance 2 Mode_recherche

Ces deux attributs ajoutent de la flexibiliteacute pour identifier une valeur la possibiliteacute drsquoutiliser les expressions reacuteguliegraveres ( ~) pour trouver une correspondance pour le premier et lrsquoordre de recherche pour le deuxiegraveme

Il devient alors possible de faire ce type de recherche

Vous noterez que la recherche se fait sur laquo T raquo dans le premier cas dans le sens de recherche laquo premier au dernier raquo (dernier attribut = 1) et dans le second cas dans le sens de recherche laquo dernier au premier raquo (dernier attribut = -1)



19

Ndeg7 Les fonctions essentielles sur les textes

Nos fichiers Excel regorgent de donneacutees numeacuteriques mais eacutegalement de donneacutees textuelles Excel possegravede quelques fonctions bien utiles agrave connaicirctre pour les manipuler

GAUCHE() DROITE() CHERCHE() TROUVE() REMPLACER() SUBSTITUER() MINUSCULE() MAJUSCULE() SUPRESPACE() et CONCATENER()

Ce sont les fonctions que vous utiliserez le plus souvent Il en existe bien drsquoautres pour manipuler des textes

Pour les deux fonctions GAUCHE() et DROITE() jrsquoavais souvent un doute sur son fonctionnement Par exemple la fonction GAUCHE() permet-elle de retirer les caractegraveres qui sont agrave gauche ou bien est-ce pour conserver ceux qui sont agrave gauche Maintenant jrsquoajoute mentalement laquo A partir de la raquo Et depuis plus de doutes GAUCHE() permet de CONSERVER le nombre de caractegraveres demandeacutes depuis la gauche du mot

Sur cette illustration voici quelques particulariteacutes que jrsquoaimerais vous faire remarquer

La fonction CHERCHE() nrsquoest pas sensible agrave la casse En recherchant une lettre majuscule ou minuscule elle retournera la position du texte rechercheacute

En revanche TROUVE() est sensible agrave la casse Pour se rappeler de cette nuance pensez agrave la nuance drsquoappellation des fonctions TROUVE() est presque laquo un ordre raquo Excel doit respecter la demande CHERCHE() a une intention un peu plus faible elle donc plus toleacuterante

Pour les deux fonctions REMPLACER() et SUBSTITUER() se rappeler de la diffeacuterence de fonctionnement nrsquoest pas plus eacutevidente Crsquoest leur liste de paramegravetres qui nous aident agrave les comprendre

REMPLACER() a une approche tregraves meacutecanique rendez-vous agrave telle position pendant tant de caractegraveres puis eacutechanger ce qursquoil y a par tel caractegravere

SUBSTITUER() permet de faire un eacutechange de mots Excel va rechercher une chaicircne de caractegraveres et lrsquoeacutechanger avec une autre chaicircne



20

Personnellement agrave chaque fois que je veux remplacer un texte je pense agrave REMPLACER() alors que crsquoest SUBSTITUER() dont jrsquoai besoin Que voulez-vous

Personne nrsquoest parfaithellip

SUPPRESPACE() est extrecircmement utile pour nettoyer des donneacutees Elle permet drsquoeacuteliminer les caractegraveres espaces qui se trouvent au deacutebut et agrave la fin drsquoune chaicircne de caractegraveres

Lorsqursquoon utilise soit lrsquoimport de donneacutees depuis des systegravemes externes soit certains copier-coller depuis drsquoautres documents des caractegraveres espaces se glissent justement au deacutebut ou agrave la fin Cette fonction vous en deacutebarrassera

Enfin une derniegravere fonction extrecircmement utile pour mettre en forme ses donneacutees la fonction CONCATENER()

Vous pouvez mettre tant des cellules (valeurs dynamiques) que des valeurs statiques pour les concateacutener

Vous noterez lrsquoutilisation de la fonction TEXTE() qui permet drsquoassurer une mise en forme des donneacutees selon vos souhaits

Ndeg8 Les erreurs cela arrivehellip Mais il faut savoir les geacuterer

Excel peut retourner une erreur dans de nombreuses situations Heureusement toutes les erreurs ne renvoient pas le mecircme message Voici la liste des messages drsquoerreurs possibles

Code drsquoerreur Situation ougrave elle se produit

VALEUR Il y a un problegraveme dans la faccedilon dont vous avez entreacute votre formule Ou il y a un problegraveme avec les cellules auxquelles vous faites reacutefeacuterence

NOMBRE La valeur du reacutesultat nrsquoest pas comprise entre -10^307 et 10^307



21


NA En geacuteneacuteral lorsque vous avez une formule qui doit trouver une cellule pour en retourner la valeur et que cette cellule nrsquoest pas trouveacutee (RECHERCHEV() par exemple)

REF Lorsque vous faites reacutefeacuterence agrave une cellule qui nrsquoexiste pas ou plus

NOM Cas typique lorsque vous faites reacutefeacuterence agrave un nom de formule qui nrsquoexiste pas ougrave agrave une cellule qui nrsquoexiste pas Ex =ZZZ1

Crsquoest une erreur sans en ecirctre une Excel vous indique simplement qursquoil nrsquoa pas la capaciteacute drsquoafficher la totaliteacute de la valeur de la cellule Vous devez alors soit ajuster le zoom soit agrandir la largeur de la cellule

DIV0 Crsquoest lrsquoune des erreurs les plus explicites Votre formule aboutit agrave une division par 0

NUL Une erreur de frappe srsquoest glisseacutee dans la reacutefeacuterence drsquoune plage de cellule Par exemple =NB(A1 A9) au lieu de =NB(A1A9)

Parfois on ne peut eacuteviter de rencontrer ces erreurs Par exemple une RECHERCHEV() qui ne trouve pas de reacutesultat cela arrive Il existe donc des fonctions pour prendre en charge ces erreurs

ESTERR() et ESTERREUR() sont les deux premiegraveres et contrairement aux apparences elles ne font pas exactement la mecircme chosehellip

Microsoft nous lrsquoindique bien dans lrsquoaide contextuelle

Cela signifie qursquoen cas de NA ESTERR() retournera FAUX Cette fonction considegraverera qursquoil nrsquoy a pas drsquoerreur

La deuxiegraveme ESTERREUR() retournera VRAI pour nrsquoimporte quelle erreur



22

Et comme lrsquoerreur NA a droit agrave un traitement de faveur il existe eacutegalement deux fonctions speacutecifiques pour cette erreur

ESTNA() permet de tester lrsquoapparition de cette erreur Et on peut mecircme vouloir retourner lrsquoerreur volontairement chose qui sera faite avec NA()

Il reste encore la fonction SIERREUR() qui combine directement lrsquoutilisation de la fonction SI() et de lrsquoeacutevaluation ESTERREUR()

=SIERREUR(mon_calculErreur de calcul)

Lorsque vous pouvez preacutedire les situations ougrave lrsquoerreur peut survenir il est preacutefeacuterable drsquoutiliser la fonction SI() pour des raisons de performances

Cela eacutevite agrave Excel une eacutevaluation parfois complexe qui aboutira agrave une erreur Dans des tableaux ougrave lrsquoeacutevaluation doit ecirctre faite pour chaque ligne cela ralentirait les mises agrave jour des reacutesultats

En rouge voyez lrsquoutilisation de SIERREUR() et en bleu avec la fonction SI() Dans le 2e cas Excel traitera plus rapidement que dans le premier



23

Ndeg9 Les tableaux dynamiques (agrave ne pas confondre avec les tableaux croiseacutes dynamiques )

Depuis peu vous pouvez retourner sans aucune manipulation particuliegravere un tableau de reacutesultats dans une cellule Auparavant il fallait indiquer agrave Excel que vous utilisiez une fonction matricielle par la combinaison de touches laquo Ctrl+Shift+Entreacutee raquo communeacutement connue sous laquo CSE raquo

Or si quelqursquoun modifiait votre formule et pressait laquo Entreacutee raquo sans laquo CSE raquo votre formule tombait agrave lrsquoeau

Deacutesormais vous pouvez vous placer en B1 et saisir =A1A10

Comme vous pouvez le voir ci-contre en B2 la formule devient griseacutee dans la barre de formule et une ombre est visible sur la feuille

Cela indique que toutes ces cellules font parties drsquoune table dynamique

Dans lrsquoexemple cette table a une taille fixe mais comme vous le verrez elle peut tout agrave fait ecirctre de longueur variable dynamique quoi

Si vous souhaitez revoir le fonctionnement des tableaux croiseacutes dynamiques (TCD) vous pouvez consulter les deux articles suivants

- Le tableau croiseacute dynamique pour les nuls - Reacuteussir son premier tableau croiseacute dynamique

Il y a mecircme son compatriote le graphique croiseacute dynamique

Ndeg10 Ecirctes-vous UNIQUE()

Depuis les derniegraveres versions drsquoExcel 365 une seacuterie de fonctions a vu le jour UNIQUE() en fait partie

Cette fonction matricielle renvoie un tableau dynamique (cf point preacuteceacutedent) Elle permet de reacutealiser la mecircme opeacuteration que laquo Supprimer les doublons raquo en un peu mieux



24

En mieux car il est possible de lrsquoutiliser dans une fonctionhellip Il sera donc possible de seacutelectionner dynamiquement uniquement les valeurs preacutesentes une seule fois

De plus la fonction UNIQUE() possegravede trois paramegravetres

1 Le premier paramegravetre est la plage qui sera la source de donneacutees 2 Le second permet de demander lrsquouniciteacute par colonne (et pas uniquement par

ligne comme crsquoest le cas de la fonction laquo Supprimer les doublons raquo) 3 Le dernier paramegravetre permet de ne conserver que les lignes ou colonnes

nrsquoapparaissant qursquoune seule fois Il eacutelimine les doubles (les deux colonnes disparaissent) et pas uniquement les doublons (seule une des deux colonnes disparaicirct)

Une illustration srsquoimpose pour bien comprendre

Voici une liste de notes drsquoeacutelegraveves

Vous pouvez remarquer que les eacutelegraveves laquo Eacutelegraveve 1 raquo et laquo Eacutelegraveve 3 raquo ont les mecircmes notes

Deux types drsquoopeacuterations possibles avec la fonction UNIQUE()

1 On veut trouver toutes les possibiliteacutes de notes pour un eacutelegraveve ougrave chaque jeu de notes nrsquoest preacutesent qursquoune seule fois (on retirerait les notes de lrsquoeacutelegraveve 3)

2 On veut trouver toutes les possibiliteacutes de notes uniques Crsquoest-agrave-dire que lorsqursquoun ensemble de notes est preacutesent deux fois on les retire tous les deux

En B9 la formule utiliseacutee est

=UNIQUE(B2F6VRAIFAUX)

1 Le premier paramegravetre indique la matrice source



25

2 Le deuxiegraveme indique que la recherche drsquouniciteacute va se faire par colonne (et non par ligne ndash valeur par deacutefaut)

3 Enfin le dernier indique que nous souhaitons conserver les doublons

Dans le deuxiegraveme cas drsquoexemple voici ce que cela donnerait

Cette fois le dernier paramegravetre de la fonction UNIQUE() est placeacute agrave VRAI Ainsi les doublons disparaissent du reacutesultat Les notes des eacutelegraveves 1 et 3 nrsquoapparaissent plus

NOTE Remarquez que seule la cellule B9 est seacutelectionneacutee et que le tableau reacutesultat (B9D13) apparaicirct avec une leacutegegravere ombre Celle-ci montre la preacutesence drsquoun tableau dynamique

ATTENTION VOUS ENTREZ DANS UNE ZONE laquo EXPERT raquo

Dans la capture ci-dessous je vous preacutesente le reacutesultat de la fonction UNIQUE() Avez-vous remarqueacute que les noms des eacutelegraveves ne sont pas concerneacutes par celle-ci

Il se trouve que jrsquoai utiliseacute une autre formule pour retrouver lrsquoeacutelegraveve concerneacute par les notes

Il srsquoagit ici de faire lrsquoeacutequivalent drsquoune RECHERCHEH() ou EQUIV() en mode multicritegraveres

Lrsquoobjectif eacutetant de trouver le nom de lrsquoeacutelegraveve qui possegravede exactement lrsquoensemble des notes du tableau



26

Pour ce faire jrsquoai utiliseacute des opeacuterations matricielles en particulier la partie en gras qui est au cœur de cette fonction Dans Excel 365 vous pouvez saisir cette partie de la formule dans une simple cellule

=INDEX(TNotes[[En-tecirctes][Eacutelegraveve 1][Eacutelegraveve 5]]

EQUIV(

B9ampB10ampB11ampB12ampB13

$B$2$F$2amp$B$3$F$3amp$B$4$F$4amp$B$5$F$5amp$B$6$F$6

0)

)

Cette partie de la fonction utilise une speacutecificiteacute de la fonction EQUIV() qui peut rechercher la correspondance entre deux reacutesultats dans le mecircme appel Il faut utiliser pour cela le caractegravere amp

Ainsi si nous prenons lrsquoexemple des deux premiegraveres notes nous souhaitons connaicirctre dans quelle colonne la premiegravere note (B9) et la deuxiegraveme note (B10) sont eacutegales aux valeurs preacutesentes dans la premiegravere ligne (B2F2) et la deuxiegraveme ligne (B3C3)

Lorsque lrsquoon combine toutes les notes cela donne une formule longue mais compreacutehensible lorsqursquoon comprend la logique

Enfin EQUIV() donnant la position de retour il faut trouver le nom de lrsquoeacutelegraveve gracircce agrave la fonction INDEX()



27

Ndeg11 FILTRE() nrsquoa jamais eacuteteacute aussi facile

FILTRE() fait partie des fonctions matricielles ajouteacutees reacutecemment agrave Excel 365 Elle permet drsquoavoir agrave travers une fonction lrsquoeacutequivalent du filtrage drsquoun tableau

Dans cet exemple vous pouvez voir lrsquoutilisation de la fonction FILTRE() qui retourne eacutegalement un tableau dynamique Ici je souhaite reacutecupeacuterer la liste des notes pour une seule matiegravere je peux non seulement le faire avec une simple ligne mais je peux rendre ce filtre parameacutetrable Crsquoest-agrave-dire baseacutee sur la valeur drsquoune autre cellule ici C9

Ndeg12 Remplissage instantaneacute

Cette fonction est tregraves utile lorsqursquoon extrait des informations drsquoune liste de texte dans Excel

Exemple vous disposez drsquoune liste drsquoadresses email et vous souhaitez reacutecupeacuterer uniquement la deuxiegraveme partie des emails soit le nom de domaine (yahoocom gmailcom hellip)



28

Parfois Excel deacuteclenche cette fonctionnaliteacute toute seule et vous voyez toute une liste se preacuteremplir

Sachez que vous pouvez vous-mecircme appeler cette fonction au moment ougrave vous le souhaitez Il suffit drsquoaller ici

Ndeg13 EN BONUS Ne plus avoir agrave utiliser laquo lrsquoadresse raquo drsquoune cellule dans les formules

Lrsquoune des premiegraveres choses qursquoon apprend lorsqursquoon utilise Excel crsquoest la diffeacuterence entre A1 A$1 A1$ et A$1$ Ensuite on apprend qursquoExcel est capable de mettre agrave jour automatiquement une formule lorsqursquoon la laquo tire raquo vers une autre cellule

Et plus le temps passe plus le nombre de reacutefeacuterences augmente et on nrsquoest plus toujours certain que nos formules reacutefeacuterencent les bonnes celluleshellip

Crsquoest pourquoi cette superbe fonctionnaliteacute va vous sauver



29

Lorsqursquoon fait des calculs de ce type

Dont voici les formules

On nrsquoa pas envie de se tromper

Lrsquoastuce consiste agrave creacuteer ce qursquoon appelle laquo un nom raquo

Etape Ndeg1 Se positionner sur la cellule qui sera reacutefeacuterenceacutee

Etape Ndeg2 Saisir le nom de reacutefeacuterence agrave cet endroit

Dans mon exemple lagrave ougrave il y avait eacutecrit laquo C2 raquo je saisis directement le nom que je souhaite donner agrave cette cellule



30

Etape Ndeg3 Ensuite il ne reste qursquoagrave utiliser cette reacutefeacuterence dans les formules

On voit alors de nombreux avantages

- Plus de doute quant agrave la reacutefeacuterence donneacutee dans les formules - Plus de doute quant agrave la position du symbole $ il nrsquoy en plus besoin - Toutes les cellules nommeacutees sont preacutesentes dans lrsquoauto-compleacutetion lors de la saisie

des formules - Cette auto-compleacutetion est identique dans lrsquoensemble du classeur Plus besoin de

faire des reacutefeacuterences du type laquo Feuil1C2 raquo

Ndeg14 EN BONUS Utiliser les segments

Degraves qursquoon utilise Excel pour analyser des donneacutees on se met agrave utiliser les fameux Tableaux Croiseacutes Dynamiques (TCD) Qui dit TCD dit alors utilisation des filtres

Il arrive qursquoil soit neacutecessaire de combiner plusieurs filtres puis de changer la valeur drsquoun des filtres etc

Les segments sont lagrave pour vous simplifier la vie

Etape Ndeg1 Disposer drsquoun TCD (ou en creacuteer un nouveau)

Suivez les eacutetapes deacutecrites sur comment reacuteussir ses premiers pas avec les TCD



31

Etape Ndeg2 Inseacuterer un ou plusieurs segments

Cliquez agrave lrsquointeacuterieur du TCD afin de faire apparaicirctre les menus associeacutes dans le ruban

Etape Ndeg3 Seacutelectionner le ou les champs agrave inseacuterer en tant que segment

Par exemple je vous propose de seacutelectionner laquo NOMDEP raquo

Etape Ndeg4 Utiliser les segments pour analyser les reacutesultats

On peut seacutelectionner une ou plusieurs valeurs (en conservant la touche Ctrl presseacutee)

Le TCD se met alors agrave jour selon les diffeacuterentes seacutelections



32

Ndeg15 EN BONUS Utiliser la chronologie

De la mecircme maniegravere que preacuteceacutedemment il existe un segment drsquoun type bien particulier la chronologie

Crsquoest extrecircmement utile pour analyser les TCD disposant drsquoune donneacutee temporelle Ce composant va permettre de filtrer plus facilement les donneacutees selon des peacuteriodes de temps



Etape Ndeg2 Inseacuterer une chronologie

Cliquez agrave lrsquointeacuterieur du TCD pour faire apparaicirctre les menus associeacutes dans le ruban apregraves avoir nettoyeacute les donneacutees de la colonne laquo DATE APPELLATION raquo et fait en sorte que la colonne soit bien reconnue en tant que laquo Date raquo

Etape Ndeg3 Seacutelectionner le ou les champs agrave inseacuterer en tant que chronologie

A cet instant seules les donneacutees sources de type date sont exposeacutees Dans le cas preacutesent une seule colonne est montreacutee (gracircce au nettoyage reacutealiseacute preacutealablement)



33

Etape Ndeg4 Utiliser la chronologie pour analyser les reacutesultats

Vous pouvez alors filtrer les peacuteriodes de votre choix

Par ailleurs notez qursquoil est possible de changer la granulariteacute de la seacutelection des peacuteriodes de temps en cliquant sur le menu en haut agrave droite



34

COPYRIGHT

Ce livre PDF a eacuteteacute transmis gratuitement agrave la suite de votre inscription agrave la newsletter du blog httpsexceller-avec-la-bureautiquecom Le simple fait de lire le preacutesent livre PDF vous donne le droit de le partager agrave qui vous le souhaitez

Si vous avez reccedilu ce guide autrement qursquoen vous inscrivant agrave la newsletter de laquo Exceller avec la bureautique raquo je vous propose de vous y inscrire crsquoest totalement gratuit Vous recevrez reacuteguliegraverement des astuces des reacuteflexions et des propositions pour vous aider agrave maicirctriser la suite Office de Microsoft

Je vous autorise agrave lrsquoutiliser commercialement selon les mecircmes conditions que sur mon blog cest agrave dire agrave lrsquooffrir sur votre blog sur votre site web agrave lrsquointeacutegrer dans des packages et agrave lrsquooffrir en bonus avec des produits mais PAS agrave le vendre directement ni agrave lrsquointeacutegrer agrave des offres punies par la loi dans votre pays

Vous ecirctes donc libre de le distribuer agrave qui vous le souhaitez agrave condition de ne pas le modifier de toujours citer Charles du site laquo Exceller avec la bureautique raquo comme lrsquoauteur de ce guide de preacutevenir lrsquoauteur gracircce agrave la page laquo contact raquo du blog et drsquoinclure un lien vers httpsexceller-avec-la-bureautiquecom

Le livre PDF laquo Les 12 fonctions Excel agrave maicirctriser absolument raquo par Charles du blog httpsexceller-avec-la-bureautiquecom est mis agrave disposition selon les termes de la licence laquo Attribution - Pas drsquoUtilisation Commerciale - Pas de Modification raquo

Les autorisations au-delagrave du champ de cette licence peuvent ecirctre obtenues aupregraves de charlesexceller-avec-la-bureautiquecom



2

TABLE DES MATIEgraveRES

Merci 3

Excel 4

Ndeg1 Donner le LA avec les SI() SICONDITIONS() et SIMULTIPLE() 4

Ndeg2 La SOMME() crsquoest bien SOMMESI() crsquoest mieux SOMMESIENS() afin drsquoecirctre au top 8

Ndeg3 Il y a aussi NB() qui peut poser ses conditions NBSI() et NBSIENS() 12

Ndeg4 Avant il y avait la RECHERCHEV() RECHERCHEH() maintenant il y a RECHERCHEX() 13

Ndeg5 Plus fort que RECHERCHEV() le couple INDEX() et EQUIV() 17

Ndeg6 Le summum avec EQUIVX() 18

Ndeg7 Les fonctions essentielles sur les textes 19

Ndeg8 Les erreurs cela arrivehellip Mais il faut savoir les geacuterer 20

Ndeg9 Les tableaux dynamiques (agrave ne pas confondre avec les tableaux croiseacutes dynamiques ) 23

Ndeg10 Ecirctes-vous UNIQUE() 23

Ndeg11 FILTRER() nrsquoa jamais eacuteteacute aussi facile 27

Ndeg12 Remplissage instantaneacute 27

Ndeg13 EN BONUS Ne plus avoir agrave utiliser laquo lrsquoadresse raquo drsquoune cellule dans les formules 28

Ndeg14 EN BONUS Utiliser les segments 30

Ndeg15 EN BONUS Utiliser la chronologie 32

Copyright 34



3

MERCI














4

EXCEL








5







6

=SI([Moyenne]lt12

Pas de mention

SI([Moyenne]lt14

Assez bien

SI([Moyenne]lt16

Mention bien

SI([Moyenne]lt18



)

)

)

)







valeur_si_vrai

valeur_si_faux



7



=SICONDITIONS(














8







9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









3

MERCI














4

EXCEL








5







6

=SI([Moyenne]lt12

Pas de mention

SI([Moyenne]lt14

Assez bien

SI([Moyenne]lt16

Mention bien

SI([Moyenne]lt18



)

)

)

)







valeur_si_vrai

valeur_si_faux



7



=SICONDITIONS(














8







9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









4

EXCEL








5







6

=SI([Moyenne]lt12

Pas de mention

SI([Moyenne]lt14

Assez bien

SI([Moyenne]lt16

Mention bien

SI([Moyenne]lt18



)

)

)

)







valeur_si_vrai

valeur_si_faux



7



=SICONDITIONS(














8







9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









5







6

=SI([Moyenne]lt12

Pas de mention

SI([Moyenne]lt14

Assez bien

SI([Moyenne]lt16

Mention bien

SI([Moyenne]lt18



)

)

)

)







valeur_si_vrai

valeur_si_faux



7



=SICONDITIONS(














8







9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









6

=SI([Moyenne]lt12

Pas de mention

SI([Moyenne]lt14

Assez bien

SI([Moyenne]lt16

Mention bien

SI([Moyenne]lt18



)

)

)

)







valeur_si_vrai

valeur_si_faux



7



=SICONDITIONS(














8







9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









7



=SICONDITIONS(














8







9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









8







9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









9







10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









10






11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









11








12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









12







Exemple






13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









13









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









14











15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









15










16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









16













17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









17





















18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









18










19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









19














20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









20















21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









21















22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









22










23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









23
















24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









24

















25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









25













26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









26



EQUIV(



0)

)







27









28









29










30














31










32












33






34

COPYRIGHT









27









28









29










30














31










32












33






34

COPYRIGHT









28









29










30














31










32












33






34

COPYRIGHT









29










30














31










32












33






34

COPYRIGHT









30














31










32












33






34

COPYRIGHT









31










32












33






34

COPYRIGHT









32












33






34

COPYRIGHT









33






34

COPYRIGHT









34

COPYRIGHT







Documents

EXCEL A MAITRISER ABSOLUMENT€¦ · Excel peut retourner une erreur dans de nombreuses situations. Heureusement, toutes les erreurs ne renvoient pas le même message. Voici la liste