Discussion :

[luca.donati]

Bonjour, tout le monde.

J’essaye de me construire en VBA Excel (2010) une fonction personnalisée qui me permette de découvrir s’il existe dans une feuille de mon fichier autre que la courante, une ligne contenant deux ou trois cellules identiques à celles que j’indique en argument.
C’est tordu, comme explication, je vais faire un exemple. Liste de mouvements bancaire dans la feuille ‘2012’! Dans la feuille courante, par contre, liste de mouvement provenant d’autre source, et contenant peut-être des différences avec l’autre liste. Champs occupés : 4 (date, description, montant débit, montant crédit) ; dans une colonne un peu plus loin, je mets ma fonction, qui a une syntaxe du genre =MaCompDB(feuille; cellule1; cellule2; cellule3). La feuille que j’indique dans la fonction est celle dans laquelle il faut chercher le « doublon », tandis que les cellules sont celles de la feuille courante, dont je cherche les valeurs.

En d’autres termes:

col A B C D E
ligne
…
5 5/6/2012 chèque untel 123,00 = MaCompDB (‘2012!’;A5;B5;C5)

La fonction doit répondre à la question : combien de cellules trouves-tu dans l’autre feuille et sur une même ligne, contenant les mêmes valeurs que les cellules que je t’indique ?

Or, depuis que j’ai découvert que la commande Find restitue une adresse, c’est tout un monde qui s’est ouvert à moi ! Du coup, je peux en extraire un numéro de ligne et de colonne…
J'ai beaucoup cherché dans les forums. Parmi les différentes sources, celle-ci me semble excellente : www.cpearson.com/excel/rangefind.htm. Dommage que je comprenne si peu l’anglais !

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
Function MaCompDB (Fgl As Worksheet, cella1 As Range, cella2 As Range, Optional cella3 As Range)
MaCompDB = 0
Dim risp As Range, c1 As String
c1 = cella1.Column 'résulte sous forme de numéro
v1 = cella1.Value
c2 = cella2.Column
v2 = cella2.Value
If Not cella3 Is Nothing Then
    c3 = cella3.Column
    v3 = cella3.Value
End If
With Fgl.UsedRange
    Set risp = .Find(v1, LookIn:=xlValues) 'Comment limiter cette recherche à la colonne indiquée dans la variable c1 ?
    Fadd = risp.Address
    If Not risp Is Nothing Then
        r = risp.Row
        MaCompDB = MaCompDB + 1
    Else
        Exit Function
    End If
If Cells(r, c2).Value = v2 Then MaCompDB = MaCompDB + 1
If Not cella3 Is Nothing Then
    If Cells(r, c3).Value = v3 Then MioConfrDB = MioConfrDB + 1
End If
End With
End Function

Bien évidemment, ça ne marche pas! (Pendant l'écriture de la fonction dans la cellule, il me dit que la syntaxe n'est pas bonne, comme si j'oubliais une parenthèse).
Quelqu’un peut m’aider ?
Merci, à bientôt !

[mercatog]

J'utiliserai SOMMEPROD (ou bien NB.SI.ENS pour excel 2007 et ultérieur)

[Philippe Tulliez]

Bonjour,
Je ne peux qu'abonder dans le même sens que Mercatog, j'utiliserais la formule SOMMEPROD.

[luca.donati]

Merci Mercatog et Corona de vos réponses. Il existe donc déjà une fonction qui peut faire cela.
J'ai tout de même encore un souci: mon Excel ne parle plus français!
Je vous assure que je n'ai pas trop bu, c'est vraiment le cas: j'avais un Excel en français, menus en français, formules en français et tout jusqu'à il y a 6 mois; mais ensuite nous avons changé d'ordinateurs au bureau, maintenant tout est en italien. Je connais par coeur le nom de certaines formules dans les deux langues, mais celle-ci c'est la première fois que j'en entends parler; j'ai cherché dans la liste et je ne trouve pas un équivalent.
Sauriez-vous me dire comment s'appelle en anglais cette fonction Sommeprod, de façon que je puisse la chercher sur un tableau d'équivalences?
Merci encore.

[gFZT82]

Bonjour,

=SUMPRODUCT( .. ) =MATR.SOMMA.PRODOTTO( .. )

Cordialement.

[luca.donati]

Effectivement, gFZT82, merci. D'ailleurs, SUMPRODUCT c'était déjà de l'anglais, je l'ai réalisé plus tard.
Seulement, cette fonction ne fait pas du tout ce dont j'ai besoin, puisqu'elle traite seulement les données numériques. Ou alors il y a une façon particulière de l'utiliser...

[mercatog]

Avec Sommeprod peut aussi compter

Code :

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

=SOMMEPROD((A5:A11="Normal")*(C5:C11>0.5)*1)

Compte le nombre de lignes où en A il y a le texte "Normal" et en C la valeur >0.5

[gFZT82]

Si cela te parait plus simple, tu peux utiliser NB.SI.ENS

Code :

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

CONTA.PIÙ.SE

Cordialement.

[luca.donati]

Merci pour toutes ces précisions.
J'ai fait quelques tentatives, avec des résultats incertains (parfois c'est bon, parfois non).

D’abord, donc, un grand merci, puisque vos suggestions sont concluantes.
J’observe les choses suivantes :
- La fonction CONTA.PIù.SE (NB.SI.ENS) est adaptée tant qu’elle ne se réfère pas à des champs contenant du texte ou vides ; dans ces cas, elle retourne 0 même si l’occurrence existe. Elle fonctionne bien pour dates et nombres existant.
- La fonction MATR.SOMMA.PRODOTTO (SOMMEPROD) est plus adaptée à mon cas, puisque j’ai des champs contenant des données de nature variée (dates, textes, nombres, champs vides).
Or, je me trouve devant une difficulté supplémentaire, parce que MATR.SOMMA.PRODOTTO cherche une correspondance exacte entre cellules (c’est normal…). Mais dans mes champs de texte, malheureusement, il y a un espace « flottant » aléatoire à la fin ; c’est-à-dire que je peux avoir une cellule identique dans les deux bases de données («chèque 01234») ou bien un espace final dans une seule des deux bases («chèque 01234 ») et pas dans l’autre, ce qui fait que l’occurrence existe mais elle n’est pas trouvée.
J’ai donc essayé de ruser en introduisant une recherche sur une partie de la cellule.
La syntaxe initiale :

Code :

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

=MATR.SOMMA.PRODOTTO(('2012'!B:B=ttt!B15)*('2012'!C:C=ttt!C15)*('2012'!D:D=ttt!D15)*('2012'!E:E=ttt!E15)*1)

est donc devenue:

Code :

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

=MATR.SOMMA.PRODOTTO(('2012'!B:B=ttt!B16)*('2012'!C:C=SINISTRA(ttt!C16;LUNGHEZZA(ttt!C16)-1))*('2012'!D:D=ttt!D16)*('2012'!E:E=ttt!E16)*1)

Pour mémoire:
matr.somma.prodotto = SOMMEPROD
sinistra = gauche
lunghezza = NBCAR
Bien évidemment, cela ne marche toujours pas, et j’en voit même la raison : il faudrait que je puisse définir pour la plage de recherche aussi ('2012'!B:B, par ex.) une limitation au premiers X caractères.
Quelqu’un sait comment faire cette limitation ?

[Philippe Tulliez]

Bonjour,
Si tu veux enlever tous les espaces d'une chaîne de caractères, tu peux utiliser la formule de la catégorie Texte, SUBSTITUE dont je ne connais malheureusement pas sa traduction italienne.
Petit exemple ici avec SOMMEPROD

Code :

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

=SOMMEPROD((SUBSTITUE($A$2:$A$5;" ";"")="Chèque1234")*$B$2:$B$5)

[EDIT]
Si tu ne veux enlever que les espaces avant et après la chaîne, c'est la formule SUPPRESPACE, toujours de la catégorie Texte

Code :

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

=SOMMEPROD((SUPPRESPACE($A$2:$A$5)="Chèque 1234")*$B$2:$B$5)

[luca.donati]

Corona, sei un mago!
Regarde comment j'ai résolu (je n'ai vu qu'après ta modif avec supprespace, mais je la note pour un'éventualité future):

Code :

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

=MATR.SOMMA.PRODOTTO(('2012'!B:B=ttt!B15)*(SOSTITUISCI('2012'!C:C;" ";"")=SOSTITUISCI(ttt!C15;" ";""))*('2012'!D:D=ttt!D15)*('2012'!E:E=ttt!E15)*1)

... et ça marche! funziona!
Grazie mille!

Désormais juste pour une curiosité générale: saurais-tu me dire comment appliquer une limitation de caractères sur une plage cible de recherche? Si j'écris
...GAUCHE(B:B;NBCAR(B:B)-1), cela ne marche pas...
Merci encore et après je clique résolu et c'est tout!

[Philippe Tulliez]

Bonjour,
Ce code fonctionne parfaitement

Code :

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

=SOMMEPROD( (GAUCHE(A2:A5;NBCAR(A2:A5)-1)="Chèque 1234") * B2:B5)

Cependant le code que tu suggères fait référence à une colonne entière ce qui forcément va faire référence à des cellules vides donc NBCAR(A2000)-1 va donner -1 et gauche (A2000,-1) va renvoyer un message d'erreur #VALEUR!
Au même titre que si tu tapes cette formule et que tu utilises la poignée de recopie. Une valeur d'erreur #VALEUR! va apparaître dès qu'une cellule sera vide.

Code :

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

=GAUCHE(A2;NBCAR(A2)-1)

[luca.donati]

Je comprends beaucoup mieux, maintenant. Merci de tout et à bientôt.