Base de données (a)

J’ai déjà traité du sujet des bases de données dans Excel, mais je penser qu’il est utile d’y revenir car je constate, chaque fois que je rencontre des utilisateurs d’Excel dans les entreprises, qu’il y a beaucoup de choses utiles à ce sujet qu’ils ne connaissent pas.  En outre, mais nous en reparlerons par la suite, l’apparition des tableaux croisés dynamiques, même si elle a ajouté de nouvelles fonctionnalités, a en revanche provoqué une certaine paresse et – par ricochet – une baisse du niveau des utilisateurs quand à l’utilisation des « bases de données » Excel.

Commençons déjà par le terme « base de données » qui est un terme abusif dans Excel. Une véritable base de données met en relation plusieurs tables. Par exemple, pour établir une facture, elle utilisera à la fois la table des articles vendus par l’entreprise, la table des clients, éventuellement aussi la table des TVA et celle des coûts de transport.

Quand le premier Excel est sorti, des clients américains ont menacé Microsoft d’un procès pour publicité abusive. Du coup, Microsoft a renommé cela en « liste », mais – pour maintenir la compatibilité ascendante – ils n’ont pas pu renommer les fonctions BDxxx().

Reprenons la table qui me sert souvent d’exemple à ce sujet, qui est une table de personnes ayant participé il y a des années à une école d’été sur la modélisation que j’organisais à HEC. Seule la colonne « Salaire » est fictive : les participants étants tous des étudiants, ils n’avaient pas de salaire. J’ai donc utilisé la fonction alea() pour créer des salaires fictifs.

Règle n°1 avec les tables

La première règle quand on travaille avec une table est de penser à cliquer dedans avant de faire quoi que ce soit. Quand vous le faites , Excel fait sans vous le dire un [Ctrl]-* pour sélectionner tout ce qui touche la cellule active.

Faisons l’expérience. Cliquez en H5 puis utilisez la commande Filtrer de l’onglet Données. Vous obtenez un message d’erreur. Cliquez à présent en C5 et recommencez. Vous obtenez à présent l’écran ci-dessous.

Remarque – Notez que les filtres de la ligne 1 sont placés en bas et le texte en haut des cellules. Cela ne vient pas naturellement : j’ai fait exprès d’agrandir la hauteur de la ligne 1 et de placer les textes en haut pour éviter que les flèches recouvrent les titres des colonnes ou me forcent à élargir les colonnes.

Vous devez presque tous savoir que les filtres sont cumulatifs. Si je déroule le filtre en A1 pour ne prendre que les « M », je n’aurai que les hommes. Si maintenant je déroule aussi le filtre en D1 pour ne garder que les « 27 », je n’aurai plus que les hommes de 27 ans.

Savez-vous comment je dois faire pour sélectionner à la fois les hommes de 27 ans et les femmes de 26 ans, sans passer par un tableau croisé dynamique ?

En effet, si j’ajoute à mon filtre actuel « F » et « 26 », j’aurai à la fois les hommes et les femmes et 26 et 27 ans, ce qui n’est pas mon objectif…

Ce qui est intéressant dans ma question, à laquelle je répondrai dans le prochain article, c’est que, avant que les tableaux croisés dynamiques n’apparaissent, la plupart des utilisateurs d’Excel savaient comment faire. Aujourd’hui, ce n’est plus du tout le cas hélas…

Validation des données (d)

Si vous consultez le commentaire de mon dernier article, vous verrez que cduigou nous a fait la suggestion dans un commentaire d’éliminer tous nos decaler() en utilisant à la place une table pour chaque colonne de données.

L’avantage de ces tables est qu’elles se redimensionnent automatiquement dès qu’on leur ajoute un élément. Il suffit alors de donner à chaque table, dans les colonnes E à J le nom de la première cellule de la colonne.

Du coup, la validation de cellule de B2 est tout simplement la formule =indirect($B$1).

En plus, la macro suivante, définie pour l’onglet, sélectionne par défaut le premier pays du continent quand on change de continent :

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$1" Then Range("B2").Value = ""

End Sub

Si vous préférez mettre par défaut le premier pays, remplacez ""  par Range(Range("B1").Value).Cells(1).Value

