Manejando excel desde Acces. Manejo de datos con SQL.

 Desde que me prejubilé no tocaba este tema. Hace casi 20 años. Mas que para difundir este tipo de trabajos es para que yo mismo los recuerde.

Se trata de manejar con  SQL unos datos que tengo en un libro excel. En principio tengo un excel con los movimientos, gastos e ingresos, habidos en una cuenta bancaria en los últimos años. Me interesa, a partir de esos datos en excel, obtener un informe tipo:

GastosMensuales

campo1	aaaa	Enero	Febrero	Marzo	Abril	Mayo	Junio	Julio	Agosto	Septiembre	Octubre	Noviembre	Diciembre	total	Num
Bizum	2021	0	0	0	0	0	0	0	0	0	0	-62	32,95	-29,05	5
Bizum	2022	-48	-65,5	-17,2	-1,5	-26	0	0	-42,7	0	0	-52	10	-242,9	27
Bizum	2023	-145	0	25	0	5,51	-399	18	-21,15	-15	-24	99	-44	-500,64	18

 A partir de unos datos, en excel, del tipo:

Consulta2

Fecha Operación	Fecha Valor	Importe	Concepto
07/01/2026	07/01/2026	-21,31	Recibo Naturgy Clientes, S.a.u. Naturgy Clientes, Factura: Fe25390043657535 , Contrato: xxx Nº Recibo xxx Bbjyywx Ref. Mandato xxx, De
05/01/2026	05/01/2026	250,00	Transferencia , Concepto Gastos Mensuales Conjunta.

Primer paso: Vincular la los datos excel a una B.D. Acces

• Bien en una B.D. nueva, creada para este propósito, bien en una B.D. existente vinculamos el excel con la B.D.
• Abrimos la B.D., buscamos "Datos Externos" Excel.
• Buscamos nuestro archivo excel.
• Seleccionamos el último item,"Vincular al origen de datos creando una tabla vinculada"
• La siguiente pantalla nos da a elegir la hoja excel que tiene nuestros datos. Seleccionamos la indicada.
• La tercera pantalla nos permite decir que la primera línea son los nombres de los distintos campos que forman nuestros datos.
• La última pantalla nos pide el nombre que le vamos a dar a la tabla vinculada.

Ya podemos trabajar la hoja excel como si fuera una tabla de acces.

Segundo paso: Adecuar esos datos a nuestras necesidades, de manera que los siguientes pasos sean lo mas cómodos posibles.

• En este caso necesito, por un lado, acortar los nombres de los campos, haciendo desaparecer espacios, acentos y otros caracteres "molestos" a la hora de preparar consultas y, por otro lado, separar la fecha en año, mes y día (sin descartar la fecha como tal)
• Filtrar los datos para eliminar algunos registros que no son propiamente datos.

Primera consulta (SQL): Consulta Paso1

SELECT [fecha operación] AS FOp, year( [fecha operación]) AS AAAA, month( [fecha operación]) AS MM, day( [fecha operación]) AS DD, val([fecha operación] & " ") AS VV, [fecha valor] AS FV, Importe, concepto

 FROM  Gastos

 WHERE Val(([Fecha Operación]) & " ")>0 ORDER BY [fecha operación]   

Con este paso, a partir de esta consulta, [fecha operación] pasa a ser FOp, [fecha valor] pasa FV, mucho mas sencillos de manejar en consultas sucesivas. Además obtenemos año, mes y día de la fecha de operación.

Después de varios intentos fallidos (si no hay nada en fecha operación da un error, por lo que hay que añadir un espacio) el filtro de registros no validos lo hago con condición Val(([Fecha Operación]) & " ")>0

Segunda consulta: Necesito incorporar 12 columnas, una por mes, para llevar el importe de cada registro al mes que le corresponde, a la columna de su mes. Para ello utilizo la función iif().

SELECT AAAA, MM, DD, FOp, VV, FV,

 iif(MM=1,Importe,0) AS M1, iif(MM=2,Importe,0) AS M2, iif(MM=3,Importe,0) AS M3, iif(MM=4,Importe,0) AS M4, iif(MM=5,Importe,0) AS M5, iif(MM=6,Importe,0) AS M6, iif(MM=7,Importe,0) AS M7, iif(MM=8,Importe,0) AS M8, iif(MM=9,Importe,0) AS M9, iif(MM=10,Importe,0) AS M10, iif(MM=11,Importe,0) AS M11, iif(MM=12,Importe,0) AS M12,

 1 AS N, *

FROM paso1

Incorporo (N) un contador, por si fuese necesario. De momento no lo utilizo.

Estas dos consultas SQL se pueden hacer en un solo paso, se pueden refundir en una sola, pero, como es lógico, queda una consulta mucho mas compleja. A veces no es necesario complicarse la vida. Es una consulta concatenada con otra en SQL:

SELECT AAAA, MM, DD, FOp, VV, FV, iif(MM=1,Importe,0) AS M1, iif(MM=2,Importe,0) AS M2, iif(MM=3,Importe,0) AS M3, iif(MM=4,Importe,0) AS M4, iif(MM=5,Importe,0) AS M5, iif(MM=6,Importe,0) AS M6, iif(MM=7,Importe,0) AS M7, iif(MM=8,Importe,0) AS M8, iif(MM=9,Importe,0) AS M9, iif(MM=10,Importe,0) AS M10, iif(MM=11,Importe,0) AS M11, iif(MM=12,Importe,0) AS M12, 1 AS N, *

FROM

(SELECT [fecha operación] AS FOp, year( [fecha operación]) AS AAAA, month( [fecha operación]) AS MM, day( [fecha operación]) AS DD, val([fecha operación] & " ") AS VV, [fecha valor] AS FV, Importe, concepto FROM Gastos WHERE Val(([Fecha Operación]) & " ")>0 ORDER BY [fecha operación])  

Tercera consulta: Ahora nos interesa encontrar dentro del campo "concepto" una serie de literales que nos faciliten identificar el movimiento bancario.

• Creamos una tabla con los literales buscados, y su significado. La dejo abierta por si hay que modificar la búsqueda de esos literales dentro de concepto.
• Campo1 es el literal buscado, campo2 es el literal que va a aparecer en el informe y campo3 de momento no lo utilizo para nada, es por si fuera necesario modificar la búsqueda. 

Tabla1

Id	Campo1	Campo2	Campo3
9	Lit1	SPG
8	958	PR15
20	013	Luz Madrid
10	117	Luz CM
19	2005	Gas 23
13	30973	Gas

SELECT Campo1, Campo2, Paso2.*, instr(Concepto,campo1) AS P

FROM Tabla1, Paso2 where instr(Concepto,campo1)>0

Esta sentencia SQL multiplica matricialmente todos los registro de la tabla1 por los registros resultantes del paso2. Con  instr(Concepto,campo1)>0 se eliminan todos los registros que no contienen el valor del correspondiente literal (campo1) en el concepto.

Paso4: en este paso, acumulamos los distintos importes de cada mes en la columna correspondiente, por año y concepto. Con cuatro consultas SQL ya tendríamos nuestro informe de gasto acumulado por mes.

SELECT campo1, aaaa, Sum(m1) AS Enero, Sum(m2) AS Febrero, Sum(m3) AS Marzo, Sum(m4) AS Abril, Sum(m5) AS Mayo, Sum(m6) AS Junio, Sum(m7) AS Julio, Sum(m8) AS Agosto, Sum(m9) AS Septiembre, Sum(m10) AS Octubre, Sum(m11) AS Noviembre, Sum(m12) AS Diciembre, Sum(Importe) AS total, Sum(n) AS Num

FROM paso3

GROUP BY campo1,AAAA

ORDER BY aaaa, campo1;

Paso4

campo1	campo2	aaaa	Enero	Febrero	Marzo	Abril	Mayo	Junio	Julio	Agosto	Septiembre	Octubre	Noviembre	Diciembre	total	Num
diputacion	Diputación	2013	0	0	0	0	0	-71,94	0	0	0	-207,66	0	0	-279,6	2
Gas Natural		2013	-31,21	0	-47,23	-67,04	0	-56,02	0	10,23	0	0	-81,01	-71,1	-343,38	9
impuesto	Impuestos	2013	0	0	0	0	0	-71,94	0	0	0	-207,66	0	0	-279,6	2
liquidacion		2013	0	0	0	1,28	0	0	0	0	0	3,44	0	0	4,72	2
San Pedro		2013	0	-30	0	0	0	0	0	0	0	0	0	0	-30	2

En este punto ¿Hemos terminado? No, no hemos terminado. Este trabajo es un trabajo preliminar, un intento de recordar como se trabaja con SQL y un intento de no dejar nada en el olvido.

Nos falta un registro con el total del año y, posiblemente, un separador de años. Tampoco he separado los valores positivos de los negativos, gastos e ingresos, cada uno por su sitio. Como ya he dicho, trabajo preliminar.

Paso5: Vamos totalizar el año, es una consulta similar a la anterior pero agrupando solamente por AAAA. Los literales Campo1 y Campo2 son fijos, AAAA es el que corresponda y el resto de los campos es una suma.

SELECT "Total año" AS Campo1, "Total año" AS campo2, AAAA, Sum(m1) AS Enero, Sum(m2) AS Febrero, Sum(m3) AS Marzo, Sum(m4) AS Abril, Sum(m5) AS Mayo, Sum(m6) AS Junio, Sum(m7) AS Julio, Sum(m8) AS Agosto, Sum(m9) AS Septiembre, Sum(m10) AS Octubre, Sum(m11) AS Noviembre, Sum(m12) AS Diciembre, Sum(Importe) AS total, Sum(n) AS Num

FROM paso3

GROUP BY AAAA

ORDER BY AAAA;

Paso6: Para terminar una consulta mas compleja, es la unión de varias consultas sencillas.

SELECT AAAA,Campo1,Campo2,Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octubre,Noviembre,Diciembre,Total

FROM 

(SELECT  * , 2 as Ord FROM Paso4

union

select *,4 as Ord from Paso5

union

select "","","",aaaa,"","","","","","","","","","","","","", 0 as Ord from paso5 group by aaaa)  

ORDER BY AAAA, Ord;

En esta unión tenemos tres consultas sencillas, dos de ellas Paso4 y Paso5 ya comentadas, la tercera:

 select "","","",aaaa,"","","","","","","","","","","","","", 0 as Ord from paso5 group by aaaa 

Es la que nos va a dar el separador de años. El único campo no nulo es el año, lo que nos va a dar una cabecera con ese año.

Introduzco un nuevo campo, Ord, que me va a dar la posición relativa de los distintos registros de la unión. Cero para la cabecera del año, dos para los registros del año y 4 para el total del año.

Paso6

AAAA	Campo1	Campo2	Enero	Febrero	Marzo	Abril	Mayo	Junio	Julio	Agosto	Septiembre	Octubre	Noviembre	Diciembre	Total
2013
2013	17916330	SPG	0	0	0	0	0	0	0	-36,96	0	0	-81,01	-71,1	-189,07
2013	San Pedro		0	-30	0	0	0	0	0	0	0	0	0	0	-30
2013	diputacion	Diputación Segovia	0	0	0	0	0	-71,94	0	0	0	-207,66	0	0	-279,6
2013	impuesto	Impuestos	0	0	0	0	0	-71,94	0	0	0	-207,66	0	0	-279,6
2013	Gas Natural		-31,21	0	-47,23	-67,04	0	-56,02	0	10,23	0	0	-81,01	-71,1	-343,38
2013	liquidacion		0	0	0	1,28	0	0	0	0	0	3,44	0	0	4,72
2013	Total año	Total año	-31,21	-30	-47,23	-65,76	0	-199,9	0	-26,73	0	-411,88	-162,02	-142,2	-1116,93

Puede que nos interesen solamente los informes de un determinado año, o de un litera, o ....

SQL con selección de año: Añadimos a la consulta anterior (Paso6)

la petición del año que queremos consultar:

SELECT Pregunta,AAAA, Campo1, Campo2, Enero, Febrero, Marzo, Abril, Mayo, Junio, Julio, Agosto, Septiembre, Octubre, Noviembre, Diciembre, Total

FROM (SELECT  * , 2 as Ord FROM Paso4

union

select *,4 as Ord from Paso5

union

select "","","",aaaa,"","","","","","","","","","","","","", 0 as Ord from paso5 group by aaaa)  AS [%$##@_Alias] 

where AAAA=Pregunta

ORDER BY AAAA, Ord;

En donde insertamos una primera variable (Pregunta) y seleccionamos con where AAAA=Pregunta.

Volumen de un cuerpo de revolución con Excel.

 Un cuerpo de revolución es aquel que se origina al girar una figura plana alrededor de un eje.

Tenemos que aprovechar la posibilidad que nos da Excel de realizar decenas de miles de operaciones matemáticas en un instante. Podemos hacer cálculos bastante complejos dividiendo la operación en miles de operaciones sencillas. En este caso, vamos a dividir la figura de revolución en miles de segmentos, miles de "cachitos" con una forma elemental, fáciles de manejar.

¿Esta manera de operar es válida? Partimos de una figura conocida y fácil de operar con ella,  vamos a calcular el volumen de media esfera, que es el resultado de girar un cuarto de circulo alrededor de un eje. 

Dividimos esa media esfera en pequeños discos. Cada disco va a tener su propio volumen, su propio radio, pero todos los discos van a tener la misma altura. Calculamos independientemente de los otros el volumen de cada uno de los discos para, posteriormente, sumarlos. 

El volumen de media esfera es fácilmente calculable mediante la matemática clásica, lo que nos va a permitir hacer una comparación y una valoración de los resultados obtenidos por las dos maneras. 

• Primero decidimos cuantos discos vamos a utilizar, con cuantos discos queremos operar. Aunque en la hoja Excel se puede modificar, vamos a utilizar 40000 "discos".
• Dividimos la altura de la figura entre ese número de puntos. En este caso es el resultado de dividir el radio de la esfera, altura de la figura, por el número de discos que vamos a utilizar.
• Vamos a calcular el radio de cada disco en el punto medio de su altura, teniendo en cuenta que cada disco está apilado sobre los discos anteriores. Esto quiere decir que la mitad del disco estará por debajo del valor que le correspondería al circulo y la la otra mitad estará por encima. 

• Esta vez los cuadrados de un determinado valor los calculo multiplicando ese valor por si mismo, en vez de elevar al cuadrado. x*x en vez de x^2. 
• Excel no digiere bien ciertas operaciones con muchos decimales. Una operación sencilla, como puede ser la suma de un pequeño incremento a un valor anterior, acaba dando un resultado ni deseado ni  esperado, como puede verse en Columna A de EsferaY, sobre todo en las últimas líneas. 
• Conocido el radio y la altura de un disco es fácil calcular su volumen. Superficie de su circunferencia por su altura.
• En algunos casos limito el número de decimales del resultado de la operación. 
• π (pi) tiene un número infinito de decimales. Vamos a utilizar un valor de π con 9 decimales. Tanto esta limitación como la del volumen de los discos, aparentemente, mejora los resultados. Reducimos el número de decimales.
• Conocido y, por Pitágoras, calculamos x.
• Conocido x, radio del disco, su volumen sería π*r^2*dy.
• Sumamos, calculamos por matemáticas clásicas el volumen y comparamos.
• El resultado es bastante bueno.

Variables con nombre del libro Excel:

• Dif , =EsferaY!$L$5. Diferencia entre ambos métodos de cálculo.
• IncX, =EsferaX!$E$1. Incremento de x, no se utiliza.
• IncY, =EsferaY!$F$1. Incremento de y, radio divido por el número de discos a utilizar.
• MiPi, =EsferaY!$C$2,. Pi con los decimales limitados a 9.
• NDec,=EsferaY!$A$2. Número de decimales.
• NPunt, =EsferaY!$C$3, Número de puntos (discos)
• Punto, =EsferaY!$F$1. Altura discos.
• Radio, =EsferaY!$J$1-EsferaY!$H$1. Radio de la esfera, donde $j$1 en principio es cero. 
• Radio2, =Radio*Radio. Radio al cuadrado.

Variables en la hoja EsferaY:

• Min Y, en H1. Mejor a cero. Es el valor mínimo de y si no queremos centrar el cuarto de circulo en 0,0.
• Max Y, en J1. MaxY-MinY=Radio de la esfera.
• Decimales de Pi, en C1.
• MiPi en C2. MiPi se calcula en esa celda.
• N.Puntos, en c3. Número de discos a utilizar.

Columnas de EsferaY:

• Columna A: Altura acumulada de los discos, de la forma "Valor anterior+dy". Se puede apreciar un error. Podemos prescindir de esta columna.
• Columna B: Números naturales de 0 a 40000.
• Columna C: Valor de Bx multiplicado por IncY.
• Columna D: Repetición de la columna A. 
• Columna E: Punto mitad de la altura del disco.
• Columnas F y G: Radio de cada disco para un y igual al de la columna E.
• Columna H: Volumen de cada disco.

Radio de la tierra. Elipsoide GS84

Radio de la tierra para una determinada latitud

Como todos sabemos la tierra no es una esfera, es un geoide, como una naranja, una esfera achatada por los polos. A partir de esta certeza las distintas aproximaciones a la forma y medidas de la tierra se basan en considerar la tierra como un elipsoide, en este caso vamos a utilizar el elipsoide GS84. Esta forma supone que según nos alejamos del ecuador el radio, distancia del centro de la tierra al punto en cuestión, disminuye.

Este trabajo básicamente consiste en crear un gráfico que nos de dicho radio para cualquier latitud. Latitud en grados enteros. Se puede, ya que estamos trabajando con Excel, ajustar el valor a cada latitud con mas exactitud, pero creo que con esta gráfica vale.

Hay una pregunta en el aire ¿Cual es el punto mas alejado del centro de la tierra? Véase la hoja Everest-Chimborazo.

Empecemos con la hoja "Esfera". Empecemos, en una primera aproximación, considerando la tierra como una esfera. Hacemos unos primeros cálculos, 90º y cada grado dividido en seis saltos de diez minutos de grado. Esto nos da un valor referencia de X. Se puede hacer de varias maneras pero, mas o menos, nos vendría a dar un resultado similar.

Los valores de los semiejes del elipsoide GS84 son:

Semieje Mayor 6378137

Semieje Menor 6356752,314 En metros.

Hoja Elipsoide: En esta hoja, una vez fijado el valor de X, a partir de los valores obtenidos el la hoja "Esfera", calculamos  el valor de Y (columna c), la latitud que se corresponde a esos valores obtenidos (columna H), el radio para esa latitud y el redondeo de grados a un valor entero (columna G).

En la hoja "Latitudes", en F1 pondríamos el valor de la latitud de la que deseamos conocer el radio de la tierra, obtenemos un valor promedio del radio de la tierra para cada latitud en grados enteros (J1), de 0 a 90, y el valor mas aproximado para una latitud con dos decimales (L1). 

En la gráfica Latitud-Radio tenemos la gráfica buscada.

 

Aproximación a los valores de la derivada de una función con Excel. II

 Capitulo anterior

En el capitulo anterior aproximo, hago un primer estudio de, si es posible obtener los valores de la derivada de una función con Excel, lo que parece que si es posible. Los valores obtenidos no son exactos pero si son lo bastante próximos al valor que deben tener como para considerarlos válidos.

Como estoy en una fase previa, debo comparar lo obtenido con este método de trabajo con el método tradicional. Esta comparación, de momento,  no me desmiente, la aproximación es bastante buena, creo que con funciones mas complejas también se pueden alcanzar buenos resultados.

Esta vez voy a buscar el punto de la derivada que toma el valor cero, un máximo, un mínimo o un punto de inflexión. Como ejemplo utilizo la función y=X³ (x^3).

El valor de X se incrementa con el valor que queramos asignar en C1. Esta vez las pequeñas áreas con las que calculo la derivada (ver capitulo anterior) son el valor que le corresponde a x-dx y el valor que le corresponde a x+dx (dx es el ancho del área, en este caso, una fracción del incremento de X). 

Los valores obtenidos se pueden ver en la gráfica GX^3

La cosa consiste en buscar un valor, mejor dicho, un intervalo lo mas próximo a cero. En este caso, cero sería el menor valor, de los calculados, de la derivada, pero, al tener un número limitado de puntos, puede que no este presente. Por eso vamos a reiterar la búsqueda hasta encontrarlo.

• Empezamos en la hoja X^3.
• Ponemos, según nuestro criterio, los valores mínimo y máximo de  x con los que queremos empezar a trabajar. 
• El libro está preparado para trabajar con mil puntos, mil valores de X. Como es lógico, si aumentamos el  número de puntos aumentamos la resolución.
• Buscamos el mínimo valor de la derivada.
• Buscamos la línea que lo tiene. (I1,K1)

El resto de las hojas son idénticas a esta primera, pero, en las sucesivas hojas (X^3(2),X^3(3)...) los límites del intervalo de trabajo lo fijan los valores anterior y posterior al mínimo encontrado. Cada una de estas hojas va acompañada de su correspondiente gráfica

Podemos ver que, al menos con esta función, los valores encontrados de la derivada por este método infinitesimal se aproximan a los encontrados por el método clásico (precisiones superiores a nueve decimales), el de toda la vida, hasta que en la hoja X^3 (4) coinciden.

¿Podemos conjeturar que esta manera de trabajar es válida para cualquier función?  Creo que si, por compleja que sea.

Aproximación a los valores de la derivada de una función con Excel. Gráfica de la derivada. Preliminar.

 Primeros pasos con la aproximación de los valores de la derivada de una función con Excel.

De momento solo trazo la gráfica de la derivada, aunque a partir de los datos obtenidos se podría encontrar máximos, mínimos y puntos de inflexión (aproximados). 

La base matemática en la que me baso es sencilla, la derivación es lo opuesto a la integración. Si la integral (definida entre dos valores) se representa como un rectángulo con una base dx, la derivada , es una conjetura, sería ese rectángulo dividido por ese dx. Por tanto para cada dos valores consecutivos de x (x, x+dx),  supongo que la función es el resultado de una integral, de la integral definida para los valores x, x+dx. 

11/07/2024 Me he dado cuenta de que eso que intento explicar ahí arriba de una manera tan absurdamente complicada, es en realidad la pendiente de la curva en ese punto. Lo que digo es cierto, no es que sea falsa esa vía para llegar a conocer el valor de la derivada, es que lo complico innecesariamente. Con lo simple que es decir que es la pendiente de la curva en ese punto (diferencia  dy dividido por dx) . "Taintatos" años sin tocar este tema.

El libro Excel contiene tres funciones elementales, fáciles de derivar y de integrar. Cada función utiliza dos hojas, una con los valores calculados y la otra son los gráficos de la función, la derivada por este método diferencia y la derivada clásica. Con esas tres funciones la conjetura se cumple, las gráficas de la derivada calculada por un método clásico se solapa con la calculada por este método "diferencial". Hay poca diferencia.

Esto es todavía un estudio preliminar, probablemente no sea muy cómodo utilizar el libro tal y como está, de momento le falta "algo" para que resulte cómodo de usar y para obtener una resolución mayor (mas adecuada)

Como se puede ver en el libro, con demasiados decimales hay cosas que no funcionan. Directamente no funciona la instrucción Coincidir().

Siguiente capítulo

¿Cuantos paneles fotovoltáicos necesito? Calcular el número de paneles necesarios para una instalación FV.

  Calculadora de Paneles

Primero, para empezar, tuve que recordar una serie de conceptos:

• Irradiancia en el punto de instalación: Resumiendo, es la potencia que nos da la radiación solar para el punto de instalación. Estos valores, al menos para las tablas que yo tengo, vienen tabulados para una superficie horizontal.
• Factor K: Es un número que multiplica al valor anterior en función de la inclinación.
• HSP (hola solar pico): Resumiendo, es el número equivalente de horas que, en la localidad donde vamos a hacer la instalación, hay una radiación (irradiancia) de 1000w por metro cuadrado.
• Potencia del panel: Resumiendo, es la potencia que entrega cada panel cuando hay una irradiancia de 1000w metro cuadrado.
• Rendimiento del panel: Potencia nominal menos las perdidas que pueda haber. Podemos suponer entre un 70% y un 80%.
• Alguno de mis trabajos anteriores sobre este tema:
• Perdidas por sombra
• Perdidas por orientación e inclinación
• Estas perdidas, por sombra e inclinación, las podemos considerar entre un veinte y un treinta por ciento. Podemos considerar que nuestros paneles van a tener un rendimiento del 70% al 80%.
• Potencia, en vatios, es la energía (en Julios) entregada en un periodo de tiempo (en segundo) P=E/T (Julios/Seg)
• Kwh o wh: Resumiendo, es la energía entregada en una hora. Es el resultado de multiplicar la potencia, en kw, por el tiempo (en horas). Un kwh es la energía de un kw sostenida durante una hora.
• Orientación del panel: Lo optimo es que los paneles, en el hemisferio norte, estén orientados al sur. Una mala orientación supone una perdida de potencia.
• Inclinación del panel: Lo optimo sería que los rayos solares incidan verticalmente sobre el panel, cosa que no siempre es posible. Normalmente está condicionada por la inclinación del tejado.

¿Cuantos kwh necesitamos obtener de nuestra instalación FV?

• Acudimos a nuestras facturas de la luz. En este caso buscamos un consumo lo mas próximo posible al consumo anual.
• Buscamos, por tanto, la última factura y la de hace aproximadamente un año.
• En algún punto de la factura aparecen conceptos como lectura anterior, con su fecha de lectura y el número anterior de kwh y última lectura, con su fecha y su número de kwh. Nos fijamos en el total de kwh.
• Anotamos los valores que aparecen en "lectura anterior" de la factura de hace un año. Total de kwh.
• Anotamos la fecha de lectura anterior.
• Anotamos los valores que aparecen como "última lectura" en la última factura. Anotamos la fecha de última lectura y anotamos el total kwh.
• Al restar esos valores tenemos el consumo anual o casi anual.
• Ya en excel, al restar dos fechas obtenemos el número de días transcurridos entre ambas. Restamos a la última lectura lectura anterior, la de hace un año.

De esta manera sabemos tanto nuestro consumo casi anual como nuestro promedio de consumo diario.

En su momento, hace mas de diez años, hice un curso para instalar paneles de agua caliente. Todavía conservo unos trabajos que hice con las tablas de irradiación solar. Solo he tenido de convertirlas, añadiendo la conversión  de Mjulios a kw.

Por otra parte, también hace un montón de años, encontré en internet que alguien había subido las coordenadas de todos los pueblos de España. De esta manera podemos conocer la latitud del sitio donde vamos a instalar los paneles. Son cosas de agradecer.

Nuestra instalación no estaría completa si no pudiéramos almacenar la energía sobrante de alguna manera. En una instalación aislada hablaríamos de poner baterías.

Las baterías son unos elementos caros y limitados, poco duraderos, pero existe la posibilidad de "almacenar" esa energía sobrante en lo que las compañías eléctricas llaman "batería virtual". Nuestros paneles entregan la energía sobrante a red eléctrica, la compañía la valora, la acumula en euros y con esos euros pagamos nuestro consumo realizado en aquellas horas que nuestros paneles no cubren nuestras necesidades energéticas. Esto nos permite producir en en sitio y consumir en otro. 

El libro excel está preparado para calcular el número de paneles necesario para cubrir el consumo de tres casas, en tres lugares distintos, en tres provincias distintas. La instalación, por supuesto, se realizará en un solo lugar.

• Buscamos la hoja "SelPoblación".
• Seleccionamos, primero provincia y luego población.
• De esta manera obtenemos la latitud de la población.
• El libro redondea la latitud y obtiene "las horas solar pico" para esa latitud.

El libro está preparado para calcular el número de paneles necesarios para tres casas. 

• Cada casa, se supone, tiene su propia factura. Seleccionamos una de las hojas "Factura" para cada casa. Podemos dejar en blanco las facturas no necesarias.
• Aportamos los datos antes descritos.
• Si fuese necesario podríamos añadir alguna "Factura" mas. Solo hay que copiar una de las hojas "Factura" y darle un nombre adecuado.
• Estos datos se suman en la hoja "TotalAnual". Si queremos eliminar o añadir una factura solo hay que eliminar/agregar un sumando en las celdas B1 y B2. 

Número de paneles: 

• Seleccionamos la hoja "Paneles".
• En A2 ponemos la potencia nominal de cada panel.
• En B2 deberíamos poner el cálculo del rendimiento del panel descontando las perdidas por orientación e inclinación. Para simplificar el cálculo ponemos entre un 70% y un 80%.
• Podemos hacer el cálculo mes a mes, solo tendríamos que seleccionar el mes en cuestión o bien el cálculo basándonos en el promedio anual de irradiación. Se supone que tenemos la posibilidad de guardar los excedentes de producción de un mes para otro.
• Inclinación: Depende del tejado, la inclinación del tejado es algo que difícilmente vamos a poder modificar. No obstante parece que no afecta mucho al resultado final.

• En E2 nos da el número de paneles necesarios.

 

Cálculo infinitesimal con Excel II. Centro de gravedad de un cuarto de círculo. Preliminar

 Capitulo anterior

En el capitulo anterior estudio como calcular el centro de gravedad de un triángulo, que es una figura sencilla. En este capitulo vamos a encontrar con Excel el centro de gravedad (o centroide) de un cuarto de círculo.

• Dividimos, como en el caso anterior, en N segmentos la figura, dividimos por el número de segmentos que deseemos calcular el valor del radio de la circunferencia. Llevamos estos valores al eje X.
• Calculamos los correspondientes valores de Y. 
• Tenemos, como ya comenté en el capitulo anterior, N barras verticales coronadas por un resto con forma de triángulo. En este caso la figura real que corona el segmento no es triangular, hay, además, un segmento circular, pero, se puede aproximar a un triángulo. Cometemos un pequeño error , pero, el resultado, creo, que se puede considerar que sigue siendo válido.
• Cuanto mayor sea el número de segmentos, menor sera este error.
• Cada barra vertical, cada segmento en el que dividimos la figura, tiene su propio centro de gravedad. Lo mismo para los restos triangulares. Cada uno tiene su propio centro.
• Cada uno de esos segmentos, cada uno de los restos. "pesa" sobre un punto determinado, tanto sobre el eje X como sobre el eje Y. 
• Sumatorio de fuerzas por distancia igual fuerza total por distancia al centro de gravedad.
• La fuerza, el "peso" de cada segmento y de la figura total es el área de cada segmento por una constante K (gr. por centímetro cubico, gravedad, altura). Como esa constante K está a ambos lados de la ecuación, despejando, fuerzas por distancia queda como fuerza  por área.

Libro Excel: Está preparado para calcular hasta 50000 puntos, que se pueden ampliar. Como ya he comentado se produce un pequeño error.

¿Estoy trabajando correctamente? ¿La idea es buena? ¿Puedo comparar resultados con los obtenidos con otro tipo cálculo?

En este caso, si. Como es lógico, no es necesario armar este follón para hacer un cálculo mucho mas fácil si se hace de otra manera, pero necesito demostrarme a mi mismo que esta manera de trabajar es correcta. Solo lo puedo hacer comparando resultados, un resultado que se que es correcto, con el resultado obtenido con el supuesto que estoy siguiendo.

¿Como puedo calcular con una matemática mas clásica este centroide ?  Me baso en:

"El volumen de un área de revolución es el área de la figura multiplicado por el camino que recorre su centro"

• En este caso el área es la de un cuarto de círculo y el volumen que se genera al girar la figura alrededor de su centro, es el de media esfera. Hoja CCirculo, columnas de la D a la G.
• En este caso estamos trabajando con una figura simétrica, los valores de X e Y del centro  son idénticos.

El centro de gravedad de un triángulo rectángulo esta situado a 2/3 del vértice.

Los valores obtenidos se pueden considerar aceptables.

Mis propia función Excel:

Esta vez incorporo una función VBasic,CCCir(Radio;Num;tipo respuesta) para Excel (utilizada en hoja Aux, de F1 a F4), mi propia función Excel, que me permite hacer estos cálculos con una mayor aproximación, con un error mucho menor. 

Function CCCir(R, N, Op)

Dim Incr As Double, Y0, Y1, Resto As Double, Area As Double, X2 As Double

Incr = R / N

For i = 1 To N - 1

X2 = (i * Incr) * i * Incr

Y1 = Sqr(R * R - X2)

Area = Area + Y1 * Incr

DY = Y0 - Y1

FDx = FDx + (i - 0.5) * Y1 * (Incr ^ 2) + (i - 2 / 3) * (Incr ^ 2) * DY / 2

FDy = FDy + (Y1 ^ 2) * Incr / 2 + (DY * (1 / 3) + Y1) * Incr * DY / 2

Area = Area + Incr * DY / 2

Y0 = Y1

Next

CCCir = Area & " " & FDx / Area & " " & FDy / Area

If Op = 1 Then CCCir = Area

If Op = 2 Then CCCir = FDx / Area

If Op = 3 Then CCCir = FDy / Area

'If Op = 0 Then CCCir = Area & Chr(10) & Pi * (Radio ^ 2) / 4 & FDx / Area & Chr(10) & FDy / Area & Chr(10) & ccc

End Function

Cálculo infinitesimal con Excel I. Preliminar. Centro de gravedad de un triangulo.

Para ingenieros, los matemáticos probablemente  piensen que soy un hereje y merezca la hoguera.

Se trata de aprovechar la posibilidad que nos da Excel de efectuar decenas de miles de operaciones en un instante. Ni podemos ni debemos desdeñar esa posibilidad.

Una vez efectuadas esas decenas de miles de operaciones podemos encontrar un valor próximo al valor buscado. En mis pruebas me he encontrado con un error menor al de una millonésima.

Para tener la seguridad de que el cálculo infinitesimal, o manera de hacerlo, es correcto, empiezo con un triángulo rectángulo, que me va a permitir calcular de una manera clásica el valor buscado, en este caso, el centro de gravedad. Podemos comparar ambos valores, en su caso, determinar el nivel de error y decidir si nuestros cálculos son correctos y/o el error cometido es asumible.

Triángulo:

El libro Excel, el ejemplo, está preparado para un triángulo rectángulo en el que podemos variar el valor de los dos catetos, con uno de sus vértices el el punto 0,0. (ver gráfica 1). La hipotenusa, por un lado,cumple el teorema de Pitágoras y por otro es una recta del tipo y=mx. Sin constante, esto es un estudio preliminar.

Cosas que recordaba levemente y que tuve que refrescar acudiendo a internet:

• Por un lado sabemos que el centro de gravedad o baricentro de un triángulo se  calcula encontrando  la intersección de las medianas. (hoja Triangulo1, celdas K3 y L3)
• En un triángulo rectángulo, que tiene un ángulo igual a 90 grados, el centro de gravedad se puede encontrar dividiendo la altura del triángulo por 3 y midiendo la distancia en el vértice del ángulo recto. Este punto se encuentra a lo largo la altitud, cual es un segmento de línea trazada desde el vértice del ángulo recto a la hipotenusa... (según internet)

Esta manera de calcular ese valor me va a permitir calcular otros centros de gravedad sin cálculos mas complicados.

Método de trabajo:

• Dividimos el tamaño del cateto situado sobre el eje x en cuantos intervalos deseemos y excel admita.
• Calculamos el valor de Y para cada uno de los puntos resultante de dividir X entre n.

• En la primera columna del la hoja "Puntos" hay un valor N, que se incrementa en la siguiente fila en 1. 
• Tenemos un valor, valor del cateto situado sobre el eje X dividido por el número de saltos deseados, que es el incremento de X entre dos puntos. 
• Al multiplicar el valor que toma N en cada punto, encontramos el valor de X. Con este valor de X encontramos el valor de Y.
• Dividimos, por tanto, la superficie que limita la función en N barras verticales, rematadas por un área (pequeña) que podemos asimilar a  triángulo rectángulo. En este caso, al ser una recta, es un triángulo rectángulo.
• La altura de la barra, o mejor dicho que altura vamos a elegir de entre las dos alturas que la delimitan cada segmento.
• En este caso la función es una función creciente, al aumentar X aumenta Y. Aunque siempre vamos a elegir el menor valor de Y de los dos valores (Y0,Y1) que delimitan cada segmento, en este caso elegiremos el primero. El área de la barra vertical es Incr*Y0.
• El pequeño triángulo que corona cada barra tiene una altura que es la diferencia entre las dos Y que delimitan el segmento, una base igual a el Incr y un área=(Incr*(Y1-Yo))/2
• La suma del área de todos los segmentos, incluyendo los pequeños triángulos, debe dar un valor muy próximo al calculado por otros medios.
• En este ejemplo, como estamos trabajando con una recta, nos debe dar valores idénticos.

Centro de gravedad:

• Cada segmento aporta, tanto para X como para Y, según lo alejado que se encuentre del origen. 
• Podemos aplicar la ley de la palanca, fuerza por distancia. 
• Aunque la ley de la palanca se refiere a fuerzas, no a superficies, imaginemos que nuestra superficie tiene un cierto volumen, pero sigue siendo plana. Tiene, por tanto, un cierto peso (fuerza). Tanto si consideramos el área total, como si tomamos cada segmento por separado, el peso va a depender de una serie de constantes (masa, gravedad, densidad, altura...)  que van a estar a ambos lados de una igualdad y, que por tanto podremos eliminar a ambos lados de la igualdad, quedándonos solo con el área de cada segmento por su distancia.
• La suma, el sumatorio, del área de cada segmento multiplicado por su distancia al origen debe ser igual al área total por la distancia del centro de gravedad. Tanto para X como para Y. Son cálculos separados.
• Cosa que se cumple.

Para figuras mas complejas, sin lugar a dudas, sobre todo por la aproximación de la superficie restante, la que está fuera de la barra de cada segmento, a un triangulo hace que se produzca un pequeño error que, cada cual, debe decidir si puede asumir o no. 

Libro Excel:

• En una primera hoja, "Triangulo1", podemos variar el valor de los catetos. Con esos valores calcula la hipotenusa, las medianas, la intersección de las medianas, y el área del triángulo.
• En la segunda hoja, "Puntos", se calcula el valor de los distintos segmentos, valores X e Y, el diferencial de Y entre dos puntos, y el tamaño de las áreas en las que hemos dividido el área total.

Variables con nombre:

• CatH: Cateto horizontal. Triangulo1!A2
• CatV: Cateto vertical. Triangulo1!B2
• Incr: Incrementos X. Triangulo1!$A$2/Triangulo1!$D$2
• NPunt. Número de puntos. =Triangulo1!$D$2

Resultados:

• Área total: Se calcula de dos manera, por la manera clásica (base*altura/2) y como resultado del sumatorio de los distintos segmentos. Hoja "Puntos".
• Palancas: Cada segmento rectangular , cada barra, tiene su centro de gravedad en su centro. En el caso de X en Incr/2 (mas la X del segmento). Para Y su centro está en la mitad de su altura.
• Restos, asimilados a un triángulo rectángulo: En este caso, al ser una función creciente, el triángulo tiene el vértice del lado horizontal a la izquierda. Su centro de gravedad está en 2/3 de Incr (mas la X del segmento). Para Y esta a 1/3 de la diferencia entre Y1 e Y0 (mas la altura sel segmento).
• En la hoja "Puntos", columnas de la N a la R están los resultados de los valores calculados, de varias maneras, utilizando tanto la función "Indirecto()"  como la función DesRef()