XL 2016 [RESOLU] Comptage complexe NB.SI.ENS plages communes

Brain Box · 26 Septembre 2018

Bonsoir le forum !

Je viens quérir votre intelligence face à un problème qui me résiste depuis plusieurs heures et dont je ne vois pas le bout...

Dans un lointain passé (pas si lointain mais tout de même un peu : https://www.excel-downloads.com/threads/génération-de-carrés-latins-incomplets.20006694/), Dranreb (que j'imagine facilement se prénommer Bernard ) m'avait sorti d'un problème en m'aidant à construire une macro générant des 'plans d'expérience' statistiques (pseudo grilles de Sudoku pour faire simple).

Tout ceci est d'une précision chirurgicale, que je ne remets pas du tout en cause

J'aimerais aujourd'hui aller un pas plus loin, en vérifiant l'équilibre de séries plus complexes que celles générées par la macro-commande en question : je réalise peu ou proue le même travail via le logiciel R (logiciel de statistiques).

Mais je m'égare... mon problème est le suivant : disposant d'une matrice colonnes/lignes dont la dimension peut varier, chaque cellule de cette matrice comprend un nombre qui représente un produit (que nous appellerons donc Produit 1 / Produit 2...). Je suis en mesure de compter très simplement le nombre de fois qu'un chiffre apparaît dans la matrice à l'aide la fonction NB.SI.
Je suis également en mesure de tester (SI / ET) la présence de couples de produits sur une même ligne (par exemple : produit 1 est testé avec produit 2 sur la première ligne).

PAR CONTRE, je ne suis pas (encore) capable de compter combien de fois le couple est présent dans l'ensemble de ma matrice... J'ai bien essayé NB.SI.ENS, mais soit je n'ai pas compris comment l'utiliser, soit je dois la combiner avec d'autres formules (SOMMEPROD ? / INDIRECT ? / LIGNE ? ...).

En parcourant ce forum et d'autres anglophones, je n'ai pas l'impression d'avoir trouvé ce problème spécifique, d'où ma bouteille à la mer ! La complexité venant du fait que les plages à renseigner dans NB.SI.ENS sont les mêmes pour toutes les conditions, là ou la plupart des sujets traitent de plages indépendantes

Merci d'avance pour vos lumières !

PS : Je pourrais créer une boucle de comptage en VBA, mais pour des raisons de partage, je vous sollicite plutôt pour une fonction Excel (même complexe !).

JHA · 26 Septembre 2018

Bonjour à tous,

Pour rechercher le chiffre:

Code:

=SOMME.SI($C$4:$E$27;$L4;$C$4:$E$27)/$L4

mais un simple

Code:

=NB.SI($C$4:$E$27;$L4)

suffit

En "L4", le chiffre à chercher

Oups!pas lu jusqu'au bout

JHA

Brain Box · 26 Septembre 2018

Bonjour @JHA,

Merci pour la formule, qui me permet de faire un comptage de chaque numéro à l'aide d'une formule déroulante (c'est déjà un progrès !).

Je ne sais pas si vous avez indiqué

Oups!pas lu jusqu'au bout

pour cette raison, mais mon objectif est plutôt de faire un comptage à contrainte : je souhaite savoir combien de fois je trouve les produits 1 ET 2 SUR UNE MEME LIGNE, combien de fois les produits 1 ET 3 sur une même ligne...
La philosophie derrière cette formule et de vérifier que la matrice est équilibrée : d'une part j'ai bien chaque produit testé autant de fois (la formule =SOMME.SI($C$4:$E$27;$L4;$C$4:$E$27)/$L4), mais surtout que les couples Produits sont eux aussi équilibrés (autant de lignes avec 1 ET 2 que 1 ET 3 par exemple).

J'espère que c'est plus clair, désolé pour la confusion dans mon premier message !

Maxence

Brain Box · 27 Septembre 2018

Bonjour à tous,

Comme je ne m'en sors pas avec Excel, je suis passé par VBA, qui arrive sans problème résoudre mon problème. Voici la fonction personnalisée en question :

Code:

Public Function EFFET_UNIVERS(Matrice As Range, FirstNB As Range, SecondNB As Range) As Integer

'DEFINITION DES VARIABLES

'Matrice As Range 'Plage des données
'FirstNB As Range, SecondNB As Range 'Emplacement des nombres à chercher
Dim i As Integer, j As Integer, k As Integer 'Indices de boucles
Dim Compteur As Integer 'Nombre de couples identifiés dans la matrice

Application.Volatile

For i = 1 To Matrice.Rows.Count
    For j = 1 To Matrice.Columns.Count
        If Matrice(i, j).Value = FirstNB.Value Then
            For k = 1 To Matrice.Columns.Count
                If Matrice(i, k).Value = SecondNB.Value Then
                    If FirstNB.Value = SecondNB.Value Then
                        Compteur = Compteur
                    Else
                        Compteur = Compteur + 1
                    End If
                Else
                End If
            Next
        Else
        End If
    Next
Next

EFFET_UNIVERS = Compteur

End Function

La question qui reste en suspens... est-il possible de transformer cette fonction personnalisée en une (ou plusieurs) fonction(s) Excel gérée(s) nativement par n'importe quel utilisateur ? (je remets le fichier si vous souhaitez tester)

Maxence

Jocelyn · 27 Septembre 2018

Bonjour le Forum,
Bonjour Brain Box, JHA ,

Un essai par formule SOMMEPROD() (Ne ce sert pas de la macro)

Cordialement

Brain Box · 28 Septembre 2018

Bonjour le forum, bonjour Jocelyn,

Alors là c'est du TRÈS TRÈS HAUT NIVEAU ! Je dis bravo, c'est exactement le résultat escompté. J'imaginais bien une histoire de SOMMEPROD quelque part, mais je ne suis pas encore à l'aise avec la syntaxe de cette formule... Du moins pour lui faire faire un comptage.

J'évalue bien la formule depuis l'onglet FORMULES pour voir le déroulé pas à pas, mais je ne suis pas sûr d'arriver à l'adapter si besoin (dans le cas de matrices avec plus de colonnes par exemple). Etant donné le nombre de cas de figures à tester pour être exhaustif (colonne 1 vs 2, 1 vs 3, 2 vs 1...), cela risque de me causer quelques cheveux blancs

En tout cas, merci beaucoup pour ce coup de main, il y a décidément de sacrées pointures sur ce forum

Je passe le titre de la discussion en résolu.

Maxence

Jocelyn · 28 Septembre 2018

re,

Bon pour le Fun une formule plus courte et plus facile a mettre en œuvre si plus de colonnes voir tableau sur fond jaune

Cordialement

Brain Box · 29 Septembre 2018

Bonsoir à tous,

Tous les chemins mènent à Rome dit-on, cette nouvelle formulation en est l'exemple concret ! Merci encore pour cette démonstration

Maxence

XL 2016 [RESOLU] Comptage complexe NB.SI.ENS plages communes

Brain Box

XLDnaute Nouveau

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

Brain Box

XLDnaute Nouveau

Brain Box

XLDnaute Nouveau

Pièces jointes

Jocelyn

XLDnaute Barbatruc

Pièces jointes

Brain Box

XLDnaute Nouveau

Jocelyn

XLDnaute Barbatruc

Pièces jointes

Brain Box

XLDnaute Nouveau

Discussions similaires