Cet article décrit la syntaxe de formule et l’utilisation de la fonction BDNBVAL dans Microsoft Excel.
Description
Compte les cellules non vides dans un champ (colonne) d’enregistrements d’une liste ou d’une base de données qui remplissent les conditions que vous spécifiez.
L’argument champ est facultatif. Si vous ne le spécifiez pas, BDNBVAL compte tous les enregistrements de la base de données qui répondent aux critères.
Syntaxe
BDNBVAL(base_de_données, champ, critères)
La syntaxe de la fonction BDNBVAL contient les arguments suivants :
-
base de données Obligatoire. Représente la plage de cellules qui constitue la liste ou la base de données. Une base de données est une liste de données liées dans laquelle les lignes d’informations liées sont des enregistrements et les colonnes de données sont des champs. La première ligne de la liste contient les étiquettes de chaque colonne.
-
champ Facultatif. Indique la colonne utilisée dans la fonction. Entrez l’étiquette de la colonne placée entre guillemets doubles, par exemple "Âge" ou "Rendement", ou un nombre (sans guillemets) représentant la position de la colonne dans la liste : 1 pour la première colonne, 2 pour la seconde, et ainsi de suite.
-
critères Obligatoire. Représente la plage de cellules qui contient les conditions que vous spécifiez. Vous pouvez utiliser n’importe quelle plage comme argument critères, à condition toutefois qu’elle comprenne au moins une étiquette de colonne et au moins une cellule sous celle-ci dans laquelle vous spécifiez une condition pour la colonne.
Remarques
-
Vous pouvez utiliser n’importe quelle plage pour l’argument critères, à condition toutefois qu’elle comprenne au moins une étiquette de colonne et au moins une cellule située sous l’étiquette de colonne pour spécifier la condition.
Par exemple, si la plage G1:G2 contient l’étiquette de colonne Salaire dans G1 et le montant 10 000 € dans G2, vous pouvez définir la plage par le nom CritèreSalaire et utiliser ce nom pour l’argument critères dans les fonctions de base de données.
-
Bien que la plage de critères puisse se trouver n’importe où dans la feuille de calcul, ne la placez pas sous la liste. Si vous ajoutez des informations à la liste, les informations sont ajoutées à la première ligne sous la liste. Si cette ligne n’est pas vide, Excel ne peut pas ajouter les informations.
-
Assurez-vous que la plage de données ne chevauche pas la liste.
-
Pour effectuer une opération sur la totalité d’une colonne dans une base de données, ajoutez une ligne vide sous les étiquettes de colonne dans la plage de critères.
Exemples
Copiez les données d’exemple dans le tableau suivant, et collez-le dans la cellule A1 d’un nouveau classeur Excel. Pour que les formules affichent des résultats, sélectionnez-les, appuyez sur F2, puis sur Entrée. Si nécessaire, vous pouvez adapter la largeur des colonnes pour afficher toutes les données. Si vous copiez l’un des exemples suivants dans Excel, veillez à sélectionner toutes les cellules de ce tableau, y compris celle se trouvant dans le coin supérieur gauche.
|
Arbre |
Hauteur |
Âge |
Rendement |
Bénéfice |
Hauteur |
|
="=Pomme" |
>10 |
<16 |
|||
|
="=Poire" |
|||||
|
Arbre |
Hauteur |
Âge |
Rendement |
Bénéfice |
|
|
Pomme |
18 |
20 |
14 |
105,0 |
|
|
Poire |
12 |
12 |
10 |
96,0 |
|
|
Cerise |
13 |
14 |
9 |
105,0 |
|
|
Pomme |
14 |
15 |
10 |
75,0 |
|
|
Poire |
9 |
8 |
8 |
76,8 |
|
|
Pomme |
8 |
9 |
6 |
45,0 |
|
|
Formule |
Description |
Résultat |
|||
|
=BDNBVAL(A4:E10; "Bénéfice"; A1:F2) |
Compte les lignes (1) contenant « Pomme » dans la colonne A avec une hauteur de >10 et <16. Seule la ligne 8 satisfait à ces trois conditions. |
1 |
Exemples de critères
-
Lorsque vous entrez =texte dans une cellule, Excel l’interprète comme une formule et tente de la calculer. Pour entrer =texte et faire en sorte qu’Excel n’essaie pas d’effectuer le calcul, utilisez la syntaxe suivante :
="= entrée "
Où entrée est le texte ou la valeur que vous recherchez. Par exemple :
|
Ce que vous tapez dans la cellule |
Ce que Microsoft Excel évalue et affiche |
|
="=Bartoli" |
=Bartoli |
|
="=3000" |
=3 000 |
-
Lorsque vous filtrez des données texte, Excel ne fait pas de distinction entre les caractères majuscules et minuscules. Vous pouvez cependant utiliser une formule pour effectuer une recherche respectant la casse.
Les sections suivantes donnent des exemples de critères complexes.
Plusieurs critères dans une colonne
Logique booléenne : (Vendeur = « Bartoli » OU Vendeur = « Beaune »)
Pour rechercher les lignes qui remplissent plusieurs critères pour une colonne, tapez les critères l’un en dessous de l’autre dans des lignes distinctes de la plage de critères.
Dans la plage de données (A6:C10), la plage de critères (B1:B3) affiche les lignes qui contiennent « Bartoli » ou « Beaune » dans la colonne Vendeur (A8:C10).
|
|
Vendeur |
|
|
="=Bartoli" |
||
|
="=Beaune" |
||
|
|
||
|
Catégorie |
Vendeur |
Ventes |
|
Boissons |
Suyama |
5 122 € |
|
Viande |
Bartoli |
450 € |
|
Produit |
Beaune |
6 328 € |
|
Produit |
Bartoli |
6 544 € |
|
Formule |
Description |
Résultat |
|
’=BDNBVAL(A6:C10;2;B1:B3) |
Compte le nombre de lignes (3) dans la plage A6:C10 correspondant à l’une ou l’autre des conditions « Vendeur » dans les lignes 2 et 3. |
=BDNBVAL(A6:C10;2;B1:B3) |
Plusieurs critères dans plusieurs colonnes où tous les critères doivent être vrais
Logique booléenne : (Type = "Produit" ET Ventes > 1 000)
Pour rechercher les lignes qui remplissent plusieurs critères dans plusieurs colonnes, tapez tous les critères dans la même ligne de la plage de critères.
Dans la plage de données suivante (A6:C12), la plage de critères (A1:C2) affiche toutes les lignes contenant « Produit » dans la colonne Catégorie et une valeur supérieure à 2 000 € dans la colonne Ventes.
|
Catégorie |
Vendeur |
Ventes |
|
="=Produit" |
>2000 |
|
|
Catégorie |
Vendeur |
Ventes |
|
Boissons |
Suyama |
5 122 € |
|
Viande |
Bartoli |
450 € |
|
Produit |
Beaune |
935 € |
|
Produit |
Bartoli |
6 544 € |
|
Boissons |
Beaune |
3 677 € |
|
Produit |
Bartoli |
3 186 € |
|
Formule |
Description |
Résultat |
|
’=BDNBVAL(A6:C12;;A1:C2) |
Compte le nombre de lignes (2) dans A6:C12 répondant aux conditions de la ligne 2 (="Produit" et >2000). |
=BDNBVAL(A6:C12;;A1:C2) |
Plusieurs critères dans plusieurs colonnes où n’importe quel critère peut être vrai
Logique booléenne : (Type = « Produit » OU Vendeur = « Bartoli »)
Pour rechercher les lignes qui remplissent plusieurs critères dans plusieurs colonnes où n’importe quel critère peut être vrai, tapez les critères dans différentes lignes de la plage de critères.
Dans la plage de données (A6:C10), la plage de critères (A1:B3) affiche toutes les lignes contenant « Produit » dans la colonne Type ou « Bartoli ».
|
Catégorie |
Vendeur |
|
|
="=Produit" |
||
|
="=Bartoli" |
||
|
Catégorie |
Vendeur |
Ventes |
|
Boissons |
Suyama |
5 122 € |
|
Viande |
Bartoli |
675 € |
|
produit |
Beaune |
937 € |
|
Produit |
Beaune |
|
|
Formule |
Description |
Résultat |
|
’=BDNBVAL(A6:C10;"Ventes";A1:B3) |
Compte le nombre de lignes (2) dans la plage A6:C10 répondant à l’une des conditions dans A1:C3, où le champ « Ventes » n’est pas vide. |
=BDNBVAL(A6:C10;"Ventes";A1:B3) |
Jeux de critères où chaque jeu comporte des critères pour plusieurs colonnes
Logique booléenne : ( (Vendeur = « Bartoli » ET Ventes >3 000) OU (Vendeur = « Beaune » ET Ventes > 1 500) )
Pour rechercher les lignes qui remplissent plusieurs jeux de critères où chaque jeu comporte des critères pour plusieurs colonnes, tapez chaque jeu de critères dans des lignes distinctes.
Dans la plage de données suivante (A6:C10), la plage de critères (B1:C3) sert à compter les lignes qui contiennent « Davolio » dans la colonne Vendeur et une valeur supérieure à 3000 € dans la colonne Ventes, ou les lignes qui contiennent « Beaune » dans la colonne Vendeur et une valeur supérieure à 1500 € dans la colonne Ventes.
|
Catégorie |
Vendeur |
Ventes |
|
="=Bartoli" |
>3000 |
|
|
="=Beaune" |
>1500 |
|
|
Catégorie |
Vendeur |
Ventes |
|
Boissons |
Suyama |
5 122 € |
|
Viande |
Bartoli |
450 € |
|
Produit |
Beaune |
6 328 € |
|
Produit |
Bartoli |
6 544 € |
|
Formule |
Description |
Résultat |
|
’=BDNBVAL(A6:C10;;B1:C3) |
Compte le nombre de lignes (2) dans la plage A6:C10 qui répondent à toutes les conditions dans la plage B1:C3. |
=BDNBVAL(A6:C10;;B1:C3) |
Jeux de critères où chaque jeu comporte des critères pour une colonne
Logique booléenne : ( (Ventes > 6 000 ET Ventes < 6 500 ) OU (Ventes < 500) )
Pour rechercher les lignes qui remplissent plusieurs jeux de critères où chaque jeu comporte des critères pour une colonne, incluez plusieurs colonnes avec le même en-tête.
Dans la plage de critères suivante (A6:C10), la plage de critères (C1:D3) affiche les lignes qui contiennent des valeurs comprises entre 6 000 € et 6 500 €, ainsi que des valeurs inférieures à 500 € dans la colonne Ventes.
|
Catégorie |
Vendeur |
Ventes |
Ventes |
|
>6000 |
<6500 |
||
|
<500 |
|||
|
Catégorie |
Vendeur |
Ventes |
|
|
Boissons |
Suyama |
5 122 € |
|
|
Viande |
Bartoli |
450 € |
|
|
Produit |
Beaune |
6 328 € |
|
|
Produit |
Bartoli |
6 544 € |
|
|
Formule |
Description |
Résultat |
|
|
’=BDNBVAL(A6:C10;;C1:D3) |
Compte le nombre de lignes (2) répondant aux conditions de la ligne 2 (>6000 et <6500) ou à la condition à la ligne 3 (<500). |
=BDNBVAL(A6:C10;;C1:D3) |
Critères de recherche de valeurs texte partageant certains caractères mais pas d’autres
Pour rechercher des valeurs texte partageant certains caractères mais pas d’autres, procédez comme suit :
-
Tapez un ou plusieurs caractères sans signe égal (=) pour rechercher les lignes qui comportent une valeur texte commençant avec ces caractères dans une colonne. Par exemple, si vous tapez le texte Bart comme critère, Microsoft Excel trouve « Bartoli » et tous les autres noms commençant par ces caractères.
-
Utilisez des caractères génériques.
Les caractères génériques suivants peuvent être utilisés en tant que critères de comparaison.
|
Utilisez |
Pour rechercher |
|
? (point d’interrogation) |
un seul caractèrePar exemple, p?rt trouve « port » et « part ». |
|
* (astérisque) |
un nombre quelconque de caractèresPar exemple, *Est trouve « Nord-Est » et « Sud-Est ». |
|
~ (tilde) suivi de ?, *, ou ~ |
Un point d’interrogation, un astérisque ou un tilde Par exemple, fy91~? trouve « fy91? ». |
Dans la plage de données suivante (A6:C10), la plage de critères (A1:B3) sert à compter les lignes comportant « Vi » comme premiers caractères dans la colonne Type ou les lignes comportant « e » comme second caractère dans la colonne Vendeur.
|
Catégorie |
Vendeur |
Ventes |
|
Vi |
||
|
?e* |
||
|
Catégorie |
Vendeur |
Ventes |
|
Boissons |
Suyama |
5 122 € |
|
Viande |
Bartoli |
450 € |
|
Produit |
Beaune |
6 328 € |
|
Produit |
Bartoli |
6 544 € |
|
Formule |
Description |
Résultat |
|
’=BDNBVAL(A6:C10;;A1:B3) |
Compte le nombre de lignes (3) répondant à l’une des conditions dans la plage A1:B3. |
=BDNBVAL(A6:C10;;A1:B3) |
Critères créés à partir d’une formule
Vous pouvez utiliser comme critère une valeur calculée qui résulte d’une formule. Toutefois, gardez les points suivants à l’esprit ; ils sont importants :
-
La formule doit avoir pour résultat VRAI ou FAUX.
-
Entrez la formule comme vous le faites habituellement, mais ne tapez pas l’expression comme suit :
="= entrée "
-
N’utilisez pas d’étiquettes de colonnes comme étiquettes de critères et ne remplissez pas les étiquettes de critères ou bien utilisez des étiquettes qui ne soient pas des étiquettes de colonnes de la plage (dans les exemples ci-dessous, Moyenne calculée et Concordance exacte).
Si vous utilisez une étiquette de colonne dans la formule à la place d’une référence relative à une cellule ou un nom de plage, Microsoft Excel affiche une valeur d’erreur telle que #NOM? ou #VALUE!, dans la cellule contenant le critère. Vous pouvez ignorer cette erreur, car elle n’a aucune incidence sur la manière dont la plage est filtrée.
-
La formule employée pour le critère doit utiliser une référence relative pour se référer à la cellule correspondante dans la première ligne.
-
Toutes les autres références de la formule doivent être des références absolues.
Filtrage de valeurs supérieures à la moyenne des valeurs dans la plage de données
Dans la plage de données suivante (A6:C10), la plage de critères (C1:C2) sert à compter les lignes dont la valeur dans la colonne Ventes est supérieure à la moyenne de toutes les valeurs Ventes (C7:C10). La moyenne est calculée dans la cellule C4 et le résultat est combiné dans la cellule C2 avec la formule =">"&C4 afin de créer le critère employé.
|
Ventes |
||
|
=CONCATENER(">";C4) |
||
|
Moyenne calculée |
||
|
=MOYENNE(C7:C10) |
||
|
Catégorie |
Vendeur |
Ventes |
|
Boissons |
Suyama |
5 122 € |
|
Viande |
Bartoli |
450 € |
|
Produit |
Beaune |
6 328 € |
|
Produit |
Bartoli |
6 544 € |
|
Formule |
Description |
Résultat |
|
’=BDNBVAL(A6:C10;;C1:C2) |
Compte le nombre de lignes (3) répondant à la condition (>4611) dans la plage C1:C2. La condition dans C2 est créée par la concaténation de =">" avec la cellule C4, qui est la moyenne calculée de C7:C10. |
=BDNBVAL(A6:C10;;C1:C2) |
