Office 365 Choisir les colonnes les plus récentes pour effectuer un taux d'évolution entre 2 dates

zaza34

XLDnaute Nouveau
Bonjour à tous,

Nouveau sur ce forum, je vais bientôt être concerné par un plan social dans mon entreprise et me retrouver à chercher du boulot dans la région montpelliéraine.
J'utilise un fichier perso que j'aimerais améliorer en l'automatisant davantage et vous solliciterai pour ce faire.

Chaque ligne de ce fichier correspond à une société, chaque colonne à une date. Les cellules sont la plupart du temps vierges, sauf quand je rentre une valeur chiffrée, mais la colonne n'est alors jamais pleine complètement. Pour que les données les plus récentes apparaissent à l'ouverture, j'incrémente le tableau en annotant les données les plus récentes à gauche.

Je souhaiterais enregistrer une formule qui prendrait uniquement les 2 dernières colonnes remplies pour chaque ligne concernée, donc sur une autre colonne rentrer cette formule qui concernerait les 2 valeurs les plus à gauche du tableau.
Jusqu'ici, je rentrais manuellement les opérations suivantes: =(SOMME([@[14/8/19]])-SOMME([@[5/8/19]]))/SOMME([@[5/8/19]]) par exemple.
Je pensais utiliser le code couleur ou plutôt son absence (-4142) pour différencier des données que je remplis systématiquement de 6 couleurs différentes: 12, 3, 43, 44, 14 et 6.

Merci pour votre aide et bon courage pour ceux d'entre vous qui reprennent.
 

Victor21

XLDnaute Barbatruc
Supporter XLD
Bonsoir, zaza34.

Un exemple allégé -et anonymisé- de votre fichier Excel nous aiderait à visualiser l'organisation, et à tester d'éventuelles propositions. Peut-être avec décaler() ?
 

Victor21

XLDnaute Barbatruc
Supporter XLD
Re,

Superbe exemple qui nous aide à comprendre ce que vous voulez faire...
Les deux colonnes les plus à gauche renseignées dans votre exemple sont les colonnes C et D.
Prenez ces deux colonnes.
Pour en faire quoi ? vous ne le dites pas :(
Pour mettre où ? vous ne dites pas :(
A tout hasard en E1 : =SI(($C1*$D1)>0;($C1-$D1)/$D1;"") à recopier vers le bas
 

job75

XLDnaute Barbatruc
Bonsoir zaza34, Patrick,

Voyez le fichier joint avec cette formule matricielle en S1 :
Code:
=SIERREUR(INDEX(A1:R1;PETITE.VALEUR(SI(ESTNUM(A1:R1);COLONNE(A1:R1));1))/INDEX(A1:R1;PETITE.VALEUR(SI(ESTNUM(A1:R1);COLONNE(A1:R1));2))-1;"")
Bonne nuit.
 

Fichiers joints

Dernière édition:

zaza34

XLDnaute Nouveau
Bonjour job75,

Merci beaucoup pour votre aide.
Malheureusement, mes données sont sous forme de tableau et lorsque je veux valider la formule, j'ai une invite qui m'affiche: "Un tableau ne peut pas contenir de formules matricielles à plusieurs cellules."
J'aurais dû vous donner un exemple plus parlant qui faisait apparaître ce qui pour moi était un détail.
Auriez-vous une idée pour contourner ce problème ?

Cordialement,
 

job75

XLDnaute Barbatruc
Avec un tableau Excel structuré les formules matricielles vont très bien, voyez ce fichier (2).

Le problème vient du fait que vous avez essayé de valider en bloc une plage de plusieurs cellules.

Il faut valider par Ctrl+Maj+Entrée uniquement la cellule S2, la formule se recopie automatiquement vers le bas.
 

Fichiers joints

zaza34

XLDnaute Nouveau
Merci job75,
Je vous joins le fichier qui me donne des résultats erronés, je ne sais pour quelle raison.
Pourriez-vous jeter un oeil sur celui-ci ?
Cordialement,
 

Fichiers joints

Victor21

XLDnaute Barbatruc
Supporter XLD
Bonjour,zzaz34, Gérard :)

Essayez en EP2:
VB:
=SIERREUR((INDEX(D2:DI2;PETITE.VALEUR(SI(ESTNUM(D2:DI2);COLONNE(A2:DF2));1))-INDEX(D2:DI2;PETITE.VALEUR(SI(ESTNUM(D2:DI2);COLONNE(A2:DF2));2)))/INDEX(D2:DI2;PETITE.VALEUR(SI(ESTNUM(D2:DI2);COLONNE(A2:DF2));2));"")
*COLONNE(A2: DF2) n'est pas une zone, mais un numéro d'ordre devant commencer par 1 (A2:A2), et contenir autant de membres qu'en contiennent les zones.
 

job75

XLDnaute Barbatruc
Tout à fait Patrick.

Puisque la formule détermine la position avec COLONNE() il faut utiliser INDEX(A2: DI2; en EP2.
Code:
=SIERREUR(INDEX(A2:DI2;PETITE.VALEUR(SI(ESTNUM(D2:DI2);COLONNE(D2:DI2));1))/INDEX(A2:DI2;PETITE.VALEUR(SI(ESTNUM(D2:DI2);COLONNE(D2:DI2));2))-1;"")
Fichier (3).
 

Fichiers joints

zaza34

XLDnaute Nouveau
Gérard et Patrick,
Merci beaucoup pour m'avoir apporté la solution à ce problème, en un temps record qui plus est.
Bonne journée à tous,
Xavier
 

job75

XLDnaute Barbatruc
Maintenant si vous voulez du VBA vous pouvez utiliser cette fonction, à placer impérativement dans un module standard :
VB:
Function Evolution(r As Range)
If Application.Count(r) < 2 Then Evolution = "": Exit Function
Dim a(1), n As Byte
For Each r In r
    If IsNumeric(CStr(r)) Then a(n) = r: n = n + 1: If n = 2 Then Exit For
Next
Evolution = a(0) / a(1) - 1
End Function
Formule très simple en EP2 du fichier .xlsm joint :
Code:
=Evolution(D2:DI2)
 

Fichiers joints

zaza34

XLDnaute Nouveau
Remarquable, mais là vous dépassez largement mon seuil de compétence et de compréhension ;)
Je vais m'en tenir à la version Excel qui me sera peut-être plus facile à transposer au cas où je modifierais les cellules concernées à l'avenir.
Merci encore
 

Haut Bas