Discussion :

[Guido76]

Bonjour,
Je souhaite utiliser la fonction SousTotalSomme de Jacques Boisgontier dans un de mes classeurs.
Cette fonction est celle ci : SousTotSomme(champ As Range, champSomme As Range)

Lorsque je dépasse 52 cellules dans le champ , j'ai une erreur #VALEUR! dans toutes les cellules.
Je précise que je colle cette fonction dans une zone couvrant 20 lignes et 2 colonnes, alors qu'il n'y a que 13 valeurs différentes dans champ. Donc, il y a la place pour récupérer toutes les valeurs.
Par ailleurs, dans le champsomme, toutes les valeurs sont numériques et correctes, et avec les premières 52 cellules, toutes les valeurs de champ (13) sont déjà présentes.

Pour éviter d'aller chercher le classeur correspondant, je remets ci dessous le code de cette fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Function SousTotSomme(champ As Range, champSomme As Range)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ
  b = champSomme
  For i = LBound(a) To UBound(a)
    clé = a(i, 1)
    d(clé) = d(clé) + b(i, 1)
  Next i
  Dim temp()
  ReDim temp(1 To Application.Caller.Rows.Count, 1 To 2)
  i = 1
  For Each c In d.keys
    temp(i, 1) = c
    temp(i, 2) = d(c)
    i = i + 1
  Next
  Call tri(temp, 1, d.Count)
  SousTotSomme = temp
End Function
 
Sub tri(a, gauc, droi)          ' Quick sort
 ref = a((gauc + droi) \ 2, 1)
 g = gauc: d = droi
 Do
     Do While a(g, 1) < ref: g = g + 1: Loop
     Do While ref < a(d, 1): d = d - 1: Loop
     If g <= d Then
       temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
       temp = a(g, 2): a(g, 2) = a(d, 2): a(d, 2) = temp
       g = g + 1: d = d - 1
     End If
 Loop While g <= d
 If g < droi Then Call tri(a, g, droi)
 If gauc < d Then Call tri(a, gauc, d)
End Sub

Question subsidiaire .... Comme éviter l'affichage de zéro dans les cellules excédentaires ? (20 lignes mais seulement 13 lignes utilisées pour le moment) : avec un formatage personnalisé des cellules peut être ?

[kiki29]

Salut, pose ta question directement sur son site

P.-S. : Il est souhaitable de baliser son code

[Guido76]

Ok, merci.
Comme j'avais vu M Boisgontier intervenir ici, j'avais pensé que ... car en fait je ne vois pas comment le contacter sur son site !!! ni même ailleurs.
Donc, si quelqu’un peut regarder ce code pour me donner au moins une piste ,

NB : J'ai noté (et corrigé) pour la balise de"code"

[boisgontierjacques]

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Function SousTotSomme(champ As Range, champSomme As Range)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ
  b = champSomme
  For i = LBound(a) To UBound(a)
    clé = a(i, 1)
    If clé <> "" Then d(clé) = d(clé) + b(i, 1)
  Next i
  Dim temp()
  If d.Count > Application.Caller.Rows.Count Then SousTotSomme = "Pas assez de lignes électionées!": Exit Function
  ReDim temp(1 To Application.Caller.Rows.Count, 1 To 2)
  i = 1
  For Each c In d.keys
    temp(i, 1) = c
    temp(i, 2) = d(c)
    i = i + 1
  Next
  Tri temp, 1, d.Count
  SousTotSomme = temp
End Function

Pour masquer les zéros, Fichier/Options/Fonctions avancées/Afficher un zéro dans les cellules vides





Boisgontier

[Guido76]

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Function SousTotSomme(champ As Range, champSomme As Range)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ
  b = champSomme
  For i = LBound(a) To UBound(a)
    clé = a(i, 1)
    If clé <> "" Then d(clé) = d(clé) + b(i, 1)
  Next i
  Dim temp()
  If d.Count > Application.Caller.Rows.Count Then SousTotSomme = "Pas assez de lignes électionées!": Exit Function
  ReDim temp(1 To Application.Caller.Rows.Count, 1 To 2)
  i = 1
  For Each c In d.keys
    temp(i, 1) = c
    temp(i, 2) = d(c)
    i = i + 1
  Next
  Tri temp, 1, d.Count
  SousTotSomme = temp
End Function

Pour masquer les zéros, Fichier/Options/Fonctions avancées/Afficher un zéro dans les cellules vides





Boisgontier
http://boisgontierjacques.free.fr

