L'apprentissage des fonctions Excel est une fonction contre dix SOUS-TOTAL !

Les amis qui débutent aiment toujours se plaindre : « Excel a trop de fonctions et je ne m'en souviens pas toujours. Existe-t-il une fonction qui peut résumer les fonctions de nombreuses fonctions ? ! La fonction la plus importante de la fonction dont nous allons parler aujourd'hui est qu'elle peut remplacer 11 fonctions. De plus, il peut également modifier le résultat final du calcul en fonction de différents résultats de filtrage ! Et ça ? Cela n'a-t-il pas l'air génial ? Sans plus attendre, jetons un coup d’œil avec l’éditeur !

Ignorez la somme des lignes filtrées

"Professeur Miao, j'ai rencontré un problème." Xiaobai est venue me voir dès qu'elle est arrivée au travail. Elle a dit : "J'ai un tableau avec des totaux. Lors de l'impression, Je ne veux pas imprimer un certain contenu. Certaines personnes sont masquées par le filtrage, mais il est difficile de changer la zone de sommation à chaque fois. "

J'ai dit : "C'est facile, changez simplement la fonction de sommation."

Xiaobai : "De quel genre de fonction s'agit-il ? Je ne l'ai jamais utilisé."

J'ai dit : "Cette fonction est beaucoup plus puissante que la fonction SUM. Elle peut gérer plusieurs scénarios de sommation ! Xiaobai : " Tellement puissant, alors tu dois m'apprendre."

"Alors écoute-moi et te dis en détail ~ Tout d'abord, résolvons le problème avec ton formulaire." Sur ce, j'ai ouvert son formulaire, comme le montre la figure.

"Maintenant, votre tableau utilise la fonction SOMME pour la sommation. Remplaçons-la par la fonction SOUS-TOTAL. Vous pouvez y jeter un œil à nouveau." Après cela, j'ai entré la formule dans la cellule A7.

=SUBTOTAL(9,A2:A6)

=SUBTOTAL(9,A2:A6)

“真的变了！”接着小白又筛选了一些别的行，发现都可以得到她想要的结果，十分高兴。不过随后她又发现了新大陆，“那这个9是什么意思呢？”

我：“这个9呀，表示忽略未筛选出的数据，仅对筛选后的结果进行求和”。

小白：“听你这说法，还有其他数字代表其他的含义咯？”

我：“当然，那我就再跟你说说其他数字的含义吧！”

忽略隐藏行求和

我们有时候会碰到这种情况，有一列数字，需要隐藏几个不进行运算的数据。如果是直接使用SUM，是无法得到正确结果的，如图所示。

即使用上刚学的SUBTOTAL函数的参数“9”，也是无法实现的，如图所示。

这时候我们就要考虑换一个参数了。

下面有请参数“109”，登场！

公式：=SUBTOTAL(109,A1:A5)

如下图所示，将SUBTOTAL函数第一参数变为“109”后，就能轻松得到忽略隐藏行后的求和结果！如图所示。

参数“109”的作用是对可见数值进行求和，它既可以对隐藏后的数据求和，也可以对筛选后的数据求和。而参数“9”只能使用在筛选行，对隐藏行则无效。

SUBTOTAL其他参数的应用

SUBTOTAL不仅仅局限在求和领域，平均值、最大值、标准差、方差，都能求，只需改变它的第一参数即可。例如，现在我们要统计忽略隐藏行的最大值，如图6所示。

公式：=SUBTOTAL(104,A1:A5)

"C'est vraiment changé !" Ensuite, Xiaobai a examiné d'autres lignes et a découvert qu'elle pouvait obtenir les résultats qu'elle souhaitait. Elle était très heureuse. Mais ensuite elle a découvert le Nouveau Monde : "Alors qu'est-ce que cela signifie 9 ?" Moi : "Ce 9 signifie ignorer les données non filtrées et additionner uniquement les résultats filtrés."

Xiaobai : "Écoutez ce que vous avez dit, y a-t-il d'autres nombres qui ont d'autres significations ?"

Moi : "Bien sûr, alors je vous parlerai de la signification des autres nombres !"

Ignorer la somme des lignes cachées

🎜🎜 Parfois, nous rencontrons cette situation. Il y a une colonne de chiffres et nous devons masquer plusieurs données qui ne sont pas soumises à des calculs. Si vous utilisez SUM directement, vous ne pouvez pas obtenir les résultats corrects, comme le montre la figure. 🎜🎜🎜🎜Prêt à l'emploi Le paramètre "9" de la fonction SOUS-TOTAL que nous venons d'apprendre ne peut pas être implémenté, comme le montre la figure. 🎜🎜🎜🎜En ce moment il faut envisager de changer un paramètre. 🎜🎜Le paramètre suivant est "109", allez ! 🎜🎜🎜🎜Formule :=SUBTOTAL(109,A1:A5)🎜🎜Comme le montre la figure ci-dessous, après avoir modifié le premier paramètre de la fonction SOUS-TOTAL en "109", vous pouvez facilement obtenir le résultat de la somme après avoir ignoré les lignes masquées ! Comme le montre l'image. 🎜🎜🎜🎜Paramètres "109 "La fonction est de additionner les valeurs visibles. Elle peut additionner les données cachées ou les données filtrées. Le paramètre « 9 » ne peut être utilisé que dans les lignes filtrées et n'a aucun effet sur les lignes masquées. 🎜🎜🎜🎜Les applications d'autres paramètres de SUBTOTAL🎜🎜🎜🎜SUBTOTAL ne se limitent pas au champ de sommation. La moyenne, le maximum, l'écart type et la variance peuvent tous être trouvés. Par exemple, nous souhaitons maintenant compter la valeur maximale des lignes masquées ignorées, comme le montre la figure 6. 🎜🎜Formule : =SUBTOTAL(104,A1:A5) 🎜🎜🎜 (avant de masquer)                             )🎜🎜Après avoir masqué la valeur maximale "8", directement dans la cellule A6 La valeur maximale actuellement visible "7 " est obtenu. 🎜🎜Alors pourquoi est-il 104 ? En fait, il existe un ensemble de règles de représentation des nombres dans la fonction SOUS-TOTAL. Aujourd'hui, nous parlerons des autres paramètres, dont 11 fonctions telles que la moyenne, le maximum, le minimum, l'écart type et la variance. Certains sont couramment utilisés et d’autres ne le sont pas. Vous pouvez choisir en fonction de vos propres besoins. Vous trouverez ci-dessous un tableau comparatif de 11 paramètres. 🎜

Ignorer les valeurs filtrées lors du calcul	Ignorer les lignes cachées et les valeurs filtrées lors du calcul	Fonction	Fonction correspondante
1	101	Moyenne	MOYENNE
2	102	Comptez le nombre de cellules contenant des nombres	COMPTE
3	103	Compter les cellules non vides	COUNTA
4	104	MAXIMUM	MAX
5	105	Valeur minimale	MIN
6	106	Multiplication	PRODUIT
7	107	Calculer l'écart type de l'échantillon	STDEV
8	108	Calculer l'écart type de la population	STDEVP
9	109	Somme	SOMME 111	Calculer la variance globale	VARP

Partie étendue 1 : uniquement un résumé statistique

Lorsque nous effectuons des tabulations, nous rencontrons souvent une telle situation récapitulative. Les éléments sont résumés. dans le même tableau, comme le montre la figure.

Si vous utilisez SUM pour le résumé, toutes les données seront comptées, comme le montre la figure.

Mais nous voulons juste additionner le contenu de chaque sous-total ! Pas de panique, remplacez simplement SUM par SUBTOTAL pour obtenir la réponse souhaitée. Comme le montre l'image.

Pourquoi ça ? En fait, en plus d'ignorer les lignes masquées et filtrées, SUBTOTAL peut également ignorer les cellules contenant les fonctions SUBTOTAL et AGGREGATE. Les cellules B3, B6 et B10 sont toutes des sous-totaux calculés à l'aide de la fonction SOUS-TOTAL. Naturellement, elles seront ignorées lorsque la fonction SOUS-TOTAL sera utilisée pour la somme finale.

Partie d'extension 2 : Numéro de série ininterrompu

"Après avoir compris les caractéristiques de la fonction SOUS-TOTAL, nous pouvons l'utiliser pour faire quelque chose, comme numéroter la liste."

"Quoi, liste numérotation Ne s'agit-il pas simplement de le faire glisser avec la souris ? "

"C'est différent~ma numérotation est automatique ! Qu'il s'agisse de supprimer une ligne ou de masquer une ligne, la numérotation peut être automatiquement réorganisée!"

"C'est ainsi. incroyable, alors je Mais j'apprends dur. "

En fait, c'est très simple. Supposons que j'ai une liste et que la colonne du numéro de série soit actuellement vide, comme le montre l'image.

Entrez la formule dans la cellule A2 : =SUBTOTAL(103,B$2:B2), puis menu déroulant pour la remplir, et vous obtiendrez le numéro de série que nous voulons. Comme le montre l'image. =SUBTOTAL(103,B:B2)，然后下拉填充，就能得到我们想要的序号。如图所示。

我们试着来隐藏一行，就会发现，序号仍然是按照顺序排列的，并没有中断，如图所示。

现在我们来逐步解释一下公式=SUBTOTAL(103,B:B2)

• Nous essayons de masquez une ligne, vous constaterez que les numéros de série sont toujours classés dans l'ordre sans interruption, comme le montre la figure.
  
• Maintenant, faisons-le Expliquez la formule étape par étape =SUBTOTAL(103,B$2:B2)

103 : En regardant le tableau de comparaison des paramètres ci-dessus, nous pouvons savoir que la fonction de 103 est d'ignorer les paramètres cachés. lignes et valeurs filtrées, et les statistiques ne sont pas le nombre de cellules vides.

B$2:B2 : La zone dans la cellule A2 est B$2:B2. Le but est de compter le nombre de cellules non vides dans la zone B2:B2, et le résultat est 1. Une fois la formule déroulée, la zone de la cellule A3 devient B$2:B3, puis le nombre de cellules non vides comptées devient deux et le résultat est 2. Comme le montre l'image.

Par analogie, à mesure que la formule est déroulée, nous pouvons obtenir un ensemble de numéros de série consécutifs. Combiné avec la caractéristique que le premier paramètre de la fonction SOUS-TOTAL ne calcule que les valeurs visibles, vous pouvez obtenir un ensemble de numéros de série ininterrompus ! 🎜🎜Que savez-vous d'autre sur les merveilleuses utilisations de la fonction SOUS-TOTAL ? Bienvenue à laisser un message et à le partager avec nous ~ Les amis qui aiment l'article voudront peut-être cliquer sur « Lecture » pour nous soutenir ! 🎜🎜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!