La NIA-ES 500.A23 establece que "la confiabilidad de la información que se va a usar como evidencia de auditoría está influenciada por su fuente y por su naturaleza.

Lo que aprenderás

> En este artículo obtendrás:
Cinco funciones de Excel que transforman archivos del cliente en evidencia auditable bajo NIA-ES 500.A23
Fórmulas específicas para detectar duplicados, vacíos y anomalías que cumplan con NIA-ES 240.A43
Plantillas de tablas dinámicas configuradas para análisis de riesgo según NIA-ES 315.A214
Técnicas de validación de datos que previenen errores de transcripción documentados en papeles de trabajo
---

Contenido

Por qué Excel es determinante para la evidencia de auditoría

La NIA-ES 500.A23 establece que "la confiabilidad de la información que se va a usar como evidencia de auditoría está influenciada por su fuente y por su naturaleza." Cuando el cliente entrega datos en Excel, tu capacidad para validar, analizar y documentar esa información determina si cumples con los requerimientos de evidencia suficiente y adecuada.
El problema no es que los clientes usen Excel. El problema es que la mayoría de auditores lo usan como calculadora glorificada cuando debería ser su herramienta principal de análisis de datos. La diferencia entre un auditor que sabe Excel y uno que lo domina es la diferencia entre documentar una muestra de 25 facturas a mano y analizar toda la población de 15.000 transacciones en dos horas.
La NIA-ES 315.A214 requiere que identifiques "la naturaleza de las clases de transacciones, saldos contables e información a revelar significativos." Esto es imposible sin herramientas que te permitan estratificar poblaciones, identificar outliers y documentar patrones de forma auditable.

Las cinco funciones que todo auditor debe dominar

Después de revisar 200 archivos de trabajo de equipos en firmas medianas, estas son las funciones que separan los papeles de trabajo eficientes de los que requieren semanas de rehacer:

1. BUSCARV/XLOOKUP: La base de toda conciliación


Todo auditor necesita hacer coincidencias entre fuentes. Facturas contra entregas. Pagos contra facturas. Extractos bancarios contra mayor. Si no dominas BUSCARV (y su sucesor XLOOKUP), pasarás horas haciendo manualmente lo que Excel puede hacer en segundos.

2. Tablas dinámicas: Análisis de población instantáneo


La NIA-ES 530.A2 permite usar técnicas de muestreo estadístico "cuando la población de la cual se selecciona la muestra es apropiada para el objetivo específico del procedimiento de auditoría." Las tablas dinámicas te permiten entender esa población antes de tomar la muestra.

3. SUMAPRODUCTO: Cálculos con múltiples criterios


Necesitas sumar todos los pagos a proveedores en diciembre que superen €1.000 pero excluir las retenciones. SUMAPRODUCTO maneja este tipo de lógica compleja en una fórmula donde SUMA necesitaría cinco pasos.

4. Validación de datos: Controles preventivos


Los mejores papeles de trabajo incluyen controles que evitan errores de entrada. La validación de datos convierte tu Excel en una aplicación con reglas de negocio integradas.

5. Herramientas de auditoría: Benford's Law y análisis de patrones


Para cumplir con NIA-ES 240.A43 sobre "procedimientos analíticos que puedan indicar riesgos de incorrecciones materiales debidas a fraude," necesitas herramientas estadísticas que vayan más allá de las funciones básicas.

BUSCARV y XLOOKUP: coincidencias y validación de datos

La diferencia entre BUSCARV y XLOOKUP


BUSCARV ha sido el estándar durante 30 años, pero tiene limitaciones críticas para auditoría:
XLOOKUP (disponible en Office 365) resuelve estos problemas:
```excel
=XLOOKUP(valor_buscado, matriz_búsqueda, matriz_retorno, si_no_encuentra, modo_coincidencia, modo_búsqueda)
```

Fórmulas necesaries para auditoría


Coincidencia básica con XLOOKUP:
```excel
=XLOOKUP(A2, FacturasEmitidas[NumeroFactura], FacturasEmitidas[ImporteTotal], "No encontrada", 0)
```
BUSCARV con manejo de errores:
```excel
=SI(ESERROR(BUSCARV(A2,TablaProveedores,3,FALSO)), "Verificar proveedor", BUSCARV(A2,TablaProveedores,3,FALSO))
```
Coincidencia aproximada para análisis de antigüedad:
```excel
=XLOOKUP(B2, {0;31;61;91}, {"Corriente";"30-60 días";"60-90 días";"+90 días"}, "+90 días", 1)
```

Identificación de duplicados para NIA-ES 240


Para cumplir con los requerimientos de la NIA-ES 240.A43 sobre identificación de transacciones inusuales:
Contador de duplicados:
```excel
=CONTAR.SI($A$2:$A$5000,A2)
```
Marca de duplicados:
```excel
=SI(CONTAR.SI($A$2:A2,$A2)>1,"DUPLICADO","")
```
Identificación de referencias circulares en conciliaciones:
```excel
=SI(A2=XLOOKUP(XLOOKUP(A2,TablaB[ID],TablaB[Referencia]),TablaA[ID],TablaA[Referencia]),"CIRCULAR","OK")
```

  • Solo busca hacia la derecha
  • No maneja bien los valores duplicados
  • Error #N/A cuando no encuentra coincidencias (sin contexto para documentar)

Tablas dinámicas: análisis de población y estratificación

Las tablas dinámicas son la herramienta más potente para cumplir con NIA-ES 315.A214: "obtener un entendimiento de las clases de transacciones, saldos contables e información a revelar significativos."

Configuración básica para análisis de cuentas por cobrar

Estratificación por importes


Para aplicar NIA-ES 530.A15 sobre "seleccionar partidas con características específicas":
Crear campos calculados:
```excel
=SI(Importe<=1000,"Bajo",SI(Importe<=5000,"Medio","Alto"))
```
O usar grupos automáticos: Botón derecho en valores → Agrupar → Por importes

Análisis de tendencias temporales


Configuración para análisis mensual:
Detección de estacionalidad:
La variación mensual superior al 50% sin justificación de negocio puede indicar riesgo de fraude según NIA-ES 240.A24.

Tablas dinámicas para análisis de proveedores


Identificar concentración de riesgo:
Los proveedores que representen más del 5% del total de compras requieren procedimientos adicionales bajo NIA-ES 550.A28 (partes relacionadas).

  • Datos origen: Lista con Fecha, Cliente, Factura, Importe, Estado
  • Filas: Cliente (si hay menos de 50) o Rango_Importe
  • Columnas: Estado (Pendiente, Pagada, Vencida)
  • Valores: Suma de Importe, Cuenta de Facturas
  • Filtros: Fecha (último trimestre), Tipo de cliente
  • Filas: Fecha (agrupada por meses)
  • Columnas: Tipo_Transacción
  • Valores: Suma de Importe
  • Mostrar valores como: % del total de la fila
  • Filas: Proveedor
  • Valores: Suma de Pagos, Cuenta de Facturas
  • Mostrar valores como: % del total general
  • Filtrar: Top 10 por importe

SUMAPRODUCTO: cálculos multicritero

SUMAPRODUCTO permite cálculos complejos con múltiples condiciones sin matrices auxiliares. necesari para análisis de auditoría donde necesitas combinar varios filtros.

Sintaxis y lógica básica


```excel
=SUMAPRODUCTO((condición1)(condición2)(condición3)rango_valores)
```
Cada condición devuelve VERDADERO (1) o FALSO (0). Al multiplicarlas, solo las filas que cumplan TODAS las condiciones contribuyen al resultado.

Aplicaciones críticas en auditoría


Análisis de gastos por período y categoría:
```excel
=SUMAPRODUCTO((Fechas>=FECHA(2023,10,1))(Fechas<=FECHA(2023,12,31))(Categorias="Consultoría")Importes)
```
Identificación de transacciones just-under (posible evasión de controles):
```excel
=SUMAPRODUCTO((Importes>=4900)(Importes<5000))
```
Análisis de concentración temporal (riesgo de manipulación):
```excel
=SUMAPRODUCTO((DIASEM(Fechas,2)=5)(HORA(Fechas)>=17)Importes)
```
Suma transacciones ingresadas viernes después de las 17:00
Validación de integridad referencial:
```excel
=SUMAPRODUCTO((XLOOKUP(Clientes,TablaClientes[ID],TablaClientes[ID],"ERROR")="ERROR")1)
```
Cuenta registros de ventas sin cliente válido

Cálculos avanzados para muestreo


Estratificación automática:
```excel
=SUMAPRODUCTO((Importes>0)(Importes<=1000)) & " facturas hasta €1.000"
=SUMAPRODUCTO((Importes>1000)(Importes<=5000)) & " facturas €1.001-€5.000"
=SUMAPRODUCTO((Importes>5000)) & " facturas sobre €5.000"
```

Validación de datos y controles de integridad

La validación de datos convierte tus hojas de cálculo en aplicaciones con reglas de negocio. Previene errores de transcripción que pueden invalidar todo el análisis.

Configuración de validación básica


Acceso: Datos → Validación de datos → Configuración
Tipos críticos para auditoría:

Fórmulas de validación avanzada


Validar formato de NIF español:
```excel
=Y(LARGO(A1)=9, ESNUMERO(VALOR(IZQUIERDA(A1,8))), ESTEXTO(DERECHA(A1,1)))
```
Validar fechas dentro del período auditable:
```excel
=Y(A1>=FECHA(2023,1,1), A1<=FECHA(2023,12,31))
```
Validar códigos de cuenta según plan contable:
```excel
=CONTAR(TablaContable[Codigo],A1)>0
```

Mensajes de error personalizados


Mensaje informativo:
Alerta de error:

Controles de integridad a nivel de hoja


Verificar completitud de datos obligatorios:
```excel
=SI(CONTARBLANCO(A:A)>1, "FALTAN DATOS EN COLUMNA A", "OK")
```
Control de suma:
```excel
=SI(SUMA(D:D)=Valor_Control_Contabilidad, "COINCIDE", "DIFERENCIA: " & SUMA(D:D)-Valor_Control_Contabilidad)
```
Verificar duplicados no permitidos:
```excel
=SI(CONTAR(A:A)-CONTARA(UNICO(A:A))>0, "HAY DUPLICADOS", "OK")
```

  • Lista desplegable: Centros de costo, códigos de cuenta, nombres de auditores
  • Fecha: Rangos de períodos auditables
  • Número entero: Códigos de referencia, números de cheque
  • Decimal: Importes con límites lógicos
  • Longitud de texto: NIF/CIF (9 caracteres), códigos internos
  • Título: "Código de cuenta"
  • Mensaje: "Seleccionar del plan contable autorizado o consultar con supervisor"
  • Título: "Fecha inválida"
  • Mensaje: "Las fechas deben estar entre 01/01/2023 y 31/12/2023 (período auditable)"

Herramientas de auditoría avanzadas

Análisis de Ley de Benford


La Ley de Benford establece que en conjuntos de datos naturales, el primer dígito 1 aparece aproximadamente 30% de las veces, el 2 aparece 17.6%, y así sucesivamente. Desviaciones significativas pueden indicar manipulación.
aplicación en Excel:
Extraer primer dígito:
```excel
=VALOR(IZQUIERDA(SUSTITUIR(SUSTITUIR(ABS(A2),".",""),",",""),1))
```
Frecuencia esperada según Benford:
```excel
=LOG10(1+1/FILA(A1:A9))
```
Cálculo de desviación:
```excel
=ABS(Frecuencia_Observada-Frecuencia_Benford)
```
Implementar en tabla :

