II.1.1. Приклад виконання Завдання № 1.

II.1. Завдання № 1

Тема: Розв’язання задачі в табличній формі і графічне представлення табличних даних в середовищі MS EXCEL

2. Побудувати діаграму за даними таблиці ( відповідний тип діаграми і дані для неї вибрати самостійно), відформатувати діаграму.

3. Вивести на друк таблицю і діаграму.

4. Вивести на друк таблицю у вигляд формул.

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

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

Розмір премії РП= 2*СЗ), якщо Стаж роботи ³ 2 років

(СЗ - Середній заробіток за минулий рік), а

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

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

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

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

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

і реалізувати таку модель в середовищі табличного процесора 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 для опції значения:

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

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

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

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

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