Формулы в электронных таблицах. Относительная и абсолютная адресация ячеек

Лекция 2. «Формулы в электронных таблицах. Относительная и абсолютная адресация ячеек»

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

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

Создать простую формулу можно с помощью констант и арифметических операторов. Например, формула =5+2*3 перемножает два числа и прибавляет к результату третье. В Microsoft Office Excel используется стандартный порядок вычисления математических операций. В предыдущем примере сначала выполняется операция умножения (2*3), а затем к результату прибавляется число 5.

Арифметические операторы

• «+» — сложение (Пример: «=1+1»);

• «-» — вычитание (Пример: «=1-1»);

• «*» — умножение (Пример: «=2*3»);

• «/» — Деление (Пример: «=1/3»);

• «^» — Возведение в степень (Пример: «=2^10»);

• «%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). Результатом вычисления любого арифметического выражения будет число.

Ход работы (первый лист «ввод формулы»)

Задание 1. Введите арифметическую формулу 5+7,5*2 в ячейку Е2.

1. В ячейке Е2 введите следующую формулу: =5+7,5*2

1. Нажмите клавишу Enter. В ячейке Е2 получилось значение 20.

Задание 2. Введите формулу 10*8-Е2*2+10 (с ссылкой на ячейку Е2).

1. В ячейке введите следующую формулу: =10*8 – Е2*2+10. Записывая в формулу адрес ячейки Е2 – Вы ссылаетесь на значение, которое находится в этой ячейке, в нашем случае, значение ячейки Е2=20. Чтобы сослаться на ячейку, достаточно просто кликнуть на неё, она подсветится цветной рамочкой, а запись её в формуле будет окрашена в тот же цвет.

1. Нажмите клавишу Enter. В ячейке Е7 получилось значение 50.

Задание 3. Введите логические формулы следующих выражений: 52 10

Значением логической формулы является ответ ИСТИНА, если условие выполняется, и ЛОЖЬ, если условие не выполняется.

Логические операторы

• "" — больше;

• "

• "=" — больше, либо равно;

• "

• "=" — равно (проверка на равенство);

• "" — неравно (проверка на неравенство).

1. Введите в ячейках следующие формулы:

=52

=10

= E2+30=E7

1. После введения формулы нажмите клавишу Enter. У Вас должны получиться следующие значения в ячейках:

Задание 4. Соедините два слова «коробка» и «конфет» из разных ячеек в одно выражение «коробка конфет», которое будет находиться в одной ячейке.

1. Введите в ячейке следующую формулу: =СЦЕПИТЬ(D17;” “;D19)

• где СЦЕПИТЬ – функция сцепления двух и более строк;

• D17 – ссылка на ячейку со значением «коробка»;

• « » - пробел, обрамленный в кавычках, т.к. в данном случае это строчный символ;

• D19 – ссылка на ячейку со значением «конфет».

1. Аналогичная формула может быть записана так: = D17&” “&D19

• где D17 и D19 ссылки на ячейки со словами;

• « » - пробел, обрамленный в кавычках, т.к. в данном случае это строчный символ;

• & - Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк.

1. В ячейке с введенными формулами должна получиться строка «коробка конфет».

З адание 5. Введите формулу: ,где заданы значения x и y

1. Введите в ячейке следующую формулу: =(E24^2-4*F24)/(2*КОРЕНЬ(100)-3)

где E24 и F24 – ссылки на значение ячеек х и y;

^ - возведение числа в степень;

КОРЕНЬ(число) – корень из числа.

1. После введения формулы нажмите клавишу Enter. У Вас должно получиться следующие значение в ячейке:

Задание 6. В коробке 20 конфет, масса брутто = 250 гр. Вес коробки составляет 20% от общей массы. Найдите вес одной конфеты.

1. Разберем решение задачи по действиям:

• Сначала найдем вес коробки: 250г:100*20=50, в excel это выражение можно записать следующим образом: 250*20%

• Далее найдем вес 20-ти конфет: 250-50=200

• И наконец, найдем вес одной конфеты: 200:20=10

1. В Excel в ячейке можно записать эти действия в одну формулу следующим образом:

1. После введения формулы нажмите клавишу Enter. У Вас должны получиться следующие значение в ячейке:

Ход работы (второй лист «сложные формулы»)

Задание 7. Заполните таблицу с использованием разных видов ссылок на ячейки.

1. Столбец F заполним формулами с относительными ссылками. Для этого в ячейке F3 введите следующую формулу: =Е3+1

Относительные ссылки. Относительная ссылка в формуле, в нашем случае Е3, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании формулы на нижние смежные ячейки, из ячейки F3 в ячейку F4, она автоматически изменяется с =Е3+1 на =Е4+1.

1. Далее заполняем столбец F до конца и также заполняем столбец G, для этого из ячейки F3 скопируйте формулу в ячейку G3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите как изменились формулы.

1. Столбец H заполним формулами с абсолютными ссылками. Для этого в ячейке H3 введите следующую формулу: =$E$3+1

Чтобы сделать ячейку абсолютной, нужно ее обрамить значками доллара, сделать это можно при помощи клавиши F4, предварительно поставив курсор перед адресом ссылки. Или проставив эти значки в ручную при помощи комбинации клавиш Shift+4 (на латинице).

Абсолютные ссылки. Абсолютная ссылка ячейки в формуле, в нашем случае, $Е$3, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Например, при копировании или заполнении абсолютной ссылки из ячейки Н3 в ячейку Н4 она остается прежней =$Е$3.

1. Далее заполняем столбец H до конца и также заполняем столбец I, для этого из ячейки H3 скопируйте формулу в ячейку I3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите изменились ли формулы.

1. Столбец J заполним формулами с смешанными ссылками. Для этого в ячейке J3 введите следующую формулу: =E$3+1

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т.д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т.д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Например, при копировании или заполнении смешанной ссылки из ячейки J3 в ячейку J4 она не изменяется с =E$3+1 на =E$4

1. Далее заполняем столбец J до конца и также заполняем столбец K, для этого из ячейки J3 скопируйте формулу в ячейку K3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите как меняются формулы.

Здесь ссылки в формуле меняются по столбцам, а по строкам нет, т.к. мы зафиксировали одну строку – третью, из ее excel и берет значения.

1. Столбец L заполним формулами с смешанными ссылками, но на этот раз закрепим столбец. Для этого в ячейке J3 введите следующую формулу: =$E3+1

1. Скопируйте формулу из ячейки L3 в ячейку L4, обратите внимание как изменилась ссылка в формуле:

1. Далее заполняем столбец L до конца и также заполняем столбец M, для этого из ячейки L3 скопируйте формулу в ячейку M3 и растяните ее за маркер заполнения до конца столбца. Покликайте по ячейкам, посмотрите как меняются формулы.

Здесь ссылки в формуле меняются по строкам, а по столбцам нет, т.к. мы зафиксировали один столбец – Е, из него excel и берет значения.

1. В итоге, у Вас должна получиться следующая таблица:

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

1. Итак, мы имеем таблицу вида:

1. В ячейку Н22 введем формулу: «всего порций» * «граммов на 1 порцию», при этом ячейку «всего порций» нам нужно сделать абсолютной, для этого обрамим ее символами $, формула имеет вид: =G22*$H$20

1. Скопируем (растянем) формулу в соседние нижние ячейки, получим:

1. Теперь при вводе значения «всего порций» в ячейку Н20, в ячейках «всего (г.)» считается необходимое количество продуктов для заданного количества порций.

Задание 9. Введите соответствующие формулы:

1. К сумме диапазона ячеек (Е22:Е31) прибавить 5 и разделить все на 3.

Введите в ячейку G22 следующую формулу: =(СУММ(E22:E31)+5)/3

• где СУММ(E22:E31) – функция суммирования, в данном случае диапазона ячеек,

• соблюдайте расстановку скобок.

1. Среднее значение диапазона ячеек (Е22:Е31) умножить на 10 корней из 4.

Введите в ячейке G24 следующую формулу: =СРЗНАЧ(E22:E31)*10*КОРЕНЬ(4)

• где СРЗНАЧ(E22:E31) – функция вычисления среднего значения из диапазона ячеек,

• КОРЕНЬ(4) – функция вычисления корня из числа.

1. Максимальное значение из диапазона ячеек (Е22:Е31) плюс 10.

Введите в ячейке G28 следующую формулу: =МАКС(E22:E31)+10

• где МАКС(E22:E31) – функция вычисления максимального значения из диапазона ячеек.

1. Сумма диапазона ячеек (Е22:Е31) минус среднее значение этого же диапазона, умножить на минимальное значение диапазона.

Введите в ячейке G31 следующую формулу:

=(СУММ(E22:E31)-СРЗНАЧ(E22:E31))*МИН(E22:E31)

• СУММ(E22:E31) – функция суммирования, в данном случае диапазона ячеек,

• СРЗНАЧ(E22:E31) – функция вычисления среднего значения из диапазона ячеек,

• МИН(E22:E31) – функция вычисления минимального значения из диапазона ячеек.

Получаем Следующие ответы:

Задание 10. Магазин "Ромашка" заказал 3 коробки шоколада "Аленка" и 4 коробки шоколада "Бабаевский". Расчетайте стоимость заказа.

Что бы посчитать стоимость заказа шоколада нужно количество коробок умножить на количество плиток в коробке и умножить на цену 1 плитки и прибавить стоимость доставки.

Для этого ведите в ячейке следующую формулу: =3*F36*F37+4*G36*G37+F38

Получим следующий ответ:

Задание 11. Посчитайте пример:

Для этого введите в ячейке Е47 следующую формулу:

=(3^2+4^2)/(100-КОРЕНЬ(36))+SIN(ПИ())/(4*3/5)

• КОРЕНЬ(36) – функция вычисления корня из числа,

• SIN() – функция вычисления синуса,

• ПИ() – функция, которая возвращает значение Пи=3,141593…

Получим следующий ответ: