Apprenez l'incroyable fonction AGGREGATE dans Excel, un vaut 19 !

Dans l'article précédent « Partage de compétences pratiques sur Excel : Utiliser « Rechercher et remplacer » pour filtrer les données de date », nous avons découvert plusieurs opérations pratiques de « Rechercher et remplacer ». Aujourd’hui, nous allons parler d’une fonction statistique magique d’Excel, qui peut en réalité valoir 19. C’est tout simplement un artefact ! Récupérez-le rapidement.

La fonction que je souhaite vous présenter aujourd'hui s'appelle AGGREGATE. Bien qu'il s'agisse d'une fonction qui existe dans Excel 2010, peu de gens connaissent cette fonction. C'est une chose très regrettable, car la fonction AGGREGATE ne peut pas seulement implémenter les fonctions de 19 fonctions telles que SOMME, MOYENNE, COMPTE, GRAND, etc. . Et vous pouvez ignorer les lignes masquées, les valeurs d'erreur, les valeurs nulles, etc. Si la plage contient des valeurs d'erreur, des fonctions telles que SUM renverront une erreur, et la fonction AGGREGATE est très pratique dans ce cas.

Parlez simplement et ne pratiquez pas de faux mouvements, jetons un coup d'œil aux compétences d'AGGREGATE.

Nous utilisons un tableau de scores pour illustrer l'utilisation de base d'AGGREGATE. La source des données est comme indiqué dans la figure :

Chaque élève passe six tests et, en fonction des résultats, les cinq contenus statistiques de la zone bleue. sont obtenus. Je pense que cela sera utile à la plupart des étudiants. Pour la plupart des amis, il n'est pas difficile de remplir ce formulaire, ce n'est rien de plus que de maîtriser quelques fonctions de base : MOYENNE (score moyen), SOMME (score total), MAX. (score le plus élevé), MIN (score le plus bas) et COUNT (score réel) effectuent respectivement des statistiques sur les cinq contenus. Il se peut que de nouveaux amis ne comprennent pas les cinq fonctions mentionnées ci-dessus, donc juste au cas où, il vous suffit d'apprendre la fonction AGGREGATE pour réaliser les statistiques des données ci-dessus.

Le format de base d'AGGREGATE est : = AGGREGATE (fonction statistique, valeurs ignorées, zone de données). Voyons comment compléter les cinq contenus statistiques dans l'exemple. = AGGREGATE(统计功能，忽略哪些值，数据区域)，以下分别来看看如何完成例子中的五项统计内容。

一、统计平均分

当前表格平均分统计公式为：=AGGREGATE(1,,B2:G2)。在H2单元格中输入公式再整列填充公式即可获得各学生的平均分。

说明：当统计功能为1的时候，函数实现计算平均值的功能。本例中我们并不需要指定忽略统计的数据，因此第二参数可以省略（此处写了两个逗号，中间省略了一个参数），最后一个参数就是要计算的数据区域B2:G2，函数用法非常简单，那么结果是否正确呢？不妨使用AVERAGE函数来验证一下：

可以看到，结果完全一致！

接下来我们再看看如何用AGGREGATE函数统计总分。

二、统计总分

当前表格总分统计公式为：=AGGREGATE(9,,B2:G2)。在I2单元格中输入公式再整列填充公式即可获得各学生总分。

只需要将第一个参数改为9即可，因为9对应的就是求和功能。

说到这里，可能会有些朋友担心，第一个参数里1代表平均值， 9代表求和，这个函数一共有19个功能，会不会很难记住。

实际上完全不需要有这种担心，Excel为我们提供了非常智能的提醒功能，当我们输入函数之后，就有对应参数功能的选项：

只要对照这个提示，选择自己需要的功能即可。

三、统计最高分

了解这个功能以后，最后的三个统计项目就很容易完成了，最高分肯定是选择4，因此J2单元格公式为：=AGGREGATE(4,,B2:G2)

四、统计最低分

最低分选择5，K2单元格公式为：=AGGREGATE(5,,B2:G2)

1. Score statistique moyen

La formule statistique actuelle du score moyen du tableau est : =AGGREGATE(1,,B2:G2). Entrez la formule dans la cellule H2 et remplissez toute la colonne avec la formule pour obtenir la note moyenne de chaque élève.

🎜Description : Lorsque la fonction statistique est 1, la fonction implémente la fonction de calcul de la moyenne. Dans cet exemple, nous n'avons pas besoin de spécifier les données pour ignorer les statistiques, donc le deuxième paramètre peut être omis (deux virgules sont écrites ici et un paramètre est omis au milieu. Le dernier paramètre est la zone de données B2:G2). à calculer. Utilisation de la fonction Très simple, le résultat est-il donc correct ? Autant utiliser la fonction MOYENNE pour le vérifier : 🎜🎜🎜🎜Vous pouvez voir que les résultats sont exactement les mêmes ! 🎜🎜Voyons ensuite comment utiliser la fonction AGGREGATE pour compter le score total. 🎜🎜🎜🎜2. Score total statistique 🎜🎜🎜🎜La formule statistique du score total du tableau actuel est : =AGGREGATE(9,,B2:G2). Entrez la formule dans la cellule I2 et remplissez toute la colonne avec la formule pour obtenir le score total de chaque élève. 🎜🎜🎜🎜Uniquement Vous devez changer le premier paramètre en 9, car 9 correspond à la fonction de sommation. 🎜🎜En parlant de cela, certains amis peuvent s'inquiéter du fait que 1 dans le premier paramètre représente la moyenne et 9 représente la somme. Cette fonction a un total de 19 fonctions, elle sera donc difficile à retenir. 🎜🎜En fait, il n'y a pas du tout besoin de s'inquiéter à ce sujet. Excel nous fournit une fonction de rappel très intelligente. Après avoir entré la fonction, il existe des options pour la fonction paramètre correspondante : 🎜🎜🎜🎜Suivez simplement cette invite et sélectionnez la fonction dont vous avez besoin. . 🎜🎜🎜🎜3. Statistiques du score le plus élevé🎜🎜🎜🎜Après avoir compris cette fonction, les trois derniers éléments statistiques peuvent être facilement complétés. Le score le plus élevé doit être 4, donc la formule de la cellule J2 est : =AGGREGATE(. 4,,B2:G2)🎜🎜🎜🎜🎜🎜4. Statistiques du score le plus bas🎜🎜🎜🎜Sélectionnez 5 pour le score le plus bas. La formule dans la cellule K2 est : =AGGREGATE(5,,B2:G2). 🎜 🎜🎜🎜🎜🎜🎜5. Statistiques sujets de référence réels🎜🎜🎜

Le compte de référence réel est le nombre de nombres dans la zone de données statistiques. Utilisez la fonction COUNT et sélectionnez 2, la formule est donc : =AGGREGATE(2,,B2:G2)=AGGREGATE(2,,B2:G2)

好了，通过以上五个例子，朋友们对于AGGREGATE的基本用法应该有所掌握，虽然说只用了一个函数就完成了五个函数的工作，相比之前要分别使用五个函数来完成工作提高了一定的效率，但每个公式还是要修改一下才能用。如果能够使用一个公式右拉下拉的话，那才爽呢。（有同感的朋友可以在文末留言哦）

六、五种统计一步到位

对于有这种想法的朋友，应该提出表扬，毕竟我们学习Excel的函数公式，不仅仅是为了完成工作，更加希望能够提高效率。那么有没有可能使用一个公式右拉下拉来完成例子中的五项统计呢？答案是肯定的：有！不过要用到一对函数组合，那就是choose和column。

在揭晓公式之前，先对问题进行简单的分析，在我们使用AGGREGATE完成五项数据统计的公式中，只有第一参数也就是统计方式在发生变化，依次为：1、9、4、5、2。如果要想使用一个公式右拉下拉来完成的话，就得让公式在右拉时第一参数按照这个顺序来进行变化（下拉时不需要变化，因为统计方式相同）。

通常要使用公式右拉得到顺序变化的数据时就会用到column这个函数：

Column这个函数的作用是得到参数对应的列号，例如column（a1）就得到a1这个单元格的列号也就是1，右拉时由于a1会变成b1、c1……，公式结果就会按照1、2、3……这个顺序变化。

在本例中，我们需要得到的并不是一个很有规律的数列，而是1、9、4、5、2这样一个无序的数列，这时候就要用到choose函数来实现：

Choose函数的基本格式为：=choose(选择指数，值1，值2，值3……)

Choose函数根据第一个参数的数字来返回参数列表中的值。例如上图，当第一参数为1时，就返回参数列表中的第1个值“1”；当第一参数为2时，就返回参数列表中的第2个值“9”，以此类推，使用column作为choose的第一参数，就可以返回指定的序列了。

以上是对choose和column这对函数组合的说明，现在回到我们的问题，可以用来右拉下拉的这个公式就是：=AGGREGATE(CHOOSE(COLUMN(A1),1,9,4,5,2),,$B2:$G2)

可能有些新手还是会觉得晕乎乎的，这很正常，相信通过持续地学习，你就可以对这种公式运用自如了。

七、第一参数功能集锦

通过以上介绍，可以看到当我们合理运用了AGGREGATE函数之后，工作效率成倍增长。这个函数的第一参数到底有哪19种功能呢，通过下面这个对照表可以一目了然：

实际上比较常用的就是那么几种。

八、第二参数功能集锦

接下来我们再来看看第二参数又是什么功能，还是通过一个对照表来直观地了解：

1.忽略空值

以下通过两个例子看看如何使用第二参数来选择忽略的内容：=AGGREGATE(9,1,B2:B15)

D'accord, via ce qui précède Cinq exemples, les amis devraient avoir une compréhension de l'utilisation de base d'AGGREGATE. Bien qu'une seule fonction soit utilisée pour terminer le travail de cinq fonctions, cela a amélioré une certaine efficacité par rapport à l'utilisation précédente de cinq fonctions pour terminer le travail, mais chacune. La formule doit encore être modifiée avant de pouvoir être utilisée. Ce serait formidable si vous pouviez utiliser une formule pour dérouler vers la droite. (Les amis qui ressentent la même chose peuvent laisser un message à la fin de l'article)

Six ou cinq types de statistiques en une seule étape strong>

Pour ceux qui ont ce genre d'amis qui ont des idées, il faut les féliciter. Après tout, nous apprenons les formules de fonctions Excel non seulement pour terminer le travail, mais aussi pour améliorer l'efficacité. Alors, est-il possible d'utiliser une formule pour dérouler vers la droite pour compléter les cinq statistiques de l'exemple ? La réponse est oui : oui ! Cependant, deux combinaisons de fonctions sont requises, à savoir choisir et colonne.

