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

Предпросмотр материала:

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

Ссылки и формулы.

1. В своей рабочей папке создайте папку Excel, если ее там нет.

2. Создайте новую книгу, в ней должен быть один лист (для тек, кто не будет выполнять задания на звездочку) или два листа (если вы будете выполнять задание на звездочку). Листы назвать “Табуляция” и “Звездочка” соответственно.

3. Не забудьте заполнить свойства документа - именно по ним определяется авторство.

4. Протабулируйте следующие функции с шагом 0,05 на отрезке [-2;2]

1. f₁(x)=x²

2. f₂(x)=

3. f3(x)=

4. f4(x)=

5. f5(x)=

6. f6(x)=

7. f₇(x)=|x|^|x|

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

Главное задание: сохраните лист “Табуляция” до 6-го занятия. (Он вам понадобится).

7. Книгу заархивируйте и отправьте по почте (………). Размер письма не должен превышать 50 Kb (для тех, кто без звездочки), 100 Kb (для тех, кто со звездочкой). Письма больше 100 Kb получать не буду.

8. Задание на звездочку. В ячейке А1 указано число a, в ячейке В1 число b. Протабулируйте функцию на отрезке [a;b] на 1001 значениях. (Т.е. надо равномерно разбить отрезок, так чтобы, считая концы, получилось 1001 точек, и во всех этих точках посчитать значение функции f.) Для того, чтобы сдать, возьмите a=-2, b=2. Но при их изменении все должно автоматически измениться.

Краткое описание материала

Ссылки и формулы.

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

Формулы

Если вы хотите вставить в 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 . У нее нет аргументов, но скобки к ней приписать все равно надо.

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

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

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

Функция(аргумент)

Описание

МАКС(список)

возвращает из списка максимальное число.

МИН(список)

возвращает минимальное значение списка аргументов.

СРЗНАЧ(список)

возвращает среднее арифметическое своих аргументов.

ЦЕЛОЕ(Х)

округляет аргумент до ближайшего меньшего целого.

СУММ(список)

возвращает сумму указанного списка.

ABS(X)

возвращает модуль (абсолютную величину) числа

EXP(X)

возвращает экспоненту заданного числа.

LN(X)

возвращает натуральный логарифм заданного числа.

LOG10(X)

возвращает десятичный логарифм заданного числа.

LOG(X;a)

возвращает логарифм заданного числа X, по заданному основанию а.

ПИ()

возвращает число p с точностью до 15-го знака.

SIN(X)

вычисляет синус угла Х, измеренного в радианах.

СЕГОДНЯ()

возвращает текущую дату.

ГОД(дата)

возвращает, преобразует дату в год.

МЕСЯЦ(дата)

возвращает номер месяца.

ДЕНЬ(дата)

преобразует дату в день месяца.

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

Ошибки.

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

Ошибка

В чем дело

#ДЕЛ/0!

попытка деления на ноль.

#ЗНАЧ!

недопустимый тип аргумента. Например, вместо числового аргумента используется текстовый.

#ИМЯ?

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

#Н/Д

неопределенные или отсутствующие данные ("нет данных")

#ПУСТО!

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

#ССЫЛКА!

недопустимая (обычно отсутствующая) ссылка

#ЧИСЛО!

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

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

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

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

ЕСЛИ(<условие>;x;y)

Если <условие> истинно, возвращает значение x, если ложно - значение y.

И(список логических выражений)

возвращает значение ИСТИНА, если все выражения истинны, если хотя бы одно из значений ложно - возвращает ЛОЖЬ.

ИЛИ(список логических выражений)

возвращает значение ИСТИНА, если хотя бы одно из выражений истинно, если все выражения ложны, возвращает ЛОЖЬ.

ИСТИНА()

Всегда возвращает значение ИСТИНА.

ЛОЖЬ()

Всегда возвращает значение ЛОЖЬ

НЕ(<логическое выражение>)

Возвращает значение ЛОЖЬ, если аргумент - ИСТИНА и возвращает ИСТИНА, если аргумент ЛОЖЬ.

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

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