Colección de fórmulas de funciones de tablas de Excel

Un resumen de las fórmulas de hojas de cálculo más utilizadas en Excel

1. Fórmulas básicas en Excel

1. La fórmula para encontrar contenido duplicado: =SI(CONTAR.SI( A:A ,A2)>1,"repetir","repetir").

2. Utilice la fecha de nacimiento para calcular la fórmula de edad: = trunc((número de días 360 (H6, "2009/8/30", false))/360, 0).

3. La fórmula para calcular la fecha de nacimiento del número de identificación de 18 dígitos es = Concatenar (mid (e2, 7, 4), "/", mid (e2, 11, 2), "/", mid (e2, 13.

4. Deje que el sistema extraiga automáticamente el sexo del número de identificación ingresado. Puede ingresar la siguiente fórmula:

= if (len (C2) = 15, if ( mod (mid (C2, 15, 1), 2) = 1, "Masculino", "Female"), if (mod (C2, 6558). ¿C2? Representa la celda donde se encuentra el ID se ingresa el número.

1, suma: =SUM(K2:K56)? Suma el área de K2 a K56;

2. Área promedio de K2 K56;

3.Rank: =RANK(K2,K$2:K$56)? Clasifica los resultados de 55 estudiantes;

4.Rank:= IF(K2>=85, "Excelente" if (K2 & gt=74, "Bueno" if (K2 & gt=60, "Pass", "Fail"))

5. Evaluación general de el término:= K2*0.3+M2*0.3+N2*0.4? ¿Suponiendo que las columnas K, M y N almacenan datos de los estudiantes? ¿Final? (K2:K56)? Encuentra la puntuación más alta de K2 a K56 (55 estudiantes);

7. Puntuación mínima:=MIN(K2:K56)? estudiantes);

8. Número de personas en el segmento de puntuación:

(1) =CONTAR.SI(K2:K56, "100") ¿Usar 100 puntos para encontrar K2 a K56? Número de personas; Supongamos que el resultado está almacenado en la celda K57;

(2) =COUNTIF(K2:K56, "& gt= 95")-K57. personas de K2 a K56; Supongamos que el resultado se almacena en la celda K58;

(3)=COUNTIF(K2:K56, "& gt=90")-SUM(K57:K58)? ~ 94,5 puntos para encontrar K2 a K56 Número de personas; asumiendo que el resultado está almacenado en la celda K59

(4)=COUNTIF(K2:K56, "& gt=85")-SUM(K57: K59)? Utilice 85 ~ 89,5 puntos para encontrar K2 Número de personas en K56; suponiendo que el resultado esté almacenado en la celda K60; ¿SUM(K57:K60)? ¿Encontrar de K2 a K56? Número de personas con 70 ~ 84,5 puntos; suponiendo que el resultado esté almacenado en la celda K61; =60")-SUM(K57:K61)? Utilice 60 ~ 69,5 puntos para encontrar el número de personas de K2 a K56; suponga que el resultado está almacenado en la celda K62;

(7) =CONTAR.SI(K2:K56, "<60") de K2 a K56 Divida el siguiente número de personas; suponga que el resultado está almacenado en la celda K63;

Nota: La función CONTAR.SI también puede calcular el número de niños y niñas en un área determinada.

¿Por ejemplo =CONTAR.SI(C2:C351, "Hombre")? Encuentra el número de machos de C2 a C351 (350 en total);

9. Excelente tasa: =SUM(K57:K60)/55*100.

10, tasa de aprobación:=SUM(K57:K62)/55*100.

11, desviación estándar:=STDEV(K2:K56)? Encuentre la fluctuación del desempeño en el área de K2 a K56 (55 estudiantes) (cuanto menor es el valor, menor es la diferencia de desempeño de los estudiantes en la clase, por el contrario, la clase está polarizada

12); , suma condicional:=SUMIF (B2:B56, "Masculino", K2:K56)? Supongamos que la columna B almacena el género de los estudiantes y la columna K almacena las puntuaciones de los estudiantes. El resultado devuelto por esta función es encontrar la suma de las puntuaciones de los niños en esta clase;

13, suma de múltiples condiciones: {=. SUM(IF(C3:C322 = "Male", if (G3: G322 = 1, 1, 0))}? Supongamos que la columna C (área C3:C322) almacena el sexo del estudiante y la columna G (área G3 :G322) almacena el código de clase del estudiante (1, 2, 3, 4, 5), el resultado devuelto por esta función representa el número de niños en una clase; esta es una función de matriz Presione Ctrl+Shift+Enter después de la entrada. ? {}?No se puede ingresar manualmente, solo se pueden usar combinaciones de teclas.

14. Calcular automáticamente la edad según la fecha de nacimiento: = trunc((número de días 360 (d3, ahora ()))/360, 0)

Supongamos que la columna D almacena la fecha de nacimiento del estudiante, ingrese esta función en la columna E para generar la edad completa del estudiante.

15. Tres consejos en Word:

(1) ¿Ingresar tres seguidos? ~?Puedes obtener una línea ondulada.

(2) ¿Ingresar tres consecutivamente? -?Puedes obtener una línea recta.

¿Ingresar tres seguidos? =?Puedes obtener una línea recta doble.

1. Cuando una celda en Excel cumple ciertas condiciones, cómo mostrar un color específico en otra celda, por ejemplo:

Cuando a1 > 1, C1 se muestra en rojo.

a 1 <0, C1 se muestra en amarillo.

El método es el siguiente:

1. La unidad llega a la celda C1, ¿hace clic? ¿Formato? >? ¿Formato condicional? , la condición 1 se establece en:

Fórmula=A1=1

2. ¿Formato? ->? ¿Fuente? ->? ¿color? , haz clic en el punto rojo? ¿está seguro? .

La condición 2 se establece en:

Fórmula = y (a 1 > 0, a 1 < 1)

3. ¿Punto? ¿Formato? ->? ¿Fuente? ->? ¿color? , haz clic en el punto verde? ¿está seguro? .

La condición 3 se establece en:

Fórmula = a1

¿Punto? ¿Formato? ->? ¿Fuente? ->? ¿color? , haz clic en el punto amarillo? ¿está seguro? .

4. Después de configurar las tres condiciones, haga clic en? ¿está seguro? Sal de inmediato.

En segundo lugar, cómo controlar la longitud de cada columna de datos en EXCEL para evitar entradas repetidas.

1. Utilice la validez de los datos para definir la longitud de los datos.

Utilice el mouse para seleccionar el rango de datos que desea ingresar y luego haga clic en "Datos" -> "Validez" -> "Configuración" y "Condiciones de validez" son "Permitir" y "Longitud del texto" es igual a “5” (las condiciones específicas se pueden cambiar según sus necesidades).

También puede definir algunos mensajes de aviso, mensajes de advertencia de error y si desea abrir el método de entrada en chino. Haga clic en Aceptar después de definirlo.

2. Utilice formato condicional para evitar duplicaciones.

Seleccione la columna a y haga clic en "Formato" --> "Formato condicional", ¿establece la condición en? Fórmula =CONTAR.SI($A:$A,$A1)>1?, haga clic en "Formato"-->"Fuente"->"Color", seleccione rojo y haga clic en "Aceptar" dos veces.

Después de esta configuración, si la longitud de los datos de entrada es incorrecta, aparecerá un mensaje. Si los datos se repiten, la fuente se volverá roja.

3. ¿Cómo identificar la diferencia entre la columna B y la columna A en EXCEL?

(1) Si necesita comparar los datos en la misma fila de la columna A y la columna B:

Suponga que la primera fila es el título, haga clic en la celda A2 y luego haga clic en ? ¿Formato? ->? ¿Formato condicional? , establezca la condición en:

? ¿El valor de la celda no es igual? =B2

¿Punto? ¿Formato? ->? ¿Fuente? ->? ¿color? , seleccione rojo, haga clic dos veces? ¿está seguro? .

Utiliza el pincel de formato para copiar el formato condicional de la celda A2.

La columna b se puede configurar de esta manera.

(2) Si la columna A y la columna B se comparan como un todo (es decir, los mismos datos no están en la misma fila):

Suponiendo que la primera fila es la encabezado, haga clic en la celda A2 y haga clic en? ¿Formato? ->? ¿Formato condicional? , establezca la condición en:

? ¿fórmula? =CONTAR.SI($B:$B,$A2)=0

¿Puntos? ¿Formato? ->? ¿Fuente? ->? ¿color? , seleccione rojo, haga clic dos veces? ¿está seguro? .

Utiliza el pincel de formato para copiar el formato condicional de la celda A2.

La columna b se puede configurar de esta manera.

Después de configurar de acuerdo con el método anterior, todos los datos de la columna AB no se colorearán. Los datos de la columna A con la columna B pero ninguna columna A o la columna B con la columna A pero ninguna columna A se marcarán. fuente roja.

4. ¿Cómo ordenar lotes por filas en EXCEL?

Supongamos que hay muchos datos (valores numéricos) y cada fila debe ordenarse de mayor a menor. ¿Cómo hacerlo?

Debido a que la clasificación por fila y la clasificación por columna solo pueden tener una palabra clave principal, la clasificación por palabra clave secundaria solo se puede realizar cuando la palabra clave principal es la misma. Entonces este problema no se puede resolver clasificando. La solución es la siguiente:

1. Suponga que sus datos están en las columnas A a E, ingrese la fórmula en la celda F1:

=LARGE($A1:$E1,COLUMN). (A1))

Utilice el controlador de relleno para copiar la fórmula hacia abajo y hacia la derecha en el rango apropiado.

Tus datos originales aparecerán en las columnas F a J en orden descendente de filas. ¿Se puede proporcionar si es necesario? ¿Pegar especial/valor? Copiar a otros lugares.

Nota: La fórmula del paso 1 se puede modificar según su situación real (rango de datos). Si desea ordenar de pequeño a grande, cambie la fórmula a = pequeño ($a1: $e1, columna (a1)).

5. Utilice hábilmente el conteo de combinación de funciones y el conteo de múltiples condiciones.

Ejemplo: La primera fila es el encabezado, ¿qué es la columna A? ¿nombre? ¿Qué es la columna b? ¿clase? ¿Qué es la columna C? ¿Puntuaciones chinas? ¿Qué es la columna d? ¿Resultado de admisión? , estadísticas ahora? ¿clase? ¿para qué? ¿dos? ,?Puntuaciones chinas? ¿Mayor o igual a 104? ¿Resultado de admisión? ¿para qué? ¿Pesado en esto? Número de personas. Las estadísticas se almacenan en otras columnas de la hoja de trabajo.

La fórmula es la siguiente:

= SUMA(SI((B2:b 9999 = " dos ")*(C2:c 9999 & gt; = 104)*(D2 :d 9999 = " duplicado "), 1, 0))

Después de ingresar la fórmula, presione Ctrl+Shift+Enter para agregar automáticamente el símbolo de fórmula matricial "{}".

6. ¿Cómo determinar si una celda contiene el texto especificado?

Suponiendo que la celda A1 tiene "texto especificado", puede utilizar cualquiera de las siguientes fórmulas:

=SI(CONTAR.SI(A1, "*" & "texto especificado"&" * ")=1,"Sí","Ninguno")

=IF(ISERROR(FIND("Texto especificado",A1,1)),"Ninguno","Sí")

Encontrar el número de datos no duplicados en un área determinada

Por ejemplo, encontrar el número de datos no duplicados en el rango A1:A100. Solo un número se repite varias veces. Hay dos métodos de cálculo:

Uno es utilizar la fórmula matricial:

= SUMA(1/CONTAR.SI(A1:A100, a 1:a 100))

Después de ingresar la fórmula, presione Ctrl+Shift+Enter para agregar automáticamente el símbolo de fórmula matricial "{}".

La segunda es utilizar la función de suma de productos:

= suma producto(1/CONTAR.SI(A1:A100, a 1:a 100))

7. Hay muchas hojas de trabajo en un libro de trabajo. ¿Cómo organizar rápidamente una tabla de contenidos?

1. Utilice Macro 3.0 para obtener el nombre de cada hoja de trabajo. Método:

Ctrl+F3 muestra un cuadro de diálogo de nombre personalizado llamado X. ¿Ubicación de referencia? Ingrese en el cuadro:

=MID(GET.WORKBOOK(1), FIND("]",GET.WORKBOOK(1))+1,100)

Claro

2. Utilice la función de hipervínculo para insertar enlaces en lotes. El método es el siguiente:

En la hoja de cálculo del índice (normalmente la primera hoja de cálculo), introduzca la fórmula en la celda A2:

=Hyperlink(" # " & INDEX(X,ROW())& !A1 ",INDEX(X,ROW()))

Abajo Complete la fórmula hasta que ocurra un error , luego genera la tabla de contenido

Supongo que te gusta:

1 Resumen.

18 Las habilidades de operación de fórmulas más utilizadas en Excel

2 Descargue la colección completa de fórmulas de funciones de Excel para aprender fórmulas de funciones de Excel

3. fórmulas en Excel

4. El resumen de funciones de Excel más completo de la historia

5. Introducción a las fórmulas de funciones de Excel