All posts tagged Excel

Les filtres automatiques Excel

Les filtres automatiques sont la colonne vertébrale de l’utilisation des feuilles Excel de données. Ils permettent d’interroger efficacement une grande quantité d’informations présentées sur une feuille de données.

Exemple, il est possible en quelques clics d’isoler les clients de notre exemple Figure 29 : résultat tri respectant les critères suivants :

  • homme,
  • né avant le 01/01/1960,
  • habitant à Paris.

 

POSITIONNER LES FILTRES AUTOMATIQUES SUR UNE FEUILLE DE DONNEES

Une feuille de données doit impérativement contenir les noms des colonnes en première ligne (si possible avec une mise en forme distincte des lignes de données, pour améliorer l’ergonomie visuelle du document). Pour positionner les filtres automatiques, il suffit de sélectionner les cellules de la première ligne sur lesquelles on veut pouvoir faire une sélection (ou la première ligne entière, les filtres seront alors positionnés sur toutes les colonnes renseignées).

Le filtre automatique est accessible de deux manières : par le menu « Accueil » du ruban et par le menu « Données ».

 

 

Lorsqu’un filtre automatique est activé, il est reconnaissable de deux manières dans l’affichage des données de la feuille :

  • le triangle de filtre devient un petit triangle couplé à un entonnoir ,
  • seules les lignes correspondantes au critère du filtre sont affichées. Leur numéro de ligne est bleu.

 

 

CHOIX POSSIBLES DE LA FENETRE DE FILTRE AUTOMATIQUE

  • Trier de A à Z

    Cette action permet de trier la colonne, dans le sens croissant.

  • Trier de Z à A

    Cette action permet de trier la colonne, dans le sens décroissant.

  • Trier par couleur

    Cette action permet de trier la colonne en affichant en premier la couleur sélectionnée.

  • Filtrer par couleur

    Cette action permet de filtrer les colonnes par couleurs. Seules les colonnes de la couleur sélectionnées sont affichées, comme dans l’exemple ci-dessous.

  • Filtrer par liste de données

    Par défaut, la case à cocher « (Sélectionner tout) » est cochée, ainsi que toutes les valeurs disponibles trouvées dans la colonne (dans la limite de 10.000).

    Si on veut afficher toutes les valeurs sauf une ou plusieurs valeurs, on décoche la ou les valeurs à enlever.

    Si on ne veut afficher qu’une ou quelques valeurs, on décoche tout d’abord la case à cocher « (Sélectionner tout) » pour décocher l’ensemble des valeurs, puis on sélectionne la ou les valeurs à afficher.

 

UTILISER LES FILTRES TEXTUELS

Les filtres textuels sont les plus puissants. Ils permettent d’établir des critères « approchés » et ainsi d’interroger finement une grande quantité de données.

Dans le cas des filtres textuels, l’interface d’Excel propose la fenêtre suivante (avec la première recherche positionnée selon le choix utilisateur). Il est possible de positionner deux critères seulement.

Un clic sur la liste déroulante affiche toutes les valeurs possibles de la colonne. Il est également possible d’entrer manuellement des valeurs, notamment pour les règles de type « commence par ».

Les règles de sélection possibles sont les suivantes :

  • est égal à,
  • est différent de,
  • est supérieur à,
  • est supérieur ou égal à,
  • est inférieur à,
  • est inférieur ou égal à,
  • commence par,
  • ne commence pas par,
  • se termine par,
  • ne se termine pas par,
  • contient,
  • ne contient pas.

 

Dans notre feuille de données exemple, un moyen sûr d’afficher tous nos clients Parisiens serait de positionner le filtre suivant :

 

COMBINER LES FILTRES SUR PLUSIEURS COLONNES

La combinaison de filtres nous permet d’isoler les lignes correspondantes aux critères suivants :

  • homme,
  • né avant le 01/01/1960,
  • habitant à Paris.

 

Il suffit pour cela de positionner les filtres adéquats sur chaque colonne concernée.

La combinaison de filtres sur plusieurs colonnes est une méthode puissante d’interrogation de feuilles de données Excel. C’est quelque peu l’équivalent du langage SQL en base de données.

SUPPRIMER TOUS LES CRITERES DE FILTRE AUTOMATIQUE

Sur certaines feuilles Excel, une multitude de critères peuvent être appliqués sur autant de colonnes. Il peut alors être fastidieux de supprimer tous les critères un par un. Pour supprimer tous les critères choisis en une seule action, il faut cliquer sur le bouton « Effacer » du ruban, menu « Données », groupe « Trier et filtrer » :

LES LIMITES DES FILTRES AUTOMATIQUES

Attention, les filtres automatiques fonctionnent correctement avec des feuilles de données. Mais ils posent problèmes avec des feuilles contenant des cellules fusionnées.

Si on filtre sur agence1 nous obtenons le résultat erroné suivant :

Enfin, les données doivent toutes être renseignées (dans le cas où l’on ne fusionne pas de cellule), il ne doit pas subsister de blancs signifiant « répétition de la valeur », comme sur l’exemple ci-dessous. En effet, un filtre positionné sur ces valeurs ne sélectionne pas la valeur à blanc !

Top

Utiliser la recherche dans Excel

La recherche de texte est une action qui peut s’effectuer sur toute feuille Excel. La recherche est accessible par deux méthodes :

  • le raccourci standard de Windows « CTRL + F »,
  • le bouton « Rechercher et sélectionner » du menu « Accueil » du ruban, groupe « Edition ».

LA RECHERCHE SIMPLE

Sur les données présentées ci-dessous, nous pouvons effectuer une recherche de la chaine de caractères « PARIS ». Chaque clic sur le bouton suivant sélectionne la cellule « suivante » contenant la chaine de caractères cherchée :

Paramètres par défaut de la recherche :

  • la recherche est effectuée ligne à ligne. C’est-à-dire que une occurrence de la chaine de caractère « PARIS » est recherchée à la ligne 1, puis la ligne 2 etc. Par exemple, la chaine « PARIS » en A8 serait trouvée avant la chaine « PARIS » située en B1,
  • la recherche n’est pas sensible à la casse (minuscule / majuscule). Si on cherche « PARIS », on trouvera également « Paris » ou « paris ».
  • La recherche ne porte pas sur le contenu entier d’une cellule. Par exemple, si une cellule C41 contient « découvrez nos paris sportifs en ligne » et que l’on cherche « PARIS », la cellule C4 validera la recherche.

Ces paramètres par défaut peuvent être modifiés, en cliquant sur le bouton « Options », c’est la recherche avancée.

Bouton « Rechercher tout » : la recherche présente tous les résultats dans une liste. Quand on clique sur une occurrence de la liste, la cellule est sélectionnée.

RECHERCHE AVANCEE

  • Dans : « Feuille / Classeur »

Si « Feuille » est sélectionné, la recherche ne porte que sur la feuille en cours. Si on sélectionne Classeur, la recherche porte sur toutes les feuilles sur classeur, ce qui est très utile lorsque nos données sont « éclatées » sur plusieurs feuilles.

  • Sens : « Par ligne / Par colonne »

Modifie le sens de la recherche

  • Regarder dans : « Formules / Valeurs / Commentaires »

« Formules » : la valeur est cherchée dans les formules pour les cellules contenant des formules, dans les valeurs affichées pour les cellules sans formules.

« Valeurs » : la valeur est cherchée dans les valeurs affichées pour les cellules avec ou sans formules.

« Commentaires » : la valeur est cherchée dans les commentaires saisis dans la feuille (menu « Révision », « Nouveau Commentaire ».

  • Respecter la casse

La valeur cherchée doit respecter exactement la casse. Exemple, si on cherche « PARIS », une cellule contenant « paris » ne sera pas sélectionnée.

  • Totalité du contenu de la cellule

Le texte de la cellule doit correspondre exactement à la valeur cherchée. Exemple, si on cherche « PARIS », « PARISOT », « PARIS SPORTIFS » ne seront pas sélectionnés. Seules les cellules contenant uniquement « PARIS » ou « paris » pourront être sélectionnées.

  • Format

La recherche peut être ciblée sur un format (couleur, police, etc.). Le format peut être choisi à partir de la cellule (auquel cas on sélectionne une cellule pour choisir un format à rechercher), ou bien en définissant le format dans la boite de dialogue des formats.

Top

Ecrire un tri en VBA Excel

En VBA, la méthode « Sort » s’applique à des objets « Range » (à des plages de cellules). Nous avons fréquemment besoin d’écrire des sort sur des plages en VBA Excel. Comme souvent dans ce cas, nous n’avons qu’à faire appel à l’enregistreur de macros, faire un tri et adapter le code. Malheureusement, dans ce cas, le code est disons… Trop riche… Voici en effet le code généré pour effectuer un tri sur une plage A3:F75 :

Range("A3:F3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Add Key:=Range( _
"A4:A75"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Echeancier").Sort
.SetRange Range("A3:F75")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Franchement, ça ne donne pas envie d’écrire un tri… Voici donc la marche à suivre :

 1/ Déclarer un Objet Range,

2/ Affecter l’objet Range, dont la première ligne contient les titres,

3/ Affecter les clés de tri,

4/ Appliquer la méthode Sort à l’objet Range sur les clés désirées.

 Voici un exemple :

Dim MonEcheancier As Excel.Range
Set MonEcheancier = Range(MaFeuilleEcheancier.Cells(1, 1), MaFeuilleEcheancier.Cells(1, 1).End(xlDown).Offset(0, 5))
MonEcheancier.Sort Key1:=MaFeuilleEcheancier.Cells(1, 1), Order1:=xlAscending, Header:=xlYes

Si nous avions plusieurs clés, nous n’aurions qu’à écrire MaRange.Sort Key1:=xxx Key2:=yyy etc. Nous avons également positionné l’option Header à oui, car nous avons une en-tête qui ne sera pas triée.

Pour certains, l’affection du Range pourrait sembler confuse… Une Range simple (rectangulaire) est délimitée par 2 cellules, en haut à gauche et en bas à droite. La syntaxe de Range est donc Range (Cell1, Cell2).

Une Cellule est identifiée par son numéro de ligne et son numéro de colonne. On écrit Cells(1,5) pour la 5ème colonne de la 1ère ligne. On peut préfixer Cells par la feuille qu’on adresse, ici MaFeuilleEcheancier, qu’on aura au préalable déclarée avec :

 Dim MonProjet As Excel.Workbook
Dim MaFeuilleEcheancier As Excel.Worksheet
Set MonProjet = Excel.ActiveWorkbook
Set MaFeuilleEcheancier = MonProjet.Worksheets("Echeancier")

Enfin, .End(XlDown) permet d’adresser directement la dernière cellule non vide vers le bas (XlToRight pour aller vers la droite) et .Offset permet de se décaler de 0 lignes et 5 colonnes dans l’exemple.

Top

Améliorer les performances de la fonction RECHERCHEV Excel

Ou comment améliorer les performances de la fonction RECHERCHEV… De façon spectaculaire !

Sur quelques articles de ce site, nous avons évoqué les performances poussives de la fonction RECHERCHEV, lorsqu’elle est utilisée en mode recherche exacte (dernier paramètre à VRAI). Dommage pour la fonction la plus utile du

Pourtant, la solution pour accélérer radicalement cette fonction est vraiment simple. Tellement simple qu’on se demande bien comment diable cette correction n’est pas implémentée directement dans Excel

Il suffit juste de travailler avec cette même fonction RECHERCHEV en recherche approximative, et tester si la valeur retournée est égale à la valeur cherchée. Si nous effectuons la recherche suivante :

=RECHERCHEV(B2;clients!$A$1:$C$10001;3;FAUX)

Nous cherchons la 3ème colonne de la correspondance exacte de B2. Nous pouvons écrire très simplement :

=SI(RECHERCHEV(B2;clients!$A$1:$C$10001;1;VRAI)<>B2;NA();RECHERCHEV(B2;clients!$A$1:$C$10001;3;VRAI))

Attention, les données cibles doivent être triées !

Ce qui revient à faire une recherche approximative (qui est 50 fois plus rapide…), tester la valeur retournée, et si elle est différence, forcer le résultat à #N/A. Si elle est égale, nous renvoyons la 3ème colonne.

Simplissime, et pour les tableaux « monstrueux » à plusieurs centaines de milliers de lignes, le résultat sera sans appel. Dans un cas avec 500.000 lignes, on est passé de 14s à 1s.

La fonction RECHERCHE est lente ? Plus maintenant !

Top

Utiliser des fonctions du Framework .NET en VBA

On peut faire beaucoup, beaucoup de choses en Excel/VBA… Au fil des articles, nous avons souvent étudié la possibilité de faire interagir Excel avec d’autres applications (Power MAC, Word, PowerPoint, Business Objects…) ou avec diverses fonctionnalités Windows (gestion de fichier FSO…). Pour cela, il faut faire appel à des bibliothèques de fonctionnalités, accessibles via le menu de l’éditeur VBA « outils – références ».

S’il existe une bibliothèque de fonctionnalités (si on peut l’appeler ainsi…) bien documentée, il s’agit du Framework .NET fourni par Microsoft. Mais à priori, pour l’utiliser, nous devons avoir affaire aux langages de « haut niveaux » que sont VB.NET et C# (entre autres), ainsi qu’à l’atelier de développement Visual Studio. Pourtant, en étant (vraiment…) motivé, on peut utiliser des fonctionnalités du Framework en VBA office. Pour l’exemple, nous allons utiliser une classe du Framework 2.0 :

la classe STRINGBUILDER (SYSTEM.TEXT.STRINGBUILDER) , qui est censée être plus rapide pour traiter les chaines de caractère. L’exemple n’est pas le meilleur du monde, car on n’est pas sensé exécuter un code rapide en VBA…

Pour utiliser une classe du Framework Dotnet, il est nécessaire (comme toujours quand on utilise des bibliothèques autres que VisualBasic, Office et Excel) d’en importer la référence via le menu de l’éditeur VBA d’Excel : « Outils – Références ». Mais on ne trouvera aucune référence au Framework, .NET ou dotnet ! Il faut l’importer manuellement. Comment savoir où se trouve cette bibliothèque, dans quel fichier dll ?

Pour cela, ouvrons le programme Visual Studio (une version gratuite Visual Basic Express Edition fait parfaitement l’affaire), nous affichons l’explorateur d’objet (F2), et nous cherchons StringBuilder :

Nous affichons l’espace de nom Sytem.Text pour connaitre la dll, il s’agit de la mscorlib :

Le chemin est le suivant (pour le Framework 2.0, si on a choisi ce Framework dans les propriétés du projet Visual Studio) :

Nous pouvons désormais importer non pas la dll dans VBA, mais le fichier avec l’extension tlb

Le code suivant fonctionne parfaitement sous Visual Studio en .NET :

Pourtant, en VBA, cela ne fonctionne pas !

Cela est dû au fait que les fonctions et les procédures (les méthodes donc…) .NET peuvent avoir plusieurs signatures, et visiblement, le compilateur VBA ne s’y retrouve pas… On observe dans l’explorateur d’objets de Visual Studio (voir copie d’écran un peu plus haut) une multitude de signatures différentes pour la méthode Append de l’objet StringBuilder. Pas moins de 19 !

Pour « invoquer » une signature, il suffit (ce n’était pas trivial…) de suffixer la méthode Append par son numéro. Pour adresser la bonne signature, celle qui attend du type « String » en paramètre, il faut suffixer par un numéro mais lequel ? Et bien, cela se corse… Il faut essayer toutes les possibilités ! Sur certains forums, il est dit qu’il suffit de donner le numéro inverse de l’ordre d’apparition dans l’explorateur d’objet, mais je n’ai rien constaté de tel. Non, il faut tester toutes les combinaisons une à une, jusqu’à la suivante :

C’est donc laborieux, mais nous avons réussi ! Cette manipulation n’est bien sûr à réserver qu’à des cas bien précis, et affirmons-le tout de suite, ce sera très rare ! De plus, l’explorateur d’objet n’est pas opérationnel en VBA, l’IntelliSense n’est plus de la partie… Encore plus mystérieux, la méthode Replace fonctionne sans invoquer la bonne signature, pas de numéro à suffixer ! Comme évoqué en début d’article, cela fonctionne, mais il faut rester motivé…

Sub TestDotnet()

Dim MonStringBuilder As New StringBuilder

Set MonStringBuilder = MonStringBuilder.Append_3(« test1 numéro 1 »)

Set MonStringBuilder = MonStringBuilder.Replace(« numéro », « chiffre »)

MsgBox (MonStringBuilder.ToString())

End Sub

Top

Générer automatiquement un document PowerPoint

Voici un exemple de code pour générer automatiquement un document PowerPoint. Bien entendu, nous pourrons générer des documents PowerPoint à partir de bases de données Excel.

Sub GenerationPowerPoint()
'programme de génération d'un document Powerpoint à partir d'un modèle
 
'instanciantion PowerPoint (ajouter la référence Microsoft PowerPoint 1x.xx Library
'dans éditeur VBA menu Outils - Référence
Dim MonPowerPoint As New PowerPoint.Application
MonPowerPoint.Visible = True
 
'ouverture de la présentation modèle (1ere diapositive titre, 2ème diapositive modèle des autres diapos)
Dim MaPresentation As PowerPoint.Presentation
Set MaPresentation = MonPowerPoint.Presentations.Open2007("D:\MonModele.pptx")
 
'on recopie le modele (MonModele) pour créer des diapositives supplémentaires
'modèle composé d'un titre et d'un cadre de texte
Dim MaDiapositive As PowerPoint.Slide
Dim MonModele As PowerPoint.CustomLayout
Set MonModele = MaPresentation.Slides(2).CustomLayout
Set MaDiapositive = MaPresentation.Slides.AddSlide(3, MonModele)
 
'pour activer une diapositive avec son numéro de page
Set MaDiapositive = MaPresentation.Slides(2)
 
'saisie du titre de la diapositive
MaDiapositive.Shapes.Title.TextFrame.TextRange.Text = "Titre diapo"
 
'saisie de 2 lignes dans le cadre principal de la diapositive
'attention, ici on choisit le 3ème cadre à titre d'exemple, à adapter
With MaDiapositive.Shapes(3).TextFrame.TextRange
.InsertAfter ("ligne 1")
.InsertAfter (Chr(10))
.InsertAfter ("ligne 2")
End With
 
'ajout et saisie d'un cadre avec texte
Set MonText = MaDiapositive.Shapes.AddShape(msoShapeRectangle, 300, 300, 250, 140)
With MonText.TextFrame.TextRange
.Text = "exemple de texte en arial"
.Font.Name = "arial"
End With
 
End Sub
Top
1 2 3 5 Page 1 of 5

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes