Formule RECHERCHE Résolu/Fermé

Question

Bonjour à vous,

J'ai un fichier Excel, avec une première feuille, dont je notifie mes boitiers 'Hors PARC' lorsque les vrais boitiers ne sont pas arrivés.

Cela est donc destiné à l'un de mes camions.


Dans ma seconde feuille, j'ai noté mes boitiers 'Hors PARC' que je dispose (de 1 à 4).
J'aimerais effectuer la liaison entre les deux, et remplir automatiquement les colonnes dans la deuxième feuille.

Dans la seconde feuille :

- Avoir dans la colonne B, la dernière date (la plus récente) du HP 1 ;
- Avoir dans la colonne C, le dernier camion (avec la date la plus récente) du HP 1 ;
etc..

Si vous pouvez m'aider, car je n'y arrive pas...

Lien : https://www.cjoint.com/c/KKEiMkZEy8s

Merci à vous.

Cordialement,

Romain

Mike-31 · Answer

Bonjour,

en B3 onglet Suivi des hors parc cette formule matricielle qu'il faut confirmer en cliquant en même temps sur trois touches du clavier 
Ctrl, Shift et Entrée et si tu fais bien la formule se placera entre ces accolades {}
=MAX(SI('Suivi des remplaçement boitiers'!$D$3:$D$10=A3;'Suivi des remplaçement boitiers'!$A$3:$A$10))

pour la formule en C pour le dernier camion avec la date la plus récente je ne comprends pas, peux tu en fonction de ton tableau dire quelle valeur tu souhaites avoir dans cette colonne 
 sous réserve toujours en matricielle
en C3
=MAX(SI('Suivi des remplaçement boitiers'!$A$3:$A$10=B3;'Suivi des remplaçement boitiers'!$B$3:$B$10))
et en D3
=MAX(SI('Suivi des remplaçement boitiers'!$A$3:$A$10=B3;'Suivi des remplaçement boitiers'!$C$3:$C$10))

romain.mrs · Answer

Bonjour Mike-31,

Dans la colonne C, ce que j'aimerais c'est, lorsque tu prends en colonne B la date la + récente, c'est y insérer le tracteur à cette date aussi.

Pour exemple, pour le HP 1, ce serait le numéro de camion '100' (avec ta formule du dessus, en colonne B, qui indiquerait le 26/06/2021).

Mike-31 · Answer

Re,

reprends mon post un ou tu as les trois formules matricielles

romain.mrs · Answer

Super Mike, merci à toi pour ton aide.

C'est nickel.

Cordialement,
Romain

Mike-31 · Answer

Re,

comme les formules matricielles utilisent pas mal de ressource système, en B3 tu es oubligé de passer par une formule matricielle que je t'ai donné
=MAX(SI('Suivi des remplaçement boitiers'!$D$3:$D$10=A3;'Suivi des remplaçement boitiers'!$A$3:$A$10))

mais en C3 passe avec une simple formule
=SIERREUR(RECHERCHEV(B3;'Suivi des remplaçement boitiers'!$A$3:$D$9;2;0);"")
et idem en D3
=SIERREUR(RECHERCHEV(B3;'Suivi des remplaçement boitiers'!$A$3:$D$9;3;0);"")
N'oublie pas de passer le statut de la discussion en résolu

romain.mrs · Answer

Impeccable,

Et dernière petite question :

dans ma deuxième feuille, en C3, j'ai la formule que tu viens de me donner.

Seulement, une fois que le nouveau boitier est installé dans un camion, le boitier 'Hors PARC' revient donc en stock.



Ce qui voudrait dire :

- sur ma deuxième feuille, en B2, si je n'ai pas de date en Feuille1/Colonne 6 (F), alors je prends la date en Feuille1/Colonne 1 (A).
Si j'ai une date en Feuille 1/Colonne 6 (F), alors y mettre en B3 la date de la colonne F.

- Et donc en C3, si je n'ai pas de date en Feuille 1/Colonne 6 (F), y mettre le numéro de PARC.
Si j'ai une date en Feuille 1/Colonne 6 (F), alors y mettre en C3 "En Stock".


si tu peux m'aider à finir tout cela :D

Cordialement,

Romain

Mike-31 · Answer

Re,

Je ne comprends pas, tu veux dire sur l'onglet Suiv des Hors Parc  B3 mettre la date du premier onglet colonne F et si en colonne F il n'y a pas de date prendre la date colonne A
Mais dans ce cas on ne recherche pas la date MAX colonne A
c'est ça 
et en colonne D que faut il inscrire !

romain.mrs · Answer

Pour expliquer :

Dans la feuille 'Suivi des Hors PARC' : nous avons 5 colonnes.

Dans la colonne B, nous avons donc recherché la date max (la + récente).

Pour faire un exemple : sur le HP 1 dans la feuille 'Suivi des Hors PARC', en colonne B nous devons avoir comme date le 26/06/2021 (qui est correct).



