Сложные формулы и стандартные функции.

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

1. Если начать ввод формулы щелчком на кнопке Изменить формулу (=) или нажатием клавиши «=», то поле Имя в строке формул заменяется раскрывающимся списком стандартных функций. Этот список содержит десять функций, использовавшихся последними, а также пункт Другие функции, с помощью которого можно открыть диалоговое окно Мастер функций.

 

Категории функций:

10 недавно использовавшихся.

Полный алфавитный перечень

Финансовые

Дата и время

Математические

Статистические

Ссылки и массивы

Работа с базой данных

Текстовые

Логические

Проверка свойств и значений

 

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

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

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

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

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

Вставить функцию также можно воспользовавшись командой Вставка > Функция… или с помощью кнопки Вставка функции (fx) на панели инструментов стандартная. В результате будет открыто диалоговое окно Мастер функций.

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

К математическим функциям относятся такие известные из курса школьной математики функции, как SIN() — синус, COS() — косинус, TAN() — тангенс, LN() — натуральный ло­гарифм, KOPEHЬ()(SQRT) — квадратный корень числа и т.д. В круглых скобках (сразу за именем функции) записывается ее аргумент. При использовании тригонометрических функций следует учитывать, что аргумент должен быть задан в радианной мере. В качестве аргумента функции может выступать числовая константа, адрес клетки табличного процессора или диапазон (блок) клеток.

Наибольший интерес представляют функции, аргументом которых является не одна ячейка, а диапазон ячеек. Наиболее часто используемой в табличных вычислениях математической функцией является функция суммирования аргументов СУММ(). Аргументами этой функции являются либо диапазон клеток, либо несколько диапазонов клеток, перечисленные через запятую (в некоторых табличных процессорах в качестве разделителя аргументов используется «;»), адреса клеток, чис­ловые константы.

Одной из целей разработки табличных процессоров была автоматизация статистической обработки данных. Этим объяс­няется довольно многочисленная группа статистических функций. Наиболее часто используемыми статистическими функциями являются: СРЗНАЧ()(AVERAGE) — вычисление среднего арифметического аргументов, MИH()(MIN) и MAKC()(MAX) — вычисление минимального и максимального значений среди аргументов. Аргументы этих функций выби­раются так же, как и у функции суммирования.

Пример 1. На отрезке [0;1] вычислить значения функ­ции с шагом 0.2.

Решение. Заполним таблицу, как показано ниже:

	А	В
	Шаг табуляции	0.2
	аргумент X	функция F(X)
		АЗ^3+КОРЕНЬ(АЗ)*0.5
	АЗ+$В$1

Скопируем формулу из клетки А4 в клетки А5:А8, а фор­мулу из клетки ВЗ в клетки В4:В8. При копировании абсо­лютный адрес $В$1, содержащий значение шага табуляции, не будет изменяться.

	А	В
	Шаг табуляции	0.2
	аргумент X	функция F(X)
		АЗ^3+КОРЕНЬ(АЗ)*0.5
	АЗ+$В$1	А4^3+КОРЕНЬ(А4)*0.5
	А4+$В$1	А5^3+КОРЕНЬ(А5)*0.5
	А5+$В$1	А6^3+КОРЕНЬ(А6)*0.5
	А6+$В$1	А7^3+КОРЕНЬ(А7}*0.5
	А7+$В$1	А8^3+КОРЕНЬ(А8)*0.5

В режиме отражения значений таблица будет выглядеть так:

	А	В
	Шаг табуляции	0.2
	аргумент X	функция F(X)
	0.2	0.2316
	0.4	0.3802
	0.6	0.6032
	0.8	0.9592
		1.5

Пример 2. В таблицу собраны данные о крупнейших озерах мира:

	А	В	С	D
	Название озера	Площадь (тыс. кв.м.)	Глубина (м)	Высота над уровнем моря
	Байкал	31.5
	Таньганьика
	Виктория
	Гурон	59.6
	Аральское море	51.1
	Мичиган

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

Решение: Для решения задачи воспользуемся статистичес­ким функциями МИН(), МАКС() и CP3HAЧ(). В клетку с ад­ресом В8 поместим формулу: МИН(С2:С7) — поиск минималь­ного значения по диапазону клеток С2:С7, содержащему значения глубин каждого озера. В клетку с адресом В9 помес­тим формулу: МАКС(В2:В7) — поиск максимального значения по диапазону клеток В2:В7. В клетку с адресом В10 поместим формулу: CP3HAЧ(D2:D7), с помощью которой вычисляется средняя высота озер над уровнем моря. В клетки А8, А9 и А10 поместим соответствующие пояснения. В результате по­лучим таблицу:

	А	В	С	О
	Название озера	Площадь (тыс. кв.м.)	Глубина (м)	Высота над уровнем моря (м)
	Байкал	31.5
	Таньганьика
	Виктория
	Гурон	59.6
	Аральское море	51.1
	Мичиган
	Миним. Глубина
	Максим. Площадь
	Средн. Высота	461.6667

Среднюю высоту озер над уровнем моря можно найти и с помощью функции суммирования: просуммировать все значе­ния из диапазона клеток D2:D8 и разделить на количество значений. Таким образом, в клетку В10 можно занести фор­мулу: СУММ(В2:В8)/6.

Лекция 3

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

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

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

Большинство диаграмм строятся в прямоугольной системе координат. По горизонтальной оси X откладываются значения независимой переменной (аргумента), а по вертикальной оси Y — значения зависимой переменной (функции). На один рисунок может быть выведено одновременно несколько диаграмм.

Диаграмма создается с помощью Мастера диаграмм, вызываемого командой Вставка — Диаграмма… или соответствующей кнопкой на стандартной панели инструментов.

Замечание. Все последующие выкладки даются на примере построения гистограммы показывающей количество проданного товара типа A, B, C за четыре месяца.

Обычно перед вызовом Мастера диаграммвыделяется интервал ячеек — область данных для построения диаграммы. Мастер диаграммосуществляет построение новой диаграммы в интерактивном режиме за несколько шагов: 1. Указание блока ячеек с исходными данными для построения диаграммы. Блок ячеек может включать как сами данные, так и дополнительную информацию, которая будет использоваться в качестве названий исходных данных (легенд), указаний меток по оси X. Блок ячеек может содержать несмежные ячейки одного рабочего листа (выделяются при нажатии клавиши Ctrl).

 

2. (Шаг 1: тип диаграммы). Выбор типа диаграмм. Excel позволяет построить диаграм­мы 14 стандартных типов.

 

3. Выбор вида (формата) диаграммы указанного типа (можно про­смотреть результаты выбора, нажав клавишу Просмотр результатов ).

4. (Шаг 2: источник данных диаграммы). Задание параметров диаграммы: расположение данных; способ использования первой строки или столбца (метки осей, текст легенды, название диаграммы).

Если блок ячеек для построения диаграммы содержит несколько строк или столбцов, можно различным образом определить понятие ряда. Ряд может соответствовать данным одного столбца или одной строки. Если интервал включает не только числовые данные, следует указать, сколько строк (ряды в строках) или столбцов (ряды в столб­цах) отводятся для меток оси X и соответственно, сколько столбцов (ряды в строках) или строк (ряды в столбцах) используются при фор­мировании легенды.

 

 

5. (Шаг 3: параметры диаграммы). Добавление легенды, ввод названия диаграммы и подписей к осям диаграммы.

Диаграмма может и не содержать легенд, если они не вошли в ин­тервал выделения, но легенду можно добавить при редактировании ря­дов диаграммы.

 

 

6. (Шаг 4: размещение диаграммы). Выбор места размещения диаграммы. Создаваемую диаграмму можно располагать либо на текущем листе, либо на другом существующим листе, либо на новом листе. При выборе опции На этом листе создается внедренная диаграмма. При выборе опции На новом листе автоматически добавится новый лист, на котором будет размещена создаваемая диаграмма.

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

 

 

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

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

Использование Мастера диаграмм. Данный вид корректировки диаграмм обеспечивает изменение:

· исходного интервала ячеек, на основании которого построена диа­грамма;

· ориентации рядов;

· числа строк и столбцов, отводимых для меток оси X и названия ле­генды в диаграмме.

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

Шаг 1. Корректировка интервала ячеек для диаграммы.

Шаг 2. Корректировка ориентации в рядах, определение меток оси X и легенд.

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

· команда «Тип диаграммы» используется для изменения типа диаграммы для отдельной последовательности данных, группы или всей диаграммы в целом;

· команда «Исходные данные» используется для добавления или изменения выделенного ряда данных или отдельного элемента диаграммы;

· команда «Параметры диаграммы» используется для изменения стандартных параметров выбранного типа диаграмм. Изменения могут затрагивать такие элементы, как сетка, оси, подписи данных и заголовок диаграммы;

· команда «Размещение» позволяет выбрать расположение выделенной диаграммы на листе;

Эти же команды можно вызвать из контекстного меню.

· команда «Добавить данные» позволяет сделать добавление выделенного ряда данных или точек на диаграмму;

· команда «Линия тренда» позволяет добавить или изменить линию тренда на диаграммах различных типов. Линии тренда можно проводить на гистограммах, графиках, линейчатых и XY-точечных диаграммах;

Отступление

Самым простым техническим инструментом изучения динамики цен является линия тренда. Линия тренда? это линия проведённая через минимальные точки спадов динамики (восходящая линия тренда) или через максимальные точки пиков динамики (нисходящая линия тренда). На основе этого понятия строятся многие инструменты графического технического анализа.

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

· команда «Объемный вид» изменяет пространственную ориентацию диаграммы.. Применяется для объемных вариантов диаграмм.

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

· выберите изменяемую диаграмму;

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

· убедитесь, что выделена полностью вся ссылка в поле Диапазон данных;

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

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