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.

Datos numéricos. Punto por coma (o coma por punto) en la separación de decimales

Profesionalmente a todos nos ha pasado que nos ha llegado un fichero, procedente normalmente de un gran host, con los datos númericos en sistema anglosajón, los decimales separados por punto en vez de por coma. Este cámbio supone que en nuestro Excel esos datos sean considerados como texto. Si queremos utilizar esos datos como números tenemos que convertirlos a número, lo que siempre es un engorro. La primera opción que se nos ocurre es la de cambiar el punto por la coma. Esto lo podemos hacer bien mediante la fórmula =VALOR(SUSTITUIR(A2;".";",")) o bien mediante la sustitución de caracteres (edición->Reemplazar o Ctrol+L). O bien mediante el asistente para convertir texto a columnas.

He preparado un ejemplo en Excel y una presentación en Power Point. Os lo podéis bajar pinchando en el  icono de la izquierda.

Formato Condicional. Emulación de un display.

 Queremos emular un display  compuesto por siete segmentos luminosos, que según el número que se quiera presentar, lucen o no lucen. En el dibujo a mano alzada de la izquierda vemos en primer lugar los siete segmentos, numerados, y a continuación los números del 0 al 9 formados por el conjunto de segmentos que al lucir representan un número.

Para representar el display consideramos que cada segmento corresponde con una celda, a la que damos forma alargada, según la forma de los segmentos que forman los display. Emulamos si un segmento luce o no luce mediante el uso del formato condicional. Apagado con el tono general de las celdas de la hoja y encendido con un tono brillante (rojo).

Determino si un segmento debe lucir de dos maneras, lo que me permite utilizar las siguientes funciones:

si()

y()

o()

indirecto()

Indice()

Además hago una conversión de decimal a binario del número a presentar.

 Primero hay que decidir, para todos los segmentos, si un determinado segmento debe lucir o no para cada número.

 En la hoja "Segmentos" analizo todos los números y el efecto que hace sobre cada segmento, en el rango a1:h11.  A su vez traslado los valores correspondientes al número seleccionado ( de 0 a 9) al rango B10:b16 de la hoja "Display" mediante las funciones Indirecto e Indice. Aunque este traslado no es necesario despeja el panorama. Utilizo además un nombre para referirme a la matriz A1:H11 (Segmentos). Una vez trasladados estos valores a la hoja "Display", rango B10:b16, los utilizo para dar un  formato condicionado a los siete segmentos que forman el display.

 Aunque esta primera versión de la emulación de un display funciona correctamente es mejorable, no necesario desplegar la matriz (hoja segmentos, rango A1:H11) en la que se analiza cada segmento para cada valor del número. Tampoco es necesario trasladar esos valores de una hoja a otra para poder utilizarlos en el formato de los segmentos.

 El  nuevo análisis para saber si un segmento luce o no luce lo hago con las funciones lógicas Y() y O(). Se puede utilizar solo una de ellas, de echo es mucho mas intuitivo utilizar la función O(), aunque resultan formulas mas cortas con la función Y().

Seg.	Fórmula
1	=Y($B$2<>1;$B$2<>4)
2	=Y($B$2<>1;$B$2<>5;$B$2<>6)
3	=Y($B$2<>1;$B$2<>2)
4	=O($B$2=0;$B$2=2;$B$2=3;$B$2=5;$B$2=6;$B$2=8)
5	=O($B$2=0;$B$2=1;$B$2=2;$B$2=6;$B$2=8)
6	=Y($B$2<>2;$B$2<>3;$B$2<>7)
7	=Y($B$2<>0;$B$2<>1;$B$2<>7)

Como vemos es mas fácil o intuitivo utilizar la función O(). Si leemos la línea 4, B2=0 o B2=2 o B2=3 o  B2=5 o B2=6 o B2=8 vemos que equivale a  b2<>1 y B2<> 4 y B2 <> 7 y B2 <> 9, que tiene un menor número de comparaciones. Si leemos la línea 1 (B2 <>1 y B2 <>4) , su equivalente con la función O() sería (B2=0 o B2=2 o B2=3 o B2=5 o B2=6 o B2=7 o B2=8 o B2=9) , que es mas fácil de desarrollar pero es mas "pesada", tiene un número de comparaciones mayor.


La nueva versión de la emulación utiliza este análisis y utiliza en vez del rango o dirección de las celdas el nombre que les queramos dar.

¿ Como damos un nombre a un rango ? Muy fácil :

¿Para que nos sirve esta operación?

Nos permite referirnos a un rango con un nombre, en vez de con una dirección, con un nombre de variable.

En el libro Display.xls utilizo para referirme al rango A1:H11 de la hoja "Segmentos" el nombre Segmentos. Cuando traslado los valores, para un número concreto, que darán formato al display utilizo la función Indice con referencia al rango en la celda B11 de la hoja Display (=INDICE(Segmentos!B2:H11;$B$4;2)) y utilizo la función Indice con referencia al nombre en las celdas B12 a B16 (=INDICE(Segmentos;$B$4;5))

En la segunda versión del libro, Display2.xls, ni  traslado los valores de hoja "Segmentos" ni me apoyo en un rango con los valores que dan el formato a emulación del display. Utilizo fórmulas y nombres directamente en el formato condicional.

Primero defino los siguientes nombres:

Nombre	Se refiere a
Num	=Display!$B$2
Num1	=Num+1
FormC1	=Y(Num<>1;Num<>4)
FormC2	=Y(Num<>1;Num<>5;Num<>6)
FormC3	=Y(Num<>1;Num<>2)
FormC4	=O(Num=0;Num=2;Num=3;Num=5;Num=6;Num=8)
FormC5	=O(Num=0;Num=1;Num=2;Num=6;Num=8)
FormC6	=Y(Num<>2;Num<>3;Num<>7)
FormC7	=Y(Num<>0;Num<>1;Num<>7)

Con estos nombres directamente entramos a la opción de formato condicional, de la siguiente manera:

 Como se puede ver, en las fórmulas de los formatos ya no utilizo la referencia B2, utilizo el nombre de variable "Num", que aunque en estas fórmulas solo utilizo una variable, siempre es mas sencillo utilizar nombres de variables que rangos excel que pueden llegar a tener un número elevado de caracteres. Pensemos que si queremos referirnos a un  rango B2, variable Num, en otra hoja, por no hablar de en otro libro, sería NomHoja!$B$2.

 En la conversión a binario del número decimal Num, hoja Display G1:J3 utilizo la función Si(), el operador * (por) y nombres de variables. Realmente pienso sacarle mucho mas provecho a esta operación, pero de momento se queda como está.