Построение и редактирование диаграмм

Диаграмма – это способ представления данных. Смысл диаграммы состоит в том, чтобы более наглядно представить информацию, содержащуюся в числах таблиц. Хотя листы книг удобны для ввода информации и выполнения вычислений, тем не менее, данные на них довольно трудно анализировать. Как в течение года изменяется суточная реализация товара? Когда реализация товара достигает максимума? Ответы на эти и подобные вопросы проще всего получить, используя диаграммы.

В Excel имеется набор стандартных типов и видов диаграмм. Диаграммы можно создавать одним из следующих способов:

- как рисунок на одном рабочем листе с данными;

- на отдельном листе рабочей книги.

Диаграмма, построенная прямо на рабочем листе, называется внедренной. Если диаграмма расположена на отдельном листе, то этот лист называется листом диаграммы.

Структура диаграммы

Данные на диаграммах представляются точками, линиями, секторами и другими элементами в зависимости от типа диаграммы. Большинство диаграмм упорядочивают данные по горизонтальной (ось Х или ось категорий) и вертикальной (ось Y или ось значений) осям. Но такая привязка не является единственно возможной. Суть не в направлении, а в том, что представляет то или иное направление. Элементами диаграммы являются:

- ось значений (обычно вертикальная);

- точка на диаграмме соответствует значению, помещенному в ячейке таблицы на листе;

- маркер данных. Это графический символ, представляющий конкретное числовое значение;

- заголовок диаграммы;

- подписи значений;

- легенда. Содержит описание маркеров данных (поясняет, какие данные отображаются диаграммой. Например, товар А, товар В);

- заголовок оси значений;

- ось категорий с масштабными метками;

- заголовок оси категорий;

- область построения диаграммы.

Создание диаграмм. Работа с Мастером диаграмм

Для создания диаграмм в Excel достаточно выполнить следующие действия:

- выделить данные для диаграммы;

- запустить Мастер диаграмм. Для этого можно выполнить команду Вставка – Диаграмма или щелкнуть по кнопке Мастера диаграмм на стандартной панели инструментов.

Рассмотрим работу Мастера диаграмм для следующего примера таблицы, содержащей поквартальные количественные показатели продажи товаров:

Требуется получить диаграмму, отражающую товарооборот в первом квартале. Вначале выделим диапазон A4:B6, включающий наименования товаров и их количественные показатели за рассматриваемый квартал. Щелкнем по кнопке Мастер диаграмм на стандартной панели инструментов.

Работа Мастера диаграмм состоит из четырех шагов. На первом шаге выбирается переходящий тип диаграммы. В окне Мастер диаграммы (шаг 1 из 4): тип диаграммы представляются 14 типов стандартных диаграмм, а каждый тип имеет еще несколько видов. Тип диаграммы выбирается в поле Тип на вкладке Стандартные. Выбрав тип и вид диаграммы, можно просмотреть диаграмму, нажав и удерживая кнопку Просмотр результата.

Кроме того, в Excel предусмотрено несколько шаблонов нестандартных диаграмм. Тип нестандартной диаграммы можно выбрать на вкладке Нестандартные. Выбор типа диаграммы на этой вкладке автоматически приводит к появлению диаграммы в поле Образец. Выбрав тип диаграммы, следует щелкнуть по кнопке Далее.

Типы диаграмм

Какой тип диаграммы лучше выбрать? Это зависит от информации, для отображения которой предназначена диаграмма. Рассмотрим различные типы диаграмм и сформулируем рекомендации по их применению.

· Гистограммы удобно использовать для сравнения значений в одном или нескольких наборах данных. Их, например, можно использовать для сравнения годовых показателей реализации продукции за последние несколько лет.

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

· Линейчатые диаграммы это те же гистограммы, но с иной ориентацией осей. В отличие от гистограмм здесь ось категорий расположена вертикально, а ось значений – горизонтально. Линейчатые диаграммы применяются в тех же случаях, что и гистограммы. Горизонтальное расположение оси зависимых переменных делает их особенно удобными для сравнительного представления разных величин в пределах одного временного периода. Скажем, реализацию товара за месяц различными продавцами очень хорошо продемонстрирует линейчатая диаграмма.

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

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

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

· Поверхностные диаграммы используют для демонстрации взаимосвязей нескольких переменных или для наглядного представления больших объемов данных. Поверхностные диаграммы выглядят как рельефные карты местности, показывая "возвышенности" и "впадины" для большой совокупности данных.

· Пузырьковые диаграммы отображают на плоскости наборы из трех значений. Первые два значения определяют тоску расположения пузырька. В этом плане эти диаграммы подобны точечным. Третье значение отображается размером пузырька.

· Кольцевые диаграммы – это одна из разновидностей круговых диаграмм. Они также отражают связи между частями и целым. На кольцевых диаграммах, в отличие от круговых, можно представлять разные данные.

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

· Биржевые диаграммы отображают наборы данных из трех значений (например, самый высокий курс, самый низкий курс и курс закрытия).

· Цилиндрические, конические и пирамидальные диаграммы – это те же гистограммы со столбцами соответственно в виде цилиндров, конусов и пирамид.

Задание в диаграммах диапазона , содержащего данные

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

Заметим, что диапазон может состоять из нескольких строк или столбцов. Если данные систематизированы по строкам (столбцам), то выбирается режим Ряды встроках и столбцах. Выделение данных по строкам или столбцам приводит к разделению данных на диаграмме по рядам. Рядом данных называется информация, которую необходимо представить в виде диаграммы. Ряды можно добавлять и удалять с помощью кнопок Добавить и Удалить на вкладке Ряд. По умолчанию каждому набору данных присваивается имя "Ряд 1", "Ряд 2", и т. д. Эти имена можно изменять, вводя новые в поле Имя. Данные имена отображаются в легенде.

После определения диапазона ячеек с данными и задания рядов диаграммы следует перейти к следующему шагу, щелкнув по кнопке Далее.

Задание дополнительных элементов диаграммы

На третьем шаге работы Мастера диаграмм в окне Мастер диаграмм (шаг 3 из 4) параметры диаграммы можно добавить легенду, если Мастер этого не сделал; можно подписать оси, озаглавить диаграмму, подписать данные и выполнить некоторые другие операции. Для реализации этих операций в окне Мастера имеются вкладки: "Подписи данных", "Таблица данных", "Заголовки", "Оси", "Линии сетки", "Легенда".

Размещение диаграммы

Выбор варианта размещения диаграммы (внедренная диаграмма или на отдельном листе) осуществляется на четвертом шаге работы Мастера диаграмм в окне Мастер диаграмм (шаг 4 из 4): размещение диаграммы.

Заметим, что внедренные диаграммы хороши в тех случаях, когда требуется, чтобы данные были представлены рядом с диаграммами. В этом случае довольно быстро и просто выполняется переход от данных к диаграмме и наоборот. В случае использования листа диаграмм для размещения диаграммы выделяется отдельный лист. Данные для представляемой на отдельном листе диаграммы берутся с другого листа. Такое расположение удобно в случаях, когда необходимо получить только диаграмму, например, для статьи, для снятия на слайд и т.п.

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

Если диаграмма готова, следует щелкнуть по кнопке Готово.

Ниже показан окончательный вариант диаграммы для приведенной выше таблицы с общим заголовком и с заголовками для осей.

Редактирование диаграмм

1. Преобразование диаграмм к другому типу:

· выделить диаграмму, щелкнув по ней левой кнопкой мыши;

· щелкнуть правой кнопкой мыши в пределах выделенной области;

· из контекстного меню выбрать команду Тип диаграммы. В появившемся диалоговом окне выполнить двойной щелчок по любой из разновидностей диаграмм.

2. Добавление данных в диаграмму:

· выделить нужный диапазон данных в таблице;

· поместить курсор на нижний край выделенной области и перетащить на диаграмму. После отпускания кнопки мыши соответствующие данные появятся на диаграмме.

Замечание. Если Excelне может однозначно определить, как разместить данные, он выведет диалоговое окно Специальная вставка. После сделанных указаний следует щелкнуть по кнопке ОК.

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

· щелкнуть правой кнопкой мыши в области диаграммы;

· выбрать из контекстного меню команду Исходные данные;

· выделить соответствующие данные;

· щелкнуть на кнопке ОК в диалоговом окне Исходные данные.

3. Выделение (выдвижение) сектора круговой диаграммы:

· щелкнуть на секторе, который следует выдвинуть. При этом вокруг него появятся маркеры;

· плавно вытащить сектор на требуемое расстояние.

4. Вставка легенды:

· активизировать панель инструментов Диаграммы.Для этого выполнить команду Вид – Панель инструментов – Диаграммы;

· на панели Диаграммынажать кнопку Легенда. При этом будет вставлено поле легенды рядом с диаграммой. При необходимости это поле можно перетащить в любое место в пределах области диаграммы или изменить его размеры с помощью размерных маркеров.

5. Разметка значениями:

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

· щелкнуть правой кнопкой на выделенном элементе. Из появившегося контекстного меню выбрать команду Формат ряда данных;

· в появившемся диалоговом окне Формат ряда данных выбрать вкладкуПодписи данных. Установить переключатель Значение;

· щелкнуть по кнопке OK.

6. Размещение подписей данных на диаграмме:

· выделить маркерами любой из элементов, представляющих в диаграмме значение (точка, столбец, линии);

· щелкнуть правой кнопкой на выделенном элементе. Из появившегося контекстного меню выбрать команду Формат ряда данных;

· в появившемся диалоговом окне Формат ряда данныхвыбрать вкладку Подписи данных.Установить переключатель Категория;

· щелкнуть по кнопке OK.

7. Форматирование подписей данных:

· выполнить двойной щелчок на любой из подписей данных и активизировать диалоговое окно Формат подписей данных;

· перейти на вкладку Шрифт и задать требуемые тип, размер и начертание шрифта;

· перейти на вкладку Выравнивание;

· задать ориентацию в области Ориентация в поле со словом надпись (можно установить вертикальную ориентацию в поле со словом Текст);

· щелкнуть по кнопке ОК.

Замечание. Щелчок на подписи приводит к появлению вокруг нее серой рамки с маркерами. Захватив край рамки мышью, можно перетащить ее в подходящее место.

8. Форматирование осей:

· дважды щелкнуть на требуемой оси;

· в появившемся диалоговом окне Формат оси можно изменить толщину, цвет и тип линии оси, задать другой шрифт, расположить текст вертикально и др.

Для выбора шага приращения по оси необходимо выполнить следующие действия:

· в диалоговом окне Формат оси выбрать вкладку Шкала;

· щелкнуть на поле значения, используемого для масштабирования оси, и ввести новую величину.

9. Добавление заголовка:

· щелкнуть правой кнопкой мыши на любом месте в области диаграммы;

· выбрать в контекстном меню команду Параметры диаграммы;

· перейти на вкладку Название диаграммы;

· ввести имя диаграммы и щелкнуть по кнопке ОК.

10. Оформление диаграммы цветом:

· выделить элемент данных на диаграмме;

· дважды щелкнуть мышью на этом элементе. Появится диалоговое окно Формат ряда данных;

· перейти на вкладку Вид;

· выбрать для элемента данных цвет в области Заливка;

· щелкнуть по кнопке Способы заливки и выбрать один из способов на вкладке Градиентная, Текстура, Узор, Рисунок;

· для возврата в предыдущие окна нажимать кнопку OK.

Нахождение корней уравнения с помощью подбора параметра и
построения графика

В данном разделе будет продемонстрировано, как на рабочем листе при помощи средства Подбор параметра можно находить корни уравнения с одним аргументом.

В качестве базового примера рассмотрим следующее уравнение:

X³ – 0,01·X² – 0,7044·X + 0,139104 = 0

Замечание. Так как мы ищем корни многочлена третьей степени, то имеется не более трёх действительных корней.

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

