matricielle 2 plages d'ampleur inégale
#1
Débutant XLPages

Inscription: 27/05/2007

Messages: 3

Posté le : 27-05-2007 03h34
salut au forum, longue vie à lui,

je peine à mettre en mots ma question.
Essayons quand même :
dans une liste de virtualités il y a
Coul. Réfer.
bleu BQSJy
noir dFVoe
noir IKnUQ
bleu JkFrG
bleu HUDQX

dans une liste de réalités il y a
Réfer. Q1 Q2
dFVoe 1 2
JkFrG 10 20
HUDQX 100 200

Les liste n'ont pas la même taille.
Cependant, je veux compter bleu et noir sans mentionner "bleu" ou "noir" devant chaque Référ.
comme pratiqué dans le fichier joint.

merci de l'attention que vous porterez à ma modeste question. Lam
Pièce jointe:
zip BN-exemple.zip   [ Taille: 3.41 Ko - Téléchargements: 715 ]
Hors Ligne
Rapport   Haut 

Re: matricielle 2 plages d'ampleur inégale
#2
Accro XLPages

Inscription: 09/05/2007
De Courcelles-les-Montbéliard

Messages: 286

Système d'exploitation:
PC & Mac
Version Excel utilisée:
Mac 2004 - 2011, et PC 2003 - 2010
Posté le : 28-05-2007 09h49
Bonjour

Après des heures de recherche (sisi)

Une formule matricielle à mettre en D3,
valable pour toutes les couleurs, sauf la "KO", à tirer vers la droite et vers le bas
=SOMME(FREQUENCE(SI(($A$18:$A$29=$A3)*(ESTNUM(EQUIV($B$18:$B$29;$C$8:$C$15;0)));LIGNE($A$1:$A$12));SI(ESTNUM(EQUIV($C$8:$C$15;$B$18:$B$29;0));EQUIV($C$8:$C$15;$B$18:$B$29;0);))*D$8:D$16)


Le problème dans les multiplications de matrices, c'est qu'elles doivent être de longueur identique. Pour passer une matrice "Verte" à une matrice "jaune", je suis passé par la fonction FREQUENCE, qui va retourner une matrice de 1 et de 0

  • Le premier argument :

  • contient les numéros d'index qui correspondent à la couleur voulue indiquée en $A3, ($A$18:$A$29=$A3)
    (ESTNUM(EQUIV($B$18:$B$29;$C$8:$C$15;0)) permet de savoir si la référence est présente dans la plage "jaune"
    LIGNE($A$1:$A$12), retourne le numéro d'index, la longueur de la plage doit être identique à la longueur de la plage "verte" et doit commencer à la ligne 1.
    Résultat de la matrice {0;0;3;0;0;6;0;0;0;0;0;12}

  • Le deuxième argument :

  • contient les numéros d'index des références de la matrice "jaune" dans la matrice "verte", ce numéro est retourné par la fonction EQUIV
    la condition permet d'écarter les références "KO" non présentent dans la matrice "verte", ce qui provoquerait une erreur par la suite.
    Résultat de la matrice {2;3;0;1;4;6;12;10}

    La fonction fréquence va retourner une matrice qui sera ensuite multipliée par la plage D$8:D$16, vous pouvez noter que cette plage est plus longue d'une unité, en effet fréquence rajoute une unité supplémentaire à la longueur de la matrice de départ.
    Résultat de la matrice {0;1;0;0;0;1;1;0;0}


    Formule pour la cellule D5, donc uniquement à tirer vers la droite
    =SOMMEPROD(ESTNA(EQUIV($C$8:$C$15;$B$18:$B$29;0))*D$8:D$15)

    la partie ESTNA(EQUIV($C$8:$C$15;$B$18:$B$29;0)), permet de tester la non présence de la référence de la plage "jaune", dans la plage "verte"

    J'espère que mes explications sont suffisantes, dans le cas contraire n'hésite pas, cela me permet aussi de m'améliorer.

    @+Jean-Marie
    Edité par JeanMarie le 29/05/2007 23:03:29
    Hors Ligne
    Rapport   Haut 

    Re: matricielle 2 plages d'ampleur inégale
    #3
    Accro XLPages

    Inscription: 23/05/2007
    De Chamrousse

    Messages: 309

    Système d'exploitation:
    PC
    Version Excel utilisée:
    2013
    Posté le : 28-05-2007 12h50
    Bonjour à tous,

    Du grand art, aussi bien dans les formules que dans les explications

    A+


    Cordialement
    Hors Ligne
    Rapport   Haut 

    matricielle 2 plages d'ampleur inégale
    #4
    Débutant XLPages

    Inscription: 27/05/2007

    Messages: 3

    Posté le : 28-05-2007 22h58
    slt Jean-Marie, JCGL,

    merci beaucoup Jean-Marie,
    et du temps passé et de la leçon.

    je vais mettre ça dans ma pipe et tâcher de comprendre le tout;

    "FREQUENCE" , mazette! Vraiment, j'en suis tout esbrouffé ; un vrai régal en perspective.



    au sérieux de l'explication ,
    considérons pour ne pas ajouter le ridicule à l'incompétence que le sujet est clos.

    au plaisir, Lam
    Hors Ligne
    Rapport   Haut 

    matricielle 2 plages d'ampleur inégale
    #5
    Débutant XLPages

    Inscription: 27/05/2007

    Messages: 3

    Posté le : 29-05-2007 19h08
    slaut au forum,
    dédicace spéciale à Jean-Maire,

    après avoir épluché ta contribution,
    rien à retirer de mon enthousiasme initial ;
    voudrais-je insister, les superlatifs me feraient défaut pour saluer "l'élégance" de ta solution.

    Avis au lecteur de passage : ÇA MARCHE DU FEU DE DIEU.

    Espérant pouvoir une fois dépanner quiconque comme je l'ai été,

    Lam
    Hors Ligne
    Rapport   Haut 

    Re: matricielle 2 plages d'ampleur inégale
    #6
    Webmestre

    Inscription: 18/05/2006
    De Saône-et-Loire (71)

    Messages: 1539

    Système d'exploitation:
    PC
    Version Excel utilisée:
    97, 2000, 2002, 2003, 2007, 2010, 2013, 2016 et 365
    Posté le : 29-05-2007 19h38
    Bonsoir Lamhiti,

    Merci à toi pour ce retour.
    Je partage tout à fait ton avis, on ne peut qu'être heureux de son passage dans ce Forum, Jean-Marie et les formules Excel, c'est de la haute voltige là !
    Pour ma part, je salue aussi (comme tu l'as d'ailleurs fait plus haut Lamhiti ainsi que JCGL) l'effort de notre ami pour les explications données. C'est franchement top ! Merci JM... j'en redemande !

    Cordialement,

    Didier_mDF
    Image redimensionnée
    Le Webmaster

    La réponse vous satisfait ? Merci de revenir solder le sujet en [résolu], voir ce lien
    Hors Ligne
    Rapport   Haut 

    Re: matricielle 2 plages d'ampleur inégale
    #7
    Accro XLPages

    Inscription: 09/05/2007
    De Courcelles-les-Montbéliard

    Messages: 286

    Système d'exploitation:
    PC & Mac
    Version Excel utilisée:
    Mac 2004 - 2011, et PC 2003 - 2010
    Posté le : 30-05-2007 22h05
    Bonsoir

  • Ce matin, j'ai signalé à Didier que la formule ne fonctionnerait pas dans le cas d'une "référence" qui se trouverait en doublon dans une des deux plages "verte" ou "jaune".


  • Et j'ai aussi reçu un message en privé pour me montrer une autre formule, la voici

  • =SOMME(N($A3=$A$18:$A$29)*{1.1.1.1.1.1.1.1}*N($B$18:$B$29=TRANSPOSE($C$8:$C$15))*TRANSPOSE($D$8:$D$15))
    et avec un petit commentaire :
    moins belle que la vôtre car elle n'automatise pas {1.1.1.1.1.1.1.1}.

    Après analyse, la partie {1.1.1.1.1.1.1.1}, n'est pas utile, tout comme l'utilisation de la fonction N

    Donc la formule à retenir ...
    =SOMME(($A3=$A$18:$A$29)*($B$18:$B$29=TRANSPOSE($C$8:$C$15))*TRANSPOSE($D$8:$D$15))

    Et là, c'est moi qui applaudit, que l'auteur en soit remercié.

    L'utilisation de la fonction TRANSPOSE permet de créer une matrice à deux dimensions. Superbe, je n'aurais jamais pensé utiliser cette fonction pour résoudre ce problème. Comme quoi on apprend tous les jours.

    Didier ton forum, commence de bien belle manière. Que les futures questions restent au même niveaux, et on va se régaler.

    @+Jean-Marie
    Hors Ligne
    Rapport   Haut 


    Vous pouvez voir les sujets.
    Vous ne pouvez pas débuter de nouveaux sujets.
    Vous ne pouvez pas répondre aux contributions.
    Vous ne pouvez pas éditer vos contributions.
    Vous ne pouvez pas effacez vos contributions.
    Vous ne pouvez pas ajouter de nouveaux sondages.
    Vous ne pouvez pas voter en sondage.
    Vous ne pouvez pas attacher des fichiers à vos contributions.
    Vous ne pouvez pas poster sans approbation.

    [Recherche avancée]


    Qui consulte actuellement ce sujet ?   1 Utilisateur(s) anonymes