Comptage d'occurences croisées [Résolu]

[flocon]

Bonjour

Données :
J'ai ébauché un tableau test pour des études sur les temps partiels de personnels.
Colonne D : motif de la demande (de droit, sur autorisation, de droit suivi autorisation sur la période référence)
Colonne E : nombre de jours libérés
Colonne F : quotité de temps partiels.
Je rencontre deux problèmes dans les formules pour calculer les résultats et ai expérimenté plein de formules sans autre résultat que faire pire encore.

Résultat désiré :
Pour chaque quotité de temps partiel, je souhaite avoir le résultat : X demandes dont X de droit, X sur autorisation et X de droit/sur autorisation

Problèmes :

Problème N° 1 - tous les temps partiels à 80% libèrent 1 journée, mais l'inverse n'est pas vrai.
Quand je veux le nombre de 80%, c'est bon
quand je veux le nombre de 1 journée libéré, il inclue les 80%, alors que je souhaite les 1 journées correspondant à toute autre valeur (cela peut faire 76, 78, 81...)
Ma formule actuelle est la suivante : =NB.SI($E$1:$E$23;"1")
Que pourrais-je y ajouter pour soustraire les ($E$1:$E$23;"1") des lignes avec ($F$1:$F$23;"80")

Problème N° 2 -
Si je n'avais que les demandes de droit (MF, MS, TE, TS) et les demandes sur autorisation (TP), cela irait. Mais j'ai aussi ceux qui changent en cours d'année (MF-TP, MS-TP, TE-TP, TS-TP). Je ne peux pas changer ces codes.
Or dans mes formules, une demande est comptée dans la catégorie dont elle ne relève pas.
Ma formule actuelle est la suivante : =NB.SI($F$1:$F$23;"50")*(NB.SI($D$1:$D$23;"MF")+NB.SI($D$1:$D$23;"MS")+NB.SI($D$1:$D$23;"TE")+NB.SI($D$1:$D$23;"TS"))

Des 50%, en tout je n'en ai que trois sur mes 21 lignes de test, et la formule aboutit à 21.
Ce qui signifie que non seulement il me compte les MF mais aussi les MF-TP, mais qu'en plus, il me compte ceux qui ne sont pas combinés avec un 50... Donc :
- Ce n'est pas le signe * qu'il faut utiliser pour combiner deux valeurs ? (c'est pourtant ce qu'il me semblait)
- Comment faire que la formule se réfère à une chaîne de caractères et pas la chaîne tronquée ou la chaîne augmentée (quand je veux MF, qu'il ne prenne pas MF-TP et vice versa) ?
J'ai essayé en mettant la première condition à la fin, en utilisant les {}... sans meilleurs résultats

J'espère mon exposé clair sinon je répondrais à toute question.
J'ai passé ma journée sur cette genèse de tableau et à ce stade je désespère....

Merci de votre attention

 

[renyeu]

Re : Comptage d'occurences croisées

Bonsoir,
Une pièce jointe dépersonnalisée serait très utile...
Cdlt
Renyeu

 

[flocon]

Re : Comptage d'occurences croisées

J'ajoute le fichier joint. Aucune donnée réelle n'y était encore enregistrée.
Je travaille sur l'onglet 2

Lignes 24 à 31 mes formules

lignes 33 à 40 les résultats attendus (avec un jeu de couleurs pour les lignes du tableau renseigné qui devraient être prises en considération à chaque fois.

 

[Modeste]

Re : Comptage d'occurences croisées

Bonsoir flocon,

Ce n'est pas le signe * qu'il faut utiliser pour combiner deux valeurs ? (c'est pourtant ce qu'il me semblait)

Si, si ... mais c'est plutôt utilisé pour cumuler deux conditions comme avec un ET (et dans ce cas-là ce sont des VRAI/FAUX que tu multiplies, pas des "quantités")

Dans ton exemple, si on prend la formule la plus simple:

=NB.SI($F$1:$F$23;"50")*NB.SI($D$1:$D$23;"TP")

Excel cherche combien d'occurrences de 50 se trouvent en colonne F (comme moi, il en trouve trois). Tu lui demandes par ailleurs de compter le nombre de fois où TP figure en colonne D (et là, une fois encore, lui et moi sommes d'accord: nous en comptons sept! on ne comptabilise ici que les cellules contenant précisément TP, sans rien devant, ni derrière, contrairement à l'hypothèse que tu formulais).
Ici, tu seras du même avis que nous: trois multiplié par sept donne 21

Si, par contre, tu utilises

=SOMMEPROD(($D$2:$D$22="TP")*($F$2:$F$22=G24*100))

ça devrait commencer à ressembler à ce que tu souhaites (je fais référence à G24, pour pouvoir recopier la formule vers le bas).

Cette autre

=SOMMEPROD(($F$1:$F$23=G24*100)*(DROITE($D$1:$D$23;3)="-TP"))

permet de trouver les 50 en colonne F avec les "TE-TP", "MF-TP", "TS-TP" et "ME-TP" de la colonne D.

---

Il reste deux problèmes:
- le premier est le contenu des cellules G28 à G30 qui empêchera de recopier la formule à cette hauteur (mais un format personnalisé devrait régler le souci)
- le second est: comment savoir s'il faut prendre en compte la colonne E ou la colonne F? Ainsi, à la ligne 12, tu as un TP avec 1 jour en colonne E et 80% en colonne F. Il sera donc comptabilisé 2 fois!?

Voilà quelques pistes et réflexions pour occuper le reste de ton week-end

 

[flocon]

Re : Comptage d'occurences croisées

Merci beaucoup.

Je crains d'avoir mal digéré la fonction NB.SI que je vais potasser (cela ne peut pas faire de mal)

Grâce à toi, j'ai toutes mes formules impec. Ce qui va m'économiser un temps fou quand plusieurs centaines de lignes sont renseignées Que du bonheur !
Pour mes doublons (par exemple MS et MS-TP) j'ai simplement fait une soustraction toute bête. Dire que je partais dans des délires complexes !!!

Merci tout plein

 

[flocon]

Re : Comptage d'occurences croisées [Résolu]

Correctif au cas où cela intéresse quelqu'un pour le deuxième problème (afin que les colonnes E et F ne comptent pas de cas en double)
Avec des soustractions, sur certains tests, mes nombres de demandes étaient fausses, notamment des négatives).
J'ai finalement opté pour une autre formule : pour le nombre de demandes 1 journée libérée différente de 80% par exemple :
=SOMMEPROD((($D$1:$D$23="MF")+($D$1:$D$23="MS")+($D$1:$D$23="TE")+($D$1:$D$23="TS"))*($E$1:$E$23=1)*($F$1:$F$23<>80))

Et cette fois-ci, toutes les simulations me donnent des résultats justes (à moins qu'il n'y ait une simulation piège à laquelle je n'ai pas pensé)