Задания для выполнения работы. Реализация арифметических и логических вычислений
Лабораторная работа №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. | Разработайте формулу, которая возвращает время года (зима, весна, лето, осень) для вводимой даты.
Для создания формулы используйте функции ЕСЛИ, вложенные одна в другую, введите ограничения. | |||||||||||||||||||||||||||||||||||||||||||||||
| 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. | Дана таблица с графиком работы:
Используя функцию ЕСЛИ, вычислите отработанное время сотрудников предприятия, работающих по скользящему графику. Отработанное время = конец смены - начало смены (если конец смены больше начала смены.) В противном случае отработанное время будет = 24ч.- начало смены + конец смены. | |||||||||||||||||||||||||||||||||||||||||||||||
| 8. | Рассчитайте сумму денег, выплачиваемых каждому из 15 сотрудников. Подоходный налог вычисляется по формуле: 13% от оклада за вычетом минимального размера оплаты труда и пенсионного налога. Пенсионный налог и профсоюзный взнос составляют по 1% от оклада. Т.к. минимальный размер оплаты труда периодически меняется, то величину целесообразно записать один раз.
| |||||||||||||||||||||||||||||||||||||||||||||||
| 9. | Даны два числа 57 и 103. Используя функции – ЕСЛИ вложенные одна в другую, отобразите числа таким образом, что если они находятся в интервале от 50 до 100, возвращаются исходные числа, в противном случае появляется сообщение «Значение вне интервала». | |||||||||||||||||||||||||||||||||||||||||||||||
| 10. | Дана таблица с результатами экзаменов:
Вычислите стипендию исходя из следующих условий. 1. Если студент обучается на платной основе, стипендия равна нулю; 2. Средний балл должен округляться до одного десятичного знака; 3.Студентам-бюджетникам, сдавшим все экзамены со средним баллом не ниже 5,5, стипендия начисляется по формуле: Базовая_ставка * Коэффициент.
Для вычисления стипендии, вначале подсчитайте средний балл, округленный до десятичного знака. Используйте функции ОКРУГЛ (округляет число до указанного количество десятичных разрядов) и СРЗНАЧ (возвращает среднее арифметическое своих аргументов). Затем введите формулу для подсчета стипендии, используйте функции ЕСЛИ, вложенные одна в другую. Установите смешанные ссылки для фиксированных значений коэффициентов и абсолютные – для базовой ставки. | |||||||||||||||||||||||||||||||||||||||||||||||
| 11. 1 | В таблицу введите сведения о времени отправления 10 поездов по расписанию и о величине задержки отправления, а также о времени прибытия их на конечную станцию по расписанию и о величине опоздания поезда. Для каждого поезда определите время нахождения в пути, используя функцию ЕСЛИ. Примите во внимание, что поезда могут прибыть на конечную станцию и на следующий день после дня отправления (при этом время нахождения поезда в пути не должно превышать 1 суток). | |||||||||||||||||||||||||||||||||||||||||||||||
| 4. | Известны оценки абитуриентов, на каждом из 3-х вступительных экзаменов. Для каждого абитуриента определите, поступил ли он в учебное заведение, величину проходного балла указать в отдельной ячейке. Рассмотреть два случая. 1. Известно, что среди абитуриентов нет получивших оценку 3. 2. Среди абитуриентов есть получившие оценку 3 (такие абитуриенты к конкурсу не допускаются). Установите абсолютные ссылки для проходного балла, а для решения используйте функцию ЕСЛИ. |
Контрольные вопросы
1. Назовите основные типы данных, используемых в MS Excel.
2. Особенности ввода различных типов данных.
3. Операторы, используемые в формулах. Приоритет операций.
4. Основные типы ошибок.
5. Способы локализации ошибок.