Formule magique ? Recherche de dates Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
Joseph67
Membre fidèle
Membre fidèle
Messages : 284
Inscrit le : 2 décembre 2007
Version d'Excel : 2007 FR, 2013FR

Message par Joseph67 » 30 novembre 2014, 14:26

Bonjour le forum,

Je suis à la recherche d'une formule qui de mon point de vue doit carrément être magique, si elle existe, car je souhaite obtenir d'après la saisie d'extraits bancaires, le solde de départ et le solde de fin d'après deux dates données (en général du 01/01/20XX au 31/12/20XX, mais pas toujours !).
Je travaille sur un fichier nommé Directeur avec un onglet Décompte et 30 onglets de C1 à C30 (limité à C5 dans l'exemple) qui représentent les comptes bancaires.
La date de début se situe sur l'onglet Décompte en D19
La date de fin se situe sur l'onglet Décompte en H19
Si je saisie 01/01/2014 en D19 et 01/01/2015 en H19 sur la feuille Décompte, je devrais obtenir pour le compte C1 (onglet C1) les valeurs de 2275,82 en C49 de la feuille Décompte et 181,79 en H49 de la feuille Décompte.
Si je saisie 01/01/2013 en D19 et 31/12/2013 en H19 sur la feuille Décompte, je devrais obtenir toujours pour le compte C1 (onglet C1) les valeurs de 1415,94 en C49 de la feuille Décompte et 2275,82 en H49 de la feuille Décompte.
Cela fait maintenant plusieurs jours que j'essaye de trouver mon bonheur, mais pour l'instant rien qui puisse convenir.
RECHERCHEV ne convient pas puisque les dates ne sont pas forcément dans l'ordre chronologique, je n'arrive à rien avec FIN.MOIS, sans doute INDEX, EQUIV mais il y a tellement de possibilités (par ex. plusieurs lignes avec la date du 31/12) que je suis totalement dépassé.

Dans un deuxième temps, seulement si une solution était possible quant à mon problème de solde évoqué plus haut, il faudrait modifier la formule en H26 de la feuille Décompte en y incorporant la condition de la date de début et de fin.
Pour l'instant c'est une suite de SOMME.SI(C1...)+SOMME.SI(C2...)+ jusqu'à SOMME.SI(C30...) ce qui me fait une formule extrêmement longue qui peut être améliorée ?

Bien conscient que mes explications peuvent être confuses ou obscures (voire les deux) et ce malgré de gros efforts de ma part, vous trouverez joint le fichier DIRECTEUR.

Merci à vous (déjà rien que pour être arrivé à cette ligne), d'avoir pris le temps de me lire, et de votre aide.

Cordialement
Joseph
DIRECTEUR.xlsm
(140.2 Kio) Téléchargé 22 fois
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'428
Appréciations reçues : 405
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 30 novembre 2014, 20:11

Bonjour,

Pour la 1ère partie :
en C49:
=INDEX('C1'!$F:$F;SI(ESTERREUR(EQUIV($D$19;'C1'!$B:$B;0));EQUIV($D$19;'C1'!$B:$B;1);EQUIV($D$19;'C1'!$B:$B;0)-1))
en I49:
=INDEX('C1'!$F:$F;SI(ESTERREUR(EQUIV($D$19;'C1'!$B:$B;0));EQUIV($D$19;'C1'!$B:$B;1);EQUIV($D$19;'C1'!$B:$B;0)-1))
Je te laisse ajouter les indirect()

Pour le reste peut-être après manger si je trouve qq chose.

eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
j
jmd
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'603
Appréciations reçues : 251
Inscrit le : 8 décembre 2007
Version d'Excel : 365 + PowerBI

Message par jmd » 30 novembre 2014, 20:22

bonjour à vous

mon avis, perso qui n'engage que moi : tu fais trop compliqué. Il te sera de plus en plus difficile de faire évoluer ton programme (et tu sis déjà que tu auras des évolutions dans l'avenir)

