Especialista en Excel

Microsoft Excel 2019 Básico

TEMA: FUNCIONES ESTADISTICAS Intructor: Hugo

Objetivos:
Contar
Contara, Contar.Blanco
Max y Min
K.esimo.Mayor y K.esimo.Menor
Promedio y Promedio.Si
Contar.Si y Sumar.Si

INSTRUCTOR:

Hugo Toledo Flores

[email protected]
Excel
9 Básico Online
Intructor: Hugo Toledo Flores

ugo Toledo Flores

[email protected]
 FUNCIONES CONTAR,CONTARA Y CONTAR.BLANCO

CONTAR ===> NOS PERMITE CONTAR CELDAS QUE CONTIENEN NÚMEROS

CONTARA ===> NOS PERMITE CONTAR CELDAS QUE CONTIENEN TODO TIPO DE CARACTER
CONTAR.BLANCO ===> NOS PERMITE CONTAR CELDAS VACÍAS

Contabiliza sólo los datos numéricos que

1) CONTAR =CONTAR(Rango)
existen en un rango de celdas.

Contabiliza el número de celdas vacías que

2) CONTAR.BLANCO =CONTAR.BLANCO(Rango)
hay en un rango de celdas.

SOCIO CATEG. CUOTA Nº Socios que Aportaron

Robin Casas A S/.250.00 10

Juan Robles C S/.350.00
Carmen Díaz C S/.150.00 Nº Socios que NO Aportaron

Javier Soto B S/.200.00 3

Janeth Vargas A S/.100.00
Carlos López B S/.100.00
Juan Castro C S/.200.00
Julio Lazo A S/.150.00
Rocío Vega A
Jorge Pérez B S/.50.00
Lourdes García B
Jorge Tello C S/.200.00
Antonio Meléndez A
Contabiliza todos los datos que hay en un
3) CONTARA =CONTARA(Rango)
rango de celdas.

ALUMNO EDAD TELEFONO Nº Alumnos con Teléfono

Manuel Burga 23 9878-6326 8

Juan Robles 20 4503-265
Carmen Díaz 28 Nº Alumnos sin Teléfono

Javier Soto 18 9745-5421 3

Janeth Vargas 20 9256-9856
Carlos López 21 9745-6532 Total de Alumnos (nombre)

Juan Castro 22 11
Julio Lazo 26 9874-6598
Rocío Vega 20 442-6530 Total de Alumnos (edad)

Jorge Pérez 19 11
Lourdes García 25 9978-5484
 15 Juan 600
60 8787
Excel hola 500
N NÚMEROS 1000
N TODO TIPO DE CARACTERES Historia 1200
600
4 3 3
=COUNT(J1:J6) =COUNTA(K1:K6) =COUNTBLANK(L1:L6)

ios que Aportaron

10 =COUNT(D14:D26) #N/A

s que NO Aportaron

3 =COUNTBLANK(D14:D26)

mnos con Teléfono

8 =COUNTA(D31:D41) 0

mnos sin Teléfono

3 =COUNTBLANK(D31:D41)

Alumnos (nombre)

11 =COUNTA(B31:B41)

de Alumnos (edad)

11 =COUNT(C31:C41)
 FUNCION MAX Y MIN

=MAX(NUM1,NUM2,NUM3)
HALLA EL MÁXIMO VALOR DE UN RANGO

VENDEDOR ENERO FEBRERO MARZO

VENDEDOR1 1861 2791 1595 MÁXIMO VALOR DE ENERO
VENDEDOR2 2263 2660 2913
VENDEDOR3 2390 1778 2701 MÁXIMO VALOR DE MARZO
VENDEDOR4 1105 1733 1755
VENDEDOR5 1099 2226 1347 MÁXIMO VALOR DEL VENDEDOR 3
VENDEDOR6 1263 2874 1932
VENDEDOR7 1607 2944 2339 MÁXIMO VALOR DEL VENDEDOR 7
VENDEDOR8 1477 2403 2730
VENDEDOR9 1999 2413 1185 MÁXIMO VALOR DE TODO EL RANGO
VENDEDOR10 1656 1592 1732

=MIN(NUM1,NUM2,NUM3)
HALLA EL MÍNIMO VALOR DE UN RANGO

VENDEDOR ENERO FEBRERO MARZO

VENDEDOR1 1861 2791 1595 MÍNIMO VALOR DE ENERO
VENDEDOR2 2263 2660 2913
VENDEDOR3 2390 1778 2701 MÍNIMO VALOR DE FEBRERO Y MARZO
VENDEDOR4 1105 1733 1755
VENDEDOR5 1099 2226 1347 MÍNIMO VALOR DEL VENDEDOR 2
VENDEDOR6 1263 2874 1932
VENDEDOR7 1607 2944 2339 MÍNIMO VALOR DEL VENDEDOR 3,5,9
VENDEDOR8 1477 2403 2730
VENDEDOR9 1999 2413 1185 MÍNIMO VALOR DE TODO EL RANGO
VENDEDOR10 1656 1592 1732
 5
6
-9
12
500
8
-3

500 =MAX(N1:N7)

50000
2390 =MAX(B12:B21) 200
8
2913 =MAX(D12:D21) 96
10
2701 =MAX(B14:D14) 40
56
2944 =MAX(B18:D18) 80
50000
2944 =MAX(B12:D21) =MAX(M11:M18)

1
1099 =MIN(B31:B40) 2
-4
1185 =MIN(C31:D40) 4
5
2263 =MIN(B32:D32) -4 =MIN(M30:M34)

1099 =MIN(B33:D33,B35:D35,B39:D39)

1099 =MIN(B31:D40)
 FUNCION PROMEDIO

=PROMEDIO(Rang

ALUMNOS NOTA 1 NOTA 2 NOTA 3 NOTA 4

JUAN 14 10 9 20
LUIS 14 13 16 20
DIANA 15 14 8 19
PROMEDIO 14 12 11 20

CUADRO DE INGRESOS

Año 2021 Enero Febrero Marzo Abril Mayo

Producto 1 3,256 3,500 2,600 3,500 3,569

Producto 2 1,600 4,800 2,300 3,400 4,563
Producto 3 1,450 5,900 2,750 5,800 4,893
Producto 4 2,500 4,100 2,100 4,522 1,426
Producto 5 2,600 2,500 3,200 1,622 3,563

Hallar:

1.- El promedio del 2do trimestre 3,804 =AVERAGE(E23:G27)

2.- El promedio del 2do mes 4,160 =AVERAGE(C23:C27)
3.- El promedio de Febrero y Mayo 3,881 =AVERAGE(C23:C27,F23:F27)
4.- El promedio del 1 bimestre 3,221 =AVERAGE(B23:C27)
5.- El promedio del semestre 3,407 =AVERAGE(B23:G27)
 FUNCION PROMEDIO

PROMEDIO(Rango)

PROMEDIO1 PROMEDIO2
13 15 =(SUM(C10:F10)-MIN(C10:F10))/3 NÚM.1
16 17 =(SUM(C11:F11)-MIN(C11:F11))/3
14 16 =(SUM(C12:F12)-MIN(C12:F12))/3

Promedio2: Calcular el Promedio2 considerando solo las 3

mejores Notas.

CUADRO DE INGRESOS

Promedio
Junio
por Producto
Calcular
7,821 4,041 =AVERAGE(B23:G23)
2,630 3,216 =AVERAGE(B24:G24)
2,587 3,897 =AVERAGE(B25:G25)
4,512 3,193 =AVERAGE(B26:G26)
2,653 2,690 =AVERAGE(B27:G27)

3:C27,F23:F27)
 PUEDE SER UNA
CELDA O RANGO

AVERAGE(B23:G23)
AVERAGE(B24:G24)
AVERAGE(B25:G25)
AVERAGE(B26:G26)
AVERAGE(B27:G27)
 4- =K.ESIMO.MAYOR(RANGO,K)

puede usar K.ESIMO.MAYOR para devolver el mayor valor de un resultado, el segundo

HALLAR LOS 5
NÚMERO PRIMEROS VALORES
1 1382 1ER MAYOR 1382
2 1242 2do MAYOR 1242
3 1104 3er MAYOR 1104
4 1083 4to MAYOR 1083
5 686 5to MAYOR 686
615
460
423
375
285

5- =K.ESIMO.MENOR(RANGO,K)

puede usar K.ESIMO.MENOR para devolver el menor valor de un resultado, el segundo

HALLAR LOS 5
NÚMERO PRIMEROS VALORES
1432 1ER MENOR 273
294 2do MENOR 294
1092 3er MENOR 502
1107 4to MENOR 621
502 5to MENOR 678
621
1473
678
1487
273
ANGO,K)

un resultado, el segundo resultado o el tercero.

Mayor Nro. 8 =LARGE(L12:L19,1)

=LARGE(C10:C19,1) Menor Nro. 1 =SMALL(L12:L19,1)
=LARGE(C10:C19,2)
=LARGE(C10:C19,3) 1
=LARGE(C10:C19,4) 2
=LARGE(C10:C19,5) 3
4
5
6
7
8

ANGO,K)

n resultado, el segundo resultado o el tercero.

=SMALL(C34:C43,1)
=SMALL(C34:C43,2)
=SMALL(C34:C43,3)
=SMALL(C34:C43,4)
=SMALL(C34:C43,5)
 CONTAR.SI(RANGO,CRITERIO)
Use CONTAR.SI, una de las funciones estadísticas, para contar el número de celdas que cumplen u

ZONA
NORTE RANGO ====> SELECCIÓN DEL RANGO DE UNA
SUR
ESTE CRITERIO ====> UN VALOR QUE SE ENCUENTR
NORTE
SUR

Sexo Área Sueldo

M Ventas S/. 2,500.00
M Legal S/. 4,200.00
F Contabilidad S/. 3,400.00
F Ventas S/. 3,000.00
F Sistemas S/. 5,500.00
F Ventas S/. 1,900.00
M Contabilidad S/. 1,300.00

EJERCICIOS:

Departamentos E_Civil Sueldo2

Lima S S/ 2,500.00
Lambayeque C S/ 4,200.00
Piura C S/ 3,400.00
Tumbes D S/ 3,000.00
Lima S S/ 5,500.00
Piura C S/ 1,900.00
Lima V S/ 1,300.00

¿Cuántos empleados son casados? 3

¿Cuántos empleados son del depart. Lima? 3

¿Cuántos empleados su sueldo es menor a 3000? 3

 Utilizando comodines para la creación de criterios

* ====> N° CARACTERES
NOS PERMITE DENTRO UNA CELDA REEMPLA

COMIENZA CON UNA LETRA TERMINA CON UNA LETRA

DINA
D* DIEGO *R
DANTE

? ====> UN SOLO CARÁCTER

NOS PERMITE DENTRO DE UNA CELDA RE

SEGUNDA LETRA
GUIDO
?U* GUILLERMO
GUSTAVO

<> ====> SÍMBOLO DIFERENTE

NOS PERMITE SEPARAR UN VALOR DE UN

DIFERENTE DE LIMA
DIFERENTE DE VENTAS

Alumnos Curso Turno

Luis Excel Mañana
Miguel Word Tarde
Yeny Word Noche
Martha Windows Tarde
Leonardo Access Tarde
Mario Excel Mañana
Alicia Windows Noche

¿Cuántos alumnos su nombre comienza con "M"?

¿Cuántos alumnos su nombre comienza con "L"?

¿Cuántos alumnos su nombre termina en la letra "O"?

¿Cuántos alumnos su nombre contiene la letra "N"?

¿Cuántos alumnos su segunda letra de su nombre es "A"?

¿Cuántos alumnos su tercera letra de su nombre es "R"?

¿Cuántos alumnos no son del curso de Excel?

¿Cuántos alumnos no son del turno noche?

GO,CRITERIO)
celdas que cumplen un criterio

LECCIÓN DEL RANGO DE UNA COLUMNA

UN VALOR QUE SE ENCUENTRA DENTRO DE LA COLUMNA

EJEMPLO: "NORTE" O "SUR" O "ESTE"

¿Cuántas personas son del sexo masculino? Ventas

Legal
3 =COUNTIF(Sexo,"M") Contabilidad
Sistemas
¿Cuántas personas son del área de ventas?

Ventas 3 =COUNTIF(Área,"Ventas")

¿Cuántas personas su sueldo es mayor a 2800?

4 =COUNTIF(Sueldo,">2800")

=COUNTIF(B32:B38,"C")

=COUNTIF(A32:A38,"Lima")

=COUNTIF(Sueldo2,"<3000")
erios

Windows
NTRO UNA CELDA REEMPLAZAR UNA CANTIDAD DE CARACTERES Word
Excel
Access
CONTIENE LETRA "S"
ADELMAR SONIA
AGAR *S* INES
AMBAR ELOISA

CTER
ENTRO DE UNA CELDA REEMPLAZAR UNA SOLA LETRA

TERCERA LETRA
GUIDO
??I* ALICIA
GUILLERMO

ENTE
EPARAR UN VALOR DE UN RANGO DE DATOS

"<>Lima"
"<>Ventas"

3 =COUNTIF(Alumnos,"M*")

2 =COUNTIF(Alumnos,"L*")

2 =COUNTIF(Alumnos,"*O")
2 =COUNTIF(Alumnos,"*N*")

2 =COUNTIF(Alumnos,"?A*")

2 =COUNTIF(Alumnos,"??R*")

5 =COUNTIF(Curso,"<>Excel")

5 =COUNTIF(Turno,"<>Noche")
 FRUTAS
PERA Cuantas frutas "Pera" hay en la lista:
NARANJA #N/A
PERA
PERA
PERA
NARANJA
PLATANO
NARANJA
NARANJA

Legal
* Ana
* Luis A* Nombres que inician con la Letra A
* Arturo *O Nombres Que terminen con la Letra O
* Fabian *r*
Arnoldo
Vilma
carlos
 SUMAR.SI(RANGO,CRITERIO,RANGOSU
Use SUMAR.SI, NOS PERMITE SUMAR VALORES DEPENDIENDO UNA CONDI

ZONA MONTO
NORTE S/. 500.00
SUR S/. 1,000.00 3-RANGOSUMA ====> SELECCIÓN DEL RANGO D
ESTE S/. 1,500.00
NORTE S/. 1,300.00
SUR S/. 800.00 2-CRITERIO ====> UN VALOR QUE SE ENCUENTR
EJEMPLO: "NORTE" O "SUR" O "ES

1-RANGO ====> SELECCIÓN DEL RANGO DE UNA COLUMNA

HALLAR EL MONTO TOTAL DE LA ZONA NORTE S/. 1,800.00

=SUMAR.SI(B6:B10,"NORTE",C6:C10)
Sexo1 Área Sueldo3
M Ventas 2,500.00 Total de sueldo del sexo femenino
M Legal 4,200.00
F Contabilidad 3,400.00 S/. 13,800.00 =SUMIF(Sexo1,"F",Sueldo3)
F Ventas 3,000.00
F Sistemas 5,500.00 Total de sueldo del área de Sistemas
F Ventas 1,900.00
M Contabilidad 1,300.00
Sistemas S/. 5,500.00

Total de sueldo de todos los empleados que su sue

S/.20,500.00 =SUMIF(Sueldo3,">1500",Sueldo

Total de sueldo de todas las áreas que terminen en

S/. 12,900.00 =SUMIF(Area,"*S",Sueldo3)

Vendedor Zonas Ventas TOTAL DE VTAS. POR ZONAS

Juan Norte S/. 1,405.00 Norte S/. 10,739.00
Diana Sur S/. 1,531.00 Oeste S/. 5,673.00
Juan Este S/. 1,648.00 Sur S/. 11,793.00
Cesar Sur S/. 941.00 Este S/. 10,239.00
Ana Oeste S/. 1,747.00
Diana Oeste S/. 1,226.00 TOTAL DE VTAS. POR VENDEDOR
Cesar Norte S/. 1,538.00 Juan S/.9,482.00
Diana Norte S/. 1,357.00 Diana S/.11,402.00
Juan Este S/. 817.00 Cesar S/.8,592.00
Ana Oeste S/. 1,215.00 Ana S/.8,968.00
Diana Este S/. 1,550.00
Juan Sur S/. 654.00
Ana Sur S/. 1,773.00 TOTAL DE VTAS. POR RANGO
Diana Este S/. 1,695.00 >1000 S/.31,452.00
Cesar Sur S/. 1,712.00 <1000 S/.6,992.00
Ana Sur S/. 1,895.00
Juan Este S/. 1,707.00
Juan Norte S/. 1,130.00
Diana Sur S/. 1,501.00
Diana Este S/. 723.00
Cesar Este S/. 1,119.00
Juan Norte S/. 702.00
Ana Este S/. 980.00
Cesar Norte S/. 690.00
Diana Oeste S/. 679.00
Cesar Oeste S/. 806.00
Ana Norte S/. 1,358.00
Juan Norte S/. 1,419.00
Diana Norte S/. 1,140.00
Cesar Sur S/. 1,786.00
ERIO,RANGOSUMA)
PENDIENDO UNA CONDICIÓN O CRITERIO

ELECCIÓN DEL RANGO DE UNA COLUMNA QUE SE VA SUMAR

LOR QUE SE ENCUENTRA DENTRO DE LA COLUMNA

LO: "NORTE" O "SUR" O "ESTE"

=SUMIF(Zona,"NORTE",Monto)

RTE",C6:C10)

MIF(Sexo1,"F",Sueldo3)

de Sistemas
Ventas
Legal
=SUMIF(Área,E29,Sueldo3) Contabilidad
Sistemas
los empleados que su sueldo sea mayor a 1500
IF(Sueldo3,">1500",Sueldo3)

las áreas que terminen en la letra "S"

MIF(Area,"*S",Sueldo3)

=SUMIF(Zonas,"Norte",Ventas)
=SUMIF(Zonas,"Oeste",Ventas)
=SUMIF(Zonas,"Sur",Ventas)
=SUMIF(Zonas,"Este",Ventas)
=SUMIF(Vendedor,"Juan",Ventas)
=SUMIF(Vendedor,"Diana",Ventas)
=SUMIF(Vendedor,"Cesar",Ventas)
=SUMIF(Vendedor,"Ana",Ventas)

=SUMIF(Ventas,">1000",Ventas)
=SUMIF(Ventas,"<1000",Ventas)
 Promedio.SI(RANGO,CRITERIO,RANGOPROMEDIO)
Permite Hallar el Promedio de un rango de Datos, que cumplan con un solo Criterio.

Ejercicio: Tenemos la siguiente lista de datos que informa las ventas de los Vendedores en diferentes
Zonas, donde a su vez se supone que cada fila es una Venta.

ZONA1 VENDEDOR1 MONTOX

Este Doris S/. 288.00
Oeste Doris S/. 430.00 PROMEDIO DE VENTAS X ZONA
Norte Irene S/. 582.00 Norte 509.666666666667
Este Irene S/. 670.00 Oeste 443
Norte Irene S/. 687.00 Sur 528
Norte Martin S/. 518.00 Este 472.666666666667
Este Angel S/. 586.00
Sur Angel S/. 755.00
Este Irene S/. 372.00 PROMEDIO DE VTAS. X VENDEDOR
Este Doris S/. 333.00 Doris 360.2
Este Irene S/. 346.00 Irene 544.625
Norte Doris S/. 296.00 Martin 484.666666666667
Norte Irene S/. 665.00 Angel 547.333333333333
Norte Irene S/. 310.00
Sur Angel S/. 301.00 PROMEDIO DE VTAS. POR RANGO
Este Irene S/. 725.00 >500 648.5
Este Martin S/. 480.00 <500 369.636363636364
Este Doris S/. 454.00
Oeste Martin S/. 456.00
MEDIO)

=AVERAGEIF(Zona1,"NORTE",Montox)
=AVERAGEIF(Zona1,"Oeste",Montox)
=AVERAGEIF(Zona1,"Sur",Montox)
=AVERAGEIF(Zona1,"ESTE",Montox)

=AVERAGEIF(Vendedor1,"Doris",Montox)
=AVERAGEIF(Vendedor1,"Irene",Montox)
=AVERAGEIF(Vendedor1,"Martin",Montox)
=AVERAGEIF(Vendedor1,"Angel",Montox)

=AVERAGEIF(Montox,">500",Montox)
=AVERAGEIF(Montox,"<500",Montox)