Diseño de escaleras.

Esta vez, como siempre, estoy liado con mi casa de pueblo y necesito construir una pequeña escalera, poco mas de 60 centímetros de altura. Me vale el modelo mas sencillo, tipo la de la foto. Hay un método gráfico que permite dibujar el perfil de una escalera de este tipo. He preparado un excel que calcula y dibuja el perfil de este tipo de escaleras y nos da los puntos a tener en cuenta sobre la zanca (lateral de la escalera).
Esto nos permite ejercitarnos en el manejo de gráficas xy con rango variable y el nos permite refrescar algunos conceptos de  trigonometrìa en excel.

Terminolgía: (Buscada en Internet)
Huella : Ancho del peldaño.
Contrahuella o tabica: Altura del peldaño.
Zanca: Lateral de la escalera. Entre dos zancas se colocan los peldaños.

Variables y rangos con nombre utilizados:

Altura =Datos!$A$2
APeld=Datos!$H$2
CosA=Tabica/Hipo
* GrafX=DESREF(Calc!$A$13;0;0;1;2*NE+1)
* GrafXR=DESREF(Calc!$A$19;0;0;1;2*NE+1)
* GrafY=DESREF(Calc!$A$14;0;0;1;2*NE+1)
* GrafyR=DESREF(Calc!$A$20;0;0;1;2*NE+1)
GZanca=Datos!$I$2
Hipo=RAIZ(Huella^2+Tabica^2)
Hipotenusa2=RAIZ(Tabla^2+Hipo^2)
Huella=Datos!$B$2
Long=Huella*NE
MaxY=Tabla!$B$6
NE=Datos!$C$2
SenA=Huella/Hipo
Tabica=Altura/NE
Tabla=Datos!$F$2
* TabX=DESREF(Tabla!$B$2;0;0;1;2*(NE+1)+1)
* TabXR=DESREF(Tabla!$B$4;0;0;1;2*(NE+1)+1)
* TabY=DESREF(Tabla!$B$3;0;0;1;2*(NE+1)+1)
* TabYR=DESREF(Tabla!$B$5;0;0;1;2*(NE+1)+1)
Xi1=Calc!$C$2
Yi1=Calc!$D$2

* Rangos variables en gráficos.

El perfil de la escalera sobre la zanca se puede dibujar mediante este método gráfico:

• Sobre la tabla que hace de zanca, o sobre un papel o dibujamos la huella y su perpendicular, la tabica o contrahuella y trazamos la hipotenusa del triangulo rectángulo formado por ellas.
• Llevamos esa distancia al lateral de la tabla, una vez por escalón. En el dibujo son las distancias AB, BC, CD, etc.
• Desde B,C,D,E, con un compás o con una cuerda trazamos un arco de radio igual a la tabica. 
• Toda esta operación con el compás  nos la podemos ahorrar dibujando y recortando el triángulo definido por la huella y la tabica y llevando sucesivamente los tres vértices a la tabla para cada escalón
• Desde A,B,C,D con un compás o con una cuerda trazamos un arco de radio igual a la huella.
• Las distintas  intersecciones, junto a los puntos A,B..., nos definen los peldaños. Unimos A-I1-B, B-I2-C, etc
• Prolongamos la recta A-I1 hasta el otro lado de la tabla que forma la zanca, hasta A'. El trozo de tabla por debajo de esa línea se puede cortar o utilizar como anclaje al suelo.
•  Prolongamos la recta, desde el último punto, en este caso E-I4 hasta el otro lado de la tabla que forma la zanca. El trozo de tabla a la derecha o por encima  de esa línea se puede cortar o utilizar como anclaje a la pared.
• Si lo dibujamos escala, comprobaremos que la distancia EG se corresponde con la altura de la escalera. La distancia AG se corresponde con huella*NE, donde NE es el número de escalones.

El libro Excel no supone un gran avance respecto al método gráfico de diseño de escaleras, aunque siempre facilita algo el trabajo. El libro  está pensado para ejercitarse en gráficos Excel con rango variable, en la rotación de ejes y en el manejo de algunos conceptos de trigonometría:

Gráfico PerfilZanca. Este gráfico requiere unos cálculos, que realizo en la hoja "Calc".

•  La hipotenusa del peldaño la calculo directamente por Pitágoras en la variable Hipo. 
• Yo suelo utilizar para gráficos XY los valores de X e Y en columnas, pero e este caso los he colocado en línea.
•  Los valores de Y (línea 14, hoja Calc) se corresponden con los valores de Y para los puntos A,B,C... , intercalados con los valores de Y para I1,I2,I3,... . Conocido el primer valor de Y  hay mas que sumar  a ese valor,la constante Hipo. Los primeros valores de Y son: 0, Y de I1, Hipo, este tercer valor mas YI1, tercer valor mas Hipo y así sucesivamente
• Los valores de X (línea 13, hoja Calc) son alternativamente 0 y el valor que corresponda con el X de I1.
• Preparo las variables  GrafX y GrafY, que  las utilizo  para dibujar el diente de sierra de los peldaños, en este caso primera serie: =SERIES(Aux!$A$2;Escalera.xls!GrafX;Escalera.xls!GrafY;1)
• La serie de datos que dibuja la supuesta zanca la he colocado en la hoja Tabla, rango B9:F10. Lo único  resaltable es la utilización de un rango variable con nombre dentro de la función MAX(GrafY).
• Los datos de la tercera serie, los limites, están en la hoja Calc, rango A16:C17. Os invito a que os peleéis la fórmula matemática.
• La presentación de un gráfico XY, o al menos de este tipo de gráfico, conviene que las unidades para X tengan aproximadamente el mismo tamaño que las unidades de Y. Para forzar esta igualdad de tamaño introduzco dos series de un solo punto, uno situado en el eje X y otro situado en el eje Y, con el mismo valor, valor que se corresponde, para este gráfico, con el valor máximo del rango GrafY (hoja Tabla, celdas B6 y B7)
• Una vez incluidas las series modifiqué el tamaño del área de trazado para que ambos ejes fuesen aproximadamente del mismo tamaño, que el valor máximo de ambos ejes fuese automático y los demás temas relacionados con el aspecto del gráfico.

Gráfico "Alzado": Representa el perfil de las escalera una vez puesta en su sitio. Son los mismos datos y las mismas series que aparecen en el gráfico "PerfilZanca" pero rotados. Las formulas para calcular los nuevos valores de X e Y de una rotación de ejes cartesianos se pueden encontrar facilmente en internet. En este caso el giro de ejes es según las agujas del reloj. El algoritmo es similar al gráfico anterior, pero con los valores rotados (GrafXR, GrafYR, valores tabla y valores truco) En este caso el valor  máximo de las dos series, digamos truco, pude ser el valor máximo de la Y rotada o el valor máximo de la X rotada. Para calcular este valor máximo utilizo la formula:
=ENTERO(MAX(MAX(B4:L4);MAX(B5:L5))/10)*10+10
En donde además de calcular ese máximo hago un redondeo y un incremento de 10. Este máximo, que es el valor máximo de dos rangos no consecutivos tenía que haberlo calculado con :
MAX(B4:L4;C5:L5)

Edición de una línea en modo ficha. Interesante pero, de momento, fallido método.

El objetivo del trabajo es el de presentar y editar el contenido de una línea de excel en una ficha. Como aun estoy en los preliminares de este trabajo, para desplazarme por las distintas líneas del área de datos utilizo una barra de desplazamiento, aunque también se puede acceder directamente a una línea determinada. El trabajo funciona correctamente, es decir presenta los datos existentes, permite modificarlos y recoge las modificaciones realizadas hasta el momento en que se guarda y se cierra el libro. Al volver a abrir el libro ya no funciona, hay que reescribir la dirección de la celda vinculada para que vuelva a funcionar.

Para realizar la ficha utilizo cuadros de texto del cuadro de controles y para vincular los distintos cuadros de texto con una celda variable utilizo unos rangos con nombre, insertar->nombre->definir Campo1 =INDIRECTO("datos!$a$" & Nr), donde Nr es el número de línea. He probado otras maneras de referenciar la celda vinculada, como en campo8b (=INDICE(Lin;8)) y campo8c (=INDIRECTO("datos!$h$" & Aux!$A$1)). He utilizado la función Desref, pero al recargar el libro, el mecanismo ya no funciona, para que funcione de nuevo hay que volver a escribir la propiedad LinkedCell del cuadro de texto.

Las variables siguen funcionando, como se puede ver en la celda J1, pero el vínculo con el cuadro de texto no. 
Actualmente yo sigo trabajando con el excel de office 2003

Ficheros m3u. Una manera sencilla de hacerlos.

La última vez que leí aquello de "la mejor manera de hacer " me pareció que era una manera de hacerlo bastante mediocre. Por eso no pongo mas calificativos que el de sencilla. El método es sencillo, por lo menos si nuestro windows todavía dispone de los comandos DOS.

Creamos un fichero .bat, con cualquier editor de textos, con el siguiente comando:

dir /b *.mp3 > XXXX.m3u

Lo ejecutamos (con doble click) y nos debe aparecer un XXXX.m3u

Para acabar cambiamos el nombre de XXXX por el que mejor nos parezca.

Para crear el fichero .bat:

• Nos situamos en el directorio con los ficheros multimedia.
• Con el botón derecho del ratón seleccionamos Nuevo->Documento de texto.
• Editamos ese documento, escribimos la línea o líneas de comando. Guardamos y salimos.
• Cambiamos el nombre al fichero de texto por xxxx.bat, donde xxxx es el nombre que nos parezca bien.

Segundas, terceras o enésimas líneas de comando:

• Primera línea. Deseamos incluir ficheros mp3:
dir /b *.mp3 > XXXX.m3u

Segunda línea. Deseamos incluir ficheros wav:

 dir /b *.wav >> XXXX.m3u

Tercera línea. Deseamos incluir ficheros mpg:

mpg:dir /b *.mpg >> XXXX.m3u

A partir de la segunda línea de comando utilizaremos el desviador ">>" en vez de ">". El símbolo > indica que la salida del comando , en vez de verse en la pantalla, debe desviarse a un fichero. El doble "mayor que" indica que la salida se añade  al fichero indicado.

Incrustar objetos en Excel. Hoja de cálculo con música de fondo.

Vamos a incluir música de fondo con el objeto “Windows Media Placer”. Este objeto permite reproducir video clips, y ficheros de sonido.

• Insertamos el objeto. Insertar->Objeto->Windows Media Placer.
• Hacemos visible el Cuadro de Control.
• Entramos en modo diseño, en el cuadro de control.
• Seleccionamos el objeto y pulsamos, en el cuadro de control, Propiedades
• La primera propiedad a resaltar es URL, es el fichero de video, sonido o  imagen a reproducir.
• Salimos de modo diseño.
• Comprobamos que todo funciona según nuestro deseo.
• Volvemos a modo diseño y modificamos la propiedad VISIBLE. Nos permite ocultar el objeto.
• Estas propiedades se pueden modificar, de un modo mas sencillo, seleccionando “personalido”, primera opción de las propiedades del objeto.

Incrustar objetos en Excel

Los objetos incrustables son muchos y variados. Se puede incluir desde documentos Word a video clips pasando por calendarios, documentos pdf, archivos de sonido, documentos de open office, ficheros comprimidos zip  y otros muchos tipos de ficheros. La inserción o incrustación se puede realizar:

Desde la línea de menú, con:

• Insertar->objeto. Una vez seleccionada esta opción se puede crear un nuevo objeto o bien cargar un fichero.
• El objeto puede aparecer como tal o como un icono dentro de la página.
• Dos objetos del mismo tipo cargados como nuevo o cargado como fichero pueden comportarse de manera distinta.
• Para modificar las propiedades de alguno de los objetos cargados a veces es necesario ver el cuadro de controles (Línea de menú ->Ver->Barra de Herramientas-Cuadro de Control)

Desde el Cuadro de Control:

·       Todos los controles son objetos incrustables. Cada uno de estos objetos tiene una serie de propiedades que no tienen porque ser las mismas que las de otro objeto de esta colección.

·       Para seleccionar objetos no propiamente de un cuadro de control (Abajo a la izquierda) Mas controles. Seleccionando después el objeto a incrustar.

El cuadro de control tiene un conmutador que permite seleccionar el modo diseño o el modo activo. En modo diseño el control no funciona como tal.

Línea de Tendencia. Estimación lineal.

En alguna ocasión, por ejemplo ayer mismo, en televisión se puede oír que en este mes de Septiembre se han vendido menos, en este caso coches, que en el mes de Agosto. ¿ Como pueden hacer esas afirmaciones, si aun no ha terminado Septiembre?

Entre otras posibilidades se puede utilizar la estimación lineal, que como su nombre indica, consiste en aproximar las ventas diarias de un periodo a una línea del tipo y=mX+c. Los acumulados de ventas, o de cualquier actividad diaria, tienden a ser lineales.

He preparado un libro excel, partiendo de datos del año 2007, en el que inserto en el gráfico una línea de tendencia y utilizo las funciones ESTIMACION.LINEAL e INDICE para calcular las constantes "m" y "c" de y=mX+c

Funciones utilizadas:

ESTIMACION.LINEAL()

INDICE()

Variables y rangos con nombre:

PDiaMes =FECHA(2007;Tendencia!$D$2;1) Primer día del mes

UDiaMes=DIA(PDiaMes+31-DIA(PDiaMes+31)) Último día del mes

UDM_3=UDiaMes-3 Último día del mes menos tres

NV_3=DESREF(Tendencia!$I$2;0;0;UDM_3;1) Ventas del mes  menos tres últimos días.

DM_3=DESREF(Tendencia!$J$2;0;0;UDM_3;1) Días del mes menos tres.

Utilizo variables y rangos con nombre, desplegables con rango de entrada variables y gráficos con rango variable.

Supuesto: Partiendo de los datos acumulados de los primeros n-3 días del mes (n=número de días del mes) hay que calcular el estimado a fin de mes.

Parto de unos datos reales del año 2007, columnas A y B de la página ventas. Preparo un desplegable con los meses con datos del año 2007 y paso los datos de los n-3 primeros días de ese mes a la columnas G (G2:G32) y H (H2:H32) de la hoja Tendencias. 

La selección del mes, las variables con nombre, gráficos con rango variable, y en general todo aquello relacionado con el manejo de datos lo he explicado con anterioridad, no es necesario repetirlo.

El supuesto consiste en estimar los tres últimos días del mes.  La estimación es lineal, por lo que hay que calcular una pendiente (m) y una constante ("c"). Además de estimar estos datos les doy continuidad  en los gráficos, tanto para la línea de tendencia como con los datos reales. Para dar continuidad a la línea tendencia utilizo directamente los valores que calcula la función Estimacion.Lineal (Tendencias!o2), pero para dar continuidad a la serie con los datos calculo la constante "c" que iguala la estimación con el último dato de la serie ((Tendencias!p2).

La función Estimacion.Lineal devuelve una matriz, para recuperar los valores devueltos hay que utilizar también la función INDICE:

=INDICE(ESTIMACION.LINEAL(NV_3;DM_3;VERDADERO;FALSO);1)
=INDICE(ESTIMACION.LINEAL(NV_3;DM_3;VERDADERO;FALSO);2)
etc..

Búsquedas en Google

En Google podemos hablar de tres tipos distintos de búsqueda, la normal, la literal y la avanzada.

Normalmente para realizar una búsqueda  en Google basta con escribir en la línea o casilla de entrada las palabras que se desean encontrar. En este tipo de búsqueda Google encuentra aquellas páginas web que contienen TODAS las palabras buscadas, aunque no necesariamente en el orden en el que están escritas. Si buscamos Juan Garcia podemos encontrar Juan Pérez, José García o incluso  José García, Juan Pérez. 

En las búsquedas no es necesario incluir los acentos. 

Búsqueda literal: Con este tipo de búsqueda pretendemos encontrar aquellas web que contienen el texto escrito TAL Y COMO LO HEMOS ESCRITO y en el orden en el que lo hemos escrito. Para ello hay que escribir el texto buscado entre comillas. Si buscamos "Juan Garcia" encontramos Juan García

Búsqueda avanzada: Es la mas completa de las tres. Permite:

1. Incluir o encontrar un texto excluyendo otro. Es decir en las páginas web encontradas aparece el texto buscado pero no aparece el texto que no queremos. Por ejemplo, si buscamos un personaje histórico, persona muy importante en la historia de una determinada zona, lo normal es que entre varias biografías  o hechos, mas o menos reales, del personaje aparezcan distintos bares, hoteles, hostales que han tomado su nombre y que en principio no nos interesan. Las palabras que no nos interesan , que queremos excluir, deben escribirse precedidas de un signo menos (-bar, -hostal, etc..)
2. Encontrar páginas web que contengan al menos una del las palabras escritas. Para ello hay que escribir los textos separándolos con la palabra OR, escrita en mayúsculas.
3. Combinar las opciones entre si, una o mas palabras fijas, o un literal con una función OR. ("capitulo 1"  OR "Capitulo 2" libro,  "capitulo 1"  OR "Capitulo 2" -libro )
4. En la parte baja de la página de búsquedas de Google hay un enlace a la página de búsquedas avanzadas. Desde esta página podemos filtrar por idioma, por región o país, por fecha de actualización, por tipo de fichero, por el sitio de la página donde aparece el texto, etc..

Como es lógico, al establecer un filtro podemos eliminar alguna página de interés.

Ejercicios propuestos:

Vamos a buscar, salvo para la función OR, a Suero de Quiñones, que es una calle de mi barrio y un personaje histórico leonés.

1.     Escribimos: suero de quiñones (en minúsculas). Analizamos las páginas encontradas. Vemos que, entre otras, nos aparecen la calle de mi barrio, un hostal y un camping.

2.     Escribimos: suero de quiñones -hostal. Analizamos lo encontrado. No debe aparecer el hostal.

3.      Escribimos: suero de quiñones -camping. Analizamos lo encontrado. No debe aparecer el camping.

4.     Escribimos: suero de quiñones -hostal -camping -calle. Analizamos lo encontrado. No debe aparecer ninguno de los tres.

5.     Escribimos: suero quiñones. Analizamos.

6.     Escribimos: “suero quiñones”. Analizamos.

7.     Escribimos: “suero de quiñones”. Analizamos.

8.     Escribimos dos palabras que difícilmente van a aparecer en un mismo texto: margarina OR pedaleo. Analizamos lo encontrado. Como parece, esta búsqueda no tiene utilidad ninguna, salvo conocer y ver el uso de OR . Una búsqueda útil puede ser, por ejemplo, ¿Como se llamaba ese grupo de montañeros, raposos plateados o plateaos ? 

Escribamos: camino smith OR schmid. Analizamos. Buscamos el enlace a Búsqueda Avanzada y vemos y analizamos.

9.     Buscamos al final de la página el enlace con la búsqueda avanzada y de una manera libre probamos las distintas opciones.

10.  Repetimos los pasos anteriores con un texto de libre elección.

La búsqueda avanzada incluye otros comandos que se pueden escribir directamente en la línea de búsqueda, pero pienso que  es mucho mas sencillo ejecutar el filtro directamente en la página de búsqueda avanzada.

Relativos a su posición en el texto:

allintitle:  margarina OR pedaleo

allintext:  margarina OR pedaleo

allinurl:  margarina OR pedaleo

allinanchor:  margarina OR pedaleo

Relativo al tipo de fichero:

margarina OR pedaleo filetype:pdf

Relativo al sitio o dominio:
raposos plateaos site:wikiloc.com

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)