По теме «Описание геометрического места точек»

№	Задание	№	Задание
1.		2.
3.		4.
5.		6.
7.		8.
9.
15.		16.
17.		18.
19.		20.
21.		22.
23.		24.
25.		26.
27.		28.
29.		30.

 

1.

G Рекомендации.

Описание геометрического места точек такого типа: следует начинать с описания незаштрихованной области, затем с помощью операции отрицания описать всё, кроме незаштрихованной области: = НЕ(Р).

2. Для описания отрезков (треугольников) следует вспомнить вывод уравнения прямой в отрезках:

.

Далее выразить y как функцию от x: y=f(x).

2.2. Пример выполнения задания по теме «Развилки»

Задание. Вычислить заданное выражение G. Значения А и В заданы.

вариант а): вариант b):

Методические указания к выполнению задания

В этом задании предложены два типа разветвляющихся алгоритмов: простая развилка (а) и вложенная развилка (b).

Простая развилка была рассмотрена ранее (2.1), алгоритмическая запись её такова: Если условие (логическое выражение)

то

действие 1

Иначе

действие 2

Всё-если;

Вложенная развилка предполагает несколько способов реализации:

1) самый простой (тройную развилку разбивают на три простые развилки):

Если условие 1

то

действие 1

Всё-если;

Если условие 2

то

действие 2

Всё-если;

Если условие 3

то

действие 3

Всё-если;

2) комбинированный (разбиение на одну простую развилку и одну сложную):

Если условие 1

то

действие 1

иначе { условие 2 }

действие 2

Всё-если;

Если условие 3

то

действие 3

Всё-если;

3) вложение одной развилки в другую:

Если условие 1

то

действие 1

Иначе

Если условие 2

то

действие 2

Иначе

действие 3

Всё-если;

Всё-если;

 

Рис.9. Выполнение примерного задания по теме «Развилки»

G Рекомендации.

1) Вывести заданные выражения для наглядности в виде объекта с помощью Редактора формул (MS Equation);

2) задать ячейкам B10 и D10 соответствующие имена;

3) применить метод последовательной детализации для вычисления алгебраических и логических выражений;

4) разработать разветвляющийся алгоритм для заданного примера одним из предложенных ранее способов;

5) записать в среде Excel вычисления G по разработанному алгоритму с помощью логической функции ЕСЛИ (см. рис. 9);

6) проверить правильность алгоритма и записанных формул в Excel, изменяя исходные данные (x,y,u) так, чтобы вычисление G выполнялось по разнымветвям развилки.

2.2.1. Варианты задания по теме «Развилки»

№	Задание	№	Задание
1.		2.
3.		4.
5.		6.
7.		8.
9.		10.
11.		12.
13.		14.
15.		16.
17.		18.
19.		20.
21.		22.
23.		24.
25.		26.
27.		28.
29.		30.

2.3. Пример выполнения задания по теме

«Логические выражения в таблице»

В среде MS Excel существует возможность не только вычислять и выводить результат того или иного выражения по заданному условию, но и подсчитывать итоговые значения в заданном диапазоне ячеек по определённому условию (критерию). Этим целям служат специальные функции Excel:

Функция	Назначение
СЧЕТЕСЛИ (диапазон; критерий) Категория † Статистические;	подсчитывает количество непустых ячеек в диапазоне, по заданному условию.
СУММЕСЛИ (диапазон; критерий; диапазон суммирования) Категория † Математические;	суммирует ячейки, заданные указанным условием.

Задание. Торговый агент получает вознаграждение в размере некоторой доли от суммы совершенной сделки: если объем сделки до 5000 руб., то в размере 5%, если не ниже – 7%. Определите объем вознаграждения для каждого агента некоторой фирмы. Сколько агентов в фирме? Сколько агентов совершили сделки на сумму более 10000 руб., каково их суммарное вознаграждение?

Методические указания к выполнению задания

1. Объем вознаграждения агента зависит от суммы совершенной сделки, имеем два варианта: сумма сделки больше или меньше 5000р.

2. Алгоритм решения данной задачи - разветвляющийся, в Excel такой алгоритм реализуется с помощью функции ЕСЛИ.

3. Вычислить «Объем вознаграждения» по формуле:

=ЕСЛИ(B2>=5000;B2*5%;B2*7%).

4. Всего агентов в фирме: Е6 =СЧЕТЗ (А2:А5) – Категория † Статистические.

Напоминание. Функция СЧЁТЗ - подсчёт количества значений (непустых ячеек) в указанном диапазоне; значениями могут быть тексты, даты, логические величины.

5. Число агентов, совершивших сделку на сумму более 10000р.:

Е7=СЧЕТЕСЛИ(В2:В5;”>=10000”),

их суммарное вознаграждение: Е8 =СУММЕСЛИ(В2:В5; “>=10000”;С2:С5).

2.3.1. Варианты задания по теме «Логические выражения в таблице»

 

1. Известны оценки (по пятибалльной шкале), полученные абитуриентами на каждом из трех вступительных экзаменов. Для каждого абитуриента найти сумму баллов и определить, поступил ли он в учебное заведение, если известно, что «проходной» балл (минимально необходимая для поступления сумма баллов) равен 13. Сколько всего абитуриентов? Сколько абитуриентов поступило в учебное заведение? Определить средний суммарный балл поступивших и не поступивших.

2. Известна информация о багаже (количество вещей и общий вес багажа) пассажиров автобуса. Если у пассажира нет багажа, то соответствующие ячейки пустые. Определить для каждого пассажира необходимость оплаты багажа (да/нет), если оплачивается багаж с весом более 10кг. Сколько всего пассажиров? Сколько пассажиров должны оплатить багаж? Определить суммарный вес багажа пассажиров, имеющих одну вещь, более двух вещей.

3. В таблице имеются сведения о результатах игр по баскетболу между двумя командами (дата игры, набранные каждой командой очки). Необходимо в каждой игре определить победителя. Сколько игр выиграла каждая команда? Определить среднее количество набранных очков каждой командой. Ничьи во внимание не принимать.

4. Известны оценки каждого ученика по трем предметам. Для каждого ученика определить средний балл. Определить успеваемость каждого ученика: «да» - если средний балл выше 3,5. Найти общее количество «успевающих». Найти общее количество пятерок в таблице, количество двоек по каждому предмету. Найти средний балл у «успевающих» учеников по одному из предметов.

5. Таблица содержит сведения о лекарствах: название, срок годности, цена, нужен ли рецепт при покупке (да/нет). При покупке любого лекарства пенсионерам предоставляется скидка: 5% - если цена ниже 100 руб., и 7% - в противном случае. Определить стоимость каждого лекарства для пенсионеров. Определить количество лекарств, отпускаемых без рецепта.

6. Таблица содержит сведения о сотрудниках двух отделов: фамилия, имя, отдел, оклад. Начислить премию каждому сотруднику: 15% - работникам склада, 20% - работникам бухгалтерии. Подсчитать сотрудников с именем Иван, определить число сотрудников, у которых оклад не превышает 2000 руб. Подсчитать общий премиальный фонд работников склада.

7. Таблица содержит сведения о количестве осадков, выпавших за каждый день недели, и о температуре воздуха в эти дни. Для каждого дня определить, шёл снег или дождь. Считать, что идет дождь, если температура воздуха выше 0⁰С. Определить, сколько дней выпадал снег и сколько – дождь. Определить суммарное количество осадков в те дни, когда шёл дождь.

8. Таблица содержит сведения о студентах (фамилия, возраст, рост), желающих заниматься в баскетбольной секции. Для каждого определить, принят ли он в команду (да/нет), если принимают тех, чей рост не ниже 175см. Сколько человек принято? Каков средний возраст принятых?

9. Таблица содержит сведения о количестве отработанных часов за каждый день месяца каждым работником предприятия. Пустая ячейка означает, что в этот день он не работал (выходной или день отпуска). Для каждого определить количество рабочих дней в этот месяц, суммарное количество отработанных часов. Начислить премию тем, кто отработал более 120 часов (да/нет). Определить количество работников, отработавших за месяц 20 и более дней. Определить общее количество часов, выработанных сотрудниками, которым премия не начислена.

10. Итоги отборочного тура чемпионата по футболу заданы в виде таблицы. За выигрыш дается 3 очка, за проигрыш – 0, за ничью – 1.

Команда						В	Н	П	О
Спартак	Х
Динамо		Х
ЦСКА			Х
Ротор				Х
Камаз					Х

Для каждой команды определить:

ü Число выигрышей (В), ничьих (Н) и проигрышей.

ü Сумму набранных очков

ü Верно ли, что число выигрышей у команды больше числа проигрышей (да/нет), среднее число набранных очков у таких команд.

11. Провайдер интернет-услуг установил следующую систему оплаты: при работе с 2 до 10 часов – 0,5$ в час, в остальное время суток – 0,75$; в час. Таблица содержит сведения о пользователях: ФИО, момент начала работы (задан в виде целого числа, обозначающего час соответствующего момента времени), количество часов работы. Для каждого пользователя определить стоимость работы в интернете, если известно, что все время работы находилось в одном из указанных интервалов времени суток. Определить количество пользователей работавших более трех часов, определить общую стоимость их работы.

12. В таблице содержатся сведения о пациентах клиники: ФИО, возраст, пол, стоимость лечения без скидок. Вычислить стоимость лечения с учетом скидки, если скидка предоставляется пациентам старше 60 лет в размере 10%. Определить количество пациентов, которым предоставляется скидка, общую стоимость их лечения. Сколько всего пациентов в клинике?

13. Таблица содержит сведения о студентах: ФИО, количество сданных лабораторных работ, оценка по контрольной работе. В отдельный столбец каждому студенту записать результат зачёта: «зачтено», если он сдал не менее 5 лабораторных работ и получил по контрольной работе оценку не менее 3 баллов. Сколько студентов аттестовано? Сколько получили зачёт и скольким «не зачтено»?

3. Лабораторная работа по теме: «Адресация»

Цель работы: научиться использовать разные типы адресации (способы указания адресов ячеек в формуле) при решении различных задач в Excel, включающих копирование формул.

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

· относительные (например, А1),

· абсолютные ($A$1),

· смешанные ($A1 или A$1).

Относительный адрес устанавливается автоматически (по умолчанию) при вводе формулы. Адреса, находящиеся в формуле, при копировании настраиваются относительно начального положения на новое местоположение в таблице по следующему правилу:

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

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

Рис.10. Пример копирования формулы с относительными адресами.

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

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

G Примечание. Ячейка, которой присвоено имя, имеет абсолютный адрес.

Смешанная адресация фиксирует только один параметр адреса: либо имя столбца, либо номер строки. Если копирование формулы происходит по столбцу таблицы (вертикально), то фиксируется номер строки (имя столбца не меняется – см. правило выше), например, С$1. Если формула копируется по строке таблицы (горизонтально), фиксируется имя столбца, например, $С1 (рис.11а и 11б).

Рис.11а. Пример копирования формулы с абсолютными и смешанными адресами

(вертикальное копирование)

 

Рис.11б. Пример копирования формулы с абсолютными и смешанными адресами

(горизонтальное копирование)

 

G Примечание

Изменить тип адреса ячейки можно так:

· выделить ячейку с формулой;

· установить курсор мыши на адресе ячейки в строке формул;

· выделить его, дважды щелкнув левой клавишей мыши;

· выбрать тип адреса, последовательно

нажимая клавишу F4.

3.1. Пример выполнения задания по теме «Адресация»

Задание. Таблица содержит сведения о продажах в некотором магазине.

Выручка от продаж была вычислена в примере 1.2. (лаб. работа 1).

Определить, какой товар требует дополнительной закупки (да/нет). Считать, что товар необходимо дополнительно закупить, если выручка от его продажи превышает среднюю выручку от продажи всех товаров на заданное число процентов, например - 10%.

Название товара	Цена (руб.)	Количество продаж	Выручка от продаж, руб.
телевизор
магнитофон
плейер

Методические указания к выполнению задания

 

 

Рис.12.

Выполнение

примерного

задания 3.1.

 

1) Ввести заданный процент в отдельную ячейку вне таблицы (D1);

2) вычислить среднюю выручку: =СРЗНАЧ(D6:D8);

3) задать имя ячейке с формулой подсчёта средней выручки: СрВыр;

4) вычислить «Необходимость закупки» в (Е6):

=ЕСЛИ((D6-СрВыр)/СрВыр > D$1;"да";"нет");

Пояснения к формуле:

- ячейки D1 и D2 – внешние ссылки, потому при копировании формулы не должны изменяться;

- ячейка D2 имеет имя СрВыр, которое задаёт ей абсолютный адрес;

- копирование формулы предполагается вертикально (по столбцу – имя столбца не меняется), потому ячейке D1 можно задать смешанный адрес по строке - D$1

5) скопировать формулу на весь столбец.

Варианты заданий по теме «Адресация»

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. Пример выполнения задания по теме: «Диаграммы»