Filtro avanzado.¿Por qué no me funciona?

Funciones utilizadas:

Espacios()

EsTexto()

Rangos con nombre:

Modelo3=Modelos!$A$1:$D$61

Filtro1=Filtro!$A$1:$F$2

Filtro2=Filtro!$A$8:$F$9

Destino=Salida!$A$1:$D$1

La semana pasada estuve ayudando a mi mujer a preparar un filtro que le daba problemas. Aparentemente el filtro estaba bien hecho, pero no funcionaba.

Los datos provenían de la informática corporativa de su empresa, un fichero de texto separado por punto y coma. La importación del fichero, como no podía ser de otra manera, se realizó sin ningún tipo de problemas.

El filtro es un filtro atípico, hay que filtrar unos determinados códigos, es decir hay que quedarse con aquellos códigos que son distintos a una lista de códigos. 

Al cabo de un rato de trastear con el fichero vimos que la columna con los códigos a filtrar incluía una serie de espacios en blanco  (no visibles) después del código, que eran los que hacían que el filtro no funcionase.

Como, evidentemente, no puedo subir a la red un fichero con los datos reales de la empresa, he preparado un fichero, a base de fechas, emulando el problema encontrado (y encontrando otro). Los datos son una sola columna (Columna A de la hoja Modelos) con fechas en formato AAAAMMDD seguidas de unos espacios en blanco. En el ejemplo compruebo la existencia o no de espacios indeseados concatenando por delante y por detrás un asterisco (columna B). En la columna C, mediante la función ESPACIOS() elimino esos espacios no deseados. En la columna D compruebo que esos espacios han desaparecido. Hecho esto preparé un filtro  (filtro2) para no seleccionar determinados domingos, pero, el filtro no funcionaba, no filtraba esos domingos.

 ¿Qué le sucede a este filtro? Muy sencillo, los datos de la columna C son texto y los datos del filtro preparado, tal cual lo preparé (hoja filtro rango A8:F9) son numéricos. La existencia de esta diferencia la compruebo en E2 (=C2=20120101, que da falso) y en F2 (=C2="20120101", que da verdadero). En E3 (=ESTEXTO(C2)) pregunto expresamente si es texto y el resultado es verdadero.

En el fichero original, el de mi esposa, los códigos son alfanuméricos, pueden ser numéricos puros, alfabéticos puros o una una mezcla de ambos. Además, algún código puede que se  haya retocado, o incluso incluido, a mano una vez convertido a excel el fichero de texto original. Por tanto nadie nos garantiza que todos los elementos de la columna con los códigos sean texto. Para no complicarme mucho la vida fuerzo a que todos los códigos sean texto concatenando un * por delante y otro por detrás (columna D de la hoja modelos) y modifico el filtro (filtro1) para incluir asteriscos  (hoja Filtro, rango A1:F2). No he profundizado en mejorar este tema, probablemente se pueda, pero de esta manera funciona correctamente.

  

Un poquito de programación. La grabadora de macros.

El grabador de macros graba desde el momento en el que lo activamos hasta el momento en el que lo desactivamos cualquier operación que hagamos. Esto nos permite, sin profundizar demasiado en el Visual Basic para excel, automatizar de una manera sencilla tareas repetitivas.

Para activarlo :

1. Ver
2. Barra de herramientas.
3. Visual Basic.

Aparece una banderola con las opciones de VBasic. De momento solo voy a comentar tres de ellas:

1.  La primera es el triangulo típico del play de cualquier reproductor. Nos permite ejecutar una macro.
2. La segunda, un botón rojo, también típico de cualquier grabador. A partir del momento en que pulsamos este botón, excel graba todo lo que hagamos. Al pulsarlo se convierte en un pequeño cuadrado, también el típico icono de pausa de los grabadores.
3. La tercera que voy a comentar es el editor de visual basic. Nos permite acceder al código grabado, modificarlo, o crear un código nuevo.

 Desde mi punto de vista Vbasic para excel es muy potente, permite hacer cosas insospechadas.