Например, протабулируем наш многочлен на интервале [-1;1] с шагом 0,2. С этой целью:

1) введем в ячейку А2 значение -1, а в ячейку А3 – значение -0.8;

2) выберем диапазон А2:А3, расположиv указатель мыши на маркере автозаполнения этого диапазона и протянем его на диапазон А4:А12;

3) в ячейку В2 введем формулу:

=А2^3-0,01*A2^2-0,7044*A2+0,139104

4) выберем ячейку В2. Расположим указатель мыши на маркере автозаполнения ячейки и протянем его вдоль диапазона В3:В12.

Рис. 4.1. Локализация корней многочлена и диалоговое окно
Подбор параметра

Из рис. 4.1 видно, что многочлен меняет знак на интервалах [-1;-0,8], [0,2;0,4] и [0,6;0,8], и поэтому на каждом из этих интервалов имеется свой корень. Так как многочлен третьей степени имеет не более трёх корней, то они все локализированы.

Прежде чем приступить к нахождению корней при помощи механизма Подбора параметра, необходимо выполнить некоторую подготовительную работу:

- следует установить точность, с которой ищется корень. Заметим, что корень при помощи Подбора параметра находится методом последовательных приближений. Для этого нужно выбрать команду Сервис – Параметрыи на вкладке Вычислениязадать относительную погрешность и предельное число итераций равным, например, 0,00001 и 1000, соответственно;

- следует отвести на рабочем столе ячейку, например С2, под искомый корень. Эта ячейка будет играть двойную роль. До применения подбора параметра в ней находится начальное значение приближения к корню уравнения, а после применения – найденное приближение значение корня;

- корень при помощи Подбора параметра находим методом последовательных приближений. Поэтому в ячейку С2 надо ввести значение, являющееся приближением к искомому корню. В нашем случае, первым отрезком локализации корня является [-1;-0,8]. Следовательно, за начальное приближение к корню разумно взять среднюю точку этого отрезка -0,9;

- следует отвести ячейку, например D2, под функцию, для которой ведётся поиск корня, причём вместо неизвестной y этой функции должна указываться ссылка на ячейку, отведённую под корень. Таким образом, в ячейку D2 необходимо ввести формулу:

=С2^3 – 0,01*С2^2 – 0,7044*С2 + 0,139104

Аналогично надо поступить с двумя другими искомыми корнями:

- отвести ячейку С3 под второй корень, ввести в неё начальное приближение 0,3, а в ячейку D2 ввести следующую формулу

=С3^3-0,01*C3^2 – 0,7044*C2 + 0,139104

- отвести ячейку С4 под третий корень, ввести в неё начальное приближение 0,7, а в ячейку D4 ввести следующую формулу

=С4^3 – 0,01*C4^2 – 0,7044*C4 + 0,139104

Теперь можно переходить к нахождению первого корня уравнения. Для этого следует выполнить последовательность действий:

1) выполнить команду Сервис – Подбор параметра. На экране отобразится диалоговое окно Подбор параметра;

2) в поле Установить в ячейке ввести ссылку на ячейку D2 (рис. 2.19). В этом поле даётся ссылка на ячейку, в которой введена формула, вычисляющая значение левой части уравнения. Для нахождения корня с помощью подбора параметра уравнение надо представить в таком виде, чтобы его правая часть не содержала переменную;

3) в поле Значение ввести 0. Здесь указывается значение из правой части уравнения;

4) в поле Изменяя значение ячейки ввести С2. В данном поле задается ссылка на ячейку, отведённую под переменную;

5) нажать кнопку ОК.

На экране отображается окно Результат подбора параметра с результатами работы команды Подбор параметра. Кроме того, рассматриваемое средство помещает найденное приближенное значение корня в ячейку С2. В данном случае оно равно -0,71999.

Аналогично в ячейках С3 и С4 находятся два оставшихся корня. Они равны 0,21000 и 0,71999 (рис. 4.2).

Рис. 4.2. Все найденные корни уравнения