Comment récupérer des données dans un tableau à 2 entrées

[aurelop]

Bonsoir,

je suis actuellement en stage dans une entreprise qui fait de la production, et j'ai un problème pour récupérer des données dans un tableau à 2 entrées:

sur la première ligne sont indiqués les numéros de machines, sur la première colonne les références d'articles, sur la deuxième colonne les secteurs de productions, et à l'intersection d'une machine et d'un article se trouve le temps de fabrication.

Secteur---Ref ------ Machine1--Machine2--Machine3--Machine4 . . .
Nord-----Article1--------5---------------------3
Sud-----Article2-------------------8--------------------12
Nord-----Article3---------7
.
.
.

La liste d'article n'étant pas limitée

J'ai donc besoin de récupérer, de façon automatique, tous les temps de production dans un tableau du type de celui-ci

Secteur----Article-----Machine-----temps de production
Nord-------Article1----Machine1-----------5
Nord-------Artilce1----Machine3----------- 3
.
.
.

Sachant que les 3 premières colonnes sont remplies manuellement, en fonction de la production lancée. mais vu la taille du premier tableau il n'est pas envisageable de remplir la dernière colonne manuellement

Je voudrai donc savoir si quelqu'un connait une fonction Excel permettant de récupérer les temps de production en fonction des critères "secteur" "article" et "machine"


Merci d'avance pour vos réponses
je compte sur vous!!!

PS: j'ai écarté la macro, car cela serait trop contraignant à mettre à jour comparé à une formule Excel.

[alsimbad]

Envoyé par aurelop

PS: j'ai écarté la macro, car cela serait trop contraignant à mettre à jour comparé à une formule Excel.

tu as écarté quelle macro par rapport a quelle formule ?

le mieux dans ton cas est soit une macro, soit un tableau croisé dynamique. les formules ne permettent pas de rajouter uns ligne a ton tableau final, donc soit tu dimensionne ton tableau pour disons 1000 ligne, cela veut dire : la formule est relativement simple, mais tu dois l'écrire sur 1000 ligne parce que suivant la machine, la tableau final va en contenir 700 ou 950 et tu ne le sais pas a l'avance.
un tableau croisé dynamique parait le plus simple a faire pour toi si tu ne connais pas le vba. il n'y a pas de recette miracle, mais c'est relativement simple a mettre en place, c'est quasi automatique

[Gorfael]

Salut aurelop et le forum

Secteur---Ref ------ Machine1--Machine2--Machine3--Machine4 . . .
Nord-----Article1--------5---------------------3
Sud-----Article2-------------------8--------------------12
Nord-----Article3---------7

Secteur----Article-----Machine-----temps de production
Nord-------Article1----Machine1-----------5
Nord-------Artilce1----Machine3----------- 3

en admettant que le premier tableau soit en A;F de F1, que la deuxièeme en A de F2 et que les lignes de titre soit en 1
Dans F2 :
D2 :

=SOMMEPROD((A2='F1'!$A$2:$A$25)*(B2='F1'!$B$2:$B$25)*INDIRECT(ADRESSE(2;EQUIV(C2;'F1'!$C$1:$F$1;0)+2;;;"F1") & ":" & ADRESSE(25;EQUIV(C2;'F1'!$C$1:$F$1;0)+2;;;)))

Mais je recommande fortement de passer par des listes de validations (ça reconnaît bien "Article1", mais nettement moins "Artilce1")

explication de la formule
EQUIV(C2;'F1'!$C$1:$F$1;0)+2
équive renvoi le numéro d'ordre où il trouve la valeur contenue en C2 dans la plage C1:F1 de la feuille "F1". : s'il le trouve en C1, il va retourner 1 => on ajoute 2 pour qu'il pointe la colonne C (n°3 de la feuille)
On utilise l'adressage absolu ($) pour qu'en déplaçant la formule, l'adresse du tableau reste la même.
C2 reste en relatif, cette valeur étant amenée à évoluer ($C2 serait mieux, la colonne C ne bougeant pas)
Le 0 est consiéré comme Faux par EQUIV qui ne recherche que la valeur identique

