Intégration d'opérations financières récurentes dans un journal de compte
Bonjour à tous,
@Pac41 tu peux réduire ton nombre d'onglet en supprimant l'onglet "Accueil" en effet dans l'interface Sheets il y a tout en bas à gauche une icone qui représente quelques barres horizontales avec la fonction "Toutes les feuilles" ce qui est plus pratique pour aller chercher une feuille.
En plus de la remarque pertinente d'Alex
1- oui supprime les colonnes devenues inutiles, tu gagneras en lisibilité
2- tu peux mettre en H1 de Prochaines échéances
={"Echéance suivante";arrayformula(IF(F2:F="";"";IF(F2:F="Mensuel";date(YEAR(G2:G);MONTH(G2:G)+1;DAY(G2:G));IF(F2:F="Semestriel";date(YEAR(G2:G);MONTH(G2:G)+6;DAY(G2:G));IF(F2:F="Annuel";date(YEAR(G2:G)+1;MONTH(G2:G);DAY(G2:G));IF(F2:F="Bimestriel";date(YEAR(G2:G);MONTH(G2:G)+2;DAY(G2:G));""))))))}et supprimer toutes les formules de la colonne. C'est une façon de répercuter une formule sur toute une colonne. Génial !
3- idem dans les feuilles,
={"Titre de la colonne";arrayformula(_____________)}avec ta formule à la place de ____________ en y mettant par exemple A2:A au lieu de A2
4- proposition : prévoir des échéances uniques en effaçant la colonne F, cela peut être intéressant pour mémoriser un règlement futur unique
5- dans le script, remplace H par G (erreur de ma part)
6- mets une date en C pour le solde initial sur chaque compte et coche la case banque, on ne sait jamais, mets par exemple 31/12/2020, cela pourra servir pour un calcul de solde
7- virement depuis et virement vers ...
- que se passe-t-il si on écrit virement vers avec une valeur négative ? in fine ne peut-on pas seulement écrire virement ?
- j'avais pensé en effet à un onglet-transaction pour les virements, mais l'inconvénient est qu'il faut à chaque fois retaper les infos
- pourquoi pas utiliser le même onglet que les futures échéances, d'autant que certains virements peuvent être récurrents, sinon ne pas mettre de périodicité, mettre juste une date en G avec le montant s'il change
- avec uniquement "Virement" (sans depuis ou vers), le code devient
function onEdit(event) {
var f1 = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if (f1.getName() == 'Prochaines échéances' && r.getColumn() == 9){
if (r.getValue()==true && r.offset(0,-2).getValue()!=""){
var origine = ["B","C","D","E","G"];
var destination = ["D","E","F","G","C"];
var compte = f1.getRange('A'+r.getRow()).getValue()
var f2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(compte);
var derL = getLastDataRow(f2) + 1;
for (var i = 0; i < origine.length; i++){
f2.getRange(destination[i] + derL).setValue(f1.getRange(origine[i]+r.getRow()).getValue());
}
Browser.msgBox('Transaction enregistrée dans ' + compte + ' ligne ' + derL)
if (f1.getRange('B'+r.getRow()).getValue()=='Virement'){
origine = ["B","C","A","G"];
destination = ["D","E","F","C"];
compte = f1.getRange('D'+r.getRow()).getValue()
f2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(compte);
derL = getLastDataRow(f2) + 1;
for (var i = 0; i < origine.length; i++){
f2.getRange(destination[i] + derL).setValue(f1.getRange(origine[i]+r.getRow()).getValue());
}
// inversion signe
f2.getRange('G' + derL).setValue( - f1.getRange('E' + r.getRow()).getValue());
Browser.msgBox('Transaction enregistrée dans ' + compte + ' ligne ' + derL)
}
f1.getRange('G'+r.getRow()).setValue(f1.getRange('H'+r.getRow()).getValue())
r.setValue(false)
trier(f1,8,2)
}
}
}
function getLastDataRow(sheet) {
var lastRow = sheet.getLastRow();
var range = sheet.getRange("C" + lastRow);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
}
function trier(feuille,colonne,depuisLigne){
var range = feuille.getRange(depuisLigne, 1, feuille.getLastRow() - (depuisLigne-1), feuille.getLastColumn());
range.sort({column: colonne, ascending: true});
}8- un compte par onglet ou pas ?
- généralement je mets tous mes comptes dans le même onglet et j'agis par TCD et par segment, dans excel; depuis ma refonte il y a 5 ans, j'ai 3600 lignes
- avec google sheets, un onglet par compte peut se tenter, notamment avec une fonction puissante qui est query, bien plus intéressante que SOMME.SI.ENS ... on pourra voir cela
9- limite les nombre de lignes au juste nécessaire surtout pour la synthèse de tous les comptes, mais aussi pour les prochaines échéances (pas besoin de 1000 lignes s'il n'y en a que 20)
D'accord pour mettre une valeur négative en virement et ne garder qu'une seule modalité "virement "
je voudrais garder l'onglet ACCUEIL car je compte mettre d'autres données de synthèse.
1 compte par onglet, je trouve ça mieux si la formule query peut gérer 11 comptes
merci à tous. Je ne suis pas chez moi ce week-end, je regarderai vos contributions dès mardi.
10- exemple de synthèse des différentes feuilles pour un mois donné
en A4 avec 2 paramètres en B1 et B2
={query('Crédit Agricole Pac'!A3:J;"select A,C,F,G,H where A<>'' AND C<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"' AND C>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' ");
query('Crédit Agricole Lolo'!A3:J;"select A,C,F,G,H where A<>'' AND C<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"' AND C>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' ");
query('Compte sur Livret'!A3:J;"select A,C,F,G,H where A<>'' AND C<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"' AND C>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' ")}et ce n'est pas un problème de mettre 11 lignes
on peut ajouter un critère, ou bien faire un TCD à partir de ce résultat, ou un SOMMEPROD sur cette synthèse
ou mieux en A3
={
"Code poste"\"Date"\"Poste"\"Tiers"\"Montant";
query('Crédit Agricole Pac'!A3:J;"select A,C,F,G,H where A<>'' AND C<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"' AND C>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' ");
query('Crédit Agricole Lolo'!A3:J;"select A,C,F,G,H where A<>'' AND C<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"' AND C>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' ");
query('Compte sur Livret'!A3:J;"select A,C,F,G,H where A<>'' AND C<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"' AND C>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' ")
}explications ...
> d'abord on a ici 4 parties séparées par ;
={
_____partie 1__________;
query(____ query sur premier compte __________);
query(____ query sur premier compte __________);
query(____ query sur premier compte __________)
}elles vont se mettre les unes sous les autres car séparées par le point-virgule
> ensuite, la partie 1 comprend 5 textes, séparés par \ ces textes se mettent alors sur la même ligne de tête puisque le séparateur est l'anti-slash
il est indispensable que le nombre 5 soit le même que les 5 colonnes choisies dans la fonction query, ici A C F G et H
11- il faudra choisir comment tu enregistres les dépenses ... avec un chiffre négatif ou en positif ?
une solution (que je pratique pas) est de séparer en 2 colonnes crédit et débit
sinon on reste tout en + et dans ce cas il faut bien conserver la distinction virement depuis et virement vers
Bonjour et encore merci. Je n'intègre toujours pas vos modifications car je n'ai pas de PC sous la main. Je suis le fil etvrepi de seulement aux questions pour le moment. J’enregistre les montants en positifs car après la finalité est de remplir l'onglet BUDGET. Ensuite, peu importe, je trouverais une solution (valeur absolue) pour totaliser les dépenses.
Pour les virements, voici comment je vois les choses. Juste une modalité "virement" et on autorise un poste "epargne” pour le compte où je dépose/prends mon épargne (ici le LDD). De cette façon, quand je toise le poste "epargne" dans mon budget, j'ai le montant que j'ai épargné.
Compte courant : __ : -200
Ldd : épargne : 200
Ldd : épargne : -200
Compte courant : ___ : 200
Pour les virements de compte à compte qui ne sont juste qu'un déplacement de liquidités, pas besoin de poste de dépense renseigné.
Voilà mon idée. Fais au plus simple, avec ou sans négatif, une ou deux colonnes, du moment que mes soldes sont justes et que calcul de budget fonctionne. Grand merci
Bonjour,
De retour de week-end, je regarde les améliorations proposées et me pose plusieurs questions.
https://docs.google.com/spreadsheets/d/1bG9waDNzaQb0svgpRFoX4ch2xxfHWiQ-jsnfMxtEceE/edit?usp=sharing
Je mets à jour le fichier partagé pour que les changements de colonnes soient pris en compte dans les propositions de script.
Dans l'onglet "Prochaines échéances", je n'ai laissé que "virement" et viré "depuis" ou "vers". Ma question est comment fait-il pour savoir quel est le compte qui envoie et celui qui reçoit ? Il manque une colonne pour le renseigner non ??? Je pense que les noms de colonnes ne sont plus les bons non plus.
J'ai intégré la fonction arrayformula sur cet onglet. Pour ceux de chaque compte, je ne l'ai pas encore fait, je vais devoir virer la ligne 1 de tous les onglets pour pouvoir le faire. Je le ferai plus tard, j'ai compris cette formule magique.
Concernant le calcul du budget, je n'ai pas tout à fait compris la formule avec les 4 parties... et les 5 textes parce que 5 colonnes.
Merci de votre aide
Dans l'onglet "Prochaines échéances", je n'ai laissé que "virement" et viré "depuis" ou "vers". Ma question est comment fait-il pour savoir quel est le compte qui envoie et celui qui reçoit ? Il manque une colonne pour le renseigner non ??? Je pense que les noms de colonnes ne sont plus les bons non plus.
Dans ce cas, le compte donneur est celui de la colonne A, le compte receveur est celui de la colonne D (Tiers). Subtilité, si la valeur est négative, cela devient l'inverse.
Il reste pas moins vrai que j'ai découvert un peu tardivement qu'il fallait éclaircir les conventions de signe, voire peut-être à séparer crédit/débit.
On en reparle, je suis pris à l'instant !
Merci. En effet, ça fonctionne bien. Il me reste à voir si je dois ou pas mettre le signe - et dans quel cas. Quand je veux épargner et que cela compte dans le budget (épargne), il faut que le compte receveur (somme positive) ait comme poste "épargne". À l'inverse, quand je déplace de l'épargne, il faut que le poste "epargne" apparaisse sur le compte ou la valeur est à déduire.
Tu me diras si tu choisis le signe ou des colonnes séparées crédit/débit. Contrairement à ce que je fais, je choisirais bien ici les 2 colonnes.
Quand j'épargne, en fait je fais juste un virement sur un compte d'épargne ! ou alors je fais juste le solde de tous les comptes en fin de mois.
En fait, je pense que pour ça marche, il faut que j'utilise le signe "-"
Pour être cohérent avec les prélèvements, si je veux que le compte en colonne A soit le compte émetteur du virement, il faut que je mette un montant en négatif. Ensuite, je renseigne le poste sur le compte qui m’intéresse plutôt que de chercher à le noter dans l'onglet "Prochaines échéances".
Je pensais que le script réalisais un tri par date à la fin de l'enregistrement, mais cela ne me semble pas fonctionner. Peut-être parce que pour le moment, j'ai toujours la ligne 1 qui comprend le nom du compte, la date du jour, le solde de la banque. Je vais voir pour la supprimer de toutes façons.
Sinon pourrais-tu me dire comment faire un tri, en fonction de la date, à partir de la ligne 4 et ce pour toutes les lignes remplies ?
Il ne me reste plus qu'à comprendre ta formule miraculeuse qui va me permettre de calculer les montants pour l'onglet budget et j'aurai une appli fonctionnelle. Grand merci pour ta patience.
Dans le script, j'ai créé cette fonction
function trier(feuille,colonne,depuisLigne){
var range = feuille.getRange(depuisLigne, 1, feuille.getLastRow() - (depuisLigne-1), feuille.getLastColumn());
range.sort({column: colonne, ascending: true});
}je ne la déclenche que dans la feuille des "Prochaines échéances" comme suit
trier(f1,8,2)avec f1 qui a été défini comme la feuille
if (f1.getName() == 'Prochaines échéances' ..........................8 est la huitième colonne, 2 est la seconde ligne
si tu souhaites en effet que ce tri s'applique aussi à la feuille qui reçoit l'échéance, tu peux ajouter alors
trier(f1,8,2)
trier(f2,X,4)remplace X par le n° de la colonne concernée (la date)
Pour être cohérent avec les prélèvements, si je veux que le compte en colonne A soit le compte émetteur du virement, il faut que je mette un montant en négatif. Ensuite, je renseigne le poste sur le compte qui m’intéresse plutôt que de chercher à le noter dans l'onglet "Prochaines échéances".
Nom d'une pipe, tu as tout à fait raison. Je peux corriger dans la macro, mais à la limite c'est plus logique !
Je vais me faire une copie et je vais passer pour moi à 2 colonnes crédit/débit. Cela évitera peut-être de tomber dans le panneau !
Sinon pourrais-tu me dire comment faire un tri, en fonction de la date, à partir de la ligne 4 et ce pour toutes les lignes remplies ?
si tu souhaites que le tri puisse être fait indépendamment des "prochaines échéances" sur n'importe quelle feuille après la saisie, je pense qu'il faudrait faire un menu personnalisé ... ce sera fait d'ici demain
J'ai donc rajouté "trier (f2,3,4)" à la suite, mais cela ne fait pas le tri dans la feuille de destination...
Je m'en sui sorti avec le budget sur les 11 onglets avec la formule "query". Je compte utiliser le résultat avec un TCD.
j'étais dessus, et il semble que les dates barrées perturbent le tri !! essaie en enlevant la MFC qui barre les cellules
nota : je pense qu'il faut mettre une date (ancienne, genre 31/12/2020) sur la ligne 3, cocher la case Pointé, et faire le tri à partir de la ligne 3
Voici une solution pour trier à la demande les feuilles (critère : ces feuilles ont la valeur Date en C2)
attention : la fonction trier a changé ...
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('**MENU**')
.addItem('Trier par date !', 'trierDate')
.addToUi();
}
function trierDate(){
var feuille = SpreadsheetApp.getActiveSheet();
if (feuille.getRange("C2").getValue()=='Date'){
trier (feuille,3,4)
}
}
function trier(feuille,colonne,depuisLigne){
var derL = getLastDataRow(feuille)
var range = feuille.getRange(depuisLigne, 1, derL - (depuisLigne-1), feuille.getLastColumn());
range.sort({column: colonne, ascending: true});
}ferme le fichier et rouvre le, tu devrais voir un menu en haut à droite
je ne mets pas cette fonction qui existe déjà ...
function getLastDataRow(sheet) {
var lastRow = sheet.getLastRow();
var range = sheet.getRange("C" + lastRow);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
}J'ai donc rajouté "trier (f2,3,4)" à la suite, mais cela ne fait pas le tri dans la feuille de destination...
essaie avec la nouvelle fonction de tri (avec ou sans dates barrées, là je ne suis pas sûr) ci-dessus
le problème est que comme il y a des cases à cocher jusqu'au bout, il n'interprète pas bien derL sans doute
Super !!! La fonction "Trier par date" a l'air de bien fonctionner !
Merci.
Juste une petite question toute bête.... Je cherche à modifier l'onglet "Synthèse". Je voudrais même le simplifier et lui demander d'afficher les valeurs pour quand les colonnes M sont égales eu chiffre inscrit en I1 (au lieu de "si date comprise entre du ... au...."). C'est tout con et je n'y arrive pas... Voici le début de la formule.:
={query('Crédit Agricole Pac'!A3:N;"select A,C,F,G,H,L where A<>'' AND M=I1 ");
Merci encore