Bonsir,
Si j'ai bien compris le code modifié comporte :
- une vérification des lignes vides. A priori, il n'y avait pas de problème avec celà, mais c'est mieux.
- une vérification sur la variable dcount, mais celà ne résout pas le problème.
D'ailleurs, le problème ne se situe pas là, puisque comme je l'ai dit, je sélectionne 20 lignes alors que le résultat attendu ne comporte que 13 lignes.
Si je prends ce nouveau code, au delà de 52 lignes sélectionnées dans les deux champs, j'ai toujours l'erreur.
Je joins un classeur en exemple.
(Merci pour votre réponse)

[boisgontierjacques]

Il devait y avoir une valeur non numérique en colonne B (ligne 53)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
Function SousTotSomme(champ As Range, champSomme As Range)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ
  b = champSomme
  For i = LBound(a) To UBound(a)
    clé = a(i, 1)
    On Error Resume Next
    If clé <> "" Then
       If IsNumeric(b(i, 1)) Then d(clé) = d(clé) + b(i, 1)
    End If
  Next i
  Dim temp()
  If d.Count > Application.Caller.Rows.Count Then SousTotSomme = "Pas assez de lignes sélectionées!": Exit Function
  ReDim temp(1 To Application.Caller.Rows.Count, 1 To 2)
  i = 1
  For Each c In d.keys
    temp(i, 1) = c
    temp(i, 2) = d(c)
    i = i + 1
  Next
  Tri temp, 1, d.Count
  SousTotSomme = temp
End Function

Boisgontier

[Guido76]

Il devait y avoir une valeur non numérique en colonne B (ligne 53)

Boisgontier

Bonjour,
Je vais essayer de regarder cela de près mais je confirme que j'avais évidemment regardé qu'en ligne 53 il y a bien une valeur en format nombre. Or, la ligne 53 est vide depuis le début, et même mieux : si on la supprime, cela ne règle pas le problème.
Reprenez le classeur que j'avais donné en exemple si vous voulez vérifier, mais sauf erreur de ma part toujours possible, ce n'est pas le contenu de la ligne 53 et suivante qui pose problème.
Je vais creuser un peu votre classeur corrigé pour essayer de comprendre, et reviendrait ensuite sur ce fil.
Merci en tout cas !
cdlt,

[boisgontierjacques]

Bonjour,

> la ligne 53 est vide depuis le début,

-La fonction =ESTVIDE(B53) renvoie Faux (=ESTVIDE(F3) renvoie VRAI)
-La fonction =CNUM(B53) renvoie #Valeur) (=CNUM(F3) renvoie 0)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
Function SousTotSomme(champ As Range, champSomme As Range)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  a = champ
  b = champSomme
  For i = LBound(a) To UBound(a)
    clé = a(i, 1)
    On Error Resume Next
    If clé <> "" Then
       If IsNumeric(b(i, 1)) Then d(clé) = d(clé) + b(i, 1)
    End If
  Next i
  Dim temp()
  If d.Count > Application.Caller.Rows.Count Then SousTotSomme = "Pas assez de lignes sélectionées!": Exit Function
  ReDim temp(1 To Application.Caller.Rows.Count, 1 To 2)
  i = 1
  For Each c In d.keys
    temp(i, 1) = c
    temp(i, 2) = d(c)
    i = i + 1
  Next
  Tri temp, 1, d.Count
  SousTotSomme = temp
End Function

Boisgontier

[Guido76]

-La fonction =ESTVIDE(B53) renvoie Faux (=ESTVIDE(F3) renvoie VRAI)
-La fonction =CNUM(B53) renvoie #Valeur) (=CNUM(F3) renvoie 0)

eh, ma foi, ... c'est exact !! Et pourtant on ne voit strictement rien dans cette cellule !!
Est ce que cela peut provenir de l’utilisation d'une fonction comme celle ci ? : =SI(G10<0;G10;"")
car je récupère le contenu de ces cellules avec ce genre de fonction, mais après , je fais un copier / coller de la valeur. Je pensais donc que le résultat donnait une cellule vide quand la fonction SI telle que je l'ai écrite, est fausse ?
Peut être devrais-je mettre =SI(G10<0;G10;0) dans ce cas ? En fait je cherche à récupérer dans deux colonnes distinctes depuis une seule colonne contenant des chiffres, les valeurs positives dans l'une, les négatives dans l'autre.


EDIT 1 : le plus curieux, c'est que les autres cellules "vides" ... ne le sont pas non plus ... et pourtant elles ne provoquent pas d'erreurs !

EDIT 2 : encore plus bizarre, sans rien changer dans le tableau de valeurs initiales, j'ai ré-écrit les formules de somme, et ca fonctionne correctement avec le code modifié. Tant mieux, mais c'est frustrant de ne pas avoir encore compris d’où venait cette erreur.

[Pierre Fauconnier]

Salut.

Au delà de l'utilisation de la fonction perso, que cherches-tu à réaliser (en français)?

[Guido76]

Salut.

Au delà de l'utilisation de la fonction perso, que cherches-tu à réaliser (en français)?

Je viens de voir votre message alors que je rédigeais la correction de mon message précédent.
Je souhaite me faire un classeur perso de gestion de mes comptes bancaires, n'ayant rien trouvé de satisfaisant sur le web. (et pourtant, ca ne manque pas !!).

[Pierre Fauconnier]

Ok, mais je parlais surtout du calcul du sous-total.

Peux-dire comment tu organises ton classeur, où calculer le sous-total?

Normalement, une bonne conception du classeur, pour ce genre de traitement, ne requiert pas l'utilisation de fonctions personnalisées, Excel exposant déjà pas mal de techniques intrinsèques pour s'en tirer dans 99% des cas.

[Guido76]

Ok, mais je parlais surtout du calcul du sous-total.

Peux-dire comment tu organises ton classeur, où calculer le sous-total?

Normalement, une bonne conception du classeur, pour ce genre de traitement, ne requiert pas l'utilisation de fonctions personnalisées, Excel exposant déjà pas mal de techniques intrinsèques pour s'en tirer dans 99% des cas.

J'ai un classeur qui comprend :
- une feuille ou j'ai listé les différents comptes, avec les entrées/sorties globales. Donc, une vue globale de l'état des comptes.

