Formule issue de la concaténation de plusieurs cellules
Bonjour,
Tout d'abord, je suis heureux d'avoir trouvé ce site d'entre-aide.
Ma question est la suivante : je souhaite pouvoir construire une formule dont l'écriture dépend du contenu de différentes cellules.
Exemple :
Dans la colonne A, j'ai une suite de nombres. Je souhaite faire la somme de ces nombres en adaptant la formule au nombre de cellules effectivement renseignées.
En B1 je fais la formule suivante :
="A" & NB(A:A)
Ainsi, si par exemple j'ai les 10 premières cellules de renseignées j'obtiens : "A10".
Je souhaite intégrer le résultat de cette cellule dans mon calcul de SOMME :
= SOMME( A1:A10)
J'essaye de jongler avec les guillemets et apostrophes, mais j'avoue ne pas bien maitriser.
Avec INDIRECT, je n'y arrive pas non plus.
Précision : j'aimerais autant que possible ne pas utiliser le VB.
Merci à vous tous pour votre aide.
Stefanopoulos
Bonjour,
Je serais toi, si ta plage de somme débute toujours au même endroit, change tes formules comme cela :
en B1 :
="A1:A"&NB(A:A)et dans ta somme :
=SOMME(INDIRECT(B1))Voir même en 1 fois si tu n'as pas besoin d'une cellule avec le résultat intermédiaire du nombre de cellule :
=SOMME(INDIRECT("A1:A"&NB(A:A)))Cordialement,
Merci beaucoup,
Mon exemple était juste pour imager ma question. Je souhaitais juste comprendre comment faire ce type de formule.
Je te remercie pour ta réponse, ou plutôt TES réponses (plusieurs solutions d'écriture).
En fait, mon véritable problème est le suivant :
Je souhaite adapter automatiquement le contenu d'une liste déroulante en fonction de l'évolution éventuel des choix.
Je vais essayer de faire cela avec les écritures que tu me suggères. En fait, j'utilisais mal la fonction INDIRECT (je faisais INDIRECT(SOMME ... au lieu de SOMME(INDIRECT...).
Encore merci,
stefanopoulos
Sinon plutot que de donner un exemple pour "imager" ton problème, expose ton problème réel ou mieux joints un fichier exemple avec ta vraie problématique, on pourra te donner des indications plus précises
OK.
Donc voici mon réel "problème" (fichier joint).
Je veux suivre la prospection des clients. J'ai la 1ère feuille qui reprend l'ensemble des prospects et les autres feuilles (pour chaque mois de l'année) qui doit me permettre de suivre l'ensemble des actions entreprises pour chaque client de la feuille prospect.
Pour l'instant, par une RECHERCHEV, je mets le CODE prospect et ça me met directement le nom du prospect concerné dans la cellule appropriée.
Ca serait bien évidemment bien plus pratique si, pour chaque feuille de suivi par mois, je pouvais faire apparaitre un menu déroulant au niveau de la cellule PROSPECT reprenant le listing de la feuille PROSPECT, classé par ordre alphabétique.
Le mieux serait peut être de faire un menu déroulant au niveau de la cellule code, le menu déroulant faisant apparaitre le nom du prospect + le CP + le CODE. Cela permettrait de gérer les éventuels homonymes. La sélection dans le menu déroulant permettrait ainsi de renseigner d'un coup plusieurs cellules.
Je doute que cela soit possible sans VB, mais on ne sait jamais.
Merci,
stefanopoulos
bonjour
si j'ai bien compris ; un essai
avec des images pour expliquer
cordialement
Alors ok, mais il s'agit de s'implement mettre une liste déroulante en cascade sur chaque cellule de la colonne, je ne vois pas le rapport avec ton 1er post ?
Dans l'onglet Formules => définir un nom et dans la fenêtre qui apparait :
Nom : tu mets ce que tu veux, "prospect" par exemple
Zone : classeur
Fait référence à : =DECALER(prospect!$B$2;;;NBVAL(prospect!$B:$B)-1)
(Formule pour ton fichier exemple, à modifier pour fichier réel si il diffère un peu)
Puis ensuite sur chaque onglet, tu séléctionnes les cellules de ta colonne prospect ou tu souhaites la liste déroulante et lorsque tu les as séléctionnées => Onglet Données => Validation des données et dans la fenêtre qui apparait :
Autoriser : Liste
Source : =prospect (ou le nom que tu as définis dans le gestionnaire de nom)
tu valides et voilà, tu as une liste déroulante sur chaque cellule avec le nom de tous tes clients, qui s'agrandira automatiquement à chaque nouveau client saisi grâce à la fonction DECALER().
Je t'ai fais l'exemple pour janvier, pour les autres mois il ne reste qu'à selectionner les cellules et refaire à partir de l'onglet Données. (Et du coup tu peux enlever tes rechercheV ).
Cordialement,
Edit: Bonjour Tulipe ^^
Merci bien.
Rapport entre mon 1er post et mon réel "problème"
Ce n'est effectivement pas évident. Pour ne pas passer pour un incohérent, permettait moi de m'expliquer.
Je voulais effectivement pouvoir définir une liste déroulante dont la plage de cellule de référence s'adapte au nombre de prospects entrés.
Je voulais donc compter le nombre de cellules renseignées l'intégrer à la plage de référence :
Ca donné donc : INDIRECT("A1:A" & NBVAL(A:A)+1)
Mais je ne savais pas comment intégrer cela à une formule. L'exemple avec la formule SOMME me permettais de simplifier ma question.
Cette solution répondait à ma problématique à l'exception de 1 points : je ne pouvais pas faire référence à une autre feuille
Solution proposée
Merci bien. Toutefois je n'arrive pas à trouver "l'onglet Formules" pour définir un nom. (c'est bon, j'ai trouvé : Menu INSERTION ---->NOM---->Définir)
Enfin, puis-je trier le contenu de la liste déroulante par ordre alphabétique ?
Encore merci de votre aide et désolé pour mon manque de clarté.
Cordialement,
stefanopoulos
re
bon
mais tant que tu ne pourras pas definir le nom de la liste ;tu ne pourras rien faire sur des autres feuilles ou alors il est possible de ruser mais c'est :moyen ;lourd ;+compliqué
pour ce qui est de l'ordre alphabetique ; tu peux parfaitement trier de a z la colonne prospect de la feuille qui te sert de base pour l'elaboration de la liste pour ce faire : Données>>>>trier >>> tu choisi la colonne de ref >>>ok
sans vouloir pousser le bouchon ; je suppose qu'il risque dy avoir des valeurs en double si tel est le cas ;il faudra regler ce probleme en amont avant de faire une liste
a+
ps il n'y a pas d'onglet formule ; c'est Insersion >>nom>>>definir
Alors sur Excel 2003 en effet ce n'est pas le même nom de menu :
il faut aller dans Insertion/Nom... Définir...
Par contre pour un tri alphabétique, il risque de falloir passer par une colonne intermédiaire ou une macro.
je me permets de poster ce message afin de faire partager la solution que j'ai trouvée pour ma problématique.
Bien évidemment, il doit y avoir beaucoup plus simple, mais peut être que cela pourra servir à d'autres qui sont du même niveau que moi (ni trop bon, ni trop mauvais, mais surtout ne souhaitant pas privilégier le VB).
Plus que de longs discours, ci-joint le doc correspondant.
Résumé de la problématique :
Je souhaitais faire un suivi des actions réalisés auprès des prospects appréhendés.
La 1ere feuille reprend les différents contacts, les feuilles suivantes les actions réalisés auprès d'eux pour chaque mois (je n'ai laissé que le mois de janvier).
Le but était, pour chaque feuille "mois", de faire afficher un menu déroulant dans les cellules prospect, basé sur le listing complet (et évolutif) de la feuille prospect. Le but était également de trier par ordre alphabétique ce menu déroulant.
Ma logique fût la suivante :
Dans la feuille "prospect", je définie le "rang" pour chaque prospect : =SI((B2<>"");(NBVAL(B:B)-NB.SI(B:B;(">"&B2)));"").
"si la cellule prospect n'est pas vide, je calcule le nombre de cellules non vide dans la colonne prospect (nombre total de prospects) duquel je soustrais le nombre de prospects dont le nom est "supérieur" (en terme de tri alphabétique) au nom du prospect de la ligne concernée".
J'obtiens logiquement le rang (position en terme de tri alphabétique) du prospect de la ligne concerné.
Dans la feuille "janvier", j'intègre la base de données de référence des prospects trié par ordre alphabétique. Pour cela je numérote de 1 à 1 000 les cellules de la colonne h (ce n'est pas bien évolutif si le nombre de prospect dépasse 1 000).
Dans la colonne i, j'intègre la formule suivante : =SI((H1<=MAX(prospect!A:A));RECHERCHEV(H1;prospect!$A:$B;2;FAUX);"").
"si le numéro dans la cellule h est inférieure ou égale au maximum des chiffres de la colonne a de la feuille prospect (nombre total de prospect), je recherche le nom du prospect dont le rang est égal au chiffre de la cellule h".
J'obtiens ainsi une matrice triant par ordre alphabétique les nom des prospects de la feuille prospect.
Dans la cellule j1 je compte le nombre de prospects de la feuille prospect : =NBVAL(prospect!B:B)-1.
"nombre de valeurs (cellules non vide) de la colonne b de la feuille prospect, moins 1 (l'entête de colonne)".
Maintenant j'intègre la liste déroulante dans les cellules prospect de la feuille "janvier". Je sélectionne toutes les cellules de la colonne et (pour excel 2003) je sélectionne (dans le menu) Données--->Validation.... Dans le formulaire apparaissant je mets LISTE dans Autorisé, et dans Source je mets : =INDIRECT("$I$1:$I"&$J$1).
"la liste déroulante est composée des valeurs allant de la cellule i1 à la cellule ix, x étant le nombre total de prospects de la feuille prospect, à savoir la valeur apparaissant dans la cellule j1".
Je conçois que ce n'est pas des plus simples mais j'espère que, pour des personnes n'ayant pas un niveau expert, cela permettra d'apporter quelques pistes.
Encore merci à tulipe et zirak pour les solutions apportées, même si je n'ai pas encore bien compris la logique de la formule DECALER qui est forte intéressante (je vais finir par comprendre la logique).
stefanopoulos