Felices Fiestas en Excel!

Desde El Sabio de Excel desearos unas Felices Fiestas!

Aqui os dejo un Excel-regalito. 

Ábrelo!

Cómo encontrar la primera y la última fecha de una venta utilizando tablas dinámicas

Hola a todos los lectores de ESdE.

Hoy quiero mostrarles un truco utilizando nuestras queridas tablas dinámicas (o Pivot-tables). Vamos a ver cómo obtner con un par de clics la primera venta de un vendedor así como la venta más reciente.

Imaginemos que nuestros datos son algo como esto:

Vemos que tenemos una tabla con registros de ventas, teniendo los datos de la región, del vendedor y del tipo de producto.

Y la pregunta va:

Un nuevo dashboard

Hola a todos

En los últimos meses he estado bastante ocupado con un estudiante de Chile al cual le he enseñado Excel intermedio, avanzado, con VBA, funciones matriciales y hasta Power Pivot. También vimos Access. Todo por Skype!

En fin, la semana pasada estabamos repasando los Dashboards, y decidimos hacer uno rapidito sobre un país como Australia. 

Pues aquí lo dejo para que lo veais.

Bueno, pues ahí está. Descargaros el dashboard y ver cómo funciona.

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.

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.

Consultoría Excel / Cursos de Excel

Descargarse el archivo

Para descargarse el dashboard sobre Australia ve al siguiente enlace.

Australia

El dashboard viene en formato protegido. Si deseas recibir el archivo desprotegido para ver la construcción del mismo, por favor mándame una donación (mínimo €5 por favor) por el trabajo y el esfuerzo realizado. Para ello haz clic en el enlace siguiente y efectúa tu donación. Una vez recibida te enviaré por email el archivo desprotegido.

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

Facebook, Twitter, Youtube y Google+

Suma con dos criterios que contengan un texto especifico en Excel

Hoy vamos a ver algo que en un momento dado te puede venir muy bien

Imaginemos que tuviesemos una tabla algo que así:

Una tabla normal, donde hay una columna de cantidades (columna B) y 2 columnas de clasificación (A y C). También tenemos una columna de Notas, o texto.

Ahora imaginemos que queremos sumar las cantidades de una región. Eso lo podemos hacer fácilmente con la función SUMAR.SI. Y si quisiemos sumas por 2 criterios o más, por ejemplo una región y un tipo, para ello usaríamos SUMAR.SI.CONJUNTO.


Hasta aquí todo bien, no?

Y si lo que necesitasemos sumar fueran las cantidades si los criterios fuesen algo así como que las notas contengan la palabra "Verano" y "Festivo".

Ahí ya todo cambiaría. Ya no podemos utilizar ninguno de los 2 SUMAR.SI a menos que cambiasemos los datos.

Y entonces cómo lo haríamos? Sigue leyendo.

Una macro que ordena de manera ascendente/descendente todas las hojas del libro de Excel

Hola qué tal?

En el último artículo, vimos cómo crear una macro que creaba y actualizaba una lista de índice o tabla de contenidos en tu libro de Excel. Esta macro se añadía a una hoja de cálculo

Aquí dejo el enlace por si aún no lo viste:

http://elsabiodeexcel.blogspot.com.mt/2016/10/macro-crear-actualizar-indice-excel.html



En el código VBA de hoy, vamos a añadir la macro al libro. La macro que veremos hoy va a clasificar todas las hojas del libro actual. Puede ordenar tanto en orden ascendente como descendente. La macro ordena por número pero también por orden alfabético. Esto es ideal si se trabaja con un gran número de hojas de cálculo en Excel y documentos que se necesitan ponerlos en orden.

Una macro para crear y actualizar una lista de indice en tu archivo de Excel

¿Quieres una hoja central desde donde se puede navegar fácilmente a cualquier hoja del libro y, a continuación, vaya hacia atrás con un solo clic? En esta macro de Excel se crea un índice que enumera todas las hojas del libro. 

La mejor parte es que el propio índice de Excel se actualiza cada vez que seleccione la hoja de índice.

Es probable que en algún libro de Excel tengamos un montón de hojas de cálculo con lo cual sería muy conveniente tener una hoja de índice en el libro. 

Para conseguir este efecto, el primer paso es agregar una hoja de trabajo más, y se le llama "Índice", o como se quiera para identificarlo como un índice ( tabla de contenidos, etc.). Para cambiar el nombre de la hoja, haga clic derecho en el nombre de la hoja y seleccione Cambiar nombre.

Una alternativa a los gráficos tradicionales - los minigráficos

Hola a todos

Hoy vamos a hablar de algo que Microsoft introdujo para Excel 2010, los minigráficos!. Esos genios que pueden hacer que las visualizaciones sean más sencillas y rápidas. Hay muchas veces que introducimos mucho gráfico y podemos sobrecargar nuestras presentaciones, y con un simple gráfico (o minigráfico en este caso) bastaría para transmitir al oyente lo que uno desea hacer.

Y qué son los minigráficos?

Añadir guiones a números de serie utilizando Excel relleno rápido

Siguiendo con el Relleno Rápido, y con sus usos que pueden ahorrar mucho pero mucho tiempo.

Hoy veremos otro ejemplo práctico:  Cómo agregar guiones a números de serie

Imaginemos que tenemos los siguiente números de serie.

Ahora imaginemos que tenemos 10.000 datos más!

Una Macro que colorea la celda donde el filtro esté aplicado

**************************************************
Entrada creada por Huicho

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

Muchas gracias Huicho! Te agradezco mucho tu primera entrada y espero que haya muchas más!.

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


Cuando se trabaja con muchas celdas, y utilizamos filtros, hay un momento en que tienes varios filtros utilizados, y entre tanta información se olvida que columnas estamos utilizando con dicha herramienta.

En esta ocasión, comparto esta macro, la cual al ejecutar colorea el fondo de la celda de color negro y la fuente de color blanco, y así es más visible la celdas que están filtradas, y no deshacer todos los filtros o quitarlos y volver a empezar.

Asi que a abri el editor de Visual Basic y pegar...

El código es el siguiente:

'---------------------------------------------------------------------------------------
' Macro     : colorearceldafiltro
' Fecha      : 19/08/2016
'---------------------------------------------------------------------------------------
'
Sub colorearceldafiltro()
With ActiveSheet

Los hipervínculos en Excel y la función Hipervínculo - Parte 1

Los hipervínculos son elementos dentro de un documento electrónico que hace referencia a otro documento o a un punto específico dentro del mismo documento. Los hipervínculos en Excel nos permiten crear enlaces entre las hojas de un libro o enlaces a otros libros de Excel u otros documentos. También es posible crear hipervínculos a páginas Web que abrirán automáticamente el navegador de Internet con la dirección electrónica especificada.

Crear hipervínculos de Excel

En una hoja de cálculo de Excel podemos crear un hipervínculo en cualquier celda y como resultado cuando se hace clic en dicho hipervínculo se va ir a la ubicación especificada.

Aquí hay cuatro formas de crear un hipervínculo - que veremos a continuación:

• escriba la dirección URL
• utilice el comando Insertar hipervínculo
• arrastrar y soltar
• utilizar la función HIPERVINCULO

Otra manera de encontrar la interseccion - con la asignación de nombres

Hace poco escribí una entrada que hablaba de cómo asignar múltiples nombres a rangos distintos al mismo tiempo. Si no la viste aún te lo recomiendo.

Pues hoy vamos a ver cómo encontrar la intersección en una tabla mediante la asignación de nombres.

Imaginemos que tenemos la tabla anterior, en la que se muestra las ventas por mes y vendedor.

Lo que nos gustaría averiguar es la cantidad de un vendedor en un mes especificado. Por ejemplo, cuánto vendió Jose Perez en Febrero.

Para resolver a tal pregunta, se podría utilizar un BuscarV, o un Indice, un sumar.si, e includo un sumaproducto.

Pero también se podría usar la asignación de nombres. Cómo?

Cómo construir un gráfico de tornado

Hola! En este post quiero explicar cómo construir paso a paso un gráfico de tornado en Excel. De lo que estoy hablando básicamente es de un gráfico así (el típico gráfico de distribución de la población por edades):

Como asignar multiples nombres a diferentes rangos a la vez en Excel

La asignación de nombres en Excel es una funcionalidad que es genial, por ejemplo a la hora de simplificar fórmulas.

No es lo mismo tener 

=BUSCARV(F6,Ventas!$A$3:$D$24,3,0)

que =BUSCARV(F6,ventas,3,0)

Con ésto en la cabeza, es fácil entender el porqué es bueno asignar nombres a rangos. Es más hay muchas razones de porqué es bueno, pero hoy no voy a hablar de ello.

De lo que quiero a hablar es de otra cosa

Imaginemos que tenemos la siguiente tabla de ventas:

Ahora imaginemos que se quiere asignar un nombre al rango de cada mes, por ejemplo llamaríamos Ventas_Enero al rango B4:B24, Ventas_Febrero a C4:C24 y así sucesivamente.

Obviamente se podría hacer uno a uno mediante la asignación de nombres.

Y después de Enero hacer Febrero, y luego Marzo...

O se podría hacer todos a la vez!!!!!

CÓMOOOOOO!!?????

Gráfico de barras verticales en celda usando las funciones REPETIR () y CONCATENAR ()

Hola, hoy quiero mostraros cómo construir gráficos de barras verticales en una celda, sin necesidad de utilizar la herramienta para crear gráficos. Para que os hagais una idea de lo que estoy diciendo, un ejemplo luciría así:

Ventajas de utilizar el formato .xlsb Libro Binario de Excel

Muchos usuarios no son conscientes de los muchos beneficios y ventajas de formato .xlsb libro binario de Excel

Los libros binarios de Excel (.xlsb) almacenan información en formato binario en lugar del formato XML al igual que con la mayoría de los archivos de Excel. Puesto que los archivos xlsb son binarios, se pueden leer y escribir mucho más rápido lo que les hace extremadamente útil para hojas de cálculo muy grandes. Esto también implica que con los cálculos en archivos .xlsb serán más rápidos y los libros serán mucho más pequeño.

Los archivos .xlsx tardan en cargar 4 veces más que las .xlsb y en guardar 2 veces más lento y además tienen 1.5 veces un archivo más grande. Algunos foros ha hecho el experimiento con una hoja de cálculo generada con 10000 filas x 10000 columnas = 10.000.000 (10 ^ 7) de celdas simples encadenados ... = 1 + fórmulas:

Además, desde el punto de vista de seguridad, al estar los datos en formato binario y no en XML, los archivos .xlsb mostrarán contenidos ilegibles si un usuario intenta cambiar la extensión del archivo a .zip.

Otras ventajas del formato de archivo .xlsb incluyen:

Ocultando celdas, fórmulas y comentarios en Excel

Siguiendo con cómo ocultar indredientes de Excel, hoy veremos cómo ocultar lo siguiente:

• Fórmulas
• El contenido de una celda
• Comentarios

Para ello echa un vistazo al siguiente video.

Muchas gracias

 

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

El Sabio de Excel les quiere recomendar un nuevo ebook que les explicará todo lo que necesitan saber sobre Power Pivots.

EL ADN DE POWER PIVOT

Guía paso a paso sobre Power Pivot, construcción de modelo de datos y el lenguaje DAX, con orientación a inteligencia de negocios.

   Formato: PDF Libre de DRM, Descarga INMEDIATA.

   Datos Técnicos: 476 Páginas + Compendio de Archivos

  Precio: $ 32 95 USD (Dólares Americanos)

El Sabio de Excel recibirá una comisión por cada compra del libro que se realice usando el link de a continuación. Esta comisión ayuda (y mucho) a seguir manteniendo esta web. 

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

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 invitarme a un café pinchando en la siguiente imagen.

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.

Consultoría Excel / Cursos de Excel

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

Facebook, Twitter, Youtube y Google+

Ocultando filas, columnas, hojas y lineas de cuadrícula

Veamos hoy cómo se hace en Excel para poder ocultar ciertos ingredientes de Excel.

Lo que aprenderemos hoy será a cómo ocultar/mostrar:

• Filas
• Columnas
• Hojas
• Lineas de cuadrícula
• Barra de fórmulas
• Títulos

Para ello veamos el siguiente video que creé.

Devolver un valor en Excel si se encuentra en un intervalo especificado - parte 2

Hola, muy buenas a todos/as. En la entrada anterior hablamos de cómo devolver un valor en Excen si se encuentra dentro de un intervalo. Aquí podeis leer la anterior entrada.

http://elsabiodeexcel.blogspot.com.mt/2016/05/devolver-valor-excel-intervalo-rango.html

En esa entrada vimos 2 maneras de hacerlo, una utilizanda la función BUSCARV y la otra utilizando la combinación de funciones INDICE + COINCIDIR.

Los 2 métodos son muy buenos, pero requieren de un requisito indispensable. 

Este requisito es que la tabla donde se indican los intervalos ha de estar ordenada.

Si esta tabla no estuviese ordenada no valdría ninguno de los 2 métodos aprendidos en la entrada anterior.

Así que hoy vamos a ver un tercer método para rectificar ésto, y es que con este método la tabla no ha de estar ordenada.

Devolver un valor en Excel si se encuentra en un intervalo especificado - parte 1

Hola, tuve una consulta recientemente de un lector que me preguntaba cómo se podía hacer en Excel para devolver un valor, si el valor especificado se encontraba en un intervalo. 

Veamos cómo hacerlo con un ejemplo

Así que tenemos una tabla de intereses, en la cual queremos asignar a valores de hasta 500, un interés del 1%, a valores entre 501 y 1000 un interés del 0.75%, a valores entre 1001 y 2000 un interés del 0.5% y por último a valores mayores de 2000 un interés del 0.25%.

De tal manera que si introducimos un valor en una celda, queremos que nos calcule el interés a aplicar de acuerdo a esta tabla.

Si es 600, sería 0.75%, y si es 1800 sería 0.50%.

Y cómo haríamos esto?

Buenas noticias para los lectores de El Sabio de Excel

Hola amantes de Excel!

Cómo estáis hoy?

Hoy les quiero comunicar una buena noticia.

Durante los últimos meses he estado muy ocupado ya que abrí un negocio (una ludoteca para niños) en la isla donde vivo (Malta) y como os podeis imaginar he estado super ocupado. La consecuencia fundamental ha sido que no le he podido dedicar tanto tiempo al blog como hubiese querido y por eso empecé a buscar colaboradores.

Durante estos meses he tenido muchas  peticiones de consultoría y de cursos, las cuales no la he podido satisfacer.

Ahora empezamos una nueva época en la que creo que tendré más tiempo para los trabajos de consultoría así como para los cursos personalizados.

Y esa es UNA MUY BUENA NOTICIA!!

Bueno, pues ahora tendré más tiempo para ellos y quería comunicar mis servicios.

Excel Servicios de Consultoría

1. Diseño de hoja de cálculo o rediseño.
2. Las tablas dinámicas, gráficos, fórmulas personalizadas.
3. Automatización de procesos de trabajo.
4. La automatización de procesamiento de datos y análisis.
5. Reducción de las cargas de trabajo a través de fórmulas y macros especiales.
6. Extracción, manipulación y análisis de datos procedentes de bases de datos y otros sistemas empresariales.
7. Revisión de hoja de cálculo y control de calidad.
8. Creación de gráficos dinámicos e informes para presentaciones.
9. Diseño de hoja de cálculo a medida y desarrollo utilizando Excel Macros (VBA).
10. Dashboard y creación de informes KPI (indicadores clave de rendimiento).
11. Integración Excel - PowerPoint y Word. Utilice Excel en PowerPoint con facilidad.
12. Resolución de problemas para casos aislados.
13. Sistemas de inventario.
14. Recuperar o eliminar la contraseña

Precios populares dependiendo del trabajo a realizar, desde €19.99

Cursos personalizados online 

También puedo ofrecer cursos personalizados online para particulares. El temario en cuestión se determinaría entre el cliente y el tutor (yo). Los precios por los cursos serían de acuerdo al temario y las horas contratadas. Los cursos pueden ser para:

• Nivel básico
• Niver intermedio
• Nivel avanzado

Precios populares dependiendo del temario a elegir, desde €19.99

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


Para más información mándame un email a elsabiodeexcel@gmail.com

Cómo crear un gráfico de paneles en Excel

En este artículo de hoy vamos a aprender lo que es un gráfico de paneles y cómo se puede construir en Excel.

¿Qué es un gráfico de paneles?


Un gráfico de paneles es un conjunto de gráficos similares perfectamente alineados en paneles para ayudarnos a comprender algunos datos que tienen múltiples variables en ellos.


