Удаление всей таблицы данных

8.1.Восстановить вид таблицы, повторив действия, описанные в п.п. 6.2 - 6.3.

8.2.Попытаться удалить данные столбца D созданной таблицы. Попытаться удалить диапазон ячеек из созданной таблицы.

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

Практическое задание №4. Использование инструмента "Подбор параметра" для решения типовых задач.

 

Цель работы

Ознакомиться со способами подбора параметров.

 

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

Выполнить задания раздела. Составить отчет по учебному заданию и защитить его у преподавателя.

 

Содержание отчета

- наименование и цель работы;

- задание на работу;

- результаты выполнения работы.

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

4.1.Создать таблицу, приведенную справа на рис.4.1, воспользовавшись расчетными формулами из левой таблицы. Эта таблица представляет гипотетическую смету расходов на выполнение некоторых работ, где отдельные статьи сметы связаны между собой определенными зависимостями. При создании таблицы (сметы) исходными данными являются величины Мк и Зп, а Q - задаваемое значение. Анализ сметы сводится к сопоставлению отдельных статей расходов, общих расходов и прибыли с общей стоимостью работ.

4.2. Для того, чтобы воспользоваться командой "Подбором параметра", нужно щелкнуть правой кнопкой мыши рядом с последней вкладкой в высветившемся меню нажать «Настройка панели быстрого доступа»:

Далее в появившемся окне выполнить четыре действия, которые показаны на рисунке 1. «Настройка». 2.«Все команды». 3. «Подбор параметра». 4. «Добавить». После этого на панели быстрого доступа появится соответствующий значок.

После отображения воспользуйтесь значком «Подбор параметра».

 

• при какой общей стоимости работ прибыль достигнет величины 500 т.р.;

 

• при какой общей стоимости работ прибыль достигнет величины 1000 т.р.;
• при какой общей стоимости работ отношение прибыли к общей стоимости достигнет величины 50%.

Рис 4.1

4.3.Изменяя значения общей стоимости работ (Q) и копируя результаты на в диапазон ячеек (E1:K3), построить таблицу, отражающую зависимость величин (П/Q)% и прибыли (П) от общей стоимости работ (Q). Аналог такой таблицы приведен на рис.4.2. При копировании необходимо указывать абсолютный адрес ячеек, т.е. в ячейке F2, формула будет выглядеть следующим образом: =F3-$C8-$C10-$C9, в ячейке F2: =F2/F3.

4.4.Построить графики зависимостей (П/Q)% и прибыли (П) от общей стоимости работ (Q) аналогично графикам приведенным на рис.4.2.

 

Рис. 4.2

4.5.Создать таблицу, приведенную на рис.4.3. В этой таблице представлены гипотетические поквартальные сведения о сбыте некоторых товаров, себестоимости продукции, доходах от реализации и величине прибыли. Объем сбыта зависит от некоторого сезонного коэффициента (Кi), а также не линейно зависит от затрат на рекламу продукции при прочих фиксированных факторах. При создании таблицы исходными данными являются величины Кi, Qi, C и R. Остальные величины вычисляются по формулам, приведенным в комментариях к таблице (диапазон A14:D17). Создав таблицу, убедиться, что полученные Вами результаты расчетов по формулам совпадают с приведенными в таблице рис.4.3. Точность представления данных в таблице – два десятичных знака после запятой, для процентных величин – младший разряд целой части числа.

Рис. 4.3

4.6.Пользуясь "подбором параметра", определить:

• можно ли получить в IV квартале прибыль P₄=100000 т.р., изменяя расходы на рекламу Q₄;
• определить величину расходов на рекламу в IV квартале (Q₄), необходимую для получения прибыли P₄=150000 т.р.;

4.7.Сделать выводы о возможностях использования "подбора параметра" и "правилах" применения данного инструмента, выполнив следующий эксперимент.

4.7.1.Задать величину затрат на рекламу Q₄=2000 т.р

4.7.2.Подбирая параметр Q₄, определить, при каком значении Q4 будет достигнута величина прибыли P₄=30000 т.р.

4.7.3.Задать величину затрат на рекламу Q₄=60000 т.р

4.7.4.Подбирая параметр Q4, определить, при каком значении Q4 будет достигнута величина прибыли P₄=32000 т.р.

4.8.Найти близкие к максимальным значения:

• прибыли для всех четырех кварталов (Pi);
• суммарную (годовую) прибыль (P);
• квартальные значения Qi;
• суммарные годовые расходы на рекламу (Q);
• долю расходов на рекламу в общем доходе от реализации продукции (Q/D).

Результат разместить в ячейках F11:G11. Для определения названных величин рекомендуется последовательно воспользоваться несколько раз подбором параметра.

4.9.Пользуясь расчетными формулами (A14:D18), построить на отдельном листе таблицу, отражающую зависимости величин V₄, D₄, P₄ от величины Q₄ (для значений Q₄ меняющихся от 10000 до 100000 с шагом 10000). Построить два графика (подобных представленным на рис.4.2), на которых будут отображены зависимости V₄=f(Q₄) для первого графика и D₄=f(Q₄) и P₄=f(Q₄) для второго графика рис.4.4.

Рис. 4.4

4.10.Точность подбора параметра.

4.10.1.Записать в ячейках С2, С3 и С4 (рис.4.5) исходные данные и формулу для вычисления произведения двух чисел Y=A*X. Исходные значения сомножителей: А=0,5 и Х=2,35.

4.10.2.Выполнить следующие действия:

• скопировать исходные данные и формулу (С2:С4) в диапазоны Е2:Е4, G2:G4 и I2:I4;
• для столбцов E, G и I установить разрядность отображаемых значений равную соответственно 4, 6 и 16 разрядов после десятичной запятой, как показано на рис.4.5.

4.10.3.Выполнить "подбор параметра" для нахождения первого сомножителя (А), расположенного в ячейке C2 при C3=2,35, искомом значении целевой ячейки C4=4,3758 и начальном значении C2=0,5.

4.10.4.Повторить "подбор параметра" для той же формулы, записанной в диапазонах Е2:Е4, G2:G4 и I2:I4.

Рис 4.5

4.10.5.Выполнить умножение вручную для данных в ячейках Е2:Е3, G2:G3 и I2:I3 с заданной разрядностью и записать результаты в ячейки Е6, G6 и I6 соответственно.

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

4.11.Создать таблицу, в которой выполняется возведение числа Х в степень Y по образцу, приведенному на рис.4.6. Формула Z=X^Y, обеспечивающая вычисления, записана в ячейке D12, а исходные данные Х=2 и У=2 - в ячейках D10 и D11 соответственно. Скопируйте формулу в ячейки B12 и C12.

Рис.4.6

4.11.1.Пользуясь "подбором параметров", выполнить поиск такого значения Х (при неизменном Y=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках В10:В12.

4.11.2.Аналогично п.4.11.1 выполнить поиск значения Y (при неизменном Х=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках С10:С12.

4.11.3.Сравнить результаты непосредственного вычисления (D10:D12) и результаты двух "подборов параметров" (B10:B12 и C10:C12). Сделать выводы относительно возможности использования инструмента "подбор параметра".

Практическое задание №5. Использование инструмента «Поиск решения».

 

Цель работы

Ознакомиться со способом поиска решения.

 

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

Выполнить задания раздела. Составить отчет по учебному заданию и защитить его у преподавателя.

 

Содержание отчета

- наименование и цель работы;

- задание на работу;

- результаты выполнения работы.

 

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

4.1.Определить максимальное значение годовой прибыли (P), которое может быть получено за счет изменения месячных величин расходов на рекламу (Qi), пользуясь инструментом "поиск решения".

4.1.1. Для того, чтобы запустить инструмент "поиск решения", нужно щелкнуть правой кнопкой мыши рядом с последней вкладкой в высветившемся меню нажать «Настройка панели быстрого доступа»:

Далее в появившемся окне во вкладке «Надстройка» нажать кнопку «Перейти»

После этого в появившемся окне поставить галочку около Поиска решения и нажать OK.

Во Вкладке «Данные» нажать «Поиск решения» просмотреть все варьируемые параметры поиска, задаваемые с помощью диалогового окна "поиск решения", представленного на рис.5.1.

4.1.2.Задать в качестве целевой ячейку, содержащую величину годовой прибыли (P).

4.1.3.Определить, будет ли в результате поиска достигаться определенное значение цели (прибыли) или ее экстремальное (максимальное или минимальное) значение.

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

1.5.Определить и последовательно добавить в список выражения, ограничивающие условия поиска. В качестве таких выражений задать следующие ограничения:

а) затраты на рекламу в каждом квартале (Qi) не могут быть отрицательными;

Рис.5.1

б) цена продукции не должна быть меньше себестоимости.

4.1.6.Выполнить поиск решения. Убедиться, что решение найдено и результаты поиска совпадают с результатами, приведенными на рис.5.2.

Рис.5.2

4.1.7.Занести полученные результаты в таблицу и сохранить на отдельном листе отчет по результатам поиска решения.

4.2.Предявить результаты преподавателю.

Список рекомендуемой литературы:

 

1. Ковальски С. Excel 2003 без проблем. – М.: Бином, 2005.

2. Харис М. Программирование для Microsoft Excel 2003 за 21 день. – М.: Вильямс, 2005

3. Уокенбах Д. Профессиональное программирование на VBA в Excel 2003 – М., 2005