Como posso somar “n” células aleatoriamente selecionadas no Excel?

Question

Usando o Excel 2010, tenho o seguinte problema:

Eu tenho uma coluna com números inteiros (de uma distribuição binomial) à qual eu irei me referir como "n". Em uma segunda folha, tenho outra coluna de valores (de uma análise de custo). Agora eu quero que o Excel escolha aleatoriamente n-células (n = um dado inteiro) da planilha 2 e some-as.

Por exemplo Folha 1, Coluna A
4
6
7
8
3
4
10
etc.

Folha 2; Coluna A
345
221
1011
223
455
12
...

Eu gostaria de criar uma coluna agora em que o Excel tenha, por ex. 4 (para a primeira linha) valores aleatórios diferentes da folha dois (por exemplo, 221, 455, 12 e 223) e os soma automaticamente, portanto, o resultado no respectivo deve ser 911. Para a próxima célula, o Excel deve somar 6 valores aleatórios, etc.

Isso é possível?

Usando a função INDEX, consegui dizer ao Excel para escolher uma célula aleatória da planilha 2 ou 5 vezes a mesma, mas não as células n e resumi-las.

    

Answer 1

Considere a seguinte função definida pelo usuário:

Public Function ransum(HowMany As Long, Population As Range) As Long
    Dim i As Long, N As Long, aray(), r As Range
    N = Population.Count
    ReDim aray(1 To N)

    i = 1
    For Each r In Population
        aray(i) = r.Value
        i = i + 1
    Next r

    Call Shuffle(aray)

    ransum = 0
    For i = 1 To HowMany
        ransum = ransum + aray(i)
    Next i
End Function

Public Sub Shuffle(InOut() As Variant)
    Dim i As Long, J As Long
    Dim tempF As Double, Temp As Variant

    Hi = UBound(InOut)
    Low = LBound(InOut)
    ReDim Helper(Low To Hi) As Double
    Randomize

    For i = Low To Hi
        Helper(i) = Rnd
    Next i


    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        J = J \ 2
    Loop
End Sub

Então, em Folha1 célula B1 nós inserimos:

= ransum (A1, Folha2! A $ 1: A $ 100)

e copie:

IssopressupõequeosdadosaseremamostradosestãonascélulasA1atravésdeA100dasegundaplanilha.

FunçõesDefinidaspeloUsuário(UDFs)sãomuitofáceisdeinstalareusar:

1. ALT-F11exibeajaneladoVBE
2. ALT-IALT-Mabreumnovomódulo
3. coleomaterialefecheajaneladoVBE

Sevocêsalvarapastadetrabalho,aUDFserásalvacomela.SevocêestiverusandoumaversãodoExcelposteriora2003,deverásalvaroarquivocomo.xlsmemvezde.xlsx

PararemoveroUDF:

1. abrirajaneladoVBEcomoacima
2. limpeocódigo
3. fecheajaneladoVBE

ParausaroUDFdoExcel:

=myfunction(A1)

Parasabermaissobremacrosemgeral,consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!