Рис.12.
Выполнение
примерного
задания 3.1.
1) Ввести заданный процент в отдельную ячейку вне таблицы (D1);
2) вычислить среднюю выручку: =СРЗНАЧ(D6:D8);
3) задать имя ячейке с формулой подсчёта средней выручки: СрВыр;
4) вычислить «Необходимость закупки» в (Е6):
=ЕСЛИ((D6-СрВыр)/СрВыр > D$1;"да";"нет");
Пояснения к формуле:
- ячейки D1 и D2 – внешние ссылки, потому при копировании формулы не должны изменяться;
- ячейка D2 имеет имя СрВыр, которое задаёт ей абсолютный адрес;
- копирование формулы предполагается вертикально (по столбцу – имя столбца не меняется), потому ячейке D1 можно задать смешанный адрес по строке - D$1
5) скопировать формулу на весь столбец.
3.1.1. Варианты заданий по теме «Адресация»
G Примечание. Для выполнения следующих заданий следует воспользоваться исходными данными из заданий п.1.2.2. (тема «Статистические функции») лабораторной работы 1 с соответствующими номерами.
1. Для каждого сотрудника вычислить сумму к выдаче (Налог – 13%):
К выдаче = Оклад - Налог*Оклад
2. Для каждого предпринимателя вычислить среднемесячный доход. Количество рабочих месяцев ввести в отдельную ячейку вне таблицы.
3. Вычислить сумму каждой партии молока, если известна стоимость одного пакета молока, указанная в отдельной ячейке вне таблицы.
4. Рассчитать массы продуктов, необходимых для приготовления заданного числа порций плова, их стоимость. Необходимое число порций задайте в отдельной ячейке.
5. Определить вес каждого компонента лекарственного препарата, если известен вес одного порционного пакетика сбора, указанного в отдельной ячейке вне таблицы.
6. Определить общую стоимость акций каждого из учредителей, а также доли каждого акционера (в процентах) от общей стоимости всех акций. Стоимости одной акции каждого вида известны и должны задаваться в отдельных ячейках вне таблицы.
7. Получить процент аварий на заводах, принимая за 100% общее число аварий за текущий год. Получить процент аварий, принимая за 100% суммарное число аварий на предприятии.
8. Начислить премии каждому сотруднику предприятия, если премия выплачивается в процентах от оклада (процент премии указать в отдельной ячейке вне таблицы).
9. Известен размер годового процента за кредит. Начисленные каждый год проценты за кредит добавляются к сумме долга за него. Подсчитать сумму возвращаемого долга по каждому кредиту, дату возврата кредита. Какая сумма должна быть возвращена Всемирному банку в заданном году?
10. Вычислить зарплату каждого преподавателя, если известна стоимость одного часа и размер налога.
11. Для каждого абитуриента определить поступил ли он (да/нет), если известен проходной балл, задаваемый в отдельной ячейке.
12. Для каждого претендента определить, может ли он рассматриваться в качестве претендента на вакансию в фирму (да/нет). По условиям приема возраст претендентов не должен превышать значения, которое будет задаваться в отдельной ячейке. Найти количество таких людей.
13. Определить, какому сотруднику фирмы следует начислить премию (да/нет), если известно, что премия начисляется тем, чей стаж работы превышает заданное число лет.
14. Для каждого школьника определить, превышает ли его вес средний вес всех школьников (да/нет).
15. Сумма денег, имеющихся у ученика, такова, что он может купить только одну штуку некоторых дешевых канцелярских товаров. Для каждого товара определить, может ли школьник купить товар (да/нет) на сумму, указываемую в отдельной ячейке. Определить количество таких товаров.
16. Определить популярность каждого журнала (да/нет). Журнал считать популярным, если его тираж превышает некоторое значение, указываемое в отдельной ячейке. Определить общую стоимость популярных журналов.
17. Определить для каждого товара, дешевый он или дорогой. Дешевым считать товар, цена которого меньше средней цены всех товаров. Определить количество дорогих товаров в данном списке.
18. Начислить премию каждому сотруднику. Если оклад превышает 2000 рублей, то премия выплачивается в процентах от оклада; если оклад не больше 2000 рублей,то премия начисляется в процентах от оклада плюс 100 рублей. Процент премии указать в отдельной ячейке.
19. Оклад торгового агента составляет 15000 рублей. Если он продал за месяц товаров больше, чем на 200000 рублей, то он получает премию в размере 3% от проданного, а если больше 500000 рублей, – то 7%. Рассчитать зарплату для каждого агента. Учесть, что зарплата агента и проценты премий могут изменяться.
20. Для каждого жильца вычислить квартплату за месяц, исходя из стоимости квадратного метра и стоимости коммунальных услуг на человека в месяц (данные содержатся вне таблицы в отдельных ячейках); подсчитать сумму оплаты, которую каждый должен внести с начала года на текущий месяц. Для каждого жильца определить, должник он или нет: должниками считать тех, кто задолжал более, чем на заданное число процентов от общей суммы, которая должна быть внесена.
21. Определить успеваемость каждого студента: “да” – если средний балл больше 3,5. Определить, сколько каждый студент должен заплатить за обучение, если известна стоимость курса обучения. Определить тех, кто является должником.
22. Какой процент от общего веса составляет вес вещей каждого пассажира. Установить плату за багаж для каждого пассажира, зная цену доплаты за каждый килограмм сверх заданного допустимого веса. Найти максимальную и минимальную доплаты.
23. Вычислить зарплату каждого рабочего, зная цену одного рабочего дня и величину налога. Определите самую высокую зарплату. На сколько процентов от нее отличается зарплата каждого рабочего?
24. К претендентам на учебу в школе моделей предъявляются следующие требования: для мужчин – рост не менее 185 см., вес не более 75 кг.; для женщин – рост не ниже 175 см., вес не более 55 кг. Набираются молодые люди не старше заданного возраста. Определить, кто принят в школу. Сколько человек принято?
25. Рассчитать денежные затраты для каждого члена семьи, зная стоимость одного талона и учитывая, что для студентов, школьников и пенсионеров предусмотрен заданный процент скидки на талон. Вычислить недельные затраты всей семьи, средние недельные затраты. Для каждого определить, превышает ли он средние затраты.
26. Подсчитать процент голосов, поданных избирателями за каждую политическую партию. Для каждой партии определить, прошла ли она выборы (да/нет), т.е. набрала ли она заданный процент голосов. Требуемый процент указать в отдельной ячейке. Сколько партий не прошли выборы?
27. Определить популярный (непопулярный) вид посуды: популярным считать тот вид посуды, по которому выручка больше средней на заданное число процентов, указанное в отдельной ячейке.
28. Для каждого лекарства, продаваемого в аптеке, определить его льготную цену, истек ли срок годности на текущую дату (да/нет). Текущую дату и процент скидки указать в отдельной ячейке. Определить общую стоимость лекарств, срок годности которых истек.
29. Подсчитать стоимость пребывания в клинике каждому пациенту, если известна стоимость лечения и стоимость операции. Также учитывать скидки: 15% – для пациентов, возраст которых превышает 40 лет; 10% – для пациентов, возраст которых не превышает 20 лет. Скольким пациентам необходима операция, какова общая стоимость этих операций без учета скидок?
30. Рассчитать размер стипендии для каждого студента.
Стипендия назначается, исходя из минимального значения стипендии и с учётом того, что сданы все экзамены:
· если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
· если средний балл от 3-х (включительно) до 4,5, выплачивается минимальная стипендия;
· если средний балл меньше 3-х, стипендия не выплачивается.
Подсчитать сумму стипендиального фонда для всей группы, количество тех, кто получает стипендию, и кто не получает. Построить диаграмму размера стипендии для каждого студента.
3.2. Пример выполнения задания по теме:
«Вычисление функции z = f(x,y)»
Задание. Cоставить таблицу значений функции двух переменных z = f(x,y),
где z = x·y, х= 2, 4, 6, 8, …18, y= 1, 3, 5, 7, …,17.
G Напоминание. Пусть задано множество D упорядоченных пар чисел (x,y). Соответствие f, которое каждой паре чисел 
сопоставляет одно и только одно число 
, называется функцией двух переменных. Пишут: z = f(x,y).
Методические указания к выполнению задания
Рис.13. Выполнение примерного задания 3.2.
1) Ввести значения x в диапазон В1:J1, значения y – в диапазон A2:A10, используя при вводе операцию автозаполнения;
2) ввести формулу в В2: =B$1*$A2;
3) скопировать формулу по столбцу в ячейки диапазона В3: В10;
4) скопировать выделенный диапазона В2:В10 по горизонтали в ячейки блока В2:J10;
Пояснения к формуле:
- каждое из значений, находящихся в ячейках А2:А10, нужно умножить на число в ячейке В1, которое при копировании формулы не должно изменяться; при копировании по столбцу имя столбца не меняется, потому ячейке В1 нужно задать смешанный адрес по строке - D$1;
- все значения y, заданные в блоке А2:А10, нужно последовательно умножить на каждое значение х, находящееся в блоке В1: J1, поэтому значения в блоке А2:А10 при копировании по горизонтали (по соответствующим строкам) не должны изменяться, потому фиксируем столбец в адресе $A2.
G Самостоятельно проверить, можно ли формулу =B$1*$A2скопировать иначе – вначале по горизонтали, а потом по вертикали?
3.2.1. Варианты заданий по теме «Вычисление функции z = f(x,y)»
Задание. Cоставить таблицу значений функции двух переменных z = f(x,y),
где х и y выбираются из области определения заданной функции
z – см. вариант в приведённой ниже таблице.
| № | Задание | № | Задание | № | Задание |
| 1. | 
| 11. | 
| 21. | 
|
| 2. | 
| 12. | 
| 22. | 
|
| 3. | 
| 13. | 
| 23. | 
|
| 4. | 
| 14. | 
| 24. | 
|
| 5. | 
| 15. | 
| 25. | 
|
| 6. | 
| 16. | 
| 26. | 
|
| 7. | 
| 17. | 
| 27. | 
|
| 8. | 
| 18. | 
| 28. | 
|
| 9. | 
| 19. | 
| 29. | 
|
| 10. | 
| 20. | 
| 30. | 
|
4. Лабораторная работа по теме: «Диаграммы»
Цель работы: освоение способов и методов построения различных типов диаграмм в Excel.
Теоретическая справка. Диаграммы – это графическое представление данных. Они используются для анализа и сравнения данных, представления их в наглядном виде, позволяют осмыслить закономерности, лежащие в основе больших объемов числовых данных. Excel предлагает большой набор возможностей по графическому представлению данных.
Любая диаграмма отражает
числовые значения функции
в зависимости от значений аргумента .
G Напоминание из математики.
1. Определение функции: Если каждому элементу х множества Х (
) ставится в соответствие один и только один элемент y множества Y (
), то говорят, что на множестве Х задана функция (функциональная зависимость) y = f(x).
При этом х называется независимой переменной (или аргументом), y – зависимой переменной. Множество Х – область определения функции, множество Y – область значений функции.
2. Функция может быть задана не только формулой, но и таблицей.
Именно этот случай является основным для Excel.
Пример 1. Диаграмма выручки от продаж каждого товара.
 |
G Примечания.
1. Таблица представляет функцию, которая каждому товару (аргумент) ставит в соответствие размер выручки от продаж этого товара, т.е.
Выручка = f (товар).
2. Аргумент функции представлен нечисловыми данными.
3. Тип диаграммы – Гистограмма
Пример 2. График роста ребёнка в возрасте до года.
| Возраст, мес | Рост, см |
| 59,5 | |
G Примечания.
1. Таблица представляет функцию, которая возрасту ребёнка ставит в соответствие его рост в этом возрасте, т.е. Рост = f (возраст).
2. Значения аргумента функции – числа.
3. Тип диаграммы – График
4.1. Пример выполнения задания по теме: «Диаграммы»
Задание. Построить столбиковую диаграмму (гистограмму) количества продаж
каждого товара.
Рис.14. Выполнение примерного задания 4.1.
