Avez-vous déjà eu un ensemble de données où la ligne des en-têtes contient des dates en tant que libellés de champ (nom de colonne) ? Vous vous demandez peut-être comment calculer la somme des n derniers jours à partir d’aujourd’hui pour chaque ligne dans Google Sheets.
Si vous êtes familier avec les fonctions SOMME.SI et AUJOURDHUI, vous pourrez peut-être calculer la somme des n derniers jours pour chaque ligne dans Google Sheets.
Lorsque vous utilisez une formule de ce type pour calculer la somme des 7, 30 et 60 derniers jours, vous devrez peut-être la copier dans les lignes suivantes car elle ne se développera pas automatiquement.
Pour comprendre les données et le résultat attendu, passons rapidement à un exemple.
Formule glissante pour calculer la somme des 7, 30 et 60 derniers jours à partir d’aujourd’hui
La formule SOMME.SI suivante dans la cellule A3 calcule la somme des 7 derniers jours pour la ligne 3, c’est-à-dire la plage D3:3. Comme je l’ai mentionné dans le premier paragraphe, les dates sont dans la colonne D2:D, c’est-à-dire dans la ligne des en-têtes.
=somme.si(D3:3,$D$2:$2;">"&AUJOURDHUI()-7,$D$2:$2;"<="&AUJOURDHUI())
Note : Pour générer les dates dans la plage D2:M2 qui conviennent à la plage de dates à tester, vous pouvez utiliser la formule =séquence(1,10;AUJOURDHUI()-9) dans D2 (première cellule vide de D2:M2). Ensuite, sélectionnez D2:M2 et formatez-les (menu) en Nombre > Date.
Cette formule SOMME.SI nécessite une action manuelle pour remplir le résultat dans les lignes suivantes. Cela signifie que vous devez faire glisser la poignée de recopie vers le bas de la cellule A2.
Vous pouvez utiliser la même formule SOMME.SI pour obtenir la somme des 7, 30 et 60 derniers jours à partir d’aujourd’hui dans Google Sheets. Comment ?
J’ai utilisé -7 dans la formule pour obtenir la somme des 7 derniers jours à partir d’aujourd’hui. Remplacez simplement ce nombre par -30, -60 ou -90 selon votre besoin.
La fonction SOMME.SI n’est pas la seule formule que nous pouvons utiliser dans Google Sheets à cette fin. Nous pouvons également utiliser la combinaison SOMME et FILTRE comme suit.
La combinaison suivante SOMME + FILTRE concerne la cellule A2. C’est à nouveau une formule glissante.
=somme(filtre(D3:3,$D$2:$2>AUJOURDHUI()-7,$D$2:$2<=AUJOURDHUI()))
Mais l’un des obstacles auxquels vous pourriez être confronté dans le scénario ci-dessus sera d’étendre automatiquement la formule d’une ligne aux lignes suivantes. J’ai aussi une solution pour cela.
Formule matricielle pour obtenir la somme des 7, 30 et 60 derniers jours pour chaque ligne dans Google Sheets
Certaines formules Google Sheets ne se développeront pas. SOMME.SI en est une parmi elles. La combinaison FILTRE et SOMME ci-dessus est un autre exemple.
Alors quelles sont les options disponibles pour totaliser les valeurs des 7/30/60/90 derniers jours pour chaque ligne dans Google Sheets ?
Les alternatives qui me viennent à l’esprit sont MMULT et DSOMME. Ici, nous utiliserons ce dernier.
Mais cela ne suffit pas. Avec DSOMME, nous devrons également utiliser d’autres fonctions. Voici cette formule puissante !
Videz la plage A3:A et insérez ma DSOMME suivante dans A3.
=formule.matricielle(dsomme(transpose({C2:C7;filtre({colonne(D2:M2)*0;D3:M7};D2:M2>AUJOURDHUI()-7;D2:M2<=AUJOURDHUI())});séquence(lignes(C3:C7);1;2);{"Article";0}))
En utilisant la formule matricielle ci-dessus, vous pouvez obtenir la somme des 7, 30, 60 et 90 derniers jours pour chaque ligne dans Google Sheets. Vous devez remplacer -7 par le nombre de jours requis.
La formule ci-dessus ne couvre que des colonnes spécifiques. Dans la dernière partie de ce tutoriel, je vous aiderai à l’étendre à l’ensemble de la colonne de la feuille.
Logique et explication de la formule
Syntaxe : DSOMME(base de données, champ, critères)
Dans notre DSOMME ci-dessus, nous avons utilisé des combinaisons de formules (expressions) comme base de données, champ et critères.
Base de données (plage filtrée transposée)
Le FILTRE suivant dans la formule ci-dessus filtre les colonnes qui se trouvent dans la plage de dates des 7, 30, 60, 90 ou n derniers jours que vous spécifiez. Il agit (partiellement) comme base de données dans DSOMME.
= {C2:C7;filtre({colonne(D2:M2)*0;D3:M7};D2:M2>AUJOURDHUI()-7;D2:M2<=AUJOURDHUI())}
Le filtre de la plage D3:M7 qui se trouve dans la plage spécifiée des dates.
La formule colonne(D2:M2)*0 (surlignée en cyan) insère une ligne en haut du résultat contenant la valeur 0. En plus de cela, j’ai ajouté la plage de colonnes C2:C7 (surlignée en jaune).
La ligne et la colonne ajoutées ci-dessus sont nécessaires dans DSOMME pour les utiliser comme argument de fonction champ et critères. Vous le comprendrez plus tard.
Transposez le résultat filtré ci-dessus, et c’est la base de données (adéquate) à utiliser dans DSOMME.
=transpose({C2:C7;filtre({colonne(D2:M2)*0;D3:M7};D2:M2>AUJOURDHUI()-7;D2:M2<=AUJOURDHUI())})
Base de données :
Logique :
Nous avons transposé les données filtrées car DSOMME est uniquement capable de retourner des totaux de colonnes sous forme de tableau. Nous voulons le total par ligne en tant qu’array.
Donc, pour calculer la somme des 7, 60 et 90 derniers jours pour chaque ligne, nous avons transposé les données filtrées. Ensuite, nous calculerons le total par colonne, qui sera égal à la somme par ligne.
Dans mon tutoriel « Comment utiliser la fonction SOMME.SI horizontalement dans Google Sheets », j’ai discuté de la même chose sous le sous-titre « Formule matricielle alternative (formule DSOMME) ».
Champ (numéros séquentiels)
Si vous vérifiez la base de données ci-dessus, vous pouvez comprendre que nous avons besoin du total des colonnes « A », « B », « C », « D » et « E », et ce sont les colonnes de la colonne n° 2 à 6 de la base de données.
Donc, les champs que nous devons utiliser sont le tableau {2;3;4;5;6}. La SEQUENCE suivante fait cette partie.
=SEQUENCE(lignes(C3:C7);1;2)
Critères (libellé de champ et condition)
Dans DSOMME ou d’autres fonctions de base de données similaires, la première colonne sera la colonne de critères.
Selon notre base de données, le libellé de champ de la colonne de critères est « Article », et le critère est 0 car il n’y a pas d’autre valeur dans cette colonne.
{"Article";0}
J’espère que vous avez pu comprendre comment la formule calcule la somme des 7, 30 et 60 derniers jours pour chaque ligne dans Google Sheets.
Total des derniers n jours pour chaque ligne à partir d’aujourd’hui et colonnes infinies
Le but de la formule est de totaliser les 7, 30, 60, 90 derniers jours en fonction des dates horizontales.
Donc, vous pouvez avoir plus de dates dans la ligne d’en-têtes à l’avenir, ce qui étendra la plage. Pour l’instant, cela va jusqu’à la colonne M.
Nous pouvons modifier soigneusement la formule pour inclure des colonnes et des lignes infinies jusqu’à 1000 comme suit.
=formule.matricielle(dsomme(transpose({C2:C1000;filtre({colonne(D2:2)*0;D3:1000};D2:2>AUJOURDHUI()-7;D2:2<=AUJOURDHUI())});séquence(lignes(C3:C1000);1;2);{"Article";0}))
Voici les changements que j’ai apportés :
- C2:C7 est devenu C2:C1000.
- D2:M2 est devenu D2:2.
- D3:M7 est devenu D3:1000.
La formule ci-dessus laissera des valeurs 0 dans les lignes vides de la colonne A en tant que trace. Pour éviter cela, nous pouvons inclure un test SI comme suit.
=formule.matricielle(si(C3:C="";;dsomme(transpose({C2:C1000;filtre({colonne(D2:2)*0;D3:1000};D2:2>AUJOURDHUI()-7;D2:2<=AUJOURDHUI())});séquence(lignes(C3:C1000);1;2);{"Article";0})))
C’est tout. Merci de votre attention. Profitez bien !
