Les possibilités presque sans limites des formules à tableaux imbriqués
Bonjour à tous,
Depuis quelque temps je travaille sur un projet d'application complexe ... Mais cette fois-ci j'ai voulu tenter quelque chose de différent, réaliser cette application essentiellement avec des formules malgré la complexité du projet (et tout de même quelques macros pour des fonctionnalités secondaires).
Ici le projet concerne Google Sheets car Excel ne gère toujours pas à ce jour les tableaux imbriqués (espérons que cela change un jour, cela rendrait alors tout ceci possible aussi sur Excel).
Si j'ai voulu tenter de le faire avec des formules, c'est également parce qu'avec Google Sheets il y a un délai d'attente avec les macros alors qu'avec les formules c'est instantané.
Je partage ça ici aujourd'hui pour vous donner une idée des possibilités offertes par l'utilisation des tableaux imbriqués ... Et d'ailleurs avant de commencer ce projet, je ne pensais pas pouvoir faire autant avec des formules (dans ce projet, chacun des principaux tableaux a été créé à l'aide d'une seule formule malgré la complexité des opérations à effectuer).
L'un des tableaux les plus complexes à réaliser (avec une seule formule) a été celui-ci :
Pour le réaliser il a notamment fallu :
- créer la ligne de titre (le plus simple)
- obtenir la première liste de comptes et les montants pour la première liste en haut à gauche en :
- filtrant les comptes et montants d'une seconde feuille en fonction du type de chaque compte (données sur une troisième feuille)
- calculer la somme de tous les montants
- répéter l'opération pour la seconde liste en haut à droite
- ces listes pouvant être de longueur variable, il a fallu ensuite calculer le nombre de lignes de chaque liste, pour ajouter ensuite le nombre de lignes manquantes au tableau le plus court
- les 2 listes sont désormais 2 tableaux de même longueur, elles peuvent donc être assemblées au-dessous de la ligne de titre
- une ligne de bénéfice ou de perte est ajoutée à gauche ou à droite selon le résultat des sommes de chaque liste
- une ligne de total est ensuite ajoutée (puisque la taille du tableau est dynamique, les mises en forme sont faites via MFC), j'avais d'ailleurs créé un tuto simple pour montrer comment ajouter un total à la fin d'un tableau de taille variable
- une autre ligne de bénéfice ou de perte est ajoutée au-dessous
- il a fallu ensuite répéter les différentes opérations de filtre des comptes, calculer la somme (en additionnant aussi la somme précédente), faire de même pour la seconde liste, ajouter les lignes manquantes, assembler, ajouter les lignes de bénéfice ou de perte et le total
- répéter tout ça encore une dernière fois
- gérer certaines exceptions comme par exemple un compte spécial qui peut se retrouver à gauche ou à droite selon sa valeur
Ce qui donne au final une sympathique formule :
=LET(tabB,Balance!B3:E1002,types,DB_COMPTES!D2:D1001,
tabVarStock,BYROW(FILTER(tabB,types=56),LAMBDA(r,{INDEX(r,1),INDEX(r,2),INDEX(r,3)-INDEX(r,4)})),
valeurVarStock,INDEX(tabVarStock,3),
siErr,{"","",0,0},
tabCharges1_0,BYROW({SIERREUR(FILTER(tabB,types=6),siErr);SIERREUR(FILTER(tabB,types=7),siErr);SIERREUR(FILTER(tabB,types=8),siErr)},LAMBDA(r,{INDEX(r,1),INDEX(r,2),INDEX(r,3)-INDEX(r,4)})),
tabProd1_0,BYROW(SIERREUR(FILTER(tabB,types=5),siErr),LAMBDA(r,{INDEX(r,1),INDEX(r,2),INDEX(r,4)-INDEX(r,3)})),
tabCharges1,SI(valeurVarStock>=0,SORT(VSTACK(tabCharges1_0,tabVarStock)),tabCharges1_0),
tabProd1,SI(valeurVarStock<0,SORT(VSTACK(tabProd1_0,{INDEX(tabVarStock,1),INDEX(tabVarStock,2),ABS(INDEX(tabVarStock,3))})),tabProd1_0),
nbLigTabCharges1,LIGNES(tabCharges1),nbLigTabProd1,LIGNES(tabProd1),diff1,nbLigTabCharges1-nbLigTabProd1,
totalCharges1,SOMME(CHOOSECOLS(tabCharges1,3)),
totalProd1,SOMME(CHOOSECOLS(tabProd1,3)),
diffTotal1,totalCharges1-totalProd1,
benNet1,SI(diffTotal1<=0,-diffTotal1,0),
perteNet1,SI(diffTotal1>0,diffTotal1,0),
total1,totalCharges1+benNet1,
tabCharges2,BYROW(SIERREUR(FILTER(tabB,types=10),siErr),LAMBDA(r,{INDEX(r,1),INDEX(r,2),INDEX(r,3)-INDEX(r,4)})),
tabProd2,BYROW(SIERREUR(FILTER(tabB,types=9),siErr),LAMBDA(r,{INDEX(r,1),INDEX(r,2),INDEX(r,4)-INDEX(r,3)})),
nbLigTabCharges2,LIGNES(tabCharges2),nbLigTabProd2,LIGNES(tabProd2),diff2,nbLigTabCharges2-nbLigTabProd2,
totalCharges2,SOMME(CHOOSECOLS(tabCharges2,3))+perteNet1,
totalProd2,SOMME(CHOOSECOLS(tabProd2,3))+benNet1,
diffTotal2,totalCharges2-totalProd2,
benNet2,SI(diffTotal2<=0,-diffTotal2,0),
perteNet2,SI(diffTotal2>0,diffTotal2,0),
total2,totalCharges2+benNet2,
tabCharges3,BYROW(SIERREUR(FILTER(tabB,types=12),siErr),LAMBDA(r,{INDEX(r,1),INDEX(r,2),INDEX(r,3)-INDEX(r,4)})),
tabProd3,BYROW(SIERREUR(FILTER(tabB,types=11),siErr),LAMBDA(r,{INDEX(r,1),INDEX(r,2),INDEX(r,4)-INDEX(r,3)})),
nbLigTabCharges3,LIGNES(tabCharges3),nbLigTabProd3,LIGNES(tabProd3),diff3,nbLigTabCharges3-nbLigTabProd3,
totalCharges3,SOMME(CHOOSECOLS(tabCharges3,3))+perteNet2,
totalProd3,SOMME(CHOOSECOLS(tabProd3,3))+benNet2,
diffTotal3,totalCharges3-totalProd3,
benNet3,SI(diffTotal3<=0,-diffTotal3,0),
perteNet3,SI(diffTotal3>0,diffTotal3,0),
total3,totalCharges3+benNet3,
{
{"Résultat de "&'Sheets-Compta'!C10&" au "&TEXTE('Sheets-Compta'!C14,"dd/mm/yyyy"),"","","","",""};
{SI(diff1<0,REDUCE(tabCharges1,SEQUENCE(-diff1),LAMBDA(tab,i,VSTACK(tab,{"","",""}))),tabCharges1),SI(diff1>0,REDUCE(tabProd1,SEQUENCE(diff1),LAMBDA(tab,i,VSTACK(tab,{"","",""}))),tabProd1)};
SI(perteNet1=0,{"","Bénéfice net d'exploitation",benNet1,"","",""},{"","","","","Perte nette d'exploitation",perteNet1});
{"","Total",total1,"","Total",total1};
SI(perteNet1=0,{"","","","","Bénéfice net d'exploitation",benNet1},{"","Perte nette d'exploitation",perteNet1,"","",""});
{SI(diff2<0,REDUCE(tabCharges2,SEQUENCE(-diff2),LAMBDA(tab,i,VSTACK(tab,{"","",""}))),tabCharges2),SI(diff2>0,REDUCE(tabProd2,SEQUENCE(diff2),LAMBDA(tab,i,VSTACK(tab,{"","",""}))),tabProd2)};
SI(perteNet2=0,{"","Bénéfice net d'entreprise",benNet2,"","",""},{"","","","","Perte nette d'entreprise",perteNet2});
{"","Total",total2,"","Total",total2};
SI(perteNet2=0,{"","","","","Bénéfice net d'entreprise",benNet2},{"","Perte nette d'entreprise",perteNet2,"","",""});
{SI(diff3<0,REDUCE(tabCharges3,SEQUENCE(-diff3),LAMBDA(tab,i,VSTACK(tab,{"","",""}))),tabCharges3),SI(diff3>0,REDUCE(tabProd3,SEQUENCE(diff3),LAMBDA(tab,i,VSTACK(tab,{"","",""}))),tabProd3)};
SI(perteNet3=0,{"","Bénéfice net de l'exercice",benNet3,"","",""},{"","","","","Perte nette de l'exercice",perteNet3});
{"","Total",total3,"","Total",total3}
})Pour les utilisateurs d'Excel qui ne connaissent pas Google Sheets, les {} c'est l'équivalent de ASSEMB.V / ASSEMB.H mais en plus pratique.
Et pour ceux qui se poseraient la question, cette formule (et les autres) sont très rapides ... A l'utilisation, c'est instantané.
J'espère ne pas vous avoir donné mal à la tête
Bonjour Sébastien,
Wow, projet intéressant, et franchement je suis surpris (positivement) en voyant qu'apparemment une formule si longue & assez lourde ne pose pas de problèmes de performances particuliers.
Je me demandais juste pourquoi tu n'avais pas utilisé les fonctions nommées, peut-être pour alléger un peu ce gros bloc sur des petits blocs que j'ai vus repris quasiment à l'identique plusieurs fois (les REDUCE et BYROW notamment) ?
Pour finir je dirais que ces fonctions à rallonge tendent de plus en plus à prendre le pas sur ce qu'on était forcé de traiter via scripts/macros, mais elles souffrent alors des mêmes problèmes : seul quelqu'un ayant des connaissances approfondies peut les comprendre, et la relecture à long terme demandera surement pas mal de courage !
En tout cas beau projet, je salue l'effort.
Merci pour ton message.
Pour les fonctions nommées, je m'étais posé la question mais il n'y a pas suffisamment de longs bouts de code à répétition à mon sens pour que ça en vaille la peine, d'autant plus qu'au cours du développement de la formule ça peut beaucoup évoluer et j'aime avoir tout le code sous les yeux.
Mais après avoir fait tout ça j'ai remarqué quelque chose ...
Tu peux en fait ajouter ta fonction nommée directement dans la formule (si tu n'en as pas besoin dans d'autres formules).
En voici un exemple simple :
=LET(
ajouter1,LAMBDA(i,i+1),
{ajouter1(22),ajouter1(33)}
)C'est comme affecter une fonction à une variable en Apps Script.
je suis surpris (positivement) en voyant qu'apparemment une formule si longue & assez lourde ne pose pas de problèmes de performances particuliers
Je ne sais pas comment ça fonctionne en arrière-plan mais je suppose que c'est un peu comme une macro où lorsque tu récupères les données d'une plage pour l'affecter à une variable c'est relativement lent, en revanche ensuite tu peux effectuer des dizaines de milliers d'opérations sur la variable qui contient le tableau et ce sera très rapide (c'est valable en VBA et en Apps Script).
Ici, il y a par exemple :
tabB,Balance!B3:E1002,types,DB_COMPTES!D2:D1001,Les données des cellules sont récupérées une seule fois, puis toutes les opérations sont faites sur les variables, ce qui devrait être beaucoup plus rapide qu'une multitude de formules qui récupèrent chacune ces mêmes plages.
seul quelqu'un ayant des connaissances approfondies peut les comprendre, et la relecture à long terme demandera surement pas mal de courage !
Effectivement, je trouve ce type de formule sensiblement plus complexe à comprendre qu'une macro équivalente commentée (mais bon dans ce cas, les utilisateurs n'ont pas à toucher aux formules donc ça va
Oui tu as tout à fait raison. Moi aussi il m'arrive de définir 1 ou 2 fonction(s) lambda dans 1 LET pour une opération répétée plusieurs fois. Je trouve ça plus propre/lisible. Et l'avantage c'est que ça reste dans le scope de la formule/cellule sans polluer le classeur.
Pour les performances nous avions vu avec @PierreLepinay sur un fil que GSheets était malin et reconnaissait qu'une plage était constante et ne la chargeait qu'une seule fois, même dans les fonctions non parallèles comme REDUCE.
Enfin, pour les commentaires, je n'hésite pas à ajouter des variables "inutiles" de type texte pour séparer mes blocs, par exemple :
=LET(
_com1; "Fonction de somme";
λSOMME2; LAMBDA(p; MAX(0; SUM(p)));
_com2; "Calcul de la somme>=0";
λSOMME2(A1:A10)
)J'avais vu cette idée quelque part et j'ai trouvé ça malin. Sur des fonctions comme la tienne je pense que ça a toute sa place.
Je ne sais pas s'il existe des équivalents GSheets (sans doute) mais l'add-on Excel Labs | Microsoft Garage que m'avait recommandé @JFL est vraiment pas mal aussi pour améliorer la lisibilité d'une longue formule. Ça rejoint ton Explorateur de formules Excel cela dit.