Тема: Построение и редактирование диаграмм.
Количество часов: 2 ч.
Упражнение 1. Построение обычной и трехмерной гистограмм.
Для построения диаграммы необходимо иметь числовые данные, на основе которых она будет построена. Введите в диапазон В2:В7 следующие числа: 13; 14; 17; 21; 15; 28.
Выделите любую ячейку вне этого диапазона. Именно «вне», т.к. в этом случае EXCEL не будет автоматически определять исходные данные. Этот способ более длинный, но зато мы лучше поймем все этапы построения диаграммы.
Выполните команды: Вставка Þ Диаграмма...
Шаг 1. Выбор типа и формата диаграммы. На этом шаге необходимо выбрать тип диаграммы и задать (в области справа) формат, который делает ее более выразительной. Выберите тип Гистограмма и вид Обычная. После выбора надо нажать на кнопке Далее и перейти на следующий шаг.
Шаг 2. Выбор и указание диапазона данных. На этом шаге задается диапазон данных, для которого будет построена диаграмма. Для этого с помощью переключателя укажите расположение данных – по строкам или по столбцам будет строиться выбранный тип диаграммы (в нашем случае по столбцам). Далее установите курсор в поле Диапазон и с помощью мыши выделите необходимый блок ячеек, адрес которого автоматически отобразится в поле. Выделите диапазон В2:В7, в поле отобразится формула =Лист1!$B$2:$B$7, что является так называемой трехмерной абсолютной ссылкой этого диапазона. Нажмите кнопку Далее >.
Шаг 3. Задание параметров диаграммы. Задание параметров диаграммы осуществляется в окнах вкладок Мастера диаграмм. Оставьте все без изменений и нажмите на кнопке Далее >
Шаг 4. Размещение диаграммы.
Созданную диаграмму можно создать на том же листе с таблицей с исходными данными, либо на отдельном листе. В диалоговом окне для этого надо установить соответствующий переключатель и нажать кнопку Готово.
Полученную диаграмму можно редактировать, а именно, перемещать, изменять размер, задавать параметры элементов диаграммы. Выделите элемент диаграммы, например Ряд1, и выполните Формат Þ Выделенный ряд или два раза щелкните на элементе. В появившемся диалоговом окне измените значения параметров (цвет ряда, например).
В EXCEL’е есть возможность так называемого графического под бора параметра. Она заключается в том, что мы можем, изменяя размер элемента диаграммы (высоту столбца) изменить и число, соответствующее этому элементу.
На нашей диаграмме мы видим, что пятый столбец ряда «нарушает» прослеживающуюся тенденцию, заключающуюся в постоянном увеличении чисел. Выделите его (сначала один раз щелкните по ряду, затем по этому столбцу) и потянув за маркер увеличьте его высоту. Вы увидите, что число в ячейке В6 соответственно изменилось. Вытянув столбец сделайте так, чтобы число в ячейке В6 стало равным 24.
Введите дополнительные данные (см. таблицу).
| A | B | C | |
| 1 | Нефть | Газ | |
| 2 | 1998 | 13 | 5 |
| 3 | 1999 | 14 | 7 |
| 4 | 2000 | 17 | 8 |
| 5 | 2001 | 21 | 10 |
| 6 | 2002 | 24 | 14 |
| 7 | 2003 | 28 | 18 |
Выделите диаграмму и выполните команды Диаграмма Þ Исходные данные… ÞВкладка Ряд.
В списке Ряд выделите Ряд1. Установите курсор в строку Имя. Выделите ячейку В1. Установите курсор в поле Подписи по оси Х и выделите диапазон А2:А7. Итак, мы изменили подписи по оси Х и название ряда на «Нефть». Теперь добавим еще один ряд: нажмите на кнопке Добавить, в списке Ряд появится Ряд2, укажите какие значения он должен включать: щелкните в поле Значения и выделите диапазон С2:С7, имя ряда возьмите из ячейки С1. Нажмите ОК. Добавьте заголовок диаграммы: выделите диаграмму и выполните команды Диаграмма Þ Параметры диаграммы… Þ вкладка Заголовки Þ в поле Название диаграммы введите «Динамика роста добычи углеводородов». Установите значения над каждым столбцом: Диаграмма Þ Параметры диаграммы… Þ вкладка Подписи данных Þ Включить в подписи значения.
Полученная диаграмма должна иметь следующий вид: 
Построим диаграмму в виде графика более быстрым способом. Выделите любую ячейку из таблицы. Выполните команды Вставка Þ Диаграмма.
Шаг 1. Тип гистограмма, вид трехмерная гистограмма (№7).
Шаг 2. На этом шаге ничего менять нам не придется, т.к. EXCEL сам «распознал», где у нас значения рядов, подписи по оси Х, названия рядов и т.д.
Шаг 3. Включите в подписи данных значения элементов. Названия рядов разместите внизу: на вкладке Легенда установите переключатель внизу.
Шаг 4. Разместите диаграмму на отдельном листе (Диаграмма 1).
Поменяйте порядок рядов: сделайте так чтобы ряд «Газ» шел перед рядом «Нефть». Выделите ряд «Газ» и выполните команды Формат Þ Выделенный ряд…, на вкладке Порядок рядов выделите «Газ» и щелкните по кнопке Вверх.
Упражнение 2. Элементы экономического анализа: линия тренда, прогноз на будущий период
Задача. Необходимо спрогнозировать рост накоплений компании на ближайший период.
| A | B | C | D | E | |
| 1 | Месяц | Доходы | Расходы | Остаток | Накопления |
| 2 | янв | 100 | 90 | 10,00 | 10 |
| 3 | фев | 90 | 50 | 40,00 | 50 |
| 4 | мар | 129 | 140 | -11,00 | 39 |
| 5 | апр | 143 | 150 | -7,00 | 32 |
| 6 | май | 209 | 100 | 109,00 | 141 |
| 7 | июн | 150 | 120 | 30,00 | 171 |
| 8 | июл | 167 | 150 | 17,00 | 188 |
| 9 | авг | 178 | 167 | 11,00 | 199 |
| 10 | сен | 213 | 200 | 13,00 | 212 |
| 11 | окт | 110 | 100 | 10,00 | 222 |
| 12 | ноя | 156 | 170 | -14,00 | 208 |
| 13 | дек | 178 | 180 | -2,00 | 206 |
| 14 | Итого | 1823 | 1617 | 206 |
Заполните данными ячейки таблицы от В2 до С13.
Введите в ячейку D2 формулу =B2-C2. Скопируйте формулу на остальные месяцы.
В ячейке В14 посчитайте сумму диапазона В2:В13. Скопируйте эту формулу на ячейки С14 и D14.
Пусть на начало года накоплений не было. Тогда накопления после января будут равны январским остаткам. Для этого введем в ячейку E2 формулу =D2. В феврале накопления составят сумму январских накоплений и февральских остатков, т.е. в ячейку E3 следует ввести формулу =E2+D3. Для остальных месяцев процедура начисления накоплений аналогичная. Поэтому скопируйте формулу ячейки Е3 на ячейки Е4:Е13.
Выделите ячейки D2:D14 и выполните команды Формат Þ Ячейки Þ Число Þ Числовой и в области Отрицательные числа выберите вариант -1234,10.
Сделайте так чтобы в тех ячейках, в которых накопления превысят 200, шрифт был красного цвета, полужирного начертания (выделите Е2:Е13 и выполните Формат Þ Условное форматирование… Þ значение | больше | 200 Þ нажать кнопку Формат Þ вкладка Шрифт Þ выбрать яркий цвет и полужирное начертание).
Постройте диаграмму на основе данных диапазона Е2:Е13. Вид диаграммы: График с маркерами. Подписи по оси Х возьмите из диапазона А2:А13.
Рассмотрим перспективы фирмы. В математике это называется «оценить тренд». Выделите диаграмму и выполните команды Диаграмма Þ Добавить линию тренда… На вкладке Тип выберите Линейная. На вкладке Параметры в области Прогноз установите вперед на 6 периодов. Установите флажок в опции Показывать уравнение на диаграмме.
Мы получили диаграмму роста накоплений и тенденцию на следующие 6 месяцев. По виду линии тренда можно оценить качество деятельности компании. Уравнение линии тренда позволит оценить и количественные показатели перспектив: первый коэффициент отражает угол наклона линии, и если он положителен, то имеется тенденция к росту.
Полученная диаграмма может использоваться для так называемого анализа «Что будет, если…?». Измените расход в июне со 120 до 320 единиц. Вы сразу же увидите, что появятся перспективы стать банкротом – кривая накоплений уверенно пошла вниз. Угловой коэффициент линии тренда k = -3,6713 < 0.