КОММЕНТАРИИ

к выполнению лабораторных работ в среде 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/(xsin(x)), т.е. в этом случае F(x) = 2/(xsin(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 следует наглядно оформлять и исходные данные, и результаты решения: использовать текстовые подписи, комментарии к ячейкам, цветовые выделения. К отчету желательно приложить оформленную в текстовом редакторе экономико-математическую модель задачи с анализом влияния изменения ресурсов на решение задачи.