Formule calcul occurrences identiques sous condition

Bonjour Le Forum,

Je cherche à formuler un calcul :

Dans une colonne (A), j'ai une liste de personne (Nom-Prénom), dans une colonne (B) des intitulés de formations. Exemple :

Colonne A :	Colonne B :
DUPONT ROMAIN	INFORMATIQUE
DUPONT ROMAIN	INFORMATIQUE
DUPONT ROMAIN	EXCEL
MARTIN STEPHANE	EXCEL
OLIVIER GEORGE	EXCEL

Monsieur DUPONT est 2 fois dans mon tableau, car il a effectué 2 fois la formation informatique, mais à des dates différentes.

J'aimerais que la formule compte Monsieur DUPONT 1 fois et non 2 fois pour la formation informatique. Je ne sais pas comment m'y prendre, malgré plusieurs essais...

Merci de votre aide.

Hello
Il suffit de tous mettre dans un tableau à 2 dimensions et de trier par ordre alphabétique
http://silkyroad.developpez.com/vba/tableaux/#LXIV-D
Après tu boucles sur le tableau pour trouver les doublons (tu trouveras donc 2 entrées avec Dupont)
Il te suffit de garder que celle qu’il te faut.
Pas plus difficile.
Tu peux aussi faire un filtre :
http://silkyroad.developpez.com/vba/tableaux/#LXI

En supposant que tes données vont de la ligne 2 à la ligne 100, tu mets en C2 la formule :

Code:

---

=SI(NB.SI.ENS(B$2:B2;B2;A$2:A2;A2)=1;1;0)

---

Attention, la présence ou non de $ est importante.

Tu copies C2 en C2:C100.

Pour connaitre le nombre de formations informatiques en ne comptant chaque personne qu'une fois :

Code:

---

=SOMME.SI(B2:B100;"INFORMATIQUE";C2:C100)

---

Peut-être qu'un magicien des formules matricielles peut transformer ça en une seule formule.

Bonjour,

La solution du VBA, pour le coup n'est pas approprié à ce que je souhaite faire. Mais merci ;)

Menhir,

Ta solution me plait bien, par contre, la formule me compte DUPONT 2 fois pour la formation informatique.

Citation:

---

Envoyé par Mél3790

Ta solution me plait bien, par contre, la formule me compte DUPONT 2 fois pour la formation informatique.

---

Quels valeurs mets la première formule en face de chaque ligne ?

J'ai répondu trop vite, Menhir, tu me sauve la vie une nouvelle fois.
J'avais fait une mauvaise manip.

Ta solution fonctionne parfaitement, cela me convient :D.

Merci beaucoup

Par contre,

Est-il possible de combiner ceci :

Code:

---

=NB.SI.ENS(Tableau2[FORMATION];A108;Tableau2[CATEGORIE];$B$4;Tableau2[date début];">="&$J$4)

---

(Cette formule cherche les formations informatique par catégorie de personnel (ouvrier ou cadre) dont la date de début de formation est supérieur à celle d'aujourd'hui)

Avec ceci :

Code:

---

=SI(NB.SI.ENS(F$3:F3;F3;B$3:B3;B3)=1;1;0)

---

?

Merci beaucoup.

Une personne aurait une idée ? :(
Merci

Bonjour,
si cette formule fonctionne

Code:

---

=NB.SI.ENS(Tableau2[FORMATION];A108;Tableau2[CATEGORIE];$B$4;Tableau2[date début];">="&$J$4)

---

et s'il s'agit d'additionner ou de soustraire, pourquoi pas

Code:

---

=NB.SI.ENS(Tableau2[FORMATION];A108;Tableau2[CATEGORIE];$B$4;Tableau2[date début];">="&$J$4)+SI(NB.SI.ENS(F$3:F3;F3;B$3:B3;B3)=1;1;0)

---

"-" pour soustraire, mais je n'ai certainement pas compris

Bonjour,
solution par formule matricielle (même si un TCD serait à mon avis la solution la plus appropriée) :

Code:

---

=SOMME(N(FREQUENCE(SI(A$2:A$6=D2;EQUIV(A$2:A$6&B$2:B$6;A$2:A$6&B$2:B$6;0));LIGNE(A$1:A$6))>0))

---

Pièce jointe 211249

A+

Bonjour,
Autre proposition d'utilisation de formule matricielle intégrant les critères statut (cadre, ouvrier) et dates de début de formation.
Pièce jointe 211255
Les noms statut, noms, formation, début désignent dans l'exemple construit les plages B2:B15 , A2:A15 , C2:C15 , D2: D15 .
Les noms choixStatut et choixDébut désignent respectivement les valeurs contenues dans les cellules $I$1 et $K$1.
Le nom choixFormation désigne le contenu de la cellule de la colonne F sur la même ligne que la cellule dans laquelle est écrite une formule utilisant ce nom.
La formule matricielle en G3 est :

Code:

---

=SOMME(SI((formation=choixFormation)*(statut=choixStatut)*(début>=choixDébut);1/NB.SI.ENS(noms;noms;formation;choixFormation;début;">="&choixDébut)))

---

validée par Ctrl + Maj + Entrée
Cordialement
Claude

Merci Paplouco, ta solution fonctionne parfaitement. Et serais-tu comment additionner le coût de la formation informatique pour chaque personne en prenant en compte toutes les conditions précédentes ?
(En admettant que la formation informatique soit de 200 euros, en colonne E de ton tableau)

merci beaucoup

J'ai réussi à adapter, merci beaucoup, cela fonctionne.

Bonjour,

Je "rouvre" cette discussion parce qu'aujourd'hui je dois améliorer cette formule matricielle :

Code:

---

=SOMME(SI((Tableau2[FORMATION]=A266)*(Tableau2[CATEGORIE]=$B$232)*(Tableau2[Etat]<>"Annuler")*(Tableau2[date début]<$B$1);Tableau2[COÛT TOTAL POUR UN OUVRIER :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A266;Tableau2[date début];"<"&$B$1)))

---

Dans cette formule, si l'Etat de la formation est "Annuler", ça ne prend pas en compte son coût.

Aujourd'hui, mon problème est : si une formation est "Annuler" mais est tout de même "Facturée", alors le coût de la formation doit être compté.

Donc il faudrait rajouter dans cette formule un truc du genre : (Tableau2[Facture]="Oui").

J'ai essayé comme ceci :

Code:

---

=SOMME(SI((Tableau2[FORMATION]=A266)*(Tableau2[CATEGORIE]=$B$232)*(Tableau2[Etat]<>"Annuler")*(Tableau2[Facture]="Oui")*(Tableau2[date début]<$B$1);Tableau2[COÛT TOTAL POUR UN OUVRIER :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A266;Tableau2[date début];"<"&$B$1)))

---

Mais ça ne fonctionne pas :(

Quelqu'un aurait une idée ?

Merci beaucoup.

Bonjour,
Essayer la formule transformée ainsi :

Code:

---

=SOMME(SI((Tableau2[FORMATION]=A266)*(Tableau2[CATEGORIE]=$B$232)*((Tableau2[Etat]<>"Annuler")+(Tableau2[Facture]="Oui"))*(Tableau2[date début]<$B$1);Tableau2[COÛT TOTAL POUR UN OUVRIER :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A266;Tableau2[date début];"<"&$B$1)))

---

Cordialement
Claude

Bonjour,

J'essaie dans la journée, je vous tiens au courant, merci !

Génial, ça fonctionne !

Merci beaucoup ! :)

Bonne journée.

Bonjour tout le monde, je repars sur ce sujet, car je suis de nouveau coincée.

L'outil évolue au fur et à mesure, et les personnes qui l'utilise sont de plus en plus exigeantes lol :)

Donc je suis avec cette formule matricielle :

Code:

---

{=SOMME(SI((Tableau2[FORMATION]=A120)*(Tableau2[CATEGORIE]=$H$5)*((Tableau2[Etat]<>"Annuler")+(Tableau2[FORMATION FACTUREE MEME SI ANNULEE ?]="Oui"))*(Tableau2[date début]>=$B$1);Tableau2[COÛT TOTAL POUR UN CADRE :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A120;Tableau2[date début];">="&$B$1)))}

---

Le principe : que ça prenne en compte tous les coûts pour des personnes (Cadres) sur une même formation(INFORMATIQUE), que les dates de formations soient supérieur à la date du jour, que si la formation est annulée elle n'est pas calculer sauf si elle est annulée mais facturée.

Là ce que je souhaite rajouter : si Monsieur BIDULE est planifié en formation INFORMATIQUE sur différente session, donc plusieurs dates, faut que les coûts pour lui se cumulent car les dates sont différentes. Tout en respectant les critères de la formule déjà existants.

Merci :)