BLOG Loé

Tableur 5 : tri sélectif

Voici enfin la possibilité de trier toutes les données pour en extraire les tendances, qui définiront, aux choix, le menu de ce soir, la restriction du budget clope ou encore ta politique commerciale. Tu veux savoir d’un coup d’oeil si la majorité de tes clients sont de la région ? Où si le prix de tes courses a augmenté sur ces derniers mois ? C’est parti !

Sommaire

Les bases de données

Excel, de part sa présentation en tableau est souvent utilisé pour créer des bases de données. Que ce soit sur ce logiciels ou un autre les bases en sont les même. Excel s’oganise en feuille de calcul, alors qu’on parlera plutôt de feuille de données pour une base de données. Les fonctionnalité internes des cellules sont un peu moins développées dans cette dernière par contre les outils autour sont uniquement dédiés à cela. Ce sera le cas d’Acess dans Microsoft Office, et de Base dans Open ou Libre Office.

Mais Excel est devenu un mode de transition reconnu entre les différents logiciels qui utilisent les bases de données (voir le cour sur les Fichiers Clients). De plus, il a développé des outils (notamment des fonctions) très pratiques qui nous permettent d’égaler voir dépasser grâce à le personnalisation certains logiciels dédiés.

En raison de cette utilisation, cela va nous orienter pour organiser nos données.

Les lignes seront utilisées pour les « enregistrements » qui décrivent un élément, (individu, produit…). Utilisées en graphique ces lignes correspondront à l’axe des abscisses le plus souvent. La première ligne ne sera pas une entrée mais servira de ligne d’intitulés pour placer le nom des champs.

Les colonnes seront utilisés pour les « Champs » qui sont un type d’information collecté pour chaque enregistrement. Les données colonne correspondront à l’axe des ordonnées d’un graphique le plus souvent. Ces Champs sont aussi utilisable pour le publipostage avec Word, et on peu importer ces données grâce à un formulaire que l’on peut créer dans Word ou Excel. La première colonne sera souvent, lorsque nous seront obligé de faire référence à une autre feuille ou fichier, une clé primaire, un champ unique pour chaque enregistrement. Elle peut être un numéro, un champ qui ne sera utilisé nulle part ailleurs, ou une combinaison de champs.

Cette combinaison peut se faire automatiquement avec des formules ou sous Excel 2013 avec la nouvelle fonctionnalité FlashFill qui est beaucoup plus performant pour les cas complexes. Pour les versions précédentes il vous faudra utiliser la fonction CONCATENER.

Les outils bases de données

Votre tableau Excel se remplit : contacts, notes, chiffres, dates… Vous aimeriez y faire un tri. Excel permet d’effectuer des tris automatiques sur un ou plusieurs critères, de filtrer l’affichage… bref, de vous économiser des dizaines de minutes de recherche d’une information égarée.

Les tris

Pour commencer nous allons nous exercer sur l’execice suivant :

Exercice : Plateforme/Exercices/Tableur perfectionnement/06_tri_filtre/Tri simple.xlsx

Pour classer les lignes d’un tableau selon l’ordre d’une colonne, commencez par sélectionne les données du tableau (sans les intitulés de colonnes que doivent rester à leur place). Pour classer à partir de la première ligne cliquez simplement sur l’icône AZ pour l’ordre alphabétique ou croissant, ZA pour l’inverse, dans l’onglet Données.

Si vous voulez trier depuis une autre colonne, vous pouvez séletionner uniquement les données de celles-ci puis cliquer sur le même icône. Un message vous proposera d’étendre la sélection à toutes les données. Cela cependant ne fonctionne pas toujours.

Vous pouvez alors utiliser les fonction de tris avancées. Sélectionnez le tableau avec ses intitulés cette fois et cliquez sur la grande icône trier. Vérifiez que « Mes données ont des en-têtes » est coché. Choisissez alors par quelle colonne trier et quel type de tri faire (nouvellement par couleurs !). Ne fermez pas tout de suite !

Il peut être parfois utile de classer selon plusieurs critères lorsqu’il y a plusieurs champs équivalent dans le premier critère de tri. Utilisez la commande « ajouter un niveau » et configurez de la même façon votre tri. Les données triées resterons triées, sauf si vous annulez ou refaites une opération de tri différente.

Si par je ne sais quelle étourderie votre tableau étais disposé à l’envers, vous pouvez sélectionner dans option votre orientation.

Filtrer l’affichage de ses données

Les filtres permettent de n’afficher que les données qui vous intéresses.

Pour cela sélectionnez la ligne de titre. Dans l’onglet Données, cliquez sur Filtrer. Des flèches ont été ajoutées à chaque champ de titre.En cliquant sur ces flèches, vous accédez aux filtres prédéfinis. Ainsi, vous pouvez trier également de cette façon, sélectionner un intitulé particulier, une couleur ou créer un filtre numérique ou textuel pour les opérations courantes (plus petits que, commence par…)

