|VIDEO| Crear gráfico de Velocimetro - simples pasos en Excel!

Veamos como realizar un gráfico de velocímetro con Excel, que nos permite visualizar rápidamente un dato dentro de una escala de valores.

Como podemos observar en los diferentes gráficos que trae excel, no viene directamente la forma de velocimetro, hoy vamos a realizarlo muy sencillo.


Pasos


1. Primero debemos tener en excel definidos los porcentajes, de nuestro fondo en este caso los colores rojo, amarillo y verde. Además vamos a agregar una porcion adicional que será la sumatoria de estos porcentajes anteriores así.

 2. Seleccionamos estos valores de B1:B4 y clic en Excel - Pestaña Insertar - Gráfico - Anillo.

 Nos quedará algo así

 

Vamos a quitar las series y el titulo. Solo es seleccionarlos y presionamos suprimir. o clic derecho eliminar. 

 3. El siguiente paso es cambiar la posicion de los anillos, para esto damos clic encima del grafico, y luego

 En la parte donde dice Angulo del primer sector (esta opcion se habilita a la derecha de tu excel, inmediatamente hagas clic encima del gráfico), le vamos a colocar 270° Nota: Puedes ir cambiando por otros valores para que veas como se van organizando los anillos.  En tamaño del agujero vamos a color 50%

4. Ahora le damos clic solo en la parte de abajo en este caso color mostaza, y le cambiamos el color a blanco, este es el truco para desaparecer la parte inferior del anillo.

 

5. Ahora de la misma manera le asignamos los colores a cada sesion, a la primera rojo, a la segunda amarillo, a la tercera verde.

6. Ahora para la aguja del velocimetro, debemos crear otro gráfico, antes vamos a definir unos valores de mi gráfico.

El valor, será la posicion que queremos de la aguja, el ancho será el grosor de la aguja, y el campo blanco, como queremos que esté igual a los valores establecidos en la primera parte de los colores, sera la suma de lo establecido en el paso 1, es decir la suma de b1 a  b4 y le restamos el valor y el ancho.

7.  Vamos a insertar un nuevo grafico. Seleccionamos I1:I3 y clic en Excel - Pestaña Insertar - Gráfico - Circular. Luego tambien eliminamos titulo y series, quedaria algo asi.

8. Ahora vamos a colocar la misma posicion el grafico anterior para esto damos clic encima del grafico, y luego en la parte donde dice Angulo del primer sector (esta opcion se habilita a la derecha de tu excel, inmediatamente hagas clic encima del gráfico), le vamos a colocar 270°.
9. Ahora a la parte gris y azul, le vamos a asignar relleno blanco y sin lineas. 

10. A la parte naranja que es nuestra aguja, le vamos a asignar el color azul y sin lineas

11. En esta parte a este grafico le vamos a asignar en las propiedades que no tenga fondo, para esto clic encima del grafico, y luego en la pestaña Formato - opcion relleno de la forma, clic en SIn Relleno.

12. Seleccionamos el grafico 1 junto al grafico 2, luego en la pestaña Formato - Alinear clic en Alinear a la izquierda y Alinear a la parte Superior, Luego en esa misma opcion clic en agrupar.

Y ya tendremos listo el velocimetro.


Nota: Si hacemos clic en el grafico de colores, pestaña Formato, clic en formato de la forma, Bisel y aqui le podrás dar otros formatos interesantes.

VER VIDEO A CONTINUACION.

Clic aquí--> VIDEO

Nota: preferiblemente abrir con mozilla o habilitar complemento flash en su explorador.


Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

Convertir campo Timestamp a fecha "normal" | muy sencillo.

En algunas bases de datos las fechas se guardan en un formato que no es el habitual, un numero que es dificil descifrar a que fecha normal o real hacen referencia. 

Para dicho caso hemos de realizar un proceso en Excel por supuesto para traer la fecha legible a nuestro conocimiento.

 Si abres una tabla y te fijas en algún campo con forma de fecha, verá que los datos que contiene son un montón de números sin sentido llamados timestamp.

 Este timestamp o lo que traduce sello de tiempo se puede definir como el número de segundos transcurrido desde la medianoche del 1 de enero de 1970 hasta este momento.

Por ejemplo, si hoy es día 23 de junio de 2018, significa que han pasado 1529712000 segundos desde enero de 1970. Hagamos el ejercicio en excel restemos las fechas esto nos da en dias. Y luego ese valor lo multiplicamos por 24*60*60 que corresponde a 24horas 60 min 60 segundos.

 Funciones: 

D3= DIAS(B3;C3)
E3= D3*24*60*60

 Ahora lo que queremos es teniendo ese valor de tiempo en segundos, volverlo al formato habitual de fecha

Al contrario aqui tomaremos el campo que tiene el formato en segundos, y le dividiremos por 60 segundos luego 60 minutos y luego 24 horas. A esto le sumaremos la fecha
Y el resultado será

otra forma

Aqui usamos esta funcion  =([@timestamp]/86400)+FECHA(1970;1;1)


 Recuerda  El resultado devuelto por la fórmula debes darle formato de fecha. Pulsa Ctrl + 1 y selecciona Fecha del cuadro de diálogo Formato de número.
   


Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

Usar datos de mi tabla dinámica - funcion IMPORTARDATOSDINAMICOS

Con esta funcion podremos extraer datos de resumen de una tabla dinámica.


 Nos será bastante útil cuando necesitemos hacer calculos posteriores con datos de la tabla dinamica.

 Una de las ventajas es que al momento en que se actualicen los datos de la tabla, los datos copiados también se actualizan.


 Sintaxis

IMPORTARDATOSDINAMICOS(camp_datos, tabla_dinámica, [campo1, elemento1, campo2, elemento2], ...)

La sintaxis de la función IMPORTARDATOSDINAMICOS tiene los siguientes argumentos:

• Camp_datos    Obligatorio. Es el nombre, entre comillas, del campo de datos que contiene los datos que desea recuperar.
  
• Tabla_dinámica    Obligatorio. Una referencia a cualquier celda, rango de celdas o rangos de celdas en una tabla dinámica. Esta información se usa para determinar qué tabla dinámica contiene los datos que desea recuperar.
  
• Campo1, Elemento1, campo2, Elemento2    Opcional. 1 a 126 parejas de nombres de campo y elemento que describen los datos que desea recuperar. Las parejas pueden estar en cualquier orden. Nombres de campo y los nombres de los elementos que no sean fechas y números están entre comillas.

 EJEMPLO

 Tenemos en esta tabla dinamica, sumatoria de ventas de dos meses, por vendedor y producto.

 
 Aqui vamos a usar el ejemplo para calcular el valor total (así se llama el campo de valor) con la siguiente formula,  =IMPORTARDATOSDINAMICOS("Valor Total"; $A$7)  la cual dará como resultado

 Y como podemos ver en la tabla dinamica inicial, es el resumen total.

Ahora si queremos mas de un filtro, tenemos que podemos escribir la funcion de la siguiente manera
=IMPORTARDATOSDINAMICOS("Valor Total"; $A$7; "Mes"; 3) y nos dará como resultado
 

294.357

 De esta misma manera podriamos agregar todos los filtros que necesitemos.

Tal vez le interese leer http://ideasdeexcel.blogspot.com/2014/08/desactivar-importardatosdinamicos-al.html

Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

Top 10 de Vendedores - Funcion Jerarquia

Para nuestro siguiente caso, tenemos le listado de vendedores, con sus respectivos valores totales de ventas.

Queremos detereminar un Top 10 de vendedores, para el informe mensual. Para esto usamos la funcion Jerarquia.equiv que nos devuelve la posicion o jerarquia de un numero dentro de un listado de numeros, veamos nuestro listado inicial.

EJEMPLO

A continuación en la columna C, vamos a usar la funcion jerarquia.equiv, para que me devuelva la posicion de cada una de las filas. usaremos la funcion en la celda C6, pero antes miremos la sintaxis de la función:

Sintaxis

JERARQUIA(número,referencia,[orden])

La sintaxis de la función JERARQUÍA.EQUIV tiene los siguientes argumentos:

Número:    Obligatorio. Es el número cuya jerarquía (clasificación) desea conocer.

Referencia     Obligatorio. Es una matriz de una lista de números o una referencia a una lista de números. Los valores no numéricos se pasan por alto.

Orden     Opcional. Es un número que especifica cómo clasificar el argumento número.

 Si omite el argumento orden o es 0 (cero), Microsoft Excel determina la jerarquía de un número como si la lista definida por el argumento referencia se ordenara en forma descendente.

Si el argumento orden es diferente de cero, Microsoft Excel determina la jerarquía de un número como si la lista definida por el argumento referencia se ordenara en forma ascendente.

 Veamos en C6

 =JERARQUIA.EQV(B6;$B$6:$B$36;0) en nuestro caso el número es B6, es el número que voy a evaluar, la referencia es la matriz o rango que contiene todos los numeros a evaluar, está con signo pesos porque deseo dejar la referencia absoluta, el orden  es cero, porque lo deseo descendente.

Teniendo este resultado podemos armar en una nueva tabla mi top 10. Observe muy muy bien, que la posicion me quedó en la tercera columna, dado que esto me afecta el proximo paso. En la F5 y G5 crearemos la tabla con las columnas Ranking y Vendedor.

Vamos a usar buscarv pero de derecha a izquierda, dado que nuestra posicion (jerarquia calculada antes) quedó en la tercera columna,, y buscarv normal nos exige que la columna a buscar esté de primera 
Puedes ver mas detalles en el vinculo a continuación http://ideasdeexcel.blogspot.com/2017/02/funcion-buscarv-de-derecha-izquierda.html 

Puedes ver mas el uso de BuscarX en  https://ideasdeexcel.blogspot.com/2021/11/video-como-utilizar-buscar-v-h-x-en.html  esta función es poderosisima, y reemplaza el buscarV de derecha a izquierda.
Aqui vamos a buscar el ranckig o posicion, de los que acabamos de calcular, y que nos devuelva el nombre del vendedor de cada una de las posiciones especificadas.

 =BUSCARV($F6;ELEGIR({2\1};$A$5:$A$36;$C$5:$C$36);2;0)

Este seria nuestro  resultado ya tenemos el top 10 de nuestros vendedores

Tambien podriamos traer el valor vendido para luego graficar, en caso de ser requerido. Este lo hacemos usando la misma funcion buscarv de derecha a izquierda.

Por ejemplo la formula uasda en H6=BUSCARV($F6;ELEGIR({2\1};$B$5:$B$36;$C$5:$C$36);2;0)

con ALT + F1 podemos graficar rapidamente

 

Descargue aqui este ejemplo

Si aún no estás suscrito al Video Curso Gratuito "10 Temas de Excel que todo usuario debe conocer" puedes hacerlo en: https://www.visiontecnologica.com/Eventos/excel2016/10temasdeexcel.html  y también te invito a suscribirte a este canal de Youtube para recibir todas nuestras novedades. 

Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

Generar un informe de Excel diferente para cada valor filtrado de mi tabla dinámica

En más de una ocasión se nos ha presentado una situación realizando nuestro informe en tabla dinamica, donde una vez construida, se hace necesario agregar un Filtro al informe para poder realizar los análisis respectivos.

También necesitaremos ver por separado cada filtro, para imprimir o para convertir en pdf para posteriormente enviarlo por correo por ejemplo.


 
Tenemos la siguiente información:

 

Como vemos en un reporte de Ventas, general por familia para dos meses. Y requerimos filtrar por vendedor.

Si desplegamos la lista a filtrar veremos que son muchos vendedores y hacer la tarea manual, es bastante tediosa y quita mucho tiempo.


Excel nos ayuda realizando una copia de mi informe base, filtrando cada uno de los items que vemos en la lista desplegable del campo filtro.


Se debe realizar los siguientes pasos:
1. clic encima de la tabla dinamica
2. pestaña Analizar

 

3. tabla dinamica
4. opciones
5. mostrar pagina de filtros de informes

Veremos que automaticamente excel hace un proceso y crea las copias de mi tabla original.

Aqui vemos las  Hojas por cada valor de filtro.

y a continuación el resultado por hoja.

Si aún no estás suscrito al Video Curso Gratuito "10 Temas de Excel que todo usuario debe conocer" puedes hacerlo en: https://www.visiontecnologica.com/Eventos/excel2016/10temasdeexcel.html  y también te invito a suscribirte a este canal de Youtube para recibir todas nuestras novedades.

Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos