Monsieur Excel
Pour tout savoir faire sur Excel !

18 septembre 2012

Comptage de groupes de « 1 »

Un challenge posé récemment dans un de mes groupes d'experts était de trouver une formule calculant, dans un bloc contenant des « 0 » et des « 1 », le nombre de groupes de un « 1 », de deux « 1 » consécutifs (au bord ou séparés par des « 0 »), de trois « 1 », … et ainsi de suite.

Pour comprendre la formule que j’ai trouvée, il est préférable d’avoir lu les articles précédents sur la fonction substitue(), en particulier le tout dernier, publié il y a quatre jours.

Dans le modèle reproduit ci-dessous, nous affichons en A7:A11 les résultats obtenus dans les cellules A1:A5. Cette présentation décalée nous permet d’éviter d’avoir une copie d’écran trop large…

Voici la formule magique entrée en A7 puis recopiée vers le bas :

="Un 1 : "&nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"010";"00"))&" - "&
"Deux 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"0110";"00")))/2&" - "&
"Trois 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"01110";"00")))/3&" - "&
"Quatre 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"011110";"00")))/4&" - "&
"Cinq 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"0111110";"00")))/5&" - "&
"Six 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"01111110";"00")))/6

Remarque – Nous avons été obligés de faire un double substitue() : le premier pour doubler tous les « 0 », le second pour ajouter des « 0 » aux bords.

Certes, cette formule est un peu lourde, mais elle fonctionne parfaitement !

1 Commentaire(s):

  • Bonjour,

    Je pense que là on utilise un peu trop de substitue. Enfin on peut le faire en 2 substitue ;).
    Il faut faire 2 fois le remplacement de "010";"00" dans la même chaîne afin d’être sur de remplacer "01010" par "000"

    ="Un 1 : "&NBCAR(A1)+2-NBCAR(SUBSTITUE(SUBSTITUE("0"&A1&"0";"010";"00");"010";"00"))

    au lieu de :

    ="Un 1 : "&nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"010";"00"))

    ce qui est quand même un peu plus court :)

    Pour ma part j'aurais fait plusieurs formules:
    1 part séquence en colonne (Un 1, Deux 1, ...) et j'aurais concaténer le tout à la fin pour une facilité de maintenance de code.

    Ainsi on peut passer "010" et "00" en paramètre et étendre la formule sur toutes les colonnes. De plus on ne sera pas limiter si on veut compter une longue séquence de 1. Ex (Vingt deux 1).

    j'essaie de voir si on ne peut pas trouver encore quelque chose de plus court :)

    By Blogger Benji, sur 1:43 PM  

Enregistrer un commentaire

<< Accueil