Случайная выборка в excel
С завидной регулярностью (а в последнее время - всё чаще) мне пишут люди с просьбами помочь в различных вычислениях, связанных с лотереями. Кто-то хочет реализовать в Excel свой секретный алгоритм подбора выигрышных чисел, кто-то - найти закономерности в выпавших номерах прошедших тиражей, кто-то - подловить организаторов лотереи на нечестной игре.
В этой статье мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие из которых, кстати, могут пригодиться и в более прозаических рабочих ситуациях.
Задача 1. Вероятность выигрыша
Возьмем для примера классическую лотерею "Столото 6 из 45". По правилам суперприз (10 млн. рублей или больше, если накопился остаток призового фонда с прошлых тиражей) получают только те, кто угадал все 6 чисел из 45. Если вы угадали 5, то получите 150 тыс. рублей, если 4 - 1500 р., если 3 числа из 6, то 150 р., если 2 числа - вернете 50 р., потраченных на билет. Угадаете только одно или ни одного - получите только эндорфины от процесса игры.
Математическую вероятность выигрыша можно легко рассчитать с помощью стандартной функции ЧИСЛКОМБ (COMBIN) , которая имеется в Microsoft Excel на такой случай. Эта функция вычисляет количество комбинаций N чисел из M. Так для нашей лотереи "6 из 45" это будет:
. что равно 8 145 060 - общее число всех возможных комбинаций в этой лотерее.
Если же хочется рассчитать вероятность для частичного выигрыша (2-5 чисел из 6), то придётся сначала вычислить количество таких вариантов, которое равно произведению числа комбинаций угаданных чисел из 6 на количество не угаданных чисел из оставшихся (45-6)=39 чисел. Затем общее количество всех возможных комбинаций (8 145 060) мы делим на полученное количество выигрышей по каждому варианту - и получим вероятности выигрыша для каждого случая:
К слову, вероятность, например, погибнуть в авиакатастрофе в России оценивается примерно как 1 к миллиону. А вероятность выиграть в казино в рулетку, поставив всё на один номер - 1 к 37.
Если всё вышеперечисленное вас не остановило и вы по-прежнему готовы играть дальше - продолжаем.
Задача 2. Частота выпадения каждого числа
Для начала давайте определим с какой частотой выпадают те или иные числа. В идеальной лотерее, если брать для анализа достаточно большой временной интервал, у всех шаров должна быть одинаковая вероятность попадания в победную выборку. В реальности же особенности конструкции лототрона и вес-форма шаров могут вносить искажения в эту картину и для каких-то шаров вероятность выпадения вполне может оказаться выше/ниже, чем для других. Давайте проверим эту гипотезу на практике.
Возьмём для примера данные по всем прошедшим в 2020-21 году тиражам лотереи 6 из 45 с сайта их организатора Столото, оформленные в виде вот такой удобной для анализа "умной" таблицы с именем таблАрхивТиражей. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк - вполне достаточная для начала выборка для анализа:
Для подсчёта частоты выпадения каждого числа используем функцию СЧЁТЕСЛИ (COUNTIF) и дополнительно вложим в неё функцию ТЕКСТ (TEXT) , чтобы добавить к одноразрядным числам начальные нули и звёздочки перед и после, чтобы СЧЁТЕСЛИ искала вхождение числа в любом месте комбинации в столбце В. Также для пущей наглядности построим диаграмму по результатам и отсортируем частоты по убыванию:
В среднем любой шар должен выпадать 1459 тиражей * 6 шаров / 45 номеров = 194,53 раз (это как раз то, что называется в статистике математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11. ) выпадали заметно чаще (+18%), а некоторые (10, 21, 6. ) наоборот заметно реже (-15%), чем основная масса. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот - делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.
Задача 3. Какие числа давно не выпадали?
Ещё одна стратегия базируется на идее, что при достаточно большом количестве тиражей рано или поздно должно выпасть каждое число из всех имеющихся от 1 до 45. Так что если какие-то числа давно не появлялись среди выигравших ("холодные шары"), то логично попробовать сделать на них ставку в будущем.
Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21 год по убыванию даты и использовать функцию ПОИСКПОЗ (MATCH) . Она будет сверху-вниз (т.е. от новых к старым тиражам) искать каждое число и выдавать порядковый номер тиража (считая от конца года к началу), где последний раз это число выпало:
Задача 4. Генератор случайных чисел
Ещё одна стратегия игры основана на том, чтобы исключить психологический фактор при угадывании номеров. Когда игрок выбирает числа, делая свою ставку, то подсознательно делает это не совсем рационально. По статистике, например, числа от 1 до 31 выбирают на 70 % чаще, чем остальные (любимые даты), реже выбирают 13 (чертова дюжина), чаще выбирают числа содержащие "счастливую" семерку и т.д. Но играем мы против машины (лототрона), для которой все числа одинаковы, так что имеет смысл выбирать их с такой же математической беспристрастностью, чтобы уравнять наши шансы. Для этого нам потребуется создать в Excel генератор случайных и - что особенно важно - неповторяющихся чисел:
- Создадим "умную" таблицу с именем таблГенератор, где в первом столбце будут наши числа от 1 до 45.
- Во втором столбце введём вес для каждого числа (он потребуется нам чуть позднее). Если все числа для нас одинаково ценны и мы хотим выбирать их с равной вероятностью, то вес везде можно поставить равным 1.
- В третьем столбце используем функцию СЛЧИС (RAND) , которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9 ) будет генерироваться новый набор из 45 случайных чисел с учётом веса для каждого из них.
- Добавим четвертый столбец, где с помощью функции РАНГ (RANK) вычислим ранг (позицию в топе) для каждого из чисел.
Теперь останется сделать выборку первых шести по рангу 6 чисел с помощью функции ПОИСКПОЗ (MATCH) :
При нажатии на клавишу F9 формулы на листе Excel будут пересчитываться и мы будем каждый раз получать новый набор из 6 чисел в зеленых ячейках. Причём числа, для которых был задан в столбце B больший вес, будут получать пропорционально больший ранг и, таким образом, чаще оказываться в результатах нашей случайной выборки. Если же вес для всех чисел задать одинаковым, то все они будут выбираться с одинаковой вероятностью. Таким образом мы получаем справедливый и беспристрастный генератор случайных чисел 6 из 45, но с возможностью внести корректировки в случайность распределения при необходимости.
Если же мы решим играть в каждом тираже не одним, а, например, двумя билетами сразу, в каждом из которых будем выбирать неповторяющиеся числа, то можно просто добавить к зелёному диапазону дополнительные строки снизу, прибавив к рангу 6, 12, 18 и т.д. соответственно:
Задача 5. Симулятор лотереи в Excel
В качестве апофеоза всей этой темы давайте создадим в Excel полноценный симулятор лотереи, на котором можно будет опробовать любые стратегии и сравнить результаты (в теории оптимизации что-то похожее ещё называют методом Монте-Карло, но у нас будет попроще).
Чтобы все было максимально приближено к реальности, представим на минуту, что сейчас 1 января 2022 года и впереди у нас тиражи этого года, в которых мы планируем играть. Реальные выпавшие числа я занёс в таблицу таблТиражи2022, отделив дополнительно выпавшие числа друг от друга в отдельные столбцы для удобства последующих вычислений:
На отдельном листе Игра создадим заготовку для моделирования в виде "умной" таблицы с именем таблИгра следующего вида:
- В желтых ячейках сверху будем задавать для макроса количество тиражей 2022 года, в которых мы хотим участвовать (1-82) и количество билетов, которыми мы играем в каждом тираже.
- Данные для первых 11 столбцов (A-J) макрос будет копировать с листа тиражей 2022 года.
- Данные для следующих шести столбцов (K-P) макрос будет брать с листа Генератор, где мы реализовали генератор случайных чисел (см. задачу 4 выше).
- В столбце Q мы считаем количество совпадений выпавших чисел и сгенерированных с помощью функции СУММПРОИЗВ (SUMPRODUCT) .
- В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли, то приз - 50 р. за билет)
- В последнем столбце S считаем общий результат всей игры нарастающим итогом, чтобы видеть динамику в процессе.
Останется ввести желаемые исходные параметры в жёлтые ячейки и запустить макрос через Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 .
Для наглядности можно ещё построить диаграмму по последнему столбцу с нарастающим итогом, отражающую изменение денежного баланса в процессе игры:
Сравнение разных стратегий
Теперь, используя созданный симулятор, можно протестировать на реальных тиражах 2022 года любую стратегию игры и посмотреть на результаты, которые бы она принесла. Если играть 1 билетом в каждом тираже, то общая картина "слива" выглядит примерно так:
- Генератор - игра, где в каждом тираже мы выбираем случайные числа, созданные нашим генератором (с одинаковым весом).
- Любимчики - игра, где в каждом тираже мы используем одни и те же числа - те, что чаще всего выпадали в тиражах за последние два года (27, 32, 11, 14, 34, 40).
- Аутсайдеры - то же самое, но используем самые редко выпадающие числа (12, 18, 26, 10, 21, 6).
- Холодные - в всех тиражах используем числа, которые давно не выпадали (35, 5, 39, 11, 6, 29).
Как видите, разницы большой нет, но генератор случайных чисел ведёт себя чуть лучше остальных "стратегий".
Можно также попробовать играть большим количеством билетов в каждом тираже, чтобы перекрыть большее количество вариантов (иногда для этого несколько игроков объединяются в группу).
Игра в каждом тираже одним билетом со случайно сгенерированными числами (с одинаковым весом):
Игра 10 билетами в каждом тираже со случайно сгенерированными числами (с одинаковым весом):
Игра 100 билетами в каждом тираже со случайными числами (с одинаковым весом):
Комментарии, как говорится, излишни - слив депозита неизбежен во всех случаях :)
В надстройку Пакет анализа входит инструмент Генерация случайных чисел , с помощью которого можно сгенерировать случайные числа, имеющие различные распределения .
С помощью надстройки Пакет Анализа можно сгенерировать случайные числа следующих распределений:
- Равномерное распределение (непрерывное)
- Нормальное распределение
- Распределение Бернулли
- Биномиальное распределение
- Распределение Пуассона
Кликнув на нужное распределение, можно открыть статью, где подробно написано о генерировании случайных чисел с помощью надстройки Пакет Анализа и с помощью формул.
В MS EXCEL имеется множество функций, с помощью которых можно сгенерировать случайные числа, принадлежащие различным распределениям. И лишь часть из этих распределений представлено в окне инструмента Генерация случайных чисел. Чтобы сгенерировать случайные числа из других распределений см. статью про распределения MS EXCEL , в которой можно найти ссылки на другие распределения.
Примечание : С помощью другого инструмента надстройки Пакет анализа , который называется «Выборка», можно извлечь случайную выборку из конечной генеральной совокупности . Подробнее см. статью Случайная выборка из генеральной совокупности в MS EXCEL .
Модельное распределение
С помощью надстройки Пакет Анализа можно сгенерировать числа, имеющее так называемое модельное распределение . В этом распределении нет никакой случайности - генерируются заранее заданные последовательности чисел .
Поле Число переменных задает количество столбцов генерируемых данных. Т.к. в них будут сгенерированы совершенно одинаковые данные, то нет особого смысла указывать в поле Число переменных значение отличное от 1.
Поле Число случайных чисел можно оставить незаполненным, т.к. оно рассчитывается автоматически в зависимости от значений, указанных в группе Параметры диалогового окна. Например, при параметрах, указанных на рисунке выше, в каждом столбце будет выведено по 24 «случайных» числа: четыре нечетных числа 1; 3; 5; 7 (от 1 и до 7; шаг равен 2) будут повторены по 2 раза, а каждая последовательность будет повторена по 3 раза (4*2*3=24).
Произвольное дискретное распределение
С помощью надстройки Пакет Анализа можно сгенерировать числа, имеющие произвольное дискретное распределение , т.е. распределение, где пользователь сам задает значения случайной величины и соответствующие вероятности.
Необходимо следить, чтобы сумма вероятностей модельного распределения была равна 1. Для этого в MS EXCEL имеется специальная функция ВЕРОЯТНОСТЬ() .
СОВЕТ : О генерации чисел, имеющих произвольное дискретное распределение , см. статью Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL . В этой статье также рассмотрена функция ВЕРОЯТНОСТЬ() .
Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.
Пусть имеется некая совокупность данных, которая слишком велика для обработки или построения диаграммы. Для этих целей можно использовать репрезентативную выборку из данной совокупности.
Данную выборку можно получить с помощью инструмента «Выборка» надстройки Пакет анализа ( Analysis ToolPak ).
Надстройка доступна из вкладки Данные , группа Анализ .
СОВЕТ : Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье Надстройка Пакет анализа MS EXCEL .
Случайная выборка
Разместим исходную генеральную совокупность в диапазоне A7:A32 (см. файл примера лист Пример ).
Для наглядности все значения совокупности сделаны последовательными числами .
Вызовем надстройку Пакет анализа , выберем инструмент Выборка. Нажмем ОК.
Если диапазон включает и заголовок, то нужно установить галочку в поле Метки . В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон A 7: A 32 .
Метод выборки установите Случайный , в поле Число выборок введите 6. Таким образом, из совокупности будет выбрано 6 чисел в случайном порядке (возможны повторы).
В поле Выходной интервал достаточно ввести ссылку на верхнюю ячейку диапазона с результатами (укажем ячейку С7 ). Нажмем ОК.
В результате работы надстройки, MS EXCEL разместил в столбце D 6 значений выбранных случайных образом из диапазона A 7: A 32 .
В принципе, количество случайных значений можно установить даже больше чем значений в исходной совокупности (безусловно, при этом будут повторы).
Аналогичную случайную выборку можно осуществить с помощью формулы =ИНДЕКС($A$7:$A$32;СЛУЧМЕЖДУ(1;СЧЁТ($A$7:$A$32)))
Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию совокупности, из которой нужно взять 1 значение ( для этой функции вероятность выбрать любую строку одинакова ).
Выборка с определенной периодичностью
С помощью инструмента «Выборка» можно осуществить выборку с определенной заданной периодичностью. В этом случае пользователь должен сам убедиться, что данная выборка будет репрезентативной (как минимум, исходная совокупность не должна быть отсортирована ).
Этот же результат можно получить формулой (см. файл примера лист Пример ): =ИНДЕКС($A$7:$A$32;$H$6*(СТРОКА()-СТРОКА($G$6)))
В ячейке H6 содержится период выборки .
Если исходная совокупность имеет цикличность и упорядочена, то можно создать выборку , содержащую значения только из отдельной части цикла. Например, если входной диапазон содержит данные для квартальных продаж за несколько лет, то создание выборки с периодом равным 4 разместит в выходном интервале значения продаж только из заданного квартала.
Гораздо проще для этого использовать формулу (см. файл примера лист Квартал ): =ИНДЕКС($C$9:$C$32;4*(СТРОКА()-СТРОКА($F$8))-4+$F$6) или =СУММЕСЛИМН($C$9:$C$32;$A$9:$A$32;E9;$B$9:$B$32;$F$6&" кв.")
Примечание : Для наглядности строки, относящиеся к нужному кварталу, выделены Условным форматированием .
Выборка из нормального распределения
Пусть имеется генеральная совокупность из 5000 значений случайной величины имеющей стандартное нормальное распределение (см. файл примера лист Выборка из НОРМ ).
Примечание : Случайные значения сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС())
Построим гистограммы распределения для генеральной совокупности и выборки размером 200.
Это удобнее сделать с помощью формулы =ИНДЕКС($A$7:$A$5006; СЛУЧМЕЖДУ(1;СЧЁТ($A$12:$A$5006)))
При каждый раз пересчете листа будет извлекаться новая выборка .
Т.к. значений в выборке значительно меньше, то и ее гистограмма визуально отличается от гистограммы генеральной совокупности .
Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом N элементов. Причин для ее возникновения может быть несколько, например:
- Объем данных слишком велик, поэтому мы удовлетворяемся анализом случайной выборки из полного набора данных.
- Выбор победителей из числа участников какого-либо конкурса или лотереи.
В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):
Способ 1. Случайная сортировка
Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС (RAND) . Затем отсортировать наш список по добавленному столбцу (Данные - Сортировка) и взять N первых элементов из получившейся таблицы:
Минусы такого способа очевидны - придется вручную каждый раз пересортировывать список, если нам необходимо будет сделать другую случайную выборку. В плюсах - простота и доступность.
Способ 2. Функция НАИМЕНЬШИЙ
Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка N позиций с наименьшим случайным числом в столбце А:
После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (VLOOKUP).
Способ 3. Случайная выборка без повторов - функция Lotto на VBA
Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис - Макрос - Редактор Visual Basic), вставим новый модуль через меню Insert - Module и скопируем туда текст вот такой функции:
У этой функции будет три аргумента:
- Bottom - нижняя граница интервала случайных чисел
- Top - верхняя граница интервала случайных чисел
- Amount - количество случайных чисел, которое мы хотим отобрать из интервала
Т.е., например, чтобы отобрать 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)
Теперь эту функцию легко использовать для отбора случайных значений. Добавим к нашему списку столбец с нумерацией и будем отбирать людей по случайным номерам, которые генерирует функция Lotto:
Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т.е. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки.
Ну, а дальше останется при помощи уже знакомой функции ВПР (VLOOKUP) вытащить имена из списка, соответствующие случайным номерам.
В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту.
Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.
Из этой статьи вы узнаете, как в Excel создать случайную выборку на основе имеющихся данных. Случайные выборки позволяют создавать объективные выборки генеральной совокупности данных.
Запустите программу Microsoft Excel. Также можно открыть существующую таблицу Excel, если в ней содержится нужная совокупность данных.
- Этот процесс зависит от типа данных, но рекомендуем начать вводить данные в столбце «A».
- Например, введите имена пользователей в столбце «А», а их ответы (например, «да» или «нет») в столбце «В».
Убедитесь, что вы ввели все необходимые данные. Если это так, можно приступить к созданию случайной выборки.
Щелкните правой кнопкой мыши по букве самого левого столбца. Например, если данные начинаются в столбце «A», щелкните правой кнопкой мыши по «A» в верхней части страницы.
- Теперь столбец, который был столбцом «А», станет столбцом «B» и так далее.
- Если Excel автоматически изменит формат функции «СЛЧИС», удалите ее, а затем повторно введите.
- На компьютере под управлением Mac удерживайте ⌘ Command вместо Control .
- Также можно щелкнуть правой кнопкой мыши по ячейке с функцией «СЛЧИС», а затем в меню выбрать Копировать .
- Щелчок по ячейке A1 и ее выделение могут привести к ошибке сортировки.
- Например, если данные расположены в столбцах «B» и «C» и в строках с 1 по 100, удерживайте ⇧ Shift и щелкните по A100, чтобы выделить ячейки с A2 до A100 столбца «А».
- На компьютере Mac удерживайте ⌘ Command вместо Control .
- Также нужно выделить случайные числа, которые находятся в столбце слева от столбцов с данными.
- Например, если данные расположены в столбцах «B» и «C» и в строках с 1 по 50, щелкните по ячейке C50, удерживая нажатой ⇧ Shift .
- Также можно удерживать клавишу мыши и перетащить курсор из левого верхнего угла в правый нижний угол данных (или наоборот), чтобы выделить их.
- На компьютере Mac щелкните двумя пальцами (или удерживайте Ctrl и нажмите кнопку мыши), чтобы открыть контекстное меню.
Нажмите Сортировка от минимального к максимальному . Также можно выбрать опцию Сортировка от максимального к минимальному — важно только то, что данные будут упорядочены случайным образом в соответствии со значениями функции «=СЛЧИС()» в столбце «A».
- Выберите первую, последнюю или центральную половину данных. Если половина данных включает чрезмерное количество значений, выберите меньшую долю данных (например, одну восьмую данных).
- Выберите все нечетные или четные данные. Например, если данные состоят из последовательного набора чисел от 1 до 10, выберите числа 1, 3, 5, 7, 9 или числа 2, 4, 6, 8, 10.
- Выберите несколько случайных данных. Этот метод подойдет для больших наборов данных, когда даже их половина включает чрезмерное количество значений.
Выберите данные, которые войдут в случайную выборку. Так вы получите объективную выборку для анализа опроса, раздачи продукта или чего-то подобного.
- Если на компьютере нет Microsoft Excel, в интернете есть другие аналогичные бесплатные программы (например, Google Таблицы или Outlook Excel), в которых можно создать случайную выборку.
- Существуют мобильные версии Excel для iPhone и Android-устройств, поэтому создавать электронные таблицы можно «на ходу».
- Если не пользоваться случайной выборкой для получения результата (например, при опросе после обновления услуг), он будет предвзятыми и, следовательно, неточными.
Дополнительные статьи
Об этой статье
В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту.
Читайте также: