Formule Excel Fermé

Question

Bonjour,

Je suis un étudiant en comptabilité et pour sujet d'étude je cherche sous excel 2003 a développer une application qui permettra d'automatiser le travail du comptable, c'est à dire journal, grand livre, balance, bilan et compte de résultat. J'ai deja achever le journal mai c'est le report des données vers le grand livre qui me pose problème.En effet, je cherche a reporter les dates, les intitulés de compte et les montants respectifs en indexant les numéro de compte.
Par exemple dans le journal j ai:

A                     B                      C                           D
Date	             Compte	         Intitulé                        montant
03 déc 2005	601	          achat	                  3000000
15 déc 2005	411	           client	                  3000000
15 déc 2005	512	           banque	                  5000000
15 déc 2005	521	          caisse	                  275000
22 déc 2005	512	          banque	                  1000000
23 déc 2005	601	           achat	                  120000

je voudrai par exemple en indexant le 512 obtenir:

Date	             Compte	         Intitulé                       montant
15 déc 2005	512	           banque	                  5000000
22 déc 2005	512	          banque	                  1000000

J'ai deja essayer avec les fonctions index/equiv mais la je ne retrouve que la premiere ligne.S'il vous plait aidez moi a trouver si possible une formule ou bien une macro qui me permettrait d'effectuer cette tache.
PS: je suis encore debutant en Visual Basic

yakov · Accepted Answer

Crystal Reports est un éditeur d'états.

Autre idée pour approfondir ce que te propose LatelyGeek...

Lorsque tes filtres automatique sont activés sur ta feuille journal, que tu t'es assuré que les données sont bien triées par numéro de compte, as-tu essayé d'utiliser les sous-totaux sur chaque changement de compte... Ainsi apparaitra, en gros, ton grand livre avec les sous totaux pour chaque champ... Ensuite tu peux créer des vues qui t'affiche sur demande le journal, ou bien le grand livre...

yakov · Answer

C'est de la gestion de données ton affaire...
EXCEL n'est pas un gestionnaire de données très pratique pour ce genre de truc...
Dois-tu forcémment faire cela avec EXCEL ?
Sinon as-tu essayé de rendre le résultat sous forme de tableau croisé dynamique ?

LatelyGeek · Answer

Je dirais que le plus simple est d'utiliser le filtre.

Tu sélectionnes ta ligne d'entête, puis Données/Filtrer/Filtre automatique.

Une "flêche" apparaît à droite de toutes tes entêtes de colonne; Si tu choisis 512 avec cette flêche dans ta colonne N°Compte, tu ne visualiseras plus que tes écritures de banque.

Et si tu as besoin ensuite de créer une feuille avec ces écritures, tu te positionnes dans le tableau, tu fais Ctrl * pour tout sélectionner, tu copies, et tu colles dans une autre feuille. 

Cette manip' peut très bien être automatisée avec un peu de VB.

giorgioo · Answer

Merci LatelyGeek pour ta solution c'est assez pratique mais cela m'amènera a créer une une nouvelle feuille par chaque compte et sa c est du boulot mais malgrés tout tu peux me donner des topos pour son automatisation avec V B. Je vais essayer avec sa.
En tout cas merci car c est la premiere fois que je vois un peu de lumiere depuis le temps que suis bloquer deçu.

yakov · Answer

Bien sûr tu peux faire cela avec EXCEL et quelques lignes de prog bien senties, mais ça ne serait pas plus simple (et donc plus rapide) avec ACCESS, si bien sûr tu en disposes... Sincèrement, je suis habitué à programmer des tableaux lourds sous EXCEL et je trouve que le temps que tu y passeras ne vaut pas le résultat... Si tu tiens à utiliser EXCEL pour saisir les données, ne peux-tu pas envisager une importation sous ACCESS (manip très simple) des journaux qui te proposerait alors des vues bien senties pour les Grands livres, Balances, Comptes de Résultat et Bilan ??? En y réfléchissant, il s'agit bien là d'une présentation différente des données, et rien de plus, donc un simple rapport à imprimer différemment selon les besoins...

Évidemment cette réflexion ne vaut que si tu disposes et a des notions d'ACCESS.

Maintenant si tu veux faire cela sur EXCEL, il te suffit d'un code qui boucle sur chaque ligne de ton journal et qui reporte les données dans les feuilles correspondantes.

As-tu par ailleurs essayé avec la fonction rechercheV ?

yakov · Answer

Je continue ma réflexion....

Si tu considères que les documents comptables que tu veux obtenir sont des Vues différentes des mêmes données, peux-tu envisager simplement de créer des Rapports avec Crystal Report qui te présenterait les données pour chaque document souhaité ?

giorgioo · Answer

Je dispose de ACCESS mais je sais pas vraiment l'utiliser. Par contre  EXCEL je ne le maitrise peut être pas totalement mais je m'en sors pas mal!
J'ai déjà essayer avec rechercheV, mais dans mon cas il n 'est aussi précis que Index/equiv dans la mesure ou lui il me renvoie carrément une valeur dans la même ligne d'une colonne que la valeur que j aurai spécifié donc pour chaque nouveau journal je serai obligé de reprendre les formules alors que je veux créer un ensemble de feuille excel standard histoire d'automatiser le travail de base du comptable d'une petite structure (journal, grand livre, bilan, compte de résultat).
En utilisant index/equiv j ai un résultat s'approchant de mon but.En effet, en spécifiant mon numéro de compte recherché le résultat m'affiche la première opération correspondante a ce numéro de compte, hélas juste la première. Ainsi si je pouvais avoir la 2e, la 3e jusqu'à la N ème ce serait une bonne solution!

Et au fait, c'est quoi Crystal Report??
Merci d'avance et bon week-end !!

LatelyGeek · Answer

Voilà un court module que tu exécutes après avoir choisi ton compte, qui copie/colle les données dans une nouvelle feuille...

Sub CréationFeuilleCompte()

'
    Selection.CurrentRegion.Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste

End Sub

giorgioo · Answer

ok! je vais essayer sa.Je te fais signe du resultat.

yakov · Answer

je continue...

tu vas penser que je suis têtu lol, mais ton post m'interresse bien alors je continue à délirer là dessus...

Si j'ai bien compris ton affaire, tu saisis dans une feuille journal (ou dans des feuilles journaux) tes données comptables.

Ensuite tu veux que les Feuilles Grand Livre, Balance et Compte de Résultat soient automatiquement générées... je suis ok là dessus, ça va pas être trop compliqué à mettre en place en programmant tout cela sous VBA car ce sont juste des présentations différentes des données (et partielles pour le CR)

Mais comment vas-tu faire pour ton Bilan ???
C'est déjà autrement plus compliqué à mettre en place car là il ne faut plus présenter des données en fonction des numéros de compte mais grouper des comptes sur entêtes différentes....
Pourras-tu t'en sortir avec ton code VBA ou devras-tu passer sur autre chose ???

Je réfléchis là-dessus pour être sûr que tu ne perdras pas ton temps à mettre tous les trucs en place pour te rendre compte que ça va bloquer pour ton bilan.....

Mais je suis sur que tu as la solution...

tenace · Answer

Bonjour,
Juste une question , avec les formules matricielles, n'y aurait-il pas une solution ?
Kev

yakov · Answer

que penses tu des sous-totaux ? (mon avant dernier message)

giorgioo · Answer

Slt les amis

J'ai du m'absenter et laisser mon boulot quelques temps a cause des compos.
Je me suis inspirer de la première solution de LatelyGeek (8) et j ai crée une macro qui a partir du journal trie les comptes puis les recopie vers un espace dédié dans mon grand livre.En gros on a pour les comptes 101000; 103000 et 106000: 

sachant que:

-A1:H20000 représente tout mon journal dont les entêtes des différentes colonnes sont: Date;Compte;Libelles;Débit/Date;Compte;Libelles;Crédit

-jl_debit et jl_credit les parties de mon journal rassemblant respectivement les operations du debit et du credit
-d_cplso la zone du grand livre ou je veux rassembler les operations du debit du compte '101000
-c_cplso la zone du grand livre ou je veux rassembler les operations du credit du compte '101000
ainsi de suite




sub classe1()

Sheets("Journal").Select
    Range("A1:H20000").Select
    Selection.AutoFilter

 '101000 cplso
    Selection.AutoFilter Field:=2, Criteria1:="101000"
    Application.Goto reference:="jl_debit"
    Selection.Copy
    Sheets("Gd livre").Select
    Application.Goto reference:="d_cplso"
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Sheets("mod JL").Select
   Selection.AutoFilter Field:=2
   
    Selection.AutoFilter Field:=6, Criteria1:="101000"
    Application.Goto reference:="jl_credit"
    Selection.Copy
    Sheets("Gd liv").Select
    Application.Goto reference:="c_cplso"
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Sheets("mod JL").Select
Selection.AutoFilter Field:=6

'103000 cplpers
Selection.AutoFilter Field:=2, Criteria1:="103000"
    Application.Goto reference:="jl_debit"
    Selection.Copy
    Sheets("Gd liv").Select
    Application.Goto reference:="d_cplpers"
   Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Sheets("mod JL").Select
   Selection.AutoFilter Field:=2

 Selection.AutoFilter Field:=6, Criteria1:="103000"
    Application.Goto reference:="jl_credit"
    Selection.Copy
    Sheets("Gd liv").Select
    Application.Goto reference:="c_cplpers"
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Sheets("mod JL").Select
Selection.AutoFilter Field:=6


'106000 ecreev
Selection.AutoFilter Field:=2, Criteria1:="106000"
    Application.Goto reference:="jl_debit"
    Selection.Copy
    Sheets("Gd liv").Select
    Application.Goto reference:="d_ecreev"
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Sheets("mod JL").Select
   Selection.AutoFilter Field:=2

Selection.AutoFilter Field:=6, Criteria1:="106000"
    Application.Goto reference:="jl_credit"
    Selection.Copy
    Sheets("Gd liv").Select
    Application.Goto reference:="c_ecreev"
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Sheets("mod JL").Select
Selection.AutoFilter Field:=6
end sub

La ou sa cloche c'est que:
1- je voudrai ordonner a excel de ne pas copier au cas ou il ne trouve pas un critere
2- il arrive parfois que excel dans l'execution de la macro recopie plusieurs fois la meme entrée dans une zone je ne sais pas a quoi c'est dû!

j espere que vous comprendrai et essayerai de m'aider.merci d'avance!

LatelyGeek · Answer

Je propose une solution:

Tu rajoutes ça en début de ton code:

    Range("K1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,C[-7])"

Dans mon exemple, ça compte dans la cellule K1 le nombre de valeurs numériques contenues dans la colonne D (7 colonnes avant K - à toi d'adapter) une fois le filtre appliqué (Fonction SOUS.TOTAL)

Et ensuite, tu rajoutes à chaque fois:

Selection.AutoFilter Field:=6, Criteria1:="106000"
If Range("K1").Value <>0 then
Application.Goto reference:="jl_credit" 
.
.
. 
End If 

Pour l'autre problème, je te conseille d'exécuter ton code pas à pas avec la touche F8 pour voir où est ce que ça déraille. (Alt F11 pour afficher VBasic Editor, tu te positionnes sur la première ligne et tu tapes F8 pour chaque ligne

Formule Excel

14 réponses

Discussions similaires