Конспект_лекц_Кишкурно

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

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

Ссылки могут быть также смешанными. Если нужно зафиксировать столбец, то знак $ ставится перед буквой столбца, например, $А7. Если необходимо зафиксировать строку, то знак $ ставится перед номером строки, например, А$7.

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

Имя рабочего листа!Имя ячейки Лист1!A1

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

[Имя книги]Имя рабочего листа!Имя ячейки [Книга2]Лист2!D5

Объемные ссылки – это ссылки на ячейки диапазона листов в книге. Например, формула =СУММ(Лист1:Лист6!В1:В20) вычисляет сумму всех значений из диапазонов В1:В20, расположенных на шести листах.

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

Изменение типа ссылок. Чтобы изменить тип ссылки с относительной на абсолютную или на смешенную, нужно нажать клавишу «F4». Причем каждое очередное нажатие изменяет тип ссылки.

41

5.6. Сообщения об ошибках

Если формула в ячейке не может быть правильно вычислена, Excel выводит сообщение об ошибке (табл. 5.1).

Таблица 5.1

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

5.7. Работа с функциями

Для выполнения вычислений в Excel можно использовать и готовые встроенные функции: математические, логические, финансовые, текстовые, даты и времени и др. Функция, по аналогии с формулой, может быть введена в любую ячейку рабочего листа. В ней всегда присутствуют имя и операнд (диапазон ячеек, которые используются для вычисления функции). Например, =СУММ(А4:А15) – суммирует значения диапазона ячеек А4:А15.

Одна из самых простых и часто используемых в Excel функций –

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

Для того чтобы вставить встроенную функцию, надо активизиро-

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

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

42

	Таблица 5.2
	Назначение некоторых функций
Функция	Назначение
ПРОСМОТР(…)	Ищет значение в одном столбце, строке или массиве

СУММЕСЛИ(диапаз, крит, Суммирует ячейки, заданные указанным условием диап_сумм)

СЧЕТЕСЛИ(диапаз, крит) Подсчитывает количество непустых ячеек, удовлетворяющих заданному диапазону

СЧЕТЗ(знач1,знач2,..)	Подсчитывает количество значений в списке аргу-
	ментов и непустых ячейках
	5.8. Логические функции
Логические функции предназначены для проверки выполнения
условия или для проверки нескольких условий. К ним относятся
функции: ЕСЛИ, И, НЕ, ИЛИ.
Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает
одно значение, если условие выполняется, и другое, если нет:
ЕСЛИ (лог_выраж.; значение_если_истина; значение_если_ложь)
Для записи логического выражения используются операторы:
=, >, <, >=, <=, <>.
Пример: A1=5; B1=10; =ЕСЛИ(A1+B1>10;0;A1-B1) – результат 0.
Функция НЕ меняет на противоположное логическое значение
своего аргумента и имеет следующий вид:

НЕ(логическое_значение)
Пример: A1=5; A2=3;	=НЕ(A1<A2) – истина.

Функция ИЛИ возвращает ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА и имеет следующий вид:

ИЛИ(логическое_значение1; логическое_значение2; ...)

Пример: A1=5; B1=1; A2=3; B3=7; =ИЛИ(A1>A2;B1>B3) – истина.

Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА и имеет следующий вид:

И (логическое_значение1; логическое_значение2; ...)

Пример: A1=5; B1=1; A2=3; B3=7; =И(A1>A2;B1<B3) – истина.

Пример: Решить квадратное уравнение: ax2 + bx + c = 0.

В ячейки рабочего листа вносятся значения в соответствии с рис. 5.1.

43

				A			B			C
	1			Решение уравнения ax^2+bx+c=0
	2			a			b			c
			1			-5			6
	3
				Дискриминант			D=		1
	4
				X1=		3
	5
				X2=		2
	6

Рис. 5.1. Оформление рабочего листа для решения квадратного уравнения

Для вычисления дискриминанта и корней уравнения в ячейки С4, B5 и B6 вводятся формулы, приведенные в табл. 3.

			Таблица 5.3
Формулы для вычисления дискриминанта корней в Excel
Ячейка			Значение
C4		=B3^2-4*A3*C3
B5		=ЕСЛИ(C4>=0;(-B3+КОРЕНЬ(С4))/(2*A3);"D<0!")
B6		=ЕСЛИ(C4>=0;(-B3-КОРЕНЬ(С4))/(2*A3);"D<0!")
Пример. Вычислить функцию f(x), x [–2; 4,5] с шагом 0,5; n –
номер по журналу.
	ln( x), при x		1 .
f (x)			.
	cos(x), при x		1

Для решения задачи в ячейки рабочего листа вводятся данные, представленные на рис. 5.2, и затем расчетные формулы копируются в остальные ячейки. Результат представлен на рис. 5.3.

Рис. 5.2. Оформление рабочего листа для вычисления функции f(x)

Рис. 5.3. Результат решения задачи

44

5.9. Отображение данных в виде диаграмм

Одним из достоинств табличного процессора Excel является возможность представления данных в графическом виде.

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

Для редактирования уже построенной диаграммы надо ее активизировать (щелчком мышью по диаграмме) и воспользоваться появившимися вкладками Конструктор, Макет и Формат. Можно задавать различные надписи на диаграмме, использовать различные шрифты и окраску. Объемная диаграмма может подвергаться поворотам, менять перспективу, высоту от основания. Удобно для редактирования использовать и контекстное меню.

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

Линия тренда – графическое представление тренда или направления изменения данных в ряде данных. Не на каждой диаграмме можно построить линию тренда. Для ее построения используются следующие типы диаграмм: ненормированные плоские диаграммы с областями; линейчатые диаграммы; гистограммы; графики; биржевые, точечные и пузырьковые диаграммы.

Выбор линии тренда зависит от данных. Линии тренда бывают:

линейные, логарифмические, полиномиальные, степенные, экспоненциальные.

Линейная (арифметическая) аппроксимация – это прямая ли-

ния, наилучшим образом описывающая набор данных: y m x b,

где m – угол наклона; b – координата пересечения оси абсцисс.

Логарифмическая аппроксимация хорошо описывает величи-

ну, которая вначале быстро растет или убывает, а затем постепенно

45

стабилизируется. Описывает как положительные, так и отрицательные величины:

y c ln x b,

где c и b – константы; ln – функция натурального логарифма.

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

y b c1 x c2 x2 ... c6 x6 ,

где b и c1…c6 – константы.

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

y c xb ,

где c и b – константы.

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

y c ebx ,

где c и b – константы; e – основание натурального логарифма. Добавить линию тренда можно с помощью контекстного меню.

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

Для редактирования линии тренда необходимо выделить ли-

нию тренда, щелкнуть ПКМ по ней и выбрать пункт Формат линии тренда. В открывшемся окне установить необходимые параметры или осуществить ее форматирование.

5.10.Защита ячеек, рабочих листов, книг от доступа

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

46

димо на вкладке Рецензирование в группе Изменения выбрать ко-

манду Защитить лист или Защитить книгу.

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

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

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

5.11. Вывод таблицы на экран и принтер

Для немедленного запуска печати текущего рабочего листа с использованием стандартных установок можно воспользоваться коман-

дой Microsoft Office / Печать/Быстрая печать.

Но лучше как в Excel, так и в Word перед тем, как отправить таблицу на печать, просмотреть документ на экране и в случае необходимости внести в него коррективы. Для этого используется команда:

Microsoft Office / Печать/Предварительный просмотр.

В режиме Предварительного просмотра щелчок по кнопке

Параметры страницы вызывает одноименное окно, в котором при необходимости можно изменить текущие параметры. Данное окно имеет четыре вкладки: Страницы – для установки размера страницы, качества печати, а также для изменения размера печатаемого листа; Поля – для установки полей для страниц, а также для указания расстояния от края листа до колонтитулов; Колонтитулы – для выбора содержания колонтитулов; Лист – для установки области печати, заголовков страниц и порядка их печати.

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

47

ЛЕКЦИЯ 6

ВЫЧИСЛЕНИЯ В ПАКЕТЕ MATHCAD

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

ческие программы типа Mathematica, MatLab, Maple, MathCAD, Gauss, Reduce, Eureka и др. Самый популярный пакет из указанного списка – математический пакет MathCAD.

Данный пакет был разработан фирмой Math Soft Inс (USA).

Всостав MathCAD входят несколько интегрированных между собой компонентов:

1)мощный текстовый редактор, позволяющий вводить, редактировать и форматировать как текст, так и математические выражения;

2)вычислительный процессор, умеющий проводить расчеты по введенным формулам, используя встроенные численные методы;

3)символьный процессор, являющийся, фактически, системой искусственного интеллекта;

4)графический процессор, позволяющий построить различные типы графиков в различных системах координат;

5)огромное хранилище справочной информации, как математической, так и инженерной, оформленной в виде библиотеки интерактивных электронных книг.

6.1.Окно MathCAD

ВMathCAD интерфейс пользователя интуитивен и сходен с другими приложениями Windows. Основные элементы – menu bar (верхнее меню, или строка меню); Worksheet (рабочая область); toolbars (панели инструментов); Standard (Стандартная)

иFormatting (Форматирование); панель инструментов Math (Математика).

На панели инструментов Math (Математика) (рис. 6.1) расположены дополнительные математические палитры символов:

48

–общие арифметические операторы;

–различные двух- и трехмерные графики;

–матричные и векторные операции;

–знак равенства, знаки отношения;

–производные, интегралы, пределы, ряды и произведения;

–булевы операторы для вставки логических операторов;

–программные структуры;

–греческие буквы;

–символьные вычисления.

Рис. 6.1. Панель Математика

6.2. Работа с документами

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

Вокруг каждой области MathCAD создает невидимый прямоугольник, который может быть отображен на экране по команде меню View / Regions (Вид / Области). Система MathCAD имеет два режима работы: автоматический – вычисления производятся по мере формирования документа, когда вводится символ «=»; ручной – вычисления производятся только после подачи соответствующей команды (нажатие клавиши «F9»). Автоматический режим вычислений устанавливается по умолчанию для каждого нового документа.

Выключение или включение режима автоматических вычислений выполняется командой меню Tools / Calculate / Automatic Calculation

(Инструменты / Вычислить / Автоматические вычисления).

Вручном режиме работы можно сначала сформировать документ,

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

Открытие, сохранение и печать документа производится с помощью меню File (Файл).

Ввод информации осуществляется в месте расположения курсора.

Программа MathCAD использует 3 вида курсора. Если ни один объект не выбран, используется крестообразный курсор, определяющий место

49

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

6.3. Построение и редактирование математических выражений

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

Чтобы определить любую переменную или функцию, необхо-

димо после ее имени напечатать символ определения («:=»), а затем значение переменной или выражение для определенной функции. При этом дробная часть числа отделяется от целой символом «.» (точка). Пример:

x 3

y x 3.45 f (x y) x2

y2

Если в имени переменной используется подстрочный индекс, то надо напечатать часть имени без индекса, а затем ввести символ «.» (точка) и напечатать подстрочный индекс. Пример:

f (x y) y x2

x y2 fx(x y) 2 y x y2 fy(x y) x2

2 x y

Замечание. MathCAD читает рабочий документ сверху вниз и слева направо.

Определив переменную, например x ее можно использовать в вычислениях везде ниже и правее равенства, в котором она определена.

Для вычисления результатов используется символ равно («=»). После этого на экране появится результат вычислений, если уста-

новлен режим вычислений в автоматическом режиме. В противном случае необходимо нажать клавишу «F9». Пример:

50