Exemple de résumé de huit utilisations de la fonction AGGREGATE dans Excel

Cet article vous apporte des connaissances pertinentes sur excel, qui présente principalement le contenu pertinent sur la fonction AGGREGATE. L'utilisation de cette fonction est similaire à la fonction SUBTOTAL, mais sa fonction est plus puissante que la fonction SUBTOTAL. écoutez, j'espère que cela aidera tout le monde.

Recommandations d'apprentissage associées : Tutoriel Excel

La fonction AGGREGATE est similaire à la fonction SUBTOTAL, mais elle est plus puissante que la fonction SUBTOTAL. Elle peut non seulement implémenter 19 fonctions telles que SUM, AVERAGE, COUNT, LARGE. , MAX, etc., et peut également ignorer les lignes masquées, les valeurs d'erreur, les valeurs nulles, etc., et prend en charge les tableaux constants.

Le premier paramètre de cette fonction est un nombre compris entre 1 et 19, permettant de préciser la méthode d'agrégation à utiliser :

Le deuxième paramètre est un nombre compris entre 0 et 7, précisé dans la zone de calcul Quels types de les valeurs doivent être ignorées :

Parlons ensuite de quelques utilisations typiques de cette fonction :

1. Plusieurs zones discontinues ignorent les valeurs d'erreur et les additionnent directement

La puissance de cette fonction est que 2. Des paramètres peuvent être spécifiés pour ignorer les valeurs d'erreur et les compter directement. Comme le montre la figure ci-dessous, la zone bleue contient différentes valeurs d'erreur.

La formule est :

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

2、筛选状态下忽略错误值

如下图，在筛选后的数据区域中包含有错误值，如何对可见单元格进行统计呢？

公式为：

=AGGREGATE(9,7,B6:B18)

第一参数使用9，表示求和，第二参数使用7，表示忽略隐藏行和错误值。

3、一个公式解决多种统计效果

如下图，A3:B14单元格区域中是筛选后的的数据，要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。

只要一个公式就够了：

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

注意是区域数组公式，先选取c17:d22区域，然后在编辑栏写上公式，最后按ctrl+shift+enter三键录入。

4、向上求和你们都会，哪怕是筛选下的，向下呢？

=AGGREGATE(9,3,A4:A)*2-AGGREGATE(9,7,A4:A)

除了向下求和的方向外还有隐藏和错误值，这是subtotal+sum(if)都无法实现的统计效果

(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)

5、这条开始才是重点-条件极值统计

这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果，而且不需要三键。

如下图，要计算1车间对应的最小值，公式为：

=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)

公式中的第一参数使用15，表示使用SMALL函数，第二参数使用6，表示忽略错误值。要统计的区域是B4:B15/(A4:A15=”1车间”)

A4:A15=”1车间”部分，先对比A列的车间是不是等于指定的条件。如果A4:A15单元格区域中等于”1车间”，就返回逻辑值TRUE，否则返回逻辑值FALSE。然后再用B4:B15除以这组内存数组，结果为：

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;19;47}

最后，AGGREGATE函数忽略里面的错误值，得到第一个最小值。

如果要计算1车间对应的第三个最小值，只需要将最后的1，变成3就好了。

如果要计算1车间对应的最大值，咱们可以修改一下第一参数，使用14，就是第k个最大值了。

6、 一对多查询

如果想要一对多查询，很多人想到的是INDEX+SAMLL+IF函数的三键客组合。其实，用aggregate函数替代也是能实现的。

如下图，要提取出二车间的所有工号，可以使用以下公式：

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(:)/(A:A=D),ROW(A1))),"")

