Name: Tremplin Numérique
Price range: $$$

Imaginez ceci : vous disposez d'un grand classeur rempli de tableaux bien formatés, filtrés et triés. Vous pensez peut-être que votre travail est terminé, mais en réalité, Excel attend que vous en fassiez plus avec ces tableaux, désireux de vous aider à tirer le meilleur parti du travail acharné que vous avez accompli jusqu'à présent.

Dans cet article, je vais passer en revue trois des fonctions ou combinaisons de fonctions que j'utilise le plus lorsque je souhaite extraire ou résumer des informations dans mes tableaux Excel.

Sommaire

RECHERCHEV et RECHERCHEH

VLOOKUP et HLOOKUP sont tous deux utilisés pour localiser et récupérer une valeur à partir d'emplacements spécifiques dans une table.

RECHERCHEV s'appuie sur le fait que le tableau de données soit vertical et recherche la première colonne (verticale) d'un tableau.
RECHERCHEH s'appuie sur le fait que le tableau de données soit horizontal et recherche la première ligne (horizontale) d'un tableau.

RECHERCHEV

Ici, j'ai une liste des notes d'examen et des notes requises pour chaque note (appelons ce tableau 1). J'ai également un tableau contenant les scores des élèves (tableau 2 à partir de ce point). Je souhaite qu'Excel utilise les informations du tableau 1 pour compléter la colonne manquante du tableau 2.

J'utiliserai RECHERCHEV, car je souhaite qu'Excel recherche les valeurs dans la première colonne du tableau 1 pour renvoyer la note de chaque élève dans le tableau 2. La fonction RECHERCHEV a la syntaxe suivante :

=VLOOKUP(a,b,c,d)

où

un est la valeur à rechercher (dans l'exemple ci-dessus, il s'agit des valeurs de la colonne E),
b est le tableau contenant les valeurs de référence (dans ce cas, ce sont les cellules A1 à B9, ou le tableau 1),
c est le numéro de colonne dans ce tableau (je veux qu'il renvoie la note, c'est donc la deuxième colonne du tableau 1), et
d est un critère facultatif qui indique à Excel de rechercher les valeurs approximativement (« VRAI ») ou exactement (« FAUX »). Si laissé vide, la valeur par défaut est VRAI.

Donc, dans mon cas, je vais taper cette formule dans la cellule F2 pour calculer la note de Tom, avant d'utiliser AutoFill pour rechercher les autres notes dans le tableau :

=VLOOKUP(E2,$A$1:$B$9,2,TRUE)

J'ai utilisé des symboles $ pour créer une référence absolue pour la valeur
b
ci-dessus, car je souhaite qu'Excel utilise continuellement les cellules A1 à B9 pour rechercher les valeurs. J'ai également utilisé « TRUE » pour la valeur
d
car le tableau des limites des scores contient des plages et non une note attribuée aux scores individuels.

RECHERCHEH

Ici, nous avons les mêmes informations sur les limites de qualité, mais cette fois, elles sont affichées horizontalement. Cela signifie que les données que nous souhaitons récupérer se trouvent dans la deuxième ligne du tableau des limites.

La fonction HLOOKUP a une syntaxe similaire à VLOOKUP :

=HLOOKUP(a,b,c,d)

où

