Rango variable en gráficos. Función DesRef.

El gráfico del ejemplo tiene tres series. Mediante unas gráficas del tipo XY representa dos polígonos inscritos en un círculo, donde uno de los polígonos se puede desplazar con respecto al otro, mediante una barra de desplazamiento (barra vertical). El número de lados del polígono y el radio del círculo se pueden variar mediante una barra de desplazamiento. Tenemos, por tanto,  un gráfico con una serie con rango de datos variable, con un número de líneas y columnas que dependen del número de lados del polígono. Esta variación del rango de la serie la  controlamos mediante  función DESREF y el uso de nombres. Definimos un nombre con la función:

DESREF(ref;filas;columnas;alto;ancho)

Donde:

Ref: Celda de referencia o inicio del rango dinámico.

Pol1X=DESREF(Datos!$I$4;0;0;Datos!$F$1+1;1)

Pol1Y=DESREF(Datos!$J$4;0;0;Datos!$F$1+1;1)

Datos!$I$4 y Datos!$J$4 inicio de las series de datos (X e Y)

Filas: Es en número de filas que a partir de la celda de referencia ($I$4 o $J$4) se desplaza el rango. Puede ser un valor positivo o negativo.

Columnas : Es en número de columnas que a partir de la celda de referencia ($I$4 o $J$4) se desplaza el rango. Puede ser un valor positivo o negativo.

Alto: Número de filas que tiene el rango. Solo valores positivos.

Ancho: Número de columnas que tiene el rango. Solo valores positivos.

La gráfica que representa el círculo en realidad representa un polígono de 360 lados. Es un falso círculo.

=SERIES(Datos!$G$3;Datos!$F$4:$F$364;Datos!$G$4:$G$364;1)

Definida de una manera básica, sin nombres.

La serie que representa al primer polígono es la que incluye nombres y por tanto la función DESREF:

=SERIES(;Poligonos2.xls!Pol1X;Poligonos2.xls!Pol1Y;2)

DESREF(Datos!$J$4;0;0;Datos!$F$1+1;1)

Donde $J$4 es el inicio del rango variable. No desplazamos ni filas ni columnas (0;0) y necesitamos $F$1+1 líneas y una columna, donde $F$1= número de lados del polígono.

Para dibujar un polígono de N lados necesitamos definir N+1 puntos, que se corresponden con los N vértices del polígono y el punto correspondiente a 360º, que coincide con el primer vértice.

Las referencias absolutas podríamos sustituirlas por un nombre.

Otras funciones utilizadas:

Seno()

Cos()

Radianes()

Grados()

PI()

ASENO()

ACOS()

Potencia()

Utilización de nombres.

          Nl : Número de líneas

NT: Número de tramos del falso circulo. En este caso es constante e igual a 360

Rad: Radio del circulo.

Pol1X =DESREF(Datos!$I$4;0;0;Datos!$F$1+1;1)

Pol1Y =DESREF(Datos!$J$4;0;0;Datos!$F$1+1;1)

Algunos cálculos son reiterativos, o innecesarios, tienen como objetivo conocer y manejar las correspondientes funciones  o comparar el resultado de dos métodos distintos de calcular un valor. En la columna B se realiza una división de los 360º de un círculo para pasar ese valor a radianes en la columna D. Es una manera de trabajar con la función RADIANES, para conocer el paso de GRADOS a Radianes.



Mi internet solo puede acceder a Google.

Además de tener chafada la columna internet ahora me vacila, con windows solo me permite acceder a Google y derivados. La conexión a internet funciona correctamente con otros PC y en este PC también va bien si arranco con Linux. El correo me funciona, pero internet no.

Solucionado:

No se ha arreglado solo, lo he arreglado yo, pero no se por qué. Me molestan este tipo de cosas.
Antes de arreglarlo he intentado:

Cambiar la página de inicio.
Los ping a las distintas direcciones (ping www.blogspot.com) funcionaban. Esto debe significar que la conexión está bien y que no es de los DNS. Además permite conocer la I.P.
Eliminé los archivos temporales de internet.Nada.
Desactive firewall y reinicié. Nada.
No había proxy.
La solución se produjo al cambiar de navegador. Pruebo primero con google chrome y todas las conexiones (o links)  funcionan. Ni con opera ni con firefox funcionan. I.E. sigue direccionando solamente google y alrededores. Pruebo con la dirección I.P. de una página web y entro. Pruebo con otra y entro. Pruebo con www.tal.cual y entro. Solucionado y mosquedado, con los unos y los otros.

Delimitadores o separadores de campo y campos de texto libre.

A veces, profesionalmente, me encontraba con ficheros, normalmente provenientes de las BB.DD. de la informática corporativa, que llegaban a mi o que salían de mi que contenían campos de texto libre, campos normalmente de comentarios, en los que los usuarios escribían libremente un texto. A veces la única limitación de ese texto era el número de caracteres que se podían poner, y a veces ni eso.

Si exportamos un fichero, con un campo de comentarios (texto libre), a un fichero de texto separado con delimitadores podemos encontrarnos que dentro del  campo de comentarios hay un delimitador (que por otra parte son caracteres muy comunes, espacios, comas, comillas, punto y coma, tabuladores, etc ).Si posteriormente importamos a excel el fichero de texto nos podemos encontrar que algún campo de comentarios crea campos no deseados, al contener delimitadores.  Es decir, desplaza datos de la columna que le corresponde.

Si recibimos un fichero en esta situación nos toca depurarlos a "mano". Si recibimos periódicamente ese tipo de ficheros debemos hablar con nuestro suministrador para que corrija ese problema y si somos nosotros los que creamos el fichero, somos nosotros los que debemos corregirlo.

Normalmente en los ficheros de este tipo (texto con delimitadores) que se tratan con Excel, los  campos de comentarios no se utilizan. Además no suele ser crítico sustituir o eliminar (dentro del texto del comentario) un carácter por otro, o sustituir un carácter por una combinación de caracteres poco probable (si queremos después rehacer el carácter original), y/o situar los campos de comentarios al final de la  línea, con lo que los desplazamientos de columna son menos rompedores.

Delimitador de texto COMILLAS , caracteres de control o caracteres no visibles.

En la entrada anterior, en alguna de las líneas, hay que concatenar el carácter "comillas", que es el carácter que se normalmente como delimitador de texto (de hecho hay que incluirlo porque hay que delimitar un texto). Si incluimos unas comillas dentro de una función excel asume que lo que va a continuación es un texto fijo y si no las incluimos, asume que es otra función o una variable. Las comillas, como texto, las admite mal, hay que incluirlas mediante la función caracter(), código ASCII 34.

Algunos caracteres de control, como los ASCII 10 y 13 (y en general los menores a 32) tampoco se pueden incluir a "mano", también hay que incluirlos mediante la función CARACTER().  Esta función se puede utilizar con cualquier código ASCII, del cero al 255.

Ficheros Gpx y ficheros PLT. Conversión de Plt a GPX.

Nueva versión de esta conversión:

Para los que además de MapSource instalado tienen Oziexplorer  esta conversión se realiza directamente desde cualquiera de las versiones de Oziexplorer que admitan la importación de ficheros GPX. Para convertir de camino (track) a waypoints o a ruta (en GPX o en PLT) yo no he encontrado nada, me lo tuve que hacer, programando, pero para aquellos interesados en Excel y GPS este puede ser un ejercicio interesante.

El trabajo de conversión que realizo a continuación en términos EXCEL es correcta independientemente del DATUM  que utilice el fichero PLT.  Si el DATUM que utiliza el fichero PLT es distinto de WGS84  a la hora de utilizar el GPS se produce un error que puede ser de cientos de metros.

El fichero Plt es un fichero de texto, editable con cualquier editor de textos. Es un formato propio de Oziexplorer. El fichero GPX también es un fichero de texto editable con cualquier editor de texto. Tanto uno como otro constan, basicamente, de una cabecera y de la localización de los puntos, coordenadas, altura, fecha y hora del momento en que se tomo el punto.

Fichero PLT:

OziExplorer Track Point File Version 2.1
WGS 84
Altitude is in Feet
Reserved 3
0,2,16711680,PuertoCanenciaValdemancoReal.gpx Convertido a PLT                 ,1,0,0,0
1920


40.8711260  , -3.7647177  ,0, 5794 , 40531.3771180556 , 2010-12-19 , 09:03:03
40.8710805  , -3.7647516  ,0, 5782 , 40531.3771759259 , 2010-12-19 , 09:03:08
40.8710777  , -3.7647554  ,0, 5768 , 40531.3771875 , 2010-12-19 , 09:03:09
40.8710748  , -3.7647584  ,0, 5754 , 40531.3771990741 , 2010-12-19 , 09:03:10
40.8710575  , -3.7647747  ,0, 5744 , 40531.3772106481 , 2010-12-19 , 09:03:11

En donde los dos primeros campos son la latitud y la longitud. El tercero (0) se utiliza si se divide el track, el cuarto campo es la altitud en pies y los otros campos son la fecha y la hora.

Fichero GPX

