lunes, 28 de febrero de 2011

Borrar o Editar un Nombre de Rango

Pregunta

Si le doy un nombre a un conjunto de celdas (tabla) y veo que me he equivocado, ¿cómo puedo quitar el nombre a esa tabla?. Supongamos que ya he guardado el fichero y no puedo deshacer la operación.

Respuesta

Si tienes la versión 2007, para gestionar los nombres de rango debes ir a:
  1. Fórmulas
  2. Administrador de nombres
  3. Donde tienes las opciones: Nuevo, Editar y Eliminar




Si tienes la versión de Excel 2003 o anteriores, puedes llegar a una ventana similar a ésta, donde podrás gestionar los nombres de rango. Está en: Insertar, Nombre, Definir.



domingo, 27 de febrero de 2011

Actualización de una TD al recalcular de una celda

Descargar el fichero: eventocalcula.xlsm

Ya hemos hablado en un post anterior de cómo actualizar automáticamente una TD (Tabla Dinámica) ante la variación de una celda concreta o la variación de alguna celda que pertenezca a un rango previamente establecido. Pero en esos casos siempre teníamos que cambiar el valor de la celda a mano. Esto es, teníamos que escribir un valor o modificar alguno existente previamente. Al pulsar Intro es cuando se lanzaba la macro que actualizaba la TD.

Ver el post anterior: Actualización automática de una Tabla Dinámica

En esta ocasión vamos a comentar el caso en el que no es necesario cambiar manualmente la celda que al cambiar su valor lanza la macro, que a su vez actualiza la TD. Esto tiene la enorme ventaja de que esa celda puede ser una fórmula que está vinculada con otras muchas celdas que al cambiar modifican el valor de nuestra celda objetivo, y eso desencadena la actualización de la TD. O incluso pudiera estar la formula de esa celda objetivo apuntando a otras hojas, otros libros, o estar tomando valores vinculados de Access u otras fuentes de datos externas. Esto es muy importante, ya que esta automatización nos permite estar tranquilos de que la TD esta siempre actualizada y no requiere intervención humana. Así podremos automatizar sistemas complejos de forma completa.


Disponemos de una sencilla Base de Datos con tres campos: Comercial, Mes e Importe. En la celda G6 sumamos toda la columna D:

=+SUMA(D:D)

Esto hace que al cambiar algún valor de esa columna, y en particular las ventas de cada comercial (de color azul), se producirá un nuevo valor para la celda G6 (en color verde). Esta es la celda que desencadenará que la TD se actualice.

Haga la prueba cambie el valor de cualquiera de las celdas azules y verá como se actualiza la TD y el Gráfico Dinámico asociado.

La celda G7 es una celda auxiliar que hemos necesitado para saber si la celda verde cambia o no.

Esta es la macro que utiliza el evento Worksheet_Calculate.


Código:

Private Sub Worksheet_Calculate()
Dim vtas_old As Double
Dim vtas_new As Double
vtas_new = Sheets("Hoja1").Range("G6").Value
vtas_old = Sheets("Hoja1").Range("G7").Value
If vtas_old <> vtas_new Then
    [G7] = vtas_new
    ActiveSheet.PivotTables("TD1").PivotCache.Refresh
End If
End Sub

miércoles, 23 de febrero de 2011

Programar la función que de la distribución de Poisson

Descargar el fichero: poisson.xlsm

Lo primero es ver un Post anterior en este mismo Blog que explica la distribución de Poisson por varios métodos. En este nuevo Post pretendemos programar la propia distribución de Poisson usando VBA (Visual Basic for Applications). Vamos a programar tanto la Poisson como su acumulada.

El Post anterior que conviene leer es este:

Simular con una distribución de Poisson


Método 3

Ahora vamos a programar la Poisson.


Código:

Function xPoisson(x As Byte, Lamda As Byte, Optional acumulado As Boolean) As Double
Dim i As Byte, s As Double
If acumulado = False Then
   xPoisson = Exp(-Lamda) * (Lamda ^ x) / xFact(x)
ElseIf acumulado = True Then
   s = 0
   For i = 0 To x
      s = s + Exp(-Lamda) * (Lamda ^ i) / xFact(i)
   Next i
   xPoisson = s
End If
End Function

Function xFact(num As Byte) As Double
Dim i As Byte
xFact = 1
For i = 1 To num
    xFact = xFact * i
Next i
End Function

También hemos tenido que programar la función que calcula el factorial (xFact).
La función xPoisson tiene tres argumentos, siendo el tercero optativo. Observar en el código el comando Optional.

El tercer argumento es acumulado que puede ser verdadero o falso:


  • Si acumulado es FALSO, o ponemos un 0, o esta vacío nos dará la función de Poisson
  • Si acumulado es VERDADERO, o ponemos un 1, nos dará la función de Poisson acumulada


Método 4

Otro método alternativo para programar la distribución de probabilidad de Poisson es llamar desde la propia macro a la función de Excel.




Código:

Function yPoisson(x As Byte, Lamda As Byte, Optional acumulado As Boolean) As Double
yPoisson = Application.WorksheetFunction.Poisson(x, Lamda, acumulado)
End Function