Excel Formules pour compter les cellules avec du texte: toute cellule spécifique ou filtrée

Comment compter les cellules contenant du texte dans Excel?

Excel a été initialement conçu pour traiter les numéros, mais aujourd'hui, nous l'utilisons également souvent pour stocker et traiter du texte. Vous voulez savoir combien de cellules sont dans la feuille de calcul qui contiennent du texte? Microsoft Excel fournit plusieurs fonctions pour y parvenir. Quelle fonction doit être utilisée? Cela dépend de la situation. Dans ce tutoriel, vous trouverez diverses formules et le meilleur moment pour utiliser chaque formule.

• Cellules statistiques contenant n'importe quel texte
• Ignorez des cordes et des espaces vides et compter les cellules contenant du texte
• Statistiques des cellules contenant du texte spécifique
• Statistiques cellules filtrées contenant du texte

Comment compter le nombre de cellules contenant du texte dans Excel

Il existe deux formules de base pour trouver le nombre de cellules qui contiennent une chaîne de texte ou un caractère dans une plage donnée.

Utilisez la formule Countif pour compter toutes les cellules contenant du texte

Lorsque vous souhaitez trouver le nombre de cellules dans Excel qui contiennent du texte, en utilisant un astérisque dans un paramètre conditionnel , la solution la meilleure et la plus facile est:

COUNTIF(*范围*, "\*") Étant donné que l'astérisque (*) est un indicateur générique qui correspond à n'importe quelle séquence de caractères, la formule comptera toutes les cellules contenant n'importe quel texte.

Cellules statistiques contenant n'importe quel texte utilisant la formule de sumproduct

Une autre façon d'obtenir le nombre de cellules contenant du texte consiste à utiliser les fonctions de sumproduct et ISText en combinaison:

SUMPRODUCT(--ISTEXT(*范围*)) ou

SUMPRODUCT(ISTEXT(*范围*)*1) La fonction ISText vérifie si chaque cellule dans la plage spécifiée contient des caractères de texte et renvoie un tableau de valeurs True (cellules contenant du texte) et fausses (autres cellules). L'opérateur double unary (-) ou l'opération de multiplication jette respectivement True et Faux à 1 et 0, générant ainsi un tableau de 1 et 0. La fonction de sumproduct résume tous les éléments du tableau et renvoie le nombre de 1, c'est-à-dire le nombre de cellules contenant du texte.

Pour mieux comprendre comment ces formules fonctionnent, voyez quelles valeurs sont comptées et lesquelles ne sont pas comptées:

Compter le contenu	Contenu non conduit
- cellules contenant du texte - caractères spéciaux - Nombres formatés en texte - Des cellules qui peuvent être considérées comme vides mais contiennent des chaînes vides (""), des apostrophes ('), des espaces ou des caractères invisibles	- nombre - date - valeurs logiques de vrai et faux - erreur - cellules vierges

Par exemple, pour compter les cellules contenant du texte dans la plage A2: A10 (excluant les nombres, les dates, les valeurs logiques, les erreurs et les cellules vierges), vous pouvez utiliser l'une des formules suivantes:

=COUNTIF(A2:A10, "*")

=SUMPRODUCT(--ISTEXT(A2:A10))

=SUMPRODUCT(ISTEXT(A2:A10)*1)

La figure suivante montre les résultats:

Ignorer les espaces et les cordes vides et compter les cellules contenant du texte

La formule discutée ci-dessus compte toutes les cellules contenant des caractères de texte. Cependant, dans certains cas, cela peut être déroutant car certaines cellules peuvent paraître vides mais contiennent en fait des caractères que l'œil humain ne peut pas voir, tels que les chaînes vides, les apostrophes, les espaces, les pauses de ligne, etc. En conséquence, les cellules qui peuvent être considérées comme vides seront comptées par les formules, ce qui entraîne l'inquiétude de l'utilisateur d'essayer de comprendre la raison.

Pour exclure les cellules vierges "faussement positives" du nombre, utilisez la fonction Counts et utilisez le caractère "exclure" dans la deuxième condition.

Par exemple, pour compter les cellules contenant du texte dans la gamme A2: A7 (ignorer les cellules contenant des caractères d'espace ), utilisez la formule suivante:

=COUNTIFS(A2:A7,"*", A2:A7, " ")

Si la plage cible contient des nombres pilotés par des formules, certaines formules peuvent entraîner une chaîne vide (""). Pour ignorer les cellules contenant des chaînes vides , remplacez "*" par "*? *" Dans le paramètre Criteria1 :

=COUNTIFS(A2:A9,"*?*", A2:A9, " ")

Un point d'interrogation enfermé avec un astérisque indique qu'il devrait y avoir au moins un caractère texte dans la cellule. Puisqu'une chaîne vide n'a pas de caractères, elle ne répond pas aux critères et ne sera pas comptée. Les cellules vierges commençant par l'apostrophe (') ne sont pas non plus comptées.

Dans la figure ci-dessous, il y a un espace dans A7, une apostrophe dans A8 et une chaîne vide dans A9 (= ""). Notre formule ignore toutes ces cellules et renvoie un nombre de cellules de texte de 3:

Comment compter les cellules contenant du texte spécifique dans Excel

Pour obtenir le nombre de cellules qui contiennent un texte ou un caractère spécifique, fournissez simplement le texte dans les paramètres conditionnels de la fonction COMTIF. L'exemple suivant explique les subtilités.

Pour correspondre exactement à l'exemple de texte, entrez le texte intégral en guillemets:

COUNTIF(*范围*, "*文本*") Pour compter les cellules partiellement appariées, placez le texte entre deux astérisques, qui représentent n'importe quel nombre de caractères avant et après le texte:

COUNTIF(*范围*, "\**文本*\*") Par exemple, pour savoir combien de cellules dans la gamme A2: A7 contiennent le mot "bananes", utilisez la formule suivante:

=COUNTIF(A2:A7, "bananas")

Pour compter toutes les cellules qui contiennent des "bananes" dans le cadre de leur contenu (quel que soit l'emplacement), utilisez la formule suivante:

=COUNTIF(A2:A7, "*bananas*")

Pour rendre la formule plus facile à utiliser, vous pouvez placer la condition dans une cellule prédéfinie, comme D2, et placer la référence de la cellule dans le deuxième paramètre:

=COUNTIF(A2:A7, D2)

Sur la base de l'entrée en D2, la formule peut correspondre entièrement ou partiellement à l'exemple de texte:

• Pour une correspondance exacte, tapez le même mot ou la même phrase que le mot ou la phrase complet affiché dans le tableau source, comme les bananes .
• Pour les correspondances partielles, tapez un exemple de texte entouré de caractères génériques, tels que * bananes * .

Étant donné que la formule est insensible à la caisse , vous n'avez pas à vous soucier de la case, ce qui signifie que * bananes * est OK aussi.

Alternativement, pour compter les cellules partiellement appariées , rejoignez les références de cellules et les caractères génériques, par exemple:

=COUNTIF(A2:A7, "*"&D2&"*")

Pour plus d'informations, voyez comment compter les cellules contenant du texte spécifique dans Excel.

Comment compter les cellules filtrées contenant du texte dans Excel

Lorsque vous utilisez des filtres Excel pour afficher uniquement les données pertinentes pour un moment donné, vous devrez parfois compter les cellules visibles contenant du texte . Malheureusement, il n'y a pas de solution en un clic pour cette tâche, mais l'exemple suivant vous guidera à travers les étapes.

Supposons que vous ayez un tableau comme indiqué dans la figure suivante. Certaines entrées ont été extraites d'une base de données plus grande à l'aide de formules et certaines erreurs se sont produites dans le processus. Vous souhaitez trouver le nombre total d'éléments dans la colonne A. La formule Countif que nous utilisons pour compter les cellules contenant du texte est très efficace lorsque toutes les lignes sont visibles:

=COUNTIF(A2:A10, "*")

Maintenant, vous réduisez la liste en fonction de certains critères, tels que le filtrage des éléments avec un certain nombre de plus de 10. La question est - combien de projets reste-t-il?

Pour compter les cellules filtrées contenant du texte , vous devez faire ce qui suit:

1. Dans la table source, rendez toutes les lignes visibles. Pour ce faire, effacez tous les filtres et les lignes cachées un peu.

2. Ajoutez une colonne d'assistance contenant la formule sous-totale qui indique si la ligne a été filtrée. Pour traiter les cellules filtrées , utilisez 3 pour le paramètre fonction_num : =SUBTOTAL(3, A2)

   Pour identifier toutes les cellules cachées (filtrées et cachées manuellement), entrez 103 dans function_num :

   =SUBTOTAL(103, A2)

   Dans cet exemple, nous voulons compter les cellules visibles contenant du texte , quelle que soit la façon dont les autres cellules sont cachées, nous entrons donc dans la deuxième formule de A2 et le coprirons dans A10.

   Pour les cellules visibles, la formule renvoie 1. Une fois que vous filtrez ou vous cachez manuellement certaines lignes, la formule renvoie 0 pour eux. (Vous ne verrez pas ces zéros car ils sont retournés pour les lignes cachées. Pour vous assurer qu'elle fonctionne de cette façon, copiez simplement le contenu de la cellule cachée contenant la formule sous-totale à n'importe quelle cellule visible, par exemple = D2, en supposant que la ligne 2 est cachée.)

   

3. Utilisez la fonction Counts et deux paires de critères / critères différents pour compter les cellules visibles contenant du texte:

   • Condition 1 - Recherchez des cellules contenant n'importe quel texte ("*") dans la plage A2: A10.
   • Condition 2 - Recherche 1 dans la plage D2: D10 pour détecter les cellules visibles.

   =COUNTIFS(A2:A10, "*", D2:D10, 1)

Vous pouvez maintenant filtrer les données comme vous le souhaitez, et la formule vous indiquera le nombre de cellules filtrées qui contiennent du texte dans la colonne A (3 dans ce cas):

Si vous ne souhaitez pas insérer des colonnes supplémentaires dans votre feuille de calcul, vous aurez besoin d'une formule plus longue pour terminer la tâche. Sélectionnez simplement celui que vous préférez:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), -- (ISTEXT(A2:A10)))

Les opérateurs de multiplication peuvent également être:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))) * (ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10)-MIN(ROW(A2:A10)),,1)) * (ISTEXT(A2:A10)))

La formule à utiliser dépend de vos préférences personnelles - les résultats sont les mêmes de toute façon:

Comment ces formules fonctionnent

La première formule utilise la fonction indirecte pour «fournir» une seule référence au sous-total pour toutes les cellules de la plage spécifiée. La deuxième formule utilise une combinaison de fonctions de décalage, de ligne et de min pour atteindre le même objectif.

La fonction subtotale renvoie un tableau de 1 et 0, où 1 représente les cellules visibles et 0 représente des cellules cachées (comme la colonne auxiliaire ci-dessus).

La fonction ISText vérifie chaque cellule dans A2: A10 et renvoie True si la cellule contient du texte, sinon renvoie false. L'opérateur double unary (-) jette des valeurs vraies et fausses à 1 et 0. À ce stade, la formule est la suivante:

=SUMPRODUCT({0;1;1;1;0;1;1;0;0}, {1;1;1;0;1;1;0;1;1})

La fonction de sumproduct multiplie d'abord les éléments à la même position dans deux tableaux, puis résume le réseau résultant.

Étant donné que le résultat de la multiplication par zéro est zéro, seules les cellules représentées par 1 dans les deux tableaux ont 1 dans le tableau final.

=SUMPRODUCT({0;1;1;0;0;1;0;0;0})

Le nombre de 1 dans le tableau ci-dessus est le nombre de cellules visibles contenant du texte.

Voici comment compter les cellules contenant du texte dans Excel. Merci d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Fichiers téléchargeables

Formules pour compter les cellules contenant du texte dans 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!