Как я могу суммировать "n" случайно выбранных ячеек в Excel?

Question

При использовании Excel 2010 у меня возникает следующая проблема:

У меня есть один столбец с целыми числами (из биномиального распределения), на который я буду ссылаться как "n". На втором листе у меня есть еще один столбец значений (из анализа затрат). Теперь я хочу, чтобы Excel случайно выбрал n-ячеек (n = заданное целое число) из листа 2 и суммировал их.

Например, лист 1, столбец А
4
6
7
8
3
4
10
и т.п.

Лист 2; Столбец А
345
221
1011
223
455
12
...

Я хотел бы создать столбец, в котором Excel взял бы, например, 4 (для первой строки) случайных, различные значения листа два (например, 221, 455, 12 и 223) и суммирует их автоматически, поэтому результат в соответствующем 911. Для следующей ячейки Excel должен суммировать 6 случайных значений и т.д.

Это возможно?

Используя функцию INDEX, мне удалось указать Excel выбрать одну случайную ячейку из листа 2 или 5 одинаковых ячеек, но не n ячеек, и суммировать их.

Answer 1

Рассмотрим следующую пользовательскую функцию:

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

Итак, в ячейку B1 Sheet1 мы вводим:

= Ransum (A1, Лист2!A $ 1:A $ 100)

и скопируйте вниз:

Это предполагает, что данные для выборки находятся в ячейках с A1 по A100 второго листа.

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

= MyFunction (А1)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!