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

jvz3sio 6uxy4ga

Après automatisation l'onglet dépenses devrait se remplir comme cela:

2phne3b

Pouvez m'aidez s'il vous plait à concevoir cette fonction?

Merci

31excel-test.xlsx (9.63 Ko)

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

27small-countif.xlsx (11.53 Ko)

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:

bvxqysh

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'utilise Excel 2016.

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

24aggregate.xlsx (12.35 Ko)

D'accord, Grazie! merci beaucoup pour cette recherche!

Petite question, j'ai remarqué lorsqu'on saisit 2 fois la même date,il y'a un bug:

gqehp6w

Est-ce que c'est possible d'avoir le détail des montants comme ceci ?

kx0j6rh

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

6date-heure.xlsx (9.98 Ko)

Salut Yves!

Merci beaucoup pour le document !

J'aurais vu plutôt quelque chose comme ça appliqué dans mon cas:

2n7ruhi

Est-ce que c'est possible à réaliser et donc modifier la formule "Petite Valeur" utilisée précédemment?

Merci pour ton aide

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

Rechercher des sujets similaires à "recherche automatique informations"