28. Формулы, ссылки и графики

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

Рис. 28.1

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

Чтобы ввести формулу:

• щелкните на ячейке;

• введите знак равенства (=), а после него — формулу необходимых вам расчетов. Пример: =А5+2.

Вы можете использовать здесь числа, арифметические операторы (+, -, * и /) и, что особенно важно, буквенно-цифровые обозначения других ячеек, тем самым подставляя в формулу их текущее значение. Например, =A5+2 называется ссылкой на другую ячейку.

Чтобы применить описанные действия на практике, создайте в ячейке C1 заголовок третьего столбца для показателя окупаемости. Допустим, нам нужно, чтобы этот показатель начинался с 50 % (или 0,5) в первой строке и увеличивался на 50 % в каждой последующей. Введите значение 0,5 в первой строке, то есть в данном случае в ячейке C2. Затем в ячейке C3 введите формулу =C2+0,5. Вы увидите, что эта ячейка отобразит правильно рассчитанный результат 1 (или 100 %).

Теперь нам нужно, чтобы следующая ячейка (C4) содержала формулу C3+0,5, ячейка C5 — формулу C4+0,5 и т.д. Мы могли бы ввести их вручную, но есть и более простой способ — скопировать формулу в нижние ячейки с помощью функции автозаполнения.

Чтобы применить функцию автозаполнения, сделайте следующее.

• Выделите ячейку с той формулой, которую нужно скопировать, — в данном случае C3. Для этого либо перейдите к этой ячейке с помощью клавиш со стрелками, либо щелкните на ней кнопкой мыши.

• Выделите эту ячейку и все расположенные ниже ячейки, в которые нужно скопировать формулу, вплоть до ячейки C9. Для этого либо выполните щелчок и перетащите курсор мыши, либо, удерживая клавишу Shift, выделите все нужные ячейки с помощью клавиш со стрелками.

• Выберите в меню команду Fill Down (Заполнить вниз) или воспользуйтесь комбинацией клавиш Ctrl+D (рис. 28.2).

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

Сходным образом можно продублировать формулу вправо. Для этого нужно выделить горизонтальную группу ячеек и выбрать в меню команду Fill Right (Заполнить вправо) или воспользоваться сочетанием клавиш Ctrl+R. Если вам нужно продублировать содержимое определенной ячейки и вправо, и вниз, чтобы заполнить прямоугольную область, просто выделите эту область, а затем по отдельности примените команды Fill Right (Заполнить вправо) и Fill Down (Заполнить вниз).

Рис. 28.2

Возможно, вас интересует, можно ли выполнить автозаполнение вверх и влево. Хотя в меню есть команды Fill Up (Заполнить вверх) и Fill Left (Заполнить влево), соответствующих сочетаний клавиш не существует. Однако есть еще один способ автозаполнения с помощью мыши, который позволяет заполнять ячейки в любом направлении. Как можно заметить на рис. 28.2, в правом нижнем углу выделенной области отображается маленький синий квадратик. Если после выделения ячейки с дублируемой формулой вы щелкнете на нем и перетащите его в нужном направлении, то будут заполнены все выделенные ячейки. (Так можно сделать и после выделения нескольких ячеек, но это приведет к несколько иному результату.)

Теперь мы можем рассчитать цену продажи и размер прибыли: чтобы определить прибыль, нужно умножить цену покупки на окупаемость, а чтобы определить цену продажи — сложить прибыль и цену покупки. Давайте выполним эти вычисления. Введите в ячейках D1 и E1 заголовки Sell Price (Цена продажи) и Profit (Прибыль) соответственно. В ячейке E2 запишите формулу =B2*C2 (чтобы определить прибыль, мы умножаем цену покупки, указанную в ячейке B2, на окупаемость, указанную в ячейке C2). В ячейке D2 запишите формулу =B2+E2 (цена продажи — это цена покупки плюс прибыль). Затем можно автоматически заполнить остальные строки в этих столбцах, чтобы рассчитать цену продажи и прибыль других товаров (рис. 28.3). (Примечание: такого же результата можно добиться, копируя и вставляя содержимое ячеек через буфер обмена. Например, если вы выделите ячейку D2 и примените команду Copy (Копировать), а затем выделите ячейки D3 — D9 и примените команду Paste (Вставить), то записанная в ячейке D2 формула будет вставлена во все остальные ячейки выделенного диапазона с соответствующей корректировкой ссылок: E3 = B3 * C3, E4 = B4 * C4 и т.д.)

Рис. 28.3

Графики

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

• Выберите весь набор данных, включая заголовки. В данном случае это означает весь блок ячеек от A1 до E9.

• Помимо прочего, в меню Insert (Вставка) можно выбрать команду Chart (Диаграмма). Выбрав в настройках линейный график, вы увидите примерно следу­ющую картину (рис. 28.4) (Чтобы график не заслонял ячейки с данными, можно щелкнуть на нем кнопкой мыши и перетащить в другое место.)

Рис. 28.4

Заметьте, что график окупаемости выглядит здесь как горизонтальная линия, потому что его значения очень малы по сравнению со значениями остальных графиков.

Возможно, вас интересует, как выглядели бы эти графики, если бы нам не нужно было представлять их в одном и том же масштабе? Чтобы узнать это, можно построить их для каждого столбца в отдельности (для этого нужно вызывать команду для построения графика, выделяя только один столбец) (рис. 28.5). Обратите внимание на то, что при этом горизонтальная ось не снабжается подписями, поскольку при построении графика не выделяется столбец А с названиями товаров.

Рис. 28.5

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

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

• Перейдите на вкладку Customize (Дополнительные) при создании графика (а если он уже существует, щелкните на нем, выберите в меню команду Edit chart (Изменить диаграмму), как показано на рис. 28.6, и выберите вкладку Customize (Дополнительные) в правой верхней части страницы.

Рис. 28.6

• Разверните раздел Series (Серии) и прокрутите его вниз, где увидите флажок Trendline (Линия тренда) (рис. 28.7).

Рис. 28.7

• Вам предлагается несколько вариантов линии тренда: Linear (Линейный), Polynomial (Полиномиальный), Exponential (Экспоненциальный), Logarithmic (Логарифмический), Power Series (Степенной ряд) и Moving Average (Скользящее среднее) (в некоторых редакторах электронных таблиц могут быть и другие варианты). При выборе линейного варианта программа построит прямую линию так, чтобы расстояние между ней и всеми точками кривой было минимальным. При выборе экспоненциального варианта программа построит кривую функции y = ax, где a — некоторая константа, опять же сделав это так, чтобы расстояние между этой кривой и всеми точками исходной кривой было минимальным. В случае полиномиального варианта таким же образом будет построена кривая функции вида y = a + bx + cx2 + dx3 +… с указанным порядком (степенью) полинома (многочлена). В полиноме 2-го порядка старшим членом является слагаемое, включающее в себя x2, в полиноме 3-го порядка — слагаемое, включающее в себя x3, в полиноме 4-го порядка — слагаемое, включающее в себя x4, и т.д.

При добавлении линии тренда следует обратить внимание еще на два параметра. Первый — Label (Ярлык), которому по умолчанию присваивается значение None (Не указано), в случае чего в название графика не добавляется описание линии тренда. При выборе варианта Custom (Другой) в название графика добавляется текстовое описание линии тренда, а при выборе варианта Use Equation (Уравнение) в качестве такого описания указывается формула уравнения, применяемого при построении линии тренда. Последний вариант удобен в случае, если вы хотите в дальнейшем использовать линию тренда в качестве математической модели для экстраполяции исходной кривой.

Второй параметр — невзрачный флажок Show R2 (Показать R2). Установив его, вы можете отобразить для кривой так называемый R-квадрат, который является удобной мерой того, насколько хорошо линия тренда соответствует кривой. R2 может принимать значения от 0 (наихудшее соответствие) до 1 (точное соответствие с прохождением через все точки данных). Не существует четкого определения того, что следует считать хорошим или плохим значением R2, но если, например, R2 = 0,988, такую линию тренда можно считать хорошим приближением, а если R2 = 0,42, то лучше опробовать иной вариант линии тренда и посмотреть, не обеспечит ли другая разновидность уравнения более высокую степень соответствия.

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

Побочный квест 28.1. Построение распространенных разновидностей кривых

Чтобы попрактиковаться в построении графиков, создайте пустую электронную таблицу. В столбец A занесите числа от 1 до 100. (Подсказка: сначала введите число 1 в ячейку A1, после чего введите формулу =A1+1 в ячейку A2 и продублируйте ее вплоть до ячейки A100 с помощью команды Fill Down (Заполнить вниз), чтобы не набирать все вручную.) После этого в столбце B используйте формулу для вычисления треугольных чисел (n2 – n) / 2, чтобы найти первую сотню треугольных чисел. Представьте значения второго столбца в виде графика, чтобы посмотреть, как выглядит треугольная кривая в рамках довольно большого диапазона значений.

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