Trois fonctions pratiques pour l'extraction des données Excel et le résumé

Supposons que vous ayez un grand classeur avec des tables magnifiquement formatées, filtrées et triées. Vous pourriez penser que le travail est terminé, mais en réalité, Excel attend que vous fassiez plus sur ces tables, désireux de vous aider à tirer le meilleur parti du travail acharné que vous avez fait jusqu'à présent.

Cet article introduira trois fonctions ou combinaisons de fonctions que j'utilise souvent qui peuvent être utilisées pour extraire ou résumer des informations dans les tables Excel.

vlookup et hlookup

vlookup et hlookup sont tous deux utilisés pour trouver et récupérer des valeurs à des emplacements spécifiques dans un tableau.

• Vlookup repose sur une table de données verticale et recherche la première colonne (verticale) dans le tableau.
• hlookup repose sur la table de données horizontale et recherche la première ligne (horizontale) dans le tableau.

vlookup

Ici, j'ai une liste des scores de test et les notes requises pour chaque note (nous l'appelons Tableau 1). J'ai également une table avec des notes d'étudiants (appelée tableau 2 à partir de ce point). Je veux qu'Excel utilise les informations du tableau 1 pour compléter les colonnes manquantes du tableau 2.

J'utiliserai Vlookup car je veux Excel pour rechercher les valeurs dans la première colonne du tableau 1 pour retourner les notes de chaque élève du tableau 2. La syntaxe de la fonction Vlookup est la suivante:

<code>=VLOOKUP(a,b,c,d)</code>

de:

• a est la valeur à rechercher (dans l'exemple ci-dessus, c'est-à-dire la valeur de la colonne E),
• b est un 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 de ce tableau (je veux qu'il renvoie le niveau, il s'agit donc de la deuxième colonne du tableau 1), et
• d est une condition facultative qui indique à Excel si les valeurs de recherche approximatives ("true") ou les valeurs de recherche exactes ("false"). Si elle est laissée vide, la valeur par défaut est vraie.

Ainsi, dans mon cas, j'entrerais cette formule dans la cellule F2 pour calculer les notes de Tom, puis j'utiliserais le automatique pour trouver d'autres grades dans le tableau:

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

J'ai utilisé le symbole $ pour créer une référence absolue pour la valeur ci-dessus b parce que je veux Excel pour utiliser en continu les cellules A1 à B9 pour trouver la valeur. J'ai également utilisé "vrai" pour la valeur d parce que le tableau de limite de score contient des plages, pas le rang attribué à un seul score.

hlookup

Ici, nous avons les mêmes informations de limite de niveau, mais cette fois, il est affiché horizontalement. Cela signifie que les données que nous voulons récupérer se trouvent dans la deuxième ligne de la table limite.

La syntaxe de la fonction hlookup est similaire à celle de Vlookup:

<code>=HLOOKUP(a,b,c,d)</code>

de:

• a est la valeur à trouver (dans cet exemple, la valeur de la colonne C),
• b est une référence absolue à la cellule contenant la valeur de recherche (dans ce cas, elle est A1 à I2),
• c est le numéro de ligne dans le tableau (je veux qu'il renvoie le niveau, donc c'est la deuxième ligne), et
• d (facultatif) est "vrai" (valeur approximative) ou "false" (valeur exacte).

donc je vais entrer cette formule dans la cellule C5 pour calculer les grades de Tom, puis utiliser le automatique pour trouver d'autres grades dans le tableau:

<code>=VLOOKUP(a,b,c,d)</code>

Index et correspondre

Un autre moyen efficace de trouver et de récupérer les valeurs est l'index et la correspondance, en particulier lorsqu'il est utilisé ensemble. L'index recherche et renvoie la valeur de la position définie, tandis que Match recherche et renvoie le bit de la valeur. Ensemble, ils peuvent implémenter la récupération dynamique des données.

grammaire unique

Avant de regarder ces fonctions ensemble, voyons-les brièvement individuellement.

La syntaxe de l'index est

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

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

sur la base de cela,

<code>=HLOOKUP(a,b,c,d)</code>

évaluera les cellules B2 à D8 et renverra les valeurs dans la quatrième rangée et la deuxième colonne de cette plage.

Pour le match, nous suivons

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

où x est la valeur que nous recherchons, y est la plage de valeurs que nous recherchons, et z (facultatif) est le type correspondant.

sur la base de cela,

<code>INDEX(a,b,c)</code>

me dira où se trouve le numéro 5 de la plage B2 à B8, tandis que 0 indique à Excel d'effectuer une correspondance exacte.

Utilisation en combinaison

Dans cet exemple, je veux Excel pour me dire le nombre de buts qu'un joueur spécifié marque dans un mois donné. Plus précisément, je veux savoir combien de buts qu'un joueur C a marqués au cours du troisième mois, mais je vais créer cette formule afin que je puisse changer ces conditions à tout moment.

Pour ce faire, j'ai besoin d'Excel pour déterminer la position du lecteur C dans le tableau, puis me dire la valeur dans la troisième colonne des données.

Dans Cell G4, je vais commencer par la fonction d'index car je veux Excel pour rechercher mes données d'origine et renvoyer une valeur. Je dirai ensuite à Excel où chercher ces données.

<code>INDEX(B2:D8,4,2)</code>

