32. Тасование колоды

Оператор выбора элементов, называемый сочетанием, реализован в электронных таблицах в виде функции COMBIN(). Так, например, с помощью формулы =COMBIN(60,7) можно определить, сколько вариантов семикарточной стартовой руки можно вытянуть из 60-карточной колоды в игре Magic: the Gathering при том условии, что в колоде нет одинаковых карт (если некоторые карты дублируются, что бывает чаще, вам придется выполнять ряд дополнительных действий). COMBIN — это сокращение от слова combination — «комбинация». Выбор такого названия объясняется тем, что вытягивание нескольких карт без возвращения их в колоду и без учета порядка их расположения в математике часто называют получением комбинации.

Когда порядок расположения учитывается, говорят о получении перестановки (permutation), соответственно, в электронных таблицах для этого используется функция PERMUT(). Так, узнать, сколько существует способов выбора 10 лучших игроков из 1000 (с учетом порядка расположения внутри этой первой десятки), можно с помощью формулы =PERMUT(1000,10). Данные функции сослужат вам хорошую службу, если вы не хотите тратить время на запоминание соответствующих математических формул.

Наряду с этими двумя функциями существует ряд функций, позволяющих сымитировать процесс случайного тасования. Мы уже встречались с функцией RAND(), генерирующей псевдослучайное число в диапазоне от 0 до 1. Еще есть функция RANK(), которая принимает два параметра: значение и диапазон ячеек, — и ищет это значение в диапазоне. В качестве результата она выдает номер позиции, занимаемой этим значением в списке: 1 соответствует первой позиции, 2 — второй, 3 — третьей и т.д.

Попробуйте сделать следующее: введите формулу =RAND() в ячейки C1:C52, чтобы каждая из них содержала псевдослучайное число. Затем в ячейку D1 введите формулу =RANK(C1,$C$1:$C$52) и с помощью функции автозаполнения продублируйте ее в расположенных ниже ячейках вплоть до D52. После этого столбец D будет содержать числа от 1 до 52, расположенные в случайном порядке!

Теперь у вас не будет проблем, если ваша колода содержит карты с номерами от 1 до 52. Но что, если вы будете использовать стандартную колоду карт? В таком случае потребуется создать нечто вроде таблицы подстановки, сопоставляющей числа от 1 до 52 с реальными картами. Этой цели в электронных таблицах служит функция VLOOKUP(), которая действует немного замысловато: она принимает определенную таблицу (то есть прямоугольный блок ячеек), находит заданное значение в крайнем левом столбце и возвращает соответствующее значение из одного из других столбцов. Она принимает три параметра: значение или ключ, по которому производится поиск, составляющий таблицу диапазон ячеек и номер столбца, из которого извлекается значение после нахождения совпадающего с ключом значения в первом столбце, где 1 означает крайний левый столбец, 2 — второй слева столбец, 3 — третий и т.д. Поскольку поиск по ключу всегда выполнятся в крайнем левом столбце, при использовании функции VLOOKUP() данные не могут располагаться произвольно — значения, с которыми сопоставляется ключ, всегда должны находиться слева.

Чтобы посмотреть, как можно применять эту функцию, создадим более продвинутую версию нашей таблицы для тасования карт, в которой пока случайным образом располагаются числа от 1 до 52. В ячейки A1:A52 последовательно введите числа от 1 до 52. В ячейки B1:B52 введите названия 52 карт стандартной колоды. При желании здесь можно указать сокращенные названия, обозначая достоинство и масть соответствующей буквой или цифрой. Например, туз пик можно обозначить AS (ace of spades), десятку треф — TC (ten of clubs), а пятерку бубен — 5D (5 of diamonds). После этого столбец B будет содержать неперетасованную колоду карт.

Если вводить 52 карты вручную кажется вам утомительным занятием, есть более простой способ. Отступив немного вправо, в ячейках J1:J13 перечислите все ранги карт: A, 2, 3, 4, 5, 6, 7, 8, 9, T, J, Q, K (в такой или любой другой последовательности). Продублируйте этот набор значений с помощью копирования и вставки так, чтобы у вас было четыре набора рангов в ячейках J1:J52. Затем в ячейке K1 введите обозначение одной из четырех мастей: C (clubs — трефы), D (diamonds — бубны), H (hearts — червы) или S (spades — пики) — и продублируйте его до ячейки K13 с помощью функции автозаполнения. Таким же образом введите обозначения трех остальных мастей, дойдя до ячейки K52. После этого можно получить достоинство и масть каждой карты, посчитав содержимое столбцов J и K. Теперь в ячейке B1 объедините содержимое ячеек J1 и K1 с помощью формулы =CONCAT(J1,K1) и скопируйте эту формулу в расположенные ниже ячейки с помощью команды Fill Down (Заполнить вниз). Функция CONCAT() принимает любое количество параметров, представляющих собой ячейки, диапазоны ячеек или данные, интерпретирует их как строки (то есть последовательности символов) и объединяет в одну строку. При желании можете записать ранги и масти в виде полных слов, например Queen (королева) и Hearts (червы), и соединить их с помощью формулы =CONCAT(J1," of ", K1), получив таким образом полные названия карт (в данном случае Queen of Hearts — королева червей).

Теперь в ячейке E1 введите формулу =VLOOKUP(D1,$A$1:$B$52,2) и продублируйте ее до ячейки E52 с помощью функции автозаполнения. Здесь мы берем значение ячейки D1 (одно из случайных чисел от 1 до 52), находим его среди отсортированных по порядку чисел в столбце A и извлекаем название карты из расположенной правее ячейки столбца B (передав в качестве третьего параметра число 2, мы указали, что возвращать значение следует из второго столбца указанного диапазона данных, то есть из B). После этого столбец E будет содержать перетасованную колоду карт!

Если вам не нравится функция VLOOKUP(), которая требует, чтобы ключи располагались в крайнем левом столбце, вместо нее можно использовать две другие функции. Функция MATCH() возвращает позицию заданного ключа в диапазоне ячеек, находящемся в одном столбце или строке. В качестве первого параметра ей передается ключ, который нужно найти, а в качестве второго — диапазон ячеек. Если ключ будет найден в крайней верхней или крайней левой позиции, функция возвратит значение 1, если во второй слева или сверху позиции — значение 2 и т.д. Необязательный третий параметр указывает, что следует делать, если ключ не обнаружен, по умолчанию он равен 1. Значение 1 означает, что список следует отсортировать по возрастанию и, если ключ не обнаружен, возвратить позицию наиболее близкого к нему меньшего значения. Значение –1 означает, что нужно отсортировать список по убыванию и, не обнаружив ключа, возвратить позицию наиболее близкого к нему большего значения. Значение 0 означает, что список может быть неотсортированным и функция должна выявлять только точное совпадение. Обычно функция MATCH() используется с третьим параметром, равным нулю. Она позволяет выполнять поиск по ключу в любом нужном вам столбце, а не только в крайнем левом.

Функция MATCH() применяется в сочетании с функцией INDEX(), которая возвращает значение из диапазона ячеек по номеру строки и столбца. Эта функция принимает три параметра. В качестве первого параметра ей передается диапазон ячеек. Второй параметр указывает номер строки: 1 означает крайнюю верхнюю строку, 2 — вторую сверху и т.д. Третий параметр указывает номер столбца: 1 означает крайний левый столбец, 2 — второй слева и т.д. Например, формула =INDEX(A1:D4,2,3) возвратит содержимое ячейки C2, поскольку именно она находится во второй сверху строке и третьем слева столбце в данном диапазоне ячеек. Формула =INDEX(E3:G5,3,1) возвратит содержимое ячейки E5, потому что именно она располагается в третьей сверху строке и первом слева столбце в блоке ячеек E3:G5.

Таким образом, мы можем создать более простую версию электронной таблицы. Снова начав с пустой таблицы, введите названия 52 карт в ячейки A1:A52. В ячейки B1:B52 введите формулу =RAND(). В ячейку C1 введите формулу =INDEX($A$1:$A$52,RANK(B1,$B$1:$B$52), 1), а затем продублируйте ее до ячейки C52 с помощью функции автозаполнения. В данном случае нам даже не потребовалась функция MATCH(), однако она может оказаться полезной, например, когда нужно найти какой-нибудь предмет в таблице выпадения добычи.

Если вы хотите просто отобразить перетасованную колоду карт, возможно, будет лучше скрыть остальные четыре столбца с данными, чтобы они никого не сбивали с толку. Выделите одну или несколько строк или столбцов, щелкая кнопкой мыши на заголовках, а затем, щелкнув правой кнопкой мыши, выберите в меню команду Hide Rows (Скрыть строки) или Hide Columns (Скрыть столбцы). Попробуйте скрыть таким образом столбцы A — D: они исчезнут, и ничто уже не будет отвлекать внимания от перетасованной колоды. Однако в заголовке столбца E будет отображаться небольшая направленная вправо стрелка, говорящая о наличии одного или нескольких скрытых столбцов слева от этого столбца (рис. 32.1). Если вы скроете столбцы B — D, то также увидите в заголовке столбца A направленную влево стрелку, указывающую на наличие одного или нескольких скрытых столбцов справа от этого столбца.

Рис. 32.1

На практике рекомендуется скрывать столбцы и строки как можно реже. Скрытые столбцы или строки легко не заметить, поскольку на их присутствие будут указывать лишь один или два крошечных треугольника и пропуск в последовательности буквенных обозначений столбцов или нумерации строк. Копирование и вставка диапазона, включающего в себя скрытые ячейки, чреваты ошибками и могут привести к неожиданным результатам. Если при написании формулы вы сошлетесь на такой диапазон, не заметив наличия скрытых ячеек, то можете получить ошибки, не понимая, чем они были вызваны. Поэтому, если вам абсолютно необходимо скрыть часть данных, чтобы представить свою таблицу в более рафинированном виде, возможно, проще будет вынести промежуточные данные на отдельный рабочий лист и ссылаться в формулах на его ячейки (о том, как это сделать, будет рассказано в главе 33).

Зачем же мы вообще упоминаем о сокрытии столбцов и строк, если эту функциональность не рекомендуется использовать? Мы делаем это для того, чтобы вы могли легко идентифицировать и устранить источник проблемы, если скрытые строки или столбцы будет содержать электронная таблица, созданная кем-то другим, или если вы случайно скроете часть своих данных, по ошибке выбрав не ту команду в контекстном меню. Чтобы снова отобразить скрытые ячейки, нужно просто щелкнуть на крошечной стрелке в заголовке строки или столбца.

Выпадение добычи

Во многих играх игроку приходится носиться по окрестностям, уничтожая монстров ради золота, опыта и добычи, и если остающаяся от поверженного врага добыча каждый раз выбирается случайным образом, это называют выпадением добычи. Разработчики игр составляют таблицы выпадения добычи, указывающие, какая добыча и с какой частотой должна выпадать из тех или иных противников. Все это можно смоделировать в электронной таблице.

Выбор различных предметов из таблицы выпадения добычи напоминает процесс тасования колоды карт с тем отличием, что обычно выбор осуществляется независимым образом, а значит, колода должна каждый раз перетасовываться заново. В то же время создание большой таблицы для тасования колоды (описанным ранее способом) исключительно для определения того, какой предмет окажется, скажем, в первой ячейке, будет откровенным излишеством. Кроме того, такой подход непрактично использовать, когда нужно имитировать выпадение сразу нескольких предметов добычи, поскольку тогда вам придется перетасовывать колоду по несколько раз, чтобы каждый из них выбирался независимо от других.

Существует и более простой способ реализации выбора, который сводится к применению мощной функции INDIRECT(). Она принимает один строковый параметр и интерпретирует его как ссылку на ячейку. Например, формула =INDIRECT("B3") делает то же самое, что и формула =B3. Однако в данном случае мы можем создавать собственную строку для случайного выбора одной из ячеек в заданном диапазоне.

Попробуйте сделать следующее: в ячейках A1:A6 составьте список из шести различных предметов добычи. Это может быть что угодно: 1000 золотых монет, артефакт невыразимой крутизны, купон на 10%-ную скидку в магазине и т.д. Теперь в ячейке B1 запишите формулу для случайного выбора одного из этих предметов: =INDIRECT("A"&RANDBETWEEN(1,6)).

Амперсанд (&) в ней объединяет две строки, что также называют конкатенацией. Хотя в разных редакторах электронных таблиц для конкатенации используются разные функции, все редакторы позволяют объединять несколько строк с помощью символа &. Таким образом, данная формула генерирует случайное целое число в диапазоне от 1 до 6, ставит перед ним букву A, чтобы получилась одна из ячеек диапазона A1 — A6, и с помощью функции INDIRECT() извлекает значение этой конкретной ячейки. Чтобы получить несколько предметов добычи, мы могли бы просто продублировать формулу из ячейки B1 с помощью команды Fill Down (Заполнить вниз).

Конкатенацию можно использовать и для составления полного названия предмета добычи из различных составных частей. Например, взгляните на рис. 32.2.

Рис. 32.2

На основе этой таблицы можно генерировать случайный вид оружия:

=INDIRECT("A"&RANDBETWEEN(1,6))&INDIRECT("B"&RANDBETWEEN(1,6))

&INDIRECT("C"&RANDBETWEEN(1,6))

Эта формула извлекает по одному элементу из первых трех столбцов таб­лицы и объединяет их в одну строку. Чтобы не получить в итоге что-то вроде IceDaggerofStunning, поставьте дополнительные пробелы после слов в столбце A и перед словами в столбце C. Обратите внимание на то, что пустые ячейки A6 и C6 позволяют получить стандартные версии того или иного оружия.

До сих пор мы предполагали, что, как и при вытягивании карт из колоды, каждый предмет в таблице добычи может быть выбран с равной степенью вероятности. Но что, если вам нужно, чтобы определенные предметы выпадали чаще или реже других? Этого можно добиться несколькими способами.

Самый простой состоит в том, чтобы использовать большее количество записей для представления предметов, которые должны выпадать чаще других. Так, если у вас есть распространенный, редкий и эпический предметы добычи, которые должны выпадать в 60, 30 и 10 % случаев соответственно, можно составить список из десяти записей, шесть из которых будут представлять распространенный предмет, три — редкий и одна — эпический.

А что делать, если нужные вам вероятности не представляют собой красивые круглые числа? Если ваша команда аналитиков установила, что для оптимальной монетизации распространенный предмет должен выпадать с вероятностью 55,79744 %, а технические специалисты сказали, что использовать таблицу добычи, содержащую 10 млн записей, крайне нецелесообразно, поскольку она займет слишком много памяти?

В таком случае можно применить функцию VLOOKUP(), делая это немного не так, как было описано ранее. Взгляните на рис. 32.3.

Рис. 32.3

Столбец A здесь содержит значения суммарной относительной доли, которые записаны таким образом, чтобы, сгенерировав случайное число в диапазоне от 0 до 1, мы могли отнести результат к следующим категориям: Common (Распространенный), если он находится в диапазоне от 0 до 0,65 (что составляет 65 % от всей добычи), Uncommon (Нераспространенный), если относится к диапазону от 0,65 до 0,95 (30 %), Rare (Редкий), если находится в диапазоне от 0,95 до 0,99 (4 %), Legendary (Легендарный), если относится к диапазону от 0,99 до 0,999 (0,9 %), и Epic (Эпический), если он больше чем 0,999 (0,1 %).

Мы можем извлечь из этой взвешенной таблицы степень редкости, используя следующую формулу:

=VLOOKUP(RAND(), A1:B5,2)

Она генерирует число от 0 до 1 и ищет его в столбце A, по умолчанию выбирая равное искомому или самое близкое меньшее число, а затем возвращает соответствующий элемент из столбца B.

Побочные квесты

Побочный квест 32.1. Тасование колоды

Давайте попрактикуемся в тасовании карт. В игре Magic: the Gathering колода обычно состоит из 60 карт, часть которых составляют карты заклинаний (помогают победить в игре, но работают за счет расходования маны) и карты земель (обычно дают за каждый ход по 1 единице маны, расходуемой на заклинания). На каждом ходе можно использовать только одну земельную карту, и мана не переносится из одного хода в другой, поэтому ее количество, доступное игроку на каждом ходе, равно либо номеру этого хода, либо количеству земель, вытянутых из колоды на данный момент, в зависимости от того, что меньше.

Допустим, что в колоде игрока 20 земель и 40 заклинаний и он сначала вытягивает семь карт в качестве исходной руки, а затем продолжает вытягивать по одной карте на каждом ходе. Смоделируйте одну операцию перетасовки этой колоды и определите, на каком ходе игрок впервые получит 6 единиц маны. Пересчитайте ячейки (Ctrl+R) несколько раз и определите средний результат. (Также можно продублировать данные несколько сотен раз с помощью копирования и вставки и вычислить среднее значение этих результатов.)

Побочный квест 32.2. Вероятности в покере

Давайте попрактикуемся в применении функций COMBIN() и PERMUT(). Рассчитайте вероятность получения в покере некоторых или всех пятикарточных выигрышных комбинаций при использовании стандартной колоды из 52 карт:

• пара (две карты из пяти имеют одинаковый ранг);

• две пары (четыре карты из пяти образуют две пары разных рангов);

• три одинаковые (три карты из пяти одного ранга);

• стрит (пять карт последовательно возрастающего ранга, например 2–3–4–5–6, при этом туз считается самым высоким рангом, потому последовательность A–2–3–4–5 не является стритом);

• флеш (все пять карт одной масти);

• фулл-хаус (три карты из пяти одного ранга, а две другие карты образуют пару);

• каре (четыре карты из пяти одного ранга);

• стрит-флеш (пять карт последовательно возрастающего ранга и одной масти);

• флеш-рояль (карты T–J–Q–K–A одной масти).

Будьте внимательны, чтобы не посчитать какие-либо варианты дважды: например, комбинацию A–A–A–A–K не следует считать двумя парами, хотя, строго говоря, это две отдельные пары тузов. Также обратите внимание на то, что, помимо перечисленных здесь, в покере есть и другие комбинации, поэтому общее количество этих комбинаций меньше общего количества всех возможных комбинаций из пяти карт.

Побочный квест 32.3. Автоматическое генерирование дизайна игр

Используя конкатенацию и функцию INDIRECT(), попробуйте создать генератор, случайным образом определяющий идею игры. Задайте несколько столбцов для различных параметров игры, таких как ее жанр, игровое окружение, тема, целевая платформа и т.д., — можете не сдерживать свое воображение и введите в каждый из них по несколько вариантов. После этого вы сможете случайным образом выбирать по одному элементу из каждого столбца и объединять их в одну строку, описывающую идею игры. Когда соберетесь с друзьями на очередной гейм-джем, предложите им установить случайные ограничения с помощью этого генератора случайных идей.

---