Частичная абсолютная адресация в формулах
Материал для подготовки. Абсолютная ссылка может быть частичной (только по столбцу или строке). В этом случае знак $ стоит перед номером строки или именем столбца. Пример частичной абсолютной ссылки- D$12 (при копировании «замораживаются» номера строк), $B5 (при копировании «замораживаются» имена столбцов). Постановка задачи: Произвести расчет расходов материалов и денежных затрат при проведении покрасочных работ. Вычисляется расход материала для поверхности и общий расход каждого используемого материала. Кроме этого вычисляется стоимость материала, израсходованного на каждую поверхность, стоимость каждого используемого материала и стоимость ремонта в целом. Выделить итоговую стоимость ремонта, если она превышает 10 000 руб. Макет таблицы:
Математическая модель 1. <израсходовано на поверхность> = <расход материала на м2>*<площадь>; Первая формула записывается в E4 ==B4*D4. Копируется в E5:E6 (вниз) и H4: H6 (вправо). При копировании формулы вниз в ячейке E5 она примет вид =B5*D5. Однако площадь первой поверхности для всех материалов одна и располагается в ячейке D4, поэтому адрес этой ячейки должен быть «заморожен» по строкам (D$4), чтобы он не изменялся при копировании формулы вниз. Получим формулу для E4=B4*D$4. При копировании такой формулы вправо в ячейку H4 получим =E4*G$4. Так как вторая поверхность обозначена в ячейке G4, второй сомножитель полностью удовлетворяет вычислениям, а первый сомножитель даст неверный или ошибочный результат, т.к. он должен показывать расход материала, размещенного в ячейке B4. Поэтому, чтобы избежать в формуле неверных вычислений, надо «заморозить» адрес ячейки B4 по столбцам для его закрепления при копировании вправо. Получим окончательную формулу E4=$B4*D$4 2. <стоимость материала на ремонт поверхности> = <стоимость 1 кг>*<кол-во материала>; Первая формула записывается в F4 == C4*E4. Копируется в F5:F6 (вниз) и I4:I6 (вправо). Требуется учесть, что при копировании формулы вправо адрес ячейки C4 не должен меняться, т.к. в этой ячейке размещается стоимость 1 кг израсходованного материала. Получим окончательную формулу F4=$C4*E4 3. <кол-во материала на ремонт поверхностей> = < израсходовано на двери>+< израсходовано на подоконники>; Формула записывается в J4 = E4+H4. Копируется в J5:J6. 4. <стоимость на ремонт поверхностей>=<израсходовано на двери>+<израсходовано на подоконники>;.Формула записывается в K4 = F4+I4. Копируется в K5: K6. Выполнение работы. Заполните лист Ремонт согласно макету, мат. модели и пояснениям: 1. Основу макета составляет таблица на листе Вариант файла Лаб1. Поэтому можно скопировать эту таблицу на лист Ремонт текущего файла, после чего выполнить необходимые изменения. Технология копирования – использование буфера обмена. 2. Ввод формулы в ячейку E4, F4. Технология ввода формулы: знаки с клавиатуры, адреса ячеек щелчком мыши, абсолютная ссылка нажатием клавиши [F4] после ввода адреса. 3. Копирование формул. 4. Ввод формулы в ячейку J4. 5. Копирование J4 в J5:J6. 6. Копирование J4:J6 в K4: K6. 7. Вычисление в K7 общей стоимости проведенного ремонта суммированием стоимости отдельных материалов на все поверхности, т.е. K7=СУММ(K4:K6). 8. Выполните условное форматирование. Контрольные вопросы: 4. Как в формуле обозначается частичная абсолютная ссылка, как она изменяется при копировании/перемещении формулы? 5. В ячейке С1 записана формула =А1*В1. Откорректируйте формулу так, чтобы адрес А1 Ø не менялся при копировании формулы в нижние или верхние ячейки таблицы. Ø не менялся при копировании формулы в правые или левые ячейки таблицы.
|
