ARRAYFORMULA et CUMUL

 ARRAYFORMULA et CUMUL

Prenons le cas d'une colonne débit ou d'une colonne crédit, pour sommer la pratique simple est d'effectuer une opération de type nouvelle valeur = valeur précédente + valeur courante

D2=D1+C2 

Or cette façon de faire suppose de tirer la formule vers le bas et ne permet pas d'appliquer une fois pour toutes la fonction globale ARRAYFORMULA.

Il faut pour cela utiliser une multiplication matricielle dont le principe est le suivant :


Il est important de bien respecter le nombre de colonnes de la première matrice et le nombre de lignes de la seconde. Une multiplication d'une matrice de 5x3 (lignes x colonnes) avec une matrice de 3x2 donnera une matrice de 5x2.

Exemple simplifié : 3x3 multipliée par 3x2 donnera donc une matrice de 3 lignes et 2 colonnes

La première matrice est constituée de 1 et 0, les 1 occupant le triangle diagonal inférieur. Prenons le dernier terme, il revient à additionner tous les termes de la seconde matrice.

En remontant les résultats, on trouvera tout simplement le cumul des valeurs.

La difficulté réside donc dans la construction de la première matrice. Celle-ci s'écrira

=arrayformula(1*(transpose(row(A2:A)) <= row(A2:A)))

et le résultat du cumul de valeurs situées colonne C (les espaces sont remplacés par des 0)

=arrayformula(mmult(1*(transpose(row(A2:A))<=row(A2:A));if(C2:C="";0;C2:C)))

à inscrire en D2

Commentaires

Enregistrer un commentaire

Posts les plus consultés de ce blog

INSÉRER UN TABLEAU PROVENANT D'UNE FEUILLE DANS UN COURRIEL

Image
Lors d'une envoi d'une partie d'informations par courrier électronique, la solution est de la faire en fichier attaché au format pdf par exemple. Une autre solution plus conviviale est d'inclure dans le corps du courriel le tableau de données. Le script suivant respecte les couleurs, polices, tailles de caractères et cellules fusionnées comme dans l'exemple joint. function   sendEmail () {    var   sh  =  SpreadsheetApp . getActiveSpreadsheet (). getSheetByName ( feuille )    var   plage  =  sh . getDataRange ()    MailApp . sendEmail ({      to :  'nom@email.com' ,      subject :  'Titre du courriel' ,      htmlBody :  tableHTMLplus ( plage )   }) }; function   tableHTMLplus ( range ){    var   data       =  range . getDisplayValues ()    var   size ...
Lire la suite

NAVIGUER AU SEIN D'UN FICHIER À L'AIDE D'UN MENU DÉROULANT

Image
Il peut être intéressant de créer un sommaire et de pouvoir aller directement à un endroit précis du fichier par l'utilisation d'un menu déroulant La solution consiste à  créer une table de choix (ici en colonnes D, E et F) avec l'identifiant de la feuille cible et la cellule cible définir une validation pour la cellule de choix (ici A1) définir par formule le lien vers la cellule cible = iferror ( hyperlink ( "#gid=" & vlookup ( A1 ; D:F ; 2 ; 0 ) & "range=" & vlookup ( A1 ; D:F ; 3 ; 0 ) ; "link to " & vlookup ( A1 ; D:F ; 3 ; 0 ) ) ) Cette solution peut aussi être utilisée pour créer un sommaire des différentes feuilles du fichier. Dans ce cas, la liste peut être créée par une fonction personnalisée = listOfTabs ( ) Le script étant function   listOfTabs (){    var   ss  =  SpreadsheetApp . getActiveSpreadsheet ()    var   list  = [[ 'Choix' , 'gid' , 'cellule' ]]    ss . getSheets (). forEach ( sh ...
Lire la suite

METTRE À DISPOSITION DES INFORMATIONS SANS ACCÈS AU FICHIER COMPLET

Même si par essence, Google Sheets est un outil collaboratif, il peut arriver qu'il faille mettre à disposition des informations sans que l'on puisse avoir accès aux éléments confidentiels contenus dans le fichier complet. Créer une vue filtrée, ou créer un fichier avec =IMPORTRANGE() n'est pas la solution car elle permettrait d'avoir accès aux informations. Ceci peut se faire selon 2 méthodes asynchrones : recopier une feuille dans un nouveau fichier qui lui seul sera accessible envoyer par mail les informations issues de la feuille Pour ce faire, les informations à recopier ou envoyer seront isolées dans une feuille spécifique avec des formules classiques telles que query, filter, vlookup/recherchev ... à partie des données complètes. var   feuille  =  'nom de la feuille à copier/envoyer' var   id  =  'id du fichier'       // pour le recopie : id du fichier...
Lire la suite