un est la valeur à rechercher (dans cet exemple, les valeurs de la colonne C),
b est une référence absolue aux cellules contenant la valeur de recherche (dans ce cas, il s'agit de A1 à I2),
c est le numéro de ligne de ce tableau (je veux qu'il renvoie la note, c'est donc la deuxième ligne), et
d (facultatif) est soit « VRAI » pour les valeurs approximatives, soit « FALSE » pour les valeurs exactes.

Je vais donc taper cette formule dans la cellule C5 pour calculer la note de Tom, avant d'utiliser AutoFill pour rechercher les autres notes dans le tableau :

=HLOOKUP(B5,$A$1:$I$2,2,TRUE)

INDEX Avec MATCH

Un autre moyen efficace de rechercher et de récupérer des valeurs consiste à utiliser INDEX et MATCH, en particulier lorsqu'ils sont utilisés ensemble. INDEX recherche et renvoie une valeur dans un emplacement défini, tandis que MATCH recherche et renvoie l'emplacement d'une valeur. Ensemble, ils permettent une récupération dynamique des données.

Syntaxes individuelles

Avant d'envisager l'utilisation de ces fonctions ensemble, examinons-les brièvement individuellement.

La syntaxe d'INDEX est

INDEX(a,b,c)

où un est la plage de cellules contenant les données, b est le numéro de ligne à évaluer, et c est le numéro de colonne à évaluer.

Sur cette base,

INDEX(B2:D8,4,2)

évaluerait les cellules B2 à D8 et renverrait la valeur dans la quatrième ligne et la deuxième colonne dans cette plage.

Pour MATCH, nous suivons

MATCH(x,y,z)

où x est la valeur que nous recherchons, oui est la plage dans laquelle la valeur doit être trouvée, et z (facultatif) est le type de correspondance.

Sur cette base,

MATCH(5,B2:B8,0)

me dirait où se trouve le chiffre 5 dans la plage B2 à B8, et le 0 indique à Excel d'effectuer une correspondance exacte.

Utilisé ensemble

Dans cet exemple, je souhaite qu'Excel m'indique le nombre de buts qu'un joueur spécifié a marqué au cours d'un mois donné. Plus précisément, je veux savoir combien de buts le joueur C a marqué au cours du troisième mois, mais je vais créer cette formule pour pouvoir modifier ces critères à tout moment.

Pour y parvenir, j'ai besoin d'Excel pour déterminer où se trouve le joueur C dans le tableau, puis de me dire quelle valeur se trouve dans la troisième colonne des données.

Dans la cellule G4, je vais commencer par la fonction INDEX, car je veux qu'Excel recherche et renvoie une valeur à partir de mes données brutes. Ensuite, je dirai à Excel où rechercher ces données.

=INDEX(B2:D8,

La partie suivante de la syntaxe INDEX est le numéro de ligne, et cela varie en fonction du joueur que j'indique dans la cellule G2. Par exemple, si je veux rechercher le joueur A, ce sera la première ligne. Pour ce faire, je vais lancer la fonction MATCH, car je veux qu'Excel fasse correspondre le joueur que j'ai tapé dans la cellule G2 avec la cellule correspondante dans la colonne du joueur (A2: A8) et détermine sur quel numéro de ligne il se trouve. J'ai également ajouté un 0 à la fin, car je veux qu'Excel renvoie une récupération exacte.

=INDEX(B2:D8,MATCH(G2,A2:A8,0),

Maintenant que j'ai indiqué à Excel le numéro de ligne de la fonction INDEX, je dois terminer avec le numéro de colonne. Dans mon cas, le numéro de colonne représente le numéro du mois que j'ai tapé dans la cellule G3.

=INDEX(B2:D8,MATCH(G2,A2:A8,0),G3)

Lorsque j'appuie sur Entrée, Excel m'informe correctement que le joueur C a marqué cinq buts au cours du troisième mois.

Désormais, je peux modifier n'importe quelle valeur de ma table de recherche pour trouver le total de n'importe quel joueur pour n'importe quel mois.

COUNTIF et SUMIF

Comme vous pouvez le constater à partir de leurs noms, ces deux fonctions comptent et additionnent les valeurs en fonction des critères que vous définissez. Tout ce qui n'est pas inclus dans vos critères ne sera ni ajouté ni compté, même s'il se situe dans la plage que vous spécifiez.

COUNTIF

COUNTIF compte les cellules contenant certains critères. La syntaxe est

COUNTIF(a,b)

où un est la plage que vous souhaitez compter, et b est le critère de comptage.

De même, si je voulais inclure plus d'un critère, j'utiliserais COUNTIFS :

COUNTIFS(a,b,c,d)

où un et b sont le premier couple plage-critère, et c et d sont le deuxième couple gamme-critères (vous pouvez avoir jusqu'à 127 couples).

Si un critère est du texte ou un symbole logique ou mathématique, il doit être placé entre guillemets.

Dans mon tableau des salaires ci-dessous, je souhaite calculer le nombre de personnes gagnant plus de 40 000 £ et, séparément, le nombre de membres du personnel de service qui gagnent une prime supérieure à 1 000 £.

Pour compter le nombre de travailleurs dont le salaire dépasse 40 000 £, je dois taper cette formule dans la cellule D8 :

=COUNTIF(C2:C6,">40000")

où C2:C6 est la fourchette où se situent les salaires, et « >40 000 » est le critère.

Pour calculer le nombre de militaires qui gagnent des primes supérieures à 1 000 £, j'utiliserais COUNTIFS, car j'ai deux critères.

=COUNTIFS(B2:B6,"Services",D2:D6,">1000")

Le B2 : B6, « Services » la partie est le premier couple plage-critère, et D2:D6, »>1000″ est le deuxième.

Même s'il y a des virgules séparant les milliers dans mon tableau ci-dessus, je ne les ai pas incluses dans la formule, car les virgules ont une fonction différente dans ce contexte.

SUMIF

SUMIF additionne les cellules en fonction des critères que vous définissez. Cela fonctionne sur un principe similaire à COUNTIF, mais avec plus d'arguments entre parenthèses. La syntaxe est

SUMIF(a, b, c)

où un est la plage de cellules que vous souhaitez évaluer avant de faire la somme, b est le critère de cette évaluation (il peut s'agir d'une valeur ou d'une référence de cellule), et c (facultatif) sont les cellules à ajouter si elles sont différentes de un.

Cette fois, nous avons trois choses à régler : la somme des salaires supérieurs à 40 000 £, le salaire total du département de service et la somme des primes pour le personnel dont le salaire est supérieur à 35 000 £.

Tout d’abord, pour calculer la somme des salaires supérieurs à 40 000 £, je dois taper la formule suivante dans la cellule D8 :

=SUMIF(C2:C6,">40000")

où C2:C6 fait référence aux salaires dans le tableau, et « >40 000 » indique à Excel de additionner uniquement les valeurs supérieures à ce montant.

Ensuite, je souhaite connaître le salaire total du département des services. Donc, dans la cellule C9, je vais taper

=SUMIF(B2:B6,"Services",C2:C6)

où B2:B6 fait référence à la colonne département, « Services » dit à Excel que je recherche spécifiquement des employés du département des services, et C2:C6 dit à Excel de additionner les salaires de ces employés.

Ma dernière tâche consiste à découvrir combien les salariés gagnant plus de 35 000 £ ont gagné en primes. Dans la cellule C10, je saisirai

=SUMIF(C2:C6,">35000",D2:D6)

où C2:C6 dit à Excel d'évaluer les salaires, « >35 000 » est le critère pour ces salaires, et D2:D6 dit à Excel de additionner les bonus des personnes qui remplissent le critère.

Excel dispose également d'une fonction SUMIFS, qui effectue le même processus mais pour plus d'un critère. Sa syntaxe est très différente de SUMIF :

SUMIFS(a,b,c,d,e)

où un est la plage de cellules à additionner, b est la première plage évaluée, c est le critère pour bet d et e sont le prochain appariement plage-critère (vous pouvez avoir jusqu'à 127 paires).

En utilisant le tableau ci-dessus, disons que je voulais résumer les primes pour le personnel gagnant plus de 45 000 £. Voici la formule que je taperais :

=SUMIFS(D2:D6,B2:B6,"Personnel",C2:C6,">45000")

Une fois que vous maîtrisez les fonctions détaillées ci-dessus, essayez la fonction XLOOKUP, qui vise à combler certaines des lacunes de RECHERCHEV en recherchant les valeurs à gauche et à droite de la colonne de valeurs de recherche sans que vous ayez à réorganiser vos données.