Veamos por ejemplo aquí un gráfico de paneles que muestra el registro de actividad en una empresa en las últimas 4 semanas.

Como se puede observar un gráfico de paneles es una muy buena opción para tener gráficos con múltiples variables. 

¿Y por qué utilizar los gráficos de paneles?

La función SI en Excel

*************************************************************************
Artículo escrito por Gabriel Ceballos - Muchas gracias Gabriel! 

Quieres escribir para El Sabio de Excel? 
Mándame un correo a elsabiodeexcel@gmail.com
*************************************************************************

En esta ocasión quiero compartir una breve explicación de una de las funciones básicas de Excel pero que a veces no utilizamos por desconocer su funcionamiento y por ende no le sacamos el máximo potencial. Por definición podemos decir que Esta función es parte del grupo de funciones lógicas para determinar si una condición es falsa o verdadera. Para que esto sea posible, es necesario que halla algo que evaluar; esto puede ser algún dato o alguna fórmula.

Para entender mejor este asunto, recuerda que la función SI evalúa una condición, misma que ha de ser falsa o verdadera, lo vamos a ilustrar con un ejemplo.  Nuestra vida cotidiana esta llena de condiciones, solo que no las "evaluamos", más bien "las vemos" como algo en lo que hemos de tomar una decisión. Imagina que quieres un refresco de esos de "color negro" para no promocionar marcas. Para que lo tengas tienes que ir a la tienda y preguntarle al tendero si tiene un refresco de "color negro". En caso afirmativo te lo traes, de lo contrario, no traes nada.

El ADN de Power Pivot

Hola lectores de El Sabio de Excel!!

Como ya sabeis éste es un blog gratuito que gracias a todos vosotros llega a muchas personas, a más de 1000 cada mes. Por eso mismo me ha llegado la oportunidad de ser afiliado de un libro de Excel que acaba de salir y que puede ser de mucha ayuda para manejar una de las mejores utilidades que posee Excel. El Power Pivot.

Para ser sincero, si alguno de mis lectores compra el susodicho libro, obtendré una comisión por la venta, que será una ayuda muy importante para poder continuar con este blog gratuito.

EL ADN DE POWER PIVOT

Power Pivot es un poderoso complemento gratuito de Excel que proporciona la posibilidad a cualquier profesional de integrar, manipular, explorar y sacar conclusiones de cantidades masivas de datos, para obtener información y conocimiento fundamentado en análisis de datos y así tomar decisiones efectivas y de alto valor agregado para la compañía, todo a través de un manejo fácil e intuitivo y a una velocidad inimaginable.

– ¿Qué es EL ADN DE POWER PIVOT?

10 Juegos en Excel

Hola, hoy vamos a hablar de juegos de ordenador, o consola o de... si si, de Excel!

Yo ya no soy muy aficionado a los juegos. Lo era más cuando era más jóven, pero ahora no me resulta tan atractivo. Pero aún así he tenido y tengo mis uegos favoritos. De siempre fueron los siguientes: Tetris, Ajedrez, Buscaminas y el Sudoku. Ya ves, no muy de acción, más bien matemáticos y de lógica.

Tarjeta de Regalo en Excel

*********************************************************************
Entrada escrita por Miguel Ibarra - Muchas gracias Miguel!

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



Hola, buenos días mi tutorial es especial no solo porque muestra las asombrosas cosas que se pueden hacer con Microsoft Excel sino que para un detalle no hay fecha ni situación especial. Por eso, dedico mi tutorial de hoy a una persona muy especial para mí llamada Karina.

En el día de hoy haremos una tarjeta de regalo, lo primero que tenemos que hacer es seleccionar 10 columnas y asignarles un ancho de 6 (esta es la combinación que me dió un cuadro perfecto no tiene que ser exactamente igual).

También usarás 10 filas de 36 de altura.

Cambia el formato a gráficos en Excel rápidamente con los estilos de diseño y temas de color

Hoy es dejo un consejo rápido para reducir el tiempo que pasamos formateando gráficos.

Qué es?

Pues sencillamente es el utilizar estilos de diseño de gráficos y los temas de color.

Excel ofrece varias combinaciones de colores predefinidos y estilos de diseño de gráficos. 

Con un par de clics se puede presentar un gráfico así

O así: