Insertion automatique des cellules
CarineVL Messages postés 320 Date d'inscription Statut Membre Dernière intervention -
Bonjour,
Je souhaite faire une petite amélioration dans le fichier « Résumé des factures ».
Toutes les factures sont numérisées et reprises dans ce fichier.
Je souhaiterais ajouter 2 colonnes dans le classeur « FACTURES » (reprises à droite et surlignées en jaune (COLONNES AP + AQ) et afficher son contenu actuel (inséré manuellement).
Pour la facilité à nouveau, ces cellules pointeraient donc sur une autre feuille « PAIEMENT BQ » reprenant les transactions bancaires et indiqueraient de manière automatique à l’aide de formules le montant du paiement effectué pour ce fournisseur et surtout sa date.
La colonne « MONTANT » servant de confirmation de la bonne recherche.
Pour essayer de filtrer au mieux et ceci pour des recherches fausses sur des montants standards comme 50 ou 100 … et pour ne pas afficher de faux résultats, serait-t-il possible d’y ajouter des conditions comme par exemple au niveau du fournisseur qui contiendrait peut-être x caractères identiques ou au niveau de la description …
Tout ceci est possible ?
Merci d'avance
https://www.cjoint.com/c/OCrrS2D2hat
Carine
- Insertion automatique des cellules
- Recherche automatique des chaînes ne fonctionne pas - Guide
- Réponse automatique thunderbird - Guide
- Logiciel de sauvegarde automatique gratuit - Guide
- Verrouiller des cellules excel - Guide
- Insertion table des matières word - Guide
15 réponses
Bonsoir,
Peut-on tenir pour acquis que le libellé du fournisseur se retrouve entièrement dans la colonne "description dépenses" ? Ou même que cette dernière colonne débute par le libellé du fournisseur ?
Daniel
Bonjour,
Dans le cas de la première facture, dans la description de FACTURES, on a "FA00049914" et sur la feuille PAIEMENT BANQU, on n'a rien ?
Daniel
Bonjour Daniel,
Oui en-effet, j'ai déjà essayé 2 reprendre 2 cas particuliers ...
(Qui peut le plus, peut le moins ...)
Pour le premier cas, Il arrive lors des paiements à distance que le fournisseur n'indique pas la communication.
Pour le deuxième cas, la description n'est pas identique car FA suivi d'un espace se retrouve en paiement alors que dans le relevé des factures, cela n'est pas repris ...
C'est la raison pour laquelle j'avais pensé à cette combinaison de conditions pour retrouver au mieux la date de paiement des factures.
(Montant de la facture + x caractères en fournisseur ou x caractères en description)
Au niveau des caractères, voir s'il est possible que si on indique par exemple 6 caractères identiques mais que la cellule n'en contient par exemple que 3 que cela continue quand même à remplir la condition.
Bien à toi,
Carine
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionBonjour,
La panne est liée à la localisation. J'étais hier en déplacement dans l'Essonne et je n'avais pas de problème. Pour wetransfer, tu peux obtenir un lien comme avec cjoint. Il faut cliquer ici :
Adresse mail supprimée Modération CCM
Puis ici :
Daniel
1. Pour la première ligne de la feuille Factures, quel colonne colonne de la feuille PAIEMENT BANQUE correspond aux 1239,03 de la colonne TTC ? Ou faut-il comparer d'autres colonnes ?
2. Le libellé "FELAI" au lieu d e"FEILAI" est inccorrect. Est-il possible d'envisager un liste déroulante de validation pour éviter ces erreurs ?
Daniel
Re,
1. Je n'ai pas retrouvé non plus le paiement de 1239.03€ sur le compte. Le paiement a dû se faire à partir d'un autre compte bancaire.
(Sur le relevé des factures, je devrai recopier les formules sur d'autres colonnes pour les autres comptes)
J'utiliserais la colonne "U" car les cellules sont déjà converties en valeur.
2. Je ne connais pas les listes déroulantes.
En sachant à quoi je veux arriver, je te donne bien sûr carte blanche au niveau des procédures.
Bien à toi,
Carine
Pour fêter le retour à la vie de cjoint (j'espère que c'est pareil pour toi), j'ai fait un petit classeur pour expliquer les listes de validation :
https://www.cjoint.com/c/OCtp7PpOSK4
Daniel
Regarde le classeur joint. Si tu as encore des problèmes avec cjoint.com, dis-le, je te fournirai un lien wetransfer.
https://www.cjoint.com/c/OCtqZEAVwE4
Restera encore à optimiser les formules.
Daniel
Le deux formules sont identiques. Seule la colonne récupérée est différente : la colonne P pour la colonne "banque date paiement" et la colonne W pour la colonne "BANQUE SC MONTANT".
Une chose que je ne t'ai pas dite : j'ai remplacé le "." par la "," décimale dans environ 2000 cellules dans les colonnes G à O de la feuille "PAIEMENT BANQUE".
Ces cellules doivent contenir des valeurs numériques.
Daniel
Bonsoir Daniel,
J’essaie de te faire le feed-back …
Les 2 formules fonctionnent mais il y a des mais car :
Depuis on dirait qu’Excel qui occupe en grande partie le processeur, (et même à moindre mesure le pc) est devenu plus instable et très, très lent ..
L’ouverture, la fermeture , l’enregistrement ou un simple formatage de cellule ou de simplement vouloir supprimer une simple ligne prend un temps fou (une quinzaine de minutes) et on dirait que les calculs se bloquent à 99% pendant x minutes …
Rien n’y fait car j’ai arrêté et redémarré le pc, réparer en ligne Office mais c’est la même chose et je ne sais pas pour quelle raison …
Au niveau de la recherche, on retrouve les paiements mais serait-il possible d’améliorer ou de diminuer les filtres pour ne pas avoir « pas trouvé » ?
Dans le fichier attaché, j’ai essayé de ne reprendre que les lignes dans le fichier des factures où il ne faudrait normalement que retrouver majoritairement une correspondance avec cette banque.
Je me dis que c’est si lent pour un seul cpte bancaire, qu’en sera-t-il si on rajoute dans ce fichier d’autres colonnes avec d’autres comptes bancaires ?
Toutes les lignes encore vides non encore alimentées dans le fichier des factures donnent aussi un résultat au 19-09-22 avec un montant de -1500,00€.
Bien à toi,
Carine
Bonjour,
Si je peux m'immiscer dans cette discussion...
Carine, ces données semblent provenir d'une extraction "csv", du moins pour la partie "Banque".
Serait-il envisageable de transmettre les 2 fichiers (banque et factures) dans leur format d'origine?
peut-être qu'un traitement via Power Query serait bien plus aisé...(importation, traitement et restitution dans un unique fichier, sans les colonnes intermédiaires...)
Peut-être?
Bonjour Cousinhub,
Effectivement les fichiers téléchargés de la Banque sont en csv.
D'une drôle de manière d'ailleurs car il me faut au départ tout convertir avant de l'utiliser.
Il n'y a donc pas d'autre format que celui qui a été communiqué.
Les fichiers à transmettre devraient l'être dans leur totalité ?
Bien à vous,
Carine
Bonsoir,
D'après les résultats que je vois, je pense qu'il y a un souci, notamment au niveau du fournisseur "Adobe"...
Moult références dans le relevé de banques, et seulement 3 dans l'onglet "Factures" (toutes avec le même montant)
Et un fichier csv n'est pas un fichier Excel, mais un fichier "Texte" (il ne s'ouvre donc pas, il s'importe)
Ce serait effectivement l'idéal, de bénéficier des fichiers dans leur totalité, mais complètement irresponsable de les diffuser en libre accès...
Bref, bon courage pour la suite
Hello,
Si je peux m'immiscer moi aussi...
Le classeur contient beaucoup, beaucoup, mais alors vraiment beaucoup (trop ?) de formules qui utilisent des colonnes entière en références !
Une colonne entière étant constitué de 1 048 576 de lignes Excel doit "scruter" chacune de ces lignes pour chacune de ces formules. C'est ingérable !
J'ajoute aussi que certaine formules sont fausses et donnent des résultats erronées bien entendus même s'ils paraissent juste.
Bon courage pour la suite, je pense qu'il faudrait repenser ce classeur différemment voire utiliser Power Query comme le suggère Consinhub
J'ai mis quelques explications en colonne AS avec les raisons pour lesquelles la recherche a échoué. Regarde s'il est possible d'améliorer.
Daniel
Re Daniel.
J'ai dû à nouveau redémarré Excel pour avoir un affichage du fichier ...
Oui effectivement tu as raison au niveau des résultats en raison des différences.
Juste une petite remarque au niveau de la différence ... (exemple la première ligne)
La référence dans "ENT et DEP SC" est FA 2023/170 au lieu de 2023-170. dans ce fichier ,,, Et si on retrouvait les mêmes 4 lettres identiques dans les 2 fichiers, on pourrait indiquer ok, non ? (2023 dans les deux fichiers)
Autrement c'est déjà parfait ...
Bien à toi,
Carine
Est-ce que tu peux poster une copie d'écran ? Je ne vois pas ce que tu veux dire.
Daniel
Re Daniel,
J'ai difficile à indiquer le bon chemin avec les formules car on ne les voit pas ...
surtout si je dois recopier ces colonnes pour les adopter aux autre comptes bancaires.
Pourrais-tu me réécrire les formules sur base des chemins figurant ci dessous:?
Banque
C:\Users\JPS\Dropbox\JPS\[BANQUES JPS.xlsx]ENT et DEP SC
Factures
c:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT
Ou y aurait-il une astuce pour le faire et s'assurer d'avoir le bon chemin ?
Bien à toi,
Carine
Re Daniel,
oui, sans doute car habituée aux formules monstrueuses :-)
J'ai repris ci-dessous la formule communiquée:
=LET(test;BYROW(colE;LAMBDA(x;SOMME(N(REGEX.TEST(x;STXT('C:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT'!D2;SEQUENCE(NBCAR('C:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT'!D2)-3);4))))));SI('C:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT'!C2<>"";PRENDRE(FILTRE(colP;(ESTNUM(CHERCHE('C:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT'!$C2;colD))*(colQ='C:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT'!$U2)
*(((colE)="")+
(test=1)))
+
(((colQ='C:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT'!$U2)*((test=1)+ESTNUM(CHERCHE(STXT('C:\Users\JPS\Dropbox\EQUINOXE SA\NUMERISATIONS\[RELEVE FACTURES EQUINOXE.xlsx]1 TOUT'!D2;3;9^9);colE)))));"pas trouvé");1);""))
Dans le fichier, celle-ci bloque sur le premier C du chemin ... (voir 2ième image)
J'avais mis des crochets pour le nom du fichier mais je ne suis pas sûre si c'est correct ...
Je vois le chemin pour les factures et c'est pourtant le fichier dans lequel la formule est insérée (dans 1 TOUT) et par contre je ne vois pas le chemin pour la banque ...
Normal ?
Bien à toi,
Carine
Je vais regarder ce qu'on peut faire avec une macro.
Daniel
Sur le classeur BANQUE JPS.xlsx, à partir de AG1, il faut ajouter les entêtes provisoires "Réserve1", "Réserve2"... jusque "Réserve37" en BQ1. Il s'agit d'une réserve de colonnes pour les ajouts futurs.
A propos du classeur joint, les recalculs sont longs, aussi je me suis mis en mode de calcul manuel. Si tu le fais, pense que c'est valable pour tous les classeurs.
Sinon, teste :
Daniel
Bonjour,
Je viens de lire ton dernier MP. Ca va un peu vite pour moi. Que faut-il faire ? Pour chaque ligne de "1 TOUT", rechercher sur les 4 feuilles banque ? (Je n'ai pas été plus loin, du coup).
Daniel
Hello Daniel,
Je viens de remarquer quelque chose ...
exemple Adobe (voir image ci-dessous)
Sur base des filtres, la recherche a retrouvé un paiement de 2023 et alors que c'est une facture de 2025.
Je ne sais pas comment est fait le filtre mais si la recherche se fait sur le début de la description, celle-ci sera toujours identique à toutes les factures (IEE...).
Est-ce que ce serait pas mieux de se baser non pas sur le début mais sur la fin ?
Une recherche sur x caractères identiques ne donnera pas non plus un bon résultat puisque ces factures avec des chiffres et lettres à rallonge renvoient les mêmes valeurs au début sur chaque facture.
A mon avis, le bon compromis serait de retrouver x caractères identiques mais à partir de la fin de la description (avec la restriction que si la description ne compte par exemple que 2 caractères, cela soit aussi accepté par la formule).
Qu'en penses-tu ?
Bien à toi,
Carine
1. Explication : sur la feuille banque, il y a 14 montants de 19,99 pour ADOBE et aucune référence.On peut facilement prendre la dernière ligne au lieu de la première, ce qui donnera 13/11/2024 pour ce cas. J'attends ta confirmation pour le faire.
2."A mon avis, le bon compromis serait de retrouver x caractères identiques mais à partir de la fin de la description (avec la restriction que si la description ne compte par exemple que 2 caractères, cela soit aussi accepté par la formule)."
Je ne suis pas sûr de pouvoir le faire sans alourdir considérablement la formule... . Tu parles bien des colonnes D de 1 TOUT et de E des colonnes E des feuilles banque ?
Daniel
Re Daniel,
1. Explication : sur la feuille banque, il y a 14 montants de 19,99 pour ADOBE et aucune référence. On peut facilement prendre la dernière ligne au lieu de la première, ce qui donnera 13/11/2024 pour ce cas. J'attends ta confirmation pour le faire.
Réponse: Comme tu le penses et pour le mieux car je remarque maintenant qu'il y a beaucoup de références vides.
Au cas où et si la référence du paiement est entièrement vide (colE), ne pas tenir compte de ce critère dans la recherche CAR
tous les paiements par Carte de crédit ou paiement à distance n'indiquent rien en référence.
2."A mon avis, le bon compromis serait de retrouver x caractères identiques mais à partir de la fin de la description (avec la restriction que si la description ne compte par exemple que 2 caractères, cela soit aussi accepté par la formule)."
Je ne suis pas sûr de pouvoir le faire sans alourdir considérablement la formule... . Tu parles bien des colonnes D de 1 TOUT et de E des colonnes E des feuilles banque ?
Réponse: oui tout-à-fait (D de 1 TOUT et de E des colonnes E)... et même 0 caractère en cas de référence vide ...
Je commence à me rendre compte maintenant de tous les paramètres à devoir tenir compte pour arriver à un résultat .optimum ..
Bien à toi,
Carine
Bonsoir Daniel,
Pour acquis non dans son entièreté je pense, car elle pourrait différer par exemple au niveau de la description si au niveau du paiement il est indiqué 123456 et qu'au niveau de l'encodage de la facture, ce soit FA123456 ...
C'est pour cela cela que j'avais pensé à x caractères identiques sur le contenu entier.
Je vois aussi lorsque c'est des paiements à distance ou via internet, la banque n' y indique pas d'élément au niveau de la description.
Je me suis dit que la combinaison des 2 recherches au niveau fournisseur et description permettrait de trouver une solution dans la plupart des cas ..
Bien à toi,
Carine
Re,
et éventuellement si c'est nécessaire, rajouter une autre colonne reprenant le libellé du fournisseur ?