Faire une recherchev à partir de dates en doublons

Bonjour à tous,

Tout d'abord bonne année et meilleurs vœux à tous !

Je débute sur Excel et je vous remercie pour ce forum car de nombreux cas mon permis de résoudre certains problèmes. Cependant, je bloque sur un cas et je ne sais pas si je m'y prends de la bonne façons :

Je souhaiterais créer un tableau de bord d’événements synchronisé à une base données. Il y a des événements qui se passent le même jour et je n'arrive pas à faire apparaître les deux dates. Avez-vous une solution à m'apporter ?

Je vous remercie par avance.

Cordialement,

Salut Max_MM et

je sais pas si j'ai bien compris, mais voir fichier

à mon avis il faut revoir la conception du fichier

Bonjour m3ellem1,

Tout d'abord merci pour votre réponse et pour votre temps.

J'essaie de concevoir un tableau de bord qui résume mois par mois les événements auxquels mon entreprise participe. Le but étant que sur la "Feuil1" les données proviennent automatiquement de la feuille "base de données Congrès" de façon claire, lisible et synthétique.

Par exemple, dans le fichier que je vous ai joint, en mars 2019 il y a deux événements le 29 mars 2019.

Dans ma colonne J j'ai donc fait une recherchev à partir des dates qui apparaissent dans la colonne AN (dates synchronisées avec le calendrier) et qui vont chercher la valeur dans la feuille "Base de données Congrès".

Or pour la recherchev, celle-ci prend uniquement la première valeur trouvée et pour le 29 mars il y a deux événements donc un seul des deux apparaît.

Le but final serait d'avoir en dessous "Informations congrès" les 3 colonnes "dates", "Noms congrès", "lieu" qui s'actualisent quand l'on change de mois ou d'année.

J'ai vu votre changement pour ma colonne lieu c'est top ! Cependant, si des événements viennent se rajouter avant en "dernière minutes" les cellules ne se mettent pas automatiquement.

Je ne sais pas si après ces explications vous y voyez plus clair sur ce que je cherche à faire et si c'est réalisable tout simplement ?

Peut-être que je ne m'y prends pas de la bonne façon et que d'autres formules permettraient de réaliser ce genre de tableau automatique ?

En tout cas je vous remercie encore et je vous ai joint le fichier de nouveau avec plus d'explications.

Cordialement,

Bonjour à tous

Peux-tu indiquer ta version Excel : les solutions en sont dépendantes

Concernant le tableau de l'onglet Base de données Congrès

• il ne devrait en aucun cas contenir de ligne vides
• il devrait avoir une nom signifiant et ne pas porter le nom automatique Tableau3
• quel est l'intérêt de découper a priori les débuts et fin en jour, mois et année alors qu'Excel sait utiliser une partie de date ?

Bonjour 78chris,

J'utilise la version 2016 d'Excel.

Concernant le tableau dans "Base de données Congrès":

• les lignes vides du tableau son pour anticiper les événements futurs dont les inscriptions se font tout au long de l'année.
• il est vrai que je n'ai pas renommé ce tableau pour l'instant.
• pour le découpage des dates c'était un conseil que l'on m'avait donné mais je peux les enlever si celles-ci sont vraiment inutiles.

Je débute sur Excel je n'ai pas encore les bons automatismes et les bonnes méthodes. Je suis à l'écoute de vos conseils et vous remercie pour votre temps.

RE

Un tableau structuré n'a pas besoin de lignes d'anticipation : les formules et formats se propagent dès la saisie d'une nouvelle ligne sous le tableau, ligne qui s'y incorpore automatiquement.

Tu compliques beaucoup les choses

Ce que tu cherches à obtenir peut être fait par un filtre avancé : voir Filtre Avancé.xlsx

Mais le filtre avancé doit être relancé à chaque modification de la base ou du choix ce qui nécessite du code pour ne pas refaire à chaque fois.

Tu as 2016 et on remplace le filtre avancé par PowerQuery : voir Congrès.xlsx

Mise à jour par Données, Actualiser tout en cas de changement de la base ou du choix

Tes MFC sont totalement à revoir : j'ai supprimé les 2 qui avaient des formules entre guillemets donc inopérantes et n'ai pas touché au reste mais là encore on pourrait simplifier énormément.

Re,

Je vous remercie pour votre temps et les conseils que vous m'avez donnés.

J'ai observé vos modifications et j'ai essayé de reproduire votre exemple cependant j'ai rencontré quelques difficultés :

• sur votre fichier "congrès.xslx" un message d'erreur s'affiche pour la macro d'actualisation, elle ne se met pas à jour automatiquement donc on doit cliquer sur "actualiser tout" mais dans l'idée c'est exactement ce que je cherche à faire.
• quand j'ai reproduit votre exemple, je n'arrive pas à faire apparaître les événements en fonction des dates sélectionnées, je ne trouve pas comment vous avez fait. Avez-vous une explication sur ce point ?
• dans la cellule AI7 vous avez mis "Choixan", je n'arrive pas à afficher l'année (le chiffre "2" se met car 2019 est la deuxième valeur) je dois surement rater quelque chose.
• je ne me suis pas penché énormément sur "Filtre Avancé" car je n'ai pas très bien compris son fonctionnement (étant donné que j'utilise Excel 2016 je n'ai pas besoin de cette fonction ?)

Je vous mets en pièce jointe l'exemple que j'ai essayé de reproduire pour que vous voyez les problèmes que je rencontre.

Si vous pouvez m'éclairer sur ces points.

Je vous remercie encore pour tous les conseils jusqu'à présent !

RE

J'avais mis une macro d'actualisation que j'ai supprimée pour fournir un fichier sans VBA, d'où le message d'erreur.

Je viens de remplacer le fichier dans le post précédent.

Comme tu as utilisé des contrôles (listes déroulantes) pour choisir le mois et l'année, je les ai associés aux cellules placées derrière, soit H4 et H5 puis ai récupéré l'année en G4 grâce à une fonction INDEX basée sur H4 et la liste des années (mise sous forme de tableau). Tu peux voir la formule en te déplaçant dans les cellules avec les flèches de déplacement et non la souris)

J'ai nommé les cellules G4 ChoixAn et H5 ChoixMois (Onglet formules, Gestionnaire de noms)

Comme tu utilisais les cellules AI7 et AK7 pour tes MFC, j'ai voulu les faire pointer sur les nouvelles cellules utilisées mais en AI7 j'aurais du mettre =H5

Le filtre avancé peut toujours être utile mais on va se concentrer sur PowerQuery

Je ne sais trop ce que tu as fait pour PowerQuery , a priori tu as établi un lien vers le classeur au lieu d'utiliser le tableau de données.

Ce que j'ai fait avec PowerQuery :

• se placer dans une cellule du tableau de données (renommé Base, sans lignes vides et avec la formule de la colonne B modifiée) et onglet Données, A partir d'un tableau : ce qui ouvre PowerQuery, y crée la requête Base (on voit les requêtes à gauche) et affiche deux actions dans cette requête à droite)
• sélectionner les colonnes Date début et Date fin, onglet Accueil, Type de données, Date et confirmer le remplacement du typage déjà fait
• ensuite récupérer les choix ainsi :
  onglet Accueil, Nouvelle Source, Autres Sources, Requête vide : ce qui crée Requête1 qu'on renomme An et on tape dans la barre de formule
  = Excel.CurrentWorkbook(){[Name="ChoixAn"]}[Content][Column1]{0}
• on refait à l'identique une secone requête renommée Mois avec la formule
  = Excel.CurrentWorkbook(){[Name="ChoixMois"]}[Content][Column1]{0}

On se place là où on veut le résultat et Données, Connexions existantes, choisir Base

Il faut mettre à jour par Données, Actualiser Tout, sinon il faut du VBA au changement de choix et à l’actualisation de cet onglet

RE,

Ça fonctionne parfaitement je vous remercie énormément pour ces explications claires et pour le temps que vous m'avez accordé !

Je te débute sur Excel donc d'autres problématiques apparaîtront pour moi mais c'est vraiment une chance d'avoir des personnes comme vous sur ce genre de forum !

Merci infiniment !

RE

Merci du retour

PowerQuery n'est pas du niveau débutant mais ici, à part la récupération des choix, c'est simple.

Alors que le filtre avancé avec des dates est compliqué.

Au plaisir de te croiser sur 2020 nouvelles questions, lol

Pense à compléter ton profil de ta version, cela facilite les échanges

[QUOTE] Max_MM

Tout d'abord merci encore pour votre aide et surtout pour vos explications qui m'ont permis de comprendre.

Cependant depuis que j'ai rajouté des congrès de l'année 2018 celle-ci ne fonctionnement plus et je ne comprend pas pourquoi ?

Le lien entre le tableau issu de la requête et la requête a été perdu. Tu as du faire une mauvaise manip.

Supprime le tableau et refais la dernière manip de mon poste du 3 janvier 2020, 17:20

"On se place là où on veut le résultat et Données, Connexions existantes, choisir Base"

Je travaille toujours sur la base données dans laquelle j'ai rajouté deux feuilles nécessaires pour obtenir les informations dont j'ai besoin.

Je rencontre un petit problème et deux autres un peu plus importants.

En suivant vos conseils d'organisation j'ai pu réaliser la base de la feuille et je rencontre quelques problèmes dans la mise en forme (en tant que débutant je dois surement ignorer d'autres méthodes plus simples qui doivent exister pour arriver à mon but) :

- le premier petit problème : je voudrais qu'une cellule affiche " " (un blanc) avec une fonction =SI, si la cellule d’accoter et vide mais celle-ci contient une formule qui est liée indirectement/directement à ma cellule. je n'arrive pas à trouver une solution pour ce problème.

On n'affiche surtout pas de blanc mais une chaîne vide "" ou un 0 (que l'on peut masquer par un format de nombre approprié)

Précise les cellules concernées afin qu'on comprenne où se situe le problème

- le deuxième problème : dans ma base de données j'ai une colonne "Participants" dans laquelle des initiales de plusieurs personnes apparaissent. Le but est que sur une feuille de synthèse je puisse voir le montant des congrès consacré à chaque personne. J'imagine que chaque initiale doit apparaître de façon unique dans les cellules j'ai donc pensé à l'outil convertir mais après cela je n'arrive pas à savoir comment retrouver ces initiales (cette explication est peut-être un peu floue je suis désolé)

Puisqu'on traite le tableau par PowerQuery, on peut découper la colonne dans PowerQuery.

Met un exemple de ta feuille synthèse pour qu'on voit comme c'est fait : TCD ou autre

- dernier problème : je souhaiterais réaliser des graphiques de synthèses montrant les montants consacrés au congrès sur une période que l'on peu choisir à l'aide de deux listes déroulante , or les graphiques peuvent comparer deux années (exemple: si sur les listes déroulantes on sélectionne 2018 et 2020 on verra uniquement ces deux années). Est-il possible de faire apparaître une période (2018, 2019, 2020) si on sélectionne 2018 et 2020 dans la liste déroulante ?

La meilleure solution serait sûrement un TCD et GCD avec une chronologie ou des segments pour choisir la période.

Lié à la question 2. On va faire une seconde requête qui découpe les participants et tu pourra l'utiliser pour toutes les synthèses et comparaisons. Pas le temps là mais je verrai ce soir

[/QUOTE]

Re,

Je vous remercie. Je vais continuer d'essayer en suivant vos conseils.

Re

Pour le tableau avec votre solution, celle-ci marche parfaitement en réduisant les critères.

J'ai essayé de rendre plus claire ce que je voulais dire car mes explications étaient complexes.

Merci d'avance pour votre temps et votre aide.

Cordialement,

RE

Es-tu sûr d'avoir 2016 ? Quand j'ouvre ton classeur dans 2019, il indique que le classeur a été créé avec une version plus récente...

Met ta version dans ton profil, afin qu'on l'ait toujours visible

Il y a une erreur dans la formule de filtre qui ne correspond pas à ce que j'avais indiqué

"onglet Ajouter une colonne, Colonne personnalisée : on donne le nom Filtre et la formule

=if (Date.Year([Date début])=An or Date.Year([Date fin])=An) and (Date.Month([Date début])=Mois or Date.Month([Date fin])=Mois) then 1 else 0"

Tu n'as saisi que la moitié de la formule et si tu as un congrès à cheval sur 2 mois, du 30 mars au 02 avril par par exemple, il n'apparaitra pas sur avril

Attention dans la base il y a parfois oui suivi d'un espace : pour EXcel ce n'est pas la même chose

Evite les fusions de cellules, inutiles dans 95% des cas et sources de nombreux problèmes.

Concernant les stats par participant : pour un congrès à 1500 tu comptes 1500 pour chaque participant quand ils sont 3 (ou plus de 1) ?

Pour les montants tu filtres sur Participation mais pas sur HELPP : est-ce normal ?

Oublie SOMME.SI pour ne garder que SOMME.SI.ENS qu'il y ait un ou n critères.

Mais globalement les stats sont plutôt à faire par TCD que par formules de façon à suivre l'évolution des sources.

Je pense que tu t'embrouille dans les requêtes : depuis la version 2016, chaque fois qu'on utilise une requête, PowerQuery la duplique et tu as du bidouiller cela. On en reparle plus tard.

Pour les choix sur l'onglet Congrès, j'avais posté un classeur sans le lien à la macro.

Donc soit tu enlèves ces liens, soit on remet la macro mais là cela génère inutilement des erreurs.

Re,

J'ai récemment mis à jour et lorsque je vais sur Fichier, Compte, à propos de Excel, il y a écrit Version 1912 (je me suis connecté sur mon compte office mis a disposition par l'école Microsoft Office 365 ProPlus). Je ne trouve pas dans les paramètres l'onglet pour rajouter ma version d'Excel (je suis allé dans tous les onglets du profil et panneau d'utilisateur).

Concernant la formule de filtre, je vous ai dit que je l'ai raccourci car lorsque je l'utilisait, celle-ci ne prenait pas les bonnes dates

Exemple : An :2019

Mois : 1

la formule me sélectionnait même des dates de 2018 (j'ai réellement essayé à de multiples reprises en relisant et vérifiant chaque élément pour finir ensuite avec la formule que vous avez vue, je vais réessayer peut-être que je suis passé à coté de quelque chose)

Ah exact je n'avais pas vu concernant les "oui", merci !

D'accord je vais éviter les fusions.

Pour les stats par participant :

exemple :

• congrès 1 : 1500€ participants: FR; MM; GR
• congrès 2 : 1500€ participants: MM;
• congrès 3: 1500€ participants: MM; FR

Stats par participant : MM : 4500€

FR: 3000€

GR: 1500€

Oui je filtre par "Participation" étant donné qu'il y a des congrès auxquels il se peut que l'on n'y participe pas et dans la feuille Congrès! il faut qu'il apparaisse uniquement les congrès auxquels nous participons.

Ça marche, merci du conseil.

D'accord, dans ce cas il faut que je regarde plus de tutos tu les TCD car j'ai une très faible connaissance à ce niveau.

C'est exact je ne comprends pas pour quand je mets mon tableau dans ma feuille Congrès!, il se rajoute en requête Liste_congrès(1) je ne sais pas pourquoi...

J'ai supprimer la macro pour le moment et lorsque le document sera terminé je mettrais en place toutes les macros nécessaires.

Merci encore pour votre temps.

Et pour les montants j'ai répondu à côté désolé : je filtre par Participation et non sur HELPP pour qu'il y ait le montant des congrès auxquels on participe. Il se peut que pour des congrès auxquels on participe la case HELPP soit vide aussi. j'ai donc pensé qu'il était plus pertinent de filtrer par Participation donc.

RE

Max_MM a écrit :

... Je ne trouve pas dans les paramètres l'onglet pour rajouter ma version d'Excel (je suis allé dans tous les onglets du profil et panneau d'utilisateur). Mets 365 :

Concernant la formule de filtre, je vous ai dit que je l'ai raccourci car lorsque je l'utilisait, celle-ci ne prenait pas les bonnes dates

Exemple : An :2019

Mois : 1

la formule me sélectionnait même des dates de 2018 (j'ai réellement essayé à de multiples reprises en relisant et vérifiant chaque élément pour finir ensuite avec la formule que vous avez vue, je vais réessayer peut-être que je suis passé à coté de quelque chose)

Pas chez moi !

C'est exact je ne comprends pas pour quand je mets mon tableau dans ma feuille Congrès!, il se rajoute en requête Liste_congrès(1) je ne sais pas pourquoi...

C'est un bug de Microsoft mais ils ont décidé de laisser en l'état

Pour qu'on puisse modifier sans problème la requête initiale, par exemple Liste_congrès, je remplace toutes les lignes de la requête Liste_congrès(2) par une ligne tapée dans la barre de formule

= Liste_congrès

Ainsi Excel continue à utiliser Liste_congrès(2) mais c'est Liste_congrès qui reste la source.

Pour les TCD, ton cas

• nécessite de croiser la table congrès et la table marketing pour le budget
• par ailleurs le fait que pour les participants tu additionnes plusieurs fois le montant du congrès mais pas pour organisateur ni prestataire, il faut 2 requêtes différentes.
  Comme des TCD n'ayant pas la même source ne peuvent partager les mêmes segments de sélection de période, cela oblige encore dupliquer les requêtes, certaines utilisées par les TCD dont le filtre est lié à une année, les autres dont le filtre est lié à une période de plusieurs années (budget comparatif)
  Et là encore il va falloir gérer les duplicatas liés au bug Microsoft

J'attends que tu sois sûr des synthèses à faire avant de finaliser.

Re,

Etant en déplacement, je ne peux pas continuer à travailler sur le document pour cette semaine.

Il devrait y avoir un tableau supplémentaire dans ma synthèse allant prendre les informations sur la feuille Base_marketing!.

J'essaierai au maximum d'y arriver en suivant vos conseils. En cas de problème je le posterai le document avec tous les éléments.

Merci encore pour tout ! Je suis très reconnaissant pour tous vos conseils !