Студентам ИТ / 3 ЛП_ИТ / ИТ_обраб_мног_данных / Решение_мат_задач_Excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ

МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ТЕХНОЛОГИЙ И УПРАВЛЕНИЯ

ИМ. К.Г. РАЗУМОВСКОГО

Кафедра Информационных технологий

Николаева С.В.

РЕШЕНИЕ МАТЕМАТИЧЕСКИХ ЗАДАЧ В EXCEL

Лабораторный практикум для обучающихся всех направлений

бакалавриата

Москва - 2014

1

ББК 32.973

УДК 681.3.06

Николаева С.В.

Решение математических задач в Excel: лабораторный практикум для обучающихся всех направлений бакалавриата.

- М.: МГУТУ, 2014. – 56 с.

Лабораторный практикум предназначен для обучающихся всех направлений бакалавриата, изучающих дисциплины: «Информатика», «Информатика и математика», «Современные информационные технологии», «Информационные технологии в образовании».

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

Значительная часть пособия составлена по книге Вильяма Орвиса «EXCEL для учѐных, инженеров и студентов»: Пер. с англ. – К.: Юниор,

1999.

Лабораторный практикум рекомендован Ученым советом института Системной автоматизации и инноватики для электронного издания.

Рецензенты: Бородин А.В., д.т.н., профессор МГУПБ; Сигов А.С., д.ф.-м.н., профессор МИРЭА.

Редактор: Николаева С.В., д.т.н., доцент МГУТУ.

Николаева С.В.

Московский государственный университет технологий и управления им. К.Г. Разумовского, 2014.

109004, Москва, Земляной Вал, 73.

2

		Содержание
Точность представления чисел и численный диапазон в
Excel ..............................................................................				2
Ошибочные числа...................................................................				2
Основные сведения о ячейках и ссылках ..................................				3
Внешние ссылки на ячейки ...................................................				4
Ссылки на диапазон ...............................................................				5
Относительные и абсолютные ссылки .................................				6
Относительные ссылки на ячейки ..............................				6
Абсолютные ссылки на ячейки...................................				7
Смешанные ссылки на ячейки ....................................				7
Использование операторов в вычислениях...............................				8
Математические операторы...................................................				8
Логические операторы ...........................................................				8
Приоритет операторов ...........................................................				9
Использование функций рабочего листа .................................				10
Ввод функций .......................................................................				10
Функции массива..................................................................				10
Математические функции....................................................				11
Лабораторная работа № 1. Точность представления чисел и
численный диапазон в Excel................................................				15
Лабораторная	работа	№ 2.	Основные математические
функции	.................................................................................			21
Лабораторная работа № 3. Система из n уравнений с n не-
известными............................................................................				25
Лабораторная	работа	№ 4.	Логарифмические функции.
Тригонометрические ............................................функции				28
Лабораторная ....работа № 5. Построение графиков функций				31
Лабораторная .....................работа № 6. Логические функции				32
Лабораторная ..работа № 7. Создание трѐхмерных диаграмм				36
Лабораторная работа № 8. Пример реальной технологии
составления ...........................рецептурной смеси - фарша				42
Лабораторная работа № 9. Вычисление линейной регрессии.46

3

Точность представления чисел и численный диапазон в Excel

Точность Диапазон

15 знаков 1,798 x 10+308

Excel обеспечивает внутреннюю (т.е. не выводимую на дисплей) точность числа до 15 знаков. Для сравнения отметим, что калькулятор для научных расчѐтов отображает 10 знаков и способен с целью уменьшения погрешности округления сохранить ещѐ один-два знака, не выводя их на дисплей.

При выполнении вычислений Excel обеспечивает точность до 15 знаков, но при выводе их на экран округляет числа в соответствии

с форматом ячейки, в котором это число отображается.

Excel сохраняет числа от –1,798 10+308 до 1,798 10+308, чем обеспечивается численный диапазон 10+308. Для сравнения: карманный калькулятор способен работать с диапазоном 10+99. Хотя самое большое число, сохраняемое Excel, равно 1,798 10+308, наибольшее число, которое можно ввести, составляет 9,999 10+307.

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

Если удалось получить число с избыточным показателем, программа сохраняет ошибочное значение #ЧИСЛО!, с помощью которого помечает данную ячейку, а также все связанные с нею ячейки, как некорректные. Если же число оказывается слишком маленьким

(т.е. меньше 2,225 10-308), Excel сохраняет это значение как нулевое. Если такое число оказывается в делителе дроби, то программа возвращает ошибочное значение #ДЕЛ/0!, в результате чего любая ячейка, содержимое которого зависит от данного значения, помечается как некорректная.

Ошибочные числа

В Excel существует 7 ошибочных значений, трактуемых ею как числа:

#ДЕЛ/0! – деление на 0;

#ИМЯ? – не определено имя переменной в формуле;

#Н/Д! – нет доступных значений;

4

#ПУСТО! – итога не существует, либо используется пересечение диапазонов, которые на самом деле не пересекаются;

#ЧИСЛО! – избыточное число, либо неверное использование числа, например, КОРЕНЬ(-1);

#ССЫЛКА! – неверная ссылка; ячейки, на которую она сделана, в рабочем листе не существует;

#ЗНАЧ! – неправильный тип аргумента; например, использование текста там, где необходимо число.

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

Основные сведения о ячейках и ссылках

Ячейки в Excel могут содержать текст, числа, указание даты и времени и формулы. Программа следит за тем, что Вы вводите, и преобразует содержание ячеек надлежащим образом. Если в ячейке содержится число, Excel сохраняет его как числовое значение. Если ячейка включает как текст, так и числа, Excel сохраняет еѐ как текст; а если содержание ячейки начинается со знака равенства (=), оно сохраняется как формула. Если содержание ячейки выглядит похожим на один из форматов, применяемых в Excel для указания даты и времени, оно сохраняется программой как порядковый номер даты, а время указывается в виде доли дня.

С каждой ячейкой Excel связано две еѐ характеристики: содержание и значение. Содержание – это то, что вводится пользователем в ячейку, а значение – то, что видно на экране. Форматирование ячейки не изменяет еѐ значения, но изменяет вид, в котором оно выводится на монитор. Для текста и чисел содержание и значение ячейки одинаковы. Что же касается формул, содержание представляет собой формулу, введѐнную Вами, а значение – итог вычислений, произведѐнных с применением данной формулы.

5

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

Тип диапазона или ячейки		Пример
Строка	Число
Столбец	Буква
Относительная ссылка	B5
Абсолютная ссылка	$B$5
Смешанная ссылка	$B5	B$5
Внешняя ссылка	‘D:\STATS\CARS.XLS’!B5
Ссылка на диапазон	B5:B7

Значение какой-либо другой ячейки вводится в формулу с помощью ссылки на ячейку. Эта ссылка состоит из комбинации букв и цифр, где буква соответствует определѐнному столбцу, в котором расположена ячейка, на которую делается ссылка, а число – строке, содержащей эту ячейку. Так, например, ссылка C5 будет соответствовать значению в ячейке, которая располагается на пересечении столбца C и строки 5. Этот тип ссылки на ячейку известен как стиль A1. Ссылка на ту же самую ячейку может иметь вид пары указателя строки и столбца, например, R5C3. В данном случае ссылка сделана в

стиле ссылки R1C1.

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

Внешние ссылки на ячейки

Ссылаясь на ячейку, расположенную на другом рабочем листе (т.е. не в том рабочем листе, в который вводится формула), Вы должны включить в ссылку имя этого рабочего листа, чтобы программа Excel знала, где искать нужную ячейку. Такая ссылка известна под названием внешней. Чтобы получить доступ к содержанию одной из ячеек какого-либо рабочего листа, содержащего внешнюю ссылку, его не надо открывать. Для создания внешней ссылки введите имя рабочего листа, затем восклицательный знак, а затем - ссылку на ячейку.

Например, если необходимо сделать ссылку на ячейку H7, расположенную на рабочем листе CARS.XLS, который находится в каталоге D:\STATS, то используйте такую форму:

‘D:\STATS\CARS.XLS’!H7.

Совет

6

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

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

Ссылки на диапазон

Можно также делать ссылки и на группы ячеек, т.е. на диапазон. Диапазон ячеек представляет собой прямоугольную зону на рабочем листе. Все ячейки, входящие в этот прямоугольник, включаются в данный диапазон (без каких-либо пропусков). Ссылка на диапазон представляет собой ссылку на ячейку, расположенную в левом верхнем углу прямоугольника, и на ячейку, находящуюся в правом нижнем углу прямоугольной зоны, отделѐнные друг от друга двоеточием. Например, ссылка H4:J6 указывает на все ячейки, расположенные в прямоугольнике, в левом верхнем углу которого находится ячейка H4, а в правом нижнем – ячейка J6 (т.е. ячейки H4, H5, H6, I4, I5, I6, J4, J5, J6). Ссылка может быть сделана и на очень маленький диапазон, состоящий, например, всего из одной ячейки (H2:H2).

Экспресс-метод

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

Разделив диапазоны точками с запятыми (;), можно также объединить несколько диапазонов в одной ссылке.

Если при отделении двух диапазонов друг от друга воспользоваться вместо точкой с запятой пробелом, то ссылка будет относиться не к объединению этих диапазонов, а к зоне их пересечения. Например, ссылка на два диапазона B3:D4 D2:E6 является ссылкой только

7

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

Совет

Чтобы выделить с помощью мыши зоны с несколькими диапазонами, выделите первую зону, нажмите Ctrl и, удерживая эту клавишу в нажатом состоянии, выделите остальные диапазоны.

Относительные и абсолютные ссылки

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

Относительные ссылки на ячейки

Предположим, например, что формула, находящаяся в ячейке G5, содержит ссылку на ячейку E3. G5 фактически не ссылается на содержание ячейки, расположенной в столбце E строки 3, она просто указывает на ячейку, находящуюся на 2 столбца левее и на 2 строки выше от ячейки G5, которая в данном случае и есть E3. Это различие становится очевидным при копировании ячеек. Если Вы скопируете содержание ячейки G5 в ячейку I8, ссылка на ячейку изменится на G6, которая находится на 2 столбца левее и на 2 строки выше ячейки

I8.

При стиле ссылок R1C1 номер строки и столбца заключается в квадратные скобки, что делает их начальными номерами строк и столбцов. В этой системе ссылок исходным является верхний левый угол рабочего листа, поэтому положительным считается направление вниз и вправо. Например, R[-2]C[2] представляет собой относительную ссылку на ячейку, расположенную на 2 строки выше и на 2 столбца правее ячейки, содержащей данную ссылку.

Относительные ссылки на ячейки очень удобны, если Вы хотите применить формулу к списку значений. Вместо того, чтобы вводить формулу для каждого значения, входящего в этот список, Вы набираете еѐ с использованием относительных ссылок и копируете по всему перечню. Например, если Вам понадобилось вычислить формулу для 50 значений в одном столбце рабочего листа, то можно ввести формулу только один раз в столбец, смежный с содержащим данные, а после этого скопировать еѐ в 49 ячеек. Excel откорректирует все относи-

8

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

Абсолютные ссылки на ячейки

Абсолютная ссылка при копировании формулы остаѐтся неизменной. Такая ссылка всегда указывает на конкретную ячейку независимо от того, в каком месте рабочего листа она расположена. Чтобы сделать ссылку абсолютной, следует перед координатами строки и столбца поставить знак доллара. Например, $G$5 представляет собой абсолютную ссылку на ячейку G5.

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

7.

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

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

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

Например, ссылка $G5 фиксирует конкретный столбец, но координата строки является относительной и изменится, если ячейка, на которую делается ссылка, будет скопирована в другую строку. В ссылке G$5, наоборот, абсолютной является координата строки, а относительной – координата столбца.

В стиле R1C1 ссылка R1C[4] является абсолютной ссылкой на строку 1 и относительной ссылкой на столбец, находящийся на 4 столбца правее от столбца, содержащего данную ссылку.

Использование операторов в вычислениях

Операторы являются базовыми структурными элементами математических формул. Их действие определяет, каким образом объединяются числа (или строки текста) для получения численного итога. Excel предлагает 3 вида операторов: математические, текстовые и логические.

Текстовый оператор существует только один – конкатенация

9

(&). Он служит для объединения двух строк текста в одну.

Математические операторы

Процент и отрицание являются унарными (одноместными) операторами. Оператор процента делит число, расположенное слева от него, на 100 (/100). Если число с оператором процента является единственным значением в ячейке, формат этой ячейки также изменяется на процентный. Положительного унарного оператора в Excel не существует; положительное значение применяется по умолчанию при отсутствии какого-либо унарного оператора. На практике, если поставить перед значением или формулой «+», то Excel удалит этот знак (за исключением случаев, когда он является показателем степени числа в экспоненциальном представлении).

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

Логические операторы

Логические операторы (в учебниках их называют оператора-

ми сравнения) применяются для сравнения 2-х численных значений или строк. Результатом действия этих операций является либо значение Истина, либо значение Ложь. При использовании логических результатов в математических формулах Истина имеет значение 1, а Ложь – значение 0.

Когда Excel сравнивает с применением логических операторов строки, регистры символов (прописные или строчные) программой игнорируются. Однако с помощью функции Excel СОВПАД Вы можете сравнивать строки текста с учѐтом регистра символов.

Приоритет операторов

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

слева направо.

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

10