EXCELeINFO

5 TRUCOS con fórmulas que todo analista de datos debe dominar en Excel

by · 7 December, 2020

En este video tutorial te enseñaré 5 trucos valiosos para que le saques el máximo provecho al tema de Fórmulas en Excel. Estos ejercicios te servirán como base para resolver situaciones que se pueden presentar en tu rol de Analista de datos.

Usaremos más de 10 funciones de Excel que seguro conoces, pero cuando las combinamos una con otras, nos damos cuenta que podemos hacer cosas sorprendentes. Desarrollaremos las siguientes fórmulas:

  • Manejo de fechas como texto con la función VALOR.
  • Trabajaremos con texto y las funciones FECHA, DERECHA, IZQUIERDA y EXTRAE.
  • Vamos a cruzar tablas con BUSCARV y la función SI y el doble signo negativo (—).
  • Vamos a armar emails con base en una columna de nombres.
  • Aprenderás a armar mega fórmulas.

Ver Video 5 trucos que todo analista de datos debe dominar en Excel

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

1. Manejo de fechas en Excel

Tenemos el siguiente texto en una celda:

06-Nov-20 12:44:52

A elegir las celdas vemos el cálculo de CONTEO, como si el contenido fuera texto

Figura 1. A elegir las celdas vemos el cálculo de CONTEO, como si el contenido fuera texto.

Aparentemente es una fecha con horas, pero realmente es un valor de texto que se extrajo desde un reporte. SI deseamos hacer cálculos no podríamos, a menos que convirtamos ese valor a fecha y hora.

Para convertir el valor de texto a fecha usamos la función VALOR que sirve para convertir a número un número almacenado como texto:

=VALOR(A2)

Ahora que obtuvimos la fecha y hora, usaremos la función FECHA para devolver sólo la fecha sin la hora, minutos y segundos:

=FECHA(AÑO(B2),MES(B2),DIA(B2))

2. Manejo de textos

Recibimos un reporte y notamos que viene una columna llamada FECHA. Para mi sorpresa, no viene con un formato que podamos manipular fácilmente. Claramente es una fecha, pero mira en qué formato viene definida:

20201106

Notemos que los primeros 4 números son el año 2020, los siguiente dos son el mes de noviembre y los últimos 2 son el día 6. Vamos a usar la siguiente fórmula para convertir ese número a una fecha con la que podamos trabajar:

=FECHA(IZQUIERDA(D2,4),EXTRAE(D2,5,2),DERECHA(D2,2))

3. Cruzar tablas con BUSCARV combinada con la función SI

Tenemos una lista de números telefónicos y nos piden que en otra columna le pongamos la ciudad a la que pertenecen. Aquí en México usamos claves LADA de 2 o 3 dígitos. Debemos evaluar los primeros 2 o 3 dígitos para luego traernos el nombre de la ciudad.

Primero tomemos la clave LADA de los números. Usamos la función SI para evaluar si los primeros 2 dígitos son 33 para definir esa clave. Si los primeros no son 33, entonces vamos a traernos los primeros 3 dígitos de la izquierda usando la función IZQUIERDA.

Cuando usamos funciones de texto, los valores que se devuelve son de texto por lo que podemos usar el doble signo negativo para convertir los valores a número. Usa la siguiente fórmula:

=–SI(IZQUIERDA(F2,2)=”33″,33,IZQUIERDA(F2,3))

Una vez que tenemos las claves, vamos a usar BUSCARV para devolver el nombre de la ciudad con base en la clave. Mira que tenemos una tabla llamada CiudadesEstados, que será con la que haremos el cruce de valores.

Tabla de ciudades para cruzar valores usando BUSCARV en Excel

Figura 2. Tabla de ciudades para cruzar valores usando BUSCARV en Excel.

Usamos la siguiente fórmula para devolver las ciudades con base en la clave LADA.

=BUSCARV(G2,CiudadesEstados,2,0)

4. Armar correos electrónicos con base en nombres

Somo los encargados de la generación de correos electrónicos a los nuevos empleados. Nos entregan una base de datos con una columna llamada NOMBRE, con la cual debemos basarnos para generar los correos. Los nombres vienen en este formato:

Lista de nombres en formato Apellido, Nombre

Figura 3. Lista de nombres en formato Apellido, Nombre.

Usaremos la siguiente fórmula para armar los correos. En el siguiente punto te explicará cómo es que vamos a armar esta fórmula paso a paso:

=MINUSC(EXTRAE(I2,ENCONTRAR(“,”,I2)+2,1)&IZQUIERDA(I2,ENCONTRAR(“,”,I2)-1)&”@dominio.com”)

5. Cómo armar mega fórmulas en Excel

En el ejercicio anterior te regalé una fórmula para armar correos electrónicos con base en una columna con nombres. Ahora te voy a explicar cómo armar esa fórmula paso a paso.

Siempre sugiero que para armar fórmulas grandes, lo hagamos por pasos, sobre todo cuando un valor depende de otro. Veamos.

  • Primero buscamos la posición de la coma usando =ENCONTRAR(“,”,I2)
  • Con base en la posición de la coma, obtenemos la primer letra el nombre usando =EXTRAE(I2,J2+2,1)
  • Igual, con la posición de la coma, extraemos el apellido usando =IZQUIERDA(I2,J2-1)
  • Unimos la primera letra del nombre y el apellido con @dominio.com usando =MINUSC(K2&L2&”@dominio.com”)

Al finalizar solo unimos todas las fórmulas en una sola, reemplazando la referencia la fórmula con la fórmula en sí.

Descarga el archivo de ejemplo

5 ejercicios con fórmulas que todo Analista debe dominar en Excel – EXCELeINFO.zip

Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.

Tags:

You may also like...