Qu'est-ce que la fonction BDECARTYPE ?
BDECARTYPE (DSTDEV en anglais) te sert quand une moyenne globale masque des réalités très différentes d'un service, d'une machine ou d'une zone à l'autre, et que tu veux savoir laquelle mérite vraiment ton attention. Plutôt que d'extraire tes lignes à la main avant de lancer un calcul d'écart-type, tu filtres directement dans la formule grâce à une zone de critères.
Dans la pratique, c'est elle qui répond aux questions du type : les ventes de la région Nord sont-elles régulières ou très variables ? Les temps de production de la machine M1 sont-ils stables ? Les salaires du département IT sont-ils homogènes ? Elle te permet d'identifier rapidement les zones de forte variabilité qui nécessitent ton attention.
Syntaxe
=BDECARTYPE(base_de_données; champ; critères)Clique sur un argument pour aller à son explication.
BDECARTYPE utilise la formule de l'écart-type d'échantillon (divise par n-1). Si tes données représentent la population entière (et non un échantillon), utilise BDECARTYPEP (divise par n). Avec un seul enregistrement correspondant, la fonction retourne #DIV/0!.
Comprendre chaque paramètre de la fonction BDECARTYPE
Les trois arguments s'enchaînent toujours dans le même ordre : d'abord la base complète avec ses en-têtes, puis le champ chiffré à analyser, enfin la zone de critères qui filtre les lignes. Aucun n'est facultatif.
Le champ se désigne soit par son nom entre guillemets ("Ventes"), soit par sa position (3), et la zone de critères doit reprendre les en-têtes de ta base à l'identique pour que le filtrage fonctionne.
base_de_données
: c'est la plage de cellules qui contient toute ta base de données, en-têtes de colonnes comprisesPar exemple, A1:D50 où A1:D1 contient les titres de colonnes (Région, Vendeur, Mois, Ventes) et A2:D50 les données. Les en-têtes sont indispensables pour que la fonction puisse identifier les champs.
Inclure la ligne d'en-têtes est non négociable : si tu sélectionnes A2:D50 (sans en-têtes), BDECARTYPE interprètera ta première ligne de données comme des noms de colonnes et faussera complètement le résultat.
Attention : N'oublie jamais d'inclure la ligne d'en-têtes dans ta plage, sinon BDECARTYPE ne pourra pas interpréter correctement tes données.
champ
: c'est la colonne sur laquelle tu veux calculer l'écart-typeTu peux l'indiquer de deux façons : soit avec le nom de la colonne entre guillemets comme "Ventes", soit avec sa position numérique comme 3 (pour la 3ème colonne). Le nom entre guillemets est plus clair et évite les erreurs si tu réorganises tes colonnes.
Ce champ doit contenir des valeurs numériques. Si tu essaies de calculer l'écart-type sur une colonne de texte, tu obtiendras une erreur.
critères
: c'est une zone séparée qui définit tes conditions de filtrageCette zone doit avoir les en-têtes sur la première ligne (correspondant exactement à ceux de ta base), et les conditions sur les lignes suivantes. Par exemple, pour filtrer sur Région = "Nord", ta zone de critères aura "Région" en F1 et "Nord" en F2.
Tu peux combiner plusieurs critères : plusieurs conditions sur la même ligne = ET logique (Région="Nord" ET Vendeur="Marie") ; plusieurs lignes = OU logique (Région="Nord" en ligne 2, Région="Sud" en ligne 3). Les opérateurs de comparaison comme >50000 et les wildcards comme Nord* sont aussi supportés.
Exemples pratiques pas à pas
Comment calculer l'écart-type d'un échantillon selon un critère avec la fonction BDECARTYPE
Un salarié de l'IT vient de partir en affirmant que les salaires du service partent dans tous les sens, et tu veux vérifier ce que vaut cette accusation avant d'en parler à la direction. Tes quatorze salariés sont listés avec leur département et leur salaire, et il te faut un chiffre en euros qui dise à quel point les rémunérations de l'IT s'écartent de leur moyenne. La fonction BDECARTYPE lit une zone de critères posée à côté du tableau et calcule l'écart-type des seules lignes qui y répondent.
- 1Dans une cellule, écris
=BDECARTYPE(. - 2En 1er argument : sélectionne la base complète avec sa ligne d'en-têtes,
A1:C15. Ce sont les titresEmployé,DépartementetSalairequi permettent à la fonction de savoir quelle colonne porte quel nom, donc les laisser en dehors de la plage n'est pas une option. - 3En 2ᵉ argument : écris le nom de la colonne dont tu veux mesurer la dispersion,
"Salaire". Ce nom se recopie à l'identique de l'en-tête de la colonne C et s'écrit entre guillemets. C'est la dispersion de cette colonne, et d'aucune autre, que la fonction calcule. - 4En 3ᵉ argument : sélectionne la zone de critères,
E1:E2. Elle se prépare à côté du tableau avant d'écrire la formule, avec l'en-têteDépartementrecopié en E1 et la valeurITjuste en dessous, en E2. ÉcrisVentesen E2 à la place deITet l'écart-type de l'autre service s'affiche, sans que tu touches à la formule. - 5Ferme la parenthèse et appuie sur Entrée.
=BDECARTYPE(A1:C15; "Salaire"; E1:E2)
Comment rapporter l'écart-type à la moyenne avec les fonctions BDECARTYPE et BDMOYENNE
L'IT et les Ventes ne jouent pas dans les mêmes ordres de grandeur de salaire, et comparer leurs écarts-types bruts en euros n'apprend rien : le même écart en euros ne pèse pas pareil selon le niveau des rémunérations. Tu veux un indicateur sans unité, qui se compare d'un service à l'autre. Le coefficient de variation rapporte la dispersion d'un groupe à sa propre moyenne et sort un pourcentage.
- 1Dans une cellule, écris
=BDECARTYPE(. - 2En 1er argument : sélectionne la base complète avec sa ligne d'en-têtes,
A1:C7. Six salariés y sont listés avec leur département et leur salaire, et cette même plage sera reprise à l'identique par la fonction du dénominateur. - 3En 2ᵉ argument : écris le nom de la colonne chiffrée à analyser,
"Salaire", orthographié comme l'en-tête de la colonne C. - 4En 3ᵉ argument : sélectionne la zone de critères,
E1:E2, où l'en-têteDépartementest recopié en E1 et la valeurITécrite en E2. C'est elle qui réduit le calcul aux seuls salaires de l'IT. - 5Ferme la parenthèse, complète par
/BDMOYENNE(A1:C7; "Salaire"; E1:E2), puis appuie sur Entrée. La fonction BDMOYENNE reçoit les mêmes trois arguments aux mêmes places, ce qui garantit que la moyenne porte exactement sur les lignes dont on vient de mesurer la dispersion. Le rapport obtenu n'a plus d'unité, et c'est le format de cellule qui l'affiche en pourcentage.
=BDECARTYPE(A1:C7; "Salaire"; E1:E2)/BDMOYENNE(A1:C7; "Salaire"; E1:E2)
Les erreurs fréquentes avec la fonction BDECARTYPE
Avec BDECARTYPE, les ennuis viennent presque toujours du même endroit : la communication entre ta base et ta zone de critères. Un en-tête mal recopié, une plage qui oublie la ligne de titres, et le filtre ne trouve plus rien ou avale tout.
Deux autres pièges sont propres au calcul lui-même : un seul enregistrement retenu déclenche #DIV/0! (l'écart-type d'échantillon a besoin d'au moins deux valeurs), et un montant comme 55 000 € avec le symbole stocké dans la cellule est vu comme du texte, donc ignoré.
Erreur #DIV/0! avec une seule valeur correspondant aux critères
BDECARTYPE calcule l'écart-type d'un échantillon, ce qui nécessite au minimum 2 valeurs. Si tes critères ne retournent qu'une seule ligne de données, le calcul divise par (n-1) = 0, ce qui provoque l'erreur.
Solution : Vérifie que tes critères ne sont pas trop restrictifs. Élargis tes filtres ou assure-toi qu'il y a bien plusieurs enregistrements qui correspondent. Avant d'appliquer BDECARTYPE, utilise =BDNB(A1:C50;"Ventes";E1:E2) pour compter le nombre de lignes correspondantes.
Zone de critères mal configurée – résultat 0 ou incorrect
Si les en-têtes de ta zone de critères ne correspondent pas exactement aux en-têtes de ta base (espace en trop, majuscule différente, accent manquant), BDECARTYPE ne trouve aucune correspondance ou analyse toutes les lignes sans filtrage.
Solution : Copie-colle directement les en-têtes depuis ta base vers ta zone de critères pour éviter toute erreur de frappe. Vérifie avec =BDNB() que le nombre de lignes correspondantes est cohérent avec tes attentes.
Résultat faux car les en-têtes ne sont pas inclus dans la base
Si tu sélectionnes A2:C50 (sans la ligne d'en-têtes) dans le premier paramètre, BDECARTYPE interprète ta première ligne de données comme des noms de colonnes, ce qui fausse complètement le résultat ou provoque une erreur.
Champ numérique contenant des symboles texte dans les cellules
Si ta colonne de montants contient "55 000 €" avec le symbole euro stocké dans la cellule (et non juste dans le format d'affichage), BDECARTYPE ignore ces cellules car elles sont de type texte et non numérique.
Solution : Garde les symboles monétaires uniquement dans le format de cellule, pas dans le contenu. Utilise le format personnalisé 0 "€" pour afficher le symbole sans qu'il soit stocké. Pour corriger des données existantes, utilise =CNUM(SUBSTITUE(A1;"€";"")) pour extraire la valeur numérique.
BDECARTYPE vs ECARTYPE vs BDVAR vs BDECARTYPEP
Choisis BDECARTYPE dès que tu veux l'écart-type d'un sous-groupe précis (une région, un département) sans extraire les données à la main : c'est elle qui filtre via une zone de critères. Si tu n'as aucun filtre à appliquer et juste une plage à mesurer, ECARTYPE suffit largement.
Bascule sur BDECARTYPEP quand tes lignes filtrées représentent toute la population et non un échantillon (division par n au lieu de n-1), et garde BDVAR pour le même filtrage mais quand c'est la variance, pas l'écart-type, qui t'intéresse.
| Critère | BDECARTYPE | ECARTYPE | BDVAR | BDECARTYPEP |
|---|---|---|---|---|
| Type de calcul | Écart-type d'échantillon (n-1) | Écart-type d'échantillon (n-1) | Variance d'échantillon (n-1) | Écart-type de population (n) |
| Filtrage par critères | Oui, multi-critères | Non | Oui, multi-critères | Oui, multi-critères |
| Structure de données | Base structurée avec en-têtes | Plage simple | Base structurée avec en-têtes | Base structurée avec en-têtes |
| Cas d'usage idéal | Variabilité d'un sous-groupe (échantillon) | Écart-type simple sur toute une plage | Variance d'un sous-groupe | Variabilité de la population entière |
Astuces avancées avec BDECARTYPE
Combiner plusieurs critères avec ET et OU
Pour un critère ET (région Nord ET vendeur senior), place les deux conditions sur la même ligne de ta zone de critères. Pour un critère OU (région Nord OU région Sud), place les conditions sur des lignes différentes sous les mêmes en-têtes.
Cette logique ET/OU s'applique à toutes les fonctions BD, pas seulement BDECARTYPE.
Créer des tableaux de bord dynamiques avec des listes déroulantes
Au lieu de coder tes critères en dur dans la zone de critères, référence des cellules contenant des listes de validation. Place =F5 dans ta zone de critères au lieu de "Nord", où F5 est une liste déroulante. BDECARTYPE se met à jour automatiquement quand tu changes la valeur.
Tu obtiens ainsi un tableau de bord interactif sans modifier les formules.
Calculer le coefficient de variation pour comparer des groupes
Pour comparer la variabilité de deux ensembles de données ayant des moyennes différentes (par exemple, les ventes de deux régions avec des CA très différents), divise l'écart-type par la moyenne. C'est le coefficient de variation (CV) exprimé en pourcentage.=BDECARTYPE(A1:C50;"Ventes";E1:E2)/BDMOYENNE(A1:C50;"Ventes";E1:E2)*100 : un CV de 10% indique une faible variabilité, 20-30% une variabilité modérée.
Questions fréquentes
ECARTYPE calcule l'écart-type sur une plage simple de cellules sans conditions. BDECARTYPE te permet de filtrer tes données selon des critères avant de calculer l'écart-type, ce qui est idéal pour analyser des sous-groupes spécifiques dans une base de données structurée. Si tu veux l'écart-type de TOUTES les ventes, utilise ECARTYPE. Si tu veux l'écart-type des ventes d'une seule région, utilise BDECARTYPE.
BDECARTYPE calcule l'écart-type d'un échantillon (divise par n-1 selon la correction de Bessel). BDECARTYPEP calcule l'écart-type de la population entière (divise par n). Utilise BDECARTYPE si tes données représentent un sous-ensemble (par exemple, les ventes d'un trimestre parmi plusieurs années). Utilise BDECARTYPEP si tu analyses la totalité de ta population.
L'écart-type mesure la dispersion de tes données autour de la moyenne. Plus il est élevé, plus tes données sont dispersées. En statistique, environ 68% des valeurs se trouvent à ±1 écart-type de la moyenne, et 95% à ±2 écarts-types. Un faible écart-type indique des données homogènes, un fort écart-type indique des données très variables.
Ta zone de critères doit avoir sur la première ligne les mêmes en-têtes que ta base de données (attention aux espaces et majuscules). Sur les lignes suivantes, tu places tes conditions. Plusieurs critères sur la même ligne = ET logique, sur des lignes différentes = OU logique. La façon la plus sûre : copie-colle les en-têtes depuis ta base vers ta zone de critères.
Oui. Tu peux utiliser des opérateurs comme >50000, <100, <>0 (différent de zéro) directement dans ta zone de critères pour créer des filtres numériques. Tu peux aussi utiliser des wildcards avec * pour des correspondances partielles, comme Nord* pour trouver toutes les régions commençant par "Nord".
Pour aller plus loin
Continue sur ta lancée après la fonction BDECARTYPE : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