ADRESSE(2;EQUIV(C2;'F1'!$C$1:$F$1;0)+2;;;"F1")
2 : numéro de la ligne
Equiv(...)+2 : numéro de la colonne
"F1" : nom de l'onglet
Adresse() renvoie une valeur en texte. ici : 'F1'!$C$2
L'autre adresse de la concaténation n'a pas le nom de la feuille et retourne : $C$25
Le total fait le texte : 'F1'!$C$2:$C$25

Le texte trouvé est converti en adresse de plage par INDIRECT(texte)

SOMMEPROD((A2='F1'!$A$2:$A$25)*1)
Fait est une formule matricielle qui fait la somme de
(A2='F1'!$A$2)+(A2='F1'!$A$3)+...+(A2='F1'!$A$24)+(A2='F1'!$A$5)
(A2='F1'!$A$2)

Pour la formule : les deux premier terme valident la formule en multipliant le reste par 1 si les 2 termes sont vrais, ou par 0 si au moins un des termes est faux.
Equiv recherche la colonne

Les limites :
Chaque nom doit être orthographié à l'identique sur les 2 feuilles
Les colonnes de machine doivent contenir des nombres (hormis le titre)
Si tu as plusieurs fois les même noms Secteur/Référence/machines, excel retournera le total
Pour la compréhension de la formule, je me suis limité à la ligne 25, mais la vraie formule serait plutôt :

=SOMMEPROD((A2='F1'!$A$2:$A$65536)*(B2='F1'!$B$2:$B$65536)*INDIRECT(ADRESSE(2;EQUIV(C2;'F1'!$C$1:$F$1;0)+2;;;"F1") & ":" & ADRESSE(65536;EQUIV(C2;'F1'!$C$1:$F$1;0)+2;;;)))

ne la recopier que suivant le besoin (temps de calcul pouvant devenir gênant.)
A+

[aurelop]

Merci beaucoup pour vos réponses, je vais essayer ça demain au boulot!!

pour répondre à alsimbad, j'ai écarté l'idée de faire une macro parce qu'elle ne se lance pas toute seule, contrairement à une fonction(ou formule classique) qui est réévaluée en permanence.

encore merci pour vos réponses
a+

[alsimbad]

Envoyé par aurelop

pour répondre à alsimbad, j'ai écarté l'idée de faire une macro parce qu'elle ne se lance pas toute seule

idée fausse, un macro peut se lancer toute seule

mais je te recommande chaudement l'étude des tableaux croisée dynamiques.
tu selectionne tes donnée, tu fais données/tableaucroisédynamique (de memoire , je suis passé a 2007) puis tu te laisse guider, c'est facile de comprendre, c'est exactement ce que tu cherche.

[aurelop]

ok merci pour la précision. mais comment fait on pour qu'une macro se lance toute seule? parce que dans mon fichier j'ai déjà fait pas mal de macro et ça pourrait être utile que certaines se lancent toutes seules

[alsimbad]

va déja voir là

[Gorfael]

Salut aurelop et le forum
Tu cliques-droit sur le nom de l'onglet +> Menu contextuel>>Visualiser le code => Tu es sur le module lié à la feuille
en haut, la case de gauche, tu sélectionnes Worksheet
dans la case de droit, tu as toutes les macro à lancement automatique concernant uniquement cette feuille.
Petite pécission : si, sur ce module, tu crées une macro qui sélectionne une cellule d'une autre feuille, tu auras une erreur. Mais les Select/Activate sont rarement nécessaires.

Dans la fenêtre Projet tu as le module ThisWorkBook.
Tu sélectionnes WorkBook dans la case de droite et tu as toutes les macro automatique concernant le classeur.

Les 2 modules ont des macros qui se déclenchent aux mêmes évènements, comme WorkSheet_Change et Workbook_SheetChange. Mais l'une ne concerne que la feuille liée, alors que l'autre concerne toutes les feuilles (avec la variable système Sh donnant la feuille concernée).
A+

[alsimbad]

je t'ai fais un petit exemple avec les possiblités, pas sur que ce soit exactement ce que tu cherche, mais les possiblités sont grandes