Решение инженерных задач в excel
Похожие темы научных работ по экономике и бизнесу , автор научной работы — Екшибаров Владимир Николаевич
Использование пакетов прикладных программ в процессе обучения будущих экономистов информационным дисциплинам
Текст научной работы на тему «Выполнение инженерных расчетов в Excel»
гериям, что в дальнейшем может служить статистической базой для определения нормативов некоторых показателей финансового положения в разрезе отдельного региона России, а также базой для формирования экономического рейтинга предприятия.
Подводя итог, следует отметить трудность в выборе критериев оценки неплатежеспособности предприятий: у должника могут отсутствовать оборотные средства, по его бухгалтерский баланс свидетельствует о наличии большой дебиторской задолженности; у должника может быть большой долг по кредитам, но имеются современные производственные
мощности, которые будут приносить прибыль в будущем и т.д. Поэтому, по нашему мнению, в российских условиях прежде всего необходимо обращать внимание на динамику изменения показателя, а не на его абсолютную величину. Только после расчета необходимых показателей (определяющих при принятии решения о платежеспособности рассматриваемого предприятия) и сравнения их с нормативными значениями, учитывающими особенности отраслевой и общеэкономической специфики, как в России, так и отдельных регионов, можно дать такую оценку финансового состояния предприятия, которую можно считать достоверной. ,
ВЫПОЛНЕНИЕ ИНЖЕНЕРНЫХ РАСЧЕТОВ В EXCEL
В настоящее время табличный процессор EXCEL компании Microsoft является одним из мощных средств автоматизации решения самых различных расчетных задач. В первую очередь, что вполне оправдано, EXCEL используется как в финансовой сфере ¿так и как средство обработки больших массивов числовой информации. Тем не менее сегодня все больше технических специалистов, инженеров и ученых начинают использовать EXCEL для сложных расчетов [1].
В этой связи можно выделить две большие категории принципиально разных задач, для решения которых целесообразно использовать EXCEL:
]) выполнение цепочечных расчетов по сложным формулам. При этом объем исходных данных невелик и они вводятся преимущественно вручную. Другими словами, “мало данных, но сложные расчеты”;
2) аналитическая обработка данных, которая во многих случаях сводится к простым операциям над большими массивами данных. Несмотря на простоту этих операций, такие вычисления вполне целесообразно выполнять с помощью EXCEL. Другими словами, “несложные расчеты, но много данных’’
Для инженерно-технических работников и студентов инженерных специальностей большой интерес представляет вопрос о применении процессора электронных таблиц EXCEL для выполнения расчетов, связанных с решением задач первой из названных категорий.
К таким задачам могут быть отнесены многие задачи, решаемые в курсовых проектах ряда общеинженерных дисциплин, в том числе в курсовом проекте по теории механизмов и машин (ТММ).
При выполнении курсового проекта традиционно графическими и графоаналитическими методами большинство решаемых задач сводится к задачам анализа (кинематического и динамического), так как решение задач синтеза указанными методами требует больших затрат времени на выполнение аналогичных графических построений. Применение аналитических методов исследования кинематики и динамики механизмов с дальнейшей реализацией на ЭВМ значительно сокращает сроки решения поставленных задач, повышает точность расчетов, а также открывает возможность решения задач последовательного синтеза и оптимизации параметров механизма: размеров звеньев, их масс и т.д.
Безусловно, получение аналитических выражений и формул расчета является трудоемкой задачей и требует соответствующего уровня математической подготовки исследователя.
Реализация аналитических методов расчета параметров механизма может быть выполнена на ЭВМ с помощью программ расчета на одном из языков программирования. Однако в этом случае написание программы требует определенного опыта работы на том или ином языке программирования, а иногда написание программы под силу лишь профессиональному программисту.
Структура отдельной программы, составленной в EXCEL, достаточно проста. Файл расчетов является рабочей книгой, которая может состоять из нескольких листов, и их количество может быть различным в зависимости от желания пользователя. Рабочий лист представляет собой электронную таблицу с множеством ячеек, в которые помещаются исходные или расчетные данные. Данными могут быть числа или текст, а также формулы, которые могут использовать данные других ячеек листа, другого листа открытой рабочей книги или даже другой рабочей книги-файла. Размер рабочего листа достаточно велик и имеет размеры в 65586 строк и 256 столбцов.
С це7\ыо компактности отображения расчетов на экране монитора представляется удобным вести расчет поблочно на отдельных рабочих листах, чтобы по возможности содержимое таблицы было соизмеримо с размером экрана в удобном для просмотра масштабе.
Для реализации аналитических методов на ЭВМ па кафедре механики машин и сооружений ИТАИ была предпринята попытка использования табличного процессора EXCEL при выполнении курсового проекта поТММ. В рамках учебноисследовательской работы студентам 3 курса ИТАИ было предложено выполнить кинематические и динамические расчеты для некоторых плоских рычажных механизмов в электронных таблицах EXCEL.
Что же показал опыт использования электронного табличного процессора при выполнении курсового проектирования по ТММ?
Во-первых, наглядно были продемонстрированы студентам новые возможности использования программных средств ЭВМ при решении инженерных задач.
Во-вторых, автоматизация расчетов и скорость их выполнения позволила студентам больше времени уделить теории изучаемых вопросов и физической сущности процессов изменения параметров механизма. Появляется резерв времени, что дает возможность значительно расширить и усложнить задачи исследований.
В-третьих, возрастает информативность результатов расчетов и оперативность их графического отображения. Диаграмма изменения того или иного параметра может быть помещена на том же листе, где и таблица данных расчета.
В-четвертых, сравнительная простота ввода текста и формул расчета в ячейки рабочего листа позволяет выполнять оформление расчетов на рабочем листе пользователю, имеющему лишь минимальные навыки работы с табличным процессором.
В-пятых, результаты расчетов позволяют считать вполне приемлемым использование табличного процессора при выполнении инженерных расчетов в курсовом проектировании.
При учитывании накопленного опыта использования аналитических методов расчета и целесообразности выполнения ряда инженерных расчетов на ЭВМ была организована лабораторная работа по ТММ для выполнения кинематического анализа механизмов на ЭВМ. Данная работа в настоящее время также выполняется в табличном процессоре EXCEL.
Организован и ежегодно работает факультатив поТММ. На занятиях факультатива при участии студентов продолжается работа по дальнейшей разработке и совершенствованию программ расчета механизмов в табличном процессоре EXCEL. В компьютерном классе ИТАИ
имеется возможность охватить большое Литература
число студентов, желающих использовать 1. Дубина А.Г. Машиностроительные
ЭВМ в курсовом проектировании. расчеты в среде Excel 97/2000. - СПб.:
БХВ-Санкт-Петербург, 2000. - 416 с.
РАЗВИТИЕ В ПОНИМАНИИ ЗАРАБОТНОЙ ПЛАТЫ ***
Заработная плата как экономическая категория соединяет в себе много функциональных задач, реализуемых в процессе взаимодействия различных носителей экономических интересов. В данном случае в качестве носителей экономических интересов принимаются в основном две стороны - это работник или коллектив работников и работодатель [ 1. с. 19]. Все они вступают в трудовые взаимоотношения, закрепленные в трудовом договоре.
Заработная плата имеет большое значение в процессе функционирования любой организационной системы (в частности вузовской): она стимулирует достижение более высоких показателей и входит в затраты па покупку рабочей силы. Эти две названные функции являются основными. Таким образом, заработная плата может являться мощным рычагом влияния па достижение наилучших экономических результатов. К сожалению, многие руководители предприятий до конца не осознают всю значимость умения управлять этим рычагом. Они считают, что максимальных экономических результатов можно добиться только совершенной технологией, и при этом незнание механизмов оплаты труда компенсируют повышением оплаты выше средней в отрасли. Однако сам факт повышения заработной платы не всегда способствует росту показателей работы, зато однозначно снижает экономические. Другая группа руководителей уменьшает заработную плату ниже средней по отрасли, но незнание принципов оплаты приводит к тому, что работники компенсируют недостачу изъятием части оборотных или основных средств либо пассивным отношением к трудовым обязанностям. Следовательно, высоких
показателей достигнуть так и не удается. А это одна из основных задач руководителей - достижение максимальных трудовых и экономических показателей, выполнить которую возможно путем освоения сущности заработной платы, механизмов ее формирования и выплаты.
В работе С. Шкурко и С. Король [2, с. 75] понятие заработной платы трактуется как “вознаграждение за труд’’ с позиции государства данное понятие очевидно, оно заинтересовано в социальной защите своих граждан, в плате за произведенный ими труд. А для руководителей организаций эго еиде не является доминантой.
Что требует работодатель, когда нанимает работника для выполнения каких-либо производственных задач? В работе ЕЭ.(Шлезингера [3, с. 243] утверждается, что оплата труда в широком смысле - это та или иная форма вознаграждения за определенное количество и качество выполненной работы’’ Для работодателя важен труд в его количественной и качественной составляющих. Па наш взгляд, здесь и заключается ошибка в понимании сущности заработной платы у руководи-телей. В данном случае такой подход подразумевает, что работник способен выполнить существующий объем работ с требуемым качеством. Это наследие старой уравнительной советской системы, когда считалось, что пет незаменимых людей, и отбрасывалась в сторону уникальность каждого конкретного человека.
Предположим, отдельные работники по своим физиологическим или умственным способностям не могут выполнить 100% заданного объема работ с требуемым качеством и количеством в совокупности, тогда руководители в один голос
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.
Решение задач оптимизации в Excel
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В Excel для решения задач оптимизации используются следующие команды:
- Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.
- Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
- Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.
Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».
Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.
Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:
На основании этих данных составим рабочую таблицу:
- Количество изделий нам пока неизвестно. Это переменные.
- В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
- Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
- Цель – найти максимально возможную прибыль. Это ячейка С14.
Активизируем команду «Поиск решения» и вносим параметры.
После нажатия кнопки «Выполнить» программа выдает свое решение.
Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.
Решение финансовых задач в Excel
Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.
Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.
Оформим исходные данные в виде таблицы:
Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
- Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
- Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
- Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
- Тип – 0.
- БС – сумма, которую мы хотим получить в конце срока вклада.
Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.
Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.
Решение эконометрики в Excel
Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.
Дано 2 диапазона значений:
Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.
Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).
Решение логических задач в Excel
В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.
- Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
- Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
- Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».
Решение математических задач в Excel
Средствами программы можно решать как простейшие математические задачки, так и более сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).
Условие учебной задачи. Найти обратную матрицу В для матрицы А.
- Делаем таблицу со значениями матрицы А.
- Выделяем на этом же листе область для обратной матрицы.
- Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
- В поле аргумента «Массив» вписываем диапазон матрицы А.
- Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.
Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.
В программе Excel имеется обширный инструментарий для решения различных видов уравнений разными методами.
Рассмотрим на примерах некоторые варианты решений.
Решение уравнений методом подбора параметров Excel
Инструмент «Подбор параметра» применяется в ситуации, когда известен результат, но неизвестны аргументы. Excel подбирает значения до тех пор, пока вычисление не даст нужный итог.
Путь к команде: «Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра».
Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:
- Введем в ячейку В2 формулу для нахождения значения функции. В качестве аргумента применим ссылку на ячейку В1.
- Открываем меню инструмента «Подбор параметра». В графе «Установить в ячейку» - ссылка на ячейку В2, где находится формула. В поле «Значение» вводим 0. Это то значение, которое нужно получить. В графе «Изменяя значение ячейки» - В1. Здесь должен отобразиться отобранный параметр.
- После нажатия ОК отобразится результат подбора. Если нужно его сохранить, вновь нажимаем ОК. В противном случае – «Отмена».
Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».
Как решить систему уравнений матричным методом в Excel
Дана система уравнений:
- Значения элементов введем в ячейки Excel в виде таблицы.
- Найдем обратную матрицу. Выделим диапазон, куда впоследствии будут помещены элементы матрицы (ориентируемся на количество строк и столбцов в исходной матрице). Открываем список функций (fx). В категории «Математические» находим МОБР. Аргумент – массив ячеек с элементами исходной матрицы.
- Нажимаем ОК – в левом верхнем углу диапазона появляется значение. Последовательно жмем кнопку F2 и сочетание клавиш Ctrl + Shift + Enter.
- Умножим обратную матрицу Ах -1х на матрицу В (именно в таком порядке следования множителей!). Выделяем диапазон, где впоследствии появятся элементы результирующей матрицы (ориентируемся на число строк и столбцов матрицы В). Открываем диалоговое окно математической функции МУМНОЖ. Первый диапазон – обратная матрица. Второй – матрица В.
- Закрываем окно с аргументами функции нажатием кнопки ОК. Последовательно нажимаем кнопку F2 и комбинацию Ctrl + Shift + Enter.
Получены корни уравнений.
Решение системы уравнений методом Крамера в Excel
Возьмем систему уравнений из предыдущего примера:
Для их решения методом Крамера вычислим определители матриц, полученных заменой одного столбца в матрице А на столбец-матрицу В.
Для расчета определителей используем функцию МОПРЕД. Аргумент – диапазон с соответствующей матрицей.
Рассчитаем также определитель матрицы А (массив – диапазон матрицы А).
Определитель системы больше 0 – решение можно найти по формуле Крамера (Dx / |A|).
Для расчета Х1: =U2/$U$1, где U2 – D1. Для расчета Х2: =U3/$U$1. И т.д. Получим корни уравнений:
Решение систем уравнений методом Гаусса в Excel
Для примера возьмем простейшую систему уравнений:
3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9
Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.
Для наглядности свободные члены выделим заливкой. Если в первой ячейке матрицы А оказался 0, нужно поменять местами строки, чтобы здесь оказалось отличное от 0 значение.
- Приведем все коэффициенты при а к 0. Кроме первого уравнения. Скопируем значения в первой строке двух матриц в ячейки В6:Е6. В ячейку В7 введем формулу: =B3:Е3-$B$2:$Е$2*(B3/$B$2). Выделим диапазон В7:Е7. Нажмем F2 и сочетание клавиш Ctrl + Shift + Enter. Мы отняли от второй строки первую, умноженную на отношение первых элементов второго и первого уравнения.
- Копируем введенную формулу на 8 и 9 строки. Так мы избавились от коэффициентов перед а. Сохранили только первое уравнение.
- Приведем к 0 коэффициенты перед в в третьем и четвертом уравнении. Копируем строки 6 и 7 (только значения). Переносим их ниже, в строки 10 и 11. Эти данные должны остаться неизменными. В ячейку В12 вводим формулу массива.
- Прямую прогонку по методу Гаусса сделали. В обратном порядке начнем прогонять с последней строки полученной матрицы. Все элементы данной строки нужно разделить на коэффициент при с. Введем в строку формулу массива: .
- В строке 15: отнимем от второй строки третью, умноженную на коэффициент при с второй строки (). В строке 14: от первой строки отнимаем вторую и третью, умноженные на соответствующие коэффициенты (). В последнем столбце новой матрицы получаем корни уравнения.
Примеры решения уравнений методом итераций в Excel
Вычисления в книге должны быть настроены следующим образом:
Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) методом итерации с применением циклических ссылок. Формула:
M – максимальное значение производной по модулю. Чтобы найти М, произведем вычисления:
f’ (1) = -2 * f’ (2) = -11.
Полученное значение меньше 0. Поэтому функция будет с противоположным знаком: f (х) = -х + х 3 – 1. М = 11.
В ячейку А3 введем значение: а = 1. Точность – три знака после запятой. Для расчета текущего значения х в соседнюю ячейку (В3) введем формулу: =ЕСЛИ(B3=0;A3;B3-(-B3+СТЕПЕНЬ(B3;3)-1/11)).
В ячейке С3 проконтролируем значение f (x): с помощью формулы =B3-СТЕПЕНЬ(B3;3)+1.
Корень уравнения – 1,179. Введем в ячейку А3 значение 2. Получим тот же результат:
Практическая работа позволит изучить возможности электронных таблиц для выполнения инженерных расчетов, научит выполнять расчет уравнений зависимостей и достоверность аппроксимации.
Описание разработки
Цель работы:
изучить возможности электронных таблиц для выполнения инженерных расчетов. Научиться выполнять расчет уравнений зависимостей и достоверность аппроксимации.
Программное обеспечение MS EXCEL
Назначение электронной таблицы
Возможности электронной таблицы при выполнении научно-инженерных расчетов
Приближенные расчеты с помощью линий тренда и аппроксимации
Краткая теория:
Excel является отличным средством автоматизации решения самых различных расчетных задач. В первую очередь (вполне оправданно) Excel используется в финансовой сфере и как средство обработки больших массивов числовой информации. Однако в настоящее время все большее количество технических специалистов, инженеров и ученных начинают применять Excel для сложных технических расчетов.
В этой связи можно выделить две большие категории принципиально разных задач, для решения которых целесообразно использовать Excel:
Цепочные расчеты по сложным формулам, при этом объем исходных данных невелик, и они вводятся преимущественно вручную (мало данных, сложные расчеты).
Аналитическая обработка данных, которая во многих случаях сводится к простым операциям с большим объемом данных. Несмотря на простоту этих операций вычисления с большими массивами информации целесообразно проводить с помощью Excel.
Аппроксимация - приближенное решение сложной функции с помощью более простых, что резко ускоряет и упрощает решение задач. В инженерных расчетах целью аппроксимации часто является укрупнение характеристик моделируемых технических объектов.
В расчетах часто используют достоверность аппроксимации, чем ближе эта достоверность к 1 (единице), тем точнее выполнен расчет.
Практическая часть.
Построить график зависимости теплоемкости компоненты от температуры. Значения теплоемкостей и соответствующие температуры приведены в табл. 1.
Последовательность выполнения операций:
1. Запустите программу Excel.
2. Поставьте курсор в ячейку А1 и, пользуясь клавиатурой, приступите к набору табличных данных (вещественная часть отделяется запятой). В верхнюю строку помещается шапка таблицы, в левый столбец – значения аргумента.
3. Выделите данные по которым строится график следующим образом: переместите курсор с помощью мыши в ячейку А1, нажав на левую клавишу мыши обведите область, содержащую данные для построения графика, при этом область будет выделена контрастным цветом (кроме ячейки А1).
4. На верхней панели инструментов выберите раздел Мастер диаграмм (в виде диаграммы с цветными столбцами) и откройте его.
5. Выберите тип диаграммы: точечная, т. к. исходные данные содержат координаты точек и нужный вид диаграмм.
6. Мышью нажмите клавишу далее, войдите в источник данных диаграммы.
7. Выберите строку: ряды: в столбцах.
8. Нажмите клавишу далее, войдите в параметры диаграммы.
9. В разделе заголовки подпишите название диаграммы и обозначения осей, нажмите клавишу далее и войдите в раздел – размещение диаграммы.
10. Выберите место расположения диаграммы (на имеющемся листе). Нажмите клавишу готово.
11. Сохраните рабочую книгу в файл под именем, например, инженерные расчеты. xls
Выполнить аппроксимацию зависимости теплоемкости от температуры (табл. 1).
Последовательность выполнения операций:
1. Запустите программу Excel и откройте рабочую книгу инженерные расчеты. xls, созданную ранее.
2. На графике выделите ряд данных щелчком мыши на экспериментальной точке.
На верхней панели откройте раздел Диаграмма и подраздел – Добавить линию тренда. Добавить линию тренда можно также следующим образом: подвести курсор к графику, щелкнуть правой кнопкой мыши и в появившемся меню выбрать: Добавить линию тренда. В открывшемся окне выберите один из типов функции (линейная, логарифмическая, полиномиальная, степенная, экспоненциальная).
Данная практическая работа предназначена для изучения технологии поиска решения для задач оптимизации (минимизация, максимизации).
Содержимое разработки
Практическая работа №2
Тема: Подготовка и применение электронных таблиц при решении задач оптимизации (на примере функции «Поиск решения» MS Excel
Цель занятия Изучение технологии поиска решения для задач оптимизации (минимизация, максимизации).
Задание12.1.Используя режим подбора параметра (Сервис /Подбор параметра), определить штатное расписание фирмы. Известно, что в штате фирмы состоят: 6 курьеров, 8 младших менеджеров, 10 менеджеров, 3 заведующих отделом, 1 главный бухгалтер, 1 программист, 1 системный аналитик, 1 генеральный директор. Общий месячный фонд зарплаты составляет 100 000р. Определить, какими должны быть оклады сотрудников.
Каждый оклад является линейной функцией от оклада курьера, а именно:
зарплата = Ai*x + Bi,
где х - оклад курьера; Ai и Вi - коэффициенты, показывающие:
Ai - во сколько раз превышается значение х; Bi - на сколько превышается значение х. Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel.
2. Создайте таблицу штатного расписания фирмы Введите исходные данные в рабочий лист электронной книги.
Штатное расписание фирмы
Зарплата сотрудника
Количество сотрудников
Суммарная зарплата
Фонд заработной платы
3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
4. В столбце D введите формулу для расчета заработной платы по каждой должности.
= B6*$D$3 + С6
Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.
В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. =D6*E6.
Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.
В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.
5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, 100 000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.
В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14,
содержащую формулу расчета фонда заработной платы; в поле Значение наберите искомый результат 100 000; в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3,в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000 р.
Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную книгу под именем «Штатное расписание» в своей папке.
Задание 2. Используя режим подбора параметра и таблицу расчета штатного расписания , определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.
Порядок работы
Запустите редактор электронных таблиц Microsoft Excel и откройте ранее созданный файл «Штатное расписание».
Читайте также: