Préparez-vous à mettre vos compétences Excel à l’épreuve !

Vous utiliserez les fonctions et fonctionnalités suivantes pour trouver la réponse à quatre questions :

SOMME.SI.ENS
INDEX
EQUIV
MAX
SOMME
NB.SI
UNIQUE
MISE EN FORME CONDITIONNELLE

Important à savoir

Les ensembles de données utilisées dans ce excercice appartient à tpg, l’opérateur de transports publics de la ville et du canton de Genève en Suisse. Les données n’ont pas été modifiées et représente un ensemble complet de données du monde réel sur les kilomètres produits par jour et par ligne en 2023 par les véhicules de tpg (28 606 lignes). Dernière modification par tpg : 12/02/2024 à 14h56.

Télécharger les données ici. Ci-dessous l’aperçu de l’ensemble de données.

Problèmes

Mettez en évidence les 5 plus grands kilomètres produits.
Quelle ligne a parcouru la plus grande distance en 2023 ?
Combien de ligne(s) n’ont pas voyagé pendant les vacances en 2023 ?
La déclaration suivante est-elle correcte : “En 2023, les véhicules des tpg ont parcouru une distance équivalente à 728 fois autour de la Terre” ?

Solutions

1. Voici les étapes à suivre pour mettre en évidence les 5 plus grands kilomètres produits:

Sélectionnez toutes les valeurs sous la colonne Kilomètres Produits en sélectionnant la première valeur (cellule E2) puis en appuyant sur Ctrl + Maj + Flèche vers le bas.
Dans l’onglet Acceuil, sélectionnez Mise en forme conditionnelle.
Sélectionnez “Règles des valeurs de plage haute/basse“, puis “10 valeurs les plus élévées…“
Maintenant, remplacez 10 par 5 et appuyez sur OK. C’est tout.
Pour voir les valeurs mises en évidence, vous pouvez trier la colonne Kilomètres Produits par ordre décroissant.

2. La ligne 14 est celle qui a parcouru la plus grande distance en 2023

Voici comment procéder:

Tout d’abord, utilisons la fonction UNIQUE pour extraire toutes les lignes individuelles. Le résultat de cette étape est une matrice. Nous lui attribuons une colonne nommée Lignes Uniques.

=UNIQUE(B2:B28607)

Le résultat ressemble à ceci (juste un extrait):

Ensuite, utilisons la fonction SOMME.SI.ENS pour trouver le total des kilomètres produits par chaque ligne. Nous attribuons le résultat à une colonne nommée Kilomètres Par Ligne. En savoir plus sur la fonction SOMME.SI.ENS.

=SOMME.SI.ENS($E$2:$E$28607;$B$2:$B$28607;I2)

Dans le formule ci-dessus, $E$2:$E$28607 désigne la plage à additionner si la valeur de Ligne (colonne B) est égale à celle de la valeur de Ligne Uniques (colonne I). Le résultat ressemble à ceci (juste un extrait):

Enfin, utilisons une combinaison de trois fonctions pour trouver la ligne correspondant au plus grand Kilomètre Par Ligne, le résultat constitue la réponse à cette question. Vous trouverez ci-dessous une la formule et son explication détaillée.

=INDEX(I2#;EQUIV(MAX(J2:J108);J2:J108;0))

La formule Excel ci-dessus recherche la valeur maximale dans la plage J2:J108 (la colonne J de la ligne 2 à la ligne 108) à l’aide de la fonction MAX().

Ensuite, la fonction EQUIV() est utilisée pour trouver la position de cette valeur maximale dans la plage J2:J108.

Enfin, la fonction INDEX() est utilisée pour renvoyer la valeur correspondante dans la matrice I2# en utilisant la position trouvée avec EQUIV(). En d’autres termes, cela renvoie la valeur de la cellule dans la colonne I qui correspond à la ligne où se trouve la valeur maximale dans la colonne J.

Si nous écrivons correctement la formule, nous obtiendrons 14, ce qui signifie que parmi les lignes de tpg, la ligne 14 est celle qui a parcouru la plus grande distance en 2023.

3- 9 lignes n’ont pas voyagé pendant les vacances en 2023

En utilisant la colonne Lignes Uniques que nous avons obtenue grâce à la fonction UNIQUE ci-dessus, nous pouvons appliquer la formule SOMME.SI.ENS pour trouver le total des kilomètres produits par chaque ligne pendant les vacances. Pour cela, nous allons ajouter le deuxième critère qui est Horaire Type doit être égal à VACANCES. Nous attribuons le résultat à une colonne nommée Kilomètres Par Ligne.

=SOMME.SI.ENS($E$2:$E$28607;$B$2:$B$28607;I2;$D$2:$D$28607;"VACANCES")

Dans la formule ci-dessus, vous pouvez remarquer que nous avons deux paires de plage_critère et critère ($B$2:$B$28607, I2 et $D$2:$D$28607, “VACANCES”). Référez-vous à ce tutoriel pour en savoir plus sur la fonction SOMME.SI.ENS.

Voici un extrait du résultat après cet étape:

Maintenant que nous avons les données ci-dessus, sachant que lorsque la valeur de la colonne Kilomètres Par Ligne d’une ligne est égal 0, cela signifie que cette ligne n’a pas voyagé, nous pouvons utiliser la fonction NB.SI pour compter les valeurs sous la colonne Kilomètres Par Ligne qui sont égales à 0, ce qui signifie que nous comptons indirectement les lignes qui n’ont pas voyagé pendant les vacances. La fonction NB.SI prend une plage et un critère. Si une valeur dans la plage satisfait le critère, elle compte 1, si une autre encore le satisfait, elle compte 2, et ainsi de suite.

=NB.SI(J2:J108;0)

Le rsultat est 9, ce qui signifie que 9 lignes des tpg n’ont pas voyagé pendant les vacances en 2023.

4- La déclaration est vraie car lorsque nous additionnons toutes les valeurs sous la colonne Kilomèteres Produts et que nous les divisons par la circonférence de la Terre, nous obtenons 728.

Voici comment le faire:

Utilisez d’abord la fonction SOMME pour additionner toutes les valeurs de la colonne Kilomètres Produits. Voici la formule :

=SUM(E2:E28607)

Le resultat est 29179163,89

Ensuite divisez le résultat obtenu par la circonférence de la terre (40075 km) et vous obtiendrez une valeur très proche de 728.

C’est tout. Cliquez ici pour plus d’exercices sur Excel.

SOME BOOKS TO LEARN EXCEL

As an Amazon Associate, I earn from qualifying purchases.