Лабораторная работа
тема: «Алгебраические и статистические вычисления»
Целью данной лабораторной работы является изучение возможностей MS Excel при вычислении различных выражений: алгебраических, тригонометрических, трансцендентных и их комбинаций, освоение способов и приёмов вычисления арифметических выражений, разработка линейного алгоритма при вычислении сложных выражений, а также знакомство со встроенными статистическими функциями.
Теоретическая справка: Excel предназначен для хранения числовых и нечисловых данных и для выполнения всевозможных операций с этими данными. Результат этих операций определяется формулами, которые помещаются в текущую ячейку рабочего листа, после чего Excel сам производит все вычисления, описываемые формулой.
Структура арифметического выражения:
· числовые константы;
· ссылки – адреса ячеек (или имена ячеек);
· встроенные функции;
· знаки арифметических операций:
| - | Унарный минус (например -1) |
| % | Процент |
| ^ | Возведение в степень |
| * и / | Умножение и деление |
| + и - | Сложение и вычитание |
· 
скобки.
Рис. 1. Структура алгебраического выражения
Ввод формулы выполняется в специальной строке формул и начинается со знака равно (=), после которого в одну строку вводится последовательность символов, соответствующая вычисляемому выражению. Математические формулы могут быть «многоуровневыми», а в Excel формулу нужно располагать в одной строке, поэтому приходится вводить скобки, которых нет в исходной формуле.
При вычислении алгебраических выражений Excel руководствуется следующими традиционными правилами, определяющими
приоритет выполнения операций:
1) вычисляются выражения внутри круглых скобок;
2) определяются значения, возвращаемые встроенными функциями;
3) выполняются операции возведение в степень (^), затем умножения (*) и деления (/), а после – сложения (+) и вычитания (-).
4) операции с одинаковым приоритетом выполняются слева направо.
Excel имеет большое количество встроенных функций, разделенных на 9 групп (категорий): математические, статистические, финансовые, логические и т.п. Ввод функций в формулу существенно облегчается Мастером функций (кнопка 
или команда меню Вставка † Функция). Функция определяется двумя шагами:
1 шаг – выбор категории в списке Категория, а затем выбор необходимой функции в списке Функция.
2 шаг – задание аргументов функции, которые вводятся в специальные строки.
Для ввода ссылки в формулу следует установить курсор в поле ввода, а затем перевести указатель мыши на рабочий лист для выделения ячейки или блока ячеек.
Для вставки в формулу вложенных функций в строке ввода предусмотрена кнопка вызова функций, например: =КОРЕНЬ(SIN(X)); вложенная в =КОРЕНЬ функция SIN вызывается кнопкой (рис.2), которая выдаёт список недавно использовавшихся функций и предлагает вызов Мастера функций (Другие функции).
Рис. 2. Вызов вложенной
функции
При обработке статистических данных довольно часто возникает необходимость определения различных статистических характеристик. Для таких вычислений в Excel встроен ряд статистических функций, например:
| СРЗНАЧ(x1,…,xn) | среднее арифметическое (x1+…+xn)/n. |
| МАКС(x1,…,xn) | максимальное значение из множества аргументов (x1,…,xn) |
| МИН(x1,…,xn) | минимальное значение из множества аргументов (x1,…,xn) |
| СЧЕТ(x1,…,xn) | количество чисел в списке аргументов |
| СЧЕТЗ(x1,…,xn) | количество значений в списке аргументов и непустых ячеек |
Итоговые суммы в Excel рассчитываются с помощью функции СУММ(x1,…,xn) (Категория † Математические, более быстрый способ – кнопка 
на панели инструментов).
Пример выполнения задания
по теме: «Алгебраические выражения»
Задание. Вычислить выражения А и В при некоторых значениях входящих в них переменных x, y, z. Значения переменных выбрать самостоятельно, учитывая область допустимых значений А и В.
Рис. 3. Пример
выполнения задания
по теме «Алгебраические выражения»
Методические указания к выполнению задания:
· вывести заданные выражения для наглядности в виде объекта с помощью Редактора формул (MS Equation).
· задать ячейкам с исходными данными имена (присвоить ячейкам B1, В2, В3 соответственно имена X, Y, Z).
· применить метод последовательной детализации для вычисления заданных арифметических выражений:
- разбить каждое из выражений на отдельные, достаточно простые части (А: А1, А2, А3; В: В1, В2);
- выделить повторяющиеся вычисления (А1) в отдельную формулу;
- последовательно вычислять отдельные части каждого выражения в соответствии с разработанным линейным алгоритмом.
Такое разбиение имеет следующие преимущества:
1) наглядность алгоритма вычислений;
2) локализация ошибок (ошибки быстрее находятся в коротких выражениях, чем в длинных и сложных).
· Снабдить промежуточные вычисления смысловыми комментариями.
G Примечание. Факториалом натурального числа n (пишут: n!) называется произведение первых n натуральных чисел, т.е. n!=1∙2∙3∙…∙n.
1.1.1. Варианты заданий по теме «Алгебраические выражения» [1.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. | 
|
Пример выполнения задания с использованием
Статистических функций
Задание. Таблица содержит сведения о продажах некоторого магазина.
| Название товара | Цена (руб) | Количество продаж | Выручка от продаж |
| телевизор | |||
| магнитофон | |||
| плеер |
G Примечание. Пустая ячейка в столбце «Количество продаж» означает, что данный товар не был продан.
Вычислить:
· выручку от продаж каждого товара;
· общую, среднюю, максимальную, минимальную выручку от продаж всех товаров;
· определить общее количество видов товаров в магазине,
· сколько видов товара продано.
Методические указания к выполнению задания:
Рис. 4.
Пример
выполнения задания
по теме «статистические функции»
· ввести в ячейку D2 (первую ячейку столбца «Выручка от продаж») формулу: =B2*C2 («Выручка от продаж»= «Цена»* «Количество продаж»);
· 
скопировать формулу на весь столбец;
· ввести формулы:
в D5 =СУММ(D2:D4) - суммарная выручка
в D6 =СРЗНАЧ(D2:D4) - средняя выручка
в D7 =МАКС(D2:D4) - максимальная выручка
в D8 =МИН(D2:D4) - минимальная выручка
в D9 =СЧЕТЗ(А2:А4) - количество видов товара
(подсчёт количества непустых значений)
в D10 =СЧЕТ(С2:С4) - количество видов проданных товаров (подсчёт количества числовых значений)
1.2.1. Варианты заданий по теме «Статистические функции»
1. Таблица содержит сведения о сотрудниках кафедры: фамилия, табельный номер, оклад, количество часов. Определить общее количество сотрудников кафедры. Определить общий объем часов, отработанных кафедрой. Определить максимальные, минимальные, средние оклады всех сотрудников кафедры.
2. Таблица содержит сведения о нескольких предпринимателях: фамилия, регистрационный номер, размер годового дохода. Определить количество человек в таблице. Определить общий, максимальный, минимальный и средний размер годового дохода.
3. Таблица содержит сведения об объеме поставок молока в магазины города молочным комбинатом: магазин, объем поставок. Определить общий, максимальный, минимальный, средний объем поставок. Определить количество магазинов, сотрудничающих с комбинатом.
4. Известна раскладка продуктов на одну порцию плова: продукт, раскладка на одну порцию в г, цена за 1 кг. Вычислить общий вес продуктов, необходимый для приготовления одной порции. Определить максимальную, минимальную, среднюю стоимость 1кг продуктов, необходимых для приготовления. Определить количество продуктов, необходимых для приготовления плова.
5. Известно процентное соотношение каждого компонента (лекарственного препарата) травяного сбора: компонент, содержание в %. Определить содержание каждого компонента в г из расчёта 100 г всего сбора. Определить максимальный, минимальный, средний процент содержания компонентов.
6. В таблице записаны сведения о составе учредителей (акционеров) некоторого акционерного общества: фамилия, количество акций обыкновенных 1-го выпуска, обыкновенных 2-го выпуска, привилегированных. Определить общее количество акций каждого из учредителей. Определить максимальное, минимальное и среднее количество акций, имеющихся у акционеров. Определить число акционеров. Определить число акционеров, имеющих привилегированные акции.
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. Лабораторная работа по теме: «Логические вычисления»
Целью данной лабораторной работы является изучение логических функций Excel, освоение способов построения логических выражений, описывающих геометрическое место точек, разработка разветвляющихся алгоритмов разной сложности, применение логических функций в табличных данных.
Теоретическая справка. Для использования логических функций Excel следует вспомнить некоторые простые понятия из математической логики:
ü Логическая величина — это величина, которая может принимать только одно из двух значений: либо ИСТИНА,либо ЛОЖЬ.
ü ИСТИНА, ЛОЖЬ — логические константы.
ü Высказывание — утверждение, относительно которого можно заключить, верно оно или нет, истинно или ложно (например, результатом высказывания 5 > 3 будет логическая величина – ИСТИНА).
ü Предикат — высказывание с переменными; в зависимости от значения переменных предикат может принимать значения ИСТИНА или ЛОЖЬ (например, результатом предиката Х > 5 будет логическая величина – ИСТИНА, если Х = 10, и ЛОЖЬ, Х = 3).
ü Логическое выражение — простое или сложное высказывание или предикат (например: b2 – 4a×c ≠ 0).
ü Логические операции — операции, которые объединяют сложные логические выражения.
Основные логические операции:
- Конъюнкция (логическое умножение)
Записывается так: A Ù B, A И B, A & B, где А и В – логические величины (значения логических выражений).
Значение такого выражения будет ИСТИНА, если А и В истинны
(например: (x ≥ 10) Ù (x ≤ 100) – значение x в диапазоне от 10 до 100).
- Дизъюнкция (логическое сложение)
Записывается так:
A Ú B, A ИЛИ B, где А и В – значения логических выражений.
Значение выражения будет ИСТИНА, если значение хотя бы одного из выражений (А, В) истинно (например: выражение (x < 10) Ú (x > 100) определяет значение x вне отрезка [10; 100]).
- Отрицание
Записывается: НЕ А, Ø А, где А – значение логического выражения.
Значение выражения изменяется на противоположное
(например: A = b > 4, Ø А = Ø (b > 4) = b ≤ 4, b не больше 4).
Структура логического выражения:
· логические константы;
· предикаты и высказывания (сравнения), которые могут содержать арифметические выражения;
· логические операции;
· скобки.
Приоритет операций в логическом выражении:
| Алгебраические выражения | |
| Сравнения (отношения) | |
| Логические операции: | |
| 1) | – отрицание |
| 2) | – конъюнкция |
| 3) | – дизъюнкция |
Так как логические переменные могут принимать только два значения ИСТИНА или ЛОЖЬ, то логические операции можно задать таблицей, где перечислены все возможные значения аргументов и соответствующие им результаты операций (таблицы истинности):
| А | В | А И В | А ИЛИ В | НЕ А |
| ЛОЖЬ | ЛОЖЬ | ЛОЖЬ | ЛОЖЬ | ИСТИНА |
| ЛОЖЬ | ИСТИНА | ЛОЖЬ | ИСТИНА | ИСТИНА |
| ИСТИНА | ЛОЖЬ | ЛОЖЬ | ИСТИНА | ЛОЖЬ |
| ИСТИНА | ИСТИНА | ИСТИНА | ИСТИНА | ЛОЖЬ |
Примеры записи логических выражений в Excel:
| Математическая запись | Excel | |
| 1. | x·y – 6 ≠ 2·z | =A4*A3 – 6 < > 2 * B1 |
| 2. | (x ≥ 10) Ù (x ≤ 100) | =И (А1>= 10; А1<= 100) |
| 3. | (x ≤ 10) Ú (x ≥100) | =ИЛИ (А1 < 10; А1 > 100) |
| 4. | Ø (b > 4) | =НЕ (B1 > 4) |
G Примечания.
1. Ввод логической формулы, как обычно, начинается со знака “=”.
2. Логические операции реализуются в Excel в виде логических функций (Категория † Логические):
| Математическая запись | В Excel |
| А ^ В | И(А;В) |
| А Ú В | ИЛИ(А;В) |
| ØА | НЕ(А) |
На практике логические выражения используются для разработки разветвляющегося алгоритма (развилки):
Алгоритмический язык
Если условие (логическое выражение)
 то
 действие 1
