Комментарии

к выполнению лабораторных работ в среде Excel

с анализом наиболее часто совершаемых ошибок[22]

Лабораторная работа №1 (INFOLAB1)

Не используют возможности Excel по копированию формул – набирают в каждой ячейке свою формулу вместо того, чтобы, набрав ее один раз и закрепив нужные ячейки знаком $ (или букву, или номер, или то и другое вместе), скопировать на остальные ячейки.

Лабораторная работа №2 (INFOLAB2)

В задании 1 на диаграмму выводят величины разных размерностей, например, рубли и проценты. Их значения сильно отличаются друг от друга (тысячи или миллионы рублей и десятки процентов) и поэтому меньшие значения не видны. Выход: или выбирать что-нибудь одно, или строить две диаграммы. При этом, для того чтобы выделить в исходной таблице два несмежных столбца, необходимо, выделив первый, нажать на клавишу Ctrl и выделить второй и т.д. Начинать лучше со столбца с подписями. Тогда они автоматически появятся на диаграмме. То же самое относится к строкам.

В задании 2 задают мало точек для табулирования функции, и она прорисовывается неточно. Необходимо отрезок [-2, 2] разбить по крайней мере на 20 интервалов. При заполнении столбца (строки) Х используйте режим автозаполнения. При наборе формулы забывают порядок выполнения операций: стандартные функции, возведение в степень, умножение и деление, сложение и вычитание. Вычисление экспоненциальной функции в Excel оформлено стандартной функцией ЕХР(Х), а не 2,7^х.

Лабораторная работа №3 (INFOLAB3)

В заданиях 1 и 2 при построении линий трендов видно, что они все неплохо приближаются к статистической кривой. Но при переносе формул трендов в таблицы и вычислении теоретических значений получаются довольно большие квадратичные отклонения. Дело в том, что значения коэффициентов в формулах выводятся на экран с недостаточной точностью. Необходимо увеличить число знаков после запятой (щелкнуть мышкой по формуле, а затем использовать кнопку «Увеличить разрядность» на панели инструментов «Форматирование»). Сколько – подобрать опытным путем так, чтобы при последующем увеличении значение мало отличалось от предыдущего.

В задании 3 ограничиваются лишь полиномами 2-ой степени. Необходимо проверить все доступные в Excel степени полиномов и по критерию детерминации (достоверность аппроксимации R^2), выбрать лучший.

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

Задание выполнять для любого месяца (возьмите, например, месяц своего рождения) для двух разных лет.

Лабораторная работа №4 (INFOLAB4)

В задании 1 сначала нужно создать таблицу всех коэффициентов (в том числе и нулевых!) полинома 4-ой степени и затем работать не с их числовыми значениями, а с соответствующими адресами ячеек. Полезно ниже разместить таблицу модулей коэффициентов полинома – пригодится. Имейте в виду, что полином 4-ой степени с действительными коэффициентами имеет четное число действительных корней.

В задании 2 наиболее часто встречающаяся ошибка – в преобразовании исходного уравнения к форме x = F (x).

Например, пусть дано уравнение x ²sin(x) = 2. Представим его в виде x = F (x). Ясно, что это можно сделать неоднозначно. Варианты:

1) x = 2/(x sin(x)), т.е. в этом случае F (x) = 2/(x sin(x));

2) x = arcsin(2/ x ²), т.е. в этом случае F (x) = arcsin(2/ x ²) и т.п.

Если не удается подобрать преобразование, приводящее к сходящемуся итерационному процессу, можно использовать метод Ньютона. Имейте в виду, для его использования исходное уравнение надо записать в виде f (x)=0 и использовать производную от левой части.

Полученное решение надо обязательно проверить – или подстановкой в исходное уравнение, или используя другой метод, например, половинного деления (задание 3).

В задании 3 исходное уравнение надо записать в виде F (x)=0 и в таблице работать с функцией, стоящей в левой части. Для выбора начального интервала [ a,b ] желательно протабулировать функцию, чтобы посмотреть, где она пересекает ось х. Также его можно выбрать, основываясь на решении задания 2.

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

В задании 5 нужно найти все корни системы уравнений. Для этого полезно проанализировать поведение функций в левых частях уравнений.

Для построения поверхности функции А(x,y) ее следует протабулировать, выбрав самостоятельно диапазон изменения x и y таким образом, чтобы найденные корни вошли в него и располагались у краев диапазона. Тогда поверхность выглядит более изящной.

Лабораторная работа №5 (INFOLAB5)

Сначала нужно создать новую таблицу, разделив все параметры на параметры Земли, и в дальнейшем работать только с ней.

При выполнении задания 3 возникают затруднения по формированию исходных и результативных областей (диапазонов).

Первый диапазон уже определен – это преобразованная исходная таблица. Второй диапазон необходимо сформировать из строки заголовков таблицы, просто скопировав ее, причем достаточно скопировать лишь те заголовки, которые участвуют в ограничениях. Отметим, что эта строка не должна являться результатом объединения нескольких ячеек! В строках, расположенных ниже, записываются условия фильтрации (например, >10, >=2 и т.п.). Третий диапазон заранее можно не формировать: достаточно в диалоговом окне расширенного фильтра указать лишь левую верхнюю ячейку диапазона результатов. Именно с нее будет начинаться таблица результатов.

Лабораторная работа №6 (INFOLAB6)

В задании 1 в первую ячейку столбца «% удержания» необходимо ввести условную формулу, вычисляющую процент удержания для любого варианта количества иждивенцев (с использованием вложенных друг в друга функций ЕСЛИ). Затем она копируется на все остальные ячейки столбца.

В заданиях 2 и 3 при формировании сводной таблицы на шаге 3 Мастера сводных таблиц в поля «Строка», «Столбец», «Страница» надо переносить классификационные категории («Количество иждивенцев», «Фамилии» и прочие). Порядок и место расположения определяются подбором, исходя из требования: получить компактную таблицу с промежуточными итогами по классификационным категориям.

Лабораторная работа №7 (INFOLAB7)

В задании 1 и 2 при получении итоговой таблицы с помощью консолидации в поле «Ссылка» диалогового окна «Консолидация» следует заносить сразу весь массив исходных данных (например, столбцы «Продано» и «Выручка»), а не одну ячейку.

Лабораторная работа №8 (INFOLAB8)

Сначала необходимо определить личные коэффициенты каждого сотрудника, исходя из предложенного алгоритма. Их и надо использовать при вычислении оклада сотрудника, но только не напрямую в формулах (как это часто делают), а через адреса ячеек, где они будут храниться. Для этого в таблицу включают два столбца «Коэффициент А» и «Коэффициент В». Тогда формула оклада вводится только один раз, а затем копируется на всех сотрудников.

Лабораторная работа №9 (PPPLAB1)

Исходные данные для этой работы желательно (но не обязательно!) задавать таким образом, чтобы в их поведении прослеживалась какая-либо закономерность (например, повышение или понижение значений). Тогда проводимый анализ и его результаты будут более интересными.

Лабораторная работа №10 (PPPLAB2)

Часто забывают, что процентная ставка в финансовых формулах задается либо в процентном формате Excel, либо в долях.

В задании 2 все варианты располагают на листе Excel вместо того, чтобы поместить решение только одного варианта, а остальные оформить через Сценарии. В отчет по сценарию следует включить ячейку, где находится функция ЕСЛИ, возвращающая текст, соответствующий выбранному финансовому решению, например, «Нести в банк» или «Дать в долг».

В задании 3 найденное значение процентной ставки будет являться «положением безразличия», т.е. обе финансовые операции по результатам будут одинаковы. Это следует иметь в виду, отвечая на поставленный в задании вопрос.

В задании 4 забывают о том, что на N -летний период идет не вся стоимость квартиры, а стоимость квартиры за вычетом начального взноса. Под комиссионными понимается все то, что заплачено сверх стоимости квартиры.

В задании 5 величина нормы ищется итерационным методом и при некоторых значениях исходных данных этот метод не может найти решение. Следует поварьировать исходными данными и подобрать такие их значения, чтобы решение было найдено.

Лабораторная работа №11 (PPPLAB3)

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

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

Лабораторная работа №12 (PPPLAB4)

В задании 2 следует наглядно оформлять и исходные данные, и результаты решения: использовать текстовые подписи, комментарии к ячейкам, цветовые выделения. К отчету желательно приложить оформленную в текстовом редакторе экономико-математическую модель задачи с анализом влияния изменения ресурсов на решение задачи.

Литература

Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: BHV, 2001. – 816 с.

Матвеев Л.А. Компьютерная поддержка решений. – СПб.: Специальная литература, 1998. – 472 с.

Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финан-сово-экономические расчеты в Excel. Изд. 3-е. – М.: Филинъ, 1999. – 328 с.

Гусева О.Л., Миронова Н.Н. Excel для Windows. Практические работы //Информатика и образование. – 1996. – №2–6.

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

Качанов В.И. Компьютерные вычисления в средах Excel и MathCAD. – М.: Горячая линия-Телеком, 2002. – 328 с.

Буров А.В., Миньков С.Л., Ушаков В.М. Моделирование экономических процессов и систем: Учебное пособие. В 2-х частях: Ч.1, 158 с., 2001 г.; Ч.2.. 167 с., 2003 г. Изд-во Томского государственного педагогического университета.

[1] Visible Calculator (Наглядный калькулятор).

[2] Не правда ли, удачное название, связанное с игрой слов: Excellent – прекрасный, Cell – ячейка, Excel – превосходить.

[3] Кроме упомянутых программных продуктов также известны Quattro Pro (фирма Word Perfect – Novell Applications Group), SuperCalc (фирма Computer Associates).

[4] Если вы хотите, чтобы число воспринималось как текст, поставьте перед ним апостроф, т.е. знак «’».

[5] Какой именно знак используется для разделения целой и дробной части числа, определяется в операционной системе. Например, в Windows 9X это можно сделать, выбрав путь Пуск | Настройка | Панель управления | Языки и стандарты | Числа | Разделитель целой и дробной части числа и определив нужный знак.

[6] Частая ошибка – начинают формулу не со знака =, а с пробела.

^[7] Для более подробного знакомства с системой хранения дат и времени суток в MS Excel обратитесь в Справку Microsoft Excel.

[8] Формулы тоже можно использовать, но тогда все адреса ячеек, используемые в них, должны иметь абсолютную адресацию.

[9] С некоторыми из них: МАКС, МИН, СРЗНАЧ, СЧЕТЕСЛИ вы познакомились ранее.

2 Файлы надстроек имеют расширения.xla,.xls и по умолчанию хранятся в папке Library (C:\Program Files\Microsoft Office\Office\Library).

[10] Приведение стоимостного показателя, относящегося к будущему, на более ранний момент времени.

[11] В Excel 2002 она называется БС.

[12] Поток платежей, все члены которого положительны и поступают через одинаковые интервалы времени, называется финансовой рентой, или аннуитетом.

[13] В Excel 2002 она называется ПС.

[14] В Excel 2002 она называется ЧПС.

[15] В Excel 2002 она называется ПЛТ.

[16] В Excel 2002 она называется СТАВКА.

[17] Очков В.Ф. Mathcad PLUS 6.0 для студентов и инженеров. – М.: КомпьютерПресс, 1996. – 238 с.

[18] Если сравниваемые значения расположены в столбце справа от искомых данных, то используется функция ГПР.

[19] Если нужен сам элемент, а не его позиция в диапазоне, то используется функция ПРОСМОТР.

[20] Это определяется тем, как была проведена установка MS Office на ПК. Подключение при первом обращении задано в установке «Типичная».

[21] Бинарные переменные могут принимать одно из двух значений: 0 или 1 и используются в задачах назначения.

[22] По опыту работы с первым изданием пособия: Миньков С.Л. EXCEL. Лабораторный практикум. – Томск: ТМЦДО, 2000. – 105 с.