Гистограммы Excel 2007
 | В практике бухгалтера, аудитора наиболее распространенным способом представления диаграмм являются гистограммы. По своей природе они похожи на графики, но имеют ряд преимуществ и специфических настроек. В чем заключаются эти особенности, мы рассмотрим в нашей статье. Причем сделаем это с применением новых инструментов Excel 2007 — разделов «Конструктор» и «Формат» меню «Работа с диаграммами». А заодно разберемся, что такое шаблоны, стили диаграмм и как их использовать бухгалтеру в своей работе. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Гистограммы — одна из самых распространенных форм представления диаграмм. Причин для этого много. Во-первых, гистограммы наглядны. Они хорошо смотрятся не только в распечатанном отчете, но и при создании электронных презентаций. Что бы ни говорили, а тонкие линии графиков на большом и недостаточно контрастном экране выглядят хуже, чем гистограмма. На гистограмме лучше видны надписи. А иногда это очень важно. Наконец, гистограмма может показать не только динамику изменения показателей, но и их процентное соотношение в каждой точке построения. Одним словом, преимуществ у гистограмм немало. Поэтому не случайно, что в программе Excel 2007 разработчики предусмотрели 19 разновидностей такого представления данных! Конечно, рассмотреть каждую из гистограмм в отдельности, да еще и в рамках одной статьи не получится, но нам этого и не нужно. Достаточно построить один тип гистограммы, настроить ее параметры, а остальные варианты вы легко освоите самостоятельно.
Строим гистограмму
Опять-таки начнем с примера. Задача будет такой. На рис. 1 показана таблица с объемами продаж четырех видов продукции за 1 — 4 кварталы 2009 года. В ячейке «
A1» записан заголовок «Наименование». Обратите внимание, что блок «A1:A2» отформатирован с объединением ячеек. В ячейке «B1» находится текст «Объемы продаж, шт.». Причем блок «B1:F1» отформатирован с выравниванием по центру выделения. Ниже в блоке «B2:E2» записаны наименования кварталов. В ячейках «A3:A6» записаны наименования продукции. В блоке «B3:E6» находятся объемы реализации соответствующего товара за определенный квартал (в штуках). В колонке «F» посчитан общий объем реализации. Наша задача — на одной диаграмме показать динамику реализации по каждому товару. Делаем так:1) вызываем Excel, загружаем документ с таблицей, как на рис. 1;
2) выделяем блок с данными. На рис. 1 это ячейки «
A1:E6». (Итоги в блок данных не включаем!);3) щелкаем на меню «
Вставка». Лента меню примет вид, как на рис. 2; 
4) в группе «Диаграммы» щелкаем на иконке «Гистограмма». Откроется дополнительное меню из 19 пиктограмм, как на рис. 3;
5) щелкаем на пиктограмме «Гистограмма с группировкой». На рабочем листе появится изображение диаграммы (рис. 4).
Что мы получили в результате? Excel правильно расставил заголовки осей, определил данные для легенды и объединил все данные о продажах в виде набора столбиковых гистограмм. При этом он пропустил объединенную ячейку «A1:A2» и корректно разобрался с заголовками в блоке «B2:E2». А вот заголовок «Объемы продаж, шт.» он поставил неверно. Это произошло потому, что в наших данных есть объединенная ячейка «A1:A2».
Важно!
Не включайте в данные для диаграммы объединенные ячейки. Excel может запутаться и неправильно расставить заголовки и названия осей. Если объединения не избежать, стройте диаграмму без заголовков, а потом доработайте ее через меню «Работа с диаграммами».То есть в нашем случае для построения диаграммы лучше сделать так:
1) открываем документ, выделяем блок «
A3:E6»;2) щелкаем на меню «
Вставка»;3) в группе «
Диаграммы» щелкаем на иконке «Гистограмма»;4) из предложенного меню (рис. 3) выбираем «
Гистограмма с группировкой». Появится диаграмма, как на рис. 5. 
Результат получился намного лучше. На диаграмме правильно показаны элементы легенды. Значения подписей по горизонтальной оси присвоены по умолчанию. Но мы это сейчас исправим. Главное, что на графике нет ничего лишнего. Итак, план нашей дальнейшей работы.
Вначале диаграмму мы дополним названиями осей. Затем на график вставим общее название гистограммы. Элементы диаграммы сделаем шире, сократим зазор между ними. Так диаграмма будет смотреться лучше. Саму диаграмму оформим в черно-белом исполнении — мы собираемся ее печатать на монохромном принтере. Причем сделаем это через макеты и стили диаграмм.
Применяем макет
Вернемся к диаграмме на рис. 5. Сейчас мы добавим к ней названия осей и общий заголовок. Сделать это можно по-разному. Например, вызвать меню «
Работа с диаграммами» и воспользоваться иконками «Названия диаграммы» и «Названия осей». Но есть и более короткий путь — применить к диаграмме так называемый макет.По своей сути макет — это описание способа оформления диаграммы. Он определяет состав диаграммы и расположение отдельных ее элементов (подписей, легенды, заголовков и т. п.). В Excel 2007 есть 11 стандартных макетов. Выбрав любой из них, мы мгновенно перестроим диаграмму в соответствии с параметрами этого макета. Посмотрим, как это выглядит практически. Делаем так:
1) щелчком мыши выделяем диаграмму. Станет доступно меню «
Работа с диаграммами»;2) переходим к разделу «
Конструктор» этого меню. Лента примет вид, как на рис. 6; 
3) находим раздел иконок «Макеты диаграммы». Щелкаем на кнопке «Дополнительные параметры», как показано на рис. 6. Откроется список из 11 элементов, изображенный на рис. 7;
4) в этом списке находим подходящий способ оформления диаграммы и щелкаем на нем левой кнопкой мыши. Я выбрал вариант «Макет 9» (рис. 7). На диаграмме появятся фиктивные названия осей (в них будет записан текст «Название оси») и заголовок гистограммы. Сейчас в заголовке написано «Название диаграммы»;
5) щелкаем правой кнопкой на заголовке вертикальной оси. Появится контекстное меню (рис. 8);
6) из этого меню выбираем «Изменить текст»;
7) в качестве заголовка оси вводим «Объем реализации, шт.»;
8) через правую кнопку мыши изменяем заголовок горизонтальной оси. Сюда пишем текст «Кварталы»;
9) дважды (последовательно) щелкаем левой кнопкой на заголовке диаграммы. Это еще один способ войти в режим редактирования текста;
10) в рамке с заголовком набираем «Объемы реализации за 14 кв. 2010 г.»;
11) щелкаем правой кнопкой на горизонтальной оси. Появится контекстное меню, как на рис. 9;
12) выбираем пункт «Выбрать данные…». Появится окно, как на рис. 10. Мы будем менять подписи по горизонтальной оси;
13) щелкаем на кнопке «Изменить» в правой части окна «Выбор источника данных» (рис. 10). Откроется вспомогательное окошко «Подписи оси», и станет доступен для выделения рабочий лист;
14) на листе обводим блок «B2:E2» (здесь расположены названия кварталов);
15) в окне «Подписи оси» щелкаем «ОК»;
16) в окне «Выбор источника данных» щелкаем «ОК». Ниже горизонтальной оси появятся надписи в виде «1 кв.», «2 кв.», «3 кв.», «4 кв.» (рис. 11);
17) вызываем меню «Главная», меняем размеры шрифтов для заголовка диаграммы и подписей осей. Результат нашей работы показан на рис. 11.
Вставить надписи и заголовки на диаграмму можно было через меню «Работа с диаграммами» (раздел «Макет», иконки «Названия осей», «Название диаграммы»). Так мы и поступали при построении графика. Сейчас те же действия мы сделали макетами диаграмм. На мой взгляд, результат был получен намного быстрее.
Совет Если вы часто пользуетесь макетами для диаграмм, имеет смысл вынести работу с ними на панель быстрого доступа.
Для этого сделайте так:
1) в меню «
Работа с диаграммами» откройте раздел «Конструктор»;2) щелкните правой кнопкой на пиктограмме любого макета в группе «
Макеты диаграмм». Появится контекстное меню из одного пункта: «Добавить коллекцию на панель быстрого запуска»;3) щелчком мыши выберите этот пункт. На панели быстрого запуска (справа от кнопки «
Офис») появится значок для доступа к макетам диаграмм Excel 2007.
Работаем со стилем диаграммы
Нам осталось перевести диаграмму в черно-белое оформление и отформатировать ее ряды. Сделать это можно по-разному. Один из способов — настроить параметры каждого элемента диаграммы. Например, чтобы придать ряду диаграммы черно-белое форматирование, нужно щелкнуть на нем правой кнопкой мыши. Из контекстного меню выбрать «
Формат ряда данных…» и в одноименном окне на закладке «Заливка» установить соответствующие параметры. Но в этом способе есть большой недостаток: все действия придется повторить для каждого ряда в отдельности, а затем еще и переформатировать остальные элементы диаграммы! Ускорить эту работу позволяют стили диаграмм Excel 2007. Делаем так:1) щелчком мыши выделяем диаграмму;
2) вызываем меню «
Работа с диаграммами»;3) переходим в раздел «
Конструктор»;4) в группе «
Стили диаграмм» щелкаем на иконке «Стиль 1» (она расположена первой в списке слева). Диаграмму Excel оформил в оттенках серого.Совет
Используйте стандартные макеты и стили для быстрого форматировании диаграмм в Excel 2007. В большинстве случаев — это самый быстрый способ решения задачи.
Изменяем ряды гистограммы
Теперь разберемся с настройкой рядов. Наша цель — сделать столбики гистограммы шире, чтобы они занимали практически всю доступную площадь области построения. Делаем так:
1) выделяем диаграмму, щелкаем правой кнопкой на любом из рядов данных. Появится контекстное меню, как на рис. 12;
2) выбираем пункт «
Формат ряда данных…». Появится окно «Формат ряда данных», как на рис. 13. Большинство параметров этого окна («Заливка», «Цвет границы», «Стиль границ», «Формат объемной фигуры») нам сейчас не нужны; 
3) переходим на закладку «Параметры ряда». Для гистограммы он представлен тремя группами параметров: «Перекрытие рядов», «Боковой зазор» и «Построить ряд»;
4) передвигаем ползунок параметра «Боковой зазор» влево, пока значение в окошке не станет равным «50 %». Можно ввести это значение с клавиатуры;
5) параметр «Перекрытие рядов» не меняем. Если увеличить его значение, ряды данных будут как бы накладываться друг на друга;
6) в окне «Формат ряда данных» нажимаем кнопку «Закрыть». Окончательный вид диаграммы показан на рис. 14.
Важно!
Параметры «Боковой зазор» и «Перекрытие рядов» действует сразу на все имеющиеся ряды гистограммы. Остальные параметры окна «Формат ряда данных» нужно устанавливать для каждого ряда данных индивидуально.
Другие возможности Конструктора диаграмм
Первый опыт работы с Конструктором мы получили. Посмотрим, какие дополнительные возможности предлагает нам раздел меню «
Работа с диаграммами». Лента меню «Конструктор» состоит из пяти групп иконок. В группе «Тип» находятся две пиктограммы:— «
Изменить тип диаграммы»: позволяет откорректировать способ представления текущей диаграммы. Например, превратить гистограмму в график и т. п. В Excel 2007 можно изменить тип для всей диаграммы или для отдельного ряда данных;— «
Сохранить как шаблон»: с помощью этой пиктограммы можно записать параметры форматирования текущей диаграммы в библиотеку шаблонов Excel. Затем этот шаблон можно использовать при создании новых диаграмм и тем самым сократить время на их оформление.В группе «
Данные» расположены две иконки:— «
Строка/Столбец»: меняет способ интерпретации данных при построении текущей диаграммы;— «
Выбрать данные»: изменяет область данных, по которым построена диаграмма.С возможностями групп «
Макеты диаграмм» и «Стили диаграмм» мы уже знакомы. Через макеты можно быстро определить состав диаграммы (наличие подписей, заголовков, их расположение). Стили диаграмм позволяют быстро задать цветовое решение для текущей диаграммы.Последняя группа иконок на ленте Конструктора называется «
Расположение». Здесь всего одна пиктограмма — «Переместить диаграмму». Щелчок на ней открывает специальное окно с единственным параметром «Разместить диаграмму:». Он оформлен в виде переключателя и может принимать одно из двух положений — «На отдельном листе:» или «На имеющемся листе».Думаю, с большинством параметров режима «
Конструктор» все понятно. Хотелось бы остановиться всего на двух моментах — как изменить тип диаграммы и область данных для ее построения. Начнем с изменения типа диаграммы. За основу возьмем гистограмму на рис. 14. На ней преобразуем ряд для товара «Lenovo» в обычный график. Делаем так:1) открываем документ. Щелкаем левой кнопкой на любой точке ряда «
Lenovo», чтобы выделить его;2) вызываем меню «
Работа с диаграммами», затем раздел «Конструктор»;3) в группе иконок «
Тип» щелкаем на пиктограмме «Изменить тип диаграммы». Появится окно «Изменение типа диаграммы», как на рис. 15; 
4) в этом окне выбираем «График с маркерами»;
5) в окне «Изменение типа диаграммы» щелкаем на кнопке «ОК». Ряд для товара «Lenovo» на диаграмме приобрел форму графика;
6) щелкаем на графике правой кнопкой мыши;
7) из контекстного меню выбираем пункт «Добавить подписи данных…». Над графиком появятся значения объемов реализации для товара «Lenovo». Окончательный вид диаграммы после всех изменений показан на рис. 16. Таким образом, на одной диаграмме можно совместить несколько разных способов представления данных.
Важно!
Не все типы диаграмм совместимы друг с другом. Например, если основным представлением является гистограмма, на этой же диаграмме нельзя показать ряд в виде поверхности, биржевой диаграммы или трехмерного графика. При попытке сделать это Excel выдаст соответствующее предупреждение.При работе с диаграммами может понадобиться изменить область данных для их построения. Речь идет не о числовых значениях — в этом случае Excel перестроит график автоматически. Имеется в виду добавление к диаграмме новых рядов, изменение ссылок на данные существующих рядов и т. п. Посмотрим, как это сделать практически:
1) нажимаем комбинацию «Ctrl+Z» (или кнопку «Отменить») до тех пор, пока диаграмма не примет вид, как на рис. 14;
2) щелчком мыши выделяем диаграмму. Можно выделить область построения или ряд данных — в этом случае разницы нет никакой;
3) через меню «Работа с диаграммами» переходим в режим «Конструктор»;
4) в группе иконок «Данные» щелкаем на пиктограмме «Выбрать данные» (рис. 6). Откроется окно, как на рис. 17.
В верхней части окна расположен параметр «Диапазон данных для диаграммы:». Сейчас в нем записано значение «=’Продажи’!$A$2:$E$6». Это диапазон данных, по которому построена диаграмма. Чтобы изменить этот диапазон, нужно щелкнуть левой кнопкой в окошке параметра и обвести новый блок на рабочем листе. С этим все понятно.
Интереснее ситуация, когда данные для диаграммы расположены на разных участках рабочего листа или на отдельных листах рабочей книги. В этом случае ряды нужно формировать вручную. Это можно сделать в левой части окна «Выбор источника данных» (группа «Элементы легенды (ряды)»). Для нашей диаграммы здесь расположены всего четыре элемента «Lenovo», «HP», «Samsung», «Acer». Они соответствуют четырем рядам данных, которые представлены на гистограмме (рис. 14). Для управления рядами предусмотрены пять кнопок: «Добавить», «Изменить», «Удалить», а также две иконки со стрелками для изменения порядка следования рядов (переместить вверх и вниз). Чтобы посмотреть на работу всех этих инструментов, делаем так:
1) выделяем ряд данных. Например, для ноутбука «Lenovo»;
2) щелкаем на кнопке «Изменить». Откроется окно «Изменение ряда», как на рис. 18;
3) в окошке «Имя ряда:» стоит ссылка на ячейку с названием ряда данных. Чтобы изменить его, нужно исправить эту ссылку (щелкнуть мышью на соответствующей ячейке рабочего листа);
4) в окне параметра «Значения:» (рис. 18) стоит ссылка на ячейки «=Продажи!$B$3:$E$3». Это блок данных для выделенного ряда (объемы продаж по товару «Lenovo»). Чтобы перестроить ряд по другим данным, щелкаем на рабочем листе и обводим новый блок значений;
5) завершив корректировку параметров ряда, нажимаем кнопку «ОК».
Для добавления нового ряда к диаграмме предусмотрена кнопка «Добавить». Окно диалога при добавлении ряда полностью повторяет изображенное на рис. 18. Единственное отличие состоит в том, что параметры «Имя ряда:» и «Значения:» изначально пусты. Для добавления ряда нужно заполнить эти параметры и нажать кнопку «ОК». Чтобы удалить ряд данных, выделяем его и нажимаем кнопку «Удалить». На диаграмме исчезнет указанный ряд и его легенда.
Совет
Если данные для диаграммы находятся на разных листах или в несвязных диапазонах рабочей книги, формируйте ряды вручную. Для этого вставьте пустую диаграмму (или укажите для нее первый ряд данных). Затем вызовите Конструктор. Откройте окно «Изменение ряда» и последовательно добавьте остальные ряды диаграммы.Две крайние правые кнопки в группе параметров «Элементы легенды (ряды)» изменяют последовательность следования рядов на существующей диаграмме. Например, я хочу переместить ряд «Lenovo» так, чтобы он занял позицию между брендами «Samsung» и «HP». Делаем так:
1) открываем окно «Выбор источника данных». Как это делать, мы уже знаем;
2) в группе «Элементы легенды (ряды)» выделяем ряд «Lenovo»;
3) щелкаем левой кнопкой на пиктограмме со стрелкой вниз. В окне «Элементы легенды (ряды)» строка с текстом «Lenovo» переместится ниже на одну позицию, а на гистограмме тут же поменяется порядок следования рядов.
Совет
Старайтесь, чтобы при корректировке диаграммы сам график и окно «Диапазон данных для диаграммы» одновременно были видны на экране. В этом случае вы сможете визуально оценить каждое сделанное изменение.В центре окна «Выбор источника данных» (рис. 17) находится важная кнопка — «Строка/столбец». Она определяет, каким образом Excel должен интерпретировать информацию из области данных для диаграммы. Если указано, что данные расположены по строкам (как в нашем случае), то каждая строка образует отдельный ряд данных. Щелчок на кнопке «Строка/столбец» меняет это положение вещей. Теперь каждый столбец из области данных будет показан на диаграмме как отдельный ряд. Диаграмма в режиме «по столбцам» для нашего примера показана на рис. 19. В ней по горизонтальной оси перечислены товары. Объемы реализации каждого товара по кварталам собраны в одну группу, а названия кварталов перенесены в область легенды. Такая форма имеет свои преимущества — она лучше показывает динамику изменения объемов продаж в пределах каждой группы товаров.
Справа в окне «Выбор источника данных» (рис. 17) есть область «Подписи горизонтальной оси (категории)». С этим параметром мы уже работали. Он позволяет изменить подписи горизонтальной оси. В окне параметров доступна единственная кнопка «Изменить». Щелчок на ней открывает окно, где можно ввести ссылку на ячейки с подписями по горизонтальной оси (рис. 10).
Важно!
При работе с подписями осей и параметрами рядов (рис. 10, 18) необязательно указывать ссылки на ячейки рабочего листа. Все значения можно ввести с клавиатуры, разделяя их символом «;» (точка с запятой).
Диаграммы и меню «Формат»
Последний этап нашей работы над диаграммой (рис. 14) будет связан с использованием меню «
Формат». Напомню, что большинство действий по форматированию элементов диаграммы можно сделать через правую кнопку мыши. Но некоторые параметры форматирования доступны именно через меню «Формат», и никак иначе. Чтобы познакомиться с возможностями данного меню, мы проделаем такие изменения.За основу возьмем диаграмму, как на рис. 14. Вокруг легенды на диаграмме создадим скругленную рамку. Заголовок той диаграммы оформим в стиле «
WordArt». Делаем так:1) открываем документ с диаграммой. Выделяем ее;
2) вызываем меню «
Работа с диаграммами»;3) щелкаем на пункте «
Формат» этого меню. Лента примет вид, как на рис. 20. 
На ленте пять групп иконок:
— «Текущий формат»: позволяет отформатировать любой объект диаграммы. Для этого нужно выделить объект, щелкнуть на иконке «Формат выделенного объекта». Откроется соответствующее окно форматирования. В нем нужно задать параметры и применить их. Очень полезная кнопка в этой группе — «Восстановить форматирование стиля». Она позволяет вернуть параметры форматирования объекта, принятые по умолчанию;
— «Стили фигур»: назначает выбранный стиль различным объектам диаграммы. Набор этих стилей зависит от типа объекта. Для легенды это будут различные обрамления. Для осей — всевозможные линии и т. д. В этой же группе параметров расположены пиктограммы для выбора заливки фигуры, контура фигуры и задания эффектов для автофигур. Большинство из этих параметров доступны и в окне форматирования объекта;
— «Стили WordArt»: здесь можно придать тексту специфическое начертание. Три иконки в правой части этой группы позволяют изменить цвет текста, цвет контура текста, придать тексту эффект тени или отражения;
— «Упорядочить»: эта группа параметров полезна при работе с множеством диаграмм и графических элементов, когда они должны определенным образом перекрывать друг друга. С помощью пиктограмм «На передний план», «На задний план» можно располагать один объект поверх другого. Три вертикально стоящие пиктограммы в правой части группы позволяют выровнять диаграммы друг относительно друга, сгруппировать несколько диаграмм в один объект, повернуть диаграмму по или против часовой стрелки.
Полезную возможность предлагает пиктограмма «Область выделения» в группе «Упорядочить». Она открывает специальное окно, в котором перечислены все имеющиеся диаграммы рабочей книги. Щелчок на любом объекте этого окна выделить нужную диаграмму. Работа с пиктограммой «Область выделения» незаменима в тех случаях, когда на рабочем листе собрано много графических элементов;
— «Размер»: эта группа содержит всего два параметра — размер диаграммы по горизонтали и по вертикали.
Теперь вернемся к нашей задаче. Чтобы отформатировать легенду и надпись на диаграмме, делаем так:
1) щелкаем на легенде диаграммы, изображенной на рис. 14;
2) открываем раздел «Формат» меню «работа с диаграммами»;
3) в группе иконок «Стили фигур» щелкаем на элементе «Цветной контур — Темный 1»;
4) щелкаем на заголовке диаграммы. Появление маркеров говорит о том, что заголовок выделен;
5) щелкаем левой кнопкой внутри рамки с заголовком, чтобы получить доступ к тексту;
6) выделяем текст заголовка и щелкаем на кнопке «Дополнительные параметры» группы «Стили WordArt». Значок «Дополнительные параметры» (прямоугольник со стрелкой) находится в правом нижнем углу группы «Стили WordArt» (рис. 20). Появится меню доступных стилей оформления;
7) из списка выбираем нужный элемент. Я остановился на варианте «Градиентная Заливка — Акцент 6, тень внутри»;
8) щелкаем правой кнопкой мыши на ряде данных «Lenovo»;
9) из контекстного меню выбираем «Добавить подписи данных». Над столбиками ряда «Lenovo» появятся числовые значения объемов продаж;
10) щелкаем левой кнопкой на любом значении ряда «Lenovo». Excel выделит весь ряд;
11) в группе «Текущий формат» щелкаем на иконке «Формат выделенного фрагмента». Откроется окно «Формат подписей данных», как на рис. 21;
12) переходим на закладку «Выравнивание», щелкаем на параметре «Направление текста»;
13) выбираем вариант «Повернуть весь текст на 2700» (рис. 21);
14) в окне «Формат подписей данных» нажимаем «Закрыть»;
15) включаем подписи данных для остальных рядов диаграммы. Все, что остается сделать, — назначить размеры шрифтов на диаграмме, выбрать тип линии сетки. Окончательный результат нашей работы показан на рис. 22.
На этом мы закончим работу с деловой графикой Excel 2007. Думаю, рассмотренных возможностей и советов более чем достаточно для практической работы. А в следующий раз мы приступим к настройке параметров программы Excel 2007. Успешной работы!
Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
