introducción a excel contable pro

Objetivo del módulo

Que conozcas la estructura completa del curso, entiendas el método PLANTILLA-FÓRMULA-AUTOMATIZA que usarás en cada lección, identifiques tu nivel actual de Excel y salgas con un plan claro de 30 días para transformar la forma en que haces reportes contables. Antes de tocar una sola fórmula, necesitas saber exactamente qué vas a aprender, en qué orden y cómo vas a medir tu progreso. Este módulo es ese mapa.

Lección 1: Bienvenida y recorrido del curso

Bienvenido a Excel Contable Pro. Soy el C.P. Ricardo Méndez y durante los últimos 12 años he trabajado en despachos contables en México, viendo de primera mano cómo contadores y auxiliares pierden tardes enteras en tareas que Excel puede resolver en minutos.

Este curso no es un curso genérico de Excel. Cada lección está diseñada alrededor de una situación contable real: conciliaciones bancarias, cálculo de depreciaciones, armado de nómina, reportes para el SAT. No vas a aprender Excel por aprender Excel; vas a aprender las funciones que de verdad necesitas en tu trabajo diario.

Qué vas a lograr

La promesa es concreta: que hagas en 20 minutos los reportes contables que hoy te toman una tarde entera. No es exageración. Cuando dominas tablas dinámicas, BUSCARX y automatización básica, lo que antes era copiar y pegar manualmente entre hojas se convierte en un par de clics.

Cómo está estructurado el curso

El curso tiene 6 módulos y 42 clases. Cada módulo construye sobre el anterior, así que te recomiendo seguir el orden:

• Módulo 1 — Introducción a Excel Contable Pro: el mapa, el método y tu plan de 30 días. (Aquí estás.)
• Módulo 2 — Configuración y preparación de Excel: deja tu Excel listo para trabajar a velocidad. Formato, atajos, estructura de hojas.
• Módulo 3 — Métodos de automatización y optimización: tablas dinámicas, BUSCARX, validación de datos, formato condicional y automatización de tareas repetitivas.
• Módulo 4 — Aplicación práctica de las herramientas de Excel: conciliaciones bancarias, depreciaciones, nómina y reportes para el SAT, todo con plantillas reales.

Cada clase termina con una plantilla descargable que puedes llevar directamente a tu despacho. No son ejercicios teóricos: son archivos que ya funcionan.

Qué necesitas

• Una computadora con Excel 2019 o Microsoft 365 (necesitas BUSCARX, que no existe en versiones anteriores).
• Conexión a internet para ver las lecciones y descargar las plantillas.
• Ganas de practicar. El curso es compacto, pero Excel se aprende haciendo, no viendo.

Acceso de por vida y soporte

Tu acceso no expira. Puedes volver a cualquier lección cuando quieras. Además, tienes acceso al grupo de soporte en Telegram donde puedes preguntar dudas y compartir tus avances. Úsalo: las preguntas que no haces son las que más te frenan.

Certificado de finalización

Al terminar todas las lecciones recibirás un certificado de finalización del curso. Para obtenerlo, completa cada lección y marca el checkbox de finalización al final de cada una.

Lección 2: El método PLANTILLA-FÓRMULA-AUTOMATIZA

Este es el corazón del curso. Cada lección sigue el mismo método de tres pasos. Si entiendes la lógica desde ahora, cada clase te resultará familiar y sabrás exactamente qué esperar.

Paso 1: Plantilla

Cada lección empieza con una plantilla real. No con una hoja en blanco, no con un ejemplo inventado. Con un archivo que ya tiene estructura: columnas con encabezados contables, formatos listos, datos de ejemplo que representan una situación real de un despacho mexicano.

¿Por qué empezar con la plantilla y no con la teoría? Porque como contador tu trabajo no es inventar hojas de cálculo desde cero: es resolver problemas con la información que ya tienes. Cuando ves la plantilla primero, entiendes de inmediato qué problema vamos a resolver y cómo se ve el resultado final.

Ejemplo: En la lección de conciliación bancaria, la plantilla ya trae dos hojas: una con el estado de cuenta del banco y otra con la contabilidad interna. Tu trabajo será conectarlas. No vas a diseñar nada desde cero: vas a aprender a hacer que Excel haga el trabajo pesado.

Paso 2: Fórmula

Una vez que ves la plantilla y entiendes el problema, aprendes la fórmula o herramienta que lo resuelve. Aquí es donde explico paso a paso cómo funciona cada función, qué argumento hace qué y qué errores comunes debes evitar.

No te lanzo diez funciones de golpe. Te enseño una fórmula a la vez, en el contexto de la plantilla. Así no memorizas sintaxis: entiendes para qué sirve cada función y cuándo usarla.

Ejemplo: En la misma conciliación bancaria, la fórmula clave es BUSCARX. Te muestro cómo buscar cada movimiento del banco en tu contabilidad interna, cómo manejar los que no encuentran coincidencia (que son exactamente los que debes investigar) y cómo marcarlos automáticamente.

Paso 3: Automatiza

El último paso es donde está el ahorro real de tiempo. Tomamos la fórmula que acabas de aprender y la convertimos en algo que se actualiza solo. Aquí entra todo: tablas dinámicas que se refrescan con un clic, formato condicional que resalta errores automáticamente, validación de datos que evita que alguien capture mal una cuenta contable.

El objetivo de este paso es que la próxima vez que tengas el mismo reporte, no repitas el proceso manual: solo pegues los datos nuevos y Excel haga el resto.

Ejemplo: En la conciliación, la automatización significa que el próximo mes solo pegas el estado de cuenta nuevo en la hoja del banco y los movimientos nuevos de tu contabilidad, y la plantilla te marca automáticamente qué partidas no concilian. Lo que te tomaba dos horas ahora te toma quince minutos.

Cómo aplicar el método en cada lección

Cuando abras cualquier lección del curso, vas a ver esta estructura:

1. Descarga la plantilla.
2. Sigue la explicación de la fórmula o herramienta.
3. Aplica la automatización en la misma plantilla.
4. Guarda el archivo como tu versión de trabajo.

Al final del curso tendrás un portafolio de plantillas listas para usar en tu despacho. Ese es el verdadero valor: no el conocimiento teórico, sino los archivos que ya funcionan.

Lección 3: Diagnóstico — ¿en qué nivel de Excel estás hoy?

Antes de empezar a aprender, necesitas saber dónde estás. Este diagnóstico rápido te ayuda a identificar qué funciones ya dominas y cuáles necesitas reforzar. No es un examen: es una herramienta para que enfoques tu energía donde más te falta.

Las 10 preguntas del diagnóstico

Responde cada pregunta con «sí» o «no» mentalmente. Sé honesto: nadie te está calificando.

1. ¿Sabes usar BUSCARV (VLOOKUP) sin dudar sobre el orden de los argumentos?
2. ¿Has usado alguna vez una tabla dinámica para resumir datos contables?
3. ¿Sabes qué es una tabla de Excel (no un rango normal, sino una tabla con formato de tabla)?
4. ¿Usas formato condicional para resaltar celdas que cumplen una condición?
5. ¿Sabes anidar funciones, por ejemplo SI con BUSCARV?
6. ¿Has usado SUMAR.SI.CONJUNTO para sumar con varios criterios (por ejemplo, por cuenta y por mes)?
7. ¿Sabes proteger celdas específicas en una hoja para que nadie modifique fórmulas?
8. ¿Has usado validación de datos para crear listas desplegables?
9. ¿Conoces BUSCARX y sabes por qué es mejor que BUSCARV?
10. ¿Sabes importar datos de un archivo CSV o de texto a Excel sin que se rompan los formatos?

Cómo interpretar tu resultado

De 0 a 3 respuestas «sí»: nivel principiante. Estás en el lugar correcto. Este curso está diseñado para ti. Empieza desde el módulo 2 sin saltarte nada. Vas a ver un cambio enorme en las primeras dos semanas.

De 4 a 7 respuestas «sí»: nivel intermedio. Ya tienes base, pero te faltan las herramientas que de verdad automatizan. Presta especial atención al módulo 3 (automatización) y al módulo 4 (aplicación práctica). Es probable que el módulo 2 lo veas rápido, pero no lo saltes: hay detalles de configuración que te van a ahorrar tiempo.

De 8 a 10 respuestas «sí»: nivel avanzado. Ya dominas Excel razonablemente bien. Tu valor en este curso está en las plantillas contables específicas y en el método. Ve las lecciones que te falten y aprovecha las plantillas del módulo 4, que son las más densas. El grupo de Telegram te sirve para resolver casos específicos.

Anota tu resultado

Escribe tu número de respuestas «sí» en el workbook de este módulo. Al final del curso vas a repetir este diagnóstico y ver cuánto avanzaste. Es la forma más clara de medir tu progreso real.

Lección 4: Tu plan de 30 días para dominar Excel contable

El curso es compacto, pero compacto no significa que lo veas todo en una tarde. Excel se aprende practicando, y practicar requiere repetición espaciada. Este plan de 30 días está diseñado para que avances sin abrumarte y, sobre todo, para que apliques lo que aprendes en tu trabajo real.

Principios del plan

• Una lección por día, de lunes a viernes. Los fines de semana son para repasar y practicar con tus propios datos.
• Cada lección dura entre 15 y 25 minutos. No necesitas bloquear toda la tarde.
• Descarga la plantilla antes de ver la lección. Tenla abierta en Excel mientras ves la explicación.
• Aplica lo aprendido en un caso real esa misma semana. Si aprendes BUSCARX el lunes, el martes úsala en tu trabajo con datos reales.

Semana 1: Fundamentos y configuración

Semana 2: Automatización

Semana 3: Aplicación contable

Semana 4: Consolidación

Qué hacer si te atrasas

La vida pasa. Si te saltas un día, no intentes ver dos lecciones al día siguiente. Simplemente retoma donde quedaste. El acceso es de por vida: no hay prisa. Lo que no funciona es acumular lecciones sin practicar, porque Excel no se aprende viendo: se aprende haciendo.

Qué hacer si ya dominas un tema

Si llegas a una lección y ya sabes hacer lo que enseña, descarga la plantilla de todos modos. Revisa si hay algún detalle que no conocías. Si no hay nada nuevo, pasa a la siguiente. Pero no saltes lecciones sin abrir la plantilla: muchas veces el valor está en el archivo, no en el video.

Workbook del módulo 1

Tarea 1: Registro de tu punto de partida

Antes de avanzar al módulo 2, completa este registro. Guárdalo en tu computadora o anótalo donde quieras. La idea es que tengas un punto de comparación al final del curso.

Preguntas:

introducción a excel contable pro (2)

1. ¿Cuánto tiempo dedicas hoy a tareas repetitivas en Excel? (estima en horas por semana)
2. ¿Qué tres tareas contables te parecen más tediosas o lentas hoy?
3. Tarea 1:
4. Tarea 2:
5. Tarea 3:
6. Resultado del diagnóstico de la lección 3 (número de respuestas «sí» sobre 10):
7. ¿Qué esperas haber logrado al terminar los 30 días? Escribe una expectativa concreta, no una generalidad. Por ejemplo: «Quiero automatizar la conciliación bancaria de mis 4 clientes principales» en lugar de «Quiero ser mejor en Excel».

Tarea 2: Preparación de tu entorno de trabajo

1. Verifica que tienes Excel 2019 o Microsoft 365 instalado.
2. Crea una carpeta en tu computadora llamada «Excel Contable Pro».
3. Dentro de esa carpeta, crea tres subcarpetas:
4. «Plantillas del curso» (aquí guardarás las plantillas que descargues)
5. «Mi trabajo» (aquí pondrás las versiones adaptadas a tu despacho)
6. «Certificado» (aquí guardarás tu certificado al finalizar)
7. Únete al grupo de soporte en Telegram con el enlace que recibiste al comprar el curso.

Tarea 3: Compromiso de práctica

Escribe esta frase con tu nombre y la fecha de hoy. No es un contrato legal: es un compromiso contigo mismo.

Yo, [tu nombre], me comprometo a dedicar de 15 a 25 minutos diarios, de lunes a viernes, durante 30 días, a completar las lecciones de Excel Contable Pro y a practicar cada herramienta con datos reales de mi trabajo.

Fecha: [fecha de hoy]

Plantilla de seguimiento de progreso

Usa esta tabla para marcar cada lección conforme la completes. Imprímela o mantenla en un archivo de Excel.

Mantén esta tabla visible. Ver los checkboxes marcados es una de las formas más efectivas de mantener el impulso durante los 30 días.

configuración y preparación de excel

Objetivo del módulo

Antes de automatizar, necesitas un terreno firme. En este módulo vas a transformar tu Excel genérico en un entorno de trabajo contable real. El objetivo es que cada vez que abras un libro, tengas los formatos, la estructura y las protecciones listas para que no pierdas ni un minuto peleando con celdas desalineadas o fórmulas borradas por accidente. Aquí sentamos las bases del método PLANTILLA-FÓRMULA-AUTOMATIZA.

Lección 1: Configura tu entorno de trabajo

La barra de herramientas superior está diseñada para usuarios genéricos, no para contadores. Si pierdes cinco segundos en buscar el botón de bordes o de formato de moneda, en un mes pierdes horas.

Tu primera tarea es personalizar la barra de acceso rápido.

1. Haz clic en la flecha que apunta hacia abajo en la esquina superior izquierda de tu pantalla.
2. Selecciona "Más comandos".
3. Agrega a tu barra: "Formato de número contable", "Aumentar decimales", "Disminuir decimales", "Bordes" y "Color de relleno".

Con estos cinco botones a la vista, el 80% del formato visual de tus reportes se resuelve con un clic. No necesitas ir a la pestaña de inicio cada vez que necesitas poner un borde a una celda.

Paso accionable: Abre Excel ahora mismo y configura tu barra de acceso rápido. Si usas una versión en español, busca exactamente esos nombres. Esta será tu base de operaciones durante los próximos 30 días.

Lección 2: Formato contable y moneda nacional

Un número rojo sin paréntesis en una hoja de cálculo es una pesadilla visual para cualquier contador. En México, necesitamos que los números negativos se vean en rojo y entre paréntesis, y que los miles estén separados por comas.

En lugar de usar el formato de moneda genérico (que a veces pone el signo de pesos pegado al número), vamos a crear un formato contable limpio.

1. Selecciona las celdas con tus montos.
2. Presiona Ctrl + 1 para abrir el menú de formato de celdas.
3. Ve a la pestaña "Número" y selecciona "Personalizada".
4. En el campo "Tipo", pega este código: _-[$$-es-MX] #,##0.00_-;-[$$-es-MX] #,##0.00_-;_-[$$-es-MX]* "-"??_-;_-@_-

Este código alinea todos los signos de pesos a la izquierda y los números a la derecha. Los negativos saldrán en rojo y con el signo de menos, perfectos para identificar abonos o cargos al instante.

Ejemplo práctico: Si escribes 1500, verás $ 1,500.00. Si escribes -1500, verás $ -1,500.00. Todo alineado en la misma columna.

Lección 3: Estructura de tu libro contable

Un libro de Excel con 15 pestañas llamadas "Hoja1", "Hoja2" y "Bancos_final_v2" es un libro muerto. La estructura debe imitar un sistema contable real.

Nombra tus pestañas con un prefijo numérico para que siempre estén ordenadas:

• 01_Catalogo
• 02_Polizas
• 03_Bancos
• 04_SAT

Asigna colores a las pestañas por categoría. Las pestañas de entrada de datos (como 02_Polizas) en azul. Las pestañas de reportes y cálculos (como 04_SAT) en verde. Las pestañas de configuración o catálogos en gris.

Además, congela los paneles. En tu hoja de pólizas, selecciona la fila 4 (donde terminan tus encabezados de Fecha, Cuenta, Debe, Haber), ve a la pestaña "Ver" y haz clic en "Inmovilizar paneles". Ahora, aunque bajes hasta la fila 500, los encabezados siempre serán visibles.

Lección 4: Protege tus fórmulas

El error más caro en un despacho contable es borrar una fórmula al escribir encima de ella. Por defecto, todas las celdas en Excel vienen "bloqueadas", pero este bloqueo no funciona hasta que proteges la hoja.

Vamos a proteger tu hoja de cálculo de saldos, dejando solo las celdas de entrada de datos libres.

1. Selecciona toda la hoja (clic en el triángulo superior izquierdo, entre la columna A y la fila 1).
2. Presiona Ctrl + 1, ve a la pestaña "Proteger" y desmarca la casilla "Bloqueada". Ahora nada está bloqueado.
3. Selecciona solo las celdas que tienen fórmulas (por ejemplo, la columna de Saldo).
4. Vuelve a Ctrl + 1, pestaña "Proteger" y marca "Bloqueada".
5. Ve a la pestaña "Revisar" y haz clic en "Proteger hoja". Deja la contraseña en blanco si quieres, o pon una que recuerdes.

Ahora, si intentas escribir en la celda del saldo, Excel te lanzará un error. Solo podrás capturar en las celdas de datos.

Lección 5: Validación de datos para evitar errores

Un reporte para el SAT no admite errores de captura. Si tu catálogo de cuentas tiene "Bancos", "Bancos " (con un espacio al final) y "Bancos1", tus fórmulas de suma van a fallar.

La validación de datos soluciona esto creando listas desplegables.

1. En una hoja nueva (llámala 00_Config), escribe tus métodos de pago en una columna: Efectivo, Transferencia, Tarjeta, Cheque.
2. Ve a tu hoja de pólizas y selecciona la columna de Método de Pago.
3. Ve a la pestaña "Datos" y haz clic en "Validación de datos".
4. En "Permitir", elige "Lista".
5. En "Origen", selecciona el rango de tu hoja 00_Config.

A partir de hoy, nadie podrá escribir "Tarjta" en esa columna. Solo podrán elegir de la lista. Esto garantiza que cuando filtres o uses BUSCARX más adelante, los datos coincidan al 100%.

Workbook del módulo 2: Tareas y plantillas

Es momento de aplicar lo que acabas de leer. Descarga la plantilla base de este módulo y realiza las siguientes tareas. No avances al siguiente módulo hasta que tu libro de trabajo esté completamente configurado.

Tareas del módulo

1. Personaliza tu barra: Abre un libro en blanco y configura tu barra de acceso rápido con los comandos de formato contable.
2. Aplica el formato de moneda: En la hoja "Mayor" de tu plantilla, aplica el formato personalizado de pesos mexicanos a las columnas de Debe, Haber y Saldo.
3. Estructura y colorea: Renombra las pestañas de la plantilla usando el formato numérico (01_Catalogo, 02_Mayor) y asigna colores según el tipo de hoja.
4. Protege fórmulas: Desbloquea todas las celdas de la hoja 02_Mayor, bloquea únicamente la columna de Saldo (que contiene la fórmula =Debe-Haber) y protege la hoja.
5. Crea tu primer desplegable: En la hoja 01_Catalogo, crea una lista de tipos de cuenta (Activo, Pasivo, Capital, Ingreso, Egreso) y pon un desplegable en la columna de clasificación.

Plantilla descargable: Libro contable base

Esta plantilla incluye la estructura básica de pestañas y las columnas ya dimensionadas. Tu trabajo es aplicar el formato, la protección y la validación de datos según las tareas anteriores. Al terminar, guárdala como "Plantilla Maestra.xlsx". Esta será la base sobre la que construyamos las automatizaciones en el siguiente módulo.

métodos de automatización y optimización

Objetivo del módulo

En este módulo vas a dejar atrás el Excel manual y entrar al Excel que trabaja por ti. Aprenderás los tres métodos de automatización que uso en mi despacho para que una tarea de tres horas se resuelva en tres minutos: fórmulas inteligentes, tablas dinámicas y macros grabadas. Cada lección termina con una plantilla que ya puedes abrir y usar en tu contabilidad real. No teoría de relleno: fórmulas, atajos y procedimientos que aplicas hoy mismo.

Lección 1: El método PLANTILLA-FÓRMULA-AUTOMATIZA

Antes de tocar una sola celda, necesitas entender cómo vamos a trabajar el resto del curso. Todo lo que construyas seguirá el mismo patrón de tres pasos, y si lo respetas, nunca más vas a empezar un reporte desde cero.

Paso 1: Plantilla. Toma la plantilla base que descargaste en el módulo anterior. Esa es tu punto de partida, no un libro en blanco. La plantilla ya tiene las pestañas, las columnas y el formato. Tu trabajo es llenarla de lógica, no de diseño.

Paso 2: Fórmula. En cada caso contable hay una fórmula que resuelve el problema. Conciliación bancaria: SUMAR.SI.CONJUNTO. Búsqueda de cuenta: BUSCARX. Cálculo de depreciación: TASA.NOMINAL o una fórmula anidada. La fórmula es el motor; la plantilla es el chasis.

Paso 3: Automatiza. Cuando la fórmula funciona, la conviertes en algo que se ejecuta solo: una tabla dinámica que se actualiza con un clic, una macro que ordena y pega, un formato condicional que pinta en rojo lo que no cuadra. Aquí pasas de "hacer el reporte" a "que el reporte se haga".

Ejemplo real de mi despacho: cada mes conciliábamos 14 cuentas bancarias. El proceso era abrir cada estado de cuenta, pegarlo en Excel, buscar diferencias manualmente. Tardábamos una tarde entera. Con el método: pegamos los 14 estados en una pestaña, una fórmula SUMAR.SI.CONJUNTO cruza depósitos contra el libro contable, un formato condicional marca las diferencias, y una macro imprime el resumen. Tiempo total: 18 minutos.

Lo que no debes hacer: abrir un Excel nuevo cada vez, copiar y pegar fórmulas a mano celda por celda, o "recordar" cómo hiciste el reporte el mes pasado. Si haces eso, no estás automatizando: estás repitiendo.

Lección 2: Fórmulas que reemplazan horas de trabajo manual

Aquí están las cinco fórmulas que más horas nos han ahorrado en el despacho. No son las más vistosas, pero son las que usamos todos los días.

SUMAR.SI.CONJUNTO

Suma valores que cumplen varias condiciones a la vez. Es la fórmula reina de la contabilidad.

=SUMAR.SI.CONJUNTO(rango_suma; rango_criterios1; criterio1; rango_criterios2; criterio2)

Ejemplo: quieres saber cuánto se pagó a un proveedor en un mes específico.

• Rango suma: columna "Importe" del libro contable
• Rango criterios 1: columna "Proveedor"
• Criterio 1: "Proveedor X"
• Rango criterios 2: columna "Fecha"
• Criterio 2: ">=01/03/2024" y otro con "<=31/03/2024"

La fórmula te da el total sin filtrar nada, sin copiar a otra hoja, sin calculadora.

BUSCARX

Busca un valor en una columna y te regresa el resultado de otra. Es la versión moderna de BUSCARV, pero sin sus limitaciones: busca a la derecha o a la izquierda, no necesita que la columna esté ordenada, y si no encuentra el dato, puedes poner un mensaje personalizado en lugar de error.

=BUSCARX(valor_buscado; rango_busqueda; rango_resultado; "No encontrado")

Ejemplo: tienes el número de cuenta en la columna B y necesitas el nombre de la cuenta que está en la columna A. Con BUSCARV era imposible porque busca solo hacia la derecha. Con BUSCARX lo resuelves en segundos.

SI anidado con SI.ERROR

Toma decisiones según condiciones y, si algo falla, no te muestra un #N/A feo sino un texto limpio.

=SI.ERROR(SI(B2>10000;"Revisar";"OK");"Falta dato")

Ejemplo: si una factura es mayor a 10,000, marca "Revisar" para autorización. Si la celda está vacía o hay un error, muestra "Falta dato" en lugar de #N/A.

TEXTO

Convierte fechas y números al formato que necesitas para reportes del SAT o presentaciones a clientes.

=TEXTO(A2;"dd/mm/yyyy")
=TEXTO(B2;"$#,##0.00")

Ejemplo: el SAT requiere fechas en formato dd/mm/yyyy. Si tu sistema exporta fechas como números seriales, esta fórmula las convierte al formato correcto en un segundo.

DIAS.LAB

Cuenta los días hábiles entre dos fechas, excluyendo fines de semana y días festivos.

=DIAS.LAB(fecha_inicio; fecha_fin; rango_festivos)

Ejemplo: calcula los días hábiles que tardó un cliente en pagar una factura. Le pasas un rango con los días festivos del año y la fórmula los excluye automáticamente.

Lección 3: Tablas dinámicas para reportes contables en minutos

Una tabla dinámica es el reporte contable más rápido que existe en Excel. Le das los datos crudos y ella los agrupa, los suma y los presenta como un estado de resultados o un balance. Sin fórmulas, sin copiar y pegar, sin formato manual.

Cómo construir un estado de resultados en 5 pasos

1. Convierte tus datos en tabla. Selecciona todo el rango de tu libro contable y presiona Ctrl+T. Marca "La tabla tiene encabezados". Ahora tus datos son una tabla de Excel: se expande sola cuando agregas filas y los nombres de columna son referencias legibles.

1. Inserta la tabla dinámica. Ve a Insertar > Tabla dinámica. Asegúrate de que el rango sea el de tu tabla (no el rango fijo, sino el nombre de la tabla). Colócala en una hoja nueva.

1. Arrastra los campos. Para un estado de resultados:
2. Filas: "Cuenta contable" (o "Tipo de cuenta")
3. Valores: "Importe" (cambia de "Cuenta" a "Suma")
4. Columnas: "Mes" (si quieres ver varios meses lado a lado)

1. Agrupa por nivel. Si tus cuentas contables tienen jerarquía (por ejemplo, 1000 Activos, 1100 Circulante, 1110 Bancos), arrastra los tres campos a Filas en orden. La tabla dinámica los anida automáticamente y puedes contraer o expandir cada nivel.

1. Aplica formato. Clic derecho en cualquier número > Formato de celdas > Número > Separador de miles, dos decimales, negativos en rojo. Listo: tienes un estado de resultados que se actualiza con un clic derecho > Actualizar cada vez que capturas nuevos movimientos.

Truco que nadie enseña: segmentación por periodo

Arrastra el campo "Fecha" a Filtros y la tabla te deja seleccionar un mes específico. Pero mejor aún: arrastra "Fecha" a Filas, clic derecho sobre cualquier fecha > Agrupar > selecciona "Meses" y "Años". Ahora puedes ver enero, febrero y marzo de cada año en columnas separadas, todo en la misma tabla.

Lo que no debes hacer

• No dejes los datos como rango normal. Si no los conviertes en tabla (Ctrl+T), cuando agregues filas nuevas la tabla dinámica no las va a incluir y tus reportes estarán incompletos.
• No olvides Actualizar. Cada vez que captures movimientos nuevos, clic derecho en la tabla dinámica > Actualizar. Si no lo haces, estás viendo datos viejos.

Lección 4: Formato condicional para detectar errores automáticamente

El formato condicional pinta las celdas según reglas que tú defines. En contabilidad es tu mejor aliado para detectar diferencias, facturas faltantes y errores de captura sin revisar celda por celda.

Regla 1: Diferencias en conciliación bancaria

Selecciona la columna de diferencias (importe en libro contable menos importe en estado de cuenta).

Inicio > Formato condicional > Reglas para resaltar celdas > Es mayor que > escribe 0 > formato: relleno rojo.

Ahora cualquier diferencia mayor a cero se pinta roja automáticamente. Si también quieres ver las negativas, agrega otra regla: Es menor que > 0 > relleno naranja.

Regla 2: Facturas duplicadas

Selecciona la columna de folios o números de factura.

Inicio > Formato condicional > Reglas para resaltar celdas > Valores duplicados > formato: relleno amarillo.

Si capturaste una factura dos veces, Excel la pinta amarilla al instante. Esto nos ha salvado de declaraciones incorrectas más veces de las que puedo contar.

Regla 3: Fechas vencidas

Selecciona la columna de fechas de vencimiento.

Inicio > Formato condicional > Nueva regla > Utilice una fórmula > escribe:

=Y(A2<>""; A2<HOY())

Formato: relleno rojo, letra blanca.

Ahora toda factura vencida se pinta roja sola. Si la fecha está vacía, no la pinta (por eso el A2<>"" en la fórmula).

Regla 4: Escalas de color para identificar montos

Selecciona la columna de importes.

Inicio > Formato condicional > Escalas de color > elige verde-amarillo-rojo.

Los montos grandes se pintan de un color y los pequeños de otro. A simple vista ves dónde está el dinero sin ordenar ni filtrar nada.

Lección 5: Macros grabadas para tareas repetitivas

Una macro es una grabación de tus pasos en Excel. La grabas una vez y la reproduces con un clic las veces que quieras. No necesitas saber programar: Excel graba lo que haces y lo repite.

Cómo activar la pestaña Programador

Archivo > Opciones > Personalizar cinta de opciones > marca "Programador" > Aceptar. Ahora verás la pestaña Programador en tu barra de herramientas.

Tu primera macro: ordenar y dar formato al libro contable

1. Ve a Programador > Grabar macro.
2. Nómbrala "OrdenarLibro". Guárdala en "Este libro".
3. A partir de aquí, Excel está grabando todo lo que haces:
4. Selecciona toda la tabla (Ctrl+T si no es tabla todavía).
5. Datos > Ordenar: por Fecha, de antiguo a nuevo.
6. Aplica formato de número a la columna Importe: separador de miles, dos decimales.
7. Pon encabezados en negrita con fondo gris claro.
8. Programador > Detener grabación.

Ahora cada mes, cuando pegues los movimientos nuevos, vas a Programador > Macros > seleccionas "OrdenarLibro" > Ejecutar. En dos segundos hace todo lo que tardabas cinco minutos.

Cómo crear un botón para ejecutar la macro

Programador > Insertar > Botón (control de formulario) > dibuja el botón en la hoja > asígnale la macro "OrdenarLibro" > Aceptar.

Ahora tienes un botón que dice "Ordenar" y con un clic ejecuta toda la secuencia. Puedes cambiarle el texto al botón: clic derecho > Modificar texto.

Lo que debes saber

• Las macros grabadas repiten exactamente lo que hiciste. Si grabaste ordenando por la columna B y el mes que viene los datos están en la columna C, la macro ordenará la columna equivocada. Por eso es clave trabajar siempre sobre la misma plantilla.
• Guarda el archivo como "Libro habilitado para macros (.xlsm)". Si lo guardas como .xlsx, la macro se borra.
• Las macros grabadas no son inteligentes: hacen lo que grabaste, ni más ni menos. Para lógica condicional necesitas editar el código, pero eso es tema del módulo siguiente.

Lección 6: Validación de datos para evitar errores de captura

La validación de datos restringe lo que se puede escribir en una celda. Si alguien intenta capturar una fecha en la columna de importe, o escribir un nombre de cuenta que no existe, Excel no lo permite. Esto previene errores antes de que lleguen a tus reportes.

Lista desplegable de cuentas contables

1. En una hoja aparte (llámala "Catálogo"), escribe todas tus cuentas contables en una columna.
2. Selecciona esa columna y nómbrala: clic en el cuadro de nombres (arriba a la izquierda) y escribe "Cuentas". Enter.
3. Ve a la hoja del libro contable, selecciona la columna "Cuenta".
4. Datos > Validación de datos > Permitir: Lista > Origen: =Cuentas.
5. Aceptar.

Ahora la columna Cuenta tiene una flecha desplegable con todas las cuentas del catálogo. No se puede escribir una cuenta que no exista. Si intentas escribir "Bancos2" y no está en el catálogo, Excel muestra un error.

Validación de fechas

Selecciona la columna "Fecha" > Datos > Validación de datos > Permitir: Fecha > Datos: mayor o igual que > Fecha inicial: 01/01/2024.

Nadie puede capturar una fecha anterior al 1 de enero de 2024. Si alguien escribe 15/06/2023 por error, Excel lo bloquea.

Validación de importes positivos

métodos de automatización y optimización (2)

Selecciona la columna "Importe" > Datos > Validación de datos > Permitir: Decimal > Datos: mayor que > Valor mínimo: 0.

No se aceptan importes negativos en esta columna. Si necesitas registrar un abono, lo capturas como positivo y la columna "Tipo" (Cargo/Abono) define el signo.

Mensaje personalizado de error

En la pestaña "Mensaje de error" de la validación, escribe: "Esta cuenta no existe en el catálogo. Verifica el número o agrégala al catálogo antes de continuar." Así, cuando alguien cometa un error, sabe qué hacer en lugar de quedarse bloqueado.

Lección 7: Protección de celdas y hojas en plantillas contables

Cuando compartes una plantilla con tu equipo o con un cliente, necesitas proteger las fórmulas y la estructura para que nadie las borre o modifique por accidente. Aquí está el procedimiento completo.

Paso 1: Desbloquear celdas de captura

Por defecto, todas las celdas de Excel están bloqueadas. Pero el bloqueo solo funciona cuando proteges la hoja. Así que primero desbloquea lo que sí se debe poder editar.

1. Selecciona las celdas de captura (las columnas de Fecha, Cuenta, Importe, Concepto).
2. Clic derecho > Formato de celdas > pestaña Proteger > desmarca "Bloqueada".
3. Aceptar.

Paso 2: Ocultar fórmulas sensibles

1. Selecciona las celdas con fórmulas (las de SUMAR.SI.CONJUNTO, BUSCARX, etc.).
2. Clic derecho > Formato de celdas > pestaña Proteger > marca "Oculta".
3. Aceptar.

Cuando protejas la hoja, estas fórmulas no se verán en la barra de fórmulas. El resultado sí se ve, pero la fórmula no.

Paso 3: Proteger la hoja

Revisar > Proteger hoja > deja marcadas solo "Seleccionar celdas desbloqueadas" > escribe una contraseña > Aceptar.

Ahora solo se pueden editar las celdas que desbloqueaste. Las fórmulas están protegidas y ocultas. La estructura de la hoja no se puede modificar.

Paso 4: Proteger la estructura del libro

Revisar > Proteger libro > marca "Estructura" > contraseña > Aceptar.

Ahora no se pueden agregar, eliminar ni mover pestañas. Si alguien intenta borrar la hoja "Catálogo", Excel no lo permite.

Contraseña: recomendación práctica

Usa una contraseña que recuerdes y anótala en un lugar seguro. Si la pierdes, no hay forma de recuperar el acceso a las fórmulas. En mi despacho usamos la misma contraseña para todas las plantillas internas y una diferente para las que enviamos a clientes.

Workbook del módulo: Métodos de automatización y optimización

Tarea 1: Construye tu primer reporte dinámico

Abre la plantilla "Libro contable base" del módulo anterior. Convierte los datos en tabla con Ctrl+T. Inserta una tabla dinámica que muestre:

• Filas: Tipo de cuenta (Ingresos, Egresos, Activos, Pasivos)
• Valores: Suma de Importe
• Columnas: Mes

Guarda el archivo como "Estado de resultados dinámico.xlsx". Envía una captura de pantalla al grupo de Telegram con el hashtag #ReporteDinámico.

Tarea 2: Aplica formato condicional para conciliación

En la hoja "Conciliación" de tu plantilla:

1. Crea una columna "Diferencia" que reste el importe del estado de cuenta menos el importe del libro contable.
2. Aplica formato condicional: diferencias mayores a 0 en rojo, menores a 0 en naranja.
3. Aplica una segunda regla: valores duplicados en la columna "Folio" en amarillo.

Tarea 3: Graba tu primera macro

Graba una macro llamada "FormatoMensual" que haga lo siguiente:

1. Ordene los datos por fecha de antiguo a nuevo.
2. Aplique formato de número con separador de miles y dos decimales a la columna Importe.
3. Ponga los encabezados en negrita.
4. Ajuste el ancho de todas las columnas al contenido.

Crea un botón en la hoja y asígnale la macro. Guarda el archivo como .xlsm.

Tarea 4: Configura validación de datos

1. Crea una hoja "Catálogo" con tus cuentas contables (mínimo 20 cuentas).
2. Nombra el rango como "Cuentas".
3. En la hoja del libro contable, aplica validación de lista a la columna "Cuenta" usando =Cuentas.
4. Aplica validación de fecha a la columna "Fecha" (mayor o igual a 01/01/2024).
5. Aplica validación decimal a la columna "Importe" (mayor que 0).
6. Personaliza los tres mensajes de error con instrucciones claras.

Tarea 5: Protege tu plantilla

1. Desbloquea las columnas de captura: Fecha, Cuenta, Importe, Concepto.
2. Oculta las fórmulas de las columnas calculadas.
3. Protege la hoja con contraseña.
4. Protege la estructura del libro.
5. Guarda como "Plantilla Maestra Protegida.xlsm".

Plantilla descargable: Plantilla de conciliación automática

Esta plantilla incluye:

• Hoja "Estado de cuenta": donde pegas el archivo del banco.
• Hoja "Libro contable": con tus polizas ya capturadas.
• Hoja "Conciliación": con fórmulas SUMAR.SI.CONJUNTO que cruzan los depósitos y retiros entre las dos hojas, y formato condicional que marca las diferencias en rojo y naranja.
• Hoja "Catálogo": con la lista de cuentas y validación de datos ya configurada.
• Macro "EjecutarConciliacion": ordena, cruza y genera el resumen con un clic.

Tu trabajo es revisar cada fórmula, entender qué hace y adaptar los nombres de las columnas a los que usa tu despacho. Al terminar, tendrás una herramienta que reduce la conciliación bancaria de tres horas a menos de veinte minutos. Esta es la base sobre la que construiremos los reportes para el SAT en el siguiente módulo.

aplicación práctica de las herramientas de excel

Objetivo del módulo

En este módulo vas a construir, lección por lección, los reportes y herramientas que un contador usa cada mes: la nómina con todas las incidencias, el cálculo de depreciaciones, los reportes para el SAT (incluyendo la DIOT), tablas dinámicas para presentar la información a tu jefe o cliente, y el cierre mensual con BUSCARX cruzando catálogos. Cada lección termina con una plantilla que puedes abrir hoy mismo en tu despacho y empezar a usar. Al terminar el módulo tendrás un sistema completo: entras los datos, presionas un botón y obtienes los reportes listos para entregar.

Lección 1: Cálculo de nómina con incidencias

El problema que resolvemos

La nómina manual mata horas. Calculas el sueldo base, le restas faltas, le sumas tiempo extra, calculas el ISR según la tarifa del Artículo 96, el IMSS con los topes del SBC, y al final armas el recibo. Si tienes 15 empleados, te vas dos tardes. Si un empleado tiene tres incidencias distintas (falta, incapacidad y horas extra), el cálculo se vuelve un dolor de cabeza.

Aquí vas a construir una plantilla de nómina que calcula todo automáticamente a partir de tres datos: el sueldo mensual, los días laborados y las horas extra.

Estructura de la plantilla

Abre un libro nuevo y crea tres hojas: Catálogos, Nómina y Recibos.

En la hoja Catálogos vas a pegar tres tablas:

Tabla 1: Tarifa ISR mensual 2024 (Artículo 96 LISR)

Tabla 2: Topes IMSS (simplificado)

Tabla 3: Catálogo de empleados

Fórmulas de la hoja Nómina

En la hoja Nómina vas a tener una fila por empleado y estas columnas:

Columna A — ID empleado: lo escribes tú.

Columna B — Nombre: usa BUSCARX para traerlo del catálogo.

=BUSCARX(A2, Catálogos!A:A, Catálogos!B:B)

Columna C — Sueldo mensual: también con BUSCARX.

=BUSCARX(A2, Catálogos!A:A, Catálogos!F:F)

Columna D — Días del periodo: escribes 30 (o 15 si es quincenal).

Columna E — Días laborados: escribes los días que sí trabajó. Si faltó 2 días, pones 28.

Columna F — Sueldo devengado:

=SI(E2=0, 0, (C2/D2)*E2)

Esto divide el sueldo mensual entre los días del periodo y lo multiplica por los días laborados. Si no laboró nada, el sueldo es cero.

Columna G — Horas extra: escribes las horas extra trabajadas.

Columna H — Pago de horas extra: el pago doble por las primeras 9 horas y triple a partir de la décima.

=SI(G2<=9, G2*(C2/D2/8)*2, 9*(C2/D2/8)*2 + (G2-9)*(C2/D2/8)*3)

El sueldo diario se divide entre 8 para obtener el pago por hora. Las primeras 9 horas se pagan al doble, las restantes al triple.

Columna I — Base gravable: la suma del sueldo devengado más las horas extra.

=F2+H2

Columna J — ISR retenido: usa BUSCARX con coincidencia aproximada para ubicar el renglón de la tarifa.

=BUSCARX(I2, Catálogos!$J$2:$J$12, Catálogos!$K$2:$K$12, , -1) + (I2 - BUSCARX(I2, Catálogos!$J$2:$J$12, Catálogos!$J$2:$J$12, , -1)) * BUSCARX(I2, Catálogos!$J$2:$J$12, Catálogos!$L$2:$L$12, , -1)

El argumento -1 le dice a BUSCARX que busque el valor más alto que sea menor o igual al buscado. Así localiza el renglón correcto de la tarifa. La fórmula suma la cuota fija más el excedente multiplicado por la tasa.

Columna K — IMSS obrero: cálculo simplificado al 2.775% de la base, con tope.

=MIN(I2, 25*108.57*30) * 0.02775

El tope es 25 UMMAs diarias multiplicadas por 30 días. Lo que quede abajo del tope se multiplica por la cuota obrera.

Columna L — Neto a pagar:

=I2 - J2 - K2

Macro para generar recibos

En la hoja Recibos diseña el formato del recibo: nombre, RFC, periodo, desglose de percepciones, deducciones y neto. En la celda donde va el ID del empleado pon una lista desplegable con validación de datos.

Crea esta macro que copia los datos de la nómina al recibo:

Sub GenerarRecibo()
    Dim idEmp As String
    idEmp = Sheets("Recibos").Range("B2").Value
    Sheets("Nómina").Select
    Dim fila As Range
    Set fila = Columns(1).Find(idEmp, LookAt:=xlWhole)
    Sheets("Recibos").Range("B3") = fila.Offset(0, 1)
    Sheets("Recibos").Range("B4") = fila.Offset(0, 5)
    Sheets("Recibos").Range("B5") = fila.Offset(0, 8)
    Sheets("Recibos").Range("B6") = fila.Offset(0, 9)
    Sheets("Recibos").Range("B7") = fila.Offset(0, 10)
    Sheets("Recibos").Range("B8") = fila.Offset(0, 11)
End Sub

Seleccionas un ID de la lista, ejecutas la macro y el recibo se llena solo.

Lo que lograste

Con esta plantilla, la nómina de 15 empleados se calcula en el tiempo que tardas en escribir los días laborados y las horas extra de cada uno. El ISR y el IMSS se calculan solos. Los recibos se generan con un clic.

Lección 2: Cálculo de depreciaciones con el método de línea recta

El problema que resolvemos

La depreciación es un cálculo que haces una vez al año para la declaración anual y cada mes para la contabilidad. Si lo haces a mano, calculas la base, divides entre la vida útil, aplicas el porcentaje del bien y registras el resultado. Con 20 activos te vas una tarde. Aquí vas a construir una calculadora que hace todo el trabajo.

Conceptos que necesitas tener claros

La depreciación es el reconocimiento del desgaste de un activo. En México, la LISR permite depreciar:

• Edificios: 5% anual
• Mobiliario y equipo de oficina: 10% anual
• Equipo de cómputo: 30% anual
• Vehículos: 25% anual (con límite de inversión)
• Herramientas: 5% anual

El porcentaje se aplica sobre el monto original de la inversión (MOI), que es el costo del activo más los gastos necesarios para ponerlo en funcionamiento (fletes, instalación, pruebas).

En el primer año, la depreciación se calcula por meses completos desde que el bien entró en operación. Si compraste un escritorio en julio, solo deprecias 6 meses (julio a diciembre).

Estructura de la plantilla

Crea una hoja llamada Activos con estas columnas:

Columna A — ID del activo: número consecutivo.

Columna B — Descripción: nombre del bien (escritorio, computadora, vehículo).

Columna C — Fecha de adquisición: formato de fecha real.

Columna D — MOI (monto original de inversión): costo del bien más gastos.

Columna E — Tipo de bien: lista desplegable con Edificios, Mobiliario, Cómputo, Vehículos, Herramientas.

Columna F — Porcentaje anual: con BUSCARX traes el porcentaje según el tipo.

=BUSCARX(E2, Catálogos!$A$2:$A$6, Catálogos!$B$2:$B$6)

Donde en Catálogos tienes:

Columna G — Vida útil en años: el inverso del porcentaje.

=1/F2

Si el porcentaje es 10%, la vida útil es 10 años.

Columna H — Meses del primer año: cuántos meses completos desde la adquisición hasta diciembre.

=SI(AÑO(C2)=AÑO(HOY()), 12-MES(C2)+1, 12)

Si compraste el bien este año, cuenta los meses desde el mes de compra hasta diciembre. Si lo compraste un año anterior, son 12 meses.

Columna I — Depreciación del primer año:

=D2*F2*(H2/12)

El MOI por el porcentaje anual, ajustado por los meses del primer año.

Columna J — Depreciación anual de años siguientes:

=D2*F2

Sin ajuste de meses, porque ya son años completos.

Columna K — Depreciación acumulada a la fecha:

=SI(AÑO(C2)=AÑO(HOY()), I2, I2 + J2*(AÑO(HOY())-AÑO(C2)-1) + D2*F2*(12-MES(HOY())+1)/12)

Esta fórmula suma la depreciación del primer año, más los años completos, más los meses transcurridos del año actual.

Columna L — Valor en libros:

=MAX(D2-K2, 0)

El MOI menos la depreciación acumulada. Nunca baja de cero.

Ejemplo concreto

Compraste una computadora el 15 de marzo de 2024 por 25,000 pesos.

• MOI: 25,000
• Tipo: Cómputo
• Porcentaje: 30%
• Vida útil: 3.33 años
• Meses del primer año: 10 (marzo a diciembre)
• Depreciación del primer año: 25,000 × 30% × (10/12) = 6,250
• Depreciación anual años siguientes: 25,000 × 30% = 7,500
• Depreciación acumulada a diciembre 2024: 6,250
• Valor en libros a diciembre 2024: 25,000 − 6,250 = 18,750

Tabla resumen para el reporte

Al lado de tu tabla de activos, crea una tabla dinámica que sume la depreciación del periodo por tipo de bien. Así sabes cuánto depreciaste en mobiliario, cuánto en cómputo, cuánto en vehículos. Ese es el número que registras en la póliza mensual.

Lo que lograste

Con esta plantilla, registrar 20 activos te toma 10 minutos: escribes la descripción, la fecha, el MOI y el tipo. Todo lo demás se calcula solo. La tabla dinámica te da el total para la póliza.

Lección 3: Reportes para el SAT — DIOT con Excel

El problema que resolvemos

La DIOT (Declaración Informativa de Operaciones con Terceros) se presenta mensualmente y requiere clasificar cada proveedor por tipo de tercero (15, 16, 17), tipo de operación (compras, servicios, arrendamientos) y el IVA acreditable y no acreditable. Si lo haces a mano, revisas cada factura, la clasificas y la capturas en el portal del SAT. Con 50 proveedores, te vas un día entero.

Aquí vas a construir una plantilla que clasifica y suma automáticamente desde tu registro contable.

Datos de entrada

Necesitas tu registro de facturas del periodo. Si ya usas la conciliación bancaria del módulo anterior, ya tienes los datos. Si no, exportas de tu sistema contable un Excel con estas columnas:

• RFC del proveedor
• Nombre del proveedor
• Tipo de comprobante (ingreso, egreso)
• Fecha
• Subtotal
• IVA
• Total
• Tipo de gasto (servicios, compras, arrendamientos, etcétera)

Estructura de la plantilla

Crea una hoja llamada DIOT con estas columnas:

Columna A — RFC: viene de tu registro.

Columna B — Tipo de tercero: 15 (nacional), 16 (extranjero), 17 (proveedor global). Para la mayoría de proveedores es 15.

Columna C — Tipo de operación: 03 (servicios), 06 (arrendamiento de inmuebles), 85 (otros). Lo traes con BUSCARX del catálogo de tipos de gasto.

=BUSCARX(H2, Catálogos!$A$2:$A$10, Catálogos!$B$2:$B$10)

Donde el catálogo tiene:

Columna D — Base (subtotal): viene de tu registro.

Columna E — IVA acreditable: el IVA que sí puedes acreditar.

Columna F — IVA no acreditable: el IVA que no puedes acreditar (gastos no deducibles, proporcionales).

Columna G — Total: la suma de base más ambos IVAs.

Tabla dinámica para el resumen

Selecciona toda tu tabla de DIOT e inserta una tabla dinámica. Configúrala así:

• Filas: RFC, Nombre del proveedor
• Columnas: Tipo de operación
• Valores: Suma de Base, Suma de IVA acreditable, Suma de IVA no acreditable

aplicación práctica de las herramientas de excel (2)

La tabla dinámica te agrupa todos los comprobantes de un mismo RFC en una sola fila, sumando los importes. Esa es la información que capturas en el portal del SAT.

Validación antes de enviar

Antes de capturar en el portal, haz estas tres validaciones:

Validación 1: RFCs válidos. Crea una columna que verifique la longitud del RFC (personas morales: 12 caracteres, personas físicas: 13 caracteres).

=SI(O(LARGO(A2)=12, LARGO(A2)=13), "Válido", "Revisar")

Validación 2: Cuadre de IVA. El IVA total debe ser el 16% de la base (o el porcentaje que aplique).

=REDONDEAR(D2*0.16, 2) = E2 + F2

Si da FALSO, hay un error en el registro.

Validación 3: Sin duplicados. Usa formato condicional para marcar los RFC que aparecen más de una vez en la tabla. En la tabla dinámica no debe haber duplicados porque ya están agrupados, pero en tu registro original sí pueden aparecer.

=CONTAR.SI($A$2:$A$1000, A2) > 1

Aplica esta fórmula con formato condicional: si la celda se pinta de rojo, tienes un RFC duplicado que revisar.

Lo que lograste

Con esta plantilla, la DIOT deja de ser un día de trabajo. Exportas tu registro, lo pegas en la plantilla, la tabla dinámica agrupa y suma, y tú solo capturas los totales en el portal del SAT. Cincuenta proveedores en 20 minutos.

Lección 4: Tablas dinámicas para reportes contables

El problema que resolvemos

Tu jefe o cliente te pide un reporte: ¿cuánto gastamos en servicios este mes? ¿Qué proveedor concentra más compras? ¿Cómo van los ingresos contra los gastos por mes? Si lo haces con fórmulas, escribes SUMAR.SI por cada categoría, por cada mes, por cada proveedor. Cambia una categoría y tienes que rehacer todo. Las tablas dinámicas lo resuelven en segundos.

Tu primera tabla dinámica: estado de resultados por mes

Toma tu registro contable (polizas con cuenta, fecha, cargo y abono) y conviértelo en tabla con Ctrl+T. Llámala Polizas.

Inserta una tabla dinámica y configúrala así:

• Filas: Cuenta contable (agrupada por naturaleza: ingresos, costos, gastos)
• Columnas: Mes (extraído de la fecha con =TEXTO(B2, "mmmm"))
• Valores: Suma de importe (cargo para gastos y costos, abono para ingresos)

El resultado es una matriz: cada fila es una cuenta, cada columna es un mes, cada celda es el total. Es exactamente lo que tu jefe quiere ver.

Segmentación de datos

La segmentación es el filtro visual de la tabla dinámica. Inserta una segmentación por Tipo de cuenta y otra por Departamento. Al hacer clic en un botón de la segmentación, la tabla dinámica se filtra al instante. Tu jefe puede ver solo gastos, solo ingresos, o solo un departamento, sin que tú rehagas nada.

Tabla dinámica con campo calculado: margen de utilidad

Las tablas dinámicas pueden calcular campos que no existen en tus datos originales. Dentro de la tabla dinámica, ve a Campos, elementos y conjuntos → Campo calculado y crea:

Margen = Ingresos - Costos

Margen % = (Ingresos - Costos) / Ingresos

Ahora la tabla dinámica muestra no solo los ingresos y los costos, sino también el margen en pesos y en porcentaje. Sin escribir una sola fórmula en las celdas.

Gráfico dinámico

Al lado de tu tabla dinámica, inserta un gráfico dinámico de columnas. Cuando filtres por la segmentación, el gráfico se actualiza solo. Ese es el reporte visual que presentas en la junta mensual.

Ejemplo concreto

Tu registro de pólizas tiene 500 líneas en el mes. Quieres saber el top 5 de proveedores por monto.

1. Selecciona los datos, inserta tabla dinámica.
2. Filas: Nombre del proveedor.
3. Valores: Suma del total.
4. Ordena de mayor a menor.
5. Filtro de valores: Top 5.

En 30 segundos tienes la respuesta. Con fórmulas, tendrías que escribir un SUMAR.SI por cada proveedor, ordenar manualmente y contar los cinco más altos.

Lo que lograste

Las tablas dinámicas te dan reportes que antes tomaban una hora en 30 segundos. Y cuando los datos cambian (agregas pólizas del mes siguiente), solo actualizas la tabla dinámica con un clic y todo recalcula.

Lección 5: BUSCARX cruzando catálogos contables

El problema que resolvemos

Tienes el catálogo de cuentas de tu despacho, pero el cliente usa otro catálogo. Necesitas mapear las cuentas del cliente a las tuyas para consolidar los reportes. O necesitas traer el nombre del proveedor desde su RFC, que está en otro archivo. BUSCARX lo resuelve sin los problemas de BUSCARV: no necesitas que la columna de búsqueda esté a la izquierda, no necesitas contar columnas, y si no encuentra el dato, te da un mensaje claro en lugar de un error feo.

Sintaxis de BUSCARX que necesitas dominar

=BUSCARX(valor_buscado, rango_busqueda, rango_resultado, [si_no_encuentra], [coincidencia], [modo_busqueda])

• valor_buscado: lo que estás buscando (un RFC, un número de cuenta).
• rango_busqueda: donde lo buscas (la columna de RFC en el catálogo).
• rango_resultado: de dónde traes el resultado (la columna de nombre).
• si_no_encuentra: qué mostrar si no lo encuentra (texto entre comillas).
• coincidencia: 0 para exacta (por defecto), -1 para aproximada.
• modo_busqueda: 1 de primero a último (por defecto), -1 de último a primero.

Ejemplo 1: Traer el nombre del proveedor desde el RFC

Tu registro de facturas tiene el RFC pero no el nombre. El catálogo de proveedores sí tiene ambos.

=BUSCARX(A2, Proveedores!$A$2:$A$500, Proveedores!$B$2:$B$500, "Proveedor no encontrado")

Si el RFC no está en el catálogo, la celda muestra "Proveedor no encontrado" en lugar de #N/A. Así identificas al instante qué proveedores faltan de dar de alta.

Ejemplo 2: Mapear cuentas entre dos catálogos

El cliente tiene su catálogo en la hoja CatCliente con cuentas como "5.1.01 Sueldos". Tu catálogo está en la hoja CatDespacho con cuentas como "6100-001 Sueldos y salarios". Creas una columna de equivalencias:

En tu registro de pólizas, traes la cuenta de tu despacho con:

=BUSCARX(B2, Equivalencias!$A$2:$A$100, Equivalencias!$B$2:$B$100, "Sin equivalencia")

Si aparece "Sin equivalencia", sabes que te falta mapear esa cuenta.

Ejemplo 3: BUSCARX con búsqueda aproximada para tarifas

Ya lo usamos en la nómina con la tarifa del ISR. El mismo principio aplica para cualquier tabla escalonada: tarifas de ISR anual, tablas de cuotas IMSS, escalas de subsidio al empleo.

=BUSCARX(base_gravable, TarifaISR!$A$2:$A$12, TarifaISR!$B$2:$B$12, , -1)

El argumento -1 busca el valor más alto que sea menor o igual al buscado. Así, si la base gravable es 15,000, localiza el renglón de 11,286.54 y aplica esa tarifa.

Ejemplo 4: BUSCARX horizontal con dos criterios

Necesitas traer el saldo de una cuenta específica de un mes específico. Tu tabla tiene cuentas en filas y meses en columnas.

=BUSCARX(cuenta, Saldos!$A$2:$A$100, INDICE(Saldos!$B$2:$M$100, 0, COINCIDIR(mes, Saldos!$B$1:$M$1, 0)))

BUSCARX encuentra la fila de la cuenta. COINCIDIR encuentra la columna del mes. INDICE trae el valor en esa intersección. Es un BUSCARV con dos criterios sin necesidad de columnas auxiliares.

Lo que lograste

BUSCARX reemplaza a BUSCARV, BUSCARH y combinaciones con INDICE y COINCIDIR. Una sola función hace todo el trabajo de cruce de catálogos, y cuando no encuentra el dato, te avisa en lugar de romper la hoja con un error.

Lección 6: Cierre mensual automatizado

El problema que resolvemos

El cierre mensual es la tarea que nadie quiere hacer. Sumas todas las pólizas, calculas los totales por cuenta, generas el estado de resultados, el balance general, las variaciones presupuestales y el reporte de cuentas por pagar y por cobrar. Si lo haces a mano, son dos días. Aquí vas a construir un tablero que lo hace en un clic.

Estructura del tablero de cierre

Crea una hoja llamada Cierre con cuatro secciones:

Sección 1: Estado de resultados

Una tabla que suma los ingresos, costos y gastos del mes. Usa SUMAR.SI.CONJUNTO contra tu registro de pólizas:

=SUMAR.SI.CONJUNTO(Polizas!$E:$E, Polizas!$A:$A, ">="&FECHA(año, mes, 1), Polizas!$A:$A, "<="&FECHA.MES(FECHA(año, mes, 1), 1)-1, Polizas!$C:$C, "Ingresos")

Esta fórmula suma la columna E (importe) de las pólizas que están en el mes indicado y cuya naturaleza es "Ingresos". Repites para "Costos" y "Gastos".

Sección 2: Balance general

Suma los saldos de activos, pasivos y capital. Usa la misma fórmula SUMAR.SI.CONJUNTO pero filtrando por tipo de cuenta.

Sección 3: Variación presupuestal

Compara lo real contra el presupuesto. Necesitas una hoja Presupuesto con las mismas cuentas y los montos planeados por mes. La variación es:

=Real - Presupuesto

=Real / Presupuesto - 1

La primera te da la variación en pesos. La segunda en porcentaje. Aplica formato condicional: verde si gastaste menos, rojo si gastaste más.

Sección 4: Cuentas por pagar y por cobrar

Desde tu registro de facturas, suma las que están pendientes de pago o de cobro. Filtra por estatus:

=SUMAR.SI.CONJUNTO(Facturas!$F:$F, Facturas!$G:$G, "Pendiente", Facturas!$H:$H, ">="&FECHA(año, mes, 1))

Suma el total de facturas con estatus "Pendiente" y fecha posterior al primer día del mes.

Macro de cierre con un clic

Crea esta macro que actualiza todo el tablero:

Sub CierreMensual()
    Dim año As Integer
    Dim mes As Integer
    año = Sheets("Cierre").Range("B1").Value
    mes = Sheets("Cierre").Range("B2").Value
    
    Sheets("Cierre").Calculate
    Sheets("Cierre").Range("A1").Value = "Cierre de " & mes & "/" & año
    
    Dim tablas As PivotTable
    For Each tablas In Sheets("Cierre").PivotTables
        tablas.RefreshTable
    Next tablas
    
    MsgBox "Cierre actualizado para " & mes & "/" & año
End Sub

Escribes el mes y el año en las celdas B1 y B2, ejecutas la macro y todo el tablero se actualiza: estado de resultados, balance, variación presupuestal y cuentas por pagar y cobrar.

Validación final

Antes de entregar el cierre, verifica que cuadre:

Cuadre 1: Activo = Pasivo + Capital. Si no cuadra, hay una póliza mal registrada.

Cuadre 2: Ingresos − Costos − Gastos = Utilidad del periodo. Esta utilidad debe ser igual a la variación del capital del mes.

Cuadre 3: Saldo de cuentas por cobrar + bancos + inventarios = Activo circulante. Si no cuadra con tu conciliación bancaria, hay un error.

Lo que lograste

El cierre mensual que te tomaba dos días ahora son 20 minutos: actualizas las pólizas, ejecutas la macro, revisas los tres cuadres y entregas. El tablero se actualiza solo cada mes porque las fórmulas usan el mes y el año que tú indicas.

Workbook del módulo 4

Tarea 1: Plantilla de nómina

Descarga la plantilla NominaPlantilla.xlsx y completa lo siguiente:

1. Da de alta a 5 empleados en el catálogo con su RFC, CURP, sueldo mensual y fecha de alta.
2. Registra las incidencias del periodo: 2 empleados con faltas, 1 con horas extra, 1 con incapacidad, 1 sin incidencias.
3. Verifica que el ISR se calcule correctamente comparando con el simulador del SAT.
4. Genera los 5 recibos con la macro y revisa que los datos coincidan con la tabla de nómina.
5. Entrega: el archivo con los 5 recibos generados y una nota con las diferencias que encontraste contra el simulador del SAT (si las hubo).

Tarea 2: Calculadora de depreciaciones

Descarga la plantilla DepreciacionesPlantilla.xlsx y completa lo siguiente:

aplicación práctica de las herramientas de excel (3)

1. Registra 8 activos: 2 computadoras, 2 escritorios, 1 vehículo, 1 edificio, 1 licencia de software y 1 herramienta.
2. Usa fechas de adquisición de años distintos (al menos 3 de años anteriores al actual).
3. Verifica que la depreciación acumulada y el valor en libros sean correctos para un activo comprado hace 2 años.
4. Crea una tabla dinámica que sume la depreciación del periodo por tipo de bien.
5. Entrega: el archivo con la tabla dinámica y el cálculo manual de un activo para verificar que la fórmula da el mismo resultado.

Tarea 3: Reporte DIOT

Descarga la plantilla DIOTPlantilla.xlsx y completa lo siguiente:

1. Pega el registro de facturas de un mes real (mínimo 30 facturas).
2. Clasifica cada factura por tipo de gasto usando el catálogo.
3. Crea la tabla dinámica que agrupa por RFC y tipo de operación.
4. Ejecuta las tres validaciones (RFCs, cuadre de IVA, duplicados).
5. Entrega: el archivo con la tabla dinámica lista para capturar en el portal del SAT y una nota con los hallazgos de las validaciones.

Tarea 4: Reporte con tabla dinámica

Usa tu propio registro de pólizas (o el archivo de ejemplo PolizasEjemplo.xlsx) y completa lo siguiente:

1. Crea una tabla dinámica de estado de resultados por mes.
2. Agrega una segmentación por tipo de cuenta.
3. Crea un campo calculado de margen de utilidad en pesos y en porcentaje.
4. Inserta un gráfico dinámico de columnas.
5. Entrega: el archivo con la tabla dinámica, la segmentación, el campo calculado y el gráfico.

Tarea 5: Cruce de catálogos con BUSCARX

Descarga la plantilla CruceCatalogosPlantilla.xlsx y completa lo siguiente:

1. Usa el catálogo del cliente (hoja CatCliente) y mapea 20 cuentas a tu catálogo (hoja CatDespacho).
2. Trae los nombres de proveedor desde el RFC usando BUSCARX.
3. Identifica los RFC que no están en el catálogo (deben mostrar "Proveedor no encontrado").
4. Usa BUSCARX con búsqueda aproximada para calcular el ISR de 3 empleados con sueldos distintos.
5. Entrega: el archivo con las equivalencias completas y la lista de proveedores faltantes.

Tarea 6: Cierre mensual

Descarga la plantilla CierrePlantilla.xlsx y completa lo siguiente:

1. Configura el mes y el año en las celdas B1 y B2.
2. Verifica que las fórmulas de estado de resultados traigan los datos correctos.
3. Completa el presupuesto del mes en la hoja Presupuesto.
4. Ejecuta la macro de cierre y revisa que los tres cuadres den correcto.
5. Entrega: el archivo con el cierre completo, el presupuesto y la captura de pantalla de los tres cuadres validados.

Plantillas descargables de este módulo

• NominaPlantilla.xlsx — Plantilla de nómina con catálogos, fórmulas y macro de recibos.
• DepreciacionesPlantilla.xlsx — Calculadora de depreciaciones por línea recta con tabla dinámica.
• DIOTPlantilla.xlsx — Plantilla para la DIOT con validaciones y tabla dinámica.
• PolizasEjemplo.xlsx — Registro de pólizas de ejemplo para practicar tablas dinámicas.
• CruceCatalogosPlantilla.xlsx — Plantilla de cruce de catálogos con BUSCARX.
• CierrePlantilla.xlsx — Tablero de cierre mensual con macro y validaciones.