Cálculo del área de una superficie, conocidas las coordenadas de su perímetro, con Excel. Utilizando nuestras propias funciones Excel.

Se trata de calcular con Excel el área de una superficie, conocidas las coordenadas de su perímetro utilizando las fórmulas de Haversine y de Herón. 

• La fórmula de Haversine nos da la distancia entre dos puntos de la geografía terrestre, conocidas las coordenadas de ambos puntos. Esta fórmula se convierte en una función propia para Excel desarrollada en VBasic.
• La fórmula de Herón nos da el área de un triángulo conocidas las longitudes de sus lados. También la utilizamos como "nuestra propia función" convirtiéndola a VBasic.
• Podemos desarrollar, para utilizarlas en excel, nuestras propias funciones en VBasic. Una vez desarrolladas estas funciones se utilizan como cualquier otra función de excel. Nombre de la función y parámetros.
• "Nuestras funciones" se incluyen en el código VBasic de excel.

Una vez descargado el libro excel, debemos autorizar la ejecución de macros (el código VBasic).

Para calcular el área:

• Tomamos n puntos del perímetro de la superficie a medir con el GPS o con cualquier aplicación que nos permita consultar y obtener las coordenadas de los puntos que consideremos precisos para una correcta medida del tamaño de una superficie.
• Promediamos esos puntos con el fin de encontrar un centro geográfico de ellos, aunque este centro podríamos moverlo a voluntad (siempre que se pueda decir que es un centro)
• Dividimos la superficie en los distintos triángulos que forman los lados entre dos puntos consecutivos y esos dos puntos con el centro.
• Primer triángulo, lado formado por el primer y segundo punto, el lado que se forma entre el primer punto y el centro, el lado que se forma entre el segundo punto y el centro.
• Primer triángulo, primer y segundo punto, centro. Segundo  triángulo, segundo y tercer punto, centro.
• Último triángulo, ultimo punto, primer punto y centro.

Tengo que comprobar mi trabajo teórico. Busco un sitio conocido, del que pueda encontrar información facilmente. Me decido por el área de juego del terreno del Real Madrid.

Con Google maps tomo las coordenadas del área de juego del campo del Real Madrid, las cuatro esquinas, en donde van los banderines de los corners. Intento ser lo mas preciso posible.

En Wikipedia, consigo enterarme de la dimensiones teóricas del área de juego del Real Madrid, 105*68 metros. 

Según mis cálculos sale, para los lados largos, que uno mide 105,2 y el otro 104,9 metros. Para los lados cortos uno mide 67,76 y el otro 68,04 metros. Bastante ajustado al valor teórico.

Variables o rangos con nombre utilizadas en el libro excel:

• NPuntos. Es el número de puntos tomados.
• XCentro: Longitud (coordenadas) del centro.
• YCentro: Latitud del centro de las coordenadas tomadas.

Si no queremos utilizar programación VBasic, tanto la distancia entre dos puntos (Haversine) y el área de un triángulo (Herón) se pueden calcular con las funciones propias de excel.

Fórmula de Haversine en VBasic:

Function DGeo(Lat1, Lon1, Lat2, Lon2)

Dim GaR, R, Lat1R, Lat2R, Lon1R, Lon2R

DGeo = -1

On Error Resume Next

'57.29577951 para convertir grados a radianes

'6378137 Radio de la tierra

GaR = 57.29577951

GaR = 57.2957795130823

R = 6378137

'R = 6397000

'R = 6371000

'6,371.0 km

Lat1R = Lat1 / GaR

Lat2R = Lat2 / GaR

Lon1R = Lon1 / GaR

Lon2R = Lon2 / GaR

'DGeo = R * Atn(Sqr((1 - (Sin(Lat1 / GaR) * Sin(Lat2 / GaR) + Cos(Lat1 / GaR) * Cos(Lat2 / GaR) * Cos(Lon2 / GaR - Lon1 / GaR)) ^ 2)) / _

(Sin(Lat1 / GaR) * Sin(Lat2 / GaR) + Cos(Lat1 / GaR) * Cos(Lat2 / GaR) * Cos(Lon2 / GaR - Lon1 / GaR)))

DGeo = R * Atn(Sqr((1 - (Sin(Lat1R) * Sin(Lat2R) + Cos(Lat1R) * Cos(Lat2R) * Cos(Lon2R - Lon1R)) ^ 2)) / _

(Sin(Lat1R) * Sin(Lat2R) + Cos(Lat1R) * Cos(Lat2R) * Cos(Lon2R - Lon1R)))

On Error GoTo 0

End Function

Fórmula de Herón en VBasic:

Function AreaHeron(a, b, c)

s = (a + b + c) / 2

  AreaHeron = Sqr(s * (s - a) * (s - b) * (s - c))

End Function

Tabla de Colores HTML pasada a colores Processing mediante Excel.

 

Abriendo la página con los colores HTML directamente con Excel:

Convirtiendo los colore HTML a código Processing:

Pasando los colores a Processing:

Las cosas no siempre salen a la primera:

Resolución de Integrales definidas con Excel. Máximos y mínimos con Excel.

 La última vez, que yo recuerde, que hice una derivada o una integral fue el siglo pasado, terminé mis estudios y no volví a tocar este tema. Hace muchos, muchos, muchos años de eso.

Hace unos días recibí en un grupo de WhatsApp una bromilla con una integral definida. Ni que decir tiene que en estos momentos mi capacidad de resolver esa integral es nula (me da la impresión que ni en mi mejor época hubiera sido  fácil, para mi, resolver la integral).

A falta de conocimientos podría haber acudido a internet en busca de una solución. Hay muchos sitios en internet que resuelven, al menos eso dicen, cualquier integral, pero no, a mi me vino a la memoria que una integral definida es el área que ocupa una función y=f(x), con el eje X, entre dos valores definidos.

Vídeo encontrado en youtube, sobre integrales definidas. No soy yo.

 Este área se puede aproximar a un sumatorio de áreas rectangulares (ΣY*DX), en donde ΔX, incremento de X, es un valor todo lo pequeño que sea posible. Este sumatorio es una suma de cientos de sumandos, mejor de miles, que hasta la aparición de las hojas de cálculo, era, si no imposible de hacer sin programación, muy complicado. Un trabajo idóneo para Excel, una suma de miles de operaciones.

Fichero Zip con los Excel

Empiezo buscando un ejemplo mas sencillo que la función propuesta, en este caso empiezo por y=x, con un ΔX=1, rectángulos realmente grandes, pero que para y=x se puede obtener un valor exacto de la integral, solo hay que tratar correctamente los errores por mas y por menos que nos da el área de los rectángulos.

Area de la función Y=X aproximada a una suma de rectángulos. En verde de mas a menos. En marón de menos a mas.

Funciones con nombre del libro excel (Fórmulas->Administrador de Nombres):

• DX= Datos!A2
• LimInf=Datos!$A$6
• LimSup=Datos!$A$8
• ValBus=Datos!$A$4
• ValX=Datos!$B:$B
• ValXR=DESREF(Datos!$B$1;Datos!G5-1;0;50;1)
• ValYR=DESREF(Datos!IO65536;0;0;Datos!H5-Datos!G5;1)

Dibujamos la función recibida por WhatsApp: 

• Primero calculamos los distintos valores X. Los colocamos, libro MaximosYMinimosI..., en la hoja datos, columna B:B. 
• Preparamos los valores de X para que cubran un poco por debajo del limite inferior de la integral y poco por encima del límite superior de la integral. Como dicen en las recetas de cocina "al gusto".
• Lo podemos hacer, tomando como referencia el eje X, de izquierda a derecha (X0+ΔX), de menos a mas o de derecha a izquierda, de mas a menos (X0-ΔX). 
• En Excel calculamos los distintos valores de X, con ΔX >0, B3=B2+ΔX  para incrementar la X, que es el que utilizo realmente,  o  de mas a menos B3=b2-ΔX, que lo contemplo como otra posibilidad.
• En B2 va uno de los valores de los limites de la representación gráfica de la función.
• Con los valores de X calculamos los valores de Y. En este caso, hoja "Datos", calculo numerador y denominador por separado. No es necesario hacer esta separación, sencillamente simplifica algo el paso a excel de la función.
• Estudiamos, por encima, la función. Tampoco es del todo necesario, nos permite prever que tipo de errors nos puede dar la función.
• La función recibida por WhatsApp, a simple vista, no es continua, tiene unos puntos de indeterminación. 
• Tiene valores de X que dan error. El denominador tiene un valor que vale cero y un intervalo que es la raíz cuadrada de un número negativo.
• Para X=1 o X=2 la función da un error, el divisor vale 0 √(X²-3X+2) o es la raíz de un número negativo. 
• Para X >1 y X<2 da negativo, raíz cuadrada de un número negativo.
• Este tipo de discontinuidades las podemos controlar fácilmente en Excel con las funciones EsErr() y Si() (=SI(ESERR(C2/D2);0;C2/D2)).  Damos el valor que deseemos, en este caso 0, que no incrementa el área en el caso de que la discontinuidad se de entre los límites de la integral.
• Una vez definido ΔX ¿Cuantas áreas debo considerar? ¿Cuantas mas, mejor? Puede que no, excel comete errores con números excesivamente pequeños.
• De menos a mas empiezo en el valor mínimo y termino en el valor máximo menos ΔX.

Llevamos los valores de X e Y a una gráfica tipo XY (Dispersión). Ya podemos ver la función, tenemos una gráfica y una relación de valores de X e Y que nos permite, entre otras cosas, encontrar máximos y mínimos relativos (esos que se encuentran derivando) y encontrar los valores de X que me dan un determinado valor de Y.

Máximos y mínimos relativos: 

• Un mínimo relativo se produce cuando su valor es menor o igual a los valores de los puntos anterior y posterior. (Hoja Datos,columna I, =SI(Y(E3<=E2;E3<=E4);1;0))
• Un máximo relativo se produce cuando su valor es mayor o igual a los valores de los puntos anterior y posterior.(Hoja Datos, Columna J,=SI(Y(E4>=E3;E4>=E5);1;0)
• En la columna N (N1 a N6) de la hoja datos encontramos el primer mínimo.
• En la columna O (O1 a O6) de la hoja datos encontramos el primer máximo.
• Llevamos estos valores a la gráfica apoyándonos en la hoja "Aux".

Búsqueda del valor de X que da un determinado valor de Y:

• Buscamos en los valores de la función los puntos anterior y posterior cuyas Y o coinciden o limitan con el valor buscado de Y.
• Este tema, búsquedas sucesivas hasta encontrar un valor ya lo trate anteriormente AQUÍ
• Basicamente, mi búsqueda por aproximaciones sucesivas ,consiste en, para tramos crecientes de una función,  empezar con un par de valores de X conocidos, X₁ y X₂, que hacen que una de las Y sea menor que el valor buscado y que la otra sea superior.
• Tomamos el valor medio de X₁ y X₂, Xm, y lo llevamos a la función. Si el valor obtenido de Y es igual o mayor al valor buscado entonces X₂=X_m. Si no X₁=X_m
• Repetimos la operación, encontrar la media de X₁y X₂, llevarla a la función, encontrar la Y correspondiente y volver a comparar, las n veces que sean precisas, hasta encontrar el valor de X que nos da el valor de y buscado.
• Para tramos decreciente intercambiaríamos X₁ y X₂.
• En este caso la búsqueda la vamos a hacer en dos pasos, primero encontramos un par de puntos entre cuyos valores está el valor buscado. 
• A continuación, en la hoja BuscaX, buscamos sucesivamente el valor de X que nos da un determinado Y.

Integral definida:

• Como han explicado en el vídeo de youtube, las áreas de los distintos segmentos de la función, de los rectángulos, ΔX*Y, salen con signo.
• No nos es necesario saber este signo, para calcular el área total debemos sumar el valor absoluto de las áreas calculadas. 
• Hay un pequeño error al calcular el área total, depende de ΔX, de la forma de la función y si vamos de mas a menos o de menos a mas.
• En principio he visto que si el valor absoluto  anterior de Y es superior al valor tratado de Y, el error incrementa la suma y si es inferior decrementa la suma. (ver libro ErrorCometido.xls o la gráfica1)
• Cuanto mas pequeño es ΔX, mas pequeño es el error cometido ,pero es mas probable que Excel no calcule todo lo exacto que deseemos. Excel también comete errores. (Vease la columna de las X, con trece decimales o mas)
• Para ΔX no demasiado pequeños, calculando en ambos sentidos, de menos a mas y de mas a menos y promediando ambos valores se supone que los errores se compensan.
• El error cometido puede llegar a compensarse por si solo, dependiendo de la forma de la función.
• Por lo que parece, el error cometido yendo de menos a mas es idéntico, pero de signo contrario al cometido yendo de mas a menos. 
• Si ΔX es lo suficientemente pequeño el error cometido puede ser considerado despreciable.
• En el libro "MaximosyMinimosIntegralDefinida.xls" he separado los distintos cálculos, incluido el tratamiento de errores. 
• En la columna H están  los valores absolutos de Y*ΔX. Hay que sumar desde x=límite inferior de la integral hasta x=límite superior -ΔX.
• Obtenemos un valor casi idéntico, muy aproximado, al valor obtenido al integrar.

En el libro MaximosYMinimosInt.... no he quitado ni el estudio de otras funciones ni la programación vbasic que calcula el valor de diversas funciones (alguna de ellas, como la función seno sin terminar).

En el libro ErrorCometido también mantengo la programación vbasic.

220v de corriente alterna. De donde salen esos 220V.

Recordando o aprendiendo algunas cosas sobre CA:

• Una de las pocas cosas que se recuerda sobre CA es que es de forma sinusoidal, y que tiene una tensión máxima que es el resultado de multiplicar el valor nominal por raíz de dos.
• En general se puede decir que la CA en Europa (220v) va a 50 ciclos/seg y en America va a 60 ciclos/seg.
• ¿De donde salen los 220v, valor nominal, de una CA? Es la tensión continua equivalente, la que entrega la misma potencia que la corriente alterna. Se puede demostrar, si te acuerdas de como se integra, o mediante excel.

Variables con nombre:

• Ancho: =Aux!$Q$2
• Fr:=Config!$B$2. Frecuencia de la red eléctrica.
• Periodo:=Config!$C$2 o inverso de la frecuencia.
• VMax:=Config!$B$4*RAIZ(2) O tensión máxima
• VolNon:=Config!$B$4 Tensión nominal.(220)
• Hoja Config: Configuración de la red eléctrica.

• Recordemos, potencia entregada es V²/R.
• Tanto integrando como en excel la cosa consiste en hallar  el área que ocupa la función seno². El área es el valor de seno², en el eje y, y un pequeño dx (base por altura).
• En excel se trata de sumar un número elevado de veces las pequeñas áreas que definen la función y un diferencial de x.
• Para calcular la tensión que produce una potencia equivalente: ∑V_i²/R = V²/R ==> ∑V_i²= V²
• En la hoja Aux del libro, columnas F,H,I puede verse los sucesivos incrementos de x, el total del sumatorio y el voltaje equivalente.

Conversión hora UTC.

En las últimas semanas me he encontrado varias veces con la hora UTC, mencionada como tal o en una especie de limbo de los datos, un chorro de números sin explicación.

La hora UTC es, mas o menos, la hora del meridiano de Greenwich, es la "hora universal coordinada". Curiosamente también se la puede encontrar como "hora zulú". Es el horario de referencia, no incluye el desfase debido a los distintos husos horarios ni los horarios de verano/invierno. En mi caso, España está (aunque no debería estar) en el huso CET (hora de centro Europa), lo que supone un adelanto de un par de horas en verano y una sola en invierno.

Cuando hacemos una foto con una cámara digital medianamente moderna, esta nos añade una cabecera (datos exif)  con los datos técnicos propios de la foto, la fecha y la hora local y, si la cámara tiene GPS, normalmente las que hacemos con el móvil, la ubicación. Para que añada la ubicación tenemos que permitirlo ajustando las propiedades de nuestro móvil. Podemos ver estos datos pulsando con el botón derecho del ratón sobre la imagen y seleccionando propiedades. Yo tengo instalado un pequeño programa gratuito para Windows, el PIE, que nos permite consultar todos estos datos sobre las imágenes de un directorio.

Cabecera Exif. Puede apreciarse varias veces la fecha local.

Trasteando, algunas de estas propiedades se pueden ver con cualquier editor de textos, otras no. Yo suelo utilizar Notepad++, pero se puede utilizar el bloc de notas de windows o cualquier otro editor de textos. La foto hay que abrirla como texto, no como imagen. Trasteando, encontré en algunas fotos, en las últimas líneas , lo siguiente:

   Image_UTC_Data1566296945314

Cola con los datos UTC

Bueno, pues el chorro de números que hay después de UTC_DATA es una fecha. Me diréis ¿*******?, por lo menos, pero lo es, es una fecha. No es una fecha con un formato facilmente reconocible, como por ejemplo las fechas incluidas en los ficheros GPX, que también, a mi, me aparecen como fecha UTC, con un formato como 2015-12-10T08:56:27Z (AAAA-MM-DDTHH:mm:SSZ)

• Recordemos que el sistema de fechas en excel comienza el 1/1/1900 y que cada día equivale a 1. Si ponemos el formato de una celda con una fecha a numérico veremos el numero de días trascurridos desde el 1/1/1900 hasta esa fecha. Si ponemos un uno y le damos formato de fecha veremos 1/1/1900.
• El chorro de números son los milisegundos trascurridos desde las cero horas del 1/1/1970 hasta el momento en el que se hizo la foto.
• Abrimos un Excel y copiamos los números (1566296945314) en la celda A2.  En b2 ponemos la siguiente fórmula

                          *                                **                 

• =A2/86400000+FECHA(1970;1;1)

• *86400 es el número de segundos que tiene un día, 24*60*60. Como hablamos de milisegundos 86400000. Al dividir por 86400000 pasamos a días (de excel) los milisegundos.
• ** Fecha(1970;1;1):podemos, o incluso lo debemos, sustituir esta función fecha() por su valor equivalente, 25569.
• No todas las fechas del tipo "segundos trascurridos desde" están referenciadas al 1/1/1970. Algunas están referenciadas al 1/1/1900 (Red Iris con Arduino).
• Damos formato de fecha a la celda y vemos la fecha como fecha. Debemos tener en cuenta el desfase por huso horario y horario de verano.

Con los datos de los GPS OruxMaps o IGN, GPS para móvil, :

• Trasteando se, como salgo al campo, y utilizo OruxMaps como GPS, se que OruxMaps guarda los datos en una base de datos Sqlite, oruxmapstracks.db. 
• Si enviamos la B.D a nuestro P.C.,  los datos de esta B.D. se pueden consultar instalando un lector o browser de es tipo de B.D. Yo tengo instalado DBBrowserSQLite.
• Los datos de los caminos seguidos están en la tabla trackpoints. Uno de los campos, el campo trckpttime también, al menos en mi caso, además de ser una fecha UTC  es el número de milisegundos trascurridos desde el 1/1/1970.
• Abrimos la tabla, copiamos las fechas a una hoja de cálculo y aplicamos la fórmula. Damos formato de fecha a las celdas y vemos las fechas.

Zip con datos

Redireccionar salida de los comandos DOS a fichero de texto. Eñes y otros caracteres.

No es que lo utilice mucho pero de, vez en cuando, utilizo alguno de los viejos comandos DOS. Últimamente he  preparado un par de trabajos en excel: 

• En uno de ellos recojo, por un lado, todos los ficheros mp3 que tengo en mi PC para copiarlos en una tarjeta SD, siguiendo los criterios necesarios para utilizarlos con una DFPlayer mini. Utilizo excel como elemento auxiliar para preparar un ejecutable .bat que cree los directorios necesarios y que realice la copia final.
• En el otro trabajo quiero controlar todas las fotos que tengo en mi colección de fotos.
• Para encontrar todos los ficheros de un determinado tipo utilizo el comando Dir del viejo DOS.
• Me interesa conocer la fecha y tamaño de los ficheros, por eso utilizo el formato largo.
• Cuando abro una ventana con un terminal DOS y lanzo el comando Dir *.jpg /s (desde el directorio de mi colección de fotos) tanto las eñes como las vocales acentuadas salen correctamente, las eñes se ven como eñes y las vocales acentuadas como vocales acentuadas.
• Si redirecciono la salida del comando a un fichero de texto, al editar ese fichero de texto tanto las eñes como las vocales no se ven ni como eñes ni como vocales acentuadas.
• Desde un punto de vista funcional, como cuando preparé la copia de los distintos mp3, creando un ejecutable bat para ello, es necesario mantener fichero original sin cambiar los caracteres que sustituyen a las eñes y vocales acentuadas por otros. El fichero BAT resultante va a trabajar con el conjunto de caracteres original. Al cambiarlos se  produce  una sustitución de caracteres en el nombre del fichero que va a impedir su correcto funcionamiento .

• Se puede preparar un fichero bat para copiar los ficheros sin problemas, aunque veamos los caracteres cambiados.
• Si se va a trabajar con otros programas, esos caracteres cambiados si dan problemas.

Le he dado bastantes vueltas a este tema, en realidad es un mero problema de configuración, aunque esté poco claro como solucionarlo. Por tanto se trata de encontrar algo que nos permita cambiar el conjunto de caracteres al conjunto de caracteres del español.

En su momento, antes de WindowsXp, se podía programar el conjunto de caracteres en DOS con Keyb (sp para español y 850 como código). Desaparecido el comando Keyb no encuentro manera de cambiar genéricamente el conjunto de caracteres.

Trasteando, de momento he encontrado un par de soluciones que me permite ver las eñes como eñes y las vocales acentuadas como vocales acentuadas. No es la super solución que todos desearíamos, pero es una buena solución o, al menos, una solución.

Sin instalar nada nuevo en nuestro PC:

Zip con algunos ejemplos

• Parto de una colección de canciones de los años 70, con sus eñes y sus vocales acentuadas. 
• Alguno de los nombres ya tienen en origen las eñes cambiadas por otros caracteres. Estos cambios no se solucionan con este cambio de configuración.
• Redireccionamos la salida del comando DOS a un fichero de texto. En el ejemplo mp3_2.txt.
• Editamos este fichero de texto. Le añadimos, como cabecera, la siguiente instrucción HTML:

<meta http-equiv="content-type" content=text/html   charset=ibm850>

• Con esta instrucción cambiamos en conjunto de caracteres.
• Salvamos el fichero de texto como un fichero HTM (mp3_2.htm). Lo convertimos en una página web.
• Abrimos ese fichero (mp3_2.htm) con Internet Explorer. A mi me funciona solo si se abre con Internet Explorer, con otros navegadores no funciona. 
• Si en otros PC la cosa se comportase como en el mío, se verían las eñes y las vocales acentuadas.
• Con el botón derecho del ratón pulsamos sobre el texto web. Se abre un desplegable, elegimos "Ver código fuente".
• En el código vemos eñes y vocales acentuadas. Salvamos como texto.

Instalando en nuestro PC Libre Office:

• Pinchamos sobre la salida txt del comando DOS con el botón derecho del ratón. 
• Seleccionamos Abrir Con->Libre office Calc.
• En mi PC, al menos, nos presenta un formulario de importación en el que podemos seleccionar el conjunto de caracteres que vamos a utilizar.
• Seleccionamos "Europa occidental (DOS/OS2-850/internacional)
• Podemos aprovechar para separar los campos que nos puedan interesar, o no. Siempre se puede hacer después.
• Salvamos el fichero como xls, html o como odt. Ni los copia pega ni los guardar como texto conservan el cambio de caracteres. No funcionan.

Estadísticas a partir del track de una excursión.

He preparado este trabajo para aquellos que les gusta trastear con el GPS y que les gustaría poder hacer sus propias estadísticas de una ruta. De paso incluyo la posibilidad que tiene excel de crear nuestras propias funciones con VBasic.

• El primer paso consiste en pasar los datos de la ruta al libro excel.
• Hay muchas maneras de hacerlo, desde el copia pega desde BaseCamp (propiedades de la ruta) hasta salvar los datos a un fichero de texto (o un fichero csv) o, incluso, utilizar Ibpindex para obtener esos datos.
• A partir de estos datos puedo conocer directamente los incrementos de altura y de tiempos. Incremento = "Lo que hay" - "Lo que había"
• La distancia recorrida hay que calcularla.
• El rumbo hay que calcularlo.
• Los acumulados de tiempo, distancia y alturas son una suma.

¿Como paso los datos a mi libro excel?

Esta vez voy a utilizar la utilidad que tienen algunos editores de rutas, Oziexplorer, BaseCamp, MapSource y OruxMaps, entre otros, para volcar los datos, coordenadas, alturas y fechas a un fichero de texto (.txt o .csv).

• Oziexplorer, el editor de rutas mas antiguo que conozco, tiene la posibilidad de exportar a texto con varias, y distintas, posibilidades.

• El trabajo que he realizado lo he hecho exportando a texto (csv) un fichero GPX con BaseCamp.

• Abro directamente con excel el fichero csv.
•  Elimino todo aquello que no sea los puntos de la traza.
• Desde datos->Texto en columnas paso cada item a su columna.

• Pasados los campos a su columna copio los datos y los pego en el libro EstadísticasManuales.xls, hoja Datos.

Como mínimo, a partir de fecha, coordenadas y alturas puedo calcular:

• Distancia entre dos puntos y distancia acumulada.
• Tiempo entre dos puntos y duración total.
• Incremento de altura entre dos puntos y acumulados de subida y bajada.
• Velocidad de recorrido. Entre puntos y total.
• Tiempo en movimiento y de parada.
• Eliminar puntos que no cumplen o que cumplen unos determinados criterios. En el ejemplo tiempo mínimo entre puntos, distancia mínima entre puntos y velocidad máxima. Otro criterio que se puede incluir es el de velocidad vertical (de subida o bajada), etc..
• Para calcular tiempos y, dado que depende del tiempo, velocidades, hay que tener presente el sistema de fechas de excel. 

• Si en una celda escribimos una fecha y cambiamos el formato fecha a formato numérico vemos un número en vez de una fecha. Es el número de días trascurridos desde el 1/1/1900. Uno equivale a un día, la fracción de uno es la fracción de día. Este sistema no me acaba de gustar, es bastante imperfecto. 
• Las velocidades, en km/hora se calculan con distanciaEnMetros/(tiempo*24000). 1000 para pasar los metros a km y 24 para pasar los días, o su fracción, a horas.
• Las duraciones, sin embargo, se calculan restando sin mas  las fechas y sus acumulados sumando las duraciones.
• Los incrementos de altura son sencillamente una resta. Altura del punto2 menos la altura del punto1.
• El calculo de las distancias es lo único realmente complejo de todo esto. Hay varias posibilidades, incluida una manera aproximada de calcularla.

Distancia entre dos puntos:

Buscando en internet encontré, hace ya varios años, una fórmula que es la que utilizo desde que empecé a hacer mis propias estadísticas.

La fórmula es bastante compleja, no difícil convertirla a fórmula excel pero queda un formulón,  por lo que creo mi propia función en VBasic a partir de esa fórmula, la funcion DGeo (distancia geográfica), que es mucho mas sencilla de manejar:

Function DGeo(Lat1, Lon1, Lat2, Lon2)

Dim GaR, R, Lat1R, Lat2R, Lon1R, Lon2R

DGeo = -1

On Error Resume Next

'57.29577951 para convertir grados a radianes

'6378137 Radio de la tierra

GaR = 57.29577951

R = 6378137

Lat1R = Lat1 / GaR

Lat2R = Lat2 / GaR

Lon1R = Lon1 / GaR

Lon2R = Lon2 / GaR

'DGeo = R * Atn(Sqr((1 - (Sin(Lat1 / GaR) * Sin(Lat2 / GaR) + Cos(Lat1 / GaR) * Cos(Lat2 / GaR) * Cos(Lon2 / GaR - Lon1 / GaR)) ^ 2)) / _

(Sin(Lat1 / GaR) * Sin(Lat2 / GaR) + Cos(Lat1 / GaR) * Cos(Lat2 / GaR) * Cos(Lon2 / GaR - Lon1 / GaR)))

