Ms Excel

Ms Excel

Traduction des formules

Traductions des fonctions
VRAI ⇔ TRUE
FAUX ⇔ FALSE
Séparateur dans les formules : EN = , | FR = ;

Tips & tricks

Newline/Linebreak

Pour insérer un saut de ligne dans une cellule : Alt + Enter. Pour un saut de ligne dans la boite de dialogue de recherche : Ctrl + j.

Search patterns

Dans le dialogue de recherche d'excel, les wildcards ? et * (exactly one et any string, respectivement) char sont utilisables. Le caractère d'échappement est ~.

Recalculation

Recalcul basique : F9
Trigger recalculation : Shift + F9
Force complete recalculation (“Complete reboot”) : Ctrl + Alt + Shift + F9

Rechercher une valeur dans une matrice sans trier

Utiliser INDEX + EQUIV.

=INDEX(
  <matrice>;
  EQUIV(
    <valeur recherchée>;
    <matrice>;
    <recherche la valeur exacte (1 : valeur supérieure / -1 : valeur inférieure)>
  );
  <num colonne dans la matrice>
)

Highlight column and row of active cell

Source

La formule en français :

=OU(CELLULE("col")=CELLULE("col";A1);CELLULE("ligne")=CELLULE("ligne";A1))

La formule pour ne mettre en valeur que la ligne :

=SI(CELLULE("ligne")=CELLULE("ligne";A1);VRAI;FAUX)

Fonctions

Nom Fr	Nom En	Description
SIERREUR		Permet de gérer les erreurs (pour ne pas obetnir des valeurs erreurs qui peuvent empêcher certaines fonctions de marcher)
ESTVIDE	ISBLANK	Permet d'évaluer si une cellule est vide
EQUIV		Renvoie la position d'une valeur dans une matrice
INDEX		Renvoie la valeur d'une cellule

Fonctions utiles

Fonction	Descriptif	“Avant : cellule 1”	Avt : cellule 2	“Après : cellule 3”	Détails de la formule utilisée
RECHERCHEV	Permet de comparer 2 listes de données présentées en colonnes et de récupérer des valeurs sur les lignes correspondantes.	“voir détails dans l'onglet`ex RECHERCHEV`“
RECHERCHEH	Idem RECHERCHEV mais à utiliser quand on compare des données présentées en lignes et non en colonnes. Cette fonction est très rarement utilisée.
CONCATENER	Permet de fusionner le contenu de 2 cellules	COM	P01	COMP01	=CONCATENER(C7;D7)
CONCATENER	Permet de fusionner le contenu de 2 cellules et également des parties de texte “fixes” entre les deux	COM	P01	COM - P01	=CONCATENER(C7;” ”;“-”;“ ”;D8)
STXT	Permet d'isoler une chaine de caractères au milieu d'une cellule. Il faut donner la position de départ (ici : le 7ème caractère) et le nb de caractères à isoler (ici : on récupère 20 caractères au total)	P01-A c'est ça que je veux 1234		c'est ça que je veux	=STXT(C4;7;20)
GAUCHE	Fonction similaire à STXT qui permet de tronquer la partie gauche du contenu d'une cellule (on indique uniquement le nb de caractères en partant de la gauche qu'on souhaite conserver, ici : 3 caractères)	P01-A c'est ça que je veux 1234		P01	=GAUCHE(C5;3)
DROITE	Fonction similaire à STXT qui permet de tronquer la partie droite du contenu d'une cellule (on indique uniquement le nb de caractères en partant de la droite qu'on souhaite conserver, ici : 4 caractères)	01-A c'est ça que je veux 1234		1234	=DROITE(C6;4)
NBCAR	Compte le nombre de caractères dans une cellule	je veux contrôler mes formats		29	=NBCAR(C9)
NB.SI	Permet de compter le nb de valeurs non vides dans une plage de valeurs. Utilisable avec des nombres ou du texte.	3	X	2	=NB.SI(C10:D10;“<>”)
SI	Permet par exemple de contrôler la valeur d'une cellule (ici : on contrôle si la valeur est inférieure à 20) et d'indiquer un message si le contrôle est OK (ici : on a mis le texte “pas d'erreur”) et un autre si le contrôle est KO (ici : on a mis le texte “à contrôler”)	22		à contrôler	=SI(C11<20;“pas d'erreur”;“à contrôler”)
MAJUSCULE	Convertit un texte en majuscules	Test texte à convertir		TEST TEXTE À CONVERTIR	=MAJUSCULE(C12)
MINUSCULE	Convertit un texte en minuscules	Test 2 TEXTE à CONVERTIR		test 2 texte à convertir	=MINUSCULE(C13)
NOMPROPRE	Met en majuscule le 1er caractère d'une cellule et les autres en minuscules	AUTRE test De CONVERSION		Autre Test De Conversion	=NOMPROPRE(C14)
SUPPRESPACE	Supprime les espaces “doubles” entre les mots	Il y a trop d'espaces dans ma phrase		Il y a trop d'espaces dans ma phrase	=SUPPRESPACE(C17)
REMPLACER	Remplace une partie du contenu d'une cellule par une autre valeur ( chiffres ou texte) : il faut indiquer l'emplacement du 1er caractère à supprimer (dans l'expe, c'est le 5ème), le nb de caractères à supprimer (dans l'exple il y en a 9) ainsi que les nouvelles valeurs à insérer (dans l'exple “Nouveau Texte inséré)	P01-à changer 1234		P01-Nouveau Texte inséré 1234	=REMPLACER(C15;5;9;”Nouveau Texte inséré“)
SUBSTITUE	Remplace en masse un mot ou une valeur par un(e) autre	Test ERREUR à modifier 01	X	Test NOUVEAU 01	=SUBSTITUE(C16;”ERREUR à modifier“;”NOUVEAU“)
ENT	Arrondit à l'entier immédiatement inférieur	2,67		2	=ENT(C18)
SI(ESTERREUR)	Combinaison de 2 formules qui permet de renvoyer une valeur (par exemple 0) ou un message (par exemple “ko”) si une valeur est en erreur (#N/A, DIV0…). Sert notamment à pouvoir faire des sommes sur un tableau complété par des RECHERCHEV et qui comporte des #N/A	#N/A		0	=SI(ESTERREUR(C19);0;C19)

Conditionnal Formatting

Lorsque l'on utilise une formule pour conditionner le formatage, ne pas oublier de virer les $ sinon le formatage se réfèrera seulement à la cellule cible et non à chaque cellule, exemple : =ESTERREUR(A2) et non =ESTERREUR($A$2).

Pense-bête : ne pas oublier de spécifier le formattage…

Nommer une plage de cellules

Il est intéressant de nommer certaines plages de cellules : cela permet d'y faire référence facilement et même de les “exposer” dans d'autres onglets.

Pour cela, aller dans le ruban Formules, Boîte Noms définis, bouton Définir un mon. La boite de dialogue qui s'affiche permet de préciser le scope du nom défini.

Plus rapidement, on peut utiliser le champ de définition du nom (à gauche de la barre de formule).

Listes déroulantes

Source Pour créer une liste déroulante sur des cellules avec une liste de valeur contenue dans un autre onglet :

Sélectionner les cellules contenant les valeurs et lui donner un nom à portée sur tout le classeur.
Sélectionner les cellules qui doivent afficher les listes déroulantes, cliquer sur le bouton Validation des données dans le ruban Données et remplir les champs suivants :

Obtenir une liste de dates de jours ouvrés

Formater les cellules en date
Mettre une date dans la première cellule (ex. : A1)
Dans la cellule dessous (A2), entrer : =SERIE.JOUR.OUVRE(A1;1)
Tirer la cellule autant que voulu

Table of Contents