Приклад виконання

Постановка задачі.

Аналізуючи роботу бригади за попередній рік, керівництво фірми прийняло рішення преміювати працівників за наступним принципом:

Розмір премії:

РП= 2*СЗ, якщо Стаж роботи ³ 2 років (СЗ - Середній заробіток за минулий рік),

РП= 0.8*СЗ в решті випадків.

Провести розрахунок нарахування премії. Відобразити в графічній формі розмір премій.

Очевидно, що розв’язок даної задачі зручно змоделювати в табличній формі наступного виду:

Бригада будівельників фірми " Умілець"

Рішення ухвалене : 31 Грудня, 2004

№ п/п	Прізвища працівників	Посада	Середній заробіток за минулий рік ( СЗ)	Дата прийому на роботу	Стаж роботи (СР)	Розмір премії ( РП)
	Петренко	Бригадир	652,45	11.09.01
	Сидоров	Слюсар	385,65	04.07.02
	Приходько	Зварювач	412,54	01.11.02
	Третяків	Монтажник	289,45	10.05.01
	Петушкова	Обліковець	265,35	07.05.04
	Шапітько	Бетонщик	385,45	25.07.01
	Абрамян	Електрик	423,65	19.08.02

і реалізувати таку модель в середовищі табличного процесора MS Excel.

Моделювання розрахункової таблиці в середовищі MS Excel.

1). Створення таблиці.

a.Вводимо початкові дані:

- Заголовок таблиці в осередок В2;

- Текст «Рішення ухвалене»: в осередок С3 і безпосередньо дату в Е3 (При введенні Дати у форматі ДД.ММ.ГГ використовують дефіс або крапку, як роздільник.).

b.Створюємо «шапку» таблиці (текстові дані для іменування стовпців):

- «№ п/п» в осередок А4;

- «Прізвища працівників» в осередок В4;

- «Посада» в осередок С4;

- «Середній заробіток за минулий рік» в осередок D4;

- «Дату прийому на роботу» в осередок Е4;

- «Стаж роботи (СР)» в осередок F4;

- «Розмір премії (РП)» в осередок G4 .

Після введення текстових даних, як бачимо, вони відображаються частково. Для виправлення ситуації потрібно їх відформатувати, подавши команду Формат®Ячейки, попередньо виділивши діапазон А4 : Н4:

В діалоговому вікні Формат ячейки вибрати (галочкою a ) опцію Виравнивание – по центру (по горизонтали і по вертикали) і опцію Отображение - переносить по словам.

 

 

Отримаємо не зовсім читабельний варіант:

 

Виправити ситуацію необхідно шляхом розширення ширини стовпців (А, В, С, D, Е, F і G) та висоти рядка 4.

Тепер «шапка» таблиці має пристойний вид:

 

 

c.Далі заповнимо стовбці А, В, С, D, Е відповідними по змісту даними:

 

Таким чином вхідні дані для розрахунку введені на лист.

d.Вводимо розрахункові формули:

- у осередок F5 вводимо формулу =($E$3-E5)/365, яка розраховуватиме стаж роботи;

- розмножуємо формулу з осередку F5 в осередки, що пролягають нижче (діапазон F6 : F11). Для цього встановлюємо табличний курсор в осередок F5, наводимо курсор миші на правий нижній кут рамки табличного курсору. Курсор миші прийме вид + :

+

Нажавши ліву клавішу миші, протягуємо вниз до чарунки F11 і отримаємо:

 

Зауважте, що у формулі =($E$3-E5)/365посилання на чарунку Е3 містить знаки $. Таке посилання називається абсолютним і встановлено воно для того, щоби при розмноженні формули адреса Е3 не змінювалася. Тобто в чарунці F6 формула прийме вид =($E$3-E6)/365, в чарунці F7 відповідно вид =($E$3-E7)/365і т. д. Таким чином Е5 змінюється на Е6, Е7 і т. д., а Е3 залишається незмінним. Саме така модифікація формул нам і потрібна.

Оскільки для стажу роботи в числових даних достатньо залишити один знак після коми, відформатуємо числові дані цього стовпця. Для цього виділимо діапазон F5 : F11 і подамо команду Формат ® Ячейки і на вкладці Число виберемо формат Числовий і Число десятичных знаков 1.

 

Отримаємо:

- далі у осередок G5 вводимо формулу = ЕСЛИ(F5>=2 ; 2*D5 ; 0,8*D5) для нарахування премії, згідно заданій умові;

Функція ЕСЛИ перевірить умову F5>=2і якщо вона виконується, то видасть результат за формулою 2*D5, а якщо не виконується, то за формулою 0,8*D5.

- розмножуємо формулу з осередку G5 в осередки, що пролягають нижче (діапазон G5 : G11) і форматуємо значення премії, залишивши 2 знаки після коми.

 

Форматування таблиці.

Попередньо ми форматували вмістиме таблиці, а тепер завершимо форматуванням самої таблиці:

- відцентруємо заголовок відносно ширини таблиці. Для цього виділимо діапазон А2 : G2і подамо команду Объединить и поместить в центре :

 

І виберемо шрифт Полужирный командою :

 

- розграфимо таблицю командою Границы , попередньо виділивши таблицю і вибравши тип Все границы:

 

 

Отримуємо остаточно відформатовану таблицю:

 

Побудова діаграми.

Побудуємо діаграму, по якій можна наглядно оцінити Розмір премії для кожного працівника. Для цього необхідно:

- виділити діапазони В4:B11 і G4:G11 (виділення несуміжних діапазонів проводиться при натиснутій клавіші Ctrl);

- Визвати Майстра діаграми кнопкою на панелі інструментів, і в першому діалоговому вікні вибрати тип – Гистограмма (Вид – 1). Натискаємо кнопку Далее.

 

 

- В другому вікні на вкладці Заголовки вказуються заголовки діаграми:

 

- на вкладці Легенда прибираємо легенду, знявши a з опції Добавить легенду:

(Легенда непотрібна, оскільки вона дублює заголовок діаграми).

 

 

- На вкладці Линии сетки прибираємо горизонтальні лінії, знявши a з опції основные линии (Ось Y значений):

- на вкладці Подписи данных включимо підписи значень над стовпцями, встановивши a для опції значения:

- в останньому вікні погоджуємося розмістити діаграму на робочому листі, що є (Имеющемся).

Після роботи з Майстром діаграмиотримаємо діаграму такого виду:

Доформатуємо отриману діаграму. А саме приберемо сіру заливку області побудови діаграми. Для цього виділимо Область построения и визвемо контекстне меню правою клавішею миші. Виберемо пункт Формат области построения…:

В виведеному вікні виберемо опції Рамка - невидимая і Заливка - білого кольору:

Остаточний вид розрахункової таблиці і діаграми матимуть наступний вигляд: