Никто не может без Excel
Построение диаграммы и графика
Данная статья является продолжением одноименной публикации, напечатанной в предыдущем номере, и рассматривает наиболее простые и типичные случаи использования Excel для обработки, оформления и визуализации табличных данных.
Excel является настолько универсальным средством, что трудно найти область исследований, в которой не требовалась бы эта программа. Ее применение далеко не ограничивается финансовыми и бухгалтерскими расчетами. Для демонстрации базовых возможностей Excel мы выбрали достаточно интересный пример использования программы для обработки данных по исторической географии. В статье мы рассматриваем пошаговое описание команд Excel для расчета и построения графика, иллюстрирующего изменения площади Государства Российского во времени. Для этого будут использованы многие типичные возможности программы по расчету элементов таблицы, оформлению данных и построению графиков и диаграмм.
Расчеты элементов таблицы
На рис. 1 представлена исходная таблица (порядок занесения в таблицу исходных данных был рассмотрен в предыдущей статье). В столбце А расположены имена правителей, в столбце В — год окончания правления каждого правителя, в столбце С — площадь в условных единицах, присоединенная при соответствующем правителе*.
На основании таблицы (рис. 1) необходимо построить столбец значений площадей государства на конец правления каждого царя. Для вычисления площади государства при каждом правителе (данные значения будем помещать в столбец D) к площади, которую наследовал правитель, необходимо прибавить площадь, присоединенную им за период правления. В ячейке С1 показана площадь государства до Ивана Грозного, соответственно в ячейке D1 нужно поместить то же значение, что и в С1 (здесь можно воспользоваться как функцией копирования, о которой упоминалось в первой части статьи, так и формулой с использованием знака «=»). Для использования формулы нужно щелкнуть на ячейке D1, в которой должен получиться результат вычислений (рис. 2), ввести знак равенства « = », щелкнуть на ячейке С1, из которой берется значение, и нажать <Enter>.
Таким образом, в ячейке D1 получим значение 96,75 (рис. 2).
Остальные ячейки столбца D получим, используя формулу суммирования.
Вычисление ячейки D2 осуществляется следующим образом: щелкните на ячейке, в которой должен получиться результат вычислений (ячейка D2), введите формулу «= D1+C2» (рис. 2).
Теперь необходимо скопировать данную формулу на весь столбец D (копирование формул производится так же, как и копирование любого содержания ячеек, при этом Excel автоматически изменяет в формулах ссылки на ячейки для каждого столбца или строки). Таким образом, после копирования в ячейке D3 появится формула « =D2+C3», а в ячейке D4 — «=D3+C4» и т.д.
Далее условную площадь, вычисленную в столбце D и выраженную в условных единицах, необходимо перевести в реальную площадь, выраженную в км2. Для этого все значения столбца D необходимо умножить на коэффициент перевода площади. В ячейку Е1 поместим значение коэффициента перевода. Значения площади в км2 будем вычислять в столбце F. В ячейку F1 помещаем формулу «=D1*E1». Если мы скопируем эту формулу на остальные ячейки столбца F, то, например, в ячейке F5 окажется формула «=D5*Е5» вместо нужной нам формулы «=D5*Е1».
Это происходит в силу того, что Excel формирует относительные ссылки на ячейки. В тех же случаях, когда в формуле необходимо сослаться на ячейку со значением, которое не должно изменяться при копировании формулы в другие ячейки, пользуются так называемыми абсолютными ссылками.
В этом случае используют символ «$». Наличие этого символа перед буквой столбца или перед номером строки свидетельствует о неизменности данного параметра. Знак «$» может стоять и перед номером столбца, и перед номером строки. В частности, для того чтобы при копировании формулы не изменялась ссылка на ячейку Е1, можно использовать запись $Е$1. При копировании формулы «=D1*$Е$1» ссылка на ячейку Е1 в скопированной формуле останется неизменной.
Того же эффекта можно добиться, если скопировать значение ячейки E1 на весь столбец E и копировать формулу «=D1*Е1».
Используя последний вариант, площадь государства и площадь, добавленную каждым из правителей, выраженную в километрах, вычисляем в ячейках F1 и G1 по формулам «=E1*D1» и «=E1*C1» и затем копируем эти формулы до конца столбцов F и G (рис. 3).
 |
|
Оформление таблицы
Чтобы придать таблице законченный вид, необходимо вставить названия столбцов. Если для этой цели не зарезервирована пустая верхняя строка, ее можно вставить следующим способом: выделить верхнюю строку, нажав на кнопку с номером строки «1», вызвать меню, нажав на выделенную строку правой клавишей мыши, выбрать команду Добавить ячейки (рис. 4).
В добавленную строку запишем названия столбцов.
Слишком длинные заголовки столбцов (как в нашем случае) удобнее вписать в режиме Переносить по словам, для установления которого необходимо выделить соответствующую строку, вызвать меню нажатием правой кнопки мыши, выбрать пункт Формат ячеек, перейти на закладку Выравнивание и установить флажок Переносить по словам (рис. 5).
В этой же вкладке можно выровнять надпись в ячейке по горизонтали и по вертикали.
После внесения текста заглавий столбцов таблица примет вид, как на рис. 6.
Как видно из рис. 6, в столбце Е слово «Коэффициент» не уместилось. Для того чтобы увеличить ширину столбца, подведите указатель мыши к разделительной линии между ячейками с обозначением столбцов (в данном случае «D» и «E»), в результате чего он приобретет вид двунаправленной стрелки. После этого перемещайте правую границу столбца D до необходимых размеров.
Для изменения размеров ячеек существует несколько способов. Например, если вам необходимо задать точное значение ширины столбца, можно воспользоваться командой Формат а Столбец а Ширина и указать численное значение ширины столбца. Для выбора оптимальной ширины столбца воспользуйтесь командой Формат а Столбец а Автоподбор ширины, которая особенно актуальна, когда необходимо сэкономить пространство.
Для придания таблице более наглядного вида удобно воспользоваться командой Автоформат.
В результате таблица примет более красочный вид (рис. 8).
Теперь продемонстрируем, как можно выделить часть информации таблицы. Например, царей периода Российской Империи. Это можно сделать, используя обычный инструмент «заливка» — выделить в таблице нужный фрагмент и залить его выбранным цветом (рис. 9).
Иногда удобно использовать средство условного форматирования.
К примеру, мы можем выделить в столбце «Даты окончания правления» все числа больше 1721 (год основания Российской Империи). Для этого необходимо выполнить команду Формат а Условное форматирование, в результате чего появится панель (рис. 10).
В данной панели выставим условие Больше или равно и число 1721.
Далее необходимо нажать кнопку Формат и выставить формат выделенных ячеек, например можно выбрать закладку Шрифт и задать определенный цвет шрифта.
Ненужные столбцы (строки) можно скрыть. Так, если нам не нужна колонка с коэффициентом перевода площади, ее можно «спрятать»: выделить столбец, вызвать меню, щелкнув по выделенному столбцу правой кнопкой мыши, и выбрать в меню пункт Скрыть. Возвратить этот столбец можно, выделив два соседних столбца, вызвав меню и выбрав пункт Отобразить. Удалить вышеуказанный столбец совсем мы не можем, поскольку его значения используются в формулах. Этот столбец можно удалить только в случае замены формул на числовые значения. Для этого необходимо выделить ячейки, где мы хотим произвести замену, и выполнить следующую команду: Правкаа КопироватьаПравка аСпециальная вставка. В результате выполнения данной команды появится панель (рис. 11).
В панели Специальная вставка (см. рис. 11) необходимо поставить флажок Значения и нажать кнопку Ok. Теперь, когда в таблице содержатся просто константы, можно удалить ненужные столбцы, выделив их и выбрав в меню пункт Удалить.
Итак, мы получили окончательный вариант таблицы (рис. 12).
|
|
Построение диаграммы и графика
Для более наглядного представления материала нашей таблицы (рис. 12) построим диаграмму площадей, присоединенных каждым из правителей, и отдельно график изменения площади Государства Российского во времени.
|
|
Построение диаграммы
Чтобы построить диаграмму, отметим необходимые данные, для этого выделим ячейки в интервале с A3 по A25 (аргумент), затем, удерживая кнопку Ctrl, выделим также ячейки с D3 по D25 (значения функции). Теперь нажмем на кнопку Мастер диаграмм (пиктограмма с изображением гистограммы), выберем тип диаграммы, например Гистограмма, выберем вид гистограммы и далее, отвечая на вопросы мастера диаграмм, создадим рисунок 13).
Следует заметить, что в качестве столбца ячеек, которые выполняют роль аргумента изображаемой функции, могут быть выбраны не только числовые, но и текстовые данные.
|
|
Построение графика
Для построения графика выделим ячейки с В2 по В25 и C2 по C25, затем нажмем на кнопку Мастер диаграмм на панели инструментов, выберем точечную диаграмму и, отвечая на вопросы мастера, построим график. Для оформления графика можно использовать меню, вызываемое нажатием правой кнопки мыши на поле графика. В зависимости от области на поле графика, на которой мы щелкнем мышью, можно форматировать различные части графика и сетки. В результате получаем оформленный график (рис. 14). Более подробно возможности оформления графиков и диаграмм мы рассмотрим в следующей статье.
КомпьютерПресс 12'2000