2. Ignorer les valeurs d'erreur dans l'état de filtrage🎜🎜Comme indiqué ci-dessous, le données filtrées La plage contient des valeurs d'erreur. Comment compter les cellules visibles ? 🎜🎜La formule est : 🎜🎜=AGGREGATE(9,7,B6:B18)🎜🎜🎜🎜Le premier paramètre utilise 9, ce qui signifie additionner, et le deuxième paramètre utilise 7, ce qui signifie ignorer les lignes masquées et les valeurs d'erreur. . 🎜🎜3. Une formule résout plusieurs effets statistiques🎜🎜Comme indiqué ci-dessous, les données filtrées se trouvent dans la zone de cellule A3:B14. Le maximum, le minimum, la moyenne, la somme, le nombre et la somme de la zone visible et toutes les données doivent être comptées. respectivement. 🎜🎜Une seule formule suffit : 🎜🎜=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)🎜🎜🎜🎜Notez qu'il s'agit d'un tableau de zones formule. Sélectionnez d’abord la zone c17:d22, puis écrivez la formule dans la barre d’édition, et enfin appuyez sur ctrl+shift+enter pour entrer. 🎜🎜4. Vous savez tous résumer vers le haut, même si c'est filtré, qu'en est-il vers le bas ? 🎜🎜=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)🎜🎜🎜🎜En plus du sens de sommation vers le bas, il existe également des valeurs cachées et d'erreur, ce qui est un effet statistique qui ne peut pas être obtenu par sous-total + somme (if) 🎜🎜 (La méthode de saisie consiste à sélectionner la zone pour positionner la valeur nulle, puis à écrire la formule dans la barre d'édition ctrl + entrée pour remplir les lots) 🎜🎜5. Le début est le point clé - Statistiques de valeurs extrêmes conditionnelles🎜🎜Cette fonction a réalisé les effets statistiques des fonctions maxifs et minifs qui n'étaient disponibles qu'en 2016 il y a cinq ans et ne nécessite pas trois clés. 🎜🎜Comme indiqué ci-dessous, pour calculer la valeur minimale correspondant à 1 atelier, la formule est : 🎜🎜=AGGREGATE(15,6,B4:B15/(A4:A15="1 atelier"),1) 🎜🎜<img src="https://img.php.cn/upload/image/467/651/536/1652758471757546.jpg" title="1652758471757546.jpg" alt="Exemple de résumé de huit utilisations de la fonction AGGREGATE dans Excel"> 🎜🎜Formule Utilisez 15 pour le premier paramètre, ce qui signifie utiliser la fonction SMALL, et utilisez 6 pour le deuxième paramètre, ce qui signifie ignorer la valeur d'erreur. La zone à compter est la section B4:B15/(A4:A15="1 atelier")🎜🎜A4:A15="1 atelier" Comparez d'abord si les ateliers de la colonne A sont égaux aux conditions spécifiées. Si la plage de cellules A4:A15 est égale à "1 atelier", la valeur logique VRAI est renvoyée, sinon la valeur logique FAUX est renvoyée. Divisez ensuite ce tableau mémoire par B4:B15, le résultat est : 🎜🎜{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;# DIV/ 0!;19;47}🎜🎜Enfin, la fonction AGGREGATE ignore les valeurs d'erreur à l'intérieur et obtient la première valeur minimale. 🎜🎜Si vous souhaitez calculer la troisième valeur minimale correspondant à l'atelier 1, il vous suffit de changer le dernier 1 en 3. 🎜🎜Si on veut calculer la valeur maximale correspondant à l'atelier 1, on peut modifier le premier paramètre et utiliser 14, qui est la kème valeur maximale. 🎜🎜6. Requête un-à-plusieurs🎜🎜Si vous voulez une requête un-à-plusieurs, beaucoup de gens pensent à la combinaison à trois touches de la fonction INDEX+SAMLL+IF. En fait, il est également possible de le remplacer par une fonction d'agrégation. 🎜🎜Comme indiqué ci-dessous, pour extraire tous les numéros de travail du deuxième atelier, vous pouvez utiliser la formule suivante : 🎜🎜<code>=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12) /(A$3 :A$12=D$3),ROW(A1))),"")🎜

L'idée de​​cette formule est fondamentalement la même que celle de la cinquième formule.

7. Comptez la valeur maximale dans la même cellule

Comme le montre l'image ci-dessous, le statut d'évaluation de plusieurs personnes dans la colonne B est écrit dans la même cellule et la valeur maximale doit être comptée. La formule est :

=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)=AGGREGATE(14,6,--MID(B4,ROW(:),COLUMN(A:AZ)),1)

公式中的MID(B4,ROW(:),COLUMN(A:AZ))部分，使用MID函数，依次从第1~50个字符处开始，各提取长度为1~50的字符串，得到一个巨长的内存数组。再使用两个负号，把内存数组中的文本变成错误值，数值仍然是其本身的值。

最后使用AGGREGATE函数，忽略内存数组中的错误值，计算出其中的第一个最小值。

8、同时统计指定条件的最大最小值

如下图所示，要同时统计1车间对应的最大和最小值。

先同时选中F4:G4单元格，编辑栏输入以下公式，按Ctrl+Shift+回车。

=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

MID(B4,ROW() dans la formule $1:$50), COLUMN(A:AZ)), utilisez la fonction MID pour extraire des chaînes d'une longueur de 1 à 50, du 1er au 50ème caractère, et obtenez une énorme matrice mémoire. Utilisez ensuite deux signes négatifs pour transformer le texte de la matrice mémoire en une valeur d'erreur, et la valeur est toujours sa propre valeur.

Enfin, utilisez la fonction AGGREGATE pour ignorer les valeurs d'erreur dans la matrice mémoire et calculer la première valeur minimale parmi elles.

8. Comptez simultanément les valeurs maximales et minimales des conditions spécifiées

Comme le montre la figure ci-dessous, les valeurs maximales et minimales correspondant à 1 atelier doivent être comptées simultanément.

Sélectionnez d'abord les cellules F4:G4 en même temps, entrez la formule suivante dans la barre d'édition et appuyez sur Ctrl+Maj+Entrée. =AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

🎜🎜🎜🎜AGGREGATE utilise le tableau constant {16,15 comme premier paramètre }, indiquant les règles de calcul d'utilisation de la valeur maximale et de la valeur minimale respectivement. 🎜🎜Le résultat final est également une matrice mémoire, donc deux cellules doivent être sélectionnées pour la saisie en même temps. 🎜🎜La caractéristique de cette fonction est que lorsque le premier paramètre est 14~19, le quatrième paramètre peut être utilisé. À ce stade, le quatrième paramètre prend en charge les tableaux, il peut donc être utilisé dans diverses applications pour remplacer la valeur d'erreur qui ne peut pas être utilisée. être ignoré directement. 🎜🎜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!