Урок  Адресация в электронных таблицах.

 

Вставка формул в ячейку

 

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

 

 

 

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

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

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

 

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

 

В Excel формула может использовать значения в ячейках  для выполнения таких операций как сложение (+), вычитание (-), умножение (*), деление (/).

Например, формула =А1+В2 обеспечивает сложение чи­сел, хранящихся в ячейках А1 и В2, а формула =А1*5 — умножение числа, хранящегося в ячейке А1, на 5.

Если формула использует не ссылки на ячейки, а константы (например =30+70+110), результат изменится только при изменении самой формулы.

 

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

Всякий раз, когда меняется ячейка, на которую ссылается формула, по умолчанию зависимая ячейка также меняется. Например, если значение одной из следующих ячеек меняется, результат формулы =B2+C2+D2 также изменится.

ВНИМАНИЕ!  При изменении исходных значений, входящих в формулу, результат пересчитывается немедленно.

 

ВНИМАНИЕ!  При вводе формулы в ячейке отображается не сама формула, а результат вычислений по этой формуле.

 

Чтобы  увидеть формулы, необходимо выполнить команду «Формулы / Зависимости формул».

 

 

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

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

 

I. Относительные ссылки в формулах используются для указания адреса ячейки, вычисляемого относительно ячейки, в которой находится формула. Относительные ссылки имеют следующий вид: А1, ВЗ и тому подобное. По умолчанию при наборе формул в  Ехсеl используются относительные ссылки.

 

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

 

Например, при копировании формулы, содержащей относи­тельные ссылки, из ячейки С1 в ячейку В2 обозначения столбцов и строк в формуле изменятся на один шаг вправо и вниз:

 

  A B C D E
1    

=А1*В1

   
2    

 

=В2*С2

 

 

 

II. Абсолютные ссылки в формулах используются для указания фиксированного адреса ячейки (т.е. адреса ячейки, вычисляемого в абсолютной системе координат и не зависящего от текущей ячейки). В абсолютных ссылках перед неизменяемыми значениями адреса ячейки ставится знак доллара. Абсолютные ссылки имеют следующий вид: $А$1, $В$1 и тому подобное.

 

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

Например, при копировании формулы, содержащей абсолютные ссылки, из ячейки СЗ в ячейку В4 обозначения столбцов и строк в формуле не изменятся:

 

  A B C D E
1    

=$А$1*$В$1

   
2    

 

=$А$1*$В$1

 

 

III. Часто применяют не чисто относительные или абсолютные ссылки, а смешанные ссылки, например, С$1 или $С1.

Если символ доллара стоит перед буквой (например $А1), то координата столбца абсолютная, а строки — относитель­ная. Если символ доллара стоит перед числом (например, А$1), то, наоборот, координата столбца относительная, а строки — абсолютная.

 

При копировании или перемещении формулы, часть ссылки, не содержащая знак "$", будет обновляться, а другая часть, со знаком "$" останется без изменения.

Например, если формулу «=A$1*5» из ячейки C1 скопировать в ячейку C2, то формула не изменится, так как положение строки зафиксировано. А если эту же формулу из ячейки C1 скопировать в ячейку D1, то формула изменится на «=B$1*5». 

  A B C D E
1    

=А$1*5

=B$1*5

 
2    

=A$1*5

 

 
 

ВОПРОС. Что произойдет, если    1) формулу «=$A1*5» из ячейки C1 скопировать в ячейку C2;

                                                                    2) формулу «=$A1*5» из ячейки C1 скопировать в ячейку D1 

  A B C D E
1    

=$A1*5

?

 
2    

?

 

 

 

 

 

ЗНАЧЕНИЯ ОШИБОК

# ДЕЛ/0! – в формуле задано деление на 0 или на значение   пустой ячейки

#ИМЯ? – программа не может распознать введенное имя ячейки или диапазона, или имя функции введено с ошибкой

#ЧИСЛО! – ввод неправильного числа в формулу

#ССЫЛКА! – в формуле задана неверная ссылка на ячейку

#ЗНАЧ! – в формуле используется оператор или аргумент неправильного типа

#ПУСТО! – задано пересечение двух областей, не имеющих общих ячеек