- une feuille Ecritures, ou je reporte le détail des mouvements.
Pour le moment, c'est encore assez "fruste" au niveau de la récupération des données, car je vais simplement sur mon compte en ligne, je fais un "copier" des mouvements et un "coller" dans une feuille temporaire ou justement je transforme ces données brutes. Je parle des chiffres, les libellés étant déjà directement utilisables tels quels, mais par contre les chiffres obtenus par cette opération sont en fait sous forme de texte, je les transforme donc en chiffres et je les répartis en deux colonnes Crédit / Débit.
(J'aimerai plus tard récupérer directement les informations en ligne depuis mon compte bancaire internet.)
Ensuite un copier/coller me permet de les mettre dans la feuille Ecritures.
Je complète cette feuille Ecritures en qualifiant chaque opération via des listes cascade pour leur affecter une Catégorie > une Rubrique > et un Regroupement. Par exemple : Abonnements et communication > Portable Véronique > Téléphone & Internet.

- une feuille Bilan ou je veux récupérer ces trois colonnes Catégorie/Rubriques/Regroupement avec en correspondance le sous total Crédit et Débit pour chaque libellé. C'est là ou je voulais utiliser cette fonction de sous total. Mais je me viens de voir que je devais "doubler" la colonne des libellés qui ne sont pas forcément identiques pour les colonnes Crédit / Débit.
La formule utilisée est du genre : =SousTotSomme(Ecritures[Rubrique];Ecritures[Débit]) en matricielle.
Ca donne quelque chose comme celà :


Il faudra que je nettoie un peu ce tableau en ne faisant pas apparaître les libellés sans Crédit / Débit affecté. Par exemple Electricité ne devrait pas apparaître dans la partie Crédit. (EDF remboursant rarement ses abonnés ...)

[Pierre Fauconnier]

Perso, je ferais un tableau à trois colonnes (Libellé, Date, Montant) avec montant positif pour les rentrées et montant négatif pour les dépenses.

Après, ok pour qualifier avec catégories, sous-catégories par listes déroulantes puis tableau croisé dynamique pour analyser sans aucune formule avec regroupement sur les mois, les années, les catégories, ...

A mon avis, bien plus simple que l'utilisation de fonctions persos.

[Guido76]

Perso, je ferais un tableau à trois colonnes (Libellé, Date, Montant) avec montant positif pour les rentrées et montant négatif pour les dépenses.

Après, ok pour qualifier avec catégories, sous-catégories par listes déroulantes puis tableau croisé dynamique pour analyser sans aucune formule avec regroupement sur les mois, les années, les catégories, ...

A mon avis, bien plus simple que l'utilisation de fonctions persos.

Oui, c'est certain. Pourquoi faire simple quand on peut faire compliqué ...
En fait, j'ai fait des classeurs autrement complexes y compris avec des TCD lorsque je bossais encore, mais c'était ... il y a déjà plusieurs années. Je faisais alors la gestion production et stocks et calcul des réappros ainsi que la gestion des résultats des commerciaux, et sans me vanter, c'était plutôt pas mal. Mais je n'avais jamais replongé dans Excel (ou si peu) depuis. J'ai un peu perdu la main !
Je vais remettre tout ca sur le métier pour simplifier un peu.
Est ce compliqué de mettre en place une récupération direct des données bancaires ? (que je mettrais en place une fois mon classeur calé)

[Pierre Fauconnier]

Pour ce qui est de la récupération des données bancaires en "automatique", je ne sais pas te répondre car cela dépend des banques.

En Belgique, il y a une norme d'export de données bancaires qui permettent des injections directes dans les logiciels de comptabilité. Je suppose que cela existe aussi en France. Il faut se renseigner auprès de sa banque et le service est peut-être payant. Je n'en sais rien.

[Guido76]

Pour ce qui est de la récupération des données bancaires en "automatique", je ne sais pas te répondre car cela dépend des banques.

Bonjour,
peut être une solution ici : https://www.developpez.net/forums/d1...-bancaire-ofx/

Sinon, je m'interroge toujours sur cette histoire de cellule "vide" pas vide. Que peut elle contenir qui ne soit pas du tout visible ? un caractère de contrôle ?
Et dans ce cas, comment a t'il pu apparaître lors de cette opération :
nombre sous forme texte > transformé en chiffre via ta fonction "Fonction numérique Chaine2" que j'ai adaptée pour capter le signe négatif > puis copier / coller de la valeur de la cellule.

[Pierre Fauconnier]

Les données des colonnes A et B ont été importées d'une autre application ou ont été saisies manuellement?

Si on regarde en VBA le type de valeurs des cellules, on a ceci, qui prouve que B53 n'est pas vide mais contient une chaine de 0 caractères.

[Guido76]

Les données des colonnes A et B ont été importées d'une autre application ou ont été saisies manuellement?

Copié depuis le site de ma banque ( sur la page web qui liste les différentes opérations sur le compte bancaire ) et collé dans la feuille. Mais une chaîne de zéro caractère ... j'ai du mal à imaginer ce que c'est ... à part du vide. C'est un peu comme le contenu des trous de gruyère, non ? .. bah, non, même pas, au moins eux contiennent de l'air !

Depuis je me suis re-penché sur la question :
J'ai d'abord pensé qu'il était préférable de télécharger un fichier .slk ou .xls proposé par le site. Mais dans ce cas,
- on récupère tout un tas d'infos qui ne me sont pas utiles car je reçois la version papier par la suite,
- et ca me donne des cellules contenant des données affligées de retour de lignes incohérents du genre :

Allianz I A R D
Prelevmnt
/allianz 0218053167752106 01t41 Nia
1ma20151200221141106900 Fr20zzz1075
14

Finalement, c'est le fichier .csv qui est le plus facile à exploiter car très propre, même si je retrouve cette fois directement deux colonnes Crédit/débit. Ce sera donc ma nouvelle solution d'import. En créant une macro pour enregistrer toutes les manips, je reformaterai en un clic pour les coller dans ma feuille Ecritures.

EDIT : eh, non, en fin de compte, le .csv n'est pas plus propre, il contient les même valeurs que les autres fichiers, j'ai été trompé par un format de cellule avec retour automatique à la ligne qui ne laissait apparaître que la première ligne de la cellule. (par Allianz I.A.R.D seulement pour l'exemple ci dessus).
Le fichier .ofx est par contre intéressant mais pas facile à exploiter.
IL faut simplement que j'arrive à ventiler sur plusieurs colonnes des informations qui sont sur une seule colonne du genre :
<STMTTRN>
<TRNTYPE>OTHER
<DTPOSTED>20180209
<TRNAMT>-109.14
<FITID>5067690008551
<NAME>MAAF ASSURANCES SA
<MEMO>PRELEVMNT
</STMTTRN>

ce schéma est identique pour toutes les opérations bancaires successives. Seules les infos en gras m'intéressent.

[Marc-L]

Bonjour,

si tu décides de lire les lignes une par une, voici un exemple de traitement de caractères en VBA
(voir l'aide VBA interne tout comme le tutoriel à disposition sur ce forum …) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub Demo4Noob1()
    Const C = "<DTPOSTED>20180209"
    If C Like "<DTPOSTED>?*" Then MsgBox Split(C, "<DTPOSTED>")(1)
End Sub

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …