Actualizador Dinámico de Impuestos. Parte 2: Método Alternativo para Recargos en Excel.

Introducción.

        El objetivo de este artículo es abundar más en el tema de recargos, ya que muchos lectores me han dicho que han visto y/o utilizado una tabla de recargos tal cual donde les muestra los meses que se van a actualizar, de ahí suman los porcentajes y sacan el resultado.

Conociendo los diferentes tipos de inteligencia que existen, se entiende que quienes tienen una inteligencia visual, quieran tener un cálculo "más manual" por así denominarlo, y es que muchos, aún en la era de las computadoras, tienen la necesidad de "palpar" y/o tener algo "tangible", y es por eso que en 2024 aún hay quienes usan la sumadora.

Es por eso que, en esta ocasión, les anexo la segunda parte del Actualizador Dinámico de Impuestos para que de esta manera el usuario pueda ver los meses que se están considerando, así como los periodos que se están aplicando de una manera "tangible", ya que incluso hay quienes gustan de imprimir esta hoja para anexarla a los papeles de trabajo.

         Para más información puedes revisar el artículo Actualizador Dinámico de Impuestos dando clic en un hipervínculo. 

Configuración Inicial de la Nueva Hoja de Recargos.

Vamos a crear una nueva hoja, similar a lo hecho en el artículo anterior, tecleamos la combinación de teclas SHIFT + F11, a esta hoja la nombraremos Bonus Recargos, le puedes llamar como gustes.

También vamos a utilizar los mismos rangos dinámicos definidos en el ejercicio anterior para más detalle clic aquí.

Método Visual para el Cálculo de Recargos (Hoja Bonus Recargos).

Diseño y Formato de la Tabla de Recargos.

Ahora vamos a darle formato a la nueva hoja de recargos, así como poblarla con las nuevas fórmulas que, como he mencionado anteriormente, tienes la libertad de implementarlas, adoptarlas e incluso mejorarlas en tus propios papeles de trabajo. Manos a la obra.

1. En la Celda A7 ponemos el texto "Mes/Año".
2. Dejo la fila 8 en blanco y continúo en la fila 9, esto porque voy a dar un formato condicional para darle más visibilidad, aunque esto es netamente opcional.
3. En A9 pongo la fórmula:

-       FECHA(AÑO(HOY()),FILA()-8,1)

Donde:

-       Recordemos la sintaxis de la Fórmula: FECHA(Año,Mes,Día).

-       AÑO(HOY()): Determinamos el año en base a la fecha actual.

-       FILA()-8: Como el mes se determina en base a un número entero, lo que estamos haciendo es que la fórmula FILA() nos da como resultado 9 (recordando que estamos en la celda A9) y el menos 8 es para que nos devuelva 1.

-       1: Equivale al día.

Nota: De esta forma, suponiendo que estamos a 25/Jul/2024, estamos obteniendo 2024/1/1.

-       Aplicamos formato de fecha para mostrar el mes. Aplicamos el atajo de teclado CTRL + 1 > Categoría: Personalizada > Tipo: MMMM

4. Corremos la fórmula 11 posiciones hacia abajo para completar los meses.

5. Aplicamos Formato Condicional:

-       Usamos la fórmula $A8<>"", es decir, si en la referencia mixta (Columna fija $A y la fila variable) es distinto a "vacío", aplicar el formato de relleno color personalizado #21B868, texto blanco en negritas y bordes naranjas (el lector puede usar los colores a su elección).

-       Se aplica al rango $A$8:$A$1006 que, de hecho, no es necesario; podemos poner incluso solo al rango que queremos aplicarlo $A$8:$A$21, pero para ejemplos prácticos mostramos un rango más amplio.

6. En la Celda B7 pondremos la fórmula AÑO(Actualizador!$I$7) con el fin de que nos traiga el año en que se debió haber efectuado el pago de la celda I7 de la hoja Actualizador. De esta manera, cada que cambiemos la fecha, la fórmula se actualizará automáticamente.

7. En C7 vamos a poner la fórmula:

 SI(B7="","",SI(AÑO(Actualizador!$I$8)=B7,"",B7+1))

Donde tenemos una función SI anidada:

-       SI(B7="","",…: Si la celda B7 está vacía, entonces poner vacío, caso contrario...

-       SI(AÑO(Actualizador!$I$8)=B7,"",…: Comparamos si el año de la hoja Actualizador y la fecha en la que se realiza el pago (celda I8) es igual al valor en B7, entonces poner vacío, caso contrario... esto con efecto de que deje de actualizar los años. Por ejemplo, si el año que se paga fue el 2023, no tiene caso que me traiga y/o se actualice la celda a 2024.

-       B7+1: Si ninguna de las dos condiciones se cumple, entonces al año en B7 súmale 1 (para efectos de traer los datos del año siguiente).

Nota: Como podemos ver, la celda tiene un valor variable, es decir, si recorremos y/o copiamos la fórmula a la celda contigua, esta será C7, y si pasamos a la siguiente D7, y así sucesivamente.

Para efectos de la práctica, esta fórmula la copiaremos de la celda B7 hasta la Z7. Comprendo que el artículo 21 del Código Fiscal de la Federación nos habla de una actualización de hasta por 5 ejercicios (5 años) y que también en caso de alguna revisión de la autoridad y/o auditoría, esta revisión se puede ampliar hasta por 10 ejercicios. Pero para efectos de la práctica y ver lo "elástico" que puede ser un rango dinámico, lo dejamos con ese rango, aunque el lector puede usar los rangos estipulados en la ley.

8. Aplicamos Formato Condicional al nuevo rango:

-       Usamos la fórmula B$7<>"", es decir, si el valor en la celda es distinto a vacío, aplica el formato condicional. A diferencia del Formato Condicional anterior, ahora estamos usando un valor mixto, pero dejamos la columna (B) como variable y la fila $7 como valor fijo. Vamos a aplicar un color personalizado de relleno #3182DF, texto blanco en negritas y borde naranja (El lector puede usar los colores de su elección).

-       Se aplica a $B$7:$Z$7, dicho rango es consistente a la fórmula que pusimos en el mismo rango.

9. Opcional. De manera similar se crea otro Formato Condicional al rango A8:Z8, solo que el relleno es naranja, el color de borde y texto lo dejamos igual. Este es opcional y es solo para darle "vista" y/o armonía visual a nuestra tabla.

-       Fórmula: A$7<>"", similar al punto anterior, solo cambia la fórmula.

-       Se Aplica a $A$8:$Z$8 ya que abarcamos desde la columna A.

Aplicando la Formula SUMAPRODUCTO para traer los datos.

         Hemos llegado a la parte medular de este tutorial de Excel, donde aprenderemos a manipular datos utilizando una de las funciones más poderosas: SUMAPRODUCTO.

Si observamos nuestra tabla de trabajo, notaremos que ha invertido el orden de los encabezados y la primera columna:

• En la tabla original de recargos: los meses aparecen como encabezados y los años en la primera columna
• En nuestra nueva tabla: los años funcionan como encabezados y los meses ocupan la primera columna.

Métodos para Extraer Datos en Excel.

Existen diversas funciones de Excel para extraer datos:

• SI
• O
• BUSCARV
• ÍNDICE y COINCIDIR
• BUSCARX

Sin embargo, como estamos trabajando con matrices de datos, optaremos por SUMAPRODUCTO, una función potente y altamente recomendada para estos fines.

¿Por qué SUMAPRODUCTO?

A pesar de que las nuevas versiones de Excel han introducido funciones como:

• CONTAR.SI
• SUMAR.SI
• CONTAR.SI.CONJUNTO
• SUMAR.SI.CONJUNTO

SUMAPRODUCTO mantiene su vigencia debido a su potencia y versatilidad.

Implementación Práctica

En la celda B9, introduciremos la siguiente fórmula:

SI(B$7="","",SI.ERROR(SUMAPRODUCTO(

(Recargos_Ejercicio=B$7)*

(Recargos_Periodo=$A9)*

(FECHA(B$7,MES($A9),17)>=Actualizador!$I$7)*

(FECHA(B$7,MES($A9),17)<=Actualizador!$I$8)*

Recargos

),0))

Vamos a desglosar la formula por partes para su mejor entendimiento:

1.    SI(B$7=””,””,…

-       ¿Qué es lo que hace?: Verifica que la celda B$7 no este vacía.

-       ¿Por qué esta aquí?: Si B$7 este vacío, la formula retorna una cadena de texto vacía previniendo así la ejecución innecesaria de la formula mostrando un resultado “en blanco”.

-       ¿Cómo funciona?: Verifica si el contenido de la celda B$7 está vacío (““), de ser así devuelve un espacio vació (““) dejando la celda en blanco, caso contrario ejecuta la formula.

Nota: Esta formula la vamos a utilizar en todo el rango por lo que al igual que en los Ejercicios (Años) estamos utilizando un valor mixto, es decir la columna B es relativa, es decir cambia al correr la formula y/o copiar y pegar, mientras que $7  se mantiene fijo, es decir siempre estará en la fila 7.

2.    SI.ERROR(…,0) donde los … significan la formula a ejecutar, pero que si esta devuelve error en lugar de mostrarlo nos mostrará un 0.

-       ¿Qué es lo que hace?: Previene los errores retornando 0 si alguna de las partes de la formula SUMAPRODUCTO genera algún error.

-       ¿Por qué esta aquí?: Como lo mencione en el punto anterior Excel puede generar un error sino se encuentra ninguna coincidencia o si la formula tiene algún problema, así que esto garantiza un resultado limpio y una experiencia agradable al usuario.

-       ¿Cómo funciona?: Envolviendo con SI.ERROR alrededor de SUMAPRODUCTO significa que si hay algún error Excel mostrará un 0 en lugar del error previniendo así malos funcionamientos.

 Nota: Recordemos la sintaxis de la formula: SI.ERROR(valor, valor_si_error) donde en valor es la formula a evaluar y valor_si_error es el resultado a mostrar si hay alguna inconsistencia.

3.    SUMAPRODUCTO.

-       ¿Qué es lo que hace?: Calcula el resultado en base a múltiples condiciones, todo se multiplica junto.

-       ¿Por qué esta aquí?: SUMAPRODUCTO nos permite evaluar múltiples criterios de una sola vez. Más abajo vamos a desglosar la formula

-       ¿Cómo funciona?: SUMAPRODUCTO multiplica matrices de datos juntas y suma los resultados. Cada condición es VERDADERA (1) o FALSA (0), es decir actúa como una condición Y así que solo cuando todos los valores son verdaderos (1 x 1 x 1) este regresará el valor de los recargos.

Veámoslo con más detalle.

4.    (Recargos_Ejercicio = B$7)

-       ¿Qué es lo que hace?: Compara cada uno de los años en el nombre definido Recargos_Ejercicio con la celda variable B$7 que conforme en que columna se ejecute la formula será C$7, D$7, E$7, etc.

-       ¿Por qué esta aquí?: Para validar que el año contenido en la tabla y/o rango dinámico Recargos con el año en B$7.

-       ¿Cómo funciona?: Retorna el número 1 (VERDADERO) si el año coincide y 0 (FALSO) sino lo hace así pues cuando se multiplica por otras condiciones, solo las filas que coinciden con el año serán consideradas.

5.    (Recargos_Periodo = $A9)

-       ¿Qué es lo que hace?: Al igual que el anterior compara cada mes el nombre definido Recargos_Periodo con el mes en el rango variable $A9 (donde la columna $A permanece fija y la fila 9 es variable).

-       ¿Por qué esta aquí?: Esto asegura que los meses que están en el rango Recargos coincida específicamente con el mes en el rango misto $A9.

-       ¿Cómo funciona?: Como en la condición anterior cuando encuentra una coincidencia retorna 1 y si no hay coincidencia retorna 0 así solo multiplicará las filas que coincidan.

6.    (FECHA(B$7, MES($A9), 17) >= Actualizador!$I$7)

-       ¿Qué es lo que hace?: Revisa si una fecha específica en el año y mes es igual o mayor a la fecha en la que se debió haber efectuado el pago (Hoja Actualizador, celda I7)

-       ¿Por qué esta aquí?: Esta condición nos ayuda a asegurarnos que la fecha esta dentro del rango comenzando por la fecha en la que se debió haber efectuado el pago.

-       ¿Cómo funciona?: La función FECHA crea una fecha con el año contenido en el rango variable B$7, luego utiliza la función MES para extraer un número entero equivalente al número de mes en el rango variable $A9 y ahora es cuando nos hace sentido la formula que pusimos al principio en A9:

FECHA(AÑO(HOY()),FILA()-8,1) de esta manera podemos obtener fácil y dinámicamente el número del mes, finalmente el día 17 para así si la condición es verdadera, que la fecha sea mayor a Actualizador!$I$7 retorna 1, caso contrario retornara 0.

7.    (FECHA(B$7, MES($A9), 17) <= Actualizador!$I$8)

-       ¿Qué es lo que hace?: Similar a la condición anterior solo que esta condición evalua si la fecha es menor a la fecha en que se realizó el pago (Hoja Actualizador, celda I8).

-       ¿Por qué esta aquí?: Se asegura que la fecha esta en el rango especificado y valida la fecha final.

-       ¿Cómo funciona?: Similar a la condición previa, usa la función FECHA para generar una fecha tomando el año de B$7 y el mes de $A9 y fija el día al 17 del mes. Si la fecha es menor a la fecha en que se realiza el pago retorna 1, caso contrario retorna 0.

8.    Recargos (Rango dinámico y/o Nombre Definido)

-       ¿Qué es lo que hace?: Obtiene el valor de la tabla Recargos que corresponde al día, mes y año.

-       ¿Por qué esta aquí?: En la tabla Recargos mantenemos el rango actual de valores que queremos retornar cuando la condición y/o condiciones se cumplan.

-       ¿Cómo funciona?: Cuando todas las condiciones en SUMAPRODUCTO son VERDADERO (1) nos retorna el valor que existe en la tabla en esa específica fila, entonces como todos los valores se multiplicaran los únicos valores que se sumaran son los que tenga el valor de 1.

En Resumen

Paso 1: Si B$7 este vacío, la formula pondrá una cadena de caracteres en blanco (””).

Paso 2: Si el valor de B$7 no es vacío entonces SUMAPRODUCTO comenzará a calcular.

Paso 3: SUMAPRODUCTO recorre cada fila en recargos validando:

-       El año coincida con B$7.

-       El mes coincida con $A9.

-       La Fecha se encuentre Fecha en que se debió haber pagado (Actualizador!$I$7) y la Fecha en que se pago (Actualizador!$I$8).

Paso 4: Si todas las condiciones se en la fila se cumplen en la tabla Recargos nos dará como resultado VERDADERO es decir 1 y estos valores son los que se incluirán en la suma.

Paso 5: SI.ERROR_envuelve la formula SUMAPRODUCTO y regresa 0 si existe algún error de cálculo haciendo la formula contra errores.

Como se puede observar SUMAPRODUCTO es una formula altamente eficiente que busca en Excel checando con múltiples criterios y retorna un resultado solo cuando la o las condiciones se cumplen. Es una poderosa solución que usa todos lo niveles y combina el chequeo de condiciones, creación de fechas y cálculos basados en una matriz de datos para alcanzar el resultado de manera flexible.

Ya con la formula analizada, ahora copiémosla al rango B9:Z20 como los rangos son dinámicos no tenemos de que preocuparnos ya que cada celda se actualizará automáticamente. De la misma forma al poner un rango tan amplio podemos ver lo versátil y “Elasticidad” de la formula.  

Rangos Dinámicos, formato condicional y configuraciones finales.

Técnicamente ya hemos terminado nuestra hoja de cálculo de Recargos en Excel, donde visualmente podemos ver los meses que se van a actualizar para tener un proceso más cercano con el cálculo.

Como hemos observado, muchas de las funciones de Excel tienen una función SI que evalúa qué si la celda actual está vacía, entonces nos regresará un valor vacío, de esta forma evitamos poner valores en lugares que no necesitamos, pero cuando sea necesario (cuando se cumplan las condiciones dadas) nos proporcionará dichos valores.

De la misma forma sucede con los formatos condicionales, como recordarán pusimos la función que si la celda actual es diferente (<>) a un valor en blanco y/o vacío entonces no nos pondrá formato alguno.

Es así como poblamos con valores y formatos las celdas, así que vamos a poner rápidamente las fórmulas y valores a aplicar.

Configuración de Rangos Dinámicos

1. Nos dirigimos a Formulas -> Nombres definidos -> Administrador de nombres.
2. En el Administrador de nombres damos clic en Nuevo.
3. En un rango que denominaremos Recargos_Bonus ponemos la siguiente fórmula (para más información de la fórmula DESREF clic aquí:

DESREF(INPC!$A$9,0,0,CONTARA(INPC!$A:$A)-1,CONTARA(INPC!$7:$7))

Explicación de la Fórmula

A grandes rasgos:

-       DESREF(INPC!$A$9,... En la Hoja INPC celda A9, vamos a seleccionar

-       0, no nos recorremos ninguna fila

-       0, no nos recorremos ninguna columna

-       CONTARA(INPC!$A:$A)-1: Buscamos el valor de la altura de nuestra matriz de datos donde contaremos todos los valores en la columna A de la hoja INPC y le restamos -1 en nuestro ejemplo nos dará el conteo de 56 la razón de restarle uno es para que no nos cuente el encabezado, sino nos daría 57.

-       CONTARA(INPC!$7:$7)): Nos contará las columnas (ancho) del rango de la fila 7 que nos da como resultado 13, nos da la suma de tres porque estamos sumando los encabezados de los meses más el encabezado de los años y/o ejercicios fiscales.

Nota: Estos valores los podemos poner fijos como el 56 y el 13, pero a la hora de agregar ejercicios fiscales no se actualizaría y se quedaría estática. En cuanto a los meses sí podría quedar fija porque difícilmente se agregarán más meses, pero para efectos de la práctica lo dejamos dinámico.

Configuración de Formato Condicional.

4.    Vamos al Inicio -> Estilos -> Formato Condicional.

5.    Seleccionamos nueva regla.

6.    En el menú Nueva regla de formato, después seleccionamos Utilice una fórmula que determine las celdas para aplicar el formato de bordes naranjas, el usuario puede elegir el formato que desee.

7.    Ponemos la formula SI(Y($B9<>"",B$7<>""),RESIDUO(FILA(),2)=1,"")

8.    Realizamos un análisis condicional con las fórmulas SI e Y donde:

9.    SI(Y($B9<>"",B$7<>""): Si el valor en la ceda variable $B9 es diferente y el valor en B$7 es diferente, siempre que ambas condiciones sean VERDADERAS aplicaremos la formula RESIDUO(FILA(),2)=1 caso contrario se pondrá un valor vacío ("").

10. RESIDUO(FILA(),2)=1: Aquí evaluamos si el residuo de la división de la FILA(), formula que regresará el número entero de la fila a evaluar dividido entre dos es igual a 1, entonces este resultado será IMPAR.

11. "": Sino se cumple la condición pondrá una cadena de texto en blanco y/o vacía, es decir, no pondrá formato alguno.

12. De forma similar haremos el Formato Condicional para las filas que sean PARES.

13. Usaremos la formula: SI(Y($B9<>"",B$7<>""),RESIDUO(FILA(),2)=0,"") que si observamos es similar a la anterior salvo por la comparación del residuo que en este caso es igual a cero, es decir si el valor de la fila actual es un número par, el residuo de esta retornará cero por lo que es un número par y aplica el formato de relleno gris claro con bordes naranjas, el usuario puede elegir el formato que mejor le convenga.

14. En la Celda A22 escribimos Total Meses:

15. En A23 escribirnos Total Recargos:

16. En B22 ponemos la formula: CONTAR.SI(Recargos_Bonus,">0").

-       La sintaxis de esta fórmula es: CONTAR.SI(rango, criterio)

-       rango: En este caso es el nombre definido Recargos_Bonus mismo que es dinámico.

-       critero: ">0" Es decir contará todo lo que sea mayor a cero, esto lo hacemos para efectos de contar cuantos meses se están considerando para el cálculo de recargos.

-       En B23 escribimos la formula: SUMAR.SI(Recargos_Bonus,">0")/100.

-       Su sintaxis es: SUMAR.SI(rango, criterio[rango_suma].

-       rango: Igual que en la formula anterior ponemos el nombre definido dinámico Recargos_Bonus.

-       critero: ">0" es decir, sumará todo lo que sea mayor que cero.

-       [rango_suma]: Esta parte es opcional, usualmente en Excel cuando una fórmula esta entre corchetes es un dato que puede ser prescindible. Se usa cuando tenemos un rango de datos, digamos A2:A50 y este rango de datos tiene las tres principales ciudades de México: GDL, CDMX y MTY y sus valores en B2:B50. Entonces deseamos sumar los valores de la columna be nuestro criterio sería por ejemplo "GDL" y el [rango_suma] B2:B50.

Finalmente, nuestra hoja dinámica de recargos se vería de la siguiente manera:

Ahora les voy a demostrar lo "Elástica" que puede ser esta fórmula y es que al ser un rango dinámico este es variable dependiendo de la fecha. Esta característica hace que nuestra hoja de cálculo sea extremadamente versátil y adaptable a diferentes periodos de tiempo.

1.    Vamos a la Hoja Actualizar y cambiemos la fecha a 30/09/2022, y después regresemos a la hoja Bonus Recargos. Veamos que pasa:

Actualizador.

Bonus Recargos.

2.    Ahora regresamos a Acutalizar y cambiemos a un rango de 10 Ejercicios para ver qué pasa, así que pongamos 25/07/2015, después regresemos a Bonus Recargos.

Actualizador

Bonus Recargos

 Interesante ¿No es así? Como se puede observar en el ejercicio, así como en las imágenes podemos ver el potencial que tenemos con estas funciones así como la comprobación de lo que les digo de la idea de "Elasticidad" que tiene un rango dinámico y su aplicación. Este concepto de elasticidad es fundamental en Excel avanzado, ya que nos permite crear hojas de cálculo que se adaptan automáticamente a nuestras necesidades cambiantes.

Así pues, siempre que tengamos actualizado nuestro libro en Excel solo basta cambiar la fecha y no solo se actualizan nuestros montos automáticamente, sino incluso nuestros rangos y no solo eso, el propio formato que le damos a nuestra tabla haciéndolo visualmente atractivo. Esta automatización nos ahorra tiempo valioso y reduce significativamente la posibilidad de errores humanos en nuestros cálculos.

Protegiendo la Hoja de Excel para evitar eliminación de fórmulas.

        Para permitir una mejor digestión del contenido de este extenso tutorial, este importante tópico lo abordaremos en nuestra próxima entrega, la cual podrás encontrar aquí: 📊 Protege tus Fórmulas de Excel: Guía Definitiva para Blindar tus Hojas de Cálculo

En dicha guía aprenderemos cómo proteger nuestras fórmulas y, por supuesto, salvaguardar nuestro trabajo de eliminaciones accidentales o modificaciones "provisionales" que pueden afectar significativamente el rendimiento y desempeño de nuestra hoja de cálculo.

Conclusiones.

         En este tutorial hemos profundizado en temas fundamentales de Excel, incluyendo:

• Rangos dinámicos y nombres definidos
• Formato condicional
• SUMAPRODUCTO y otras funcionalidades avanzadas

Además, exploramos fórmulas lógicas esenciales como:

• SI combinado con Y
• CONTAR.SI
• SUMAR.SI Y los impresionantes resultados que podemos obtener al combinar estratégicamente estas funciones.

Les he demostrado lo que considero la "Elasticidad" de un rango dinámico y la importancia crucial de realizar una configuración inicial sólida. Si bien esta configuración inicial requiere una inversión significativa de tiempo, posteriormente solo necesitará un mantenimiento mínimo para continuar funcionando eficientemente, minimizando nuestra intervención y reduciendo considerablemente los errores humanos.

Pero sobre todo, hemos destacado la importancia fundamental de:

• Mantenerse informado
• Cultivar la curiosidad
• Estar siempre dispuesto al aprendizaje Recordando siempre aquella máxima que nos dice que cada día podemos aprender algo nuevo, especialmente si nos lo proponemos.

Mi consejo final para todos los lectores es: mantén siempre viva tu curiosidad. Para poner en práctica todo lo aprendido, aquí encontrarás el Link directo de descarga del ejercicio. ¡Que lo disfrutes!

¿Te resultó útil este tutorial? No olvides compartirlo y dejar tus comentarios sobre qué otros temas de Excel te gustaría que exploráramos.