Fonction complexe dans Excel 2007

=SI(ET(B3>"01/07/2014"*1;OU(D3>30;G3-F3>30));"ENTRETIEN";"")

Daniel

Bonjour,

Qu'il y ait une formule en colonne E ne change rien. Tu peux télécharger mon classeur de test ici :

http://1drv.ms/1pJ6pzp

**>**Je n'avais pas connaissance des Fonctions matricielles, j'ai cru voir que ça

n'était pas courant, effectivement, pour un utilisateur lambda, ça n'est pas

enseigné

Tu peux avoir plus d'informations ici :

http://www.excelabo.net/excel/matricielles

**>**Pourquoi utilise-t-on la fonction MAX

Si on remplace : ($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I") par sa valeur (en appuyant sur F9), on obtient : 

{2;0;0}

Note : le valeurs 0 correspondent aux lignes n'ayant pas "DUPONT Charles" en colonne A et "I" en colonne B.

donc 3 valeurs. On n'a besoin que de la plus grande, c'est à dire la ligne contenant "DUPONT Charles" en colonne A et "I" en colonne B. On utilise MAX pour récupérer "2" qui est le numéro de la ligne cherchée.

**>**à quoi sert la fonction INDIRECT

Le but premier de la fonction est de renvoyer la valeur d'une cellule en spécifiant son adresse; par exemple, si tu as "toto" en A1, INDIRECT(A1) renverra "toto". Dans le cas présent, pour simplifier, on remplacera "MAX(($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I"))" par sa valeur : 2.

INDIRECT("A"&2&":A"&LIGNE())

En F4, LIGNE() a la valeur 4. Donc on a :

INDIRECT("A"&2&":A"&4)

INDIRECT renvoie donc A2:A4 considéré comme une référence à une plage de cellules et 

SOMME.SI(INDIRECT("A"&MAX(($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I"))&":A"&LIGNE());A4;INDIRECT("E"&MAX(($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I"))&":E"&LIGNE()))

est donc équivalent à :

SOMME.SI(A2:A4;A4;E2:E4)

(ouf !)

Note que tu ne pourrais pas te servir d'une concaténation dans une formule. Par exemple :

SOMME("A"&2&"A"&4) ne fonctionne pas alors que SOMME(INDIRECT("A"&2&":A"&4)) est correct.

Cordialement.

Daniel

Pour que l'explication soit plus claire, je décortique la formule de F4. Le but est de trouer la ligne de ela dernière cellule contenant I pou le nom en A4. J'utilise :

MAX(($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I"))

C'est à dire "les noms de la colonne A égaux à A4. SI tu surlignes "($A$2:A4=A4)" et que tu appuies sur F9, tu obtiens :

{VRAI;FAUX;VRAI} (les accolades signifient qu'il s'agit de matrices de valeurs).

En multipliant cette plage par le numéro de ligne (sachant que "FAUX" = 0), on ontient les numéros de ligne pour les cellules de la colonne A contenant "DUPONT Charles". En surlignant "($A$2:A4=A4)*LIGNE($A$2:A4)" et appuyant sur F9, on obtient :

{2;0;4}

on multiplie le tout par "($B$2:B4)="I")" pour ne conserver que les lignes ayant I en colonne B et on obtient avec F9 :

{2;0;0} Il n'y a donc qu'une ligne ayant un I correspondant à ce nom. S'il y en avait plusieurs on détermine le plus bas avec :

MAX(($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I"))

On connaît donc le n° de ligne du dernier I. La formule qu'on cherche à appliquer est :

=SOMME.SI(A2:A4;A4;E2:E4) à ceci prêt que le "2" esst déterminé par la formule précédente. Il va donc falloir l'intégrer à la dernière formule. Pour ce faire, on utilise la fonction INDIRECT.

INDIRECT("A"&MAX(($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I"))&":A"&LIGNE())

équivaut , en remplaçant MAX... par sa valeur (2) à écrire A2:A4 (LIGNE() renvoyant la ligne sur laquelle est la formule, c'est à dire 4). De la même manière, :

INDIRECT("E"&MAX(($A$2:A4=A4)*LIGNE($A$2:A4)*(($B$2:B4)="I"))&":E"&LIGNE()));SI(B4="i";E4;"")

équivaut à E2:E4. On arrive donc à la formule :

=SOMME.SI(A2:A4;A4;E2:E4)

Daniel

Comme la formule de Philippe et de Daniel, ma formule est matricielle.

Apres l'avoir copier/écrite  il ne faut pas valider normalement (enter) mais maintenir les touches CTRL+MAJ et appuyer sur ENTER

Ps : La même sans validation matricielle

=E13+SOMMEPROD(E$2:E12*(B$2:B12="I")*(A$2:A12=A13) * (LIGNE(A$2:A12)=MAX(  (A$2:A12=A13)*LIGNE(A$2:A12) )))*(B13="P")

Bonjour

Moi je propose (en matricielle) :

=E3+SOMME(E$2:E2*(B$2:B2="I")*(A$2:A2=A3) * (LIGNE(A$2:A2)=MAX(  (A$2:A2=A3)*LIGNE(A$2:A2) )))*(B3="P")

Ne fonctionne pas sur la 1ere ligne donc a placer en F3

La valeur en F2 devant être saisie

Quelques explications :

On prend le nombre de jour et on lui ajoute la valeur I si cette valeur était P

=E3+SOMME(E$2:E2*(B$2:B2="I")*(A$2:A2=A3) * (LIGNE(A$2:A2)=MAX(  (A$2:A2=A3)*LIGNE(A$2:A2) )))*(B3="P")

Valeur à ajouter : Dernière valeur I pour cette personne

Liste de valeur / Que les I / Cette personne

E$2:E2*(B$2:B2="I")*(A$2:A2=A3) * (LIGNE(A$2:A2)=MAX(  (A$2:A2=A3)*LIGNE(A$2:A2) ))

Numéros ligne : 0 pour tous sauf pour la dernière valeur :

Liste de N° = au N° ligne Max pour cette personne

(LIGNE(A$2:A2)=MAX(  (A$2:A2=A3)*LIGNE(A$2:A2) ))

Bonjour,

Formule matricielle ( valider avec Ctrl+Maj+Entrée ) en F3 à recopier vers le bas :

=SI(B3="p";SOMME.SI(INDIRECT("A"&MAX(($A$3:A3=A3)*LIGNE($A$3:A3)*(($B$3:B3)="I"))&":A"&LIGNE());A3;INDIRECT("E"&MAX(($A$3:A3=A3)*LIGNE($A$3:A3)*(($B$3:B3)="I"))&":E"&LIGNE()));SI(B3="i";E3;""))

En G3, à recopier vers le bas :

=SI(OU(E3>30;F3>30);"ENTRETIEN";"")

Cordialement.

Daniel

Bonjour

Pouvez-vous insérer une colonne avant la date de début et y entrer le motif de l'arrêt

Si oui vous pourrez différencier les arrêts et en faire la somme avec cette formule

{=SOMME(SI($A$11:$A13=$A13;SI($C$11:$C13=$C13;$F$11:$F13;0);0))}

En suivant vos données

La liste commence en ligne 11

Colonne A les noms

colonne C les motifs

colonne F les Nombres des jours