Detección de números redondos


Los números excesivamente redondos (terminados en 00, 000, 500) pueden indicar estimaciones no soportadas.
Identificar números "redondos":
```excel
=SI(O(RESIDUO(A2,100)=0, RESIDUO(A2,1000)=0, RESIDUO(A2,500)=0), "REDONDO", "")
```
Porcentaje de números redondos en población:
```excel
=SUMAPRODUCTO((RESIDUO(Importes,100)=0)1)/CONTAR(Importes)
```
Un porcentaje superior al 20% en transacciones operativas puede requerir investigación adicional.

Análisis de distribución y outliers


Identificar outliers usando regla 3-sigma:
```excel
=SI(ABS(A2-PROMEDIO($A$2:$A$1000))>3DESVEST($A$2:$A$1000), "OUTLIER", "")
```
Análisis de cuartiles para estratificación:
```excel
Q1: =CUARTIL.EXC(Importes,1)
Q3: =CUARTIL.EXC(Importes,3)
IQR: =Q3-Q1
Límite_Superior: =Q3+1.5IQR
```

Análisis temporal avanzado


Identificar concentración de transacciones en fechas específicas:
```excel
=SUMAPRODUCTO((DIA(Fechas)=31)1)/CONTAR(Fechas)
```
Porcentaje de transacciones el último día del mes
Análisis de patrones semanales:
```excel
=FRECUENCIA(DIASEM(Fechas),{1;2;3;4;5;6;7})
```

  • Crear campo calculado: Primer_Digito
  • Filas: Primer_Digito (1-9)
  • Valores: Cuenta de registros, % del total
  • Comparar con frecuencias teóricas de Benford

Ejemplo práctico completo

> Escenario: Manufacturas Levantinas S.L., Valencia, facturación anual de 12,8 millones de euros. El cliente ha proporcionado su registro de cuentas por cobrar con 2.847 facturas del ejercicio 2023. Necesitas evaluar la razonabilidad de la provisión para clientes de dudoso cobro (€127.000) y seleccionar una muestra para circularización.

Paso 1: Validación y limpieza inicial


Verificar integridad de datos:
```excel
=SI(CONTARBLANCO(A:E)>0, "Datos faltantes detectados", "Datos completos")
=SI(CONTAR(NumeroFactura)-CONTAR(UNICO(NumeroFactura))>0, "Duplicados encontrados", "Sin duplicados")
```
Documentación: Verificación de completitud de datos según NIA-ES 500.A31

Paso 2: Análisis de antigüedad con tabla


Configuración:
Resultado típico:
Documentación: Análisis de antigüedad para evaluar recuperabilidad según NIA-ES 540.A86

Paso 3: Análisis de concentración de riesgo


```excel
=SUMAPRODUCTO((Saldos>50000)1) & " clientes con saldo >€50K"
=SUMAPRODUCTO((Saldos>50000)Saldos)/SUMA(Saldos) & "% del total"
```
Resultado:
Documentación: Identificación de concentraciones significativas para procedimientos de confirmación dirigida

Paso 4: Detección de anomalías


Análisis de Benford en importes facturados:
Los primeros dígitos 1, 2, 3 representan el 61% de las facturas (esperado según Benford: 60.2%). Distribución normal.
Identificación de números redondos:
```excel
=SUMAPRODUCTO((RESIDUO(Importes,100)=0)1)/CONTAR(Importes)
```
Resultado: 31% de facturas terminan en .00 (alto pero explicable por precios industriales estándar)
Documentación: Análisis de patrones para identificar posibles manipulaciones según NIA-ES 240.A24

Paso 5: Selección de muestra para circularización


