Microsoft 365 RECHERCHEV() vers la gauche ou SOMMEPROD()

Aloha · 24 Janvier 2020

Bonjour,

La situation réelle:

J'ai 2 fichiers:

* un fichier qui parvient tous les mois d'une tierce partie et qui doit être rempli des chiffres H1, H2, H3: "Var_0020_Jan.xlsx" (appelé Destination par la suite)

* un fichier contenant les données: "Source.xls" dans l'exemple (l'extension du fichier réeel est bien .xls et non pas .xlsx) appelé Source par la suite

L'architecture du fichier "Source.xls", reprenant celle du fichier réel:

A à F = architecture du fichier réel (abstraction faite d'une multitude de colonnes entre celles reproduites ici; ainsi le numéro se trouve en BW)
A: Mois (format MMMM YYYY)
B: Nom (sans importance ici)
C: H1
D: H2
E: H3
F: Numéro (servant, à la place du nom, à identifier les agents)

Colonne G ajoutée dans le fichier "Source.xls", pour faire fonctionner la formule SOMMEPROD()

G: Rang du mois avec élimination des erreurs dues aux "/" séparant les unités et les mois en A, par ESTERREUR()

Ma tâche:

additionner pour chaque agent, identifié par son numéro en A dans Destination et en F dans Source, le total pour un mois donné des chiffres en H1, H2 et H3.
Si j'avais le numéro en A dans Source, je pourrais construire une fontion RECHERCHEV(), mais comme dans la situation réelle il se trouve tout à droite, ce n'est pas possible.

Destination Feuille Mois MMMM YYYY:
Les formules avec SOMMEPROD() se basant sur la colonne A dans Source pour le mois ne fonctionnent pas à cause des "/" et je ne sais pas comment en tenir compte dans ce type de formule.

Destination Feuille Rang Mois (1 à 12):
Voilà pourquoi j'ai ajouté la colonne G et les formules se basant sur cette colonne pour le mois fonctionnent.

Ma question:

est-il possible de faire les calculs avec le mois en colonne A (ma préférence puisque je veux éviter de charger Excel de formules supplémentaires; dans le fichier réel il y en a déjà une multitude), ou bien faut-il absolument la colonne G?

Si je ne me trompe il y a aussi moyen de construire une formule faisant fonction de RECHERCHEV() vers la gauche, avec INDEX() et MATCH(), mais j'ignore son fonctionnement.

Quelle est la meilleure solution pour accomplir cette tâche?

Merci d'avance pour toute aide.

Bien à vous

Aloha

CISCO · 24 Janvier 2020

Bonjour

Tu peux aller rechercher des informations sur la gauche avec INDEX(...;EQUIV(...))
Cf. un exemple en pièce jointe.

@ plus

Aloha · 24 Janvier 2020

Bonjour et merci pour cette formule.
Hélas, bien que ton exemple soit bien clair, je n'arrive pas à l'adapter à ma situation, parce qu'une variable manque: le mois.
Si j'ai bien compris le système, si je fais abstraction du mois, la formule en Destination C3 devrait être:

=INDEX('[Source.xlsx]1'!$C$2:$C$30;EQUIV(A3*1;'[Source.xlsx]1'!$F$2:$F$30;0))

qui donne 4 comme résultat, donc exact.

Cependant, je me répète, elle ne tient pas compte du mois.
Je constate que mon fichier source est mal construit, puisque 100002 n'apparaît qu'au mois 1.
Seulement, même si je rajoute ce code aussi dans les mois 2 et 3, le résultat reste toujours 4.
A+
Aloha

CISCO · 24 Janvier 2020

Bonjour

Où est-ce que tu veux mettre ta formule ?

Dans J12, tu peux faire avec

Code:

SOMMEPROD(($F$2:$F$16=J10)*SIERREUR(MOIS($A$2:$A$16)=J11;0)*($C$2:$C$16))

à valider en matriciel en Ctrl+maj+enter, à cause du SIERREUR

@ plus

Aloha · 24 Janvier 2020

Re,
dans le fichier Var_0020_Jan:
les chiffres de
H1 en C3:C30
H2 en D330
H3 en E3:E30
Le fichier Var_00_20_[janvier à décembre] est fourni tous les mois par une tierce partie et il contient en A les numéros et en B les noms et le mois en C1.
Et je dois remplir les colonnes pour H1, H2, H3.
A+
Aloha
P.S: les ":" et "D" pour H2 ont été transformés en émoticone!

CISCO · 24 Janvier 2020

Rebonjour

j'ai rajouté une formule dans mon dernier post. Essaye de la comprendre.

@ plus

Aloha · 24 Janvier 2020

Re,
J'ai su adapter cette formule à mon exemple et elle fonctionne. Merci beaucoup!
Cependant, dans mon fichier réel j'obtiens des #REF.

J'ai fait maintenant ceci:
* j'ai ouvert le fichier source réel et j'ai copié la feuille contenant les données (Database) dans un nouveau classeur auquel j'ai donné le nom du fichier réel: Base
Les colonnes de Database requises pour les besoins de cette tâche:
A: Mois
Q: H1
R: H2
S: H3
BW: numéro
* le fichier de destination est le fichier réel que j'ai anonymisé.
J'y ai mis ta formule adaptée qui donne REF.

Donc les deux fichiers reproduisent la situation réelle.
A+
Aloha

Aloha · 24 Janvier 2020

Re,
Voici ce que je veux faire après, lorsque les formules fonctionneront:
Je vais donc chercher ces données par des formules; si elles fonctionnaient, je continuerais en remplaçant par VBA les formules par les résultats affichés et le tour serait joué.

Seulement, je suppose que VBA doit permettre une solution plus "élégante" et directe, sans devoir passer par des formules.
A+
Aloha

CISCO · 24 Janvier 2020

Bonsoir

Dans Var_0020_feb!C3, essayes avec

Code:

SOMMEPROD(SIERREUR([Base.xls]Database!$BW$3:$BW$1500=--$A3;0)*SIERREUR(MOIS([Base.xls]Database!$A$3:$A$1500)=$C$1;0)*SIERREUR([Base.xls]Database!Q$3:Q$1500;0))

en matriciel.

Cela fonctionne si les deux fichiers sont ouverts.

Attention : le contenu de la colonne A dans cette feuille Var_0020_feb est au format texte, alors que celui de l'autre fichier, dans la colonne BW, est au format nombre. Il faut donc mettre --$A3 pour faire passer ce A3 du format texte au format nombre.

Si cela fonctionne, il faudra adapter cette formule pour obtenir les formules dans D3 et E3.

@ plus

Aloha · 25 Janvier 2020

Bonjour,

Merci beaucoup, cette formule fonctionne, même lorsque Base est fermé! et même si entrée par Entrée et non pas en matricilell (je n'avais pas vu "en matriciel" d'abord.
J'étais conscient que A est au format texte et j'avais modifié =A$3 en A$3*1.

Je vais maintenant essayer de faire exécuter l'insertion des formules et la copie vers le bas ainsi que le remplacement des formules par leurs valeurs par VBA.

Ce qui m'embête encore, mais ce que j'arrive à gérer (en effaçant le chemin; je préférerais savoir comment l'éviter), c'est que le chemin est ajouté à chaque fois que Base est invoqué, alors que ces deux fichiers se trouvent dans le même dossier.

Update:
après intégration de la formule dans le code VBA, la macro fait ce qu'il faut, mais les formules donnent toutes 0 comme résultat.
En vérifiant je constate que le fameux @ s'est glissé dans le formules:

VB:

=SOMMEPROD(SIERREUR(@[Base.xls]Database!$BW3:$BW$1500=--$A3;0)*SIERREUR(MOIS(@[Base.xls]Database!$A3:$A$1500)=$C$1;0)*SIERREUR(@[Base.xls]Database!R3:R$1500;0))

Si je les enlève la formule fonctionne à nouveau.

D'où ma question: comment éviter cet @?

J'ai essayé avec succès à effacer les @ par rechercher @ et remplacer par rien:

Code:

Selection.Replace What:="@", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Ce qui semble donc être une solution possible, mais il vaudrait mieux éviter dès le début que le @ se glisse dans les formules.

Bonne journée
Aloha

Aloha · 25 Janvier 2020

Re,
Je pense avoir trouvé la solution, consistant à déclarer les formules directement comme formule matricielle:

VB:

Range("C3").FormulaArray = _

et ça fonctionne!
Salutations
Aloha

CISCO · 25 Janvier 2020

Bonsoir à tous, bonsoir Aloha

Je vois que tu commences à fouiller dans les possibilités offertes par les macro VBA...

@ plus

Aloha · 25 Janvier 2020

Bonsoir,
En effet et c'est un peu l'histoire de se lancer dans l'eau froide.
J'ai enregistré une macro, puis j'ai essayé de la généraliser. Ce n'était pas évident mais en cherchant sur Internet dans toutes les langues que je maîtrise j'ai trouvé un maillon après l'autre. Certains m'ont causé dvantage de soucis que d'autres.
P.ex. pour remplacer la référence à un classeur bien déterminé dans une formule par la référence à une variable, le classeur n'étant pas toujours le même, je me suis bien battu avec les guillemets avant de les dompter.
Puis avec les fameux @ que j'ai maîtrisés (si je ne me trompe) par les formules matricielles.
Etc, etc.
Et après je me suis encore efforcé de simplifier le code.
Merci encore pour votre aide.
Je pense que je vais reprendre un autre projet pour lequel j'avais fait appel à votre aide, la gestion des dépenses d'un certain nombre de personnes. La solution trouvée fonctionne, mais je voudrais que toutes les opérations se fassent dans des masques de saisie et non pas dans les feuilles de calcul.
Bonne nuit et bon dimanche
Aloha

Microsoft 365 RECHERCHEV() vers la gauche ou SOMMEPROD()

Aloha

XLDnaute Accro

Pièces jointes

CISCO

XLDnaute Barbatruc

Pièces jointes

Aloha

XLDnaute Accro

Pièces jointes

CISCO

XLDnaute Barbatruc

Aloha

XLDnaute Accro

CISCO

XLDnaute Barbatruc

Aloha

XLDnaute Accro

Pièces jointes

Aloha

XLDnaute Accro

CISCO

XLDnaute Barbatruc

Aloha

XLDnaute Accro

Aloha

XLDnaute Accro

CISCO

XLDnaute Barbatruc

Aloha

XLDnaute Accro

Discussions similaires