Е.Е. Дадонова Анализ функций проектирование простых таблиц средствами Excel
.pdf1
МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ
КУЗБАССКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ
Кафедра вычислительной техники и информационных технологий
АНАЛИЗ ФУНКЦИЙ И ПРОЕКТИРОВАНИЕ ПРОСТЫХ ТАБЛИЦ СРЕДСТВАМИ EXCEL
Методические указания к лабораторной работе по дисциплине «Информатика»
для студентов специальности «Социально–культурный сервис и туризм»
Составители Е.Е. Дадонова Ю.В. Клещ А.Г. Пимонов
Утверждены на заседании кафедры Протокол № 9 от 1 июня 2001 г.
Рекомендованы к печати учебнометодической комиссией специальности 230500 Протокол № 3 от 14 июня 2001 г.
Электронная копия хранится в библиотеке главного корпуса КузГТУ
КЕМЕРОВО 2001
2
1.ВЫЧИСЛИТЕЛЬНЫЕ ВОЗМОЖНОСТИ EXCEL
Смомента появления в 1985г. первой версии электронной таблицы Excel, эта программа, постоянно совершенствуясь, остаётся на самых передовых позициях в ряду аналогичных программных продуктов. «Простые задачи должны решаться просто». Этому постулату как нельзя лучше отвечают вычислительные возможности Excel, которые без оговорки можно назвать безграничными. Практически любую задачу вычислительного характера можно решить средствами Excel, используя уникальную по простоте технологию, условно называемую «щёлк – щёлк – щёлк». В сочетании с языком программирования Visual Basic for Application (VBA) Excel приобретает универсальный характер. Для ускорения и облегчения вычислительной работы Excel предоставляет в распоряжение пользователя мощный аппарат функций рабочего листа, позволяющих осуществить математические, финансовые, статистические и т.д. расчёты.
1.2.Функции рабочего листа
Вцелом Microsoft Excel содержит около 1000 функций рабочего листа (встроенных функций), обеспечивающих возможность выполнения самых разнообразных вычислений. Все они в соответствии с характером вычислений делятся на 12 групп:
1) математические функции;
2) текстовые функции;
3) логические функции;
4) информационные функции;
5) функции ссылки и автоподстановки;
6) функции даты и времени;
7) финансовые функции;
8) инженерные функции;
9) статистические функции;
10)функции проверки свойств и значений;
3
11)функции DDE и внешние функции;
12)функции для работы со списками.
Обращение к каждой функции производится указанием её имени и следующего за ним в круглых скобках списка аргументов (параметров). Наличие круглых скобок обязательно, именно они служат признаком того, что используемое имя является именем функции. Аргументы списка разделяются точкой с запятой. Их количество не должно превышать 30, а длина формулы, содержащей сколько угодно обращений к функциям, не может превышать 1024 символов. В качестве аргументов могут использоваться константы, адреса ячеек, интервалы, выражения. Например:
=СУММ(2,71; A1; B2:D10; 2*SIN($A$3*ПИ()))
Наличие знака равенства (=) перед формулой обязательно, именно на основании его присутствия перед выражением Excel интерпретирует (точнее пытается интерпретировать) выражение как формулу, а не как текст. Большинство имён функций образовано буквами русского алфавита и имеет определённую смысловую нагрузку, иногда понятную даже неискушённому пользователю (=СУММ() – возвращает сумму своих аргументов). Все имена при вводе формулы лучше набирать строчными буквами, тогда правильно введённые имена Excel отображает прописными буквами.
Нет возможности, да и необходимости приводить полное описание более трёх сотен встроенных функций. Excel обладает отличной справочной системой, которой в состоянии воспользоваться даже малоопытный пользователь. Приведём краткое описание некоторых математических и логических функций.
1.2.1.Математические функции
1)ABS(число) – возвращает модуль (абсолютную величину) числа.
2)ACOS(число) – возвращает арккосинус числа. Арккосинус числа – это угол, косинус которого равен числу. Угол определяется в радианах в интервале от 0 до π.
|
|
|
|
|
4 |
3) ACOSH(число) |
|
– |
возвращает гиперболический арккосинус числа |
||
|
+ |
x |
2 |
−1 |
|
ArCh( x ) = Ln x |
|
. Число должно быть больше или равно 1. Ги- |
|||
|
|
|
|
|
|
перболический арккосинус числа – это значение, гиперболический косинус которого равен числу, так что ACOSH(COSH(x)) равняется x.
4)ASIN(число) – возвращает арксинус числа. Арксинус числа – это угол, синус которого равен числу. Угол определяется в радианах в интервале от
–π/2 до π/2.
5) ASINH(число) – возвращает гиперболический арксинус числа
|
x |
2 |
|
ArSh( x ) = Ln x + |
|
+1 . Гиперболический арксинус числа – это зна- |
|
|
|
|
|
чение, гиперболический синус которого равен числу, так что
ASINH(SINH(x)) равняется x.
6)ATAN(число) – возвращает арктангенс числа. Арктангенс числа – это угол, тангенс которого равняется числу. Угол определяется в радианах в диапазоне от –π/2 до π/2.
7)ATAN2(x; y) – возвращает арктангенс для заданных координат x и y. Арктангенс – это угол между осью OX и линией, проведенной из начала координат (0, 0) в точку с координатами (x, y). Угол определяется в радианах в диапазоне от –π до π, исключая –π.
8) ATANH(число) – возвращает гиперболический арктангенс числа
|
1 |
|
1 |
+ x |
|
ArTh( x ) = |
|
Ln |
|
|
. Число должно быть в интервале от – 1 до 1 (ис- |
2 |
|
|
|||
|
1 |
− x |
|||
ключая –1 и 1). Гиперболический арктангенс числа – это значение, гиперболический тангенс которого равен числу, так что ATANH(TANH(x)) равняется x.
9) COS(число) – возвращает косинус заданного угла.
5
10) COSH(число) – возвращает гиперболический косинус числа
Ch( x ) = |
ex +e−x |
. |
|
2 |
|||
|
|
11)EXP(число) – возвращает число e, возведенное в указанную степень. Число e=2,71828182845904 – основание натурального логарифма.
12)LN(число) – возвращает натуральный логарифм числа. Натуральный логарифм – это логарифм по основанию e (2,71828182845904).
13)LOG(число; основание) – возвращает логарифм числа по заданному основанию.
14)LOG10(число) – возвращает десятичный логарифм числа.
15)SIN(число) – возвращает синус заданного угла.
16) SINH(число) – возвращает гиперболический синус числа
Sh( x ) = ex −e−x . 2
17)TAN(число) – возвращает тангенс заданного угла.
18) TANH(число) – возвращает гиперболический тангенс числа
Th( x ) = ChSh(( xx )) .
19)ГРАДУСЫ(угол) – преобразует радианы в градусы.
20)ДВФАКТР(число) – возвращает двойной факториал числа n!!=1 3 5…n.
21)ЗНАК(число) – определяет знак числа. Возвращает 1, если число положительное, ноль (0), если число равно 0 и – 1, если число отрицательное.
22)КОРЕНЬ(число) – возвращает положительное значение квадратного корня.
23)КОРЕНЬПИ(число) – возвращает квадратный корень из значения выражения (числоπ).
6
24)МОБР(массив) – возвращает обратную матрицу для матрицы, хранящейся в массиве.
25)МОПРЕД(массив) – возвращает определитель матрицы (матрица хранится в массиве).
26)МУЛЬТИНОМ(число1; число2; ...) – возвращает отношение факториала суммы значений к произведению факториалов.
27)МУМНОЖ(массив1; массив2) – возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2.
28)НЕЧЁТ(число) – возвращает число, округленное до ближайшего нечетного целого.
29)НОД(число1; число2; ...) – возвращает наибольший общий делитель двух или более целых чисел. Наибольший общий делитель – это наибольшее целое, на которое делятся число1 и число2 без остатка.
30)НОК(число1;число2; ...) – возвращает наименьшее общее кратное целых чисел. Наименьшее общее кратное – это наименьшее положительное целое, которое кратно всем целым аргументам число1, число2 и так далее.
31)ОКРВВЕРХ(число; точность) – возвращает результат округления с избытком до ближайшего числа, кратного точности.
32)ОКРВНИЗ(число; точность) – округляет число до кратного заданной точности с недостатком.
33)ОКРУГЛ(число; число_разрядов) – округляет число до указанного количества десятичных разрядов.
34)ОКРУГЛВВЕРХ(число; количество_цифр) – округляет число по мо-
дулю до ближайшего большего целого.
35)ОКРУГЛВНИЗ(число; количество_цифр) – округляет число до бли-
жайшего меньшего по модулю целого.
7
36)ОКРУГЛТ(число; точность) – возвращает число, округленное с желаемой точностью.
37)ОСТАТ(число; делитель) – возвращает остаток от деления аргумента число на делитель. Результат имеет такой же знак, как и делитель.
38)ОТБР(число; число_разрядов) – усекает число до целого, отбрасывая дробную часть числа, так что остается целое число.
39)ПИ( ) – возвращает число π=3,14159265358979, математическую константу π с точностью до 15 цифр.
40)ПРОИЗВЕД(число1; число2; ...) – перемножает числа, заданные в качестве аргументов, и возвращает их произведение.
41)ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; ссылка1; ссыл-
ка2;...) – возвращает промежуточный итог в список или базу данных.
42)РАДИАНЫ(угол) – преобразует градусы в радианы.
43)РЯД.СУММ(x; n; m; коэффициенты) – возвращает сумму степенного ряда, вычисленную по формуле
РЯД( х,n,m,a ) = a1xm + a2 x( n+m ) + a3 x( n+2m ) +K+ ai x( n+( i−1 )m ) ,
где x – значение переменной степенного ряда;
n – показатель степени x для первого члена степенного ряда;
m – шаг, на который увеличивается показатель степени n для каждого следующего члена степенного ряда; коэффициенты – это набор коэффициентов при соответствующих степенях x.
44)СЛУЧМЕЖДУ(нижн_граница;верхн_граница) – возвращает случай-
ное число между двумя заданными числами. Каждый раз, когда рабочий лист перевычисляется, возвращается новое случайное число.
45)СЛЧИС( ) – возвращает равномерно распределенное случайное число, большее либо равное 0 и меньшее 1. Новое случайное число возвращается каждый раз, когда рабочий лист перевычисляется.
8
46)СТЕПЕНЬ(число; степень) – возвращает результат возведения в степень.
47)СУММ(число1; число2; ...) – суммирует все числа в интервале ячеек.
48)СУММЕСЛИ(интервал; критерий; сумм_интервал) – суммирует ячейки, специфицированные заданным критерием.
49)СУММКВ(число1; число2; ...) – возвращает сумму квадратов аргументов.
50)СУММКВРАЗН(массив_x; массив_y) – возвращает сумму квадратов разностей соответствующих значений в двух массивах.
51)СУММПРОИЗВ(массив1; массив2; массив3; ...) – перемножает со-
ответствующие элементы заданных массивов и возвращает сумму произведений.
52)СУММРАЗНКВ(массив_x; массив_y) – возвращает сумму разностей квадратов соответствующих значений в двух массивах.
53)СУММСУММКВ(массив_x; массив_y) – возвращает сумму сумм квадратов соответствующих элементов двух массивов.
54)СЧЁТЕСЛИ(интервал; критерий) – подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию.
55)ФАКТР(число) – возвращает факториал числа. Факториал числа – n!=1 2 3 ... n.
56)ЦЕЛОЕ(число) – округляет число до ближайшего меньшего целого.
57)ЧАСТНОЕ(числитель; знаменатель) – возвращает частное от деле-
ния нацело. Эта функция используется, когда нужно отбросить остаток от деления.
58)ЧЁТН(число) – возвращает число, округленное до ближайшего четного целого.
59)ЧИСЛКОМБ(число; число_выбранных) – возвращает количество комбинаций для заданного числа объектов.
9
1.2.2.Логические функции
1)И(логическое_значение1; логическое_значение2; ...) – возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение
ЛОЖЬ.
Логическое_значение1, логическое_значение2, ... – это от 1 до 30 про-
веряемых условий, которые могут иметь значение либо ИСТИНА, либо
ЛОЖЬ.
Примеры:
а) =И(2+2=4; 2+3=5) равняется ИСТИНА
б) Если ячейка B4 содержит число между 1 и 100, то:
=И(1<B4; B4<100) равняется ИСТИНА
в) Предположим, что нужно вывести на экран содержимое ячейки B4, если она содержит число строго между 1 и 100 и текст "Значение вне интервала" в противном случае. Тогда, если ячейка B4 содержит число 104, то в ячейке, куда будет записано выражение:
=ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала") мы увидим
"Значение вне интервала", а если ячейка B4 содержит число 50, то результат вычисления выражения:
=ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала")
равняется 50.
2)ИЛИ(логическое_значение1; логическое_значение2; ...) – возвраща-
ет ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Логическое_значение1, логическое_значение2, ... – это от 1 до 30 проверяемых условий (логических выражений), в результате вычисления которых получается значение либо ИСТИНА, либо ЛОЖЬ.
10
Примеры:
а) =ИЛИ(1+1=1;2+2=5) равняется ЛОЖЬ.
б) Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то: =ИЛИ(A1:A3) равняется ИСТИНА.
3)НЕ(логическое_значение) – меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.
Пример:
=НЕ(1+1=2) равняется ЛОЖЬ.
4) ИСТИНА( ) – возвращает логическое значение ИСТИНА.
Можно непосредственно ввести значение ИСТИНА в ячейки и формулы без использования этой функции. Функция ИСТИНА предназначена для совместимости с другими системами электронных таблиц.
5) ЛОЖЬ( ) – возвращает логическое значение ЛОЖЬ.
Можно непосредственно ввести слово ЛОЖЬ в ячейку рабочего листа или использовать его в формуле, и Microsoft Excel будет интерпретировать это слово как логическое значение ЛОЖЬ.
6)ЕСЛИ(лог_выражение;значение_если_истина;значение_если_
ложь) – возвращает значение_если_истина, если заданное условие при вычислении дает значение ИСТИНА, и значение_если_ложь в противном случае.
Лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Примеры:
а) Предположим, что необходимо записать формулу для вычисления функции F(x)=1-3/х. Значение аргумента содержится в ячейке В2. Функция не определена при х=0. Таким образом, формула примет вид