nommer matrice se trouvant sur plusieurs onglets pour rechercheH

[melba]

Bonjour à tous,

Puis-je vous solliciter pour le problème suivant?

Je voudrais faire une recherche H dans un tableau étendu sur plusieurs onglets d'un même classeur mais ne sais pas comment écrire ma formule en ce qui concerne le paramètre "table matrice".

J'ai essayé de nommer ma table mais je me heurte au même problème, je n'arrive pas à nommer quand la plage se trouve sur plusieurs onglets?

Pourriez-vous m'aider?

Par avance merci

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

Je vois que mon problème vous inspire : un très grand merci à tous.
Je rentre du travail et malheureusement je n'aurai sans doute pas le temps de tester vos solutions avant samedi matin et je le regrette car j'ai hâte de voir si ça fonctionne et de vous tenir au courant.

Je reprends contact sans faute samedi matin ou si possible avant je l'espère.

Encore mille merci

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour melba, JB, le forum,

Pas de problème melba, prenez votre temps.

En fait nul besoin de concaténer les plages de toutes les feuille.

Avec la disposition des dates que j'ai proposée en ligne 22, il suffit de concaténer les mois d'octobre, novembre, décembre de l'année précédente et les mois de l'année en cours.

Toujours dans ThisWorkbook :

Private Sub Workbook_Open() 'par sécurité
If TypeOf ActiveSheet Is Worksheet Then _
  Workbook_SheetChange ActiveSheet, [E7]
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeOf Sh Is Worksheet Then Workbook_SheetChange Sh, [E7]
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.[E7:P19]) Is Nothing Then Exit Sub
Dim an%, w As Worksheet, mat, i As Byte, j As Byte
an = Year(Sh.[E7]) - 1 'année précédente
For Each w In Worksheets
  If Year(w.[E7]) = an Then Exit For
Next
ReDim mat(1 To 13, 1 To 15)
For i = 1 To 13 Step 6 'lignes 1 7 13
  If Not w Is Nothing Then
    For j = 1 To 3 'octobre novembre décembre année précédente
      mat(i, j) = w.[N7].Cells(i, j).Value2
    Next
  End If
  For j = 1 To 12 'année en cours
    mat(i, j + 3) = Sh.[E7].Cells(i, j).Value2
  Next
Next
Me.Names.Add "Matrice", mat 'nom défini
End Sub

Matrice est recalculée à l'activation de la feuille ou quand on modifie les valeurs.

J'ai aussi revu les formules en E22 et E23 :

=SI((E7>$A5)+ESTNA(INDEX(Matrice;1;COLONNE()-4));"";E7)

=SI(E22="";"";TRONQUE((RECHERCHEH(MOIS.DECALER(E7;-3);Matrice;7)+RECHERCHEH(MOIS.DECALER(E7;-2);Matrice;7)+RECHERCHEH(MOIS.DECALER(E7;-1);Matrice;7))/3+E19;2))

Fichier (4).

Edit : il y a un inconvénient avec cette version : l'analyse par formules de plusieurs feuilles n'est pas possible, en effet le nom Matrice n'est valable que pour la feuille active

A+

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Re,

Finalement une solution beaucoup plus simple par fonction VBA (dans Module1) :

Function Salaire!(P As Range, prime!)
Dim derlig&, i%, n As Byte, an%, w As Worksheet
derlig = P.Rows.Count
For i = P.Columns.Count - 1 To 1 Step -1
  n = n + 1
  Salaire = Salaire + P(derlig, i)
  If n = 3 Then GoTo 1
Next
an = Year(P(1)) - 1 'année précédente
For Each w In Worksheets
  If Year(w.Range(P(1).Address)) = an Then Exit For
Next
If w Is Nothing Then End
For i = n + 10 To 12 'complément d'octobre à décembre
  Salaire = Salaire + w.Range(P(derlig, i).Address)
Next
1 Salaire = Salaire / 3 + prime
End Function

Elle va juste chercher la feuille de l'année précédente pour prendre les mois qui manquent.

Les formules en E22 et E23 sont très simples aussi :

=SI((E7>$A5)+ESTERR(Salaire($E7:E13;E19));"";E7)

=SI(E22="";"";TRONQUE(Salaire($E7:E13;E19);2))

Fichier (5).

Edit : voir le post suivant...

A+

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour melba, le forum,

Avec la version (5), si l'on modifiait les salaires d'octobre novembre décembre, les formules en E23 F23 G23 de l'année suivante ne se mettaient pas à jour.

Il faut donc forcer le recalcul des formules en les rendant volatiles.

Donc uniquement pour la plage E23:G23 ajouter la fonction volatile ALEA() :

=SI(E22="";"";TRONQUE(Salaire($E7:E13;E19);2)+ENT(ALEA()))

Edit : et au cas où l'on ajouterait une feuille pour l'année 2008, on peut aussi modifier E22:G22 :

=SI((E7>$A5)+ESTERR(Salaire($E7:E13;E19))+ENT(ALEA());"";E7)

Fichier (6).

A+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour à tous,

Tout d'abord je vous prie de m'excuser ,j'ai dû m'absenter le week-end dernier pour des raisons familiales et ensuite compte tenu d'une semaine très chargée je ne reviens sur le forum qu'aujourd'hui.

Je vois que les idées fusent et je vais avoir du mal à suivre pauvre novice que je suis.

Je viens de regarder la proposition du post 14 car j'avais plus de chance de comprendre une formule et de la reproduire en cas de modification dans la présentation de mon fichier qu'un code VBA.

Tout va bien jusqu'à le dernier calcul pour le 01/01/2010 dans la cellule N25 dans laquelle je devrais avoir un résultat de 1150.66 et non 150.
Y aurait-il quelque chose à revoir?

