• Publicité

RECHERCHEV et DATE

Dans ce forum, vous pouvez poser toutes vos questions concernant l'utilisation de logiciels de bureautique (Microsoft Office, Open Office, etc.)

Modérateurs: Sebastien, Stéphane, Christophe

RECHERCHEV et DATE

Messagepar Nanisa » 11 Mar 2016 13:46

Bonjour à tous et toutes

Voici ma demande :
Je vais travailler sur 3 colonnes, afin de remplir une cellule sur un autre tableau.

Sur la première ligne, je vais enregistrer la date du jour en première colonne, puis le nom d'une personne en deuxième colonne et enfin une donnée (date, calcul, texte...) en troisième colonne.
A chaque fois que je vais vouloir mettre cette troisième colonne à jour, je vais faire un copier-coller de la ligne et modifier la donnée sur cette nouvelle ligne. Cette nouvelle ligne sera datée, en première colonne, du jour de la modification.

Ainsi, mon tableau se verra incrémenté d'une ligne pour chaque mise à jour.

Sur un autre tableau, je souhaite une mise à jour automatique d'une cellule dans le format :
Je souhaite voir la donnée de la colonne 3, de la ligne dont la colonne 1 a la date la plus récente, pour le nom donné.

Exemple, si les données de "Marcel" ont été mises à jour plusieurs fois, c'est la dernière qui doit s'inscrire.

Bien entendu, il s'agit d'un exemple car j'ai bien plus de 3 colonnes, ayant de multiples données à traiter, chacune d'elles ayant sa propre colonne. Et donc plusieurs cellules destinataires dans l'autre tableau.

J'ai bien pensé faire une rechercheV intriquée avec la formule GRANDE.VALEUR (pour récupérer la date voulue) mais je m'y perds.
Et c'est là que j'appelle au secours !

GRAND.MERCI d'avance :mercibcp:
Nanisa
No0b
No0b
 
Messages: 1
Enregistré le: 11 Mar 2016 12:44

Re: RECHERCHEV et DATE

Messagepar Sebastien » 12 Mar 2016 20:18

Bonjour,

Vous pouvez oublier RECHERCHEV dans un cas pareil. On peut arriver à quelque chose en utilisant des formules matricielles.
Rappel : Pour fonctionner, une formule matricielle doit être validée par la combinaison de touche CTRL+MAJ+ENTREE.

Exemple de tableau pour l'exemple :
Image

Dans le tableau "Source", on a les dates en colonne A, le nom en B et une donnée en C.
L'objectif est, pour un nom donné, de récupérer la donnée présente en C pour laquelle la date (en A) est la plus grande.

Je vais procéder en deux étapes :

:arrow: ETAPE 1 / Récupération de la date la plus grande pour un nom donné

Dans le tableau cible, les noms sont dans la colonne F. En G2, si l'on tape la formule suivante :
Code: Tout sélectionner
=MAX(SI(F2=$B$2:$B$8;$A$2:$A$8;""))

et qu'on la valide en matricielle (CTRL+MAJ+ENTREE), la formule devient :
Code: Tout sélectionner
{=MAX(SI(F2=$B$2:$B$8;$A$2:$A$8;""))}

et retourne le résultat : 06/03/2016.

Aperçu :
Image

On peut donc récupérer la date max pour un nom donné. Nous disposons donc d'un nom et d'une date pour effectuer notre recherche sur deux critères.

Des infos sur MAX et SI :

:arrow: ETAPE 2 :
En H2 on peut, avec une combinaison de formules INDEX et EQUIV, faire une recherche des valeurs présentes en F2 et G2 dans les matrices A2:A8 et B2:B8 pour y afficher la valeur présente dans C2:C8).
A valider avec CTRL+MAJ+ENTREE pour avoir la formule en matricielle.
EQUIV va nous permettre de récupérer le numéro de ligne dans laquelle se trouve la combinaison de la date et du nom. INDEX va nous permettre d'afficher la donnée.

Code: Tout sélectionner
{=INDEX($C$2:$C$8;EQUIV(G2&F2;$A$2:$A$8&$B$2:$B$8;0))}


Des infos sur INDEX et EQUIV :

En H2, on obtient le résultat "Données 6 mars"

Aperçu :
Image


Si on veut une seule cellule dans le tableau cible, on peut combiner les deux étapes dans une même formule :

Code: Tout sélectionner
{=INDEX($C$2:$C$8;EQUIV(MAX(SI(F2=$B$2:$B$8;$A$2:$A$8;""))&F2;$A$2:$A$8&$B$2:$B$8;0))}


Aperçu :
Image

@+
Image
Avatar de l’utilisateur
Sebastien
Administrateur
Administrateur
 
Messages: 5561
Enregistré le: 14 Fév 2005 19:17
Localisation: LILLE

Re: RECHERCHEV et DATE

Messagepar Chameley » 29 Juin 2020 15:48

Sebastien a écrit:Bonjour,

Vous pouvez oublier RECHERCHEV dans un cas pareil. On peut arriver à quelque chose en utilisant des formules matricielles.
Rappel : Pour fonctionner, une formule matricielle doit être validée par la combinaison de touche CTRL+MAJ+ENTREE.

Exemple de tableau pour l'exemple :
Image

Dans le tableau "Source", on a les dates en colonne A, le nom en B et une donnée en C.
L'objectif est, pour un nom donné, de récupérer la donnée présente en C pour laquelle la date (en A) est la plus grande.

Je vais procéder en deux étapes :

:arrow: ETAPE 1 / Récupération de la date la plus grande pour un nom donné

Dans le tableau cible, les noms sont dans la colonne F. En G2, si l'on tape la formule suivante :
Code: Tout sélectionner
=MAX(SI(F2=$B$2:$B$8;$A$2:$A$8;""))

et qu'on la valide en matricielle (CTRL+MAJ+ENTREE), la formule devient :
Code: Tout sélectionner
{=MAX(SI(F2=$B$2:$B$8;$A$2:$A$8;""))}

et retourne le résultat : 06/03/2016.

Aperçu :
Image

On peut donc récupérer la date max pour un nom donné. Nous disposons donc d'un nom et d'une date pour effectuer notre recherche sur deux critères.

Des infos sur MAX et SI :

:arrow: ETAPE 2 :
En H2 on peut, avec une combinaison de formules INDEX et EQUIV, faire une recherche des valeurs présentes en F2 et G2 dans les matrices A2:A8 et B2:B8 pour y afficher la valeur présente dans C2:C8).
A valider avec CTRL+MAJ+ENTREE pour avoir la formule en matricielle.
EQUIV va nous permettre de récupérer le numéro de ligne dans laquelle se trouve la combinaison de la date et du nom. INDEX va nous permettre d'afficher la donnée.

Code: Tout sélectionner
{=INDEX($C$2:$C$8;EQUIV(G2&F2;$A$2:$A$8&$B$2:$B$8;0))}


Des infos sur INDEX et EQUIV :

En H2, on obtient le résultat "Données 6 mars"

Aperçu :
Image


Si on veut une seule cellule dans le tableau cible, on peut combiner les deux étapes dans une même formule :

Code: Tout sélectionner
{=INDEX($C$2:$C$8;EQUIV(MAX(SI(F2=$B$2:$B$8;$A$2:$A$8;""))&F2;$A$2:$A$8&$B$2:$B$8;0))}


Aperçu :
Image

@+


Bonjour Sébastien,

Je sais qu'il n'est pas souhaitable de faire remonter un sujet aussi ancien, mais je tenais absolument à vous remercier pour le temps que vous avez pris à aider cette personne qui ne méritais sans doute pas autant étant donné qu'elle n'a même pas pris la peine de vous répondre.

Je rencontrais le même souci et votre réponse a résolue mon problème, et je ne pense pas être le seul que vous avez aidé ici ! :-D


Alors encore un grand merci de votre aide et des efforts que vous mettez pour apporter une réponse claire et concise, de la part de toutes les personnes dotées de principes qui comme moi sont en plein apprentissage de ce formidable outil qu'est Excel.

Bonne journée à vous et bonne continuation.

Cordialement,

Alexandre A.
Chameley
No0b
No0b
 
Messages: 1
Enregistré le: 29 Juin 2020 15:34

Re: RECHERCHEV et DATE

Messagepar Sebastien » 30 Juin 2020 20:09

Bonjour,

Content de savoir que ça ait pu servir à quelqu'un, même 4 ans après ;)

@+

Sébastien
Image
Avatar de l’utilisateur
Sebastien
Administrateur
Administrateur
 
Messages: 5561
Enregistré le: 14 Fév 2005 19:17
Localisation: LILLE

Re: RECHERCHEV et DATE

Messagepar Cuardo » 09 Juil 2020 16:38

Ca vient de me servir également, merci infiniment :)
Cuardo
No0b
No0b
 
Messages: 1
Enregistré le: 06 Juil 2020 09:30

Re: RECHERCHEV et DATE

Messagepar Ken Kingston » 20 Jan 2021 11:11

Je vous remercie énormément pour votre contribution.
C'est extrêmement clair et cela règle une question importante dont je ne trouvais pas la réponse.

Merci encore !
Ken Kingston
No0b
No0b
 
Messages: 1
Enregistré le: 20 Jan 2021 11:09

Re: RECHERCHEV et DATE

Messagepar miyoul » 22 Avr 2021 16:43

Merci infiniment Sebastien.
Je suis désolé mais j'ai un problème supplémentaire car moi la colonne date est composé de date et heure; et potentiellement c'est l'heure qui peux faire la différence; J'ai donc séparer les dates dans une colonne et les heures dans une autre , et j'ai appliqué la formule MAX sur la colonne Date uniquement sauf que j'ai des erreurs sur certaines lignes qui ont la même date et seul l'heure diffère.
Ma question est donc comment faire une formule qui incluera le max des dates tout en prenant en compte l'heure la plus récente stp?
Merci d'avance
miyoul
No0b
No0b
 
Messages: 2
Enregistré le: 22 Avr 2021 16:39

Re: RECHERCHEV et DATE

Messagepar Sebastien » 22 Avr 2021 19:07

Bonjour,
miyoul a écrit:Je suis désolé mais j'ai un problème supplémentaire car moi la colonne date est composé de date et heure; et potentiellement c'est l'heure qui peux faire la différence; J'ai donc séparer les dates dans une colonne et les heures dans une autre , et j'ai appliqué la formule MAX sur la colonne Date uniquement sauf que j'ai des erreurs sur certaines lignes qui ont la même date et seul l'heure diffère.
Ma question est donc comment faire une formule qui inclura le max des dates tout en prenant en compte l'heure la plus récente stp?

Plutôt que de séparer les dates des heures, il vaut mieux utiliser un seul champ qui contient toutes ces données et ne pas les séparer. En effet, un champ date peut avoir un format personnalisé qui tient compte de tous ces paramètres (même à la seconde si besoin).
Le format en question est jj/mm/aaaa hh:mm

La gestion des formats de cellule se fait via un clic droit sur la cellule > format de cellule > onglet nombre > Catégorie : Personnalisée

Voici un test fait avec une date à laquelle j'ai ajouté les heures et testé la formule max :
Image

En utilisant une seule cellule pour la notion de date / heure, la formule expliquée plus haut devrait fonctionner.

@+
Image
Avatar de l’utilisateur
Sebastien
Administrateur
Administrateur
 
Messages: 5561
Enregistré le: 14 Fév 2005 19:17
Localisation: LILLE

Re: RECHERCHEV et DATE

Messagepar miyoul » 23 Avr 2021 13:41

Bonjour Sébastien Merci pour ton retour;
Je peux t'envoyer mon fichier pour que tu teste par ce que moi ca ne marche pas stp ? la formule MAX avec une cellule personnalisé en Date heure (mm/jj/aa hh:mm AM/PM) me renvoie 0 :-(
Merci d'avance
miyoul
No0b
No0b
 
Messages: 2
Enregistré le: 22 Avr 2021 16:39

Re: RECHERCHEV et DATE

Messagepar Sebastien » 26 Avr 2021 19:39

Bonjour,

Tu peux partager un fichier en utilisant un service d'hébergement en ligne du type CJOINT et en collant le lien dans ton prochain message.

Attention à bien supprimer du fichier toutes les données personnelles et confidentielles avant de le partager avec les visiteurs du forum.

@+
Image
Avatar de l’utilisateur
Sebastien
Administrateur
Administrateur
 
Messages: 5561
Enregistré le: 14 Fév 2005 19:17
Localisation: LILLE

Re: RECHERCHEV et DATE

Messagepar intox45 » 28 Sep 2021 16:32

Bonjour à tous,

et oui 4 ans après je reprend votre sujet. plus ou moins. je ne suis pas un expert sur Excel mais la ca devient compliqué et je vais vous expliquer pourquoi !

pour faire simple j'ai un tableau avec des dates en colonne A des noms d'operateurs en colonne B et le nombre d'erreur fait par operateur sur la colonne c.

j'ai un deuxième onglet avec un top 5 qui me sort les 5 operateur par jour qui ont commis le plus d'anomalie. c'est de la que viens mon problème. je dois arriver à avoir mon classement d'operateur premier deuxième troisième etc... en lien avec le nombre d'erreur sur une date.

je souhaite avoir le classement des 5 operateur par jour qui auront commis le plus d'erreur.

voici mon tableau ou je rentre les données chaque jour.

Date User Nombre D'erreur
01/09/2021 CHIMICR 0
01/09/2021 DIASALR 3
01/09/2021 GABNICR 0
01/09/2021 IBRMUHR 4
01/09/2021 MENCHAR 2
01/09/2021 SALMOHR 0
01/09/2021 SYLLMAR 0
01/09/2021 WAKIDRR 1
01/09/2021 WILVAMR 0
02/09/2021 CHIMICR 0
02/09/2021 DENMOHR 3
02/09/2021 DIASALR 0
02/09/2021 GABNICR 0
02/09/2021 IBRMUHR 0
02/09/2021 JEMCORR 4
02/09/2021 SALMOHR 0
02/09/2021 STK01R 0
02/09/2021 SYLLMAR 0
02/09/2021 WAKIDRR 1
02/09/2021 WILVAMR 0

sur mon autre onglet (top 5)ça ce présente comme ceci:

Date
20/09/2021 la date fonctionne avec un filtre qui me permet d'extraire les données renseignées. si j'ai 20 ligne sur la date du 27/09 il devra prendre en compte toute cette plage de date.



operateur NOMBRE D'ERREURS
1 WADCAMR 3
2 SERDIOR 0
3 MAISTER 0
4 LAAZHOR 5
5 IVESERR 17

je ne sais pas si j'ai été clair. mais j'espère comprendre d'où viens mon erreur car la je commence à perdre espoir. :-(
intox45
No0b
No0b
 
Messages: 2
Enregistré le: 28 Sep 2021 16:17

Re: RECHERCHEV et DATE

Messagepar Sebastien » 28 Sep 2021 21:05

Bonjour,

Pour sortir un top 5 en fonction d'une date, vous devriez utiliser un tableau croisé dynamique (TCD).

SI on reprend vos données, il y a 3 colonnes qui serviront de champs dans le TCD. La date, l'utilisateur et le nombre d'erreur.

Aperçu du tableau de données :
Image

Sur la page en cours ou dans une autre page, cliquez sur Insertion > Tableau croisé dynamique.
Pour les données à analyser, sélectionnez la plage de votre tableau.
Sélectionnez les 3 champs et placez-les dans les blocs suivants du TCD :
  • Colonnes = date
  • Lignes = User
  • Valeurs = Somme de Nombre d'erreur.

Aperçu :
Image

Image


Pour obtenir le top pour la journée du 01/09, sélectionnez la date dans le filtre "étiquettes de colonnes".
Le TCD va se mettre à jour.

Faites un clic droit dans les données chiffrées du TCD, choisissez "Trier" et "Du plus grand au plus petit".
Vous avez votre top pour la date sélectionnée.

Image

La colonne total général vous permet également de faire des calculs sur plusieurs dates si besoin.

Enfin, vous pouvez copier / coller le TCD pour en garder un par jour (coller le en texte brut car si vous le collez en TCD, il se mettra à jour si vous bougez les données sources).

Aperçu :
Image

@+
Image
Avatar de l’utilisateur
Sebastien
Administrateur
Administrateur
 
Messages: 5561
Enregistré le: 14 Fév 2005 19:17
Localisation: LILLE

Re: RECHERCHEV et DATE

Messagepar intox45 » 29 Sep 2021 08:37

Merci pour votre retour et d'avoir pris le temps pour moi c'est vraiment gentil de votre part de donner de votre temps.

je ne souhaite pas de TCD. si j'arrive à vous envoyer mon fichier, peut être que cela serait plus simple. c'est un outil qui doit être intuitif sur une présentation plus jolie que le TCD et sans avoir à faire de copier coller. je ne suis pas loin de trouver la solution.

je vous transfert l'adresse du fichier je pense que ce sera plus simple https://www.cjoint.com/c/KIDhbEHe2Xs
intox45
No0b
No0b
 
Messages: 2
Enregistré le: 28 Sep 2021 16:17

Re: RECHERCHEV et DATE

Messagepar zinzineeee » 21 Oct 2021 14:29

Bonjour, après plusieurs semaines à essuyer des échecs dans mes recherches de formule, je me décide à demander de l'aide...
J'ai un tableau avec plusieurs onglets de mois (Mai à Avril) et un onglet Récap à la fin.
Chaque mois nous recevons des commandes donc dans chaque onglet de mois je saisis les références, le prix, et la date d'achat (entre autre).
Ces références sont toutes listées dans l'onglet Récap.
J'ai besoin quand dans cet onglet Récap, apparaisse la date de commande la plus récente. Dit comme ça, ça parait simple, mais je m'arrache les cheveux.
Voilà la formule que je voulais intégrer :
=MAX.SI(INDIRECT($P$3:$AA$3&"!$A:$A"));A2;INDIRECT($P$3:$AA$3&"!$K:$K")))

J'explique :
- J'ai saisi le nom des mois sur la feuille récap en P3 jusqu'à AA3
- Les références sont en colonne A donc je cherche dans chaque onglet si ma référence est présente,
- si elle est présente je veux la date la plus récente qui se trouve en colonne K

Si déjà j'arrive à faire ça je serai ravie.
Ensuite j'aurai besoin que le prix de la référence (colonne G des mes onglets mois) à cette date soit reporté dans ma colonne prix du Récap (colonne F)...

J'essaie de mettre le lien de mon tableau : https://cdcarrosserie-my.sharepoint.com ... w?e=t0ePIV

Merci de votre aide
zinzineeee
No0b
No0b
 
Messages: 1
Enregistré le: 21 Oct 2021 13:50


Retourner vers Forum d'entraide : Bureautique

 


  • Articles en relation
    Réponses
    Vus
    Dernier message

Qui est en ligne

Utilisateurs parcourant ce forum : Aucun utilisateur enregistré et 1 invité

  • Publicité