Estratificación para NIA-ES 505.A7:
Fórmula de selección aleatoria:
```excel
=SI(ALEATORIO()<0.238, "SELECCIONAR", "")
```
0.238 = 35/147 para el estrato medio

Paso 6: Evaluación de la provisión


Provisión actual del cliente: €127K
Análisis por antigüedad:
Documentación: La provisión actual de €127K es razonable y está soportada por el análisis de antigüedad

Conclusión del análisis


El análisis de Excel identificó:
Documentación: Análisis completado conforme NIA-ES 330.A23 - procedimientos de sustantivos apropiados para saldos contables*

  • Filas: Días_Vencimiento (agrupado: 0-30, 31-60, 61-90, >90)
  • Columnas: Cliente_Tipo (A, B, C según volumen)
  • Valores: Suma de Saldo, Cuenta de facturas
  • Filtros: Estado (pendiente)
  • 0-30 días: €3.2M (78% del saldo)
  • 31-60 días: €654K (16% del saldo)
  • 61-90 días: €187K (5% del saldo)
  • >90 días: €93K (2% del saldo, pero incluye €67K del cliente Distribuciones Costa Blanca S.L.)
  • 12 clientes representan el 67% del saldo total
  • Cliente más grande: €187K (4.1% del saldo, verificar si excede límites de crédito)
  • Estrato 1 (>€25K): Circularizar el 100% (23 clientes)
  • Estrato 2 (€5K-€25K): Muestra aleatoria de 35 de 147 clientes
  • Estrato 3 (<€5K): Muestra aleatoria de 40 de 2.677 clientes
  • >90 días: €93K × 85% = €79K (deterioro estimado)
  • 61-90 días: €187K × 25% = €47K (deterioro estimado)
  • Total sugerido: €126K
  • Concentración en Distribuciones Costa Blanca S.L. (requiere confirmación directa)
  • Provisión actual adecuada según análisis de deterioro
  • Patrones de facturación normales (sin señales de manipulación)
  • Muestra de circularización de 98 clientes (72% del saldo)

Lista de verificación práctica

Antes de iniciar cualquier análisis:
Verificar que tu análisis cumple NIA-ES 500.A23 sobre evidencia suficiente y adecuada.

  • Verificar integridad de datos: CONTARBLANCO, duplicados, formato consistente
  • Establecer controles de validación: Fechas, importes, códigos obligatorios
  • Crear campos calculados necesarios: Antigüedad, clasificaciones, indicadores
  • Documentar fuente de datos: Fecha de extracción, responsable del cliente, controles aplicados
  • Configurar fórmulas de control: Suma total, cantidad de registros, valores mínimos/máximos
  • Preparar plantilla de documentación: Referencia PT, objetivo del análisis, conclusiones

Errores comunes que evitar

Excel como calculadora glorificada: No uses Excel solo para sumar importes que ya están sumados en el sistema del cliente. Úsalo para análisis que añadan valor: tendencias, anomalías, estratificación.
Fórmulas sin control de errores: Todo BUSCARV necesita manejo de #N/A. Todo cálculo con denominador necesita validación de división por cero.
Tablas dinámicas sin documentar: Una tabla sin contexto escrito no es evidencia de auditoría válida. Documenta qué muestra, por qué es relevante, qué conclusión soporta.

Contenido relacionado

  • Calculadora de materialidad: Configura niveles de materialidad que integren con tus análisis de Excel
  • Plantillas de papeles de trabajo NIA-ES: Incluye plantillas de Excel preconfiguradas con validación de datos
  • Análisis de riesgo de fraude NIA-ES 240: Cómo usar análisis de patrones en Excel para cumplir con NIA-ES 240.A24

Recibe información práctica de auditoría, semanalmente.

Sin teoría de examen. Solo lo que hace que las auditorías funcionen más rápido.

Más de 290 guías publicadas20 herramientas gratuitasCreado por un auditor en ejercicio

Sin spam. Somos auditores, no vendedores.