11 funciones avanzadas de Excel que te harán la vida más fácil

Tenemos muy claro que una de las herramientas fundamentales de cualquier empresa son las hojas de cálculo. Estas nos facilitan horas de trabajo en el manejo de datos de contacto, administración de recursos, pagos a proveedores y más. Requiere práctica aprender a usarlas, pero son verdaderas maravillas que están a nuestro favor cuando las conocemos a profundidad.

Por ello que queremos compartir contigo algunas funciones avanzadas que te ayudarán a agilizar tus tareas relacionadas con el Excel. Aquí encontrarás casos concretos en los que puedes aplicar las fórmulas en la gestión empresarial y mejorar así tu rendimiento en el trabajo.

1. Insertar y quitar columnas rápido

Veamos el siguiente caso, en esta tabla es necesario agregar dos columnas entre las letras A y B. Para ello nos colocamos en la columna B y pulsamos las teclas Ctrl + Barra espaciadora del teclado; se va a seleccionar toda la columna.

Luego tenemos dos opciones:

1. Tecleamos Ctrl + tecla de más dos veces y ya tenemos nuestras dos filas.
2. Pulsamos Shift + flecha izquierda una vez, para que queden seleccionadas dos columnas; luego tecleamos Ctrl + tecla de más y listo.

En el caso de querer eliminar una columna aplicaremos los mismos pasos, pero con el signo de menos. Una vez colocados en la columna que queremos quitar:

1. Oprimimos Ctrl + Barra espaciadora para seleccionar toda la columna.
2. Luego, tecleamos Ctrl + tecla de menos y la columna desaparece.

2. Añadir y eliminar filas velozmente

En el caso de que necesitemos añadir más filas, vamos a ubicarnos en la casilla donde queremos insertarlas y vamos a teclear Shift + Barra espaciadora para seleccionar toda la fila. 

Igual que con las columnas, tenemos estos dos modos para las filas.

1. Tecleamos Ctrl + tecla de más las veces que necesitemos y automáticamente se añaden.
2. Pulsamos Shift + flecha abajo o Shift + flecha arriba hasta seleccionar el número de filas que queramos; al final tecleamos Ctrl + tecla de más.

Para eliminar una fila determinada vamos a colocarnos en ella  y luego:

1. Damos a Shift + Barra espaciadora para seleccionar toda la fila.
2. Pulsamos las teclas Ctrl + tecla de menos, y adiós a la fila.

3. Lista desplegable

Esta es una de las funciones avanzadas de Excel más prácticas para encontrar un valor específico en una columna. Es como un pequeño buscador donde escribes el número o la palabra y te dice si está en la lista o no. Está función nos puede servir para verificar un producto en el inventario, por ejemplo. 

Supongamos que uno de tus clientes te pregunta si tienes un producto en verde oscuro; en lugar de checar casilla por casilla utilizas la Lista desplegable. Para ello selecciona una casilla fuera de la columna de colores y en la pestaña superior de «Datos» da clic en «Validación de datos» y saldrá este recuadro.

En la parte de «Permitir» en la flechita negra vamos a indicar «Lista» y en la parte de «Origen» en la flechita roja seleccionamos los datos de la columna de colores. Luego le damos en «Aceptar». 

Ya tenemos nuestra lista desplegable donde podemos consultar si el verde oscuro está en nuestro inventario al escribirlo y dar clic en la flechita negra de al lado de la casilla. En caso de no encontrarlo o teclearlo mal, el mismo programa nos lo hace saber con un anuncio de error.

4. INDICE

Índice es una función que soluciona las limitaciones de búsqueda de BUSCARV, porque en ella solo podemos buscar colores que se encuentran en la primera columna de la izquierda. En el caso de INDICE no importa en qué columna o fila se encuentre el valor, podríamos decir que convierte una tabla en un mapa cartesiano donde indicamos las coordenadas de valor que queremos. 

Supongamos que quieres consultar las comisiones que generaron cada uno de los vendedores de tu empresa a lo largo de cada mes. Bien, vamos a colocarlos en una casilla, poner = y a continuación buscamos INDICE(matriz; núm_fila; [núm_columna]) donde:

• matriz: es el rango de filas y columnas donde se va a buscar, en este caso de la tabla de datos.
• núm_fila: corresponde al número de fila donde se encuentra el valor que buscamos dentro de la matriz.
• núm_columna: es el número de columna con el valor que buscamos dentro de la matriz.

Ahora lo que nosotros queremos saber es cuánto ganó por comisión Carlos Altamirano en enero. Cuando nos pide el valor de «Matriz» vamos a seleccionar toda la tabla con el mouse y ponemos una coma. 

Luego indicamos la fila con el número 4, coma otra vez, colocamos el número 2 para la columna, cerramos el paréntesis y listo: ya tenemos el valor que buscamos. Recuerda que los valores de fila y columna corresponden a lo que está dentro de la tabla, como nos lo indican los números rojos de la imagen; no son los de toda la hoja directamente.

5. Búsqueda avanzada

Una vez explicadas las funciones Índice y Listas desplegables vamos a unirlas con COINCIDIR para encontrar valores específicos en bases de datos extensas. Estas tres funciones en acción son una verdadera maravilla para consultar datos.

Para buscar nuestros datos casi de manera automática tomaremos la siguiente tabla como ejemplo. Lo primero que vamos a hacer es una Lista desplegable de la columna de vendedores. Damos clic en la casilla de la lista, en la pestaña de «Datos» damos clic en «Validación de datos»; en el recuadro donde dice «Permitir» indicamos «Lista» y en «Origen» seleccionamos la columna de vendedores.

Vamos a seguir el mismo procedimiento en la casilla de al lado para hacer la Lista desplegable de los meses, solo que en el último paso de «Origen» seleccionamos la fila con enero, febrero, marzo y abril.

Aquí es donde haremos la combinación de funciones en lugar de esta que ya habíamos visto: INDICE(matriz; núm_fila; [núm_columna]). Vamos a reemplazar los valores de fila por COINCIDIR de fila y COINCIDIR de columna. La fórmula va a quedar así =INDICE(matriz;COINCIDIR(valor_buscado;matriz_buscada;[tipo_de_coincidencia]); COINCIDIR(valor_buscado;matriz_buscada;[tipo_de_coincidencia])).

Para el COINCIDIR de fila es así:

• matriz: son todos los valores contenidos en la tabla.
• valor buscado: es la casilla de la lista desplegable de Vendedores.
• matriz buscada: es la selección de los valores de la fila de Vendedores.
• tipo de coincidencia: es el número 0 para que nos arroje valores exactos.

En el COINCIDIR de columna queda así:

• matriz: son todos los valores contenidos en la tabla.
• valor buscado: es la casilla de la lista desplegable de Mes.
• matriz buscada: es la selección de los valores de la columna de Mes.
• tipo de coincidencia: es el número 0 para que nos arroje valores exactos.

Luego de poner =INDICE nos va a pedir los valores de la «Matriz»; seleccionamos toda la tabla (color azul).

Luego ponemos coma, escribimos la función COINCIDIR; en «Valor buscado» seleccionamos la casilla de la lista desplegable de Vendedor (color rojo); en «Matriz buscada» seleccionamos la columna de Vendedores (color morado); en «Tipo de coincidencia» colocamos 0 y cerramos paréntesis.

Después escribimos otra coma, volvemos a insertar la función COINCIDIR; en «Valor buscado» tomamos la casilla de la Lista desplegable de Mes (color verde); en «Matriz buscada» seleccionamos la fila de los meses (color rosa); en «Tipo de coincidencia» ponemos 0 y colocamos dos paréntesis para cerrar toda la operación.

Ahora ya puedes mover las listas desplegables de acuerdo con los datos que quieres conocer. Selecciona el vendedor y el mes en el que deseas consultar su comisión. Esta fórmula puede aplicarse para cualquier base de datos y seguramente te ahorrará horas de búsqueda.

6. Fijar casilla en operaciones

Uno de los errores frecuentes que realizamos en Excel es que cuando necesitamos que se haga una operación con un mismo valor a lo largo de toda una columna, el resultado no sea correcto. Nos explicaremos mejor con el siguiente ejemplo.

Supongamos que tenemos la lista de precios de nuestros productos y necesitamos obtener el precio total, que incluye el precio más un impuesto fiscal del 15 %. Entonces aplicamos la siguiente operación =precio del producto x (1+15%), que en Excel queda así.

Si arrastramos la fórmula por toda la columna va a lanzar resultados equivocados como vemos en la imagen: 128 euros más un 15 % no son 128 euros, ¿cierto?

La solución a este pequeño inconveniente es fijar la casilla de valor que queremos que se aplique a toda la columna, en este caso es el porcentaje. Para hacerlo agregamos un signo de pesos antes de la letra de la columna y el número de fila; o más fácil aún: ponemos el cursor en ese valor de la operación para enseguida oprimir la tecla F4. Ahora sí extendemos la fórmula hacia abajo y listo.

7. Transponer datos

A veces recibimos tablas o datos que llegan con un formato horizontal, lo cual puede ser un tanto incómodo para trabajar, realizar algunas fórmulas como las condicionales SI, o incluso crear una tabla dinámica. Pero descuida que aquí tenemos la solución.

En primer lugar vamos a seleccionar toda la tabla de datos y la copiamos. Ya sea con Ctrl + tecla C, con un clic derecho del mouse o con el botón «Copiar» que aparece en la parte superior de la hoja. Luego seleccionamos la casilla donde queremos ponerla; en la pestaña de «Inicio» damos clic en la flechita que aparece debajo del botón de «Pegar».

De inmediato se despliega una lista con diferentes opciones. Buscamos la que dice «Transponer», le damos clic y listo, nuestros datos se han vuelto verticales conservando sus valores intactos. En algunas casillas pueden aparecer signos de gato; esto es completamente normal, basta con extender la columna o dar doble clic. 

8. SI. ERROR

Esta fórmula nos sirve para detectar errores en los resultados de las fórmulas que se aplican en la hoja de cálculo. Nos indica cuando la operación no es posible, ya sea porque hay un número dividido por cero o cuando se están juntando dos valores de diferente tipo, es decir, una mezcla de números con letras.

Dentro de los inventarios de un negocio esta fórmula es realmente eficaz para localizar los errores en los resultados e insertar una palabra o frase que nos ayude a ejecutar una acción al respecto. Por ejemplo, en el siguiente caso tenemos un reporte de ventas que indica que el producto set 4 se ha agotado; por lo tanto, no arroja el neto de las ventas. 

Así que vamos a poner =SI.ERROR(valor:valor_si_error), donde:

• valor: es la operación que queremos que revise.
• valor_si_error: es la palabra o signo que queremos que ponga para distinguir el error, la cual hay que poner entre comillas.

En este ejemplo, indicamos la multiplicación de la columna B por la C y colocamos la palabra “REVISAR” para que nos indique dónde está el error.

9. Texto en columna

Texto en columna es una de las funciones de Excel que nos puede ayudar mucho a segmentar la base de datos de leads de nuestra marca. Por ejemplo, si quieres lanzar una campaña de mailing solo a contactos de ciertas regiones. En estos casos las funciones de EXTRAER, IZQUIERDA, MED y DERECHA no nos pueden ayudar, porque cada palabra tiene diferentes caracteres.

Lo primero que debemos hacer es seleccionar nuestra tabla con las direcciones, luego vamos a la pestaña de «Datos» y en la barra superior damos clic en «Texto en columnas» y nos aparece el recuadro que está a continuación. Lo dejamos marcado en «Delimitados» y le damos clic a «Siguiente».

En esta parte vamos a especificar qué queremos que se separe por la «Coma» que existe en la dirección de cada uno de nuestros leads. Luego damos en «Siguiente» y en el último recuadro elegimos «Finalizar».

Al final así quedarán los datos, divididos en municipio, estado o región, código postal y país. 

10. Unir cadenas

Se podría decir que es la función contraria de Texto en columna. Unir cadenas nos sirve para juntar los caracteres y valores de diferentes columnas. Por ejemplo, tienes las direcciones de tus leads separadas y tienes que enviarles un paquete a su domicilio, por lo que necesitarás su dirección completa para agregarla en la etiqueta. Sería más útil que estuviera junta para copiar y pegar rápido, en lugar de ir casilla por casilla.

Lo primero que vamos a hacer es ir a la casilla donde queremos que se una la información. Buscamos la función de UNIRCADENAS o TEXTJOIN, según tu versión de Excel, y le damos en «Insertar función». Nos va a aparecer el siguiente recuadro en «Delimitador»; vamos a poner una coma entrecomillada, en «Ignorar_vacías»: VERDADERO, en «Texto1» la casilla de la ciudad, en «Texto2» casilla del estado y así hasta terminar con todos los datos. Luego damos en «Aceptar».

Después arrastramos toda la fórmula en la columna o hacemos doble clic para que se aplique la función en todos los contactos, y listo. 

11. Quitar duplicados

Otra de las funciones que nos ayuda bastante con las bases de datos de contactos que se repiten en innumerables listas es la de Quitar duplicados. Nos sirve para no enviar dos veces un correo electrónico a la misma persona, para que nuestras cuentas no se entorpezcan o simplemente para tener limpias las bases de datos. 

O, por ejemplo, si tienes registradas las entradas de productos con sus respectivos proveedores, puedes conocer a cuántos les compraste en cierto periodo si aplicas esta función en la columna de los proveedores; para este y casos similares esta función es la indicada.

En el ejemplo de la base de datos vamos a seleccionar las columnas que queremos limpiar. Nos dirigimos a la pestaña superior de «Datos», donde daremos clic al botón de «Quitar duplicados».

Hacemos clic en «Aceptar», nos va a indicar cuántos valores duplicados encontró y ya tenemos nuestra lista sin datos repetidos. 

Ahora ya tienes más conocimientos de cómo aprovechar el programa de Excel todavía más. Solo tienes que practicar para ir puliendo los detalles. Aplica los atajos de las teclas, las funciones, las pestañas, los botones y combinación de funciones hasta que logres dominarlas totalmente. Si quieres conocer otras fórmulas, atajos y trucos, te sugerimos pasar por este artículo.