Filtro avanzado de datos.

El filtro avanzado de datos permite una selección de datos mucho mas flexible y amplia que el filtro automático. Como casi todas las explicaciones de Microsoft la ayuda del filtro avanzado es un galimatías que no hay quien entienda, pero que en la práctica es muy sencillo de utilizar. Es mas, permite filtrar datos desde otra página distinta a la que contiene los datos. Los datos se pueden filtrar en la propia hoja o se puede obtener una copia del resultado del filtro en otra hoja distinta. En el libro que he preparado hay una hoja para el filtro, otra para los datos y otra para los datos filtrados. Utilizo, como datos, la relación de los mas de 18000 pueblos de España, y en este caso no utilizo funciones, pero si rangos con nombre.

Variables o rangos con nombre:

Prv=Pobl!$E$1:$E$18642

Fil=Filtro!$A$1:$B$2

RanDat=Pobl!$A:$E

• Los datos a filtrar deben tener cabecera, cada columna debe presentar su cabecera. Estas cabeceras funcionan como nombre o rótulo de la columna. Al definir el filtro debemos utilizar ese nombre de columna, y debajo de el indicar la condición del filtro.
•  El filtro debe tener como mínimo una fila con el/los nombres (para facilitar el diseño del filtro yo copio la cabecera de los datos a filtrar, entera) de las columnas que intervienen en el filtro y al menos una fila mas con las condiciones del filtro. Estas condiciones se ponen debajo del cada nombre de columna.
•  El rango de criterios (el filtro) puede estar en una hoja distinta a la de los datos, en este caso la hoja FILTRO. Los datos resultantes del filtro los copio a la hoja DATOS.
• En el filtro pueden intervenir los campos que componen los datos mas de una vez. 
• Al definir el filtro los valores situados en una línea están vinculados mediante un Y lógico y a su vez, si utilizamos mas de una línea, las líneas entre si están vinculadas mediante un O lógico.
•  Si en el ejemplo queremos encontrar los pueblos de la provincia de CADIZ que empiecen por V debemos poner CADIZ debajo de provincia y V debajo de nombre (conviene leerse la ayuda de excel).
•  Si quisiéramos obtener los pueblos de CADIZ y HUELVA (lo decimos así pero en realidad estamos indicando de CADIZ o de HUELVA) pondríamos, debajo de provincia, CADIZ en la primera línea y HUELVA en la segunda.
•  Si necesitamos hacer un filtro del tipo, para los datos del ejemplo, latitud mayor o igual a 42 y menor que 43 (42<=Lat <43) en la fila de cabecera pondríamos dos veces el rótulo LAT y en la misma fila, debajo de  uno de ellos pondríamos >=42 y debajo del otro <43.
• Los valores del filtro admiten funciones, referencias a otras celdas y variables o rangos con nombre ( En el libro excel adjunto hay dos rangos de datos con nombre, RanDat y Prv)
• Podemos utilizar un nombre para todos los rangos utilizados al hacer un filtro avanzado.
• Para obtener una copia del resultado del filtro hay que activar el filtro desde la hoja de destino, en este caso la hoja "datos".
•  Una vez en esa hoja activamos el filtro avanzado. A veces, según donde estemos,Excel nos envía un mensaje indicando que no puede encontrar los rótulos de las columnas, aceptamos y aparece un desplegable que nos pide los rangos de los datos, del filtro, del destino de los datos, si queremos copiar los datos y si queremos registro único.  
• No es necesario que el rango del filtro incluya todas las cabeceras, solo hay que incluir aquellas que participen en el filtro (con sus correspondientes valores).
• Conviene leer la ayuda de excel sobre este tema, ya que soporta caracteres comodín y otras combinaciones ( >,>=,<,<=)

La entrada anterior del Blog esta pensada para ver algunas opciones de las funciones de búsqueda. En ella busqué el nombre de las distintas provincias mediante las funciones Buscar,BuscarV y Coincidir. Para obtener esos mismos nombres con el filtro avanzado podemos dejar el blanco la celda E2 (provincia) del filtro  o el carácter comodín *, activamos el filtro, como rango de datos ponemos el rango con nombre Prv, el rango del filtro sera en este caso Filtro!$E$1:$E$2 , como destino Datos!$A$1, señalamos que queremos copia y señalamos SOLO REGISTROS Únicos.

Items en una lista.Funciones de búsqueda BUSCAR,BUSCARV y COINCIDIR.