Formato punto del camino en GPX`
      <trkpt lat="40.8282234" lon="-3.8302293">
        <ele>1643.0302734</ele>
        <time>2011-10-09T07:57:34Z</time>
      </trkpt>


Nueva versión de esta conversión:

Es conveniente tener instalado y ver los resultados de la conversión con OpenOffice.

Funciones utilizadas:

Entero()

Espacios()

Texto

Uso de nombres.

De texto a datos.

El primer paso es pasar los datos del fichero de texto a la hoja excel, solo los datos, sin cabecera. En este caso lo hice con un copia-pega, aunque quizas debí importar esos datos directamente desde el fichero de texto. Una vez todos los datos en la columna A los pasé, mediante la opción de texto a datos, cada uno a su columna. Además los pase todos ellos como campo tipo texto con el fin de hacer las cosas paso a paso.

Las coordenadas, campos 1 y 2 (columnas A y B) se van a utilizar tal cual están, números separados por punto. La altura se tiene que pasar de pies a metros (aprox. pies*o,28). Esta multiplicación es una aproximación por lo que en el resultado podemos redondear, prescindir de los decimales. El primer campo fecha (considerado texto), números separados por un punto, hay que pasarlo a numérico (en notación española, en mi caso). Utilizo para ello la opción de texto en columnas, primero selecciona la columna a convertir,Datos->Texto en columnas, como solo es una columna, Ancho fijo, avanzamos hasta el paso tres y pulsamos el botón Avanzadas. Cambiamos para el separador decimal la coma por el punto  y para el separador de miles  el punto por la coma. Damos destino a ese cambio (el resultado puede sustituir a los datos originales) y ya tenemos el dato convertido a numérico. Calculo la altitud en metros, redondeo y coloco las etiquetas correspondientes (k) con :

="<ele>" & ENTERO(I2*PM) & "</ele>"

A la fecha con formato GPX y sus correspondientes etiquetas le doy forma (L) con la función texto y el formato de fecha correspondiente:

 ="<time>" & TEXTO(J2;"aaaa-mm-ddThh:mm:ssZ") & "</time>"

Las coordenadas y sus etiquetas son la cocatenación de valores de texto.

El punto completo de GPX incluye saltos de línea. Los saltos de línea son los códigos ASCII 13 (CR) y 10 (LF), retorno de carro y avance de línea de las ya muy antiguas (casi olvidadas) máquinas de escribir. Según el editor de textos que se utilice se puede notar la diferencia, o no. Se pueden encontrar ficheros de texto con ambos caracteres al final de la línea , o con uno solo de ellos. Realmente no suele dar problemas, salvo la aparición de líneas en blanco.

Para utilizar comodamente los caracteres  que necesito incluir mediante la funcion CARACTER(), les doy nombre (insertar->Nombre->definir):

CR =CARACTER(13)
LF=CARACTER(10)
CRLF=Cr & LF
COM (de comillas) =CARACTER(34)
PM (de pies a metros) =0,28
En las columnas con los puntos completos los utilizo:

=$M2 & CRLF& $K2 & CRLF& $L2 & CRLF& "</trkpt>"

Para poder utilizar estos datos en un fichero GPX editamos un fichero GPX con cabera y cola, copiamos (sin la cabecera o título de la columna excel)  y pegamos los puntos en el sitio correspodiente del fichero GPX y ya (si lo hacemos bien)  podemos utilizarlo, por ejemplo, en MapSource. Esta copia se hace mejor abriendo el fichero XLS con Open Ofice y los ficheros de texto con WordPad.

Ficheros GPX preparados.

Irradiación y factor K. Autocritica.

En la entrada anterior mencioné que era mejorable el traslado de los valores de K, no tenía que arrastrar la latitud a todas las líneas, y que el código provincial podía referenciarlo mediante un nombre en vez de una dirección.
Hoja TablasK
Funciones:
Decimal
Indirecto
Indice
Utilización de nombres.

 Incluimos el nombre mediante el submenú Insertar->Nombre->Definir, Cp y como dirección =MaxIrrad!$A$3. A partir de definir el nombre referenciamos este código como Cp ( INDICE(Irrad;CP;F$1), =INDICE(Irrad;CP;C3-1), etc).

Las tablas del factor K tienen todas ellas las mismas dimensiones, 19 filas y de la columna D a la P. Asegurandonos de que las líneas de separación entre tablas es la misma, podemos hacer un algoritmo para localizar la tabla dentro de la página, según la latitud.

En la hoja TablaK, celdas C1 a G1, podemos ver el algoritmo utilizado:

En C1 "factork!$d$" & $F$1 & ":$p$" & $G$1. Da una cadena alfanumérica que es la dirección de la matriz donde están los valores de K para una latitud determinada. Este valor lo utiliza la variable TablasK, utilizando la función INDIRECTO(TablaK!$C$1)

En D1 con  DECIMAL(LatPob;"0") hacemos la conversión que en la hoja MaxIrrad hice con TEXTO(LatPob;"0")
La primera latitud (28) se corresponde con un valor 0 en el algoritmo de cálculo de la línea inicial. Si las latitudes fuesen continuas este valor valdría para todas ellas, pero como las latitudes no son seguidas (28 y 29 para Canarias y entre 34 y 45 para el resto) en E1 utilizo  SI(D1<30;28;32). Viendo las tablas 28 es el primer valor y se corresponde con un cero, 29 segundo valor se corresponde con un uno y 34 tercer valor se corresponde con un dos (34-32).   

En F1 calculo la línea donde empieza la tabla de la latitud de D1 22*($D$1-$E$1)+8+2 (8 primeras líneas dedicadas a la cabecera de la hoja y 2 las líneas que no utilizamos de las tablas) 
En G1 $F$1+18 obtengo la última línea util de la tabla.
Para trasladar valores utilizo =INDICE(TablasK;$A4;C$3)

Irradiación y Factor K

La irradiación es la energía aportada por el sol sobre una superficie horizontal. El factor K es una constante que multiplica esa energía en función de la la inclinación del plano y de la latitud. Tanto la irradiación  como el factor K vienen tabulados. En este caso la tabla de irradiación se refiere a la irradiación media mensual de las provincias españolas y las tablas del factor k incluyen las latitudes 28º y 29º (Canarias) y de la 35º a 45º (península y Baleares).

El trabajo consiste en conocer para una población determinada, seleccionada mediante un desplegable para seleccionar la provincia y otro para seleccionar la población, la inclinación con la que debemos colocar un panel solar para optimizar la instalación.

La selección de población es la misma que la explicada en las entradas anteriores dedicadas a las zonas climáticas de España.

Funciones Excel utilizadas:

Texto()

Indice()

Max()

Coincidir()

Suma()

Si()

Utilización de nombres.

Algunas hojas, algunas columnas y algunas filas están ocultas pero no protegidas.

Las tablas originales estaban ordenadas por orden alfabético.  Siguiendo el criterio de que es mucho mejor ordenar por el código postal, como primera medida así lo hice. Incluí el C.P. y ordene las tablas según el C.P. para aquellas tablas que lo necesitaban.

En las tablas del factor K arrastré la latitud para todas las líneas (columna B de la hoja factor K) mediante la función:

 =SI(C8="";B7;C8)

La latitud aparece en este caso como parte de las cabeceras, en la columna C (pero solo en la cabecera).El algoritmo de la fórmula se interpreta como “si en Cx no hay nada toma el valor de la celda anterior  de la columna B y si tiene algo toma el valor de Cx”.  Como en primer lugar pensé en localizar, mediante la función Coincidir, la concatenación inclinación-latitud, necesitaba que todas las líneas tuvieran la latitud correspondiente. Esta manera de hacerlo es mejorable (de hecho en la hoja tablaK lo mejoro) para este caso, pero es muy útil si queremos, por ejemplo, pasar los datos de una hoja excel (como la que tenemos, con datos solo en la cabecera) a una tabla debase de datos.

Concatené la latitud y la inclinación en la columna A.

Como en la tabla K la latitud viene en grados enteros redondeo la latitud de la población mediante la función Texto (TEXTO(B7;"0") & "º" ), en la hoja MaxIrrad celda D7. Esta función, así utilizada, redondea, si los decimales son menores que 0,5 al entero anterior y si son mayor o igual al entero superior. En esta misma hoja, en columna A (de A4 a A21) utilizo la función Coincidir ( =COINCIDIR($E4 & $D$7;Incl;0) ) , en donde la concatenación $E4 y $D$7 es inclinación-latitud e Incl es el nombre de la columna A de FactorK. En A3 está el código provincial.

Al buscar la concatenación inclinación-latitud encuentro la línea de la hoja factorK en donde están los datos que necesito para esa inclinación.

La optimización se supone que se produce cuando se obtiene el máximo de energía (sumatorio de irradiación*factorK), por lo que necesitamos obtener los datos de irradiación de la provincia seleccionada y multiplicarlos por el factor k (para cada mes y cada inclinación). Los valores de irradiación los obtengo con la función:
 INDICE(Irrad;$A$3;I$1), en donde Irrad es el nombre de =Irradiación_H!$E$7:$Q$58, $a$3 es el código provincial (debí ponerle nombre) e I$1 es el número de mes. los datos sobre el factor k los obtengo mediante la formula =INDICE(TVal;$A4;I$1), donde Tval es el nombre de =FactorK!$E:$S (area de datos de factor K) y $A4 es el número de la línea donde están los datos.
En la columna obtengo la suma de la energía anual, en las columnas S y T la energía de invierno y de verano.
Mediante la función Max encuentro el máximo para cada mes o periodo de tiempo. A su vez busco ese máximo, con la función Coincidir en su columna. Con el número de línea obtenido, mediante la función Indice, coloco la primera inclinación, en grados, que nos da ese valor máximo. La formula es (para la columna R, anual):

=INDICE(Inclinaciones;COINCIDIR(R22;R4:R21;0))

El resalte, de color azul, de los máximos lo hago utilizando el formato condicional, para cada columna (valor de la celda = máximo de la columna)

Algoritmo de ordenación de cuatro elementos, con aplicación práctica.

 Algoritmo de ordenación de cuatro elementos con aplicación práctica con los resultados del grupo de España en el último mundial de fútbol.

 El orden, en este caso, es de mayor a menor, es decir el primero es el que mas puntos tiene y el último el que menos, como siempre se establece una clasificación de este tipo.

 Para establecer el orden final hago, en un primer paso, comparaciones entre cada dos elementos de la lista, primero con segundo, tercero con cuarto, y si la lista tuviese mas de cuatro elementos, quinto con sexto, etc.. 

  En un segundo paso comparo segundo con tercero, cuarto con quinto, etc. En el ejemplo, como solo tenemos cuatro elementos cierro las comparaciones comparando cuarto con primero, o último con primero, cosa que no podría hacer si el número de elementos de la lista fuese impar . El tercer paso es igual al primer paso, primero con segundo,etc.

 La pregunta es ¿ Cuantos pasos hay que dar para ordenar n elementos?

 En principio parece que el caso mas desfavorable es cuando en la lista original el elemento que al final del ordenamiento estará primero, está el último. Este elemento ascenderá al primer puesto, en nuestro caso, (n=4, número par) en tres pasos (de cuarto a tercero, de tercero a segundo y de segundo a primero), pero no es el caso mas desfavorable. El caso mas desfavorable es aquel caso en que el primero está el último y el segundo está penúltimo. En este caso, al terminar el primer paso, el que terminará segundo, está el último. Este elemento necesita N-2 pasos mas  para ir a su sitio, el segundo. Si no hacemos, o no podemos hacer, la comparación entre último y primero, cosa que sucede si N es impar, este elemento sigue necesitando N-2 pasos mas para llegar a su sitio, pero no participa en el segundo paso, con lo que para que suba a su sitio necesita N-1 pasos a partir del segundo paso. Es decir necesitamos N pasos si N es impar y N-1 pasos si N es par, para ordenar, con este algoritmo, N elementos.

 Si N fuese impar y empezamos comparando primero y segundo, tercero y cuarto, el quinto elemento no participaría en el primer paso. Si este elemento debe llegar al primer lugar, necesita N-1 pasos mas para hacerlo. Como no ha participado en el primer paso, necesitamos N pasos para asegurarnos de que el ordenamiento se hace correctamente. Si empezamos comparando segundo con tercero, cuarto con quinto, etc el que no participa en el primer paso es el primer elemento, que si es el que ira al último lugar necesita N-1 pasos mas para hacerlo, en total N pasos.

 En la hoja pruebas del libro excel puede verse este proceso para para cuatro elementos.

 El ejemplo de uso de este algoritmo, su puesta en práctica, es, como ya dije, el grupo de España del último mundial de futbol. Antes de empezar vamos a establecer los requisitos de la aplicacion:

• Todo partido, de la lista de partidos a celebrar, presenta un 0-0 antes de comenzar.
• Si un partido no ha comenzado, pese a estar como 0-0, no suma puntos a la clasificación.
• Todo partido empezado suma puntos en la clasificación, y si hubiera variaciones en el resultado, se deben recoger en la clasificación, pero se debe incrementar el contador de partidos jugados.
• Debe ser posible saber la clasificación de los equipos al final de una jornada anterior a la actual. Esto significa que en estos momentos, meses después del  fin del mundial, podemas saber la clasificación al final de la primera, segunda o tercera jornada.
• En caso de empate a puntos la clasificación se resolvera por la mayor diferencia entre goles marcados y goles recibidos, y en caso de continuar el empate se resolverá por el mayor número de goles marcados

Resistencia aerodinamica.

Este supuesto calculo el incremento de la fuerza de resistencia aerodinamica, que es el sumando mas importante en el consumo, en función de la velocidad de un coche.  Además hace un cálculo del posible consumo del coche, comparandolo con un consumo conocido (para una velocidad determinada). En el gráfico XY se puede ver el factor por el que hay que multiplicar la resistencia dinamica a una velocidad determinada para conocer la de otra velocidad. Si la fuerza se multiplica por un factor, el trabajo=fuerza*distancia también se incrementa según ese factor y, el consumo, en este caso de manera aproximada, también se puede multiplicar por dicho factor.

Resistencia aerodinamica sgún Wikipedia

Resistencia aerodinamica (Origen texto y fórmula)

 Resistencia aerodinámica. Aplicada a un coche, se expresa como la fuerza que necesita para desplazarse (dentro de la atmósfera), sin tener en cuenta el rozamiento con el suelo.

 La fuerza necesaria para desplazarse en la atmósfera es proporcional a la superficie frontal (S), al coeficiente de penetración (Cx), a un medio de la densidad del aire (ro) y al cuadrado de la velocidad del coche con relación al aire, no con relación al suelo (v).

 Es sencillo ver que la relación de fuerzas (f/f') solamente depende de las velocidades al cuadrado. Se debe mencionar que aunque en la fórmula la velocidad es en m/s, para pasar los km/h a metros/seg. hay que multiplicar por una constante, que a la hora de dividir esta arriba y abajo con lo que, para nuestro supuesto, no es necsario hacer la conversión.

 Utilizo como ya he dicho un gráfico XY, la función potencia escrita como A^2 y la función INDIRECTO. También incluyo, en el gráfico, una lista desplegable y una barra de desplazamiento. Con la lista de desplazamiento selecciono la velocidad de referencia y con la barra de desplazamiento incremento los valores de las velocidades.

  Considero que la aproximación entre la resistencia aerodinamica y consumo puede aplicarse a partir de 90km/h, por lo que he preparado una hoja ("VLDAD") en la que en la columna A pongo las velocidades que van entre 90 Km/h y 160 Km/h. Los valores de estas velocidades no son constantes, se ven afectados por el valor que da la barra de desplazamiento del gráfico.

Zonas Climáticas IV. Presentación sobre el mapa.

Presentación sobre el Mapa:

 Los mapas a menudo tienen pequeñas deformaciones, tanto por que son mapas sin una escala reconocida o porque algunos sistemas deforman un poco los mapas. La primera vez que me enfrenté a este problema tiré por la calle de en medio y lo resolví corrigiendo el mapa con las herramientas de dibujo que tengo en mi ordenador. Poco a poco y mediante sucesivas aproximaciones cuadre el mapa.

La posición de la población sobre el mapa la conoceremos mediante un gráfico del tipo XY, cuya serie es:

=SERIES(Población!$B$3;Población!$C$3;Población!$D$3;1)

Insertamos el gráfico XY, en principio como gráfico en hoja aparte y con la serie arriba indicada.

Con el botón derecho del ratón seleccionamos “Formato del área del gráfico”, ponemos  la trama a Ninguno, seleccionamos “Efectos de relleno” -> Imagen-> Seleccionar Imagen, seleccionamos “PenisulaYBaleares5.jpg”, que es el mapa  resultante de mi trabajo con las herramientas de dibujo.

 Seleccionamos el eje Y, formato de ejes-> Escala. Ponemos mínimo a 35 y máximo a 44. Para el eje X el mínimo es -10 y el máximo es 4,5. Estos valores se corresponden con los límites de las latitudes y longitudes de las coordenadas de las poblaciones de la península.

Los valores de las coordenadas de las poblaciones de Canarias tienen un error constante. Pese a ese error se puede presentar la población en el mapa. No obstante no lo voy a corregir, lo dejo para que se corrija como ejercicio dentro de este estudio.

Las líneas de división secundarias de los ejes yo las he suprimido, lo mismo que podía haber suprimido también los ejes. Lo dejo al gusto de quien quiera modificarlo.

En la península hay pueblos o ciudades situados en sitios muy significativos, como pueden ser La Guardia (o A Garda) en la desembocadura del Miño y frontera con Portugal, Ayamonte, frontera con Portugal, Irún, frontera con Francia, Port Bou, también frontera con Francia, Tarifa, punta sur de la península. Con estos puntos vamos a cuadrar el mapa. Preparamos una hoja Excel, en el mismo libro, con las coordenadas de estas poblaciones. Añadimos en la propia hoja “Población” los datos de esas poblaciones. (rango A20:C25)

En el  gráfico modificamos la serie a:

=SERIES(Población!$A$19;Población!$B$20:$B$30;Población!$C$20:$C$30;1)

Con esta serie ajustamos el formato del área de trazado hasta que estas poblaciones estén en su sitio

Este mismo ajuste podemos hacerlo con la serie:

=SERIES(;Poblaciones!$C$2:$C$18641;Poblaciones!$B$2:$B$18641;1)

Es decir con todas las poblaciones de la península y Baleares.

Para Canarias las series son:

=SERIES(;Poblaciones!$C$12931:$c$13499;Poblaciones$b$12931:$b$13499;1)

=SERIES(Población!$B$11;Población!$C$11;Población!$D$11;1)

Una vez ajustado el mapa recuperamos la serie original, copiamos los desplegables de provincias y poblaciones en el gráfico, y protegemos.

Zonas climáticas III. Pausa y autocrítica.

 ¿ Estoy haciendo bien las cosas? ¿Las puede hacer mejor? ¿Puedo ser algo menos confuso?
Si, las estoy haciendo bien pero lo puedo hacer mejor. Intentare ser menos confuso.

Hagamos una copia de todas0.xls, Todas2.xls. Abramos todas2.xls, seleccionemos la hoja Provincias y borremos las columnas D, E, y F. En el nuevo D1 colocamos la siguiente fórmula:

=COINCIDIR(INDICE(MP;$H$1);MPPob;0) Con esta fórmula obtenemos la línea de la primera población de la provincia seleccionada.

En E1 colocamos la siguiente fórmula:

=CONTAR.SI(MPPob;INDICE(MP;$H$1))+COINCIDIR(INDICE(MP;$H$1);MPPob;0)-1 Con esta fórmula obtenemos la línea de la última población de la provincia seleccionada.

Si ampliamos la fórmula a : ="Poblaciones!$a$" & COINCIDIR(INDICE(MP;$H$1);MPPob;0) & ":$E$" & CONTAR.SI(MPPob;INDICE(MP;$H$1))+COINCIDIR(INDICE(MP;$H$1);MPPob;0)-1 tenemos el rango completo de poblaciones de la provincia seleccionada.

Con ="Poblaciones!$d$" & COINCIDIR(INDICE(MP;$H$1);MPPob;0) & ":$d$" & CONTAR.SI(MPPob;INDICE(MP;$H$1))+COINCIDIR(INDICE(MP;$H$1);MPPob;0)-1 tenemos el rango con los nombres de las poblaciones.

Por último si definimos un nombre con el formulón  siguiente :=INDIRECTO("Poblaciones!$d$" & COINCIDIR(INDICE(MP;$H$1);MPPob;0) & ":$d$" & CONTAR.SI(MPPob;INDICE(MP;$H$1))+COINCIDIR(INDICE(MP;$H$1);MPPob;0)-1) podemos dar un contenido variable al desplegable de los pueblos sin necesidad de apoyarnos en ninguna celda.

Profesionalmente lo doy por bueno. Todo esto es, sin duda, correcto. Complejo pero correcto. Particularmente yo creo que si se deben evitar fórmulas tan complejas. Hay que apostar por la sencillez.

Empecemos de nuevo. Borremos todas las variables incluidas hasta el momento

Insertemos los siguientes nombres (variables):

Nombre	Fórmula
Canarias	=Provincias!$B$60:$B$61
CelVin	=INDIRECTO("Provincias!$d$" & PrvSel+1)
CelVinCan	=INDIRECTO("Provincias!$d$" & PrvSelCan+59)
MP	=Provincias!$C$2:$C$53
MPCan	=Provincias!$C$60:$C$61
MPP	=INDICE(MP;PrvSel)
MPPCan	=INDICE(MPCan;PrvSelCan)
MPPPob	=Poblaciones!$E:$E
Npob	=CONTAR.SI(MPPob;MPP)
NpobCan	=CONTAR.SI(MPPob;MPPCan)
PobPrSel	=INDIRECTO("Poblaciones!$D$" & PP & ":$d$" & Npob+PP-1)
PobPrSelCan	=INDIRECTO("Poblaciones!$D$" & PPCan & ":$d$" & NpobCan+PPCan-1)
PP	=COINCIDIR(MPP;MPPob;0)
PPCan	=COINCIDIR(MPPCan;MPPob;0)
Provincias	=Provincias!$B$2:$B$55
PrvSel	=Población!$F$2
PrvSelCan	=Población!$G$2
RanPobSel	=INDIRECTO("Poblaciones!$a$" & PP & ":$d$" & Npob+PP-1)

Insertamos una nueva hoja, la hoja “Población” desde la que seleccionaremos tanto la provincia como la población. Por otra parte incluimos una celda vinculada con el rango de poblaciones de cada provincia, lo que nos permite volver a la población seleccionada con anterioridad al volver a una provincia. Esta vinculación la hago mediante la variable CelVin (CelVinCan para Canarias)

El rango de entrada variable de los pesplegables de las poblaciones se calcula con la variable PobPrSel (Poblaciones de la Provincia Seleccionada. Esta variable, para su calculo, incluye las variables PP (Primera Población de una provincia) y Npob (Número de Poblaciones de una provincia)

El mecanismo que pretendemos seguir es:

 Primero elegimos provincia, con el correspondiente desplegable de provincias. Esta desplegable está vinculado con la celda F2 (variable PrvSel) y su rango de entrada es la variable Provincias. La selección de una provincia modifica tanto el rango de entrada como la celda vinculada del desplegable de poblaciones. La celda vinculada se calcula con la variable CelVin. El rango de entrada variable de los pesplegables de las poblaciones se calcula con la variable PobPrSel (Poblaciones de la Provincia Seleccionada. Esta variable, para su calculo, incluye las variables PP (Primera Población de una provincia) y Npob (Número de Poblaciones de una provincia). Por último, una vez seleccionada la población, presentamos los datos de la población seleccionada, en las celdas B3:D3. Para ello utilizo las variables RanPobSel (Rango Poblaciones Seleccionado, que es similar a PobPrSel) y CelVin.

Para Canarias el concepto es similar, por lo que no me extiendo. 

Con esto hemos incluido las dieciocho mil poblaciones y un mecanismo que nos permite seleccionar cómodamente una de ellas.

Zonas Climáticas II.Desplegables con contenido variable.

Ya tenemos los dieciocho mil pueblos, con sus correspondientes coordenadas en un libro Excel. Ahora vamos a incorporar las provincias, un desplegable con el que seleccionaremos la provincia y otro desplegable, con contenido variable, que nos permitirá seleccionar la población. Una vez seleccionada la población como primer paso pasaremos sus datos a unas celdas concretas. Una vez tengamos los datos de la población en esas celdas presentaremos la población en el mapa. He preparado un fichero con todas las provincias. De momento contiene una sola hoja, con el nombre de la provincia, el código provincial y las antiguas matrículas de los coches. También he incorporado los siguientes nombres: CP, MP y Provincias (insertar->Nombre->Definir). El siguiente paso es incorporar esta hoja con las provincias a fichero con las poblaciones. Copiamos la hoja en el fichero Todas0.xls (botón derecho del ratón sobre la etiqueta de la hoja Provincias-> Mover o Copiar->Al libro Todas0.xls, seleccionando crear Copia). Cerramos el libro Provincias y guardamos Todas0.Xls.

 Canarias. Hasta este momento no he hablado de Canarias. Obviamente, por su situación geográfica, hay que contemplarlas aparte, hacer un apartado específico para ellas. También podíamos decir que como son todas ellas de la zona climática V no es necesario conocer si una población está en una zona u otra, porque están todas en la V. Sin quitar las provincias canarias de la lista general, he preparado una lista propia de Canarias (hoja Provincias A60:C61). Insertamos los nombres referentes a estas dos provincias (Canarias- rango B60:B61,MPCan rango C60:C61, CPCan rango A60:A61)

 Continuo. Ordenamos de nuevo las poblaciones según las columnas E y D (Matrícula y nombre de la población). Insertamos el nombre MPPob para la columna E. Guardamos. Seleccionamos la hoja Provincias y, como primera medida localizamos el comienzo de las poblaciones de cada provincia en la hoja poblaciones mediante la fórmula: =COINCIDIR($C2;MPPob;0) Contamos en número de poblaciones de una determinada provincia con la fórmula: =CONTAR.SI(MPPob;C2) Vemos el rango en el que están las poblaciones de una determinada provincia con: ="$A$" & D2 & ":$E$" & E2-1+D2 El siguiente paso es incluir el despleable que nos va a permitir seleccionar una provincia. Seleccionamos Ver->Barras de Herramientas->Formularios

Seleccionamos Cuadro combinado :

Con el botón derecho del ratón damos estos valores al cuadro combinado:

Repetimos la operación para Canarias. En este caso vinculamos con la celda K1 y rango de entrada Canarias.

 Insertamos los nombres Rangos (f2:f53) y RangosCan (f60:f61). En la celda I1 colocamos, para la peninsula, la siguiente fórmula: ="Poblaciones!" & INDICE(Rangos;$H$1) y en la celda L1 colocamos ="Poblaciones!" & INDICE(RangosCan;$K$1), para Canarias. Esto nos prepara para poder tener listas desplegables variables.

Lista desplegable variable. Insertamos un nombre con la función INDIRECTO :

Preparamos otra lista desplegable con el siguiente formato:

Repetimos la operación para Canarias.

Zonas climáticas en España. Importación de ficheros de texto a excel.

Funciones utilizadas:

Espacios()
Izquierda()
Sustituir()

Este trabajo no es un supuesto, tiene una utilidad real. En España para hacer una instalación de energía solar térmica hay que tener en cuenta las zonas climáticas, que a su vez dependen de las horas de insolación. Dependiendo de zona y de las necesidades de agua caliente sanitaria de un proyecto la ley obliga a cubrir, vía paneles solares,  desde un 50% a un 70% de las necesidades.

En su momento, después de hacer un curso sobre energía solar térmica hice una utilidad que me permite conocer, gráficamente, la zona climática de un determinado pueblo.

Zonas climáticas de  la península y Baleares

Buscando en Internet encontré que alguien, del que desconozco el nombre, había subido un fichero tipo zip con mas de cincuenta ficheros, tipo wpt (way point) para oziexplorer, con las coordenadas de mas de dieciocho mil pueblos de España. Es un trabajo muy completo y es de agradecer que lo haya puesto a disposición de todo el mundo que lo necesite.

La estructura de un fichero de waypoints (puntos con coordenadas)  de OziExplorer es la siguiente:

OziExplorer Waypoint File Version 1.0

European 1979

Reserved 2

Reserved 3

   1,VIA000        ,  42.945730,  -2.915620,36381.47136, 0, 1, 6,         0,     65535,ABECIA                                  , 0, 2,    0, -777

   2,VIA001        ,  42.880310,  -2.681010,36381.47136, 0, 1, 6,         0,     65535,ABECHUCO                                , 0, 2,    0, -777

   3,VIA002        ,  42.930010,  -2.887190,36381.47136, 0, 1, 6,         0,     65535,ABORNICANO                             , 0, 2,    0, -777

   5,VIA004        ,  42.833190,  -2.474520,36381.47136, 0, 1, 6,         0,     65535,ACILU                                   , 0, 2,    0, -777

   6,VIA005        ,  42.976660,  -2.709700,36381.47136, 0, 1, 6,         0,     65535,ACOSTA                                  , 0, 2,    0, -777

 Una cabecera  compuesta por las cuatro primeras líneas y un número indeterminado de líneas, una por punto. Cada una de estas líneas esta compuestas por diversos item, separados por comas. Los dos primeros item se refieren a un ordinal y una referencia del punto. Los dos siguientes item son las coordenadas propiamente dichas (latitud y longitud). El siguiente item es la fecha y hora según el sistema de fechas en Excel. Los siguientes cinco item son valores referidos a temas propios de OziExplorer. El siguiente es un comentario sobre el waypoint y el último , que en el ejemplo aparece como -777, es la altitud del punto en pies (en este caso -777 significa que no se ha tomado la altura).  Los item propios de Oziexplorer son valores que indican colores, tamaños,  signos, etc… propios de la presentación de los puntos en OziExplorer

 Para conseguir nuestro objetivo solo nos interesan los item 2, 3, 4 y 11. Si la información de la altitud fuese buena podríamos tenerla en cuenta, lo que no es el caso. 

 La persona que hizo el trabajo de buscar pueblo a pueblo y grabar los distintos ficheros con los waypoints y luego los subió a Internet, a su vez, (para ver esto hay que analizar, viéndolos, los ficheros), utilizó el segundo item con los dos primeros caracteres iguales al antiguo código de matriculación de coches seguido de un ordinal. Utilizar la antigua matriculación de los coches, a mi,  me parece que no es  la mejor opción, es mucho mas útil el código provincial o código postal. No obstante nos va a permitir trabajar correctamente, sin necesitar otros datos mas o menos correctos.

 Los ficheros los bajé de Internet en un fichero ZIP con 63 ficheros. El primer movimiento es fusionar los 63 ficheros. En este punto es cuando hay que tirar de viejos conocimientos, el antiguo DOS (Windows, al menos hasta el XP sigue manteniéndolo. Abrir, copiar y pegar 63 ficheros lleva su tiempo. En lugar de ello prepare un fichero ejecutable ( Todas.BAT) con la instrucción:

type *.wpt >> todas0.txt

 La ejecución de este comando, una sola vez, da como resultado el fichero todas0.txt, con todas las líneas de todos los ficheros.  A partir de este fichero pasaremos nuestros wps a un libro Excel. Se puede hacer de dos maneras. La primera, la mas ortodoxa es abrir Excel y desde el Excel abierto, abrir el fichero de texto.

En este punto podemos seleccionar los campos que queremos importar y los que no queremos. No lo vamos a hacer, no es necesario. Ya lo haremos en Excel, aunque reconozco que sería interesante hacer el ejercicio de no importar algunos campos.

La otra manera de pasar todas.txt a Excel es :

• Abrir todas0.txt con notepad.
• Seleccionar todo el texto.
• Copiarlo.
• En un libro de Excel nuevo seleccionar la celda a1 de la hoja deseada y pegar el texto. Lo copiado ocupa la columna A.
• Seleccionar la columna A entera.
• Seleccionar Datos-Texto en columnas
• A partir de este punto se sigue el mismo proceso, con los mismos desplegables que en la manera anterior.

Eliminamos los campos que no necesitamos. Seleccionamos la columna correspondiente , edición y eliminar.  Terminada la eliminación de los datos no necesarios ordenamos los datos según la columnas A y D (provincia-pueblo). El la celda E1 ponemos la instrucción :

=ESPACIOS(IZQUIERDA(SUSTITUIR($A1;"-";" ");2))

 y la arrastramos hasta el final. En la columna E tenemos ahora la matricula provincial. Guardamos el fichero como todas0.xls.

Algo que yo creo que es conveniente es copiar los datos obtenidos con las formulas eliminando las formulas, lo que aligera  las hojas. Hacer esto es muy sencillo, seleccionamos la columna E, copiamos, seleccionamos F1, seleccionamos editar->Pegado Especial->Pegar Valores. Hecho esto podemos eliminar la columna E, que es la que contiene las fórmulas.

Fichero ZIP con las coordenadas de los pueblos.