jueves, 4 de octubre de 2012

VBA: Una función para calcular el área de un polígono irregular.

En la entrada anterior vimos como calcular en nuestra hoja de cálculo el área de un polígono irregular, aplicando el método de producto en cruz, a partir de varios puntos que definian los vértices y lados del polígono (ver).
Una ventaja de la función UDF que vamos a programar es que no requiere la repetición del primer punto al final de la serie, lo que facilita nuestro trabajo.
Esta función personalizada en VBA replica el mismo método de cálculo visto en la entrada anterior, de hecho, si nos fijamos bien, es la forma que tiene Excel de realizar un sumatorio, con lo que efectivamente estamos contruyendo la fórmula del Área, explicada como sumatorio de diferentes productos.

Recordemos nuestros puntos de partida:



El código que insertaremos en un módulo del Explorador de proyectos del Editor de VBA, formando nuestra Function:

Public Function AreaPolig(x As Range, y As Range) As Double
Dim n As Long, area As Double, i As Long
area = 0
'contamos número de puntos...
n = x.Cells.Count
'controlamos los rangos introducidos
'que los rangos x,y sean de una columna
'que el rango x e y tengan el mismo tamaño
'que haya al menos 3 puntos (o sea que sea un triángulo!!! - polígono cerrado mínimo)
If x.Columns.Count <> 1 Or y.Columns.Count <> 1 Or n <> y.Cells.Count Or n < 3 Then Exit Function

'recorremos el rango de puntos, excepto el último punto!!
For i = 1 To n - 1
    'acumulamos el producto en pares de puntos
    'trabajamos con los ranfos como matrices.
    area = area + (x(i) * y(i + 1) - x(i + 1) * y(i))
Next i
'finalizamos añadiendo el área del último punto con el primero al acumulado anterior
area = area + (x(n) * y(1) - x(1) * y(n))

'obtenemos la función en valor absoluto dividido por dos.
AreaPolig = Math.Abs(area) * 1 / 2

End Function

En esta programación nos aprovechamos de las Array (matrices en VBA) para trabajar con los diferentees elementos de loss rangos, es decir, con cada punto que compone el polígono. La fórmula, como vemos es simple, ya que realiza el producto cruzado de cada par de puntos, acumulando el resultado al siguiente par, para acabar aplicando al resultado final el valor absoluto y dividiéndolo por 2. Obteniendo el área de un polígono irregular

10 comentarios:

  1. perdon por escribir aca, no es un comentario, es que trato de hacer una consulta y no encuentro donde escribirla...¿DONDE???

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      puedes enviar tu consulta a
      excelforo@gmail.com

      pero te constestaría igualmente por esta via 'comentario'.
      Slds

      Eliminar
  2. Hola ExcelForo, muchas gracias.
    Te cuento entonces: soy psiquiatra y estoy tratando de armar un libro para administrar mi consultorio. Mucho lo pude ir descubriendo con la ayuda de Excel.
    Lo que necesito saber es como hacer para ingresar un nuevo valor (precio) de consulta medica, en una celda consolidada, pero que Excel tome este valor desde una determinada fecha en adelante, sin que me actualice de esa fecha hacia atrás. Seria para ir actualizando el libro según los nuevos valores de las consultas pero que los valores anteriores no se modifiquen.
    Ya encontré que Excel maneja las fechas como un numero de serie secuencial, supuse que de alguna manera podría usar eso, pero no pude.
    Desde ya, gracias por tu tiempo.
    Saludos
    Diego

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      bueno, la idea desde luego es la que comentas. Diría que hay varias opciones.
      Una podría ser generar una columna nueva auxiliar (a la izquierda de todas las demás) en tu tabla matriz, en la que concatenes Fecha y Codigo, para luego realizar tu BUSCARV sobre esa columna, siendo el valor buscado, por supuesto, esa combinación Fecha+Código... un poco burda, pero muy sencilla y 'resultona'.

      Otra sería aplicar fórmulas matriciales, por ejemplo, suponiendo en A las fechas, en B los códigos y en C los precios, en la celda H4:
      =SUMA(SI(A1:A13=F4;SI(B1:B13=G4;C1:C13)))
      pero ejecutada matricialmente!!!, en F4 ponemos la fecha a buscar, y en G4 el Código.

      Esta opción te valdría para Fechas y Códigos concretos, si estás hablando de intervalos de fechas, la fórmula matricial es similar, pero añadiendo más condiciones con operadores <= y >=.

      Espero te oriente alguna de las soluciones. Si no es el caso, puedes enviarme a mi correo un ejemplo de tu caso.
      Slds

      Eliminar
    2. Hola, muchas gracias, va bien lo de formula matriz!!,pero solo para las fechas que defino puntualmente en la columna A. Como hago entonces para que me tome los rangos entre fechas??? por ejemplo, si el valor de la consulta es 50 entre 01/01/12 y 01/06/12, y es de 75 entre 02/06/12 y 01/12/12. se entiende la pergunta??
      Trate de meter en la formula la condicion > y < pero no funcionó.
      gracias por tu tiempo.

      Eliminar
    3. Hola Diego,
      bueno, tendría esta forma (siempre matricialmente):
      =SUMA(SI(A1:A13>=E6;SI(A1:A13<=E7;SI(B1:B13=F6;C1:C13))))
      siendo E6 y E7 las fechas inferior y superior, y F6 el código.

      También podrías emplear una función estandar:
      =SUMAR.SI.CONJUNTO(C1:C13;A1:A13;">="&E6;A1:A13;"<="&E7;B1:B13;F6)
      Slds

      Eliminar
  3. hola Ismael, otrs vez gracias por tu tiempo. creo que es muy complicado para este psiquatra.
    la idea es que yo pueda actualizar el valor de la consulta en una celda consolidada y que no se me vaya modificando el libro hacia atras,pero este valor va estar relacionado a un rango de fechas. es decir,cuando yo ingrese un determinado codigo de consulta, tiene que tomar el valor actualizado para esta fecha.pero van a haber multiples valores de consulta segun las distintas fechas y ahi es donde me paro y no puedo seguir.
    Voy a hacer algo mas simple, creo, que es cada mes que termina copio la hoja del mes en otro libro solo pegando los valores sin formulas, seguramente es algo medio rudimentario pero creo que va a servir.
    te agradezco mucho tu tiempo.
    te dejo un saluo desde buenos aires.

    ResponderEliminar
    Respuestas
    1. Como quieras...
      creo que la opción del SUMAR.SI.CONJUNTO te podría ir bien, asociando el acumulado a diferentes celdas con valores de fecha y código que tu puedes ir cambiando.
      Saludos

      Eliminar
  4. Disculpa, con una consulta:

    Quise probar la macro que tu indicas, y si los datos los tengo en el siguiente rango C2:D18, entonces en una celda anexa escribo =AreaPolig(C2:D18) pero no me da ningun resultado, me sale #¡VALOR!

    A qué podrá deberse dicho error?

    ResponderEliminar
    Respuestas
    1. Hola!,
      tienes que incorporar DOS argumentos a tu función, que compondrán los productos de sus elementos uno a uno cruzados, en tu caso, si tienes los datos en C2:D18, tendrías que escribir:
      =AreaPolig(C2:C18;D2:D18)

      Slds cordiales

      Eliminar