Función MOD De Excel Para Obtener El Resto Y Más

El tutorial muestra cómo usar la función MOD para realizar la operación de módulo en Excel. También aprenderá cómo encontrar el resto puede ayudarlo a resolver tareas más complejas, como sumar cada enésima celda, resaltar números pares o impares, y más.

La función MOD de Excel está diseñada para obtener el resto de la división. Sencillo, ¿no? Pero esta función es capaz de mucho más que su propósito original, y en un momento aprenderá algunos usos no triviales de MOD en Excel que pueden resultarle útiles en su trabajo.

Función MOD de Excel: sintaxis y usos básicos

La función MOD en Excel se usa para encontrar el resto después de la división de un número (dividendo) por otro (divisor). En matemáticas, esta operación se denomina operación módulo o módulo, de ahí el nombre MOD.

La función MOD tiene la siguiente sintaxis:

MOD(número, divisor)

Dónde:

Número (obligatorio) – el número a dividir.
Divisor (obligatorio) – el número por el que dividir.

Por ejemplo, MOD(10,3) devuelve 1 porque 10 dividido por 3 tiene un cociente de 3 y deja un resto de 1 (10=3*3+1). La fórmula MOD(10,5) devuelve cero porque 10 se divide por 5 sin resto.

3 cosas que debes saber sobre MOD en Excel

El resultado de la función MOD tiene el mismo signo que el divisor.
Si el divisor es 0, MOD devuelve el #DIV/0! error porque no se puede dividir por cero.
Si el número o el divisor es un valor de texto, una fórmula de Excel Mod devuelve el #¡VALOR! error.

Fórmulas MOD para calcular celdas

En las hojas de trabajo de la vida real, la función MOD de Excel rara vez se usa sola. Más a menudo, lo encontrará como parte de fórmulas más grandes que realizan varios cálculos basados en el resto de la división.

Mod fórmula para sumar cada enésima fila o columna

Por sorprendente que parezca, Microsoft Excel no tiene una función integrada para sumar cada fila 2, 3, etc. en una hoja de cálculo. Sin embargo, eso se puede hacer fácilmente usando una de las siguientes fórmulas.

Suma cada dos filas

Para sumar valores en cada segunda fila, use MOD junto con ROW y SUMPRODUCT:

Suma incluso filas:

SUMAPRODUCTO((MOD(FILA(rango),2)=0)*(rango))

Suma extraño filas:

SUMAPRODUCTO((MOD(FILA(rango),2)=1)*(rango))

Suponiendo que B2:B7 son las celdas a sumar, las fórmulas se ven de la siguiente manera:

Suma incluso filas:
=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=0)*($B$2:$B$7))

Suma extraño filas:
=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=1)*($B$2:$B$7))

La siguiente captura de pantalla muestra el resultado:

En estas fórmulas, la combinación MOD ROW determina qué filas sumar y SUMPRODUCT suma los valores. Así es cómo:

FILA proporciona una matriz de números de fila al argumento numérico de la función MOD. MOD divide cada número de fila por 2, y compruebas el resultado:

Para resumir incluso filas, el resultado debe ser 0 porque los números pares son divisibles por 2 de manera uniforme, sin resto.
Para resumir extraño filas, el resultado debe ser 1 porque los números impares divididos por 2 dejan un resto de 1.

En lugar de SUMPRODUCT, podría usar la función IF para evaluar la condición y SUM para sumar los números (es una fórmula de matriz, que se ingresa presionando Ctrl + Shift + Enter):

=SUM(IF(MOD(ROW($B$2:$B$7),2)=0,$B$2:$B$7,0))

Personalmente, me quedaría con SUMPRODUCT porque ROW es una función volátil y usarla en una fórmula de matriz hace que toda la fórmula sea volátil. Eso significa que la fórmula se volverá a calcular con cada cambio que realice en un libro de trabajo, lo que puede ralentizar notablemente su Excel.

Suma cada N-ésima fila

Para sumar cada fila 3, 4, 5, etc., use esta fórmula genérica:

SUMAPRODUCTO((MOD(FILA(rango)-FILA(primera_celda)+1,n)=0)*(rango))

Por ejemplo, para sumar cada tercera celda en el rango C2:C10, la fórmula es la siguiente:

=SUMPRODUCT((MOD(ROW($C$2:$C$10)-ROW($C$2)+1,3)=0)*($C$2:$C$10))

En comparación con el ejemplo anterior, esta fórmula tiene una «lógica de filtrado» un poco más compleja:

MOD(ROW($C$2:$C$10)-ROW($C$2)+1,3)=0

Primero, resta la primera fila de todas las demás filas en el rango para obtener una matriz de números de fila relativos {0; 1; 2; 3; 4; 5; 6; 7; 8}, a los que agrega 1 para comenzar a contar de uno: {1;2;3;4;5;6;7;8;9}

La matriz anterior entra en el argumento numérico de la función MOD, que devuelve el resto después de dividir cada número por 3: {1;2;0;1;2;0;1;2;0}

Tenga en cuenta que aparecen ceros para cada 3.ª fila, y MOD()=0 le dice a Excel que devuelva VERDADERO solo para estas filas, FALSO para todas las demás filas. Con VERDADERO igual a 1 y FALSO a 0, nos queda una matriz de {0,0,1,0,0,1,0,0,1}

Finalmente, SUMPRODUCT multiplica la matriz anterior y C2:C10, y luego suma los productos. Dado que multiplicar por cero siempre da 0, solo las filas que contienen 1 en la primera matriz sobreviven a la multiplicación.

Suma cada enésima columna

Para sumar números en cada columna 2, 3, etc., simplemente reemplace FILA con COLUMNA en la fórmula anterior, así:

SUMAPRODUCTO((MOD(COLUMNA(rango)-COLUMN(primera_celda)+1,n)=0)*(rango))

Por ejemplo, para sumar números en cada cuarta columna en el rango B2:I2, usa esta fórmula:

=SUMPRODUCT((MOD(COLUMN($B2:$I2)-COLUMN($B2)+1,4)=0)*($B2:$I2))

Mod fórmula para concatenar cada N celdas

En situaciones en las que los detalles relacionados con el mismo elemento se dividen en varias celdas, combinar los contenidos de cada número N de celdas en una celda puede ser un desafío.

En este ejemplo, tenemos detalles de pedidos en tres celdas diferentes en la columna B, por lo que buscamos concatenar cada bloque de tres celdas, comenzando con B2. Esto se puede hacer con la siguiente fórmula:

=IF(MOD(ROW()-1, 3)=0, CONCATENATE(B2, "-", B3, "-", B4), "")

¡Nota IMPORTANTE! Para que la fórmula funcione correctamente, debe ingresarse en la 3.ª fila con datos (C4 en este ejemplo) y luego copiarse hacia abajo en la columna.

La lógica de la fórmula es muy simple:

Primero, verifica si una fila dada es divisible por 3 sin resto: MOD(ROW()-1, 3)=0. Para obtener la posición relativa de la fila actual, restamos 1 del número de fila real (dado que nuestros datos comienzan en la fila 2, no en la fila 1). Si el resto de la división es igual a 0, la función CONCATENAR combina valores de 3 celdas y los separa con el delimitador de su elección («-» en este ejemplo). El uso de referencias de celdas relativas garantiza que se concatenen tres celdas diferentes cada vez.

Consejo. En lugar de la corrección -1, puede obtener la posición relativa de cada fila como hicimos en el ejemplo anterior, es decir, IF(MOD(ROW()-ROW($B$2)+1, 3)=0, CONCATENATE() , «»)

Esto hará que su fórmula sea más flexible y no requerirá más ajustes sin importar en qué fila comiencen sus datos.

Modificar fórmulas para contar celdas que contienen números pares o impares

Como ya sabes, la forma más fácil de identificar un número par es dividir el número por 2 y verificar que no quede nada. Para identificar un número impar, busca un resto de 1. De nuevo, usaremos la función MOD para encontrar restos y SUMPRODUCT para contar las «celdas filtradas».

Contar células con extraño números:

SUMAPRODUCTO((MOD(rango,2)=1)*1)

Contar células con incluso números:

SUMAPRODUCTO((MOD(rango,2)=0)*1)

Con números en las celdas A2 a A8, las fórmulas toman la siguiente forma:

Contar las celdas que contienen extraño números:

=SUMPRODUCT((MOD(A2:A8,2)=1)*1)

Contar las celdas que contienen incluso números:

=SUMPRODUCT((MOD(A2:A8,2)=0)*1)

Cómo usar MOD en Excel para resaltar celdas

Además de calcular celdas en función de un resto específico, la función MOD en Excel se puede usar para resaltar esas celdas. Para ello, tendrá que crear una fórmula basada en regla de formato condicional. Los pasos detallados para configurar una regla se pueden encontrar aquí y las fórmulas se proporcionan a continuación.

Resalta números pares e impares

Para resaltar las celdas que contienen números pares o impares, use la siguiente fórmula de Excel Mod que divide el número por 2 y luego busca un resto de 1 y 0, respectivamente:

Resaltar celdas con extraño números:

=MOD(A2,2)=1

Resaltar celdas con incluso números:

=MOD(A2,2)=0

Donde A2 es la celda más a la izquierda con datos.

Resaltar números enteros y decimales

Identificar enterossolo sigue esta cadena lógica: cualquier número dividido por 1 es igual a sí mismo, lo que significa que un número entero dividido por 1 es siempre el mismo número entero, lo que significa que el resto es cero, lo que significa que podemos usar esta sencilla fórmula MOD:

=MOD(A2,1)=0

Si el resto es mayor que cero, se trata de un fracción (incluido decimales):

=MOD(A2,1)>0

Dado que en las reglas de formato condicional basadas en fórmulas, las referencias de celda son relativas a la celda superior izquierda del rango seleccionado, en este ejemplo hacemos referencia a A2:

Resaltar múltiplos de un número

Para resaltar múltiplos de un valor específico, simplemente divida un número por ese valor y verifique que no quede nada.

Por ejemplo, para resaltar las celdas que contienen 10 y todos sus múltiplos, use esta fórmula:

=MOD(A3,10)=0

Donde A3 es la celda superior izquierda en el rango para el que crea la regla (A3:C9 en la captura de pantalla a continuación).

O bien, puede ingresar el número de interés en cualquier celda vacía, digamos C1, y hacer referencia a esa celda en su fórmula (recuerde bloquear la celda con una referencia absoluta):

=MOD(A3, $C$1)=0

Excel MOD con validación de datos

Otro uso inteligente de la función MOD de Excel puede ser evitar la entrada de ciertos datos en celdas seleccionadas, como números impares, números pares o decimales. Esto se puede hacer con las mismas fórmulas MOD que hemos usado para resaltar celdas, pero esta vez definiremos una regla de validación de datos.

Para crear una regla de validación de datos basada en una fórmula, haga clic en Datos > Validación de datos. En la pestaña Configuración, seleccione Personalizado en el cuadro Permitir e ingrese su fórmula en el cuadro Fórmula. Para conocer los pasos detallados, consulte Cómo crear una regla de validación personalizada en Excel.

Por ejemplo, para permitir solo números enteros en las celdas A2:A8, seleccione estas celdas y configure una regla con esta fórmula:

=MOD(A2,1)=0

De manera similar, puede limitar la entrada de datos a números pares o impares:

Permitir solo extraño números: