Excel

101 funciones de Excel que debe conocer

101 Excel Functions You Should Know

A continuación se muestra una breve descripción general de unas 100 funciones importantes de Excel que debe conocer, con enlaces a ejemplos detallados. También tenemos un gran lista de fórmulas de ejemplo , a lista más completa de funciones de Excel , y entrenamiento en video . Si es nuevo en las fórmulas de Excel, ver esta introducción .

Funciones de fecha y hora

Excel proporciona muchas funciones para trabajar fechas y veces .



AHORA y HOY

Puede obtener la fecha actual con el Función HOY y la fecha y hora actual con el Función NOW . Técnicamente, la función NOW devuelve la fecha y hora actuales, pero puede formatear solo como hora, como se ve a continuación:



Funciones AHORA y HOY

|_+_|

Nota: estos son funciones volátiles y se volverá a calcular con cada cambio de hoja de trabajo. Si quieres un valor estático, usa fecha y hora atajos.



DÍA, MES, AÑO y FECHA

Puedes usar el DÍA, MES , y AÑO funciones para desmontar cualquier fecha en sus componentes en bruto, y el Función FECHA para volver a armar las cosas.

Funciones para desmontar y volver a montar fechas

|_+_|

HORA, MINUTO, SEGUNDO y HORA

Excel proporciona un conjunto de funciones paralelas para tiempos. Puedes usar el HORA , MINUTO , y SEGUNDO funciones para extraer partes de un tiempo, y puede ensamblar un TIEMPO a partir de componentes individuales con el Función HORA .



Ejemplos de funciones de tiempo

|_+_|

DATEDIF y YEARFRAC

Puedes usar el Función DATEDIF para obtener el tiempo entre fechas en años, meses o días. DATEDIF también se puede configurar para obtener el tiempo total en denominaciones 'normalizadas', es decir, '2 años y 5 meses y 27 días'.

Ejemplo de función DATEDIF

Usar YEARFRAC para obtener años fraccionarios:

Ejemplo de función YEARFRAC

|_+_|

EDATE y EOMONTH

Una tarea común con las fechas es adelantar (o retroceder) una fecha en un número determinado de meses. Puedes usar el EDAR y Funciones EOMONTH para esto. EDATE se mueve por mes y retiene el día. EOMONTH funciona de la misma manera, pero siempre devuelve el último día del mes.

Ejemplos de funciones EDATE y EOMONTH

|_+_|

DÍA DE TRABAJO y DÍAS DE RED

Para calcular una fecha n días laborables en el futuro, puede utilizar el Función DÍA DE TRABAJO . Para calcular el número de días laborables entre dos fechas, puede utilizar DÍAS DE RED .

Ejemplo de función de DÍA DE TRABAJO

|_+_|

Video: Cómo calcular las fechas de vencimiento con WORKDAY

Ejemplo de función NETWORKDAYS

|_+_|

Nota: Ambas funciones se saltan automáticamente los fines de semana (sábado y domingo) y también se saltan los días festivos, si se proporcionan. Si necesita más flexibilidad sobre los días que se consideran fines de semana, consulte la Función WORKDAY.INTL y Función NETWORKDAYS.INTL .

WEEKDAY y WEEKNUM

Para calcular el día de la semana a partir de una fecha, Excel proporciona el Función WEEKDAY . WEEKDAY devuelve un número entre 1 y 7 que indica domingo, lunes, martes, etc. Utilice el Función WEEKNUM para obtener el número de la semana en un año determinado.

Ejemplos de funciones WEEKDAY y WEEKNUM

|_+_|

Ingenieria

CONVERTIR

La mayoría de las funciones de ingeniería son bastante técnicas ... encontrará muchas funciones para números complejos en esta sección. sin embargo, el Función CONVERTIR es muy útil para las conversiones de unidades diarias. Puede usar CONVERT para cambiar las unidades de distancia, peso, temperatura y mucho más.

Ejemplo de función CONVERT

|_+_|

Funciones de información

ISBLANK, ISERROR, ISNUMBER e ISFORMULA

Excel proporciona muchas funciones para verificar el valor en una celda, incluyendo ES NÚMERO , ISTEXT , ISLOGICO , ESTÁ EN BLANCO , ISERROR , y ISFORMULA Estas funciones a veces se denominan funciones 'ES', y todas devuelven VERDADERO o FALSO según el contenido de una celda.

ISNUMBER ISTEXT ISLOGICAL ISBLANK ISERROR ISFORMULA

Excel también tiene ES IMPAR y INCLUSO Las funciones probarán un número para ver si es par o impar.

Por cierto, el relleno verde en la captura de pantalla anterior se aplica automáticamente con un formato condicional fórmula.

Funciones lógicas

Las funciones lógicas de Excel son un componente clave de muchas fórmulas avanzadas. Las funciones lógicas devuelven los valores booleanos VERDADERO o FALSO. Si necesita una introducción a las fórmulas lógicas, t su video pasa por muchos ejemplos .

Y, O y NO

El núcleo de las funciones lógicas de Excel son las Y función , la Función OR , y el No funciona . En la siguiente pantalla, cada una de estas funciones se utiliza para ejecutar una prueba simple en los valores de la columna B:

Funciones AND, OR y NOT

|_+_|

IFERROR e IFNA

los Función IFERROR y Función IFNA se puede utilizar como una forma sencilla de detectar y gestionar errores. En la pantalla de abajo, BUSCARV se utiliza para recuperar el costo de un elemento del menú. La columna F contiene solo un Función BUSCARV , sin manejo de errores. La columna G muestra cómo usar IFNA con BUSCARV para mostrar un mensaje personalizado cuando se ingresa un elemento no reconocido.

Función IFNA con ejemplo VLOOKUP

|_+_|

Mientras que IFNA solo detecta un error # N / A, el Función IFERROR detectará cualquier error de fórmula.

Funciones IF e IFS

los Función SI es una de las funciones más utilizadas en Excel. En la siguiente pantalla, IF verifica los puntajes de las pruebas y asigna 'aprobado' o 'reprobado':

Ejemplo de función IF

Varias funciones IF se pueden anidar juntos para realizar pruebas lógicas más complejas.

Nuevo en Excel a través de Office 365, el Función IFS puede ejecutar múltiples pruebas lógicas sin IF de anidación .

Ejemplo de función IFS

|_+_|

Funciones de búsqueda y referencia

VLOOKUP y HLOOKUP

Excel ofrece una serie de funciones para buscar y recuperar datos. El más famoso de todos es BUSCARV :

Ejemplo de función BUSCARV

|_+_|

Más: 23 cosas que debe saber sobre VLOOKUP .

HLOOKUP funciona como BUSCARV , pero espera datos dispuestos horizontalmente:

Excel mostrando la fórmula en lugar de calcular

Ejemplo de función HLOOKUP

|_+_|

ÍNDICE y PARTIDO

Para búsquedas más complicadas, ÍNDICE y PARTIDO ofrece más flexibilidad y potencia:

Ejemplo de función INDICE y COINCIDIR

|_+_|

Tanto el yo Función NDEX y el Función COINCIDIR son funciones potentes que aparecen en todo tipo de fórmulas.

Más: Cómo utilizar INDEX y MATCH

BUSCAR

los Función BUSCAR tiene comportamientos predeterminados que lo hacen útil a la hora de resolver determinados problemas. BUSCAR asume que los valores se ordenan en orden ascendente y siempre realiza una coincidencia aproximada. Cuando LOOKUP no puede encontrar una coincidencia, coincidirá con el siguiente valor más pequeño. En el siguiente ejemplo, usamos BUSCAR para encontrar la última entrada en una columna:

Ejemplo de función BUSCAR - última celda no vacía

Fila y columna

Puedes usar el Función FILA y Función COLUMNA para encontrar números de filas y columnas en una hoja de trabajo. Observe los valores de retorno de FILA y COLUMNA para el celda actual si no se proporciona ninguna referencia:

