Табличный процессор 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. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка fx). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические» (рис. 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 на панел