Les tables … ou la fonction decaler()

Certes, cette solution a l’avantage de nous permettre d’éliminer la nécessité pour la fonction decaler().

En revanche, il faut payer un prix pour cela.

Le premier problème est qu’il ne suffit plus d’effacer par exemple Angleterre si l’on veut éliminer ce pays de la liste Europe : il faut alors supprimer la cellule correspondante pour qu’elle disparaisse de la table. Sinon, il y aura dans la table une cellule vide…

Le second problème est plus général. Pour moi, une des clefs de la puissance et de la flexibilité extraordinaires d’Excel, est le fait que les lignes, les colonnes et les cellules sont indépendantes les unes des autres. Ces qualités disparaissent quand vous utilisez la pire fonctionnalité jamais inventée pour Excel, la fusion de cellules.

La force d’Excel réside dans l’indépendance de toutes ses cellules !

Dès qu’il y a deux tables au moins couvrant des lignes en commun ou des colonnes en commun, vous perdez cette indépendance fondamentale.

Pour en faire l’expérience, c’est tout simple. Créez donc la première table, en colonne E. Sélectionnez alors les lignes 3 à 6, faites un clic droit, et vous constatez que les commandes Insérer et Supprimer sont actives.

Créez maintenant la seconde table, en colonne F. Sélectionnez alors les lignes 3 à 6, faites un clic droit, et vous constatez que les commandes Insérer et Supprimer sont désormais inactives. Vous venez donc de perdre la merveilleuse indépendance que vous aviez auparavant dans Excel !

Validation des données (c)

Définition du nom Pays

J’avais défini, dans le dernier article, le nom Pays par :

= decaler($E$1;0; $C$1): decaler($E$1;nbval(decaler($E:$E;0; $C$1))-1; $C$1)

Un lecteur du blog, Phillippe M, a suggéré que nous remplacions la définition du nom Pays par :
=decaler($E$1;0;$C$1;nbval(decaler($E:$E;0;$C$1));1)

C’est une excellente idée ! Il est vrai que j’avais tendance à oublier le troisième et le quatrième argument que je n’ai pas l’habitude d’enseigner à mes élèves du MBA quand je leur parle de la fonction Decaler : en effet, ces deux derniers arguments peuvent créer de la confusion dans leur esprit pour une fonctionnalité dont l’usage n’est quand même pas très fréquent.

Conflit entre Continent et Pays

Il reste un petit problème… Dans la copie d’écran de l’article précédent, nous avions sélectionné le continent Europe, puis le pays France. Qu’arrive-t-il si nous sélectionnons maintenant un autre continent ? Nous aurons alors par exemple Amérique en B1 et France en B2, ce qui est choquant visuellement et, de plus nous donnera un résultat incohérent.

La façon la plus simple de résoudre ce problème est de donner un format conditionnel à la cellule B2 qui affiche clairement que le pays en B2 ne correspond pas au continent en B1. C’est ce que nous avons fait dans la copie d’écran, en donnant un fond rouge au pays quand il y a un conflit entre B1 et B2.

Remarque – En fait, quand j’utilise cette technique chez un client, je donne un fond noir à la cellule pour occulter complètement le nom du pays et faire voir au client qu’il y a un problème. Il sait qu’il doit alors soit effacer B2, soit sélectionner un pays appartenant bien au continent qu’il vient de choisir.

Validation des données (b)

Maintenant que nous avons un menu déroulant dynamique pour choisir le continent, nous allons nous concentrer sur la sélection d’un pays dans le continent sélectionné.

Il nous faut tout d’abord voir quel est le numéro du pays sélectionné. A cet effet, nous entrons en C1 la formule =equiv(B1;E:E;0).

La plus compliqué à présent est de définir un nom Pays qui pointe – de façon dynamique, une fois encore – sur l’ensemble des pays de ce continent. La formule est cette fois plus compliquée :

=decaler($E$1;0;$C$1):decaler($E$1;nbval(decaler($E:$E;0;$C$1))-1;$C$1)

Vous pouvez probablement imaginer dès à présent comment nous pourrons créer un troisième niveau hiérarchique dans lequel nous pourrions sélectionner une région du pays sélectionné…