Partage pratique de compétences Excel : Comment ignorer les colonnes cachées et effectuer des sommes ?

Dans l'article précédent « Partage de compétences pratiques sur Excel : la « mise en forme conditionnelle » et la « formule de fonction » sont utilisées ensemble », nous avons appris à utiliser ensemble la « mise en forme conditionnelle » et la « formule de fonction » à travers 2 exemples. Aujourd'hui, nous allons parler de la sommation des données et présenter la méthode de sommation en ignorant les colonnes cachées. Venez apprendre !

Dans le travail quotidien, nous utilisons souvent la sommation. Ce genre de problème est simple pour tout le monde. Il peut être résolu en utilisant la fonction SOMME :

Parfois, quelques lignes peuvent être masquées. la fonction SOUS-TOTAL sera utilisée pour la sommation. Lorsqu'il n'y a pas de masquage, le résultat de la fonction SOUS-TOTAL est cohérent avec le résultat calculé par la fonction SOMME, comme suit :

Une fois que nous masquons certaines lignes de données (telles que les lignes 3, 6, 9 et 12 ), le résultat est Les changements se sont produits comme suit :

Notez que le premier paramètre de la fonction SOUS-TOTAL utilise 109 pour indiquer que la somme des lignes masquées est ignorée.

Concernant la fonction SOUS-TOTAL, des tutoriels ont déjà été publiés. Les amis intéressés peuvent lire les articles historiques.

Le problème dont nous allons discuter aujourd'hui n'est pas de savoir comment additionner si vous ignorez les lignes masquées, mais comment additionner si vous ignorez les colonnes masquées. Tout d'abord, précisons que la fonction SOUS-TOTAL ne peut pas faire cela. C'est clairement indiqué dans l'aide de la fonction :

Non seulement la fonction SOUS-TOTAL ne peut pas faire cela, mais à l'heure actuelle, Excel ne l'a pas encore fait. pour ignorer les colonnes masquées. Fonction qui effectue la sommation. Alors comment répondre à cette demande ? Cela nécessite l'utilisation d'une fonction relativement nouvelle : la fonction d'assistance CELL.

Je crois que peu d'amis ont vu cette fonction, et encore moins savent comment l'utiliser. Voyons d'abord brièvement à quoi sert la fonction CELL. Entrez =c dans la cellule et vous verrez cette fonction. Après avoir sélectionné la fonction, une explication simple apparaîtra :

Dans cette phrase, vous pouvez à peu près comprendre que la fonction CELL peut obtenir le format d'une cellule, son emplacement. et d'autres informations. Double-cliquez sur cette fonction, et quelques options apparaîtront :

Comme vous pouvez le voir, la fonction a deux paramètres, info_type et reference. Le premier paramètre info_type, type d'information, il y a 12 types au total. La signification spécifique de chaque type peut être comprise grâce à la fonction :

Les amis qui sont intéressés par ces types d'informations peuvent jeter un œil par eux-mêmes, ils le sont. très facile à comprendre. La chose la plus importante à utiliser aujourd'hui est le dernier type d'information « largeur », qui est simplement la largeur de la colonne.

Certains amis ont peut-être pensé que si une colonne est masquée, sa largeur de colonne sera de 0. Est-ce vrai ? Nous pouvons essayer ? Sélectionnez "largeur" ​​comme premier paramètre de la formule et définissez le deuxième paramètre sur B1, ce qui signifie que vous souhaitez obtenir la largeur de colonne de la cellule B1 (en fait la largeur de colonne de la colonne B). Entrez la formule dans la cellule B16 : =CELL("width", B1), puis tirez la formule vers la droite :

Le résultat est un total de 8. Nous pouvons essayer d'ajuster la largeur des colonnes individuelles et voir s'il y a un changement :

Après avoir ajusté la largeur, le résultat est toujours 8. Y a-t-il un problème avec la formule ?

En fait non, la raison est que la fonction CELL est un peu colérique Lorsque le format de la cellule change (la largeur de la colonne est un format), il faut la recalculer pour mettre à jour le résultat. Il existe deux façons de recalculer, l'une consiste à appuyer sur la touche de fonction F9, l'autre consiste à double-cliquer sur n'importe quelle cellule et à appuyer sur Entrée. En y regardant à nouveau, j'ai constaté que les résultats avaient été mis à jour :

La taille du nombre correspond à la largeur de la cellule. En parlant de comment ignorer les sommes de colonnes cachées, la réponse est déjà évidente : utilisez la fonction CELL pour obtenir la largeur de colonne, puis utilisez la fonction SUMIF pour implémenter la somme. Entrez la formule dans la cellule I2 sous la forme : =SUMIF($B$16:$G$16,">0",B2:G2), puis déroulez la formule vers le bas.

Lorsqu'elles ne sont pas masquées, toutes sont additionnées. Masquons maintenant quelques colonnes pour voir l'effet :

N'oubliez pas d'appuyer sur F9 ou de double-cliquer après avoir masqué.

Le problème semble s'arrêter là, mais il y a toujours des partenaires qui ne souhaitent pas utiliser de colonnes auxiliaires (lignes auxiliaires) et souhaitent utiliser des formules matricielles pour y parvenir, par exemple : =SUMPRODUCT((CELL("width" ,B1:G1>0 )*B2:G2)

L'idée semble raisonnable, mais ce n'est pas possible car si la référence du paramètre est une certaine plage de cellules, la fonction CELL ne renverra l'information qu'à la cellule en haut à gauche C'est-à-dire que même si j'ai écrit une zone comme B1:G1, je n'ai obtenu que la largeur de colonne de B1

Ne pensez-vous pas que l'humeur de la fonction cellulaire est assez étrange~~~

. En fait, il existe de nombreuses utilisations intéressantes de cette fonction, si vous voulez savoir, laissez un message ci-dessous

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!