Bonjour,
Sous Excel j’ai une fonction personnalisée qui me permet de récupérer le nombre de cellules composant la dernière séquence sur la ligne en fonction d’une condition.
ex: x x x z z d d d d d x x x x x x
pour x : le résultat sera 6
pour d : le résultat sera 5
pour z : le résultat sera 2
Ma fonction sous Excel est :
Function SousTerre(L As Integer) As Single
Application.Volatile
x = Cells(3, 3).Value
Dim c1%, c2 As Integer
For c1 = x To 20 Step -1
If Cells(L, c1) = "BR" Or Cells(L, c1) = "ST" Then Exit For
Next
For c2 = c1 To 20 Step -1
If Cells(L, c2) <> "BR" And Cells(L, c2) <> "ST" Then Exit For
Next
SousTerre = IIf(c2 = c1, 0, (c1 - c2) )
End Function
Ma question, comment traduire cela pour Apps script
Merci
Bonjour,J’avance un peu, j’ai créé ce code en m’inspirant de celui fait en VBA mais je n’ai rien qui s’affiche.
function sousterre(){
// définir les variables
var ss = SpreadsheetApp.getActiveSpreadsheet()
var C1;
var C2;
// trouver le premier « ST » en partant de la fin de la ligne - comptage de droite à gauche
for(C1=1;C1>30;C1–){
ss.getRange(row,C1)=« ST »;
// trouver la première occurence différente en partant de C1 - comptage de droite à gauche
for(C2=1;C2<C1;C2–){
ss.getRange(row,C2)!= « ST »;
// pour afficher le résultat
sousterre = If(C2=C1,0,(C1-C2))
}
}
}
J’ai peut-être raté une subtilité, mais je ne crois pas que de l’Apps Script soit nécessaire (ou du VBA au demeurant) ?
S’il faut simplement compter sur une plage de données le nombre de fois qu’une valeur particulière apparaît, la fonction NB.SI (ou COUNTIF) fait très bien le travail. Par exemple : =NB.SI(I19:T19;« GE ») donnera le nombre de « GE » sur la ligne 19 de la colonne I à la colonne T.
Bonjour,
Oui NB.SI me compte le nombre total de la valeur cherchée, mais ce n’est pas ce que je recherche.
Si la séquence est coupée par une autre, alors il ne faut compter que la dernière sur la ligne en sachant que le nombre de colonne augmente en fonction des nouvelles séquences.
Bonjour,
j’ai enfin trouvé mais avec de l’aide.
Voici le code :
function sousterre(){
// définir les variables
var ss = SpreadsheetApp.getActiveSheet()
var n= ss.getLastColumn();
for(var C1=n;C1>=1;C1–) { // boucle de n à 1
if(ss.getRange(ss.getCurrentCell().getRow(),C1).getValue() == « ST »){
// on a trouvé la cellule qui contient ST
for(var C2=C1;C2>=1;C2–){
if(ss.getRange(ss.getCurrentCell().getRow(),C2).getValue() !=« ST »){
return C1-C2;
}
}
}
}
}
Ah, je vois ! Effectivement, j’avais raté un morceau
Bravo d’avoir réussi à le faire en Apps Script ! Il est aussi possible de le faire purement en Sheets par ailleurs, même si la formule est un peu dense :
=REDUCE(0;BYCOL(I6:AE6;LAMBDA(cellule;INDEX(I6:AE6;1;COLONNES(I6:AE6)+COLONNE(I6:AE6)-COLONNE(cellule))));LAMBDA(acc;curr;SI(ET(curr=« Di »;acc<=0);acc-1;ABS(acc))))
Elle utilise doublement les nouvelles fonctions LAMBDA, donc c’est un peu dur à lire, mais dans l’idée :
BYCOL(…) : Le but ici est de retourner notre ligne pour qu’elle soit lue dans l’autre sens par REDUCE. Basiquement, on prend chaque colonne de notre ligne, et pour chacune, on prend à la place grâce à INDEX la valeur en partant de l’autre bout.
REDUCE(…) : Permet de réduire notre tableau à une seule valeur. La façon dont ça marche : On part de 0. Si la cellule en cours vaut Di et que l’accumulateur est inférieur ou égal à 0, on réduit de 1, sinon on prend la valeur absolue. Du coup, tant qu’on ne trouve pas la valeur de Di, on reste à 0. Dès qu’on trouve une séquence de Di, on mesure sa longueur (de manière négative), et dès qu’on sort de la séquence, on passe la longueur en positif, ce qui empêche les prochaines séquences d’être comptabilisées. Et vu qu’on commençait par la droite, on obtient bien la longueur de la séquence de Di la plus à droite !
Je tenais à passer cette solution qui peut aussi être intéressante et éviter de passer par Apps Script (malgré mon amour pour le code) !
Bonjour,
Très belle formule et surtout avec un très gros avantage, aucun temps de latence pour mise à jour du résultat contrairement au script.
De mon coté j’ai également trouvé une formule qui arrive au même résultat mais qui se base sur les numéros de colonne.
En premier je recherche la colonne contenant la dernière occurrence (ex: ST).
=ArrayFormula(MAX((H6:6=« ST »)*COLONNE(H6:6)))
Puis une seconde qui recherche la première occurrence mais en partant du n° de colonne du résultat précédemment.
=ArrayFormula(MAX(SI(DECALER($H6;0;0;1;MAX((H6:6=« ST »)*COLONNE(H6:6))-9)<>« ST »;COLONNE(DECALER($H6;0;0;1;MAX((H6:6=« ST »)*COLONNE(H6:6))-9)))))))
Reste à faire une soustraction pour avoir le nombre de cellule.
=ArrayFormula(SI(MAX((H6:6=« ST »)*COLONNE(H6:6))=0;0;MAX((H6:6=« ST »)*COLONNE(H6:6))-MAX(SI(DECALER($H6;0;0;1;MAX((H6:6=« ST »)*COLONNE(H6:6))-9)<>« ST »;COLONNE(DECALER($H6;0;0;1;MAX((H6:6=« ST »)*COLONNE(H6:6))-9))))))
