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

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

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

Мета роботи: Закріпити навики проведення розрахунків в табличній формі і побудови діаграм.

Зміст завдання:

1. Створити і відформатувати таблицю. Ввести початкові дані і відповідні формули

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

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

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

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

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

Розмір премії РП= 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 для опції значения:

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

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

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

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

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

Дата добавления: 2014-11-18