Unidad 4. VARIABLES ALEATORIAS Y DISTRIBUCIONES DE PROBABILIDAD ()

Ejemplo 179

Construya una macro que permita realizar todos los cálculos relativos a una variable aleatoria bidimensional discreta a partir de la distribución de probabilidad conjunta ingresada en una hoja del Excel. La macro debe ser capaz de recibir una tabla de cualquier tamaño y realizar todos los cálculos como las distribuciones marginales, las esperanzas, esperanzas condicionales, varianzas, covarianza y el coeficiente de correlación.La siguiente imagen corresponde a un segmento de la hoja que se debe diseñar. Toda la información obtenida a partir de la fila 8 debe ser obtenida mediante la macro.

La siguiente es la macro que resuelve lo pedido:

Dim nx, ny, mx, my As Integer

Sub DistrBid()

'Hoja = InputBox("Nombre de la hoja")

'Sheets(Hoja).Select

Range("C2").Select

ny = Selection.End(xlToRight).Column

Range("B3").Select

nx = Selection.End(xlDown).Row

Cells(2, ny + 1) = "Marginal de X"

Cells(2, ny + 1).ColumnWidth = 14

Cells(nx + 1, 2) = "Marginal de Y"

mx = nx - 2

my = ny - 2

For j = 3 To nx

Cells(j, ny + 1).Select

Cells(j, ny + 1) = "=Sum(RC3:RC[-1])"

Next

For j = 3 To ny

Cells(nx + 1, j).Select

Cells(nx + 1, j) = "=Sum(R3C:R[-1]C)"

Next

ValEsp

ValEspXY

End Sub

Sub ValEsp()

Cells(nx + 3, 2) = "E(X) = "

Cells(nx + 7, 2) = "E(Y) = "

Cells(nx + 4, 2) = "E(X²) = "

Cells(nx + 8, 2) = "E(Y²) = "

Cells(nx + 5, 2) = "V(X) = "

Cells(nx + 9, 2) = "V(Y) = "

ActiveWorkbook.Names("Rx").Delete

ActiveWorkbook.Names("Ry").Delete

ActiveWorkbook.Names("Rpy").Delete

ActiveWorkbook.Names("Rpx").Delete

ActiveWorkbook.Names("Rxy").Delete

ActiveWorkbook.Names("Rpxy").Delete

Range(Cells(2, 3), Cells(2, ny)).Name = "Ry"

Range(Cells(nx + 1, 3), Cells(nx + 1, ny)).Name = "Rpy"

Range(Cells(3, 2), Cells(nx, 2)).Name = "Rx"

Range(Cells(3, ny + 1), Cells(nx, ny + 1)).Name = "Rpx"

Cells(nx + 3, 3).Select

ActiveCell = "=SUMPRODUCT(Rx,Rpx)"

ActiveCell.Name = "Ex"

Cells(nx + 4, 3).Select

ActiveCell = "=SUMPRODUCT(Rx,Rx,Rpx)"

Cells(nx + 5, 3).Select

ActiveCell = "=R[-1]C-R[-2]C^2"

ActiveCell.Name = "Vx"

Cells(nx + 7, 3).Select

ActiveCell = "=SUMPRODUCT(Ry,Rpy)"

ActiveCell.Name = "Ey"

Cells(nx + 8, 3).Select

ActiveCell = "=SUMPRODUCT(Ry,Ry,Rpy)"

Cells(nx + 9, 3).Select

ActiveCell = "=R[-1]C-R[-2]C^2"

ActiveCell.Name = "Vy"

End Sub

Sub ValEspXY()

Cells(nx + 3, 5) = "XY "

Cells(nx + 4, 5) = "p(xy)"

k = 5

For i = 3 To nx

For j = 3 To ny

k = k + 1

Cells(nx + 3, k) = Cells(i, 2) * Cells(2, j)

Cells(nx + 4, k) = Cells(i, j)

Next

Next

Range(Cells(nx + 3, 6), Cells(nx + 3, 5 + mx * my)).Name = "Rxy"

Range(Cells(nx + 4, 6), Cells(nx + 4, 5 + mx * my)).Name = "Rpxy"

Cells(nx + 7, 5) = "E(XY) = "

Cells(nx + 7, 6) = "=SumProduct(Rxy,Rpxy)"

Cells(nx + 7, 6).Name = "Exy"

Cells(nx + 8, 5) = "COV(X,Y) = "

Cells(nx + 9, 5) = "ro(X,Y) = "

Cells(nx + 8, 6) = "=Exy-Ex*Ey"

Cells(nx + 8, 6).Name = "Cov"

Cells(nx + 9, 6) = "=Cov/sqrt(Vx*Vy)"

'Cells(nx + 8, 6) = Cells(nx + 7, 6) - Cells(nx + 3, 3) * Cells(nx + 7, 3)

'Cells(nx + 9, 6) = Cells(nx + 8, 6) / Sqr(Cells(nx + 5, 3) * Cells(nx + 9, 3))

End Sub

Sub Clear()

Range(Cells(2, ny + 1), Cells(nx + 1, ny + 1)).ClearContents

Range(Cells(nx + 1, 2), Cells(nx + 1, ny + 1)).ClearContents

End Sub