31. Математические функции

До сих пор мы задействовали в формулах только числа, ссылки на другие ячейки и простые арифметические операторы. Однако иногда вам может потребоваться более продвинутая функциональность, на этот случай в электронных таблицах предусмотрены так называемые функции. Хотя существует много различных функций, для вызова каждой из них используется одинаковый синтаксис: сначала вы записываете имя функции, а затем в круглых скобках — все необходимые параметры, если таковые имеются.

Функции можно применять в любой формуле электронной таблицы как сами по себе, так и складывая или умножая их на другие числа, ячейки или функции. Для функции, принимающей параметры, одним из них может быть другая функция, которая, в свою очередь, может принимать в качестве параметра еще одну функцию. При вычислении значения функции в ячейке электронная таблица сначала вычисляет значения параметров функции, а уже затем на их основе — окончательное значение самой функции. (Однако не забывайте ставить знак равенства перед именем функции!)

Псевдослучайные функции

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

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

Что, если вам нужно сгенерировать случайное число не в диапазоне от 0 до 1, а в каком-то другом? Вы можете задать любой диапазон, складывая или умножая функцию RAND() на определенное число. Например, если нужно получить число в диапазоне от 150 до 250, это можно сделать с помощью формулы =RAND()*100+150. Умножая число в диапазоне от 0 до 1 на 100, мы получаем число в диапазоне от 0 до 100. Складывая этот результат с числом 150, получаем диапазон от 150 до 250.

А если вам нужно получить целочисленный результат, как, например, при бросании кубика 1d6? С помощью формулы =RAND()*6+1 вы можете получить число в диапазоне от 1 до 6,999 999 99. Чтобы получить только целую часть числа, то есть округлить результат до ближайшего целого числа вниз, воспользуйтесь функцией FLOOR(). Она принимает два параметра. В качестве первого из них передается округляемое число. Второй параметр указывает, с какой точностью следует выполнять округление. При округлении до ближайшего целого числа здесь следует записать 1, но вы также можете записать 100 для округления до ближайшей меньшей сотни, или 0,1 — для округления до ближайшего меньшего числа, кратного 0,1, или 33,52 — для округления до ближайшего меньшего числа, кратного 33,52. Таким образом, для кубика 1d6 можно использовать формулу =FLOOR(RAND()*6+1,1). Схожим образом можно применить функцию CEILING(), которая округляет до ближайшего большего числа, и функцию MROUND(), которая округляет до ближайшего числа вверх или вниз.

Возможно, предложенный ранее способ немного громоздок для бросания простого шестигранного кубика, но есть и более простая функция для генерирования целых чисел. Функция RANDBETWEEN() принимает два параметра — нижнюю и верхнюю границы диапазона — и генерирует псевдослучайное целое число, находящее­ся в этих пределах, включая границы. Так, например, формула =RANDBETWEEN(1,6) имитирует бросок кубика 1d6, а формула =RANDBETWEEN(0,1) — подбрасывание монеты, дающее в результате 0 или 1.

Но что, если вам нужно сымитировать бросание кубиков 2d6? Если вы попытаетесь использовать для этого формулу =RANDBETWEEN(1,6)*2, то получите результат броска кубиков не 2d6, а 1d6, умноженный на 2 (например, он никогда не будет равен 7). Вместо этого следует вызвать эту функцию дважды: =RANDBETWEEN(1,6)+RANDBETWEEN(1,6). Еще один возможный подход состоит в том, чтобы записать формулу =RANDBETWEEN(1,6) в двух отдельных ячейках и вычислить результат их сложения в третьей. При бросании большого количества кубиков использовать второй подход будет проще. Так, при броске 100d6 можно записать формулу =RANDBETWEEN(1,6) в одной ячейке, продублировать ее в 100 строках с помощью функции автозаполнения, а затем сложить эти ячейки с помощью функции SUM() в последней ячейке. Функция SUM() принимает любое количество параметров, каждый из которых может быть ячейкой или диапазоном ячеек, и выдает результат сложения всех этих параметров. Например, если результаты броска 100d6 находятся в ячейках A1:A100, то их сумму можно определить с помощью формулы =SUM(A1:A100) (того же результата можно добиться и с помощью формулы =SUM(A1:A50,A51:A100) или =SUM(A1:A10,A25:A50,A77:A100,A51:A76,A11:A24), хотя использовать их, вероятно, будет нецелесообразно).

Если вас интересует, что означают двоеточия в представленных ранее примерах, то они служат для указания диапазона ячеек внутри электронной таблицы. Две разделенные двоеточием ячейки означают, что редактор электронных таблиц должен использовать все содержимое прямоугольника, в противоположных углах которого находятся эти две ячейки. Так, диапазон A1:A5 включает в себя пять ячеек (A1, A2, A3, A4 и A5), диапазон C3:D4 — четыре (C3, C4, D3 и D4), а диапазон E24:D22 — шесть (D22, D23, D24, E22, E23 и E24, хотя обычно принято сначала указывать ячейку, находящуюся в левом верхнем углу диапазона, а затем — находящуюся в его правом нижнем углу). Редактор электронных таблиц может даже исправить введенное вами обозначение в соответствии с этим правилом. Вы также можете указать целую строку или столбец, опустив вторую координату. Так, формула =SUM(A:B) просуммирует все содержимое столбцов A и B, а формула =SUM(3:3) — все содержимое строки 3.

Можно заметить, что при наличии в электронной таблице ячеек с функциями RAND() или RANDBETWEEN() их содержимое изменяется после изменения содержимого любой другой ячейки. Это объясняется тем, что редактор электронных таблиц пересчитывает все цифры после внесения любых изменений в рабочий лист (именно поэтому немедленно обновляются и ячейки с такими функциями, как SUM(), после изменения ячеек, на которые они ссылаются). Чтобы принудительно пересчитать значения, то есть сымитировать бросание кубиков, без необходимости изменять ячейки, в редакторе Google Spreadsheets нужно нажать сочетание клавиш Ctrl+R. Это может оказаться полезным, например, когда нужно применить метод Монте-Карло несколько раз, чтобы убедиться, что его результаты находятся в довольно узком диапазоне.

Статистические функции

При использовании метода Монте-Карло могут оказаться полезными еще несколько функций. Когда требуется вычислить среднее значение большого набора чисел, это можно сделать, сложив их с помощью функции SUM() и разделив результат на их количество. Ведь, в конце концов, именно так выглядит математическое определение среднего значения. Однако такой подход чреват ошибками, если количество ячеек впоследствии может измениться. Если при изменении количества ячеек вы забудете соответствующим образом скорректировать формулу, результат окажется неверным. Более простой способ состоит в применении функции AVERAGE(). Как и функция SUM(), она принимает любое количество параметров, каждый из которых может быть ячейкой или диапазоном ячеек, и усредняет все ячейки, содержащие числа (и, что очень удобно, игнорирует пустые ячейки и ячейки с текстом и другими нечисловыми данными). Так что если все ваши данные находятся в одном столбце, например C, который, помимо данных, содержит только строку заголовка, то вместо того, чтобы указывать точный диапазон ячеек, можете вычислять среднее значение с помощью формулы =AVERAGE(C:C), и вам не придется ничего корректировать при добавлении строк с данными.

А что делать, если у вас много строк с данными и нужно определить точное их количество? Допустим, вы загрузили в электронную таблицу экспортированные из игры результаты игрового тестирования и некоторые из этих строк не содержат данных, поскольку игра не была доведена до конца из-за сбоя, но вам нужно знать, сколько у вас строк с данными. Выяснить это можно с помощью функции COUNT(), которая, подобно функциям SUM() и AVERAGE(), принимает любое количество параметров, представляющих собой ячейки или диапазоны ячеек, и выдает общее количество ячеек, содержащих числовые данные.

Наряду с функцией AVERAGE(), которая выдает среднее значение, существует и функция MEDIAN(), выдающая медианное значение (то значение из группы, которое окажется посередине, если эти значения будут отсортированы в порядке возрастания). И как вы уже могли догадаться, существует также функция MODE(), которая находит модальное значение (оно встречается в наборе данных наибольшее количество раз). Существует много других полезных функций, например функции MAX() и MIN(), которые выявляют наибольшее и наименьшее значения в переданном им наборе значений, или функция STDEV(), которая вычисляет стандартное отклонение, нахождение которого вручную, как правило, требует больших усилий и чревато ошибками. Все они схожи с функциями SUM() и AVERAGE() в том, что принимают любое количество параметров, представляющих собой числа, ячейки или диапазоны ячеек.

Наконец, следует упомянуть еще об одной функции, которая может быть полезной при использовании статистики и аналитики. Функция CORREL() принимает в качестве параметров два одинаковых по размеру диапазона ячеек и поэлементно сравнивает их. В качестве результата она выдает коэффициент корреляции в диапазоне от –1 до 1, где –1 означает наличие строгой обратной корреляции, 0 — отсутствие корреляции и 1 — наличие строгой прямой корреляции.

Условные функции

Часто требуется проверить, удовлетворяет ли значение ячейки конкретному условию (например, имитируя с помощью функции RAND() бросок кубика, имеющий определенную вероятность успеха, можно затем проверять в отдельной ячейке, был ли бросок успешным). Для таких случаев предусмотрена одновременно удобная и универсальная функция IF(), которая принимает три параметра. Первый представляет собой булево выражение (дающее в результате истину или ложь), в котором обычно одна вещь сравнивается с другой, второй — значение, которое возвращается, если выражение истинно, а третий — значение, возвращаемое, если оно ложно. Вот несколько примеров вызова этой функции.

• =IF(A2>4,1,0). Здесь мы проверяем, превышает ли значение в ячейке A2 число 4. Если это так, то результат будет равен 1, если нет — 0. Такая проверка часто выполняется, когда нужно определить, сколько объектов соответствует определенному критерию.

• =IF(B5="Fire",C5,""). Здесь мы проверяем, содержит ли ячейка B5 текст Fire (обратите внимание на то, что, в отличие от чисел, текст нужно заключать в кавычки). Если это так, то результат будет равен содержимому ячейки C5. В противном случае ячейка не будет содержать никакого значения (пара кавычек, между которыми ничего нет, означает пустую ячейку). Обратите внимание: если в этом выражении заменить Fire на "", то формула будет проверять, является ли ячейка B5 пустой. Такую проверку можно использовать, например, для установки определенных параметров у всех противников, относящихся к определенной стихии.

• =IF(C1>=3,IF(C1<=6,15,0),0). Эта формула возвращает 15, если значение ячейки C1 находится в диапазоне от 3 до 6 включительно, и 0 — в противном случае. Обратите внимание на то, что функция IF() может находиться внутри другой функции IF(), что программисты называют вложенными операторами if. Хотя это выглядит немного странно и запутанно, на самом деле здесь нет ничего сложного. Нужно лишь понять, что каждый из трех параметров функции IF() может представлять собой не только число, но и выражение или формулу.

Для выполнения последнего вида проверки можно использовать также логическую функцию AND(), которая принимает любое количество параметров и возвращает true, если все они истинны, и false — в противном случае. Выражение из предыдущего примера можно переписать следующим образом: =IF(AND(C1>=3,C1<=6), 15,0). Полезными могут оказаться и такие логические функции, как OR() (возвращает false, если все ее параметры ложны, и true, если хотя бы один из них истинен) и NOT() (определяет, равен ли переданный ей параметр значению true или false, и возвращает противоположное значение).

При использовании симуляции Монте-Карло часто требуется выполнять определенную проверку с помощью функции IF() многократно (обычно по одному разу в каждой строке) с последующим сложением результатов.

Например, при генерировании случайных результатов броска кубика, которые могут быть успешными или неудачными, часто требуется определить среднюю относительную долю успешных результатов. Для этого можно в одном из столбцов использовать функцию IF(), которая будет принимать критерий успеха в качестве первого параметра и выдавать 1 и 0 в случае успешного и неудачного результата проверки соответственно. После этого еще в одной ячейке следует вычислить среднее значение этого столбца с помощью функции AVERAGE(), что и будет представлять собой среднюю долю успешных результатов. Однако есть более компактный способ выполнения таких расчетов, который сводится к применению функции COUNTIF(). Она принимает два параметра: некоторый диапазон ячеек и булево выражение, используемое в качестве критерия при проверке ячеек этого диапазона. В качестве результата функция COUNTIF() выдает количество ячеек, удовлетворяющих заданному критерию.

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

• =COUNTIF(A:A,5). Эта формула просматривает все содержимое столбца A и подсчитывает количество ячеек, содержащих число 5.

• =COUNTIF(A:A,B2). Эта формула просматривает все содержимое столбца A и подсчитывает количество ячеек, содержащих такое же значение, как и ячейка B2.

