Абсолютная и относительная адресация ячеек.

Цель работы - Ознакомление с особенностями адресации ячеек при создании формул в электронных таблицах Excel.

Общие сведения.

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

Пусть, например, ячейка B6 содержит формулу =A5; искомое значение находится на одну ячейку выше и левее ячейки B6. Адресация по методу "левее","правее","выше" называется относительной адресацией.

При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место, ссылки будут указывать на другие ячейки. Если формулу из ячейки B6 скопировать в ячейку B7, в ячейке B7 будет содержаться формула =A6, которая ссылается на одну ячейку выше и левее ячейки B7.

По умолчанию Excel рассматривает адреса ячеек как относительные, это позволяет копировать формулы методом заполнения (используя маркер заполнения) нужного диапазона. Благодаря относительной адресации формулы будут правильными для всех его ячеек.

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

Таким образом , ссылка на ячейку возможна в 4-х вариантах:

1. А1 (при копировании меняется обозначение столбца и строки)

2. $А1 (при копировании меняется обозначение строки

3. А$1 (при копировании меняется обозначение столбца)

4. $А$1 (при копировании обозначение столбца и строки не меняются)

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

Задание №1

Создать ЭТ для подсчета суммы депозитного вклада в банке через n-ое количество лет, для различных значений годовых процентов по вкладу. При этом Вы хотите знать сумму вклада например, через каждый год, а диапазон предлагаемых банками процентов находится в пределах 10% -21%

Исходные данные

Сумма вклада ( грн. )

Минимальное количество лет, на которое размещается вклад

Минимальный годовой процент по вкладу (%)

Шаг изменения по годам.

Шаг изменения годовых %

Подсчет реальной суммы (реальной стоимости денег) по прошествию n лет осуществляется по формуле:

Р = S*(1+%)^n ,

где S - начальная сумма вклада,

% - ставка по вкладу,

n - количество лет

Вид ЭТ и требуемые для вычислений формулы приведены на рисунке.

Порядок выполнения работы:

1. Заполните ячейки В5: D10 исходными данными

2. В ячейки В16 и С16 введите формулы. Для получения абсолютных адресов воспользуйтесь клавишей F4.

3. Используя маркер заполнения, скопируйте формулу из ячейки С16 в диапазон ячеек D16:K16.

4. Введите формулы в ячейки А17, А18

5. Используя маркер заполнения, скопируйте формулу из ячейки А18 в диапазон ячеек А19:А28.

6. Вводите формулу =$B$6 * (1+$A17) ^B$16 в ячейку В17.

7. Используя маркер заполнения, скопируйте формулу из ячейки В17 в диапазон ячеек
С17:К17.

8. Этот диапазон останется выделенным, а в его правом нижнем углу есть маркер заполнения.

9. Подведите к нему указатель мыши и , нажав ее левую кнопку, перетащите его до ячейки К28. Все формулы диапазона В17:K17 скопируются на диапазон . В18:K28.

10. Просмотрите выборочно несколько ячеек диапазона . В17:K28. Объясните, почему формулы имеют именно такой вид.

11. Проанализируйте полученные результаты.

12. Измените исходные данные, и проследите за изменениями в таблице.

13. Отформатируйте таблицу, приготовьте ее для печати.

Задание для самостоятельной работы.

Составьте таблицу всех значений функции y = kx + b , где k и b любые числа, если х изменяется в диапазоне от -10 до 10 с шагом 0,5.

Лабораторная работа №3.

Программирование в ЭТ.

Разветвляющиеся и циклические алгоритмы. Функция ЕСЛИ.

Цель работы - Изучение принципов построения разветвляющихся и циклических алгоритмов
в электронных таблицах Excel.

Общие сведения.

Разветвляющиеся алгоритмы в ЭТ Excelреализуются с помощью функции ЕСЛИ. Принцип действия функции приведен на рисунке.

Пример: сравнение двух чисел

Условие: Если два числа в ячейках C27=C28равны, в ячейке, содержащей функцию ЕСЛИдолжно появиться слово"равны",если числа не равны - слово"не равны"

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

Задание№1

Рассчитать заработную плату сотрудников фирмы, если известны:

1. объем выработки каждым рабочим в штуках
(в предположении, что все работники фирмы производят продукцию одного
вида)

2. норму выработки в штуках

3. расценки за изготовление единицы продукции

Начисленная заработная плата зависит от фактической выработки:

a) если выработка рабочего не превышает норму, зарплата определяется
произведением цены на количество выработанных изделий.

b) если выработка превышает норму, за изделия,
изготовленные сверх нормы, расценки повышаются на 30%.

Задан также срок выплаты зарплаты. Реально выплачиваемая зарплата зависит от срока ее выплаты:

a) если выплата зарплаты произведена не более, чем на 20 дней
после определенного срока, выплачиваемая зарплата равна расчетной;

b) при задержке выплаты свыше 20 дней заработная плата увеличивается
на 0,5% за каждый день (свыше 20 дней) просрочки.

Реальная заработная плата должна быть определена на день выплаты .

Порядок выполнения работы:

1. Текст заголовка (до слов «на 5.11.98») введите в ячейку В1.
По умолчанию ширина столбца составляет 8,43 символа, а текст первой строки заголовка (с учетом пробелов) содержит 47 символов, следовательно, он не сможет быть отображен в одной ячейке. Однако, если ячейки справа от В1 будут пустыми, то весь текст первой строки заголовка будет виден на экране.

2. Вторая строка заголовка состоит из данных разного типа: «на» - текстовое значение и «5.11.98» - дата. Поэтому их следует ввести в разные ячейки таблицы - соответственно в С2 и D2. Если дату вводить в формате ЧЧ.ММ.ГГ (т.е. в данном случае как 05.11.98 ), то Ехсе1 воспримет значение как дату. В строке формул появится «05.11.98». Но при выполнении вычислений с этой ячейкой, в расчетах будет участвовать целое число , которое обозначает количество дней между базовой датой (1 января 1900 г.) и введенной датой. Это число можно увидеть в ячейке С2, если установить числовой формат данных ячейки, выделить ее и выполнить команду Формат / Ячейки, а затем в открывшемся диалоговом окне Формат ячеек на вкладке Число в списке Числовые форматы щелкнуть Числовой, а в поле Число десятичных знаков щелкнуть «0» (это количество знаков дробной части числа), далее «ОК». .

3. Введите шапку таблицы в ячейки А4:Н4. Так как заголовки некоторых столбцов таблицы состоят из двух или трех строк, укажите режим ввода текста с переносом на следующие строки. Для этого сначала выделим диапазон ячеек А4:Н4 и выполним команду Формат /Ячейки и в открывшемся окне диалога Формат ячеек щелкните вкладку Выравнивание. В поле по горизонталивыберите вид выравнивания - по центру, в поле по вертикали также - по центру установим флажок Переносить по словам, затем ОК. Снимем выделение щелчком вне зоны выделения.

4. Введите в ячейку А5 число 1, а в ячейку В5 - число 2. Затем выделите эти две ячейки и установите указатель мыши в правый нижний угол выделения. Когда указатель мыши примет вид тонкого крестика, нажмите левую кнопку мыши и, не отпуская ее, протяните вправо до конца таблицы. Выделите диапазон ячеек А5:Н5 и щелкните по кнопке По центру панели инструментов Форматирование .

5. Аналогичным образом заполните ячейки А6:А15 числами от 1 до 10.

6. Заключите поля шапки таблицы в рамки. Выполните команду Формат /Ячейки и в открывшемся окне диалога Формат /Ячейки щелкните вкладку Границы

7. Введите фамилии 10 рабочих, а в ячейки С6 - С15 выработку (например, если норму выработки считать 100, то числа в этих ячейках должны быть больше и меньше 100).

8. Норму выработки и расценки для простоты считайте одинаковыми для всех рабочих, поэтому их можно ввести только в первые ячейки (Dб и Е6) соответствующих столбцов и затем скопировать в остальные ячейки столбцов.

9. Содержимое ячеек F6- F15 следует вычислять по формуле:

«Расц-ка» * «Выр-ка», если «Выр-ка»<= «Норма»
«Нач-но»= «Расц-ка»* «Выр-ка» + («Выр-ка» - «Норма»)*0,3 * «Расц-ка»,
если «Выр-ка» > «Норма»,

для чего в ячейку F6 надо ввести формулу

=ЕСЛИ(6<=D6;Е6*С6;E6*С6+(С6-D6)*0,З*Е6)
Формулу в ячейку F6 введите с помощью Мастера функций, для чего:

§ Выделите ячейку F6;

§ Выполните команду Вставка / Функции или щелкнем кнопку на панели инструментов;

§ в открывшемся окне диалога Мастер функций - шаг 1 из 2
выберите щелчком категорию Логические, а в списке Функции щелчком функциюЕСЛИ;
Нажмите ОК

§ В открывшемся окне диалога Мастерфункций

§ в открывшемся окне диалога Мастер функций по очереди активизируйте щелчком мыши поля для ввода аргументов и введите в первое поле – С6<=D6 (чтобы сделать это, уберите диалоговое окно на время выделения этого диапазона, для чего нажмите кнопку свертывания диалогового окна справа от поля с названием Логическое выражение (с косой красной стрелкой), кликнете по ячейке С6,с клавиатуры введите знак операции <=и сновакликнете по ячейке D6. Кнопка разворачивания окна диалога справа от поля ввода формулы позволяет снова вывести на экран окно Мастера функций. Кликнете по ней.
Так же введите во второе поле – Е6*С6 ,
в третье – E6*С6+(С6-D6)*0,З*Е6;

§ Кликнете ОК,

§ Нажмите Enter для окончания ввода формулы.

§ Скопируйте формулу в остальные ячейки этого столбца .

10. В ячейкуG6 введем формулу =СЕГОДНЯ() ,где СЕГОДНЯ - функция, определяющая текущую дату, и скопируем ее в ячейки G7 – G15.

11. Содержимое ячеек Н6 - Н15 следует вычислять по формуле:

«Начислено», если «Тек. дата»-«Срок» <= 20;

«К выпл»=
«Начислено»+(«Тек. дата»-«Срок»-20)*0,005* «Начислено»,

если «Тек. дата»-«Срок»>20,
для чего в ячейку Н6 надо ввести формулу:

=ЕСЛИ(G6-$D$2<=20;F6;F6+(G6-$D$2-20)*0,005*F6),

а в остальные ячейки этого столбца скопировать ее. Здесь срок выплаты зарплаты (содержимое ячейки D2) остается неизменным для всех сотрудников и поэтому при копировании формулы ссылка на эту ячейку. не должна измениться. Поэтому в формуле использована абсолютная ссылка на ячейку D2 (т.е. $D$2). Формулу в ячейку Н6 введите с помощью Мастера функций:

11. В итоговой строке в ячейку В16 введем текст ( Итого:), а в столбцах C,F и H подсчитаем сумму значений постолбцам. Для этого используем функцию СУММ, аргументом которой следует взять соответствующий диапазон ячеек. Например, чтобы просуммировать данные столбца С, надо:

¨ выделить диапазон ячеек С6:С15

¨ щелкнуть кнопку Автосуммирование в панели инструментов
Стандартные.В ячейке С16 появится искомая сумма. Аналогично просуммируем столбцы Е и Н.

12. Отформатируем данные в столбцах F и Н, установив вывод чисел с заданным количеством десятичных знаков (например, двух) после запятой, для чего:

¨ выделим диапазон ячеек F6:F16

¨ выполним команду Формат/Ячейки

¨ в открывшемся окне диалога Формат ячеек щелкнем вкладку Числовой

¨ в списке Числовые форматы щелкнем Числовой

¨ в поле Число десятичных знаков выберем «2»

¨ щелкнем ОК. Аналогично отформатируем столбец Н.

Отформатируйте таблицу, приготовьте ее для печати.

Лабораторная работа № 4.1.