Recherche d'une valeur dans une table en fonction d'une heure approximative
Bonjour à tous,
J'ai aujourd'hui deux tableaux de données. Ces deux tableaux sont générés par deux équipements qui ne communiquent pas entre eux mais dont les informations sont complémentaires. Pour essayer d'être le plus clair possible j'ai mis en pièce jointe un exemple qui explique mon problème :
J'ai aujourd'hui 2 tables :
- 1 table avec une date ( jour/mois/année) et une heure de production de début et de fin
- 1 table avec une date ( jour/mois/année) et une heure de production précise ainsi qu'un numéro de produit associé.
Ce que je souhaite c'est compléter la colonne D de la table 1 avec les numéros de produit présents dans la table 2 en fonction de la date ( qui doit être identique ) et de l'heure de production de la table 2 ( qui doit se trouver entre l'heure de début et l'heure de fin de la table1 ) . Si la recherche n'aboutit à rien je laisse la case vide
Dans un premier temps j'ai commencé à faire l'exercice avec les fonction INDEX(...,EQUIV(...,...,...)...) mais je suis rapidement arrivé à des solutions très peu optimisées. Avez-vous des recommandations à me donner pour être le plus efficace possible ?
Bonne journée
Hello,
Une proposition,
Mais je présume que tu vas sûrement avoir plusieurs fois la même date dans ton premier onglet donc ma solution ne sera plus viable.
N'hésite pas à préciser les règles à appliquer si jamais
@+
Hello,
Merci pour le retour.
En revanche tu as bien deviné, les datas dont je dispose sont par centaines et il y a des dates qui sont identiques. Pour le reste le fonctionnement désiré semble être compris :)
Cdlt
Hey,
Mais en fait cette fonction AGREGAT, en fonction de l'opération que tu fais tu peux faire des SOMME.SI.ENS, MAX.SI.ENS etc avec nan ? C'est une formule un peu tout en un j'ai l'impression ?
@+
re,
@Baroute78, oui, c'est une formule un peu tout (et existe depuis excel2010 ! ). Je préfère la forme matricielle, la forme référentielle est plus ambetante. Alors MAX.SI.ENS est GRANDE.VALEUR(....,1) --> agregat(14;6;...;1), mais SOMME.SI.ENS est plus difficile à postuler.
Bonjour,
Merci à vous pour la solution ! une question cependant, je ne comprends pas quel est le rôle des chiffres 14 et 6 ci dessous:
=SIERREUR(INDEX('Table 2'!C:C;AGREGAT(14;6;LIGNE(TBL_2[Numéro de produit])/((TBL_2[jour/mois/année]=[@[jour/mois/année]])*([@[Heure de début]]<=TBL_2[Heure de production])*(TBL_2[Heure de production]<=[@[Heure de fin]]));1));"erreur")
Question complémentaire, si je souhaite rajouter des conditions pour la recherche comme un numéro de machine comment dois-je m'y prendre ? en croisant toutes mes datas je me suis rendu compte que j'ai beaucoup de données provenant de plusieurs machines qui peuvent arriver le même jour et durant la même plage horraire.
Hello,
15 représente la fonction GRANDE.VALEUR. Cet argument représente des fonctions, 1 pour Moyenne, 2 pour NB etc.
Le 6 signifie, que si il rencontre des valeurs d'erreur ça ça ne faussera pas la formule.
Tu as une bonne explication sur cette vidéo : Excel - Comment utiliser la Fonction Agregat() - YouTube
Sur la base du fichier de BsAlv, je t'ai rajouté le critère. Un gros merci encore à BsAlv elle va bien m'aider celle-ci
@+
re,
@Baroute78 et comme pas de côté, la somme des machines individuelles. Je sais que, ici, c'est plus facile avec SOMME.SI.ENS, mais c'est pour vous montrer le fonctionnement. Il faut créer une séquence exact du nombre qu'on veut sommer comme 4ème paramètre de agregat et puis un Somme autour d'agregat.
Avec cette méthode, mais moins bête, une question pour la somme du Top-5 est facile à réaliser, voir colonne L.
J'ai lu et je le crois, que agregat n'est pas une formule matricielle, donc elle n'est pas aussi éprouvant pour le processeur.
PS. pour certaines formules dans mon exemple, il faut avoir excel 365, pour les versions plus agées, c'est un petit peu plus compliqué.
,
Hello BsAlv,
J'ai bien enregistré ton fichier et bien compris le paramètre k. Dans la table d'origine, nous recherchions le numéro de ligne donc "1" était de mise. Si on ajoute à ça tous les critères ça fait que les fonctions PETITE et GRANDE valeur renvoient le même résultat.
Je ne suis pas encore hyper à l'aise, mais je pense que ça va venir. Je vois le potentiel que ça peut avoir et tu m'as apporté un éclaircissement quant au paramètre k qui n'est pas forcément hyper bien expliqué dans les tutos.
Pour les top 5, 10 etc j'avoue que c'est puissant et que je vais être amené à le réutiliser. J'avais la solution avec PowerQuery ou via les TCD. Par contre quand je devais le faire directement par formule j'avoue que je faisais un tri dans le tableau et avec une fonction décaler on trouvait la première ligne qui allait bien et ensuite on étendait la plage. Mais avec cette méthode, pas vraiment besoin de se prendre la tête =D
Je te remercie et garde ton fichier précieusement
@+
Bonjour,
Désolé pour la réponse tardive.
Après transposition de vos exemples je dois commettre une erreur toutes mes lignes sont en erreur :
Cdlt
re,
il y a un espace en plus pour les dates de "DATA", c'est "06.03.2023 " au lieu de "06.03.2023", donc j'ai ajouté un "SUPPRESPACE" dans la formule
=SIERREUR(@INDEX(Finishing!D:D;AGREGAT(14;3;LIGNE(TBL_2[Numéro de produit])/
(
(TBL_2[jour/mois/année]=SUPPRESPACE([@[jour/mois/année]]))*
([@[Heure de début]]<=TBL_2[Heure de production])*
(TBL_2[Heure de production]<=[@[Heure de fin]])*
(TBL_2[Machine]=[@Machine])
);1));"erreur")
Bien vu ! Merci pour la réponse la solution est efficace et correspond parfaitement à mes attentes.
Bonne journée