Web viewEXCEL 2010 . les formules De P BOULANGER. octobre 2012 Volume 2/2. Niveau 2, FORMULES & FONCTIONS TABLE DES MATIÈRES. LES FORMULES D’EXCEL 2010. 4

FAF Access-formation

EXCEL 2010 EXCEL 2010 les formulesles formulesDe P BOULANGER

octobre 2012

Volume 2/2

Niveau 2, FORMULES & FONCTIONS

Page 1 sur 54


TABLE DES MATIÈRES

LES FORMULES D’EXCEL 2010 4

Introduction 4

Les opérateurs arithmétiques 4

Les opérateurs et leur priorité 6

Les parenthèses 7

LES FONCTIONS 8

Introduction 8

Liste de fonctions de base 10

Quelques exemples 10

L’imbrication 11

DÉFINIR UN NOM 12

Les références de cellules 15

Les références relatives 15

Introduction 15

Le « copier-coller » de formules 15

L’évolution de la feuille de calculs 17

Les références absolues 18

Introduction 18

Passons en références absolues 20

L’EMBRANCHEMENT CONDITIONNEL IF 22

Introduction 22

La comparaison sur une chaîne de caractères 23

Les opérateurs de comparaison 24

Des exemples ? 25

Les fonctions 26

La fonction « ET » 26

La fonction « OU » 27

La fonction « ESTVIDE » 28

Les fonctions « ESTTEXTE » et « ESTNONTEXTE » 28

La fonction « ESTNUM » 28

LA FONCTION ARRONDI 29

Les fonctions ARRONDI.INF et ARRONDI.SUP 32

Page 2 sur 54


LA FONCTION LIEN_HYPERTEXTE 33

LA FONCTION AUJOURDHUI 38

LES FONCTIONS DE TRAITEMENTS DE CHAÎNES DE CARACTÈRES 39

CONCATENER 39

CAR 40

CODE 41

EXACT 42

GAUCHE et DROITE 43

NBCAR 44

MINUSCULE et MAJUSCULE 44

STXT 45

NOMPROPRE 46

REMPLACER 46

REPT 47

CHERCHE 48

SUPPRESPACE 49

Les fonctions CNUM et TEXTE 49

Substitue 50

LES FORMULES MATRICIELLES 51

Page 3 sur 54


LES FORMULES D’EXCEL 2010

Introduction

Une formule est une expression qui réalise un traitement afin d’afficher un résultat ;

Le résultat est toujours affiché dans la cellule contenant la formule ;

Une formule débute toujours par le signe « = » et doit être écrite avec une syntaxe rigoureuse.

Les opérateurs arithmétiques

Les opérateurs mathématiques de base sont :

« + » à pour l’addition, il en résulte une « somme » ;

« -« à Pour la soustraction, il en résulte une « différence » ;

« * » à Pour la multiplication, il en résulte un « produit » ;

« / » à Pour la division, il en résulte un « quotient ».

Par exemple, pour additionner A1 et A2 dans A3 :

EXEMPLE A

1 21,75

2 9,25

3 =A1+A2

Page 4 sur 54


Note :

La formule affichera le résultat uniquement après validation de la cellule ;

Dans l’exemple suivant, le résultat sera :

EXEMPLE A

1 21,75

2 9,25

3 31,00

Page 5 sur 54


Les opérateurs et leur priorité

Dans le cas d’une suite d’opérations mathématiques,

Les priorités d’exécution des expressions sont les suivantes :

1. Les multiplications et les divisions de gauche à droite ;

2. Les additions et les soustractions de gauche à droite.

EXEMPLE A

Donne 35 car la multiplication est

réalisée avant l’addition…

1 5,00

2 10,00

3 3,00

4 =A1+A2*A3

EXEMPLE A B

1 Montant HT 25,00 €

2 Montant TTC =B1+B1*5,6%

à Donne 26,40€, la TVA est calculée en priorité grâce à la multiplication, puis le produit obtenu est additionné au montant HT ;

à Si l’on n’avait pas utilisé le format « pourcentage » pour le taux de la TVA, on aurait dû écrire la formule de la façon suivante :

=B1+B1*5,6/100

Page 6 sur 54


Cependant, pour une suite d’opérations de même priorité, les calculs sont effectués de gauche à droite.

Remarque :

Pour modifier une formule, il vous faut double-cliquer sur la cellule la contenant…

Les parenthèses

Lorsque l’on souhaite imposer une priorité à une opération qui ne la possède pas naturellement, vous avez la possibilité d’employer les parenthèses. Avant même que les opérateurs mathématiques soient vérifiés, les expressions placées entre parenthèses sont prioritaires, et dans le cas où une expression placée entre parenthèses contient elle-même une ou plusieurs autres expressions placées entre parenthèses, la priorité est donnée à l’expression la plus imbriquée.

En reprenant l’exemple précédent, nous plaçons l’addition entre parenthèses. Cette fois-ci, le résultat est « 45 » et non plus « 35 », car l’addition est réalisée avant la multiplication !

EXEMPLE A

Donne 45 car l’addition est réalisée

avant la multiplication…

1 5,00

2 10,00

3 3,00

4 =(A1+A2)*A3

Page 7 sur 54


LES FONCTIONS

Introduction

Une fonction se place dans une formule, elle est un traitement qui réalise une opération « au sens général du terme » sur une ou plusieurs valeurs ;

Une valeur peut être située ailleurs dans une autre cellule, dans ce cas, les références de la cellule seront fournies à la fonction.

Par exemple, pour additionner A1 et B1 :

=SOMME(A1;B1)

EXEMPLE A B

1 70,00 35,00

2 =SOMME(A1;B1)

Pour séparer les différents paramètres de la fonction, vous devez utiliser le « ; » « point-virgule ».

Page 8 sur 54


Par exemple, pour additionner A1, B1, A2 et B2 :

=SOMME(A1;B1;A2;B2)

EXEMPLE A B C

1 70,00 30,00

2 50,00 50,00 =SOMME(A1;B1;A2;B2)

Dans cet exemple, la cellule est une formule composée de la fonction « SOMME » qui contient 4 paramètres, A1, B1, A2 et B2.

Toutefois, dans le cadre de l’implication de plusieurs cellules qui se suivent, on peut

employer le séparateur « : » pour spécifier la plage de cellules.

Par exemple, pour additionner de A1 jusqu’à J1, et de A10 jusqu’à J10 :

=SOMME(A1:J1;A10:J10)

Page 9 sur 54


Liste de fonctions de base

SOMME à Réalise une addition ;

PRODUIT à Réalise une multiplication ;

QUOTIENT à Réalise une division ;

MIN à Affiche la plus petite valeur ;

MAX à Affiche la plus grande valeur ;

MOYENNE à Calcule la moyenne.

Quelques exemples :

Pour additionner la plage de cellules de A1 à Z1 :

=SOMME(A1:Z1)

Pour calculer la moyenne de A1 à Z1 :

=MOYENNE(A1:Z1)

Pour multiplier la plus petite valeur à la plus grande valeur de la plage de cellules de A1 à Z1 :

=MIN(A1:Z1)*MAX(A1:Z1)

Pour calculer le double de la cellule A1 :

=PRODUIT(A1;2)

Pour rajouter 100 à la plage de cellule de A1 à Z1 :

=SOMME(A1:Z1;100)

Page 10 sur 54


L’imbrication

Il est tout à fait possible d’imbriquer une fonction en tant que paramètre à une autre fonction…

Par exemple, pour calculer le produit de la plus petite valeur à la plus grande valeur de la plage de cellules A1 à Z1 :

=PRODUIT(MIN(A1:Z1);MAX(A1:Z1))

Note :

L’ordre de la position des caractères dans une formule est dit « la syntaxe ».

Page 11 sur 54


DÉFINIR UN NOM

Dans une feuille de calculs, il est possible de définir un nom pour une cellule ou pour une plage de cellules.

Cette possibilité a pour objectif de substituer les références afin de d’appliquer des traitements sur plusieurs cellules.

Dans l’exemple suivant, nous allons créer un tableau d’achats dont la cellule «g1 « correspondant au taux de la TVA « 5,60% » sera définie avec le nom « TauxTVA ».

Sélectionnez la cellule G1 ;

Dans le ruban, dans l’onglet « Formule », cliquez sur le bouton « Définir un nom » situé dans le sous menu "Nom défini", groupe "Définir un nom" ;

Une boîte de dialogue apparaît à l’écran, saisissez le nom, en l’occurrence ici, TauxTVA, puis cliquez sur le bouton « Ok ». Désormais, dans les formules, nous pourrons faire référence à la cellule G1 depuis son nom ;

Maintenant, appliquez le format « Pourcentage » à cette cellule.

Page 12 sur 54


Voici le tableau avec les formules :

POISSON QUANTITÉ PRIX DU KILO MONTANT HT MONTANT TTC TAUX TVA : 5,60%

Soles 30 14,00 € =PRODUIT(B2;C2) =D2+D2*TauxTVA

Sardines 65 4,50 € =PRODUIT(B3;C3) =D3+D3*TauxTVA

Langoustines 28 14,75 € =PRODUIT(B4;C4) =D4+D4*TauxTVA

Avec les valeurs plutôt que les formules ?

POISSON QUANTITÉ PRIX DU KILO MONTANT HT MONTANT TTC TAUX TVA :

Soles 30 14,00 € 420,00 € 443,52 €

Sardines 65 4,50 € 292,50 € 308,88 €

Langoustines 28 14,75 € 413,00 € 436,13 €

Remarque :

D’ailleurs, si vous ouvrez la fenêtre « Atteindre » avec F5, vous pourrez directement saisir TauxTVA au lieu de G5.

Mieux encore, à droite de la zone d’édition dans laquelle vous saisissez les références de la cellule à atteindre, se trouve une zone de liste déroulante contenant tous les noms du classeur.

Vous avez la possibilité de définir un nom pour une plage de cellules…

Page 13 sur 54


Par exemple, si vous définissez le nom « ligne » pour la plage de cellules A1 E1, les deux formules suivantes donnent le même résultat :

EXEMPLE A

1 =SOMME(ligne)

2 =SOMME(A1:E1)

Page 14 sur 54


Les références de cellules

Les références relatives

Introduction

Lorsque vous écrivez simplement les références de cellules dans une formule, elles sont dites « relatives » :

=SOMME(A1=J4)

Cette manière de noter les références de cellules présentent les avantages suivants :

1. Lors d’un « copier-coller » de formules, les références sont automatiquement modifiées pour s’intégrer dans leur nouvel environnement ;;

2. Lors de l’évolution de la feuille, ajouts et suppressions de lignes et de colonnes, les formules alors déjà présentent se mettent automatiquement à jour en modifiant les références qu’elles contiennent afin de s’adapter aux changements de structures.

Le « copier-coller » de formules

Au préalable, observez attentivement ce tableau :

EXEMPLE A B C

1 29,75€ 130,30€=SOMME(A1 ;B1)

2 55,25€ 12,70€

Page 15 sur 54


Dans la cellule C1, les références de la fonction « SOMME » pointent vers les 2 cellules situées sur sa gauche.

Si vous réalisez un « copier-coller » de cette cellule vers C2, vous obtiendrez directement la formule suivante :

=SOMME(A2;B2)

En effet, compte tenu du fait que les références A1 et B1 sont notées en « relatif », l’implantation de la formule dans C2 depuis C1 par le biais d’un

« copier-coller » engendre une mise à jour des références afin qu’elles s’adaptent au nouveau contexte de la formule.

Donc,

=SOMME(A1;B1) devient naturellement =SOMME(A2;B2)

EXEMPLE A B C

1 29,75€ 130,30€=SOMME(A1 ;B1)

2 55,25€ 12,70€=SOMME(A2 ;B2)

Page 16 sur 54


L’évolution de la feuille de calculs

Maintenant, toujours dans le même petit tableau, imaginez que l’utilisateur insère une ligne vide entre les lignes 1 et 2 !

Il en résulte que la ligne 2 devient alors la ligne 3.

Du coup, la formule située dans la cellule C2 voit automatiquement les références qu’elle contient se mettre à jour pour s’adapter au nouveau contexte, et c’est ainsi que :

EXEMPLE A B C

1 29,75€ 130,30€=SOMME(A1;

B1)

2 =SOMME(A2;B2)

3 55,25€ 12,70€=SOMME(A3;

B3)

C’est la raison pour laquelle on dit que les références notées ainsi sont « relatives »…

Page 17 sur 54


Les références absolues

Introduction

La capacité d’adaptation des références relatives peut néanmoins engendrer une erreur ! En effet, certaines valeurs communes d’une feuille de calculs ne peuvent être écrites qu’une seule fois… Par conséquent, les références employées pour pointer sur cette cellule doivent impérativement être invariables.

Dans le tableau suivant, constatez que le taux de la TVA est situé dans la cellule B5 :

EXEMPLE A B C D E 3

1 LÉGUME

Prix du kilo

QUANTITÉ

H.T T.T.C

2 Avocats

2,40€

10 KG

=PRODUIT(B2;C2)

=D2+D2*B5

3

4

5 Taux TVA :

5,60%

Page 18 sur 54


La limite des références relatives :

Pour l’instant tout va bien, mais si l’on ajoutait un légume ?

(Et ce bien sûr, en réalisant un « copier-coller » des 2 formules !)

EXEMPLE A B C D E

1 LÉGUMEPrix du kilo

QUANTITÉH.T T.T.C

2 Avocats 2,40€ 10 KG =PRODUIT(B2;C2) =D2+D2*B5

3 Asperges 4,50€ 12 KG =PRODUIT(B3;C3)

=D3+D3*B6

4

5 Taux TVA :

5,60%

Que constatez-vous comme anomalie ?

Eh bien, les références qui résultent des 2 « copier-coller » se sont parfaitement adaptées en ce qui concerne les valeurs spécifiques au nouveau légume, cependant, concernant le taux de la TVA, il y a un problème !

En effet, le taux de la TVA étant invariablement dans B5, le « T.T.C » des asperges est faux car il est devenu B6, c’est l’inconvénient des références relatives…

Page 19 sur 54


Passons en références absolues

Je vous propose de figer la référence de ligne « 5 » dès l’écriture de la première formule par l’intermédiaire d’un préfix, le signe « $ ». Du coup, le « 5 » n’étant plus « relatif » mais « absolu », il devient définitivement figé durant les opérations de

« copier-coller »et durant l’évolution de la structure de la feuille de calculs.

EXEMPLE A B C D E

1 LÉGUMEPrix du kilo

QUANTITÉH.T T.T.C

2 Avocats 2,40€ 10 KG =PRODUIT(B2;C2) =D2+D2*B$5

3 Asperges 4,50€ 12 KG =PRODUIT(B3;C3)

=D3+D3*B$5

4

5 Taux TVA :

5,60%

Voilà, désormais nous n’aurons plus de soucis avec les références de B5, car le « 5 » est devenu absolu…

Toutefois, sachez que ici dans notre exemple, nous n’avons figé que le « 5 » car l’évolution du tableau se manifestait que de manière verticale, mais il est tout à fait possible de mettre un « $ » devant la colonne pour la figer en absolu !

=D2+D2*$B$5

Page 20 sur 54


Remarque :

Lors de l’utilisation d’une définition de nom, l’emploi du nom d’une cellule dans une formule revient à la représenter de façon « absolue ». Pour régler le problème des références absolues, la définition de nom est donc un excellent moyen…

Page 21 sur 54


L’EMBRANCHEMENT CONDITIONNEL IF

Introduction

L’embranchement conditionnel est une fonction qui permet de réaliser un traitement selon si une ou plusieurs conditions sont remplies.

Le nom de la fonction ? « SI ».

La syntaxe :

=SI(condition;Traitement1;Traitement2)

Description :

« Condition »

C’est une expression de comparaison qui peut utiliser des opérateurs et des fonctions appropriés ;

« Traitement1 »

C’est l’opération ou le contenu à afficher dans la cellule si « Condition » est vraie ;

« Traitement2 »

C’est l’opération ou le contenu à afficher si « Condition » est fausse.

Page 22 sur 54


La comparaison sur une chaîne de caractères

Une chaîne de caractères est en fait du texte.

Mais dans le cadre de traitements informatiques, un texte concerné par un traitement est une valeur au sens propre du terme, elle est appelée « chaîne de caractères ».

Dans la partie « Condition » de la fonction « SI », nous pouvons comparer le contenu d’une cellule à une chaîne de caractères.

Dans l’exemple suivant, dans A2, nous vérifions si A1 contient le mot « EXCEL ».

Si tel est le cas, nous afficherons ceci :

« Un tableur de chez Microsoft ».

Dans le cas contraire, on affichera :

« Application inconnue » :

EXEMPLE A B

1 EXCEL

=SI(A1="EXCEL";"Un tableur de chez

Microsoft";"Application inconnue")

Dans cet exemple, compte tenu du fait que A1 contient « EXCEL », A2 contiendra :

« Un tableur de chez Microsoft ».

Pourquoi des guillemets ?

Dans la fonction, toutes les chaînes de caractères sont encadrées par des guillemets…

En effet, dans une formule EXCEL, il en est ainsi !

Afin que le tableur puisse distinguer « références de cellules », « nombre » et « chaînes de caractères », ces dernières sont placées entre guillemets.

Attention, seules les chaînes de caractères sont placées entre guillemets à l’intérieure d’une formule.

Page 23 sur 54


Les opérateurs de comparaison

Voici la liste des opérateurs de base que vous pouvez employer pour effectuer des vérifications dans la partie « Condition » de la fonction « SI » :

OPÉRATEURS

DESCRIPTIONS

= Egal

<= Inférieur ou égal

>= Supérieur ou égal

< Strictement inférieur

> Strictement supérieur

<> Différent

Page 24 sur 54


Des exemples ?

1. Si A1 est supérieur ou égal à 100, on affiche « ça commence à devenir cher », sinon, on affiche « coût raisonnable » :

EXEMPLE A B

1 80,00€

=SI(A1>=100;"ça commence à

devenir cher !";"Coût raisonnable…")

2. si A1 est vide, on affiche « Valeur manquante « , sinon on affiche rien :

EXEMPLE A B

1 =SI(A1="";"Valeur manquante !";"")

Note :

Chaine vide, c’est-à-dire « aucun texte », ne s’exprime en ne saisissant que des guillemets sans contenu…

Page 25 sur 54


Les fonctions

La fonction « ET »

La syntaxe :

=si(et(condition1;condition2;condition3...);"traitement1";"traitement2")

Description :

Si toutes les conditions spécifiées dans la fonction « ET » s’avèrent vraies, « traitement1 » est exécuté, sinon, « traitement2 » est exécuté.

Dans l’exemple suivant, on vérifie que les cellules A2 et B2 contiennent bien une valeur supérieure à 0 pour que le produit soit calculé, sinon, on affiche un message d’erreur :

EXEMPLE A B C

1 Prix unitaire

Quantité

Total

2 4,77€

14 KG =SI(ET(A2>0;B2>0);PRODUIT(A2;B2);"Erreur")

Page 26 sur 54


La fonction « OU »

Moins rigide que la fonction « Et », la fonction « OU » vérifie qu’au moins l’une des conditions soit vraie pour que le traitement approprié soit exécuté.

Dans l’exemple suivant, on vérifie si l’une des cellules A2 ou B2 est manquante, et si tel est le cas, on affiche un message d’erreur !

EXEMPLE A B

1 NOM PRÉNOM

2 DURAN Pascal

3=SI(OU(A2="";B2="");

"Identité incomplète";"Identité validée")

Page 27 sur 54


La fonction « ESTVIDE »

Cette fonction permet de vérifier si une cellule est vide !

=si(ESTVIDE(A1);"La cellule A1 est vide !";"La cellule A1 contient une donnée...")

Les fonctions « ESTTEXTE » et « ESTNONTEXTE »

La fonction « EstTexte » vérifie si la cellule passée en paramètre contient du texte.

1. Si la valeur contenue dans la cellule n’est pas de type « texte », la fonction « ESTTEXTE » retourne « faux » ;

2. =SI(ESTTEXTE(A1);"A1 contient du texte";"A1 contient autre chose que du texte !")

Il existe une fonction qui réalise la vérification inverse, c’est la fonction « ESTNONTEXTE »

La fonction « ESTNUM »

Cette fonction vérifie que la cellule passée en paramètre contient bien une valeur numérique !

=SI(ET(ESTNUM(A1);ESTNUM(A2));SOMME(A1;A2);"Erreur, calcul impossible !")

Page 28 sur 54


LA FONCTION ARRONDI

Cette fonction arrondit une valeur numérique eau nombre de chiffres indiqué.

La syntaxe :

=ARRONDI(Nombre;No_chiffres)

Description :

1. Nombre représente le nombre à arrondir ;

2. No_chiffres permet de spécifier le nombre de chiffres à partir desquels « nombre « doit être arrondi.

Note :

1. Si No_chiffres est supérieur à 0 (zéro), nombre est arrondi au nombre de décimales indiqué ;

2. Si No_chiffres est égal à 0, nombre est arrondi au nombre entier le plus proche ;

3. Si No_chiffres est inférieur à 0, nombre est arrondi à gauche de la virgule.

Page 29 sur 54


Un exemple ?

1. Dans la cellule A1, écrivez la valeur 50,77 ;

2. Dans la cellule B1, écrivez la formule :

=ARRONDI(A1;1).

EXEMPLE A B

1 50,77=ARRONDI(A1;1)

Cette formule affichera le résultat 50,7.

Pourquoi ?Comme le deuxième argument de la fonction est à 1, donc une valeur positive, la fonction arrondit la partie décimale à un chiffre après la virgule.

Si vous aviez écrit 2 à la place de 1, les deux chiffres après la virgule auraient été conservés.

Page 30 sur 54


Maintenant, nous allons remplacer le nombre 1 par le nombre 2.

Vous constaterez que le résultat est 50,77, c’est-à-dire que cette fois, les 2 chiffres après la virgule sont pris en compte.

Pour arrondir la valeur à l’entier le plus proche, vous devez saisir le nombre 0 dans la partie arrondi de la fonction. Si vous appliquer cette formule à la cellule A1, vous obtiendrez le résultat 51.

Pour arrondir la valeur à gauche de la virgule, c’est-à-dire supprimer la partie décimale, l’argument de l’arrondi de la fonction devra être -1.

Donc :

=ARRONDI(A1;-1)

Cette fonction vous affichera le résultat 50.

Il existe également deux autres fonctions similaires de celle que nous venons d’étudier.

Il s’agit :

Page 31 sur 54


Les fonctions ARRONDI.INF et ARRONDI.SUP

ARRONDI.INF arrondit vers une valeur inférieure ;

ARRONDI.SUP arrondit vers une valeur supérieure.

La syntaxe :

=ARRONDI .INF(Nombre;précision)

=ARRONDI .SUP(Nombre;précision)

Descriptions :

Nombre est la valeur décimale qui doit être arrondie par les fonctions.

Précision est le nombre qui précise le nombre de chiffres que l’on souhaite après la virgule.

1. Si précision est 0, la fonction ARRONDI.INF arrondit le nombre à l’entier inférieur, et la fonction ARRONDI.SUP arrondit le nombre à l’entier supérieur ;

2. Si précision est 1, les deux fonctions arrondissent le nombre sur le premier chiffre de la partie décimale ;

3. Si précision est 2, les deux fonctions arrondissent le nombre sur les deux chiffres de la partie décimale.

Page 32 sur 54


LA FONCTION LIEN_HYPERTEXTE

Cette fonction permet d’insérer un lien HYPERTEXTE dans une cellule.

Le lien HYPERTEXTE est un texte qui vous permet, par l’intermédiaire d’un clic sur son nom, d’atteindre un emplacement précis dans le classeur, ou d’ouvrir un autre document, soit dans le disque, soit en réseau.

La syntaxe :

=LIEN_HYPERTEXTE(Document_ou_page;Texte_Afficher)

Description :

Document_ou_page est une chaîne de caractères qui désigne le chemin et le document hyper lié. Si cet argument n’est pas représenté par les références d’une cellule, la chaîne de caractères doit être placée entre guillemets.

Le chemin de destination du document à ouvrir peut débuter par http ou FTP.

Texte_affiché est le texte à afficher dans le lien hypertexte. Si cet argument n’est pas représenté par les références d’une cellule, la chaîne de caractères doit êtres placée entre guillemets. Ce texte va donc jouer le rôle d’étiquette.

Page 33 sur 54


Voici un exemple qui permettra d’ouvrir un document WORD depuis un lien EXCEL :

1. Dans le dossier « Mes documents », créez un document WORD que vous nommerez « visitez-moi.docx » ;

2. Créez un classeur EXCEL que vous enregistrez dans le dossier « Mes documents » ;

3. Dans une cellule du classeur, saisissez la formule suivante : =LIEN_HYPERTEXTE("visitez-moi.docx";"Cliquez ici")

4. Essayez le lien…

Note :

Vous avez la possibilité de spécifier le nom d’un signet juste après le nom du document hyperlié, il vous suffit d’employer le séparateur # (dièse).

Par exemple, si le signet à atteindre s’intitule « bienvenue » :

=LIEN_HYPERTEXTE("visitez-moi.docx#bienvenue";"Cliquez ici")

Avec le clavier, le signe dièse s’obtient en réalisant le raccourci :

ALT GR +3 du pavé alphanumérique.

Page 34 sur 54


Voici une mise en pratique avec une zone de liste déroulante :

1. Nous créons un lien hypertexte qui utilise le contenu d’une zone de liste déroulante pour déterminer le document WORD à ouvrir ;

2. Dans un même dossier, nous élaborons 3 documents WORD, par exemple, informatique.docx, astronomie.docx et histoire.docx ;

3. Dans chacun de ces documents WORD, nous saisissons un texte dont le sujet est en rapport avec le nom du document ;

4. Toujours dans ce dossier, nous créons un classeur index.xlsx ;

5. Dans ce classeur, nous créons une zone de liste déroulante dont les 3 items seront :

« Informatique », « Astronomie » et « Histoire » ;

6. A droite de la zone de liste déroulante, dans une autre cellule, on élabore une formule dont le lien hypertexte affiche automatiquement le sujet du document à ouvrir :

a. Lorsque l’utilisateur sélectionne l’item « informatique » dans la zone de liste déroulante, le lien affiche « Visiter l’informatique » ;

b. « Visiter l’astronomie », lorsqu’il sélectionne l’item « Astronomie » ;

c. « Visiter l’histoire», lorsqu’il sélectionne l’item « Histoire ».

7. Enfin, il suffira que l’utilisateur active le lien pour que le fichier WORD correspondant soit ouvert.

La difficulté ?

Comme le premier paramètre de la fonction « Lien_HYPERTEXTE » d’EXCEL doit être le nom entier du fichier à ouvrir, c’est-à-dire avec son extension, et comme l’item récupéré dans la zone de liste déroulante ne comporte pas l’extension, nous devrons réaliser une concaténation de l’extension « .docx » après le nom du fichier.

Idem pour l’affichage du texte du lien, c’est-à-dire le deuxième paramètre de la fonction « LIEN_HYPERTEXTE « , où nous devrons concaténer l’item sélectionné de la zone de liste déroulante après le texte suivant : « Ouvrir l’ « .

Page 35 sur 54


Vous êtes prêts ?

Élaborez les 3 documents WORD dans un dossier commun, et ensuite, ouvrez un classeur et enregistrez le dans ce dossier en le nommant index.

Maintenant, nous allons travailler dans le classeur index.xlsx…

1. Dans la cellule A1, saisissez le titre suivant :

« Choix du sujet ».

2. Placez-vous maintenant dans la cellule A2, nous allons créer la zone de liste déroulante.

3. Cliquez sur la commande du ruban :

Onglet "Données" puis :

"Outils de données|Validation des données|Validation des données ».

Une boîte de dialogue apparaît à l’écran.

Cliquez sur l’onglet « options ».

Dans la liste intitulée « Autoriser », choisissez « Liste ».

Placez-vous dans la zone d’édition intitulée « Source ».

Saisissez les 3 items de la future zone de liste déroulante, en l’occurrence : informatique, astronomie et histoire, n’oubliez pas de les séparer par un point virgule.

Pour valider cette boîte de dialogue, cliquez sur le bouton « Ok ».

Voilà, la zone de liste déroulante est créée…

4. Déplacez-vous dans la cellule B2, voici la formule que vous devez écrire, d’abord en français, puis avec la véritable syntaxe :

Si A2 n’est pas vide, donc si l’utilisateur a réalisé un choix, créer un lien qui pointe sur le fichier dont le nom est l’item sélectionné dans A2 plus le rajout de l’extension point docx, et afficher ouvrir plus le nom de l’item sélectionné, sinon, afficher une chaîne vide, donc, rien…

Page 36 sur 54


Voici la syntaxe, ici, pour réaliser les concaténations, nous utilisons le et commercial.

=SI(A2<>"";LIEN_HYPERTEXTE(A2&".docx";"Ouvrir l'"&A2);"").

Page 37 sur 54


LA FONCTION AUJOURDHUI

Rappel sur le formatage de cellules

Voici une première notion de fonctions et de formats spécifiques à la date et à l’heure :.

1. Pour obtenir le format de cellules du genre « 14 mars 2001 », sélectionnez les cellules pour lesquelles vous souhaitez appliquer le format ;

2. Réalisez un clic droit de la souris sur la sélection, puis, dans le menu contextuel qui apparaît à l’écran, Cliquez sur la commande intitulée « Formats de cellules » ;

3. Dans la boîte de dialogue qui apparaît à l’écran, cliquez sur l’onglet « nombres » ;

4. Dans la liste des catégories, sélectionnez « Date » ;

5. Dans la liste intitulée « type », sélectionnez le format « 24 mars 2001 » ;

6. Cliquez sur le bouton « Ok ».

Dans ces cellules, durant la saisie des dates, vous pourrez saisir directement le, ou les chiffres du jour, le moi et l’année sans avoir besoin d’avoir à saisir les espace car ils seront directement insérés… D’ailleurs, vous pouvez même vous offrir le luxe d’oublier de saisir l’année, elle sera insérée automatiquement…

Maintenant, voici la description de la fonction aujourdhui :

Pour insérer automatiquement la date du jour dans l’une des cellules pour lesquelles vous venez de configurer le format, écrivez la formule :

=AUJOURDHUI()

Page 38 sur 54


LES FONCTIONS DE TRAITEMENTS DE CHAÎNES DE CARACTÈRES

CONCATENER

Cette fonction permet de concaténer, donc d’aligner une suite de chaînes de caractères passées en paramètres.

La syntaxe :

=CONCATENER(chaîne1;chaîne2;chaîne3;ETC)

Description :

Chaque paramètre est séparé du paramètre suivant par un « Point virgule » ;

Un paramètre peut être les références d’une cellule qui contient une valeur de type « chaîne de caractères », ou bien, directement une chaîne de caractères placée entre guillemets.

Exemple :

EXEMPLE A B C

1 DURAN2

2Martine

2 =CONCATENER"Je m’appelle ";C1;" ";A1;"et mon âge est ";B1;" ans")

Page 39 sur 54


Et ça, ça donne quoi ?

RÉSULTAT A B C

1 DURAN2

2Martine

2 Je m’appelle Martine DURAN et mon Age est 22 ans

Vous constaterez que dans la formule, les références de cellules ne sont pas placées entre guillemets tandis que le texte que nous souhaitons afficher tel que, nous le plaçons entre guillemets. N'oubliez pas de prévoir les espaces nécessaires dans les caractères placés entre guillemets.

Remarque :

Le caractère & « et commercial » est un opérateur de concaténation.

Il est donc possible, dans le cas où la concaténation doit avoir lieue en dehors d’une fonction, , d’utiliser un opérateur de concaténation, le « Et commercial » « & ».

Par exemple, pour concaténer les cellules A1 et B1 :

=a1&B1

Ou encore, pour concaténer A1 et B1 en plaçant un espace entre les deux :

=A1&" "&B1

Du coup, on aurait pu aussi :

="Je m’appelle "&C1&" "&A1&"et mon âge est "&B1&" ans"

Page 40 sur 54


CAR

Cette fonction permet d’afficher le caractère dont le code est passé en argument.

Dans l’exemple qui suit, nous allons introduire la fonction CAR dans la fonction CONCATENER.

Positionnez-vous dans la cellule A1 et écrivez cette formule :

=CONCATENER(CAR(71);CAR(65);CAR(71);CAR(78);CAR(69);CAR(82))

Ainsi, vous concaténez une suite de caractères alphabétiques qui se suivent grâce à la fonction CONCATENER.

Cette formule doit afficher : le mot gagner.

Pour les caractères alphabétiques, le « A » majuscule débute au code 65 et le « a » minuscule débute au code 97.

CODE

C’est la fonction inverse de la fonction CAR.

Cette fonction affiche le code du premier caractère de la chaîne de caractères passée en paramètre.

Si les caractères sont saisis directement dans la fonction, vous devez les encadrer par des guillemets.

Par exemple :

=CODE("a"

Page 41 sur 54


Cette formule renvoie le code correspondant au « a » minuscule, c’est-à-dire le code 97.

Page 42 sur 54


EXACT

Cette fonction compare deux chaînes de texte et renvoie la valeur VRAI si elles sont identiques ou la valeur FAUX dans le cas contraire. EXACT respecte la casse, mais ne tient pas compte des différences de mise en forme. Utilisez EXACT pour tester la conformité d'un texte tapé dans un document.

Exemple :

1. Dans la cellule A1, écrivez votre prénom en minuscule ;

2. Dans la cellule B1, écrivez votre prénom en majuscule ;

3. Dans la cellule A5, écrivez la formule suivante :

=SI(EXACT(A1;B1);"Les deux cellules sont identiques";"Les deux cellules ne sont pas identiques")

4. Vous allez constater que la formule vous affiche ceci :

« Les deux cellules ne sont pas identiques ».

5. Alors, retournez dans la cellule A1 et écrivez votre prénom en majuscule cette fois, afin que A1 et B1 soient identiques.

Retournez voir se que vous affiche la formule dans la cellule A5.

Normalement, vous devriez lire ceci :

« Les deux cellules sont identiques ».

Dans notre exemple, nous avons introduit la fonction EXACT dans la structure conditionnelle SI. Par conséquent, nous déterminons à l’avance des messages à afficher selon si les cellules A1 et B1 sont identiques. Mais si vous placez directement la fonction EXACT dans une cellule, elle affichera, selon le résultat de la comparaison des arguments, la valeur VRAI ou FAUX;

Page 43 sur 54


GAUCHE et DROITE

La fonction GAUCHE renvoie le, ou les, premiers caractères d'une chaîne de texte en fonction du nombre de caractères que vous spécifiez.

La syntaxe :

=GAUCHE(texte;nombre)

Description :

Texte est un texte placé entre guillemets ou les références d’une cellule texte.

Nombre est le nombre de caractères que l’on veut extraire à partir du premier caractère du texte.

Exemple :

1. Dans la cellule A1, écrivez le prénom Philippe ;

2. Positionnez-vous dans la cellule A5 et saisissez la formule suivante :

=CONCATENER(GAUCHE(A1;3);GAUCHE(A1;3))

3. Normalement, vous devriez obtenir le résultat suivant : « PhiPhi ».

Ici, la fonction GAUCHE a été intégrée dans la fonction CONCATENER.

En fait, dans la formule de notre exemple, la fonction GAUCHE renvoie les 3 premières lettres de Philippe, et comme cette fonction est utilisée deux fois dans la fonction CONCATENER, il y a donc concaténation de « Phi » et de « Phi ».

La fonction DROITE s’utilise de la même manière mais par opposition à la fonction GAUCHE, elle extrait les caractères de la chaîne de caractères à partir de la droite.

Page 44 sur 54


Page 45 sur 54


NBCAR

Cette fonction retourne un numérique. Il s’agit de la taille en nombre de caractères de la chaîne de texte passée en argument.

Exemple :

1. Dans la cellule A1 écrivez bonjour ;

2. Placez-vous maintenant dans la cellule A5, écrivez la formule suivante :

=NBCAR(A1)

Voilà, vous pourrez constater que cette formule retourne la valeur 7, c’est-à-dire le nombre de caractères de la chaîne de texte « bonjour ».

MINUSCULE et MAJUSCULE

Ces fonctions convertissent les caractères passés en paramètres, soit en minuscule si vous utilisez la fonction MINUSCULE, soit en majuscule si vous utilisez la fonction MAJUSCULE.

Par exemple, dans la cellule A1, saisissez paris en minuscule.

Dans A2, saisissez la formule suivante :

=majuscule(A1)

Cette formule affichera dans A2, PARIS en majuscule…

Page 46 sur 54


STXT

Cette fonction renvoie un nombre défini de caractères d'une chaîne de texte à partir de la position que vous indiquez.

La syntaxe :

=STXT(texte ;départ ;taille)

Description :

Texte est une chaîne de caractères placée entre guillemets ou les références d’une cellule contenant un texte.

Départ est la position du caractère dans le texte pour lequel l’extraction commence.

Taille et le nombre de caractères à extraire.

Exemple :

1. Dans la cellule A1, écrivez : J’ai mal aux dents ;


=STXT(A1;14;4)

Description :

1. Cette formule pointe sur la cellule contenant « J’ai mal aux dents » ;

2. Elle extrait 4 caractères à partir du quatorzième caractère de la chaîne ;

3. Le texte affiché est donc le mot : Dent » au singulier puisque le « S » n’a pas été extrait.

Page 47 sur 54


NOMPROPRE

Cette fonction affiche en majuscules la première lettre du texte passé en paramètre.

REMPLACER

Cette fonction remplace une chaîne de caractères par une autre, en fonction du nombre de caractères spécifiés.

La syntaxe :

=REMPLACER(texte;Départ;Nombre;Nouvelle_chaîne)

Description :

Texte est le texte à traiter, soit placé entre guillemets, soit représenté par les références d’une cellule.

Départ correspond à la position du premier caractère à partir duquel le remplacement doit avoir lieu.

Nombre correspond au nombre de caractères à remplacer.

Nouv_chaîne correspond à la chaîne de caractères à insérer.

Exemple :

1. Dans la cellule A1, écrivez : Je être content ;


=REMPLACER(A1;4;4;"suis")

3. En résultat, vous obtenez: « Je suis content ».

Page 48 sur 54


REPT

Cette fonction répète « N » fois la chaîne de caractères passée en argument.

La syntaxe :

=REPT(Texte;Nombre)

Description :

Texte est un texte.

Nombre correspond au nombre de fois que le texte doit être répété.

Par exemple :

Pour répéter 10 fois la chaîne de caractères TOTO qui se trouve dans la cellule A1, écrivez la formule suivante :

=REPT(A1;10)

Page 49 sur 54


CHERCHE

La fonction CHERCHE renvoie la position du premier caractère d’une chaîne de texte recherchée.

Cette recherche est réalisée dans une autre chaîne de texte de façon

A pouvoir utiliser ensuite les fonctions STXT ou REMPLACER, et ce, afin d’en modifier le texte.

La syntaxe :

=CHERCHE(TexteRecherché;TexteSource;Départ)

Description :

TexteRecherché est le texte qui est recherché à l’intérieur du texte source, situé juste après, dans la fonction.

Départ est la position du caractère de départ d’où débute la recherche à l’intérieur du texte source.

Par exemple :

4. Dans la cellule A1, écrivez : Je suis fort.


=CHERCHE("F";A1;1)

Cette formule vous renverra la valeur 9, c’est-à-dire la position du caractère « F » à l’intérieur du texte contenu dans la cellule A1.

Si la fonction « cherche » est imbriquée dans le deuxième argument de la fonction « remplacer », il est possible de rechercher une chaîne de caractères à l’intérieur d’un texte afin de le substituer…

Page 50 sur 54


Voici un exemple :

Dans A2, nous désirons afficher le texte de la cellule A1, mais en remplaçant PARIS par MARSEILLE.

EXEMPLE A B

1

J’habite dans

la magnifique

ville de PARIS

depuis 5 ans !

=REMPLACER(A1;CHERCHE("PARIS";A1;1);5;"MARSEILLE")

SUPPRESPACE

Cette fonction supprime les espaces qui n’ont aucune utilité à l’intérieure de la chaîne de caractères passée en paramètre.

Les fonctions CNUM et TEXTE

La fonction CNUM convertit la chaîne de caractères passée en paramètre en un numérique ;

A l’inverse, La fonction TEXTE convertit le nombre passé en paramètre en une chaîne de caractères.

Page 51 sur 54


Substitue

La fonction Substitue remplace une sous chaîne dans un texte.

La syntaxe :

=Substitue(chaîne1,chaîne2,chaîne3)

Description :

Le premier paramètre est la sous chaîne à extraire ;

Le second paramètre est la nouvelle sous chaîne ;

Le troisième paramètre correspond au texte à traiter.

Par exemple :

Si dans la cellule A1, vous avez un texte comportant « bonjour », voici la formule qui permettrait de remplacer « bonjour » par « au revoir ».

=substitue("bonjour","au revoir",a1)

Page 52 sur 54


LES FORMULES MATRICIELLES

Les formules matricielles permettent d’exécuter une fonction sur un groupe de valeurs.

1. Si par exemple vous désirez faire la somme de la colonne A, de A2 à A10 et uniquement si les valeurs sont supérieures à 10, voici comment procéder :

{=SOMME(SI(A2:A10>=10;A2:A10))}

Attention, pour que cette formule puisse être considérée comme une formule matricielle et non pas comme une formule classique, vous devez la valider par le raccourci clavier : MAJ +CTRL +Entrée. Automatiquement, la formule sera placée entre accolades…

2. Pour faire la moyenne des valeurs supérieures ou égales à 10 :

{=MOYENNE(SI(A2:A10>=10;A2:A10))}

3. Et maintenant, la moyenne des valeurs inférieures à 10 :

{=MOYENNE(SI(A2:A10<10;A2:A10))}

4. Pour afficher le texte « ok » si l’une des valeurs comprises de a1 à J1 contient la valeur 50 :

{=SI(OU(A1:J1=50);"OK")}

Page 53 sur 54


Voici un exemple particulièrement concret…

Dans ce tableau, nous avons un certain nombre d’opérations de retraits pour quelques banques.

Nous désirons réaliser la somme des retraits pour chacune des banques :

BANQUES RETRAITS Le total par banqueBNP 75,00 € BNP {=SOMME(SI(A2:A12="BNP";B2:B12))}

CRÉDIT AGRICOL 135,00 € CRÉDIT AGRICOL {=SOMME(SI(A2:A12="CRÉDIT AGRICOL";B2:B12))}CAISSE

D'ÉPARGNE 200,00 €CAISSE

D'ÉPARGNE{=SOMME(SI(A2:A12="CAISSE

D'ÉPARGNE";B2:B12))}CRÉDIT MUTUEL 40,00 € CRÉDIT MUTUEL {=SOMME(SI(A2:A12="CRÉDIT MUTUEL";B2:B12))}

BNP 70,00 €CRÉDIT AGRICOL 250,00 €CRÉDIT AGRICOL 250,00 €

BNP 700,00 €BNP 300,00 €

CRÉDIT AGRICOL 50,00 €CAISSE

D'ÉPARGNE 120,00 €

Philippe BOULANGER

Page 54 sur 54

Documents

Web viewEXCEL 2010 . les formules De P BOULANGER. octobre 2012 Volume 2/2. Niveau 2, FORMULES & FONCTIONS TABLE DES MATIÈRES. LES FORMULES D’EXCEL 2010. 4