EXCEL - Comment incrémenter une référence absolue sur des 100aines de lignes ?

[Ortho92]

[Résolu] Comment incrémenter une référence absolue sur des 100aines de lignes ?

Bonjour,

Dans le classeur Décompte il y a 2 feuilles :
- Feuil1 : liste de personnes, 1 par ligne, avec leur nom dans la colonne A (zone nommée Feuil1_Noms), donc pas de doublon,
- Feuil2 : liste de réalisations faites par chacune des personnes, dont le nom apparaît aussi dans la colonne A (zone nommée Feuil2_Noms) de cette feuille.

Dans la Feuil1, je voudrais faire apparaître en colonne D le nombre de fois que chaque personne listée dans Feuil1 apparaît dans la Feuil2.

J'ai donc écrit dans chaque cellule de Feuil1-colonne D la formule : NB.SI(Feuil2_Noms;A1)

Les cellules de la colonne A de la Feuil1 ne peuvent pas être protégées car la liste est censée pouvoir évoluer au cours du temps. Ce qui fait que tout utilisateur peut faire un tri, un copier ou un couper sur cette zone, et modifier la position relative des cellules concernées.

Exemple : je coupe A4, qui contient la valeur "nom 2", que je déplace en A9.
La cellule D4 contient la formule : =NB.SI(Feuil2_Noms;A9) et donne toujours le décompte d'apparitions de nom 2, mais la cellule A4 est vide.
La cellule D9 de Feuil1 contient la formule : =NB.SI(Feuil2_Noms;#REF!) et ne fait donc plus le total des apparitions de "nom 2" dans Feuil2.
Et les choses empirent si je trie, car alors la cellule D16 fait face à une cellule A16 vide, et contient la formule =NB.SI(Feuil2_Noms;A21), qui fait donc référence à la ligne 21 et pas la 16...

Pour y remédier, je voudrais pouvoir indiquer une référence absolue à A1 dans la formule de la cellule D1, donc sous forme $A$1, et pouvoir la recopier vers le bas en l'incrémentant, car sinon je vais devoir rentrer des 100aines de références : $A$2, $A$3, etc.

Puis je ne permettrai le tri que sur les colonnes à gauche de la D, et je forcerai ainsi chaque formule de la colonne D de donner le résultat correspondant au vrai contenu de la cellule correspondante de la colonne A.

J'ai tenté plusieurs solutions dans Feuil1 colonne D :
- =NB.SI(Feuil2_Noms;ADRESSE(1;LIGNE(D20))) - ne marche pas,
- =NB.SI(Feuil2_Noms;cellule("contenu";ADRESSE(1;LIGNE(D20)))) - Excel n'est pas content, d'ailleurs cellule reste en minuscule car erreur...

Ci-joint le classeur Décompte.xls pour visualiser ma description peut-être un peu confuse... Je travaille avec Excel 97.

Merci d'avance à qui pourra m'aider.

Cordialement.

 

[phlaurent55]

Re : EXCEL - Comment incrémenter une référence absolue sur des 100aines de lignes ?

Salut Ortho92,
je pense qu'il faudrait d'abord aller faire un petit tour du côté des cellules D9 et D16 qui semblent contenir une erreur dès le départ
ensuite si tu coupe qq chose, il vaudrait mieux couper E4:E4 au lieu de A4 seule
à te lire

 

[JeanMarie]

Re : EXCEL - Comment incrémenter une référence absolue sur des 100aines de lignes ?

Bonsoir

Fait un essai avec cette formule

=NB.SI(Feuil2_Noms;INDIRECT(ADRESSE(1;lgne())))

@+Jean-Marie

 

[JHA]

Re : EXCEL - Comment incrémenter une référence absolue sur des 100aines de lignes ?

Bonsoir ortho92, Jean-Marie, le forum

Je me permet une petite modif sur la formule de Jean-Marie

=NB.SI(Feuil2_Noms;INDIRECT(ADRESSE(LIGNE();1)))
ainsi, tu peux couper la cellule en colonne A et la deplacer.

JHA

 

[Ortho92]

Re : EXCEL - Comment incrémenter une référence absolue sur des 100aines de lignes ?

Bonjour,

Un grand merci pour votre aide, je ne connaissais pas l'existence de la fonction INDIRECT().

Dans l'aide d'Excel 97, elle n'est pas proposée dans le lien Voir aussi, et dans la liste Fonctions de recherche et de références le texte Renvoie une référence indiquée par une valeur de texte ne me "parlait" pas beaucoup...

Juste pour le plaisir de comprendre : dans =NB.SI(Feuil2_Noms;cellule("contenu";ADRESSE(1;LIG NE(D20)))) pourquoi la fonction CELLULE() ne fonctionne-t-elle pas ?

Cordialement.