Задания для выполнения работы. Реализация арифметических и логических вычислений

Лабораторная работа №4

Реализация арифметических и логических вычислений

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

Методические указания

MS Excel оперирует с двумя основными типами данных - формулы и константы. К константам относятся числовые и текстовые значения, логические значения, а также значения даты и времени. Формула может содержать различные типы констант, встроенные или пользовательские функции и знаки арифметических, текстовых, адресных операций и операций сравнения.

Формула в MS Excel – это начинающееся со знака равно «=» выражение, состоящее из разного типа констант и (или) встроенных функций MS Excel, а также знаков арифметических, текстовых и логических операций. В формулах можно использовать следующие знаки операций (арифметические операторы в табл. 4.1 расположены в порядке возрастания приоритета).

 

Таблица 4.1–Виды и приоритет операций

 

Виды операций	Знаки
Арифметические	+ (сложение)
	- (вычитание)
	* (умножение)
	/ (деление)
	% (процент)
	^ (возведение в степень)
	–Унарный минус (изменение знака)
Текстовые	& (конкатенация)
Сравнения	= (равно)
	< (меньше)
	> (больше)
	>= (больше или равно)
	<= (меньше или равно)
	<> (не равно)
Адресные	: (двоеточие)
	; (точка с запятой)
	() (пробел)

 

Ввод формул в MS Excel отличается от ввода констант. Формулы в MS Excel начинаются с символа " = ". Отсутствие лидирующего символа " = " приводит к заданию не формулы, а текстового выражения, которое не подлежит вычислению. В качестве операндов арифметических выражений можно использовать константы, ссылки (адреса ячеек), функции.

Пример 1. Вычислим значение выражения , при .

Решение. В результате вычислений выражение будет равно 1 (рис.4.1).

 

Рис. 4.1. Ввод исходных данных и расчетные формулы для примера 1

 

Приоритет унарного минуса выше приоритета возведения в степень. Поэтому правильной должна быть реализация формулы в виде .

Пример 2. Необходимо вычислить значение арифметического выражения .

Решение. Возведение в степень имеет более высокий приоритет по сравнению с делением, а деление — по сравнению со сложением, то для корректного вычисления результата следует приоритет операторов задать с помощью скобок (рис.4.2).

 

Рис. 4.2. Ввод исходных данных и расчетные формулы для примера 2

 

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

Для реализации логических вычислений вложенная функция, используемая в качестве аргумента, должна возвращать соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!».

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

 

 

Решение. Для того, чтобы вычислить значения при разных условиях, следует воспользоваться функцией ЕСЛИ (рис.4.3). Эта функция имеет три аргумента. Первый аргумент – логическое выражение, задающее условие; второй аргумент – выражение, которое выполняется в том случае, если логическое выражение приобретает значение «истина»; третий аргумент – выражение, которое выполняется в том случае, когда логическое выражение приобретает значение «ложь».

Введенная формула будет иметь следующий вид:

 

 

Рис. 4.3. Ввод исходных данных и расчетные формулы для примера 3

 

В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности.

При использовании Мастера функций скобки при её аргументах расставляются автоматически, т.е. автоматически контролируется их баланс. Отредактировать формулу, содержащую функцию, можно как вручную, так и с использованием Мастера функции. Можно повысить читабельность сложных формул, разбивая их на логические блоки и размещая в строке формул в несколько строк (ALT–ENTER).

Переключение режимов отображения на экране результатов вычислений или формул осуществляется сбросом или установкой флажка Показывать формулы, а не их значения на вкладке Файл – Параметры Excel – категория Дополнительно.

При записи формул адреса ячеек можно задавать путем ввода с клавиатуры или щелчком левой клавиши мыши по нужным ячейкам. В последнем случае адреса ячеек подставляются автоматически.

При создании формул в MS Excel пользователь может сталкиваться со стандартными ошибками (рис. 4.4).

 

 

Рис. 4.4. Примеры некоторых часто встречающихся ошибок

 

1. Значки ### - ошибки как таковой нет, просто текст не вмещается в ячейку, требуется изменить её ширину.

2. Деление на нуль #ДЕЛ/0.

3. Ссылка на ячейки с неопределенным значением: #Н/Д.

4. Excel не распознаёт имя или адрес ячейки: #ИМЯ? Либо собственное имя ячейки написано неверно или ячейки с таким именем не существует.

5. Наличие пересекающихся областей: #ПУСТО! Задано пересечение двух диапазонов, которые не имеют общих ячеек.

6. Числовой аргумент, находящийся за пределами допустимых значений или функция не может получить результат при заданных аргументах: # Число!

7. Неверная ссылка на ячейку: #ССЫЛКА! Ячейки, на которые ссылается формула, были удалены или на их место помещено содержимое скопированных ячеек.

8. Значение не того типа, что ожидается: #ЗНАЧ! Например, мы ссылаемся на ячейку, в которой вместо числовых данных находится текст.

Локализацию ошибок можно осуществлять следующим образом:

1. Выделить ячейку с ошибкой.

2. В строке формул выделить вызывающий сомнение фрагмент.

3. Нажать F9. Происходит замена выделения значением. Повторять действие до тех пор, пока не будет выявлено в каком месте формулы ошибка.

4. Клавиша «Esc» – отказ от сделанных изменений, возврат к исходной формуле и исправление ошибки.

При локализации ошибок в формулах можно пользоваться командой трассировки ошибок.

Чтобы проследить зависимость в формулах от адресов ячеек, можно использовать команды, которые находятся: Формулы – Зависимости формул.

1. «Влияющие ячейки» - стрелками будут показаны ячейки, содержимое которых влияет на значение текущей ячейки. Вторичное использование этой команды укажет ячейки, оказывающие косвенное влияние на содержимое текущей ячейки.

2. «Зависимые ячейки» - устанавливает стрелки, указывающие на ячейки, на содержимое которых влияет текущая ячейка.

3. «Проверка наличия ошибок» - «Источник ошибки» - стрелками указываются ячейки, которые могут быть потенциальными источниками ошибок.

4. «Убрать все стрелки» - удаляет с экрана отображение всех зависимостей.

 

Задания для выполнения работы

1. Рассчитайте значения функций по формулам (табл. 4.2). Значения аргумента задайте в зависимости от области определения функции.

 

Таблица 4.2–Исходные данные к выполнению задания 1

 

Вариант	Расчетная формула
	, при [-1;1] с шагом 0,2
	, при [-90;90] с шагом 10
	, при [-180;180] с шагом 20
	, при [-1;1] с шагом 0,1
	, при [-10;10] с шагом 1
	, при [-60;60] с шагом 5
	, при [0;90] с шагом 10
	, при [20;30] с шагом 1
	при (-1;1] с шагом 0,1
	, при [-10;1] с шагом 1
	, при (0;90) с шагом 10
	, при [90;180] с шагом 10

2. Рассчитайте некоторые параметры, описывающие поведения экономических систем, по следующим формулам (табл. 4.3).

 

Таблица 4.3–Исходные данные к выполнению задания 2

Вариант	Расчетная формула	Примечание
	, при	Вероятность отказа в обслуживании заявки одноканальной СМО с ожиданием и ограниченной длиной очереди
	, при	Относительная пропускная способность одноканальной СМО с ожиданием и ограниченной длиной очереди
	, при	Среднее число заявок, находящихся в одноканальной СМО с ожиданием и ограниченной длиной очереди
	, при	Среднее число заявок в очереди в многоканальной СМО с ограниченной длиной очереди
	, при	Предельная вероятность k -го состояния многоканальной СМО с ограниченной длиной очереди
	, при	Предельная вероятность k -го состояния многоканальной СМО с неограниченной очередью
	, при	Вероятность k-го состояния многоканальной СМО замкнутого типа
	, при	Предельная вероятность k-го состояния многоканальной СМО с отказами
	, при	Предельная вероятность k-го состояния одноканальной СМО с ожиданием и ограниченной длиной очереди
	, при	Издержки в единицу времени однономенклатурной системы с учетом неудовлетворенных требований
	, при , ,	Оптимальный размер партии поставки для однономенклатурной системы с учетом неудовлетворенных требований
	, при , , ,	Оптимальная величина максимально задолженного спроса однономен- клатурной системы с учетом неудовлетворенных требований

3. Выполните задание с использованием логических функций согласно варианту (табл. 4.4).

Таблица 4.4–Исходные данные к выполнению задания 3

 

Вариант	Задание
1.	Запишите число (с именем ). Выясните, принадлежит ли оно отрезку [-5;4], используя функцию И, которая проверяет все ли аргументы имеют значения ИСТИНА. Если число удовлетворяет двум и более условиям, то в ячейке появится значение ИСТИНА, если число не удовлетворяет хотя бы одному условию, появится значение ЛОЖЬ.
2.	Разработайте формулу, которая возвращает время года (зима, весна, лето, осень) для вводимой даты.   Дата Месяц Результат 04.04.2007   весна   Для создания формулы используйте функции ЕСЛИ, вложенные одна в другую, введите ограничения.
3.	Запишите число (с именем ). Выясните, принадлежит ли оно одному из лучей на числовой оси: или , используя функцию ИЛИ. Для того, чтобы число принадлежало хотя бы одному лучу, нужно, чтобы истинным был хотя бы один аргумент. Если число удовлетворяет условию, то в ячейку появится значение ИСТИНА, если не удовлетворяет – ЛОЖЬ.
4.	Запишите число (с именем ). Выясните, принадлежит ли оно одному из лучей на числовой оси: или , используя функцию НЕ. Лучи составляют дополнение к отрезку [-5;4]. Функцию НЕ изменяет значение ИСТИНА на ЛОЖЬ, а ЛОЖЬ на ИСТИНА.
5.	Преподаватель проверяет тест, состоящий из 10 заданий. Каждое оценивается от 0 до 5. Если результат лежит в диапазон от 45 до 50 – ставится оценка «отлично», если в диапазоне от 35 до 45 – «хорошо», если в диапазоне от 25 до 35 – «удовлетворительно», в противном случае – «неудовлетворительно». Введите результаты тестирования в диапазон A2:J5. В ячейку K2 введите формулу, возвращающую оценку и скопируйте на диапазон K2: K5. Используйте функцию СУММ для подсчета количества баллов, набранных по тестированию, а затем используйте функции – ЕСЛИ вложенные одна в другую для отображения итоговой оценки.
6.	Продавец получает процент от суммы совершенной сделки. Если объем сделки до 5000, то это 2%, если до 25000, то 3%, а если более 25000, то 5%. Для расчета вознаграждения выберите функцию ЕСЛИ.
7.	Дана таблица с графиком работы:   График работы персонала Фамилия Начало смены Конец смены Отработано Жучкин 15:00 23:30   Терешко 7:15 15:40   Дубович 22:00 6:35   Петрова 8:00 16:40   Иванов 23:30 8:15     Используя функцию ЕСЛИ, вычислите отработанное время сотрудников предприятия, работающих по скользящему графику. Отработанное время = конец смены - начало смены (если конец смены больше начала смены.) В противном случае отработанное время будет = 24ч.- начало смены + конец смены.
8.	Рассчитайте сумму денег, выплачиваемых каждому из 15 сотрудников. Подоходный налог вычисляется по формуле: 13% от оклада за вычетом минимального размера оплаты труда и пенсионного налога. Пенсионный налог и профсоюзный взнос составляют по 1% от оклада. Т.к. минимальный размер оплаты труда периодически меняется, то величину целесообразно записать один раз.   № п/п ФИО сотруднка Оклад Подоходный налог Пенсионный налог Профсоюзный налог Сумма к выдаче               …                             МРОТ:
9.	Даны два числа 57 и 103. Используя функции – ЕСЛИ вложенные одна в другую, отобразите числа таким образом, что если они находятся в интервале от 50 до 100, возвращаются исходные числа, в противном случае появляется сообщение «Значение вне интервала».
10.	Дана таблица с результатами экзаменов:   ФИО Тип обучения Результаты экзаменов Ср. балл Стипендия     Иванов И.И. бюджет                 Петров П.В. платно                 Сидоров А.А. бюджет                   Вычислите стипендию исходя из следующих условий. 1. Если студент обучается на платной основе, стипендия равна нулю; 2. Средний балл должен округляться до одного десятичного знака; 3.Студентам-бюджетникам, сдавшим все экзамены со средним баллом не ниже 5,5, стипендия начисляется по формуле: Базовая_ставка * Коэффициент.     Базовая ставка   Средний балл Коэффициент от 5,5 до 6,9 1,0 от 7,0 до 7,9   8,0 и выше 1,7   Для вычисления стипендии, вначале подсчитайте средний балл, округленный до десятичного знака. Используйте функции ОКРУГЛ (округляет число до указанного количество десятичных разрядов) и СРЗНАЧ (возвращает среднее арифметическое своих аргументов). Затем введите формулу для подсчета стипендии, используйте функции ЕСЛИ, вложенные одна в другую. Установите смешанные ссылки для фиксированных значений коэффициентов и абсолютные – для базовой ставки.
11. 1	В таблицу введите сведения о времени отправления 10 поездов по расписанию и о величине задержки отправления, а также о времени прибытия их на конечную станцию по расписанию и о величине опоздания поезда. Для каждого поезда определите время нахождения в пути, используя функцию ЕСЛИ. Примите во внимание, что поезда могут прибыть на конечную станцию и на следующий день после дня отправления (при этом время нахождения поезда в пути не должно превышать 1 суток).
4.	Известны оценки абитуриентов, на каждом из 3-х вступительных экзаменов. Для каждого абитуриента определите, поступил ли он в учебное заведение, величину проходного балла указать в отдельной ячейке. Рассмотреть два случая. 1. Известно, что среди абитуриентов нет получивших оценку 3. 2. Среди абитуриентов есть получившие оценку 3 (такие абитуриенты к конкурсу не допускаются). Установите абсолютные ссылки для проходного балла, а для решения используйте функцию ЕСЛИ.

Контрольные вопросы

1. Назовите основные типы данных, используемых в MS Excel.

2. Особенности ввода различных типов данных.

3. Операторы, используемые в формулах. Приоритет операций.

4. Основные типы ошибок.

5. Способы локализации ошибок.