Ejemplo de función FILA y COLUMNA

La función de fila también aparece a menudo en fórmulas avanzadas que procesan datos con números de fila relativos .

Filas y columnas

los Función FILAS y Función COLUMNAS proporcionar un recuento de filas en una referencia. En la pantalla de abajo, contamos filas y columnas en un Tabla de Excel llamado 'Table1'.

Ejemplo de función FILAS y COLUMNAS

Nota ROWS devuelve un recuento de filas de datos en una tabla, excluyendo la fila de encabezado. Por cierto, aquí están 23 cosas que debe saber sobre las tablas de Excel .

Puedes usar el Función HIPERVÍNCULO para construir un vínculo con una fórmula. Tenga en cuenta que HYPERLINK le permite crear enlaces externos e internos:

Ejemplo de función HIPERVÍNCULO

|_+_|

GETPIVOTDATA

los Función GETPIVOTDATA es útil para recuperar información de tablas dinámicas existentes.

Ejemplo de función GETPIVOTDATA

|_+_|

ESCOGER

los Función ELEGIR es útil en cualquier momento que necesite tomar una decisión basada en un número:

Ejemplo de función ELEGIR

|_+_|

Video: Cómo utilizar la función ELEGIR

TRANSPONER

los Función TRANSPONER le ofrece una manera fácil de transponer datos verticales a horizontales y viceversa.

Ejemplo de función TRANSPONER

|_+_|

Nota: TRANSPOSE es una fórmula y por lo tanto es dinámica. Si solo necesita realizar una operación de transposición única, utilice Pegado especial en lugar de.

COMPENSAR

los Función OFFSET es útil para todo tipo de rangos dinámicos. Desde una ubicación inicial, le permite especificar desplazamientos de fila y columna, y también el tamaño final de fila y columna. El resultado es un rango que puede responder dinámicamente a condiciones e insumos cambiantes. Puede alimentar este rango a otras funciones, como en la siguiente pantalla, donde OFFSET construye un rango que se alimenta a la función SUM:

Ejemplo de función OFFSET

|_+_|

INDIRECTO

los Función INDIRECTA le permite construir referencias como texto. Este concepto es un poco complicado de entender al principio, pero puede ser útil en muchas situaciones. A continuación, usamos INDIRECTO para obtener valores de la celda A1 en 5 hojas de trabajo diferentes. Cada referencia es dinámica. Si cambia el nombre de una hoja, la referencia se actualizará.

Ejemplo de función INDIRECTA

|_+_|

La función INDIRECTA también se usa para 'bloquear' referencias para que no cambien, cuando se agregan o eliminan filas o columnas. Para obtener más detalles, consulte los ejemplos vinculados en la parte inferior de la Página de función INDIRECTA .

Precaución: tanto OFFSET como INDIRECT son funciones volátiles y puede ralentizar hojas de cálculo grandes o complicadas.

Funciones ESTADÍSTICAS

COUNT y COUNTA

Puede contar números con el Función COUNT y celdas no vacías con CONTAR . Puede contar celdas en blanco con COUNTBLANK , pero en la siguiente pantalla contamos celdas en blanco con CONTAR , que es más útil en general.

Ejemplos de funciones COUNT y COUNTA

|_+_|

COUNTIF y COUNTIFS

Para recuentos condicionales, el Función COUNTIF puede aplicar un criterio. los Función COUNTIFS puede aplicar varios criterios al mismo tiempo:

Ejemplos de funciones COUNTIF y COUNTIFS

|_+_|

Video: Cómo utilizar la función CONTAR.SI

SUM, SUMIF, SUMIFS

Para resumir todo, use el Función SUM . Para sumar condicionalmente, use SUMIF o SUMIFS. Siguiendo el mismo patrón que las funciones de conteo, el Función SUMIF puede aplicar solo un criterio mientras que el Función SUMIFS puede aplicar varios criterios.

Ejemplos de funciones SUM, SUMIFS y SUMIFS

|_+_|

Video: Cómo utilizar la función SUMIF

PROMEDIO, PROMEDIO SI y PROMEDIO SI

Siguiendo el mismo patrón, puede calcular un promedio con PROMEDIO , PROMEDIO SI , y PROMEDIO SI .

Ejemplos de funciones PROMEDIO, PROMEDIO SI y PROMEDIOIFS

|_+_|

MIN, MAX, GRANDE, PEQUEÑO

Puede encontrar valores mayores y menores con MAX y MIN y enésimo valores más grandes y más pequeños con GRANDE y PEQUEÑO . En la siguiente pantalla, 'datos' es el rango con nombre C5: C13, utilizado en todas las fórmulas.

Ejemplos de funciones MAX, MIN, LARGE y SMALL

|_+_|

Video: Cómo encontrar el enésimo valor más pequeño o más grande

MINIFS, MAXIFS

los MINIFS y MÁXIMO . Estas funciones le permiten encontrar valores mínimos y máximos con condiciones:

Ejemplos de funciones MINIFS y MAXIFS

|_+_|

Nota: MINIFS y MAXIFS son nuevos en Excel a través de Office 365 y Excel 2019.

MODO

los Función MODO devuelve el número que ocurre con más frecuencia en un rango:

Ejemplo de función MODE

|_+_|

RANGO

Para clasificar los valores de mayor a menor, o de menor a mayor, utilice el Función RANK :

Ejemplo de función RANK

Video: Cómo clasificar valores con la función RANK

Funciones MATH

SECCIÓN

Para cambiar los valores negativos a positivos, utilice el Función ABS .

Ejemplo de función ABS

|_+_|

RAND y RANDBETWEEN

Ambos Función RAND y Función EDGEWARE puede generar números aleatorios sobre la marcha. RAND crea números decimales largos entre cero y 1. RANDBETWEEN genera números enteros aleatorios entre dos números dados.

Ejemplos de funciones RAND y RANDBETWEEN

|_+_|

REDONDEAR, REDONDEAR, REDONDEAR, INT

Para redondear los valores hacia arriba o hacia abajo, utilice el Función REDONDA . Para forzar el redondeo a un número determinado de dígitos, utilice REDONDEO . Para forzar el redondeo hacia abajo, use REDONDEAR A LA BAJA . Para descartar la parte decimal de un número por completo, use el Función INT .

Ejemplos de funciones ROUND, ROUNDUP, ROUNDDOWN, INT

|_+_|

MUNDO, TECHO, PISO

Para redondear valores al más cercano múltiple utilizar el Función MROUND . los Función PISO y Función TECHO también se redondea a un múltiplo dado. Las fuerzas de SUELO se redondean hacia abajo y las fuerzas de TECHO se redondean hacia arriba.

Funciones MROUND, TECHO, SUELO

|_+_|

CONTRA

los Función MOD devuelve el resto después de la división. Esto suena aburrido y geek, pero MOD aparece en todo tipo de fórmulas, especialmente fórmulas que necesitan hacer algo 'cada enésima vez'. En la siguiente pantalla, puede ver cómo MOD devuelve cero cada tercer número cuando el divisor es 3:

Ejemplo de función MOD

SUMPRODUCTO

los Función SUMPRODUCT es una herramienta potente y versátil cuando se trata de todo tipo de datos. Puede usar SUMPRODUCT para contar y sumar fácilmente según los criterios, y puede usarlo de formas elegantes que simplemente no funcionan con COUNTIFS y SUMIFS. En la siguiente pantalla, estamos usando SUMPRODUCT para contar y sumar pedidos en marzo. Ver el Página SUMPRODUCT para obtener detalles y enlaces a muchos ejemplos.

Ejemplo de función SUMPRODUCT

|_+_|

TOTAL PARCIAL

los Función SUBTOTAL es una 'función agregada' que puede realizar una serie de operaciones en un conjunto de datos. En total, SUBTOTAL puede realizar 11 operaciones, incluyendo SUMA, PROMEDIO, RECUENTO, MÁX, MIN, etc. (ver esta página para ver la lista completa). La característica clave de SUBTOTAL es que ignorará las filas que se han 'filtrado' de un Tabla de Excel y, opcionalmente, filas que se han ocultado manualmente. En la siguiente pantalla, SUBTOTAL se usa para contar y sumar solo las 7 filas visibles en la tabla:

Ejemplo de función SUBTOTAL

|_+_|

AGREGAR

Como SUBTOTAL, el Función agregada también puede ejecutar una serie de operaciones agregadas en un conjunto de datos y, opcionalmente, puede ignorar filas ocultas. Las diferencias clave son que AGGREGATE puede ejecutar más operaciones (19 en total) y también puede ignorar errores.

En la siguiente pantalla, AGREGAR se usa para realizar operaciones MIN, MAX, GRANDE y PEQUEÑA ignorando los errores. Normalmente, el error en la celda B9 evitaría que estas funciones devuelvan un resultado. Ver esta pagina para obtener una lista completa de las operaciones que AGGREGATE puede realizar.

Ejemplo de función AGREGAR

|_+_|

Funciones de TEXTO

IZQUIERDA, DERECHA, MEDIA

Para extraer caracteres del lado izquierdo, derecho o medio del texto, utilice IZQUIERDA , DERECHO , y Funciones MID :

Ejemplos de funciones LEFT, RIGHT, MID

|_+_|

LEN

los Función LEN devolverá la longitud de una cadena de texto. LEN aparece en muchas fórmulas que cuentan palabras o caracteres .

Ejemplo de función LEN

Para buscar texto específico en una celda, use el Función ENCONTRAR o Buscando función . Estas funciones devuelven la posición numérica del texto coincidente, pero SEARCH permite comodines y FIND distingue entre mayúsculas y minúsculas. Ambas funciones arrojarán un error cuando no se encuentre el texto, por lo que debe ajustar el ES NÚMERO función para devolver VERDADERO o FALSO ( ejemplo aquí ).

Ejemplos de funciones ENCONTRAR y BÚSQUEDA

|_+_|

REEMPLAZAR, SUSTITUIR

