Blog

La función BUSCARX

La función BUSCARX es una función que se encuentra disponible en Excel a partir de la versión de Excel 2019. También lo tienen disponibles los usuarios de Microsoft 365.

Esta potente función de Excel pertenece al grupo de funciones de búsqueda y llegó con el fin de mejorar una de las fórmulas que con más frecuencia hemos usamos en Excel, que es la fórmula BUSCARV. Supera tanto a la función BUSCARV como BUSCARH, ya que nos sirve para búsquedas en vertical como en horizontal. La ventaja es que es más potente y a la vez muy sencilla de utilizar.

Sintaxis de la función BUSCARX

La función BUSCARX esta buscará en un rango o matriz y devolverá el elemento correspondiente al valor buscado. Si no existe ninguna coincidencia, BUSCARX puede entregar el valor (coincidencia aproximada) más cercano.

La sintaxis de la función BUSCARX es la siguiente:

=BUSCARX(valor_buscado; matriz_buscada; matriz_devuelta; [si_no_se_encuentra]; [modo_de_coincidencia]; [modo_de_búsqueda])

Valor_buscado (obligatorio): Es el valor que se va a buscar.
Matriz_buscada (obligatorio): La matriz o rango en el que se va a buscar la información deseada.
Matriz_devuelta (obligatorio): Es el rango o matriz en el que se quiere que se devuelva el valor o la información solicitada.
[si_no_se_encuentra] (opcional): Si no se encuentra el valor o la coincidencia válida, podrá devolver el texto que se proporcione. En caso contrario la función devolverá el mensaje #N/A.
[modo_de_coincidencia] (opcional): Permite especificar un tipo de coincidencia:
- 0 (coincidencia exacta). Si no se encuentra ningún valor, devolver #N/A. Este valor es el predeterminado.
- -1 (coincidencia exacta). Si no se encuentra ningún valor, devolver el siguiente valor más pequeño.
- 1 (coincidencia exacta). Si no se encuentra ningún valor, devolver el siguiente valor más grande.
- 2 (coincidencia comodín) donde elementos como *, ? y ~ tienen un significado especial.
[modo_de_búsqueda] (opcional): Especifique el modo de búsqueda que se usará (“1” para el primer elemento y “-1” para el último) o inicias una búsqueda binaria.
- 1: realizar una búsqueda empezando por el primer elemento. Este valor es el predeterminado y el más común.
- -1: realizar una búsqueda inversa empezando por el último elemento.
- 2: realizar una búsqueda binaria que se base en que la matriz se ordena en orden ascendente. Si no está ordenada, se devolverán resultados no válidos.
- -2: realizar una búsqueda binaria que se base en que la matriz se ordena en orden descendente. Si no está ordenada, se devolverán resultados no válidos.

Ventajas de la utilización de la función BUSCARX

No es necesario que el valor buscado se encuentre en la primera columna o fila. mientras que con BUSCARV y BUSCARH era necesario que el valor buscado se encontrara en la primera columna o fila, con BUSCARX no es necesario, puede encontrarse en cualquier columna o fila.
Coincidencia exacta por defecto. Mientras que con BUSCARV y BUSVARH, cuando queríamos que se realizara una búsqueda con coincidencia exacta teníamos que incluir en el cuarto argumento la opción de FALSO, en BUSCARX por defecto se entiende que la búsqueda es por coincidencia exacta, que es lo más habitual.
Sirve para búsquedas en vertical y en horizontal. Es decir une dos funciones en una. Ahora podemos hacer lo mismo que antes con la funciones BUSCARV y BUSCARH, usando solo esta función.
Podemos establecer un modo de búsqueda. Nos permite buscar el primer valor que encuentre o el último.
Podemos establecer un modo de coincidencia. Valor exacto, o el siguiente hacia arriba o hacia abajo.
Podemos establecer un valor cuando no encuentre el dato. Nos permite indicarle el valor o dato que queremos que nos devuelva en el caso de que no encuentre el valor.

En el siguiente video veremos un ejemplo de funcionamiento de la función BUSCARX.

Video explicativo:

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

Suscríbete

Ficha personalizada en Excel

Vamos a comprobar lo sencillo que es personalizar la cinta de opciones, insertando una ficha personalizada para nosotros.

En esta ficha podremos incluir los grupos de comandos y los comandos que más utilizamos, bien sea porque sean los comandos que más utilicemos o bien porque queramos tenerlos todos juntos en una sola ficha o pestaña.

Insertar nueva ficha

Para insertar una nueva ficha lo que tenemos que hacer es irnos a la ficha de Archivo, después seleccionamos Opciones y aquí tenemos la opción de Personalizar la cinta de opciones.

Si quiero insertar una nueva ficha personalizada, me sitúo en la ficha a partir de la cual quiero insertar la pestaña nueva y le doy a la opción Nueva pestaña.

Veremos que nos incluye directamente una nueva pestaña y un nuevo grupo. Tanto a uno como al otro les voy a cambiar el nombre.

Así le indicaré a la pestaña el nombre «Frecuentes» y al nuevo grupo lo voy a llamar «Formato».

Ahora, situándome encima de las del grupo Formato, le incluyó los comandos que yo quiera insertar dentro de ese grupo. Si quiero seleccionarlos de entre todos los posibles comandos debo comprobar que tenemos seleccionada la opción «Todos los comandos«.

Aparecerán todos los comandos que tengo disponibles para incluir dentro de ese grupo y seleccionamos los que deseemos incluir dentro de ese grupo.

Una vez seleccionados los comandos deseados le damos a Aceptar y comprobaremos que se ha generado la ficha que acabo de crear, con los dos grupos que he generado y con los comandos que incluido en cada uno de ellos.

Aparte de para tener agrupados los comandos que más utilizamos, tener una ficha personalizada también es muy útil cuando queremos tener a mano algunos de los comandos que no se encuentran directamente en las demás fichas.

Video explicativo:

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

Suscríbete

Tablas de Excel

Es muy importante conocer la diferencia entre un rango de celdas y unas tablas de Excel. En este artíuclo vamos a comprobar las ventajas de utilizar las tablas de Excel.

Las tablas de Excel son una herramienta que se implantó a partir de la versión de Excel 2007 y veremos que su utilización nos ofrece muchas ventajas.

¿Cómo saber si estamos ante una tabla de Excel?

Cuando nos encontramos con un rango de datos similar al que se muestra a continuación podemos pensar que nos encontramos ante una tabla, cuando realmente es un rango de datos con un encabezado resaltado y los bordes en negro.

Sin embargo, no es una tabla, con la ventajas que esta nos ofrece.

La principal forma de saber si estamos ante una tabla en Excel es que cuando nos situamos sobre cualquier celda de la misma se muestra la pestaña Diseño de tabla.

Otra forma de identificar una tabla es que en la parte inferior derecha de la tabla se muestra un pequeño ángulo azul que sirve para delimitar esa tabla.

De hecho, podemos mover ese ángulo para aumentar o disminuir el rango de la tabla.

¿Cómo se crea una tabla?

Lo más indicado, cuando queremos crear una tabla es que los datos de partida no tengan ningún formato.

Para crear una tabla lo que tenemos que hacer es situarnos encima de cualquier celda de la tabla y nos vamos a la pestaña Inicio y le damos aquí el botón Dar formato como tabla. Podemos elegir directamente el estilo que nosotros nos interese.

Si elegimos cualquier estilo, se nos abre la ventana Crear tabla, donde es muy importante indicarle si la tabla tiene encabezados o no. Al darle al botón Aceptar nos creará la tabla con ese estilo que hemos seleccionado.

Otra manera de crear una tabla es situarnos encima de cualquier celda de la tabla y nos vamos a la pestaña Insertar y le damos aquí el botón Tabla.

De esta forma también nos abre la ventana Crear tabla.

También podemos abrir la ventana Crear tabla con la combinación de teclas Ctrl+T.

De cualquiera de esas tres formas podemos crear una tabla. Lo importante es comprobar que el rango está bien determinado y si se ha señalado o no que tiene encabezados.

Dándole a Aceptar ya tenemos creada nuestra tabla.

Ventajas de las tablas en Excel

La utilización de las tablas nos ofrece muchas ventajas frente a la utilización de un rango de datos, como veremos a continuación.

Ventaja 1: Es muy sencillo cambiar el estilo

Si hemos creado una tabla es muy fácil cambiar el estilo de la misma. Para ello únicamente nos tenemos que colocar encima de unas de las celdas y dentro de la ficha diseño de tabla podemos elegir el estilo que nosotros queramos. Solamente situándonos encima nos va mostrando cómo se quedaría la tabla y podemos elegir la que más nos guste.

Además podemos cambiarle a otras Opciones de estilo de tabla. Una vez situado encima de cualquier celda podemos indicarle que la primera columna nos la señale de otro color, o bien que sea la última columna, o bien podemos indicarle que en columna alterna nos muestre bandas… Como vemos, solamente haciendo clic o señalando uno u otro estilo, nos cambia directamente el estilo de la tabla. Cuando teníamos un rango todo esto había que hacerlo de una forma manual.

Ventaja 2: Es muy sencillo incluir totales

La segunda ventaja es que podemos incluir una fila de totales de una forma automática. Cuando teníamos un rango de datos teníamos aplicarle una fórmula de suma o de lo que deseemos.

Sin embargo, con una tabla, situándonos en cualquier celda, en Diseño de tabla podemos indicarle que nos incluya una fila de totales y directamente nos incluye la fila de totales.

Si le indicamos que incluya una fila de totales aparecerá debajo.

Vemos que nos ha escrito Total y muestra la suma del beneficio. Pero para cualquier campo, si hacemos clic en la última fila nos muestra un desplegable donde podemos indicarle de qué forma queremos totalizar los datos.

Ventaja 3: Se actualiza automáticamente el rango de la tabla

La tercera ventaja es que se actualiza automáticamente el rango de la tabla. Si nosotros añadimos un nuevo registro por ejemplo el 06/06/2021, directamente nos amplía el rango de esta tabla.

De igual manera ocurre si nosotros incluimos una nueva columna, esta se incorpora dentro del rango de la tabla.

Ventaja 4: Nombres intuitivos de los campos

Una cuarta ventaja es que es mucho más intuitivo la aplicación de una fórmula. Si, por ejemplo, queremos calcular el beneficio, que sería ingresos menos gastos, cuando escribo la fórmula directamente nos indica que es Ingresos menos Gastos.

No nos indicaría C2 – D2, que serían menos intuitivo.

Ventaja 5: Las fórmulas se rellenan automáticamente

Otra ventaja es que las fórmulas se rellenan de forma automática. Así, si cálculo el beneficio que como hemos visto era ingresos menos gastos, vemos que nos lo aplica a todas las celdas que están en esa columna.

Esta propiedad de las tablas es muy interesante ya que con hacer cualquier cambio en una celda, lo aplica directamente a todas las demás celdas de esa columna.

Ventaja 6: Segmentación de datos

Una de las mejores ventajas que tiene la tabla de Excel es que podemos insertar una segmentación de datos. Esto ya lo vimos en un artículo anterior.

La segmentación de datos, que es una forma muy sencilla y visual de filtrar los datos solamente se puede aplicar a tablas o tablas dinámicas.

¿Cómo convertir una tabla en un rango de datos en Excel?

En caso de que, a pesar de las ventajas que nos ofrecen la tabla, prefiramos trabajar con un rango de datos (sin formato de tabla) nos tendríamos que situar encima de cualquier celda de la tabla y, en la pestaña Diseño de tabla seleccionamos la opción Convertir en rango.

De esa forma, los datos dejarán de tener formato de tabla.

Video explicativo:

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

Suscríbete

Gráficos interactivos

En Excel no es preciso que los gráficos sean un objeto estático. Podemos lograr que sean gráficos interactivos, en el sentido de que varíen en función del contenido de una celda, por ejemplo.

Esto sería aplicable a cualquier tipo de gráfico y esa interactividad la obtendremos haciendo uso de una tabla intermedia que calculará los valores en función de la opción que elijamos.

Creación de un gráfico interactivo en Excel

Queremos crear un gráfico que nos muestre los datos según un valor que indiquemos en una celda.

En nuestro caso tenemos una serie de ventas por zonas y el gráfico muestra las ventas de todas las zonas cuando en la celda I4 le indicamos «Todas las zonas«.

En cambio, si seleccionamos una zona en I4, como por ejemplo, «Centro«, solamente me muestra el gráfico las ventas de la zona Centro.

Utilización de una tabla auxiliar

Para crear este tipo de gráficos tendremos que usar una tabla auxiliar que muestre todos los datos cuando en la celda I4 se indique esa opción, y en caso de seleccionar una zona concreta, solamente muestre los datos correspondientes a esa zona.

Hacemos un copia y pega en la zona inferior. Ahora nos quedaría rellenar la tabla auxiliar con los datos precisos, utilizando para ello una fórmula.

La fórmula que utilizaremos anidará dos funciones: la función SI y la función O.

En la columna O tenemos escritas todas las posibilidades para rellenar la celda I4, mediante una lista desplegable. por eso en la fórmula hacemos referencia a la celda O1.

También podríamos escribir el texto «Todas las zonas» como vemos en la siguiente imagen.

Como vemos en el gráfico lo que hace esta fórmula es comprobar el valor de la celda I4.

Si el valor es «Todas las zonas» o bien es igual a la zona de esa fila le pedimos que nos rellene el valor de la tabla superior correspondiente a esa zona.
En caso contrario, le indicamos que nos deje la celda en blanco.

Aplicando las referencias absolutas correspondientes podremos rellenar con esta fórmula todas las celdas de la tabla auxiliar.

Creación del gráfico dinámico

Ahora crearíamos el gráfico interactivo a partir de los datos que aparecen en la tabla auxiliar.

Para ello, nos situamos encima de cualquier celda de ese gráfico y le insertamos un gráfico, en nuestro caso, de columnas 2D apiladas.

Lo que podemos hacer es tapar con el gráfico la tabla auxiliar para que no se vea.

De esta forma ya tendríamos el gráfico interactivo que muestra los datos según el valor que incluya una celda.

Video explicativo:

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

Suscríbete

Segmentación de datos

Las segmentaciones de datos proporcionan botones en los que puede hacer clic para filtrar tablas o tablas dinámicas. Además del filtrado rápido, las segmentaciones de datos también indican el estado de filtrado actual, lo que facilita la comprensión de lo que se muestra exactamente en ese momento.

La segmentación de datos nos proporciona una manera fácil y muy visual de filtrar datos de una tabla o tabla dinámica, permitiéndonos hacer los filtros necesarios para facilitarnos la visualización de la información que precisamos.

Crear una segmentación a partir de una tabla o tabla dinámica

Tenemos que tener en cuenta que la segmentación de datos solamente es posible crearla a partir de una tabla de Excel o una tabla dinámica.

Crear una tabla en Excel

Si tenemos un rango e datos tendremos que transformarlo primero en una tabla de Excel.

Partimos de un rango de datos que tienen las ventas en una serie de países, ciudades y vendedores.

Nos situamos encima de cualquier valor de esos datos y pulsamos en la pestaña Insertar el botón Tabla.

Se abre una ventana que nos muestra el rango que ha detectado y nos pregunta si los datos tienen encabezado.

Cuando le damos a Aceptar, veremos que ya nos ha convertido el rango de datos en una tabla de Excel, que tiene muchas ventajas respecto de tener los datos en un rango.

Crear la segmentación de datos

Cuando tenemos una tabla y nos seleccionamos un dato de la misma, se muestra una nueva pestaña llamada Diseño de tabla, que nos ofrece gran cantidad de posibilidades de estilo, formato y herramientas.

Una de las herramientas es la de Insertar segmentación de datos.

Si pulsamos sobre ese botón nos muestra todos los campos de la tabla que permiten una segmentación.

Nosotros vamos a seleccionar País, Ciudad y Vendedor y le damos a Aceptar. De esa forma aparecerán las diferentes segmentaciones de datos.

Cada uno de esas segmentaciones es un objeto que se puede mover y modificar como podríamos hacer con cualquier otro objeto en Excel.

Cambios en segmentaciones de datos

Para modificar el aspecto de cualquier segmentación de datos hacemos clic sobre la misma y veremos que se muestra una pestaña llamada Segmentación que nos ofrece numerosas posibilidades de estilo, tamaño, organización y configuración,

Tras hacer cambios en el estilo, el número de columnas y el alto de las columnas obtenemos unas segmentaciones como las siguientes.

Filtrar datos con las segmentaciones de datos

Filtrar los datos con las segmentaciones es muy sencillo ya que únicamente tendremos que pulsar el botón correspondiente al filtro que deseemos realizar.

Por ejemplo, si deseamos filtrar por las ventas realizadas en España, pulsamos en el botón de España y nos hará directamente el filtro en la tabla.

Vemos que la tabla ha quedado filtrada por el país seleccionado.

También podemos comprobar que de los demás segmentadores hay botones con el color original y otros con el color más claro.

Los que tienen un color más claro indican que de esos filtros no hay ningún dato dentro de la tabla resultante.

Ocultar los elementos que no contienen datos

Si queremos que en lugar de aparecer con un color más claro no aparezcan dentro de las segmentaciones de datos, tendremos que hacer el siguiente cambio en la Configuración de Segmentación de datos.

En la ventana que se abre marcamos Ocultar los elementos que no contienen datos.

Así podemos comprobar que no aparecen esos botones que no tienen datos.

Hacer varios filtros en una segmentación de datos

Para hacer varios filtros dentro de una misma segmentación tenemos dos opciones:

Pulsar el botón de selección múltiple que se encuentra en la parte superior, al lado del embudo del filtro.

Con ese botón pulsado podemos seleccionar varios botones.

Otra posibilidad, que es más rápida y sencilla, es ir seleccionando los botones mientras tenemos pulsada la tecla Control.

De esa forma hemos seleccionado por las ventas de España y de Francia.

Varios filtros en diferentes segmentaciones de datos

También podemos utilizar varias segmentaciones conjuntamente y me aplica los filtros que cumplen con todas la opciones marcadas.

Para ello, solamente debemos marcar los botones que deseemos filtrar. En este caso, hemos filtrado las ventas en España y Francia, que ha realizado el vendedor Juan Marcos.

Eliminar filtros en segmentaciones de datos

Para eliminar los filtros aplicados en las segmentaciones de datos únicamente tendremos que pulsar sobre el icono con el filtro que se encuentra en la parte superior derecha de la caja de la segmentación de datos.

Haciendo clic sobre ese icono con un embudo y una x roja se eliminará el filtro realizado con esa segmentación.

Como podemos comprobar, con la utilización de las segmentaciones de datos, hacer filtros en tablas o tablas dinámicas es mucho más sencillo y visual.

Video explicativo:

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

Suscríbete

La función UNICOS

La función UNICOS se ha incorporado en el año 2020 y pertenece al grupo de funciones de búsqueda y referencia y además, es una función de matrices dinámicas, lo que significa que el resultado que devuelve puede ocupar más de una celda.

La función UNICOS devuelve una lista de valores únicos de una lista o rango.

Sintáxis de la función UNICOS

Su sintáxis es la siguiente:

=UNICOS(matriz;[by_col];[exactly_once])

Matriz: Es el rango de celdas o matriz de la que queremos extraer los valores únicos. Puede estar formada por valores de cualquier tipo.

By_col: Opcional. Con este argumento le indicamos si queremos que el resultado se muestre por columnas o filas. Es un valor lógico (FALSO o VERDADERO). Por defecto aparece FALSO.

Si se pone FALSO se devolverá el resultado por filas.
Si se pone VERDADERO se devuelve el resultado por columnas.

Exactly_one: Opcional. Con este argumento le indicamos si queremos que nos muestre solo los valores que se encuentran exactamente una vez. Es un valor lógico (FALSO o VERDADERO). El valor por defecto es FALSO.

Si ponemos FALSO devolverá todos los valores una vez.
Si ponemos VERDADERO devolverá solo aquellos valores que aparezca una única vez.

Ejercicio con la función UNICOS

En el siguiente listado aparecen un rango de datos con Países y Ciudades.

Si deseamos tener un listado con los diferentes países indicados nos situaríamos en la celda en la que queremos mostrar el resultado y escribimos =UNICOS(A2:A23)

De esa forma aparecen los 4 países que aparecen en el listado.

Aplicar UNICOS a pares de valores

También podemos aplicar la función para ver los pares de valores. Así si aplicamos la función UNICOS a un rango de datos con más de una columna nos devolverá los valores únicos para las combinaciones de País-Ciudad.

Devolver valores únicos por columna

Si utilizamos el segundo argumento y le indicamos VERDADERO los resultados aparecerán por columna. Esto sería aplicable cuando los valores de origen se encuentran por columnas.