petite valeur avec conditions

[mikeo]

Bonjour the Forum,

J'essaie d'extraire, sur une liste de factures, les factures non payées par un client . Il s'agit de regrouper les factures non payées par un client pour lui envoyer une relance.

Je sais pour cela qu'il y a une combinaison magique de petite.valeur avec lignes() et éventuellement sommeprod(). mais je n'arrive pas à la mettre en place.

Pouvez-vous m'aider à le faire svp ? Merci.

Mikeo

 

[exene]

Re : petite valeur avec conditions

Bonjour,

Pour le sommeprod , essaye

=SOMMEPROD((clients!$A$2:$A$8=numero)*(clients!$C$2:$C$8=$A22)*(clients!$H$2:$H$8))

@+

 

[Misange]

Re : petite valeur avec conditions

Bonjour

J'ai renommé tes plages pour qu'elles soient dynamique.( Encore plus simple, tu les transformes en listes puisque tu as excel 2003 et elles le deviennent automatiquement). Autrement dit quand tu ajouteras une facture dans la première page elle sera prise en compte automatiquement.

En A21 sur la page relance la formule est à valider en matricielle

=SI(ESTERREUR(INDEX(numeropiec;PETITE.VALEUR(SI(CPTECOMPTA=numero;LIGNE(CPTECOMPTA)-LIGNE(clients!$A$1));LIGNE(1:1))));"plus de facture pour ce client";INDEX(numeropiec;PETITE.VALEUR(SI(CPTECOMPTA=numero;LIGNE(CPTECOMPTA)-LIGNE(clients!$A$1));LIGNE(1:1))))

les autres formules de la ligne sont normales et vont juste récupérer les valeurs correspondantes au N° de pièce dans le tableau de départ.

 

[R@chid]

Re : petite valeur avec conditions

Bonjour @ tous,
une autre interprétation,
@ + +

 

[mikeo]

Re : petite valeur avec conditions

Bonjour exene, Misange, Rachid,

Merci beaucoup Misange et Rachid, les deux solutions marchent bien. C'est cette formule magique que je ne suis pas encore arrivé à comprendre, et encore moins à maîtriser.

C'est tellement rare de trouver, sur cette petite Terre, un espace d'entraide où il n'y a pas de dimanche, pas de 35 heures, pas de rémunération, et avec autant de gentillesse, de patience, d'enthousiasme et de dévouement, que ce Forum est vraiment précieux. Cela vaut pour tous les intervenants de ce Forum.

Bon dimanche

Mikeo

 

[Misange]

Re : petite valeur avec conditions

Salut @tous

Pour décortiquer ce genre de formule on part de l'intérieur vers l'extérieur.
Et surtout use et abuse de l'outil d'évaluation de formule dans excel qui te permet de suivre pas à pas comment excel traite cette formule.
Déjà tu enlèves les si(esterreur(machin);"valeur si erreur";machin)
machin ici c'est ce bout de formule :

INDEX(numeropiec;PETITE.VALEUR(SI(CPTECOMPTA=numero;LIGNE(CPTECOMPTA)-LIGNE(clients!$A$1));LIGNE(1:1))

ligne(1:1) : vaut ... 1 !
quand tu tires vers le bas ça devient
ligne(2:2)
ligne(3:3)
...

qui est une façon de dire à excel d'incrémenter une série de nombres 1, 2, 3....
cette valeur est le dernier argument de petite.valeur

Petite valeur regarde le classement du plus petit au plus grand d'une série de valeurs
petite.valeur (truc;1) renvoie la première valeur de ce classement (donc la plus petite valeur)
petite.valeur(truc;2) renvoie la seconde plus petite valeur
...
maintenant comment définir la série truc ? Cette série contient tous les numéros de ligne pour lesquels cptecompta est identique au numéro que tu as choisi.( Tu as nommé cette cellule "numero".)

c'est une formule matricielle donc excel regarde tous les numéros de ligne, et pour chacun renvoie vrai ou faux suivant que la condition est remplie.

ça donne un truc du genre (je n'ai plus tes données sous les yeux)
...si({v;v;f;f;f;v};...

Ligne (cptecompta) renvoie
{2;3;4;5;6;7}
puisque ta plage commence en C2

on va utiliser ensuite cette portion de la formule comme argument de la fonction index.
Index renvoie la POSITION RELATIVE d'un élément dans une série. Pas le N° de ligne.
donc il faut corriger, en enlevant à toutes ces valeurs le N° de la ligne d'entête de ton tableau
...si({v;v;f;f;f;v};{2;3;4;5;6;7};-{1}

devient donc
...si({v;v;f;f;f;v};{1;2;3;4;5;6}

...petite.valeur(si({v;v;f;f;f;v};{1;2;3;4;5;6}...
c'est à dire
petite.valeur({1;2;f;f;f;6}

ajoute maintenant l'argument ligne(1:1) (ou 2:2)... quant tu tires la formule vers le bas

petite.valeur({1;2;f;f;f;6};ligne(1:1)
renvoie 1
NB : La 3° VALEUR de cette série est 6 car les Faux ne sont pas des valeurs et sont ignorés

Donc enfin,
index(cptecompta;1)
renvoie la valeur de la première cellule de la plage ctecompte c'est à dire la valeur de C2
OUF !!

capito ?

Allez c'est l'heure de l'apéro

 

[R@chid]

Re : petite valeur avec conditions

Bonjour @ tous,
Salut Misange,
Pour rappelle, il y une différence entre mon fichier et celui de Misange...
Sur le mien on ne récupère que les factures qui n'ont pas de dates de règlements par-contre celui de Misange on récupère toutes les factures du client, tu peux faire une comparaison en ajoutant une date en I2 (Onglet : Clients)...
@ + +

 

[mikeo]

Re : petite valeur avec conditions

Bonjour Rachid, Misange, et le forum,

J'ai mieux compris avec l'explication de Misange mais je n'ai pas évité de prendre une aspirine. C'est vrai que la réponse de Rachid tient compte d'une condition supplémentaire (pas de règlement). J'en ai tenu compte. Et puis chacun de vous a utilisé ses astuces préférés, l'un avec colonne() et l'autre avec décaler qui rendent les formules vraiment "goûteuses". Ce sont des exemples d'école qui peuvent servir à d'autres.

Merci à vous deux.

Mikeo