Extraire dynamiquement lignes répondant à un critère Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Répondre
t
theophile
Nouveau venu
Nouveau venu
Messages : 2
Inscrit le : 8 janvier 2019
Version d'Excel : Mac 2011 FR

Message par theophile » 8 janvier 2019, 18:35

Bonsoir tout le monde,

Je tiens une comptabilité toute simple pour une petite association. J'ai l'habitude d'utiliser Google sheet (et beaucoup moins excel). Mais comme on essaie de se passer de Google, on a maintenant un Nextcloud au lieu de Google drive, et donc il faut que j'utilise excel pour cette compta.

Dans le fichier, il y a une feuille principale, avec une ligne par ticket/facture, qui comprend différentes informations. Il y a "trois catégories principales" pour notre comptabilité, et pour avoir une bonne vue d'ensemble, j'avais sur le google sheet une feuille secondaire pour chacune de ces catégories, qui extrayaient dynamiquement les lignes les concernant dans la feuille principale.
En d'autres termes, le problème qui m'intéresse pourrait être formulé de la manière suivante: "extraire dynamiquement les lignes d'une plage qui correspondent à un critère"

Sur google sheet, je faisais cela de cette manière:
=FILTER({BAS!A10:I510},RegExMatch(BAS!F10:F510,B3))

où BAS est la feuille principale
A10:I510 la plage dans laquelle il faut chercher les bonnes lignes
F la colonne de la plage principale, où est trouvée l'information pour choisir les lignes à prendre, et B3 la cellule qui contient l'expression régulière à comparer

Cela me faisait une seule formule finalement pas si compliquée pour le problème.

Pour obtenir la même chose sur excel, ça n'a pas été du tout simple pour moi. Finalement, après plusieurs recherches (sur Google toujours :oops: ) je suis arrivé à ceci:
=SIERREUR(INDEX(BAS!A$10:A$510;PETITE.VALEUR(SI(STXT(BAS!$F$10:$F$510;1;1)=$B$3;LIGNE(BAS!A$10:A$510)-MIN(LIGNE(BAS!A$10:A$510))+1);LIGNES($A$7:A7)));"")
Formule que j'entre en pressant shift + ctrl + enter, et que je dois ensuite décaler sur chaque cellule de la feuille secondaire.
Je vous ai joint l'excel en question (il n'y a aucune information "secrète" dedans :lol: ).

Ça fonctionne, donc ça me convient, et peut-être qu'il n'y a pas mieux, mais je trouve cette formule relativement compliquée et peu flexible.
Je me demandais donc s'il existait une meilleure solution sur excel, peut-être plus proche de celle de Google Sheet, pour résoudre le problème décrit.

Je ne cherche donc pas de "solution toute faite", mais plutôt, j'aimerais qu'on m'oriente un peu, qu'on me dise si j'ai manqué un type de formules sur excel qui serait plus approprié, ou de quel côté je devrais regarder...

Je vous remercie d'avance !

PS: j'utilise excel pour Mac 2011

EDIT: Je ne connaissais pas VBA, j'imagine que c'est de ce côté que je devrais chercher ?
Comptabilité 2018-2019.xlsx
(474.47 Kio) Téléchargé 18 fois
Avatar du membre
Yvouille
Passionné d'Excel
Passionné d'Excel
Messages : 9'125
Appréciations reçues : 90
Inscrit le : 6 avril 2007
Version d'Excel : 2016

Message par Yvouille » 14 janvier 2019, 10:06

Salut,

Tes explications assez compliquées et ton fichier qui va dans tous les sens en ont - semble-t-il - découragé plus d'un :|

Reprends un seul problème à la fois et explique simplement ce que tu souhaites comme résultat sans te perdre dans l'explication de ce que tu n'as pas réussi. Mais utilise absolument les références aux objets Excel tels que noms des feuilles, références aux plages et cellules, etc.

Un exemple d'explication claire : Je souhaiterais additionner - dans la cellule C8 de la feuille ''Bas'' - la cellulede la plage A1:A5 et les cellules de la colonne B de la feuille ''Haut''.

Une fois le premier problème résolu, on passera au suivant.

Cordialement.
Yvouille

Valais de Coeur
Avatar du membre
Pedro22
Passionné d'Excel
Passionné d'Excel
Messages : 3'934
Appréciations reçues : 401
Inscrit le : 26 janvier 2017
Version d'Excel : 2010 FR
Version de Sheets : FR
Téléchargements : Mes applications

Message par Pedro22 » 14 janvier 2019, 10:23

Bonjour, Salut Yvouille !

Ces petites manip' me semblent complexes pour un souhait simple : filtrer des lignes selon plusieurs conditions. Pourquoi ne pas utiliser directement les fonctions de filtres dans les en-têtes de tes tableaux ? Tu peux filtrer des lignes selon qu'une colonne contient un mot ou un caractère, qu'une autre respecte un intervalle de dates ou de valeurs défini...
Merci de prendre 30 sec pour lire la charte du forum.
Quelques conseils : ici
Avatar du membre
Ausecour
Passionné d'Excel
Passionné d'Excel
Messages : 3'118
Appréciations reçues : 346
Inscrit le : 31 mai 2018
Version d'Excel : 2010 FR, 2013 FR

Message par Ausecour » 14 janvier 2019, 10:40

Bonjour,

La seule simplification de formule que je peux te proposer c'est :
=SIERREUR(INDEX(BAS!A$10:A$510;PETITE.VALEUR(SI(STXT(BAS!$F$10:$F$510;1;1)=$B$3;LIGNE(BAS!A$10:A$510)-LIGNE(BAS!A$10)+1);LIGNES($A$7:A7)));"")

parce que MIN(LIGNE(BAS!A$10:A$510)) revient à faire LIGNE(BAS!A$10) pour moi, sinon le reste est obligatoire par formule.

Seulement, tu n'as pas besoin d'utiliser de formule, puisque ce que tu veux faire au final c'est un filtre avancé, du coup, il ne reste plus qu'à en faire un.

Ces quelques lignes de code font le même travail:
Sub Filtre()
der_lig = Range("a" & Rows.Count).End(xlUp).Row
Rows("7:" & der_lig).EntireRow.Clear

Sheets("BAS").Range("Table2[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("A3:A4"), CopyToRange:=Range("A7"), Unique:=False
der_lig = Range("a" & Rows.Count).End(xlUp).Row
Range("H6").FormulaR1C1 = "=SUM(R8C8:R" & der_lig & "C8)"
Range("I6").FormulaR1C1 = "=SUM(R8C9:R" & der_lig & "C9)"
End Sub
J'ai mis le déclenchement du code sur un bouton, on peut le lancer quand on change quelque chose dans la case contenant le critère.

Je te joins le fichier:
Copie de Comptabilité 2018-2019.xlsm
(353.08 Kio) Téléchargé 8 fois
Plus un sujet a un titre précis, des explications claires, et un fichier Excel bien préparé, plus il a de chances d'avoir une réponse qui répond au besoin, mettez toutes les chances de votre côté :bien:
"100% des gagnants auront tenté leur chance" :trfl:
t
theophile
Nouveau venu
Nouveau venu
Messages : 2
Inscrit le : 8 janvier 2019
Version d'Excel : Mac 2011 FR

Message par theophile » 22 janvier 2019, 18:02

Hello, je m'excuse pour la réponse tardive. J'avais bien vu vos messages, j'ai laissé passer une période d'examens... ^^
Désolé aussi pour le fichier qui va dans tous les sens.

Merci beaucoup à vous trois ! Ausecours, la solution que tu proposes correspond bien à la version "Filtre" de ce que je veux. En ajoutant encore:

Private Sub Worksheet_Activate()
Call Filtre
End Sub

dans les feuilles appropriées, plus besoin du bouton et j'obtiens bien ce que je veux.

Bonne soirée !
Avatar du membre
Ausecour
Passionné d'Excel
Passionné d'Excel
Messages : 3'118
Appréciations reçues : 346
Inscrit le : 31 mai 2018
Version d'Excel : 2010 FR, 2013 FR

Message par Ausecour » 23 janvier 2019, 08:13

Bonjour,

pas de problèmes pour la réponse tardive, au moins tu réponds :wink:

Il ne reste plus qu'à faire une chose! :btres:
Plus un sujet a un titre précis, des explications claires, et un fichier Excel bien préparé, plus il a de chances d'avoir une réponse qui répond au besoin, mettez toutes les chances de votre côté :bien:
"100% des gagnants auront tenté leur chance" :trfl:
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message