Manejo de ficheros INM con Excel
PRECIPITACIONES TOTALES MENSUALES
SERIE 1971-2000
AÑO
ENE
1971
29.8
1972
75.1
1973
12.9
1974
ip
1975
19.5
1976
5.1
1977
95.2
1978
20.8
1979
199.5
1980
3.9
1981
26.3
Para precipitaciones medias
mensuales y máximas en 24 horas,
se trabajará con los doce meses.
1982
20.6
1983
0.0
1984
0.6
1985
16.9
1986
17.8
En percentiles se hará lo mismo
trece veces (12 meses y 1 año)
1987
57.4
1988
79.1
1989
4.0
1990
8.9
1991
54.6
1992
36.4
1993
0.0
1994
2.0
1995
2.6
1996
81.1
1997
135.6
1998
31.7
1999
22.4
2000
1.6
Realizar cálculos.
Vamos a realizar como ejemplo los
cálculos de precipitación mensual
media. Cogemos el mes de enero de
ejemplo.
Manejo de ficheros INM con Excel
Realizar cálculos.
1) Observamos si los datos son números. Ojo con formato celda y si
van puntos o comas para separar decimales.
2) observamos en la ficha o fichero de formato si la precipitación viene en
décimas de mm o mm. Si viene en décimas de mm (hay que dividir por 10 los
valores que obtengamos para darlos en mm).
3) si hay valores especiales: p.ej. de ‘-3’ precipitación inapreciable y ‘-1’
falta de dato; o ip (inapreciable). Para que Excel no emplee estos valores en
los cálculos (e interfieran la media) sustituiremos estos valores en las
columnas de precipitación (también en precipitación máxima en 24 horas, si
los hubiese, cuando trabajáramos con ellos). Para ello podemos ejecutar
‘Edición >> Reemplazar >> Por columnas (paso a paso)’ para localizar y
reemplazar los valores. El valor ‘-3’ lo reemplazaremos por 0 (ó 0.05 ya que
inapreciable es < 0.1 mm) y el ‘-1’ por ‘blanco’ (= casilla vacía).
** Hay que tener mucho cuidado de no reemplazar valores válidos
en otras columnas **
Manejo de ficheros INM con Excel
Realizar cálculos. MEDIA.
Una vez reemplazados los valores especiales (si procede) y reordenado
por meses (si procede) pasamos a calcular la media.
Se recomienda que aparezcan ordenados por años. Y así mes a mes se
calcula la media.
Se haría doce veces el valor anual sería el sumatorio de los doce
mensuales.
Manejo de ficheros INM con Excel
Cálculo de percentiles.
Ordeno la hoja de datos por año y mes (trece
veces). Selecciono en la hoja las precipitaciones
que vayamos a usar para calcular los percentiles
(se hará columna a columna).
Selecciono la columna del mes con el que voy a
trabajar, y doy a ordenar datos
AÑO
ENE
1971
29,80
1972
75,10
1973
12,90
1974
ip
1975
19,50
1976
5,10
1977
95,20
1978
20,80
1979
199,50
1980
3,90
1981
26,30
1982
20,60
1983
0,00
1984
0,60
1985
16,90
1986
17,80
1987
57,40
1988
79,10
1989
4,00
1990
8,90
1991
54,60
1992
36,40
1993
0,00
1994
2,00
1995
2,60
1996
81,10
1997
135,60
1998
31,70
1999
22,40
2000
1,60
Aparecerán (se hace trece veces mes a
mes) ordenados los valores del menor
al mayor.
Una vez ordenados se puede rellenar
sin problemas el cuadro de los quintiles.
Manejo de ficheros INM con Excel
Cálculo de percentiles.
N
o
v
a
l
e
Los datos deberán aparecer ordenados
del menor al mayor mes a mes y para el
año. Los años de la primera columna no
vale.
Ojo si hay fórmulas deberá eliminarlas
para evitar errores. Para trabajar
necesito estos datos con los meses-año
en columnas y como valores reales, no
como valores resultado de fórmulas
(suelen dar problemas).
Para ello, abro una nueva hoja de
cálculo ‘Insertar >> Hoja de cálculo’ y
pulso ‘Edición >> Pegado especial’. En la
ventana de ‘Pegado especial’ pulso
‘Pegar >> valores’
Manejo de ficheros INM con Excel
Cálculo de percentiles.
Se pueden usar las tablas dadas
en los cuadros tipo. O hacer
directamente.
Fórmula
Valor
La tabla anterior la puedo manipular
separando grupos de años (Con 30
años, de seis en seis si pretendo
calcular los quintiles, percentil 20).
Puedo calcular el percentil hallando la
media entre los dos valores que
limitan el percentil incluyendo la
fórmula ‘=promedio(Q7;Q9)’.
O puedo calcularlo duplicando una fila:
por ejemplo la fila 6 si calculara el
primer quintil sobre una serie de 28
años (1*28/5 = 5.6 >> 6ª fila).
Si no usa los cuadros.
Puede utilizar la función de excel estadística
PERCENTIL(RANGO DE VALORES; PERCENTIL)
RANGO VALORES …. B3..b32
PERCENTIL… el que sea 0,2 ó 0,4 ó 0,6 ó 0,8
El percentil 20 para enero sería según el cálculo del
excel 2,48, si lo hubiéramos calculado como media del
inferior o superior nos daría 2,3.
Es más correcto el 2,48 pues se ha hecho asignando las
probabilidades correctas a cada valor (correspondiente
a su orden), como vimos en el material de lectura
TAMBIÉN LO PUEDE CALCULAR ASIGNANDO
PROBABILIDADES A CADA VALOR ORDENADO Y
CALCULANDO POR INTERPOLACION LOS PERCENTILES
CORRESPONDIENTES
METODO EMPIRICO
(SE EXPLICA A CONTINUACION)
DISTRIBUCION EMPÍRICA
Los percentiles empíricos se calculan a partir de la función de
distribución empírica definida por los valores de la serie con la que se
trabaja ordenada desde el valor menor al mayor, y asignando a cada
valor ordenado su probabilidad calculada según la expresión:
Prob (xi) = 100·[i/(N +1 )]
(en %)
Donde ”i” representa el número de orden que ocupa el valor “x” en la
serie de datos ordenada en orden creciente y “N” el número total de
datos. La probabilidad correspondiente al 20, 40, 50, 60 ó 80 por ciento
se obtienen por interpolación lineal, considerando las probabilidades
asignadas a cada dato ordenado.
Por ejemplo:
Se pide calcular los valores de los percentiles 20 y 40 mediante la función de
distribución empírica, de la siguiente serie de valores:
102.2 96.3
377.7 119.9
221.1 32
153.8 199
261.9 58.7
160
209.8
270
60.4
171.9 142
138.3 83.5
172.1 148.5
13.5
289.4 183.6 269.4
18.1
299.9 197.9
118
110.5 300.7
Solución.
Se deben ordenar los datos de precipitación en orden creciente, y asignar a
cada valor de precipitación su probabilidad empírica en función del orden de
situación del valor y del número de datos.
Así para los dos primeros valores y para los dos últimos tendremos:
Nº orden Precipitación
1º
2º
...
29º
30º
Probabilidad
13.5 mm Prob (13.5) = i/(N +1) = 1/(30+1) = 0.03226
18.1 mm Prob (18.1) = i/(N +1) = 2/(30+1) = 0.06452
300,7 mm Prob (300,7) = i/(N +1) = 29/(30+1) = 0.9354
377,7 mm Prob (377,7) = i/(N +1) = 30/(30+1) = 0.9677
(3.226 %)
(6.452 %)
(93,55 %)
(96,77 %)
Los percentiles se calculan por interpolación lineal.
Así: El percentil 20 se obtiene por interpolación sabiendo que será un
volumen de precipitación entre el valor que está en la posición sexta
(19,35 %) y el valor que está en la posición séptima (22,58 %)
P19.35 83.5
P22,58 96.3
P20
86.07
Se deben ordenar los datos de precipitación en orden creciente, y asignar a
cada valor de precipitación su probabilidad empírica en función del orden de
situación del valor y del número de datos.
Nº orden
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Prob %
3.226
6.452
9.677
12.9
16.13
19.35
22.58
25.81
29.03
32.26
35.48
38.71
41.94
45.16
48.39
51.61
54.84
58.06
61.29
64.52
67.74
70.97
74.19
77.42
80.65
83.87
87.1
90.32
93.55
Prec (mm)
13.5
18.1
32
58.7
60.4
83.5
96.3
102.2
110.5
118
119.9
138.3
142
148.5
153.8
160
171.9
172.1
183.6
197.9
199
209.8
221.1
261.9
269.4
270
289.4
299.9
300.7
Los percentiles se calculan por interpolación lineal.
Así: El percentil 20 se obtiene por interpolación sabiendo que será un
volumen de precipitación entre el valor que está en la posición sexta
(19,35 %) y el valor que está en la posición séptima (22,58 %)
P19.35 83.5
P22,58 96.3
P20
86.07
Descargar

Click here to get the file