XL 2010 référence texte cellule dans une formule

[Cyrille Aix]

bonjour à tous
je me décide à m'inscrire sur ce forum car après avoir lu pas mal de topics, je patoge encore sur mes problèmes.

le premier : dans la feuille 26.05, ma colonne L représente mes compagnies et les colonnes suivantes des statistiques sur chaque compagnie : formule à la clé. Le soucis c'est que je dois modifier dans chaque formule, la référence à la compagnie et j'aimerai automatiser tout ça.
dans la cellule M2 j'ai la formule =NB.SI.ENS(E:E;"*31*";E:E;"*AAF*")
je suis obligé de mettre des * car souvent les nom sont AAF03BV, le suivant AAF005 etc... mais ils appartiennent tous à la compagnie AAF
ce que je voudrais c'est remplacer AAF dans la formule, par une référence au contenu de la cellule L2 qui est le nom de la compagnie : AAF
ainsi si j'étends la formule sur toute la colonne M je n'aurais pas besoin d'écrire en M3 =NB.SI.ENS(E:E;"*31*";E:E;"*ABR*"), en M4 =NB.SI.ENS(E:E;"*31*";E:E;"*AEE*") etc...ce serait automatique et le jour où je rajoute des colonnes de stats ou des compagnies il me suffira d'étendre ma 1ère formule.
j'ai lu que la formule indirect pourrait certainement m'aider mais je n'arrive pas à m'en servir comme il faut.

deuxième problème
dans la feuille RESULTATS année, je fais la somme de toutes les statistiques depuis le début de l'expérience (26.05) jusqu'à la fin de l'année 2016, en utilisant une formule =SOMME('26.05:31.12'!G2) Je précise que 26.05 et 31.12 sont les noms de mes feuilles.
problème : on va me demander des stats sur une période bien précise par exemple du 30.05 au 07.06. Et donc si je pouvais dans ma feuille RESULTATS période avoir les mêmes formules que dans RESULTATS année, avec non pas 26.05 et 31.12, mais date 1 et date 2, et que je puisse remplir dans une autre cellule (par exemple) date 1 = 30.05 et date 2 = 07.06 pour que cela s'implémente dans toutes les formules de cette feuille ce serait super!

j'espère avoir été clair, je suis convaincu que vous avez la solution

Merci bcp de votre aide

Cyrille.

 

[vgendron]

Re : référence texte cellule dans une formule

Hello

pour ta formule en M2
=NB.SI.ENS(E:E;"*31*";E:E;"*" &L2 &"*")
et tu tires vers le bas

 

[Cyrille Aix]

Re : référence texte cellule dans une formule

super!
je suis fan, merci bcp. Et un problème en moins

 

[vgendron]

Re : référence texte cellule dans une formule

Pour l'autre pb..
je pensais que la fonction indirect irait très bien. sauf qu'apparemment. lui donner un ensemble de feuilles. ca ne lui plait pas..

avec les noms de feuilles de début et fin en J2 et K2
SOMME(INDIRECT("'" &J2 &":" &K2 &"'!G2")) ca ne MARCHE pas.

alors que juste
SOMME(INDIRECT("'" &J2 &"'!G2")) ca lui va très bien...

donc la. comme toi.. je sèche

 

[Cyrille Aix]

Re : référence texte cellule dans une formule

je te remercie d'avoir cherché en tout cas

 

[vgendron]

Re : référence texte cellule dans une formule

Bon.. je crois que la réponse est quelque part par la...
La fonction Indirect()

 

[Cyrille Aix]

Re : référence texte cellule dans une formule

bonjour,
je n'arrive vraiment pas à m'en dépatouiller de cette fontion indirect.
j'ai d'adapter la solution de vgendron avec une formule =SOMME('"&B5&":"&B6&"'!I2) oui je mets en B5 et B6 les bornes des feuilles à prendre en compte. Reponse d'Excel #REF! et il me réécrit la formule ainsi =SOMME('"&B5&":["&B6&"]"&B6&"'!I2)
je ne comprends pas pourquoi ce qui a marché pour mon problème 1 (compléter la formule avec le texte venant d'une autre cellule) ne fonction pas ici...??
je rappelle que ma formule de base est =SOMME('26.05:31.12'!I2) pour compter du 26.05 au 31.12 (chaque feuille porte le nom du jour : 26.05 puis 27.05 etc...)
HELP!

 

[BOISGONTIER]

Re : référence texte cellule dans une formule

Bonjour,

Formules Matricielles 3D

jb

 

[Cyrille Aix]

Re : référence texte cellule dans une formule

Merci BOISGONTIER
jsuis tout près du but... j'essaie de croiser les infos pour que ma colonne Noms des feuilles soit remplie automatiquement, et qu'ensuite j'aille dire à excel "fais moi les calculs de telle à telle feuille. Mais je tombe sur une incompatibilité à chaque fois

 

[vgendron]

Re : référence texte cellule dans une formule

Hello

voir PJ
PS: j'ai commencé à remplir à la main, la zone NomFeuilles (dans novuel onglet Liste Feuilles)

La formule semble fonctionner..
à adapter pour que ca suive aussi le numéro de colonne..

 

[Cyrille Aix]

Re : référence texte cellule dans une formule

Bonjour,
super ça marche!
tu penses qu'on peut automatiser le remplissage de la liste des feuilles comme dans le fichier en PJ ? en fait j'essaie de faire un mix des deux, mais c'est pas de mon niveau, je n'arrive pas à comprendre comment créer des champs etc...
on touche au but
merci merci

 

[vgendron]

Hello

pour créer un champ - Zone nommée: 2 façons
de façon "statique": tu sélectionnes la zone de ta feuille - et en haut à gauche de la barre de formule, (là où apparaissent les noms de cellules: A1 , A2 B1.....) tu tapes le nom souhaitée (sans espace ni caractère spéciaux)
ensuite, il te suffit de cliquer sur la flèche vers le bas pour dérouler la liste des zones nommées:

de façon "Dynamique": avec une formule pour que la zone s'adapte automatiquemnet à l'ajout ou suppression de lignes dans cette zone:
ouvrir le "gestionnaire de noms" ( dans le menu formules- Noms définis) et tu tapes la formule qui va bien (souvent à base de décaler)

note: les zones dynamiques n'apparaissent pas dans la liste déroulante..
les zones statiques apparaissent dans le gestionnaire de noms. tu peux donc les modifier..

 

[Cyrille Aix]

Merci,
je vais ré-essayer de faire le 2 en 1 dont je parlais

 

[Cyrille Aix]

bonjour à tous,
ça y est j'ai réussi à faire mon petit mix entre les 2 fichiers Mat3DSommeDebutFin-1.xlsx (feuille Projet3) et Somme3DDebutFin-1.xslx et je suis très content.
Le soucis, c'est que je ne comprends pas comment fonctionnent les formules utilisées et y a un petit hic dans la formule du Mat3DSommeDebutFin-1.xlsx, feuille Projet3, c'est que contrairement à ce qui est indiqué, si on choisit comme début Feuil3 et comme fin Feuil5, Excel fait la somme des cellules A2 des Feuilles 4 à 6...
J'imagine que c'est lié à la fonction DECALER dans la formule, mais ne maîtrisant pas du tout malgré mes recherches, les fonctions utilisées, je n'arrive pas à repenser la formule globale pour ne pas avoir ce décalage.
je joins les fichiers en question, dont mon résultat, si quelqu'un a une solution je suis preneur
merci et belle semaine à vous.