Je vais essayer de comprendre la dernière proposition de JOB75 ( c'est loin d'être gagné).

Je vous remercie tous pour votre ténacité.
Vous êtes vraiment très sympas et l'on ne se sent pas abandonné.

@+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir le Forum,

J'ai regardé le post N° 19 de Job75 : les résultats correspondent tout à fait à mes attentes mais comme je l'avais dit précédemment je ne connais pas les codes ( je sais juste faire quelques macros simples) et ne pourrai malheureusement pas reproduire le sien à moins d'une "traduction".

Aussi si Job75 avait l'amabilité de me donner un décryptage je lui serais très reconnaissante.

En effet mon fichier réel est un peu différent et je dois donc modifier le code .

Je compte beaucoup sur vous une fois de plus.

@+

A défaut quelqu'un pourrait-il répondre à mon post précédent?

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour melba,

comme je l'avais dit précédemment je ne connais pas les codes ( je sais juste faire quelques macros simples) et ne pourrai malheureusement pas reproduire le sien à moins d'une "traduction".

Ma version (6) du post #19 utilise la fonction VBA Salaire, utilisée ensuite dans les feuilles de calcul.

Il n'y a pas d'adaptation de code à faire, et même il ne faut pas y toucher.

A+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

Est-ce à dire que si je copie le code tel quel dans mon fichier il fonctionnera?
Ce serait de la magie!!!?
Eh bien je viens de copier les formules dans mon fichier : je ne comprends pas du tout comment elles s'adaptent à mon fichier mais cela semble cohérent, je n'ai pas encore vérifier jusqu'au bout mais je pressens que ça ira.
Il me reste je crois à savoir comment actionner le code que je viens de placer dans un module dans visual basic.

Merci par avance pour un coup de pouce à nouveau.
@+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

Je ne sais pas comment c'est possible mais la magie vient de se produire, je viens d'avoir un résultat, je m'en vais de ce pas vérifier si ça fonctionne à tous les coups.

Si c'est le cas , c'est purement merveilleux.

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Re,

Il me reste je crois à savoir comment actionner le code que je viens de placer dans un module dans visual basic.

Oui, toute fonction VBA est à placer dans un module standard.

Et il n'y a pas à l'"actionner", le simple fait de la mettre dans une cellule exécute le code.

Et quand la formule ou elle se trouve se recalcule. Voir mes explications sur "volatile" et la fonction ALEA.

A+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

Pour moi c'est Noël avant l'heure!!!!!C'est un prodige qui vient de se réaliser...
Je ne sais comment vous dire merci.
Puis-je quand même avoir un décryptage du code?
@+

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Re,

Puis-je quand même avoir un décryptage du code?

Faites quand même l'effort de le lire...

Le code recherche les salaires du trimestre précédent (c'est ce que vous voulez), et si ce trimestre est incomplet, recherche la feuille de l'année précédente et complète le trimestre.

A+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

Encore un grand merci. Je vais relire tous les posts tranquillement : aussi bien pour le code que pour les formules.

A bientôt

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour le forum,

J'ai adopté la dernière proposition du post 19 de job75 c'est à dire le code suivant :

Function Salaire!(P As Range, prime!)
Dim derlig&, i%, n As Byte, an%, w As Worksheet
derlig = P.Rows.Count
For i = P.Columns.Count - 1 To 1 Step -1
n = n + 1
Salaire = Salaire + P(derlig, i)
If n = 3 Then GoTo 1
Next
an = Year(P(1)) - 1 'année précédente
For Each w In Worksheets
If Year(w.Range(P(1).Address)) = an Then Exit For
Next
If w Is Nothing Then End
For i = n + 10 To 12 'complément d'octobre à décembre
Salaire = Salaire + w.Range(P(derlig, i).Address)
Next
1 Salaire = Salaire / 3 + prime
End Function

Puis sur mes feuilles de calculs job 75 m'avait donné :

1)

SI(E29="";"";TRONQUE(Salaire($E14:E20;E26);2)+ENT( ALEA())) pour la 1ère cellule

2)
SI(H29="";"";TRONQUE(Salaire($E14:H20;H26);2))

Le problème concerne je pense la formule tronque.

Voilà un exemple :

la moyenne tronquée à 2 chiffres des salaires suivants :
tronque(( 1230.45 + 123.45 + 789.12);2) doit me donner 2143.02

Ensuite le résultat de la division de ce nombre par 3 doit me donner :

tronque((2143.02/2);2) = 894.79 et non 894.78.

Cela est très important dans mon fichier.

Souvent j'ai les bons résultats car cela ce joue à 0.01 euros mais quelquefois....
Serait-il possible d'y remédier?
Je ne peux pas le faire toute seule car je pense que cela dépend du code "salaire" que job75 a écrit que je ne sais pas modifier.

Je joins un fichier allégé.

Si je peux me permettre par ailleurs: j'aurai par la suite un autre problème : en fait le code fourni ainsi que les formules concernent 2 situations ( je ne l'avais pas évoqué la 1ere fois car j'avançais pas à pas) .

Si je vous indique quelles sont ces situations, le code est-il modifiable?

Et pour finir il y aurait une 3ème situation dans laquelle les calculs et donc les formules ( et peut-être aussi le code ) devraient se faire différemment.


Je joins un fichier allégé dans lequel je pointe les résultats ko et indique par ailleurs les 3 situations auxquelles je fais référence.

J'attends avec beaucoup d'impatience votre réponse


@+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

aucune réponse?
Mon exposé n'est peut-être pas assez clair?

@+