Re : besoin aide SOMMEPROD (ou autre fonction si besoin)
Bonsoir nightswan,
Je m'interroge surtout sur ces "--" en tête de formule
Une fonction matricielle va appliquer la formule à chacune des valeurs de la plage (indiquée dans la formule) et renvoyer une matrice dont chaque élément est le résultat de la formule pour chacune des valeurs.
exemple: si A1=3, A2=2, A3=1, A4=4 et A5=5, et si la formule matricielle est {=A1:A5>=3}
Alors la formule matricielle renvoit la matrice : {VRAI;FAUX;FAUX;VRAI;VRAI}
Si on veut compter le nombre de VRAI, on pourrait utiliser la formule: {=SOMME(A1:A5>=3)}
Mais VRAI et FAUX sont des valeurs logiques (boolean) et ne sont pas égales à des nombres, la formule SOMME donne 0 (aucun nombre à sommer)
On utilise le fait qu'Excel tente de convertir les valeurs logiques en nombre (FAUX en 0 et VRAI en 1) si ces valeurs sont utilisées dans des calculs. Comme opération on pourrait prendre 1* (multiplication par 1), ou 0+ (addition de zéro) ou -- (deux fois le changement de signe -- , qui revient à multiplier deux fois de suite par -1)
La formule devient donc {=SOMME(1*(A1:A5>=3))} ou =SOMME(0+(A1:A5>=3))} ou =SOMME(--(A1:A5>=3))}
Ces trois formules vont donner =SOMME(1*{VRAI;FAUX;FAUX;VRAI;VRAI}) ou
=SOMME(0+{VRAI;FAUX;FAUX;VRAI;VRAI}) ou =SOMME(--{VRAI;FAUX;FAUX;VRAI;VRAI}).
soit =SOMME({1*VRAI;1*FAUX;1*FAUX;1*VRAI;1*VRAI}) ou =SOMME({0+VRAI;0+FAUX;0+FAUX;0+VRAI;0+VRAI}) ou
=SOMME({--VRAI;--FAUX;--FAUX;--VRAI;--VRAI}).
Ces trois formule vont toutes aboutir à: =SOMME({1;0;0;1;1}) soit la valeur 3.
(je ne connais pas la fonction FREQUENCE)
La fonction fréquence nécessite deux plages de valeurs:=FREQUENCE(PlageADistribuer,PlageBornes)
PlageADistribuer représente une plage de nombres à ventiler dans des intervalles dont les bornes sont indiquées par la plage PlageBornes.
ex: A1=1, A2=2, A3=3, A4=1; A5=10, A6=5; A7=7; A8=3, A9=5 soit {1;2;3;1;10;5;7;3;5}
B1=2, B2=5, B3=8 soit {2;5;8)
=FREQUENCE(A1:A9;B1:B3) soit =FREQUENCE( {1;2;3;1;10;5;7;3;5};{2;5;8)) va renvoyer une matrice de quatre éléments (le nombre d'éléments de B1:B3 +1) égale à
{3;4;1;1}
- 1er élém: 3 (car il y a 3 valeurs dans {1;2;3;1;10;5;7;3;5} <= à 2)
- 2eme élém: 4 (car il y a 4 valeurs dans {1;2;3;1;10;5;7;3;5} > à 2 et <=5)
- 3eme élém: 1 (car il y a 1 valeur dans {1;2;3;1;10;5;7;3;5} > à 5 et <=8)
- 4eme élém: 1 (car il y a 1 valeur dans {1;2;3;1;10;5;7;3;5} > à 8)
Une forme particulière de FREQUENCE: les deux intervalles sont les mêmes.
=FREQUENCE({1;2;3;1;10;5;7;3;5};{1;2;3;1;10;5;7;3;5})
Pour faciliter, on peut lister les bornes de manière unique:{1;2;3;5;7;10; supérieur à 10}, on a donc les intervalles possibles ]-infini,1], ]1,2], ]2,3], ]3,5], ]5,7], ]7,10], ]10,+infini]
.
- 1 er élém: 2 (car il y a 2 valeurs de {1;2;3;1;10;5;7;3;5} dans ]-infini,1] correspondant à l'intervalle du 1er élém de {1;2;3;1;10;5;7;3;5}
- 2 eme élém: 1 (car il y a 1 valeur dans ]1,2] correspondant à l'intervalle du 2eme élém de {1;2;3;1;10;5;7;3;5}
- 3 eme élém: 2 (car il y a 1 valeur dans ]2,3] correspondant à l'intervalle du 3eme élém de {1;2;3;1;10;5;7;3;5}
- 4 eme élém: 0 (il y a bien 2 valeurs dans ]-infini,1] correspondant à l'intervalle du 4eme élém de {1;2;3;1;10;5;7;3;5} mais ces valeurs ont déjà été comptées).
- 5 eme élém: 1 (car il y a 1 valeur dans ]7,10] correspondant à l'intervalle du 5eme élém de {1;2;3;1;10;5;7;3;5}
- 6 eme élém: 2 (car il y a 2 valeurs dans ]3,5] correspondant à l'intervalle du 6eme élém de {1;2;3;1;10;5;7;3;5}
- 7 eme élém: 1 (car il y a 1 valeur dans ]5,7] correspondant à l'intervalle du 7eme élém de {1;2;3;1;10;5;7;3;5}
- 8 eme élém: 0 (il y a bien 2 valeurs dans ]2,3] correspondant à l'intervalle du 8eme élém de {1;2;3;1;10;5;7;3;5} mais ces valeurs ont déjà été comptées).
- 9 eme élém: 0 (il y a bien 2 valeurs dans ]3,5] correspondant à l'intervalle du 9eme élém de {1;2;3;1;10;5;7;3;5} mais ces valeurs ont déjà été comptées).
- 10 eme élém: 0 (car il y a 0 valeur dans ]10,+infini] correspondant au dernier intervalle.
On obtient donc la matrice: {2;1;2;0;1;2;1;0;0;0}. On s'aperçoit que cette forme de fréquence compte le nombre d'occurence de chaque valeur dans l'ordre d'apparition des valeurs dans {1;2;3;1;10;5;7;3;5} avec des zéros intercalés quand ce n'est pas la première apparition de la valeur.
Il suffit de compter le nombre de valeurs non nulles pour avoir le nombre d'éléments sans doublon. Pour cela on applique la formule décrite en haut de ce message: =somme(--(frequence(A1:A9;A1:A9)>0)) soit 6 (6 valeurs différentes dans {1;2;3;1;10;5;7;3;5})
Dans le cas qui nous interesse
Nous devons construire la matrice des valeurs à distribuer (qui sera la même que celles des bornes) afin de compter le nombre de jour ou A a été controlé. On utilise la formule : SI( $D$3:$D$34=J4;$F$3:$F$34)
Cette formule matricielle renvoit le jour de controle si le nom+prenom est égal à J4 et ceci pour chacun des noms de la colonne $D$3:$D$34. Si le nom n'est pas égal à J4, cette formule renvoit FAUX. on obtient une matrice du type {41275;41275;41275;41275;41275;FAUX;FAUX;FAUX;FAUX;FAUX;41306;41306;...}
41275 est le nombre représentant la date 01/01/2013, 41306 est le nombre représentant la date 01/02/2013.
Il suffit de compter les valeurs (sans doublon) pour obtenir le nombre de contrôles globaux pour la valeur J4 (une chance pour nous, la fonction FREQUENCE ne tient pas compte des valeurs non numériques donc la fonction FREQUENCE ignore les valeurs FAUX).
On obtient la formule:
Intervalle à prendre en compte :
SI( $D$3:$D$34=J4;$F$3:$F$34)
Matrice des fréquences:
FREQUENCE(SI( $D$3:$D$34=J4;$F$3:$F$34);SI( $D$3:$D$34=J4;$F$3:$F$34)
Repérer les valeurs >0:
FREQUENCE(SI( $D$3:$D$34=J4;$F$3:$F$34);SI( $D$3:$D$34=J4;$F$3:$F$34)>0
Transformer les valeurs VRAI ou FAUX en 1 ou 0
--(FREQUENCE(SI( $D$3:$D$34=J4;$F$3:$F$34);SI( $D$3:$D$34=J4;$F$3:$F$34)>0)
Compter les valeurs >0
{=SOMME(--(FREQUENCE(SI( $D$3:$D$34=J4;$F$3:$F$34);SI( $D$3:$D$34=J4;$F$3:$F$34)>0)=}
Je m'interroge sur [..] cet espace après le SI( avant la plage D3: D34.
Concernant les espaces dans la formule, ils sont inutiles et peuvent être supprimés.