7.5 Le coin des geeks

TABLE DES MATIÈRES

• 7.5.1 Les formules 3D
• 7.5.2 Mieux comprendre les formules matricielles
• 7.5.3 Transpose
• 7.5.4 Les finesses de SI
• 7.5.5 Les finesses de SI, BIS
• 7.5.6 Excel 2021 / Excel 365
• 7.5.7 Import données Web
• 7.5.8 Filter
• 7.5.9 XLookup

---

7.5.1 Les formules 3D

Parfois, vous utiliserez des fichiers dont la structure est le même de feuille à feuille, et chacune d’entre elles correspond par exemple à un moi. Si vous avez besoin d’effectuer des opérations sur chacune de ces feuilles, par exemple en faisant la somme des différents résultats de chaque mois, vous entreriez la formule suivante :

Feuil1 !A12 + Feuil2 !A12 + etc etc…

Le problème de cette approche est que vous devrez mettre manuellement à jour votre formule à chaque fois que vous ajouterez une feuille, ce qui vous empêche d’automatiser des formules.

Les formules 3D peuvent vous permettre de contourner cet écueil, en effet, si vous écrivez par exemple la formule suivante :

=SOMME(Feuil1 :Feuil4 !A12)

La formule fera donc la somme de toutes les cellules A12 des différentes feuilles 1 à 4. Mais de manière beaucoup plus intéressante et créative, imaginons que vous nommiez deux feuilles vide, « début » et « fin ». Si vous glissez vos feuilles dans l’intervalle entre la feuille « début » et la feuille « fin », vous pouvez écrire la formule

=SOMME(début :fin !A12)

Et toutes les cellules A12 des feuilles que vous glisserez dans cette intervalle seront additionnées, ce qui facilite énormément le traitement de données structurées de manière répétitives, sachant que vous pouvez faire référence à des intervalles et pas seulement des cellules, en écrivant par exemple :

=MOYENNE(début :fin !A12 :A15)

7.5.2 Mieux comprendre les formules matricielles

N’oubliez pas qu’étant donné la logique des formules matricielles (qui s’appliquent donc à une intervalle), vous pouvez utilise cela a votre avantage pour trouver un intervalle de valeurs, et pas simplement une seule valeur, comme c’est le cas avec l’utilisation « normale » des formules Excel.

Imaginons par exemple que vous cherchez le mode d’une série statistique (à des fins d’analyse, de façon à voir quelles sont les valeurs les plus courantes de la série par exemple). Il est possible qu’il en existe plusieurs, notamment si vous manipulez des séries avec un petit nombre d’entiers. Prenons l’exemple suivant : vous cherchez à voir quelle est la note la plus courante dans les devoirs des élèves d’un programme d’éducation, dans ce fichier.

Les données sont entrées dans l’intervalle suivant : B2 :B13. Si l’on entre la formule =MODE.MULTIPLE(B2 :B13) et qu’on tape entrée, vous aurez le résultat suivant :

Ce qui équivaut dans l’idée à entrer la formule =MODE.SIMPLE et donc à ne trouvez qu’un seul résultat, alors que la réponse correcte est qu’il existe 2 modes (6 et 8).

De la même façon, si vous sélectionner la cellule, retapez la formule et que vous faites Ctrl = Alt + Entrée, vous aurez l’exact résultat précédent. Par contre, si vous faites en sorte de sélectionner une plage de cellules et ensuite de rentrer la formule en matricielle, vous aurez tous les résultats, soit ici 6 et 8 de la façon suivante :

Vous pouvez appliquer cette logique pour ainsi trouver les 5 plus grandes valeurs d’une liste, les occurrences les plus communes etc etc… et ainsi extraire des intervalles à partir duquel vous pouvez faire référence. N’hésitez pas à vérifier votre formule dans l’exemple résolu.

Par exemple, extraire le nom des 5 employés avec le plus de jours travaillés lors des x derniers mois, et utiliser la plage de nom extraits pour trouver le nombre de congés qu’ils ont de disponible (en faisant référence à une autre feuille par exemple) pour mieux gérer la rotation de vos équipes par exemple.

7.5.3 Transpose

Parfois vous voulez réorganiser un tableau pour pouvoir utiliser les données présentées horizontalement, de manière verticale dans un autre document (et vice versa).

Plutôt que d’effectuer cette opération manuellement, il existe une formule qui permet de faire cela dans excel, c’est la formule TRANSPOSE. Il est par contre important de savoir deux choses au sujet de l’utilisation de cette formule. Tout d’abord, qu’il vous faudra l’utiliser comme une formule matricielle (voir Section 7.1 Les formules matricielles) de manière à ce qu’elle fonctionne.

Par exemple si l’on veut transposer Le tableau A1 :B5 en horizontal a partir de D1, on écrira dans D1, la formule =Transpose(A1 :B5)

Ensuite, il vous faudra non pas simplement entrer votre formule simplement dans sa cellule et taper Ctrl + Alt + Entrée, puisque cela vous donnera le résultat suivant :

Pour faire en sorte de transposer l’intégralité de vos données, vous devez donc sélectionner une plage, qui soit au moins aussi grande (voire plus si vous faites référence à une table que vous allez mettre à jour plus tard) que la plage de référence. Une vois cette plage sélectionnée, vous pouvez Ctrl+Alt+Entrée, et vous aurez la transposition de votre plage sélectionnée, comme suit :

Cela vous permettra donc de lier des tableaux, de manière à extraire une partie des données d’une feuille dans une autre pour y effectuer des calculs plus approfondis par exemple, d’autant plus que les deux matrices restent liées (si vous changez des valeurs dans la plage initiale, la plage de destination transposée changera en conséquence) avec le résultat d’affichage suivant :

7.5.4 Les finesses de SI

Quand vous écrivez une formule Excel avec de multiples SI, il est possible d’utiliser Vrai, pour limiter le nombre d’arguments que vous entrez. Pour donner un exemple concret, imaginons que dans le traitement d’un fichier d’enquête CAP (connaissance, attitude, pratique) et seules 2 types d’infrastructures correspondent à des infrastructures fonctionnelles ou semi-fonctionnelles, et que toutes les autres correspondent à des infrastructures non fonctionnelles. Dans ce cas, au lieu d’écrire toutes les options, Vous pouvez donc écrire par exemple :

=SI.CONDITIONS(A2=”forage”;”fonctionnel”;A2=”PMH”;”semi-fonctionnel”;vrai;”non fonctionnel”)

Cela vous permet de ne pas avoir à écrire toutes les autres options de la liste (qui peuvent être nombreuses) et donc du moment que la condition est vraie, proposer toujours la même réponse. Vous pouvez également utiliser cette astuce avec INDEX/EQUIV, ou avec toutes formules a conditions multiples.

Téléchargez ce fichier si vous voulez vérifier la formule.

7.5.5 Les finesses de SI, BIS

Comme vous l’indique ce lien il est parfois plus intéressant d’utiliser une logique booléenne (employer un Vrai/faux, de manière schématique) que des SI, notamment pour des questions de logiques.

En guise d’exemple, comparez :

=SI(A2=”rouge”,25,SI (A2=”blanc”,101,SI(A2=”bleu”,74,0)))

Et :

=(A2=”rouge”)*25 + (A2=”blanc”)*101 + (A2=”bleu”)*74

Retenez dans tous les cas que la logique booléenne est souvent très puissante dans Excel, notamment parce qu’elle permet d’effectuer des opérations plus rapidement qu’avec des formules, mais également parce qu’elle permet de trouver des logiques de contournement aussi élégantes qu’intéressante (nous vous renvoyons à l’exemple d’INDEX/EQUIV en formule matricielle pour un exemple de logique booléenne appliquée).

7.5.6 Excel 2021 / Excel 365

Comme indiqué dans la section transpose, de nombreuses fonctionnalités ont été ajoutées et viennent ainsi changer la façon dont on utilise Excel.

Au premier plan, c’est le changement au niveau des fonctions qui nécessitaient une entrée via Ctrl+Alt+Entrée telles que transpose, mode.multiple etc…. : vous n’avez désormais plus besoin de sélectionner la zone de destination de votre formule et de l’entrée avec une matrice, Excel fera cette opération pour vous automatiquement.

7.5.7 Import données Web

Si vous utilisez fréquemment les données d’un site web, vous pouvez importer directement les données dans Excel.

Pour ça, comme indiqué dans la capture d’écran ci-dessous, allez dans Données => Otenir des données => A partir d’autres sources => A partir du web.

Vous pourrez ainsi sélectionner les données que vous voulez télécharger en indiquant l’adresse web qui vous intéresse, https://fr.wikipedia.org/wiki/Population_mondiale par exemple.

Et vous pourrez ensuite sélectionnez la table qui vous intéresse à télécharger ici en cliquant sur celle-ci, comme par exemple l’estimation de la répartition de la population en 2022.

Et vos données seront chargées automatiquement sous forme de tableau :

Attention : Si la table n’est pas forcément utilisable dès le début (si par exemple, vous avez plusieurs sous catégories dans le tableau, l’importation sera bugguée), vous devrez utiliser le bouton « Transformer les données » qui vous emmènera vers Power Query.

Par exemple, dans le cas de la page Wikipédia de la population mondiale comme vous pouvez le voir, la table 1 ne sera pas vraiment utilisable avec l’ajout du texte dans la colonne sur le chiffre de la population, vous devrez donc transformer les données.

Vous pouvez vous référez à ces ressources pour l’utilisation basique de PowerQuery si vous voulez transformez vos données.

La vidéo suivante vous offre une introduction (uniquement en anglais) :

En compagnie de celle-ci (uniquement en anglais) :

7.5.8 Filter

Dans les versions post 2019 et Office365 d’Excel, vous pouvez désormais remplacer l’utilisation de vlookup et de Hlookup par Xlookup.

Cette formule dont vous pouvez trouver la syntaxe sur le site officiel de Microsoft vous permet de filtrer une table selon un ou plusieurs critères et d’en faire un extrait directement renvoyé sous forme de tableau.

C’est particulièrement utile pour obtenir des données de manière dynamique (de la même façon mais en plus complexe que transpose) puisque les données qui s’ajoutent dans votre tableau initial seront-elles aussi filtrées par la fonction dès leur ajout.

7.5.9 XLookup

Dans les versions post 2019 et Office365 d’Excel, vous pouvez désormais remplacer l’utilisation de vlookup et de Hlookup par Xlookup.

Cette formule dont vous pouvez trouver la syntaxe sur le site officiel de Microsoft vous permet de rechercher une correspondance dans un intervalle, et vous affranchit des limites de rechercheV et rechercheH (recherche a droite de la formule etc…) ! Elle dispose en plus de paramètres de recherche supplémentaire, et vous permet de renvoyer non pas une valeur, mais une/des références, ce qui vous permet de créer des formules plus dynamiques.

Si vous voulez en apprendre plus sur cette formule, n’hésitez pas à regarder cette vidéo (uniquement en anglais) :

Et à consulter ce lien.

Elle est particulièrement puissante et utile (puisqu’elle vous permet de répondre aux utilisations de rechercheV/H et index/equiv, si vous l’avez donc dans votre version d’Excel, n’hésitez pas à l’utiliser !