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

[zaza34]

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]

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() ?

 

[zaza34]

Bonsoir,

Voici un exemple allégé, sans les couleurs.

 

[Victor21]

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

 

[zaza34]

Bonsoir,

Je voudrais une formule pour que la colonne S se calcule automatiquement.

 

[job75]

Bonsoir zaza34, Patrick,

Voyez le fichier joint avec cette formule matricielle en S1 :

=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.

 

[zaza34]

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]

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.

 

[zaza34]

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,

 

[Victor21]

Bonjour,zzaz34, Gérard 🙂

Essayez en EP2:

=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]

Tout à fait Patrick.

Puisque la formule détermine la position avec COLONNE() il faut utiliser INDEX(A2: DI2; en EP2.

=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).

 

[zaza34]

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]

Maintenant si vous voulez du VBA vous pouvez utiliser cette fonction, à placer impérativement dans un module standard :

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 :

=Evolution(D2:DI2)

 

[zaza34]

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