Организация вычислений и построение диаграмм в Microsoft Excel

Размещено на http://www.allbest.ru/

Практическая работа

Организация вычислений и построение диаграмм в Microsoft Excel

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

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

MSExcel позволяет организовать вычисления посредством формул и стандартных функций.

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

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

Создание формул

В MSExcel можно создать формулы двумя способами: непосредственным вводом с клавиатуры и указанием ячеек рабочего листа.

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

§ Выделить ячейку, в которую нужно ввести формулу;

§ Ввести знак равенства, с которого должна начинаться любая формула;

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

К сожалению, при вводе ссылок в формулу часто возникают ошибки. Их можно практически исключить, если создавать формулу, указывая ячейки с помощью мыши:

§ Выделить ячейку, в которую нужно ввести формулу и ввести знак = ;

§ Щелкнуть левой кнопкой мыши по ячейке, ссылку на которую необходимо ввести в формулу;

§ Далее - ввести арифметический знак и аналогичным способом выделить следующую ячейку и т.д.;

§ Нажатие ENTERзавершает ввод формулы.

Ссылки на ячейки

Как упоминалось ранее, в формулах часто используются ссылки на ячейки. В MSExcel используется несколько видов ссылок:

Относительные ссылкиРазмещено на http://www.allbest.ru/

на ячейки текущего рабочего листа;

Абсолютные ссылки Размещено на http://www.allbest.ru/

на ячейки текущего рабочего листа;

Смешанные ссылки Размещено на http://www.allbest.ru/

на ячейки текущего рабочего листа;

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

Размещено на http://www.allbest.ru/

Ссылки на данные другой рабочей книги.

Размещено на http://www.allbest.ru/

Чаще всего используются относительные ссылки (D6, F4, A3). При копировании формул относительные ссылки преобразуются так, чтобы соответствовать новому расположению формулы, т.е. настраиваются на номер строки и имя столбца, в которые копируются.

Абсолютные ссылки ($A$3) применяются в таких случаях, когда нужно использовать ссылки на конкретные ячейки, независимо от того, в какой ячейке находится сама формула.

Например, в ячейке А4 находится числовая константа, на которую требуется сделать ссылку в формуле A4*B2-C2. Если формулу в данном виде (с использованием относительных ссылок) скопировать в строку 3, то получим A5*B3-C3 и обращения к данным ячейки А4 не произойдет и результат будет вычислен неверно. Во избежание подобных ошибок, ссылки на ячейки, данные которых необходимо использовать в нескольких однотипных формулах, следует оформлять как абсолютные: $A$4*B2-C2.

Смешанные ссылки являются комбинациейабсолютных и относительных ссылок. Например, можно создать ссылку, состоящую из относительной ссылки на столбец и абсолютной - на строку (А$5), или наоборот ($A5). Если скопировать формулу, содержащую смешанную ссылку $A5, то номер строки изменится в зависимости от нового положения формулы, а номер столбца останется прежним.

Можно использовать ссылки на ячейки другого рабочего листа одной рабочей книги. Для этого нужно поместить название листа и восклицательный знак (!) перед ссылкой на ячейку(диапазон) - Лист1!А4 или Лист1!А4:А10.

Иногда при построении формул требуется получить данные другой рабочей книги. Такая ссылка должна включать имя рабочей книги, номер рабочего листа и адрес ячейки (диапазона) - [Ведомость.xls]Лист1!С2:С9

При построении формул допускается использование следующих арифметических операций:

+	Сложение
-	Вычитание
*	Умножение
/	Деление
%	Процент
^	Возведение в степень

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

§ Возведение в степень

§ Умножение, деление

§ Сложение, вычитание

Исправление ошибок в формулах

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

# ДЕЛ / 0!	В формуле присутствует операция деления на 0
#Н/Д	Формула ссылается на неопределенные данные
#ИМЯ ?	В формуле используется неопределенное имя
#ПУСТО!	Формула содержит ссылку на пустое пересечение ячеек
#ЧИСЛО !	Формула неверно использует число
#ССЫЛКА !	В формуле используется недопустимая ссылка на ячейку
#ЗНАЧ !	Формула содержит недопустимый аргумент или операцию

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

Чтобы создать примечание к ячейке необходимо:

Выделить ячейку, которой нужно назначить примечание и выбрать команду РЕЦЕНЗИРОВАНИЕ/СОЗДАТЬ ПРИМЕЧАНИЕ (или использовать контекстное меню ячейки);

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

Все ячейки, содержащие примечания, отмечены красной точкой в правом верхнем углу. Чтобы просмотреть примечание к ячейке, необходимо поместить на нее указатель мыши. Для удаления примечания к ячейке необходимо выделить ячейку и выполнить команду РЕЦЕНЗИРОВАНИЕ/УДАЛИТЬ.

Использование функций

MSExcel содержит более 200 встроенных функций, которые называются функциями рабочего листа: математические, статистические, финансовые, работы с базами данных, информационные, логические, текстовые, тригонометрические и т.д.

Формула, использующая функцию, состоит из знака равенства, имени функции и одного или нескольких аргументов, разделенных символом < ; >

В качестве аргументов могут использоваться:

§ Ссылки на ячейки текущего рабочего листа, другого рабочего листа, другой рабочей книги: =СУММ(А1;А10), =МАКС(Лист1!А1;Лист1!А10),

= МИН([Ведомость.xls]Лист1!С2;([Ведомость.xls]Лист1!С9);

§ Ссылки на диапазоны ячеек:

=СУММ(А1:А10), =МАКС(Лист1!А1:А10),

= МИН([Ведомость.xls]Лист1!С2:С9);

§ Константы (числовые, текстовые, дата, время);

§ Формулы: =МАКС(А1; А10*45%),;

§ Другие функции: =МИН(А4;МАКС(В1:В10))

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

§ Ввести знак равенства;

§ Затем имя функции и открывающую скобку;

§ Указать в качестве аргумента конкретную ячейку или диапазон (выделив мышью или с клавиатуры);

§ Ввести закрывающую скобку и нажать клавишу ENTER для завершения ввода и выполнения вычисления.



excel ссылка формула график

Другой, более удобный способ ввода функции в активную ячейку, - применение Мастера функций, который начинает свою работу после выбора команды ФОРМУЛА/ВСТАВИТЬ ФУНКЦИЮили нажатия кнопки , расположенной в строке формул.

Работа Мастера функций состоит из двух шагов:

§ Первый шаг позволяет выбрать категорию функции в раскрывающемся списке Категория: и в списке Выберите функцию: - имя функции, с помощью которой будет вычислен результат;

§ Если Вы не уверены, какую функцию следует выбрать, или какие действия выполняются выделенной функцией, можно нажать кнопку Справка по этой функции;

§ После выбора нужной функции необходимо нажать клавишу ОК, для перехода ко второму шагу Мастера функций;

§ В диалоговом окне Аргументы функции вводят аргументы, которые используются для вычисления результата;

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

§ Нажатие кнопки ОК вводит формулу в активную ячейку.



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

Чтобы отредактировать формулу, содержащую функцию, с помощью Мастера функций нужно:

§ выделить ячейку, содержащую функцию и выбрать команду ФОРМУЛА/ВСТАВИТЬ ФУНКЦИЮили нажать кнопку , расположенную в строке формул;

§ на экране появляется диалоговое окно Аргументы функции (второй шаг работы Мастера функций) - в поле ввода можно ввести необходимые изменения и нажать клавишу ОК.

Чтобы отредактировать функцию вручную необходимо:

§ выделить ячейку, содержащую функцию;

§ выполнить двойной щелчок мышью на ячейке или щелкнуть по строке формул;

§ выделить аргумент, который нужно изменить и ввести новое значение;

§ нажать клавишу ENTER или - в строке формул.

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

Еще одним способом представления табличных данных в MSExcel являются диаграммы, которые отображают данные, введенные в рабочую таблицу, в графической форме.

Построение диаграммы осуществляет Мастер диаграмм, который начинает свою работу при нажатии кнопки , расположенной на стандартной панели инструментов или при выборе команды ВСТАВКА - ДИАГРАММА.

Элементы диаграммы

Элементы двумерной диаграммы

Любая диаграмма состоит из нескольких стандартных элементов. Двумерная диаграмма (см. Рис. 1)состоит из следующих элементов:

а. Ось Y, или ось значений, по которой откладываются точки данных;

б. Ось Х, или ось категорий, на которой указываются категории, к которым относятся точки данных;

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

г. Имя категории, которое указывает, какие данные наносятся на ось Х;

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

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

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

з. Линии сетки, которые могут быть нанесены для облегчения восприятия данных диаграммы;

и. Метки значений, которые добавляют для того, чтобы показать числовые значения рядов данных;

к. Рамка выделения указывает, что можно отредактировать диаграмму.

Элементы объемной диаграммы

Объемная диаграмма располагает следующими, дополнительными элементами (см. Рис.2):

а. Ось Z, или ось значений, по которой откладываются точки данных;

б. Ось Х, или ось категорий, которая ничем не отличается от оси Х двумерной диаграммы;

в. Ось Y, или ось рядов, на которой указываются отдельные ряды данных. Эта ось создает объемное представление диаграммы;

г. Стена, которая рассматривается как фон для диаграммы;

д. Углы, с помощью которых можно изменить расположение диаграммы;

е. Основание - это прямоугольная область, на которой построена объемная диаграмма.



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

Табличный редактор предоставляет возможность выбора одного из 14 типов диаграмм. Кроме того, для каждого типа диаграммы существует еще несколько видов. Выбор того или иного типа диаграммы зависит от поставленной задачи (показать соотношение … или отобразить зависимость … и т.п.) и от того, какие данные необходимо представить на диаграмме.

Рассмотрим некоторые, наиболее применимые типы диаграмм:

Гистограммы

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

Графики

Этот вид диаграмм показывает изменение данных за равные промежутки времени или зависимость одной величины от другой. Каждый ряд данных на графике представлен линией. На графике Рис.3 представлено два ряда данных - остаток на начало месяца и остаток на конец месяца. На графике Рис.4 показана зависимость остатка на конец месяца от стоимости реализованного товара.

Круговые диаграммы

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



Точечные диаграммы

Точечные диаграммы позволяют проследить зависимость между парами чисел. Одно число из этой пары наносится на ось Х, другое - на ось У. Например, в таблице дана числовая последовательность от -5 до 5. Для каждого числа вычислено значение функции =степень(число;4). Точечная диаграмма для такой таблицы выглядит следующим образом:



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

Команда ВСТАВКА - ДИАГРАММА активизирует работу Мастера диаграмм:

Шаг1из 4 : Тип диаграмм. Можно выбрать нужный тип и вид диаграммы. Кнопка [ДАЛЕЕ] позволяет выполнить переход к следующему шагу;

Шаг 2 из 4: Источник данных диаграммы. На данном шаге (см. Рис.7) следует ввести числовые значения рядов данных, по которым будет построени диаграмма и подписи по оси категорий.

Вкладка РЯД позволяет создать требуемое количество рядов (переменные, по числовым значением которых строится диаграмма), присвоить каждому ряду имя , последовательность числовых значений (путем выбора диапазона ячеек из таблицы), а так же задать диапазон ячеек, данные которых будут использоваться в качестве подписей по оси категорий.

Шаг 3 из 4: Параметры диаграммы.

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

Шаг 4 из 4: размещение диаграммы. Можно задать, где именно будет располагаться диаграмма: или на активном рабочем листе или на отдельном листе, которому будет присвоено имя Диаграмма1.

Если при построении диаграммы возникает необходимость перейти к предыдущему шагу работы Мастера диаграмм, следует нажать кнопку [НАЗАД].

После вода всех необходимых параметров, в диалоговом окне.

Размещение диаграммы следует нажать кнопку [ГОТОВО] - создание диаграммы завершено и диаграмма помещается в активной рабочей книге, или кнопку [ОТМЕНА] - создание диаграммы отменяется.

Модификация диаграмм

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

Далее, вывести на экран контекстное меню выделенного объекта (щелчок правой клавишей мыши) и выбрать одну из открывшегося списка (см. Рис. 8) команду. Используя команды контекстного меню можно изменить вид области диаграммы, получить доступ к любому шагу Мастера диаграмм и ввести изменения.

Кроме того, на редактируемой диаграмме можно выделить и внести изменения в ее отдельные элементы.

Размещено на Allbest.ru