Мне интересно, если кто-нибудь знает, есть ли способ использовать функцию RANDBETWEEN для генерации серии случайных чисел в Excel, но также сделать эти случайные числа равными указанному среднему значению. Я бы использовал это для заполнения таблицы с ценами, например, если я использую RANDBETWEEN, чтобы заполнить 100 строк, и мне нужно, чтобы среднее значение составляло 50 долларов, я знаю, что могу заполнить строки, используя RANDBETWEEN(25,75) и перетащите его через 100 рядов, но это не обязательно приведет к средней цене, которая мне нужна. Кто-нибудь знает, возможно ли внести поправки, позволяющие мне установить это среднее значение? спасибо за любую помощь
3 ответа
2
Рассмотрим лотерею, в которой спонсор продает пачку билетов (скажем, 100), помещает их в контейнер (назовем это шляпой), а затем случайным образом выбирает билет из шляпы, чтобы решить, кто получит приз. Теперь предположим, что есть несколько призов; спонсор разыгрывает первого победителя, затем второго и третьего. Естественно, выигрышные билеты не помещаются обратно в шляпу после розыгрыша, потому что это позволит их снова выбирать, в результате чего один билет выигрывает более одного приза, что противоречит правилам. Поэтому все выборы после первого ограничены. В некотором смысле, игра сфальсифицирована, так что последующие победители могут прийти только из пула не победителей, как механизм достижения цели не присуждать несколько призов ни одному из билетов.
Теперь предположим, что есть 100 призов - каждый выигрывает что-то. К тому времени, когда окончательный билет вытащен из шляпы, там только один билет. Выбор ограничен до предопределенности. И вы знаете, кто является сотым победителем - это грустный парень, который стоит в толпе, ожидая, когда его имя назовут; последний оставшийся не победитель.
Ваша проблема может быть решена с помощью аналогичной стратегии:
- начать с «истинно» случайных чисел (под которыми я подразумеваю числа, статистически распределенные в соответствии с распределением вероятностей по вашему выбору, например, равномерное распределение в диапазоне 25–75), а затем
- ограничить более поздние числа (корректируя распределение) как механизм достижения цели оговоренного среднего.
А логика ограничения распределения вероятностей проста: не генерируйте (и не вводите в таблицу цен) любое число, которое делает невозможным достижение цели.
Мне легче думать о цели, как о достижении определенного общего, а не определенного среднего значения. Конечно, они логически эквивалентны. Например, предположим, что вы хотите только 4 числа вместо 100. Вы хотите, чтобы среднее значение составляло 50, поэтому общая сумма должна быть 4 × 50 = 200. Допустим, вы сгенерировали первые два числа: v 1 = 68 и v 2 = 70. Это в сумме 138, поэтому v 3+v 4 должно быть 62. Если v 3 равно 50, тогда v 4 должно быть 12, что находится за пределами допустимого диапазона 25-75. Решение 68+70+v 3+25 = 200 дает v 3 = 200- (68+70+25)= 37, поэтому мы ограничиваем v 3 между 25 и 37. После того, как V 3 выбран, V 4 определяется; она должна быть 62- v 3; т.е. 200− (v 1+v 2+v 3). Это аналогично ситуации с сотым лотерейным билетом; это должно быть то, что осталось после того, как были сделаны первые n -1 выбор.
Это звучит как работа для VBA, но я нашел решение на основе формул.
Далее я выбрал границы желаемого распределения вероятности (25 и 75) параметров; т.е. помещать их в отдельные ячейки (A1 и B1), а не жестко кодировать их в формулах. И я использую несколько вспомогательных столбцов.
- Установите
C1на желаемую сумму чисел. Вы можете просто ввести постоянное значение или вычислить его как Average_number × number_of_numbers, то есть=AVERAGE(A1, B1) * 100. - Установите для
C2=C$1-SUM(G$1:G1). - Установите
D1в=100-ROW(). - Установите
E1=MAX(C1-D1*B$1, A$1). - Установите
F1на=MIN(C1-D1*A$1, B$1). - Установите
G1в=RANDBETWEEN(E1, F1). - Перетащите
C2,D1,E1,F1иG1до строки 100.
Обсуждение:
Пусть n будет 100, количество случайных чисел и, следовательно, количество строк. Ниже приведена общая реализация четырехстрочного примера, который я привел ранее.
Для каждой строки i (1 ≤ i ≤ n)
Gi- это v i, i-е случайное число.EiиFi- нижняя и верхняя границы диапазона, из которого выбираетсяGi(как очевидно из формулы дляGi).Diведет отсчет от n − 1 в строке 1 до 0 в строке n ; то есть, он дает количество строк ниже текущей. Если вы начинаете с строки, отличной от 1, измените эту формулу соответствующим образом.C1- это v 1+v 2+… +v n − 1+v n
Ci=C1- (v 1+v 2+… +v i − 2+v i − 1), что равно (целевое значение) v i+v i +1+… +v n − 1+v nD1*A$1→Di×A1- минимальное допустимое значение для v i +1+v i +2+… +v n − 1+v n
Итак,C1-D1*A$1(Ci-Di×A1) является верхней границей для v i, чтобы можно было достичь общей суммы.Давайте вернемся к примеру с четырьмя рядами. Если бы (гипотетически) v 1 было 126, было бы невозможно достичь цели v 1+v 2+v 3+v 4 = 200 из-за ограничения, что v 2, v 3 и v 4 будут ≥ 25. Но если бы v 1 было 125, было бы возможно достичь цели с v 2 = v 3 = v 4 = 25. Таким образом, 125 является верхней границей для v 1. Но, конечно, v 1 также должно быть ≤ 75; 125 предел спорный. Значения v должны удовлетворять обоим наборам ограничений (как диапазон 25-75, так и ограничения, необходимые для достижения общего значения n × 50), поэтому они должны удовлетворять жесткости двух границ с каждой стороны (сверху и снизу).
Таким образом,Fiявляется верхней границей для v i.- И
Ei- нижняя граница для v i.
Вот снимок того, что я получил с n = 10:
Естественно, нажатие клавиши F9 приводит к тому, что Excel пересчитывает лист и генерирует новый набор случайных чисел.
Но G1:G10 всегда добавляет до 500; т.е. в среднем 50.
Если v n определяется v 1, v 2,…, v n − 2 и v n − 1, действительно ли оно все еще «случайно»?
Это интересный вопрос.
- Если n равно 1, то v 1 равно 50. Это явно не случайно. Но, конечно, это крайний случай.
Если n равно 2, v 1 выбирается случайным образом, тогда v 2 устанавливается равным 100− v 1. Ясно, что v 2 не зависит от v 1. Но
- P(v 2 = 25) = P(v 1 = 75) = 1/51.
- P(v 2 = 26) = P(v 1 = 74) = 1/51.
- ︙
- P(v 2 = 75) = P(v 1 = 25) = 1/51.
Таким образом, v 2 равномерно распределен по диапазону 25-75, так же, как v 1.
- Возвращаясь к примеру лотереи - победитель 100-го приза определяется победителями первых 99. Но до того, как какие-либо билеты вынуты из шляпы, каждый участник имеет равные шансы выиграть этот 100-й приз. Таким образом, победитель 100-го приза является случайным.
У меня возникли проблемы с математикой для этого вопроса для n > 2. Поэтому я провел эмпирический эксперимент. Я сгенерировал 100 случайных чисел от 25 до 75, и я пересчитал 250 раз, накапливая результаты. Затем я составил график распределения частот для нескольких значений v :
(Изображения являются ссылками на полноразмерные версии самих себя.)
До версии 91 распределения выглядят довольно равномерно:
Но, начиная с версии 92, начинает происходить что-то интересное:
Обратите внимание, что мне пришлось изменить масштаб на v 94 и выше:
Похоже, что последние несколько значений v имеют неравномерное распределение. Я предполагаю, что, когда n становится больше, становится более вероятным, что первые n -10 (или около того) значений будут отклоняться от желаемого среднего значения, заставляя последние несколько значений к крайним числам на концах диапазона, в порядке чтобы вернуть среднее значение на ходу. Но, по крайней мере, все значения должны иметь симметричные распределения.
- У них нет причин быть асимметричными.
- Мои графики кажутся симметричными.
Если вы заинтересованы в решении этой проблемы, вы можете задать ее на бирже Math Stack Exchange - они едят такие вопросы на завтрак.
1
Теоретически невозможно создать случайный (или даже псевдослучайный) список с фиксированным средним, потому что для этого требуется, чтобы более поздние значения зависели от более ранних значений и даже могли заставить вас вернуться назад и изменить некоторые из более ранних значений в некоторых случаев.
1
Как объяснила Кэтрин, такой встроенной функции не существует. После того, как вы создали случайные данные, вы должны вернуться и немного изменить данные. Таким образом, задача может быть выполнена с помощью VBA или другого языка программирования.
Вот решение VBA. Все, что он делает, это:
- пользователь указывает мин, макс для случайной функции и сколько чисел будет создано
- программа генерирует случайные данные
- Программа рассчитывает разницу между желаемым состоянием и текущим состоянием
- если числа слишком велики, то он посещает по одной строке за раз и вычитает -1, пока все не будет в порядке. если числа слишком низкие, то он посещает по одной строке за раз и добавляет +1, пока все не будет в порядке.
Вариант 1: скачать рабочее решение
ВНИМАНИЕ: Запуск макросов, которые вы загружаете в любом месте Интернета, не зная, что VBA может нанести вред вашему ПК. Вот почему есть второй вариант.
Рабочее решение можно скачать здесь. Был создан в Excel 2013, поэтому он должен нормально работать в Excel 2010 и более поздних версиях. После открытия файла у Microsoft должно появиться два всплывающих окна безопасности: popup1, popup2. Вы должны принять их. Запуск очень прост: просто нажмите кнопку ...
Вариант 2: установить макрос
Откройте Excel -> Alt+F11 -> Вставить -> Модуль -> вставьте следующий код -> Ctrl+S -> и выберите «Excel Macro-Enabled Workbook (* .xlsm)» из выпадающего списка
Sub RandomGenerator()
Dim min, max, cnt As Variant
Do While True
min = InputBox("Set the minimum", "Generating random with average", 25)
If min = "" Then Exit Sub
If Not IsNumeric(min) Then
MsgBox "Minimum has to be an integer. Try again.", vbExclamation, "Wrong input"
ElseIf CInt(min) <= 0 Then
MsgBox "Minimum has to be a positive integer. Try again.", vbExclamation, "Wrong input"
Else
Exit Do
End If
Loop
Do While True
max = InputBox("Set the maximum", "Generating random with average", 75)
If max = "" Then Exit Sub
If Not IsNumeric(max) Then
MsgBox "Maximum has to be an integer. Try again.", vbExclamation, "Wrong input"
ElseIf max <= min Then
MsgBox "Maximum has to be greater than minimum. Try again.", vbExclamation, "Wrong input"
ElseIf ((CInt(max) + CInt(min)) Mod 2) = 1 Then
MsgBox "Average of (min + max) has to be even. Try again.", vbExclamation, "Wrong input"
Else
Exit Do
End If
Loop
Do While True
cnt = InputBox("Set the count of numbers to generate", "Generating random with average", 100)
If cnt = "" Then Exit Sub
If Not IsNumeric(cnt) Then
MsgBox "Count has to be an integer. Try again.", vbExclamation, "Wrong input"
ElseIf CInt(cnt) <= 0 Then
MsgBox "Count has to be a positive integer. Try again.", vbExclamation, "Wrong input"
Else
Exit Do
End If
Loop
Call generateRandomWithAverage(CInt(min), CInt(max), CInt(cnt))
End Sub
Sub generateRandomWithAverage(min As Integer, max As Integer, cnt As Integer)
Dim random As Double
Dim i, avg, sum, desiredAvg, diff As Integer
sum = 0
desiredAvg = (min + max) / 2
For i = 1 To cnt
Cells(i, 1) = Excel.Application.WorksheetFunction.RandBetween(min, max)
sum = sum + Cells(i, 1)
Next
diff = sum - desiredAvg * cnt
i = 1
Do While diff <> 0
If diff > 0 Then
If Cells(i, 1) = min Then GoTo continue
Cells(i, 1) = Cells(i, 1) - 1
diff = diff - 1
Else
If Cells(i, 1) = max Then GoTo continue
Cells(i, 1) = Cells(i, 1) + 1
diff = diff + 1
End If
continue:
i = i + 1
If i > cnt Then
i = 1
End If
Loop
End Sub
Чтобы запустить макрос:
- Убедитесь, что вы находитесь на рабочем листе, которым хотите быть! И нажмите в любом месте на листе! (Потому что этот макрос выполняется в активированной рабочей таблице)
- Alt+F11 -> Нажмите на второй ряд в коде -> нажмите F5
Вы также можете создать кнопку на рабочем листе и назначить ей макрос - она более удобна для пользователя и вам не нужно проверять, что вы находитесь на нужном рабочем листе.