Microsoft Excel ofrece a los usuarios cientos de funciones y fórmulas diferentes para una variedad de propósitos. Ya sea que tenga que analizar sus finanzas personales o cualquier conjunto de datos de gran tamaño, son las funciones las que hacen que el trabajo sea fácil. Además, ahorra mucho tiempo y esfuerzo. Sin embargo, encontrar la función correcta para su conjunto de datos puede ser muy complicado.
Así que si ha estado luchando para encontrar la función de Excel apropiada para el análisis de datos, entonces ha llegado al lugar correcto. He aquí una lista de algunas funciones esenciales de Microsoft Excel que puede utilizar para el análisis de datos y puede aumentar su productividad en el proceso.
>
Note: Para los no iniciados, las funciones mencionadas en este post deben ser añadidas en la Barra de Fórmula de una hoja de cálculo de Excel que contenga datos o dentro de la celda en la que se desea el resultado.
1. CONCATENAR
=CONCATENATE es una de las funciones más importantes para el análisis de datos, ya que le permite combinar texto, números, fechas, etc. de múltiples celdas en una sola. La función es particularmente útil para combinar datos de diferentes celdas en una sola celda. Por ejemplo, es útil para crear los parámetros de seguimiento de las campañas de marketing, crear consultas de API, añadir texto a un formato de número y muchas otras cosas.
En el ejemplo anterior, quería el mes y las ventas juntos en una sola columna. Para eso, he usado la fórmula =CONCATENATE(A2, B2) en la celda C2 para obtener Jan$700 como resultado.
Fórmula: =CONCATENATE( celdas que desea combinar )
2. LEN
=LEN es otra función útil para el análisis de datos que esencialmente produce el número de caracteres en una celda dada. La función es predominantemente utilizable al crear etiquetas de título o descripciones que tienen un límite de caracteres. También puede ser útil cuando se trata de averiguar las diferencias entre los diferentes identificadores únicos, que a menudo son bastante largos y no están en el orden correcto.
En el ejemplo anterior, quería contar las cifras del número de visitas que recibía cada mes. Para esto, hice uso de la fórmula =LEN(C2) en la celda D2 para obtener 5 como resultado.
Fórmula: =LEN( celda )
3. VLOOKUP
=VLOOKUP es probablemente una de las funciones más reconocibles para cualquiera que esté familiarizado con el análisis de datos. Se puede utilizar para hacer coincidir los datos de una tabla con un valor de entrada. La función ofrece dos modos de coincidencia: exacta y aproximada, que se controla mediante el rango de búsqueda. Si ajusta el rango a FALSE, buscará una coincidencia exacta, pero si lo ajusta a TRUE, buscará una coincidencia aproximada.
En el ejemplo anterior, quería consultar el número de vistas en un mes en particular. Para eso, usé la fórmula =VLOOKUP(«Jun», A2:C13, 3) en la celda G4 y obtuve 74992 como resultado. Aquí, «Jun» es el valor de búsqueda, A2:C13 es el array de tablas en el que busco «Jun» y 3 es el número de la columna en la que la fórmula encontrará las vistas correspondientes para junio.
El único inconveniente de utilizar esta función es que sólo funciona con datos que se han organizado en columnas, de ahí el nombre – búsqueda vertical. Por lo tanto, si tiene los datos ordenados en filas, primero tendrá que transponer las filas en columnas.
Fórmula: =VLOOKUP( lookup_value, table_array, col_index_num,[range_lookup] )
4. INDEX/MATCH
Al igual que la función VLOOKUP, las funciones INDEX y MATCH son muy útiles para buscar datos específicos basados en un valor de entrada. El INDICE y el MATCH, cuando se usan juntos, pueden superar las limitaciones de VLOOKUP de entregar los resultados equivocados (si no se tiene cuidado). Por lo tanto, cuando se combinan estas dos funciones, pueden señalar con precisión la referencia de los datos y buscar un valor en un array de una sola dimensión. Esto devuelve las coordenadas de los datos como un número.
En el ejemplo anterior, quería consultar el número de vistas en enero. Para ello, utilicé la fórmula =INDEX (A2:C13, MATCH(«Jan», A2:A13,0), 3). Aquí, A2:C13 es la columna de datos que quiero que la fórmula devuelva, «Jan» es el valor que quiero igualar, A2:A13 es la columna en la que la fórmula encontrará «Jan» y el 0 significa que quiero que la fórmula encuentre una coincidencia exacta para el valor.
Si desea encontrar una coincidencia aproximada tendrá que sustituir el 0 por 1 o -1. De modo que el 1 encontrará el valor más grande menor o igual al valor de búsqueda y -1 encontrará el valor más pequeño menor o igual al valor de búsqueda. Tenga en cuenta que si no usa 0, 1, o -1, la fórmula usará 1, por.
Ahora, si no desea codificar el nombre del mes, puede reemplazarlo con el número de celular. Así que podemos sustituir «Jan» en la fórmula mencionada anteriormente por F3 o A2 para obtener el mismo resultado.
Fórmula: =INDEX( columna de los datos que desea devolver, MATCH(punto de datos común que está intentando coincidir, columna de la otra fuente de datos que tiene el punto de datos común, 0) )
5. MINIFS/MAXIFS
Las funciones =MINIFS y =MAXIFS son muy similares a las funciones =MIN y =MAX, excepto por el hecho de que le permiten tomar el conjunto mínimo/máximo de valores y hacerlos coincidir en criterios particulares también. Por lo tanto, la función busca los valores mínimos/máximos y los compara con los criterios de entrada.
En el ejemplo anterior, quise encontrar las puntuaciones mínimas basadas en el género del estudiante. Para ello, utilicé la fórmula =MINIFS (C2:C10, B2:B10, «M») y obtuve el resultado 27. Aquí C2:C10 es la columna en la que la fórmula buscará las puntuaciones, B2:B10 es una columna en la que la fórmula buscará los criterios (el género), y «M» es el criterio.
Del mismo modo, para las puntuaciones máximas, utilicé la fórmula =MAXIFS(C2:C10, B2:B10, «M») y obtuve el resultado 100.
Fórmula para MINIFS: =MINIFS( min_range, criteria_range1, criteria1,…. )
Fórmula para MAXIFS: =MAXIFS( max_range, criteria_range1, criteria1,…. )
6. PROMEDIOS
La función =AVERAGEIFS permite encontrar un promedio para un conjunto de datos determinado basado en uno o más criterios. Al utilizar esta función, debe tener en cuenta que cada criterio y cada intervalo medio pueden ser diferentes. Sin embargo, en la función =AVERAGEIF, tanto el rango de criterios como el rango de suma deben tener el mismo rango de tamaño. ¿notas la diferencia de singular y plural entre estas funciones? Bueno, ahí es donde tienes que tener cuidado.
En este ejemplo, quería encontrar la puntuación media basada en el género de los estudiantes. Para ello utilicé la fórmula, =AVERAGEIFS(C2:C10, B2:B10, «M») y obtuve 56.8 como resultado. Aquí, C2:C10 es el rango en el que la fórmula buscará el promedio, B2:B10 es el rango de criterios, y «M» es el criterio.
Fórmula: =AVERAGEIFS( rango_medio, rango_de_criterios1, criterio1,…. )
7. CUENTAS
Ahora, si desea contar el número de instancias en las que un conjunto de datos cumple con criterios específicos, deberá utilizar la función =COUNTIFS. Esta función le permite agregar criterios ilimitados a su consulta, y por lo tanto es la forma más fácil de encontrar el conteo basado en los criterios de entrada.
En este ejemplo, quería encontrar el número de alumnos o alumnas que obtuvieron notas de aprobación (es decir,>=40). Para ello utilicé la fórmula =COUNTIFS(B2:B10, «M», C2:C10, «>=40»). Aquí, B2:B10 es el rango en el que la fórmula buscará el primer criterio (género), «M» es el primer criterio, C2:C10 es el rango en el que la fórmula buscará el segundo criterio (marcas), y «>=40» es el segundo criterio.
Fórmula: =COUNTIFS( criteria_range1, criteria1,…. )
8. SUMPRODUCTO
La función =SUMPRODUCT le ayuda a multiplicar rangos o matrices juntos y luego devuelve la suma de los productos. Es una función bastante versátil y se puede utilizar para contar y sumar matrices como COUNTIFS o SUMIFS, pero con una flexibilidad añadida. También puede utilizar otras funciones dentro de SUMPRODUCT para ampliar aún más su funcionalidad.
En este ejemplo, quería encontrar la suma total de todos los productos vendidos. Para ello, utilicé la fórmula =SUMPRODUCTO(B2:B8, C2:C8). Aquí, B2:B8 es la primera matriz (la cantidad de productos vendidos) y C2:C8 es la segunda (el precio de cada producto). La fórmula entonces multiplica la cantidad de cada producto vendido con su precio y luego lo suma todo para entregar las ventas totales.
Fórmula: =SUMPRODUCTO( array1,[array2],[array3],…. )
9. TRIM
La función =TRIM es particularmente útil cuando se trabaja con un conjunto de datos que tiene varios espacios o caracteres no deseados. La función le permite eliminar estos espacios o caracteres de sus datos con facilidad, lo que le permite obtener resultados precisos mientras utiliza otras funciones.
En este ejemplo, quería eliminar todos los espacios adicionales entre las palabras Mouse y pad en A7. Para eso usé la fórmula =TRIM(A7).
La fórmula simplemente eliminó los espacios extra y entregó el Mouse Pad con un solo espacio.
Fórmula: =TRIM( texto )
10. BUSCAR/BUSCAR
Las funciones FIND/SEARCH le ayudarán a aislar texto específico dentro de un conjunto de datos. Ambas funciones son bastante similares en lo que hacen, excepto por una diferencia importante – la función =FIND sólo devuelve coincidencias sensibles a mayúsculas y minúsculas. Mientras tanto, la función =SEARCH no tiene tales limitaciones. Estas funciones son especialmente útiles cuando se buscan anomalías o identificadores únicos.
En este ejemplo, quise encontrar el número de veces que Gui apareció dentro de Tecnotuto para el cual utilicé la fórmula =FIND(A2, B2), que me dio el resultado 1. Ahora bien, si quisiera encontrar el número de veces que aparece gui en Tecnotuto, tendría que usar la fórmula =SEARCH porque no distingue entre mayúsculas y minúsculas.
Fórmula para buscar: =FIND( find_text, within_text,[start_num] )
>
Fórmula para la búsqueda: =SEARCH( find_text, within_text,[start_num] )
Dominar el análisis de datos
Estas funciones esenciales de Microsoft Excel definitivamente le ayudarán con el análisis de datos, pero esta lista sólo raspa la punta del iceberg. Excel también incluye varias otras funciones avanzadas para lograr resultados específicos. Si está interesado en obtener más información sobre estas funciones, háganoslo saber en la sección de comentarios a continuación.
El siguiente: Si desea utilizar Excel de forma más eficiente, debería consultar el siguiente artículo para encontrar algunos atajos de navegación de Excel que debe conocer.