Изменение проекции объемной диаграммы.
Формулы и функции Excel
Цель: знакомство с особенностями математических функций рабочего листа MicrosoftExcel и приобретение практических навыков использования этих функций;
Знакомство с различными типами диаграмм и графиков, приобретение навыков их построения, овладение основными приемами редактирования и оформления диаграмм.
Встроенные функции MSExcel
Встроенные функции MSExcel– это функции рабочего листа, вычисление которых выполняется по определенным алгоритмам, содержащимся в приложении MSExcel. Вызов встроенной функции происходит при вычислении по формуле, содержащей эту функцию.
Запись функции в формуле MSExcel аналогичная записи функции в математике. Она имеет вид f(x1; x2; … ;xn),
где f – имя функции, x1; x2; … ;xn – аргументы.
В общем случае аргументами функций могут быть данные любого вида, но для конкретной функции возможные аргументы определяются ее синтаксисом. Аргументы отделяются друг от друга точкой с запятой. Существуют встроенные функции, не содержащие аргументов, например, число π вычисляется с помощью встроенной функции ПИ().
Встроенные функции разбиты на категории. Каждая категория предназначена для определенных целей, например, имеются математические, логические, статистические функции и т.д. В табл.1 перечислены математические функции, причем только те, которые соответствуют элементарным функциям в математике.
При наборе функций с клавиатуры безразлично, набираются строчные или прописные буквы, но нужно соблюдать соответствие языка имени функции. Ссылки на ячейки записываются только латинскими буквами. При указании типа аргумента не рассматриваются ограничения на область определения функций, но, разумеется, их нужно соблюдать.
|
|
|
Таблица 1
Встроенные математические функции
| Математи-ческая функция | Встроенная функция Excel | Пояснение |
| ABS(x) | Абсолютная величина х (модуль х) |
| arccos x | ACOS(x) | Значение функции выражено в радианах |
| arcsin x | ASIN(x) | Значение функции выражено в радианах |
| arctg x | ATAN(x) | Значение функции выражено в радианах |
| cos x | COS (x) | Косинус величины x, выраженный в радианах |
| ex | EXP(x) | Экспонента от х |
| ln x | LN(x) | Натуральный логарифм х |
| loga x | LOG(x;a) | Логарифм х по основанию а |
| lg x | LOG10(x) | Десятичный логарифм х |
| sin x | SIN (x) | Синус величины х, выраженный в радианах |
| tg x | TAN (x) | Тангенс величины х, выраженный в радианах |
| КОРЕНЬ(x) | Квадратный карень |
| СТЕПЕНЬ(x;a) | х в степени а |
| π | ПИ() | Число π |
Арифметические и некоторые другие действия в формулах записываются с помощью специальных символов, называемых знаками операций, список которых приведен в табл. 2 .
|
|
|
Таблица 2
Операции
| Знак операции | Операция | Пример записи |
| Арифметические операции | ||
| + | сложение | =A1+2 |
| - | вычитание | =4-C4 |
| * | Умножение | =A3*C6 |
| / | деление | =B3/5 |
| % | процент | =10% (равно 0,01) |
| ^ | возведение в степень | =2^3 (равно 8) |
| Операции сравнения | ||
| = | равно | A5=0 |
| < | меньше | A5<1 |
| > | больше | B3>100 |
| <= | меньше или равно | 3<=2*A10 |
| >= | больше или равно | A10>=0 |
| <> | Не равно | A10<>5 |
| Оператор связывания ячеек | ||
| : | диапазон | =СУММ(A1:C10) |
Операции выполняются над некоторыми данными (операндами). Операндом может быть число, ссылка на ячейку, ссылка на диапазон ячеек, функция, выражение взятое в скобки.
Порядок вычисления значения по формуле в MSExcel^
· вычисляются значения функций, входящих в формулу;
· вычисляются выражения в скобках;
· выполняются операции слева направо с учетом приоритета арифметических операций.
Если формула не может быть вычислена, в ячейке появляется сообщение об ошибке, которое начинается символом #:
#ДЕЛ/0! – деление на нуль
#ЧИСЛО! – недопустимый аргумент числовой функции
#ЗНАЧ! – недопустимое значение аргумента или оперенда
#ИМЯ? – неверное имя ссылки или функции
#Н/Д! – неопределенные данные
|
|
|
#ССЫЛКА! – ссылка на несуществующие ячейки.
Табулирование функции
Вычисление значения функции для каждого соответствующего аргумента, заданного с определенным шагом, в четко установленных границах называется табулированием. Так составляются таблицы значений функций, отсюда и название – табулирование.
Процедура табулирования является инструментом для решения целого ряда задач. С её помощью можно при численном решении нелинейных уравнений f(x) = 0, путем табулирования можно отделить (локализовать) корни уравнения, т.е. найти такие отрезки, на концах которых, функция имеет разные знаки. С помощью табулирования можно найти минимум или максимум функции.
Иногда случается так, что функция не имеет аналитического представления, а ее значения получаются в результате вычислений, что часто бывает при компьютерном моделировании различных процессов. Если такая функция будет использоваться в последующих расчетах (например, она должна быть проинтегрирована или продифференцирована и т.п.), то часто поступают следующим образом: вычисляют значения функции в нужном интервале изменения аргумента, т.е. составляют таблицу (табулируют), а затем по этой таблице строят каким-либо образом другую функцию, заданную аналитическим выражением (формулой).И т.д. и т.п.
|
|
|
Постановка задач
Задание 1
Построить график по данным таблицы значений функции
для аргумента х, изменяющегося от 0 до 2 с шагом 0,1. Построить график функции
Решение разбивается на два основных этапа: построение таблицы значений функций и построение графика функции.
Построение таблицы:
· введите заголовки столбцов xи y в ячейках A1, B1.
· введите в ячейку А2 первое значение Х, равное 0
· введите в ячейку А3 формулу =А2+0,1, которую затем скопируйте на диапазон А4:А22
· в ячейку В2 введите формулу =SIN(4*A2)^2/(A2+1), которую затем скопируйте на диапазон B4:B22
Построение графика функции:
· Для построения графика выделите диапазон данных (ячейка А1:В22).
· На вкладке ленты Вставка в разделе Диаграммы выберите вид диаграммы Точечная с гладкими кривыми и маркерами (рис. 1)
Рисунок 1. Табулирование функции
Задание 2
Построить график кусочной функции для аргумента х, изменяющегосяна участке [-3;5] с шагом 0,2. Значение параметра а=1,4
Решение
Построение таблицы. Решение выполним на новом рабочем листе Excel.
Введите заголовки столбцов x и y в ячейках A1, B1. В ячейку С1введите а=. В ячейку D1введите значение1,4.
В ячейку А2 введите начальное значение х равное -3. Выполните команду Прогрессия (вкладка Главная, группа Редактирование, кнопкаЗаполнить). Задайте в диалоге опцию Расположение по столбцам, Арифметическая прогрессия, Шаг 0,2, Предельное значение 5. Заполняются ячейки А3:А42.
Рисунок 2. Задание параметров прогрессии
Ввод формулы в ячейку В2желательно осуществить с помощью Мастера функций.введите формулу.
Чтобы начать формулу с функции, нажмите в строке формул кнопку Вставить функцию 
.Знак равенства (=) будет вставлен автоматически.На первом шаге мастера из категории Логические выберите функциюЕсли.Функция ЕСЛИ являются неотъемлемой частью многих моделей электронных таблиц, поскольку позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат. У функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй – если сравнение ложно.
Синтаксис функции ЕСЛИ:
ЕСЛИ(лог_выражение; [значение_если_истина];[значение_если_ложь])
лог_выражение– это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
значение_если_истина,значение_если_ложь– как видно изих названия, это точто необходимо сделать в зависимости от того, что вернул лог выражения: ИСТИНА иЛОЖЬ
На втором шаге мастера в поле Лог_выражениезаполните поля аргументов как показано на рис.3
Рисунок 3
Поскольку все значения аргумента Xнаходятся в диапазоне А2:А42 следует начать проверку условия с его первой ячейки (А2). В нашем примере логическое выражение А2<-1, т.е. проверяется меньше или нет содержимое ячейки A2 чем -1.
Щелкните по полю Значение_если_истина.
Если меньше (ИСТИНА), то в ячейку B2 будет помещено значение функции 
. Для ввода в поле Значение_если_истинавложенной функцииКОРЕНЬ, вызовите ее из списка функций в поле Имя, как показано на рисунке 4. Если функции КОРЕНЬ нет в списке недавно использовавшихся, выберите команду Другие функции, найдите функцию в категории Математические.
Рисунок 4
Аргументами функции КОРЕНЬбудет х2+a2. Значение х мы возьмем в ячейке А2, которое возведем в квадрат (A2^2). Значение параметра «а» постоянно и находится в ячейке D1. Для преобразования относительной ссылки на ячейку D1 в абсолютную нажмите клавишу F4. Возведите параметра а в квадрат ($D$1^2). В итоге получаем следующий расчет A2^2+$D$1^2 (рис.5)
Рисунок 5
Теперь необходимо перейти к вводу значения в поле Значение_если_ложь функции ЕСЛИ. Для этого щелкните в строке формул по функции ЕСЛИ. После этого у вас снова появится диалоговое окно функцииЕСЛИ с заполненным полем Значение_если_истина(рис. 6).
Рисунок 6
Выше мы рассчитали функцию у, если значение аргумента x<-1. Если нет, то осталось два варианта: либо аргумент лежит в диапазоне -1≤ х< 2, либо x≥2. Снова два условия, потребуется вложеннаяЕСЛИ. Будет легче, если мы сначала проверим условиех< 2, а оставшийся вариант будетx≥2.
Перейдите в ячейку Значение_если_ложьоткройте раскрывающийся список последних использованных функции в левом углу и выберите ЕСЛИ, как показано на рисунке 7.
Рисунок 7
После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Заполните полелог_выражение.В нашем примере логическое выражение А2<2, т.е. проверяется меньше или нет содержимое ячейки A2 чем 2. (рис. 8)
Рисунок 8
Щелкните по полю Значение_если_истина.
Если меньше (ИСТИНА), то в ячейку B2 будет помещено значение функции 
. Для ввода в поле Значение_если_истина вложенной функции расчета тангенса (TAN) откройте в левом углу (поле ИМЯ) раскрывающийся список, выберите команду Другие функции, найдите функцию TANв категории Математические.
Аргументами функции TANбудет 
. Значение х мы возьмем в ячейке А2. Значение параметра «а» постоянно и находится в ячейке D1. Для преобразования относительной ссылки на ячейку D1 в абсолютную нажмите клавишу F4 ($D$1). В итоге получаем следующий расчет A2/$D$1 (рис.9).
Рисунок 9
Щелкните в строке формул по вложенной функцииЕСЛИ для ввода аргументов поля Значение_если_ложь. После этого у вас снова появится диалоговое окно функцииЕСЛИ с заполненным полем Значение_если_истина (рис. 6).
Рисунок 10
При всех остальных значениях аргумента x , больших или равных 2 в ячейку B2 будет помещено значение функции 
. Значение параметра «а» постоянно и находится в ячейке D1. Для преобразования относительной ссылки на ячейку D1 в абсолютную нажмите клавишу F4 ($D$1). Для того, чтобы прибавить к параметру азначение натурального логарифма 
воспользуйтесь вложенной функцией LN. Откройте в левом углу (поле ИМЯ) раскрывающийся список, выберите команду Другие функции, найдите функцию LN в категории Математические.
Значение х мы возьмем в ячейке А2, которую возведем в квадрат. В итоге получаем следующий расчет A2^2 (рис.11)
Рисунок 11
В диалогом окне Аргументы функции нажмите кнопку ОК, чтобы завершить ввод функции. Вот так выглядит полная формула:
=ЕСЛИ(A2<-1;КОРЕНЬ(A2^2+$D$1^2);ЕСЛИ(A2<2;TAN(A2/$D$1); $D$1+LN(A2^2)))
Для копирования функции из ячейки В2на диапазон B3:B42 необходимо в ячейке В2 навести курсор на правый нижний угол (должен появиться черный крестик) и дважды щелкнуть мышью по маркеру заполнения.
Построение графика функции:
· Для построения графика выделите диапазон данных (ячейка А1:В42).
· На вкладке ленты Вставка в разделе Диаграммы выберите вид диаграммы Точечная с гладкими кривыми и маркерами (рис. 1)
Рисунок 12Табулирование кусочной функции
· Для сохранения диаграммы как рисунка, щелкните область диаграммы. На вкладке Главнаянажмите кнопкуКопировать.
· На вкладке Главнаящелкните стрелку под командойВставитьи выберите пунктСпециальная вставка. В диалоговом окне Специальная вставкав спискеКаквыберите нужный формат графического файла, например, Рисунок .
Рисунок 13- Параметры графического файла
Постройте формы кривой функции в зависимости от разных значениях параметров константыa (рис. 14).
Рисунок 14
Изменяя параметр а
Задание 3
Построить поверхность, заданную уравнением
.
аргументы х и y изменяются от -1 до 1 с шагом 0,1.
Решение
Построение таблицы. Решение выполним на новом рабочем листе Excel.
· В ячейку B1 введите начальное значение y равное -1. Выполните команду Прогрессия (вкладка Главная, группа Редактирование, кнопкаЗаполнить). Задайте в диалоге опцию Расположение по строкам, Арифметическая прогрессия, Шаг 0,1, Предельное значение 1. Заполняются ячейки C1:V1.
· В ячейку A2 введите начальное значение y равное -1. Выполните команду Прогрессия (вкладка Главная, группа Редактирование, кнопкаЗаполнить). Задайте в диалоге опцию Расположение по столбцам,Арифметическая прогрессия, Шаг 0.1, Предельное значение 1. Заполняются ячейки А3:А22
· Выделите,начиная с ячейки B2, всю пустую часть таблицы (диапазон В2:V22).Затем в активную ячейку (B2) выделенного диапазона следует ввести формулу, для расчета Z. Формула в алгебраическом виде – Z=(x*y*(x2-y2))/КОРЕНЬ(x2+y2)
· Чтобы данную формулу можно было легко скопировать на весь диапазон ячеек, необходимо использовать смешанные ссылки.Смешанная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором - перед наименованием столбца. Для автоматического ввода знаков $ последовательно нажимайте клавишу F4
· Поскольку все значения Xсодержатся в столбце Aсмешанная ссылка на переменную Х будет$A2.Если мы потянем за Маркер заполнения эту формулу вправо, то ссылки во всех скопированных формулах будут указывать на ячейку A2, названия столбцов изменяться не будут, то есть ссылки будут вести себя как абсолютные. Если потянем вниз – ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, созданные формулы, будут использовать один и тот же столбец (А), но номера строк в них будут меняться
· В свою очередь все значения Yнаходятся в первой строке, поэтому ссылка будет B$1. Если мы потянем за Маркер заполнения эту формулу вниз, то ссылки во всех скопированных формулах будут указывать на ячейку B1, номер строки изменяться не будет, то есть ссылки будут вести себя как абсолютные. Если потянем вправо – ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, созданные формулы, будут использовать одну и ту же сроку 1, но названия столбцов у них будет меняться.
· Введите в ячейку B2 формулу
=($A2*B$1*($A2^2-B$1^2))/КОРЕНЬ($A2^2+B$1^2).
· Нажмите сочетание клавиш Ctrl и Enter. Весь диапазон заполнится значениями.
Рисунок 15Фрагмент таблицы исходных значений
Построение графика функции:
· Для построения графика выделите диапазон данных (ячейка А1:J10).
· На вкладке ленты Вставка в разделе Диаграммы выберите вид диаграммы Другие диаграммы – Поверхность (рис. 5)
Рис. 1. График поверхности
Изменение проекции объемной диаграммы.
На поверхности автоматически выделяются слои уровня. Отформатируйте объёмное изображение диаграммы путем задания форматов объемной проекции. Для ориентирования надо выделить диаграмму и выбрать командуПоворот объемной фигуры. На экране отобразится диалоговое окно Формат области диаграммы, элементы которого позволяют изменять ориентацию поверхности в пространстве
Рисунок 16
Представьте объемную диаграмму в двух протекциях.
Рисунок 17
Варианты заданий
Задание 1
Вычислить таблицу значений функции для аргумента, изменяющегося с данным шагом в заданном интервале и построить график функции
| № | Функция | Интервал | Шаг |
| 1 | Y= 
| 3,0 ≤х≤ 13,0 | 1 |
| 2 | Y= 
| 0,0≤х≤6,28 | 0,785 |
| 3 | Y= 
| 0,0≤х≤6,28 | 0,785 |
| 4 | Y= 
| 0,0≤х≤6,28 | 0,785 |
| 5 | Y= 
| 0,0≤х≤6,28 | 0,785 |
| 6 | Y= 
| 0,0≤х≤6,28 | 0,785 |
| 7 | Y= 
| 0,0≤х≤6,28 | 0,785 |
| 8 | Y=ln(cos  )
| 1,0≤х≤10,0 | 1,0 |
| 9 | Y=2 sin x+ 
| -3,14≤х≤3,14 | 0,785 |
| 10 | Y=tg 
| 0,0 ≤х≤20,0 | 2,0 |
| 11 | Y=cos x + x sin x | 0,0≤х≤6,28 | 0,785 |
| 12 | Y=x+ 
| 0,0≤х≤1,0 | 0,1 |
| 13 | Y= 
| 0,785≤х≤6,28 | 0,785 |
| 14 | Y=ln( 
| -5,0 ≤х≤5,0 | 1,0 |
| 15 | Y= 
| -5,0 ≤х≤5,0 | 1,0 |
| 16 | y=(x2+x+1)∙ln(x+1) | 0,0≤х≤5,0 | 0,25 |
| 17 | y=(x4+3x)∙sin(5x) | 0,0≤х≤5,0 | 0,25 |
| 18 | y=lg(6x+1)∙sin(4x) | 0,0≤х≤5,0 | 0,25 |
| 19 | y=lg(x+2)∙sin(x+1) | 0,0≤х≤5,0 | 0,25 |
| 20 | y=sin(2x)cos(3x) | 0,0≤х≤5,0 | 0,25 |
Задание 2
Построить график кусочной функции на участке [a;b] с шагом 0,25.Параметр а задать произвольно.
| № | Функция | [c,d] |
| 1 | 
| [-30;30] |
| 2 | 
| [-10;10] |
| 3 | 
| [-3;3] |
| 4 | 
| [-7;7] |
| 5 | 
| [-5;5] |
| 6 | 
| [-3;10] |
| 7 | 
| [-3;5] |
| 8 | 
| [-3;5] |
| 9 | 
| [-3;7] |
| 10 | 
| [-7;7] |
| 11 | 
| [-7;7] |
| 12 | 
| [-5;5] |
| 13 | 
| [-5;5] |
| 14 | 
| [-7;7] |
| 15 | 
| [-3;13] |
| 16 | 
| [-5;10] |
| 17 | 
| [-4;4] |
| 18 | 
| [-3;3] |
| 19 | 
| [-7;7] |
| 20 | 
| [-30;30] |
Задание 3
| № | Функция | x,y |
| 1 | z = x2 – 2y2 | [-1; 1] |
| 2 | z = 3x2 – 2sin2(y) y2 | [-1; 1] |
| 3 | 
| [-1; 1] |
| 4 | 
| [-1; 1] |
| 5 | z = x2cos2(x) – 2y2 | [-1; 1] |
| 6 | z = 2e0.2x x2 – 2 y4 | [-1; 1] |
| 7 | z = x2 – 2e0.2yy2 | [-1; 1] |
| 8 | Z=3x2sin2x – 5e2yy | [-1; 1] |
| 9 | 
| [-1; 1] |
| 10 | 
| [-1; 1] |
| 11 | 
| [-1; 1] |
| 12 | Z=cosx+siny | [-1; 1] |
| 13 | 
| [-1; 1] |
| 14 | 
| [-1; 1] |
| 15 | 
| [-1; 1] |
| 16 | 
| [-1; 1] |
| 17 | 
| [-1; 1] |
| 18 | 
| [-1; 1] |
| 19 | 
| [-1; 1] |
| 20 | 
| [-1; 1] |
Дата добавления: 2018-04-15; просмотров: 1067; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!