La partie suivante de la syntaxe d'index est le numéro de ligne, qui variera en fonction du lecteur que je déclare dans Cell G2. Par exemple, si je veux trouver le joueur A, ce sera la première ligne. Pour ce faire, je vais commencer la fonction de match car je veux Excel pour correspondre au lecteur dans la cellule G2 à la colonne de la cellule correspondante dans le joueur (A2: A8) et déterminer la ligne qu'elle est située. J'ai également ajouté les derniers 0 parce que je veux Excel pour renvoyer la récupération exacte.

<code>=VLOOKUP(a,b,c,d)</code>

Maintenant que j'ai raconté le numéro de ligne de fonction Index Excel, je dois le faire avec le numéro de colonne. Dans mon exemple, le numéro de colonne représente le numéro de mois que j'ai tapé dans Cell G3.

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

Lorsque j'ai appuyé sur la touche Entrée, Excel m'a correctement dit que le joueur C avait marqué cinq buts au cours du troisième mois.

Maintenant, je peux modifier n'importe quelle valeur dans ma table de recherche pour trouver le total de n'importe quel joueur dans un mois.

counttif et sumif

Comme vous pouvez le voir à partir de leurs noms, ces deux fonctions comptent et sommement des valeurs en fonction des conditions que vous définissez. Tout ce qui n'est pas inclus dans votre état ne sera pas ajouté ou compté, même s'il se trouve dans la portée que vous avez spécifiée.

counttif

COUNFIF COUNTES Les cellules contenant des conditions spécifiques. La syntaxe est

<code>=HLOOKUP(a,b,c,d)</code>

où a est la plage que vous souhaitez compter, et b est la condition de comptage.

De même, si je veux inclure plusieurs conditions, j'utiliserai les commifs:

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

où a et b sont le premier couple de la condition de plage, et c et d sont le jumelage de la deuxième plage (vous (vous peut avoir jusqu'à 127 paires).

Si une condition est un symbole de texte ou logique ou mathématique, il doit être enfermé en doubles citations.

Dans mon tableau de salaire ci-dessous, je souhaite calculer le nombre de personnes gagnant plus de 40 000 ￡ et le nombre de personnes qui re?oivent plus de 1 000 ￡ de bonus séparément.

Pour calculer le nombre d'employés qui ont un salaire de plus de 40 000 ￡, j'ai besoin de saisir cette formule dans la cellule D8:

<code>INDEX(a,b,c)</code>

où C2: C6 est la gamme de salaire, "& gt; 40000" est la condition.

Pour calculer le nombre de membres du personnel qui re?oivent plus de 1 000 ￡ de bonus, j'utiliserai les commifs car j'ai deux conditions.

<code>INDEX(B2:D8,4,2)</code>

B2: B6, "Services" La pièce est le premier couple conditionnel, d2: d6, "& gt; 1000" est le deuxième.

Même si je sépare des milliers de chiffres avec des virgules dans mon tableau ci-dessus, je n'inclue pas ces virgules dans la formule, car les virgules ont des fonctions différentes ici.

sumif

Sumif résume les cellules selon les conditions que vous définissez. Il fonctionne de manière similaire à Counif, mais contient plus de paramètres entre parenthèses. La syntaxe est

<code>MATCH(x,y,z)</code>

où a est la plage de cellules que vous souhaitez évaluer avant de demander, b est la condition pour cette évaluation (cela peut être une valeur ou une référence de cellule), c (facultatif) est la cellule à ajouter si elle est différente de a .

Cette fois, nous devons calculer trois choses: le salaire total de plus de 40 000 ￡, le salaire total du service des services et le bonus total pour les employés qui ont plus de 35 000 ￡.

Tout d'abord, pour calculer le salaire total de plus de 40 000 ￡, je dois saisir la formule suivante dans la cellule D8:

<code>=VLOOKUP(a,b,c,d)</code>

où c2: c6 cite le salaire du tableau, "& gt; 40000" dit à Excel de résumer que des valeurs dépassant ce montant.

Ensuite, je veux découvrir le salaire total du service des services. Donc, dans la cellule C9, je taperai

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

où b2: b6 cite les colonnes du département, "services" Dites à Excel que je cherche des employés dans le service des services, C2: C6 Tell Excel sur les salaires de ces employés sont additionnés.

Ma dernière tache était de savoir combien de bonus les employés gagnant plus de 35 000 ￡ ont re?u. Dans la cellule C10, je taperai

<code>=HLOOKUP(a,b,c,d)</code>

où c2: c6 dit à Excel d'évaluer les salaires, "& gt; 35000" sont les conditions de ces salaires, d2: d6 dire à Excel de remplir de remplir Les conditions de somme bonus personnelle.

Excel a également la fonction SUMIFS, qui effectue la même procédure, mais pour plusieurs conditions. Il est très différent de la syntaxe de Sumif:

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

où a est la plage de cellules à ajouter, b est la première plage à évaluer, c est b , d et e sont le jumelage des conditions de plage suivante (vous pouvez avoir jusqu'à 127 paires).

En utilisant le tableau ci-dessus, disons que je veux résumer les bonus pour les employés qui gagnent plus de 45 000 ￡. Voici la formule que je vais entrer:

<code>INDEX(a,b,c)</code>

---

Après avoir ma?trisé les fonctions ci-dessus, essayez d'utiliser la fonction xlookup, qui est con?ue pour résoudre certains des inconvénients de Vlookup en trouvant les valeurs à gauche et à droite de la colonne de valeur de recherche sans réorganiser les données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!