Файл: Надстройки ms excel обзор, возможности, примеры применения.docx

Рисунок 1 – Включение надстроек

Разберем для примера использование простейшей надстройки Автосохранение. После загрузки этой надстройки в меню Сервис появляется команда Автосохранение. Если выполнить эту команду, то откроется диалоговое окно (рисунок 2), в котором нужно указать параметры автосохранения рабочих книг.

Рисунок 2 – Диалоговое окно Автосохранение.

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

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

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

2. Практическое применение надстроек

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

Одной из самых используемых надстроек считается Поиск решения.

Рассмотрим применение надстройки для решения задачи оптимального выпуска продукции.

Задача. Предприятие решило производить два вида объективов А и В. Объектив вида А состоит из 3-х линзовых компонентов, вид В - из 4-х. За неделю можно изготовить не более 1750 линз. На сборку объектива вида А требуется - 10 минут, вида В - 25 минут. Рабочая неделя для 4 сотрудников составляет 160 часов.

Сколько объективов А и В надо изготовить, чтобы получить максимальную прибыль, если объектив вида А стоит 3400 рублей, вида В - 4800 рублей.

Решение. Пусть надо изготовить х (штук) объективов вида А и у (штук) объективов вида В. Тогда целевая функция (Р), в этом случае прибыль от продажи продукции, будет рассчитана по формуле:

P = 3300х +4500у.

Затраты на комплектацию (общее количество произведенных линз для обоих объективов) соответственно равны:

Zk = 3х +4у,

Zk < 1700.

Затраты по времени на сборку объективов даны в минутах - необходимо перевести в часы, т.е. 20 минут – 1/3 часа, 35 минут – 35/60 часа.

Таким образом, временные затраты рассчитываются следующим образом:

Zt = 1/6х + 5/12у,

Zt < 160.

Для решения этой задачи необходимо составить и заполнить таблицу в Excel, как показано на рисунке 3.

Рисунок 3 - Заполнение таблицы исходными данным

Ячейки B2 и В3 переименуем в х и у.

В ячейку B5 введем: = 3400*x+4800*y

В ячейку B7 введем: =3*x+4*y

В ячейку B8 введем: =1/6*x+5/12*y

Выделим ячейку В5 и выберем в меню Сервис/Поиск решения и заполним ячейки этого окна в соответствии с рисунком 4.

Рисунок 4 - Пример заполнения окна Поиск решения

Нажмем кнопку Выполнить. Если все сделано правильно, то решение будет таким, как указано ниже (рисунок 5).

Рисунок 5 - Таблица результатов

То есть, были подобраны конкретные значения для расчета оптимальной прибыли.

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

Рассмотрим надстройку категории «Что-если» подбор параметра на примере следующей задачи.

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

Исходные данные приведены на рис.6.

Известно, что в штате фирмы состоят: 6 курьеров; 8 младших менеджеров; 10 менеджеров; 3 заведующих отделами; 1 главный бухгалтер; 1 программист; 1 системный аналитик; 1 генеральный директор фирмы.

Рисунок 6 – Исходные данные

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

1. Выберите коэффициенты уравнений для расчета согласно табл. 1 (один из пяти вариантов расчетов).

2. Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 р. Результаты подбора значений зарплат скопируйте в табл. 2 в виде специальной вставки.

Решение

Математическая постановка задачи. Базовым окладом является ставка курьера, которая обозначается через х.

Каждый оклад является линейной функцией от оклада курьера, а именно: Зарплата = А*х + В где х — оклад курьера; А и В - — коэффициенты, показывающие: А-, — во сколько раз превышается значение х; В — на сколько превышается значение х.

Приведем пример. Для вычисления ставок работников при фонде зарплаты 400000 руб. нужно в окне подбора параметра выставить следующие данные:

Рисунок 7 – Применение подбора параметров

В результате получим:

Рисунок 8 – Результат подбора параметра

Общая таблица будет иметь вид:

Рисунок 9 – Результат выполнения

2.3. Таблицы данных

Инструмент «Таблица данных» предназначен для того, чтобы рассчитывать результат при различных вариациях одной или двух определенных переменных. После расчета все возможные варианты предстанут в виде таблицы, которую называют матрицей факторного анализа. «Таблица данных»относится к группе инструментов «Анализ «что если»», которая размещена на ленте во вкладке «Данные»в блоке «Работа с данными». До версии Excel 2007 этот инструмент носил наименование «Таблица подстановки», что даже более точно отражало его суть, чем нынешнее название.

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

Но также следует знать

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

Приведем пример использования данной надстройки.

Стоимость оборудования равна 1000000 руб. Бизнесмен хочет взять кредит на эту сумму на 10 лет. Необходимо вычислить выплаты по кредиту при разных ставках процента по кредиту: 5%, 10%, 12%, 15%.

Введем исходные данные:

Рисунок 10 – Исходные данные

Введем формулу для вычисления периодических выплат:

Рисунок 11 – Функция ПЛТ

Для выполнения таблицы данных нужно вызвать окно надстройки и задать следующую информацию:

Рисунок 12 – Использования таблицы данных

Получим:

Рисунок 13 – Полученный результат

Заключение

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

В настоящее время Microsoft Excel – ведущая программа обработки электронных таблиц, представляющая собой достаточно мощное средство разработки информационных систем, которое включает как электронные таблицы (со средствами финансового и статистического анализа, набором стандартных математических функций, доступных в языках программирования высокого уровня, рядом дополнительных функций, встречающихся только в библиотеках инженерных программ), так и средства визуального программирования (Visual Basic for Application).

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

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

Список использованных источников

1. Павлыш В.Н., Анохина И.Ю., Кононенко И.Н., Зензеров В.И. Начальный курс информатики для пользователей персональных компьютеров / Уч.-метод. пособие. – Донецк: ДонНТУ, 2012. – 235 с.

2. Гарнаев А. Использование MS Excel и VBA в экономике и финансах. – Санкт-Петербург, BHV, 2011. – 336 с.

3. Персон Р. Microsoft Excel 2007 в подлиннике. Т. I. – Санкт-Петербург, BHV, 2013. – 1272 с.

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

5. Дубина А., Орлова С., Шубина И., Хромов А. Excel для экономистов и менеджеров. Экономические расчёты и оптимизационное моделирование в среде Excel. – СПб.: Питер, 2014. – 295 с.: ил.

6. Конрад Карлберг. Бизнес-анализ с помощью Excel 2010.: Учеб. пособие/ Пер. с англ. – М.: Издат. дом “Вильямс”, 2011. – 480 с.: ил.

Список иллюстраций (рисунков)
Рисунок 1 - Включение надстроек…………………………….………………10

Рисунок 2 - Диалоговое окно Автосохранение…………………………...…..11

Рисунок 3 - Заполнение таблицы исходными данным………………….........13

Рисунок 4 - Пример заполнения окна Поиск решения……………………….13

Рисунок 5 - Таблица результатов………………………………..…………….14

Рисунок 6 - Исходные данные …………………………………………..…….14

Рисунок 7 - Результаты подбора значений зарплат………………….....……15

Рисунок 8 - Результат подбора параметра…………………….…..………….16

Рисунок 9 - Результат выполнения…………………………………..……..…16

Рисунок 10 - Исходные данные...……………………….……...…...………....17

Рисунок 11 - Функция ПЛТ………………………….……………...……….....18

Рисунок 12 - Использования таблицы данных……………………..……..…..18

Рисунок 13 - Полученный результат………………..………………….....…...18