Filtros avanzados (I): Copiar los datos a otro rango u otra hoja

El uso de los filtros (antes autofiltros) para poder ver en una hoja solamente aquellas filas, dentro de un rango de celdas, que cumplen ciertas condiciones, es relativamente conocido y difundido por los usuarios/as de Excel. Claro, el efecto se da en la misma hoja y dentro del mismo rango (se “ocultan” filas), pero ¿qué ocurre cuando queremos que el resultado del rango filtrado se vea en otras celdas (otro rango) o incluso en otra hoja? En esos casos usaremos los “Filtros avanzados” (ahora “Filtro – Avanzadas”).

Para este ejemplo lo primero que haremos es insertar tres filas vacías encima de nuestros datos.

En la primera fila repetiremos los nombres de los campos (títulos de las columnas) que deseamos filtrar. No es necesario que sean todos y no olvidemos que nunca deben ser más que la cantidad del número de columnas de nuestros datos (recordemos eso sobre todo en el caso de que queramos filtrar por rango de fechas).

Vamos a suponer que deseamos filtrar las ventas solo de la tienda “San Borja” y que queremos los datos a partir de la celda “G4”, entonces colocaremos la palabra “San Borja” en la celda “C2” y luego en la pestaña “Datos” vamos al grupo “Ordenar y filtrar” y le damos clic al botón “Avanzadas”. En el cuadro de diálogo que sale a continuación primero elegiremos la opción “Copiar a otro lugar”, elegiremos el rango de nuestros datos (“Rango de la lista”), también nuestro “Rango de criterios” y en “Copiar a” vamos a elegir la celda “G4” en nuestro caso.

Si hicimos todo bien tendremos como resultado algo así:

Vamos ahora a suponer que queremos esos mismos resultados, pero en otra hoja. Seguiremos los mismos pasos, pero en el cuadro de diálogo elegiremos (o escribiremos) la celda de otra hoja.

Cuando le demos clic a “Aceptar” Excel nos dirá lo siguiente:

Entonces ¿es imposible que se copien los datos en otra hoja? Pues no es imposible y ahora veremos cómo hacerlo.

Vamos a supone que en “Hoja2” queremos los resultados y que en la hoja “Datos” está nuestra base, entonces vamos a la “Hoja2” y desde ahí activaremos la función de “Avanzadas”. Deberíamos tener algo así:

Y una vez que damos clic a “Aceptar”, y si todo salió bien nuevamente, tendremos lo siguiente en la “Hoja2”:

Y así, amigos y amigas, hemos aprendido a filtrar los datos en otra hoja y si usar macros. Hasta la próxima.

Abraham Valencia

Nota: Para más formas de filtrar ver el siguiente enlace

"Dashboard" en Excel: Creando tu propio tablero de control

Cada día es más habitual encontrar libros de Excel en los cuales se ha agregado “Tableros de Control” más conocidos simplemente como “Dashboards”. Dichos tableros facilitan la presentación y/o análisis de datos que en muchas ocasiones necesitamos por lo que en esta ocasión veremos cómo hacer uno que incluso nos permita visualizar los datos a través de gráficos.

Para comenzar, la disposición de nuestros datos para el ejemplo será de este modo:

Para que el ejercicio resulte mucho más visible, he agregado varios registros con datos de tres años.  Para que no haya problema cuando vamos agregando más datos, yo siempre recomiendo convertir en “Tabla” dichos rangos. Además de usar el ya “clásico” ejemplo de ventas para estos casos, también necesitaremos, como siempre se recomienda, tener tres hojas en nuestro libro: Una para los datos, otras para las tablas dinámicas que vamos a necesitar y una para el “Dashboard”.

En este ejemplo lo que vamos a necesitar, para nuestro “Dashboard”, son los cruces de ventas por vendedor y de ventas por tienda, para eso vamos a necesitar dos tablas dinámicas. Obviamente cada quien debe decidir lo que desea mostrar. Para eso, entonces, crearemos dos tablas dinámicas que colocaremos en la hoja que yo he llamado “TD” (estoy asumiendo que todos/as saben cómo crearlas) de tal modo que tengamos esto como resultado:

Sugiero dar formato a los números para que después en los gráficos que usemos se refleje dicho formato. En mi caso usaré el formato de moneda, tal cual está en la tabla de datos.

Ahora lo que haremos será insertar un gráfico dinámico para cada una de las tablas dinámicas. Por si se han olvidado en dónde está la opción para crearlas, se sitúan en cualquier celda de la tabla dinámica respectiva y en la pestaña “Analizar” la veremos así:

Podemos elegir el tipo de gráfico que deseemos, en mi caso yo he elegido barras 3D agrupadas (horizontales).

No olviden que es un gráfico por cada tabla dinámica que usemos.

Luego lo que debemos hacer es cortar y pegar cada gráfico en la hoja llamada “Dashboard”. Por supuesto que podemos cambiar el diseño de cada gráfico con las opciones, justamente, de la pestaña “Diseño”. Además de eso, sugiero usar las opciones de cada gráfico dinámico para ocultar los botones de campos.

Una vez hecho eso, seleccionamos cualquiera de nuestros gráficos dinámico y vamos a la pestaña “Analizar” y en el grupo “Filtrar” usaremos el botón “Insertar escala de tiempo”.

En el menú que veremos elegimos, en mi caso, el campo “Día” y aceptamos.

Nuestra escala de tiempo incluirá todos los meses ya años que existan en nuestros datos.

Podemos cambiar el diseño a dicha escala y, además, una de las ventajas que tiene es que nos permitirá elegir días, meses, trimestres o años.

Lo que toca hacer ahora es relacionar nuestra escala de tiempo con las tablas dinámicas, para ello seleccionaremos nuestra escala y en la pestaña “Opciones”, en el grupo “Escala de tiempo” vamos a usar el botón “Conexiones de informes”.

Y activaremos la conexión a todas las tablas dinámicas que estamos usando.

Si bien ya tenemos como ir filtrando por tiempo, ahora lo que haremos es agregar algo que nos permita también hacerlo por vendedor o tienda, para eso seleccionamos cualquiera de los gráficos y en la pestaña “Analizar”, en el grupo “Filtrar”, esta vez vamos a elegir “Insertar segmentación de datos” y elegiremos “Vendedor” y “Tienda”.

Con los “slicer” que tenemos ahora, lo que debemos hacer es usar “Conexiones de informes” y relacionarlos a nuestras tablas dinámicas, tal y como hicimos con la escala de tiempo. Por supuesto también podemos cambiarle el diseño a dichos “slicer”.

Para ver como seguir agregando datos que nos pueden ser útiles, en una celda obtendremos el total de ventas y haremos que dependa de lo que vamos a ir eligiendo en la escala de tiempo y/o los “slicer”. Para ello vamos a usar el total de general de una de las tablas dinámicas y vincular dicho resultado a la celda deseada. En este caso he usado el total de la tabla dinámica con los resultados de las “Tiendas” .

Como las tablas dinámicas trabajan sobre los mismos datos, vamos a usar la de “Vendedores” y en el campo de totales vamos cambiar para que nos dé como resultado el promedio.

Vinculamos otra celda con dicho resultado del promedio, y con ello tendremos el promedio de venta de cada vendedor en base a lo que vayamos filtrando en la hoja “Dashboard”.

Para finalizar podemos colocar cada elemento de los obtenidos en la parte de la hoja que deseemos y tendremos algo como esto:

Y listo, ya podemos comenzar a “jugar” con los datos. Espero les sea útil. Hasta la próxima.

Abraham Valencia

Descarga el ejemplo aquí: Enlace

 

Conectar Excel con una Hoja de Cálculo de Google a través de Power Query

Hoy en día en que el trabajo compartido a través de internet se masifica más y más, no es raro que compartamos y/o se trabaje en archivos “on line”. Una de las aplicaciones cuyo uso se está difundiendo mucho es la “Hoja de Cálculo de Google” e incluso muchas personas creen que es lo mismo que Excel. No es extraño que mientras en nuestra portátil o nuestra PC de escritorio usamos Excel, nosotros mismos y/o nuestros compañeros/as estén trabajando en la aplicación de Google y que necesitamos usar esos mismos datos. Por supuesto que una alternativa es entrar a la hoja de Google en internet y descargar en formato Excel, pero habría que hacer eso cada vez, lo que puede resultar algo tedioso. Entonces ¿qué otra alternativa tenemos?

En esta ocasión vamos a conectar una hoja de cálculo de Google con un libro de Excel. Para eso vamos a comenzar suponiendo que tenemos datos dispuesto, por ejemplo, así:

Ahora, lo primero que haremos es ir al menú “Archivo” y elegir la opción “Publicar en la Web…”

En el cuadro de dialogo que saldrá, vamos a elegir, en la primera lista, el nombre de la hoja cuyos datos vamos a compartir y en la segunda lista vamos a elegir la opción “Valores separados por tabuladores (.tsv)”.

Luego de ello, Google nos brindará un enlace que debemos copiar. No olvidar eso o no podremos realizar la conexión.

Ahora sí, vamos a nuestro querido Excel. En el libro y hoja en donde queramos los datos vamos a la pestaña “Datos” y en el grupo “Obtener y transforma datos” vamos a darle click al botón “Desde el texto/CSV” y en el cuadro de dialogo que se abrirá, pegaremos el enlace de Google en la parte de “Nombre de archivo:”

Veremos cómo se abre el editor de Power Query y ahí le daremos doble click a la imagen en donde dice “docs.google.com”.

El resultado debe ser similar a esto:

Si al igual que yo usan tildes y algunos datos se ven como en la imagen anterior, no se preocupen, hay formas de arreglarlo. En el editor de Power Query vamos a la pestaña “Inicio” y en el grupo “Consulta” elige el botón “Editor avanzado” y al abrirse buscaremos los siguiente:

¿Ven el número 1252? Pues reemplácenlo por el 65001 que es el correspondiente al formato UTF-8, denle click al botón “Listo” y ahora los datos se verán así:

Ahora elijan la pestaña “Transformar” y en el grupo “Tabla” elige “Usar la primera fila como encabezado” y en la lista que se despliega elige la opción del mismo nombre. En el grupo “Columna de texto” elige “Dividir columna” y ahí “Por delimitador”, en el cuadro que tendrán a la vista, verifiquen que estas opciones estén activadas y denle click a “Aceptar”:

Ahora sí tendremos los datos casi listos:

En la pestaña “Archivo” elijan “Cerrar y cargar” para enviar los datos a nuestra hoja de Excel. En la pestaña “Datos” de la hoja, vayan al grupo “Consultas y conexiones” y ahí en “Actualizar todo” elijan “Propiedades de conexión…” y activen, si lo desean, “Actualizar cada” y coloquen la cantidad de minutos deseados, activen la opción “Actualizar al abrir el archivo” y verifiquen que la opción “Actualizar esta conexión en Actualizar Todo” esté activada. Por último, activar “Habilitar la carga de datos”. Listo, ahora nuestra hoja de Excel se actualizará con los datos que se cambien/agreguen/eliminen en la Hoja de Cálculo de Google. Espero les sea útil. Hasta la próxima.

Abraham Valencia