Gráficos XYZ. Gráficos de burbujas.

Este tipo de gráficos es similar a los gráficos de dispersión, tipo XY, pero con un tercer valor (Z) representado, en este caso, mediante una "burbuja". El tamaño o diámetro de la burbuja es proporcional al tercer valor (Z).

En el ejemplo utilizo los datos, obtenidos mediante GPS, de una de mis excursiones, entre el pueblo burgalés de Herran y el alaves de Lalastra. Los datos obtenidos por el GPS los copio desde el editor de rutas del programa MapSource (copia-pega) a un libro excel.

En la hoja DatosImportados mantengo los datos copiados desde MapSource a excel. En la hoja Datos están los datos procesados. Los datos los preparé, o mejor dicho, los separé mediante la opción Datos->Texto en Columnas. Una vez separados los datos de las unidades hice unas operaciones matemáticas elementales con el fin de mejorar la presentación del gráfico.

Los datos que utilizo son las coordenadas UTM y la altura de los puntos tomados por el GPS. El dato proveniente de MapSource viene con unidades, para el caso de la altura, y como un conjunto en el  caso de las coordenadas.

Eliminar las unidades de la altura:

1. Seleccionamos la columna, en este caso C.
2. Entramos en Datos->Texto en columnas.
3. Seleccionamos Delimitados. Continuamos (siguiente). Validamos Espacio y continuamos.
4. En esta pantalla podemos ver los dos campos, que en este caso, forman la columna C. Seleccionamos el segundo campo e indicamos "No importar". Como en este caso no quiero mantener la columna original pongo como destino $C$1.
5. Ya tenemos las alturas, sin unidades, en la columna C.

Separación de las coordenadas:

1. Seleccionamos la columna, en este caso J.
2. Entramos en Datos->Texto en columnas.
3. Seleccionamos Delimitados. Continuamos (siguiente). Validamos Espacio y continuamos.
4. En esta pantalla podemos ver los cuatro campos, que en este caso, forman la columna J. En este caso voy a mantener los cuatro campos y la columna original. Pongo como destino $K$1.
5. Ya tenemos separadas las coordenadas (Utm).

Para mejorar la presentación del gráfico paso los valores de las coordenadas de metros a Km, dividiendo por mil (Columnas O y P)  y resto la altura mínima del trayecto a  las alturas de los distintos puntos (Columna D). Esta operación aumenta la diferencia de tamaño de las burbujas.

1. Insertamos  un gráfico.
2. Seleccionamos, dentro de "Tipos Standar",  el tipo "Burbujas".
3. Continuamos. En este segundo paso seleccionamos serie e indicamos el rango de los valores para X (columna O), para Y (columna P) y para Z (columna D)
4. Una vez creado el gráfico seleccionamos la serie, entramos a formato de la serie, seleccionamos opciones, seleccionamos si queremos que el tamaño de la burbuja sea el diámetro  o la superficie y la proporción del tamaño que queremos ver representada.
5. En gráfico->Datos de origen podemos revisar los rangos de entrada.

Mi mando a distancia ¿Funciona o no funciona?

Esta es una pregunta que todos nos hemos hecho alguna vez. ¿Funciona el mando?¿Tiene pilas?¿Que le pasa a esto?

Durante una grabación con mi cámara de vídeo (la cámara estaba sobre un trípode y yo la controlaba con el mando a distancia) descubrí que la señal del mando a distancia se ve en la cámara. El diodo led de un mando a distancia emite una señal no visible por el ojo humano, pero que si es visible por los sensores de luz de una cámara de vídeo  (o de fotografía digital). Sencillamente, con una cámara de fotos digital podemos saber si un mando a distancia no funciona, no podemos garantizar que funcione, pero si podemos estar seguros de que no funciona. Enfocamos el mando a la cámara, pulsamos un botón, el botón o los botones problemáticos  y si el led no luce es que o le faltan pilas, o están mal puestas, o es que efectivamente algún botón esta mal o es que el mando se ha estropeado. 
La última vez que utilicé este truco lo que pasaba es que se había quedado pillada una tecla, el led lucía siempre. Es otra manera de estar estrpeado.

Validación por lista.

Rangos o variables con nombre:

DiasSem=Hoja2!$A$1:$A$7

MesActual=MES(AHORA())

Meses=INDIRECTO("Hoja2!$B$" & MesActual & ":$B$12")

Funciones utilizadas:

Ahora()

Indirecto()

Mes()

El Primer paso consiste en preparar la lista con los posibles datos válidos para la celda o celdas en cuestión. He preparado dos listas, una con los días y otra con los meses. Están en hoja2.

En el segundo paso hay que  insertar un rango con nombre para cada lista, mediante: Insertar->nombre->Definir. Se indica  nombre que vamos a dar a la lista  y el rango donde está la lista.

Para validar:

1. Hay que seleccionar la celda o celdas.
2. Mediante Datos->Validación y Selección de lista. En origen pones =Nombrelista (en el ejemplo DiasSem o Meses). 

Una de las listas es de contenido fijo y la otra de contenido variable. La lista DiasSem es una lista de contenido fijo, y la lista Meses es una lista de contenido variable. 

• El supuesto del que parto para esta lista variable es el de no poder seleccionar meses anteriores al mes actual.
• Mediante Mes(ahora()) calculo el mes actual.
• Mediante  Meses=INDIRECTO("Hoja2!$B$" & MesActual & ":$B$12") hago variable la lista.

Perdidas por sombras.

Este trabajo es una mejora (o al menos esa es mi intención) de un libro Excel que permite calcular las perdidas por sombra en una instalación de paneles solares, tanto fotovoltaica como térmica. Los obstáculos u objetos que producen sombra sobre una instalación de paneles tienen una altura determinada y están a una distancia determinada, definida mediante los ejes correspondientes. Mediante los cálculos correspondientes estos tres valores, en metros, se pasan a dos, en grados  Una vez realizados los cálculos el autor original del libro propone que con las herramientas de dibujo de Excel se trace el área de sobra. Por mi parte no efectúo ninguna modificación sobre los distintos cálculos de la hoja original, me limito a  trazar automáticamente la sombra sobre el gráfico que permite calcular la pérdida por sombras en vez de dibujarlo a mano y evito que aparezcan errores en la página Cálculo_Altura_Azimut.

En Cálculo_Altura_Azimut, líneas 11 y 12,  se puede producir el error de dividir por cero. La gestión del error se puede hacer preguntando si el divisor es cero (si c7 =0  para ATAN2(C8;C7)) , pero en este caso para gestionar el error utilizo la función EsErr().

=SI(  ESERR(GRADOS(ATAN2(C8;C7))+$C$4);"";GRADOS(ATAN2(C8;C7))+$C$4)

No modifico ni replanteo ni cuestiono de ninguna manera la formula utilizada por el autor original de la hoja.

Las sombras las producen objetos reales, tales como edificios, árboles, chimeneas , etc.. No todos los puntos de  un  objeto tridimensional producen sombra, por lo que solo hay que poner las coordenadas de aquellos puntos que si la producen. El autor original solo considera aquellos puntos que están por encima del suelo (altura=0). Yo añado en la hoja AUX un primer y un último punto a altura cero, con el fin de definir bien el perfil.

Los valores  XYZ que se toman son los de aquellos puntos que están por encima del suelo, aunque para tener completa el área de sombra hay que conocer los puntos del perfil del objeto con altura cero. Paso a la página oculta AUX los valores de las líneas 11 y 12, añadiendo además  los puntos de altura cero. Estos puntos de altura cero son la proyección, a nivel del suelo, del primer y último punto de la serie. Como último paso utilizo un pequeño truco, consistente en repetir los valores del último  punto utilizado, para no tener que modificar la serie a dibujar.

