compter des surfaces

[pphilippe]

Bonjour
Un problème récurent que j'ai du mal à résoudre avec SOMMEPROD.
J'ai plusieurs listes ( voir fichier )
une liste de date, de lieu ( correspondant à des parcelles .. de vignes ) de taches, et une colonne pour l'age des vignes.
je souhaite établir un tableau de bord permanent pour savoir en temps réel de quelle surface je me suis occupé qui avait 1 an et/ou de 2 ans et/ou de trois ans etc ... pour ce qui est du temps passé, c'est un SOMMEPROD simple, je sais le faire, mais je ne sais pas compter les parcelles concernées et additionner les surfaces.
je vous remercie d'avance pour vos lumières.

 

[Modeste]

Re : compter des surfaces

Bonsoir pphil13,

Sous réserve que j'aie bien compris, peut-être aussi avec sommeprod
En H2:

=SOMMEPROD((ANNEE($A$2:$A$20)=ANNEE(H$1))*($E$2:$E$20=LIGNES($1:1))*($C$2:$C$20))

... à recopier en bas et à droite (et en forçant le calcul, ensuite, puisque tu es en "manuel")

 

[pphilippe]

Re : compter des surfaces

Merci Modeste, ça à l'air de fonctionner, mais, comme j'aime bien comprendre ...
Comment comprendre la deuxième partie de la proposition celle qui fait intervenir :

($E$2:$E$20=LIGNES($1:1))*($C$2:$C$20)
merci beaucoup

 

[Modeste]

Re : compter des surfaces

Re-bonsoir pphil13,

Cette partie de la formule:

($E$2:$E$20=LIGNES($1:1))

permet simplement, lorsqu'on recopie la formule vers le bas, de vérifier si la colonne E contient un 1, puis un 2, puis un 3 et enfin un 4 (ceux-ci correspondant au nombre d'années).

Si ce n'est pas clair, teste ceci dans une cellule vide de ton choix:

=LIGNES($1:1)

et recopie vers le bas, aussi loin que tu veux!

Pour ce qui est de:

($E$2:$E$20=LIGNES($1:1))*($C$2:$C$20)

on additionne les surfaces renseignées en colonne C, si le nombre d'années est 1, 2, 3, etc.

 

[pphilippe]

Re : compter des surfaces

génial ;-) je testerai cela demain au boulot, encore merci pour tout !

 

[pphilippe]

Re : compter des surfaces

Bonjour Modeste
C'est vraiment très fort, je comprends pas tout mais merci !

 

[Modeste]

Re : compter des surfaces

Bonjour pphil13,

Ca vaut ce que ça vaut (mais j'avais 5 minutes!) ... en pièce jointe, une illustration de la formule proposée dans mon 1er message ... tu verras bien si ça t'aide

 

[pphilippe]

Re : compter des surfaces

merci c'est sympa !
mais si je comprends bien ( et d'après ce qui va suivre c'est bien que je ne comprends pas bien )
LIGNES ( $1:1) prend la valeur 1 quand il est sur la ligne 1, la valeur 2 quand il est sur la ligne 2 etc ? en tout cas c'est comme ça qu'il se comporte chez moi, c'est ça que je ne comprends pas bien. Il effectue un test , mais lequel ?

C'est quoi l'astuce ?
Merci

 

[Modeste]

Re : compter des surfaces

Re,

=LIGNES($1:1) donnera le même résultat, quelle que soit la ligne dans laquelle tu inscris la formule. L'aide d'Excel te dira que la fonction LIGNES() renvoie le nombre de lignes d'une référence ou d'une matrice
Dans le cas présent, on demande à la fonction de déterminer combien de lignes sont comptabilisées dans la plage allant de la ligne 1 à ... la ligne 1 (... je te laisse le temps de faire le calcul ). Si tu colles cette fonction en ligne 25, le résultat sera toujours le même: 1

Par contre, si on la recopie une ligne plus bas, avec la poignée de recopie, elle devient =LIGNES($1:2) (grâce au signe $ devant le premier '1' ... voir à ce sujet les "références absolues et relatives", si un doute subsiste). Dans ce cas-ci, le résultat affiché sera le nombre de lignes comprises dans la plage allant de la ligne 1 à la ligne 2, et ainsi de suite ...

... Est-ce plus clair?

 

[pphilippe]

Re : compter des surfaces

Oui c'est plus clair. Je comprends bien que LIGNES($1:1) compte de nombre de ligne d'un point "$1" fixe à un autre mobile "1" ok
quand il rencontre le critère souhaité il renvoie "VRAI" OK. Ce que je comprends moins c'est qu'il soit capable de ne multiplier qu'une seule fois la surface d'une parcelle donnée alors qu'il rencontre plusieurs fois le critère DEMANDÉ ( LA PARCELLE ) .
j'essaie d'être plus clair ; j'effectue plusieurs travaux sur une parcelle (LIEU) chaque année. Cette parcelle , tout comme nous, vieilli d'un an tous les ans. Donc une parcelle donnée en 2010 peut avoir 2 ans et donc en 2011 ... 3 ans.
la formule cherche plusieurs critères ( LIEU; ANNEE; AGE ...) elle rencontre plusieurs fois une réponse VRAI, mais ne multiplie qu'une fois la surface de la parcelle ... C'est surtout ça que j'ai du mal à comprendre.
Au final je cherche à savoir chaque année quelle surface est travaillée qui a 1 an, 2 ans etc ...
je ne sais pas si je suis clair.
Mais je continue d'y réfléchir, merci pour tout.

 

[Modeste]

Re : compter des surfaces

Bonjour pphil13, le forum (et Efgé qui passait par là )

Je crains bien de ne pas trop comprendre à mon tour: jusque là, les formules ne prenaient pas le "lieu" en considération!?
Dans l'exemple donné, donc: en 2010, la parcelle 4 a bénéficié de tes soins en "taille" et "labour". Les vignes d'un an comptabilisent donc 2 fois les 3 ha de cette parcelle, soit 6 ha. Tu voudrais ne comptabiliser qu'une seule fois ces 3 ha en 2010?

Autrement dit, les résultats sont corrects et tu ne comprends pas quelque chose ... ou tu voudrais des résultats différents?

 

[pphilippe]

Re : compter des surfaces

En fait, je viens de me rendre compte que le résultat n'était pas aussi exact que ça. Pour répondre à ta question, oui je voudrais trouver 3 ha en 2010 pour des vignes de 1 an, 2 ha pour des vignes de 2 ans , 0.5 ha pour des vignes de 3 ans etc ... et en 2011 4ha pour des vignes de 1 an ; 2ha pour des vignes de 2 ans etc etc
Tout pour quoi ? Parce que les ratios de temps de travaux étant différents entre 1;2 et 3 ans je souhaite savoir en temps réel si je suis dans les clous des ratios types , mais comme les vignes prennent un an tous les ans, je dois trouver cette solution en dynamique ...
il faudrait peut-être appliquer ta méthode et diviser par le nombre de fois que la même parcelle est rencontrée pour ne la comptabiliser qu'une fois ?
Mais il doit y avoir plus simple

 

[Modeste]

Re : compter des surfaces

Bonjour pphil13,

... On va finir par y arriver!
En annexe, une proposition qui tient compte des dernières précisions (si j'ai bien compris!?). Il est peut-être possible de faire "en une fois", mais il me semble que ça va devenir lourd, comme formule ... j'ai donc ajouté, en colonne F, une formule pour n'indiquer -pour chaque année- que la première fois que la parcelle est mentionnée.

Au cas où le calcul devrait aussi tenir compte du type de tâche (labour, traitement, taille), les mêmes formules (colonne F) n'affichent la combinaison an-lieu que si la tâche en colonne D est la même qu'en H1! Un liste déroulante en H1 permet de sélectionner une des trois tâches.

Les formules en I2:L5 ont été adaptées en conséquence.

... Tu jettes un oeil et tu nous dis ...

 

[pphilippe]

Re : compter des surfaces

Bonsoir
On approche, je valide la méthode, c'est bien, je l'ai adapté à différents cas de figure, rajouté mon grain de sel (moins élégant mais plus à ma portée) merci à Modeste, pour ta gentillesse ;-)
pphilippe13