Obtenir données selon plusieurs critères
Fermé
Shaarlun23456
-
23 juil. 2014 à 12:14
Theo.R Messages postés 575 Date d'inscription vendredi 11 juillet 2014 Statut Membre Dernière intervention 30 mars 2016 - 24 juil. 2014 à 17:17
Theo.R Messages postés 575 Date d'inscription vendredi 11 juillet 2014 Statut Membre Dernière intervention 30 mars 2016 - 24 juil. 2014 à 17:17
A voir également:
- Obtenir données selon plusieurs critères
- Reinstaller windows sans perte de données - Guide
- Supprimer les données de navigation - Guide
- Obtenir windows 10 - Accueil - Mise à jour
- Tnt base de données vide ✓ - Forum TNT / Satellite / Réception
- Trier des données excel - Guide
6 réponses
Theo.R
Messages postés
575
Date d'inscription
vendredi 11 juillet 2014
Statut
Membre
Dernière intervention
30 mars 2016
31
23 juil. 2014 à 15:56
23 juil. 2014 à 15:56
La solution dans le doc :
https://www.cjoint.com/?DGxp3NsFNWO
j'ai enregistré pour format excel 2003 normalement vous n'aurez pas de souci.
Bonne continuation
https://www.cjoint.com/?DGxp3NsFNWO
j'ai enregistré pour format excel 2003 normalement vous n'aurez pas de souci.
Bonne continuation
Merci pour votre réponse.
Mais cela ne correspond pas vraiment à mon besoin. Voici un lien présentant le tableau dont je dispose. En rouge ce que je souhaite pouvoir trouver "facilement".
http://cjoint.com/?3GxqIl6Fn0G
Ces données étant traité dans un cadre professionnel, je vais tenter d'expliquer la situation en même si les caractèristiques ne correspondent à rien de concret :
Pour un prénom, je peux avoir un nom avec des variantes [1], [2]...
Je dois reprendre dans un tableau qui recherche les correspondances des prénoms avec le reste des informations (nom, heures, nombre de jour) mais en prenant les données les plus représentative (c'es à dire celle avec le plus grande nombre de jour).
Je ne souhaite pas juste faire un calcul mais créer un 2e tableau (bien plus petit que le premier) reprenant les valeurs les plus representatives.
Est ce que je suis assez clair ?
Merci à vous
Cdt
Mais cela ne correspond pas vraiment à mon besoin. Voici un lien présentant le tableau dont je dispose. En rouge ce que je souhaite pouvoir trouver "facilement".
http://cjoint.com/?3GxqIl6Fn0G
Ces données étant traité dans un cadre professionnel, je vais tenter d'expliquer la situation en même si les caractèristiques ne correspondent à rien de concret :
Pour un prénom, je peux avoir un nom avec des variantes [1], [2]...
Je dois reprendre dans un tableau qui recherche les correspondances des prénoms avec le reste des informations (nom, heures, nombre de jour) mais en prenant les données les plus représentative (c'es à dire celle avec le plus grande nombre de jour).
Je ne souhaite pas juste faire un calcul mais créer un 2e tableau (bien plus petit que le premier) reprenant les valeurs les plus representatives.
Est ce que je suis assez clair ?
Merci à vous
Cdt
Theo.R
Messages postés
575
Date d'inscription
vendredi 11 juillet 2014
Statut
Membre
Dernière intervention
30 mars 2016
31
23 juil. 2014 à 17:35
23 juil. 2014 à 17:35
Merci pour le doc, c'est plus simple ;)
Voici un essai avec le même modèle de formule qu'auparavant, cela convient ?
https://www.cjoint.com/?DGxrGY6f8iL
P.S: je n'ai pas bien saisi les explications de vos attentes dans votre message, mais en tout cas j'arrive aux résultats que vous souhaitiez.
Voici un essai avec le même modèle de formule qu'auparavant, cela convient ?
https://www.cjoint.com/?DGxrGY6f8iL
P.S: je n'ai pas bien saisi les explications de vos attentes dans votre message, mais en tout cas j'arrive aux résultats que vous souhaitiez.
Merci pour votre réponse cela marche comme souhaité.
Serait il possible d'avoir quelques lignes d'explication ? Si vous n'avez pas le temps, se n'est pas grave merci encore.
Cdt
Serait il possible d'avoir quelques lignes d'explication ? Si vous n'avez pas le temps, se n'est pas grave merci encore.
Cdt
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Theo.R
Messages postés
575
Date d'inscription
vendredi 11 juillet 2014
Statut
Membre
Dernière intervention
30 mars 2016
31
24 juil. 2014 à 12:13
24 juil. 2014 à 12:13
Bonjour
Pas de soucis pour l'explication ! :) Ça sera long et fastidieux mais ça en vaut la peine ;)
Je garde l'exemple https://www.cjoint.com/?DGxrGY6f8iL
La formule finale est la suivante :
(N.B: j'ai pris l'exemple de la formule en C14 pour afficher l'heure de début de Richard BERNARD)
=INDEX(C$2:C$9;EQUIV(1;(ESTNUM(TROUVE($B14;$A$2:$A$9)))*($B$2:$B$9=$A14)*($E$2:$E$9=MAX(SI(($B$2:$B$9=$A14)*(ESTNUM(TROUVE($B14;$A$2:$A$9)));$E$2:$E$9)));0))
Les infos sources qui peuvent être modifiées sont :
- Colonne avec les horaires de début OU de fin : C$2:C$9 OU D$2:D$9
- Nom à rechercher : $B14
- Zone dans laquelle rechercher ce Nom : $A$2:$A$9
- Prénom à rechercher simultanément: $A14
- Zone dans laquelle rechercher ce Prénom : $B$2:$B$9
- Colonne avec les Nombres de jour dont il faut extraire le Maximum : $E$2:$E$9
Une fois ces données adaptées à la disposition des informations, voici la structure de la formule :
La fonction INDEX combinée avec EQUIV sert à afficher une valeur qui correspond à une colonne donnée d'une part et à une ligne déterminée par EQUIV avec des conditions d'autre part. On a la forme générale :
INDEX([Colonne dans laquelle choisir la donnée à afficher];EQUIV(détermination de ligne selon des conditions))
Pour la colonne, nous avons dans notre exemple C$2:C$9 qui correspond à l'horaire de début (c'est bien ce qu'on veut afficher, reste à voir quelle ligne !)
Reste à déterminer les conditions pour trouver la ligne : l'intérieur de la fonction EQUIV. J'ai utilisé une forme matricielle à plusieurs conditions, qui, en bref, s'utilise de la manière suivante :
EQUIV(1;[Condition1]*[Condition2]*...*[ConditionK];0)
On obtient ainsi la ligne remplissant toutes ces conditions simultanément
Précisions enfin le contenu des conditions, on a :
Condition1 = (ESTNUM(TROUVE($B14;$A$2:$A$9)))
Condition2 = ($B$2:$B$9=$A14)
Condition3 = ($E$2:$E$9=MAX(SI(($B$2:$B$9=$A14)*(ESTNUM(TROUVE($B14;$A$2:$A$9)));$E$2:$E$9)))
Explications Sens/Formule:
Condition1 - Sens : le nom à chercher doit être contenu dans la colonne des Noms
Formule : combinaisons classique de ESTNUM avec TROUVE qui affiche VRAI ssi le premier terme donné est contenu dans le deuxième. Dans notre cas, en 1er terme nous avons le nom à chercher ($B14) qui doit être contenu dans la colonne des noms ($A$2:$A$9)
Condition2 - Sens : Le prénom doit être STRICTEMENT le même que celui cherché
Formule : un simple égalité enter le prénom cherché ($A14) et la colonne des prénom ($B$2:$B$9). Si on avait voulu faire comme avec le nom on serait passé par : (ESTNUM(TROUVE($A14;$B$2:$B$9)))
Condition3 : Sens : le Nombre de Jours doit être le plus grand parmi les lignes contenant le même Nom (non strictement) ET Prénom (strictement)
Formule : égalité entre la colonne des Nbres de jours ($E$2:$E$9) et le Maximum avec conditions d'avoir sur la même ligne le Nom et le Prénom cherchés (toujours non strict pour le nom et strict pour le prénom). La fonction MAX(SI(...)) est souvent utilisée et fonctionne ainsi :
MAX(SI([Condition1]*...*[ConditionK];[Valeur à afficher si vrai]))
Les conditions sont du même type que celles déjà évoquées ci-dessus.
Au final, on arrive avec le modèle de fonction suivant :
=INDEX([Colonne dans laquelle choisir la donnée à afficher];EQUIV(1;[Condition1]*[Condition2]*...*[ConditionK];0))
Le tout est à valider en Formule Matricielle (Ctrl+Shift+Entrer) et voilà !
L'explication est assez fastidieuse la comprendre vous permettra d'adapter la formule, voire de l'améliorer/corriger selon son utilisation future.
Bonne continuation,
Pas de soucis pour l'explication ! :) Ça sera long et fastidieux mais ça en vaut la peine ;)
Je garde l'exemple https://www.cjoint.com/?DGxrGY6f8iL
La formule finale est la suivante :
(N.B: j'ai pris l'exemple de la formule en C14 pour afficher l'heure de début de Richard BERNARD)
=INDEX(C$2:C$9;EQUIV(1;(ESTNUM(TROUVE($B14;$A$2:$A$9)))*($B$2:$B$9=$A14)*($E$2:$E$9=MAX(SI(($B$2:$B$9=$A14)*(ESTNUM(TROUVE($B14;$A$2:$A$9)));$E$2:$E$9)));0))
Les infos sources qui peuvent être modifiées sont :
- Colonne avec les horaires de début OU de fin : C$2:C$9 OU D$2:D$9
- Nom à rechercher : $B14
- Zone dans laquelle rechercher ce Nom : $A$2:$A$9
- Prénom à rechercher simultanément: $A14
- Zone dans laquelle rechercher ce Prénom : $B$2:$B$9
- Colonne avec les Nombres de jour dont il faut extraire le Maximum : $E$2:$E$9
Une fois ces données adaptées à la disposition des informations, voici la structure de la formule :
La fonction INDEX combinée avec EQUIV sert à afficher une valeur qui correspond à une colonne donnée d'une part et à une ligne déterminée par EQUIV avec des conditions d'autre part. On a la forme générale :
INDEX([Colonne dans laquelle choisir la donnée à afficher];EQUIV(détermination de ligne selon des conditions))
Pour la colonne, nous avons dans notre exemple C$2:C$9 qui correspond à l'horaire de début (c'est bien ce qu'on veut afficher, reste à voir quelle ligne !)
Reste à déterminer les conditions pour trouver la ligne : l'intérieur de la fonction EQUIV. J'ai utilisé une forme matricielle à plusieurs conditions, qui, en bref, s'utilise de la manière suivante :
EQUIV(1;[Condition1]*[Condition2]*...*[ConditionK];0)
On obtient ainsi la ligne remplissant toutes ces conditions simultanément
Précisions enfin le contenu des conditions, on a :
Condition1 = (ESTNUM(TROUVE($B14;$A$2:$A$9)))
Condition2 = ($B$2:$B$9=$A14)
Condition3 = ($E$2:$E$9=MAX(SI(($B$2:$B$9=$A14)*(ESTNUM(TROUVE($B14;$A$2:$A$9)));$E$2:$E$9)))
Explications Sens/Formule:
Condition1 - Sens : le nom à chercher doit être contenu dans la colonne des Noms
Formule : combinaisons classique de ESTNUM avec TROUVE qui affiche VRAI ssi le premier terme donné est contenu dans le deuxième. Dans notre cas, en 1er terme nous avons le nom à chercher ($B14) qui doit être contenu dans la colonne des noms ($A$2:$A$9)
Condition2 - Sens : Le prénom doit être STRICTEMENT le même que celui cherché
Formule : un simple égalité enter le prénom cherché ($A14) et la colonne des prénom ($B$2:$B$9). Si on avait voulu faire comme avec le nom on serait passé par : (ESTNUM(TROUVE($A14;$B$2:$B$9)))
Condition3 : Sens : le Nombre de Jours doit être le plus grand parmi les lignes contenant le même Nom (non strictement) ET Prénom (strictement)
Formule : égalité entre la colonne des Nbres de jours ($E$2:$E$9) et le Maximum avec conditions d'avoir sur la même ligne le Nom et le Prénom cherchés (toujours non strict pour le nom et strict pour le prénom). La fonction MAX(SI(...)) est souvent utilisée et fonctionne ainsi :
MAX(SI([Condition1]*...*[ConditionK];[Valeur à afficher si vrai]))
Les conditions sont du même type que celles déjà évoquées ci-dessus.
Au final, on arrive avec le modèle de fonction suivant :
=INDEX([Colonne dans laquelle choisir la donnée à afficher];EQUIV(1;[Condition1]*[Condition2]*...*[ConditionK];0))
Le tout est à valider en Formule Matricielle (Ctrl+Shift+Entrer) et voilà !
L'explication est assez fastidieuse la comprendre vous permettra d'adapter la formule, voire de l'améliorer/corriger selon son utilisation future.
Bonne continuation,
Whouaou ! Merci de l'explication. Un peu longue mais très claire.
Concernant la première condition vous dites
"combinaisons classique de ESTNUM avec TROUVE qui affiche VRAI ssi le premier terme donné est contenu dans le deuxième"
Or où s'arrête le premier terme ? Je dois assurer une correspondant où la donnée rechercher en $A14 possède "/" alors que dans les plages de données recherchés, il n'y a pas ce caractère. Je possède une erreur sur une modification du fichier et je suppose que l'erreur vient de là (ou bien est ce autre chose mais pour l'heure, je n'ai pas trouvé quoi).
Est il possible de ne pas mettre des plages de cellules mais des colonnes que l'on aurait nommé ? En effet, je dois faire cela de nombreuses fois et donc cela rendrait la copie plus facile.
Merci,
Cdt
Concernant la première condition vous dites
"combinaisons classique de ESTNUM avec TROUVE qui affiche VRAI ssi le premier terme donné est contenu dans le deuxième"
Or où s'arrête le premier terme ? Je dois assurer une correspondant où la donnée rechercher en $A14 possède "/" alors que dans les plages de données recherchés, il n'y a pas ce caractère. Je possède une erreur sur une modification du fichier et je suppose que l'erreur vient de là (ou bien est ce autre chose mais pour l'heure, je n'ai pas trouvé quoi).
Est il possible de ne pas mettre des plages de cellules mais des colonnes que l'on aurait nommé ? En effet, je dois faire cela de nombreuses fois et donc cela rendrait la copie plus facile.
Merci,
Cdt
Theo.R
Messages postés
575
Date d'inscription
vendredi 11 juillet 2014
Statut
Membre
Dernière intervention
30 mars 2016
31
24 juil. 2014 à 17:17
24 juil. 2014 à 17:17
Aucun souci pour ça !
Le tout continuera de fonctionner tant que les "formats" des plages restent cohérents. Par format j'entends l'étendue des lignes prises en compte. Dans mon exemple, j'ai pris les lignes 1 à 9 pour définir mes matrices, ça peut être n'importe quoi (disons m et n) tant qu'on garde le format Am:An pour la colonne A, Bm:Bn pour B, etc. Possibilité de définir des colonnes entières (format alors comme suit : A:A, B:B, etc.).
Mon conseil pour être efficace et garder la ligne 1 pour les titres de colonnes : Définir les plages sur les lignes 2 à 9000 : A2:A9000 ; B2:B9000, etc. Vous pouvez aussi nommer ces plages pour aller plus vite dans les formules. Mais attention aux "$" que j'ai utilisés pour étendre la formule comme il faut.
Cordialement,
Le tout continuera de fonctionner tant que les "formats" des plages restent cohérents. Par format j'entends l'étendue des lignes prises en compte. Dans mon exemple, j'ai pris les lignes 1 à 9 pour définir mes matrices, ça peut être n'importe quoi (disons m et n) tant qu'on garde le format Am:An pour la colonne A, Bm:Bn pour B, etc. Possibilité de définir des colonnes entières (format alors comme suit : A:A, B:B, etc.).
Mon conseil pour être efficace et garder la ligne 1 pour les titres de colonnes : Définir les plages sur les lignes 2 à 9000 : A2:A9000 ; B2:B9000, etc. Vous pouvez aussi nommer ces plages pour aller plus vite dans les formules. Mais attention aux "$" que j'ai utilisés pour étendre la formule comme il faut.
Cordialement,