Формулы и вычисления. Синтаксис формул.
Формула - это математическое выражение, состоящее из операндов, соединенных специальными символами, называемыми операторами.
Формула в Excel обязательно должна начинаться со знака « = », а иначе введенные данные будут восприниматься как текст.
Выражения, входящие в формулу, могут быть арифметическими, логическими и строковыми (текстовыми строками).
Существует несколько типов аргументов, используемых в формулах:
- числовые константы (числа);
- имена клеток таблицы;
- табличные функции (т.е. встроенные формулы, которые получают в качестве аргументов ряд значений, преобразуют их заданным образом и возвращают результат операции).
Операторы. В процессоре Excel используются два типа операторов: арифметические операторы и логические операторы. Допускается использование следующих арифметических операторов:
+ сложение; - вычитание;
* умножение; / деление;
% проценты; ^ возведение в степень.
При отсутствии скобок арифметические операторы вычисляются в соответствии с алгебраической очередностью. Сначала выполняется возведение в степень, а затем умножение, деление, нахождение процентов, сложение и вычитание. Использование круглых скобок изменяет приоритет операторов.
Логические операторы служат для формирования значений ячеек и вычисления логических выражений. Имеются следующие операторы сравнения:
= равно; <> неравно;
< меньше; > больше;
<= меньше или равно; = > больше или равно.
Для текстовых данных используется оператор строки &(сцепление строк). Если текстовая строка является операндом в выражении, то она должна быть заключена в двойные кавычки, например: "это - строка".
Следует помнить, что хотя в ячейке записана формула, но в таблице в ячейке мы видим значение — результат вычисления по формуле (если не установлен режим вывода формул), а сама формула отображается в строке формул, если сделать эту ячейку активной.
Вычислите произведение двух чисел, для этого выполните следующие действия:
1. Перейдите на Лист 6.
2. Введите в ячейку А1 число 2.
3. Введите в ячейку В1 число 10.
4. В ячейку С1 введите формулу «=А1*В1», для этого»:
- наберите на клавиатуре символ « = », он появится в строке формул;
- щелкните мышью на ячейке А1, она будет обрамлена мерцающей рамкой, а ее адрес появится в строке формул;
- наберите на клавиатуре знак умножения « * »;
- щелкните мышью на ячейке В1, получится , и нажмите Enter, в ячейке С1 появится результат вычислений «20»
5. Исправьте содержимое ячейки С1 «=A1-B1» .
6. Посмотрите, как изменится содержимое ячейки C1 после нажатия клавиши ENTER, результат «-8».
7. Измените значение ячейки В1 на число 20. В ячейке C1 появится число - 18 .
Если значение формулы не может быть вычислено, то в ячейке появится сообщение об ошибке, начинающееся со знака #, например:
#ДЕЛ/0!— деление на ноль,
#ИМЯ? - неверное имя функции или области,
#ЗНАЧ! - недопустимый тип аргумента или операнда,
###### — размер ячейки недостаточен для размещения числа или результата, необходимо увеличить ширину столбца.
Относительные и абсолютные ссылки
Как известно, одним из возможных операндов формулы могут быть адреса ячеек - ссылки. Ссылки бывают относительные и абсолютные. Относительная ссылка— это обычный адрес ячейки, например: В2, СЗ, F5. Если скопировать формулу с относительной ссылкой, то относительные ссылки в ней изменятся в соответствии с новым местоположением ячейки, а именно, в относительных адресах происходит смещение на величину переноса. Таким образом, относительные ссылки при копировании формулы изменяются.
Однако иногда бывает необходимо, чтобы адрес ячейки в копируемой формуле не изменялся, т.е. нужно зафиксировать его. Для того, чтобы управлять изменением ссылок при копировании, вводится понятие абсолютной ссылки. Абсолютная ссылка на ячейку, например C5, может иметь вид $C5, C$5, $C$5. При копировании будут изменяться только те атрибуты адреса, перед которыми не стоит символ «$». Ссылка типа $С$5 при копировании остается неизменной.
Обратите внимание! Существует простой способ получения любого из вариантов абсолютной ссылки. Для этого используется функциональная клавиша F4, нажатие да которую приводит к автоматическому изменению адресации, если ссылка на ячейку находится в строке формул.
Для использование абсолютных ссылок выполните следующие действия:
Имеется список сотрудников и должностных окладов. Каждому необходимо начислить премию в размере 20% оклада, имея в виду, что процент премии может измениться, и тогда потребуется перерасчет.
1. Перейдите на Лист 7 , создайте таблицу и заполните ее исходными данными как на рис. 1.2.11.
Рис. 1.2.11. Таблица с исходными данными
2. Вставьте перед ячейкой А1 «Фамилия» три пустые строки. Для этого выделите мышью на вертикальной адресной полосе номера строк 1, 2 и 3. Не уводя курсор с адресной полосы, нажмите правую кнопку мыши, появится контекстное меню, и выберите пункт Вставить.
3. Вставьте один новый столбец слева от столбца В «Фамилия». Для этого на горизонтальной адресной полосе выделите столбец А, нажмите правую кнопку мыши и выберите пункт Вставить.
4. Выполните Автозаполнение диапазона ячеек A5:A8 порядковыми номерами.
5. В ячейку B1 поместите текст: «% премии». В ячейку C1 поместите число 20%.
Получится таблица, как на рис. 1.2.12.
Рис. 1.2.12. Данные для расчета премии
!Значение процента премии задается в виде константы, записанной в ячейке С1. Так как эта константа неизменна для всех строк таблицы, значит, она не должна меняться при копировании формулы. Поэтому ссылка на ячейку С1 должна быть абсолютной, в данном примере записываемой в виде $C$1.
6. В ячейку D5 введите формулу «=С5*С1». Для получения абсолютной ссылки $C$1нажмите F4. Формула изменит вид на «=С5*$C$1». Нажмите Enter.
7. Выделите ячейку D5 и скопируйте ее в диапазон ячеек D6:D8 с помощью маркера заполнения.
Результат вычислений представлен на рис.1.2.13.
Рис. 1.2.13. Пример использования абсолютных ссылок
Для того чтобы увидеть в ячейках расчетные формулы (рис.1.2.14), необходимо перейти в режим отображения формул, для этого:
8. Выберите вкладку Формулы → панель Зависимости формул → кнопку .
Рис. 1.2.14. Режим отображения формул
9. Отмените режим отображения формул.
10. В ячейку С1 введите новое значение процента премии, например 25%, и проанализируйте, как изменился размер премии.
Самостоятельно
1. Перейдите на лист 8, перенесите на него таблицу, как на рис 1.2.15.
2. Вместо «?» знаков произведите расчет по формулам, используя относительные ссылки.
Рис. 1.2.15. Таблица для расчета самостоятельного задания
3. Допишите в столбец В еще шесть фамилий, а в столбец С - размеры окладов.
! «Подоходный налог» рассчитывается от «Всего начислено»,
! Все остальные значения рассчитываются от «Оклада».
! «Итого» - разность между «Всего начислено» и «Подоходный налог».
! «Всего начислено»- посчитайте сумму нужных показателей.