Es sabido que desde hace unos meses Microsoft está empleando su versión Web de Excel (Excel Online) como campo de lanzamientos previo a sus versiones perpétuas o de subscripción (Office script, funciones desbordadas, etc..).
Un último apunte a éstos es el predictivo dentro de la validación de datos tipos lista, largamente esperado!!.
Si bien parece que aún no alcanza a la totalidad de usuarios de esta versión Web... Deberemos esperar poco para su implantación en las versiones de escritorio (ojalá!!).
Su implantación es automática... basta configurar una Validación de datos 'normal', así en la cinta de opciones de nuestro Excel Online, desde la ficha Datos > Herramientas de datos > Validación de datos accederemos a la ventana de configuración
En esta pestaña de configuración, al igual que haríamos en la versión de escritorio, permitiremos elementos de una lista con origen en el rango deseado, en el ejemplo B3:B9
Tras aceptar la configuración la funcionalidad está lista.
Basta empezar a escribir en la celda validada para que aparezcan en el desplegable aquellos elementos con caracteres coincidentes...
Eso sí, la limitación es que deben ocupar la misma posición, esto es, no es un buscador predictivo por aproximación.
Por ejemplo, si escribimos una 'm' despliega los elementos de la lista: manzana, melocotón, melón
Si escribimos 'me' solo mostraría: melocotón, melón
Impresionante... y solo hemos esperado treinta años para esto :DD
Pediremos a Microsoft para que no tarde otros tantos en implementarlo al resto de usuarios y versiones ;-)
Hoy explicaré un buen truco para incorporar una fila de totales a un rango desbordado, ejemplo versionado del gran Jon Acampora.
Los usuarios que disponemos de estas funciones desbordadas sabemos del problema de incluir al final del rango desbordado devuelto un subtotal, ya que a priori no es conocido el tamaño del rango retornado... Con este truco sí será posible... pero requiere un rango auxiliar de trabajo que ocultaremos a la vista ;-)
Recuerda cambiar, por estética, el formato de las fechas... El primer paso será construir la conocida función FILTRAR, así en H4 escribiremos: =FILTRAR(Tabla1;Tabla1[País]=$M$1)
que retorna todos los campos de la 'Tabla1' pero solo los registros con el país coincidente con el desplegado en la celda M1. El siguiente paso consiste en numerar los registros obtenidos, lo que conseguiremos con la función SECUENCIA. Por tanto en la celda F4 escribiremos: =SECUENCIA(FILAS(H4#))
que devuelve una secuencia ordenada desde 1 hasta el total de filas obtenidas con FILTRAR, de ahí la referencia a H4# Siguiente paso importante... en la fila 3, la inmediata superior a los rangos desbordados obtenidos!!
En la celda G3 añadimos =MAX(G4#)+1
con lo que conseguimos un ordinal superior en uno al número máximo de filas recuperadas con FILTRAR.
Mientras en H3 escribimos el texto que queramos ver en la fila de totales final.
Un truco importante para evitar formateos posteriores es escribir en I3 un espacio en blanco, de no hacer esto, posteriormente en el rango desbordado final, aquí veríamos un cero, que tendríamos que 'eliminar' con algún formato condicional o quizá personalizado!!.
Y finalmente en J3 para obtener el parcial por país añadimos un SUMAR.SI =SUMAR.SI(Tabla1[País];$M$1;Tabla1[Unidades])
O si eres un amante de las nuevas funciones desbordadas =SUMA(INDICE(H4#;0;3))
que suma la tercera columna del rango devuelto por FILTRAR.
Hasta aquí la fase de trabajo 'dura', y que debemos ocultar posteriormente :-(
Desarrollaremos por último el rango a visualizar...
Para ello en la celda L4 añadiremos la función ORDENARPOR como sigue: =ORDENARPOR(H4#:H3;G4#:G3;1)
y aquí esta el gran truco del post!!
Debemos fijarnos en cómo hemos alimentado los argumentos..., se observa que la matriz a ordenar se informa como: H4#:H3
curioso cuanto menos... esta forma permite indicar a la función que el rango a ordenar se compone del rango desbordado de FILTRAR (en H4) pero además se incluye la celda H3; que era donde aparecía el texto 'Parcial por país'...
De forma similar, para indicar el segundo argumento de ORDENARPOR y referir cuál es el rango sobre el que ordenaremos, usamos G4#:G3
para incluir los valores ordenados 1,2,3.... incluyendo el orden de la 'Fila de totales' !!
Ya podemos ocultar las columnas auxiliares de cálculo F:J, y listo...
Tenemos, a partir del país elegido en M1, un resultado de registros de dicho país incluyendo un Subtotal al final del rango!!
En estos meses tan tristes que nos ha tocado vivir, con el trágico impacto en nuestras vidas personales y familiares que ha tenido este COVID-19, he visto muchos gráficos en televisión y prensa que mostraban la repercusión en vidas humanas en todo el mundo, y en especial en España.
Pretendo con este post dos cosas, una es hacer visible la realidad de este coronavirus, y la otra (más mundana) sería como conseguir en Excel un gráfico que represente el número de fallecidos sobre un mapa.
El primer paso será desde la Ficha Insertar > grupo Gráficos > desplegable Dispersión-Burbujas > Gráfico de Burbuja
Para que resulte más cómodo el trabajo siguiente ajustaremos el área del gráfico, y numeraré el eje horizontal y vertical de 0 a 10... como se ve en la imagen siguiente el área del gráfico tiene un tamaño de 20 celdas de alto por 5 de ancho (estas celdas se podrán eliminar posteriormente).
El siguiente paso es importante ya que inserta el Mapa deseado, el de España en este caso.
Así pues, tras seleccionar el área del gráfico, accederemos al Formato de área de gráfico > Opciones de gráfico > Relleno > Relleno con imagen o textura > botón Insertar
Desde la ventana de 'Insertar imagenes' siguiente elegiremos el mapa necesario... bien desde un Archivo que ya tengamos, bien desde las Imágenes en línea.
Yo he optado por una búsqueda entre las imágenes online libres (con licencia de Creative Commons).
Siguiente paso. Ahora toca añadir las ubicaciones aproximadas, manualmente a 'ojo de buen cubero', de acuerdo a la cuadrícula anterior respecto a los ejes horizontal y vertical. Esto lo haremos en el rango de celdas C6:D10.
Por ejemplo, la Comunidad de Madrid respecto a esa cuadrícula estaría ubicada en la x=4 y la y=6,5.
Con el rango informado estamos dispuestos para el siguiente paso. Añadir la serie de datos. Haciendo clic derecho encima del área del gráfico seleccionaremos Seleccionar datos...
En la ventana que se abrirá de 'Seleccionar origen de datos' presionaremos 'Agregar' en Series.
Indicaremos los rangos para los valores X de la serie: C3:C7
Para los valores Y de la serie: D3:D7
Y para el tamaño de la burbuja: B3:B7
MUY IMPORTANTE ajustar el área de trazado al área del gráfico!!. En el siguiente paso seleccionaremos las Burbujas agregadas y accederemos al formato de serie de datos > Opciones de serie, donde indicaremos que queremos que el tamaño representa el Área de la burbuja y por otro lado que deseamos una escala de la burbuja al 75%
Solo nos queda añadir las etiquetas de datos a las burbujas centradas y cambiar el color de la fuente...
Además, desde las opciones de las etiquetas indicaremos que se muestre como Contenido de la etiqueta el Tamaño de la burbuja y listo.
Hace una semana, rebuscando por la www, me encontré con el canal de Leila Gharani (muy recomendado!!) donde mostraba un gran truco al aplicar la función desbordada FILTRAR.
Me permito escribir sobre este 'tip' para extenderlo y hacerlo de general conocimiento por lo práctico y útil.
Como se observa en la imagen anterior, es posible obtener un desbordamiento de la función FILTRAR de aquellos registros que cumplan la condición dada (ventas de más de 90 unidades), pero de aquellos campos NO anexos, i.e., discontinuos que hayamos marcado como VERDADERO !!.Si has empleado ya esta función FILTRAR (disponible de momento solo para suscriptores de MS 365) entenderás la potencia del truco!. El truco es incluir en el rango B1:G1 una secuencia de VERDADEROS o FALSOS, según queramos mostrar o no dicho campo... igualmente válido emplear valores 1 y 0, ya que de todos es sabido que
VERDADERO = 1
FALSO = 0 Entonces en J6 añadimos nuestra fórmula desbordada: =ORDENAR(FILTRAR(FILTRAR(TblPedidos;TblPedidos[Unidades]>=$K$2);B1:G1);3;-1)
que descomponemos de la siguiente manera.... desde lo más profundo de la fórmula tenemos FILTRAR(TblPedidos;TblPedidos[Unidades]>=$K$2)
Que nos devuelve TODOS los campos de la Tabla de trabajo que verifiquen la condición aportada de unidades vendidas...
Comprobamos como conseguimos los registros deseados PERO mostrando todos los campos del origen (Fecha País Sección Comercial Producto Unidades)
Ahora viene lo interesante... a esta matriz obtenida volvemos a aplicarle de nuevo la función FILTRAR indicando en el segundo argumento include el rango B1:G1, o si lo prefieres añadir manualmente la matriz
{FALSO\VERDADERO\FALSO\FALSO\VERDADERO\VERDADERO}
o
{0\1\0\0\1\1}
OJO con los indicadores de columnas, ya que dependiendo de la configuración podrían ser , ; \ o incluso /
Así pues con FILTRAR(FILTRAR(TblPedidos;TblPedidos[Unidades]>=$K$2);B1:G1)
también podríamos, según comentado FILTRAR(FILTRAR(TblPedidos;TblPedidos[Unidades]>=$K$2);{0\1\0\0\1\1})
tendríamos lo buscado...
se hizo la magia... obtenemos únicamente los campos deseados!!. ASOMBROSO!.
Finalmente aplicaremos la función ORDENAR para ver esos registros, sobre los campos requeridos, ordenados en sentido descendente según las unidades... =ORDENAR(FILTRAR(FILTRAR(TblPedidos;TblPedidos[Unidades]>=$K$2);B1:G1);3;-1)
el segundo argumento de ORDENAR, el valor 3, nos indica que operamos la ordenación sobre el tercer campo/columna de la matriz devuelta de TRES campos.
Por último, si además queremos automatizar el encabezado del resultante, escribimos en la celda J5: =INDICE(TblPedidos[#Encabezados];FILTRAR(B1:G1*(COLUMNA(B:G)-1);B1:G1*(COLUMNA(B:G)-1)>0))
que nos devuelve los Encabezados de los campos indicados con 1 o VERDADERO
Son muchos los cambios que en los últimos meses estamos viviendo en el entorno de Excel, y muchas discusiones sobre el fin de una época (Scripts versus VBA, entre otras).
Sin dejar pasar estas nuevas oportunidades, sin dejar de aprender todos estos nuevos recursos, no es necesario (en mi opinión personal) dejarse llevar por el nerviosismo.
Está más que demostrado que Excel, hasta la fecha, no abandona ni deja a nadie atrás... incluso 'tecnologías' desfasadas de un pasado remoto de nuestra hoja de cálculo siguen estando operativas. Hablo en particular de las funciones de Excel - macros 4.0 . Hoy en particular hablaré de la función INDICAR.LIBRO (GET.WORKBOOK), con su sintáxis: =INDICAR.LIBRO(tipo;[Libro])
El segundo argumento, opcional, indicaría el libro de trabajo sobre el que actúa la función.
Si no indicamos nada trabajará sobre el libro abierto.
El primer argumento 'Tipo' admite valores enteros entre 1 y 38, siendo los más útiles:
1 - para recuperar una matriz/listado horizontal de todas las hojas del libro, en la forma [Libro]Hoja
3 - matriz/listado horizontal de todas las hojas seleccionadas del libro
4 - el número de hojas en el libro
38 - el nombre de la hoja activa.
Recordemos que para interactuar con estas funciones macros 4.0 debemos incorporarlas dentro del contexto de Nombres definidos. Hoy emplearemos esta posibilidad para obtener un índice de las hojas de nuestro libro de trabajo, sobre el que construiremos anexo a éste un hipervínculo para movernos entre hojas...
El primer paso necesario será crear un Nombre definido ('VinculoHojas') con la siguiente fórmula: =DERECHA(TRANSPONER(INDICAR.LIBRO(1));LARGO(TRANSPONER(INDICAR.LIBRO(1)))-ENCONTRAR("]";TRANSPONER(INDICAR.LIBRO(1)))) & T(AHORA())
Aspectos relevantes de la fórmula empleada... Primero: usamos TRANSPONER sobre la fúnción INDICAR.LIBRO(1) ya que esta devuelve una matriz horizontal de datos... y la necesitamos en Vertical!. Segundo: al devolver el nombre de la hoja en su forma [Libro]Hoja aplicamos la combinación clásica:
=DERECHA(texto;LARGO(texto)-ENCONTRAR("]";TEXTO))
lo que permite recuperar únicamente el nombre de la hoja. Tercero: concatenamos al final de la fórmula anterior la función T(AHORA()) para incorporarle volatilidad ante los cambios en el número de hojas... Si bien siempre habrá que Calcular (presionar F9) para refrescrar los datos si aparecen hojas nuevas... Si trabajamos con Microsoft 365 operaremos con su forma desbordada y bastará escribir en B3 la llamada a nuestro nombre definido =VinculoHojas
Por decorar un poco más el índice en A3 añadimos un ordinal: =FILA(B3#)-FILA($B$3)+1
haciendo referencia a la fórmula desbordada creada en B3... lo que se hace empleando #.
Y por último, por añadir más funcionalidad, en C3 insertamos la fórmula HIPERVINCULO: =HIPERVINCULO("#'"&B3#&"'!A1")
Listo. Ya tenemos nuestro índice dinámico de hojas...
Como curiosidad observamos que solo la primera celda toma el aspecto de 'celda vinculada' típica; si bien todas ellas son operativas. Si no usas MS 365 tendrás que emplear una combinación de la función INDICE con otras funciones (como FILA) para recuperar los diferentes elementos de nuestra matriz de INDICRA.LIBRO(1).
Revisa esta entrada del blog y te sorprenderas de otra aplicación de esta función macro 4.0
