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 facilitan el trabajo. Además, ahorra mucho tiempo y esfuerzo. Sin embargo, encontrar la función adecuada para su conjunto de datos puede ser muy difícil.
Por lo tanto, si ha tenido dificultades para encontrar la función de Excel apropiada para el análisis de datos, entonces ha llegado al lugar correcto. Aquí hay 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.
Nota: Para los no iniciados, las funciones mencionadas en este post necesitan ser añadidas en la Barra de Fórmulas de una hoja de cálculo de Excel que contenga datos o dentro de la celda en la que se desea el resultado.
Tabla de contenidos
1. CONCATENAR
=CONCATENAR es una de las funciones más cruciales para el análisis de datos ya que 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. Por ejemplo, resulta ú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 varias otras cosas.
En el ejemplo anterior, quería el mes y las ventas juntas en una sola columna. Para ello, he usado la fórmula =CONCATENADO(A2, B2) en la celda C2 para obtener Jan$700 como resultado.
Fórmula: =CONCATENADO( celdas que quiere combinar )
2. LEN
=LEN es otra función muy útil para el análisis de datos que, esencialmente, da como resultado el número de caracteres de una celda determinada. La función es predominantemente utilizable mientras se crean 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 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 ello, utilicé la fórmula =LEN(C2) en la celda D2 para obtener 5 como resultado.
Fórmula: =LEN( cell ) )
3. VLOOKUP
=VLOOKUP es probablemente una de las funciones más reconocibles para cualquier persona familiarizada 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 por el rango de la búsqueda. Si establece el rango en FALSO, buscará una coincidencia exacta, pero si lo establece en VERDADERO, buscará una coincidencia aproximada.
En el ejemplo anterior, quise buscar el número de vistas en un mes en particular. Para ello, utilicé 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 arreglo 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 a Junio.
La única desventaja de usar esta función es que sólo funciona con datos que han sido organizados en columnas, de ahí el nombre – búsqueda vertical. Por lo tanto, si tiene los datos organizados en filas, primero tendrá que transponer las filas a las columnas.
Fórmula: =VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
4. ÍNDICE/MARCHA
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 ÍNDICE y la PAREJA, cuando se usan juntos, pueden superar las limitaciones de VLOOKUP de entregar los resultados incorrectos (si no se tiene cuidado). Por lo tanto, cuando se combinan estas dos funciones, pueden localizar la referencia de los datos y buscar un valor en un arreglo de una sola dimensión. Esto devuelve las coordenadas de los datos como un número.
En el ejemplo anterior, quise buscar 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, «Ene» es el valor que quiero que coincida, A2:A13 es la columna en la que la fórmula encontrará «Ene» y el 0 significa que quiero que la fórmula encuentre una coincidencia exacta para el valor.
Si quieres encontrar un partido aproximado tendrás que sustituir el 0 por el 1 o el -1. De modo que el 1 encontrará el valor más grande menor o igual que el valor de búsqueda y -1 encontrará el valor más pequeño menor o igual que el valor de búsqueda. Ten en cuenta que si no usas 0, 1 o -1, la fórmula usará 1, por.
Ahora, si no quieres codificar el nombre del mes, puedes reemplazarlo con el número de la celda. Así que podemos reemplazar «Jan» en la fórmula mencionada arriba por F3 o A2 para obtener el mismo resultado.
Fórmula: =INDEX( columna de los datos que quieres devolver, MATCH(punto de datos común que intentas hacer coincidir, columna de la otra fuente de datos que tiene el punto de datos común, 0) )
5. MINIFS/MAXIFS
=MINIFS y =MAXIFS son muy similares a las funciones =MIN y =MAX, excepto por el hecho de que permiten tomar el conjunto de valores mínimos/máximos y hacerlos coincidir también con criterios particulares. Por lo tanto, esencialmente, la función busca los valores mínimos/máximos y los hace coincidir con los criterios de entrada.
En el ejemplo anterior, quería 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.
De manera similar, 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. 6. PROMEDIOS
La función =AVERAGEIFS le permite encontrar un promedio para un conjunto de datos particular basado en uno o más criterios. Al utilizar esta función, debe tener en cuenta que cada criterio y rango de promedio puede ser diferente. Sin embargo, en la función =AVERAGEIF, tanto el rango de criterios como el rango de la suma deben tener el mismo rango de tamaño. ¿Notas la diferencia de singular y plural entre estas funciones? Bueno, ahí es donde necesitas tener cuidado.
En este ejemplo, quise 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 del criterio y «M» es el criterio.
Fórmula: =AVERAGEIFS( rango_medio, rango_criterio1, criterio1,… )
7. CONTABILIDADES
Ahora, si quieres contar el número de instancias que un conjunto de datos cumple con criterios específicos, necesitarás usar la función =COUNTIFS. Esta función le permite añadir criterios ilimitados a su consulta, y por lo tanto hace que sea 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 estudiantes masculinos o femeninos que obtuvieron calificaciones aprobatorias (i.e.>=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 (notas), y «>=40» es el segundo criterio.
Fórmula: =COUNTIFS( criteria_range1, criteria1,… )
8. 8. PRODUCTO
La función =SUMPRODUCTO le ayuda a multiplicar los rangos o matrices juntos y luego devuelve la suma de los productos. Es una función bastante versátil y puede ser utilizada para contar y sumar matrices como COUNTIFS o SUMIFS, pero con mayor flexibilidad. 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 matriz (el precio de cada producto). La fórmula luego multiplica la cantidad de cada producto vendido por su precio y luego lo suma todo para obtener las ventas totales.
Fórmula: =SUMPRODUCT( 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 permite eliminar estos espacios o caracteres de los datos con facilidad, lo que permite obtener resultados precisos mientras se utilizan otras funciones.
En este ejemplo, quise eliminar todos los espacios extra entre las palabras Mouse y pad en A7. Para ello utilicé la fórmula =TRIM(A7).
La fórmula simplemente eliminó los espacios extra y entregó el resultado Alfombrilla de ratón con un solo espacio.
Fórmula: =TRIM( texto )
10. BUSCAR / BUSCAR
Para redondear, las funciones BUSCAR/BUSCAR le ayudarán a aislar un 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 =BUSCAR sólo devuelve coincidencias en mayúsculas y minúsculas. Mientras tanto, la función =BUSCAR no tiene tales limitaciones. Estas funciones son particularmente útiles cuando se buscan anomalías o identificadores únicos.
En este ejemplo, quise encontrar el número de veces que «Gui» apareció dentro de ahount para el cual usé la fórmula =FIND(A2, B2), que dio el resultado 1. Ahora, si quisiera encontrar el número de veces que «gui» apareció dentro de ahount en su lugar, tendría que usar la fórmula =SEARCH porque no distingue entre mayúsculas y minúsculas.
Fórmula de búsqueda: =FIND( find_text, within_text, [start_num] )
Fórmula de búsqueda: =BUSCAR( find_text, within_text, [start_num] )
Dominar el análisis de datos
Estas funciones esenciales de Microsoft Excel le ayudarán sin duda alguna en el análisis de datos, pero esta lista sólo araña la punta del iceberg. Excel también incluye varias otras funciones avanzadas para lograr resultados específicos. Si está interesado en aprender más sobre estas funciones, háganoslo saber en la sección de comentarios a continuación.
