Recherche automatique informations Excel
Bonsoir,
Dans mon document d'exemple sur Excel, il y'a 2 feuilles, la première "Saisie" ou l'on retrouve les dépenses des personnes rentrées en vrac et la seconde feuille "Dépenses" contient les dépenses individuelles.
J'aimerais que la feuille "Dépenses" récupère automatiquement les dépenses de Bernard et Valentine et les classe automatiquement dans le tableau. J'aimerais qu'a chaque fois que je saisie dans la feuille Saisie, automatiquement dans la feuille dépenses, les informations sont collectées selon le prénom
Après automatisation l'onglet dépenses devrait se remplir comme cela:
Pouvez m'aidez s'il vous plait à concevoir cette fonction?
Merci
Bonsoir,
crée trois tableaux, un pour Valentine, un pour Bernard, un pour tes saisies.
Dans le menu Outils de tableau, selectionne Ligne Total
OK, la formule est un peu complexe car on s'aventure sur des formules matricielles.
en premier lieu, on doit retrouver la plus petite date de depenses pour chaque. Si pas de depenses, il n'y doit pas avoir d'affichage de date, on ne veut pas avoir 15/12/17 sur le tableau de Bernard si il n'a rien depense a cette date.
Attention: Je ne suis pas sur du seperateur d'operateur dans Excel FR. J'ecris mes formules avec ",". Il se peut que le defaut soit ";"
Formule pour retrouver la date:
{=SIERREUR(PETITE.VALEUR(tblExpenses[date]*(tblExpenses[nom]=$J$2),NB.SI(tblExpenses[nom],"<>"&$J$2)+[@index]),"")}
Vu que c'est une formule matricielle, on doit la rentrer en tapant CTRL-SHIFT-ENTER, ce qui genere les {}
Formule pour retrouver le montant (hypothese de depart, il nous suffit de faire une SOMMESI.ENS()
=SOMME.SI.ENS(tblExpenses[montant],tblExpenses[nom],$J$1,tblExpenses[date],[@Date])
je te joins le fichier exemple. La partie Bernard est faite en tableau. a partie valentine en intervalle.
a bientot
Yves
Bonsoir,
Merci beaucoup pour tes explications, c'est un peu plus clair pour moi maintenant.
"Dans le menu Outils de tableau, sélectionne Ligne Total" Je n'ai pas trouvé ce menu, ou se trouve t'il?
Dans Excel FR le séparateur utilisé est le ";" effectivement.
J'ai une petite question, est-ce que c'est possible de simplifié un peu tout ça, j'aimerais que tout simplement dans le tableau "Dépenses de Bernard" qu'il se remplisse automatiquement au fur et à mesure qu'il détecte justement le prénom Bernard, mais sans l'option de classement des dates, car lors de ma saisi dans le tableau "Saisie en vrac " les dates seront classées de la plus ancienne à la plus récente (en partant de haut en bas) par moi même.
ça devrait donner quelque chose comme ça:
Merci d'avance pour ton aide !
Pas de souci.
Quelle version de Excel as-tu?
Quand tu es dans un tableau, il y a un menu qui s'affiche:
Outils de Tableau > Création > sélectionne la case a cocher "Ligne Total" - ceci dans la version Excel 2016.
Si le total ne s'affiche pas, ce n'est pas un problème. Tu insères simplement ton total en haut de colonne au lieu d'en bas. C'est plus facile de maintenir la formule SOMME(). Le fait que ton tableau va grandir, ton total se déplacera tout seul. Le Total en Tableau évite ce problème.
En ce qui concerne le classement des dates, il se fait tout seul, naturellement et tu n'as qu'a en effet remplir les dépenses et la répartition se fait automatiquement (dates, dépenses).
oui les formules sont un peu complexes. mais elle marchent.
Ciao
Yves
Merci beaucoup pour ton aide encore une fois !
J'ai trouvé la fonction pour le totale
J'ai décortiquer les formules que m'a proposé, c'est incroyable la puissance d'Excel !
Petite question, la formule "PETITE.VALEUR" utilise la colonne "index" pour avoir la valeur "k" , j'aimerais ne pas voir la colonne "index" dans le tableau. Comment adapter la formule?
Merci !
Dans le cas de ma formule, le mieux serait de déplacer la colonne index en dehors du champ. de changer la couleur du font en blanc, ou de cacher la colonne.
J'ai demandé sur un autre forum, car ta formule m'a fait transpire un peu a trouver. il y en a quelques autres qui n’utilisent pas d'index; En voici une avec la fonction AGREGAT() que je n'ai pas vraiment utilisée avant mais qui me semble très puissante. Il faut l’étudier un peu.
Ciao
Yves
haha!
oui, la fonction PETITE.VAlEUR est indexée sur les dates avec le critère B ou V.
Donc si deux valeurs sont identiques la fonctionne prendra 2 fois la plus petite; donc bug
le plus simple serait de:
- consolider les deux lignes en une seule
- considérer DATE et HEURE en colonne A au lieu de DATE uniquement. Cela permettrait de minimiser les doublons.
Yves
D'accord d'accord
Du coup, quel sont les paramètres à rentrer dans la formule pour que cela prenne la date et l'heure afin de minimiser les doublons?
la meme formule exactement
tu changes juste la colonne A de maniere a ce qu'elle soit formattee en date et heure (en s'assurant que l'heure soit changee)
la meme formule exactement
tu changes juste la colonne A de maniere a ce qu'elle soit formattee en date et heure (en s'assurant que l'heure soit changee)
D'accord merci beaucoup!
Encore une petite question,
Est-ce que c'est possible de mettre la date dans une colonne et l'heure dans une autre et faire fonctionner la formule "petite.valeur"? (je pose cette question car je trouve ça plus clair à l'œil de séparer ces informations)
Merci
pour séparer date et heure utilise ARRONDI.INF()
la différence entre une date et une heure c'est une question de virgules
dis-moi si tu n'y arrives pas
D'accord,
Pourrais-tu me montrer s'il te plaît comment appliquer la formule car je ne sais pas trop comment faire.
Merci
salut Elwcel,
voici un petit fichier te montrant la relation entre date et heure'
les dates comme tu sais sont numériques dans Excel; les heures sont décimales, avec 0 = minuit; 0,5 = midi; 0,25 = 6h; 0,75 = 18h; etc
Donc pour séparer les heures de dates (qui comprennent le temps), tu prends l'arrondi inférieur =ARRONDI.INF()
Ciao
Yves
il s'agit d'une demande nouvelle, vu que l'heure (temps) ne faisait pas partie de ton premier jet; la formule "plutôt" est donc un peu inadaptée. mais bon; on n'est pas la pour apprendre le français
Pourquoi veux-tu modifier la formule petite valeur? le tableau reste le même en fonction de Bernard ou Valentine en ordre croissant de dates, non?
Quelle formule/fonction as-tu essaye d'utiliser tout seul?
Dans ce cas précis:
Est-il possible que Bernard aie des heures pour le même jour? si oui, il faut appliquer une SOMME.SI() identique a celle de la colonne revenu; si non il suffit d'une RECHERCHEV() ou un INDEX(EQUIV) en faisant référence a la date ET a l’employé, en s'assurant que la fonction soit matricielle.
Oui c'est vrais haha
En faites, tout ce que tu m'a montré c'est vraiment parfait!
Je voudrais juste savoir si c'est possible avec le même fonctionnement, avoir la colonne heure au lieu de la date et heure dans la même cellule pour une lecture plus facile et donc adapter petite.valeur pour qu'elle fasse exatement le même travail c'est à dire de classer par ordre croissant les dates et heures auxquels apparaissent les transactions pour chaque personnes.
Je sais pas si tu vois ce que je veux dire?
Bonjour,
pourquoi vouloir dupliquer les données alors qu'un simple filtre automatique te donne la même chose ?
transforme ta plage en Tableau et tu pourras ajouter la ligne des Totaux, et plus aucune formule
eric
Eiwcel,
Je te suggère d'essayer d'abord de ton cote , non?
Tout a fait ok pour t'aider a comprendre les formules et ce que tu fais, mais l’idée c'est que toi tu puisse progresser et apprendre a réutiliser ces formules.
La fonction PETITE.VALEUR, que l'on a remplacée par AGREGAT(), plus élégante; cette formule nous a aidée a peupler les tableaux pour Bernard et Valentine et les classer par date dans l'ordre croissant. Je confirme que ces fonctions ne marchent que si tu as une seule date par personne. Si tu entres deux fois la même date pour la même personne et des revenus différents, ça va créer un doublon.
Donc, a partir de la, on a inséré la fonction SOMME.SI.ENS() pour calculer la somme des revenus correspondant a la date et a l’employé en question.
Vu qu'on ne peut avoir qu'une transaction par jour par employé, on n'a pas besoin de classer les heures (il n'y en a qu'une seule par jour). les heures se mettent tranquillement en insérant simplement une RECHERCHEV(), mais avec deux paramètres (donc matricielle). Commence par rentrer une simple fonction RECHERCHEV() avec la valeur date uniquement pour le moment.
Dis-moi ou tu bloques,
Bonjour,
pourquoi vouloir dupliquer les données alors qu'un simple filtre automatique te donne la même chose ?
transforme ta plage en Tableau et tu pourras ajouter la ligne des Totaux, et plus aucune formule
eric
Bonsoir;
Je n'ai jamais utilisé les filtres automatiques sauf pour colorer des données, pourrais-tu me montrer comment ça fonctionne s'il te plait?
Pour les totaux j'ai trouvé la formule du coup, merci ! :Wink:
Eiwcel,
Je te suggère d'essayer d'abord de ton cote , non?
Tout a fait ok pour t'aider a comprendre les formules et ce que tu fais, mais l’idée c'est que toi tu puisse progresser et apprendre a réutiliser ces formules.
La fonction PETITE.VALEUR, que l'on a remplacée par AGREGAT(), plus élégante; cette formule nous a aidée a peupler les tableaux pour Bernard et Valentine et les classer par date dans l'ordre croissant. Je confirme que ces fonctions ne marchent que si tu as une seule date par personne. Si tu entres deux fois la même date pour la même personne et des revenus différents, ça va créer un doublon.
Donc, a partir de la, on a inséré la fonction SOMME.SI.ENS() pour calculer la somme des revenus correspondant a la date et a l’employé en question.
Vu qu'on ne peut avoir qu'une transaction par jour par employé, on n'a pas besoin de classer les heures (il n'y en a qu'une seule par jour). les heures se mettent tranquillement en insérant simplement une RECHERCHEV(), mais avec deux paramètres (donc matricielle). Commence par rentrer une simple fonction RECHERCHEV() avec la valeur date uniquement pour le moment.
Dis-moi ou tu bloques,
Bonsoir Yves,
Je suis complètement d'accord avec toi, j'ai justement créé ce poste pour apprendre davantage sur l'utilisation des formules sur Excel
Du coup aujourd'hui j'ai essayé de voir si c'est possible d'éviter les doublons avec "INDEX et EQUIV" mais c'est toujours le même problème, je n'arrive pas à trouver la solution :/
J'ai joint le fichier à ce post pourrais tu jeter un petit coup d'œil s'il te plaît?
Merci