Trouver valeur en commençant par la fin de colonne Fermé

Question

Bonjour à tous,

Pensez vous qu'une formule excel puisse trouver une valeur alphabétique (M ou A) dans une colonne en faisant une recherche en partant du bas de cette colonne ? Ou autre procédure amenant au même résultat ?

Je m'explique : Dans un tableau de relevés de compte, la colonne en question signale par "M" une ligne de comptage mensuelle et par "A" une ligne de comptage annuelle.
Sur plusieurs années de saisie de compte, il y a plein de "M" et de "A".

Par exemple, pour que la ligne de comptage mensuelle de juin 2015 face les totaux du mois, il lui faut trouver la ligne de comptage de mai 2015, c'est à dire, le "M" précédent.
En trouvant le "M" précédent et en renvoyant sa référence de cellule, je saurais ainsi tirer le numéro de ligne et renseigner la plage de cellules pour que la fonction somme fasse son travail.

Mon problème est donc de trouver le dernier "M" dans la colonne en contenant plein et de me donner le numéro de sa ligne. Je suis limité aux fonctions excel, sans macro, ni fonction matricielle car mon application doit aussi tourner sur mon smartphone.

Compliqué pour moi mais surement de la rigolade pour vous !? Je vous remercie d'avance pour votre aide ...

Mike-31 · Answer

Bonsoir,

Serait il possible d'avoir un exemple de fichier anonymisé que l'on comprenne sa structure avec ton exemple décrit dans la demande s'il te plait, 
pour joindre ton fichier exemple sert toi de ce lien
https://www.cjoint.com/ 
 
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

lou-i · Answer

Voici mon fichier via le lien ... si je ne me suis pas planté dans la procédure !

https://www.cjoint.com/c/EGzukNhGfZv

eriiic · Answer

Bonjour,

Pas très bonne idée de mettre les totaux dans la même colonne. 
=SOMME(D4:D23)-SOMME.SI($C$4:C23;"M";$D$4:D23) ?
eric

Mike-31 · Answer

Re,
Salut Eriiic,

d'après ce que je comprends, tu souhaites faire les sommes mensuelles, si c'est cela en E4 tu colle cette formule et tu incrémente vers le bas

=SI(C4="";"";SOMMEPROD((MOIS($B$4:$B$37)=MOIS(B4))*($D$4:$D$37)))

par contre si tu cherches à rapatrier la date du M précédent je ne vois qu'une formule matricielle colle cette formule sur la ligne confirme la en matricielle et incrémente vers le bas mais cela ne correspond pas à ta demande (sans matricielle)
 =SI(C4="";"";GRANDE.VALEUR(SI($C$4:C4="M";$B$4:B4;0);2))
à par que tu ne cherches que le l'index ligne du M précédent
=SI(C5="";"";EQUIV(E5;$B$4:$B$37)+3)
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

lou-i · Answer

Ouille !!!

Merci Eriiic pour ta réponse ... comme tu dis, ce serait plus simple de faire les totaux sur une autre colonne mais avec mon écran smartphone, je suis limité en visu !

Ta formule est sympa mais elle reprend au passage le résultat des sommes mensuelle précédente et cela fausse le résultat.

Merci également Mike-31 ... j'essaie de tirer tous les éléments de ta première formule (je ne suis pas un pro ... il me faut du temps).
Comme tu l'as mentionné, pas de matricielle.
Aussi, la colonne supplémentaire est un problème.

Au premier essai, ta formule reprend sa propre somme mensuelle et ne donne donc pas le bon résultat.

Je réfléchis à comment avancer avec toutes vos propositions et merci si vous en trouvez d'autres ...

Mike-31 · Answer

Re,

d'après ton fichier en E24 cette formule te donne le total du mois 422,133333333333

=SI(C24="";"";SOMMEPROD((MOIS($B$4:$B$37)=MOIS(B24))*($D$4:$D$37)))

si ce n'est pas ce que tu cherches que souhaites tu avoir en E24 en face le 3 éme "M" 
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

lou-i · Answer

Merci merci merci ...

J'ai juste fait une petite modif à ta super formule (formule que je n'avais jamais utilisé jusqu'à maintenant et qui est super adaptée à mon besoin actuel).
=SOMMEPROD((MOIS($B$4:B33)=MOIS(B34))*($D$4:D33))
Ainsi, n'est pris en compte que la plage de cellule au dessus de la ligne de comptage concernée.

Je vous envoie le fichier modifié avec nouvelle formule et intégration à la colonne "D" comme je le voulais.

Ce que je n'avais pas précisé en présentation (car, pas de première importance en rapport de ma question principale), c'est que mes lignes de comptage (de toute une année et plus si besoin) sont stockées en amont de ma feuille de compte. Elles viennent prendre leurs place à date échéante à chaque trie de la feuille.
Les formules qu'elles intègrent font alors le calcul de toutes les lignes concernées du même mois (comme ta formule sait le faire). 

Merci encore et bonne nuit ...

lou-i · Answer

Voici le fichier 

https://www.cjoint.com/c/EGzwWktozxv

J'ai du utiliser le lien que tu m'as envoyé le premier coup car je n'ai pas trouver comment le joindre avec mon message.

Si tu as une info, je suis preneur sinon, je chercherai demain.

Bonne nuit messieurs ...

Mike-31 · Answer

Re,

ton tableau s'arrête à la ligne 37 donc inutile de modifier la formule pour arrêter à la ligne 33 et même si la formule prenait en compte la plage jusqu'à la ligne 50 cela ne changerait rien au résultat
 si tu ne veux prendre en compte que la plage jusqu'au dernier M tu peux le faire en automatique avec cette formule
=SI(C5="";"";SOMMEPROD((MOIS(INDIRECT("$B$4:$B$"&EQUIV(9^9;D:D;1)))=MOIS(B5))*(INDIRECT("$D$4:$D$"&EQUIV(9^9;D:D;1)))))

mais pour ma part je prendrais en compte la totalité du tableau

=SI(C5="";"";SOMMEPROD((MOIS(INDIRECT("$B$4:$B$"&EQUIV(9^9;B:B;1)))=MOIS(B5))*(INDIRECT("$D$4:$D$"&EQUIV(9^9;B:B;1))))) 

Ensuite je ne comprends pas ta dernière question post 8

lou-i · Answer

Bonjour Mike,

Waaaaoouuu !!! Tu es le roi de la formule ! Il va me falloir du temps pour analyser tes deux dernières formules, mais je vais m'y coller.

En attendant, comme je te l'ai écrit hier soir, mon problème est résolu avec la formule d'hier.
Si tu regardes le dernier fichier envoyé avant que j'aille dormir, tu vois que chaque ligne de "comptage mensuelle" intègre ta formule modifiée ... uniquement dans les lignes de comptage mensuelle et en colonne D (donc, dans l'alignement de tous les montants de transactions).

Cela marche grâce à la petite modification de la plage de référence dans SOMMEPRO(), celle définissant la fin de plage que SOMMEPRO() doit prendre en compte.
Il suffit simplement de supprimer la valeur absolue de fin de plage (correspondant dans ta formule à la fin du tableau) pour la remplacer par la référence cellule de dernière transaction avant la ligne de comptage. Ainsi, chaque ligne de comptage ne prends en compte que la plage au dessus d'elle.
Egalement le début de formule "SI C?="";"";... " n'a plus lieu d'être.

Ce sera plus simple de regarder mon fichier en D5 D19 D24 D34 pour tout comprendre.

A propos de ma dernière question d'hier, je ne trouve pas par quel moyen ont peut ajouter une pièce jointe (type fichier Excel) à notre message.
J'ai réussi grâce au lien que tu m'as envoyé mais n'ai pas les yeux en face des trous pour trouver, dans la présentation CCM, où la trombone se trouve qui me permette l'accès à mon arborescence de fichiers !?

A+

lou-i · Answer

Salut Eric,

Je viens de voir ta réponse.

L'erreur ne vient pas de ta formule qui fait bien les comptes sur toutes la plage ... sauf, comme cela a été remarqué hier, je dois faire le comptage du mois dans la même colonne où se trouve toutes les transactions.
La formule telle que tu me l'a proposé calcul le mois en cours mais aussi, le résultat du comptage du mois en cours (donc, le double).

Veux tu regarder le dernier fichier envoyé, la solution qui me convient s'y trouve (en cellule D5 D19 D24 D34). Simple et léger.

Je ne connaissais pas cette formule avant que Mike m'en fasse une proposition.

Je vous remercie tous les deux et retiens les solutions que vous m'avez proposez pour toutes mes applications à venir ...

A+

Mike-31 · Answer

Re,

Si la proposition d'Eriiic ne fonctionne pas sur ton Smart, je ne vois pas pourquoi tu veux impérativement les bornes de chaque plage année et mois qu'il est bien sur possible de définir par des formules complexes alors qu'il existe des solutions assez simple,  en face chaque M il suffit de coller cette formule, exemple pour D19

=SOMMEPROD((ANNEE($B$4:$B$37)=ANNEE(B19))*(MOIS($B$4:$B$37)=MOIS(B19))*($D$4:$D$37))
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

lou-i · Answer

Je suis d'accord avec toi Eric, "indiquer le résultat voulu est indispensable" et je me rends compte que ce n'est pas évident de tous expliquer et d'être compris.

A suivre vos réponses, je me demande si vous avez vraiment compris mon organisation avec les lignes de comptage qui viennent automatiquement en fin de mois pour donner le résultat des transactions du mois au dessus d'elle ?!

Toujours est-il que vous m'avez aidé à trouver la solution à mon problème et cela marche très bien !

Voici le récapitulatif sur fichier de ce que j'ai expliqué :
https://www.cjoint.com/c/EGAxGQoDwjv

A+

lou-i · Answer

Bonjour Eric,

Sincèrement pardon si j'ai dit ou fait des choses outrageantes.

Je relis l'ensemble de mes écrits et suis surpris de ta réaction vivace !?!?
Je reviens vers toi pour tenter d'éclaircir cette situation.
J'ai été , je crois, assez poli dans tous mes messages avec toi et Mike, en disant bonjour et en remerciant sincèrement encore et encore pour l'aide que vous m'apportez tous les deux, pour trouver ta réaction un peu dure.
Je n'ai pas dit bonjour dans ce dernier message et t'en demande pardon.

Avant de dire que "j'avais l'impression que toi et Mike n'aviez peut être pas compris la fonctionnalité de mes lignes de comptage", ce qui pourrait justifier que l'on arrive pas totalement à s'entendre sur cette histoire de résultat faux (car, le double de ce qui devrait être), je t'ai écrit que j'étais d'accord avec toi sur le fait "qu'indiquer le résultat voulu est indispensable" et aussi, que "je me rendais compte que ce n'était pas évident de tous expliquer et d'être compris". Autrement dit, je ne porte pas la responsabilité sur quelqu'un comme tu sembles l'avoir compris ... encore moins celle de vous prendre pour des boeufs. Je suis sincèrement désolé que ma façon d'écrire t'est amené à de telles déductions.

En retour, j'ai dit à plusieurs reprises que vous m'aviez aidé à trouver ma solution et le résultat est en démonstration "simplifiée" dans le fichier en PJ (présentation simplifiée limité au problème pour lequel je vous ai sollicité car je n'ai pas vu l'intérêt de vous présenter l'ensemble des fonctionnalités et formules de mon application bien plus importante que ce présenté).
Les lignes de comptage prennent leurs place automatiquement (grâce à des formules qui revoient un numéro de date "conditionnelle" dans la première ligne de mon application). Le trie automatique,dans mon application, fonctionne très bien et me suis donc contenté de vous expliquer que "les lignes de comptage prenaient leur place automatiquement.

La difficulté à bien s'entendre, entre nous trois, semble venir de ces fameuses lignes de comptage et aussi, de l'unique colonne dans laquelle toutes cohabitent (défini dans ma présentation du sujet, dès le départ). Vous comprenez bien que si ces lignes font le total, elles ne doivent pas être, elle-même, prise en compte dans le calcul du mois, sinon le résultat devient faux du fait du double de la réalité.

Revenons à l'outil actuel, les formules y sont courtes, simplement 13 formules par an (12 mensuelles et 1 annuelle ... léger pour Smartphone), fonctionnent à merveille, respectent la nécessite d'une seule colonne de comptage ... pour moi c'est super ... et grâce à votre aide ... pourquoi y aurait il un problème entre nous ? Quelque chose d'autres m'échappe t-il ?

Si mes explications et le fichier en exemple ne nous amènent toujours pas à l'entente, je veux bien avoir une conversation téléphonique ... plus facile que l'écrit.

Merci encore à toi ...

A+

Trouver valeur en commençant par la fin de colonne

14 réponses

Discussions similaires

Newsletters