Para reemplazar texto por posición, use el Función REEMPLAZAR . Para reemplazar texto por coincidencia, use el Función SUSTITUIR . En el primer ejemplo, REPLACE elimina los dos asteriscos (**) reemplazando los dos primeros caracteres con una cadena vacía (''). En el segundo ejemplo, SUBSTITUTE elimina todos los caracteres hash (#) reemplazando '#' con ''.

Ejemplos de funciones REEMPLAZAR y SUSTITUIR

|_+_|

CÓDIGO, CHAR

Para averiguar el código numérico de un carácter, utilice el Función CODIGO . Para traducir el código numérico de nuevo a un carácter, use el Función CHAR . En el siguiente ejemplo, CODE traduce cada carácter de la columna B a su código correspondiente. En la columna F, CHAR vuelve a traducir el código a un carácter.

Ejemplos de funciones CODE y CHAR

|_+_|

Video: Cómo utilizar las funciones CODIGO y CHAR

RECORTAR, LIMPIAR

Para deshacerse del espacio adicional en el texto, use el Función TRIM . Para eliminar los saltos de línea y otros caracteres que no se imprimen, utilice LIMPIO .

Ejemplos de funciones TRIM y CLEAN

|_+_|

Video: Cómo limpiar texto con TRIM y CLEAN

CONCAT, TEXTJOIN, CONCATENATE

Las novedades en Excel a través de Office 365 son CONCAT y TEXTJOIN. los Función CONCAT le permite concatenar (unir) varios valores, incluido un rango de valores sin un delimitador. los Función TEXTJOIN hace lo mismo, pero le permite especificar un delimitador y también puede ignorar valores vacíos.

Ejemplos de funciones CONCAT y TEXTJOIN

|_+_|

Excel también proporciona Función CONCATENAR , pero no ofrece funciones especiales. No me molestaría con eso y en su lugar lo haría concatenar directamente con el carácter ampersand (&) en una fórmula.

EXACTO

los Función EXACTA le permite comparar dos cadenas de texto distinguiendo entre mayúsculas y minúsculas.

Ejemplo de función EXACTA

SUPERIOR, INFERIOR, ADECUADO

Para cambiar el caso del texto, use el SUPERIOR , MÁS BAJO , y Función APROPIADA

Ejemplos de funciones SUPERIOR, INFERIOR, CORRECTA

|_+_|

Video: Cómo cambiar el caso con fórmulas

TEXTO

Por último, pero definitivamente no menos importante, es el Función TEXTO . La función de texto le permite aplicar formato numérico a los números (incluidas fechas, horas, etc.) como texto. Esto es especialmente útil cuando necesita incrustar un número formateado en un mensaje, como 'La oferta finaliza el [fecha]'.

Ejemplo de función TEXT

|_+_|

Más: Ejemplos detallados de formato de número personalizado .

Funciones de matriz dinámica

Matrices dinámicas son nuevos en Excel 365 y son un importante actualizar al motor de fórmulas de Excel. Como parte de la actualización de la matriz dinámica, Excel incluye nuevas funciones que aprovechan directamente las matrices dinámicas para resolver problemas que tradicionalmente son difíciles de resolver con fórmulas convencionales. Si está utilizando Excel 365, asegúrese de conocer estas nuevas funciones:

Función Objetivo
FILTRAR Filtrar datos y devolver registros coincidentes
RANDARRAY Genera una matriz de números aleatorios
SECUENCIA Genera una matriz de números secuenciales
CLASIFICAR Ordenar rango por columna
ORDENAR POR Ordenar rango por otro rango o matriz
ÚNICO Extrae valores únicos de una lista o rango
XLOOKUP Reemplazo moderno para BUSCARV
XMATCH Reemplazo moderno de la función MATCH

Video: Nuevas funciones de matriz dinámica en Excel (unos 3 minutos).

Navegacion rapida

SECCIÓN , AGREGAR , Y , PROMEDIO , PROMEDIO SI , PROMEDIO SI , TECHO , CARBONIZARSE , ESCOGER , LIMPIO , CÓDIGO , COLUMNA , COLUMNAS , CONCAT , CONCATENAR , CONVERTIR , CONTAR , CONTAR , COUNTBLANK , CONTAR , CONTADOS , FECHA , DATEDIF , DÍA , EDAR , MESES , EXACTO , ENCONTRAR , SUELO , GETPIVOTDATA , HLOOKUP , HORA , HIPERVÍNCULO , SI , SI ERROR , IFNA , IFS , ÍNDICE , INDIRECTO , EN T , ESTÁ EN BLANCO , ISERROR , INCLUSO , ISFORMULA , ISLOGICO , ES NÚMERO , ES IMPAR , ISTEXT , GRANDE , IZQUIERDA , LEN , BUSCAR , MÁS BAJO , PARTIDO , MAX , MÁXIMO , MEDIO , MIN , MINIFS , MINUTO , CONTRA , MODO , MES , MROUND , DÍAS DE RED , NO , AHORA , COMPENSAR , O , ADECUADO , FILA , BORDE ENTRE , RANGO , REEMPLAZAR , DERECHO , REDONDO , REDONDEAR A LA BAJA , REDONDEO , FILA , FILAS , BUSCAR , SEGUNDO , PEQUEÑO , SUSTITUIR , TOTAL PARCIAL , SUMA , SUMIF , SUMIFS , SUMPRODUCTO , TEXTO , TEXTJOIN , HORA , HOY DIA , TRANSPONER , PODAR , SUPERIOR , BUSCARV , DÍA LABORABLE , WEEKNUM , DIA LABORAL , AÑO , YEARFRAC

Autor Dave Bruns


^