Табличный процессор MS Excel

ТАБЛИЧНЫЙ ПРОЦЕССОР MS EXCEL

Краткие сведения

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

Excel относится к программным продуктам, которые известны под названи­ем электронные таблицы. Электронная таблица – это интерактивная про­грамма, состоящая из набора строк и столбцов, изображенных на экране в специальном окне. Область, находящаяся на пересечении строки и столбца, называется ячейкой. В ячейке могут находиться число, текст или формула, с помощью которой осуществляются вычисления, относящиеся к одной или нескольким ячейкам. Ячейки можно копировать, перемещать, а также изме­нять их содержимое.

Ячейки могут содержать независимые данные, которые, как правило, заносятся вручную или загружаются из некоторого файла данных. Эти данные могут ис­пользоваться как исходные для вычисления значений других ячеек. В ячейки с вычисляемыми значениями заносятся формулы для вычислений. При каждом изменении содержимого ячейки производится ав­томатический пересчет содержимого всех ячеек, использующих в формулах измененную ячейку. На основе групп ячеек создаются диаграммы, сводные таблицы и карты. Электронную таблицу можно сохранить в отдельном фай­ле для дальнейшего использования.

Пользователь обычно видит на экране результаты вычислений по заданным формулам. Современные табличные процессоры, к которым можно причислить и Excel, пре­доставляют пользователю следующие возможности:

· работа с «рабочими книгами»(совокупностью таблиц);

· задание в таблицах чисел и формул, пересчет значений вычисляемых ячеек при изменениях исходных данных;

· построение диаграмм по данным таблиц;

· большой набор встроенных функций;

· автоматическое заполнение ячеек последовательностями (дни недели, меся­цы и т. п.);

· возможность работы с внешними базами данных;

· управление параметрами текста;

· вывод таблиц на печать;

· возможность создания макросов (серии команд, сгруппированных вместе для упрощения работы);

· проверка орфографии;

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

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

Операции с рабочими листами

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

Для переименования листа, например с именем лист 1, щелкните правой кнопкой мыши на ярлычке листа и в раскрывшемся контекстном меню вы­берите команду Переименовать (Rename). В открывшемся диалоговом окне Переименование листа (Rename Sheet) в поле Имя листа введите новое имя листа, например отчет, и нажмите кноп­ку ОК. В результате произойдет переименование листа. Переиме­новать выделенный лист можно также командой Формат, Лист, Переимено­вать (Format, Sheet, Rename).

При необходимости удалить рабочий лист щелкните правой кнопкой мыши на ярлычке листа и в раскрывшемся контекстном меню выберите команду Удалить (Delete). Перед удалением Excel предупреждает, что восстановить удаленный лист невозможно. Удалить лист можно и командой Правка, Уда­лить лист (Edit, Delete Sheet).

Вставка нового листа осуществляется щелчком правой кнопки мыши на яр­лычке листа, перед которым необходимо вставить новый лист. Выбор ко­манды Вставка (Insert) в раскрывшемся контекстном меню открывает диа­логовое окно Вставка (Insert), в котором следует выбрать тип вставляемого листа (рис. 2.3). Новый рабочий лист можно вставить и с помощью коман­ды Вставка, Лист (Insert, Worksheet).

Для перемещения или копирования листа щелкните правой кнопкой мыши на ярлычке листа и в раскрывшемся контекстном меню выберите команду Переместить/скопировать (Move or Copy).

Рис. 2.3. Вкладка Общие диалогового окна Вставка

В диалоговом окне Переместить или скопировать (Move or Copy Sheet) (рис. 2.4) укажите, в какую рабочую книгу следует переместить выбранный лист и перед каким листом он дол­жен располагаться. В случае копирования листа необходимо установить флажок Создавать копию (Create a Copy).

Рис. 2.4. Диалоговое окно Переместить или скопировать

Копировать или перемещать выделенный лист можно и с помощью коман­ды Правка, Переместить/скопировать (Edit, Move or Copy).

Операцию перемещения листа удобно производить мышью. Для этого необ­ходимо установить указатель мыши на ярлычке листа и, нажав левую кноп­ку мыши, перетащить ярлычок в нужное место. Подобным образом можно буксировать не только ярлычки листов, но и выделенные ячейки, их группы и любые графические объекты. Если буксировка производится при нажатой клавише <Ctrl>, то объект копируется.

Команда Формат, Лист, Скрыть (Format, Sheet, Hide) позволяет скрыть вы­деленный лист, а команда Формат, Лист, Показать (Format, Sheet, Unhide) – отобразить скрытый лист.

Перетаскивая Вешку ярлычков (Tab split box), можно изменить число ярлыч­ков, отображаемых на экране (рис. 2.l).

Excel предоставляет возможность работать с группой листов как с одним в так называемом Групповом режиме (Group). Выделение группы листов осу­ществляется одним из следующих способов:

· Щелкните правой кнопкой мыши на ярлычке листа и в раскрывшемся контекстном меню выберите команду Выбрать все листы (Select all sheets);

· Добавить отдельный лист в группу можно, щелкнув на его ярлычке при нажатой клавише <Ctrl>;

· Выделить в группу несколько подряд идущих листов можно, щелкнув первый и последний ярлычки группы, причем щелчок на последнем яр­лычке необходимо выполнять при нажатой клавише <Shift>.

Ярлычки листов, выделенных в группу, окрашены белым цветом (рис. 2.1). При желании выделенную группу листов можно удалять, перемещать, копи­ровать, вводить в нее информацию как в один лист.

Раскрывающийся список на панели Стандартная (Standard) по­зволяет изменять масштаб отображения рабочего листа. Для более точного масштабирования воспользуйтесь командой Вид, Масштаб (View, Zoom).

Рис. 2.5. Диалоговое окно Масштаб

В открывшемся диалоговом окне Масштаб (Zoom) установите необходимый масштаб от 10 до 400% (рис. 2.5).

 

Адресация ячейки

На активном рабочем листе одна ячейка является активной (active) или, другими словами, выделенной. Эта ячейка обрамлена черной рамкой. Пере­мещение черной рамки по рабочему листу осуществляется мышью или кла­вишами <↑>, <↓>, <←> и <→>. Каждая ячейка на активном рабочем листе определяется своим адресом, состоящим из имени столбца и номера строки, например А1. Ячейка на неактивном рабочем листе идентифицируется име­нем листа и ее адресом на листе, например лист2!А1 (обратите внимание на разделительный восклицательный знак).

Существует другой способ адресации ячейки – по имени. Имя или адрес активной ячейки выводится в поле имен, которое расположено у левого края строки формул (рис. 2.1).

Для присвоения имени активной ячейке выберите команду Вставка, Имя, Присвоить (Insert, Name, Define) и в поле ввода Имя (Name) открывшегося диалогового окна Присвоить имя (Define Name) введите новое имя ячейки, например Первая _ячейка.

В именах ячеек не допускаются пробелы, поэтому в качестве разделителя между словами обычно используют символ подчеркивания "_". При нажатии кнопки ОК ячейке присваивается указанное имя. Конечно, активной ячейке можно присвоить имя и непосредственно, введя его в поле имени. Адреса­ция ячейки по имени абсолютна, поэтому при ссылке на ячейку по имени на неактивном рабочем листе нет необходимости указывать имя этого листа.

Ввод формулы в ячейку

Для вычисления возвращаемой суммы нужно ввести в нужную ячейку рас­четную формулу. Формула в ячейке начинается со знака равенства "=" и мо­жет содержать операторы (табл. 2.1), пары круглых скобок, числа, адреса или имена ячеек, а также функции рабочего листа, вводимые ко­мандой Вставка, Функция (Insert,Function) или нажатием кнопки Мастер функций (Function Wizard) . Завершается ввод формулы нажатием клавиши <Enter> или кнопки , а также щелчком левой кнопки мыши.

Таблица 2.1. Допустимые операторы

Оператор	Название
+	Сложение
-	Вычитание
*	Умножение
/	Деление
^	Возведение в степень
&	Конъюнкция
=	Логическое сравнение
>	Логическое сравнение «больше»
>=	Логическое сравнение «больше или равно»
<	Логическое сравнение «меньше»
<=	Логическое сравнение «меньше или равно»
<>	Логическое сравнение «не равно»

 

По умолчанию Excel создает в формулах относительные ссылки на адреса ячеек. Это означает, что Excel при копировании изменяет ссылки на ячейки в соответствии с новым положением формулы. Причем, копирование формулы может быть выполнено как традиционным способом (через буфер обмена), так и способом, присущим только процессору Excel (с помощью маркера заполнения).

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

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

Технология работы

1. Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel).

Рис. 2.7. Исходные данные для Задания 7.1.

2. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.

3. Для оформления шапки таблицы выделите ячейки на тре­тьей строке (массив A3:D3) и создайте стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль (рис. 2.8) наберите имя стиля «Обычный» и нажмите кнопку Изменить.

Рис. 2.8. Создание стиля оформления шапки таблицы

В открывшемся окне на вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание – по центру (рис. 2.9), на вкладке Число укажите формат – Текстовый. После этого нажмите кнопку Добавить.

Рис. 2.9. Окно форматирования ячеек

4. На третьей строке введите названия колонок таблицы – «Дни недели», «Доход», «Расход», «Финансовый результат», далее запол­ните таблицу исходными данными согласно Заданию 7.1.

Краткая справка. Для ввода дней недели наберите «Поне­дельник» и произведите автокопирование до «Воскресенья» (ле­вой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).

5. Произведите расчеты в графе «Финансовый результат» по сле­дующей формуле:

Финансовый результат = Доход - Расход,

для этого в ячейке D4 наберите формулу

= В4 - С4.

Краткая справка. Введите расчетную формулу только для расчета по строке «Понедельник», далее произведите автокопиро­вание формулы (так как в графе «Расход» нет незаполненных дан­ными ячеек, можно производить автокопирование двойным щел­чком мыши по маркеру автозаполнения в правом нижнем углу ячейки).

6. Для ячеек с результатом расчетов задайте формат «Де­нежный» с выделением отрицательных чисел красным цветом (Формат/Ячейки /вкладка Число/формат - Денежный/От­рицательные числа – красные.)Число десятичных знаков задайте равное 2 (рис. 2.10). Обратите внимание, как изменился цвет отрицательных значе­ний финансового результата с черного на красный.

Рис. 2.10. Задание формата отрицательных чисел красным цветом

 

7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка f_x). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические» (рис. 2.11). Для расчета фун­кции СРЗНАЧ дохода установите курсор в соответствующей ячей­ке для расчета среднего значения (В12), запустите мастер функ­ций (Вставка/Функция/категория - Статистические/СРЗНАЧ). В качестве первого числа выделите группу ячеек с данны­ми для расчета среднего значения – В4:В10. Аналогично рассчитайте среднее значение расхода. Те же значения можно получить, пользуясь кнопкой Автосуммирования, выбрав вкладку Среднее и выделив группу ячеек для которых вычисляется среднее значение.

Рис. 2.11. Выбор функции расчета среднего значения

 

8. В ячейке D13 выполните расчет общего финансового результа­та (сумма по столбцу «Финансовый результат»). Для вычисления суммы воспользуйтесь кнопкой Автосуммирования на панели инструментов (как при вычислении среднего значения) или функцией СУММ (Вставка/ Функция/категория - Математические/СУММ), как показано на рис. 2.12.

Рис. 2.12. Задание интервала ячеек при использовании функции СУММ

9. Проведите форматирование заголовка таблицы. Для этого вы­делите интервал ячеек от А1 до D1, объедините их кнопкой пане­ли инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/вкладка Выравнивание / отображение – Объе­динение ячеек. Задайте начертание шрифта – полужирное; цвет – по вашему усмотрению.

Конечный вид таблицы приведен на рис. 2.13.

Рис. 2.13. Таблица расчета финансового результата

10. Постройте диаграмму (линейчатого типа) изменения фи­нансовых результатов по дням недели с использованием мастера диаграмм.

Для этого выделите интервал ячеек с данными финансового результата и выберите команду Вставка/Диаграмма. На первом шаге работы с мастером диаграмм выберите тип диаграммы – линейча­тая; на втором шаге на вкладке Ряд в окошке Подписи оси X укажи­те интервал ячеек с днями недели – А4:А10. Далее введите название диаграммы и подписи осей; дальней­шие шаги построения диаграммы осуществляются автоматичес­ки по подсказкам мастера. Конечный вид диаграммы приведен на рис. 2.14.

11. Произведите фильтрацию значений дохода, превышающих 5400 р.

Краткая справка. В режиме фильтра в таблице видны толь­ко те данные, которые удовлетворяют некоторому критерию, при этом остальные строки скрыты. В этом режиме все операции фор­матирования, копирования, автозаполнения, автосуммирования и т.д. применяются только к видимым ячейкам листа.

Рис. 2.14. Конечный вид диаграммы Задания 7.1

 

Для установления режима фильтра выделите одну из ячеек таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся кнопки со стрелкой, направленной вниз. Щелк­ните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выбе­рите команду для фильтрации – Условие (рис. 2.15).

Рис. 2.15. Выбор условия фильтрации

В открывшемся окне Пользовательский автофильтр задайте условие «Больше 5000» (рис. 2.16).

Рис. 2.16. Окно пользовательского автофильтра

Произойдет отбор данных по заданному усло­вию. Проследите, как изменились вид таблицы (рис. 2.17) и построенная диаграмма.

Рис. 2.17. Вид таблицы после фильтрации данных

12. Сохраните созданную электронную книгу в своей папке.

 

Задание 7.2. Оформление ведомости учета брака.

Произвести необходимые рас­четы, выделить минимальную, максимальную и среднюю сум­мы брака, а также средний процент брака; произвести фильтра­цию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака по месяцам (рис. 2.18).

Рис. 2.18. Исходные данные для задания 7.2

Формула для расчета:

Сумма брака = Процент брака х Сумма зарплаты.

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/вкладка Число/фор­мат – Процентный). Проведите сортировку по фамилиям в алфавитном порядке (или табельным номерам) по возрастанию. С этой целью выделите фрагмент таблицыс заголовка до последней строки с7писка фамилий (без итогов). Затем выберете меню Данные / Сортировка, сортировать по Ф.И.О. (или Табельный номер).

 

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel.

2. Создайте таблицу штатного расписания фирмы по приведен­ному образцу (рис.2.19). Введите исходные данные в рабочий лист электронной книги.

Рис. 2.19. Исходные данные для задания 8.1

 

3. Выделите ячейку D3 для зарплаты курьера (пере­менная «x») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

4. В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: = В6 * $D$3 + С6, где ячейка D3 задана в виде абсо­лютной адресации. Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.

В столбце F задайте формулу расчета заработной платы всех ра­ботающих в данной должности. Например, для ячейки F6 формула расчета имеет вид: = D6 * Е6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.

В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.

5. Произведите подбор зарплат сотрудников фирмы для суммар­ной заработной платы, равной 100000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы;

в поле Значение наберите искомый результат 100000;

в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000р. (рис. 2.20).

Рис. 2.20. Результаты расчета окладов сотрудников фирмы

 

6. Присвойте рабочему листу имя «Штатное расписание 1». Со­храните созданную электронную книгу под именем «Штатное рас­писание» в своей папке.

Анализ задач показывает, что с помощью MS Excel можно ре­шать линейные уравнения. Задания 8.1 и 8.2 показывают, что поиск значения параметра формулы – это не что иное, как чис­ленное решение уравнений. Другими словами, используя возмож­ности программы MS Excel, можно решать любые уравнения с одной переменной.

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

 

Основные типы диаграмм MS Excel приведены в табл. 2.25.

Таблица 2.25. Типы диаграмм MS Excel

Название	Описание
Стандартные типы диаграмм
Гистограммы	Используются для сравнения отдельных величин или их изменений в течение некоторого периода времени. Удобны для отображения дискретных данных
Линейчатые диаграммы	Похожи на гистограммы (отличие – повернуты на 90° диаграммы по часовой стрелке). Используются для сопоставления отдельных значений в определенный момент времени, не дают представления об изменении объектов во времени. Горизонтальное расположение полос позволяет под­черкнуть положительные или отрицательные отклонения от некоторой величины. Линейчатые диаграммы можно использовать для отображения отклонений по разным статьям бюджета в определенный момент времени. Можно перетаскивать точки в любое положение.
Название	Название
Графики	Отображают зависимость данных (ось Y)от величины, которая меняется с постоянным шагом (ось X). Метки оси категорий должны располагаться по возрастанию или убыванию. Графики чаще используют для коммер­ческих или финансовых данных, равномерно распреде­ленных во времени (отображение непрерывных данных), или таких категорий, как продажи, цены и т. п.
Круговые диаграммы	Отображают соотношение частей и целого и строятся диаграммы только по одному ряду данных, первому в выделенном диапазоне. Эти диаграммы можно использовать, когда компоненты в сумме составляют 100%
Точечные диаграммы	Хорошо демонстрируют тенденции изменения данных диаграммы при неравных интервалах времени или других интерва­лах измерения, отложенных по оси категорий. Можно использовать для представления дискретных измерений по осям X и Y. Вточечной диаграмме деления на оси категорий наносятся равномерно между самым низким и самым высоким значением X
Диаграммы с областями	Позволяют отслеживать непрерывное изменение суммы с областями значений всех рядов данных и вклад каждого ряда в эту сумму. Этот тип применяется для отображения процесса производства или продажи изделий (с равно отстоящими интервалами)
Кольцевые диаграммы	Сравнивают вклад частей в целое. В отличие от круговой диаграммы на кольцевой диаграмме могут быть представлены два и более ряда данных
Лепестковые диаграммы	Используют обычно, чтобы показать соотношения отдельных рядов данных, а также – одного определенного ряда данных и всех остальных рядов. Каждая категория лепестковой диаграммы имеет собственную ось координат (луч). Точки данных располагаются вдоль луча. Ли­нии, соединяющие точки данных одного ряда, охваты­вают площадь, характеризующую совокупность значе­ний в этом ряду. На лепестковой диаграмме можно отобразить, например, динамику затрат времени на про­ект, включающий несколько задач. В этом случае каж­дой категории (лучу) соответствует определенная задача проекта, а точке на луче – затраты времени на нее к какому-то сроку
Поверхность	Показывает низкие и высокие точки поверхности. Эти диаграммы используются для набора данных, который зависит от двух переменных. Диаграмму можно повора­чивать и рассматривать с разных точек зрения
Пузырьковые диаграммы	Позволяют отображать на плоскости наборы из трех значений. Первые два значения откладываются по осям X и Y. Третье значение представляется размером пузырька
Биржевая	Используется для отображения изменения информации о ценах на бирже. Отображает наборы данных из трех значений
Цилиндрические и др.	Являются объемными вариантами гистограмм и линейчатых диаграмм
Нестандартные типы диаграмм
Нестандартные типы, в отличие от стандартных, имеют некоторые улучшения в форматировании и отображении

 

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

1. Подготовить диапазон ячеек и ввести в них данные для диаграммы.

2. Выделить подготовленный диапазон и воспользоваться мастером построения диаграмм (вызывается командой Вставка/Диаграмма либо кнопкой мастера диаграмм панели инструментов Стандартная).

3. Отформатировать полученную диаграмму.

Задание 13.1. Построение гистограммы годовых поставок.

Исходные данные: результаты поставок товара в период с 2002 года по 2009 год.

Решение

1. Введите данные на рабочий лист в соответствии с рис. 2.55.

Рис. 2.55. Подготовка данных для построения диаграммы

2. Выделите мышью диапазон А4:В11 и выполните команду Вставка/ Диаграмма либо нажмите кнопку мастера диа­грамм панели инструментов Стандартная.

3.Далее работа с мастером диаграмм осуществляется в пошаго­вом режиме:

• Шаг первый – выбор вида диаграммы. Для дискретных статистических данных подходя­щим видом диаграммы будет гистограмма (рис. 2.56).

Рис. 2.56. Работа с мастером диаграмм. Выбор типа диаграммы

 

• Нажав кнопку Далее,переходим к шагу 2 (рис. 2.57). На вкладке Ряд выполняется следующая последовательность действий:

o в поле Ряд удаляется с помощью кнопки Удалить вкладка с именем Ряд 1, которой на гистограмме соответствует ряд из значений столбца Год (см. примечание);

o заполни­те поля Ряд и Значения. В нашем случае в поле Ряд вносим Объем и в поле Значения – диапазон $В$4:$В$11;

o в поле Подписи по оси X указываем диапазон $А$4:$А$11.

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

Рис. 2.57. Определение рядов данных

 

Таким образом, на вкладке Ряд можно удалить либо добавить необходимые ряды данных, выбрать подпись ряда данных и, соответственно, диаграммы (поле Имя),и выбрать подписи по осям.

Перейдя к следующему шагу мастера диаграмм (кнопкой Далее), установите необходимые параметры диаграммы (рис. 2.58).

На последнем шаге укажите параметры размещения диаграммы:

· задайте расположение диаграммы на отдельном листе;

· укажите название листа с диаграммой – Продажи.

размещение диа­граммы (рис. 2.59).

4. Отформатируйте полученную диаграмму, используя контекст­ное меню каждого ее элемента.

 

Рис. 2.58. Выбор параметров диаграммы

Рис. 2.59. Построенная диаграмма Объем продаж

1. Щелкните по кнопке Готово.

2. Завершите создание диаграммы, настроив вид и расположение ее объектов (легенды, подписей данных и осей), чтобы в результате получилась диаграмма, изображенная на рис. 2.59.

Задание 13.2. Построение и настройка круговой диаграммы

По данным одного листа

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

Технология работы

1. Откройте файл с таблицей квартального расхода средств на закупку канцелярских принадлежностей, либо введите исходные данные на рабочий лист в соответствии с рис. 2.60.

Рис. 2.60. Таблица с исходными данными

 

2. Выделите область ячеек с итогами за I квартал: от ячейки В3 до ячейки С7. Желательно, чтобы блок ячеек включал как сами данные, так и их краткие названия. Последние будут использованы для формирования легенды (рас­шифровки условных обозначений на диаграмме).

3. Запустите мастер диаграмм командой Вставка > Диаграмма.

4. Шаг 1. Выберите тип и формат диаграммы: Объемный вариант разрезанной кру­говой диаграммы (рис. 2.61) и щелкните на кнопке Далее.

5. Шаг 2. На этом шаге не надо ничего делать, так как диапазон, по которому строится диаграмма, был указан выделением блока ячеек (отображается в строке Диапазон на рис. 2.62).

Затем щелкните на кнопке Далее.

Рис. 2.61. Выбор типа и вида диаграммы

 

Рис. 2.62. Определение диапазона исходных данных

6. Шаг 3. Настройте параметры диаграммы:

• на вкладке Заголовки укажите заголовок диаграммы: Расходы по канцтоварам за I квартал 2009 года;

• на вкладке Легенда укажите, где должна находиться легенда (справа);

• на вкладке Подписи данных отметьте имена категорий и доли (рис. 2.63);

• щелкните на кнопке Далее.

Рис. 2.63. Настройка подписей данных

 

3. Шаг 4. Укажите, что диаграмма должна находиться на отдельном листе, вы­брав первый переключатель (рис. 2.64).

8. Шаг 5. Щелкните на кнопке Готово, чтобы завершить работу мастера диаграмм.

Диаграмма будет размещена на отдельном листе книги (рис. 2.64). Ее настройки можно изменить при помощи панели инструментов Диаграмма (рис.2.65).

Рис. 2.64. Установка параметров размещения диаграммы

 

Рис. 2.65. Вид диаграммы с легендой и панелью инструментов Диаграмма

 

Выбрав в первом поле панели соответствующий объект, измените с помощью кнопки Формат свойства заголовка диаграммы, легенды, подписей данных.

Цвета сегментов диаграммы можно изменить с помощью кнопки Заливка.

И Мастера диаграмм

 

Для построения графиков Exсel предоставляет эффективное средство – Мастер диаграмм с большим набором графиков и диаграмм, позволяющий компактно и наглядно представить данные. Работа по построению графиков функций предполагает исполь­зование следующей методики:

1. Подготовить диапазон области определения функции (или функций) с помощью маркера автозаполнения.

2. Рассчитать значение функции (функций) на данном диапазоне, используя формулы и функции рабочего листа MS Excel и маркер автозаполнения.

3. Выделить диапазон области определения и области значения функции (функций) и воспользоваться мастером построения диаграмм.

4. Отформатировать полученный график (графики).

Задание 14.1. Построение графика функции.

В качестве примера рассмотрим процедуру

.

Для построения графика функции необходимо сначала построить таблицу ее значений при различных значениях аргумента, причем, обычно аргумент из­меняется с фиксированным шагом. Шаг выбирают небольшим, чтобы таб­лица значений функции отражала ее поведение на интервале табуляции. В данном случае будем считать, что шаг изменения аргумента равен 0,1. Не­обходимо найти: y(0), y(0,1), y(0,2),..., y(1). С этой целью в диапазон ячеек А1:А11 введем следующие значения переменной х:0; 0,1; 0,2;...; 1. Отметим, что выбранные нами значения переменной образуют арифметическую прогрессию. Заполнение ячеек членами арифметической прогрессии в Excel можно осуществить двумя способами:

Первый способ. В ячейки А1 и А2 вводятся первый и второй члены арифме­тической прогрессии, а затем, эти ячейки выделяют. После этого указатель мыши устанавливается на маркере заполнения выделенного диапазона (в виде жирного крестика) и протаскивается вниз до тех пор, пока не получится числовой ряд нужной длины.

Второй способ. В ячейку А1 вводится первый член арифметической прогрес­сии. Затем выбирается команда Правка, Заполнить, Прогрессия и в открывшемся диалоговом окне Прогрессия (рис. 2.66) в группе Распо­ложение устанавливается переключатель в положение По столбцам, а в группе Тип – в положение Арифметическая. В поле Шаг вводится значение 0,1, а в поле Предельное значение – 1. После нажатия кнопки ОК будет выполнено построение прогрессии.

Рис. 2.66. Диалоговое окно Прогрессия

С помощью команды Правка, Заполнить, Прогрессия мож­но создавать также геометрические прогрессии. Вернемся к примеру построения графика. В ячейку В1 введем формулу

Ввод формул в ячейку можно производить с клавиатуры или с помощью диалогового окна Мастер функций, вызываемого командой Вставка, Функция или нажатием кнопки панели инструментов Стандартная. Мастер функций содержит список всех встроенных в Excel функций, а также справки по синтаксису функций и примеры их применения.

Рассмотрим приемы работы с мастером функций на примере ввода выше­упомянутой формулы. Выделим ячейку В1 и нажмем кнопку панели ин­струментов Стандартная. На экране появится первое диалоговое окно Мастер функций (рис. 2.67).

Это окно содержит два списка: Категория – список, включающий 11 категорий функций, и Функция – список имен функций, входящих в выбранную категорию. Категория Полный алфа­витный перечень (АИ) содержит все встроенные функции Excel, имена кото­рых упорядочены в алфавитном порядке. Категория 10 недавно использо­вавшихся содержит имена десяти недавно исполь­зовавшихся функций. Она ускоряет вызов функций, постоянно используе­мых пользователем.

Рис. 2.67. Первое диалоговое окно Мастер функций

Функция cos относится к категории Математические. Выберем эту функцию и нажмем кнопку Далее. На экране появится второе диалоговое окно Аргументы функции (рис. 2.68).

Рис. 2. 68. Второе диалоговое окно Аргументы функции

 

В поле Число второго диалогового окна Аргументы функции вводится аргумент функции. В рассматриваемом примере это

ПИ () * А1.

Конечно, его можно ввести с клавиатуры. Однако этот аргумент содержит встроенную функцию ПИ(), поэтому, лучше, нажав кнопку , расположен­ную за полем COS на панел