Относительные и абсолютные ссылки в формулах.

Четверо продавцов занимаются реализацией товара. Каждому продавцу полагается премия в размере 2 % от суммы выручки. Необходимо рассчитать сумму премии для каждого продавца.

1. Перейти на Лист 2.

2. Используя Автозаполнение, ввести в диапазон ячеек B1:F1 дни недели.

3. В ячейку G1 записать «Выручка».

4. В диапазон ячеек А2:А5 записать имена продавцов: Сергей, Наталья, Елена, Александр.

5. В диапазон ячеек B2:F5 ввести данные как на рис. 6.

Рис. 6. Данные рабочего листа «Лист2»

6. В ячейке G2 подсчитать суммарную выручку Сергея за пять дней работы.

7. Используя Автозаполнение, формулу из ячейки G2 распространить на диапазон ячеек G3:G5.

8. В ячейку Н1 ввести «Премия».

9. В ячейку I1 ввести ,02 и применить к этой ячейке процентный формат данных.

10. Данные в таблице отформатировать как на рис. 6.

11. Активизировать ячейку Н2.

12. В ячейку Н2 ввести формулу = G2*I1 и нажать Enter – в ячейке Н2 отобразится 2-процентная премия Сергея.

13. Из ячейки Н2 перетащить маркер Автозаполнения в ячейку Н3.

Обратить внимание на то, что премия Натальи равняется нулю. Это произошло потому, что формула в Н3 относительная. При активизации ячейки Н3 можно видеть, что в ней записана формула =G3*I2. Так как I2 не содержит никаких значений, премия начисляется нулевой (см. рис. 6).

14. Откорректировать формулу в ячейке Н2:

− активизировать ячейку Н2;

− щелкнуть справа от звездочки в строке формул, чтобы определить точку ввода;

− изменить формулу так, чтобы она выглядела следующим образом: =G2*$I$1;

− нажать Enter.

15. Из ячейки Н2 перетащить маркер Автозаполнения в ячейку Н5.

16. Просмотреть формулы в ячейках Н3-Н5.

Рабочий лист должен выглядеть как на рис. 7.

Рис. 7. Данные рабочего листа «Лист2»

17. Сохранить рабочую книгу в папку под именем «Вычисления в Excel».

18. Закрыть Microsoft Excel.

ЛАБОРАТОРНАЯ РАБОТА № 4

Часть 3.

ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL. ВЫПОЛНЕНИЕ РАСЧЕТОВ С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ И ФУНКЦИЙ

Цель лабораторной работы: освоить создание простых и сложных формул в таблицах. Изучить возможности работы с мастером функций, рас­смотреть основные математические, логические и финансовые функции Excel.

Создание простых формул

Задание 1. Создать документ средствами табличного процессора Microsoft Excel по следующему образцу:

Последствия нарушения договорных обязательств по предприятию

Виды ущерба	Ставка ущерба за единицу, руб.	Возмещаемый ущерб	Удельный вес (в % к суммарному ущербу)
Количество единиц	Сумма ущерба, руб.
	Нарушение срока поставки	0,2
	Ухудшение качества поставляемой про­дукции	0,1
	Изменение ассорти­мента поставляемой продукции	0.05
	Нарушение условий хранения продукции	0.09
	Несоответствие ко­личества по сопро­водительным доку­ментам	0,6
	Отказ в доставке продукции	0,3

суммарный ущерб:

Выполнить следующие расчеты в документе:

· Сумма ущерба = Ставка ущерба за единицу, руб. х Количество единиц

· Суммарный ущерб определяется как сумма ущерба по всем видам

· Удельный вес = Суммарный ущерб / Сумма ущерба, руб.

Рекомендации к выполнению:

1. Для создания документа необходимо запустить Microsoft Excel и на первом листе новой рабочей книги создать проект таблицы и оформить его следующим образом:

Примечание. Для переноса текста в ячейках необходимо выделить нужный интервал ячеек, выполнить команду Формат Ячейки и на вкладке Выравнивание включить параметр переносить по словам.

2. Для выполнения расчета Сумма ущерба = Ставка ущерба за единицу, руб. х Количество единиц необходимо выполнить следующее:

· в ячейку Е5 ввести формулу =C5*D5 и нажать клавишу Enter для расчета;

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

3. Для расчета Суммарного ущерба необходимо выполнить следующее:

· выделить ячейку Е11 и нажать кнопку Автосумма на панели инструментов Стандартная;

· продолжая находиться в режиме редактирования формулы, выделить интервал ячеек Е5:Е10 для суммирования, при этом фор­мула будет иметь вид =СУММ(Е5:Е10);

· нажать клавишу Enter для расчета.

4. Для выполнения расчета Удельный вес = Суммарный ущерб / Сумма ущерба, руб. необходимо выполнить следующее:

· в ячейку F5 ввести формулу =E5/$E$11 и нажать клавишу Enter для расчета (знак "$" означает абсолютную ссылку на ячейку и добавляется нажатием клавиши F4 в режиме редактирования формулы);

· скопировать формулу в ячейки F6:F10. При этом можно обра­тить внимание, что абсолютный адрес остается неизменным.

5. Для задания процентного формата необходимо выделить интервал ячеек F5:F10 и нажать кнопку Процентный формат на панели инстру­ментов Форматирование.

6. С помощью команды Сохранить как... документ необходимо сохранить в своей папке под именем Лабораторная работа4.

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

Задание 2. Известны следующие данные о преступности в двух районах (данные на 10 000 человек населения):

	1 район	2 район
Количество преступлений
Количество лиц, совершивших преступления
Население

Требуется проанализировать уровень преступности в районах, рас­считав следующие коэффициенты:

· коэффициент интенсивности ;

· коэффициент активности .

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

Рекомендации к выполнению:

1. На втором листе рабочей книге Лабораторная pa6oma4.xls необхо­димо создать проект таблицы и оформить его следующим образом:

2. В ячейках В5 и С5 необходимо рассчитать коэффициенты интенсивности по формулам, указанным в задании.

3. В ячейках В6 и С6 необходимо рассчитать коэффициенты активно­сти по формулам, указанным в задании.

4. Для проведения анализа и соответствующего вывода необходимо выполнить следующее:

· выделить ячейку В7 и выполнить команду Формулы – Вставить функция;

· на первом шаге мастера функций необходимо из списка катего­рий выбрать Логические, а в списке функций выбрать ЕСЛИ и нажать кнопку Ок;

Примечание. Функция ЕСЛИ возвращает одно значение, если за­данное условие при вычислении дает значение ИСТИНА, и другое значе­ние, если ЛОЖЬ. Функция ЕСЛИ используется для условной проверки значений и формул. Синтаксис ее следующий:

ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь),

где Лог выражение - это любое значение или выражение, ко­торое при вычислении дает значение ИСТИНА или ЛОЖЬ;

Значение_если_истина - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если истина опущено, то воз­вращается значение ИСТИНА. Значение если истина может быть другой формулой;

Значение_если_ложь - это значение, которое возвращается, ес­ли лог выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

· на втором шаге мастера функций необходимо задать аргументы функции следующим образом.

Примечание. Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Синтаксис ее следующий:

И(логическое_значение1; логическое_значение2;)

где Логическое_значение1, логическое_значение2, ... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

· для выполнения расчета необходимо нажать кнопку Ок.

5. С помощью кнопки Сохранить необходимо сохранить изменения в документе.