Закрепление макроса за различными элементами. 3.4.1. Скопируйте и вставьте табл
3.4.1. Скопируйте и вставьте табл. 18 без заголовка на новый лист.
3.4.2. Для закрепления макроса за пиктограммой:
· выполним командыСервис – Настройка –вкладкаПанели
Инструментов;
· из списка в окне Панель инструментов выберем Стандартная (стоит
по умолчанию), начать на кнопку Добавить;
· в появившемся окне Добавить команды в окне списка Категория
выбрать Макрос OpenOffice.org,и, как в п. 3.1.9, раскрыв соответствующие вложенные папки (<Имя файла> - Standart),ставим курсор на имя папки Module 1;
· в окне Команды ставим курсор на имя нужного макроса, щелкнем по
кнопке Добавить, Закрыть;
· в окне Настройка в поле Содержимое панели инструментов
появился заголовок макроса.В окне Настройка (курсор стоит на названии вашего макроса), щелкнем по кнопке Изменить;
· в открывшемся меню выберем команду Выбрать значок, в окне
Выбор значка выберем любой значок, Ок;
· Еще раз щелкнем по Изменить, выберем команду Переименовать;
· В появившемся окне Переименовать панель в текстовом окне Новое
название удалим имя макроса, Ок;
· Нажмем кнопку Окв окне Настройка. На панели инструментов
Стандартная выведен значок вашего макроса, нажатием на который можно запустить макрос на выполнение.
3.4.3. Создадим графический объект в виде прямоугольника с помощью панели инструментов Рисование (Вид–Панель инструментов–Рисование) для управления вторым макросом. Для этого:
· выберем место на листе для кнопки и щелкнем по инструменту Прямоугольник;
· перемещая мышь,нарисуем прямоугольник;
· в поле Макрос раскроем папку Module 1, находящуюся в папке вашего файла;
· в поле Существующие макросы в: курсором мыши выберем имя макроса, далее нажмем кнопку Назначить, Ок;
· выполним макрос, активизировав предварительно ячейку А1.
Отчет по работе
Опишите порядок создания макроса, закрепления за макросом кнопки, создания графического объекта.
Литература: [2], c. 49-54.
Работа 7. РЕШЕНИЕ ЗАДАЧ ПРОГНОЗИРОВАНИЯ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ
Цель работы
Научить пользователя основным принципам и приемам работы в электронных таблицах при решении задач прогнозирования.
Основные теоретические положения
В табличные процессоры включены несколько инструментов для исследования и прогнозирования финансовых и экономических задач: подбор параметра, поиск решения, таблица подстановки, прогнозирование с использованием графика и так далее. Данная работа служит для ознакомления с двумя режимами: подбор параметра и таблица подстановки.
Порядок выполнения работы
В данной работе необходимо решить и провести исследование (прогнозирование) упрощенной задачи расчета прибыли при реализации определенного количества изделий с определенной величиной наценки к закупочной цене.
Задание 1. Создать таблицу решения задачи, присвоить имена ячейкам для ввода конечной формулы.
Задание 2. Создать таблицы данных при изменении одной и двух величин, пересчитать таблицу данных при новых условиях.
Задание 3. Создать таблицу данных при одновременном изменении двух величин.
Задание 4. Осуществить подбор параметра для поиска значения исходного параметра, обеспечивающего заданный конечный результат.
Выполнения задания 1
3.1.1. Создание таблицы:
· откройте новую таблицу;
· в ячейку А1 введите заголовок: ЗАКУПКА, в ячейку D1 введите заголовок: РЕАЛИЗАЦИЯ, установите в первой строке Размер – 16, Полужирный;
· в ячейки А2, А3 и А4 введите соответственно наименования: Количество, Цена, Стоимость;
· в ячейку В2 введите число 30, в Е2 – 0,15;
· активизируйте ячейку В3. В Строке формул щелкните мышью на знак равенства – строка готова к вводу формулы. Введите формулы согласно табл. 19 и убедитесь, что в этих ячейках появились результаты вычислений;
· в ячейки D2, D3, D4, D5 и D7 соответственно введите наименования: Наценка, Доход, Расход, Зарплата, Прибыль (см. табл. 20);
· установите обрамление двойной линией нижней границы первой строки и осуществите автоподбор ширины столбцов А и D(выделить А и D; Формат – Столбцы – Автоподбор ширины).
|
Таблица 19. Исходные данные
| Ячейка | Формула | Результат |
| В3 | =10000*В2/(В2-5) | |
| В4 | =В2*В3 | |
| Е3 | =В4*Е2 | |
| Е4 | =В4/(150-В2) | |
| Е5 | =1000*В2 |
Затем щелкните кнопку ОК. В поле имен адрес заменится именем ячейки.
В программе OpenOffice.org Calcвыполните команды меню Вставка – Названия – Определить.В диалоговом окне Определить название в поле Название ввести Доход, Ок.
· аналогичным образом ячейкам Е4 и Е5 присвойте имена Расход, Зарплата соответственно.
3.1.3. Ввод формул с использованием имен ячеек:
· активизируйте ячейку Е7 и наберите в ней знак равенства;
· щелкните кнопкой мыши по ячейке Е3 с именем Доход, и название этой ячейки скопируется в ячейку Е7. В программе OpenOffice.org Calcвыполните команды Вставка – Названия – Вставить – Доход, Ок.;
· наберите знак минус;
· щелкните кнопкой мыши по ячейке Е4 с именем Расход. Имя скопируется в ячейку Е7. В программе OpenOffice.org Calcвыполните команды Вставка – Названия – Вставить – Расход, Ок.;
· наберите знак минус;
· щелкните кнопкой мыши по ячейке Е5 с именем Зарплата. Имя скопируется в ячейку Е7. В программе OpenOffice.org Calcвыполните команды Вставка – Названия – Вставить – Зарплата, Ок.;
· нажмите кнопку клавиатуры Enter. В ячейке Е7 появится результат 21000.
Щелкните в ячейке Е7 и убедитесь, что в Строке формул формула содержит не адреса, а имена ячеек (=Доход–Расход–Зарплата). Этим обеспечивается использование в формуле абсолютной адресации ячеек (при копировании адреса ячеек не меняются). В результате получим табл. 20 (режим показа формул) или табл. 21 (режим показа вычислений);
· для проверки правильности решения задачи введите в ячейку В2 число 50. Программа произведет пересчет по формулам, и в ячейке Е7 появится результат 27777,78… . Таким образом, можно прогнозировать результат при изменении любого параметра, входящего в расчеты.
3.1.4. Форматирование таблицы:
· в ячейках В3, В4, Е3, Е4, Е5, Е7 установите формат числа Денежный,а в ячейке Е2– Процентный (Формат – Ячейки – Число);
· установите ширину столбцов В и Е – 12.
Таблица 20. Таблица расходов и реализации в режиме показа формул
| A | B | C | D | E | |
| ЗАКУПКА | РЕАЛИЗАЦИЯ | ||||
| Количество | Наценка | 0,15 | |||
| Цена | =10000*B2/(B2-5) | Доход | =B4*E2 | ||
| Стоимость | =B2*B3 | Расход | =B4/(150-B2) | ||
| Зарплата | =1000*B2 | ||||
| Прибыль | =Доход-Расход-Зарплата |
Таблица 21.Таблица расходов и реализации в режиме показа вычислений
| A | B | C | D | E | |
| ЗАКУПКА | РЕАЛИЗАЦИЯ | ||||
| Количество | Наценка | 15% | |||
| Цена | 12 000р. | Доход | 54 000,00р. | ||
| Стоимость | 360 000р. | Расход | 3 000,00р. | ||
| Зарплата | 30 000,00р. | ||||
| Прибыль | 21 000,00р. |
Выполнение задания 2.
3.2.1. В ячейки А10 и А11 введите числа 10 и 20 соответственно.
3.2.2. При помощи автозаполнения введите в ячейки А12:А19 последовательность чисел от 30 до 100 с шагом 10. Для этого:
· выделите диапазон ячеек А10:А11;
· установите курсор мыши в правом нижнем углу выделенного блока (курсор примет вид черного крестика);
· нажмите левую кнопку мыши и, не отпуская ее, протяните вниз до ячейки А19.
3.2.3. В ячейку В9 скопируйте формулу из ячейки Е7.
3.2.4.При работе в Excel выделите диапазон ячеек А9:В19 и исполните
команды Данные – Таблица подстановки.В окнеТаблицы подстановкиввести: Подставить значения по строкам $B$2, щелкните ОК;
В программе OpenOffice.org Calcвыделите диапазон ячеек А10:В19 и выполните команды Данные – Совмещенные операции. В окне Совмещенные операции ввести:
- в поле Формулы – $В$9;
- в поле Столбец ввода – $В$2;
- щелкните ОК.
В ячейках В10:В19 появятся значения величины прибыли в зависимости от количества проданных изделий (табл. 22). Таким образом, можно прогнозировать результат в наглядном табличном виде при изменении одного параметра.
3.2.5. Проверьте правильность ваших действий: для 30 изделий прибыль (табл. 21) должна составлять 21 000,00… (сравните с табл. 21). При ошибке всю таблицу данных необходимо очистить, выделив ее и выполнив команду Очистить содержимое контекстного меню (щелкнуть правой кнопкой мыши), и повторить действия заново.
3.2.6. Оформите таблицу. Активизируйте ячейку А8, наберите =А2 и нажмите <Enter> (текст из А2 запишется в А8). Аналогичным образом запишите в В8 текст из D7. Сделайте обрамление более толстой линией, а внутренние линии тонкими. Измените цвет шрифта.
Выполнение задания 3.
3.3.1. В ячейку D9 скопируйте формулу из Е7. В программе OpenOffice.org Calcформулу из Е7 скопируйте в ячейку D8, ячейка D9 должна быть пустой!!.
3.3.2. В ячейки D10:D19 скопируйте данные из А10:А19.
3.3.3. В ячейки Е9 и F9 введите числа 0,1 и 0,15 соответственно, установите в них формат числа – Процентный и при помощи автозаполнения заполните ячейки G9:K9 значениями наценки до 40 % с шагом 5 % (аналогично п 3.2.2).
Таблица 22. Результат работы таблицы подстановки
| A | B | C | D | E | |
| Закупка | Реализация | ||||
| Количество | Наценка | 15% | |||
| Цена | 11 111,11р. | Доход | 83 333,33р. | ||
| Стоимость | 555 555,56р. | Расход | 5 555,56р. | ||
| Зарплата | 50 000,00р. | ||||
| Прибыль | 27 777,78р. | ||||
| 27 777,78р. | |||||
| 18 571,43р. | |||||
| 17 948,72р. | |||||
| 21 000,00р. | |||||
| 24 415,58р. | |||||
| 27 777,78р. | |||||
| 30 909,09р. | |||||
| 33 653,85р. | |||||
| 35 809,52р. | |||||
| 37 058,82р. | |||||
| 36 842,11р. |
3.3.4. Выделите диапазон ячеек D9:К19 и исполните команды Данные - Таблица подстановки. В программе OpenOffice.org Calcвыделите диапазон D9:К19, выполните команды Данные – Совмещенные операции.
3.3.5. В диалоговом окне ввести
В программе Ехсе1:
Подставить значения по столбцам $E$2;
Подставить значения по строкам $B$2;
В программе OpenOffice.org Calc:
Формулы $D$8
Строка ввода $E$2
Столбец ввода $B$2
НажмитеОК.
3.3.6. Установите денежный формат в ячейках Е10:К19.
Таким образом, результат можно прогнозировать в наглядном табличном виде при изменении двух параметров. В ячейках Е10:К19 появятся значения величины прибыли в зависимости от количества проданных изделий и величины наценки (табл. 23). Проверьте правильность ваших действий: для 50 изделий при наценке 30 % прибыль должна составлять 111 111,1… (табл. 23). При ошибке всю таблицу данных необходимо очистить и повторить действия заново.
3.3.7. Установите обрамление в ячейках последней созданной таблицы.
Таблица 23. Результат подбора двух параметров одновременно
| D | E | F | G | H | I | J | K | |
| 30 909,09р | 10% | 15% | 20% | 25% | 30% | 35% | 40% | |
| 8 571р. | 18 571р. | 28 571р. | 38 571р. | 48 571р. | 58 571р. | 68 571р. | ||
| 4 615р. | 17 949р. | 31 282р. | 44 615р. | 57 949р. | 71 282р. | 84 615р. | ||
| 3 000р. | 21 000р. | 39 000р. | 57 000р. | 75 000р. | 93 000р. | 111 000р. | ||
| 1 558р. | 24 416р. | 47 273р. | 70 130р. | 92 987р. | 115 844р. | 138 701р. | ||
| 0р. | 27 778р. | 55 556р. | 83 333р. | 111 111р. | 138 889р. | 166 667р. | ||
| -1 818р. | 30 909р. | 63 636р. | 96 364р. | 129 091р. | 161 818р. | 194 545р. | ||
| -4 038р. | 33 654р. | 71 346р. | 109 038р. | 146 731р. | 184 423р. | 222 115р. | ||
| -6 857р. | 35 810р. | 78 476р. | 121 143р. | 163 810р. | 206 476р. | 249 143р. | ||
| -10 588р. | 37 059р. | 84 706р. | 132 353р. | 180 000р. | 227 647р. | 275 294р. | ||
| -15 789р. | 36 842р. | 89 474р. | 142 105р. | 194 737р. | 247 368р. | 300 000р. |
3.3.8. Пересчет таблицы данных:
а) активизируйте ячейку D9 (В программе OpenOffice.org Calc активизируйте ячейку D8), щелкните в Строке формул и наберите в конце формулы +1. Завершите ввод формулы и убедитесь, что произошел автоматический пересчет таблицы (например, данные в строке 12 увеличились на 1);
б) в ячейке К9 измените 40 % на 50 %. После ввода нового значения произойдет автоматический пересчет таблицы данных в столбце К.
Выполнение задания 4.
3.4.1. В ячейку Е2 введите величину наценки 20 %.
3.4.2. Для данной величины наценки необходимо определить количество изделий, обеспечивающее прибыль 50 000 р. Для этого активизируйте ячейку Е7 и выполните команды Сервис – Подбор параметра.При этом в диалоговом окне Подбор параметра в поле Установить в ячейке(в программе Calc – в полеЯч. с формулой) будет введен абсолютный адрес ячейки $Е$7.
3.4.3. В поле Значение (в программе Calc – в полеЦелевое значение) – введите число 50 000. В поле Изменяя значение в ячейке (в программе Calc – в полеИзменяемая яч.) щелкните по ячейке В2,будет введен ее абсолютный адрес – $B$2. Щелкните кнопкой ОК.
3.4.4. В окне Результат подбора параметра щелкните ОК. Проверьте правильность решения: в ячейке В2 количество изделий равно 43.
Самостоятельная работа
3.5.1. Введите дополнения в табл. 22, показанные в табл. 24:
Таблица 24.Ввод дополнений в табл. 22
| Ячейка | Содержимое ячейки | Формат ячейки |
| А6 | Ставка налога | Текст |
| В6 | Денежный | |
| D6 | Налог | Текст |
| Е6 | =В6*(1+В4/100000) | Дать имя Налог, Денежный |
| Е7 | Дополните в формулу вычитание – Налог | Денежный |
В результате получим табл. 25.
Таблица 25. Таблица закупок и реализации с учетом налога
| A | B | C | D | E | |
| Закупка | Реализация | ||||
| Количество | Наценка | 20% | |||
| Цена | 11 111р. | Доход | 111 111,11р. | ||
| Стоимость | 555 556р. | Расход | 5 555,56р. | ||
| Зарплата | 50 000,00р. | ||||
| Ставка налога | Налог | 6 556р. | |||
| Прибыль | 49 000,00р. |
3.5.2. При количестве изделий, равном 50, и наценке 20 % определите ставку налога, обеспечивающую прибыль 30 000 р. (В ячейке В6 появится результат 3898).
3.5.3. Продемонстрируйте работу преподавателю. Сохраните документ в своей папке, завершите работу приложения.
Отчет по работе
. Распечатка полученных результатов в режиме счета и формул.
Литература: [3], c. 56-62.