Vous pouvez filtrer plusieurs colonnes en même temps ou annuler les filtre à votre bon vouloir. Cela répond à toute les les utilisations simples du filtre. Mais si par exemple votre plage était mal reconnue ou que vos critères étaient plus complexes, alors il vous faut passer aux filtre avancés ou élaborés. Pour les tester ouvrons l’exercice suivant.

> Exercice : Plateforme/Exercices/Tableur perfectionnement/06_tri_filtre/filtre_simple_elabores.xlsx

Il faut ajouter à table de données une zone de critères qui doit avoir en première ligne des champs de colonnes (dans le même ordre ou non mais avec la même orthographe) et au moins une ligne qui contiendra les critères. Dans l’exercice elle est sur la feuille filtre élaboré.

Dans l’onglet Données, groupe Trier et filtrer, commande Avancé. Une boîte de dialogue apparaît. Elle comprend :
Filtrer la liste sur place : filtre directement dans plage initiale.
Copier vers un autre emplacement : permet dupliquer les données filtrées vers un autre emplacement défini dans Copier dans.
Plages : la zone à filtrer.
Zone de critères : désigne la plage dans laquelle est insérée les critères de filtrage.
Copier dans : emplacement de la copier filtrée.

Extraction sans doublon : permet de ne pas afficher les données en double.

Sélectionner la plage de données sur la feuille base de données si ce n’est pas déjà fait, et la zone de critères ici A1 à I3 de la feuille filtre élaborés.
Sélectionnons Copier vers un autre emplacement puis la plage de copie, après celle de critère par exemple.Les filtres actuels de la plage de critères sont : >2500 ; femme ; >01/01/1970, soit les femmes étant née après 1970 et ayant un salaire inférieur à 2500 euros. Les critère peuvent donc être des données, des opération, ou des fonctions.

Si on ajoute un critère sur la même ligne il s’additionnera aux autres, restreignant la sélection. Par exemple dans la colonne site, Paris. Si par contre je mets ce critère dans la ligne de dessous, excel traduira comme les femmes étant née après 1970 et ayant un salaire inférieur à 2500 euros OU les personnes vivant à Paris. La liste sera donc plus longue.

La validation des données

La validation des données limite le type de données valides pour des cellules ou des plages. Pour créer les règles de validation, il faut commencer par sélectionner les cellules à limiter.Dans l’onglet Données, cliquer sur Validation des données.
Le menu déroulant nous propose alors des types de données :
Nombre entier, Décimal, Liste, Date, Heure, Longueur du texte, Personnalisé.
Nombre entier, Décimal, Date, Heure et Longueur du texte pourront être soumis à des limitation, min max = > <…
Liste proposera un liste déroulante dont il faudra choisir la source.
Personnalisé propose d’inclure une formule.Les autres onglets de la fenêtre nous donnent la possibilité de rédiger un message de saisi ou une action en cas d’erreur.

> Exercice : Plateforme/Exercices/Tableur perfectionnement/11_Listes déroulantes/liste_deroulante_villes_France.xls

La consolidation

La consolidation consiste à compiler des chiffres issus de différentes feuilles de calcul ou Classeurs présentées de manière identique.
Par exemple : une société disposant de plusieurs succursales fait remplir à chacune d’entre elles un tableau de bord. L’objectif consiste à « additionner » tous ces tableaux en un seul tableau général récapitulatif.

Pour cela : faites une copie vide de votre tableau dans une nouvelle feuille ou Classeur. Dans l’Onglet Données, cliquez sur Consolider. Dans la fenêtre, la liste déroulante propose plusieurs fonctions, dont somme.

En bas de la boîte de dialogue, cochez la case « Lier aux données source ». Ainsi, si vous modifiez un élément d’un de vos fichiers, le récapitulatif sera automatiquement mis à jour. Cliquez sur le champ « Référence » pour sélectionner la première plage, Ajouter, recommencer avec les autres tableaux, OK. C’est terminé.> Exercice : Plateforme/Exercices/Tableur perfectionnement/09_Consolidations/Consolidation_venteCD.xls

Le groupe de travail permet de créer une même architecture de tableau sur différentes feuille. C’est notamment très utile en prévision de l’utilisation de la consolidation des données.

Les fonctions de base de données

Les fonctions des bases de données, permettent d’utiliser une plage de critère comme les filtres complexes mais de façon manuelle.

Les fonctions BDNB BDNBVAL

La fonction BDNB compte le nombre de fois qu’une occurrence existe dans le tableau et qui correspond aux critères indiqués dans la zone de critères. (La fonction BDNBVAL s’applique seulement aux données Alphanumériques). La syntaxe sera :

=BDNB(Plage de données;colonne;Plage de critères)

Les fonctions BDSOMME BDMOYENNE BDMIN et BDMAX

La fonction BDSOMME s’applique seulement aux données numériques. De la même façon la syntaxe sera :

=BDSOMME(Plage de données;colonne;Plage de critères)

BDMOYENNE proposera sur ce même modèle une moyenne, BDMIN indiquera la valeur minimum et BDMAX la valeur maximum. Il y en a également d’autres que vous trouverez ici.

Conclusion

Allez, maintenant qu’on est dans le vif du sujet, on retourne aux fonctions !

Graphisme & com pro