• =COUNTIF(B3:D5, "x"). Эта формула просматривает девять ячеек: B3, B4, B5, C3, C4, C5, D3, D4 и D5 — и подсчитывает количество ячеек, содержащих текст x.

• =COUNTIF(C:C,">=14"). Эта формула подсчитывает, в скольких ячейках в столбце C значение больше или равно 14.

А если, наоборот, требуется подсчитать количество ячеек, не удовлетворяющих определенному критерию? Вычтите результат функции COUNTIF() из результата функции COUNT(), полученного для того же диапазона ячеек. Так, в первом из представленных ранее примеров вы можете определить, сколько ячеек в столбце A содержит числовое значение, не равное 5, с помощью формулы =COUNT(A:A)-COUNTIF(A:A,5).

В то время как функция COUNTIF() возвращает количество ячеек, удовлетворя­ющих критерию, иногда требуется получить не количество таких ячеек, а их сумму. Это можно сделать с помощью функции SUMIF(), которая принимает такие же параметры, как и COUNTIF(). Допустим, вы моделируете бой с облаченным в доспехи противником, где атаки, наносящие не более 3 очков урона, не срабатывают, а атаки с более высоким уроном срабатывают как обычно. Если количество очков урона, нанесенного в ходе одного раунда боя, будет записано в диапазоне E2:E6, то итоговое количество можно подсчитать с помощью формулы =SUMIF(E2:E6,">3").

Метод Монте-Карло и метод перебора

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

Начинающие геймдизайнеры обычно слишком часто выбирают из этих трех способов метод Монте-Карло. Хотя это очень мощный инструмент, его недостатком является то, что, в отличие от двух других методов, он дает приблизительный ответ, который изменяется при каждом пересчете содержимого таблицы. Обладая соответствующими знаниями, лучше использовать метод, основанный на теории вероятностей. Если уровень ваших знаний недостаточно высок, стоит применить пусть и не очень элегантный, зато надежный метод полного перебора.

В качестве примера рассмотрим простейший случай, когда вы бросаете кубик 2d6. Здесь у вас лишь 36 возможных вариантов, потому будет совсем не сложно вручную перечислить и учесть в расчетах каждый из них. Это гораздо проще, чем имитировать с помощью метода Монте-Карло несколько тысяч попыток броска 2d6, и к тому же, в отличие от метода Монте-Карло, даст не приблизительный, а точный ответ. Хотя на первый взгляд перебор всех возможных вариантов кажется откровенной наивностью и многих программистов приведет в ужас даже сама мысль о возможности такого подхода, этот метод вполне применим и может быть очень полезен в сфере разработки игр.

Конечно, с увеличением количества возможных вариантов применять метод перебора становится сложнее, однако это вполне можно сделать в большинстве реальных ситуаций, с которыми приходится сталкиваться геймдизайнеру. Получить представление о масштабе решаемой задачи можно, подсчитав общий размер пространства возможностей. Например, при бросании 7d4 общее количество возможных вариантов составляет 4 × 4 × 4 × 4 × 4 × 4 × 4 (вычислите произведение с помощью калькулятора или просто введите формулу =4*4*4*4*4*4*4*4, =POWER(4,7) либо =4^7 в одну из ячеек электронной таблицы). Это дает 16 384 варианта, что в принципе слишком много для того, чтобы выписывать каждый из них вручную… В то же время электронная таблица легко может вместить такое количество строк, и мы потратим не так уж много времени, если будем использовать методы ускорения ввода.

Один из таких методов сводится к тому, чтобы выборочно применить копирование и вставку, клавиши со стрелками и клавиши Ctrl и Shift. Давайте попробуем сделать это в столбцах A — G, для простоты начав со строки 1 (если все будет сделано правильно, мы должны закончить в строке 16 384). Сначала нужно записать числа от 1 до 4 в ячейках A1:A4. Эти числа будут циклически повторяться до конца всей последовательности чисел. После ввода числа 4 в ячейку A4 перейдите вправо, нажав клавишу Стрелка вправо или клавишу Tab, и введите число 1 в ячейке B4. Затем нажмите клавишу Стрелка вверх, удерживая клавишу Ctrl, чтобы перейти к следующей ячейке со значением… но, поскольку выше находятся только пустые строки, вы сразу перейдете к верхней ячейке B1. Здесь тоже введите число 1. Теперь нажмите клавишу Стрелка вниз, удерживая клавиши Shift и Ctrl. Это расширит выделение до следующего значения в ячейке B4 (рис. 31.1).

Рис. 31.1

Теперь нажмите Ctrl+D для применения команды Fill Down (Заполнить вниз). Тем самым вы вставите число 1 и в промежуточные ячейки B2 и B3.

Обратите внимание на то, что курсор при этом останется в верхней ячейке диапазона, B1. Нажмите клавишу Стрелка влево, чтобы вернуться в ячейку A1 (тем самым сняв выделение с диапазона B1:B4), и, удерживая клавиши Ctrl и Shift, нажмите клавишу Стрелка вниз, чтобы выделить диапазон A1:A4. Скопируйте его, нажав сочетание клавиш Ctrl+C. Теперь, удерживая клавишу Ctrl (без клавиши Shift), нажмите клавишу Стрелка вниз, чтобы перейти к нижней ячейке этого диапазона (A4), а затем нажмите клавишу Стрелка вниз, не удерживая управляющих клавиш, чтобы перейти к ячейке A5. Затем нажмите сочетание клавиш Ctrl+V, чтобы продублировать значения из диапазона A1:A4 в расположенных ниже ячейках (рис. 31.2).

Рис. 31.2

Теперь вставим число 2 в расположенные правее ячейки. Для этого сначала перейдите в нижнюю ячейку диапазона (Ctrl+Стрелка вниз), а затем нажмите клавишу Стрелка вправо, чтобы перейти в столбец B. Введите здесь число 2. Затем с помощью сочетания клавиш Ctrl+Стрелка вверх перейдите к предыдущему блоку единиц и спуститесь в следующую пустую ячейку с помощью клавиши Стрелка вниз. Здесь тоже введите число 2. Теперь выделите все ячейки от верхней до нижней двойки (Ctrl+Shift+Стрелка вниз) и вставьте двойки в пустые ячейки с помощью команды автозаполнения (Ctrl+D). Курсор при этом будет находиться в верхней ячейке этого диапазона, B5. Нажав клавишу Стрелка влево, перейдите в ячейку A5 и выделите диапазон A5:A8 с помощью сочетания клавиш Ctrl+Shift+Стрелка вниз. Скопируйте ячейки (Ctrl+C), после чего перейдите в конец диапазона (Ctrl+Стрелка вниз) и спуститесь к следующей пустой ячейке A9, нажав клавишу Стрелка вниз без удержания других клавиш. Вставьте скопированное содержимое в ячейки A9:A12 (Ctrl+V). Затем таким же образом вставьте число 3 в диапазон B9:B12, продублируйте цепочку 1–2–3–4 в диапазон A13:A16 и вставьте число 4 в диапазон B13:B16.

По правде говоря, нам пришлось проделать слишком много работы для того, чтобы внести значения в 16 ячеек в двух столбцах, и это можно было сделать быстрее, просто вводя эти значения одно за другим, что вы, возможно, и сделали. Однако следует учесть, что этот метод можно масштабировать на любое количество ячеек. Теперь вы можете ввести число 1 в ячейки C1:C16, продублировать содержимое ячеек A1:B16 в ячейках A17:B32, ввести число 2 в ячейки C17:C32 и т.д., пока не заполните все ячейки вплоть до строки 64. Эти данные будут представлять собой все возможные результаты бросков 3d4. При этом добавление третьего кубика займет столько же времени, сколько и добавление второго, несмотря на то что для второго кубика нужно добавить 12 новых строк, а для третьего — 48.

Заполнив таким же образом столбец D вплоть до строки 256, вы получите все возможные результаты бросков 4d4. Заполните таким же образом оставшиеся столбцы E, F и G — и всего за несколько минут вы получите все возможные результаты бросков 7d4 — все 16 384 варианта.

Если вам кажется, что в этом методе слишком много грубой силы и слишком мало математики, еще один возможный подход состоит в применении формулы. В столбце А введите числа от 0 до 16 383, используя для этого любой предпочитаемый вами метод. Затем запишите приведенные далее формулы в первой строке и продублируйте их в остальных строках с помощью функции автозаполнения:

• ячейка B1 — =MOD(A1,4)+1;

• ячейка C1 — =MOD(FLOOR(A1/4),4)+1;

• ячейка D1 — =MOD(FLOOR(A1/(4^2)),4)+1;

• ячейка E1 — =MOD(FLOOR(A1/(4^3)),4)+1;

• ячейка F1 — =MOD(FLOOR(A1/(4^4)),4)+1;

• ячейка G1 — =MOD(FLOOR(A1/(4^5)),4)+1;

• ячейка H1 — =MOD(FLOOR(A1/(4^6)),4)+1.

Что же здесь происходит? Функция MOD() принимает два параметра и возвращает остаток от целочисленного деления первого параметра на второй. В столбце B мы вычисляем остаток от деления числа из столбца A на число 4, что дает последовательность 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3 и т.д. Поскольку результаты броска d4 варьируются в диапазоне от 1 до 4, а не в диапазоне от 0 до 3, мы добавляем 1 к выдаваемому функцией результату и таким образом получаем последовательность 1, 2, 3, 4, 1, 2, 3, 4, циклически повторяющуюся до конца таблицы. Это также объясняет, почему в столбце A мы начали вводить числа с нуля, а не с единицы: если бы начали с единицы, то в результате получили бы последовательность 2, 3, 4, 1, 2, 3, 4, 1. Хотя она все равно содержала бы все нужные нам числа, с ними удобнее иметь дело, когда они располагаются по возрастанию.

В столбце C значения тоже должны циклически изменяться от 1 до 4, но в четыре раза медленнее, чем в столбце B. Для этого мы предварительно делим содержимое столбца A на 4, отбрасывая остаток с помощью функции FLOOR(). Здесь также можно было бы применить функцию QUOTIENT(), которая, как и функция MOD(), выполняет целочисленное деление, но возвращает не остаток, а целочисленный результат деления: =MOD(QUOTIENT(A1,4), 4)+1. В столбце D значения должны циклически изменяться от 1 до 4 в четыре раза медленнее, чем в столбце C (и соответственно в 16 раз медленнее, чем в B). Остальные столбцы должны вести себя точно так же, но с каждым разом все медленнее. Этот метод работает еще быстрее, чем метод перебора, но, будучи более сложным, иногда требует отладки для обеспечения надлежащей работы всех формул.

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

При использовании симуляций Монте-Карло и метода перебора процесс может идти и очень быстро, и очень медленно в зависимости от того, насколько хорошо вы отточили свои навыки в этой области. Если хотите немного попрактиковаться, пришло время выйти на старт! Если вместе с вами эту книгу читает кто-то из друзей или одноклассников, пригласите их поучаствовать в небольшом турнире.

Побочный квест 31.1. Скоростной перебор вариантов для броска 4d6

Сначала с помощью симуляции Монте-Карло определите вероятность того, что при броске 4d6 суммарный результат окажется в диапазоне от 13 до 15 включительно. Для этого запишите несколько формул для вычисления результата каждой отдельной попытки броска 4d6. После этого можно в каждой строке применить функцию IF(), возвращающую 1 в случае попадания в нужный диапазон и 0 — в противном случае, и усреднить полученные результаты с помощью функции AVERAGE(). Также можно сразу определить частоту попадания в заданный диапазон, применив ко всем строкам функцию COUNTIF(). Пересчитав результаты симуляции несколько раз, определите, чему должна равняться искомая вероятность. Если выполняете это задание вместе с друзьями, узнайте, все ли согласны с вашим выводом.

Затем попробуйте определить то же самое методом перебора. Существует 1296 различных способов броска 4d6. Используя копирование и вставку, автозаполнение и любые подходящие формулы, создайте 1296 строк, представляющих все возможные результаты броска 4d6, а затем получите точный ответ, выполняя одни и те же расчеты для каждого из этих результатов. Поскольку заполнение всех 1296 строк вручную требует больших усилий и чревато ошибками, постарайтесь придумать максимально быстрый и безошибочный способ сделать это. Опытный пользователь электронных таблиц мог бы справиться с этой задачей менее чем за 5 минут, а сколько времени она займет у вас? И насколько близким к истине оказался ответ, полученный с помощью метода Монте-Карло?

Побочный квест 31.2. Корреляция вероятностей

Теперь поближе познакомимся с функцией CORREL(). Вероятно, вы уже успели узнать (из этой книги или откуда-то еще), что наличие корреляции не всегда озна­чает существование причинно-следственной связи. Давайте разберемся с тем, что на самом деле означает наличие корреляции и как она проявляется в реальных данных, чтобы понять, чем могут быть полезны корреляции и как они могут ввести вас в заблуждение.

