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

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour melba,

Il suffit de 3 SI imbriquées :

=SI(ESTNUM(RECHERCHEH(A4;A10:L16;7;0));RECHERCHEH(A4;A10:L16;7;0);SI(ESTNUM(RECHERCHEH(A4;'2'!A10:L16;7;0));RECHERCHEH(A4;'2'!A10:L16;7;0);SI(ESTNUM(RECHERCHEH(A4;'3'!A10:L16;7;0));RECHERCHEH(A4;'3'!A10:L16;7;0);"")))

Et revoyez les dates en feuille '2'.

A+

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Re,

Avec NB.SI c'est plus léger :

=SI(NB.SI(A10:L16;A4);RECHERCHEH(A4;A10:L16;7;0);SI(NB.SI('2'!A10:L16;A4);RECHERCHEH(A4;'2'!A10:L16;7;0);SI(NB.SI('3'!A10:L16;A4);RECHERCHEH(A4;'3'!A10:L16;7;0);"")))

A+

 

[BOISGONTIER]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,


=SOMMEPROD(SOMME.SI(INDIRECT(LIGNE(1:3)&"!A10:L10");$A$4;INDIRECT(LIGNE(1:3)&"!A16:L16")))

Formules Matricielles 3D

JB

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour JB,

Bien vu avec les feuilles nommées 1 2 3, évidemment il ne faudra pas modifier ces noms.

A+

 

[BOISGONTIER]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

+ simple avec des noms de feuilles qq (on peut ajouter des feuilles)


=SOMMEPROD(SOMME.SI(INDIRECT(nf&"!A10:L10");$A$4;INDIRECT(nf&"!A16:L16")))

Nom de champ nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

NB; Si on veut que la recherche s'effectue seulement sur certaines feuilles, créer un champ avec les noms des feuilles et nommer ce champ nf.

JB

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

Un très grand merci pour vos contributions.

Je vais essayer d'insérer vos propositions dans mon fichier réel et vous tiens au courant.

@+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonsoir,

Je n'ai pas réussi à appliquer vos propositions dans mon fichier car en plus du problème de matrice se trouvant sur plusieurs onglets je me heurte à un problème de sommes à additionner se trouvant dans la matrice mais avec un décalage par rapport à ma date de référence:

en bref je veux additionner des résultats se trouvant dans la matrice E719 sur une ligne 7 pour les 3 mois précédant une date de certaine date, diviser ce résultat par 3, ajouter un montant se trouvant toujours dans la matrice mais cette fois ci en ligne 13 toujours pour la même date. Le résultat doit être tronqué à 2 chiffres.

Mon fichier sera peut-être plus clair .

Merci par avance pour votre aide, j'en ai vraiment besoin.

@+

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour melba, le forum,

Joli problème ma foi, le post #8 mérite un Like.

Il "suffit" de remplacer dans la formule la plage $E$7:$P$19 par la matrice résultant de la concaténation des plages E719 de toutes les feuilles.

D'où cette macro dans ThisWorkbook :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.[E7:P19]) Is Nothing Then Exit Sub
Dim mat, n&, tablo, decal&, i As Byte, j As Byte
ReDim mat(1 To 13, 1 To 12 * Worksheets.Count)
For n = 1 To Worksheets.Count
  tablo = Worksheets(n).[E7:P19].Value2 'Value2 à cause des dates
  decal = 12 * (n - 1)
  For i = 1 To 13 Step 6 'lignes 1 7 13
    For j = 1 To 12
      mat(i, j + decal) = tablo(i, j)
    Next
  Next
Next
Me.Names.Add "Matrice", mat 'nom défini
End Sub

Le nom Matrice est redéfini chaque fois qu'une des plages E719 est modifiée.

Fichier joint.

A+

 

[melba]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour,

Merci à Job75, je viens de regarder le fichier mais je vois pas le résultat : que dois-je faire pour activer le code?

Par ailleurs ne maîtrisant pas du tout les codes , je pourrais-je pas arriver au même résultat par le biais d'une formule que je finirai peut-être par comprendre et reproduire?

Merci par avance de vos suggestions

@+

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Re,

La concaténation de plages sur des feuilles différentes ne peut se faire que par VBA, pas par formules Excel.

Par ailleurs j'ai bien dit que le nom Matrice est redéfini (recalculé) chaque fois qu'une plage E719 est modifiée : sélectionnez par exemple la cellule E9 puis touche <Suppr>.

Enfin la présentation du fichier (2) joint me paraît beaucoup plus logique et plus simple avec ces formules en E22 et E23 :

=SI((E7<=$A5)*ESTNUM(RECHERCHEH(MOIS.DECALER(E7;-3);Matrice;1;0));E7;"")

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

Nota : dans chaque fonction RECHERCHEH il vaut mieux préciser le 4ème argument 0, pour le cas où les feuilles ne seraient pas classées dans l'ordre.

A+

 

[BOISGONTIER]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour,

Sans VBA cf pj

JB

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Bonjour JB,

Effectivement avec cette solution tu évites de concaténer les plages, bravo.

A+

 

[BOISGONTIER]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Un peu + simple

=SOMME(DECALER($E$7;6;EQUIV(E22;INDIRECT(E24&"!E77");0)-4;;3))/3+RECHERCHEH(E22;INDIRECT(E24&"!E7:$P19");13)

ou tout intégré ( sans la ligne 24)

=SOMME(DECALER($E$7;6;EQUIV(E22;INDIRECT(INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!E77");E22)>0);0))&"!E77");0)-4;;3))/3+RECHERCHEH(E22;INDIRECT(INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!E77");E22)>0);0))&"!E7:$P19");13)
Valider avec maj+ctrl+entrée

JB

 

[job75]

Re : nommer matrice se trouvant sur plusieurs onglets pour rechercheH

Re,

J'avais oublié que la matrice Matrice ne peut pas avoir plus de 256 colonnes, c'est à dire stocker des informations au delà de 21 ans et 4 mois.

Si nécessaire on peut y remédier en la transposant :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.[E7:P19]) Is Nothing Then Exit Sub
Dim mat, n&, tablo, decal&, i As Byte, j As Byte
ReDim mat(1 To 12 * Worksheets.Count, 1 To 13) 'matrice transposée
For n = 1 To Worksheets.Count
  tablo = Worksheets(n).[E7:P19].Value2 'Value2 à cause des dates
  decal = 12 * (n - 1)
  For i = 1 To 13 Step 6 'lignes 1 7 13
    For j = 1 To 12
      mat(j + decal, i) = tablo(i, j)
    Next
  Next
Next
Me.Names.Add "Matrice", mat 'nom défini
End Sub

Dans les feuilles de calcul il faut alors remplacer RECHERCHEH par RECHERCHEV.

Fichier (3).

Edit : les 256 colonnes c'est jusqu'à Excel 2003, à partir d'Excel 2007 pas de problème jusqu'à 16384 colonnes.

A+