Table of Contents

Ms Excel

Traduction des formules

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

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

FonctionDescriptif“Avant : cellule 1”Avt : cellule 2“Après : cellule 3”Détails de la formule utilisée
RECHERCHEVPermet 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'ongletex RECHERCHEV
RECHERCHEHIdem 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.
CONCATENERPermet de fusionner le contenu de 2 cellulesCOMP01COMP01=CONCATENER(C7;D7)
CONCATENERPermet de fusionner le contenu de 2 cellules et également des parties de texte “fixes” entre les deuxCOMP01COM - P01=CONCATENER(C7;” ”;“-”;“ ”;D8)
STXTPermet 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 1234c'est ça que je veux=STXT(C4;7;20)
GAUCHEFonction 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 1234P01=GAUCHE(C5;3)
DROITEFonction 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 12341234=DROITE(C6;4)
NBCARCompte le nombre de caractères dans une celluleje veux contrôler mes formats29=NBCAR(C9)
NB.SIPermet de compter le nb de valeurs non vides dans une plage de valeurs. Utilisable avec des nombres ou du texte.3X2=NB.SI(C10:D10;“<>”)
SIPermet 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”)
MAJUSCULEConvertit un texte en majusculesTest texte à convertirTEST TEXTE À CONVERTIR=MAJUSCULE(C12)
MINUSCULEConvertit un texte en minusculesTest 2 TEXTE à CONVERTIRtest 2 texte à convertir=MINUSCULE(C13)
NOMPROPREMet en majuscule le 1er caractère d'une cellule et les autres en minusculesAUTRE test De CONVERSIONAutre Test De Conversion=NOMPROPRE(C14)
SUPPRESPACESupprime les espaces “doubles” entre les motsIl y a trop d'espaces dans ma phraseIl y a trop d'espaces dans ma phrase=SUPPRESPACE(C17)
REMPLACERRemplace 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 1234P01-Nouveau Texte inséré 1234=REMPLACER(C15;5;9;”Nouveau Texte inséré“)
SUBSTITUERemplace en masse un mot ou une valeur par un(e) autreTest ERREUR à modifier 01XTest NOUVEAU 01=SUBSTITUE(C16;”ERREUR à modifier“;”NOUVEAU“)
ENTArrondit à l'entier immédiatement inférieur2,672=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/A0=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 :

Obtenir une liste de dates de jours ouvrés

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