Trouver derniere (premiere) cellule dans une plage

[SAMWRC]

Bonjour le forum,

Mon problème est le suivant : comme vous pouvez le contacter dans mon fichier en pièce jointe, j'ai plusieurs plages de valeurs les unes au-dessus des autres. A chaque plage de valeurs est associée une somme pour la colonne C (cellule rouge et cellule bleue). Je fais appel au résultat de cette somme dans la colonne B.

Etant donne que je vais utiliser cette partie de fichier a beaucoup plus grande échelle, je voudrais appeler automatiquement en colonne B la case dans laquelle se fait la somme. Je pense que l'on peut faire cela avec equiv et index, mais je sèche complètement.

Pour faire simple au lieu d'avoir B5 = A5 + $C$1, je voudrais avoir B5 = A5 + "Formule magique qui me renvoie la valeur de C1".

Merci d'avance pour votre aide et bonne soirée

 

[phlaurent55]

Re : Trouver derniere (premiere) cellule dans une plage

Bonjour Sam,

tu pourrais remplacer B2 = A2 + $C$1
par
B2=A2+SOMME($C$2:$C$6)
et étendre la formule vers le bas

à+
Philippe

 

[SAMWRC]

Re : Trouver derniere (premiere) cellule dans une plage

Bonjour Philippe,

Le probleme, c'est que je ne serai pas l'utilisateur de ce fichier et les personnes qui seront amenees a l'utiliser vont ajouter beaucoup de lignes. Ces lignes seront sans doute ajoutees a la fin, ce qui faussera la formule de la somme que tu proposes.

Merci quand meme pour ton aide

 

[ROGER2327]

Re : Trouver derniere (premiere) cellule dans une plage

Bonjour SAMWRC, phlaurent55

Peut-être ceci en B2 (formule matricielle à valider comme il se doit) :

[COLOR="DarkSlateGray"][B]=SI(A2="";"";A2+INDIRECT("C"&MAX((B$1:B1="")*LIGNE($1:1))))[/B][/COLOR]

puis recopier vers le bas autant que de besoin.​

ROGER2327
#4124

Samedi 7 Absolu 138 (Saint Alambic, abstracteur, SQ)
28 Fructidor An CCXVIII
2010-W37-2T21:51:56Z

 

[ROGER2327]

Re : Trouver derniere (premiere) cellule dans une plage

Suite...

Ou, plus simple :

[COLOR="DarkSlateGray"][B]=SI(A2="";"";SI(ESTNUM(B1);B1-A1;C1)+A2)[/B][/COLOR]

en B2, à recopier vers le bas.​

ROGER2327
#4125

Samedi 7 Absolu 138 (Saint Alambic, abstracteur, SQ)
28 Fructidor An CCXVIII
2010-W37-2T23:08:14Z

 

[SAMWRC]

Re : Trouver derniere (premiere) cellule dans une plage

Bonjour Roger,

Tres élégante solution qui fonctionne parfaitement.

Cependant ... j'avais simplifie l'exemple car je m'attendais a ce vous trouviez une solution générale avec equiv et index ...

Du coup, je vous renvoie une PJ avec mon cas particulier.

Je souhaite donc avoir une formule en colonne A pour laquelle je n'aurais pas besoin de modifier l'adresse de la somme a utiliser en colonne B, a chaque fois que je copie mon bloc de cellules.

J'aimerais également avoir une formule pour la somme en colonne B (cellules rouge et bleue) afin que la somme détermine automatiquement les cellules a additionner sans me soucier du fait que j'ajoute ou je supprime des lignes. La plage de cellule a additionner étant délimitée par la cellule de la somme elle-même et la prochaine case vide.

Merci pour votre aide

Sam

 

[ROGER2327]

Re : Trouver derniere (premiere) cellule dans une plage

Re...

C'est un tout autre problème... ...qui fait reprendre le boulot à zéro !

En clair, j'ai passé pas mal de temps à résoudre un problème qui ne se posait pas...
Pas grave, le temps des bénévoles ne coûte pas cher.​

Bon, assez plaisanté, revenons aux choses sérieuses.
Je propose donc :

1. En A2 :

   [COLOR="DarkSlateGray"][B]=SI(B1="";"";SI(ESTNUM(B2);SI(2*B2>INDIRECT("L"&MAX((A$1:A1="")*LIGNE($1:1))&"C(1)";0);INDIRECT("L"&MAX((A$1:A1="")*LIGNE($1:1))&"C(1)";0)-B2;B2);""))[/B][/COLOR]

   (À recopier ensuite sur les lignes suivantes.)
   
   
2. En B2 :

   [COLOR="DarkSlateGray"][B]=SOMME(INDIRECT("L"&LIGNE(3:3)&"C"&COLONNE(B:B)&":L"&MIN(SI(B3:B$201="";LIGNE(2:$200);99999))&"C"&COLONNE(B:B);0))[/B][/COLOR]

Formules toutes deux hautement matricielles à valider en conséquence.

J'espère que d'autres s'y colleront, car tout cela me semble bien compliqué, et la référence circulaire guette au coin du bois...
Les vrais formulistes trouveront peut-être des choses moins alambiquées et moins risquées.​

ROGER2327
#4132

Dimanche 8 Absolu 138 (Absinthe, ci devant Saint Alfred, ST)
29 Fructidor An CCXVIII
2010-W37-3T21:27:39Z

 

[SAMWRC]

Re : Trouver derniere (premiere) cellule dans une plage

Merci beaucoup Roger et désolé pour la mauvaise explication initiale.

J'ai teste les formules, elles marchent au poil.
Cependant, si tout fonctionne parfaitement sur mon ordinateur équipé d'Excel 2007 en français, lorsque j'ouvre le même fichier (qui fonctionne donc parfaitement) avec mon second ordinateur équipé d'Excel 2003 en anglais, ça ne marche plus ...

Les fonctions sont pourtant bien traduites. Je me disais que cela était peut être du aux "L" que j’interprète (sans doute naïvement) comme la première lettre du mot ligne. Mais en remplaçant toutes les occurrences de "L" par "R" pour row, cela ne marche toujours pas ...

Sachant que tout fichier se transfert d'ordinaire très bien entre mes deux ordinateurs, je ne sais pas quoi faire ...

Encore un éclair de génie ?

Merci beaucoup

Sam

 

[ROGER2327]

Re : Trouver derniere (premiere) cellule dans une plage

Re...

Pour un fois, je propose une solution non testée.
Car ne possédant pas de version étasunienne d'Excel, je ne peux tester...

Exécutez le code ci-dessous dans votre version étasunienne : je pense qu'il vous donnera la version en Anglais des formules en A2 et B2.
Merci de me tenir au courant du résultat.

[COLOR="DarkSlateGray"][B]Sub toto()
   [B3].Value = 0
   [A2].FormulaArray = "=IF(R[-1]C[1]="""","""",IF(ISNUMBER(RC[1]),IF(2*RC[1]>INDIRECT(""R""&MAX((R1C:R[-1]C="""")*ROW(R1:R[-1]))&""C(1)"",0),INDIRECT(""R""&MAX((R1C:R[-1]C="""")*ROW(R1:R[-1]))&""C(1)"",0)-RC[1],RC[1]),""""))"
   [B2].FormulaArray = "=SUM(INDIRECT(""R""&ROW(R[1])&""C""&COLUMN(C)&"":R""&MIN(IF(R[1]C:R201C="""",ROW(R:R200),99999))&""C""&COLUMN(C),0))"
End Sub[/B][/COLOR]

ROGER2327
#4137

Lundi 9 Absolu 138 (Descente du Saint Esprit de Vin, SQ)
30 Fructidor An CCXVIII
2010-W37-4T20:47:03Z

 

[SAMWRC]

Re : Trouver derniere (premiere) cellule dans une plage

Roger, j'ai teste votre solution et le résultat est très surprenant ...

La formule en B2 marche parfaitement alors qu'il n'y a aucune différence avec la formule française traduite ...
Et la formule en A2 ne marche pas ...

J'avoue ne pas très bien comprendre.

Merci encore

 

[Staple1600]

Re : Trouver derniere (premiere) cellule dans une plage

Bonsoir à tous

Pour infos
Pour afficher une formule dans son équivalent anglais