DGeo = R * Atn(Sqr((1 - (Sin(Lat1R) * Sin(Lat2R) + Cos(Lat1R) * Cos(Lat2R) * Cos(Lon2R - Lon1R)) ^ 2)) / _

(Sin(Lat1R) * Sin(Lat2R) + Cos(Lat1R) * Cos(Lat2R) * Cos(Lon2R - Lon1R)))

On Error GoTo 0

End Function

Esta función, mi función, la función añadida por mi, como fórmula excel queda:

=Radio* ATAN(RAIZ((1 - (SENO(D2*GaR) * SENO(D3*GaR) + COS(D2*GaR) * COS(D3*GaR) * COS(E3*GaR - E2*GaR)) ^ 2)) / (SENO(D2*GaR) * SENO(D3*GaR) + COS(D2*GaR) * COS(D3*GaR) * COS(E3*GaR - E2*GaR)))

Donde:

• Radio=radio de la tierra. Es una variable con nombre.
• De GaR sirve para convertir grados a radianes. Es una variable con nombre. Podía haber utilizado la función RADIANES().
• Columna D es la latitud. 
• Columna E es la longitud.

Entre ambas maneras de calcular distancias hay una pequeña diferencia.

Para distancias cortas he encontrado una manera aproximada de calcular la distancia entre dos puntos, columna I de la hoja "datos2". Empiricamente hay muy poca diferencia para puntos cercanos en metros y bastante diferencia para puntos muy separados. Creo que como estamos trabajando con la traza de una excursión a pie o en bici los puntos están muy cerca unos de otros podemos utilizar la siguiente fórmula:

• =RAIZ(((D2-D3)*M_G)^2+(M_G*COS(D3*GaR)*(E2-E3))^2), que tampoco se puede considerar una formulita.
• Columna D es la latitud. 
• Columna E es la longitud.
• M_G es una variable con nombre. Es el número de metros por grado en el Ecuador de la tierra.

Rumbo: El rumbo es el ángulo que forma nuestra dirección, la dirección que llevamos al movernos,  con el eje norte-sur, con una brújula graduada es fácil de ver. Es una información, también desde mi punto de vista, mas útil en náutica que sobre tierra, en tierra nuestros tramos rectos son pequeños y nuestro camino va por donde va, casi nunca podemos ni elegir el rumbo ni salirnos del camino. Hay varios casos mas en los que el ángulo entre una dirección y el eje norte-sur se puede utilizar, pero no vienen al caso.

• No he conseguido encontrar una fórmula exacta de calcular el rumbo, pero si una fórmula aproximada para puntos cercanos.
• Queda una fórmula compleja, por lo que ni siquiera intento pasarla a excel. Solo hago la fórmula en VBasic.
• En tierra solo he visto utilizar el rumbo para eliminar algunos puntos. Si tres puntos consecutivos tienen el mismo rumbo se puede eliminar el punto central sin perder demasiada información.

Código de Rumbo:

Function Rumbo(Lat1, Lon1, Lat2, Lon2)
Dim D1 As Double, D2 As Double, D3 As Double, ArcoTan As Double
'corregir casos 2 y 4
'111319.491
'Pi = 3.141592654

D1 = DGeo(Lat1, Lon1, Lat2, Lon2)
D2 = DGeo(Lat1, Lon1, Lat2, Lon1)
D2 = Abs(111319.491 * (Lat2 - Lat1))
D3 = DGeo(Lat2, Lon1, Lat2, Lon2)
D3 = Abs(111319.491 * (Lon2 - Lon1) * Cos(Lat2 * 2 * Pi() / 360))
On Error Resume Next

C = 0
ArcoTan = Atn(D3 / D2)
e = Err()
If (D2 > 0 And D3 > 0) Then
If Lat2 >= Lat1 And Lon2 <= Lon1 Then
Rumbo = (2 * Pi() - ArcoTan)

End If
If Lat2 >= Lat1 And Lon2 >= Lon1 Then
Rumbo = ArcoTan

End If
If Lat2 <= Lat1 And Lon2 <= Lon1 Then
Rumbo = Pi() + ArcoTan

End If
If Lat2 <= Lat1 And Lon2 >= Lon1 Then
Rumbo = (Pi() - ArcoTan)

End If
End If

If D2 = 0 Then
Rumbo = Pi() / 2
If Lon1 > Lon2 Then Rumbo = 1.5 * Pi()
End If

If D3 = 0 Then
Rumbo = 0
If Lat1 > Lat2 Then Rumbo = Pi()
End If

On Error GoTo 0
'f = "0." & Left("0000000000000", Dec)
'Rumbo = Format(Rumbo * 360 / (2 * Pi()), f) + 0
Rumbo = Rumbo * 360 / (2 * Pi())
End Function

Function Pi()
Pi = 3.141592654

End Function

En donde Lat1,Lon1,Lat2 y Lon2 son las coordenadas del punto de inicio y del punto de destino.

Cambios de base. De decimal a binario y de decimal a hexadecimal.

Estoy trabajando, en mi blog de cacharreo, con un reproductor de mp3, el DFPlayer Mini y un Arduino Uno. El aparato se puede programar directamente, según la documentación del fabricante, o se puede programar siguiendo alguna de las librerías preparadas al efecto. La muy escasa documentación del fabricante sobre este aparato hace referencia a unos comandos escritos directamente en hexadecimal, aunque en la práctica ese hexadecimal se convierte en una base de ocho bits, los ocho bits de un carácter ascii.

Como apoyo a lo que estoy haciendo, por un lado hablar el mismo idioma (hexadecimal) del pdf del fabricante y por otro trabajar, de hecho, con una base de 8 bits, he preparado un libro excel que me permite estar seguro, antes de entrar en la programación del Arduino, de lo que estoy haciendo.

• La primera conversión, líneas 6 y 7 es la de decimal a binario. En este caso no la utilizo para nada, pero es la base de la electrónica digital.
• Hago una conversión de decimal a hexadecimal en las líneas 21 y 22.
• Hago una conversión de decimal a una base byte, una de dos caracteres u una de cuatro caracteres.
• Por último hago una conversión de decimal a decimal, como comprobación de la idea.
• Normalmente, en nuestro sistema de numeración, el número de menor peso es el de más a derecha y el de mas peso es el de mas a la izquierda.
• Independientemente de la base de numeración, cada una de las unidades  de una cifra vale el número de valores que toma la base de numeración elevado a un exponente, cero el de mas a derecha, uno el siguiente, dos, tres y así sucesivamente.
• Esto es fácil de seguir, partiendo de cero, sumando sucesivamente uno, en binario pasamos de cero a uno y de uno a 10. En decimal pasamos de 9 a 10. En hexadecimal pasamos de 0F a a 10.
• Para pasar de una cifra a otra debemos incrementar n (número base, contando el cero) veces la cifra de la derecha.
• Si continuamos el razonamiento, para llegar a la tercera cifra hemos tenido que incrementar n^2 veces el cero inicial. En decimal 100, tres cifras, es 99 +1, es 10^2 cada unidad de esa tercera cifra vale 10^2. Lo mismo para 1000, cuatro cifras, 999+1, cada unidad de esa cifra vale 10^3.
• Este razonamiento es válido para el resto de las posibles bases de numeración que se nos ocurra.
• En la conversión de decimal a otra base hay que introducir el concepto resto. Vamos a calcular la cifra N (empezando en cero). Debemos quitar el valor de las cifras de mas a la izquierda, a partir de N+1, esto se hace hallando el resto de dividir el valor decimal por la base elevada a N+1.
• El entero de dividir ese resto por la base elevada a N nos da el valor de esa cifra.
• Repitiendo ese proceso hallamos todas las cifras de la conversión.

Esto, quizás parezca un poco lioso, pero el la práctica resulta sencillo.

Copia y renombre masivo de ficheros con apoyo en Excel. Preliminar.

Una noche de insomnio compre en amazón un lector mp3 para Arduino, con el fin de incorporar música a alguno de mis proyectos. Como lo compré sin meditarlo mucho, al empezar, al intentar utilizarlo, me di cuenta de que no era exactamente lo que yo pensaba que era, no se ajustaba exactamente a lo imaginado. Lleva un soporte de tarjeta microsd en donde se supone que tenemos que tener la música.

En concreto los diferentes mp3 ni pueden llamarse de cualquier manera, ni pueden estar donde me apetezca, deben tener como nombre un número y estar en la raíz de la tarjeta microsd.

¿Como hago yo una copia masiva de mis mp3 y, además les pongo otro nombre?:Con el antiguo DOS y con apoyo en Excel.

