Nommer automatiquement une plage

[Schom]

Bonjour,

Je nage un peu, j'ai besoin d'une bouée.

Soit deux onglets, le premier est une liste de dépenses avec en première colonne la date (jj/mm/aaaa) suivi du poste de dépense et enfin du montant.
Le deuxième onglet est un récapitulatif mois par mois (colonne) des dépenses poste par poste (ligne).

Une formule (SOMME.SI) me permet d'aller chercher la somme des dépenses d'un poste donné sur une plage. Comme je fonctionne au mois, je dois préalablement définir et nommer une plage en sélectionnant les cellules dont le mois correspond à celui qui m'intéresse.

Par ex. =SOMME.SI(Septembre14;"Gasoil";Montants0914)
- Septembre14 : plage préalablement défini en sélectionnant manuellement les cellules dont les dates correspondent au mois de septembre 2014
- "Gasoil" : c'est l'occurrence que je recherche
- Montant0914 : plage où sont les montants (encore que celle là, je pense pouvoir la choper avec un DECALER, attendez ma question pour comprendre pourquoi je dis ça)

La contrainte est de définir à chaque fois une plage précise, qui peut s'agrandir au grès des dépenses du mois.

Dans le menu : Insertion/Nom/Définir, je cherche à trouver la formule qui me permettrait de lui dire : quand la date de la cellule de cette colonne est comprise entre le 01/09/2014 et le 30/09/2014, tu l'ajoutes à la plage "Septembre14".
Me dérange pas de répéter l'opération pour tous les mois, c'est pas une corvée, même si ce serait classe qu'il nomme automatiquement la plage.

Pour la plage des montants, je crois pouvoir m'en sortir en partant de la première plage des dates et en lui demandant de décaler la sélection de quelques colonnes.

Si une bonne âme charitable pouvait m'aider, je lui vouerais une reconnaissance éternelle.... ou presque

 

[pierrejean]

Re : Nommer automatiquement une plage

Re

La reconnaissance éternelle nous passionne de façon toute relative , par contre un fichier exemple sans données confidentielles pourrait nous donner du cœur à l'ouvrage !!!!

 

[Schom]

Re : Nommer automatiquement une plage

Merci pour cette prompte réponse fort à propos.
Voici donc l'objet du délit.

