Esta publicación demostrará cómo usar fórmulas de Excel para determinar selecciones de muestra basadas en unidades de dólar. La idea básica es que cada dólar es una unidad de muestreo y, como tal, es más probable que este método seleccione artículos de mayor valor para realizar pruebas. Este método recibe varios nombres, incluido muestreo de unidades monetarias, muestreo de unidades en dólares o probabilidad proporcional al tamaño. Aunque ya no realizo auditorías, quería responder una pregunta que recibí de Anthony, quién sí lo hace. Pidió una forma rápida de determinar sus selecciones de muestra en Excel.

Aquí está su pregunta básica:

“Si he determinado que el intervalo monetario es $1,000,000, tomaremos nuestra población de préstamos (en una tabla de Excel), elegiremos un préstamo aleatorio para comenzar, arrastraremos hacia abajo la columna de saldo de préstamos hasta que los saldos de los préstamos sean iguales o exceder 1.000.000 y seleccionar ese préstamo. A partir del siguiente préstamo, repetimos este proceso una y otra vez hasta que tengamos el número necesario para nuestra muestra. Tiene que haber una manera más fácil…”

Sí, hay una manera más fácil. En lugar de hacerlo manualmente (de forma lenta), podemos usar fórmulas que nos ayuden a hacerlo de forma rápida

Objetivo

Antes de pasar a las fórmulas, dejemos claro nuestro objetivo.

Suponiendo que nuestra población son préstamos, creamos una lista e incluimos el monto del préstamo. Nos gustaría poder ingresar un monto de intervalo, digamos, 1000, y que Excel determine qué préstamos se seleccionan. Cuando el saldo acumulado de préstamos llegue a 1000, seleccionaríamos ese préstamo y luego comenzaríamos el proceso en el siguiente préstamo. Esto se ilustra en la captura de pantalla siguiente.

Los primeros 1000 se alcanzan en el préstamo C. Luego, según la pregunta de Anthony, el proceso comenzaría nuevamente en el siguiente préstamo. Los siguientes 1.000 se alcanzan en el préstamo E, luego el proceso comienza de nuevo en el préstamo F y continúa.

Ahora, veamos cómo podemos ayudarnos con las fórmulas de Excel.

Detalles

Como sabes, en Excel hay muchas formas de realizar cualquier tarea determinada. Entonces, esta es sólo una de las muchas opciones posibles. Si tiene otro que le gustaría compartir, publique un comentario a continuación.

El enfoque presentado en esta publicación utilizará dos columnas auxiliares para definir el rango acumulativo y luego comenzará de nuevo cuando se seleccione un préstamo. Con estas columnas auxiliares implementadas, la fórmula de selección es bastante sencilla.

Echemos un vistazo rápido al resultado final y luego revisemos los detalles de las fórmulas:

Veámoslo una fórmula a la vez.

Préstamo A

Columna De: Comencemos con el primer préstamo, Préstamo A. El valor De inicial , 1, simplemente se ingresa en la celda.

Columna Hasta: La fórmula Hasta debe devolver el monto del préstamo, 111. Una fórmula para lograr esto es:

=D11+C11-1

Columna de selección: la fórmula de selección debe devolver VERDADERO si el valor del intervalo en C6 se encuentra entre los valores Desde y Hasta. Una forma de hacerlo es utilizar la función AND, que devuelve VERDADERO cuando todos sus argumentos son VERDADEROS. Algo como esto funcionaría:

=Y($C$6=D9,$C$6=E9)

Si el valor del intervalo en $C$6 (referencia absoluta) es mayor o igual que el valor Desde en D9 (referencia relativa) y menor o igual que el valor Hasta en E9 (referencia relativa), entonces la función AND devuelve VERDADERO, indica que el préstamo está seleccionado. De lo contrario, devuelve FALSO, lo que indica que el préstamo no es una selección.

Ahora pasemos al siguiente préstamo.

Préstamo B

Columna De: El valor De para el siguiente préstamo depende de si se seleccionó el préstamo anterior. Si se seleccionó el préstamo anterior, entonces el valor Desde debe restablecerse a 1 porque el proceso de muestreo comienza nuevamente. De lo contrario, el valor Desde debe incrementar el valor Hasta del préstamo anterior en 1. Podemos usar una función SI para lograr esta lógica condicional, de la siguiente manera.

=SI(F9,1,E9+1)

Columna A: la lógica de la columna A no cambia con respecto al préstamo anterior, por lo que simplemente podemos completar la fórmula desde el Préstamo A hacia abajo.

Columna de selección: la lógica de la fórmula de selección no cambia con respecto al préstamo anterior, por lo que podemos simplemente completar la fórmula del Préstamo A.

Préstamos restantes

Simplemente podemos completar las fórmulas para el préstamo B en todos los préstamos restantes.

Formato condicional

Si quisiéramos darle un poco de sabor a las cosas, podríamos seleccionar la columna Selección y luego usar Inicio Formato condicional Resaltar reglas de celda Igual a para indicarle a Excel que formatee las celdas VERDADERAS con verde o cualquier otro formato preferido.

Video

Grabé un video rápido que demuestra cómo escribir estas fórmulas.

Conclusión

Este es solo un enfoque, si prefieres otro, ¡compártelo publicando un comentario a continuación!

Si desea ver las fórmulas en acción, no dude en descargar el archivo de muestra:

• Muestra.xlsx