МЕТОДИЧКА_EXCEL_НОВАЯ
.pdfМИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ УКРАИНЫ
НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ХАРЬКОВСКИЙ ПОЛИТЕХНИЧЕСКИЙ ИНСТИТУТ
МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ И РАСЧЕТНЫХ
ЗАДАНИЙ ПО КУРСУ
«Информатика»
(Инженерные расчеты в среде Excel)
для студентов химических специальностей всех форм обучения
УТВЕРЖДЕНО редакционно-издательским советом университета,
протокол № от |
2013г. |
Харьков НТУ «ХПИ» 2013
1
Методические указания к выполнению лабораторных работ и расчетных заданий по курсу «Информатика» (Инженерные расчеты в среде Excel) для студентов химических специальностей всех форм обучения /Сост. В.А. Коцаренко, В.А. Иванов, Л.В. Соловей. – Харьков: НТУ «ХПИ», 2013. – 100 с. – На русском языке.
Составители: В. А. Коцаренко В. А. Иванов Л. В. Соловей
Рецензент А. В. Сатарин
Кафедра интегрированных технологий, процессов и аппаратов
2
Содержание |
|
Вступление............................................................................................................... |
4 |
ТЕМА 1 Операторы. Математические функции в EXCEL............................. |
5 |
1.1. Краткие теоретические сведения....................................................................... |
5 |
1.2. Примеры решения задач.................................................................................... |
7 |
1.3. Задачи для выполнения лабораторных работ................................................. |
10 |
ТЕМА 2 Логические функции в EXCEL .......................................................... |
12 |
2.1. Краткие теоретические сведения..................................................................... |
12 |
2.2. Примеры решения задач.................................................................................. |
14 |
2.3. Задачи для выполнения лабораторных работ................................................. |
19 |
ТЕМА 3 Построение диаграмм в среде EXCEL............................................... |
21 |
3.1. Примеры решения задач.................................................................................. |
21 |
3.2. Задачи для выполнения лабораторных работ................................................. |
27 |
ТЕМА 4 Работа с таблицами.............................................................................. |
30 |
4.1. Примеры решения задач.................................................................................. |
30 |
4.2. Задачи для выполнения лабораторных работ ................................................ |
35 |
ТЕМА 5 Решение уравнений при помощи команды ПОИСК РЕШЕНИЯ.......... |
40 |
5.1. Примеры решения задач.................................................................................. |
40 |
5.2. Задачи для выполнения лабораторных работ ................................................ |
46 |
ТЕМА 6 Нахождение экстремумов функций с помощью команды ПОИСК |
|
РЕШЕНИЯ.................................................................................................................................... |
48 |
6.1. Примеры решения задач.................................................................................. |
48 |
6.2. Задачи для выполнения лабораторных работ ................................................ |
57 |
ТЕМА 7 Решение систем уравнений в среде EXCEL……………………….. 58 |
|
7.1. Примеры решения задач.................................................................................. |
58 |
7.2. Задачи для выполнения лабораторных работ ................................................ |
67 |
ТЕМА 8 Добавление линий тренда ................................................................... |
69 |
7.1. Примеры решения задач.................................................................................. |
69 |
7.2. Задачи для выполнения лабораторных работ ................................................ |
73 |
ТЕМА 9. Использование Excel как базы данных............................................ |
76 |
Список литературы.............................................................................................. |
98 |
3
Вступление
Программа Ехсеl предназначена для автоматизации различного рода расчетов с числовыми данными, организации учета и различного вида отчетности.
Ехсеl дает возможность системно и упорядоченно сосредоточить в едином комплексе колоссальные объемы числовой и текстовой информации, позволяет сортировать и обрабатывать эту информацию, организовывать поиск нужной информации, обобщать и наглядно отображать зависимости элементов этой информации.
Microsoft Excel позволяет выполнять табличные расчеты (математические, физические, инженерные, статистические, экономические, финансовые, бухгалтерские и т.д.) любой сложности легко и быстро с помощью широкого спектра функций, разбитых на категории:
Финансовые; Дата и время; Математические; Статистические; Ссылки и массивы;
Работа с базой данных; Текстовые; Логические;
Проверка свойств и значений.
Электронные таблицы позволяют также проиллюстрировать данные и результаты их обработки в виде графиков и диаграмм.
Незаменим Microsoft Excel для студентов, аспирантов, преподавателей, научных работников, специалистов различного профиля. Студентам он поможет выполнить сложные вычисления для лабораторных, практических, курсовых или дипломных работ, а уже будучи специалистами они найдут в Microsoft Excel надежного помощника при работе над различного рода проектами, требующими сложных вычислений.
4
ТЕМА 1
Операторы. Математические функции в EXCEL
1.1. Краткие теоретические сведения
Операторы в Microsoft Excel
Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Типы операторов
Арифметические операторы. Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение. Операции выполняются над числами. Используются следующие арифметические операторы.
|
|
|
Арифметический оператор |
|
Значение (пример) |
|
|
|
+ (знак плюс) |
|
Сложение (3+3) |
|
|
|
– (знак минус) |
|
Вычитание (3–1) |
|
Отрицание (–1) |
|
|
|
|
|
|
|
* (звездочка) |
|
Умножение (3*3) |
|
|
|
/ (косая черта) |
|
Деление (3/3) |
|
|
|
% (знак процента) |
|
Процент (20%) |
|
|
|
^ (крышка) |
|
Возведение в степень (3^2) |
|
|
|
Операторы сравнения. Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
|
|
Оператор сравнения |
Значение (пример) |
|
|
= (знак равенства) |
Равно (A1=B1) |
|
|
> (знак больше) |
Больше (A1>B1) |
|
|
< (знак меньше) |
Меньше (A1<B1) |
|
|
>= (знак больше или равно) |
Больше или равно (A1>=B1) |
|
|
<= (знак меньше или равно) |
Меньше или равно (A1<=B1) |
|
|
<> (знак не равно) |
Не равно (A1<>B1) |
|
|
5
Текстовый оператор конкатенации. Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.
Текстовый оператор |
Значение (пример) |
& (амперсанд)
Объединение последовательностей знаков в одну последовательность ("Северный"&"ветер")
Оператор ссылки. Для описания ссылок на диапазоны ячеек используются следующие операторы.
Оператор ссылки |
Значение (пример) |
: (двоеточие)
; (точка с запятой)
Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон ячеек (B5:B15)
Оператор объединения. Объединяет несколько ссылок на ячейки в одну ссылку
(СУММ(B5:B15;D5:D15))
(пробел)
Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8)
Приоритет оператора
Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, показанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом – например операторы деления и умножения – они выполняются слева направо.
|
|
|
Оператор |
|
Описание |
|
|
|
: (двоеточие) |
|
|
(один пробел) |
|
Операторы ссылок. |
, (запятая) |
|
|
|
|
|
– |
|
Знак «минус» |
|
|
|
% |
|
Процент |
|
|
|
^ |
|
Возведение в степень |
|
|
|
* и / |
|
Умножение и деление |
|
|
|
+ и – |
|
Сложение и вычитание |
|
|
|
& |
|
Объединение двух текстовых строк в одну |
|
|
|
= < > <= >= <> |
|
Сравнение |
|
|
|
6
Использование круглых скобок
Для того чтобы изменить порядок выполнения, заключите часть формулы, которая должна выполняться первой, в скобки. Например, результатом следующей формулы =5+2*3 будет число 11, поскольку Microsoft Excel выполняет умножение до сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 5.
Если же с помощью скобок изменить синтаксис, =(5+2)*3, то Microsoft Excel сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.
1.2. Примеры решения задач
ПРИМЕР 1.1.. Вычислить значение выражения:
sin x
z 
1 m2 sin2 x ,
где x=1,5; m=5,74
Последовательность расчета:
1.Введите значения констант x и m соответственно в ячейки B1 и B2.
2.В ячейки A1 и A2 вводим текст «x=» и «m=»
3.В ячейку B3 введите формулу:
=SIN(B1)/КОРЕНЬ(1+B2^2*SIN(B1)^2)
При написании формулы воспользуйтесь табл. 1.1. Результат решения представлен на рис. 1.1.
Рисунок 1.1 – Вычисление значения выражения
7
Таблица 1.1 – Математические функции в EXCEL
Оператор |
Описание |
ABS (число) |
Возвращает модуль (абсолютную величину) |
|
числа. |
ACOS (число) |
Возвращает арккосинус числа в радианах, в |
|
диапазоне от 0 до π. Арккосинус числа есть |
|
угол, косинус которого равен числу. |
ACOSH (число) |
Возвращает гиперболический арккосинус |
|
числа. |
ASIN (число) |
Возвращает арксинус числа в радианах, в |
|
диапазоне от – π/2 до π/2 |
ASINH (число) |
Возвращает гиперболический арксинус числа. |
ATAN (число) |
Возвращает арктангенс числа в радианах, в |
|
диапазоне от – π /2 до π /2 |
ATANH (число) |
Возвращает гиперболический арктангенс |
|
числа. |
COS (число) |
Возвращает косинус числа. |
COSH (число) |
Возвращает гиперболический косинус числа. |
EXP (число) |
Возвращает число e, возведенное в указанную |
|
степень. |
LN (число) |
Возвращает натуральный логарифм числа. |
LOG (число; основание) |
Возвращает логарифм числа по заданному |
|
основанию. |
LOG10 (число) |
Возвращает десятичный логарифм числа. |
SIN (число) |
Возвращает синус заданного угла. |
SINH (число) |
Возвращает гиперболический синус числа. |
TAN (число) |
Возвращает тангенс числа. |
TANH (число) |
Возвращает гиперболический тангенс числа. |
ГРАДУСЫ (угол) |
Преобразует радианы в градусы. |
ЗНАК (число) |
Возвращает знак числа: |
|
1 - если число положительное |
|
|
|
0 - если оно равно нулю |
|
|
|
-1 - если число отрицательное |
КОРЕНЬ (число) |
Возвращает значение квадратного корня. |
МОБР (массив) |
Возвращает обратную матрицу (матрица |
|
хранится в массиве). |
МОПРЕД (массив) |
Возвращает определитель матрицы, |
|
хранящейся в массиве. |
МУМНОЖ (массив1; массив2) |
Возвращает произведение матриц, |
|
хранящихся в массивах. |
|
|
8
Окончание таблицы 1.1
Оператор |
Описание |
ОКРВВЕРХ (число; точность) |
Округляет число до ближайшего целого или |
|
до ближайшего кратного указанному |
|
значению. |
ОКРВНИЗ (число; точность) |
Округляет число до ближайшего меньшего по |
|
модулю целого. |
ОКРУГЛ (число; |
Округляет число до указанного количества |
число разрядов) |
десятичных разрядов. |
ОКРУГЛВВЕРХ (число; |
Округляет число до ближайшего по модулю. |
число разрядов) |
|
ОКРУГЛВНИЗ (число; |
Округляет число до ближайшего меньшего по |
число разрядов) |
модулю. |
ОСТАТ (число; делитель) |
Возвращает остаток от деления. |
ОТБР (число; число_разрядов) |
Отбрасывает дробную часть числа, так что |
|
остается целое число. |
ПИ() |
Возвращает число π (значение |
|
3,14159265358979). |
ПРОИЗВЕД (число1; число2; …) |
Возвращает произведение аргументов. |
РАДИАНЫ (угол) |
Преобразует градусы в радианы. |
СЛЧИС ( ) |
Возвращает случайное число в интервале от 0 |
|
до 1. |
СТЕПЕНЬ (число; степень) |
Возвращает результат возведения числа в |
|
степень. |
СУММ (число1; число2; …) |
Суммирует аргументы. |
СУММКВ (число1; число2; …) |
Возвращает сумму квадратов аргументов. |
|
Аргументами могут являться числа, массивы, |
|
имена или ссылки на ячейки, содержащие |
|
числа. |
СУММКВРАЗН (массив_x; |
Возвращает сумму квадратов разностей |
массив_y) |
соответствующих значений в двух массивах. |
СУММПРОИЗВ(массив1; |
Возвращает сумму произведений |
массив2; массив3; …) |
соответствующих элементов массивов. |
СУММРАЗНКВ (массив_x; |
Возвращает сумму разностей квадратов |
массив_y) |
соответствующих значений в двух массивах. |
СУММСУММКВ(массив_x; |
Возвращает сумму сумм квадратов |
массив_y) |
соответствующих элементов двух массивов. |
ФАКТР (число) |
Возвращает факториал числа, равный |
|
1*2*3…*число |
ЦЕЛОЕ (число) |
Округляет число до ближайшего меньшего |
|
целого. |
ЧАСТНОЕ |
Возвращает целую часть частного при |
|
делении. |
9
1.3. Задачи для выполнения лабораторных работ
Расчетные формулы и значения исходных данных представлены в табл. 1.2
Таблица 1.2 – Варианты заданий
|
Вар. |
|
|
|
|
|
|
|
|
Расчетные формулы |
Значения исходных |
|||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
данных |
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
|
3 |
||
1 |
|
a |
2cos(x 6) |
|
|
|
|
|
|
|
|
|
x 1,426 |
|||||||||||||||||||||||
|
|
|
|
1 2 sin2 y |
|
|
|
|
|
|
|
|
|
|
|
|
y 1,22 |
|||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||||||||||
|
|
b 1 |
|
|
z |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
z 3,5 |
|||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||
|
3 z2 5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||
2 |
|
|
|
|
|
|
y x |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
x 1,825 |
||||||
|
|
|
x |
|
3 y x |
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
y 18,225 |
|||||||||||||||
|
|
(y x) |
|
y z (y x) |
|
|
|
|
|
|||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
z 3,298 |
||||||||||||||||||||||||||||
|
|
1 (y x)2 |
||||||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||||||||
3 |
|
|
|
|
|
bt |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
a 0,5 |
|||||||
|
y e |
sin(at b) |
|
|
|
bt a |
|
|||||||||||||||||||||||||||||
|
|
|
|
|
||||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
b 1,7 |
||||||||||||||||||||||||
|
|
s bsin(at2 cos2t) 1 |
||||||||||||||||||||||||||||||||||
|
|
t 0,44 |
||||||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
a |
|
1,5 |
|
|
|
|
|
|
x2 b b2 sin3 (x a) x |
||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
b 15,5 |
||
|
|
y cos2 |
|
x3 x |
|
a2 |
b2 |
|||||||||||||||||||||||||||||
|
|
|
|
x 2,9 |
||||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
5 |
|
|
|
3 |
|
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
a 16,5 |
||||||||
|
|
|
|
(x b) a |
|
|
x b |
|||||||||||||||||||||||||||||
|
|
s x tg |
|
|
|
b 3,4 |
||||||||||||||||||||||||||||||
|
|
Q |
|
bx2 a |
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
x 0,61 |
|||||
|
|
e |
ax |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||
6 |
|
R x2 (x 1) b sin2(x a) |
a 0,7 |
|||||||||||||||||||||||||||||||||
|
|
S |
|
|
|
|
|
|
cos2(x b)3 |
b 0,05 |
||||||||||||||||||||||||||
|
|
|
|
|
xb a |
|||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
x 0,5 |
||
7 |
|
y sin3(x2 a)2 |
|
|
|
|
|
|
|
a 1,1 |
||||||||||||||||||||||||||
|
x b |
|||||||||||||||||||||||||||||||||||
|
|
z |
x2 |
|
|
cos(x b)3 |
|
|
|
|
|
|
|
|
|
b 0,004 |
||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
x 0,2 |
|||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||||||||||
|
|
|
|
|
a |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
10