Nunca te ha pasado, bien sea por que alguien te lo pregunta o por el trabajo propio, tener que analizar las fórmulas y funciones para ver cómo está estructurada la hoja de cálculo.
Cuando decimos estructura de una hoja de cálculo, nos referimos a cómo unos datos de entrada son utilizados y procesados para arrojar ciertas salidas, ciertos resultados.
A mí me ha pasado, pongo en modo edición fórmula por fórmula y de esta manera voy analizando cómo está relacionado cada celda o rango para identificar que datos sirven como entrada, que fórmulas y funciones las procesan para obtener los resultados.
Lo sé, no es la forma más eficiente de hacerlo. pero es válido interpretar la estructura de una hoja de cálculo de esta manera y, me atrevo a decir que la gran mayoría lo hace frecuentemente. Sin embargo, es completamente cierto, contamos con las herramientas de auditoría de fórmulas para que este trabajo sea mucho más sencillo e incluso divertido.
En este artículo no vamos a profundizar en cada comando y opción de la herramienta de auditoría de fórmulas, en lugar a ellos vamos a explorar de manera general las distintas funcionalidades.
Para poner en un contexto más apropiado, vamos auditar (a nivel de fórmulas) un sencillo análisis de riesgo, por lo tanto veamos rápidamente como hacer este análisis sencillo.
Descripción del Riesgo
Para poder interpretar numéricamente algún riesgo que siempre va asociado a un acto o acontecimiento, lo que debemos hacer es listar todos los posibles resultados de dicho acto, así como determinar y asociar la probabilidad de que se produzca cada uno.
– Qué tal un ejemplo?
Bien.
Supongamos, por ejemplo, que estamos evaluando la posibilidad de invertir en el alquiler de una tienda de comidas rápidas dentro de un campo de fútbol privado para el «próximo sábado». Si ese día no llueve se generarían en utilidad $250 USD, por el contrario si resulta ser un día lluvioso, entonces se generará una talidad de $120 USD, el análisis meteorológico indica que para el fin de semana es dos veces más probable que no llueva.
Vamos a notar el acontecimiento con la letra: X, entonces tenemos:
La lista de resultados para este acontecimiento son dos:
La Probabilidad se refiere a las posibilidades de que se produzca un resultado, en nuestro ejemplo, la probabilidad de que no llueva es dos veces más por lo que corresponde a 2/3, mientras de que llueva 1/3, estos es así porque recordemos que las probabilidades de todos los acontecimientos posibles deben sumar 1, los datos quedarían así:
Algunas personas distinguen entre incertidumbre y riesgo. La incertidumbre puede referirse a situaciones en la cuales hay muchos resultados pero sus probabilidades son un misterio. El riesgo, por otra parte, se refiere a las situaciones que se puede determinar la lista de resultado y sus respectivas probabilidades. A mí personalmente me gusta hacer esta diferenciación.
Esperanza Matemática
La esperanza matemática (también llamada: valor esperado, media poblacional o simplemente esperanza) No es más que la tendencia central de los datos, a que número «convergen» todo el conjunto de datos.
– Hmmmmm no es tan claro, ¿Verdad?
Lo que quiero decir, es que el valor esperado es el número al cual todo un conjunto de datos está más cercano, en otras palabras, el rendimiento del valor que esperamos en promedio. Por lo tanto representa la cantidad media que se espera como resultado de un acontecimiento definido.
Tomemos el ejemplo anterior, el cual tiene dos resultados posibles: No Lluvia genera un rendimiento de $250 USD el sábado, mientras que si llueve genera un rendimiento de $ 120 USD.
Si representamos la probabilidad con la abreviación Pr, entonces el valor esperado para esta situación se cálcula:
Cuando tenemos n resultados el esperado se calcula:
Utilizando la notación que definimos con anterioridad y utilizando notación sigma podemos resumir la esperanza (E) de un acontecimiento (X) con (n) resultados posibles, de la siguioente manera:
Cuando Todas las Esperanzas se Han Ido
Extendamos nuestro escenario un poco más de la siguiente forma:
– Ejemplo extendido
Ahora tenemos la posibilidad de hacer nuestra inversión de alquiler de un puesto de comidas rápidas dentro de un campo de fútbol privado para el próximo sábado en dos ciudades diferentes, adicionalmente tenemos 3 posibles resultados de clima para ese día, estos son: Soleado, Lluvioso y nublado.
Parala ciudad 1 sabemos que si el días es soleado tendremos una ganancia de $250USD, si es lluvioso $120USD y si es nublado $200USD. Contamos con las probabilidades de cada uno para ese día, así soleado 0.6, lluvia 0.1 y nublado 0.3.
Por otra parte en la ciudad 2, los posibles resultados son los mismos, sin embargo, la probabilidades cambian de la siguiente manera: soleado 0.4 con lo cual se obtendría una ganancia de $300USD, Lluvia 0.4 con una ganancia de $140USD y nublado 0.2 con una ganancia de $230USD.
En esta situación lo que se nos ocurre es calcular la esperanza de cada ciudad, con lo cual el mayor resultado sería la opción más viable para hacer nuestra inversión, no obstante, si calculamos las dos esperanzas cuidadosamente obtenemos:
¿Qué se puede hacer en esta situación?, la esperanzas son iguales, ¿no podremos determinar de acuerdo a algún criterio cuál es la mejor alternativa?
Ó
Ya que las esperanzas son iguales: ¡Todas las esperanzas se han ido! y debemos escoger al azar
Evidentemente no,
Cuando todas las esperanzas se han ido, queda la VARIABILIDAD
Además no es la única opción, pero es la más fundamental a evaluar: La Variabilidad es el grado en el que difieren los resultados posibles de un acontecimiento, por lo tanto, la variabilidad de los acontecimientos posibles son diferentes. Se mide reconociendo que las grandes diferencias entre los rendimientos reales y los esperados (positivos o negativos) señalan que el riesgo es mayor.
Ciudad 1 | Soleado: $250USD – $222USD = $28USD
Ciudad 1 | Lluvia: $120USD – $222USD = $-102USD
Es importante tener en cuenta que la desviación por sí misma no es una medida de variabilidad puesto que algunos son positivos y otros negativos, para esto tenemos que elevar al cuadrado cada desviación y así tendremos como resultados siempre números positivos.
Con lo anterior podemos calcular la desviación típica que corresponde a sacar la raíz cuadrada de la sumatoria de todas las desviaciones de un acontecimiento, matemáticamente su notación sería:
«El Arsenal» de Auditoría de Fórmulas en Excel
Ahora que sabemos como hacer un análisis de riesgo bastante sencillo, supongamos que el escenario anterior (el da las dos ciudades) le pedimos a alguien que nos realiza dicha tarea de forma detallada, es otros términos, que nos presente el análisis de riesgo paso a paso y cuál sería la opción óptima en lugar de un resultado «seco» (ya que en Excel podemos hacer esto con un par de fórmulas bastante rápido, pero es útil la manera desglosada para poder presentarla ante un compañero o grupo de personas)
– Entrega
El siguiente cuadro muestra lo que nos han entregado del análisis de riesgo:
Como se puede observar en la imagen el requisito de hacer el análisis de riesgo de forma detallada para una explicación a terceros parece ser acertada, a pesar de esto, es inmensamente notorio que contiene problemas, el hecho de ver errores ya nos obliga devolver el trabajo o revisarlo y corregir nosotros mismos.
Vamos a optar por revisarlo y arreglarlo nosotros mismos por lo que requiere una inspección y verificación de la estructura de la hoja de cálculo para hacer el análisis de riesgo.
– Grupo Auditoría de Fórmulas en Excel
Por fortuna, para auditar fórmulas/funciones y entender la estructura de una hoja de cálculo contamos con un grupo de comandos que se encuentran en la pestaña FÓRMULAS, grupo AUDITORÍA DE FÓRMULAS. Ver la imagen a continuación.
Para la situación planteada (El análisis de riesgo) utilizaremos los comandos:
- Mostrar fórmulas
- Comprobación de errores
- Rastreo dependientes
- Rastreo precedentes
Los comandos Evaluar fórmula y Venta de inspección así como otras técnicas los exploraremos en próximos artículos.
«La hora de la verdad» Estructura de la hoja de cálculo
Antes de siquiera pensar en corregir los errores tipo #N/A que se ven el archivo, lo primero que debemos hacer es entender la estructura, para ello debemos conocer en que celdas hay fórmulas y funciones, la formas más eficiente de hacer esto es pulsando clic en el comando Mostrar fórmulas del grupo auditoría de fórmulas, véase la imagen
Como su nombre lo indica este comando permite ver las fórmulas en las celdas en lugar del resultado de las mismas que es como normalmente lo vemos, ver imagen debajo. Para empezar a revisar debemos identificar la celda que contenga la primera fórmula más a la izquierda y más arriba (Estos es así porque la gran mayoría de personas empiezan a crear sus hojas de cálculo con un flujo de lectura de esta manera – Izquierda a Derecha, Arriba Abajo – ) Para nuestra situación la primera fórmula que vemos está en la celda C10 con BUSCARV, ver imagen:
Para empezar necesitamos entender las entradas, los datos que alimentan las fórmulas y funciones de donde proviene, para ellos nos posicionamos en la celda identificada C10 y pulsamos clic en el comando restreo de precedentes, ver imagen:
Rastrear Precedentes: Este comando muestra mediante flechas, iconos y recuadros todas aquellas celdas que se necesitan para calcular el valor de la celda seleccionado, a saber todas aquellas celdas que necesita los argumentos de la función BUSCARV para ejecutarse apropiadamente
Para la celda C10 se necesita el valor de la celda C8 señalado con un punto, así como la celda B2 que también está señalada con punto y el rango B2:D4 que esta resaltado con el recuadro azul, la flecha señala la celda que necesita de los precedentes.
Con solo el análisis de la celda C10 podemos inferir que la tabla: • Datos y Esperanza Matemática está buscando los valores de las distintas ciudades para darles una organización diferente (Tal vez más apropiada para presentación) Además identificamos que los datos de entrada son las dos tablas en la parte superior y que se procesan en primera medida por la función BUSCARV con el objetivo de reorganización.
Ahora si nos mantenemos en la celda C10 y pulsamos clic en el comando rastrear dependientes
Rastrear dependientes: Este comando muestra mediante flechas, iconos y recuadros todas aquellas celdas que necesitan de la celda seleccionada para calcularse
Pongamos nuevamente el modo mostrar fórmulas y clic en precedentes y dependientes una vez respectivamente
La flecha dependiente señala a la celda I10, por lo que se puede notar que las probabilidades están multiplicando a las ganancias de cada uno de los posibles resultados, es decir, como lo indica el título está calculando la esperanza matemática, además si observamos detenidamente la fórmula vemos que esta planteada de forma correcta de acuerdo a las etiquetas de la tabla: C10*D10 que corresponde a la probabilidad de soleada por la ganancia de soleada, más la probabilidad de lluvia por la ganancia y finalmente la suma del producto de la probabilidad de dúa nublado con su ganancia correspondiente
Haciendo la misma observación en la fila 11 podemos ver que también estan plateadas la fórmulas de forma correcta
En conclusión para esta primera parte la tabla datos y esperanza toma los valores de las tablas en la parte superior mediante la función BUSCARV para organizar los datos de cada ciudad en una fila cada una, para así al final de ellas podar calcular la esperanza
– Niveles de Depedencia «rastreo»
Quitemos el modo mostrar fórmulas y dejemos nuestra celda en C10, como ya hemos pulsados una vez clic en dependencia: ¿Qué pasa si volvemos a pulsar clic en este comando nuevamente?, esto:
Ahoramuestra aquellas celdas que necesitan de la señalada por la flecha azul en primer lugar (I10) – el primer nivel – para calcularse, dicho de otro modo, aquellas celdas que necesitan de I10 para calcularse, donde está la esperanza matemática; de esta manera podemos ir viendo el flujo de cálculo en el cual primero se organizan los datos, después se calcula la esperanza para posteriormente calcular las desviaciones que son las etiquetas que tienen la celdas señaladas en el segundo nivel de dependencia.
Ahora debemos comprobar que la fórmula de desviación este correcta, para esta ocasión solo posicionémonos en la celda D16 (la primera que señala una de las flechas) y pulsemos la tecla F2 para quedar en modo edición y así ver la fórmula en dicha celda únicamente, véase la imagen
Analizando la fórmula en D16 vemos que la desviación está calculada correctamente e igualmente para las demás desviaciones, clic nuevamente en rastrear dependencia para detectar dependencia para ver otro nivel
En esta ocasión se puede ver directamente que en este nivel solamente se está trayendo el valor de las desviaciones a la taba, si pulsamos clic en rastrear dependencia nuevamente vemos que indica la celda inmediatamente a la derecha, que tiene como etiqueta cuadrado de la desviación, si ponemos en modo edición la celda D23 vemos que está hallando el cuadrado de forma correcta
Recuerda siempre dejar la Celda en la primera fórmula, en este caso C10, cuando se pulse clic en el comando rastrear dependencia
Si pulsamos nuevamente en rastrear dependencia vemos que señala ahora a celda I23 que en su etiqueta indica que se calcula al desviación típica, analizando las flechas y la fórmula (se puede ver en la barra de fórmulas si dejamos al celda activa en I23) vemos que está sacando la raíz cuadrada a la suma desviaciones, lo cual es completamente correcto, ver imagen debajo
Finalmente si pulsamos clic nuevamente en rastreo de dependencia (dejando al celda activa en C10) vemos que señala a la celda D27 que contiene la fórmula: =SI(I23<I24;»Ciudad 1″; «Ciudad 2») dónde básicamente indica que escriba la ciudad con menor desviación típica
Así hemos concluido que las fórmulas están bien planteadas para el análisis de riesgo, también hemos verificado la estructura de la hoja (como es su lectura). No obstante aún no hemos detectado a que se deben los errores #N/A.
También podemos aplicar niveles de dependencia con el comando rastrear precedcentes, podemos también una vez terminado nuestra auditoría quitar toda las flechas utilizando el comando del mimos nombre en el grupo de aduitoróa de fórmulas o si lo preferimos podemos quitar flechas por niveles.
Comprobación de Errores
Para esto dejamos la celda activa en C10 y desplegamos las opciones de comprobación de errores, allí nos encontramos con tres opciones: Comprobación de errores, rastreo de errores y referencias circulares; para este post solo veremos Comprobación de errores, pulsamos clic en el
Con esto se despliega un cuadro de diálogo que nos indica porque el error, véase la imagen a continuación.
El texto dice que error de valor no disponible para la función. Conociendo la función BUSCARV sabemos que esto se debe a que no encontró ningún coincidente, por lo que si nos ubicamos en la celda del primer #N/A y analizamos la función:
=BUSCARV(E$8;$F$2:$H$4;2;0)
Vemos qque en el argumento valor_buscado hace referencia a la celda E8 que dice Lluvioso, mirando el segundo argumento de esta función vemos que hace referencia al rango $F$2:$H$4, mirando detenidamente la primera columna vemos que dice Lluvia en vez de lluvioso, por lo que notamos que debido a esto no está encontrando el valor buscado, so lo corregimos automáticamente todo el análisis se arregla.
Quiere decir que para nuestro análisis de riesgo la alternativa más adecuada después de asegurar que tanto la estructura como las fórmulas utilizando las herramientas de auditoría de fórmulas para tomar una decisión confiable, indica que la ciudad 1 es la menos riesgosa, por lo tanto se debería invertir en esta.
Existen otros comandos en el grupo de auditoría de fórmulas que iremos explorando poco a poco y de forma detallada, es más quiero dedicar un post completo Evaluar Fórmula, ya que es muy poderoso para detectar errores, corregir problemas, detectar inconsistencia, etc. Adicionalmente tenemos el Add-in Inquiere que nos permite entender la estructura de una hoja de cálculo e incluso de un libro de una manera muy sencilla, además extienda nuestras posibilidades para auditar fórmulas en Excel, sino embargo dejaremos este tema para un próximo porque sino no extendemos mucho.
Por lo pronto me gustaría escuchar de ti en los comentarios cuales son los aspectos que más utilizas de la auditoría de fórmulas y que alternativas usas para entender una hoja de cálculo creada por otra persona.
– Eso es todo por mi el día de hoy, hasta la próxima oprtunidad