Gráfico tipo velocímetro en Excel

Presentación

En esta ocasión voy a describir la forma de crear un gráfico tipo "velocímetro", ideal para los paneles de control. Si bien hoy día Excel nos ofrece un conjunto de íconos variado dentro del formato condicional, un gráfico como el que hoy explico es, a mi parecer, mucho más práctico.

Las últimas versiones de MS Excel ofrecen una forma de crear gráficos combinados pero esta característica no está aún disponible para la versión de Mac, por tanto, voy a emplear un proceso más estándar que puede ser usado tanto en Mac como en Windows.

Este gráfico es el resultado de mezclar dos gráficos básicos:

• Gráfico circular 
• Gráfico de anillo
  

 Desarrollo

En primer lugar vamos a crear la tabla con los parámetros principales de la "carátula", tal y como se muestra en la imagen.

 

Señalamos los datos del rango D5:D10 y creamos un gráfico de anillo, ahora giramos el objeto haciendo doble clic sobre el gráfico y en "Ángulo del primer sector" escribimos 270.
 
A continuación, quitamos los bordes y relleno del sector más grande, así como el título y leyenda del gráfico.

Finalmente, editamos los colores de cada sector restante para señalar los distintos niveles de la variable.

Ahora vamos a crear el indicador de nuestro gráfico:

Para esto, usaremos un gráfico circular con 3 sectores, comenzamos creando un rango auxiliar (en este caso lo he incluído en el rango N17:N19 con la siguiente estructura:

• La celda N17 apunta hacia la celda C2 (donde escribimos el valor a representar)
• La celda N18 es auxiliar para el trazo y le hemos asignado un valor arbitrario sólo para poder visualziar el sector y editarlo.
• La cel ca N19 contiene la siguiente fórmula: 2*D10 - N17 (el tamaño del sector variará de acuerdo al valor escrito por el usuario, permitiendo el desplazamiento del indicador)   

Nuevamente, vamos a modificar el gráfico obtendo:

• Giramos el gráfico 270°
• Eliminamos el relleno y bordes de los sectores gris y azul
• Al sector más pequeño hacemos los bordes en color gris o negro y su valor (celda N18) lo hacemos 0.
• Quitamos el relleno del gráfico

Finalmente debemos superponer ambos gráficos.

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 deseo una buena semana.

Cálculo con Excel del ISR para Personas Físicas con Actividad Empresarial.

Aclaración

El contenido de esta publicación se enfoca únicamente en una posible forma de emplear Excel para calcular el Impuesto Sobre la Renta para Personas Físicas con Actividad Empresarial, los pormenores, fundamentos legales, teóricos, etc. deberán ser consultados con especialistas del área contable o fiscal.

Desarrollo

El cálculo del ISR, si bien es sencillo, no deja de ser un poco laborioso. Ya sea que contemos con un contador o no, para el cumplimiento de nuestras obligaciones fiscales, considero interesante conocer la dinámica del cálculo.

El cálculo del ISR involucra el uso de tablas año con año y publicadas en el Diario Oficial de la Federación. Las de este año (2016) pueden ser consultadas en http://www.sat.gob.mx/informacion_fiscal/tablas_indicadores/Documents/Tarifa_pprov_retenciones_2016.doc.

La imagen nos muestra la estructura de las tablas usadas en el cálculo, los valores en estas tablas varían de mes a mes.

Para facilitar nuestro calculo deberemos incluir la columna mes el principio de la tabla, quedando la estructura como sigue:

Para el cálculo del ISR nos apoyamos en la siguiente estructura:

En esta estructura podemos identificar tres de las columnas de las tablas para el cálculo del ISR.

Obtención de los valores.

A primera vista, podríamos suponer que el uso de la función buscarv o buscarh podrían servir, sin embargo, éstas funciones buscan un valor único y devuelven, si existe, un solo valor, en este caso, tenemos dos parámetros de entrada (Mes e Ingresos).

La función que emplearemos para resolver esto es DBEXTRAER, su sintaxis es la siguiente:

BDEXTRAER(Base_de_datos,Nombre_de_campo,Criteros)

• Base_de_datos: Se refiere al rango donde se encuentra la información a consultar (incluye la fila de los encabezados de la tabla)

• Nombre_de_campo: El nombre, entre comillas, del encabezado de la columna cuyo valor desea extraerse

• Criterios: El rango que contiene los criterios.

 Ejemplifiquemos para facilitar la explicación.

Supongamos el siguiente escenario.

Se desea calcular el ISR a pagar el mes de enero con ingresos de 19,000.00 sin deducciones.

Preparemos la siguiente plantilla:

Las fórmulas de las celdas H5, H6 y H7 serán:

Como ya se explicó, el primer parámetro corresponde a la base de datos, en este caso el rango G2 a E133 pues es dónde están todos los factores de las tablas para el cálculo. En segundo lugar, está el campo cuyo valor se desea extraer (“Límite inferior”, “por ciento…” y “cuota fija” respectivamente), finalmente los criterios. 

Veamos la siguiente imagen para comprender mejor la lógica de los criterios:

 

La función BDEXTRAER buscará todos los registros donde:

• MES = 1,

• Límite superior sea mayor o igual a 19000 (marcados en gris)

• Límite inferior sea menor o igual a 19000 (marcados en rosa)

La fila donde todos estos valores convergen es de dónde extraerá los valores buscados.

Con los valores obtenidos, completamos nuestra estructura del cálculo, quedando ésta como sigue:

De esta forma, hemos calculado el monto a pagar por concepto de ISR.

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 deseo una buena semana.

Funciones Definidas por el Usuario en Excel

Funciones Definidas por el Usuario -UDF (User Defined Functions)

Sabido es que MS Excel nos ofrece una gran cantidad de funciones propias, clasificadas en diferentes categorías como como matemáticas, búsqueda, referencia, lógicas, texto, etc. Sin embargo, en nuestra labor diaria nos vemos obligados a escribir fórmulas propias para cálculos diversos - algunos sencillos, otros poco más complejos.

Las funciones definidas por el usuario (UDF) son funciones escritas en VBA (Visual Basic for Applications) que permiten ampliar las posibilidades de Excel. Estas funciones estarán totalmente customizadas o personalizadas a nuestras necesidades.

Las UDF pueden ser tan simples o complejas como se requiera, a continuacion se muestra un ejemplo:

Public Function tarifaISRMensual(basePago As Double, mes As Integer) As Double 
Dim encontrado As Boolean 
encontrado = False 
For r = 2 To 133 
     If Worksheets("tablas").Cells(r, 1).Value = mes Then 
          If Worksheets("tablas").Cells(r, 2).Value > basePago Then 
               tarifaISRMensual = 
                    Worksheets("tablas").Cells(r - 1, 4).Value 
               encontrado = True 
               Exit For 
          End If 
     End If 
Next 
If Not encontrado Then 
     tarifaISRMensual = 0 
End If 
End Function
 
Como podemos ver, las UDF no se refieren únicamente a simples fórmulas aritméticas, pueden contener estructuras más complicadas como cíclos, condicionales, e inclusive llamadas a otras UDF.

¿Por qué usar UDF?

En primer lugar, la simplificación de cálculos. Muchas veces, para poder obtener un resultado debemos apoyarnos en varias fórmulas involucrando diferentes celdas, y si estos cálculos deben repetirse a lo largo de toda una tabla, la complejidad y desempeño de la hoja, disminuyen, con las UDF el desempeño no disminuye pues los cálculos se desarrollan en un mismo bloque.

En segundo lugar podemos mencionar la seguridad. Varios negocios requieren que sus procesos, fórmulas, parámetros, etc., sean confidenciales, empleando UDF podemos ocultar los cálculos, parámetros y métodos involucrados.

Finalmente, el mantenimiento, es decir, cambios, correcciones, mejoras que deban o puedan hacerse al cálculo. Cuando detectamos algún error de cálculo o alternativa para mejorarlo, el rastreo del error se complica dependiendo del número de celdas involucradas en el cálculo, con las UDF, el cálculo se realiza en un mismo bloque y la detección del fallo o punto de mejora se detecta más fácilmente.

¿Cómo se crea una UDF?

• [ALT] + [F11] para abrir el editor VBA
• Elegir "Insertar  -> Módulo"
• En el área de trabajo podremos escribir nuestras UDF.

Toda UDF tiene la siguiente estructura:

Public Function Nombre_Funcion (Lista_Parametros) As Tipo_Devuelto

     --- Código de la función ---

End Function


Tipo_Devuelto
Determina el tipo de dato que la función va a devolver es decir, el tipo de respuesta que obtendrá el usuario, los principales tipos son:

• Integer. Números enteros
• Double. Números reales
• String. Cualquier combinación de números, letras y signos
• Boolean. Falso / Verdadero

Lista_Parametros
Se refiere a los datos que requiere la función para realizar los cálculos, la sintáxis para definir dichos parámetros es:

Nombre_Parametro As Tipo_Dato

Donde los valores de Tipo_Dato son similares a los definidos en Tipo_Devuelto. Veamos el siguiente ejemplo:

Public Function VPN (tasaMinimaAceptable as double, Periodo as Integer, flujoNetoEfectivo as double) as Double 
                          VPN = flujoNetoEfectivo / (1+tasaMinimaAceptable)^Periodo 
End Function

Entendamos el código:

• El código anterior genera una función llamadaVPN (Valor Presente Neto)
• La función recibe tres parámetros:
•  tasaMinimaAceptable de tipo "Double"
• Periodo de tipo "Integer"
• flujoNetoEfectivo de tipo "Double"
• La función devuelve al usuario un dato de tipo "Double"
• La línea VPN = flujoNetoEfectivo / (1+tasaMinimaAceptable)^Periodo indica que a la función VPN se le asigna el resultado de la operación correspondiente y puede "devolverlo".

 Una vez hecho esto, podremos llamar a nuestra función desde la hoja de Excel como a cualquier otra función.

 Importante. Los libros que contengan UDF deberán ser guardados como "Libro de Excel Habilitado para Macros (*xlxm)"

 

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 deseo una buena semana.

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.