Офисные прикладные программы MS Office 2007: Microsoft Excel
Реализация простых вычислений в MS Excel
В качестве примера реализации простых вычислений в MS Excel рассмотрим следующую задачу.
Задача 2.1.
Вычислить сопротивления последовательно соединенных резисторов и силы тока при заданном напряжении.
Исходные данные
Цепь постоянного тока, состоящая из трех последовательно соединенных сопротивлений Rl, R2, R3 с постоянным напряжением и на зажимах.
Модель решения задачи
Общее сопротивление в последовательной цепи вычисляется по формуле R=R1+R2+R3, а ток - по формуле I=U/R.
Решение задачи
Исходные значения сопротивлений вводятся в ячейки ВЗ, D3, F3, а значение напряжения - в ячейку D10 (рис.40).
2. В ячейки D11 и D12 вводятся формулы согласно таблицы 2
При оформлении задачи было применено следующее форматирование:
- изображения резисторов получены с применением выделения соответствующих ячеек рамкой Главная - Ячейки - формат ячеек - Границы;
- линии соединения между резисторами и место подключения источника питания созданы с помощью элементов рисования Вставка - Иллюстрации - Фигуры - Линии;
- одни данные в ячейках выровнены по центру, другие - по правому или левому краю: U=, l=, R= - выровнены по правому краю, а В, А, Оm - по левому краю.
Результат решения задачи приведен на рис.40.
Задача 2.2.
Руководитель проекта по созданию Web-сайта должен составить штатное расписание, чтобы определить сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет $ 10 000.
Исходные данные
Для нормальной работы проекта нужно 5-7 HTML-верстальщиков, 8-10 Web-дизайнеров, 10-12 Web-программистов, 1 контент-менеджер, 3 руководителя групп, 1 Mail-менеджер, 1 интернет-маркетолог, 1 руководитель проекта. На некоторых должностях количество людей может меняться. Например, зная, что найти верстальщиков трудно, руководитель может принять решение о сокращении числа верстальщиков, чтобы увеличить оклад каждого из них. Общий месячный фонд зарплаты задан.
Модель решения задачи
За основу берется оклад верстальщика, а все остальные оклады вычисляются исходя из него во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада верстальщика А*C+B, где С - оклад верстальщика, А и B - коэффициенты, определяемые руководителем для каждой должности.
Предположим, что руководитель проекта решил:
Web-дизайнер должен получать в 1,5 раза больше верстальщика (А=1, 5, В=0);
Web-программист в 3 раза больше верстальщика (A=3, B=0);
руководитель группы - на $30 больше, чем Web-программист
(А=3, В=30);
контент-менедокер в 2 раза больше верстальщика (A=2, B=0);
интернет-маркетолог - на $40 больше верстальщика (A=1, B=4 0);
Mail-менеджер в 4 раза больше верстальщика (A=4, B=0);
руководитель проекта - на $20 больше Mail-менеджера (А=4, В=20).
Задав количество человек на каждой должности, можно составить уравнение:
N1*(Al*C+Bl) +N2*(А2*С+В2) +...+N8*(А8*С+В8)=10000,
где N1 - количество верстальщиков;
N2 - количество дизайнеров и т.д.
В этом уравнении известны коэффициенты А1...А8 и В1...В8, а неизвестны - оклад верстальщика С и количество людей на должностях N1...N8.
Решение задачи
Решать подобное уравнение следует путем подбора. Решение задачи представлено на рис.41.
В столбце D Зарплата сотрудника записана формула =А*C+B вычисления заработной платы для каждой должности. Формула вносится только в одну ячейку D6 и раскопируется в остальные. Для этого в формуле вместо С следует использовать абсолютную ссылку на ячейку H6 ($H$6), в которой будет подобрана зарплата верстальщика (=B6*$H$6+C6). Далее следует ее раскопировать с помощью маркера заполнения на весь столбец D. Изменение содержимого этой ячейки должно приводить к изменению содержимого всего столбца D и перерасчету всей таблицы.
В столбце F суммарную зарплату вычисляем по формуле =3арплата сотрудника*Кол-во сотрудников. Формула вводится 1 раз и раскопируется с помощью маркера заполнения. Внизу таблицы в ячейке F14 вычисляем сумму зарплат всех специалистов по формуле =СУММ(F6:F13) .
После подготовки таблицы можно перейти к подбору параметра (зарплаты верстальщика). Это можно сделать вручную, а можно поручить компьютеру (что мы и сделаем), учитывая, что расчетный месячный фонд заработной платы должен быть не больше $10 ООО.
Для этого во вкладке Данные - Работа с данными - Анализ «что-если» открываем диалоговое окно Подбор параметра (рис.42).
В окне Установить в ячейке ссылаемся на ячейку с формулой подсчета суммарной зарплаты всех специалистов.
В окне Значение вводим значение фонда заработной платы, которое не должно быть превышено. Подбирать будем значение зарплаты верстальщика, поэтому в окно Изменяя значение ячейки введем ссылку на $Н$6. Заканчиваем ввод кнопкой ОК, и получаем значение зарплаты верстальщика. Окончательный вид таблицы представлен на рис.43.
