Auditoría de Fórmulas en Excel

En el día a día con Excel, es común enfrentarnos a la tarea de revisar que las referencias en las fórmulas sean correctas, para ayudarnos en estas tareas Excel nos ofrece un conjunto de herramientas muy útiles.

En la pestaña "Fórmulas" buscarmos el grupo "Auditoría de Fórmulas"

Rastreo de precedentes / dependientes

Estas primeras dos opciones nos pemiten observar las relaciones entre las celdas a partir de flechas que parten de la celda dónde se ubica el cursor.

Rastrear Precedentes nos mostrará todas las celdas que alimentan a la fórmula en cuestión, es decir, de dónde toma los datos para el cálculo.

Rastrear Precedentes

La opción Rastrear Dependientes señalarálas fórmulas que dependen del dato o fórmula indicada.

Rastrear Dependientes

Una vez señalada la celda cuyo contenido deseamos auditar hacemos clic en la opción "Rastrear Precedentes" o "Rastrear Dependientes" según corresponda. Si se vuelve a presionar la misma opción, el rastreo continuará hasta no encontrar más dependencias o precedencias. Es común que las fórmulas tengan referencias en otras hojas o incusive archivos, éstas se denotarán con la siguiente imágen:
 

Referencias Externas

Una vez mostradas las relaciones, podemos hacer doble clic sobre la flecha y ubicará el cursor en la celda referida. Para las referencias externas, aparecerá el cuadro de la herramineta "Ir a", en este caso hacemos doble clic en la referencia del panel superior para que Excel nos lleve a la celda correspondiente.

Evaluar Fórmula

 Otra herramienta que nos ofrece Excel está en la opción "Evaluar Fórmula", ésta nos ayuda a visualizar paso a paso, el comportamiento de una fórmula, la siguiente animación ayudará a clarificar esto:

Una vez ubicado el cursor en la celda con la fórmula a evlauar, podremos, con el botón "Paso a paso para entrar" observar los valores que están alimentando a nuestra fórmula. Así seremos capaces de visualziar claramente el posible error en ésta.

Espero esto sea de utilidad para ustedes, les invito a comentar sobre el tema y dejar sus dudas aquí mismo o envíen un mensaje a través del formulario de contacto.

Les agradezco su atención y deseo una buena semana.

Uso de botones de opción en Excel

En una publicación anterior describí la forma de agregar controles especiales de formulario en una hoja electrónica. En esta ocasion explicaré cómo configurar y usar los botones de opción.

Una vez dibujados los controles, el proceso de configuración es prácticamente el mismo para todos los controles: deberemos cambiar la etiqueta que indique al usuario, el valor que representa y la celda de referencia, para esto hacemos clic con el botón derecho sobre el control dibujado y elegimos la opción "Formato de Control", y obtendremos una ventana similar a la siguiente:

 

• La opción "Vincular con la celda" indica en qué celda se leerá el valor del control.
• La pestaña "Texto Alternativo" nos permite cambiar la etiqueta del control. 

Veamos la siguiente situación
 

El funcionalidad de esta pequeña plantilla consiste en modificar el valor de una celda (C5) dependiendo de la opción elegida por el usuario.

Observemos que al hacer clic en cada opción el valor de la celda A6 también cambia, ésta es la celda vinculada de los botones de opción. El valor mostrado en ésta corresponde a un valor asignado por Excel a cada opción según se han ido dibujando (Niños: 1, Jóvenes: 2, Adultos: 3).

¿Cómo obtenemos los "costos de inscripción"? Estos valores deberán estar en alguna tabla o catálogo, ya sea en la misma hoja o en alguna otra, para este caso supongamos que la lista está en el rango AA1:AA3

La celda C5 deberá leer el valor de AA1, AA2 o AA3 ¿cómo en una celda indicamos la referencia a dichas celdas?. Una alternativa simple es usar la función INDIRECTO como se muestra a continuación:

= INDIRECTO ("AA" & A6)

De esta forma, leemos el valor de la celda de referencia (A6) y lo concatenamos (juntamos) con la referencia de la columna "AA", la función INDIRECTO interpreta el nombre de la celda y devuelve el valor que haya en ella.

Espero esto sea de utilidad para ustedes, les invito a comentar sobre el tema y dejar sus dudas aquí mismo o envíen un mensaje a través del formulario de contacto.

Les agradezco su atención y deseo una buena semana.

Controles de Formulario en Excel

Presentación

Excel es más que una herramienta para ordenar, filtrar y graficar los datos, ofrece muchas herramientas para ayudarnos a procesar y controlar la información de manera ágil, entre otras contamos con ciertos controles avanzados como botones, listas desplegables, opciones, etc.

Con estos controles podremos por ejemplo, permitir que el usuario elija valores de entre una lista de elementos, mostrar u ocultar datos complementarios en base a criterios proporcionados por el usuario y hasta ejecutar macros haciendo clic en algún botón.

En este artículo procuro, de forma breve, mostrar la configuración y uso de los controles de formulario de Excel.

¿Dónde están los controles de formulario?

Los controles de formulario se encuentran en la pestaña "Desarrollador" (o "Programador" - Dependiendo la versión de Excel).

Habilitando la pestaña "Desarrollador"

1. Hacemos clic con el botón derecho sobre alguna pestaña.

2. Elegimos la opción "Personalizar la cita de opciones"

3. En el recuadro derecho señalamos el elemento "Desarrollador" o "programador"

4. Clic en "Aceptar"

5. Ahora podemos ver la pestaña que necesitamos.

Los controles

En la pestaña "Desarrollador" en la sección "Controles" encontramos en menú "Insertar", aquí tenemos los controles que necesitamos para ampliar la funcionalidad de nuestra hoja electrónica.




Para insertar los controles, bastará con señalar el que se desea usar y "dibujarlo" sobre la hoja como lo muestra la siguiente animación.

A continuación una breve descripción de cada uno de estos controles:

 Botón. Permite ejecutar una macro.

Lista. Muestra varios valores, de los cuales puede elegirse uno o varios dependiento la necesidad.

Cuadro combinado. Es una lista "desplegable". Al diferencia de la lista, los valores en este control permanecen ocultos hasta que el usuario hace clic en la flecha de la derecha.

 Control de números. Permite incrementar o decrementar valores numéricos en una celda indicada.

Botón de opción. Sirve para dar al usuario la oportunidad de elegir una de entre varias alternativas.

Casilla de verificación. Muestra alternativas al usuario para que este pueda, o no, elegirlas.  

 Cuadro de grupo. Engloba varios controles en una misma sección de la hoja.



En las siguientes publicaciones mostraré algunos ejemplos de aplicación para estos controles.

Espero esto sea de utilidad para ustedes, les invito a comentar sobre el tema y dejar sus dudas aquí mismo o envíen un mensaje a través del formulario de contacto.

Les agradezco su atención y deseo una buena semana.

Lenguaje R

 ¿Qué es R?

R es un lenguaje y software estadístico distribuído bajo la licencia GNU. Originalmente creado por Ross Ihaka y Robert Gentleman del Departamento de Estadística en la Universida de Auckland, hoy día es un esfuerzo de colaboración internacional que permite a los usuarios contar con paquetes o extensiones de diversas áreas como:

• Economía y finanzas
• Biología
• Bioquímica
• Simulación
• Geolocalización, entre otras.

 ¿Dónde se obtiene?

R puede ser descargado directamente en https://cran.r-project.org/ y está disponible para Linux, Mac y Windows, los paquetes adicionales o extensiones pueden ser descargados desde https://cran.r-project.org/web/packages/, también pueden ser descargado desde la consola de R con la línea:

install.packages();

 Mi experiencia con R

Durante mucho tiempo trabajé emplenado SPSS o, en el peor de los casos, los complementos de análisis de datos en Excel, sin embargo, en un proyecto me tocó trabajar con R, al principio no me sentía cómodo con el entorno de trabajo pues es bastante austera (como puede verse en la imagen) sin embargo, existen un par de productos de terceros que permiten interactuar de manera mucho más amena:

• RStudio
• R Commander.

RStudio está orientado principalmente al desarrollo de procesos y la automatización, por su parte R-Commander, aunque pemrite el desarrollo de funciones y procedimientos propios, se orienta más hacia el análisis de datos. Por mi línea de trabajo, mi elección es RStudio.

Fuente: http://www.rcommander.com/

Fuente: http://rprogramming.net/download-and-install-rstudio/ 

En las próximas entregas profundizaré un poco más sobre la forma de trabajar con este lenguaje. Estoy seguro que será de interés para ustedes.

Les invito a comentar sobre el tema y dejar sus dudas e inquietudes aquí mismo o envíen un mensaje a través del formulario de contacto.

Les agradezco su atención y que tengan una buena semana.

Habilitar la herramienta de Análisis de Datos en Excel para Mac.

Antes que todo, debemos revisar la versión de Excel que tenemos instalada en Mac. Las herramientas de Análisis de Datos y el Solver, no están disponibles para todas las versiones. La siguiente tabla resume esto:

Versión	Análisis de Datos	solver
Excel 2016	Si	No
Excel 2011	No	Si*
Excel 2008	No	No

* Service Pack 1 (version 14.1.0)

Instalación de Herramientas de Datos y Solver (Excel 2016) 

1. Clic en "Herramientas" (Tools).

     2. Seleccionar "Complementos" (Add-Ins).

     3. Elegir la opción Herramientas para Análisis de Datos o Solver, según se requiera.

     4. Clic en "Aceptar".

Las nuevas opciones aparecen en el menú Datos.

Para las versiones donde no están disponibles estas herramientas, Microsoft recomienda los siguientes productos desarrollados por terceros:

• StatPlus 
• Solver 2008 para Mac

Espero esto sea de utilidad para ustedes, les invito a comentar sobre el tema y dejar sus dudas aquí mismo o envíen un mensaje a través del formulario de contacto.

Les agradezco su atención y deseo una buena semana.