• En windows, abriendo un terminal DOS (con CMD en la línea ejecutar) o bien creando un fichero ejecutable .bat
• Utilizo el comando dir c:\*.mp3 /s /b >mp3.txt
• Este comando da todos los mp3 que hay en el disco C: y redirecciona la salida a un fichero de texto (mp3.txt)
• Por supuesto que no es necesario buscar en todo el disco, podemos buscar en varios directorios de uno en uno. Solo hay que incorporar una línea por directorio y redireccionar la salida como >>mp3.txt
• En mi PC algunos caracteres no se representan correctamente. No importa, al utilizarlos de vuelta, se compensa ese error.
• Este error parece que se soluciona configurando el DOS a teclado en español (keyb sp o kb16 sp dependiendo del tipo de windows), pero en mi windows no están instalados ninguno de ellos.
• Con cualquier editor de texto, o abriendo el fichero de texto directamente desde excel, copiamos el contenido de este fichero en una hoja excel (a1)
• Vamos a preparar un fichero ejecutable .bat de DOS.  En las columnas B y C preparo el ordinal, el nombre de la copia. En B incremento el ordinal (B2=B1+1) y en C pongo los ceros a la izquierda necesarios, con =DERECHA(100000+B1;5).
• En la columna D preparamos una línea con el comando de DOS copy. El nombre original va precedido y seguido de comillas, de esa manera podemos incluir espacios en blanco en los nombres.
• El nombre de destino, en este caso, no tiene espacios en blanco, luego no necesitamos incluir las comillas.
• El comando resultante, uno por línea, es:="copy " & CARACTER(34) & A1 & CARACTER(34) & " c:\temp\mp3\" &C1 & ".mp3".
• Copiamos toda la columna en un fichero .bat, ejecutable en DOS. Salvamos.
• Ejecutamos el .bat, podemos o no desviar la salida a un fichero de texto, lo que nos permite controlar los posibles errores de copia. Nos copia todos los ficheros mp3 en un solo directorio y con los nombres adecuados.
• Ya solo nos queda pasarlos, si no lo hemos hecho directamente, a la tarjeta SD.

Ayer por la mañana escribí la primera parte de este articulo. Por la tarde me estuve documentando sobre el lector de mp3 y parece que si admite que la tarjeta SD tenga directorios aunque con algunas limitaciones.

Preparo una segunda  hoja (hoja2), en el libro CopiarMp3_2.xls, que me separa los mp3 por directorios. En principio tanto las canciones como los directorios toman un nombre numérico, que es lo que recomiendan algunas páginas.

• Para separar los directorios necesito conocer la última posición del carácter "\". Para ello creo una función, Ultima,  en vbasic para encontrar esa ultima posición.
• Una vez separado el directorio , con =SI($B4<>$B3;$D3+1;$D3) incrementamos el número de directorio. Esta instrucción se puede leer como "si el directorio actual es distinto del directorio anterior, incrementa uno. Si no déjalo como está"
• Para numerar las canciones dentro de su directorio utilizo =SI($B4<>$B3;1;$H3+1), que puede leerse como "si el directorio anterior es distinto del actual pon 1, si no incrementa uno".
• En la columna F preparo unas instrucciones DOS, con = " mkdir " & $H$1  & DERECHA($D3+1000;3), para crear los directorios mediante un fichero ejecutable ".bat". Estas instrucciones se copian en un fichero .bat, que debe ejecutarse antes de la copia de los mp3.
• Dejo abierta la posibilidad de trabajar con los nombres de directorios o/y ficheros.

Mi propia función, la función Ultima(): Entrando en los módulos VBasic podemos verla. No tiene gran explicación, se basa en la instrucción vbasic InStrRev que nos da la última aparición de un determinado carácter en una cadena alfanumérica

Function Ultima(Cad, Sep)

'Cad=Cadena alfanumérica

'Sep=separador, carácter buscado.

Ultima = InStrRev(Cad, Sep)

End Function

La pregunta es ,Y dado que nos metemos a programar en vbasic ¿porque no lo hacemos todo de una tacada en vbasic?
Código de la función que lee el fichero con los mp3 y crea un ejecutable (.bat). Este ejecutable crea los directorios y copia los ficheros.
Aunque también escribe en el excel, este paso no es necesario. Se puede omitir.
Cuando utilizo un fichero de texto creado con windows 7 me aparecen una serie de caracteres (unicode) que me introducen unos salto de línea incontrolados, que se pueden eliminar copiando a excel la línea para después recuperarlos.

Código de la función:
Sub Proceso()
Dim Carac, Lin, DirT, Datos, Salida, N, Pos1, Pos2, LinA, C(255)
Dim Lat, Lon, V, FH, Fic, Dir2, Dir1, DirAnt, ND, NF
'DirEntr = "C:\Documents and Settings\Fernando y Use\Mis documentos\Mi Garmin\"
'Nom = "fUENCARRAL, tRES cANTOS, aLCOBENDASReal.gpx"
With Sheets("Config")
DirT = .Range("b1").Value
Datos = .Range("b3").Value
Salida = .Range("b2").Value
End With
'Sheets(1).Range("a" & 1 + NF) = DirEntr & Nom
Close #1
Close #2
Open Datos For Input As #1
Open Salida For Output As #2


Lin = ""
N = 1
With Sheets("Datos")
.Select
.Cells.Clear
'MsgBox DirEntr & Nom
Do While Not EOF(1)

