Formule générique se rapportant à l'onglet précédent
Bonjour,
J'ai quelques toutes petites notions d'Excel mais il y a quelque chose que je n'arrive pas à faire : j'ai des onglets comme ceux-ci :
avr 15
mai 15
jun 15
jui 15
aou 15
Dans chaque feuille (chaque mois, donc), il y a l'emploi du temps de 4 employés (ouverture, fermeture, journée). Je comptabilise le nombre de O, J, F, et je compte aussi le nombre de samedi de libres. Tout ça, c'est OK.
A noter qu'en A3 il y a la date
=DATE(2016;1;1), il suffit donc de copier une nouvelle feuille, de changer le mois en A3, et le tableau "emploi du temps" se met en forme tout seul (lundi 1, mardi 2, mercredi 3, jeudi 4... par exemple).
Je comptabilise un nombre de O, J et F sur chaque page (chaque mois, donc), avec un petit résumé du mois.
Mais j'aimerais en plus cumuler les O-J-F en question sur les 12 derniers mois (une année glissante, donc).
Je voudrais éviter d'utiliser une macro, et m'en tenir aux formules. Je pense que la fonction INDIRECT doit être utilisée, mais entre les & à rajouter, les ' car il y a un espace dans mes noms de feuilles, je n'arrive pas à l'écrire.
J'ai trouvé sur internet des formules qui peuvent m'aider
=CELLULE("filename";A1)pour pouvoir extraire le nom de la feuille avec STXT, et je pense qu'il faudra peut-être standardiser le nom de mes feuilles en fonction de la date : j'ai trouvé ça :
=NOMPROPRE(GAUCHE(SI(OU(MOIS(V17)=6;MOIS(V17)=7);S UBSTITUE(TEXTE(V17;"mmm");"i";"");TEXTE(V17;"Mmmm" ));3)&" "&TEXTE(V17;"aa"))qui m'écrit la date sous la forme Avr 15, Mai 15, Jun 15, Jul 15 ... (principalement pour différencier juin et juillet ; et ça me rajoute une majuscule).
Bref, si vous pouvez m'aider ça super super. Merci !
Bonsoir,
Sur l"exemple, on récupère la cellule A2 de l'onglet précédent.
La formule est la même pour tous les onglets
=INDIRECT("'"&TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;"mmmm aaaa")&"'!A2")
Si les noms des onglets sont Janv 15,Févr 15, Mars 15:
=INDIRECT("'"&TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;"mmm aa")&"'!A2")
Ceuzin
Merci c'est parfait !
Mais comme je ne comprends rien à cette formule, pourriez-vous m'indiquer quel est le terme qui fait référence à l'onglet précédent ? S'agit-il du "-1" ? Si je mets "-2" à la place, vais-je trouver l'onglet d'encore avant ? Et si je mets "+1", celui d'après ?
(Pour ne rien vous cacher, j'ai essayé le "-2" et ça ne fonctionne pas, j'imagine donc que je me trompe...)
Merci encore.
Bonsoir,
(comme un ch'veu sur la soupe
en fait le -1 correspond à moins 1 jour, si j'ai bien compris la fonction cherche le 01/du mois de l'onglet puis on retire 1 jour pour tomber sur le mois d'avant et on en prend la valeur de la cellule A2, si vous voulez remonter deux mois en arrière il faut mettre -31 par exemple...
@ bientôt
LouReeD
Bonjour,
Feuille actuelle:
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99)
Premier jour du mois
=DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))
Le dernier jour du mois précédent
=DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1
Feuille précédente:
=TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;"mmmm aaaa")
Feuille pré-précédente:
=TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-1);"mmmm aaaa")
ps: -31 ne fonctionne pas pour le mois de Février. Il faut mettre -32
Ceuzin
Merci encore, c'est impeccable. Voilà ce que ça donne :
=INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;0);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-1);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-2);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-3);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-4);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-5);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-6);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-7);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-8);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-9);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-10);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-11);"mmm aa")&"'!p4")+P4En mettant, 0, -1, -2 ... -11.
Bonsoir,
Rien qu'à la voir, une formule telle celle que tu produis me désole !
Je n'ai pas fait de réponse sur ton sujet jusqu'ici parce que pour répondre de façon rationnelle et efficace un fichier était nécessaire, et que tu n'as pas cru bon d'en mettre un...
Mais cela me désolerait encore plus de voir des débutants se pencher sur le sujet et imiter ce genre de formule...
Le problème posé : des onglets mensuels nommés avec les 3 premières lettres du mois (exception Juin et Juillet) et les 2 derniers chiffres de l'année, séparés par une espace, et avec majuscule initiale (on suppose que l'accentuation est respectée pour les caractères accentués). Sur l'onglet du mois en cours, on veut pouvoir faire des totaux cumulés des 12 mois précédents. Sur chaque onglet la date du premier jour du mois figure en A3
On commence par mettre sur l'onglet mensuel une formule permettant de lister les noms d'onglets des 12 mois précédant le mois en cours. Par exemple en K1 :
=NOMPROPRE(GAUCHE(SUBSTITUE(TEXTE(MOIS.DECALER($A$3;LIGNE()-13);"mmm");"jui";"ju");3)&TEXTE(MOIS.DECALER($A$3;LIGNE()-13);" aa"))On tire la formule jusqu'en K12 et on aura la liste des onglets des 12 mois précédents.
[NB- Si on veut faire le cumul glissant sur 12 mois, mois en cours inclus, il suffit de remplacer -13 par -12 dans la formule]
Cette colonne peut être masquée. Pour chaque nouvel onglet issu de duplication, la liste demeurera celle des 12 onglets précédents selon la date du mois en cours.
Supposons ensuite que l'on veut faire le cumul du contenu de A1 des 12 mois précédents. Une formule un peu plus lisible permettra de le faire :
=SOMMEPROD(SOMME(INDIRECT("'"&$K$1:$K$12&"'!A1")))Fichier d'illustration...
Bonjour à tous,
@ Mferrand : Le débutant que je suis apprécie l'attention ! Pour info, en ouvrant le fichier je constate que sur la feuille NOV 15 en F 7 le résultat est : # N/A ...
Bonne journée / nuit
VBABEGINNER
Je viens d'ouvrir le fichier sur le post, qui affiche (normalement : 78 en F7 !
Au demeurant, en cas d'incident aboutissant à une erreur, l'affichage aurait pu être soit 0, soit une valeur d'erreur #VALEUR ou #REF ou éventuellement #NOM, mais très peu probablement #NA !
Cordialement
Bonjour,
>Mais cela me désolerait encore plus de voir des débutants se pencher sur le sujet et imiter ce genre de formule...
Pour qui te prends tu?
Feuille précédente:
=TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;"mmmm aaaa")
Ce genre de formule est tout à fait banal.
Ceuzin
Bonjour ceuzin et désolé que tu te sentes visé !
Je faisais référence, ce qui me semblait évident, et me le semble toujours à la relecture de ma phrase, à une formule dépassant 15 lignes...
Je n'ai rien dit du tout s'agissant de la formule que te proposais concernant la récupération du nom de la feuille précédente ! Elle occupe une ligne, donne le résultat attendu, demeure facilement lisible et analysable... Et même si on peut toujours trouver des variantes ou des formules alternatives, je n'ai nullement cherché à le faire pensant que cela n'apporterait rien de plus. OK !
La situation se modifie lorsque l'on veut faire faire un travail, je dirai "répétitif" à une formule de base, qui aboutit généralement à une formule ultra-longue... Rien de condamnable pour un débutant, et même à d'autres niveaux... C'est toutefois l'indice qu'il faut penser le problème un peu différemment, et c'est notre rôle de le montrer. Les sujets de débutants venant avec ce type de formules (fréquent notamment avec les imbrications de SI...) demander si l'on ne pourrait pas procéder autrement montrent que même débutants, ne disposant pas des moyens de rechercher eux-mêmes une autre solution, ils sentent bien que le problème est dans ce cas à reconsidérer (question d'échelle qui le fait muter...)
Or, il faut le dire, il y a toujours moyen de faire autrement ! Quand on ne peut vraiment pas aboutir avec une seule formule, il devient préférable de la scinder, d'utiliser des formules intermédiaire nommées, etc. Mais les formules matricielles (avec ou sans validation matricielle), pas toujours aisées à utiliser même pour un utilisateur chevronné, permettent de répondre à pas mal de problèmes. Et au cas particulier, le problème tel qu'il a fini par se dessiner relève des "formules 3D" pour lesquelles Excel oblige à quelques contorsions... Ce qui a été largement défriché par des précurseurs qui nous ont précédé (à qui je rends hommage).
Lorsque je propose une formule de ce type, j'utilise un savoir acquis en étudiant leurs solutions. Je ne me prends nullement pour un "cador" comme tu sembles le sous-entendre, mais je ne perds pas conscience que je ne fais que participer à la diffusion de leur travail... Une formule que je propose est autant que possible adaptée au problème soumis : il m'arrive encore de devoir remonter à telle ou telle source pour trouver le déclic permettant de déboucher : je ne manque jamais dans ce cas de mentionner la source, même si la formule finale peut paraître dissemblable du modèle.
Sur ce, bien cordialement, et bonne journée à tous.
Bonsoir,
je suis d'accord avec le maréchal !
quand on voit sur le forum un truc comme cela :
=INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;0);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-1);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-2);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-3);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-4);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-5);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-6);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-7);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-8);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-9);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-10);"mmm aa")&"'!p4")+INDIRECT("'"&TEXTE(MOIS.DECALER(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;-11);"mmm aa")&"'!p4")+P4
en effet ça fait peur !!!
Quant à votre formule ceuzin elle est beaucoup plus simple et j'aimerais de mon coté pouvoir en "sortir" des comme celle là mais je n'arrive pas à assimiler, à avoir une vision globale des fonctions et leur imbrications pour le faire.
Souvent je décortique dans plusieurs cellules puis je regroupe morceaux après morceaux.
@ bientôt
LouReeD
Re,
Pourtant je jure que c'est bien écrit # NA ... Etrange ... Ca doit être moi.
Bonne soirée.
Bonsoir à tous,
Il semble que 2007 ne permettait pas encore des solutions qui marchent sur 2010...
Je n'ai plus 2007 sous la main, j'ai donc enregistré le classeur en .xls pour l'ouvrir sous Excel 2000.
Effectivement, là j'obtiens #NA !
Il fallait donc trouver une solution compatible avec 2007....
J'ai donc illustré mes propos
=SOMMEPROD(N(INDIRECT("'"&$K$1:$K$12&"'!A1")))Il faut donc remplacer SOMME par N dans la formule.
Celle-là marche sous Excel 2000, elle devrait donc fonctionner aussi sous 2007.
Une question de réglée ! J'espère...
@ LouReed : merci de ton implication !
D'accord avec toi sur la formule de ceuzin... Mais quand tu dis :
mais je n'arrive pas à assimiler, à avoir une vision globale des fonctions et leur imbrications pour le faire.
tu te sous-estimes un peu
Il n'y a pas si longtemps, tout compte fait, que je me sens un peu à l'aise avec les matricielles (et certains cas me font encore renacler un bon bout de temps). Et très peu de temps que j'ai appris à me servir de l'évaluateur de formule : ça permet effectivement de déceler souvent l'endroit où ça coince dans une formule ou la fonction qui ne fait pas ce qu'on voudrait...
Sinon construire pas à pas (quand on le peut) la formule en vérifiant qu'on a un résultat [et cohérent] à chaque étape est sans doute la solution la plus simple et efficace à la fois, on voit tout de suite où ça déraille (ce qui ne donne pas la solution
Si tu n'en es pas déjà familier je conseille vivement le site de Boisgontier, pas toujours facile à appréhender, car très dense et les explications sont concises, cela demande un travail mais qui fait découvrir pas mal de choses auxquelles on ne pense pas spontanément le plus souvent. Tu ne vas pas forcément y trouver la solution que tu cherches, mais la diversité donne un élargissement, des combinaisons, voire un déclic par analogie qui te permet souvent de voir comment la situation sur laquelle tu butes pourrait se débloquer (ce que j'appelais aller chercher de l'inspiration
Commence à se faire tard. Bonne fin de soirée.
Bonjour,
Ha oui maintenant ça marche super !
Bonne nuit à tous !
bonjour,
Pour faire la somme de janvier à décembre si les feuilles sont disjointes
=SOMMEPROD(N(INDIRECT("'"&TEXTE(DATE(2015;LIGNE(1:12);1);"mmm aa")&"'!A2")))
Ceuzin
Bonsoir le forum,
Au fait Maréchal, pourquoi "rose"
Qu'Est-ce donc ?
@ bientôt
LouReeD
J'avais pas trop le choix des couleurs, mais pourquoi pas rose ?
Et j'ai toujours l'impression que je rougis facilement (bien que n'ayant jamais eu de miroir sous la main au bon moment pour m'en assurer).
Sans vouloir vous embêter, connaissez vous Caro974 ?
à prioris elle est de la Réunion mais plus de nouvelle depuis le 16 juillet...
@ bientôt
LouReeD