Sujet Précédent Sujet Suivant

Formule complexe excel !

Résolu/Fermé
Antoine - 28 janv. 2010 à 11:31
tompols Messages postés 1273 Date d'inscription jeudi 29 juillet 2004 Statut Contributeur Dernière intervention 25 novembre 2013 - 28 janv. 2010 à 15:21
Bonjour,

Attention voilà une formule qui me casse la tête bien comme il le faut.

Voilà ce que je dois faire.

J'ai deux statuts celui de départ 1100 et celui d'arrivée 1650.
J'ai une date pour le statut 1100 et une pour le statut 1650.
J'ai un nombre de données qui varie selon les mois (en janvier je peux avoir 20 dates puis février 100 je dois donc prendre en compte toute la feuille excel pour permettre l'automatisme)
Je dois calculer le délai moyen pour toute ma feuille de passage entre le statut 1100 et le statut 1650.
Ici vous me direz limite c'est simple.... mais il s'avère que dans ma colonne 1650 j'ai des trous étant donnée que mon ordre n'est pas arrivé encore à 1650... Je peux donc avoir des lignes avec une date en 1100 et pas en 1650 il faut donc exclure ces lignes.
En plus de cela j'ai une colonne statut actuel dans lequel on me dit l'ordre est actuellement en 1100 ou entre 1100 et 1650 (1250, 1300 , 1400...) mais aussi j'ai des statuts 1990 qui eux sont particuliers et ne doivent pas être pris en compte à savoir que si j'ai un statuts 1990 j'ai une date de 1100 et une de 1650 il faut donc aussi exclure cela du calcul final.

En simple la formule doit être comme ceci : je calcule le délai moyen de passage entre tous mes 1100 et mes 1650 en excluant les ordres qui n'ont pas de dates en 1650 et ceux qui sont en 1990...

Que du bonheur non ?
A voir également:

12 réponses

Réponse 1 / 12
michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
28 janv. 2010 à 11:47
Bonjour,

Montre un bout de ton classeur, car tes explications sont pour le moins "brouillon"
pour mettre un classeur format XL97-2003
https://www.cjoint.com/
et tu colles le lien proposé dans ton message
0
Réponse 2 / 12
Antoine
28 janv. 2010 à 11:56
Voilà le fameux fichier...
Ici c'est une simulation.
A terme il devrait y avoir au minimum 500 lignes il faut donc prendre en compte le document en entier.


https://www.cjoint.com/?bCl2wSTemz
0
Réponse 3 / 12
tompols Messages postés 1273 Date d'inscription jeudi 29 juillet 2004 Statut Contributeur Dernière intervention 25 novembre 2013 435
28 janv. 2010 à 12:13
Salut,
essaie avec cette formule (plage à adapter) : 
=SOMMEPROD((J4:J20<>1990)*(M4:M20<>"")*(M4:M20-L4:L20))/SOMMEPROD((J4:J20<>1990)*(M4:M20<>""))
0
Réponse 4 / 12
michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
28 janv. 2010 à 12:14
OK merci,

Donc tu voudrais avoir le temps moyen de passage 1100 à 1650 colonnes L & M ?
c'est bien çà ?
SI c'est OK essaies en test sur ligne 4 à 9 (délai moyen 7) 
=MOYENNE(SI((L4:L9<>"")*(M4:M9<>"");(M4:M9)-(L4:L9)))

formule matricielle à valider par Ctrl+maj+entree, le curseur étant dans la barre de formules et non par entree comme d'hab XL encadre alors automatiquzment la formule par des accolades
si ca te convient remplace ensuite les 9 par es 500 lignes

Edit: en lisant la réponse de Tompols (bonjour),je vois que j'avais pas vu la colonne J 
=MOYENNE(SI((J4:J500=1650)*(L4:L500<>"")*(M4:M500<>"");(M4:M500)-(L4:L500)))
0
Antoine
28 janv. 2010 à 12:24
La même michel dès que je dépasse les lignes écrites il me met 0.
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Réponse 5 / 12
Antoine
28 janv. 2010 à 12:19
ça marche parfaitement lorsque l'on dépasse pas 20 par contre vu que je vais inclure ça dans une macro, il faut que dès le départ je fasse ma formule pour qu'elle prenne en compte toute la feuille même si il y a des lignes totalement vides. Dans le cas de ta formule si je garde mon fichier comme cela et que je remplace 20 par l'infini j'ai un résultat de 0
0
Réponse 6 / 12
michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
28 janv. 2010 à 12:27
Pourquoi ne pas avoir précisé que tu voulais faire ca par macro au départ? la résolution est complètement différente !!! d'autre part, que cela soit par sommeprod ou par matricielle, le calcul est refusé si on prend des colonnes entières...

on a que ce que tu as écrit pour t'aider, donc...
0
Réponse 7 / 12
tompols Messages postés 1273 Date d'inscription jeudi 29 juillet 2004 Statut Contributeur Dernière intervention 25 novembre 2013 435
28 janv. 2010 à 12:40
Re,
effctivement c'est différent...que veux-tu exactement :
- on peut faire le calcul en vba et n'afficher que le résultat
- on peut créer dynamiquement la formule en vba en determinant la derniere ligne de ta liste
- on peut aussi imaginer juste une formule(sans vba), soit on fixe la plage jusqu'à la ligne 65536, soit on determine la derniere ligne ds la formule....
- autres....
0
Antoine
28 janv. 2010 à 13:41
Voilà ce qu'il va se passer.

Je vais avoir des données comme celle du fichier que j'ai envoyé, en format TXT.
Tous les mois elles arrivent sous un nom précis et toujours dans le même répertoire.
J'ai un classeur excel ou j'ai ma macro finale, avec juste un bouton, dans laquelle il y a plusieurs actions.
La 1ere va chercher le fichier TXT par rapport à sa date et classe les informations dans le fichier excel que j'ai envoyé (c'est préconfiguré dans mon TXT donc c'est simple).
Ensuite la macro doit lancer automatiquement mon calcul quel que soit le nombre de lignes présentes.
Il faut donc que mon calcul se fasse qu'il y ait 20 lignes ou 500 lignes sans devoir intervenir.
0
Antoine
28 janv. 2010 à 14:08
Au passage j'ai une deuxième formule à intégrer dans ma macro qui est le nombre de retards : ici toujours avec la même contrainte qui est que je ne sais pas à l'avance combien de lignes je vais avoir, je dois dire le nombre de retards qu'il y a eu sachant qu'on calcule un retard par le rapport entre la date de création soit le statut 1100 et la date de réception de l'instruction, si le délais excède 2 jours.

J'avoue que j'en demande beaucoup mais je suis totalement bloqué...
0
Réponse 8 / 12
Antoine
28 janv. 2010 à 13:21
Désolé d'avoir oublié ce gros détail...

Oui il faut que je mette tout ça dans une macro avec vous une solution ?

Merci pour votre aide à tous !
0
Réponse 9 / 12
tompols Messages postés 1273 Date d'inscription jeudi 29 juillet 2004 Statut Contributeur Dernière intervention 25 novembre 2013 435
28 janv. 2010 à 14:15
Re
Bon tu nous dit toujours pas si le calcul doit s'effectuer ds le code et renvoyer la valeur ou si tu veux une formule...
J'ai pris l'option 2, pour mettre la formule en X1 : 
derlig = Sheets(1).Range("A65536").End(xlUp).Row

Range("X1").Formula = "=Sumproduct((J4:J" & derlig & "<>1990)*(M4:M" & derlig & "<>"""")*(M4:M" & derlig & "-L4:L" & derlig & "))/Sumproduct((J4:J" & derlig & "<>1990)*(M4:M" & derlig & "<>""""))"
0
Réponse 10 / 12
tompols Messages postés 1273 Date d'inscription jeudi 29 juillet 2004 Statut Contributeur Dernière intervention 25 novembre 2013 435
28 janv. 2010 à 14:29
re,
Pour la 2eme formule : 
"=SUMPRODUCT((O4:O" & derlig & "-L4:L" & derlig & ">2)*1)"
0
Réponse 11 / 12
Antoine
28 janv. 2010 à 14:37
J'ai tout testé c'est génial pour les deux !!!! Merci beaucoup pour ces réponses et surtout la rapidité !!!!
0
Réponse 12 / 12
tompols Messages postés 1273 Date d'inscription jeudi 29 juillet 2004 Statut Contributeur Dernière intervention 25 novembre 2013 435
28 janv. 2010 à 15:21
de rien :)
0

Discussions similaires

Formule Excel : "SI" une cellule contient un mot, alors ?
Tonio5933 -
Mike-31 -

9 réponses
Excel si cellule contient texte alors...
caba_44 -
RENOU -

41 réponses
recopier une formule sans décalage
aziz_alg80 -
aziz_alg80 -

2 réponses