Carac = Input(1, #1)
C(Asc(Carac)) = C(Asc(Carac)) + 1
'If Asc(Carac) = 127 Or Asc(Carac) = 129 Or Asc(Carac) = 141 Or Asc(Carac) = 143 Or Asc(Carac) = 144 Or Asc(Carac) = 157 Then MsgBox Asc(Carac)

If (Asc(Carac) = 10 Or Asc(Carac) = 13) And Lin <> "" Then
N = N + 1


Pos1 = Ultima(Lin, "\")
Fic = Mid(Lin, Pos1 + 1)
Dir1 = Left(Lin, Pos1 - 1)
Pos1 = Ultima(Dir1, "\")
Dir2 = Mid(Dir1, Pos1 + 1)
If DirAnt <> Dir2 Then

ND = ND + 1
.Range("f" & ND + 1) = " mkdir " & DirT & Mid(1000 + ND, 2)
'.Range("g" & ND + 1) = " mkdir " & DirT & Dir2
Print #2, " mkdir " & DirT & Mid(1000 + ND, 2)
NF = 1
End If

Sheets("config").Range("d1").Value = Lin
' en Sheets("config").Range("e1")  hay que colocar la fórmula =limpiar(D1)
Lin = Sheets("config").Range("e1").Value
.Range("a" & N) = Lin
.Range("b" & N) = Dir1
.Range("c" & N) = Dir2
.Range("d" & N) = ND
.Range("e" & N) = Fic
DirAnt = Dir2
Print #2, " copy " & Chr(34) & Lin & Chr(34) & " " & DirT & Mid(1000 + ND, 2) & "\" & Mid(1000 + NF, 2) & ".mp3"
Lin = ""
NF = NF + 1
Else

If Asc(Carac) <> 13 And Asc(Carac) <> 10 Then Lin = Lin & Carac

End If
Loop

'.Range("1") = N
For i = 0 To 255
.Range("h" & i + 2) = i
.Range("i" & i + 2) = C(i)
.Range("j" & i + 2) = Chr(i)
Next
End With


Close #1
Close #2

End Sub


Caracteres no imprimibles: En principio son aquellos cuyo código ascii es menor de 32 (espacio). los no imprimibles van de de 0 a 31. En texto unicode hay otros  caracteres no imprimibles (127,129,141,143,144 y 157).

Bascula de cocina con un FSR y un Arduino Uno. Estudio preliminar con excel.

Estoy preparando una bascula de cocina con un FSR, un sensor de peso, y un Arduino Uno, en una articulo anterior analizo un poco la gráfica del fabricante del sensor de peso. Esta vez, ayudándome, como la vez anterior, de un libro excel, analizo el posible comportamiento del proyecto.

• Utilizo un divisor de tensión, una resistencia fija y el propio FSR. En este caso R2 en la resistencia fija y el FSR es R1.
• La salida Vout se lleva a un convertidor A/D, lo que nos da un valor digital que a su vez, nos permite conocer el valor que tiene el FSR en ese momento y dado ese valor, conocer el peso que hay sobre el.

• La conversión A/D da una serie limitada de valores, hay un salto conocido entre cada dos valores de tensión.
• La gráfica del fabricante es logarítmica, el FSR tiene un comportamiento logarítmico, lo que supone que un mismo salto de tensión entre dos medidas en el convertidor A/D, no se corresponda con los mismos gramos de diferencia. Según aumenta el peso, el incremento de peso para un mismo salto  entre dos medidas, es mayor. 
• Se supone, según la gráfica del fabricante, que el FSR puede pesar hasta 10kg. Como creo que es bastante difícil construir un platillo de báscula con menos de 50 gramos, el comienzo del tercer tramo lineal de la gráfica, solo considero en el libro excel ese tercer tramo.
• El libro excel incluye la gráfica del fabricante (GResistenciaGramos) y los cálculos necesarios para conocer para conocer la pendiente del tramo lineal. (hoja Tramo3)
• Sobre la gráfica del fabricante aparecen los valores calculados, tanto de la hoja Tramo3 como para la hoja GResistenciaGramos. 

Creando la gráfica XY

Valores de la Gráfica del fabricante.

Valor de los ejes y escala logarítmica.

Gráfica del fabricante como fondo de nuestra gráfica XY

• La hoja GResistenciaGramos permite conocer, de momento de un modo preliminar, el comportamiento del proyecto.
• Número de bits del convertidor A/D, en la celda A2. En esta celda se puede escribir lo que se quiera, aunque como es lógico, se debe incluir un valor que se corresponda con un valor de un convertidor A/D comercial.
• El propio Arduino Uno incluye un convertidor A/D de 10 bits. En internet he encontrado convertidores externos al arduino de 12, 14 y 16 bits. 
• Un mayor número de bits aumenta la precisión y un menor número de bits la disminuye. Con 10 bits, al aproximarnos al peso máximo, 10kg., cometemos un error de cuantificación de casi medio kilo. Con 14 bits pasa a ser de poco mas de 30 gr.
• La hoja GResistenciaGramos está preparada para funcionar completamente con un convertidor de hasta14 bits. Con 16 bits funciona parcialmente, habría que expandir las formulas mas alla de la línea 65000.
• Los cálculos están realizados con los valores digitales de tensión, tanto en su valor máximo como en los posibles valores intermedios.
• La resistencia R2 se calcula en función, tanto del número de bits del convertidor como del primer valor que queremos que tengo nuestro proyecto así como del inicio del tramo que estemos considerando. En este caso parto, según la gráfica del fabricante, para 50 gramos, de 10000 ohmios.
• En la celda B7 podemos variar el valor digital que queramos para esos 50 gr.
• En C2 se calcula el valor teórico de R2. Entre D2 y D5 se aproxima, con hasta tres resistencias, puestas en serie, un valor comercial a ese valor teórico.
• En H2 podemos indicar cual es el valor optimo de nuestra bascula. Aunque podamos pesar hasta 10kg, nuestro valor optimo puede ser mucho menor.
• Da el primer peso superior a nuestro peso optimo, la línea en que aparece y el posible error que se puede producir, que se corresponde con el último salto entre pesos, y que también se corresponde con el máximo error que consideremos bueno para nuestro proyecto.

Gestión de errores:

La hoja GResistenciaGramos tiene un gran número de líneas con unas fórmulas que algún momento, según los valores correspondientes, producen un error. Estos errores se pueden gestionar de muchas maneras, pero creo que hay que gestionarlos, queda mucho mejor y evita otros errores en cadena, aunque ello suponga un incremento del peso de las fórmulas.

• En la columna C controlo el posible cero o menor que cero cambiando el valor por el último valor superior a cero con =SI($C$2*(Vmax-$B12)/PV>0;$C$2*(Vmax-$B12)/PV;$C11)
• En la columna E ese control de error lo hago con SI.ERROR(10^((LOG10(D8)-Cte)/Pend);$E7), aunque  este caso es un caso de error en cadena. Se produce cuando D8 (C8/1000) toma un valor negativo. Al haber resuelto el error, valor de C8 menor que cero, en esta columna no deberían producirse errores.