En principio todos pensamos que una función de búsqueda, para que funcione correctamente, debe devolvernos bien un valor exactamente igual al valor buscado, o bien un error. No es así, algunas opciones de las funciones de búsqueda en excel encuentran valores distintos a los buscados, no es ninguna novedad, ya lo he mencionado con anterioridad y la ayuda de excel   para estas funciones lo deja claro. Algunas opciones de las funciones BUSCAR, BUSCARV y COINCIDIR encuentran el mayor valor que es menor o igual al valor buscado (último valor anterior al valor buscado). La función COINCIDIR en una de sus opciones encuentra el menor valor que es mayor o igual al valor buscado (primer valor mayor que el valor buscado). 

Basándome en las opciones de estas funciones que no devuelven exactamente el valor buscado he preparado un par de trabajos que me permiten conocer, dentro de una lista, los distintos valores, que repetidos conforman la lista. Utilizo la lista de  los mas de 18000 pueblos de España. La información de cada pueblo esta compuesta por el nombre, coordenadas y el nombre de la provincia. En la columna nombre de  la provincia realizo la búsqueda de los distintos items que la componen.

Funciones utilizadas:

BUSCAR()

BUSCARV()

COINCIDIR()

CARACTER()

CODIGO()

LARGO()

DERECHA()

IZQUIERDA()

Variable o rango con nombre:

Prv=Pobl!$E:$E Se refiere a la columna con los nombres de las provincias (columna E de la hoja Pob)

En BusquedaItems3.xls busco los nombres de las provincias desde la última a la primera. En este caso al buscar encuentro el último valor anterior al valor buscado, lo que me obliga a buscar en orden descendente. En principio puedo buscar cualquier valor, algo encontraría, pero si quiero buscar hacia arriba no puedo sistematizar la búsqueda. Puedo conocer un valor, o el siguiente valor menor al último encontrado, pero si utilizo un algoritmo similar para valores mayores al último valor encontrado probablemente me conduzca a ese mismo valor.    Lo hago utilizando las funciones COINCIDIR (columnas A y B), BUSCARV (columnas C y D) y BUSCAR (columnas E y F). Lo hago así aprovechando que las funciones me van a devolver "el mayor valor que sea menor o igual al buscado". En este caso busco cadenas de distinta longitud, alfabéticas y ordenadas de modo ascendente. En este caso el máximo valor que puede tomar una cadena alfabética es una cadena compuesta por zetas. Por tanto la primera búsqueda que hago es =BUSCARV("ZZZ";Prv;VERDADERO) , que encuentra ZARAGOZA (entre los valores del ejemplo). ¿Cual es el valor anterior, en un ordenamiento alfabético, a ZARAGOZA? ¿como lo calculo de una manera genérica? Para este caso el valor anterior es sustituir el último carácter de la cadena por el carácter anterior a ese último carácter. Para ZARAGOZA el último carácter es A y el carácter anterior a A es @. Por tanto la cadena anterior a ZARAGOZA es ZARAGOZ@ . Esta trasformación la hago mediante la formula:

IZQUIERDA($A3;LARGO($A3)-1) & CARACTER(CODIGO(DERECHA($A3;1))-1)

Separo el último carácter de la cadena mediante la función DERECHA, hallo el código ASCII de ese carácter mediante la función CODIGO, le resto uno a ese código y reconstruyo el carácter mediante la función  CARACTER. El resto de la cadena la separo mediante las funciones IZQUIERDA y LARGO (Columnas B,D y F)

La siguiente (y sucesivas) búsquedas las hago mediante la siguiente fórmulas:

=INDICE(Prv;COINCIDIR($B3;Prv;1))

=BUSCARV($D3;Prv;VERDADERO)

=BUSCAR($F3;Prv)

En BusquedaItems2.xls busco los nombres de las provincias desde la primera a la última. Aquí solo utilizo (de las funciones de BÚSQUEDA) la función COINCIDIR(). En este caso la lista con los nombres está ordenada de modo descendente. En este caso buscamos el menor valor que sea mayor o igual al valor buscado. El primer valor buscado es la @. El valor encontrado es "A CORUÑA". En las sucesivas búsquedas buscamos un valor mayor que último valor encontrado. Como en este caso solo utilizamos caracteres alfabéticos el siguiente valor a buscar es la concatenación del valor encontrado con cualquier carácter menor o igual a la @. Utilizo @ en =A1&"@" y  busco la siguiente provincia mediante =INDICE(Prv;COINCIDIR($C1;Prv;-1))

Web cam.Pequeño y barato método de instalar una web cam de vigilancia.

Pequeño y barato método de instalar una web cam de vigilancia.

1. Necesitamos una cámara web  con software de detección de movimiento (Logitech lo tiene). En preferencias seleccionamos el directorio de detección de movimiento.
2. Necesitamos un servidor web y/o una utilidad que permita compartir ficheros. El navegador Opera tiene una utilidad, Opera unite, que entre otras cosas, tiene un servidor web y un compartidor de ficheros. Activamos ambos y como directorio para ambos ponemos el mismo directorio que hemos puesto para la detección de movimiento. Protegemos o no el acceso con clave (creo que funciona mejor sin clave, con acceso libre)
3. El servidor,en este caso Opera unite, nos da una dirección (una para el servidor web y otra para compartir ficheros), a la que podemos acceder desde cualquier otro ordenador conectado a internet. 
4. Podemos mejorar estos mínimos creando o modificando páginas html que reconozcan los ficheros creados al detectarse un movimiento.

No es que sea una solución perfecta, de hecho no lo es, pero permite tener un sistema de videovigilancia sin grandes alardes ni económicos ni técnicos. 

Perdidas por orientación e inclinación.

Esta herramienta está pensada para calcular las perdidas por orientación e inclinación en las instalaciones de energía solar, tanto térmica como fotovoltaica, mediante el método gráfico. Este método consiste en llevar  al gráfico que permite calcular las perdidas los valores de la inclinación y de la orientación de instalación.

Utilizo las siguientes variables con nombres:

Az=Aux!$A$2 (para azimut)

EO=Aux!$D$1 (para Este-Oeste)

Lat=Aux!$L$1  (para Latitud)

NS=Aux!$G$1 (para Norte-Sur)

Raz=Aux!$A$3 (radianes de Az

Rx=Aux!$A$1  (para la inclinación)

Utilizo las siguientes funciones:

Caracter()

Cos()

Seno()

Radianes()

SI()

Y()

Indirecto()

El gráfico es un gráfico del tipo XY con un solo punto en el que incluimos, como fondo, la gráfica que permite calcular las pérdidas.

La gráfica tiene dos entradas, una la inclinación (hacia arriba) de los paneles y otra la orientación de los paneles (norte-sur-este-oeste). La inclinación se traslada a lo largo de la gráfica mediante círculos concéntricos y la orientación se representa mediante los radios. La intersección radio-circulo da el punto de perdidas para una orientación e inclinación determinada de la instalación.

Parámetros del gráfico:

Tipo de gráfico: XY (Dispersión)

Título           : =Aux!$A$9

Valores de X: =Aux!$A$6:$A$6

Valores de Y: =Aux!$A$7:$A$7

Los ejes no son visibles.

Los valores  de los ejes, tanto para X como para Y, se corresponden con los valores de los círculos concéntricos, en este caso, de 0 a 90.

Para incluir la gráfica en el gráfico seleccionamos Formato del área seleccionada->Efectos de relleno->Imagen->Seleccionar imagen. Seleccionamos la imagen de la gráfica (PerdidasOrientacionInclinacion2.jpg, se puede bajar con el botón derecho del ratón) y la añadimos como fondo. Ajustamos los valores máximo (90) y mínimo (-90) de los ejes  X e Y. Manualmente ajustamos el área de trazado a la imagen. Por último, ocultamos los ejes.

Los valores de X e Y son proporcionales al valor de inclinación (radio del circulo) y al seno (para X)  y al coseno (para Y) de la orientación. La orientación es con respecto al eje Y, no con respecto al eje X. Estos valores los calculo en la  celdas A6 y A7 de la hoja AUX.

En el propio gráfico incluyo dos barras de desplazamiento, que me permiten variar los valores de inclinación y de orientación y dos desplegables, que me permiten seleccionar norte-sur (eje Y) y este-oeste (eje X). Parece natural, de hecho en el curso que hice al respecto así lo hacían, referirse a una orientación mediante dos parámetros (norte o sur) y los grados de inclinación.  Llevados al gráfico el norte  y este suponen valores positivos y oeste y sur valores negativos. Esto implica la aparición de un signo (mas o menos). En Aux!D1 y  Aux!G1 calculo el signo usando la función SI():

En D1 =SI(C1=1;-1;1)

En G1 =SI(F1=1;1;-1)

Los valores de X e Y los calculo en Aux!A6 y Aux!A7 mediante:

=D1*RX*SENO(RAz)

=RX*G1*COS(RAz)

Queda comentar el título del gráfico, donde incluyo la orientación y la inclinación y un salto de línea. El salto de línea lo hago con la función caracter(10). El cálculo, en grados según la gráfica, de la orientación lo realizo mediante la función indirecto y la combinación de los valores norte-sur y este-oeste, mediante la fórmula, en aux!e3:

=SI(Y(EO=1;NS=1);1;0)+SI(Y(EO=-1;NS=1);2;0) +SI(Y(EO=-1;NS=-1);3;0)+SI(Y(EO=1;NS=-1);4;0)

El valor a presentar en el título se obtiene mediante la fórmula:

=INDIRECTO("f" & E3+3)

Desplegable con los días del mes.

Días del mes: El objetivo del supuesto es crear una lista desplegable que nos permita seleccionar un día del un mes y año determinado. En el supuesto seleccionamos el año  mediante una barra de desplazamiento  y mediante una lista desplegable se selecciona el mes. Seleccionados mes y año en la lista desplegable de las fechas aparecen los todos días del mes.

Hay dos desplegables con las fechas del mes, en uno de ellos utilizamos la función INDIRECTO y en otro utilizamos la función DefRef

Funciones utilizadas:

DefRef()

DiaSem()

Indirecto()

Indice()

Si()

Para realizar este supuesto utilizamos las siguientes variables con nombre: 

Año=Fechas!$A$2

DiaSel=Fechas!$C$2

DiaSel2=SI(DiaSel>UDiaMes;UDiaMes;DiaSel)

DiaSemana=INDICE(Semana;DIASEM(Fechas!$D$2;2))

DiasMes=INDIRECTO(Listas!$E$2)

DiasMes2=DesRef(Listas!$a$1;1;2;UDiaMes;1)

LineaSel=INDIRECTO(Fechas!$B$15)

Meses=Listas!$A$2:$A$13

MesSel=Fechas!$B$2

PDiaM=FECHA(Año;MesSel;1)

PDiaMes31=Listas!$C$2+31

Semana=Listas!$B$2:$B$8

UDiaMes=DIA(PDiaMes31-DIA(PDiaMes31))

Barra de desplazamiento (Años):

Vinculada con la celda Año

Lista desplegable con los meses:

 Rango de entrada Meses.

 Celda vinculada: MesSel

Primera lista desplegable con los días del mes:

            Rango de entrada DiasMes2

Celda vinculada DiaSel

Segunda lista desplegable con los días del mes:

Rango de entrada DiasMes

Celda vinculada DiaSel

Para seleccionar una fecha  primero que hay que seleccionar el año. En este caso utilizo una barra de desplazamiento, con valores de 1900 a 2100, pero se puede utilizar cualquier otro método, como por ejemplo, escribir el número de año en la celda correspondiente o incluso definir la variable Año como una constante (=2012). Lo mismo se puede decir para el desplegable del mes, se puede optar por escribir el número del mes, elegir un número entre 1 y 12 mediante la validación de datos o incluso utilizar la variable MesSel como una constante. Conocidos el año y el mes calculo el último día del mes mediante el método, ya explicado en el blog, de:

Mediante la función Fecha(), llevada a una variable con nombre, transformo a fecha el 1/Mes/año. Este cálculo lo hago mediante la variable PDiaM (Primer Día Mes)

PDiaM=FECHA(Año;MesSel;1)

A esa fecha, la del mes en proceso, le sumo 31. Esta suma nos lleva necesariamente al mes siguiente al mes del proceso.

Mediante la función Dia() calculo el día del mes siguiente al del proceso, al que nos lleva la suma anterior. Si el mes tiene 31 días esa suma nos lleva al día uno del mes siguiente. Si tiene 30 días nos lleva al día 2, si tiene 29 nos lleva al día tres y si tiene 24 nos lleva al día cuatro. Si a la suma le restamos el día del mes siguiente al que nos ha llevado la suma obtenemos el último día del mes en proceso. Esta vez el cálculo no lo realizo sobre la hoja Excel, lo calculo mediante el uso de variables (con nombre).

Primer día del mes en proceso mas 31: PDiaMes31=Listas!$C$2+31.
Esta variable debía ser PDiaMes31=PDiaM+31

Último día del mes en proceso: UDiaMes=DIA(PDiaMes31-DIA(PDiaMes31))

Conocido el último día del mes ya sabemos el tamaño del desplegable.

            El la hoja “Listas”, columna C preparo las fechas del mes mediante:

En C2 =PDiaM. En las siguientes filas sumo uno a la fila anterior (C3=C2+1, C4=C3+1, etc..) hasta completar 31 días.

El cálculo del rango del desplegable lo hago de dos maneras, una utilizando la función Indirecto() y otra utilizando la función DesRef()

DiasMes=INDIRECTO(Listas!$E$2)

DiasMes2=DesRef(Listas!$a$1;1;2;UDiaMes;1)

Estas variables las podemos llevar bien a una lista desplegable o bien a una validación por lista (hoja “fechas” Datos->Validación)

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.