Formulario de contacto

Nombre

Correo electrónico *

Mensaje *

domingo, 15 de octubre de 2017

Gráfico de Infografía en Excel - 1a parte

Hoy en día vemos muchas infografías en informes, en internet, en los periódicos...

Que qué es una infografía??

Pues de acuerdo a Wikipedia, la infografía es una representación visual informativa o diagrama de textos escritos que en cierta manera resume o explica figurativamente; en ella intervienen diversos tipos de gráficos y signos no lingüísticos y lingüísticos (pictogramas, ideogramas y logogramas) formando descripciones, secuencias expositivas, argumentativas o narrativas e incluso interpretaciones. La presentación gráfica figurativa envuelve o glosa los textos y puede o no adoptar la forma de una secuencia animada incluso con sonidos. De intención ante todo didáctica, la infografía nació como un medio de transmitir información gráficamente de forma más dinámica, viva e imaginativa que la meramente tipográfica. A los documentos elaborados con esta técnica se los denomina infogramas.

Muchas de las infografías que vemos no están desarrolladas en Excel... pero se podrían hacer?

Veamos!




martes, 10 de octubre de 2017

Cómo calcular cuántas veces una palabra aparece en una frase en Excel

Hola buenos días a todos.

El otro día un lector me propuso el siguiente problema: cómo calcular el número de veces que una palabra aparece en una frase en Excel. Pensé que a lo mejor había muchos lectores que podrían en un momento dado encontrarse con un problema similar y decidir escribir el post.

Imaginemos que tenemos los siguientes datos.




Y lo que necesitamos es calcular el número de veces que la palabra en la columna B aparece en la frase de la columna C.

Esta situación se puede resolver con una fórmula no muy compleja.

Veamos cómo!

lunes, 2 de octubre de 2017

3 errores comunes al escribir la función BUSCARV en Excel

Hola

Has estado alguna vez en una situación en la que tu función BUSCARV no funciona y no sabes porqué?

Déjame rescatarte los 3 errores más comunes usando la función BUSCARV.

1. Tener espacios (fantasmas) en los datos o valores de búsqueda

miércoles, 13 de septiembre de 2017

Usar la herramienta de cámara para hacer gráficos dinámicos en Excel

Hola!!

Excel tiene algunas características únicas y una de ellas es la herramienta de cámara. A priori no parece que sea gran cosa. Pero la herramienta de la cámara cuando se combina con una fórmula y botones de opciones, puede ayudar a hacer un gráfico dinámico bastante impresionante.

Veamos cómo!


Así que digamos que tenemos estos datos ..

Como se puede ver es una división de ingresos por mes y clientes. De estos datos hacemos 2 gráficos.



Pero nuestro objetivo es mostrar un gráfico cada vez (ya sea ventas por mes o por cliente)

lunes, 28 de agosto de 2017

La función Desref y sus aplicaciones en Excel - 2a parte


Hola buenos días, buenas tardes o buenas noches, según os pille.

La semana pasada escribimos sobre la increíble función DESREF (aquí dejo la entrada en caso de que no pudieras leerla http://elsabiodeexcel.blogspot.com/2017/08/la-funcion-desref-y-sus-aplicaciones-Excel-1.html) y el feedback que he recibido ha sido muy bueno. Muchas gracias! Se agradece trabajar así cuando veo que los lectores aprecian el esfuerzo hecho (también se puede agradecer de otra manera, mira el final de esta entrada). No olviden que requiere mucho esfuerzo sacar tiempo para investigar y escribir para que vosotros los lectores puedan aprender de una manera gratuita.

Bueno, pues hoy vemos cómo podemos aplicar esta incredible función DESREF.


Aplicaciones de DESREF


miércoles, 23 de agosto de 2017

La función Desref y sus aplicaciones en Excel - 1a parte

Hola buenos días, buenas tardes o buenas noches, según os pille.

La semana pasada puse una entrada sobre cómo hacer que BUSCARV vaya más rápido,  y recibí algunos mensajes en privado, de cómo valoraban la entrada. Igual que yo! Ojalá, y lo digo sinceramente hubiera sabido ese truco en mis años de analista de datos en Londres. La de tiempo que habré perdido esperando a que el BUSCARV actualice...  Eso, es lo bueno de aprender BIEN las funciones y de los usos que te pueden dar.... 

Por eso hoy quiero hablar de otra función que te puede ayudar MUCHISIMO! Esa gran desconocida para muchos llamada función DESREF.


La función de DESREF es una de las más importantes en la familia de las funciones de búsqueda y nos trae nuevas posibilidades en Excel, pero se sugiere utilizarla con precaución debido a su naturaleza volátil. Veamos esta función y sus aplicaciones en profundidad!

Sólo para poder explicarlo mejor vamos a dividir la sintaxis DESREF en 2 partes (el básico y avanzado)

lunes, 14 de agosto de 2017

Cómo hacer que BUSCARV vaya más rápido cuantos teneos gran cantidad de datos en Excel

Hola lectores

Hoy les quiero mostrar un super truco, que seguro que a mucho analistas les va a venir muy bien.
Es más, ojalá hubiera sabido yo esto hace tiempo, ya que me hubiera ahorrado mucho pero mucho tiempo.

A veces cuanto tenemos muchos pero que muchos datos, digamos que 100.000, intentar ejecutar un BUSCARV puede llegar a tardar bastante tiempo, desde 10-20 segundos  a incluso minutos, dependiendo del procesador de tu ordenador.

Pues bien, veamos hoy 

Cómo hacer que BUSCARV vaya más rápido cuantos tenemos gran cantidad de datos en Excel

Para aquellos que no andan muy familiarizados con la función BUSCARV, aquí les dejo un pequeño tutorial la función BUSCARV (VLOOKUP en inglés)


Al aplicar BUSCARV en datos grandes (digamos 100 mil filas o más) a menudo se ven agobiados por hojas de cálculo extremadamente lentas o incluso a veces por esta dulce notificación.





Y ya sabemos como nos hace ésto sentir! NOOOOOO!!!  # $% Auytiuyoidqjsjfas AARRGGHHH !.

Una de las maneras de acelerar este proceso sería usar PowerPivot (que es genial!) pero si por algún motivo aún no han hecho la transición a PowerBI, si es eres como muchos, pues en ese caso vamos a explorar juntos este truco ..


La parte increíble es que es sumamente simple! Ya veréis!

martes, 8 de agosto de 2017

Diferentes respuestas a la pregunta de la semana pasada

La semana pasada os puse un test, y he de decir, que estoy un poco desilusionado de la poquita cantidad de personas que tomó parte en él, pero supongo que así es la vida!

Espero que para el próximo haya más personas.

Aún así, recibí algunos comentarios sobre cómo hacerlo y eso es lo que me gustaría comentar hoy.

Para ver el test y descargar el archivo, se puede hacer aquí

Básicamente el test era el siguiente, teniendo una tabla de alumnos y asignaturas con sus correspondientes puntuaciones, la pregunta era: 

Utilizando una sola fórmula,  qué asignatura obtuvo la calificación más baja para cualquier estudiante?


La tabla de datos en cuestión era esta.


Bueno, pues veamos CÓMO resolverlo.

martes, 1 de agosto de 2017

Test de Excel - Pregunta. Encuentra la asignatura con la menor puntuacion para cualquier estudiante

Hola, estos días he estado leyendo muchas cosas por internet y entre ellas me decidí que era hora de escribir una entrada para hacer un pequeño test de Excel a los lectores.

Ahí va el Challenge!

Imaginemos que tenemos una tabla como la siguiente.

Estudiantes Matemáticas Química Física Dibujo Técnico Informática
Sandra 60 66 72 73 56
Gloria 74 77 71 70 62
Pedro 87 72 53 57 84
German 84 48 85 95 93
Ana 79 84 47 60 92
Andrea 66 95 87 75 56
Lisa 88 88 94 60 50

Ahora la pregunta a resolver es... 

miércoles, 26 de julio de 2017

Cómo personalizar los marcadores en un gráfico en Excel

Hola buenas.

En pleno verano donde estoy es más difícil encontrar tiempo para escribir una entrada, pero allá vamos.

Hoy vamos a ver como se pueden personalizar los marcadores en un gráfico de Excel. Mira el siguiente gráfico.





Como se puede ver en el gráfico los marcadores del gráfico tienen un teléfono móvil o celulares como se conocen en América Latina. 

No seré yo el que te diga que es mejor así, o que me gusta mucho, pero hay gráficos en los que queda más razonable. Por ejemplo.



Todos son datos inventados.

En fin, que depende de ti claro está si prefieres los gráficos así.

Bueno pues veamos cómo se hacen.


sábado, 1 de julio de 2017

Alternativa a Ctrl + C (Copiar) y Ctrl + V (Pegar) en Excel

Hola a todos y todas. Hoy quiero comentarle un truquito en caso de que no lo conozcan!

Una práctica común para copiar y pegar en Excel es utilizar los atajos de teclado para Copiar (Ctrl + C) y Pegar (Ctrl + V). Ctrl + C y Ctrl + V a primera vista parecen bastante eficiente, pero existe todavía un truco mejor y aún más inteligente


Y cúal es?

domingo, 25 de junio de 2017

10 trucos sobre AutoSuma y la función Suma - 2a parte

Pues aquí va la segunda parte... 5 trucos más!

Para ver la 1a parte haz clic aquí


Segunda Parte:

Truco #6 Total de filas filtradas




  • Si se desea que el Total se actualice cuando se filtren las filas, se puede utilizar la función SUBTOTALES en lugar de la función SUMA
  • =SUBTOTALES(9,Rango) en mi caso Rango puede ser C2:C10, o include C1:C11
  • El número 9 representa sólo la suma de filas visibles cuando se aplica el filtro

Aconsejo leer este artículo para entenderlo mejor:



Truco #7 Suma de la filas visibles




Se puede calcular la suma de SÓLO las celdas visibles, mediante la funciones SUBTOTALES y el índice 109.
  • Toma nota que las filas de la 4 a 7 se han ocultado manualmente y no con un filtro
  • Siempre que trabajemos con filas ocultas manualmente se ha de usar esta fórmula =SUBTOTALES(109, Rango) en mi caso C2:C10
  • El número 109 representa sólo la suma de filas visibles especialmente cuando las filas se ocultan manualmente



Truco #8 Total general de Subtotales en un solo rango






  • Cuando se está intentando tomar un total general de subtotales múltiples es mejor utilizar la función subtotales en todas partes
  • Observa que los subtotales por vendedor también se hacen usando Subtotales
  • Y el Total General también se hace usando Subtotales. Esto evita que se sumen las celdas dos veces





Truco #9 Totales acumulativos




Para calcular totales acumulativos, simplemente bloqueamos la primera parte (con el signo $) del rango de suma y luego arrastramos la fórmula hacia abajo.







Truco #10 Ignorando errores en el rango de Suma


Cuántas veces los errores molestan a la hora de calcular totales?

Para ello tenemos la genial función AGREGAR.


  • Para ignorar los valores de error, utilizamos la función Agregar
  • El número 9 es el número de función para la suma
  • El número 6 significa que ignora los valores de error

Y ya está! Cuántos de estos no sabías? Déjame un comentario abajo. Muchas gracias


Te ha gustado el artículo?

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes dejar una donación pinchando en la siguiente imagen.



PayPal. La forma rápida y segura de pagar en Internet


2. Puedes hacer clic en los anuncios mostrados en la página


3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.


Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.



El Sabio de Excel busca colaborades del blog

Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com


Subscríbete y recibe todos las entradas por email


Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales


miércoles, 21 de junio de 2017

10 trucos sobre AutoSuma y la función Suma - 1a parte


Hola qué tal? Cómo estamos hoy.

Hoy vamos a ver trucos relacionado con algo que hacemos muy a menudo en Excel.

Calcular el total sobre celdas en Excel es algo que todos sabemos o deberíamos saber, verdad? Pero esa simple función de suma se puede utilizar de muchas maneras distintas y para diversos propósitos.

Así que hoy os muestro 10 trucos! Echa un vistazo y dime cuántos de ellos eran nuevos para ti!

Primera Parte:

Truco #1 Autosuma


  • Siempre que se quiera tomar un total (horizontal o vertical)
  • Simplemente seleccione la celda y pulse Alt = (o sea la tecla Alt más la tecla =) 
  • La función de suma se aplicará y adivinará el rango que se desea totalizar. Si el rango es correcto, pulsamos enter 🙂

Truco #2 Autosuma de múltiple celdas

lunes, 12 de junio de 2017

10 trucos para escribir mejores fórmulas en Excel - 2a parte

Seguimos con los mejores consejos para escribir mejores fórmulas.

Si te perdiste la primera entrada, aquí la tienes! : http://elsabiodeexcel.blogspot.com.es/2017/05/10-trucos-mejores-formulas-excel-v1.html


6. Copiar la fórmula hacia abajo en el modo de edición



Este consejo es bastante bueno cuando tienes que copiar la fórmula de una celda de arriba, pero todavía quiere hacer cambios en ella!



domingo, 4 de junio de 2017

Conteo por color en Excel


*****************************************************************
*****************************************************************

Entrada escrita por nuestro colaborador Miguel Ibarra.
Si te gustaría colaborar mándame un email a:



*****************************************************************
*****************************************************************

Muchas gracias Miguel!

------------------------------------------------------------------------

Hola, nuestro tutorial de hoy es muy sencillo, pero trata de una acción bastante inusual en excel, contar colores. ¿Quién no ha utilizado el formato condicional para color? y ¿qué tal si además saber utilizarlo podemos contabilizarlo y obtener conclusiones de eso?.

Entrando en materia,hay dos casos en los que se podrían querer aplicar colores y por consiguiente, poder contarlos:

1. (En mi opinión más importante cuando tenemos formatos condicionales, la función dada cuenta las celdas que tienen el color dado por el primer argumento, dentro del rango dado por el segundo. Por ejemplo, en el caso de =CuentaColor(3;A1:A10) cuenta cuántas celdas rojas (teniendo en cuenta que esta función para poder contar los colores los codifica con números, el 3 es roja) hay en el rango A1:A10. La instrucción inicial Application.Volatile hace que la función recalcule su valor cada vez que se modifica la hoja. Esta función la escribimos en la hoja como cualquier otra función de Excel, A continuación les presento esta sencilla fórmula:




Para poder saber que número identifica cada color utilice la fórmula:

domingo, 28 de mayo de 2017

10 trucos para escribir mejores fórmulas en Excel - 1a parte

Hola, hoy quiero hacer una entrada que tendrá 2 partes. Se trata de buenos trucos que he aprendido y encontrado por internet para que puedas mejorar cómo escribes una de las funciones más importantes de Excel, las fórmulas!

Éstos son algunos de los mejores consejos para ayudarte a trabajar más rápido y a escribir mejores fórmulas en Excel!

La primera parte (ésta) constará de 5 consejos y la segunda parte tendrá otros 5 consejos.



Empezamos!


domingo, 21 de mayo de 2017

Crear una tabla de índice de hoja en Excel con VBA

Hoy vemos otro truco muy muy útil!

Todos estamos acostumbrados a trabajar con varias hojas en un solo libro, verdad? Pues estaría genial poder crear un índice para poder acceder a todas las hojas de una manera eficaz!


Pues hoy veremos una macro que nos ayudará a hacer exactamente eso!


Veremos una manera inteligente de crear un índice para todas las hojas, en el que se pueda hacer clic en el nombre de la hoja para ir directamente a esa hoja.









Así es como lo hacemos



domingo, 14 de mayo de 2017

La función FILA vs la función FILAS para la generación de números enteros consecutivos en Excel

Hola, veamos hoy una pequeña aclaración entre las funciones FILA y FILAS, y cómo se pueden aplicar.

A menudo, deseamos incorporar a nuestra fórmula una construcción que, como la fórmula se copia a sucesivas filas, generará una serie de números enteros consecutivos, usualmente comenzando con 1.


Un ejemplo clásico es con la configuración estándar INDICE + K.ESIMO.MENOR para devolver varios valores correspondientes a un cierto conjunto de criterios, por ejemplo:


{=INDICE($B$1:$B$11,K.ESIMO.MENOR(SI($A$1:$A$11="Luis",FILA($A$1:$A$11)),1))}


Esta fórmula obtendría la primera aparición de Luis en el rango A1:A11 y devolvería su valor correspondiente en la columa B.


Hay que tener en cuenta que es una fórmula matricial, asi que si aún no dominas mucho el tema, te recomiendo este enlace: Las fórmulas de matriz




El 1 del extremo de la fórmula resaltado en rojo aquí ({=INDICE($B$1:$B$11,K.ESIMO.MENOR(SI($A$1:$A$11="Luis",FILA($A$1:$A$11)),1))}

representa k para la función K.ESIMO.MENOR. Ahora si se quiese tener todos los valores correspondientes a Luis, se tendría que modificar manualmente este valor a 2, 3, etc. para las fórmulas en filas sucesivas. Pero por supuesto nos gustaría tener esto automatizado, y así copiarlo hacia abajo y no tener que modificar nada manualmente.


Cómo lo hacemos?

domingo, 7 de mayo de 2017

Una macro para consolidar datos provenientes de varias hojas

Hola.

Hoy quiero mostrarles una macro para resolver uno de estos problemas que suceden de vez en cuando.

Qué problema?

Uno de los problemas más comunes en la gestión de datos es agrupar todos los datos. Digamos que tenemos algunos datos dispersos en varias hojas que queremos reunir en una sola hoja. ¿Como lo harías?


  1. Una forma es copiarlo desde múltiples hojas y pegarlo en una ubicación 
  2. O también se puede escribir una simple macro para que haga lo mismo, ahorrando tiempo



domingo, 30 de abril de 2017

Cómo trabajar en varias hojas a la vez en Excel

Veamos hoy un truco que le puede ser muy útil a alguien o a muchos!

Este truco realmente viene muy a mano cuando tienes que agregar datos o editar varias hojas a la vez. Vamos a echar un vistazo a esto, es rápido y fácil!





Supongamos que tenemos que añadir este encabezado a esas 4 hojas





Se puede observar que el encabezado tiene un poco de formato aplicado. Incluyendo el formato nos gustaría colocar este encabezado en 4 hojas distintas.



  • Una forma es copiar y pegar 4 veces en 4 hojas diferentes
  • La otra forma es trabajar en varias hojas a la vez


Aquí es cómo lo hacemos ..


lunes, 24 de abril de 2017

Cómo crear un panel deslizante en Excel

Hoy quiero mostraros un super truco que descubrí hace unos meses, que puede ser muy útil para la creación de dashboards. La creación de un panel deslizante en Excel.

Crearemos un panel el cual se ocultará al hacer clic en la flecha y en dicho panel se podría ocultar controles etc. Muy ideal para los dashboards. (Para entender cómo funcionan los dashboards aconsejo ver este enlace: Dashboards (o Tableros de Control)




Hace muy poco usé esta técnica de panel deslizante en un dashboard (tablero de control) y la verdad es que me gustó mucho el resultado. Lo que hice fue básicamente mostrarlo como un panel de control para todos los ajustes y opciones del dashboard! 

El resultado visual queda así:



Bueno, pues veamos cómo crearlo en tu hoja de Excel!

lunes, 17 de abril de 2017

El comando Justificar en Excel

Hola

Veamos hoy un comando no muy utilizado en Excel, pero que en una situación específica puede llegar a ser de gran utilidad.

La verdad es que yo ni conocía de su existencia hasta hace poco, y cuando lo descubrí, pensé en las veces que tuve que afrontrar problemas que con éste comando se hubieran solucionado rápidamente.

Esta es una aplicación interesante de una de las herramientas disponibles en Excel que nos ayuda a romper la cadena de texto en una sola línea en varias filas y viceversa

Supongamos que tenemos este texto en diferentes celdas


Esto se puede claramente hacer a través de la función CONCATENAR pero hay una manera simple y más inteligente de hacerlo. Tambíen más rápida. Utilizar el comando Justificar.

domingo, 9 de abril de 2017

Creando cuadros de texto dinámicos en Excel

Hola! Imagino que habras trabajado con cuadros de texto en Word o PowerPoint o incluso en Excel, pero el truco que vamos a ver hoy hará que los cuadros de texto cambien de una manera dinámica .. en términos simples, el texto puede cambiar según la situación

Las aplicaciones de esta técnica en Dashboards, gráficos y visualizaciones son bastante útiles




Para insertar un cuadro de texto encontraremos la opción en la ficha Insertar
Sólo dibuja el cuadro de texto y empieza a escribir
Pero el problema es: ¡Si escribes algo en él, es un texto estático!


Veamos cómo puedes hacerlo dinámico.


domingo, 26 de marzo de 2017

Listas personalizadas para ordenar en Excel

Usas las listas personalizadas? No? Pues ya verás lo útiles que pueden ser. Yo admito que no las usaba y cuando las descubrí empecé a pensar en el tiempo que me podría haber ahorrado algunos días si lo hubiera sabido... así que... sigue leyendo!



Con la opción de ordenar se puede ordenar los datos (texto, números, fechas, etc.) en orden ascendente / descendente o alfabético, pero ¿qué pasa si desea tener sus propios criterios de clasificación? O por ejemplo, como en la tabla anterior, por meses si no están registrados como fechas sino como texto.