Au passage, petite coquille dans la def de la formule. La plage "Septembre14" est en fait celle qui liste les postes de dépense, dans lequel est recherché le critère.... évidemment ::rallyes: (c'était pour savoir si vous suiviez...)

 

[pierrejean]

Re : Nommer automatiquement une plage

Re

je suis plutôt spécialisé dans le Vba et quasiment nul en formule
Aussi je te propose une fonction personnalisée qui permet de définir la plage de la colonne A de la feuille Relevé B si le mois est celui mentionné en parametre
Une sub test te permettra de voir comment on peut l'utiliser
Si toutefois tu tiens à une formule ,reviens et je t'aiguillerai vers un spécialiste es formules

 

[chris]

Re : Nommer automatiquement une plage

Bonjour
Coucou Pierrejean

En mettant de vraies dates en colonne A des dépenses et pas des textes, et en modifiant de même les en-tête de Suivi Budget un simple SOMME.SI.ENS suffit sans plages nommées.

En mettant la source sous forme de Tableau, cela éviterait cependant des calculs sur des colonnes entières...

 

[Schom]

Re : Nommer automatiquement une plage

Coucou

Re

je suis plutôt spécialisé dans le Vba et quasiment nul en formule
Aussi je te propose une fonction personnalisée qui permet de définir la plage de la colonne A de la feuille Relevé B si le mois est celui mentionné en parametre
Une sub test te permettra de voir comment on peut l'utiliser
Si toutefois tu tiens à une formule ,reviens et je t'aiguillerai vers un spécialiste es formules

Merci pour ce script.
J'allais te dire que je taquine (un peu) le VBA sur windows, et que je ne suis pas très à l'aise avec la version mac. Mais j'ai eu du mal à décrypter ta macro. C'est dire si j'ai du chemin à parcourir. Quoiqu'il en soit, elle répond exactement à ce que j'ai demandé et je t'en remercie.
Et en effet, je pense que je préfère une formule. Le fichier va être utilisé par d'autres, et je gagnerais en simplicité si je passe par une formule.

Merci pour le temps que tu m'as accordé.

 

[pierrejean]

Re : Nommer automatiquement une plage

Re

OK je te laisse avec Chris (bises)

 

[Schom]

Re : Nommer automatiquement une plage

Bonjour
Coucou Pierrejean

En mettant de vraies dates en colonne A des dépenses et pas des textes, et en modifiant de même les en-tête de Suivi Budget un simple SOMME.SI.ENS suffit sans plages nommées.

En mettant la source sous forme de Tableau, cela éviterait cependant des calculs sur des colonnes entières...

Alors celle là, elle est excellente... à défaut de définir automatiquement la plage, tu as répondu à ce que je cherchais de manière fort élégante.

J'espère que tu pourras m'accorder un peu de ton temps pour me donner quelques pistes de compréhension...

J'avais repéré cette fonction (SOMME.SI.ENS.), mais l'avais laissé tomber n'arrivant pas à la mettre en application.

=SOMME.SI.ENS(Relevé[[Montant de l''opération]:[Montant de l''opération]];Relevé[[Date de l''opération]:[Date de l''opération]];">="&D$1;Relevé[[Date de l''opération]:[Date de l''opération]];"<="&FIN.MOIS(D$1;0);Relevé[[Catégorie]:[Catégorie]];'Suivi Budget'!$B5)

Première question, comment fais tu pour que la plage "Relevé" que tu as créée s'adapte automatiquement au nombre de ligne ?
Quand tu fais appelles à une plage, peux tu m'expliquer la syntaxe : "[Montant de l''opération]:[Montant de l''opération]" par exemple (pourquoi est ce répété deux fois, question bête).
Quel est le sens du point d'exclamation ?
Cela m'aiderait si tu pouvais me vulgariser la formule, en me l'écrivant en "français".

Bref, me contenterais bien de ta formule, mais ça pique ma curiosité. J'ai faim, mais le poisson ne me suffit pas, j'ai besoin d'apprendre à pécher

Merci encore pour le temps que tu m'as accordé

 

[chris]

Re : Nommer automatiquement une plage

Bonjour

L'élasticité est obtenue grace à l'utilisation des tableaux, objet intermédiaire entre le tableur et la table de base de données.

Un peu de lecture ici Ce lien n'existe plus et là Ce lien n'existe plus

Notamment pour la syntaxe, la répétion équivaut à mettre des $ (non applicables à cette syntaxe) comme expliqué dans le second lien.

 

[Schom]

Re : Nommer automatiquement une plage

Coucou

merci pour cette lecture instructive.

En effet, le mode tableau est très efficace, ôte bien des soucis, et apporte pas mal de fonctionnalités et de souplesse.
Merci encore pour votre aide.

[EDIT] : et à tout zazar, est il possible, lorsque la formule en question prend en compte la valeur d'une cellule pour faire son calcul, que cette derniers soit grisée ?
Vous l'aurez compris, l'idée étant de voir d'un seul coup d'oeil les cellules qui n'ont pas été traitées pour les recodifier afin qu'elles le soient.
J'ai réfléchi à la question et à part en VBA (je suppose peut être à tort que c'est possible, de manière plus ou moins tordue, de le faire), je vois pas comment. À moins qu'il existe une formule spécifique qui grise quand l'appel de la valeur est fait...
Un moyen tout aussi tordu de le faire en formule, serait de considérer qu'à partir du moment où les critères de la formule initiale sont valides (check sur la correspondance des catégories par ex), elle sera calculée et donc à griser. Mais elle ne fera que m'indiquer que cette cellule est potentiellement intégrée à mon tableau...