• если срок аренды в текущем месяце менее 10 часов, то скидка не предоставляется;

• если срок аренды в текущем месяце от 10 до 30 часов (10<=…<30), то скидка составляет 3 % от стоимости аренды;

• если срок аренды в текущем месяце от 30 до 100 часов (30<=…<100), то скидка составляет 5 % от стоимости аренды;

• если срок аренды в текущем месяце составляет 100 и более часов (>=100), то скидка составляет 7 % от стоимости аренды.

4. Составить формулы для расчета Счета за текущий месяц и Баланса расчетов на конец текущего периода.

5. Отформатируйте таблицу по своему усмотрению, но так, чтобы максимальное количество данных было видно на экране.

6. С помощью соответствующих формул подсчитайте:

- итоговые суммы по отмеченным в образце столбцам (строка 22);

- максимальный и минимальный срок аренды в текущем месяце, средний счет за текущий месяц (результаты запишите в строках 24-26);

- количество помещений, арендуемых компанией «Аквамарин» (результат запишите в строке 27).

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

7. Сделайте копию основной таблицы (диапазон A10:M20) ниже на этом же листе (вставьте только значения), отформатируйте копию таблицы. Отсортируйте копию таблицы по залу по убыванию и по счету за текущий месяц в порядке возрастания (это одна «двойная» сортировка ). Перед отсортированной копией поместите соответствующий заголовок.

ВАРИАНТ 4

1. Создайте электронную таблицу (рабочую книгу MS Excel) из 4-х рабочих листов и назовите рабочие листы:

Таблица, График, Подбор параметра, Доп.

Сохраните рабочую книгу в папке H:\Lab2\

1. На листе Таблица создайте таблицу для расчетов с клиентами за товары, участвующие в акции (см. рисунок). ФИО клиентов могут быть вымышленными.

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

3. В зависимости от числа имеющихся у клиента акционных наклеек, магазин предоставляет скидку за каждую покупку, причем отдельно рассчитывается скидка по акционным товарам, и отдельно по неакционным товарам. Правила вычисления скидок указаны в диапазоне B1:D7. Заполнить столбец Скидка по наклейкам Gold, руб. на основе правил начисления баллов из диапазона B1:D4, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейки C2:С4 – проценты будут меняться при проверке работы). Скидки вычисляются по следующему принципу:

• если сумма за акционные товары >= 6000 руб., то скидка составляет 50% от суммы за акционные товары;

• если сумма за акционные товары от 3000 до 6000 руб. (3000<=…<6000) и при этом у покупателя более двух наклеек Gold, то скидка составляет 30% от суммы за акционные товары;

• если сумма за акционные товары от 3000 до 6000 руб. (3000<=…<6000) и при этом у покупателя одна или две наклейки Gold, то скидка составляет 20% от суммы за акционные товары.

---

4. Заполнить столбец Скидка по наклейкам Silver, руб. на основе правил начисления баллов из диапазона B6:D7, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейки C2:С4 – суммы будут меняться при проверке работы). Скидки вычисляются по следующему принципу:

• если сумма за неакционные товары составляет >=5000 руб., то за каждую наклейку Silver покупатель получает скидку 300 рублей, однако скидка не может превышать сумму за неакционные товары;

• если сумма за неакционные товары составляет <5000 руб., то за каждую наклейку Silver покупатель получает скидку 100 рублей, однако скидка не может превышать сумму за неакционные товары.

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

6. Столбец Итоговая сумма, руб. вычисляется как разность между суммой покупки и суммарной скидкой.

7. Вычислить в столбце Наклейки новой акции, шт. количество наклеек, которые необходимо выдать клиенту в рамках новой акции, из расчета 1 наклейка за каждые 500 рублей итоговой суммы.

8. Отформатируйте таблицу по своему усмотрению, но так, чтобы максимальное количество данных было видно на экране.

9. С помощью соответствующих формул подсчитайте:

• итоговые суммы по всем столбцам (строка 22);

• максимальную и минимальную скидку по наклейкам Gold, среднее количество наклеек новой акции (результаты запишите в строках 24-26);

• количество клиентов, получивших скидку 900 руб. по наклейкам Silver (результат запишите в строке 27).

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

10. Сделайте копию основной таблицы (диапазон A10:L20) ниже на этом же листе (вставьте только значения), отформатируйте копию таблицы. Отсортируйте копию таблицы по скидке по наклейкам Silver по убыванию и по клиентам в порядке возрастания (это одна «двойная» сортировка ). Перед отсортированной копией поместите соответствующий заголовок.

ВАРИАНТ 5

1. Создайте электронную таблицу (рабочую книгу MS Excel) из 4-х рабочих листов и назовите рабочие листы:

Таблица, График, Подбор параметра, Доп.

Сохраните рабочую книгу в папке H:\Lab2\

1. На листе Таблица создайте таблицу расчета суммы налога (ФИО собственников могут быть вымышленными):

2. Столбец Долг "-" (Переплата "+"), руб. заполняется произвольно следующим образом: сумма долга отражается отрицательным значением, сумма переплаты – положительным.

3. В столбце Налог, руб. за кв. м. рассчитать размер налога в зависимости от площади квартиры на основе правил вычисления налога из диапазона B1:C5, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейки C2:С5 – суммы будут меняться при проверке работы). Правила вычисления налога:

• если площадь квартиры < 30 кв. м., то налог составит 3 рубля за кв. м.;

• если площадь квартиры от 30 до 50 кв. м. (30<=…<50), то налог составит 5 рублей за кв. м.;

• если площадь квартиры от 50 до 70 кв. м. (50<=…<70), то налог составит 10 рублей за кв. м.;

• если площадь квартиры >= 70 кв. м., то налог составит 2 рубля за кв. м.

---

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

5. Заполнить столбец Просрочка оплаты, дней, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса. При этом если к сегодняшнему дню срок оплаты еще не прошел, то просрочка оплаты отсутствует (0 дней).

6. Пени за просрочку вычисляется в зависимости от количества дней просрочки. Заполнить столбец Пени за просрочку, % в день на основе правил начисления пени из диапазона I1:J4, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейки J2:J4 – проценты будут меняться при проверке работы). Правила начисления пени:

• если просрочка оплаты налога отсутствует, то пени не взымается;

• если просрочка оплаты составляет до 100 дней, то пени составит 0,5% в день от общей суммы налога;

• если просрочка оплаты составляет 100 и более дней, то пени составит 0,3% в день от общей суммы налога.

7. Вычислить пени в рублях, составив соответствующие формулы в столбце Пени за просрочку, руб.

8. Вычислить общую сумму в столбце Итого к оплате, учитывая долг (переплату), налог и пени. Считать, что переплата не возвращается (при положительном значении суммы к оплате указывается эта сумма, а при отрицательном – ноль).

9. Отформатируйте таблицу по своему усмотрению, но так, чтобы максимальное количество данных было видно на экране.

10. С помощью соответствующих формул подсчитайте:

• итоговые суммы по отмеченным в образце столбцам (строка 19);

• максимальную и минимальную площадь квартиры, средний срок просрочки оплаты налога (результаты запишите в строках 21-23);

• количество собственников, выплачивающих налог в размере 5 руб. за кв. м. (результат запишите в строке 24).

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

11. Сделайте копию основной таблицы (диапазон A7:L17) ниже на этом же листе (вставьте только значения), отформатируйте копию таблицы. Отсортируйте копию таблицы по просрочке оплаты по убыванию и по итоговой сумме в порядке возрастания (это одна «двойная» сортировка ). Перед отсортированной копией поместите соответствующий заголовок.

ПОДБОР ПАРАМЕТРА

2. На листе Подбор параметра создайте таблицу для решения следующей задачи.

Вы планируете поездку в Екатеринбург с парой-тройкой друзей. Необходимо решить, что дешевле – поехать на машине или на поезде (рассматривается вариант проезда в купе).

Исходные данные для этой задачи (Рисунок 1):

Рисунок 1. Исходные данные

С помощью подбора параметра определить:

• при какой цене билета РЖД ехать вдвоем на поезде будет столь же выгодно, как и ехать на машине;

• при каком количестве пассажиров ехать на поезде будет столь же выгодно, как и ехать на машине;

• при какой цене бензина ехать на поезде будет столь же выгодно, как и ехать на машине.

---

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

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

Если результат, который необходимо получить при вычислении формулы, известен, но неясно, какое входное значение формулы требуется для получения этого результата, можно использовать средство подбора параметров. Средство подбора параметров поддерживает только одно входное значение переменной. Если необходимо определить сразу несколько входных значений, например, количество пассажиров и цену билета, то следует использовать вместо этого надстройку "Поиск решения". Для данной задачи достаточно подбора параметра.

Для подбора первого параметра (цены билета) скопируйте исходную таблицу ниже на этот же лист рабочей книги. Сделайте соответствующий заголовок (Рисунок 2).

Рисунок 2. Данные для подбора цены билета

Чтобы поездка на поезде была столь же выгодна, как и поездка на машине, необходимо, чтобы разность в ячейке B16 была равна нулю. Выделите ячейку B16, далее на вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Подбор параметра (в Microsoft Excel 2010 выберите вкладку Данные, группу Работа с данными, команду Анализ «что если», пункт Подбор параметра). В открывшемся диалоговом окне задайте для ячейки B16 значение 0, которое должно быть получено путем изменения значения в ячейке B15 (цена билета), см. Рисунок 3.

Рисунок 3. Диалоговое окно "Подбор параметра"

При нажатии кнопки ОК в ячейке B16 будет отражено полученное значение 0, а в ячейке B15 – подобранное значение, при этом возникнет диалоговое окно, отражающее результат подбора параметра (Рисунок 4). Если в этом диалоговом окне нажать кнопку ОК, то полученные значения останутся в ячейках на листе. Если нажать Отмена, то будут возвращены первоначальные значения.

Рисунок 4. Результат подбора параметра

Выделите ячейку B15 любой заливкой.

Аналогично подберите параметр для определения количества пассажиров и цены бензина (Рисунок 5).

Рисунок 5. Результаты подбора параметра

Из полученных результатов можно сделать следующие выводы. На поезде ехать столь же выгодно, как и на машине, если:

• цена билета РЖД снизится до 2408,8 руб.;

• число пассажиров – более одного;

• цена бензина поднимется до 51,75 руб. за литр.

3. В столбцах E:G решите аналогичную задачу для своего варианта (оставьте решенный пример с Екатеринбургом):

   № варианта	Исходные данные	Определить, при каких обстоятельствах ехать на поезде будет столь же выгодно, как и ехать на машине:
   		при каком количестве пассажиров; при каком расходе бензина. Выделите варьируемый параметр.
   		при какой цене бензина; при каком расстоянии. Выделите варьируемый параметр.
   		при какой цене билета; при каком расходе бензина. Выделите варьируемый параметр.
   		при каком количестве пассажиров; при каком расходе бензина. Выделите варьируемый параметр.
   		при какой цене билета; при каком расстоянии. Выделите варьируемый параметр.

4. Сохраните рабочую книгу.

---

ГРАФИК

5. На листе График постройте график функции в соответствии со своим номером варианта.

№ варианта	Исходные данные
	,
	,
	,
	,
	,

Для построения графика функции необходимо составить формулу вычисления функции y при каждом значении аргумента x. Например, для функции зададим следующие данные (Рисунок 6), и скопируем формулу для всех строк таблицы.

Рисунок 6. Исходные данные для графика

Составьте формулу для своего варианта!

Чтобы построить график функции y необходимо выделить диапазон, содержащий значения функции (можно с заголовком столбца), и на вкладке Вставка в группе Диаграммы нажать кнопку График (Рисунок 7).

Рисунок 7. Кнопка "График"

По умолчанию на текущем рабочем листе появится график функции y (график для функции приведен на Рисунок 8).

Рисунок 8. Первоначальный вид графика

6. Впишите свою функцию в название диаграммы. Измените подписи горизонтальной оси на значения аргумента x.

Для этого в название диаграммы достаточно вписать формулу из своего варианта.

По умолчанию подписи горизонтальной оси содержат порядковые номера значений функции. Для изменения подписей оси x нужно выделить область диаграммы и в контекстном меню выбрать пункт Выбрать данные. Откроется диалоговое окно Выбор источника данных. В поле «Подписи горизонтальной оси» необходимо нажать кнопку Изменить (Рисунок 9).

Рисунок 9. Диалоговое окно "Выбор источника данных"

В открывшемся диалоговом окне «Подписи оси» в поле Диапазон подписей оси выбрать диапазон, содержащий все значения аргумента x и нажать ОК (Рисунок 10).

Рисунок 10. Выбор диапазона подписей оси

После всех подготовительных действий график должен выглядеть примерно следующим образом (Рисунок 11).

Рисунок 11. График функции

7. С помощью подбора параметра определите корни уравнения для своего варианта. Подпишите значения на графике.

Используя подбор параметра, корни можно легко найти. Для этого скопируем одну строку из области исходных данных отдельно на этот же рабочий лист, например, в диапазон D2:E2. Используя подбор параметра, зададим значение E2=0, изменяя значение D2. Добавим соответствующие названия и числовой формат данных (Рисунок 12).

Рисунок 12. Подбор параметра для первого корня

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

Рисунок 13. Поиск корней с помощью подбора параметра

Таким образом, корни уравнения: x1 = -1,74; x2 = 5,74. С помощью инструмента «Надпись» подпишем на графике полученные точки и отметим их красными метками (Рисунок 14).

---