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 :
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)
如下图，在筛选后的数据区域中包含有错误值，如何对可见单元格进行统计呢？
公式为：
=AGGREGATE(9,7,B6:B18)
第一参数使用9，表示求和，第二参数使用7，表示忽略隐藏行和错误值。
如下图，A3:B14单元格区域中是筛选后的的数据，要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。
只要一个公式就够了：
=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)
注意是区域数组公式，先选取c17:d22区域，然后在编辑栏写上公式，最后按ctrl+shift+enter三键录入。
=AGGREGATE(9,3,A4:A)*2-AGGREGATE(9,7,A4:A)
除了向下求和的方向外还有隐藏和错误值，这是subtotal+sum(if)都无法实现的统计效果
(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)
这个函数提早五年就实现了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个最大值了。
如果想要一对多查询，很多人想到的是INDEX+SAMLL+IF函数的三键客组合。其实，用aggregate函数替代也是能实现的。
如下图，要提取出二车间的所有工号，可以使用以下公式：
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(:)/(A:A=D),ROW(A1))),"")
=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 decette formule est fondamentalement la même que celle de la cinquième formule.
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函数，忽略内存数组中的错误值，计算出其中的第一个最小值。
如下图所示，要同时统计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)
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!
Comparez les similitudes et les différences entre deux colonnes de données dans Excel
Marquage de couleur du filtre en double Excel
Comment copier un tableau Excel pour lui donner la même taille que l'original
Barre oblique du tableau Excel divisée en deux
L'en-tête diagonal d'Excel est divisé en deux
Méthode de saisie de référence absolue
Java exporter Excel
La valeur d'entrée Excel est illégale
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
