35. Итеративные вычисления

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

Хотя при описании работы с редакторами электронных таблиц мы обычно делаем акцент на редакторе Google Sheets, пришло время поговорить о возможности, которая имеется исключительно в редакторах Microsoft Excel и OpenOffice Calc. Речь идет об итеративных вычислениях, которые могут оказаться полезными при моделировании постепенного нарастания различий с течением времени или по мере выполнения ходов. Но перед тем, как перейти к этой теме, давайте разберемся с тем, что представляют собой так называемые циклические ссылки.

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

Однако в редакторе Excel вы можете выбрать в меню File (Файл) пункт Options (Параметры), в открывшемся диалоговом окне перейти на вкладку Formulas (Формулы) и установить флажок Enable Iterative Calculation (Включить итеративные вычисления). При этом также нужно указать, какое максимальное количество итераций должен выполнять редактор Excel при пересчете циклической ссылки (рис. 35.1).

Рис. 35.1

Если максимальное количество итераций — 100 и в ячейке A1 будет записана формула =A1+1, то при каждом пересчете ячеек, вызываемом модификацией какой-либо ячейки или нажатием клавиши F9, которая в редакторе Excel выполняет ту же функцию, что и сочетание клавиш Ctrl+R в редакторе Google Sheets, значение ячейки А1 будет увеличиваться на 100. Если максимальное количество итераций — 1, то значение этой ячейки будет увеличиваться только на 1.

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

Итеративные вычисления можно использовать также при моделировании PvP-игр, в которых игрокам приходится бороться друг с другом, — игр жанра «файтинг»/«массовая драка», MMO- и MOBA-игр для отслеживания того, как с течением времени меняется уровень здоровья и/или сила персонажа, особенно когда речь идет об атаках с определенным временем срабатывания или временем перезарядки. При этом полезно создать ячейку глобального таймера, которая будет отслеживать количество прошедшего времени, увеличивая свое значение на одну единицу времени (с той степенью дробности, которая имеет смысл в вашей игре). Например, атака с перезарядкой может быть реализована следующим образом.

• Ячейка A1 представляет мощную атаку, которая начинается по истечении времени перезарядки. Если пришло время атаковать, эта ячейка помечается символом X, в противном случае остается пустой: =IF(B1>0," ", "X").

• Ячейка B1 показывает, сколько еще времени будет продолжаться перезарядка. При выполнении атаки в нее заносится значение 10, которое затем уменьшается на 1 с каждой прошедшей секундой: =IF(B1>0,B1-1,IF(ISBLANK(A1),0,10)).

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

• ячейка J1 — глобальный таймер, =J1+1;

• ячейка K1 — формула для вычисления номера текущего хода;

• ячейки L1:L20 — числа от 1 до 20;

• ячейка M1 — =IF($J$1=L1,$K$1,M1). С помощью функции автозаполнения продублируйте эту формулу вниз вплоть до ячейки M20.

А что делать, если вы работаете в редакторе Google Sheets или другом редакторе электронных таблиц, который не поддерживает итеративные вычисления? Итерации можно будет использовать и в этом случае, но потребуется чуть больше места на рабочем листе и чуть больше операций копирования и вставки. Организуйте данные так, чтобы все необходимое для отслеживания игрового состояния, соответствующего отдельному моменту времени, находилось в одной строке, а затем снабдите каждую следующую строку ссылкой на предыдущую. В продолжение предыдущего примера: допустим, мы имеем дело с той же ситуацией, но начинаем с начального игрового состояния, заданного в строке 1 (оно жестко кодируется и не содержит формул, поскольку это фиксированное начальное состояние). Строка 2 при этом будет выглядеть следующим образом:

• ячейка A2 — =IF(B1>0," ", "X");

• ячейка B2 — =IF(B1>0,B1-1,IF(ISBLANK(A2),0,10));

• ячейка J2 — =J1+1;

• ячейка K2 — формула для вычисления номера текущего хода;

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

Если игровое состояние описывается слишком сложными данными, которые не могут поместиться в одной строке в читабельном виде, можно распределить их на несколько строк, а затем продублировать эти строки вниз путем копирования и вставки. Например, если для представления игрового состояния, соответствующего отдельному моменту времени, требуются четыре строки, то начальное состояние будет представлено в строках 1:4, состояние после первого хода — в строках 5:8, состояние после следующего хода — в строках 9:12 и т.д., где каждая ячейка станет ссылаться на соответствующие ячейки из предыдущего блока из четырех строк. В таком случае полезно добавить горизонтальную границу в начале или конце каждого блока, чтобы визуально отделить каждое игровое состояние от соседних. Еще один возможный подход в таком случае состоит в том, чтобы использовать четыре рабочих листа, занимая на каждом из них по одной строке.

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

Побочный квест 35.1. Пара ИИ для игры «Камень, ножницы, бумага»

Реализуйте пару рандомизированных ИИ для игры «Камень, ножницы, бумага», играющих друг против друга.

Если вы используете редактор электронных таблиц с поддержкой итеративных вычислений, в ячейках A1 и A2 генерируйте случайное число с помощью формулы =RANDBETWEEN(1,3), а в ячейках B1 и B2 преобразуйте это число в камень (К), бумагу (Б) или ножницы (Н), задействуя пару вложенных функций IF(). В ячейке B3 определяйте победителя, применяя еще одну цепочку вложенных функций IF(): отображайте число 1, если победил игрок 1 (данные которого находятся в строке 1), число 2, если победил игрок 2 (данные которого в строке 2), или 0 в случае ничьей. В ячейке C1 разместите таймер, увеличивающий свое значение на 1 на каждой итерации. В столбце D последовательно запишите числа от 1 до 30. В столбце E укажите победителей первых 30 игр, используя для этого значение таймера из ячейки C1 и результат игры из B3.

Более сложная задача. Добавьте кнопку сброса в ячейке C2: пусть все остальные формулы в ячейке C1 и столбце E сбрасываются, если ячейка C2 не пустая, и работают как обычно, если она пустая. То есть для того, чтобы сбросить таблицу и начать все сначала, пользователю нужно будет что-то ввести в ячейку C2 и затем ее очистить.

Чтобы сравнить два изученных нами метода, можете сделать следующее.

Если работаете в редакторе электронных таблиц, не поддержива­ющем итеративные вычисления, то в столбцах A и B используйте формулу =RANDBETWEEN(1,3), как и в первом случае, а в столбцах C и D преобразуйте число из столбцов A и B в камень (К), бумагу (Б) или ножницы (Н). В столбце E должен определяться победитель текущей игры, а столбец F должен содержать порядковые номера игр от 1 до 30. Заполните 30 строк, продублировав содержимое первой строки в остальных строках с помощью функции автозаполнения.

---