lunes, 1 de octubre de 2012

Cálculo en Excel del área de un polígono irregular - producto en cruz -.

Hace poco un lector me consultaba sobre si existía en Excel alguna función que calculara el área de un polígono irregular, lamentablemente la respuesta es que no; sin embargo, eso no significa que Excel no nos pueda ayudar a realizar dicho cálculo.
En primer lugar debemos estar familiarizados con conceptos geométricos básicos para entender las fórmulas que voy a explicar. Y es que para hallar ese área necesitamos entender el planteamiento del método de producto en cruz, lo que está muy bien explicado en el siguiente sitio Web (latecladeescape.com).
Basicamente este método nos dice que descomponemos nuestro polígono irregular en multitud de triángulos, calculamos su área (recuerdo =base x altura /2), restamos aquellas partes duplicadas, y luego acumulamos esas áreas 'positivas' menos las áreas 'negativas'.
Ese cálculo acumulativo del área es el que vamos a conseguir de una manera muy sencilla aplicando Excel y replicando el método de producto en cruz.


Voy a explicar tres formas diferentes de calcular lo mismo, basado en el mismo método; y para ello, partiremos de un conjunto de puntos en un plano, que entre todos delimitan un polígono irregular o regular.
Por ejemplo, partiremos de seis puntos, seis vértices y por tanto seis lados:

Cálculo en Excel del área de un polígono irregular - producto en cruz -.


Como vemos, hemos añadido al final, el punto inicial repetido, digamos que eso nos permite cerrar el polígono, además, y fundamental, luego será necesario para el cálculo del área.
Para construir el gráfico, no es necesario para el cálculo pero sí muy visual, seleccionamos el rango de puntos B1:C8 y desde la ficha Insertar > grupo Gráficos > botón Dispersión > opción Dispersión con líneas rectas y marcadores, luego simplemente eliminamos Leyenda y Título y agragamos Etiquetas de datos con valores de ambas series.


Para comprender las diferentes formas de cálculo debemos conocer en qué consiste el método de producto en cruz. El asunto es muy sencillo, ya que sólo requiere multiplicar datos cruzados por la diagonal principal menos el producto de datos cruzados de la diagonal secundaria. En la imagen anterior se mostraba la representación del método en forma aritmética:

Cálculo en Excel del área de un polígono irregular - producto en cruz -.


Sumaremos los productos de los pares en rojo y restaremos los productos de los pares en azul, el resultado en valor absoluto, lo dividiremos por dos.
Y es es precisamente lo que haremos para calcular el área de la primera forma, con la fórmula:
=+(B2*C3)-(B3*C2)

Cálculo en Excel del área de un polígono irregular - producto en cruz -.


No requiere mucha explicación ya que simplemente en las celdas del rango E3:E8 calculamos el producto cruzado de ambas diagonales, sumando y restando siguiendo el criterio del método del producto en cruz. Finalmente en la celda E9 insertamos:
=ABS(SUMA(E3:E8))/2
que nos dirá cuál es el área del polígono determinado por esos seis puntos.


Una segunda manera de llegar a lo mismo, empleando funciones de Excel, en particular la función MDETERN. Si nos fijamos el producto en cruz para dos puntos con signos contrarios para las dos diagonales, no es ni más ni menos, que el cálculo del determinante de una matriz de 2x2; asi que es fácil entender la siguiente fórmula:
=MDETERM(B2:C3)

Cálculo en Excel del área de un polígono irregular - producto en cruz -.


Arrastraríamos, lógicamnte la fórmula en el rango F3:F8, y en F9 insertamos la fórmula:
=ABS(SUMA(F3:F8))/2
que igual que antes nos devolvería el área buscada.


El inconveniente de estas dos maneras de cálculo anteriores es que requerimos los cálculos intermedios. Sin embargo en la última forma (de momento) que contaré hoy, el asunto se simplifica. Ya que existe una función estándar en Excel que me permite multiplicar punto a punto dos rangos de valores. Hablo de la función SUMAPRODUCTO.
Sin necesidad de cálculos auxiliares intermedios, directamente podemos calcular el área de nuestro polígono:
=ABS(SUMAPRODUCTO(B2:B7;C3:C8)-SUMAPRODUCTO(B3:B8;C2:C7))/2

Cálculo en Excel del área de un polígono irregular - producto en cruz -.


Vemos como nuestra fórmula responde exactamente al método de producto en cruz, sumando el producto cruzado de los puntos de la diagonal principal, restando el producto de la segunda diagonal, aplicando valor absoluto (función ABS) y dividiendo el resultado por 2.

En estos tres casos la formulación requiere la repetición al final del listado de puntos, del primer par. este inconveniente lo resolveremos en una próxima entrada construyendo una función personalizada en VBA, y dando una alternativa matricial a la última de nuestras fórmulas.

3 comentarios:

  1. Hola!! no encontré otro lugar para escribir una duda que tengo, perdón si no es al tema!!.. He buscado desde anoche (2 días), intentado de todo, leído cientos de páginas y no puedo resolverlo.

    Estoy tratando de hacer una operación en base a un conjunto de condiciones (Estaba utilizando SUMAR.SI.CONJUNTO pero no me sirve). Esta función de SUMAR.SI.CONJUNTO me funciona perfectamente, y es lo que quiero que paso PEROOO sin sumar, quiero hacer otra operación. Una regla de 3. Sin embargo me conformo con hacerlo "en 2 pasos" y que en lugar de sumar MULTIPLIQUE y en otra fórmula luego dividiré.

    No lo logro. La formula que tengo es esta: =SUMAR.SI.CONJUNTO(O:O;M:M;I44;N:N;">=0")
    Lo que quisiera lograr es algo así: ="OPERACIÓN".SI.CONJUNTO=( [O:O * N:N/ (1-N:N)] ;M:M;I44;N:N;">=0")

    Yo sé que no va con corchetes [] pero es para que se vea mejor. Lo que quiero hacer es que: Si se cumplen las condiciones que están al final de la fórmula [ ;M:M;I44;N:N;">=0" ], que el dato de la columna M sea igual al dato de la celda I44 y que el dato de la columna N sea mayor o igual a 0, entonces que se realice la siguiente operación:

    [O:O * N:N/ (1-N:N)]. Que el dato de la columna O se multiplique por el dato de la columna N y se divida para el dato de 1 - el dato de N.

    Mi utopía sería algo así: ="OPERACIÓN".SI.CONJUNTO=(O15 * N15/ (1-N15) ; M = I44; N >=0 ) y así hacia abajo: ="OPERACIÓN".SI.CONJUNTO=(O17 * N17/ (1-N17) ; M = I44; N >=0 ) y así sucesivamente.

    Si alguien me podría ayudar le agradecería demasiado!

    Nicolás

    ResponderEliminar
  2. Adjunto un archivo de ejemplo y una imagen a continuación:

    IMAGEN: http://screencast.com/t/bhdfebwTlzd
    EJEMPLO: https://docs.google.com/file/d/0B4j9sMH8Th3uSHR4UWczQ0o5Y0k/edit

    ResponderEliminar
    Respuestas
    1. Hola Nicolas,
      he revisado tu fichero, y tienes dos alternativas.
      Una primera usando fórmulas matriciales, aplicando la siguiente fórmula:
      =SUMA(SI(B:B=$H$8;SI(C:C<=0;(D:D*ABS(C:C/(1-C:C))))))
      ejecutándola presionando Ctrl+Mayusc+Enter (en vez de sólo Enter).

      La segunda es añadiendo en tu columna E la siguiente fórmula y luego arrastrando:
      =D4*ABS(C4/(1-C4))
      Para luego en otra celda calcular normálmente:
      =SUMAR.SI.CONJUNTO(E:E;B:B;H8;C:C;"<="&0)

      Espero te sirvan
      Slds

      Eliminar