FuncionesExcel

FUNCIONES DE EXCEL •

• • • •

Generalidades sobre las funciones: o concepto de argumento o argumentos literales (por valor) y por referencia o anidamiento de funciones Asistente de funciones Tipos de funciones: de texto, numéricas, de búsqueda,....

Tipos de funciones: matriciales y no matriciales (Referencias relativas y extensión)

FUNCIONES MATEMÁTICAS MAS COMUNES LOG(argumento) LN(argumento) EXP(argumento) RAIZ(argumento) POTENCIA(numero; potencia)

ABS(argumento) ENTERO(argumento) REDONDEAR(argumento) SUMA(rango o lista de argumentos) PRODUCTO(rango o lista de argumentos)

MANEJO DE MATRICES (funciones matriciales en sentido algebraico) Funciones de manejo algebraico de matrices.

MMULT(matriz1; matriz2) MINVERSA(matriz) MDETERM(matriz) TRANSPONER(matriz)

Ejemplo: Resolución de un sistema de ecuaciones lineales x=A-1b haciendo {=MMULT(MINVERSA(MatrizA);VectorB)} aplicada de forma matricial sobre el rango de salida Además muchas funciones tienen una versión matricial: Ejemplos: • Obtención de frecuencias relativas 1.- nombrar rango de frecuencias absolutas: Frecuencia 2.- nombrar celda de total: N 3.- marcar columna de frecuencias relativas e introducir la fórmula matricial {=Frecuencia/n} • Cálculo de la media con datos tabulados no agrupados 1.- nombrar rango de valores de la variable: X 2.- nombrar rango de frecuencias absolutas: Frecuencia 3.- nombrar celda de total: N 4.- marcar columna de F X e introducir la fórmula matricial {=Frecuencia*X/N}

Pedro Femia - Resumen de funciones de Excel

FUNCIONES DE TEXTO & (concatenación)

• Operador de Concatenación Ejemplos: - Generación de campos de texto - creación de referencias que servirán de argumento a la función indirecto() - creación de condiciones que servirán de argumento a la función contar.si() MAYUSC(texto) • Convierte el texto en mayúsculas MINUSC(texto)

•

Convierte el texto en minúsculas

IZQUIERDA(Texto; número _ caracteres) • Extrae la subcadena constituida por el número de caracteres indicados

comenzando por la izquierda dentro de la cadena de texto DERECHA(Texto; número _ caracteres) • Extrae la subcadena constituida por el número de caracteres indicados

comenzando por la derecha dentro de la cadena de texto EXTRAE(texto; posición_inicial; número _ caracteres) • Extrae la subcadena constituida por el número de caracteres indicados a partir

de la posición inicial dentro de la cadena de texto LARGO(texto)

•

Devuelve la longitud de la cadena

HALLAR(subcadena;cadena;pos_inicial)

• •

Devuelve la posición de la subcadena dentro de la cadena de texto a partir del carácter pos_inicial No distingue entre mayúsculas y minúsculas

ENCONTRAR(subcadena;cadena;pos_inicial)

• •

Devuelve la posición de la subcadena dentro de la cadena de texto a partir del carácter pos_inicial Distingue entre mayúsculas y minúsculas

Ejemplo de extracción: Las estadísticas de baloncesto en la web presentan columnas con valores del tipo 3/7 (3 canastas conseguidas en 7 intentos por ejemplo). Si el valor de entrada está por ejemplo en A1 la separación en dos variables se puede hacer con la función =IZQUIERDA(A1;ENCONTRAR("/";A1)-1)

h devuelve 3

=EXTRAE(D21;ENCONTRAR("/";D21)+1;LARGO(D21)-ENCONTRAR("/";D21)+1)

h devuelve 7

Las dos implementaciones son robustas para datos con mas dígitos y funcionarán bien para una entrada del tipo 10/13 o con mas dígitos

Caracter(valor) • Devuelve el carácter ASCII al cual corresponde el código valor Codigo(caracter) Pedro Femia - Resumen de funciones de Excel

•

Devuelve el código ASCII del carácter indicado

Ejemplo: creación de una lista alfabética FUNCIONES DE BUSQUEDA BUSCARV(búsqueda; tabla; columna_ a_devolver; Ordenado)

•

Búsqueda en la columna mas a la izquierda de la tabla. Devuelve el contenido de la fila correspondiente a la búsqueda encontrada en la columna indicada

BUSCARH(búsqueda; tabla; fila_ a_devolver; Ordenado) • Búsqueda en la fila superior de la tabla. Devuelve el contenido de la columna •

correspondiente a la búsqueda encontrada en la fila indicada En los dos casos, si no encuentra el valor devuelve el mas próximo. Si hay repeticiones devuelve el último

•

Ordenado = Verdadero / Falso

Ejemplo:

FUNCIONES LÓGICAS Las funciones VERDADERO() FALSO()

Devuelven los valores lógicos correspondientes. No llevan argumento y no es necesario el paréntesis.

Evaluación condicional SI(condicion; resultado_si_verdadero; resultado_si_falso)

•

La condición se elabora con operadores lógicos y relacionales: o Operadores lógicos: implementados como funciones lógicas

Pedro Femia - Resumen de funciones de Excel

Y(arg1; arg2;...)

O(arg1; arg2;...)

NO (argumento)

o Operadores relacionales: = Igual

< Menor

<= Menor o igual

<> distinto

> Mayor

>= Mayor o igual

Las siguientes funciones evalúan el tipo de contenido de una celda y devuelven VERDADERO/FALSO ESNUMERO(celda) Devuelve VERDADERO si la celda argumento contiene un número ESBLANCO(celda) Devuelve VERDADERO si la celda argumento esta vacía ESTEXTO(celda) Devuelve VERDADERO si la celda argumento contiene texto ESREF(argumento) Devuelve VERDADERO si el argumento es una referencia Exploración de errores: ESERROR(celda) • Devuelve VERDADERO/FALSO El error particular se puede explorar con la función TIPO.DE.ERROR(celda)

•

Devuelve un código de error o #N/A si en la celda aludida no hay error.

Códigos de error 1 2 3 4 5 6 7

Error #¿NULO! #¡DIV/0! #¡VALOR!

#¡REF! #¿NOMBRE? #¡NUM! #N/A

Pedro Femia - Resumen de funciones de Excel

FUNCIONES DE REFERENCIA INDIRECTO(dirección)

•

Hace referencia al contenido de la dirección indicada

DIRECCIÓN(Fila; Columna; Abs; A1; [hoja]) • Fila: Número de fila • Columna: Número de columna (A=1; B=2;...)

• Abs: 1=$A$1; 2=$A1; 3=A$1; 4=A1

• •

A1: indica la forma de la referencia A1 o A1B1 Hoja: opcional; nombre de la hoja

Ejemplo: [=DIRECCION(fila_inicial+num_datos-1;COLUMNA(INDIRECTO(Dir_col)); 4;1)] == K12

COLUMNA() [COLUMNA(INDIRECTO(C19))]==7 FILA()

Las funciones • •

COLUMNAS(Rango) FILAS(Rango)

devuelven el número de columnas y de filas que componen el rango indicado

Pedro Femia - Resumen de funciones de Excel

ESTADÍSTICA DESCRIPTIVA En los casos que siguen, Datos es el rango nombrado de celdas con los valores a analizar.

RECUENTO Y SUMA DE DATOS. Contar(Datos) Suma(Datos) TABULACIÓN DE DATOS. FUNCIONES DE FRECUENCIA Frecuencia (Datos; Grupos)

• •

Admite forma matricial pero cambia el resultado: - en forma matricial proporciona la frecuencia no acumulada - en forma no matricial da la frecuencia acumulada.

No considera valores de texto

Contar.Si(Datos; Criterio)

• •

Admite forma matricial Criterio es la referencia a una celda que contiene los caracteres identificativos de la clase. Son válidos los valores de texto.

Sumar.Si(Datos; Criterio)

• •

Admite forma matricial Criterio es la referencia a una celda que contiene los caracteres identificativos de la clase. Son válidos los valores de texto.

Suma(Si(Rango=Criterio;1;0))

• •

Es matricial (aunque se aplique sobre una sola celda) Permite elaborar condiciones mas complejas anidando funciones SI().

Ejemplo: {Suma(Si(A1:A100=1;Si(A1:A100=2;1;0)))}

MEDIDAS DE POSICIÓN

# Cf. Cálculo mediante expresiones algebraicas (datos tabulados o casos variables) con expresión matricial o no.

Funciones útiles en el cálculo de medidas de posición SumaProducto(Matriz1;Matriz2;...)

•

Devuelve el producto escalar de dos vectores (rangos): ∑ fi xi =SUMAPRODUCTO(rango_f; rango_x)

•

No admite forma matricial.

Suma.Cuadrados(Matriz1;Matriz2;...)

Medidas de tendencia central: Promedio(Datos)

Pedro Femia - Resumen de funciones de Excel

n

•

Media aritmética: x =

∑x i =1

i

n

Moda(Datos) Mediana(Datos)

Medidas de tendencia no central: Max(Datos); Min(Datos) • Extremos de la distribución.

Percentil(Datos; p)

•

Con 0^p^1 el percentil buscado. Devuelve el valor X correspondiente a dicho percentil p

Cuartil(Datos; q) • Equivalente a la función Percentil()

•

Los valores posibles para el segundo argumento son q=0 da el mínimo; q=1 da el 1er. cuartil; q=2 da la mediana; q=3 da el 3er cuartil) y q=4 da el máximo.

Rango.Percentil(Datos; X [; cifras significativas])

•

Devuelve qué percentil (p) representa el valor X en la tabla de datos

K.Esimo.Menor(Datos; k)

•

Devuelve el valor X que representa el menor k-ésimo

K.Esimo.Mayor(Datos; k)

•

Devuelve el valor X que representa el mayor k-ésimo

Jerarquia(k; datos; criterio)

•

Devuelve el valor X que representa el menor o mayor k-ésimo según el criterio: criterio=0 descendente; equivale a K.Esimo.Mayor() criterio=1 ascendente; equivale a K.Esimo.Menor()

MEDIDAS DE DISPERSIÓN Var(Datos)

•

Obtiene la varianza muestral 2 1 ⎛ k 1⎛ k ⎞ ⎞ 2 2 s = ⎜ ∑ fi xi − ⎜ ∑ f i xi ⎟ ⎟ n − 1 ⎜⎝ i =1 n ⎝ i =1 ⎠ ⎟⎠

DesvEst(Datos);

•

Desviación típica muestral

Las funciones VARP() y DESVESTP() son los equivalentes poblacionales

Pedro Femia - Resumen de funciones de Excel

CÁLCULO DE PROBABILIDADES CON EXCEL

NÚMEROS COMBINATORIOS Funciones elementales de cálculo combinatorio Fact(n)

•

Factorial de un número entero; Fact(n) h n!

COMBINAT(n;x)

•

⎛n⎞ n!

Número combinatorio Cn,x = ⎜ ⎟ = ⎝ x ⎠ x !(n − x)!

DISTRIBUCIONES DE PROBABILIDAD Distribuciones discretas Distribución Binomial

⎛n⎞ X → B(n, p) si P ( X = x ) = ⎜ ⎟ p x (1 − p) n − x ; x=0,1,...,n ⎝ x⎠ DISTR.BINOM(x; n; p; acumulado) • x = Número de éxitos en n pruebas de tipo Bernouilli de parámetro p (Be(p)) • n = número de pruebas • p= probabilidad de éxito en cada una de las pruebas • acumulado = Verdadero/Falso o 1/0. Si es Verdadero devuelve la función Binomial acumulada (función de distribución Binomial) x ⎛n⎞ P ( X ≤ x ) = ∑ ⎜ ⎟ p k (1 − p) n − k k =0 ⎝ k ⎠ • Momentos de la distribución: E ( X ) = np, V ( X ) = npq Distribución binomial negativa

⎛ r + x − 1⎞ r x X → BN ( x, r , p) si P ( X = x ) = ⎜ ⎟ p (1 − p) ; x=0,1,2,... ⎝ x ⎠ NEGBINOMDIST(x; r; p) • x = Nº de fallos antes del r-ésimo éxito en pruebas de tipo Be(p) • p = probabilidad de éxito • La distribución geométrica es el caso particular de la Binomial Negativa en que r=1, X → g ( p ) si P ( X = x ) = p(1 − p) x , modeliza por tanto la

•

probabilidad asociada al número de fallos antes del primer éxito en pruebas de tipo Be(p) Momentos de la distribución Binomial negativa: E ( X ) = rq p , V ( X ) = rq p 2

Pedro Femia - Resumen de funciones de Excel

Distribución de Poisson

X → P(λ ) si P( X = x) = POISSON(x; λ; acumulado)

• • •

e− λ λ x ; x=0,1,... x!

x = número de sucesos por unidad de tiempo, espacio, área... λ = número medio de sucesos del tipo anterior acumulado = Verdadero/Falso o 1/0. Si es Verdadero devuelve la función de distribución de Poisson x

P( X ≤ x) = e − λ ∑

λk

k!

Momentos de la distribución: E ( X ) = V ( X ) = λ k =0

•

Distribuciones continuas Distribución normal Distribución normal estándar N(0,1) DISTR.NORM.ESTAND(zα).

• •

Dada la abcisa zα devuelve P(z ≤ zα) Ej. Distr.Norm.Estand(1.96) h 0.975

DISTR.NORM.ESTAND.INV(1−α) • Dada la probabilidad 1−α devuelve la abcisa zα tal que P(z ≤ zα)=1−α • Ej: Distr.Norm.Estand.Inv(0.975) h1.96

Distribución normal N(μ, σ) DISTR.NORM(x; μ; σ; acumulada) • Dada la abcisa x devuelve P(X ≤ x) para XhN(μ,s) • •

acumulada = Verdadero/Falso o 1/0. Si es Verdadero devuelve el valor en x de la función de distribución N(μ, σ) Ej. Distr.Norm(1.96; 0; 1; Falso) h 0.975

DISTR.NORM.INV(1−α; μ; σ) • Funciona igual que en el caso de la función Distr.Norm.Estand.Inv() • Ej: Distr.Norm.Inv(0.975; 0;1) h 1.96

Pedro Femia - Resumen de funciones de Excel

Distribuciones en el muestreo Distribución t-Student DISTR.T(x; gl; colas) • Devuelve el valor de α o de α/2, según sea a 1 o

•

2 colas, para la abcisa x y grados de libertad gl dados Ej. Distr.T(1.96;1000; 2) h 0.05

DISTR.T.INV(α; gl) • Devuelve la abcisa x asociada a la probabilidad •

α a dos colas para los gl indicados Ej: Distr.T.Inv(0.05;1000) h 1.96

Distribución F-Snedecor DISTR.F(Fα; gl1; gl2) • Da la probabilidad α asociada a la abcisa Fα para •

los grados de libertad gl1 y gl2 Ej: Distr.F(3.841; 1; 10000) h 0.05

DISTR.F.INV(α; gl1; gl2)

•

•

Devuelve la abcisa Fα que deja una cola de probabilidad α para los grados de libertad indicados.

Ej: Distr.F.Inv(0.05; 1; 10000) h 3.841

Distribución χ 2 DISTR.CHI(x,gl) • Devuelve la probabilidad α asociada a la abcisa •

x para los grados de libertad gl Ej: Distr.Chi(3.841;1) h 0.05

PRUEBA.CHI.INV(α; gl)

• •

Devuelve la abcisa χ 2 que deja una cola de probabilidad α Ej: Prueba.Chi.Inv(0.05;1) h 3.841

Pedro Femia - Resumen de funciones de Excel

Construcción de intervalos de confianza con Excel Intervalo de confianza para la media de una normal

μ ∈ x ± tα ;n −1 •

Precisión de la estimación δ = tα ;n −1

•

s⎞ ⎛t Tamaño de muestra n ≥ ⎜ α ;n −1 ⎟ ⎝ δ ⎠

s n

s n

2

siendo tα;g = Distr.Tinv(1−α/2; n−1).

Intervalo de confianza para la media de una variable arbitraria

Para variables no normales con n>60 la expresión es válida sustituyendo la tα por el valor zα equivalente de la N(0,1): s μ ∈ x ± zα n Si la v.a. aproximada es discreta debe añadirse la c.p.c. ±1/ 2n : μ ∈ x ± zα

s 1 ± n 2n

Intervalo de confianza para una proporción.

a) Si x 5 y (n-x) 5: ⎛ z2 z 2 ( x ± 0.5) ( (n − x) ∓ 0.5 ) ⎞ ⎜ ( x ± 0.5) + α ± zα α + ⎟ ⎜ ⎟ 2 4 n ⎝ ⎠ b) Si x 20 y (n-x) 20: p∈

1 n + zα2

⎛ pˆ (1 − pˆ ) 1 ⎞ + ⎟⎟ p ∈ pˆ ± ⎜⎜ zα n 2n ⎠ ⎝ • Precisión: puede considerarse δ=(LS-LI)/2 con LI y LS los límites del IC obtenido z 2 × 0.25 • Tamaño de muestra: n ≥ α 2 δ siendo zα=Distr.Norm.Estand.Inv(1−α/2); o bien el argumento es [(1−α) + 1−((1−α)/2) ] si se da como valor de entrada la confianza (1−α) Intervalo de confianza para el parámetro de Poisson

a) Método exacto. El IC a nivel α para λ viene dado por los valores ( λ1 , λ 2 ) , en

donde

1 2 1 2 χ1−α / 2; 2 x ; λ 2 = χ i ∑ 2n 2n α / 2; 2+ 2 ∑ xi con χ12−α / 2; gl =Prueba.Chi.Inv(1−(α/2); gl) y χ α2 / 2; gl ==Prueba.Chi.Inv(α/2; gl) λ1 =

Pedro Femia - Resumen de funciones de Excel

b) Método aproximado a través de la Normal. Para x ≥ 15 (con x una observación de la P(λ)) 2

1⎛ z ⎞ λ ∈ ⎜ α ± ∑ xi + 0.5 ± 0.5 ⎟ n⎝ 2 ⎠ siendo zα el percentil 1−α/2 de la Normal estándar.

• Precisión: puede considerarse δ=(λ2−λ1)/2 • Tamaño de muestra: para conseguir una estimación tal que x − λ ≤ d se tiene zα2 λ en donde el valor de λ utilizado suele ser el máximo del obtenido en d2 una muestra piloto. n=

Pedro Femia - Resumen de funciones de Excel