En la gráfica sitúo como fondo del área de gráfico el dibujo (por no utilizar de nuevo la gráfica) que permite calcular las perdidas por sombra. Ajusto los ejes sobre este dibujo (a veces hay que ampliar los márgenes del dibujo antes de poder utilizarlo como fondo ) y añado la serie (=SERIES(;Aux!$B$1:$N$1;Aux!$B$2:$N$2;1)) que da el perfil de las sombras.


Para insertar la imagen como fondo del gráfico:

1. Pulsamos en el área del gráfico.
2. Formato. 
3. Formato del gráfico seleccionado.
4. Tramas.
5. Efectos de relleno.
6. Seleccionar imagen. Seleccionamos y aceptamos.

Para ajustar los ejes a la imagen:

1. Seleccionamos los ejes, dándoles el valor correspondiente a la imagen. En nuestro caso para Y el valor mínimo es cero, el máximo es 80, unidad mayor 10 y cruza con X en el cero. El eje X tiene un mínimo de -120, un máximo de 120, unidad mayor de 30 y se cruza con Y en el cero.
2. Seleccionamos, pulsando, el área de trazado.
3. Ajustamos, manualmente, el área de trazado al la imagen.

Hipervínculos. Menús con autoformas e imagenes en excel.

Variables o rangos con nombre utilizados:

• Gallina =Animales!$A$1
• Ganso =Animales!$B$1
• Pato =Animales!$A$1
• SbMenu =SubMenú!$A$1

En Excel algunos objetos tienen la posibilidad de tener un enlace, mediante lo que se conoce como hipervínculo, con páginas web externas o con rangos del propio libro excel. Mediante estos hipervínculos internos he preparado un sistema de menús que permite moverse a lo largo de un libro excel. 

Las autoformas, rectángulos, elipses, cuadros de texto, imágenes, celdas y otros objetos tienen la posibilidad de contener un hipervínculo.

Independientemente de los hipervínculos existe la  posibilidad, que no suele utilizarse, de incluir una imagen como fondo de una página, en forma de mosaico, se repite hasta ocupar toda la página. En el paquete zip del icono de arriba he incluido un fichero gif para utilizarlo como fondo de la hoja del menú principal.

Inclusión de una imagen como fondo:

1. Formato
2. Hojas
3. Fondo
4. Seleccionamos la imagen deseada.

Menú principal:

1. Preparamos tres entradas. Las dos primeras nos llevan cada una a una hoja distinta, desde las cuales se puede volver al menú principal y la tercera nos lleva a un submenú.
2. Las tres entradas las creamos mediante  "Wordart".
3. Los vínculos se pueden referenciar mediante una dirección absoluta o mediante la utilización del nombre de un rango. Las dos primeras entradas tienen una referencia absoluta y la tercera utiliza un rango con nombre (SbMenu)
4. Poco después de situar el cursor encima de una entrada aparece un texto alternativo, que si no se ha definido es la dirección del enlace. Este texto se puede incluir al definir el enlace.

Los vínculos se definen mediante:

• Botón derecho del ratón sobre el objeto. Agregar o modificar hipervínculo.
• Para este caso seleccionamos, en la izquierda del desplegable,"Lugar en este documento".
• Seleccionamos el rango de referencia o el nombre del rango.
• Incluimos el texto alternativo, si así lo consideramos.

Submenú con imagen de fondo:


En realidad es un pequeño truco, consistente en situar varias autoformas sobre la imagen, cada una con su correspondiente vínculo. Posteriormente volvemos trasparentes esas autoformas, aunque en este caso solo he acabado el proceso para el ganso.
En el submenú con animales de granja he puesto una elipse sobre la gallina y una forma libre tanto sobre el pato como sobre el ganso. La referencia de los vínculos, como era previsible, son los nombres de rango gallina,ganso y pato.
La invisibilidad de una autoforma se consigue:

• Botón derecho del ratón.
• Formato de la autoforma.
• Colores y líneas.
• Color: Sin relleno.
• Línea: Sin línea.

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)