36. Продвинутая работа с графиками

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

При копировании и вставке блока ячеек могут оказаться полезными два особых режима вставки, для доступа к которым нужно щелкнуть правой кнопкой мыши и выбрать в контекстном меню пункт Paste Special (Специальная вставка). При выборе режима вставки Paste Values Only (Только значения) будут копироваться только значения ячеек, но не содержащиеся в них формулы. Например, попробуйте сделать следующее: в ячейке A1 введите число 1, после чего в ячейке A2 введите формулу =A1+1 и продублируйте эту ячейку вниз несколько десятков раз с помощью функции автозаполнения. Теперь скопируйте все эти ячейки, щелкните правой кнопкой мыши на ячейке B1 и выберите команду Paste Special/Paste Values Only (Специальная вставка/Только значения). После этого столбец B будет выглядеть так же, как и столбец A, но если вы сравните содержимое строки формул, то увидите, что в ячейке A2 она отображает все ту же формулу =A1+1, а в ячейке B2 — просто значение 2. Если теперь вы опять скопируете и вставите, скажем, ячейку B10 в ячейку D10, то снова получите значение 10, а не формулу =D9+1.

Этот режим вставки можно применять, в частности, для фиксации значений постоянно пересчитываемых ячеек, например тех, где используются функции RAND() и RANDBETWEEN(). Обычно такие ячейки генерируют новый случайный результат при каждом изменении таблицы, но если вам нужно сохранить определенную комбинацию значений (например, если таблица предназначена для генерирования параметров персонажа настольной ролевой игры и требуется сгенерировать значения один раз и сохранить их для последующего применения), просто скопируйте эти ячейки и вставьте их с помощью команды Paste Special/Paste Values Only (Специальная вставка/Только значения).

Иногда может пригодиться и режим вставки Paste Transposed (Вставка с транспонированием). Допустим, вы работаете над диаграммой и после введения в таблицу большого количества данных неожиданно выясняете: то, что вы ввели в столбцы, нужно было вводить в строки и наоборот. Такая ошибка расположения данных совсем не редкость при создании диаграмм и графиков. К счастью, это легко исправить. Чтобы поменять местами строки и столбцы, сохранив неизменными все данные и формулы, скопируйте прямоугольный блок ячеек и вставьте его, используя команду Paste Special/Paste Transpose (Специальная вставка/Вставка с транспонированием). Так, если вы скопируете блок ячеек размером 3 × 4 и затем выполните вставку с транспонированием, то получите в результате блок ячеек размером 4 × 3 (рис. 36.1).

Рис. 36.1

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

Рис. 36.2

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

Параметры графиков

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

Щелкнув правой кнопкой мыши в любом месте области построения диаграммы, вы можете получить доступ к большому количеству настроек (рис. 36.3).

Рис. 36.3

Прежде всего с помощью этого контекстного меню можно добавить отображаемое вверху название графика или диаграммы. Будет полезно взять это за привычку. Название позволяет понять, что представляет собой тот или иной график или диаграмма, когда электронная таблица содержит сразу несколько графиков или когда график рассматривается вне контекста соответствующей электронной таблицы. Сходным образом подменю Axis (Ось) позволяет снабдить метками горизонтальную или вертикальную ось графика, что (опять же для большей ясности) полезно делать всегда. Используя подменю Axis (Ось), можно также указать, какой диапазон значений должен быть представлен на горизонтальной или вертикальной оси, поскольку редактор не всегда выбирает по умолчанию нужный вам диапазон (например, когда значения варьируются в диапазоне от 0 до 1, редактор может решить, что на оси должен быть представлен диапазон от 0 до 1,2).

Подменю Series (Серии) позволяет настроить способ представления ряда данных (то есть отдельного набора отображаемых на графике данных — на каждом можно представить несколько отдельных рядов данных, используя для каждого из них разные цвета и способ представления). Например, для точечной диаграммы рассеяния или линейного графика можно настроить цвет, размер и форму точек отображаемого ряда данных. Если для построения графика применен не тот ряд данных, который предполагалось, или если вы хотите добавить на него еще один ряд данных, щелкните на нем правой кнопкой мыши и выберите в контекстном меню команду Series (Серии). В правой части экрана откроется боковая панель Chart Editor (Редактор диаграмм) с параметрами ряда данных, выделенного в данный момент. Чтобы настроить параметры используемого ряда данных, перейдите на вкладку Customize (Дополнительные), а затем — в раздел Series (Серии). Чтобы добавить новый ряд данных, перейдите на вкладку Setup (Настройки) и щелкните в поле для добавления нового ряда данных (рис. 36.4).

Рис. 36.4

После этого укажите, какой диапазон ячеек следует использовать в качестве нового ряда данных.

Виды графиков и диаграмм

Редакторы электронных таблиц предлагают целый ряд достойных изучения способов графического представления данных. Один из них — это уже знакомая нам диаграмма рассеяния, которая отображает пары значений x и y в виде точек на координатной сетке. Еще один полезный способ графического представления — линейный график. Если диаграмма рассеяния просто отображает точки, не учитывая порядок их расположения, то линейный график — серию последовательно расположенных точек, позволяя проследить, как меняются значения одного показателя по мере изменения другого (например, как меняется значение параметра DAU с течением времени или средняя продолжительность игрового сеанса при изменении уровня персонажа). Чтобы создать простой линейный график, выделите данные одной строки или столбца. Затем в меню Insert (Вставка) выберите команду Chart (Диаграмма) и укажите линейный график в качестве типа создаваемого графика (рис. 36.5).

Рис. 36.5

Хотя в некоторых случаях достаточно и этого, обычно также нужно снабдить ось X соответствующими метками, например датами, если вы строите график зависимости параметра DAU от времени. В таком случае нужно предварительно выделить две строки или два столбца, где верхняя строка или левый столбец будет представлять значения оси X. Построив график, установите флажок Use row 1 (2, 3…) as headers (Заголовки — значения строки 1 (2, 3…)), если исходные данные расположены в строках, или флажок Use column A (B, C…) as labels (Ярлыки — значения столбца A (B, C…)), если они находятся в столбцах.

Если вы попробуете представить таким образом неотсортированные данные, то график будет выглядеть так, как показано на рис. 36.6, представляя собой просто результат последовательного соединения исходных точек. Простейший способ исправления такой ситуации состоит в том, чтобы отсортировать все данные по строке или столбцу со значениями горизонтальной или вертикальной оси. Также можно установить флажок Aggregate (Объединить), в таком случае значения на оси X будут расположены в той же последовательности, в какой они находятся в соответствующих строке или столбце.

Рис. 36.6

Чтобы представить на одном графике несколько наборов данных, нужно выполнить те же действия, предварительно разместив наборы данных в отдельных строках или столбцах и выделив все эти данные (рис. 36.7).

Рис. 36.7

Иногда может пригодиться и такой вид диаграмм, как гистограмма (или столбчатая диаграмма, согласно терминологии, принятой в редакторе Google Sheets). Для ее создания нужно сделать то же самое, что и в случае линейного графика, указав в качестве типа создаваемого графика столбчатую диаграмму. Столбчатая диаграмма, по сути, визуализирует то же самое, что и линейный график, — один или несколько рядов точек, откладываемых вдоль оси Y напротив определенного места на оси X. Но если линейный график служит главным образом для отслеживания того, как изменяется переменная y при изменении x (например, для отслеживания изменений, происходящих с течением времени или при изменении уровня игрока), то столбчатая диаграмма акцентирует внимание на относительной высоте каждого столбца и поэтому используется в основном для наглядного поточечного сравнения нескольких наборов данных.

