Методическая разработка по информатике на тему "Формулы и функции в Excel"

Формулы и

функции Excel

Что такое Формула

Формула – выражение, по которым выполняют вычисления.

Для активации строки формул надо:

• Открыть меню «Вид» на строке меню

• В списке команд выбрать пункт «Строка формул»

Формулы

Формулы могут содержать:

• Числа
• Математические операторы
• Ссылки на ячейки
• Функции

Категории формул:

• Математические
• Логические

=

Правила построения формул

Любая формула состоит из нескольких основных частей:

• Знак равенства (=) , с которого обязательно начинается любая формула. Если знак равенства отсутствует, Excel интерпретирует данные не как формулу, а как ввод данных в ячейку.
• Значение или ссылка на ячейки , с которыми выполняются расчеты.
• Операторы (это условное обозначение, определяющее действия со значениями).

+

/

Ввод формулы

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

• Выделить ячейку, где будет находится формула
• Ввести в данную ячейку знак « = »

Ввод формулы

• После знака «=» ввести нужные значения (или ссылки на ячейки) и операторы

Ввод формулы

• После создания формулы нажать клавишу «Enter» (для перехода вниз по столбцу) или клавишу «Tab» (для перехода вправо по строке)

Операторы Excel

В Microsoft Excel включено четыре вида операторов:

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

Обозначение

Оператор

+

-

Выражение

Сложение

*

Результат

Вычитание

=5+3

/

8

=6-4

Умножение

^

Деление

=5*4

2

20

=24/6

%

Возведение в степень

4

=5^3

Процент

125

=60%

0.6

Выражение Результат =8=12 Больше ЛОЖЬ Меньше =243 = ИСТИНА =15Не меньше Не больше ЛОЖЬ =10=10 ИСТИНА =6Не рано ЛОЖЬ =135 ИСТИНА " width="640"

Операторы Excel

• Операторы сравнения – используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА , либо ЛОЖЬ .

Обозначение

=

Оператор

Равно

Выражение

Результат

=8=12

Больше

ЛОЖЬ

Меньше

=243

=

ИСТИНА

=15

Не меньше

Не больше

ЛОЖЬ

=10=10

ИСТИНА

=6

Не рано

ЛОЖЬ

=135

ИСТИНА

:

;

Операторы Excel

&

• Текстовый оператор «амперсанд» – используется для объединения нескольких текстовых строк в одну строку. Результат выполнения выражения «Информационные» & «системы» будет строковое значение «Информационные системы»

• Операторы ссылок используются для определения диапазонов, участвующих в вычислениях.

• Двоеточие , используемое для определения диапазона, называется оператором диапазона (например A1:D7)
• Точка с запятой – оператор объединения. Он объединяет не менее двух ссылок на несмежные ячейки или диапазоны (например D8;E11 или A17:D17;E15:L15)
• Пробел – оператор пересечения, которая ссылается на общие ячейки диапазонов (например D2:D13 C12:H12 ссылаются на D12 так как она общая для этих двух диапазонов)

Р е д а к т и р о в а н и е ф о р м у л

Введенную формулу можно в любой момент отредактировать. Для этого просто нужно:

• выделить ячейку с формулой

• щелкнуть по строке формул

• ввести нужные изменения

Р е д а к т и р о в а н и е ф о р м у л

Закончив редактирование, нажать клавишу Enter . Excel выполнит расчеты по формуле и отобразит в ячейке новый результат.

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

А$1

$A$1

А1

$A1

Типы адресаций

Адресация – адрес ячейки или диапазона ячеек на табличном поле, в котором содержаться значения.

Существует три типа адресации:

Относительная

Абсолютная

Смешанная

=A1

Относительная адресация

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

При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.

Данный тип адресации используется по умолчанию.

$Е$13

Абсолютная адресация

Абсолютная ссылка в формулах Excel ссылается на ячейку, расположенную в определенном месте.

При перемещении ячейки с формулой адрес ячейки с абсолютной ссылкой не корректируется.

$A1

A$1

Смешанная адресация

Смешанная адресация содержит либо абсолютный столбец и относительную строку,

либо абсолютную строку и относительный столбец.

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

Что такое Функция

Функции – это заранее определенные формулы, которые выполняют вычисления по заданным величинам и в указанном порядке.

Excel содержит обширный список стандартных функций, призванных облегчить выполнение простых и сложных вычислений.

Ввод функций

• Выделяем ячейку, где будет располагаться функция

• Открывает меню Вставка на строке меню

• В списке команд выбираем пункт Функция

Ввод функций

• В окне Мастер функций – Шаг 1 из 2

в графе Категории открываем список существующих функций

и выбираем нужный тип функции. (например Дата и время)

Ввод функций

• В окошке Выберите функцию

выбираем нужную, на данный момент, функцию (например Дата )

и нажимаем кнопку Ok

Ввод функций

• Откроется окно Аргументы функции (для каждой функции – собственного вида), в котором в соответствующие графы вводим нужные аргументы для вычисления

• вводим нужные значения и адреса ячеек с строки аргументов выбранной функции

Ввод функций

• Закрываем окно кнопкой Ok

• В выбранной ячейке отобразиться результат вычисления функции

Категории функций

Все функции подразделены на категории. Каждая категория содержит функции, которые решают определенные задачи:

10 недавно использовавшихся

Полный алфавитный перечень

Дата и время

Финансовые

Математические

Статистические

Ссылки и массивы

Работа с базой данных

Текстовые

Логические

Проверка свойств и значений

Категории функции

10 недавно использовавшихся

Эта категория выводит список 10 функций, применявшихся последними

Категории функции

Полный алфавитный перечень

Эта категория выводит список всех функций

Категории функции

Дата и время

Эта категория для операций прямого и обратного преобразования даты и время в текстовые строки

Категории функции

Финансовые

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

Категории функции

Математические

Эта категория предназначена для математических и тригонометрических функций

Категории функции

Статистические

Эта категория функции для расчета среднего значения, дисперсии, статистических распределений и других вероятных характеристик

Категории функции

Ссылки и массивы

Эта категория для операций преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel

Категории функции

Работа с базой данных

Эта категория для формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных

Категории функции

Текстовые

Эта категория для работы с текстовыми строками

Категории функции

Логические

Эта категория имеет шесть функций для работы с данными логического типа, то есть величинами или условиями, принимающими значение ИСТИНА или ЛОЖЬ

Категории функции

Проверка свойств и значений

Эта категория для проверки типа данных аргумента, режима форматирования ячейки, типа сгенерированной ошибки и других специальных условий