Разрешение вопросов, возникающих при появлении ошибок в формулах

Excel отображает ошибку, если формула обработана неправильно. Например, если формуле требуются числовые значения, а на самом деле указаны текстовые, то отобразится ошибка. Ошибка появляется при удалении ячейки, на которую ссылается функция, а также при использовании ячейки, ширины которой не хватает для вывода результата. Ошибки могут возникать не только из-за неправильной обработки формулы. Например, если отображается ошибка #Н/Д или #ЗНАЧ!, то, возможно, ошибка содержится в ячейке, на которую ссылается формула. Для определения ячеек, с которыми связаны ошибки, можно воспользоваться средствами поиска зависимостей.

Ошибка ##### выводится в том случае, когда ячейка содержит число, дату или время, число знаков которого больше ширины ячейки, или в том случае, если ячейка содержит дату и/или формулу, которая выводит отрицательный результат. Увеличьте ширину столбца. Изменить размер столбца можно, перетащив границу между заголовками столбцов. Выберите другой числовой формат. В некоторых случаях, можно изменить числовой формат ячейки, чтобы число помещалось в ячейку текущей ширины.

Ошибка #ЗНАЧ! появляется, когда используется недопустимый тип аргумента.

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на ноль (0).

Ошибка #ИМЯ? появляется, когда Excel не может распознать имя, используемое в формуле.

Значение ошибки #Н/Д является сокращением термина «Неопределенные Данные». Это значение помогает предотвратить использование ссылки на пустую ячейку.

Ошибка #ССЫЛКА! появляется, когда используется недопустимая ссылка на ячейку.

Ошибка #ЧИСЛО! появляется, когда возникают проблемы при использовании чисел в формуле или функции.

 

Лабораторная работа № 6.

Несмотря на то, что Excel служит в первую очередь для простых вычислений, в его среде есть возможности для решения уравнений, оптимизации и других математических задач. Рассмотрим сначала следующую задачу: найти корни алгебраического уравнения .

Для нахождения корней требуется предварительное исследование функции, находящейся в левой части уравнения. График этой функции мы уже строили (см. рис. 21). Точки, в которых он пересекает ось X, соответствуют корням уравнения. На графике есть одна такая точка. Заметим, что функция непрерывна и неограниченно возрастает при . Тогда существует по крайней мере еще один корень. Вернемся на лист Функции и попробуем изменять диапазон A2:A22. После опытов с графиком выясняется, что функция два раза меняет знак, то есть уравнение имеет два корня. Введите в ячейку A2 значение –2, и заполните ряд вниз с шагом 0,2. И по графику, и по ряду значений функции видно, что один из корней лежит на промежутке [-0,8;-0,6], а другой – на [0,8;1]. Чтобы уточнить их значения, можно продолжать, уменьшая шаг до тех пор, пока не будет достигнута нужная точность. Неизвестно, сколько это займет времени, и мы выберем другой путь.

Подбор параметра

Введите формулу для задания функции в ячейку D1 листа Функции. Это можно сделать простым копированием, например, ячейки B2. В ячейку C1, на которую ссылается формула, поместите произвольное число. Если она останется пустой, Excel при расчете воспримет ее как нулевую, что, впрочем, тоже нам не повредит. В этом случае значение, рассчитанное в D1, окажется отрицательным и равным –4,403. Если бы значение формулы в ячейке D1 оказалось равным нулю, мы с уверенностью могли утверждать, что значение, хранящееся в ячейке C1, есть корень предложенного уравнения. Можно ли подобрать значение в C1 такое, чтобы значение в D1 обращалось в ноль? Поручим эту работу Excel. Выберите команду Сервис-Подбор параметра, и в поля ввода окна, изображенного на рис. 23, задайте нужные ссылки и значение. Установите курсор в поле Установить в ячейке и щелкните ячейку D1. В поле Значение введите число ноль. Щелкните ячейку C1, поместив курсор в поле Изменяя значение ячейки. Затем нажмите кнопку OK.

Рис. 23.

Excel сообщает, что решение найдено. Значение, установленное в ячейке D1 теперь выглядит как 5,3379E-07. Возможно, что такое представление чисел Вам незнакомо. Это так называемый экспоненциальный формат, и значение равно . Если такая точность Вас не удовлетворяет, обратитесь к Сервис - Параметры -вкладка Вычисления и уменьшите относительную погрешность и увеличьте число итераций.

Таким образом, мы получили один из корней уравнения. Его значение теперь содержится в ячейке C1 и равно –0,7913679… Попробуем найти другой корень уравнения. Установим в ячейке C1 значение, равное 1,2 и снова проделаем подбор параметра. На этот раз Excel не справился с задачей. Второй корень может быть все же найден, если задать начальное значение, равное 0,8. Значение этого корня равно 0,85978…

Поиск решения

Как мы только что убедились, Excel не всегда правильно выполняет наши желания. Нам все же удалось добиться своего, но в других случаях поиск решения может вообще не достигнуть результата. На этот счет у Excel есть еще одно, более эффективное средство. Оно так и называется – «Поиск решения». Найдем одноименную команду в меню Сервис. Однако вполне может случиться так, что она в списке отсутствует. Попробуем тогда вызвать команду Надстройки. Перед нами раскроется список добавочных приложений Excel. Нужно найти в этом списке пункт Поиск решения и установить напротив него галочку.

Что делать, если и здесь нет такого пункта? Значит, при установке всего пакета MS Office не предусмотрели возможности использования такого полезного средства. Тогда придется установить это приложение средствами операционной системы.

Подобные трудности все же преодолимы, и, наконец, мы обращаемся к команде Поиск решения. Пусть перед этим остается выделенной ячейка D1. Диалоговое окно для настройки поиска решения представлено на рис. 24.

Рис. 24.

Установите данные для поиска решения так, как это показано на рисунке. В поле Установить целевую ячейку не обязательно вводить нужную ссылку с клавиатуры. Достаточно щелкнуть по ячейке D1. Ниже выберите переключатель Равной: значению и в поле ввода занесите 0. В поле Изменяя ячейки поместите ссылку на C1. Это также можно сделать, щелкая мышью в нужном месте, а именно, по ячейке C1.

Пока все полностью повторяло действия с подбором параметра. Обратите внимание теперь на список ограничений. Сейчас мы должны определить интервал, в котором Excel должен искать корень. Найдем сначала отрицательный корень. Нажмите кнопку Добавить. В появившемся диалоговом окне (рис. 25) заполните Ссылка на ячейку, щелкнув по C1, выберите из списка посередине «меньше или равно», а в последнее поле можно ввести правую границу интервала. Пусть интервал поиска корня [-1;-0,5]. Нажмите кнопку Добавить.

Рис. 25.

Аналогичным образом внесите второе ограничение и закройте окно, щелкнув кнопку OK. После этого на экране должно быть окно, в точности такое, как на рис. 24. Теперь щелкните кнопку Выполнить, и довольно быстро Excel сообщит Вам, что решение найдено и все условия выполнены. Сохраните найденное решение.

Чтобы найти второй корень, обратитесь опять к Поиску решения, удалите или измените ограничения. Пусть интервал нахождения второго корня [0,5;1]. Обратите внимание, что корни уравнения совпали с теми, что были найдены ранее другим способом.

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

Решение задач оптимизации.

Механизм Поиска решения позволяет решать самые разнообразные математические и экономические задачи, недоступные для простого Подбора параметра. Рассмотрим решение классической транспортной задачи.

Пусть имеется некоторый однородный продукт, сосредоточенный в m пунктах отправления в количествах  соответственно. Требуется доставить весь этот груз в n пунктов назначения в количествах . Стоимость  доставки одной единицы продукта из пункта i в пункт j известна для всех пар (i,j). Задача состоит в том, чтобы определить количество продукта x_ij, которое нужно отправить по каждому пути так, чтобы минимизировать общую стоимость доставки. Такова постановка транспортной задачи в ее классическом варианте. Если при этом общее наличное количество продукта равно требуемому , задача называется закрытой, в противном случае (с избытком или недостатком) – открытой транспортной задачей. Транспортная задача формулируется следующим образом: минимизировать функцию  при условии, что

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

Итак, рассмотрим следующую задачу. Задано четыре поставщика некоторого продукта и четыре потребителя с соответствующими параметрами a_i и b_j. Заданы соответствующие значения c_ij матрицы стоимостей перевозок единицы продукта. Все эти значения Вы можете увидеть на рис. 26. В интервале B3:E3 помещены значения b₁ b₂ b₃ b₄ (потребности), а в интервале A4:A7 помещены значения a₁ a₂ a₃ a₄ (предложения поставщиков). Так как суммы значений из первой строки и из первого столбца равны (160), ясно, что задача закрытая. Интервал H4:K7 содержит значения стоимостей перевозки .

Вставьте новый лист в свою рабочую книгу и занесите данные так, как это выглядит на рис. 26.

Рис. 26.

В ячейку F4 поместите формулу для суммы элементов строки (B4:E4) и заполните ряд вниз с помощью маркера автозаполнения. Формулу суммирования элементов интервала (B4:B7) и заполните ряд вправо. Так как суммируемые ячейки еще ничего не содержат, результатом суммирования оказываются нулевые значения.

Целевая функция, выражающая общую стоимость перевозок, есть сумма соответствующих произведений количества перевозимого продукта на стоимость доставки единицы продукта. Введите в ячейку C11 формулу «=СУММПРОИЗВ(B4:E7;H4:K7)» (см. Рис. 26). Отформатируйте нужные ячейки в денежном формате.

А теперь приступайте к поиску решения. Настройте его так, как показано на рис. 27.

Рис. 27.

Обратите внимание, что перевозки не могут быть отрицательными. Такое ограничение необходимо добавлять при решении задачи. Также заметим, что при вводе ограничений допустимо оперировать с интервалами, а не только с отдельными ячейками. Ограничение о целочисленности решения, возможно, не обязательно, но требование его выполнения естественно.

Если все сделано правильно, Excel сообщит о том, что решение найдено и удовлетворяет ограничениям. В целевой ячейке теперь результатом формулы является значение 308р. В интервал B4:E7 занесены значения перевозок от поставщиков к потребителям.

 

Задание для контрольной работы

1) Создать рабочую книгу MS Excel, на первом листе которой создать таблицу с даными по заказам, в которой должны быть следующие поля(столбцы): вид товара, наименование товара, дата заказа, количество, цена за единицу, заказчик. Все данные должны быть получены из внешнего источника - базы данных формата MS Access, содержащейся в файле TOVAR2000.MDB.

Вид товаров(парфюмерия, продукты, компьютерная техника, бытовая электроника и т.д.) задается в каждом из вариантов. При обращении к базе данных выбираются те записи, которые соответствуют номеру варианта, равному N.

На этом же рабочем листе должны быть добавлены два столбца, содержащие формулы - "год" и "цена товара", и все данные организованы в виде списка данных.

2) На следующем листе создать сводные таблицы по заказчикам, видам товара и по годам с подсчетом общей суммы продаж. На основе этих таблиц построить диаграммы.

3) Используя линейную экстраполяцию, сделать прогноз по количеству и сумме продаж по каждому виду товаров. Предварительно таблицу с данными рекомендуется отсортировать.

4) На отдельном рабочем листе вести учет товаров на складе(столбцы - наименование товара, количество, цена за единицу), этот лист должен быть связан с первым листом.

5) Только для специальности "Прикладная информатика": Создать приложение с диалоговым окном для оформления накладных (приходной или расходной) для заказа (1 наименование). Бланк можно взять из листа "Накладная" данной книги.

При оформлении накладных необходимо, чтобы автоматически менялось количество товаров на складе.

6) Решить следующую задачу оптимизации перевозок:

У поставщиков (филиалы склада) в нескольких пунктах имеются товары, которые необходимо развезти заказчикам. Необходимо так спланировать перевозки, чтобы минимизировать издержки (транспортная задача).

Исходные данные о размещении товара и стоимости перевозок единицы товара даются на соответствующих листах прилагаемой рабочей книги (номер варианта равен N).

7) Решить уравнение Sin(Ax+B)=Cx. Значения A,B,C - из таблицы согласно номеру зачетки. При этом использовать построение графика, "Подбор параметра" и Поиск решения". Найти все корни.

8) Построить поверхность для функции двух переменных F(x,y) = SIN(Ax) - By. Пусть x и y принадлежат интервалу [-5, 5] с шагом, равным 0,5

Рекомендуемая литература:

1. Долженков В.А., Excel 2000 в подлиннике, – BHV,– СПб., 2001.

2. Долженков В.А., Колесников Ю.В, Самоучитель Microsoft Excel 2000, – BHV,– СПб.,2001.

3. Гарнаев А.Ю. Использование MS Excel в экономике и финансах, – BHV. – СПб., 1999.

4. Харвей Г. Excel 2000 для себя. Самоучитель,– BHV– СПб., 2001.

5. Лавренов С.М. Excel: сборник примеров и задач,– Финстат–, 2001.

6. Уокенбах Д. Microsoft Excel 2000. Библия пользователя, – Диалектика, 2001.

7. Фултон Д. Освой самостоятельно Microsoft Excel 2000. 10 минут на урок, – Вильямс – СПб., 1999.