Téléchargez l'exemple de classeur
Ce didacticiel Excel montre comment utiliser le Fonction MOD Excel pour calculer le reste après division.
Présentation de la fonction MOD
La fonction MOD renvoie le reste, ou module, d'un nombre après avoir effectué la division. Cependant, la fonction MOD n'est pas strictement pour nous aider avec nos problèmes de division. Il devient encore plus puissant lorsque nous voulons rechercher chaque Nième élément d'une liste ou lorsque nous devons générer un motif répétitif.
Pour utiliser la fonction de feuille de calcul MOD Excel, sélectionnez la cellule et tapez :
Syntaxe et entrées de la fonction MOD :
| 1 | =MOD(nombre, diviseur) |
numéro - Un numéro.
diviseur - Un nombre par lequel diviser.
Qu'est-ce que la fonction MOD ?
La fonction MOD renvoie le reste, ou module, d'un nombre après avoir effectué la division. Cependant, la fonction MOD n'est pas strictement pour nous aider avec nos problèmes de division. Il devient encore plus puissant lorsque nous voulons rechercher chaque Nième élément d'une liste ou lorsque nous devons générer un motif répétitif.
MOD Maths de base
Lorsque vous essayez de diviser 13 par 4, vous pouvez dire que la réponse est 3 reste 1. Le "1" dans ce cas est spécifiquement connu sous le nom de module (d'où le nom de la fonction MOD). Dans une formule alors, on pourrait écrire
| 1 | =MOD(13, 4) |
Et la sortie serait 1.
En regardant ce tableau, vous trouverez d'autres illustrations sur le fonctionnement de l'entrée/sortie du MOD.
| 1 | =MOD(A2,3) |
Notez que lorsque l'entrée était 3, il n'y avait pas de reste et donc la sortie de la formule était 0. De plus, dans notre table, nous avons utilisé la fonction ROW pour générer nos valeurs. Une grande partie de la puissance de MOD proviendra de l'utilisation de la fonction ROW (ou COLUMN) comme nous le verrons dans les exemples suivants.
MOD Somme une ligne sur deux
Considérez ce tableau :
À des fins d'illustration, la deuxième colonne a la formule
| 1 | =MOD(A2, 2) |
Pour ajouter toutes les lignes paires, vous pouvez écrire une formule SUMIF et demander aux critères de rechercher 0 valeurs dans la colonne B. Ou, pour ajouter toutes les lignes impaires, demander aux critères de rechercher 1 valeurs.
Cependant, nous n'avons pas du tout besoin de créer la colonne d'aide. Vous pouvez combiner la puissance de MOD dans SUMPRODUCT pour tout faire en une seule étape. Notre formule pour cela serait
| 1 | =SOMMEPROD(A2:A5, --(MOD(B2:B2, 2)=0) |
Comme elle se trouve dans SUMPRODUCT, la fonction MOD sera capable de gérer notre entrée de tableau. Nous avons déjà vu la sortie dans la colonne d'aide, mais le tableau de notre MOD dans cette formule sera {0, 1, 0, 1}. Après avoir vérifié les valeurs égales à 0 en appliquant le double unaire, le tableau sera {1, 0, 1, 0}. Le SUMPRODUCT fait ensuite sa magie ou multiplie les tableaux pour produire {2, 0, 4, 0}, puis additionne pour obtenir la sortie souhaitée de 6.
MOD Somme chaque Nième ligne
Puisqu'une formule de MOD(x, N) produira un 0 à chaque Nième valeur, nous pouvons l'utiliser pour aider les formules à choisir les valeurs à utiliser dans d'autres fonctions. Regardez ce tableau.
Notre objectif est de récupérer les valeurs de chaque ligne marquée "Total". Prenez note que le Total apparaît tous les 3rd ligne, mais à partir de la ligne 4. Notre fonction MOD utilisera donc 3 comme 2sd argument, et nous devons soustraire 1 du premier argument (puisque 4 -1 = 3). De cette façon, les lignes souhaitées que nous voulons (4, 7, 10) seront des multiples de 3 (3, 6, 9). Notre formule pour additionner les valeurs souhaitées sera
| 1 | =SOMMEPROD(C2:C10, --(MOD(LIGNE(A2:A10)+2, 3)=0)) |
Le tableau produit se transformera comme ceci :
| 12345 | {2, 3, 4, 5, 6, 7, 8, 9, 10}{1, 2, 3, 4, 5, 6, 7, 8, 9}{1, 2, 0, 1, 2, 0, 1, 2, 0}{Faux, faux, vrai, faux, faux, vrai, faux, faux, vrai}{0, 0, 1, 0, 0, 1, 0, 0, 1} |
Le tableau de critères de notre SOMMEPROD est maintenant configuré de la manière dont nous devons saisir tous les 3rd valeur, et nous obtiendrons le résultat souhaité de 90 $.
Somme MOD sur les colonnes
Jusqu'à présent, nous avons utilisé des exemples qui vont verticalement et utilisent ROW, mais vous pouvez également aller horizontalement avec la fonction COLUMN. Considérez cette disposition :
Nous voulons résumer tous les éléments. Notre formule pour cela pourrait être
| 1 | =SOMMEPROD(B2:E2*(MOD(COLONNE(B2:E2), 2)=0) |
Dans ce cas, nous sommes configurés pour saisir tous les 2sd colonne dans notre plage, de sorte que le SUMPRODUCT ne conservera que des valeurs non nulles pour les colonnes B et D. Pour référence, voici un tableau indiquant les numéros de colonne et leur valeur correspondante après avoir pris MOD 2.
Mettez en surbrillance chaque Nième ligne
Un autre endroit courant pour utiliser la fonction MOD est lorsque vous souhaitez qu'une ligne en surbrillance apparaisse chaque Nième ligne. La forme générale pour cela sera
| 1 | =MOD(LIGNE() ± décalage, N)=0 |
Où N est le nombre de lignes entre chaque ligne en surbrillance (c'est-à-dire pour mettre en surbrillance tous les 3rd ligne, N = 3), et Décalage est éventuellement le nombre que nous devons ajouter ou soustraire pour que notre première ligne en surbrillance s'aligne sur N (c'est-à-dire pour mettre en surbrillance tous les 3rd ligne mais en commençant à la ligne 5, nous aurions besoin de soustraire 2 puisque 5 -2 = 3). Notez qu'avec la fonction ROW, en omettant tous les arguments, elle renverra le numéro de ligne de la cellule dans laquelle se trouve la formule.
Utilisons notre table d'avant :
Pour appliquer une surbrillance à toutes les lignes Total, nous allons créer une nouvelle règle de mise en forme conditionnelle avec une formule de
| 1 | =MOD(LIGNE()-1, 3)=0 |
Lorsque la mise en forme conditionnelle applique cette formule, la ligne 2 verra
| 1234 | =MOD(2-1, 3)=0=MOD(1, 3) = 0=1=0=Faux |
La ligne 3 connaîtra une sortie similaire, mais la ligne 4 verra
| 1234 | =MOD(4-1, 3)=0=MOD(3, 3) = 0=0=0=Vrai |
Nous avons donc notre règle qui fonctionne correctement, comme indiqué ici :
Mettez en surbrillance les nombres entiers ou pairs
Plutôt que de mettre en évidence des lignes spécifiques, vous pouvez également vérifier les valeurs réelles dans les cellules. Cela peut être utile lorsque vous voulez trouver des nombres qui sont des multiples de N. Par exemple, pour trouver des multiples de 3, votre formule de format conditionnel serait
| 1 | =MOD(A2, 3)=0 |
Jusqu'à présent, nous avons eu affaire à des nombres entiers. Vous pouvez cependant avoir une entrée d'un nombre décimal (par exemple 1,234), puis diviser par 1 pour obtenir uniquement la partie décimale (par exemple 0,234). Cette formule ressemble
| 1 | =MOD(A2, 1) |
Sachant que, pour ne mettre en évidence que des entiers, la formule de format conditionnel serait
| 1 | =MOD(A2, 1)=0 |
Concaténer toutes les N cellules
Nous utilisions auparavant MOD pour indiquer à l'ordinateur quand saisir la valeur à chaque Nième élément. Vous pouvez également l'utiliser pour déclencher l'exécution d'une formule plus importante. Considérez cette disposition :
Nous voulons concaténer les noms ensemble, mais seulement tous les 3rd ligne à partir de la ligne 2. La formule utilisée pour cela est
| 1 | =SI(MOD(LIGNE()+1, 3)=0, CONCATENER(A2," ", A3," ", A4), "") |
Notre fonction MOD est ce qui agit comme critère pour la fonction SI globale. Dans cet exemple, nous devions ajouter 1 à notre ROW, car nous commençons à la ligne 2 (2 + 1 = 3). Lorsque la sortie du MOD est 0, la formule fait la concaténation. Sinon, il retourne simplement vide.
Compter les valeurs paires/impaires
Si vous avez déjà eu besoin de compter le nombre de valeurs paires ou impaires dans une plage, vous saurez que COUNTIF n'a pas la capacité de le faire. Nous pouvons le faire avec MOD et SUMPRODUCT. Regardons ce tableau :
La formule que nous utiliserons pour trouver les valeurs impaires sera
| 1 | =SOMMEPROD(1*(MOD(A2:A7, 2)=1)) |
Plutôt que de charger certains numéros de ligne, notre MOD va charger les valeurs réelles des cellules dans le tableau. La transformation globale se déroulera alors ainsi :
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- A pris le mod de 2{ Vrai, Vrai, Vrai, Vrai, Faux, Vrai } <- Vérifié si la valeur était 0{1, 1, 1, 1, 0, 1} <- Multiplié par 1 pour convertir de Vrai/Faux en 1/0 |
Le SUMPRODUCT additionne ensuite les valeurs de notre tableau, donnant la réponse souhaitée : 5.
Motif répétitif
Tous les exemples précédents ont vérifié la sortie de MOD pour une valeur. Vous pouvez également utiliser MOD pour générer un motif répétitif de nombres, ce qui peut à son tour être très utile.
Tout d'abord, disons que nous avons une liste d'éléments que nous voulons répéter.
Vous pouvez essayer de copier-coller manuellement autant de fois que vous en avez besoin, mais cela deviendrait fastidieux. Au lieu de cela, nous voudrons utiliser la fonction INDEX pour récupérer nos valeurs. Pour que l'INDEX fonctionne, nous avons besoin que l'argument de ligne soit une séquence de nombres qui va {1, 2, 3, 1, 2, 3, 1, etc.}. Nous pouvons accomplir cela en utilisant MOD.
Tout d'abord, nous allons commencer par la fonction ROW. Si vous commencez par
| 1 | =LIGNE(A1) |
Et puis copiez cela vers le bas, vous obtenez la séquence de nombres de base de {1, 2, 3, 4, 5, 6,… }. Si nous appliquons la fonction MOD avec 3 comme diviseur,
| 1 | =MOD(LIGNE(A1), 3) |
nous obtiendrions {1, 2, 0, 1, 2, 0,… }. Nous voyons que nous avons un motif répétitif de "0, 1, 2", mais il manque le 0 initial à la première série. Pour résoudre ce problème, sauvegardez une étape et soustrayez 1 du numéro de ligne. Cela changera notre séquence de départ en {0, 1, 2, 3, 4, 5,… }
| 1 | =MOD(LIGNE(A1)-1, 3) |
Et après sa sortie du MOD, nous avons {0, 1, 2, 0, 1, 2,… }. Cela se rapproche de ce dont nous avons besoin. La dernière étape consiste à ajouter 1 au tableau.
| 1 | =MOD(LIGNE(A1)-1, 3)+1 |
Ce qui produit maintenant une séquence de nombres de {1, 2, 3, 1, 2, 3,… }. C'est notre séquence désirée! En le branchant sur une fonction INDEX, nous obtenons notre formule de
| 1 | =INDEX(MaListe, MOD(LIGNE(A1)-1, 3)+1) |
La sortie ressemblera maintenant à ceci :
Exemples de MOD en VBA
Vous pouvez également utiliser la fonction DROITEREG en VBA.
Dans VBA, MOD est un opérateur (tout comme les opérateurs plus, moins, multiplication et division). Donc, en exécutant les instructions VBA suivantes
| 123456 | Plage ("C2") = Plage ("A2") Plage Mod ("B2")Plage ("C3") = Plage ("A3") Plage Mod ("B3")Plage ("C4") = Plage ("A4") Plage Mod ("B4")Plage ("C5") = Plage ("A5") Plage Mod ("B5")Plage("C6") = Plage("A6") Plage de modulation("B6")Plage ("C7") = Plage ("A7") Plage Mod ("B7") |
produira les résultats suivants
Pour les arguments de la fonction (known_y's, etc.), vous pouvez soit les entrer directement dans la fonction, soit définir des variables à utiliser à la place.
Revenir à la liste de toutes les fonctions dans Excel
Fonction MOD Google Sheets
La fonction MOD fonctionne exactement de la même manière dans Google Sheets que dans Excel :
