Renvoyer des valeurs d'une autre table si condition respectée
Bonjour,
J'ai un premier onglet "Age Lait" qui recense la date et heure de remplissage d'un tank en particulier et la date et heure du soutirage de ce même tank. J'ai un deuxième onglet "Injection" qui récapitule la date et heure des injections pour chaque tank. J'aimerais parcourir la BDD présente dans l'onglet "Age Lait" et rajouter à chaque ligne l'information de la date et heure de la première injection pour le tank considéré. L'injection a forcément lieu entre la date de remplissage du tank et celle du soutirage. Si aucune valeur d'injection n'est trouvée entre ces deux dates alors abandonner la recherche de l'injection pour la ligne considérée de l'onglet "Age Lait". J'ai commencé une macro test() qui fonctionne à moitié et qui n'est pas du tout optimisée. Pourriez vous m'aider s'il vous plait ? Peut être que l'on peut utiliser la fonction VlookUp mais je ne sais pas trop comment elle fonctionne..
Je vous remercie d'avance,
Léa
Bonjour,
Une solution sans passer par macro, en formule matricielle à valider par CTRL + SHIFT + ENTER en D2 puis en tirant :
=MIN(SI((Injection!$A$2:$A$99>$A2)*(Injection!$A$2:$A$99<$B2)*(Injection!$B$2:$B$99=$C2);Injection!$A$2:$A$99;"NA"))Cdlt,
Bonjour,
Je vous remercie pour votre réponse mais cette solution ne fonctionne qu'à moitié. Toutes les lignes colorées devraient laisser apparaître une date d'injection et ce n'est pas le cas avec ma solution proposée ni la votre... De plus, étant donné que c'est en format date, les lignes où l'injection n'est pas trouvée affichent "0/1/00 0:00".
Bonjour,
La formule fonctionne comme demandée. Vous avez des espaces après vos numéros dans la secondes feuilles. Pour ce qui est des valeurs a 0 il suffit d'utiliser un format personnalisé afin de masquer les valeurs nulles.
Cdlt,
Effectivement je viens de comprendre ! Je vous remercie ! Pensez-vous qu'il est possible d'insérer votre formule matricielle dans une macro ? Car dans l'idéal j'aimerais obtenir que les valeurs et non les formules.
Bonjour,
Au plus simple je dirais en introduisant cette formule directement dans la table, en la copiant et la collant en valeur, je l'ai rendue dynamique au cas où vous avez plus de lignes dans l'une des deux feuilles :
Sub INJECTION()
Dim LR_I%, LR_F%
LR_I = Worksheets("Injection").Cells(Worksheets("Injection").Rows.Count, 1).End(xlUp).Row
LR_F = Worksheets("Age Lait").Cells(Worksheets("Age Lait").Rows.Count, 1).End(xlUp).Row
[D2].FormulaArray = "=MIN(IF((Injection!$A$2:$A$" & LR_I & ">$A2)*(Injection!$A$2:$A$" & LR_I & "<$B2)*(Injection!$B$2:$B$" & LR_I & "=$C2),Injection!$A$2:$A$" & LR_I & ",""""))"
Range("D2:D" & LR_F).FillDown
Range("D2:D" & LR_F).Copy
Range("D2:D" & LR_F).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End SubCdlt,
Edit : On pourrait passer par des arrays et boucler, mais ça me semble plus long à réaliser et exécuter.
Je vous remercie beaucoup ! Ca fonctionne. Je ne comprends pas bien la syntaxe avec les " " pourriez vous m'expliquer s'il vous plait ? Egalement, pourquoi mettre FormulaArray et pas FormulaR1C1 ? Je ne comprends également pas en quoi le fait de copier et coller les cellules permet de modifier le format de cellule et ne pas faire apparaître les 0 ?
Bonjour,
Sous VBA une formule s'introduit sous forme de texte en anglais (VBA étant codé uniquement en anglais), d'où les " ". Je dois les fermer à chaque fois que j’introduis une variable, par exemple LR_I = dernière ligne de votre classeur donc 99 que je concatène/fusionne avec le reste du texte de la formule à l'aide de l'opérateur &.
FormulaArray me permet de rentrer la formule directement en matricielle.
FormulaR1C1 est assez complexe à maîtriser pour ma part, c'est une formule qui fait référence aux numéros de ligne et de colonne par rapport à la cellule dans laquelle on rentre la formule. Par exemple si nous souhaitons renvoyer A3 dans la cellule A1 alors la formule serait qu'on renvoi la 2ème ligne à partir de la cellule active 3 = 1+2 mais pas de décalage de colonne donc :
A1.FormulaR1C1 = "=R[2]C"
A1.Formula = "=A3"Vous voyez à travers cet exemple qu'il est plus simple d'utiliser .Formula. Cependant dans certains cas, FormulaR1C1 peut s'avérer utile.
Copier/Coller les cellules ne permet pas de modifier son format, pour supprimer les 0 il est nécessaire de renseigner au moins une fois sur votre colonne de destination le format suivant "j/m/aa h:mm;;;@". C'est aussi possible de le faire par macro, mais comme c'est une variable qui ne va jamais changer, je ne vois pas trop l'intérêt sauf si vous travaillez sur un nouveau fichier à chaque fois. Ensuite le collage en valeur permet de ne pas écraser le format mis en place.
Cdlt,
Merci beaucoup pour ces explications très précises ! Dernière question : du coup à quoi sert "& ",""""))" à la fin de la formule ? Je ne comprends pas pourquoi 4 guillemets
Bonjour,
Le premier guillemet sert à rouvrir la partie texte de la formule qui se poursuit alors par une , pour indiquer le résultat si test faux de la formule SI/IF.
J'ai décidé que nous renvoyons rien donc sous Excel rien est symbolisé par deux doubles guillemets donc "". Sauf que ces guillemets sont sous VBA aussi le signe d'une fin ou ouverture de texte comme je l'ai expliqué. Donc au final pour dire à VBA de ne pas comptabiliser ces deux doubles guillemets comme une ouverture/fermeture, il est nécessaire de les encadrer par encore deux doubles guillemets, ce qui en fait 4 à la suite.
Le dernier double guillemet, lui, sert simplement à dire qu'après la double parenthèse de fermeture mon MIN et IF, c'est la fin de la formule textuelle.
Cdlt,
Super ! J'ai bien compris merci beaucoup !
J'ai essayé d'écrire la ligne de code suivante : "Range("D" & LastRow_Age).NumberFormat = "j/m/aa h:mm;;;@"" pour formater directement toute la colonne en data + heure sans 0 plutôt que les dernières lignes de votre code mais ça ne fonctionne pas, le message d'erreur "impossible de définir la propriété NumberFormat de la classe Range" apparaît. Sauriez-vous pourquoi s'il vous plaît ?
Re bonjour,
J'ai essayé de faire une formule matricielle en colonne E de l'onglet "Remp-Sout" pour calculer l'âge de la matière injectée dans chaque tank en faisant une soustraction entre la date d'injection présente en colonne D de l'onglet "Remp-Sout" et le remplissage de la matière indiqué en colonne B de l'onglet "BDD matière". Pour trouver la bonne date de remplissage de la matière il faut que la date d'injection soit comprise entre une date de remplissage et une date de soutirage associée. Ca fonctionne seulement pour la cellule E3.. Pourriez-vous m'aider s'il vous plait ?
Bonjour,
Comme expliqué, VBA est codé en anglais donc :
Sub Injection()
Dim LastRow_Inj As Integer, LastRow_Age As Integer
[D1] = "INJECTION"
LastRow_Inj = Worksheets("Injection").Cells(Worksheets("Injection").Rows.Count, 1).End(xlUp).Row
LastRow_Age = Worksheets("Remp-Sout").Cells(Worksheets("Remp-Sout").Rows.Count, 1).End(xlUp).Row
[D2].FormulaArray = "=MIN(IF((Injection!$A$2:$A$" & LastRow_Inj & ">$A2)*(Injection!$A$2:$A$" & LastRow_Inj & "<$B2)*(Injection!$B$2:$B$" & LastRow_Inj & "=$C2),Injection!$A$2:$A$" & LastRow_Inj & ",""""))"
Range("D2:D" & LastRow_Age).FillDown
Range("D2:D" & LastRow_Age).Copy
Range("D2:D" & LastRow_Age).PasteSpecial Paste:=xlPasteValues
Range("D2:D" & LastRow_Age).NumberFormat = " d/m/yy h:mm;;;@"
Application.CutCopyMode = False
End SubPour la formule elle est mal utilisée, la soustraction est réalisée par au bon endroit et dans le mauvais sens je pense, de plus il faut penser au cas où la date d'injection n'est pas entre deux dates de remplissages donc à valider en matricielle par CTRL + SHIFT + ENTER :
=SI(MIN(SI(($D2>'BDD matière'!$B$2:$B$7)*($D2<'BDD matière'!$C$2:$C$7);'BDD matière'!$B$2:$B$7;""))=0;"";D2-MIN(SI(($D2>'BDD matière'!$B$2:$B$7)*($D2<'BDD matière'!$C$2:$C$7);'BDD matière'!$B$2:$B$7)))Ci-joint votre fichier.
Cdlt,
Bonjour, je vous remercie pour votre réponse, ça fonctionne parfaitement. Cependant, je ne comprends pas trop mon erreur pour la formule de l'âge de la matière. Pour moi le fait de mettre la valeur "" si la condition n'est pas vérifiée devrait prendre en compte le fait que si l'injection ne se situe pas entre une date de remplissage et un nettoyage il y ait "" dans la case ?
J'ai essayé d'écrire votre formule sous VBA mais ça ne fonctionne pas, j'ai un message du type "erreur de compilation, attendu : fin d'instruction". Je ne vois pas trop où cela bloque. Auriez-vous une idée s'il vous plait ?
[E2].FormulaArray = "=IF(MIN(IF(($D2>BDD matière!$B$2:$B$"&LastRow_Age&")*($D2<BDD matière!$C$2:$C$"&LastRow_Age&"),BDD matière!$B$2:$B$"&LastRow_Age&",""""))=0,"""",D2-MIN(IF(($D2>BDD matière!$B$2:$B$"&LastRow_Age&")*($D2<BDD matière!$C$2:$C$"&LastRow_Age&"),BDD matière!$B$2:$B$"&LastRow_Age&")))"Bonjour,
Ci-contre. Une feuille avec espace dans son nom est encadré par des simples guillemets que ce soit en formule ou en VBA :
[E2].FormulaArray = "=IF(MIN(IF(($D2>'BDD matière'!$B$2:$B$" & LastRow_Age & ")*($D2<'BDD matière'!$C$2:$C$" & LastRow_Age & "),'BDD matière'!$B$2:$B$" & LastRow_Age & ",""""))=0,"""",D2-MIN(IF(($D2>'BDD matière'!$B$2:$B$" & LastRow_Age & ")*($D2<'BDD matière'!$C$2:$C$" & LastRow_Age & "),'BDD matière'!$B$2:$B$" & LastRow_Age & ")))"Pour votre première question, une formule matricielle ne renvoi que des vrai ou faux si les données testées dans les conditions sont du bon types (ici des dates) qui sont alors traduit en 0 (si faux) ou 1 (si vraie). Donc le minimum de vos conditions est systématiquement 0 dès lors que les conditions ne sont pas respectées. C'est le fonctionnement des matricielles.
Cdlt,
Re bonjour,
Je vous remercie pour ces explications bien précises !
Re bonjour,
Je m'excuse de vous redemander de l'aide. J'ai essayé de faire une formule matricielle dans l'onglet "Remp" en colonne C mais elle ne fonctionne pas. Je pense que je n'ai pas bien compris l'utilisation des formules matricielles. J'aimerais que soit affiché "OUI" dans la colonne C de l'onglet "Remp" si la date+heure de remplissage de la ligne en question indiquée en colonne A est présente (ou correspond presque, à 2min près maximum) dans la BDD de l'onglet "Sout". Les lignes colorées dans l'onglet "Remp" sont celles qui devraient afficher "OUI" en colonne C. Pourriez-vous m'aider s'il vous plait ?
Je vous remercie d'avance,
Léa
Bonjour,
En C2 :
=SI(SOMMEPROD((A2>=(Sout!$A$8:$A$23)-0,00138888888888889)*(A2<=(Sout!$A$8:$A$23)+0,00138888888888889))>0;"OUI";"")Pas besoin de valider en matriciel, SOMMEPROD l'est nativement.
Cdlt,