Вставка формул в ячейку
Формула представляет собой вычислительную процедуру, выполняемую 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 обозначения столбцов и строк в формуле изменятся на один шаг вправо и вниз:
II. Абсолютные ссылки в формулах используются для указания фиксированного адреса ячейки (т.е. адреса ячейки, вычисляемого в абсолютной системе координат и не зависящего от текущей ячейки). В абсолютных ссылках перед неизменяемыми значениями адреса ячейки ставится знак доллара. Абсолютные ссылки имеют следующий вид: $А$1, $В$1 и тому подобное.
При перемещении или копировании формулы абсолютные ссылки не изменяются.
Например, при копировании формулы, содержащей абсолютные ссылки, из ячейки СЗ в ячейку В4 обозначения столбцов и строк в формуле не изменятся:
III. Часто применяют не чисто относительные или абсолютные ссылки, а смешанные ссылки, например, С$1 или $С1.
Если символ доллара стоит перед буквой (например $А1), то координата столбца абсолютная, а строки — относительная. Если символ доллара стоит перед числом (например, А$1), то, наоборот, координата столбца относительная, а строки — абсолютная.
При копировании или перемещении формулы, часть ссылки, не содержащая знак "$", будет обновляться, а другая часть, со знаком "$" останется без изменения.
Например, если формулу «=A$1*5» из ячейки C1 скопировать в ячейку C2, то формула не изменится, так как положение строки зафиксировано.
А если эту же формулу из ячейки C1 скопировать в ячейку D1, то формула изменится на «=B$1*5».
ВОПРОС. Как изменится формула, если
1) формулу «=$A1*5» из ячейки C1 скопировать в ячейку C2;
2) формулу «=$A1*5» из ячейки C1 скопировать в ячейку D1
ЗНАЧЕНИЯ ОШИБОК:
# ДЕЛ/0! – в формуле задано деление на 0 или на значение пустой ячейки
#ИМЯ? – программа не может распознать введенное имя ячейки или диапазона, или имя функции введено с ошибкой
#ЧИСЛО! – ввод неправильного числа в формулу
#ССЫЛКА! – в формуле задана неверная ссылка на ячейку
#ЗНАЧ! – в формуле используется оператор или аргумент неправильного типа
#ПУСТО! – задано пересечение двух областей, не имеющих общих ячеек
ДОМАШНЕЕ ЗАДАНИЕ:
1) учебник § 4.2.3
2) Решить задачи:
1. В ячейку Р13 ввели формулу =Р12/$В$4. Затем эту формулу скопировали в ячейку Р16. Какая формула содержится в ячейке Р16? 2. В ячейку В7 ввели формулу =(А6+А7)*$D$4. Затем эту формулу скопировали в ячейку F7. Какая формула содержится в ячейке F7? 3. В ячейку D5 ввели формулу =$А5+В$5. Затем эту формулу скопировали в ячейку E2. Какая формула содержится в ячейке Е2?