Создайте новую электронную таблицу и введите в ячейку A1 формулу =RAND(), генерирующую случайное число в диапазоне от 0 до 1. Используя функцию автозаполнения, продублируйте эту формулу в ячейке B1, а затем в столбцах A и B вплоть до 1000-й строки. Теперь столбцы A и B содержат по 1000 псевдослучайных чисел.

В свободных ячейках таблицы введите следующие четыре формулы:

• =CORREL(A1:A10,B1:B10);

• =CORREL(A1:A50,B1:B50);

• =CORREL(A1:A100,B1:B100);

• =CORREL(A1:A1000,B1:B1000).

Конечно, мы знаем, что на самом деле здесь нет никакой корреляции и все эти значения — независимые псевдослучайные числа, поэтому нет никаких причин для того, чтобы увеличение числа в первом списке вело к увеличению или уменьшению соответствующего числа во втором списке. То есть коэффициент корреляции между двумя списками должен равняться нулю. Но, как вы увидите, возвращаемый формулой результат будет немного отличаться от нуля, потому что случайные вариации будут все же порождать некоторый небольшой уровень корреляции. Нажмите Ctrl+R несколько раз и посмотрите, в каком диапазоне колеблется каждый результат. Для списка из 1000 элементов коэффициент корреляции обычно очень невелик и не должен превышать 0,05. Для 100 элементов он может доходить уже до 0,1 или 0,2. Для 50 элементов коэффициент корреляции будет варьироваться в более широких пределах, то сильно приближаясь к нулю, то становясь очень высоким (для двух списков псевдослучайных чисел), — так, этот результат может оказаться даже выше 0,5 или ниже –0,5. Для всего лишь 10 элементов коэффициент корреляции может оказаться выше 0,8 или ниже –0,8, что будет говорить о наличии сильной прямой или обратной корреляции.

А что произойдет, если мы передадим список псевдослучайных чисел, варьирующихся в диапазоне от 0 до 1, функции STDEV()? Произведя некоторые математические выкладки, в которые мы не будем здесь углубляться, можно установить, что в случае равномерного распределения чисел от 0 до 1, которое генерирует функция RAND(), стандартное отклонение составляет , или около 0,2887. Насколько близок к этому значению будет результат формулы =STDEV(A1:A1000)? А что насчет формулы =STDEV(A1:A10)? Попробуйте сделать то же самое с функциями AVERAGE() и MEDIAN(). Результат этих функций должен равняться 0,5, но, как вы увидите, он будет немного отличаться от этого значения.

Чтобы наглядно увидеть, что здесь происходит и как выглядит корреляция, выделите весь блок ячеек A1:B1000 (как вы помните, это можно сделать, нажимая клавиши со стрелками и удерживая клавиши Shift и Ctrl) и выберите в меню команду для построения точечной диаграммы рассеяния. Программа построит график, содержащий 1000 отдельных точек, рассматривая пару значений в каждой строке как координаты x и y. Теперь добавьте линейную линию тренда и отобразите значение R2. Если вы забыли, как это делается, еще раз просмотрите ту часть главы 28, где мы говорили о построении графиков. В данном случае линия тренда должна быть горизонтальной, а значение R2 — очень близким к нулю. Затем постройте этот график, выделяя только 10 и 50 пар точек, и посмотрите, сильно ли он при этом изменится. Сравните значения R2, отображаемые на линии тренда графиков, со значениями, полученными с помощью функции CORREL(). Попробуйте использовать другие разновидности линии тренда, например полиномиальную или экспоненциальную, чтобы посмотреть, как они выглядят при абсолютно случайных данных.

Вывод из данного упражнения сводится к следующему: при выявлении каких-либо корреляций очень важно понимать характер рассматриваемых данных и не слишком сильно полагаться на форму кривой, особенно при экстраполяции за пределы текущего набора данных. Так, например, на начальном отрезке быстрого роста сигмоидная (S-образная) кривая сильно напоминает экспоненциальную. Но, руководствуясь здравым смыслом, можно понять, что, например, количество активных пользователей вашей игры не может изменяться по экспоненциальному закону, даже если форма кривой пока говорит об обратном, потому что количество жителей нашей планеты, готовых сыграть в вашу игру, не бесконечно и рано или поздно вы выйдете на плато!

---