иначе
 действие 2
всё-если;
|  Блок-схема
|
Для построения развилки в Excel существует логическая функция ЕСЛИ, структура её такова: ЕСЛИ ( логическое выражение; оператор 1; оператор 2)
Если значение логического выражения ИСТИНА,
то выполняется оператор 1,
иначе выполняется оператор 2.
Рис. 5.
Пример задания аргументов функции ЕСЛИ
(нахождение максимального значения из двух чисел)
Пример выполнения задания
«Описание геометрического места точек»
Задание. Принадлежит ли точка с координатами x и y заштрихованной области?
Вывести об этом соответствующее сообщение.
Методические указания к выполнению задания:
1. 
Описать заштрихованную область с помощью логического выражения, значение которого присвоить логической переменной Р.
2. Применяя метод последовательной детализации, разделить заштрихованную область на две части и задать логическим переменным, описывающим каждую часть соответственно Р1 («полукруг») и Р2 («треугольник»).
3. Заданная точка может находиться либо в одной части заштрихованной области, либо в другой, потому Р = Р1 Ú Р2
4. Описать 1-й часть области – «полукруг»:
| уравнения, ограничивающие область: | неравенства, описывающие заштрихованную область: | логическое выражение, описывающие заштрихованную область: |
| x2 + y2 = 4 y = 0 | x2 + y2 ≤ 4 y > 0 | Р1=(x2 + y2 ≤ 4) Ù (y > 0) |
Проверка:
1) Задать т. М (1; 1), x = 1, y = 1
Р1 = 12 + 12 £ 4 Ù 1 > 0
2 £ 4 Ù 1 > 0
ИСТИНА Ù ИСТИНА = ИСТИНА
т.о., точка М (1; 1)
принадлежит " полукругу "
2) Задать т. М (0; -1), x = 0, y = -1
Р1 = 02 + (-1)2 £ 4 Ù -1 > 0
1 £ 4 Ù -1 > 0
ИСТИНА Ù ЛОЖЬ = ЛОЖЬ
т.о., точка М(0; -1)
не принадлежит " полукругу"
5. Описать 2-й часть области – «треугольник»:
| уравнения, ограничивающие область: | неравенства, описывающие заштрихованную область: | логическое выражение, описывающие заштрихованную область: |
| y = 0 x = 0 y = x – 3 | y £ 0 x ≥ 0 y ≥ x – 3 | P2 = (y £ 0)Ù(x ≥ 0)Ù(y ≥ x - 3) |
Проверка:
1) Задать т. М (1; -1), x = 1, y = -1
Р2 = (-1 £ 0) Ù (1 ≥ 0) Ù (-1 ≥ 1 - 3)
ИСТИНА Ù ИСТИНА Ù ИСТИНА = ИСТИНА
т.о., точка М(1; -1)
принадлежит " треугольнику "
2) Задать т. М (0; 1), x = 0, y = 1
Р2 = ( 1 £ 0) Ù (0 ≥ 0) Ù (1 ≥ 0 - 3)
ЛОЖЬ Ù ИСТИНА Ù ИСТИНА = ЛОЖЬ
т.о., точка М(0; 1),
не принадлежит " треугольнику "
Вывод проверки: логические выражения, описывающие обе части заштрихованной области, составлены правильно.
6. Для всей области переменная Р будет истинной, если Р1 либо Р2, либо обе вместе - истинны: Р = Р1 Ú Р2
Проверку логического выражения всей области выполнить самостоятельно, задавая т. М(x,y) различные координаты (точка в 1-й области, точка в 2-й области, вне заштрихованной области).
7. Вывод сообщения выполняется с помощью развилки:
ЕСЛИ Р
ТО «т. принадлежит области»
ИНАЧЕ
«т. не принадлежит области»
ВСЕ - ЕСЛИ
8. Выполнить задание в Excel по разработанному алгоритму (рис.7)
Рис. 7.
Пример выполнения задания по теме «Логические выражения»
Варианты заданий
по теме «Описание геометрического места точек»
| № |  Задание
| № | Задание | ||||
| 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.
|
Описание геометрического места точек такого типа: 
следует начинать с описания незаштрихованной области, затем с помощью операции отрицания описать всё, кроме незаштрихованной области: = НЕ(Р).
2. Для описания отрезков (треугольников) следует вспомнить вывод уравнения прямой в отрезках:
.
Далее выразить y как функцию от x: y=f(x).
2.2. Пример выполнения задания по теме «Развилки»
Задание. Вычислить заданное выражение G. Значения А и В заданы.
вариант а): 
вариант b): 
