на предыдущую  на следующую

5.  Расчеты в Excel

5.1.  Формулы в электронных таблицах

Ввод формул

Содержимое ячейки воспринимается программой Excel как формула, если оно начинается со знака «=». Формула может содержать числовые константы, функции Excel и ссылки на ячейки. Ввод формулы заканчивается нажатием клавиши <Enter> или щелчком на кнопке Ввод в строке формул. В ячейке выводится результат вычисления, а при активизации ячейки в строке формул отображается введенная формула.

Примечание. Чтобы увидеть формулы в ячейках таблицы, нужно в диалоговом окне СервисПараметры на вкладке Вид в области Параметры окнаустановить флажок Формулы. Для возвращения к обычному виду ячеек необходимо сбросить этот флажок.

Правило использования формул в программе Excel состоит в том, что если вычисляемое значение зависит от других ячеек таблицы, то всегда следует использовать формулу со ссылками на эти ячейки. Ссылка задается указанием адреса ячейки. На рисунке 5.1 показан пример вычисления в ячейке С2 по формуле: = A2*B2

Рис. 5.1

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

1. ввести адрес ячейки с клавиатуры;
2. по ходу ввода формулы щелкать на нужной ячейке.

Второй способ является более быстрым и удобным.

Так для ввода указанной формулы, следует последовательно выполнить следующие действия:

1. активизировать ячейку С2;
2. ввести с клавиатуры знак "=";
3. щелкнуть в ячейке А2;
4. ввести с клавиатуры знак "_*";
5. щелкнуть в ячейке В2;
6. нажать <Enter>.

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

Копирование формул

Копирование формулы в смежные ячейки производится методом автозаполнения, т.е. протягиванием маркера заполнения ячейки с формулой на соседние ячейки (по столбцу или по строке). Это самый удобный и быстрый способ копирования.

Другие способы копирования формул:

1. выделить диапазон для заполнения (включая ячейку с введенной формулой) и выполнить команду меню ПравкаЗаполнитьВниз (если копирование выполняется по столбцу).
2. протянуть маркер заполнения ячейки с формулой правой кнопкой мыши, в появившемся контекстном меню выбрать нужную команду
   • копировать ячейки;
   • заполнить только значения.

Ссылки на адреса ячеек при копировании формулы автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемых копий (рисунок 5.2).

Рис. 5.2

Относительные и абсолютные ссылки

Корректировка ссылок при копировании ячеек выполняется по умолчанию. Такие ссылки называются относительными. При изменении позиции формулы изменяется и ссылка.

Если же необходимо, чтобы при копировании формулы ссылка на определенную ячейку оставалась неизменной, то такую ссылку следует определить как абсолютную. Для указания абсолютной адресации используется символ «$».

На рисунке 5.3 показан пример вычисления налога по формуле:

Налог = Стоимость * НДС

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

Рис. 5.3

Чтобы определить ссылку как абсолютную, нужно после щелчка на ячейке (в данном примере это ячейка – А2) нажать клавишу <F4>. Адрес ячейки в формуле автоматически дополнится символами «$»перед именем столбца и номером строки.

Смешанные ссылки

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

A$2 – фиксированная строка.

$A2 – фиксированный столбец;

Тип адресации (относительная, абсолютная, смешанные) меняется повторными нажатиями клавиши <F4>при вводе адреса ячейки в формулу или при редактировании формулы.

Имена ячеек для абсолютной адресации

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

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

Присвоение имени текущей ячейке (диапазону):

Первый способ:

1. щелкнуть в поле адреса строки формул, ввести имя;
2. нажать клавишу <Enter>.

Второй способ:

1. выполнить команду ВставкаИмяПрисвоить ;
2. в диалоговом окне ввести имя.

Это же диалоговое окно можно использовать для удаления имени, однако следует иметь в виду, что если имя уже использовалось в формулах, то его удаление вызовет ошибку (сообщение – «# имя?»).

Просмотр зависимостей

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

Команда меню СервисЗависимости формул позволяет увидеть на экране связь между ячейками.

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

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

Все зависимости в таблице изображаются стрелками. Для удаления стрелок служит команда СервисЗависимости формулУбрать все стрелки.

При необходимости просмотра многих зависимостей удобно отобразить панель инструментов Зависимости командой СервисЗависимости формулПанель зависимостей.

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

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

Изменение ссылки в формуле:

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

Изменение типа адресации:

• выделить адрес ячейки двойным щелчком;
• нажать клавишу <F2>.

Для подтверждения внесенных изменений использовать клавишу <Enter>или кнопку Ввод в строке формул; для отмены изменений – клавишу <Esc> или кнопку Отмена в строке формул.

Ссылки на другие рабочие листы

Копирование ячеек с формулами

При копировании числовых или текстовых данных с одного листа на другой используют буфер обмена. Если копируемая ячейка содержит формулу со ссылками на другие ячейки, то при выполнении операции вставки возникает ошибка (#ССЫЛКА), т.к. на новом листе ссылки оказываются неверными. При копировании зависимой ячейки возможны два варианта.

1. вставить на другой лист только числовое значение ячейки;
2. скопировать содержимое ячейки с учетом ссылок.

Копирование числового значения:

1. поместить содержимое ячейки (диапазона) в буфер обмена;
2. перейти на другой лист и выбрать команду Специальная вставка в меню Вставка (или в контекстном меню ячейки вставки);
3. установить переключатель Значение.

В этом случае на новом листе будет храниться только число, которое уже не зависит от содержимого ячейки листа-источника.

Копирование с сохранением зависимостей:

1. перейти на лист-адресат, активизировать ячейку вставки и ввести знак «=»;
2. щелкнуть на ярлыке листа-источника и выбрать ячейку, предназначенную для копирования, – ее адрес с именем листа отобразится в строке формул (например, =Лист1!F8;
3. нажать клавишу <Enter>.

При таком способе копирования изменение значения влияющих ячеек на листе-источнике влечет изменение скопированного значения на листе-адресате.

Формулы со ссылками на другие листы

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

Например:

=Лист1!$А$2*Лист2!В5

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

в начало

на предыдущую  на следующую

Copyright © 2010-2020
Ющик Е.В. All Rights Reserved

E-mail:
mailto:yuschikev@yandex.ru?subject=Письмо автору