Comment utiliser la fonction MOD d'Excel pour résoudre les problèmes du monde réel

La fonction MOD dans Microsoft Excel vous permet de savoir si un numéro est divisible par un autre. Si ce n'est pas le cas, cela vous dit ce qui reste. Par exemple, si vous divisez 3 par 2, la fonction MOD reviendra 1. Dans ce guide, je vais vous montrer comment vous pouvez l'utiliser pour résoudre des problèmes réels.

La syntaxe de la fonction mod

Avant de vous montrer la fonction Mod en action, prenons un moment pour voir comment cela fonctionne:

=MOD(n,d)

où n est le numéro que vous souhaitez diviser, et d est le diviseur. Donc, si vous voulez diviser 3 par 2, 3 est le nombre, et 2 est le diviseur.

Si le diviseur est négatif, le résultat sera également négatif. D'un autre côté, si le diviseur est 0, Excel renverra le # div / 0! Erreur, car vous ne pouvez pas diviser un nombre par zéro.

Cependant, la fonction MOD est rarement utilisée à elle seule – sa vraie valeur apparaît vraiment lorsqu'elle est utilisée aux côtés d'autres fonctions et outils Excel.

Exemple 1: Utilisation de la fonction Mod pour minimiser les restes

Disons que vous avez divers éléments que vous souhaitez emballer et distribuer. La colonne A contient l'ID du produit, la colonne B vous indique combien de chaque produit vous disposez et la ligne 2 contient les différentes tailles de package. Votre objectif est de déterminer la taille optimale du package pour chaque produit, selon le nombre de chaque produit que vous disposez.

Dans ce cas, vous pouvez utiliser la fonction MOD pour vous dire si le nombre de produits dans la colonne B est divisible par la taille du package dans la ligne 2, et si ce n'est pas le cas, combien vous en avez.

Donc, dans la cellule C3, type:

=MOD($B3,C$2)

Le symbole du dollar ($) placé avant une ligne ou une référence de colonne – également connue sous le nom de référence mixte – les vestes excellent pour la verrouiller afin que la formule puisse être appliquée à d'autres cellules de la gamme.

Lorsque vous appuyez sur ENTER (ou CTRL + ENTER pour rester dans la même cellule), vous pouvez voir que si vous utilisez un package avec 10 espaces pour le produit 2805, quatre resteront déballés.

Maintenant, lorsque vous cliquez et faites glisser la poignée de remplissage dans la cellule C3 vers la droite pour compléter la ligne, vous pouvez voir que l'utilisation de packages avec huit ou trois espaces est idéal pour ce produit.

Ensuite, dans Cell G3, vous voulez Excel pour vous dire la meilleure taille de package à utiliser pour ce produit. Pour ce faire, utilisez l'index et XMatch:

=INDEX($C$2:$F$2,1,XMATCH(SMALL(C3:F3,1),C3:F3))

où Index ($ C 2 $: $ F 2,1 $ dit à Excel de renvoyer une valeur des cellules C2 à F2, et XMatch (Small (C3: F3,1), C3: F3)) recherche la plus petite valeur dans les cellules C3 à F3 pour définir le numéro de colonne qui alimente la partie index de la formule.

Enfin, sélectionnez les cellules C3 à G3 et double-cliquez sur la poignée de remplissage pour dupliquer toutes les formules aux lignes restantes dans la gamme.

Maintenant, grâce à la fonction MOD utilisée dans les cellules C3 à F12, la colonne G vous indique les meilleures tailles de package à utiliser pour chaque produit, et vous pouvez consulter dans les colonnes C à F pour chaque produit pour voir combien (le cas échéant) vous aurez laissé après avoir utilisé cette taille optimale de package.

Si la plus petite valeur se produit deux fois de suite, XMatch utilise la première instance de gauche à droite par défaut. Dans ce cas, cela est idéal car vous voulez utiliser le moins d'emballage possible.

La beauté de l'utilisation de cette combinaison de fonctions est que si le nombre de produits dans la colonne B change, la taille optimale du package se met à jour en fonction des formules de mod que vous avez utilisées dans les cellules C3 à F12. Par exemple, si vous avez mal comptabilisé le produit 2805, et que vous en avez réellement 25 au lieu de 24, lorsque vous mettez à jour la valeur dans la cellule B3, vous constatez que la taille optimale du package passe à cinq.

Exemple 2: Utilisation de la fonction Mod pour mettre en évidence certaines cellules

La fonction MOD d'Excel n'a pas besoin d'être utilisée pour renvoyer une valeur – elle peut également être utilisée pour déterminer quelles cellules doivent être formatées par formatage conditionnel.

Dans cet exemple, imaginez que vous voulez créer des activités pour dix groupes de personnes. Cependant, différentes activités nécessitent différents nombres de sous-groupes, vous devez donc déterminer quels groupes peuvent réaliser quelle activité, garantissant que tous les membres sont impliqués.

Tout d'abord, dans la cellule B1, vous souhaitez créer une liste déroulante des activités de la colonne D. Pour ce faire, avec la cellule B1 sélectionnée, dirigez-vous vers l'onglet « Données » sur le ruban et cliquez sur la moitié supérieure du bouton « Validation des données ».

Maintenant, lorsque vous sélectionnez la cellule B1, vous pouvez voir une liste déroulante des activités, et vous êtes prêt à appliquer le formatage aux cellules A4 à B13 qui vous indique si chaque groupe peut effectuer chaque activité.

Pour ce faire, sélectionnez les cellules A4 à B13, et dans l'onglet Home sur le ruban, cliquez sur Formatage conditionnel> nouvelle règle.

Ensuite, dans la boîte de dialogue de la nouvelle règle de formatage, sélectionnez « Utilisez une formule pour déterminer les cellules à formater » et dans le champ de formule, type:

=MOD($B4,XLOOKUP($B$1,$D$2:$D$9,$E$2:$E$9))=0

où

• $ B4 est le premier des cellules de la colonne B agissant comme le nombre à diviser,
• Xlookup ($ b 1 $, $ d 2 $: $ d 9 $, $ e 2 $: $ e 9 $) recherche la valeur dans la cellule B1 et renvoie la taille du sous-groupe correspondant de la table de recherche (le diviseur), et
• = 0 Applique le formatage aux cellules où le nombre de membres d'un groupe est divisible par le nombre de personnes requises dans le sous-groupe d'une activité.

Placer le symbole du dollar avant la référence de la colonne ($ b4) dans cette formule indique à Excel que vous voulez que le formatage soit conditionnel sur toutes les cellules sélectionnées de la colonne B.

En d'autres termes, vous voulez Excel pour formater les groupes où la taille entière du groupe est divisible par la taille du sous-groupe requise pour l'activité sélectionnée dans la cellule B1.

Enfin, cliquez sur « Format » pour choisir le formatage pour les cellules où ces conditions sont remplies. Dans mon cas, je suis allé pour un fond de cellule vert clair.

Cliquez sur « OK » pour fermer la boîte de dialogue.

Maintenant, sélectionnez une activité dans le menu déroulant dans la cellule B1, et les groupes qui peuvent participer à cette activité seront mis en évidence en fonction du formatage que vous avez sélectionné. Dans cet exemple, vous pouvez voir que les groupes 1, 2, 3, 5 et 8 peuvent participer au quiz car le nombre total de membres dans ces groupes est divisible par la taille du sous-groupe requise pour cette activité.

D'un autre côté, lorsque vous sélectionnez « randonnée » dans la liste, seul le groupe 5 est identifié comme étant capable de faire cette activité, car c'est le seul groupe dont le nombre global est divisible par la taille du sous-groupe.

Exemple 3: Utilisation de la fonction mod pour cracher des valeurs de date-temps

La fonction MOD de Microsoft Excel peut être utilisée pour extraire la partie décimale d'un nombre. Dans cet exemple, taper:

=MOD(A2,1)

dans la cellule B2 divise 10.2 par 1 et vous indique que le reste de cette division est de 0,2.

Vous pouvez également extraire l'entier de ce nombre en utilisant la fonction int, qui arrond une valeur vers le nombre entier le plus proche. Donc, tapant:

=INT(A2)

dans la cellule C2 supprime la décimale de la valeur dans la cellule A2, renvoyant le nombre entier uniquement.

De la même manière, vous pouvez utiliser MOD et INT pour diviser les valeurs de date-heure.

Ici, la cellule A2 contient une date et une heure, et la cellule A3 contient le numéro de série correspondant.

Excel stocke les dates et les heures comme numéros de série, qui peuvent être vus lorsque vous modifiez le format de nombre en «général». Ainsi, dans cet exemple, les valeurs des cellules A2 et A3 sont identiques, mais le format de nombre pour la cellule A2 est un format de numéro de date de date personnalisé, tandis que le format de nombre pour la cellule A3 est le format de numéro général.

Dans le numéro de série dans la cellule A3, 45782 représente la date et 0,4375 représente le temps, et vous pouvez utiliser Mod et INT pour les diviser.

Tout d'abord, sélectionnez la cellule B2 et modifiez le format de nombre en « temps » dans le groupe numérique de l'onglet Home sur le ruban.

Ensuite, sélectionnez la cellule C2 et modifiez le format de numéro en «date courte».

Ensuite, retournez à la cellule B2 et tapez:

=MOD(A2,1)

Cela extrait la décimale (temps) de la représentation du numéro de série de la valeur de date de date dans la cellule A2, et parce que vous définissez le format de numéro de cellule à l'heure, il convertit la décimale série en une valeur temporelle.

Enfin, retournez à la cellule C2 et tapez:

=INT(A2)

Cela extrait l'entier (date) du numéro de série dans la cellule A2 en l'arrondissant vers le numéro entier le plus proche, et comme vous le définissez comme un format de numéro de date court, il convertit l'entier de série en une valeur de date.

---

La moitié de la bataille avec l'utilisation de fonctions dans Microsoft Excel est de savoir laquelle est la meilleure pour résoudre votre problème. Par exemple, si vous souhaitez affiner vos calculs, la fonction agrégée fera probablement l'affaire, et si vous souhaitez créer une liste de nombres, la fonction de séquence est votre meilleur pari. Heureusement, Excel est là pour vous aider à trouver la fonction dont vous avez besoin – vous pouvez cliquer sur le bouton « FX » à côté de la barre de formule ou rechercher une fonction dans la boîte de dialogue d'insertion, et les outils de fonction d'Excel vous aideront en cours de route lorsque vous créez la formule parfaite.