Résumé du calcul dans l'état de filtrage Excel

Cet article vous apporte des connaissances pertinentes sur excel. Il présente principalement comment ajouter des numéros de série après le filtrage, multiplier après le filtrage et compter selon les conditions après le filtrage. J'espère que cela vous sera utile. tout le monde aide.

Recommandations d'apprentissage associées : Tutoriel Excel

1. Ajouter des numéros de série après le filtrage

Comme le montre la figure ci-dessous, pour maintenir les numéros de série continus dans l'état filtré, nous pouvons d'abord annuler le filtre et l'ajouter. à la cellule D2 Entrez la formule suivante puis déroulez vers le bas :

=SUBTOTAL(3,E$1:E2)-1

La fonction SOUS-TOTAL ne compte que le contenu des cellules visibles.

Le premier paramètre utilise 3, qui indique la règle de calcul pour exécuter la fonction COUNTA, c'est-à-dire compter le nombre de cellules visibles pour le deuxième paramètre.

Le deuxième paramètre utilise une plage étendue dynamiquement E$1:E2 Au fur et à mesure que la formule est déroulée, cette plage deviendra E$1:E3, E$1:E4, E$1:E5,...

La formule calcule toujours. Le nombre de cellules visibles non vides dans la colonne E, de la première ligne à la ligne où se trouve la formule. Soustrayez 1 du résultat, et le résultat calculé sera le même que le numéro de série, et il restera continu après filtrage.

Remarque, veuillez noter que si cette formule est modifiée en =SUBTOTAL(3,E$2:E2), c'est-à-dire à partir de la ligne où se trouve la formule, le résultat du numéro de série sera correct, mais la dernière ligne sera être traité comme une ligne récapitulative par Excel lors du filtrage. Toujours affiché.

2. Multiplier après filtrage

Comme le montre la figure ci-dessous, après avoir filtré la colonne E, vous devez calculer le montant total multiplié par le prix unitaire.

La formule de la cellule E2 est :

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

Pour calculer le produit filtré, le la clé du problème est de déterminer si les données sont visibles.

Comment juger de ce statut visible ?

Besoin de combiner les fonctions OFFSET et SUBTOTAL.

Utilisez d'abord la fonction OFFSET, en prenant la cellule E3 comme point de base, et décalez les lignes 1 à 13 vers le bas afin d'obtenir une référence multidimensionnelle. Cette référence multidimensionnelle contient 13 zones de référence avec une ligne et une colonne, c'est-à-dire que les cellules individuelles de E4 à E16 sont référencées respectivement.

Ensuite, utilisez la fonction SOUS-TOTAL. Utilisez 3 comme premier paramètre, c'est-à-dire comptez le nombre de cellules visibles dans chaque cellule de E4 à E16. Si la cellule est dans l'état affiché, le résultat statistique pour cette cellule est 1. . Sinon, le résultat statistique est 0. Obtenez un tableau de mémoire similaire à l'effet suivant :

{1;0;1;1;1;1;0;0;1;1;0;1;0}

Utilisez le résultat ci-dessus multiplié par le nombre et somme de la colonne F Le prix unitaire de la colonne G, si la cellule est à l'état d'affichage, équivaut à 1*quantité*prix unitaire, sinon il équivaut à 0*quantité*prix unitaire.

Enfin, utilisez la fonction SUMPRODUCT pour additionner les produits.

3. Décompte selon conditions après filtrage

Comme le montre la figure ci-dessous, après avoir filtré les départements de la colonne E, il faut calculer le nombre de personnes ayant plus de 3 ans d'ancienneté.

La formule de la cellule E2 est :

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

Le principe de calcul du premier semestre et ce qui précède Un exemple est le même, le noyau consiste également à déterminer si la cellule est visible.

Les conditions statistiques de la seconde moitié de la formule (G4:G16>3) sont multipliées par les résultats du jugement de la première moitié, indiquant que les deux conditions sont remplies en même temps, c'est-à-dire le nombre d'éléments dans la état visible et la colonne G est supérieure à 3.

4. Corrigez automatiquement le titre après le filtrage

Comme le montre la figure ci-dessous, après avoir filtré le nom du département dans la colonne E, vous souhaitez que le titre de la cellule D1 soit automatiquement remplacé par le nom du département correspondant. =LOOKUP(1,0 /SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&"Statistical Table"

La partie combinée de la fonction SUBTOTAL et OFFSET, le but est toujours de déterminer l'unité de la colonne D si la grille est visible. Obtenez un tableau de mémoire composé de 0 et 1 :

{0;1;0;0;0;0;1;1;1;1;0;1;0;1;0}

Utilisez 0/cette mémoire Array, obtenez un nouveau tableau mémoire composé de 0 et d'une valeur d'erreur :

{#DIV/0!;0;#DIV/0!…;0;0;0;0;#DIV/0!;0;# DIV /0!;0;#DIV/0!}

La fonction LOOKUP utilise 1 comme valeur de requête, trouve la position du dernier 0 dans le tableau mémoire ci-dessus et renvoie le contenu de la colonne E à la position correspondante.

Le but ultime est d'extraire le contenu de la dernière cellule affichée après filtrage.

Connectez le contenu extrait avec le "tableau statistique" et transformez-le en un titre de tableau mis à jour automatiquement.

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!