Упражнения. 11.1. Определить, является ли число в некоторой ячейке положительным или отрицательным

11.1. Определить, является ли число в некоторой ячейке положительным или отрицательным. В зависимой ячейке поместить текст «положительное» или «отрицательное».

11.2. Составить формулу для вычисления функции у(х), определенной по следующему правилу:

11.3. Вычислить . Если у лежит в заданных пределах [a, b], в зависимой ячейке сформировать соответствующий текст.

11.4. По условиям контракта работнику установлена оплата с коэффициентом 1,25 тарифа за час в рабочий день и коэффициентом 2 за каждый час работы в выходной. Рассчитать дневную оплату при заданных дате, тарифе и количестве отработанных часов.

Работа 12. Адресация ячеек

В зависимости от выполняемых задач в Excel можно использовать либо относительные ссылки, определяющие положение ячейки относительно положения ячейки формулы, либо абсолютные ссылки, которые всегда указывают на конкретные ячейки. Если перед буквой или номером стоит знак доллара, например, $A$1, то ссылка на столбец или строку является абсолютной. Относительные ссылки автоматически корректируются при их копировании, а абсолютные ссылки — нет. Поясним сказанное на примерах.

Задание 12.1. Создать новую книгу с именем «Матрицы».

Пример 12.1. Относительные, абсолютные ссылки.

4 Листу 1 книги «Матрицы» дать имя «Ссылки».

4 В ячейку С3 поместим простенькую формулу, например, «=5*A1».

4 Включим режим отображения формул в ячейках: команда меню Сервис/Зависимости формул/Режим проверки формул.

Для возвращения в обычный режим отображения в ячейках результатов расчетов следует еще раз выполнить эту же команду.

4 Скопируем эту формулу в соседние ячейки по горизонтали: выделить ячейку С3 Þ установить курсор мыши на маркер заполнения Þ произвести копирование.

Видим, что в соседних ячейках появились формулы «=5*B1», «=5*С1» и т. д.

4 Скопируем формулу из ячейки С3 по вертикали и получим последовательность: «=5*A2», «=5*A3»…

Если мы хотим, чтобы при копировании по горизонтали адрес столбца не изменялся, то в формуле ссылку на столбец нужно сделать абсолютной, т.е. формула в ячейке С5 должна быть такой: =5*$A1 (опробовать этот вариант). Знак доллара перед соответствующей координатой означает, что она (координата) при копировании формулы изменяться не будет. Аналогично для того, чтобы при копировании по вертикали адрес строки не изменялся, ссылка на строку должна быть абсолютной: «=5*A$1» (опробовать и этот вариант).

Ну и для того, чтобы при любом копировании ссылка всегда была на одну и ту же ячейку (в нашем случае А1), формула должна быть «=5*$A$1».

Пример 12.2. Имеется таблица стоимости товара в условных единицах. Создать таблицу пересчета стоимости товара в рублях.

Создадим таблицу со следующими столбцами:

товар

цена 1 шт.; у.е.

количество, шт.

стоимость, у.е.

стоимость, руб

4 Поместим названия столбцов, начиная с ячейки В10.

4 Ввести данные (5 строк) в столбцы «товар», «цена», «количество».

4В ячейку Е11 ввести формулу расчета стоимости товара «=C11*D11».

4 Скопировать формулу из ячейки Е11 в ячейки Е12:Е15: выделить ячейку с копируемой формулой Е11 Þ установить курсор на маркер заполнения Þ протянуть маркер до ячейки Е15.

4 В ячейку В8 поместим текст: «1 у. е. =», а в ячейку С8 – число, являющееся текущим курсом 1 у. е.

4 В ячейку F11 ввести формулу расчета стоимости товара в рублях: «=E11*C8» Þ скопировать формулу из ячейки F11 в ячейки F12:F15.

Видим, что получилась ерунда (см. далее).

Обратим внимание на ссылки (адреса ячеек) в формулах в ячейках Е12, Е13 и т.д. Видим, что при копировании произошло изменение номеров строк по сравнению с исходной формулой в ячейке Е11 (но именно это нам и надо). Но при расчете стоимости в рублях необходимо, чтобы стоимость в у.е. всегда умножалась на одно и то же число – содержимое ячейки С8. Поэтому:

4 Подкорректируем формулу в ячейке F11: «=E11*$C$8» Þ скопируем формулу из ячейки F11 в ячейки F12:F15.

Теперь второй сомножитель – это всегда ссылка на одну и ту же ячейку: С8.

4 Изменяя содержимое ячейки С8 (курс рубля), сразу получаем пересчет стоимости товара.

Пример 12.3. Имеется прямоугольная матрица А размером 5´3 (5 строк, 3 столбца) и матрица-столбец В (5 строк). Необходимо построить матрицу С размером 5´3, элементы которой есть элементы матрицы А, сложенные с элементами соответствующих строк матрицы В, т.е. .

Пусть матрица А расположена в ячейках В23:D27, матрица В – в ячейках F23:F27, а матрица С – в ячейках H23:J27.

4 Заполнить произвольными числами матрицы А (В23:D27) и В (F23:F27) Þ в ячейку Н23 поместить формулу «=B23+F23» Þ скопировать формулу из ячейки Н23 в ячейки Н24:Н27 Þ убедиться, что сложение произошло правильно.

4Скопировать формулу из ячейки Н21 в ячейки I23:J23 Þ убедиться, что сложение произошло НЕПРАВИЛЬНО!

Нам надо, чтобы у второго слагаемого ссылка всегда была на столбец F. Можно, конечно, отдельно построить каждый столбец новой матрицы, но мы поступим более грамотно: ссылку на столбец F надо сделаем абсолютной. Итак:

4 Выделить ячейку Н23 Þ подкорректируем формулу: перед буквой F поставим знак доллара ($), т.е. ссылку на столбец F сделаем абсолютной.

4 Скопировать формулу из ячейки Н23 в ячейки I23:J23.

4 Скопировать строку матрицы H23:J23 в ячейки H24:J27: выделить ячейки H23:J23 Þ произвести копирование вниз (до ячеек H27:J27).

4 Убедиться, что матрица С построена правильно.

Дата добавления: 2014-12-27