Formule magique ? Recherche de dates

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

23directeur.xlsm (140.20 Ko)

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

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

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

11directeur.zip (48.44 Ko)

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

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

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

8directeur.xlsm (147.81 Ko)

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

6directeur.zip (48.90 Ko)

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

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

14directeur-2.zip (60.60 Ko)

Bonjour

Ne te soucies pas de revenir sur le sujet, c'est le but du Forum que d'aider jusqu'à la solution définitive.

Pour C49, tu as mal placé le -1

=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"))))-1))

Cordialement

Bonjour,

si on considère que c'est 2275.82 (et non .92) il me semble que ma proposition est correcte.

eric

Bonsoir Eric, Amadeus,

Bonsoir le forum,

Je viens de penser à quelque chose.

Si au lieu de se positionner sur la bon solde, il était possible de le calculer!

En effet, si je mets 01/01/2014 en date de début pour avoir le solde il faut calculer solde de départ - les dépense jusqu'au 31/12/2013 + les rentrées d'argent jusqu'au 31/12/2013.

Je vais essayer de voir si je trouve quelque chose de ce côté.

A bientôt

Joseph


Eric, Amadeus,

Ma parole, vous me répondez plus vite que mon ombre !!!

Amadeus, je vais re-tester.

Eric, oui ta proposition semble fonctionner.

Pourrais-tu me dire d'où vient le =ancienSolde et nouveauSolde

Je ne les ai pas trouvé en gestionnaire de noms

D'autre part, en A48 de Décompte il n'y aura pas C1, mais par exemple Compte Courant, LDD, etc.

Par quoi faudra-t'il remplacer le A48 dans

=ancienSolde(A48;$D$19)

; 'C1!' ?

Je m'y remets demain pour les tests, parce que là je n'arrête plus de tousser et de me moucher.

J'espère que cela ira mieux demain.

Vous êtes formidables !

Merci à vous

Joseph

Bonsoir Eric, Amadeus

Bonsoir le forum,

Bon, j'ai encore refait des tests, et il me semble que tout fonctionne.

Pour l'exemple avec les formules, il reste encore le #VALEUR si la date de fin est trop élevée.

D'ailleurs il faut que je revienne sur un truc qui m'a fait repenser à une blague que l'on m'avait racontée :

Pour C49, tu as mal placé le -1

Une entreprise a un problème matériel sur un PC et fait venir un réparateur.

Ce dernier ouvre le boitier, regarde un peu, puis serre une vis.

Il repart, tout fonctionne.

Quelques jours après, arrive la facture à l'entreprise : 400 € !

Le responsable rappelle le réparateur et lui dit : vous n'êtes pas peu gonflé de demander 400 € juste pour serrer une vis !!!

et le réparateur de lui répondre : ah non, serrer la vis c'était 1 €. Mais savoir laquelle, c'est ça qui a couté 399 €

Ce qui m'a fait penser au -1 : il fallait savoir où le mettre !

Je ferme ce petit interlude.

Ensuite dans vos propositions vous faites référence pour l'exemple à la cellule A48 pour ouvrir l'onglet C1

Or cette cellule contiendra en vrai par exemple Compte Courant, LEP, etc...

Je crois bien avoir tout essayé (sauf la bonne méthode cf. mon interlude plus haut)

'C1'!, C1, "C1'!; "; "'"C1 (avec ou sans !), etc...

C'est d'ailleurs fou le temps que l'on peut prendre à chercher sur internet !

J'ai même essayé en cherchant comment trouver le nom de l'onglet ce qui est idiot puisque je les connait déjà : c'est C1, C2...C30

Eric, ne tient pas compte de ma dernière réponse. Ce sont 2 macros que tu as faites. Et moi qui regarde dans le gestionnaire de noms...

En fait, ce que je connais étant principalement par des livres, je croyais qu'une macro n'était déclenchée que par une action, style appuyer sur une touche, cliquer sur OK, Annuler, etc.

Reste encore les formules en D49, E49, F49, G49 et H49 de Décompte.

Celles là ne me poserons pas de problème. J'aurai juste voulu savoir une chose : laquelle est la meilleure ?

SOMME(SI.....) comme en D49, ou SOMME.SI () comme en E49

Voilà.

Que dire d'autre, sinon encore et toujours MERCI

Et si l'un ou l'autre peut me dévoiler un secret !!! Je suis preneur.

Amicalement

Joseph

8directeur-2.zip (60.61 Ko)

Bonjour

Un essai en I49 (Pas triste) Matricielle

=INDEX(INDIRECT("'"&$A48&"'!$F:$F");SI(MAX(INDIRECT("'"&A48&"'!$B$3:$B$1000"))<$H$19;MAX(SI((INDIRECT("'"&A48&"'!$B$3:$B$1000")<$H$19)*(INDIRECT("'"&A48&"'!$B$3:$B$1000"));LIGNE(INDIRECT("'"&A48&"'!$B$3:$B$1000"))));MIN(SI((INDIRECT("'"&A48&"'!$B$3:$B$1000")>$H$19)*(INDIRECT("'"&A48&"'!$B$3:$B$1000"));LIGNE(INDIRECT("'"&A48&"'!$B$3:$B$1000"))))-1))

Cordialement

Bonjour,

Pourrais-tu me dire d'où vient le =ancienSolde et nouveauSolde

Je ne les ai pas trouvé en gestionnaire de noms

D'autre part, en A48 de Décompte il n'y aura pas C1, mais par exemple Compte Courant, LDD, etc.

Par quoi faudra-t'il remplacer le A48

Je vois que tu as trouvé.

Il s'agit de fonctions personnalisées.

Une fois crées elles s'utilisent sur une feuille comme une fonction native d'excel.

On passe dans le 1er paramètre le nom de la feuille (via A48 ici) où doit s'effectuer la recherche du solde.

Si le libellé en A48 est différent du nom de la feuille il faut que tu te crées une table de correspondance libellé|Nom Feuille et que tu le retrouves le nom avec un recherchev() (valable pour la proposition d'Amadeus aussi).

eric

Bonsoir Eric, Amadeus,

Bonsoir le forum,

Amadeus, pas triste en effet la formule ! Impressionnant même, je dirais. Je vais l'essayer !

Eric, il n'est pas possible de créer une correspondance Libellé/Nom feuille.

Le libellé peut être n'importe quoi. Cela peut être Compte courant pour 'C1' et également Compte courant pour 'C2'

Par contre la cellule C49 en Décompte est et sera toujours le solde de la feuille 'C1', comme la cellule C52 sera toujours le solde de la feuille 'C2', etc.

Donc dans

=ancienSolde(A48;$D$19)

il faut simplement remplacer la valeur de A48 qui est dans l'exemple C1, par 'C1' qui est le nom de l'onglet.

Un problème de syntaxe pour moi, mais cela n'est peut-être pas possible.

Bon, après je peux imaginer de mettre C1, C2...C30 dans une colonne cachée et y faire référence dans la formule.

Je vais chercher.

Merci à vous deux

Amicalement

Joseph

Bonsoir Eric, Amadeus,

Bonsoir le forum,

Je ne vous ai pas oublié, j'y travaille (lentement certes, mais j'y travaille.), mais j'ai eu beaucoup d'autres choses à faire aujourd'hui.

Je reviens vers vous dès que j'aurai avancé (bis : lentement certes, mais dès que j'aurai avancé.)

Donc, à demain.

Sans oublier les mercis...

Seul je n'aurai rien trouvé de toutes les réponses que vous m'avez apportées.

Amicalement

Joseph

Bonsoir Amadeus, Eric

Bonsoir le forum,

J'ai rajouté au fichier réel une feuille VAR dans laquelle j'ai mis la valeur C1 dans la cellule A1, la valeur C2 dans la cellule A2, et ainsi de suite jusqu'à la valeur C30 dans la cellule A30.

Ce qui m'a permis de remplacer tant dans les formules que dans la macro A48 par VAR!A1 et cela me semble fonctionner.

Amadeus, dans la formule "magique" qui l'est toujours, j'ai cherché et cherché pourquoi elle fonctionne pour les onglets C1 à C5, mais pas pour les onglets C6 à C30. Je l'ai donc modifiée (en ne sachant évidemment pas vraiment si c'est bien ou pas)

&COLONNE(A:E)&

par &COLONNE(A:AD)& ce qui me donne ceci :

=SOMMEPROD((NB.SI(DECALER(INDIRECT("C"&COLONNE(A:AD)&"!A3");LIGNE($1:$509)-1;0);$A26)*(N(DECALER(INDIRECT("C"&COLONNE(A:AD)&"!B3");LIGNE($1:$509)-1;0))<=H$19)*(N(DECALER(INDIRECT("C"&COLONNE(A:AD)&"!B3");LIGNE($1:$509)-1;0))>=D$19)*(N(DECALER(INDIRECT("C"&COLONNE(A:AD)&"!D3");LIGNE($1:$509)-1;0)))))

Le AD correspondant à 30

Est-ce correct ? En tout cas j'obtiens bien le total de tous les onglets.

Je reviens encore sur les formules en D49, E49, F49, G49 et H49 de Décompte.

Quelle est la meilleure solution ?

Utiliser SOMME(SI.....) comme en D49, ou SOMME.SI () comme en E49

Il ne me restera plus ensuite qu'à modifier toutes les formules et macros du fichier réel...

Et les comptes étant au nombre de 30, je vais donc en faire 15 par formule et 15 par macro.

Vous avez été tellement sympathiques que je ne peux pas décemment n'utiliser qu'une seule solution !

Merci

Amicalement

Joseph

Bonjour Amadeus, Eric

Bonjour le forum,

Bon j'ai toujours la grippe qui me mine, et qui explique le délai de réaction.

J'ai je pense tout testé, et bien évidemment tout fonctionne bien.

Tout, oui : mais la saisie et bien plus lente qu'avant.

Il faut que je voie ce qu'il est possible de faire.

Peut-être le sujet d'un autre post, en tout cas, je peux clore celui-ci.

Encore merci

Joseph

Rechercher des sujets similaires à "formule magique recherche dates"