Convertir NB.SI.ENS en macro

Version imprimable

12/12/2017, 15h19
LeDVLP

2 pièce(s) jointe(s)

Convertir NB.SI.ENS en macro

Bonjour,

Je vous contacte car je fais face à un problème concernant un NB.SI.ENS contenant 4 critères (dont une date) sur deux classeurs.

Le NB.SI.ENS ne fonctionne pas lorsque le classeur source est fermé.

Je souhaiterais donc trouver une solution par macro pour que cela fasse le même effet qu'un NB.SI.ENS classique.

J'ai beaucoup de difficulté, pourriez-vous m'indiquer la marche à suivre svp ?

Mes deux fichiers sont en pièces jointes. Le fichier source est AZC 2017 et le fichier destinataire est TestDate.

Dans le fichier TestDate, se trouvent les formules NB.SI.ENS que j'ai créées.

Je vous remercie par avance.
12/12/2017, 16h29
joe.levrai

Bonjour,

tu peux éventuellement, dans ta procédure VBA :

- figer le rafraichissement d'écran (Application.ScreenUpdating)
- ouvrir le classeur (Workbooks.Open())
- et là utiliser au choix
==> Application.WorksheetFunction.CountIfs()
==> la puissante fonction Evaluate(MaFormuleEnAnglais)
- refermer le classeur source
12/12/2017, 16h42
LeDVLP

Bonjour,

Je vous remercie pour votre réponse. Je vais essayer et vous tiens au courant.

Je me pose une question : Si je récupère les informations en ouvrant le classeur, est-ce que les cellules afficheront #Valeur lorsque le classeur sera fermé ou bien les valeurs seront collées ?

Dites moi si je ne suis pas clair.

Merci d'avance.
12/12/2017, 16h49
joe.levrai

Les deux mon capitaine !

si tu conserves les formules dans tes cellules (chose qui n'est pas incluse dans mes propositions) ça sera le cas sauf liaison externe à mettre à jour
si tu utilises la classe Worksheetfunction ou Evaluate (évaluation interne) tu as directement le résultat sans les formules
si tu utilises les formules excel et qu'ensuite tu écrases les formules par les valeurs, il n'y aura ni #Valeur .... ni mise à jour automatique des résultats de surcroit

Le beurre et l'argent du beurre n'existe pas chez Excel (comme partout ailleurs bien souvent). C'est le but qui définit la méthode .... et la conception qui définira le modèle

90% c'est de la réflexion, les 10% restants (la mise en place) viennent ensuite naturellement
12/12/2017, 17h05
LeDVLP

Je vous remercie,

Je vais essayer avec le Worksheetfunction !

A bientôt
12/12/2017, 19h52
eriiic

Bonjour,

Si tu veux une mise à jour dynamique Sommeprod() fonctionne sur les classeurs fermés
Et tu peux faire un équivalent de nb.si.ens() avec des tests. C'est ce qu'on utilisait avant l'arrivée de cette fonction.
eric
13/12/2017, 08h17
LeDVLP

Bonjour eriiic,

Effectivement, j'avais pensé au Sommeprod() mais il me semble qu'il n'est pas adapté à mon cas.

Merci de votre réponse
13/12/2017, 09h42
eriiic

Bonjour,

je ne vois pas quelle formule de nb.si.ens() tu ne pourrais pas faire avec sommeprod().
eric
13/12/2017, 09h59
LeDVLP

Bonjour,

D'après mes fichiers joints, je cherche à connaitre le nombre de lignes en fonction de la date (colonne B), de la série ( colonne D), de la nature (colonne J) et de la CAT, (colonne K).

Autrement dit, je dois mesurer le nombre de fois où la même date apparaît en fonction de ces 3 critères.

Je ne comprends pas comment utiliser un sommeprod dans ce cas..

Merci

Bonjour,

Un exemple tout bête à adapter selon le contexte (ne connaissant pas exactement le contexte) - Attention, ici c'est sur la même feuille.
PS : on n'ouvre quasi jamais les fichier joint pour diverses raisons … => cf. ici

RÉSULTATS

13/12/17 A B C 1 =SOMMEPROD(($A$2:$A$12=A2)*($B$2:$B$12=B2)*($C$2:$C$12=C2)*($D$2:$D$12=D2))

14/12/17 A B C 4 =SOMMEPROD(($A$2:$A$12=A3)*($B$2:$B$12=B3)*($C$2:$C$12=C3)*($D$2:$D$12=D3))

23/12/17 A B C 2 =SOMMEPROD(($A$2:$A$12=A4)*($B$2:$B$12=B4)*($C$2:$C$12=C4)*($D$2:$D$12=D4))

14/12/17 A B C 4 =SOMMEPROD(($A$2:$A$12=A5)*($B$2:$B$12=B5)*($C$2:$C$12=C5)*($D$2:$D$12=D5))

17/12/17 A B C 1 =SOMMEPROD(($A$2:$A$12=A6)*($B$2:$B$12=B6)*($C$2:$C$12=C6)*($D$2:$D$12=D6))

18/12/17 A B C 1 =SOMMEPROD(($A$2:$A$12=A7)*($B$2:$B$12=B7)*($C$2:$C$12=C7)*($D$2:$D$12=D7))

14/12/17 A B C 4 =SOMMEPROD(($A$2:$A$12=A8)*($B$2:$B$12=B8)*($C$2:$C$12=C8)*($D$2:$D$12=D8))

20/12/17 A B C 1 =SOMMEPROD(($A$2:$A$12=A9)*($B$2:$B$12=B9)*($C$2:$C$12=C9)*($D$2:$D$12=D9))

21/12/17 A B C 1 =SOMMEPROD(($A$2:$A$12=A10)*($B$2:$B$12=B10)*($C$2:$C$12=C10)*($D$2:$D$12=D10))

14/12/17 A B C 4 =SOMMEPROD(($A$2:$A$12=A11)*($B$2:$B$12=B11)*($C$2:$C$12=C11)*($D$2:$D$12=D11))

23/12/17 A B C 2 =SOMMEPROD(($A$2:$A$12=A12)*($B$2:$B$12=B12)*($C$2:$C$12=C12)*($D$2:$D$12=D12))

13/12/2017, 10h54
LeDVLP

Bonjour RyuAutodidacte,

J'ai effectivement utilisé cette méthode qui conviendrait (je m'étais mis dans la tête que le sommeprod ne pouvait pas fonctionner mais visiblement si), le problème est que soit il m'affiche vide, soit #Valeur.

Je mène des recherches pour résoudre ce problème.

Merci pour l'information concernant les fichiers joints.
13/12/2017, 11h01
RyuAutodidacte

Citation:

le problème est que soit il m'affiche vide, soit #Valeur.

es tu sûr que le ciblage dans la formule est bien fait ??
13/12/2017, 11h14
LeDVLP

Mon fichier source est AZC 2017 :

=SOMMEPROD(('AZC 2017.xlsx'!$B$3:$B$40=$I$8)*('AZC 2017.xlsx'!$D$3:$D$40=$C24)*('AZC 2017.xlsx'!$J$3:$J$40=$I$10)*('AZC 2017.xlsx'!$K$3:$K$40=$B24))

Quand je rajoute le dernier argument, le résultat est #Valeur, quand je ne le mets pas la cellule est vide.

Est-ce que la mise en forme des cellules pourrait empêcher de correctement faire fonctionner le sommeprod ?

Merci d'avance
13/12/2017, 11h32
RyuAutodidacte

Regarde bien l'écriture en classeurs ouverts, j'ai fait juste le lien sur une cellule d'un 2è classeur pour que tu puisses de rendre compte de la différence ;

Code:

=[Classeur1.xlsm]Feuil1!$K$20

On voit le nom du classeur entouré de "[]", le nom de la feuille concernée "Feuil1!" et la plage "$K$20"
je ne vois pas cela dans ta formule …
13/12/2017, 11h47
LeDVLP

Les [] et le nom de la feuille se sont ajoutés automatiquement après avoir renommée la feuille, je suppose donc que la syntaxe est correcte.

Cependant, les cellules restent vides.

=SOMMEPROD(('[AZC 2017.xlsx]Décembre'!$B$3:$B$40=$I$8)*('[AZC 2017.xlsx]Décembre'!$D$3:$D$40=$C25)*('[AZC 2017.xlsx]Décembre'!$J$3:$J$40=$I$10)*('[AZC 2017.xlsx]Décembre'!$K$3:$K$40=$B25))
13/12/2017, 14h38
LeDVLP

Re,

J'ai réussi à faire fonctionner le SommeProd mais étant donné que j'ai 124 colonnes à remplir via cette formule, cela prend un temps fou (3 minutes pour 16 colonnes !!)

Je me tourne finalement vers une macro qui pourra certainement m'éviter de perdre des heures.

Reste à écrire le code..

Merci :)
13/12/2017, 18h56
RyuAutodidacte

Re,

Si c'est pour mettre tes formules sur toute ta plage, cela peut se faire par macro, pour peu que la formule soit bien faite en amont.
Pour ce faire, il faut utiliser Range.FormulaLocal => cf aide vba - il y a aussi plein d'exemples dans les discussions du forum
14/12/2017, 08h06
LeDVLP

Bonjour,

Je vous remercie pour vos conseils :)

				RÉSULTATS
13/12/17	A	B	C	1	=SOMMEPROD(($A$2:$A$12=A2)($B$2:$B$12=B2)($C$2:$C$12=C2)*($D$2:$D$12=D2))
14/12/17	A	B	C	4	=SOMMEPROD(($A$2:$A$12=A3)($B$2:$B$12=B3)($C$2:$C$12=C3)*($D$2:$D$12=D3))
23/12/17	A	B	C	2	=SOMMEPROD(($A$2:$A$12=A4)($B$2:$B$12=B4)($C$2:$C$12=C4)*($D$2:$D$12=D4))
14/12/17	A	B	C	4	=SOMMEPROD(($A$2:$A$12=A5)($B$2:$B$12=B5)($C$2:$C$12=C5)*($D$2:$D$12=D5))
17/12/17	A	B	C	1	=SOMMEPROD(($A$2:$A$12=A6)($B$2:$B$12=B6)($C$2:$C$12=C6)*($D$2:$D$12=D6))
18/12/17	A	B	C	1	=SOMMEPROD(($A$2:$A$12=A7)($B$2:$B$12=B7)($C$2:$C$12=C7)*($D$2:$D$12=D7))
14/12/17	A	B	C	4	=SOMMEPROD(($A$2:$A$12=A8)($B$2:$B$12=B8)($C$2:$C$12=C8)*($D$2:$D$12=D8))
20/12/17	A	B	C	1	=SOMMEPROD(($A$2:$A$12=A9)($B$2:$B$12=B9)($C$2:$C$12=C9)*($D$2:$D$12=D9))
21/12/17	A	B	C	1	=SOMMEPROD(($A$2:$A$12=A10)($B$2:$B$12=B10)($C$2:$C$12=C10)*($D$2:$D$12=D10))
14/12/17	A	B	C	4	=SOMMEPROD(($A$2:$A$12=A11)($B$2:$B$12=B11)($C$2:$C$12=C11)*($D$2:$D$12=D11))
23/12/17	A	B	C	2	=SOMMEPROD(($A$2:$A$12=A12)($B$2:$B$12=B12)($C$2:$C$12=C12)*($D$2:$D$12=D12))