Bienvenue sur les forums conseil & création · S'enregistrer

Se connecter automatiquement

Forums » Tableurs et logiciels de gestion (Money, Excel, Open Office...) » Améliorer la recherche dans Excel · Envoyer le lien

Par Biniozet, le 20 Juil 2005, 15:16 Revenir en haut de page Répondre en citant

Bonjour,
Pour compléter un classeur réservé à une simple comptabilité ménagère, j'ai créé une feuille recevant le résumé de toutes les opérations de l'année et conçue ainsi : colonne A=date, colonne B=libellé de l'opération,C=objet de l'opération,D=crédit,E=débit.
Je me sers de cette page pour trouver à l'aide de RECHERCHE les diverses apparitions d'une opération inscrite en B ou C. Je voudrais améliorer cette recherche en faisant apparaître dans 2 cellules qq, par expl F1 et G1, le total des sommes de D ou E trouvées lors de la recherche.
La longueur de la feuille est variable selon l'année, la recherche ne porte que sur les colonnes B ou C et les réponses sont soit crédit, soit débit sans qu'il y ait mélange des deux.
Il serait intéressant de pouvoir commencer la recherche là où se trouve le curseur et obtenir l'arrêt quand on retrouve la première apparition trouvée;
Y a-t-il une solution avec Excel 2000 ?
Merci pour les conseils.
Par Cédric, le 20 Juil 2005, 23:54 Revenir en haut de page Répondre en citant

Salut,

Biniozet a écrit:
Je me sers de cette page pour trouver à l'aide de RECHERCHE


Tu parles de la fonction recherche ( formule =RECHERCHE(...) ) ou du menu (Edition / Rechercher) ?
Par Biniozet, le 21 Juil 2005, 16:14 Revenir en haut de page Répondre en citant

Bonjour,
En fait j'utilise le menu Edition/Rechercher. Je peux ainsi inscrire un libellé par expl : Leclerc (c'est la colonne B) ou un objet par expl : Essence (concerne alors la colonne C), et trouver toutes les opérations effectuées sur l'année mais sans pouvoir obtenir le total des sommes correspondantes et c'est ce total que j'aimerais faire apparaître automatiquement.
Par Cédric, le 21 Juil 2005, 19:12 Revenir en haut de page Répondre en citant

Citation:
En fait j'utilise le menu Edition/Rechercher.


Une question : connais-tu le Filtre Automatique d'Excel ? Ca serait beaucoup plus simple que le menu Rechercher !

  1. sélectionnes une cellule dans ton tableau
  2. Menu Données / Filtrer / Filtre automatique
  3. Il y a des flêches qui apparaissent sous tes libellés de colonne. Cliques dessus... et vois ce que tu en penses.


Citation:
mais sans pouvoir obtenir le total des sommes correspondantes et c'est ce total que j'aimerais faire apparaître automatiquement.

Il va falloir utiliser une formule matricielle. Exemple simple : tu veux en F1 et G1, le total des sommes de D et E (débit/crédit) selon la chaîne recherchée dans la colonne B (libellé). La recherche est saisie en F2.

Attention : les formules en F1 et G1 sont des formules matricielles. Il faut donc, après les avoir saisies (ou copié/collé), valider par [Ctrl+Shift+Entrée] et pas [Entrée] comme on a l'habitude de le faire.
  • F2 : Leclerc [Entrée]
  • F1 : =SOMME(SI($B$2:$B$10000=F1;$D$2:$D$10000)) [Ctrl+Shift+Entrée]
  • G1 : =SOMME(SI($B$2:$B$10000=F1;$E$2:$E$10000)) [Ctrl+Shift+Entrée]


Avec Visual Basic : il est possible d'aller plus loin avec Visual basic, et faire en sorte de lier les deux fonctions (filtre automatique et résultats débit/crédit de la recherche). En saisissant le texte de recherche, le tableau est filtré automatiquement et les totaux affichés en même temps.
Arrow ça t'intéresserait ?
Par Biniozet, le 22 Juil 2005, 18:19 Revenir en haut de page Répondre en citant

Bonjour

  • Je ne connaissais pas le filtre automatique d'Excel, je l'ai essayé et il est parfait pour avoir une vision globale des apparitions d'une opération donnée et je l'utiliserai de préférence au menu RECHERCHER .
  • Par contre pour obtenir le total j'ai essayé les formules indiquées en les adaptant ainsi : Recherche colonne B de B6 à B 10000; Inscription de l'opération recherchée en G 14, Formule pour totaliser les crédits en G15 et débits en H15. J'ai donc sélectionné G15 et j'ai écrit ceci:
    =SOMME(SI(§B§6:§B§10000=G14;§D§6:§D§10000))
    La formule me semble correcte et pourtant j'obtiens un message d'erreur, j'ai remplacé G14 PAR G15 sans succès. Je ne vois pas mon erreur?
  • Je suis bien sur intéressé si avec une macro et Visual basic il est possible d'obtenir une fonction de recherche et de totalisation automatique. Toutefois si je peux en comprendre à peu près le sens je ne connais pas le vocabulaire exact et la syntaxe de Vbasic Embarassed
Par Cédric, le 22 Juil 2005, 19:21 Revenir en haut de page Répondre en citant

Citation:
La formule me semble correcte et pourtant j'obtiens un message d'erreur, j'ai remplacé G14 PAR G15 sans succès. Je ne vois pas mon erreur?

As-tu bien suivi toutes les instructions... ? surtout faire [Ctrl+Shift+Entrée] au lieu de [Entrée] pour valider la formule ?

Citation:
Recherche colonne B de B6 à B 10000; Inscription de l'opération recherchée en G 14

Si tu mets les résultats sur une ligne > à 6 (début du filtre automatique), tu devras parfois modifier le filtre en cours... je m'explique : si tu filtres ton tableau, les lignes qui ne correspondent pas au critère recherché seront cachées par Excel. Si les lignes 14/15 sont sur une des lignes cachées, tu ne pourras pas y accéder dans modifier les critères.

Arrow pour ne pas avoir ce problème, tu devrais mettre ta recherche en G1 / G2 / H2 (enfin pas plus bas que la ligne 5).

Citation:
Je suis bien sur intéressé si avec une macro et Visual basic il est possible d'obtenir une fonction de recherche et de totalisation automatique. Toutefois si je peux en comprendre à peu près le sens je ne connais pas le vocabulaire exact et la syntaxe de Vbasic

Ok je mettrai un fichier en ligne dès que possible (n'hésites pas à me rappeler à l'ordre si ça dure trop longtemps, ça m'arrive d'oublier Embarassed )
Par Biniozet, le 23 Juil 2005, 16:34 Revenir en haut de page Répondre en citant

Bonjour et merci.
En utilisant tes conseils la formule fonctionne, toutefois Excel 2000 l'accepte mais en n'utilisant pas §.
Citation:
F1 : =SOMME(SI($B$2:$B$10000=F1;$D$2:$D$10000))

devient donc : =SOMME(SI(B2:B10000=F1;D2:D10000)). Mystère de cette bête capricieuse appelé PC. Exclamation
Je n'oublierai pas de réclamer pour la macro mais déjà avec le filtre automatique plus les formules de calcul j'ai bien avancé dans ce que je désirais obtenir. Very Happy
@+
Par Cédric, le 23 Juil 2005, 19:41 Revenir en haut de page Répondre en citant

Citation:
toutefois Excel 2000 l'accepte mais en n'utilisant pas §.

Euuuuh... c'est " $ ", pas " § " ! Faute de frappe sur le forum ? Parce que si tu utilisais §, c'est normal qu'Excel ne l'ait pas accepté !

En principe, Excel 2000 doit fonctionner avec les références absolues (donc avec le signe $)... mais pour ce que tu en fais ici, ça peut fonctionner sans.
Par Biniozet, le 24 Juil 2005, 10:36 Revenir en haut de page Répondre en citant

Bonjour.
Confused Confused Embarassed Je n'avais pas assez regardé mon clavier et je tapais § au lieu de $ tout en étant persuadé de taper $. Aux innocents les mains pleines, j'ai eu de la chance que la formule sans $ fonctionne. Je viens de rectifier et tout est OK. Désormais je ferai un peu plus attention. $$$$$$...
Merci pour le rectificatif et si un jour tu as le temps de voir pour lier recherche et total des sommes avec Vbasic je suis intéressé.
@+
Par Cédric, le 27 Aoû 2005, 10:20 Revenir en haut de page Répondre en citant

Salut,

Je viens de trouver plus simple pour le calcul débit/crédit : la fonction Excel SOUS.TOTAL propose plusieurs fonctions et permet d'exclure les cellules cachées.

Arrow Il suffit d'associer le filtre automatique avec la fonction SOUS.TOTAL. Pour le filtre, cf plus haut...

  • Total débit = SOUS.TOTAL(109;D8:D10000)
  • Total crédit = SOUS.TOTAL(109;E8:E10000)

Dès que tu modifieras le filtre (choix d'un libellé par exemple), en utilisant VBA ou pas, ces fonctions afficheront le total.

Pour un exemple d'utilisation du filtre avec VBA, je te posterai un exemple d'ici peu


NB: 109 est un paramètre de la fonction... affiche la somme des cellules non masquées.

Autres valeurs possibles : (rouge = toutes les cellules; bleu = cellules non masquées)

  • Pour la MOYENNE : 1 ou 101
  • Pour le NOMBRE de valeurs NUMERIQUES uniquement : 2 ou 102
  • Pour le NOMBRE de valeurs : 3 ou 103
  • Pour la VALEUR MAXIMUM : 4 ou 104
  • Pour la VALEUR MINIMUM : 5 ou 105
  • Pour le PRODUIT : 6 ou 106
  • Pour l'ECART TYPE (basé sur un échantillon de la population) : 7 ou 107
  • Pour l'ECART TYPE (basé sur la population totale) : 8 ou 108
  • Pour la SOMME : 9 ou 109
  • Pour la VARIANCE (basé sur un échantillon de la population): 10 ou 110
  • Pour la VARIANCE (basé sur la population totale) : 11 ou 111


@+, Cédric
Par Biniozet, le 29 Aoû 2005, 16:14 Revenir en haut de page Répondre en citant

Bonjour et merci
Cette fois j'ai vraiment ce que je recherchais. Le filtre me permet d'obtenir la liste complète des apparitions d'une opération et la fonction SOUS.TOTAL donne le total recherché. Very Happy
Par contre j'ai utilisé la fonction en passant par : DONNEES/SOUS.TOTAL et en configurant la fenêtre qui apparaît car je n'ai pas été capable d'utiliser les fonctions Embarassed
Citation:
Total débit = SOUS.TOTAL(109;D8:D10000)

Total crédit = SOUS.TOTAL(109;E8:E10000)

Comme, ainsi que tu me l'expliques, elles permettent en changeant la valeur de la fonction de réaliser d'autres opérations je voudrais bien m'en servir. Quelle cellule faut-il configurer. J'ai essayé en bas de la liste triée sans autre réponse que "#VALEUR#". D'autres essais avant ou après le tri ne marchent pas. Alors si tu peux me donner la marche à suivre ce serait parfait.

@+
Par Cédric, le 29 Aoû 2005, 21:10 Revenir en haut de page Répondre en citant

Citation:
J'ai essayé en bas de la liste triée sans autre réponse que "#VALEUR#".

Si j'étais toi je n'utiliserais pas une cellule en fin de liste. Tu peux par exemple concatener le résultat avec l'en-tête de ta colonne, ce qui te permet d'avoir le résultat directement quand tu modifies le filtre (si tu utilises une cellule en fin de liste et que le résultat du filtre est sur 50 lignes, tu seras obligé de descendre pour voir le résultat).

Exemple : si l'en-tête de ta colonne Débit est dans la cellule D1, tu peux entrer :

Code:
D1 = CONCATENER("Débit : ";SOUS.TOTAL(109;D2:D10000))


Ce qui indiquera le total directement dans l'en-tête.


Citation:
je n'ai pas été capable d'utiliser les fonctions ... D'autres essais avant ou après le tri ne marchent pas. Alors si tu peux me donner la marche à suivre ce serait parfait.

Difficile à dire comme ça sans avoir ton tableau, mais dans ce cas utilises le menu Insertion / Fonction, et renseignes les paramètres de cette fonction pas à pas, en vérifiant le résultat. Fais aussi un essai sur une plage de données dans une nouvelle feuille pour vérifier que tu t'y prends bien (si ça fonctionne, tu sauras au moins que le problème vient de tes données).

Si tu veux envois moi le dossier Excel je pourrai mieux t'aider.
Par Biniozet, le 11 Sep 2005, 14:33 Revenir en haut de page Répondre en citant

Bonjour Cédric et merci
J'ai été qq temps sans pouvoir me connecter, j'en ai profité pour essayer tes conseils et je suis très content car grâce à eux tout marche très bien. J'obtiens ce que je souhaitais.

@+ Binozet
Par Zhah001, le 28 Oct 2014, 09:50 Revenir en haut de page Répondre en citant

Bonjour,
Je vous remercie de la disponibilité que vous prendrez pour repondre a ma question;
J ai un fichier excel, dans lequel je fais des statistique a l aide de macros commandes.
J ai cree des boutons qui lient ces macro aux boutons;
cependant, je desiereais placer ces macro en lien directement apartir de la barre de mmenus.
J ai donc creer des menus; mais le probleme c est que ces menus, restent toujours la, meme qd je n utilise pas
ces fichiers.

Forums » Tableurs et logiciels de gestion (Money, Excel, Open Office...) » Améliorer la recherche dans Excel · Envoyer le lien