Иногда при визуализации данных требуется отобразить не только сами данные, но и значение стандартной ошибки, чтобы можно было оценить степень надежности представленных данных. Если у двух точек данных накладываются планки погрешностей, отражающие значение стандартной ошибки, это обычно свидетельствует о том, что между ними нет статистически значимой разницы. Небольшая стандартная ошибка чаще всего говорит о высокой надежности данных, а большая, наоборот, — о том, что представленные данные могут сильно отличаться от реальных, поэтому на них не стоит слишком сильно полагаться. Чтобы отобразить планки погрешностей, перейдите после выбора типа диаграммы на вкладку Customize (Дополнительные), а затем — в раздел Series (Серии) (рис. 36.8), после чего установите флажок Error bars (Планки погрешностей) (это можно сделать и для линейного графика, и для столбчатой диаграммы). После этого можно задать размер планок погрешностей: он может составлять заданное количество процентов от конкретного значения, некоторое постоянное значение или заданное количество стандартных отклонений.

Наконец, вероятно, вы заметили, что при вставке график, как правило, занимает много места и заслоняет собой часть электронной таблицы. Что, если вместо этого вам нужно отобразить много графиков, например по одному линейному графику для каждой из 50 строк с данными? Такая необходимость часто возникает, когда нужно выявить общие тенденции в рамках множества слабо детализированных рядов данных, что требует наглядного сравнения большого количества графиков. Например, если содержимое каждой строки показывает, как изменяется численность игрового населения одной из зон MMO-игры с течением времени, а у вас несколько десятков различных зон, возможно, будет проще разместить по одному графику зависимости игрового населения от времени в отдельной ячейке в конце каждой строки. Это позволит просмотреть графики всех зон в одном столбце на одном экране и таким образом составить общее представление о том, в каких зонах больше всего игроков. А если нужно сбалансировать список игровых объектов относительно кривой стоимости, таким образом можно получить общее представление об относительной мощности каждого объекта.

Рис. 36.8

Когда нужно отобразить график в ячейке таблицы, то вместо использования команды из меню Insert (Вставка) следует записать в целевой ячейке формулу с функцией SPARKLINE(). Применять ее не так уж просто, поскольку она предлагает очень широкие возможности настройки. Вы можете настроить множество таких параметров, как диапазон значений, цвет, обозначения осей, тип графика и т.д. Функция SPARKLINE() принимает два аргумента: диапазон данных и заключенный в фигурные скобки набор параметров, где для каждого параметра сначала указывается имя, а потом через запятую — значение. Используя несколько параметров, их отделяют друг от друга точкой с запятой. Список доступных параметров обширен, поэтому, если вам нужно что-то конкретное, проще будет свериться с имеющейся в Интернете документацией по данной функции. Однако надо сказать, что практически всегда нужен параметр charttype, задающий тип создаваемого графика. Этому параметру, в частности, можно присвоить значение line (линейный график) или column (столбчатая диаграмма). Например, вот как можно построить линейные графики для множества рядов случайных данных (рис. 36.9).

Рис. 36.9

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

Побочный квест 36.1. Крошечные графики

Заполните данными четыре столбца. Первый должен содержать последовательный ряд чисел, кратных 5 (линейная зависимость). Во втором запишите первые десять треугольных чисел от 1 до 55 (полиномиальная зависимость). В третьем запишите результаты возведения в степень числа 1,5, где степень возрастает от 1 до 10 (экспоненциальная зависимость). В четвертом столбце используйте десять раз формулу =RANDBETWEEN(1,50) (равномерно распределенная псевдослучайная последовательность). Представьте все четыре набора данных на одном линейном графике. Затем с помощью функции SPARKLINE() создайте линейный график для каждого столбца в отдельности.

Обратите внимание на то, что при каждом пересчете ячеек будут меняться и содержимое столбца со случайными данными, и соответствующий график. Выделите десять ячеек с функцией RANDBETWEEN(), скопируйте их и вставьте на том же месте, используя команду Paste Special/Paste Values Only (Специальная вставка/Только значения), чтобы заменить формулы фиксированными значениями. Обратите внимание: после этого при нажатии клавиш Ctrl+R для пересчета ячеек содержимое столбца со случайными данными уже не будет меняться.