Intégration d'opérations financières récurentes dans un journal de compte

Une question : la nomenclature des rubriques ? est-ce une version personnelle ou "officielle" pour pouvoir se comparer, et quelle serait alors la source ?

Les 12 postes sont issus de la nomenclature européenne officielle, nommée COICOP. Ensuite, pour ce que j'appelle "poste 2", j'ai regroupé en fonction de mes usages.

Merci

Au passage, quand tu n'auras plus de questions, clos ce fil de discussion en cliquant sur

Complément sur lesquel j'ai travaillé ...

en A1 tu as mis

={"Code Poste";ARRAYFORMULA(IF(F2:F="";""; LOOKUP(F2:F;Lib;CAT)))}

je n'y avais pas pensé ... dans le cas où Lib ne serait pas trié, tu peux mettre

={"Code";arrayformula(vlookup(iferror(match($F$2:$F;Lib;0);1); {row(CAT) \ CAT}; 2))}

à condition de laisser vierge la première valeur dans Lib et CAT pour les erreurs éventuelles

l'avantage est de ne pas avoir à trier

explications ...

je cherche à quelle ligne dans Lib, je trouve la valeur de F (si pas trouvé je mets ligne 1 sensée être vide)

iferror(match($F$2:$F;Lib;0);1

puis je construis une matrice ligne/valeur de catégorie

{row(CAT) \ CAT}

sur laquelle j'applique vlookup

autre chose nettement plus complexe (là je n'aurais réussi à la construire seul, même si j'ai simplifié ce qui était proposé)

le solde courant en colonne K est

={"Trésorerie";ArrayFormula(if(isblank(A2:A);;mmult(transpose(if(transpose(row(H2:I))>=row(H2:I);I2:I-1*H2:H; 0));row(A2:A)^0)))}

avec en I les crédits et H les débits

L'astuce complètement délirante est qu'une formule de ce type

=arrayformula(transpose(row(A2:A))>=row(A2:A)) 

dessine la matrice suivante

TRUE TRUE TRUE TRUE
FALSE TRUE TRUE TRUE
FALSE FALSE TRUE TRUE
FALSE FALSE FALSE TRUE

etMMULT multiplie 2 matrices, la seconde est uniquement composée de 1 en nombre égal au nombre de lignes (row(A2:A)^0)

edit : plus simple

={"Trésorerie";ArrayFormula(if(isblank(A2:A);;mmult(1*(transpose(row(H2:I))<=row(H2:I));I2:I-H2:H)))}

Merci de tes recherches complémentaires. Je vais voir pour les intégrer.

La 1ere astuce sert à pouvoir rajouter de nouvelles catégories sans avoir à les trier ?

La seconde, permet d'avoir le calcul d'une façon plus évoluée mais du coup, je vais perdre le solde en banque des opérations que j'ai pointé ? Si j'intègre ta formule, j'aurai "Trésorerie" à la place ?

Bojour,

Pac41 a écrit :

La 1ere astuce sert à pouvoir rajouter de nouvelles catégories sans avoir à les trier ?

OUI, fait toujours un essai sur un fichier test !

Pac41 a écrit :

La seconde, permet d'avoir le calcul d'une façon plus évoluée mais du coup, je vais perdre le solde en banque des opérations que j'ai pointé ? Si j'intègre ta formule, j'aurai "Trésorerie" à la place ?

C'est vrai ... MAIS :

• on peut aménager la formule pour multiplier par la colonne "pointé" ... je vais faire un essai
• ou bien comme je l'avais fait/proposé, mettre les soldes dans accueil, y compris ce que je fais souvent : solde courant + engagement de dépenses seuls !

je me suis fait un condensé de ce que nous avons partagé

https://docs.google.com/spreadsheets/d/1jNCIXezjAVgATnTKLlkVxwQ53jrj9T03dIfaeMamzD0/edit?usp=sharing

Je vais regarder tout cela. A première vue, je constate que dans l'onglet Accueil, tu vas chercher les soldes sur tous les onglets, quels que soient leurs noms, du moment que ce soit le nom en A. Cela permet d'avoir quelque chose de personnalisable sans avoir à écrire en dur les noms des onglets !

Bonjour,

on peut aménager la formule pour multiplier par la colonne "pointé"

=SOMMEPROD((indirect("'"& $A3&"'!$F$2:$F"))*(indirect("'"& $A3&"'!$G$2:$G")=VRAI))-SOMMEPROD((indirect("'"& $A3&"'!$E$2:$E"))*(indirect("'"& $A3&"'!$G$2:$G")=VRAI))

@Mike peut-on faire la somme par compte mais avec un pointage par des croix

Merci pour ta réponse.

Bonjour Alex

Alexander a écrit :

@Mike peut-on faire la somme par compte mais avec un pointage par des croix

Merci pour ta réponse.

comme ceci alors ...

=SUMPRODUCT((indirect("'"& $A3&"'!$F$2:$F"))*(indirect("'"& $A3&"'!$G$2:$G")="x"))-SUMPRODUCT((indirect("'"& $A3&"'!$E$2:$E"))*(indirect("'"& $A3&"'!$G$2:$G")="x"))

Super ! mais pour ma part j'ai du remplacer le (*) par (;) sinon :

Erreur

Le paramètre 1 de la fonction MULTIPLY attend des valeurs du type nombre. Mais est du type texte et ne peut pas être forcé pour être nombre.

=SOMMEPROD((indirect("'"& $A2&"'!$K$2:$K"));(indirect("'"& $A2&"'!$L$2:$L")="x"))-SOMMEPROD((indirect("'"& $A2&"'!$J$2:$J"));(indirect("'"& $A2&"'!$L$2:$L")="x"))

Un grand MERCI !!!

Juste une petite question. Tout fonctionne à merveille J'ai 11 onglets qui tournent avec des Arrayformula, d'autres onglets qui tournent avec des query assez sophistiqués (merci à Steelson), pas mal de mise en forme conditionnelle. Bref, tout fonctionne, mais qu'est-ce que c'est lent !!! J'ajoute 1 opération et là il faut 5 minutes pour qu'il fasse le job: la barre verte en haut à droite est longue à de remplir avec "calcul des cellules"....

Y-a-t-il des choses que je puisse puisse faire pour accélérer les calculs ???

Merci de vos conseils

Beaucoup, beaucoup beaucoup de choses sans doute !

1. Réduit d'abord et avant tout le nombre de lignes du fichier, pas la peine d'avoir 1000 lignes pour chaque onglet. Mets le juste nécessaire et une marge suffisante.
2. Ensuite, car je pense que cela ne sera pas suffisant, iI faut retourner à l'essentiel ! supprime tout ce qui est "de confort" au niveau des formules. Il n'est peut-être pas utile d'avoir le solde ligne à ligne sur tous les comptes.
3. Au pire, essaie sur une copie en supprimant la consolidation par mois des 11 onglets. On pourrait alors voir comment le faire dans un fichier séparé en lien avec ce fichier.
4. On pourrait peut-être aussi voir à supprimer le onEDit qui se déclenche quelle que soit la modification et la passer "à la demande" ... donc essaie aussi sans onEdit et on verra comment faire assez facilement si cela gagne en réactivité.
5. Regarde aussi quelle est ta vitesse de connexion internet avec fast.com par exemple (si < 10 Mbps, il y a un problème pour ce genre d'application)
6. Fais des dons à des assos pour solder quelques comptes.

Merci, en effet, j'ai supprimé les lignes et ça marche beaucoup mieux. Cela ne fait que repousser le problème à plus tard, mais je peux aussi penser à en faire 1 par an par exemple ?

Je vais voir pour supprimer l'affichage systématique des soldes en fin de ligne. A l'origine, j'avais juste une cellule par onglet qui affichait le solde pointé et celui à venir. C'est suite à ton modèle que j'ai rajouté l'"option" à chaque ligne.

Merci encore !

Bonjour à tous.

Encore une question: tout fonctionne et maintenant rapidement. Une de mes motivations à passer sous Sheets était de pouvoir utiliser ce fichier depuis tous les supports (tablette, smartphone...). Je me rends compte d'une limite depuis la tablette: comment activer les scripts (on eDit) ?

Merci à vous !

Bonjour,

onEdit ne sert qu'à confirmer des virements et des transactions périodiques. Tout le reste fonctionne.

Je viens de tester onEdit sur l'appli android, cela fonctionne jusqu'à l' apparition du message ... qui ne s'affiche pas ! et bloque le reste du script. Néanmoins si tu fais un virement, il sera enregistré dans les 2 comptes, mais la case à cocher restera cochée et aucun message n'apparaîtra.

• On peut enlever le message, ou déplacer l'effacement de la case à cocher et la translation des dates et mettre le message tout à la fin !
• Mais in fine, le plus simple, est de remplacer partout

Browser.msgBox(_______________)

par

f1.getRange('J'+r.getRow()).setValue(____________________)

vérifie auparavant que la colonne J est bien affichée et disponible !

Merci, ça fonctionne. Je me suis mal exprimé. Tu m'avais permis la création d'un menu supplémentaire "**MENU**" avec la fonction trier par date; Ce bouton n'est pas accessible sur la tablette ou je n'ai pas trouvé comment l'afficher ;)

Oui, en effet les menus customisés n'apparaissent pas ! et a priori

https://developers.google.com/apps-script/guides/menus

Android Add-ons have been deprecated and no longer supported. New Android add-ons can't be reviewed or published. Existing Android add-ons continue to function.

Dans ce cas, il faut créer un bouton dans chaque page et lui affecter la macro.

Merci encore. J'ai créé un bouton sur onglet et assigné le script "trierDate". Depuis l'ordi, le bouton est cliquable et exécute la macro. Par contre depuis la tablette, le bouton apparaît, je clique dessus et rien ne se passe, à part qu'il m'affiche la fonction "supprimer" si je reste appuyé longtemps dessus. J'ai aussi essayé depuis la tablette d'insérer une image et je n'ai pas la possibilité d'y assigner un script. Pas possible non plus, depuis la tablette, d'insérer un dessin....

Je vais continuer à chercher ... mais décidemment c'est pas simple.

J'ai essayé avec onSelectionChange(e)mais cla ne fonctionne pas non plus sur android.

J'ai une solution qui permet de trier quand on entre une nouvelle date dans la dernière ligne, mais curieusement le tri ne s'effectue pas dans l'ordre alors que j'appelle la même fonction.