Geum.ru

Урок №7. Тема: Функции в Excel

Вид материалаУрок

Содержание


Основные математические функции.
Тригонометрические функции.
Функции округления.
Функции генерирования случайных чисел.
Ввод функций.
Лабораторная работа №7 Тема: Формулы и ссылки в Excel.
Подобный материал:
УРОК №7.

Тема: Функции в Excel.


Цели:
  1. Обучающая: научить учащихся применять функции при решении задач в Excel.
  2. Развивающая: продолжить обучение учащихся логически мыслить, принимать правильное решение.
  3. Воспитательная: способствовать воспитанию самостоятельности, активности учащихся.

Ход урока
  1. Организационный момент.

Проверка присутствующих и готовности, учащихся к занятию.
  1. Актуализация.
  1. -Что такое Excel?
    -Для чего нужна формула?
    -На какие ячейки могут ссылаться формулы?
  2. -Что можно ввести в ячейку?
    -Что такое ячейка и адрес ячейки?
    -Как обозначаются арифметические операции в Excel?
    -Каким образом осуществляется ввод формулы?
  3. -Ссылка на ячейку. Относительная ссылка.
  4. -Абсолютная ссылка, смешанная.
  5. -Чем отличаются относительная и абсолютная ссылки. В каких случаях проявляются различия между ними?
    1. Новая тема.

Функции Excel.

Функции призваны облегчить работу при создании и взаимодействии с электронными таблицами. Простейшим примером выполнения расчетов является операция сложения. Воспользуемся этой операцией для демонстрации преимуществ функций. Не используя систему функций нужно будет вводить в формулу адрес каждой ячейки в отдельности, прибавляя к ним знак плюс или минус. В результате формула будет выглядеть следующим образом: =B1+B2+B3+C4+C5+D2.

Заметно, что на написание такой формулы ушло много времени, поэтому кажется, что проще эту формулу было бы легче посчитать вручную. Чтобы быстро и легко подсчитать сумму в Excel, необходимо всего лишь задействовать функцию суммы, нажав кнопку с изображением знака суммы или из Мастера функций, можно и вручную впечатать имя функции после знака равенства. После имени функций надо открыть скобку, введите адреса областей и закройте скобку. В результате формула будет выглядеть следующим образом: =СУММ(B1:B3;C4:C5;D2). Если сравнить запись формул, то видно, что двоеточием здесь обозначается блок ячеек, запятой разделяются аргументы функций. Использование блоков ячеек, или областей, в качестве аргументов для функций целесообразно, поскольку оно во-первых, нагляднее, а во-вторых, при такой записи программе проще учитывать изменения на рабочем листе. Например нужно подсчитать сумму чисел в ячейках с А1 по А4. Это можно записать так: =СУММ(А1;А2;А3;А4). Или то же самое другим способом: =СУММ(А1:А4).

Программа Excel содержит множество всевозможных функций. Наиболее обще их можно представить следующим образом:

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

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

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

Математическая – вычисление математической величины: косинуса, логарифма и т.д.

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

Ссылки и массивы – вычисляют ссылки и массивы, значение диапазона, создание гиперссылки для сетевых и Web документов.

Текстовые – преобразование текстов в верхние и нижние регистры, образует символы, объединяет некоторые строки и т. д.

Логические – вычисления выражения выяснения значения истина или ложь.

Проверка свойств и значений – возвращение из Excel в Windows информации о текущем статусе ячейки, объекта или среды.

Таблица 1. Основные математические функции.

Наименование
Обозначение
Примечание

Знак
=ЗНАК(х)
Х- число, ссылка на ячейку с числом или формула, возвращающая числовое значение.

Возвращаемее значения: 1, если х>0; 0, если х=0; -1, если х<0.

Абсолютное значение
=ABS(х)
Х- число, ссылка на ячейку с числом или формула, возвращающая числовое значение.

Сумма
=СУММ(х1;…хn)
N<=30; игнорируются пустые ячейки, текстовые и логические значения

Произведение
=ПРОИЗВЕД(х1; … xn)

Корень квадратный
=КОРЕНЬ(х)
Х>=0

Корень квадратный из х*π
=КОРЕНЬПИ(х)
Возвращает корень квадратный1 из числа (х*π)

Факториал
=ФАКТР(х)
Х>=0. Если х нецелое, то дробная часть отбрасывается перед вычислением функции

Частное
=ЧАСТНОЕ(х;у)
Возвращает целую часть от деления х на у.

Остаток от деления
=ОСТАТ(х;у)
Возвращает остаток от деления, вычисляемый как х-ent[x/y]. Если х

Наименьшее общее кратное
=НОК(х1; … xn)
ТБ=29/ Если аргумент xi не целый, то он усекается до целого. Ограничения на аргументы: xi>0/

Наибольший общий делитель
=НОД(x1; …xn)

Таблица 2. Логарифмические функции.

Наименование
Обозначение
Примечание

Натуральный логарифм
=LN(x)
x>0, при x<=0 возвращает ошибочное значение #ЧИСЛО!

Десятичный логарифм
=LOG10(x)
x>0, при x<=0 возвращает ошибочное значение #ЧИСЛО!

Логарифм по заданному основанию
=LOG(x; основание)
По умолчанию основание =10

Экспонента от х
=EXP(x)



Возведение в степень
=СТЕПЕНЬ (х;а)



Таблица 3. Тригонометрические функции.

Матем. обозначение
Обозначение в Excel
Примечание

π
=ПИ()
Возвращает значение π с 14 значащими разрядами после десятичной точки




=ГРАДУСЫ(угол)
Преобразует угол в радианах в градусы




=РАДИАНЫ(угол)
Преобразует угол в градусах в радианы

Sin x
=SIN(x)
Х-угол в радианах

Cos x
=COS(x)
Х-угол в радианах

Tg x
=TAN(x)
Х-угол в радианах

Arctg x
=ATAN(x)
Возвращаемое значение лежит на интервале между –π/2 и π/2 радиан.

Arcsin x
=ASIN(x)
Ограничения на аргумент: -1<=x<=1. Возвращаемое значение лежит на интервале от

–π/2 до π/2 радиан.

Arccos x
=ACOS(x)
Ограничения на аргумент: -1<=x<=1. Возвращаемое значение лежит на интервале от 0 до π радиан.

Arctg x
=ATAN2(x;y)
Возвращает значение угла между осью х и линией, соединяющей точки с координатами (0,0) и (х,у). Возвращаемое значение z находится в диапазоне

-π

Таблица 3. Функции округления.

Наименование
Обозначение
Примечание

Округление
=ОКРУГЛ(х;n)
n=0—округление до ближайшего целого; n>0—округление дробной части х до n разрядов после десятичной точки (n=-2, округление до сотых долей); n<0—округление до n разрядов слева от десятичной запятой (n=2, округление до сотен). Цифры <5 округляются с недостатком (вниз), цифры >=5 округляется с избытком (вверх).

Округление с избытком
=ОКРУГЛВВЕРХ

(х;n)
Функция аналогична функции ОКРУГЛ, но округление до ближайшего большего

Округление с недостатком
=ОКРУГЛВНИЗ(х;n)
Функция аналогична функции ОКРУГЛ, но округление до ближайшего меньшего

Округление до четного
=ЧЕТН(х)
Округление вверх до ближайшего четного целого числа.

x>0 – округление вверх;

x<0 – округление вниз

Округление до нечетного
=НЕЧЕТ(х)
Округление вверх до ближайшего нечетного целого числа.

x>0 – округление вверх;

x<0 – округление вниз

Округление до большего числа, кратного n
=ОКРВВЕРХ(х)
Округление до ближайшего большего целого числа, кратного n. Значения x и n должны иметь одинаковый знак. В противном случае возвращается ошибочное значение #ЧИСЛО!

Округление до меньшего числа, кратного n
=ОКРВНИЗ(х;n)
Округление до ближайшего бменьшего целого числа, кратного n. Значения x и n должны иметь одинаковый знак. В противном случае возвращается ошибочное значение #ЧИСЛО!

Округление до целого
=ЦЕЛОЕ(х)
Округление числа х вниз до ближайшего целого

Отбрасывание разрядов
=ОТБР(х;n)
Отбрасывает все цифры справа от десятичной точки. Аргумент n определяет позицию в дробной части числа, после которого производится усечение.

Таблица 4. Функции генерирования случайных чисел.

Обозначение
Примечание

=СЛЧИС()
Генерирует случайные числа, распределенные на интервале [0;1]. Значение, возвращаемое функцией, изменяется при каждом перерасчете листа. Если установлено автоматическое обновление вычислений, значение функции изменяется каждый раз при вводе данных в листе.

=СЛУЧМЕЖДУ(х;у)
Генерирует случайные числа, равномерно распределенные на интервале [х;у].

Ввод функций.

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

Существует также два способа, равноценных последнему, но не требующих предварительного ввода знака равенства:
  • Через пункт меню Вставка/ Функция
  • С помощью кнопки Вставка функции.

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

Операторы.

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

Оператор Функция Пример

+ сложение =A1+1

- вычитание =4-С4

* умножение =A3*X123

/ деление =D3/Q6

% процент =10%

Лабораторная работа №7

Тема: Формулы и ссылки в Excel.


Цель: научиться применять функции при решении задач в Excel.
  1. Запустите MS Excel.
  2. Создайте следующую таблицу:
  3. Произведите расчет изменения цены в колонке «Е» по формуле: Изменение цены = Цена на 01.06.2003/Цена на 01.04.2003. Для этого в ячейку Е6 введите =D6/B6. Затем используя мастер автозаполнения, заполните остальные ячейки.
  4. Не забудьте задать процентный формат чисел в колонке «Е» (Формат/Ячейки/Число/Процентный)!!!
  5. Рассчитайте среднее значение по колонкам, пользуясь мастером функций. Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в ячейку В143, запустите мастер функций (кнопкой Вставка функции или командой Вставка/Функция) и на первом шаге мастера выберите функцию СРЗНАЧ.
  6. После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК. В ячейке В14 появится среднее значение данных колонки «В».
  7. Аналогично рассчитайте среднее значение в других колонках.
  8. В ячейки А2 задайте функцию Сегодня, отображающую текущую дату, установленную в компьютере (Вставка/Функция/Дата и Время/Сегодня).
  9. Сохраните файл под названием «Динамика цен».
  10. Результаты работы покажите учителю.

ЗАДАНИЕ.
  1. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).


  1. Произвести расчет Премии по формуле: Премия = Базовая ставка * 0,25 при условии, что План расходования ГСМ > Фактически израсходованного ГСМ. Для проверки условия используйте функцию ЕСЛИ.
  2. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию.