Cómo convertirse en un experto en Excel: parte 2

​Esta semana os traemos la 2º parte del artículo que publicamos "Cómo convertirse en un experto en Excel" que podéis leer si pincháis aquí.

Fórmulas de Excel

7. Cálculos sencillos

Además de realizar cálculos complejos, Excel te permite hacer cálculos sencillos con miles de datos como sumar, restar, multiplicar y dividir.

• Para sumar, escribe + en el cuadro de fórmulas.
• Para restar, escribe - en el cuadro de fórmulas.
• Para multiplicar, escribe * en el cuadro de fórmulas.
• Para dividir, escribe / en el cuadro de fórmulas.
  

Además, puedes utilizar los paréntesis para determinar qué operaciones se realizan primero. Por ejemplo, en (10+10*10), primero se realizaría la cuenta 10*10 y al resultado se le sumaría 10. Sin embargo, en (10+10)*10, primero se sumaría 10+10 y el resultado se multiplicaría por 10.

8. Formato condicional

Esta herramienta te permite cambiar el color de las celdas basándose en la información contenida. Por ejemplo, si quieres acceder fácilmente a los 10 clientes que más facturan para hacerles un obsequio, o los que están por debajo de la media para tratar de incentivar sus compras, puedes hacer esto y acceder de manera más sencilla a la información.

Para ello, selecciona las celdas en las que quieras aplicar el formato condicional, selecciona desde el menu Inicio el botón Formato condicional. Elige desde el menú desplegable la opción que desees, o crea una nueva regla.

Como veis, hemos aplicado el formato condicional en el curso de los alumnos para saber en qué nivel se encuentran cada uno. A aquellos alumnos de color verde que están en el último curso podremos ofrecerles sesiones de orientación laboral para su inminente futuro laboral.

9. El condicional SI

A veces necesitamos contar un dato de celdas que vamos a añadir que contengan una determinada información, pero no podemos hacerlo manualmente por la cantidad de tiempo que perderíamos. Para ese tipo de casos tenemos la formula SI.

Por ejemplo, imagina que queremos darle una beca a los alumnos cuya nota sea sobresaliente. En lugar de ir buscando alumno por alumno cuáles son los que poseen un sobresaliente, podemos utilizar la fórmula SI y establecer qué y cuántos alumnos obtienen beca.

Para conseguir este resultado, debes posicionarte en la celda donde quieres que aparezca el resultado y escribir "=SI". La fórmula que te aparecerá será: =SI(prueba_lógica; [valor_si_verdadero]; [valor_si_falso].

• Prueba_lógica: se refiere a la condición que se debe cumplir para rellenar la celda. Es decir, en nuestro ejemplo hemos determinado la fórmula como "Si la celda E2 contiene la palabra sobresaliente, el resultado en la columna beca será 1; si no contiene la palabra sobresaliente, el resultado será 0". Para copiar esta fórmula en el resto de celdas, posiciónate en aquella donde escribiste la fórmula y pincha y arrastra en el icono + que te aparecerá abajo a la derecha.

10. El símbolo del dólar

El símbolo del dólar $ se utiliza en Excel para fijar una celda, una fila o una columna cuando utilizas fórmulas. Por ejemplo, si quisieras saber qué porcentaje del total de ingresos ha sido facturado por cada alumno, debes hacer una división de la facturación del primer alumno entre el total facturado. Para que la fórmula se copie al resto de alumnos, pincha en el símbolo + que te aparece en la parte inferior derecha de la primera celda.

¿Por qué aparece #¡DIV/0! a partir del 2º alumno? Muy sencillo, al copiar la fórmula del primer alumno al resto de celdas, como no hemos utilizado el símbolo $ para fijar el valor de la facturación total, Excel ha entendido que elegíamos la siguiente celda tanto para la facturación del alumno 2 como para la facturación total, y ésta última celda no contiene datos por lo que estamos dividiendo 2.100 euros entre 0.

¿Cómo lo solucionamos? Fijando la celda con los símbolos del dólar.

Esta aplicación es muy útil, pero escribir a mano los símbolos del dólar es un poco pesado. Para ayudarnos con esta tarea podemos hacer algo muy sencillo: colócate en la celda donde estás escribiendo la fórmula, selecciona el número de celda que quieres fijar y presiona F4. De esta forma se añadirán los símbolos automáticamente y tu celda se hará fija. ¿Quieres fijar una fila o una columna entera? Pulsa F4 dos veces para fijar toda la fila y tres veces para fijar una columna entera.

Funciones de Excel

11. La función BUSCARV

¿Alguna vez has tenido dos conjuntos de datos en dos hojas de cálculo diferentes y has necesitado combinarlas? Supongamos que tienes una hoja de Excel con los nombres y direcciones de correo electrónico de leads de la empresa, y otra con los nombres y la compañía para la que trabajan esas mismas personas. Esto es difícil de manejar, lo más útil sería combinar esos datos en una misma hoja de cálculo, ¿verdad?

Para conseguir combinar estos datos, necesitas que, al menos una columna de una hoja sea idéntica a una columna de la otra hoja. Revisa tus columnas para comprobar que son iguales, incluyendo el número de espacios en blanco de cada celda.

Nosotros tenemos 2 hojas en las que poseemos, por un lado el nombre de los alumnos y sus apellidos, y por otro lado, el nombre de los alumnos y sus correos electrónicos. Queremos combinar ambos datos para obtener una única hoja que contenga el nombre, los apellidos y el email.

La fórmula =BUSCARV(valor_buscado; matriz_buscar_en; indicador_columnas; [ordenado]) funciona de la siguiente manera:

• ​Valor_buscado: este es el valor idéntico que tienes en ambas hojas de cálculo. Elige el primer valor de tu primera hoja. En nuestro caso sería el nombre, que en la hoja 1 es la celda A2, es decir, "Hoja1!A2".
• Matriz_buscar_en: es el rango de columnas de la hoja 2 de donde vas a sacar los datos, incluyendo  la columna de datos idénticos, en nuestro caso, el nombre que vas a combinar en la hoja 1. Nosotros tendríamos que escribir en la fórmula: "Hoja2!A:B". La A es la columna A de la hoja 2, que es la columna idéntica de la hoja 1 (el nombre). La B significa columna B de la hoja 2, que es la información que queremos combinar en la hoja 1.
• Indicador_columnas: aquí debes indicar en qué columna de la selección que hemos realizado en la hoja 2 se encuentra la columna que quieres incluir en la hoja 1. En nuestro caso, nuestra selección sólo tiene 2 columnas y el correo electrónico se encuentra en la 2º columna.  Si nuestros datos de la hoja 2 estuviesen en la columna B y C (siendo B el nombre del alumno y C el correo electrónico), seguiríamos escribiendo 2 en nuestra fórmula, pues el correo se encuentra en la 2º columna de la selección.
• Ordenado: utiliza FALSO para asegurarte que incluyes sólo las coincidencias exactas.

12. Las funciones ÍNDICE y COINCIDIR

Al igual que la función BUSCARV, las funciones ÍNDICE y COINCIDIR extraen información de otra hoja de cálculo y la llevan a una misma localización, aunque tienen algunas diferencias:

1. La función BUSCARV es una fórmula mucho más sencilla. Pero si trabajas con hojas de datos extensas que requieren miles de búsquedas, las funciones ÍNDICE y COINCIDIR pueden reducir drásticamente el tiempo utilizado.
2. La función BUSCARV sólo te permite encontrar valores que están a la derecha del valor coincidente, mientras que ÍNDICE y COINCIDIR te permiten buscar valores que se encuentran a la izquierda.

Por tanto, si quisieras combinar información de las hojas 1 y 2 en la hoja 1, pero los datos de la columna no están iguales en las 2 hojas, para utilizar BUSCARV tendríamos que reorganizar las columnas. En ese caso, sería mejor utilizar las funciones ÍNDICE y COINCIDIR.

Imaginemos en nuestro ejemplo que tenemos en una hoja el nombre completo de los alumnos y su dirección de correo electrónico, y en otra hoja su correo electrónico (en otro orden diferente) y sus notas. La información coincidente en ambas hojas es el correo electrónico, pero se encuentra ordenada de diferente manera. Utilizar la función combinada ÍNDICE COINCIDIR en lugar de BUSCARV nos evitará tener que reordenar una de las columnas de correo electrónico.

La fórmula sería =INDEX(matriz, COINCIDIR), que sería así, una vez desarrollado =INDICE(matriz; COINCIDIR(valor_buscado; matriz_buscada; tipo_de_coincidencia)).

Así funcionan las variables de esta función combinada:

• Matriz: el rango de columnas de la hoja 2 que contiene la nueva información que deseas incluir en la hoja 1. En nuestro ejemplo serían las notas, que la tenemos en la hoja 2, la columna B completa: "Hoja2!B:B".
• Valor_buscado: es la columna de la hoja 1 que contiene la misma información (aunque con otro orden) que la hoja 2. En nuestro caso es el correo electrónico, que se encuentra en la columna B, por lo que sería "B:B".
• Matriz_buscada: es la columna de la hoja 2 que contiene la misma información que la hoja 1, es decir, el correo electrónico de nuevo, pero en la hoja 2. A nosotros nos quedaría así "Hoja2!A:A".
• Tipo_de_coincidencia: "1" menor que, "0" coincidencia exacta y "-1" mayor que. En nuestro caso, es coincidencia exacta.

13. La función CONTAR.SI

A la hora de hacer un recuento de determinados valores, en lugar de hacerlo manualmente, podemos recurrir a la fórmula CONTAR.SI. Con esta fórmula, Excel contará el número de veces que una palabra o una cifra aparece en un rango determinado datos.

Por ejemplo, imaginemos que queremos contar el número de notables que tenemos en nuestra base de datos.

La fórmula =CONTAR.SI(rango; criterio) quedaría así en nuestra hoja de cálculo =CONTAR.SI(D:D; "notable"). 

Las variables de la fórmula funcionan así:

• Rango: dónde se encuentran los datos que queremos contabilizar. En nuestro caso, en la columna D, pero nos habría dado el mismo resultado si hubiéramos puesto, por ejemplo, rango de la columna A a la D (A:D) porque esos datos sólo aparecen en la columna D.
• Criterio: qué palabra o cifra quieres que cuente Excel. Ten en cuenta que debes incluir las comillas para que Excel pueda contabilizar esas palabras concretas. Para cifras esto no es necesario.

14. Combinar celdas utilizando "&"

Las bases de datos suelen tener la información dividida para obtener la información más exacta posible. Por ejemplo, los formularios de contacto suelen separar, por un lado, el nombre y, por el otro, los apellidos para que la información que proporciona el usuario contenga la mayor cantidad de datos.

Pero, a la hora de trabajar, quizá nos resulte más cómodo tener el nombre y apellidos en una misma celda (para evitar falsos duplicados), o quizá queramos tener en el mismo lugar la ciudad y el código postal de nuestros clientes para segmentar mejor futuros contactos de marketing.

Para realizar esta combinación de forma sencilla se utiliza el comando "&". En nuestro ejemplo de los alumnos de una facultad, para combinar el nombre y los apellidos, que se encuentran en la columna A y B, respectivamente, debemos escribir la siguiente fórmula =A2&" "&B2. De esta forma combinaremos el nombre de la primera alumna, María, con su apellido, Pérez, en una nueva columna C llamada Nombre completo.

¿Por qué añadimos "un_espacio_en_blanco" a fórmula? Si simplemente escribiésemos =A2&B2, el nombre de la alumna aparecería unido y quedaría MaríaPérez. Al agregar =A2&" "&B2, permitimos un espacio de separación entre ambas palabras.

Ahora ya sabes unos cuantos trucos que te ayudarán a ser más eficiente y, sobre todo, a ahorrar tiempo cuando utilizas Excel. ¿Conoces más trucos de Excel que te ayudan con tus tareas diarias? ¡Cuéntanos en comentarios y ayuda a mejorar a nuestra comunidad!

Marketing inmobiliario 11/09/2017

Por qué tu inmobiliaria debe pensar en la seguridad informática

Lead nurturing 31/05/2017

[GUÍA] 10 consejos para optimizar tus landing pages