10 funciones esenciales de Microsoft Excel para el análisis de datos
Miscelánea / / November 29, 2021
1. CONCATENAR
= CONCATENAR es una de las funciones más cruciales para el análisis de datos, ya que le permite combinar texto, números, fechas, etc. de múltiples celdas en una. La función es particularmente útil para combinar datos de diferentes celdas en una sola celda. Por ejemplo, resulta útil para crear parámetros de seguimiento para campañas de marketing, crear consultas de API, agregar 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 = CONCATENAR (A2, B2) en la celda C2 para obtener $ 700 de enero como resultado.
Fórmula: = CONCATENAR (celdas que desea combinar)
2. LEN
= LEN es otra función útil para el análisis de datos que esencialmente genera el número de caracteres en cualquier celda dada. La función se puede utilizar principalmente al crear etiquetas de título o descripciones que tienen un límite de caracteres. También puede ser útil cuando intenta averiguar las diferencias entre diferentes identificadores únicos que a menudo son bastante extensos y no están en el orden correcto.
En el ejemplo anterior, quería contar las cifras de la cantidad de vistas que obtenía cada mes. Para esto, utilicé la fórmula = LEN (C2) en la celda D2 para obtener 5 como resultado.
Fórmula: = LEN (celda)
3. BUSCARV
= BUSCARV es probablemente una de las funciones más reconocibles para cualquiera que esté familiarizado con el análisis de datos. Puede usarlo 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 establece el rango en FALSO, buscará una coincidencia exacta, pero si lo establece en VERDADERO, buscará una coincidencia aproximada.
En el ejemplo anterior, quería buscar la cantidad de vistas en un mes en particular. Para eso, utilicé la fórmula = BUSCARV ("Jun", A2: C13, 3) en la celda G4 y obtuve 74992 como resultado. Aquí, "Jun" es el valor de búsqueda, A2: C13 es la matriz de la tabla en la que estoy buscando "Jun" y 3 es el número de la columna en la que la fórmula encontrará las vistas correspondientes de junio.
El único inconveniente de usar esta función es que solo funciona con datos que se han organizado en columnas, de ahí el nombre: búsqueda vertical. Por lo tanto, si tiene sus datos organizados en filas, primero deberá transponer las filas en columnas.
Fórmula: = BUSCARV (lookup_value, table_array, col_index_num, [range_lookup])
4. ÍNDICE / PARTIDO
Al igual que la función BUSCARV, las funciones INDICE y COINCIDIR son útiles para buscar datos específicos basados en un valor de entrada. El ÍNDICE y COINCIDIR, cuando se usan juntos, pueden superar las limitaciones de BUSCARV de entregar resultados incorrectos (si no tiene cuidado). Entonces, cuando combina estas dos funciones, pueden identificar la referencia de datos y buscar un valor en una matriz de una sola dimensión. Eso devuelve las coordenadas de los datos como un número.
En el ejemplo anterior, quería buscar la cantidad de vistas en enero. Para eso, utilicé la fórmula = INDICE (A2: C13, MATCH ("Jan", A2: A13,0), 3). Aquí, A2: C13 es la columna de datos que quiero que devuelva la fórmula, "Jan" es el valor que quiero hacer coincidir, A2: A13 es el 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, deberá sustituir el 0 por 1 o -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. 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 celda. Entonces podemos reemplazar "Jan" en la fórmula mencionada anteriormente con F3 o A2 para obtener el mismo resultado.
Fórmula: = ÍNDICE (columna de los datos que desea devolver, COINCIDIR (punto de datos común que está tratando de 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 le permiten tomar el conjunto mínimo / máximo de valores y hacerlos coincidir también en criterios particulares. Entonces, esencialmente, la función busca los valores mínimo / máximo y los empareja con los criterios de entrada.
En el ejemplo anterior, quería encontrar las puntuaciones mínimas según el género del estudiante. Para eso, 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á los puntajes, 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 (rango_min, rango_criterio1, criterio1, ...)
Fórmula para MAXIFS: = MAXIFS (rango_máximo, rango_criterio1, criterio1, ...)
6. PROMEDIO SI
La función = AVERAGEIFS le permite encontrar un promedio para un conjunto de datos en particular basado en uno o más criterios. Al usar esta función, debe tener en cuenta que cada criterio y rango promedio 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. ¿Observa la diferencia de singular y plural entre estas funciones? Bueno, ahí es donde debes tener cuidado.
En este ejemplo, quería encontrar el puntaje promedio basado en el género de los estudiantes. Para eso 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: = PROMEDIO SI (rango_promedio, rango_criterio1, criterio1, ...)
7. CONTADOS
Ahora, si desea contar la cantidad de instancias que un conjunto de datos cumple con criterios específicos, deberá usar la función = CONTAR.SI. Esta función le permite agregar criterios ilimitados a su consulta y, por lo tanto, lo convierte en la forma más fácil de encontrar el recuento en función de los criterios de entrada.
En este ejemplo, quería encontrar la cantidad de estudiantes hombres o mujeres que obtuvieron calificaciones para aprobar (es decir,> = 40). Para eso utilicé la fórmula = CONTAR.SI (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 (puntos) y "> = 40" es el segundo. Criterios.
Fórmula: = CONTAR.SI (rango_criterios1, criterios1, ...)
8. SUMPRODUCTO
La función = SUMPRODUCTO 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 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 eso, 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). Luego, la fórmula multiplica la cantidad de cada producto vendido con su precio y luego suma todo para obtener las ventas totales.
Fórmula: = SUMPRODUCTO (matriz1, [matriz2], [matriz3], ...)
9. PODAR
La función = TRIM es particularmente útil cuando está trabajando 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 usa otras funciones.
En este ejemplo, quería eliminar todos los espacios adicionales entre las palabras Mouse y pad en A7. Para eso utilicé la fórmula = TRIM (A7).
La fórmula simplemente eliminó los espacios adicionales y entregó el resultado Alfombrilla de ratón con un solo espacio.
Fórmula: = RECORTAR (texto)
10. ENCONTRAR / BÚSQUEDA
Para redondear las cosas, están las funciones ENCONTRAR / BÚSQUEDA que lo 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 solo devuelve coincidencias que distinguen entre mayúsculas y minúsculas. Mientras tanto, la función = SEARCH no tiene tales limitaciones. Estas funciones son particularmente útiles cuando se buscan anomalías o identificadores únicos.
En este ejemplo, quería encontrar la cantidad de veces que apareció 'Gui' dentro de Guiding Tech para lo cual usé la fórmula = FIND (A2, B2), que arrojó el resultado 1. Ahora, si quisiera encontrar la cantidad de veces que apareció 'gui' dentro de Guiding Tech, tendría que usar la fórmula = SEARCH porque no distingue entre mayúsculas y minúsculas.
Fórmula para encontrar: = ENCONTRAR (buscar_texto, dentro_texto, [núm_inicial])
Fórmula de búsqueda: = BUSCAR (buscar_texto, dentro_texto, [núm_inicial])