fais une feuille simple (et une seule) pour la saisie des mouvements
http://optim18.rcac.fr/TablesExcel_13ReglesOr.pdf
et puis des tris, filtres (par macros si tu veux), et des tableaux croisés

simple, sans fioritures, efficace
dans 15 ou 20 ans ça fonctionnera encore
Apprenez les fonctions d'Excel.
Exemple "Mettre sous forme de tableau", TCD, "Récupérer des données".
Apprendre les fonctionnalités "récentes".
A
Amadéus
Modérateur
Modérateur
Messages : 17'693
Appréciations reçues : 78
Inscrit le : 7 mai 2006
Version d'Excel : Office Excel 2003 FR et 2013FR

Message par Amadéus » 30 novembre 2014, 22:29

Bonjour
Pour la formule "magique" en H26
=SOMMEPROD((NB.SI(DECALER(INDIRECT("C"&COLONNE(A:E)&"!A3");LIGNE($1:$509)-1;0);$A26)*(N(DECALER(INDIRECT("C"&COLONNE(A:E)&"!B3");LIGNE($1:$509)-1;0))<=H$19)*(N(DECALER(INDIRECT("C"&COLONNE(A:E)&"!B3");LIGNE($1:$509)-1;0))>=D$19)*(N(DECALER(INDIRECT("C"&COLONNE(A:E)&"!D3");LIGNE($1:$509)-1;0)))))
Cordialement
DIRECTEUR.xls
(197.5 Kio) Téléchargé 11 fois
Amadéus vous informe que, pour cause de saturation, il ne peut plus répondre aux messages privés non sollicités.
Avatar du membre
Joseph67
Membre fidèle
Membre fidèle
Messages : 284
Inscrit le : 2 décembre 2007
Version d'Excel : 2007 FR, 2013FR

Message par Joseph67 » 1 décembre 2014, 11:15

Bonjour eric, jmd, Amadeus
Bonjour le forum,

Tout d'abord, merci à vous trois de vous être intéressés à mon post et merci pour vos réponses. (rapides les réponses ! vu la complexité, je ne m'attendais pas à des réponses si vite)
Ce que j'essaye de faire, c'est de modifier la saisie des mouvements bancaires (pour chaque compte un onglet) sans limite de date.
Pour l'instant, à chaque 31/12, j'archive le décompte puis en recréé un nouveau en reprenant les soldes de fin au 31/12 de l'ancien décompte pour en faire les soldes de début dans le nouveau décompte, puis je recommence la saisie de l'année.
S'il m'est possible de le faire, je cherche à garder toujours le même décompte mais en saisissant tous les mouvements bancaires de plusieurs années. Puis-je prévoir disons 50.000 lignes par onglet C1 à C30 au lieu des 512 actuelles ?
L'onglet Décompte servant à présenter les données d'après les dates de début et de fin.

jmd, faire trop compliqué c'est un peu l'histoire de ma vie !
Mais je pense qu'il est maintenant trop tard pour tout chambouler. Ca fait maintenant des années que ce programme tourne (depuis 2006), et les tableaux croisés, je n'y connait rien. Je me retrouve un peu coincé, et maintenant je fais avec. (Merci pour le lien)

eric et Amadeus, : mais comment on peut faire pour trouver des formules pareilles !!!
Il y a des cours dans d'autres galaxies, ou c'est moi qui ai manqué une étape de l'évolution de l'espèce!
eric, "je te laisse ajouter les indirect". Si tu pouvais me voir, tu verrai le grand point d'interrogation qui vient de se former au-dessus de ma tête !

PS : ne le répétez à personne, mais vos formules, je n'arrive même pas à les déchiffrer !

Mille (au moins) mercis à vous.
Je teste dans la journée et vous tiens au courant

Amicalement
Joseph
Avatar du membre
Joseph67
Membre fidèle
Membre fidèle
Messages : 284
Inscrit le : 2 décembre 2007
Version d'Excel : 2007 FR, 2013FR

Message par Joseph67 » 1 décembre 2014, 17:30

Bonsoir eric, jmd, Amadeus
Bonsoir le forum,

Bon, j'ai testé.

jmd, c'est très séduisant et sans nul doute très juste ce que tu dis, mais il faudrait que je reprenne tout du début. Cela me fait sentir au bord d'un précipice...et j'avoue ne pas en avoir le courage.
Amadeus, ta formule miracle fait des .... miracles. Rien à dire (mais tout à comprendre)
=SOMMEPROD((NB.SI(DECALER(INDIRECT("C"&COLONNE(A:E)&"!A3");LIGNE($1:$509)-1;0);$A26)*(N(DECALER(INDIRECT("C"&COLONNE(A:E)&"!B3");LIGNE($1:$509)-1;0))<=H$19)*(N(DECALER(INDIRECT("C"&COLONNE(A:E)&"!B3");LIGNE($1:$509)-1;0))>=D$19)*(N(DECALER(INDIRECT("C"&COLONNE(A:E)&"!D3");LIGNE($1:$509)-1;0)))))
!A3,!B3,!D3, et le N, ils viennent d'où ? LIGNE($1:$509) ce sont bien les lignes des onglets C, C2, etc. Je peux aller à combien de lignes ?
Est ce que je pourrai comprendre un jour ???!!!
eric, j'ai rectifié la formule en I49 en remplaçant le D19 par H19. Toutefois la résultat n'est pas celui escompté.
En tout premier parce que je n'ai pas encore compris quoi faire avec les INDIRECT, ni où les mettre, ni pourquoi.
Deuxièmement, visiblement le positionnement dans l'onglet C1 n'est pas bon. Exemple : je tape 01/01/2014 et 31/12/2014, le positionnement en C1 est respectivement la ligne L195 et L503.
De même si j'entre dans Décompte B19 une date inférieure à la première date de C1, la formule me renvoie #N/A

Enfin, soyez sympas, comment on apprend à faire de telles formules ! Moi je n'arrive même pas à les imaginer...
Quel est le secret ?

Et en fin de fin, si vous pouviez encore m'aider pour les formules en C49 et I49....

Et de toute façon : MERCI

Cordialement
Joseph
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'428
Appréciations reçues : 405
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 1 décembre 2014, 19:31

Un raté de collé de la 2nde formule, mis 2 fois la même...

Je n'avais pas fait attention que tes dates n'étaient pas triées. Du coup c'est, je pense, infaisable par formule.
Proposition de 2 fonctions personnalisées à mettre dans un module standard.

Principe de ancienSolde(Feuille, date)
- balayage des dates de haut en bas
- arrêt sur date >= date1
- retourne solde ligne précédente, ou "???" si pas de date >= date1

Principe de nouveauSolde(Feuille, date)
- balayage des dates de bas en haut
- arrêt sur date <= date1
- retourne solde ligne, ou "???" si pas de date <= date1

Les formules peuvent être copiées-collées
Si ça ne fonctionne pas dans certains cas exprime ton besoin de la même façon que j'ai utilisé. Précise pour le cas la ligne de date à prendre en compte et la ligne de solde à retourner.

eric
DIRECTEUR.xlsm
(147.81 Kio) Téléchargé 8 fois
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
A
Amadéus
Modérateur
Modérateur
Messages : 17'693
Appréciations reçues : 78
Inscrit le : 7 mai 2006
Version d'Excel : Office Excel 2003 FR et 2013FR

Message par Amadéus » 1 décembre 2014, 19:52

Bonjour
En C49, formule matricielle à valider avec les 3 touches Ctrl+Maj+Entrée
=SI(D$19<MIN(INDIRECT("'"&$A48&"'!$B$3:$B$10000"));"";INDEX(INDIRECT("'"&$A48&"'!$F:$F");MIN(SI(INDIRECT("'"&$A48&"'!$B$3:$B$10000")>=$D$19;LIGNE(INDIRECT("'"&$A48&"'!$B$3:$B$10000"))))))
En I49, toujours matricielle
=INDEX(INDIRECT("'"&$A48&"'!$F:$F");MIN(SI((INDIRECT("'"&A48&"'!$B$3:$B$10000")>$H$19)*(INDIRECT("'"&A48&"'!$B$3:$B$10000"));LIGNE(INDIRECT("'"&A48&"'!$B$3:$B$10000"))))-1)
Cordialement
DIRECTEUR.xls
(199 Kio) Téléchargé 6 fois
Amadéus vous informe que, pour cause de saturation, il ne peut plus répondre aux messages privés non sollicités.
Avatar du membre
Joseph67
Membre fidèle
Membre fidèle
Messages : 284
Inscrit le : 2 décembre 2007
Version d'Excel : 2007 FR, 2013FR

Message par Joseph67 » 1 décembre 2014, 22:14

Bonsoir Éric, Amadeus
Bonsoir le forum,

Éric, j'essaye effectivement d'écrire d'abord ce que je veux pour tenter de le transformer en formule.
J'avais vu lors de mes recherches sur internet qu'il y a moyen de faire sur une liste non triée et en utilisant MIN
Inutile je crois de préciser que c'est au delà de mon niveau...

Amadeus, je pense que la formule pour C49 n'est pas entièrement écrite, elle s'arrête par =$.
Enfin c'est peut être parce que je suis sur ma tablette.
Je ne saurai pas la compléter seul...mais elle est sans doute entière dans le fichier.

Le PC est éteint, alors je testerai demain.

Un énorme merci à vous deux
Et bravo pour vos connaissances.

Cordialement
Joseph
Avatar du membre
Joseph67
Membre fidèle
Membre fidèle
Messages : 284
Inscrit le : 2 décembre 2007
Version d'Excel : 2007 FR, 2013FR

Message par Joseph67 » 2 décembre 2014, 18:00

Bonsoir Eric, Amadeus,
Bonsoir le forum,

Dans le nanar Grippe II : le retour, je tiens le rôle principal depuis ce matin.
Autant dire que j'ai les idées moins claires que d'habitude (ou plus embrumées, c'est selon)

Bon, j'ai un petit peu modifié le C1, en mettant en 3e ligne le solde de départ (et non plus en F2).
C'est ainsi que j'ai pu le voir lors de mes recherches.
Je joins à cet effet Directeur-2 en fin de sujet.
Les formules fonctionnent bien, mais j'ai remarqué 2 choses :
La première, c'est quand par exemple je mets en date de début 01/01/2014 en D19 de Décompte, le solde en C49 de Décompte affiche 2252,82 ce qui est bien sûr correct. Mais le solde recherché et au 01/01/2014 MAIS avant toute opération, soit 2275,92 ligne 185 de C1 qui est la dernière valeur saisie avant le 01/01/2014.
J'ai bien essayé de faire comme les grands en rajoutant -1 à la formule (avec les accolades), ce qui donne ceci :
=SI(D$19-1<MIN(INDIRECT("'"&$A48&"'!$B$3:$B$10000"));"";INDEX(INDIRECT("'"&$A48&"'!$F:$F");MIN(SI(INDIRECT("'"&$A48&"'!$B$3:$B$10000")>=$D$19-1;LIGNE(INDIRECT("'"&$A48&"'!$B$3:$B$10000"))))))
mais cela n'a rien donné.
La deuxième, si je rentre 31/12/2015 en H19 de Décompte, je retrouve en solde de fin (cellule I49 de Décompte) #VALEUR!
Sans doute parce que la valeur de 31/12/2015 n'existe pas en C1

Voilà.
Maintenant, je me rends bien compte que c'est très difficile, bien plus que je ne l'imaginai et je ne me permettrai pas de vous demander de continuer, sauf bien sûr si cela vous intéresse.
Il y a une limite à la gentillesse que je ne voudrai pas dépasser.

De toute façon, je vous remercie pour tout ce que vous avez déjà fait.

Amicalement
Joseph
DIRECTEUR-2.xls
(271 Kio) Téléchargé 13 fois
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message