XL 2021 calculer nombre de cellules contant 1 dans colonnes non contigües

[DUMART]

Bonjour, je cale sur une formule me permettant de calculer le nombre de cellules contant le chiffre 1 dans des cellules non contigües.

Ex ; col A B C D E F G H I J K L
********2 1 1 1 3 0 2 4 5 1 7 0

Le calcule consiste à vérifier dans les colonnes B D F H J L si la valeur est bien 1. Si oui déterminer le nombre de fois ou 1 apparait. Dans l'exemple le résultat est : 3. Cela sans tenir compte des cellules A C E G I K, car elles contiennent des 1 mais ne doivent être comptabilisées.

Si quelqu'un à la réponse, ce serait sympa.
Merci pour toutes vos propositions.

 

[Saboh12617]

Bonjour,
En stockant les positions de colonnes à garder dans une liste, on peut filtrer la plage puis faire l'équivalent d'un NB.SI avec SOMME :

=SOMME(1 * (FILTRE(A1:L1; {0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1}) = 1))

Ici j'ai indiqué dans {0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1} les colonnes à garder par un 1, et celles à skip par un 0.

 

[Cousinhub]

Bonjour,
autre solution, compatible Excel2021 et +

=LET(a;A1:L1;NB(FILTRE(a;EST.PAIR(COLONNE(a))*a=1)))

Bonne apm

 

[mapomme]

Bonjour ,
Ou plus classiquement :
=SOMMEPROD((B1:L999=1)*EST.PAIR(COLONNE(B1:L999)))

rem: 999 est un majorant du nombre de max de lignes susceptibles de contenir des données ( à adapter).

 

[DUMART]

Bonjour ,
Ou plus classiquement :
=SOMMEPROD((B1:L999=1)*EST.PAIR(COLONNE(B1:L999)))

rem: 999 est un majorant du nombre de max de lignes susceptibles de contenir des données ( à adapter).

 

[DUMART]

Bonjour, mapomme.
J'ai utilisé ta formule cela fonctionne bien , sauf pour les cellules avec 0. Je pense que c'est dû au fait qu'il y a des cellules vides. Y aurait-il un moyen d'ignorer les cellules vides ?
merci pour ton aide déjà et si tu une solution ce serait top.
Michel

 

[job75]

Bonjour DUMART,

Si vous recherchez les valeurs 1 les cellules vides ou contenant 0 ne sont pas comptées.

Mais si vous recherchez les valeurs 0 les cellules vides sont comptées.

Pour ne pas les compter utilisez :

=SOMMEPROD((""&B1:L999="0")*EST.PAIR(COLONNE(B1:L999)))

ou bien :

=SOMMEPROD(EXACT(B1:L999;0)*EST.PAIR(COLONNE(B1:L999)))

A+

 

[DUMART]

[td]

1​

[/td][td]

1​

[/td]​

[td]

​

[/td]​

[td]

2​

[/td][td]

1​

[/td][td]

​

[/td]
[td]

5​

[/td][td]

0​

[/td][td]

​

[/td]
[td]

6​

[/td][td]

0​

[/td][td]

​

[/td]
[td]

10​

[/td][td]

3​

[/td][td]

​

[/td]
[td]

12​

[/td][td]

2​

[/td][td]

​

[/td]
[td]

13​

[/td][td]

3​

[/td][td]

​

[/td]
[td]

18​

[/td][td]

0​

[/td][td]

​

[/td]
[td]

21​

[/td][td]

2​

[/td][td]

​

[/td]
[td]

24​

[/td][td]

0​

[/td]​

[td]

​

[/td]​

Voici une ligne de ce que j'ai. La recherche sur 0 même avec les nouvelles fonctions ne compte pas exactement.
Pour les 0 j'ai un résultat de 3 (au lieu de 4) et pour les 1 un résultat de 2(résultat exact), pour les 2 résultat de 1 (au lieu de 2), pour les résultat de 3 un résultat de 0 (au lieu de 2). J'ai bien entendu changer les valeurs 0, 1, 2, 3, à chaque formule.
J'ai utilisé : =SOMMEPROD((""&D101:AG101="0")*EST.PAIR(COLONNE(D101:AG101))) La colonne contenant le 1 en noir a gauche est D et la colonne vide à droite du 0 en rouge est AG. Ce qui donne le résultat ci dessous, sachant que le 3 correspond au 0, le 2 est le résultat pour 1, le 1 est le résultat pour le 2 et le 0 le résultat pour le 3. Merci de me dire si tu vois où est le problème.
Avec tous mes remerciements pour ton travail.
Michel

[td]

3​

[/td]​

[td]

2​

[/td]​

[td]

1​

[/td]​

[td]

0​

[/td]​

 

[DUMART]

[td]
1

[/td][td]
1

[/td]​

[td]


[/td]​

[td]
2

[/td][td]
1

[/td][td]


[/td]
[td]
5

[/td][td]
0

[/td][td]


[/td]
[td]
6

[/td][td]
0

[/td][td]


[/td]
[td]
10

[/td][td]
3

[/td][td]


[/td]
[td]
12

[/td][td]
2

[/td][td]


[/td]
[td]
13

[/td][td]
3

[/td][td]


[/td]
[td]
18

[/td][td]
0

[/td][td]


[/td]
[td]
21

[/td][td]
2

[/td][td]


[/td]
[td]
24

[/td][td]
0

[/td]​

[td]


[/td]​

Voici une ligne de ce que j'ai. La recherche sur 0 même avec les nouvelles fonctions ne compte pas exactement.
Pour les 0 j'ai un résultat de 3 (au lieu de 4) et pour les 1 un résultat de 2(résultat exact), pour les 2 résultat de 1 (au lieu de 2), pour les résultat de 3 un résultat de 0 (au lieu de 2). J'ai bien entendu changer les valeurs 0, 1, 2, 3, à chaque formule.
J'ai utilisé : =SOMMEPROD((""&D101:AG101="0")*EST.PAIR(COLONNE(D101:AG101))) La colonne contenant le 1 en noir a gauche est D et la colonne vide à droite du 0 en rouge est AG. Ce qui donne le résultat ci dessous, sachant que le 3 correspond au 0, le 2 est le résultat pour 1, le 1 est le résultat pour le 2 et le 0 le résultat pour le 3. Merci de me dire si tu vois où est le problème.
Avec tous mes remerciements pour ton travail.
Michel

[td]
3

[/td]
[td]
2

[/td]
[td]
1

[/td]
[td]
0

[/td]​

Voici une copie d'cran car le message ci-avant semble avoir donné un résultat difficilement compréhensible.
En premier les chiffres en colonne de D à AG. Ensuite 3 2 1 0 est le résultat obtenu.

 

[job75]

Bonjour DUMART,

La formule utilise la fonction EST.PAIR donc seules les colonnes paires sont comptées.

Votre tableau commence en colonne D donc :

- le 1 en colonne E (5) n'est pas compté

- le 0 en colonne K (11) n'est pas compté

- le 3 en colonne Q (17) n'est pas compté

- le 3 en colonne W (23) n'est pas compté

- le 2 en colonne AC (29) n'est pas compté.

A+

 

[laurent950]

Bonsoir @job75 , @DUMART , le forum

Somme prod colonne non contigüe.
La Formule : pour la ligne 3 de la [Colonne de A à AC]
=SOMMEPROD((A3:AC3=0)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 4 pour les 0
=SOMMEPROD((A3:AC3=1)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 2 pour les 1
=SOMMEPROD((A3:AC3=2)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 2 pour les 2
=SOMMEPROD((A3:AC3=3)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 2 pour les 3

Pour les 0 j'ai un résultat de 3 (au lieu de 4) et pour les 1 un résultat de 2(résultat exact), pour les 2 résultat de 1 (au lieu de 2), pour les résultat de 3 un résultat de 0 (au lieu de 2)

 

[job75]

Bonsoir laurent950,

Comme cela a été dit plus haut A3:AC3=0 comptera aussi les cellules vides, il faut ""&A3:AC3="0"

A+

 

[Cousinhub]

Bonsoir,
Quitte à avoir 2021, autant en utiliser les avantages, non?
Cette formule devrait convenir, pour les valeurs 0 (à recopier vers la droite, en modifiant la valeur à dénombrer pour 1, 2, 3)

=LET(a;$D1:$AG1;NB(FILTRE(a;EST.IMPAIR(COLONNE(a))*(a=0))))

Bonne soirée

 

[laurent950]

Bonsoir @job75
Formule :
=SOMMEPROD((A3:AC3=0)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 4 pour les 0

Décomposition ci-dessous

On utilise MOD pour reproduire un pattern régulier sur une plage de cellules,​

comme une expression régulière (regex) mais appliquée aux colonnes.​

Le pattern

Chaque groupe contient 3 cellules :​

[A , B , C] ' On veut travailler uniquement sur la cellule B de chaque groupe.​

Deux conditions dans la formule

Condition 1 :​

la cellule doit contenir la valeur recherchée (ex : 0) dans la cellule B de chaque groupe.​

Condition 2 :​

la cellule doit être la 2ᵉ cellule de chaque groupe de 3​

MOD(COLONNE(plage) - COLONNE(première cellule); 3) = 1​

Pourquoi COLONNE(plage) - COLONNE(première cellule)

COLONNE(A3:AC3) donne des indices Excel : Excel Base 1​

1,2,3,4,...,29​

Mais pour appliquer un pattern régulier, on travaille en base 0 (Mémoire machine) :

0,1,2,3,...,28​

Donc on écrit :

COLONNE(A3:AC3) - COLONNE(A3)​

Pourquoi MOD(...;3)

MOD(x;3) donne la position de chaque cellule dans un groupe de 3 :​

0,1,2 | 0,1,2 | 0,1,2 | ...​

Groupes :

Avant MOD :​

Groupe 1 : 0 1 2​

Groupe 2 : 3 4 5​

Groupe 3 : 6 7 8​

Après MOD :​

0 1 2 | 0 1 2 | 0 1 2 | ...​

Pourquoi = 1

Parce que dans un groupe [0,1,2],​

la position 1 correspond à la 2ᵉ cellule (B) du groupe : référence a en base 0 (mémoire machine)​

0 = A ← Exclus de SOMMEPROD ​

1 = B ← celle qu’on garde (si elle contiennent 0) on fait la somme avec SOMMEPROD​

2 = C ← Exclus de SOMMEPROD ​

Donc :

MOD(...;3)= 1 pour rappel [A , B , C] ' On veut travailler uniquement sur la cellule B de chaque groupe.​

Logique : [A , B , C] | [D , E , F] | [G , H , I] | Etc.​

sélectionne uniquement les cellules B, E, H, K, Etc.​

Rôle de SOMMEPROD

SOMMEPROD combine les deux conditions :​

la cellule est à la bonne position dans le pattern soit B, E, H, K, Etc.​

la cellule contient la valeur recherchée soit 0​

Et additionne les cas où les deux sont vrais.​