Вычисления. Формулы и функции

Формулой в Excel называется введенная в ячейку последовательность символов, начинающаяся со знака равенства «=». В эту последовательность символов могут входить: константы, адреса ячеек, функции, операторы. Результатом работы формулы является вычисленное значение. Результат вычислений отображается в ячейке, а сама формула – в строке формул.

В Excel допустимы следующие операторы:

АРИФМЕТИЧЕСКИЕ ОПЕРАТОРЫ

Обозначения	Назначения
-	инвестирование (умножение на минус 1)
%	операция процента (применяется к отдельному числу)
^	возведение в степень
*, /	умножение, деление
+, -	сложение, вычитание

Операции выполняются слева направо в порядке их приоритетов, которые могут быть изменены круглыми скобками.

ТЕКСТОВЫЙ ОПЕРАТОР

& - сцепление двух строк в одну.

ОПЕРАТОРЫ ОТНОШЕНИЯ

=, <, >, <=, >=, <> используются как с числовыми, так и текстовыми данными.

Функции в Excel используются для выполнения стандартных вычислений. В Excel имеется более 400 встроенных функций, которые объединены в 9 групп:

1. Финансовые.

2. Дата и время.

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

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

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

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

7. Текстовые.

8. Логические.

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

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

ПРАВИЛА РАБОТЫ С ФОРМУЛАМИ И ФУНКЦИЯМИ

· Формулы начинаются знаком «=».

· Формулы могут включать обращение к одной или нескольким функциям.

· После имени каждой функции в () задаются аргументы. Если функция не использует аргументы, то за ее именем следуют пустые () без пробела между ними.

· Аргументы перечисляются через запятую.

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

· В формулах недопустимы пробелы.

· Функция может иметь не более 30 элементов.

· В формуле можно использовать знаки арифметических операций.

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

· Длина формулы не должна превышать 1024 элементов.

ПЕРЕМЕЩЕНИЕ И КОПИРОВАНИЕ ФОРМУЛ

После того, как формула введена ячейку, ее можно перенести или скопировать. При перемещении формулы в новое место таблицы ссылки в формуле не изменяются. При копировании формула перемещается в новое место таблицы, ссылки перенастраиваются на новые адреса. Необходимо определить способ автоматического изменения адресов входящих в эту формулу ссылок. Для этого вводятся понятия относительной и абсолютной ссылок. Абсолютная ссылка – адрес ячейки, не изменяющийся при копировании формулы. Относительная ссылка – адрес ячейки, автоматически изменяющийся при копировании формулы. Относительная ссылка записывается в обычной форме (F3, E7). Для указания абсолютной адресации вводится ее признак – символ $. Различают два типа абсолютной адресации: полная и частичная. Полная абсолютная адресация записывается в формуле, если при ее копировании в адресе ячейки, содержащей исходные данные, не меняются обе части: имя столбца и номер строки. Для указания такой ссылки в ее записи знак $ ставится перед именем столбца и перед номером строки ($A$6). Частичная абсолютная ссылка используется, когда при копировании формулы в этой ссылке меняется только какая-то одна часть: либо имя столбца, либо номер строки ($C8, F$9).

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

Правила копирования формул:

Ø Для ускорения заполнения электронной таблицы в ней надо определить ячейки, в которых будут находиться однотипные формулы.

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

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

Технология копирования формулы из ячейки в блок ячеек:

§ указать курсором ячейку, где введена исходная формула;

§ с помощью команды Копировать из раздела меню Правка скопировать эту формулу в буфер обмена;

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

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

Технология копирования формул из одного блока в другой:

§ выделить блок-оригинал, из которого надо скопировать введенные туда ранее формулы;

§ командой Копировать из раздела меню Правка скопировать формулы из выделенного блока в буфер обмена;

§ установить курсор на первую ячейку того блока, в который требуется скопировать формулы;

§ задать команду Вставить из раздела меню Правка.

ВЫЧИСЛЕНИЕ ЧАСТИ ФОРМУЛЫ

При поиске ошибок в составленной формуле бывает удобно посмотреть результат вычисления какой-то части формулы. Для этого:

1. Встать на ячейку, содержащую формулу.

2. В строке формул выделить часть формулы, которую нужно вычислить.

3. F9 – вычисление

Enter – результат вычисления вставить в формулу.

Esc – возврат формулы в исходное состояние.

МАСТЕР ФУНКЦИЙ

При вводе формулы обращение к встроенной функции можно написать вручную или вызвать Мастер функций: Вставка Функция или нажать пиктограмму f_x на панели инструментов, или нажать f_x на панели формул. В левом окне Категория нужно выбрать категорию функции, в правом Функция – саму функцию. Затем, нажав кнопку Далее, выполнить пошаговую подстановку аргументов. Посреди окна несколько строк для ввода аргументов, названия которых указаны слева от этих строк. Если название аргумента притушено, значит он необязателен. Значение аргумента можно записать вручную, а можно выделить на листе диапазон клеток. Кнопки f_x предназначены для создания вложенных функций. Справа вдавленный прямоугольник, в котором отображается текущее значение аргумента.

Автосуммирование доступно через кнопку ∑ на панели инструментов. С ее помощью можно очень просто реализовать функцию суммирования, практически не прикасаясь к клавиатуре.

Недопустим символ «пробел» в числе и в формуле. Длинные текстовые данные автоматически растекаются на соседние клетки, при условии, что эти клетки чистые. Формулы автоматически пересчитываются, как только хотя бы один из их аргументов изменен.

Дата добавления: 2014-12-07