Редактор электронных таблиц MS Excel.
Занятие №3.
Ссылки и формулы.

Формулы. Относительные и абсолютные ссылки. Функции. Ошибки.

Формулы

Если вы хотите вставить в Excel автоматически вычисляемое поле, начните вводить информацию со знака “=”, либо нажмите кнопку “Изменить формулу” в строке формул. Например, если вы введете содержимое ячейки “1+2”, то в ячейке увидите просто “1+2”; если же вы введете “=1+2”, то в ячейке вы увидите “3” (и на печати получите 3), в строке ввода вы будете видеть “=1+2”, чтобы знать, по какой формуле вычислено значение. Если содержимое ячейки начинается со знака =, его называют формулой.

Ссылки.

Очень часто в таблице содержимое одной ячейки зависит от содержимого других ячеек. Для этого используют ссылки. Например, если содержимое ячейки b1 должно быть равно содержимому ячейки a1, просто напишите в ячейке b1 “=a1”. Если вы захотите изменить содержимое ячейки a1, содержимое ячейки b1 заменится автоматически. Чтобы вставить ссылку в вашу формулу, можно просто в процессе ввода формулы кликнуть мышкой по нужной вам ячейке.

Ссылки бывают абсолютные и относительные. Поймем на примере, что это такое. Когда вы пишите в ячейке b2 формулу “=a1”, на самом деле Excel понимает это так: взять значение из клетки, которая находится на одну строчку выше и на один столбец левее. Если мы скопируем информацию из ячейки b2 и вставим в ячейку с8, то в ячейке c8 значение будет равно числу в ячейке b7 (Excel подумает, что вам нужна не клетка a1, а клетка на одну выше и на одну левее, чем c8, т.е. b7). Такие ссылки называются относительными (Excel помнит не название ячейки, а ее относительное расположение). На самом деле это бывает очень удобно. Например, в столбике L вы хотите посчитать сумму чисел из столбиков B:K. Вы должны только в ячейке L1 посчитать сумму в ячейках b1:k1, а потом просто скопировать значение на все нижние ячейки столбика L (заполнить вниз).

Но иногда все-таки нужно ссылаться именно на конкретную ячейку. (Нужна абсолютная ссылка). Тогда вместо ”a1” (что создало бы относительную ссылку), напишите “$a$1”. Теперь Excel будет помнить именно название ячейки, а не ее относительное расположение.

Примечание: Если надо зафиксировать только строку, а для столбца оставить относительное расположение, поставьте знак $ только возле названия строки (т.е. напишите “a$1”). Это называется смешанная адресация.

Быстрый ввод: чтобы преобразовать ссылку из относительной в абсолютную, встаньте на ссылку в строке ввода и нажмите кнопку F4.

Чтобы сослаться на ячейку, которая находится на другом листе надо использовать формат Лист!а1. (т.е. перед адресом ячейки поставить название листа и !) (Проще - в процессе ввода формулы переключиться на другой лист и кликнуть по нужной ячейке).

Функции.

Функция - выражение вида f(x), где f - собственно функция, а x - ее аргумент. В Excel’е в качестве аргумента может выступать число, ссылка на ячейку, ссылка на диапазон ячеек, ссылка на несколько ячеек, и т.д. У функции может не быть аргумента. Функции используются для облегчения рассчетов.

Например, можно написать в ячейке “=a1+cos(0)+sin(2*b3)”. Здесь a1 и b3 - ссылки на ячейки таблицы, sin и cos - функции, 0, 2 - просто числа. Т.е. вы можете использовать функцию в составе более сложного выражения. Кроме того, вы можете в качестве аргумента использовать число, ссылку, или более сложное выражение (даже выражение, содержащее функции). У функции обязательно должны быть скобки, даже если у нее нет аргументов. Например, есть функция ПИ(). Она возвращает 15 знаков числа p . У нее нет аргументов, но скобки к ней приписать все равно надо.

Как вставить функцию в формулу?

Очень просто: набираете формулу (т.е. что-то, что начинается со знака =), когда вам понадобилась функция, используете команду Меню=>Вставка=>Функция. Выберите необходимую вам функцию. Быстрый способ - выбрать функцию из списка, который возникает рядом со строкой ввода, как только вы начинаете вводить формулу. (В этом списке содержатся не все, а только часто используемые, функции.)

Некоторые полезные функции.

Например, формула “=СУММ(А1:А300)” подсчитает сумму чисел в трехстах ячейках диапазона А1:А300 (напомним, что для указания диапазона ячеек между именами ячеек ставится двоеточие, а для указания двух отдельных ячеек - точка с запятой).

Ошибки.

При использовании ссылок и формул возникают ошибки. Например, можно в ячейке а1 написать “=с1”, а в ячейке с1 написать “=а1+1”. Понятно, что так не бывает, Excel выдаст сообщение об ошибке. Но большинство сообщений об ошибках не выдается, а пишется прямо в ячейке. Вот самые распространенные из них:

Логические выражения.

Кроме числовых, текстовых, денежных, процентных и т.д., выражения бывают логические. Логические выражения имеют два значения: ЛОЖЬ или ИСТИНА. Например, “=2>1” - логическое выражение. Оно истинно. Т.е. в ячейке возникнет слово “ИСТИНА”. Например, “=а1>0” - логическое выражение. Оно истинно, если в ячейке а1 стоит положительное число. Если в ячейке а1 стоит отрицательное число или 0, то оно ложно. Значит в ячейке возникнет соответствующее слово. Оно автоматически изменится при изменении содержимого ячейки а1.

Логические функции.

Логические функции нужны для задания более сложных формул.

Например, проводится психологический тест. Результат теста такой: если человек набрал более 25 очков - он нормальный, меньше или равно 25 - ненормальный. Пусть результаты теста записаны в столбце D, а в столбце L будем писать вердикт. В ячейке L1 пишем ЕСЛИ(D1>25;”нормальный”;”ненормальный”). Заполняем вниз.

Практическое задание №3

1. В своей рабочей папке создайте папку Excel, если ее там нет. (На сервер заходить не обязательно).
2. Создайте новую книгу, в ней должен быть один лист (для тек, кто не будет выполнять задания на звездочку) или два листа (если вы будете выполнять задание на звездочку). Листы назвать “Табуляция” и “Звездочка” соответственно.
3. Не забудьте заполнить свойства документа - именно по ним определяется авторство.
4. Протабулируйте следующие функции с шагом 0,05 на отрезке [-2;2]
   1. f₁(x)=x²
   2. 
   3. 
   4. 
   5. 
   6. 
   7. f₇(x)=|x|^|x|;

   Объяснение: Т.е. вы должны составить таблицу, в столбике А числа от -2 до 2 с шагом 0.05. В столбике В значения функции f₁ при аргументе из столбика А, в столбике С значение функции f₂ и т.д. Все столбики обязательно подпишите. Для этого в Excel’е воспользуйтесь MicrosoftEquation. (Как в Word’е).

   Для самопроверки см. файл ex3.xls.

5. Главное задание: сохраните лист “Табуляция” до 6-го занятия. (Он вам понадобится).
6. Книгу заархивируйте и отправьте по почте (katpop@yandex.ru ). Размер письма не должен превышать 50 Kb (для тех, кто без звездочки), 100 Kb (для тех, кто со звездочкой). Письма больше 100 Kb получать не буду.
7. Задание на звездочку. В ячейке А1 указано число a, в ячейке В1 число b. Протабулируйте функцию на отрезке [a;b] на 1001 значениях. (Т.е. надо равномерно разбить отрезок, так чтобы, считая концы, получилось 1001 точек, и во всех этих точках посчитать значение функции f.) Для того, чтобы сдать, возьмите a=-2, b=2. Но при их изменении все должно автоматически измениться.