Sub Formule_EN_Anglais()     
MsgBox ActiveCell.Formula 
End Sub

 

[ROGER2327]

Re : Trouver derniere (premiere) cellule dans une plage

Bonjour Staple1600

Bonsoir à tous

Pour infos
Pour afficher une formule dans son équivalent anglais

Sub Formule_EN_Anglais()     
MsgBox ActiveCell.Formula 
End Sub

Ça, c'est ce qu'on raconte à la veillée dans les chaumières pour émerveiller les petits enfants. N'étant plus un petit enfant, ce genre de faribole ne m'émerveille plus car je sais que la transcription étasunienne de =INDIRECT("LC("&D1&")";0) n'est pas =INDIRECT("LC("&D1&")",0), quoi qu'en pense Mademoiselle Formula.
Pour traduire les formules de notre ami, il faudra un autre outillage.

Ceci dit, le recours à la propriété Formula peut quelquefois être utile : par exemple il est parfaitement exact que la transcription de =A1+B1 est =A1+B1.

Je plaisante, bien sûr...​

ROGER2327
#4138

Mardi 10 Absolu 138 (Dilution, V)
1er Sanculottide An CCXVIII
2010-W37-5T01:13:38Z

 

[ROGER2327]

Re : Trouver derniere (premiere) cellule dans une plage

Re...

Roger, j'ai teste votre solution et le résultat est très surprenant ...

La formule en B2 marche parfaitement alors qu'il n'y a aucune différence avec la formule française traduite ...
Et la formule en A2 ne marche pas ...

J'avoue ne pas très bien comprendre.

Merci encore

Il y a quelques petites différences ici ou là, si on regarde de près.
Pour la formule en A2 je propose ceci (corrections en rouge) :

[COLOR="DarkSlateGray"][B]
   [A2].FormulaArray = "=IF(R[-1]C[1]="""","""",IF(ISNUMBER(RC[1]),IF(2*RC[1]>INDIRECT(""R""&MAX((R1C:R[-1]C="""")*ROW(R1:R[-1]))&""C[COLOR="Red"][[/COLOR]1[COLOR="Red"]][/COLOR]"",0),INDIRECT(""R""&MAX((R1C:R[-1]C="""")*ROW(R1:R[-1]))&""C[COLOR="Red"][[/COLOR]1[COLOR="Red"]][/COLOR]"",0)-RC[1],RC[1]),""""))"
[/B][/COLOR]

Mais j'insiste, je ne peux rien vérifier, ne disposant pas d'une version étasunienne du logiciel. Il se peut que l'erreur soit ailleurs. Bon courage !​

ROGER2327
#4139

Mardi 10 Absolu 138 (Dilution, V)
1er Sanculottide An CCXVIII
2010-W37-5T01:32:33Z

 

[SAMWRC]

Re : Trouver derniere (premiere) cellule dans une plage

Roger,

Cela fonctionne parfaitement désormais !
Merci beaucoup pour votre aide.

Bonne journée

PS : je suis tombe sur ce fil du forum https://www.excel-downloads.com/threads/trouver-la-premiere-et-derniere-valeur-non-nulle.6703/
Je pense qu'il y a moyen d'en tirer quelque chose pour mon cas précis, mais je ne suis pas arrive bien loin.

 

[ROGER2327]

Re : Trouver derniere (premiere) cellule dans une plage

Re...

Roger,

Cela fonctionne parfaitement désormais !
Merci beaucoup pour votre aide.

Bonne journée

PS : je suis tombe sur ce fil du forum https://www.excel-downloads.com/threads/trouver-la-premiere-et-derniere-valeur-non-nulle.6703/
Je pense qu'il y a moyen d'en tirer quelque chose pour mon cas précis, mais je ne suis pas arrive bien loin.

Parfait !

Je me demande cependant si, une fois votre classeur adapté pour l'Anglais, il fonctionne encore lorsque vous l'ouvrez sur un logiciel en Français.
Si vous faites l'expérience, je serai intéressé par le résultat...
Bonne continuation.​

ROGER2327
#4143

Mardi 10 Absolu 138 (Dilution, V)
1er Sanculottide An CCXVIII
2010-W37-5T23:07:21Z