Avant de révéler la formule, effectuons une analyse simple du problème. Dans la formule que nous utilisons AGGREGATE pour compléter cinq statistiques de données, seul le premier paramètre, qui est la méthode statistique, change, dans l'ordre : 1, 9, 4. , 5 ,2. Si vous souhaitez utiliser une formule pour dérouler vers la droite, vous devez modifier le premier paramètre de la formule dans cet ordre lorsque vous tirez vers la droite (aucune modification n'est nécessaire lorsque vous tirez vers le bas, car la méthode statistique est la même. ). 🎜🎜Habituellement, lorsque vous devez utiliser une formule pour tirer vers la droite pour obtenir des données changeant de manière séquentielle, vous utiliserez la fonction de colonne : 🎜🎜🎜🎜Colonne Cette fonction permet d'obtenir le numéro de colonne correspondant au paramètre. Par exemple, la colonne (a1) obtiendra le numéro de colonne de la cellule a1, qui est 1. En tirant vers la droite, puisque a1 deviendra b1, c1..., les résultats de la formule changeront dans l'ordre 1, 2, 3.... 🎜🎜Dans cet exemple, ce que nous devons obtenir n'est pas une séquence très régulière, mais une séquence non ordonnée de 1, 9, 4, 5, 2. A ce stade, nous devons utiliser la fonction choisir pour obtenir : 🎜 🎜🎜🎜Choisissez la fonction La base le format est : =choose (choisissez l'index, la valeur 1, la valeur 2, la valeur 3...)🎜🎜La fonction Choisir renvoie la valeur dans la liste des paramètres en fonction du numéro du premier paramètre. Par exemple, dans la figure ci-dessus, lorsque le premier paramètre est 1, la première valeur « 1 » dans la liste des paramètres est renvoyée ; lorsque le premier paramètre est 2, la deuxième valeur « 9 » dans la liste des paramètres est renvoyée ; , en utilisant column comme premier paramètre de Choose, vous pouvez renvoyer la séquence spécifiée. 🎜🎜Ce qui précède est une explication de la combinaison de fonctions de choisir et de colonne. Revenons maintenant à notre problème, la formule qui peut être utilisée pour le menu déroulant droit est : =AGGREGATE(CHOOSE(COLUMN(A1),1, 9, 4,5,2),,$B2:$G2)🎜🎜🎜🎜Certains novices peuvent encore avoir des vertiges, ce qui est normal. Je crois que grâce à l'apprentissage continu, vous pouvez utiliser cette formule librement. 🎜🎜7. Première collection de fonctions de paramètres🎜🎜Grâce à l'introduction ci-dessus, vous pouvez voir cela lorsque nous utilisons correctement la fonction AGGREGATE. , l'efficacité du travail augmente de façon exponentielle. Quelles sont les 19 fonctions du premier paramètre de cette fonction ? Vous pouvez le voir clairement à travers le tableau de comparaison suivant : 🎜🎜🎜🎜En fait, il n'y en a que quelques-uns couramment utilisés. 🎜🎜8. Collection de fonctions du deuxième paramètre🎜🎜Jetons ensuite un œil à ce qu'est le deuxième paramètre, ou obtenons un visuel. compréhension à travers un tableau comparatif : 🎜🎜🎜🎜1. Ignorer les valeurs nulles🎜🎜Ce qui suit utilise deux exemples pour voir comment utiliser le deuxième paramètre pour sélectionner le contenu ignoré : =AGGREGATE(9,1, B2:B15 )🎜🎜🎜🎜🎜Sélectionnez 9 pour le premier paramètre, qui représente la somme, et sélectionnez 1 pour le deuxième paramètre, qui représente l'ignorance des lignes masquées. Lorsque toutes les données sont affichées, utilisez la fonction AGGREGATE pour additionner et la SOMME. fonction Les résultats sont cohérents (le score total de la ligne 16 est additionné à l'aide de la fonction SUM. Lorsqu'on masque certaines lignes de données, on peut voir la différence : 🎜🎜🎜🎜).

Après avoir masqué les lignes 4, 8 et 11, la formule =AGGREGATE(9,1,B2:B15) résume uniquement les données actuellement affichées.

En parlant de cela, les étudiants qui ont appris la fonction SOUS-TOTAL penseront certainement que SOUS-TOTAL a également une telle fonction. Cependant, la fonction AGGREGATE apparue aujourd'hui est plus puissante que la fonction SUBTOTAL, car SUBTOTAL ne peut pas gérer les valeurs d'erreur et les résumés récapitulatifs imbriqués, mais AGGREGATE peut toujours la gérer.

2. Ignorez la valeur d'erreur

Le dernier exemple aujourd'hui, voyons ce qui se passe lorsqu'une valeur d'erreur est rencontrée :

Comme le montre la figure ci-dessus, les scores chinois de chaque élève sont obtenus à partir du fonction vlookup utilisant la fonction vlookup Obtenue à partir de la table de scores (cette fonction a déjà été abordée dans un tutoriel, si vous ne la connaissez pas encore, vous pouvez cliquer sur le lien pour en savoir plus : insérer un lien). Lorsque le nom n'est pas dans le tableau des scores, une valeur d'erreur sera obtenue, comme Li Si et Zhang San. À ce stade, peu importe que nous utilisions la fonction SOMME ou la fonction SOUS-TOTAL, nous ne pouvons pas obtenir le score total correct du. Score chinois. Seul AGGREGATE peut ignorer la valeur d'erreur pour obtenir un résultat correct. Bien sûr, vous pouvez utiliser iferror et d'autres fonctions pour traiter, puis utiliser SUM pour additionner, mais cela ne masque pas la puissance d'AGGREGATE.

19 types de fonctions statistiques plus 7 types d'éléments ignorés Ce genre de fonction d'intégration incroyable n'est vraiment pas comparable aux fonctions ordinaires ! AGGREGATE est le roi bien mérité des fonctions statistiques, collectionnez-le maintenant !

Recommandations d'apprentissage associées : Tutoriel Excel

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!