Blog

Rellenar CELDAS EN BLANCO automáticamente

En muchas ocasiones nos habrá pasado que tenemos una tabla de datos en la que algunos campos no se repiten en las celdas y se quedan celdas en blanco.

Así, por ejemplo, en este caso vemos en nombre de unos alumnos, el curso al que se han apuntado y el precio del mismo. Sin embargo, el campo «Nombre» no se repite cuando realiza más de un curso.

Esto nos puede generar muchísimos problemas sobre todo si queremos trabajar con tablas o tablas dinámicas basándonos en esos datos.

Sin embargo existe una forma muy sencilla de rellenar estas celdas en blanco de una forma directa.

Rellenar celdas en blanco

Para rellenar las celdas en blanco con el valor que se encuentra en la celda superior lo primero que tenemos que hacer es seleccionar todo el rango en el que se encuentran las celdas en blanco.

Ahora, dentro de la pestaña Inicio iríamos al icono del comando Buscar y seleccionar y desplegamos su menú.

Del menú seleccionamos Ir a Especial…

Se nos abre la ventana Ir a especial, donde señalamos la opción Celdas en blanco y le damos al botón Aceptar.

De esa forma conseguimos que se queden seleccionadas todas las celdas que se encuentra en blanco.

A continuación nos tenemos que fijar en cuál es la celda activa. Eso lo vemos en el cuadro de nombre y porque, de las celdas seleccionadas es la que tiene un color en blanco. En nuestro caso la celda activa es B4.

Siempre será la primera celda en blanco del rango que hemos seleccionado.

En nuestro caso la celda activa es B4.

Para rellenar todas las celdas en blanco lo que hacemos es escribir en la barra de fórmulas el símbolo igual seguido de la dirección de la celda inmediatamente superior a la celda activa. Es decir =B3.

Ahora, en lugar de darle únicamente a la tecla Intro, pulsaremos la combinación teclas Ctrl+Intro.

De esa forma vemos que nos ha rellenado de forma directa todas las celdas en blanco con el nombre que se encontraba en la parte superior. Ya sí podríamos aplicarle cualquier formato de tabla o tabla dinámica a estos datos.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

La herramienta CÁMARA en Excel

Excel incorpora diversas herramientas que, aunque sean muy fáciles de utilizar, en muchas ocasiones son desconocidas para muchos usuarios.

En este caso se encuentra la herramienta cámara. Esta herramienta nos permite hacer una captura de una zona de nuestra hoja de Excel y tratarla como si una imagen fuera. Sin embrago, no será una imagen corriente, sino que es una imagen dinámica, vinculada al origen de datos.

Por tanto, si yo cambio en el origen de los datos cualquier número cualquier valor me lo cambia también en la imagen.

Activar la herramienta cámara

La forma más sencilla para activar la herramienta cámara y que la tengamos disponible para su utilización es incluirla en la barra de herramientas de acceso rápido.

Para ello, hacemos clic sobre el desplegable a la izquierda de la barra de herramientas de acceso rápido y seleccionamos del menú la opción Más comandos…

Se nos abre la ventana de Opciones de Excel y el el apartado de Comandos disponibles en seleccionamos Todos los comandos.

Ahora tenemos en la parte central un listado de todos los comandos disponibles, ordenados alfabéticamente. Buscamos el comando Cámara y le damos al botón Agregar>>>.

De esa forma el comando aparecerá en la parte izquierda, donde aparecen los comandos incluidos en la barra de herramientas de acceso rápido. Finalmente pulsamos en el botón Aceptar.

Vemos que ahora nos aparece el comando Cámara en la barra de herramientas de acceso rápido.

Utilización del comando cámara

Partimos de una serie de datos que tenemos en una tabla.

Vamos a hacer una captura del rango A1:B3 utilizando el comando cámara.

Para ello primero tenemos que seleccionar el rango de datos que queremos capturar le damos clic al icono de cámara.

Ahora nos desplazamos al lugar en el que queremos que se pegue esa imagen hacemos clic con el botón izquierdo del ratón.

De esa forma ya tenemos una imagen que podemos mover o cambiarle el formato, como si se tratase de cualquier otro tipo de imagen.

En nuestro ejemplo que hemos dado una sombra y redondeado las esquinas.

Lo mejor es que, a pesar de ser un objeto que funciona a todos los efectos como una imagen, si hago cualquier modificación en los datos de origen, también se cambia en la imagen.

Así por ejemplo voy a modificar el valor de la celda B2, y en lugar de un 2 voy a incluir un 4.

Podemos comprobar que al escribir un 4 en la celda B2, hace también el cambio en la imagen que hemos creado con la herramienta cámara.

Por tanto, esta herramienta es ideal para su utilización en informes o cuadros de mando que incluyan imágenes que estén enlazadas con un origen de datos.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

La función FORMULATEXTO

La función FORMULATEXTO es una función de búsqueda y referencia, que sirve para devolver una fórmula como cadena de texto. Es decir se muestra en la celda la fórmula tal y como está escrita en la barra de fórmulas.

La sintaxis de la función FORMULATEXTO es:

=FORMULATEXTO(referencia)

donde referencia es una referencia a una celda o rango de celdas.

Ejercicio con la función FORMULATEXTO

En este ejemplo tenemos una serie de personas y sus ingresos.

En la celda B11 tenemos el cálculo de la suma de los ingresos. Para ello se ha utilizado en la celda B11 la fórmula SUMA(B2:B10).

Si deseamos que en la celda C11 se muestre la fórmula que hemos utilizado en la celda B11, lo único que tenemos que hacer es escribir en la celda C11 la fórmula =FORMULATEXTO(B11)

De esa forma nos muestra la fórmula utilizada.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Gráficos con formato condicional

Vamos a ver cómo podemos aplicarle un determinado formato condicional a un gráfico.

Lo que queremos conseguir es un efecto similar a que vemos en la siguiente imagen.

Tenemos una serie de ventas por día de semana y queremos que el gráfico nos muestre en otro color, por ejemplo rojo, el día de mayores ventas. Además queremos que sea algo dinámico. Si cambiamos los valores el gráfico se actualice y nos muestre en color rojo el día o los días de mayores ventas.

Uso de columna auxiliar

Si queremos utilizar un formato condicional en un gráfico la única forma que tenemos es haciendo uso de otra serie de datos que nos sirva de apoyo.

En nuestro caso utilizaremos la columna C en la que mostraremos los datos que cumplan nuestra condición. Si cumple nuestra condición nos muestra el mismo valor. Y en caso de que no lo cumplan nos devuelve el cero.

Como en nuestro caso queremos que nos muestre el día con más ventas utilizaremos la función MAX.

Creación de gráfico

Ahora solamente tenemos que crear el gráfico incluyendo los datos auxiliares.

Hemos utilizado un gráfico de columnas en 2D en el que aparecen 2 series: una para los valores y otra para el valor máximo.

Ahora lo que queremos es sobreponer ambas series para que la serie del máximo se muestre por encima de la serie de los valores.

Para ello haremos las siguientes modificaciones.

Cambio de colores

Utilizare la serie normal en verde y la del máximo en rojo para que resalte más.

Superposición de datos

Para que se produzca la superposición de ambas series señalamos cualquier serie y le damos al botón derecho del ratón y le damos a Dar formato a serie de datos…

Ahora aparece lo siguiente como Formato de serie de datos, donde vamos a cambiar la Superposición de series (en lugar de -27% indicaremos 100%).

También podemos cambiar el ancho de las columnas en Ancho del Rango (en lugar de 219% indicaremos 80%).

De esa forma hemos creado un gráfico de de forma dinámica tiene un formato condicional.

Para establecer qué serie es la que se muestra delante o detrás tendremos que seleccionar el gráfico y, en la pestaña, Diseño de gráfico elegimos Seleccionar datos.

Se nos abre la ventana Seleccionar origen de datos y en la parte izquierda se encuentran las 2 series que hemos utilizado.

La serie que se encuentra debajo es la que se muestra por encima cuando se superponen las series en el gráfico. Seleccionando la serie y con la flechas hacia arriba o abajo podemos moverlas de sitio.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Emojis dinámicos en Excel

Veremos un truco para insertar emojis de una forma dinámica en Excel. De esta forma podemos mejorar mucho el aspecto visual de algún informe o alguna tabla.

Lo que queremos conseguir es algo similar a los que vemos a continuación.

Así vemos que podemos tener un listado de goleadores en los que aparece el número de goles y, en la columna de al lado, representados los números de balones que corresponden al número de goles que han marcado.

Igual podríamos indicar el número de estrellas de los hoteles y que en función del número que indiquemos aparezcan representadas las estrellas.

Lo mejor es que es totalmente dinámico. Conforme cambiamos el número, se van modificando los emojis representados.

Insertar emojis en Excel

Para insertar emojis en Excel tendremos que darle a la combinación de teclas Windows+punto.

De esa forma se abre una ventana con todos los emojis.

En la parte inferior tenemos una serie de iconos que nos permiten buscar el emoji más fácilmente, bien haciendo una búsqueda (lupa), eligiendo entre los recientemente utilizados (reloj) o según alguna de las categorías representadas.

Solamente tendríamos que seleccionar el emoji correspondiente para que se incorpore en una celda.

Emojis dinámicos

Sin embargo, en este caso deseamos obtener emojis dinámicos, que se repitan en función de un número indicado en otra celda.

Para ello utilizaremos la función REPETIR, que lo que hace es repetir un texto un número determinado de veces. Aquí lo que haremos es repetir emojis en lugar de texto.

Partimos de los datos con la columna C en blanco.

No situamos en la celda C4 y tecleamos =REPETIR. Abrimos paréntesis y en lugar de escribir un texto incorporamos entre comillas un emoji. En nuestro caso un balón de fútbol.

Para indicarle el número de veces que queremos que se repita ese balón lo referenciamos a la celda B4.

Si le damos a Intro aparecen reflejados los 7 balones correspondientes a los goles marcados.

Si arrastramos la fórmula hacia abajo aparecen los goles con emojis para todos los jugadores.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Incorporar un CUADRO DE BÚSQUEDA en Excel

Vamos a ver cómo podemos incluir dentro de una lista de datos un cuadro de búsqueda. Para ello nos ayudaremos de la utilización de tablas dinámicas y también de un código que insertaremos en el Editor de Visual Basic.

Lo que queremos conseguir es algo similar a lo que vemos a continuación.

Nos encontramos con un listado de todos los países del mundo y en la celda A2 tenemos un cuadro resaltado en gris que nos permitirá hacer una búsqueda de una forma dinámica. Conforme estemos escribiendo en la celda A2 se van filtrando los países que contienen esa cadena de texto.

Esto puede ser muy útil cuando utilizamos tablas muy grandes de artículos, clientes, datos personales, proveedores… donde podremos hacer búsquedas, filtrando conforme escribimos.

Veremos cómo podemos hacerlo.

Crear tabla dinámica

Partimos del listado de países y lo primero que haremos es crear una tabla dinámica con los mismos.

Para ello nos situamos sobre cualquier elemento de la tabla y le damos a la ficha Insertar – Tabla dinámica.

El único campo de la tabla dinámica es el de los Países y lo arrastramos al área de filas.

De esa forma tendremos una tabla similar a la que teníamos antes pero que ahora es una tabla dinámica.

Ajustes en tabla dinámica

A esta tabla dinámica le realizaremos los siguientes ajustes.

Eliminar el total general

En la parte inferior de la tabla aparece un Total general que no queremos que se muestre.

Para ello nos situamos encima de la tabla dinámica y vamos a la pestaña Diseño y en Totales generales, le damos a «Desactivado para filas y columnas».

De esa forma habrá desaparecido ese Total general.

Eliminar etiquetas de fila

También queremos que no aparezca el encabezado Etiquetas de fila

Para ello, situándonos de nuevo encima de cualquier valor de la tabla dinámica, le damos con el botón
derecho del ratón, elegimos Opciones de tabla dinámica, y en la pestaña de Mostrar, quitamos «Mostrar títulos de campo y filtrar desplegables«.

Con ello ha desaparecido el título de campo.

Eliminar autoajuste del ancho de columna

No queremos que se nos autoajuste el ancho de la columna cada vez que haga una búsqueda. Para ello nos situamos sobre los datos de la tabla y le damos al botón derecho del ratón, Opciones de tabla dinámica y en Diseño y formato, quitamos el check de Autoajustar anchos de columnas al actualizar.

Cambiar el nombre de la tabla dinámica

Necesitaremos conocer el nombre de la tabla dinámica para lo cual lo ideal es ponerle un nombre adecuado.

Para cambiar el nombre de la tabla dinámica nos situamos encima de cualquier valor de la misma y nos vamos a la pestaña Análisis de tabla dinámica.

Después deplegamos el botón Tabla dinámica y ahí nos aparece el nombre de la tabla que podremos sustituir por cualquier otro, por ejemplo, «TDBuscar«

También vemos que aparece que el campo activo es el de PAISES.

Esos dos nombres, el de la tabla (TDBuscar) y el del campo (PAISES) los necesitaremos para incorporarlos en el código que utilizaremos a continuación.

Insertar un cuadro de texto

Aunque podía parecer que la celda A2 es una celda normal, realmente es un cuadro de texto que hemos situado justo encima de la celda.

Para ello deberemos ir a la pestaña Programador (o Desarrollador, según la versión de Excel que tengamos).

Si no tenemos visible esa pestaña deberemos activar la pestaña Programador.

Dentro de la pestaña, nos vamos a la opción Insertar y seleccionamos cuadro de texto que es el que se marca en color rojo a continuación.

Dibujamos un cuadro encima de la celda A2.

Si queremos cambiar el relleno del cuadro de texto para que no sea blanco podemos hacerlo dándole a la opción Propiedades y seleccionando otro color dentro de BackColor.

Para que este cuadro de texto funcione tendremos que situarnos encima y le damos a «Ver código».

Se abrirá el Editor de Visual Basic y debemos escribir el siguiente código:

Private Sub TextBox1_Change()
With ActiveSheet.PivotTables(«TDBuscar»).PivotFields(«PAISES»)
.ClearAllFilters
.PivotFilters.Add2 Type:=xlCaptionContains, Value1:=TextBox1.Text
End With
End Sub