6.2.7. Использование и создание формул

Вычисления в таблицах программы Excel осуществляются при помощи формул. Программа рассматривает содержимое ячейки как формулу, если оно начинается со знака равенства (=). Формула может содержать знаки математических операций, пары круглых скобок, числовые константы, ссылки на ячейки, а также функции рабочего листа, вводимые в формулу командой Вставка, Функция или нажатием кнопки Мастер функций. Скобки позволяют изменять стандартный порядок выполнения действий. Если ячейка содержит формулу, то в рабочем листе отображается текущий результат вычисления этой формулы. Если сделать ячейку текущей, то сама формула отображается в строке формул.

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

Ссылку на ячейку можно задать разными способами:

1. адрес ячейки можно ввести вручную;

2. произвести щелчок на нужной ячейке или выбрать нужный диапазон. Ячейка или диапазон при этом выделяются пунктирной рамкой.

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

Формулы могут ссылаться на ячейки, находящиеся в других рабочих листах. Формат такой «межлистовой» ссылки: <имя листа>!<адрес>

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

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

Чтобы выбрать трехмерный диапазон, надо сначала выбрать группу рабочих листов (щелкая по ярлыкам листов, принажатой клавише SHIFT или CTRL), а затем диапазон ячеек на одном из рабочих листов этой группы. В формуле ссылка на трехмерный диапазон может выглядеть, например, так: Лист2:Лист3!В4:С15

Для редактирования формулы нужно дважды щелкнуть на соответствующей ячейке.

Операции в формулах.

Оператор Название

+ Сложение

- Вычитание

* Умножение

/ Деление

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

& Конкатенация

= Логическое сравнение

> Логическое сравнение на больше

< Логическое сравнение на меньше

Примеры формул:

=3*6 умножение двух чисел

=B4+C4 сложение содержимого двух ячеек

=A1^(1/3) возведение содержимого ячейки в степень 1/3

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

Абсолютная относительная и смешанная адресация ячеек и блоков. При обращении к ячейке можно использовать следующий способ В5, G9. Такая адресация называется относительной. При ее использовании в формулах Excel запоминает расположение относительно текущей ячейки. Так, например при вводе формулы =В1+В2 в ячейку В4 Excel интерпретирует формулу как прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше». Если скопировать формулу из ячейки В4 в С4 Excel также интерпретирует формулу как прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше». Таким образом, формула в ячейке С4 примет вид =С1+С2. Т.е. при перемещении формул все относительные ссылки перестраиваются так, чтобы в новом положении сохранились прежние связи.

Если при копировании формул необходимо сохранить ссылку на конкретную ячейку или область, то необходимо воспользоваться абсолютной адресацией. Для ее задания необходимо перед именем столбца и перед номером строки ввести символ $. Например: $B$4 или $C$2:$F$48.

Смешанная адресация. Символ $ ставится только там, где он необходим. Например: В$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой – нет.

Копирование формул. Существует два способа кодирования:

1. С помощью команд Копировать и Вставить из меню Правка.

2. С использованием маркера заполнения выделенной ячейки, который протаскивается вниз (метод автозаполнения). (Маркер заполнения – черный квадрат в нижнем правом углу выделенной ячейки или диапазона ячеек.)

Работа с именами диапазонов. Использование в формулах имен ячеек и блоков вместо адресов имеет преимущество в наглядности формул. Присвоить имя ячейке или блоку можно следующими способами:

1. Выделить ячейку или блок, щелкнуть мышью в поле имени (слева от строки ввода) и набрать в этом поле нужное имя. Ввод имени завершается нажатием Enter.

2. Выделить ячейку или блок, выбрать пункт меню Вставка, Имя, Присвоить. Появляется диалоговое окно. В нижнем поле ввода – абсолютная ссылка на ячейку или блок. Эта ссылка предварена именем рабочего листа, например =Лист1!S14.

3. Если имена для ячеек уже присутствуют на рабочем листе как текстовые строки в ячейках, например, слева от ячеек, подлежащих именованию, то нужно выделить блок, в который входят ячейки с текстовыми строками и ячейки, которым надо дать имена. В меню выбрать Вставка, Имя, Создать. Excelспрашивает, как расположены ячейки с будущими именами – слева (сверху, снизу, справа), и сам предлагает (проставляет флажок в диалоговом окне).

Имена не должны содержать пробелов (пробелы рекомендуется заменять символом подчеркивания), не должны совпадать с адресами ячеек (недопустимо, например, имя В2). Имена «Налог» и «налог» для него совпадают.

Выделить ячейку или блок по имени можно так: нажать кнопку выпадающего списка, расположенную справа от поля имени, и щелкнуть мышью по нужному имени в списке.

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

Использование мастера функций. Вызов осуществляется нажатием кнопки f_x , расположенной в левой части строки формул или по команде Вставка, Функция:

• На первом шаге в списке Категория выбирается категория, к которой относится функция (если определить категорию затруднительно, используют пункт Полный алфавитный перечень), а в списке Функция – конкретная функция данной категории. После щелчка на кнопке ОК имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров. Текстовый курсор устанавливается между этими скобками.

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

Для единственной функции суммирования (СУММ) предусмотрена отдельная кнопка на стандартной панели инструментов (кнопка Автосумма). После нажатия этой кнопки диапазон суммирования, выбираемый автоматически, включает ячейки с данными, расположенные над текущей ячейкой (предпочтительнее) или слева от нее и образующие непрерывный блок. При неоднозначности выбора используется диапазон, непосредственно примыкающий к текущей ячейке.

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