Sur le HP 2, lorsque l'on regarde la feuille 'Suivi des remplaçement boitiers', nous avons une date dans la colonne F (en F6) qui correspond au boitier revenu chez nous.

Du coup, ce qu'il faudrait sur le HP 2 dans la feuille 'Suivi des Hors PARC', en colonne B, c'est avoir comme formule :
- si l'on a une date en colonne F, prendre cette date et donc en C4 mettre "En Stock" ;
- sinon, si jamais nous n'avons pas de date en colonne F, prendre la date en Colonne A et donc en C3 mettre le numéro de tracteur (comme la formule de base).

Lien fichier modifié : https://www.cjoint.com/c/KKEmXBqkfIs

Cordialement,

Romain

Mike-31 · Answer

Re,

regarde le fichier en retour voir, mais en colonne D que faut il faire si la date vient de la colonne F

https://www.cjoint.com/c/KKEnapbG6MF

romain.mrs · Answer

Bonjour Mike,

Pour la colonne D, j'ai repris ta formule en colonne C et ai modifié le "En stock".

Merci à toi pour tes retours et ta disponibilité.
Merci de ton aide.

Cordialement,

Romain

Mike-31 · Answer

Re,

OK, je passe le statut de la discussion en résolu

romain.mrs · Answer

Et dernière question, pourquoi mets-tu des formules matricielles ?

Mike-31 · Answer

Re,

contrairement à une formule classique, une formule matricielle va effectuer plusieurs calculs sur une plage ou il faudrait normalement en effectuer plusieurs ce qui est le cas dans ta demande ou il faut boucler sur plusieurs valeurs pour extraire la valeur MAX
exemple si tu sélectionnes la cellule C3, et dans la barre des formules sélectionne cette partie de formule
'Suivi des remplaçement boitiers'!$A$3:$D$9  et que tu clic sur la touche de fonction F9
tu verras s'afficher ça qui correspond aux données de ta plage
{43821.105."a"."HP 1";43957.120."b"."HP 3";44160.102."c"."HP 2";44277.119."d"."HP 2";44373.100."e"."HP 1";44492.272."f"."HP 4";44580.150."g"."HP 2"}

43821 est une des dates de la plage ,105 est la valeur N° Parc, a est une des valeurs que j'ai saisi en IMMAT, HP 1 le critère puis idem pour la ligne suivante
Donc Excel va rechercher le, critère dans toutes ces lignes.

pour sortir de ce mode matriciel et retrouver ta formule clic sur Echap

regarde ce tutoriel
https://www.cours-gratuit.com/tutoriel-excel/tutoriel-excel-formules-matricielles

romain.mrs · Answer

Merci à toi pour ton aide Mike, ça m'aide fortement :D

Cordialement
Romain

romain.mrs · Answer

Re-bonjour Mike,

J'ai un petit souci sur ma deuxième feuille :

Lorsque je change par exemple des boitiers le même jour, cela me met les valeurs du premier boitier (rempli dans la feuille 1)...

Saurais-tu m'aider pour cette dernière étape stp ?

Cordialement

Rom1

Mike-31 · Answer

Re,

Si j'ai bien compris ton retour,  tu aurais deux dates le même jour et Excel te retourne le premier N) parc et immat etc ... équivalent à la première date de ta colonne

Es ce cela !

romain.mrs · Answer

Bonjour Mike,

C'est exactement cela, et si j'ai plusieurs dates au même jour, j'aurais que le premier véhicule dans la liste, et non la ligne correspondant au numéro de boitier.

Merci de ton aide.

Cordialement,

Rom1

Mike-31 · Answer

Re,

Alors toujours en formule matricielle, sur ton onglet Suivi des Hors Parc cellule B3

=SIERREUR(SI(NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A3)<=NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A3);INDEX(TableauSuiviBoitiersEurope[DATE];PETITE.VALEUR(SI(TableauSuiviBoitiersEurope[HORS PARC]=A3;LIGNE(INDIRECT("1:"&LIGNES(TableauSuiviBoitiersEurope[HORS PARC]))));NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A3)));"");"")

en cellule C3
=SIERREUR(SI(NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A3)<=NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A3);INDEX(TableauSuiviBoitiersEurope[N° PARC];PETITE.VALEUR(SI(TableauSuiviBoitiersEurope[HORS PARC]=A3;LIGNE(INDIRECT("1:"&LIGNES(TableauSuiviBoitiersEurope[HORS PARC]))));NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A3)));"");"")

et en cellule D3
=SIERREUR(SI(NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A4)<=NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A4);INDEX(TableauSuiviBoitiersEurope[immat.];PETITE.VALEUR(SI(TableauSuiviBoitiersEurope[HORS PARC]=A4;LIGNE(INDIRECT("1:"&LIGNES(TableauSuiviBoitiersEurope[HORS PARC]))));NB.SI(TableauSuiviBoitiersEurope[HORS PARC];A4)));"");"")

incrémente les 3 formules vers le bas
 
par contre si tu nommes tes plages, par exemple onglet Suivi des remplaçement boitiers plage A3:A30 nommée Dates la plage B3:B30 nommée N_Parc la plage C3:C30 nommée Immat et la plage D3:D30 nommée H_Parc

la formule onglet Suivi des Hors Parc cellule B3 devient
=SIERREUR(SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Dates;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));"");"")

en C3
=SIERREUR(SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(N_Parc;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));"");"")
et en D3
=SIERREUR(SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Immat;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));"");"")

A toi de choisir

Mike-31 · Answer

Re, En me replongeant sur ta demande, je m'aperçois que dans mes dernières formules j'ai omis de traiter le retour en stock. Pour alléger les formules je te conseille de nommer tes plages et ajouter dans Suivi des remplaçement boitiers F2:F30 nommée Stock en B3 la formule matricielle devient =SIERREUR(SI(SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Stock;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));"")>0;SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Stock;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));"");SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Dates;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));""));"") en C3 =SIERREUR(SI(SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Stock;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));"")>0;"Stock";SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(N_Parc;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));""));"") en D3 =SIERREUR(SI(SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Stock;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));"")>0;"";SI(NB.SI(H_Parc;A3)<=NB.SI(H_Parc;A3);INDEX(Immat;PETITE.VALEUR(SI(H_Parc=A3;LIGNE(INDIRECT("1:"&LIGNES(H_Parc))));NB.SI(H_Parc;A3)));""));"")

romain.mrs · Answer

Bonjour Mike,

J'ai copié exactement tes formules, et et bien remplacé les termes pas les miens, mais cela me laisse la case invisible, comme ci il y avait une erreur.

Mike-31 · Answer

As tu confirmé les formules en matricielles,

lorsque tu actives une cellule contenant une formule, dans la barre des formules es ce que la formule est entre ces accolades {}
sinon clic sur la formule dans la barre des formules et clic en même temps sur les trois touches Ctrl Shift et Entrée

https://www.cjoint.com/c/LAhl6P0K7IF

romain.mrs · Answer

Bonjour Mike,

Effectivement c'est moi qui ai m*rdé...

Super en tout cas, un grand merci à toi !

Rom1

romain.mrs · Answer

C'est encore moi.

Je viens de me rendre compte que j'ai rentré une date dans la colonne (Date retour Télépéage des exploits) mais dans ma feuille B, cela me met toujours le numéro de camion en question sur cette ligne, et non "En Stock" comme prévu car j'ai une date de retour.

Rom1

Mike-31 · Answer

Re,

teste ce fichier voir
https://www.cjoint.com/c/LAmjtQ2Ly4F

romain.mrs · Answer

Super, un grand merci à toi :D

Cordialement,

Rom1

romain.mrs · Answer

Bonjour Mike-31,

Après des semaines d'utilisation du fichier j'y rencontre des souci...
Comme tu peux le voir, la feuille 'Suivi des Hors Parc' ne s'actualise pas du tout avec les dates de la feuille 1, comme prévu...
Par hasard, lorsqu'un boitier n'est pas indiqué dans la feuille 1, comme le HP 2 par exemple, pourrions-nous mettre dans le numéro de PARC et dans l'immat "En Stock" ?


Lien : https://www.cjoint.com/c/LDbhzAAC84s

Merci à toi pour ton retour.

Mike-31 · Answer

Re,

En D4 comme en E4 la formule se termine par   ;""));"")

remplace le dernier "") 
par ce complément et valide en matricielle
SI([@[Numéro de série
S/N]]<>"";"En Stock";""))

romain.mrs · Answer

Bonjour Mike-31,

Super, cependant il me reste une dernière petite erreur.

Sur ma feuille 'Suivi des Hors Parc', j'ai le HP 44 qui m'indique qu'il est en Date depuis le 06/03, mais qu'il est en stock (en colonne E & F).

Hors, sur ma feuille 'Suivi des remplacements boitiers', j'ai deux lignes avec le HP 44 :
- le 12/01/2022, sur le TR 224. Cela est revenu en stock le 23/02.
- le 06/03/2022, sur le TR 224. Cela n'est pas revenu en stock et est toujours le TR.


Lien : https://www.cjoint.com/c/LDfjURCA7Cs

Cordialement,
Rom1

Mike-31 · Answer

Re,

Je suis un peu bousculé ces temps ci, mais voilà, il suffit de changer les formules toujours en matricielles en D4, E4 et F4 

https://www.cjoint.com/c/LDgpqnRzeQF

Mike-31 · Answer

Re,

teste ce fichier, j'ai allégé les formules voir si elles répondent à tes attentes

https://www.cjoint.com/c/LDgtY4j0ENF

Mike-31 · Answer

Re,

tu peux me dire si le dernier fichier que je t'ai retourné répond à tes attentes que je le détruise de mon système
Merci.

romain.mrs · Answer

Salut Mike,

Un grand merci cela correspond tout à fait, un grand merci encore une fois !!

Rom1

Formule RECHERCHE

32 réponses

Discussions similaires