Тема: Использование функций в вычислениях.
Цели:1. Познакомиться с дополнительными возможностями программы.
2. Научиться работать с мастером функций.
3. Научиться применять итоговые функции.
4. Получить навыки форматирования таблиц.
План:
1.
Основные встроенные функции.
2.
Аргументы в формулах.
3.
Кнопки панели формул.
4.
Использование мастера функций.
5.
Технология работы.
6.
Задания для самостоятельной работы.
7.
Контрольные вопросы.
1. Основные встроенные функции.
Функции - это специально, заранее созданные формулы, которые позволяют легко и быст- ро выполнять сложные вычисления. Они подобны специальным клавишам на некоторых кальку- ляторах, которые выполняют вычисления квадратных корней, логарифмов и статистических ха- рактеристик.
Microsoft Excel имеет более 300 встроенных функций, и все они для удобства выбора и обращения к ним объединены по назначению в группы, называемые категориями: математиче- ские, статистические, текстовые, логические, финансовые, функции даты и времени и др.
Использование всех функций в формулах происходит по одинаковым правилам:
каждая функция имеет свое неповторимое (уникальное) имя;
при обращении к функции после ее имени в круглых скобках указывается список аргу- ментов, разделенных точкой с запятой;
ввод функции в ячейку надо начинать со знака «=», а затем указать ее имя.
Имя функции - как, например, СУММ и СРЗНАЧ - описывает операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. Напри- мер, в формуле: =СУММ(СЗ:С5), СУММ - это имя функции, а С3:С5 - ее единственный аргу- мент. Эта формула суммирует числа в ячейках СЗ, С4 и С5. В таблице 1 приведен перечень функций, наиболее часто используемых.
Таблица 1 – Функции Excel
Функция
Описание
ABS
Возвращает модуль (абсолютную величину) числа.
ACOS
Возвращает арккосинус числа.
ASIN
Возвращает арксинус числа.
ATAN
Возвращает арктангенс числа.
COS
Возвращает косинус числа.
EXP
Возвращает число e, возведенное в указанную степень.
LN
Возвращает натуральный логарифм числа.
LOG
Возвращает логарифм числа по заданному основанию.
LOG10
Возвращает десятичный логарифм числа.
МОПРЕД
Возвращает определитель матрицы, хранящейся в массиве.
МОБР
Возвращает обратную матрицу (матрица хранится в массиве).
МУМНОЖ
Возвращает произведение матриц, хранящихся в массивах.
ПИ
Возвращает число «пи».
СТЕПЕНЬ
Возвращает результат возведения числа в степень.
ПРОИЗВЕД
Возвращает произведение аргументов.
РАДИАНЫ
Преобразует градусы в радианы.
SIN
Возвращает синус заданного угла.
2
Функция Описание КОРЕНЬ
Возвращает положительное значение квадратного корня.
СУММ
Суммирует аргументы.
TAN
Возвращает тангенс числа.
СРЗНАЧ
Возвращает среднее арифметическое аргументов.
СРГЕОМ
Возвращает среднее геометрическое.
МАКС
Возвращает максимальное значение из списка аргументов.
МЕДИАНА
Возвращает медиану заданных чисел.
МИН
Возвращает минимальное значение из списка аргументов.
МОДА
Возвращает значение моды множества данных.
СТАНДОТКЛОН Оценивает стандартное отклонение по выборке.
ДИСП
Оценивает дисперсию по выборке.
И
Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.
ЕСЛИ
Выполняет проверку условия.
НЕ
Меняет на противоположное логическое значение своего ар- гумента.
ИЛИ
Возвращает ИСТИНА, если хотя бы один аргумент имеет зна- чение ИСТИНА.
Одним из важнейших назначений электронной таблицы является автоматизация различ- ных вычислительных операций над данными. Для этого в те объекты таблицы, в которых пред- полагается хранить результаты таких вычислений, вводятся различные формулы. Ввод любой формулы начинается со знака равенства. Если он будет пропущен, то вводимая формула воспри- нимается как текст.
Если вы введете пробел или другие символы между именем и открывающей скобкой, то в ячейке будет отображено ошибочное значение #ИМЯ?
Несколько функций,
таких как ПИ и ИСТИНА, не имеют аргументов.
Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки. 2. Аргументы в формулах. При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, со- держащим любое число ячеек листа. Например, функция: =СУММ (А1 :А5; С2 : С10; D3 : D17) имеет три аргумента, но суммирует числа в 29 ячейках. Указанные в ссылке ячейки в свою оче- редь могут содержать формулы, которые ссылаются на другие ячейки или диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных вычисле- ний или операций.
Можно использовать комбинацию функций для создания выражения, которое Excel сво- дит к единственному значению и интерпретирует его как аргумент. Например, в формуле
=СУММ(SIN(А1*ПИ());2*СОЗ(А2*ПИ())):
SIN(А1*ПИ()) и
2*COS(А2*ПИ()) - это выражения, которые вычисляются и используются в каче- стве аргументов функции СУММ.
Название и назначение функции можно при необходимости найти и выбрать в соответ- ствующем разделе меню. Наиболее часто используемые функции приведены в таблице 3.
Формулы представляют собой соединенные знаками арифметических и/или логических операций операнды. В арифметических формулах используются арифметические операции (сло- жение «+», вычитание «—», умножение «*», деление «/», возведение в степень «^»). При вычис- лении по формулам соблюдается принятый в математике порядок выполнения арифметических операций. Результатом вычислений по арифметической формуле является число, которое выво- дится на экран в режиме отображения в той ячейке, в которую была введена эта формула. При
3 каждом изменении входящих в формулу операндов результат пересчитывается заново и после этого выводится на экран.
Именно поэтому табличный процессор используется как инструмент, позволяющий про- водить вычисления при различных изменениях входящих в формулы величин и практически мгновенно выводить на экран новую, пересчитанную таблицу.
Логические формулы содержат условия, а также различные логические функции (И, ИЛИ и т. д.). Логическая формула определяет истинно или ложно некое выражение. Истинным выра- жениям присваивается численное значение 1, а ложным — 0. Таким образом, вычисление логи- ческой формулы заканчивается получением оценки «истина» (1) или «ложь» (0).
3. Кнопки панели формул. Информацию в Excel можно вводить непосредственно в ячейки или с помощью
строки формул. Содержимое активной ячейки всегда появляется в строке формул.
Хотя
можно вводить информацию прямо в ячейку, использование строки формул имеет некоторые преимущества. Когда указатель мыши установлен на строке формул, то после нажа- тия кнопки мыши, на ней появляются три кнопки:
Кнопка отмены, кнопкой ввода, изменить формулу. При нажатии кнопки ввода Excel «фиксирует» введенную информацию в строке формул и переносит ее на лист. Нажатие кнопки ввода аналогично нажатие Enter за исключением того, что нажатие Enter дополнительно активизирует ячейку, находящуюся непосредственно ниже той ячейки, в которую вы вводили данные. Для удаления ошибочного ввода нажмите кнопку
отмены или Еsс.
Третья кнопка на строке формул - это кнопка мастера функций. При нажатии этой кнопки появляется окно диалога мастера функций, позволяющее вставить в активную ячейку любую встроенную функцию листа.
Если начать ввод формулы щелчком на кнопке
Изменить формулу в строке ввода, под строкой ввода появляется
палитра формул, обладающая свойствами диалогового окна. Она со- держит значение, которое получится, если немедленно закончить ввод формулы. В левой части строки формул, где раньше располагался номер текущей ячейки, теперь появляется раскрываю- щийся список функций. Он содержит десять функций,
которые использовались последними, а также пункт
Другие функции.
4. Использование мастера функций. Выбор функции. При выборе пункта
Другие функции запускается Мастер функций, об- легчающий выбор нужной функции. В списке
Категория выбирается категория, к которой отно- сится функция (если определить категорию затруднительно, используют пункт Полный алфавит- ный перечень), а в списке
Функция — конкретная функция данной категории. После щелчка на кнопке
ОК имя функции заносится в строку формул вместе со скобками, ограничивающими спи- сок параметров. Текстовый курсор устанавливается между этими скобками.
Ввод аргументов функции. В ходе ввода аргументов функции палитра форм изменяет вид. На ней отображаются поля, предназначенные для ввода аргументов. Если название аргумен- та указано полужирным шрифтом, аргумент является обязательным и соответствующее поле должно быть заполнено (рис..2). Аргументы, названия которых приводятся обычным шрифтом, можно опускать. В нижней части палитры приводится краткое описание функции, а также назна- чение изменяемого аргумента.
Кнопка отмена
Кнопка Ввода
Изменить формулу
Рис. 1
4
Аргументы можно вводить непосредственно в строку ввода или в поля палитры формул, а если они являются ссылками — выбирать на рабочем листе. Если аргумент задан, в палитре формул указывается его значение, а для опущенных параметров — значения, принятые по умол- чанию.
Здесь можно также увидеть значение функции, вычисленное при заданных значениях па- раметров.
Правила вычисления формул, содержащих функции, не отличаются от правил вычисления более простых формул. Ссылки на ячейки, используемые в качестве параметров функции, также могут быть относительными или абсолютными, что учитывается при копировании формул мето- дом автозаполнения.
5. Технология работы. Задание 1. Применение итоговых функций.
1. Запустите программу Excel (
Пуск
Программы
Microsoft Excel) и откройте рабочую книгу
book.xls, созданную в предыдущей работе.
2. Выберите рабочий лист
Данные.
3. Сделайте текущей первую свободную ячейку в столбце А.
4. Щёлкните на кнопке
Автосуммирование на стандартной панели инструментов.
5. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу ENTER.
6. Сделайте текущей следующую свободную ячейку в столбце А.
7. Щёлкните на кнопке
Вставка функции на стандартной панели инструментов.
8. В списке
Категория выберите пункт
Статистические.
9. В списке
Функция выберите функцию СРЗНАЧ и щёлкните на кнопке ОК.
10. Переместите методом перетаскивания палитру формул, если она заслоняет нужные ячейки. Обратите внимание, что автоматически выбранный диапазон включает все ячейки с чис- ловым содержимым, включая и ту, которая содержит сумму. Выделите правильный диапазон
(без ячейки содержащей сумму) методом протягивания и нажмите клавишу ENTER.
11. Используя порядок действий, описанный в п.6 – 10, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе
(СЧЁТ).
12. Сохраните рабочую книгу
book.xls.
Задание 2. Найдите сумму, среднее значение, максимум и минимум найденных вами значений функции
1 1
)
(
2 2
xxkxy (задание 2, лабораторной работы 1).
1. Откройте
созданную в первой лабораторной работе книгу, перейдите на лист “функ- ция”.
Строка ввода обяза- тельного аргумента
Строка ввода допол- нительного аргумента
Сворачивающая кнопка
Краткое описание функции
Рис 2
5 2. Подсчитайте сумму вычисленных значений Y и запишите ее в ячейку F23. Для этого выделите ячейку F23 и нажмите кнопку
панели инструментов Стандартная. Убедитесь, что нужный диапазон суммирования задан верно и нажмите клавишу ENTER.
3. В ячейку Е23 запишите поясняющий текст “Сумма у =“.
4. Оформите нахождение среднего арифметического вычисленных значений у (по анало- гии с нахождением суммы) в ячейку F24.
5. Занесите в ячейку Е24 поясняющий текст.
6. Оформите нахождение минимального и максимального значений у, занеся в ячейки Е25 и Е26 поясняющий текст, а в ячейки F25 и F26 - минимальное и максимальное значения. Для это- го воспользуйтесь Мастером функций, который вызывается командой Вставка
Функция или кнопкой на панели инструментов Стандартная. В результате вы получите лист, представленный на рисунке 3.
Рис.3
6. Задания для самостоятельной работы
Задание 1.
Создайте на новом листе вашей рабочей книги опросник «Страны Западной Европы». Для этого задайте две таблицы, одна для ввода значений, другая для оценки введенного выражения.
Таблицы могут иметь вид:
A
B
C
D
E
1
ЗАПОЛНИТЕ ТАБЛИЦУ
2
Страна
Англия
Франция
Россия
Германия
3
Язык
английский французский русский
Немецкий
4
Столица
Лондон
Париж
Москва
Вена
5
Площадь
6 7
РЕЗУЛЬТАТЫ
8
Страна
Англия
Франция
Россия
Германия
9
Язык
верно верно верно верно
10
Столица
верно верно верно ложь
11
Площадь
6
Причем, заполняя первую таблицу во второй должно автоматически указываться, верно ли вве- дено название.
Примечание: используйте логические функции.
Задание 2. Пользуясь аналогичным приёмом, что и при выполнении задания 1, создайте “Таблицу умножения”, которая бы определяла, верно ли занесён результат умножения двух чисел. При вводе логических функций
можно воспользоваться абсолютной ссылкой, например,
=ЕСЛИ(В3=$A2*B$3;верно; неверно), а затем использовать автозаполнение (рис.4).
Рис. 4
Задание 3. Вычислите прогнозируемый объём продаж в течении года, если он зависит от расходов на рекламу по следующему правилу
)
(
log
2 2
iixy
, а затраты на рекламу изменяются каждый месяц по правилу
1 1
*
)
sin(
*
2
xxxii
, где
xi – расходы на рекламу в i-ом месяце года (
12
;
1
i).
yi – прогнозируемый объём продаж.
Работу выполните на отдельном листе вашей книги, который назовите «расчеты» в виде таблицы.
A
B
C
1
Планируемые расходы на рекламу
2
Месяц расходы на рекламу (тыс.руб) прогноз объёма продаж (тыс. шт.)
3 январь
200 15,2877 4 февраль
349,3189 16,8968 5 март
226,5611 15,6475 6 апрель
143,3106 14,326 7 май
373,2044 17,0876 8 июнь
240,5196 15,82 9 июль
392,9666 17,2365 10 август
105,7493 13,449 11 сентябрь
349,8804 16,9014 12 октябрь
367,2995 17,0416 13 ноябрь
105,4555 13,441 14 декабрь
391,0348 17,2223 15
Сумма 190,3574
7
Задание 4.
Используя данные предыдущего задания, вычислите коэффициент вариации V, используя формулу
%
100
x
G
V
, где x -- Средняя величина,
G – среднее квадратичное отклонение, которое рассчитывается по формуле
y
y
x
x
G
i
2
)
(
Задание 5.
Выполнить вычисления по формуле (таблица 2). Используя "Мастер функций" вычислить сумму и среднее арифметическое для трех наборов данных.
Пример выполнения задания представлен на рисунке 5. а) б)
Рис.5 – Пример выполнения задания: а – в режиме отображения формул; б – в режиме вычислений
Таблица 2 – Варианты данных к заданию 5
В
ар иан т
Функция
Первый набор данных
Второй набор данных
a
b
c
m
n
a
b
c
m
n
1
2
sin
a
b
c
c
F
m
n
4,3 17,21 8,2 12,417 8,37 5,2 15,32 7,5 21,823 8,13 2
3 2cos a
a
b
F
m
c
n
13,5 3,7 4,22 34,5 23,725 18,5 5,6 3,42 26,3 14,782 3
3 2
tg
b
a
b
m
F
c
n
2,754 11,7 0,65 2
6,32 3,236 15,8 0,65 3
7,18 4
2 3
lg sin
a
b c
m
F
n
23,16 8,32 145,5 28,6 0,28 17,41 1,27 342,3 11,7 0,71 5
4 2
2
ln
a
b
c
F
b
m
n
22,16 5,03 3,6 12,37 86,2 15,71 3,28 7,2 13,752 33,7 6
2
c
arctg
a
b
F
m
n
16,342 2,5 1
9,14 3,6 12,751 3,7 2
8,12 1,7
8
В
ар иан т
Функция
Первый набор данных
Второй набор данных
a
b
c
m
n
a
b
c
m
n
7 2
2 6
n
e
n a
m b
F
c
23,16 8,32 145,5 28,6 0,28 17,41 1,27 342,3 11,7 0,71 8
5
m
a
a
b
F
n
c
16,342 14,32 38,17 2
3,6 12,751 10,324 23,76 3
1,7 9
2 3
3
c
a
b
a
F
m
n
a
10,82 2,786 3
0,28 14,7 9,37 3,108 4
0,46 15,2 10
2 1
sin
m
n
c
b
a
F
m
2,044 4,2 1,2 2
1 1,175 3,8 5,7 3
2 11
3
cos
m
a
b
c
F
c
m
n
5,3 18,21 1
13,417 8,371 6,2 16,32 2
20,863 7,562 12 2
sin
n
m
a
b
F
m
c
n
12,5 3,2 4,22 1
23,722 19,5 5,9 3,49 3
14,782 13
3 2
3 4
a
b
m
tg
m
F
c
n
3,754 11,3 0,63 7
6,32 4,236 14,8 0,64 3
7,15 14
1
cos
n
a
b
m
F
c
n
b
25,16 8,52 143,5 28,7 1
16,41 1,67 356,3 14,3 2
15
2 3
ln
n
c
a
b
F
m
n
22,16 5,03 3,6 5
1 15,71 3,28 7,2 6
3
7. Контрольные вопросы.
1. Что такое функции Excel?
2. Какие функции вы знаете?
3. Что называется аргументом функции?
4. Может ли быть выбран в качестве аргумента функции целый диапазон ячеек?
5. Как запустить мастера функций?
1
Лабораторная работа №3. 
Скачать 1.63 Mb.