Практичне завдання 2 Посилання на інші листи робочої книги. Робота з формулами.

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

Хід роботи:

1. Запустимо табличний процесор EXCEL.

2. Відкриємо файл з лабораторними роботами.

3. Відкриємо новий аркуш, давши йому ім'я «Структура вартості майна»

4. Заповнимо графи таблиці, як показано на рис. 2.1

5. У чарунках D7, F7, H7, J7 введемо значення 100.

6. Щоб встановити посилання на значення Разом активів аркуша Баланс, активізуємо чарунку D7. Поставимо в ній знак =, переходимо на аркуш Баланс, клацаємо мишею по чарунці D13 і натискуємо клавішу Enter. посилання встановлене. Аналогічні посилання встановлюємо у відповідні чарунки рядка 7, 9, 13 і 14 аркуша «Структура вартості майна» (рис. 2.1, рис. 2.2).

7. Значення в стовпцях «в % до вартості майна» для рядків 9, 10, 12, 13, 14 розраховували по формулі (рис. 3).

8. Звірили правильність заповнення таблиці з рисунком 3.

Інтерфейс діалогового вікна виконання практичного завдання 2 представлено на рисунках 2.1 та 2.2.

Рис. 2.1. Структура вартості майна

Рис. 2.2. Формули таблиці «Структура вартості майна»

Практичне завдання 3 Використання відносних і середніх величин в аналізі діяльності підприємства засобами excel

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

Хід роботи:

1. Запускаємо табличний процесор EXCEL.

2. Відкриваємо файл з практичними роботами.

3. Відкриваємо новий аркуш робочої книги, давши йому ім'я «Аналіз зростання показників».

4. Створюємо таблицю (рис. 3.3).

5. За допомогою функції СРЗНАЧ() розрахуємо середні значення основних показників діяльності підприємства за період з 1.01.2001 по 1.01.2004 рр. (дані аркуша «Баланс»). Для цього активізуємо чарунку Е8. За допомогою майстра функцій (Вставка\Функция або кнопка f_x зліва від рядка формул) викликаємо функцію СРЗНАЧ(), перейдемо на аркуш Баланс, клацнемо по чарунці D7, потім затиснемо клавішу Ctrl клацнувши по черзі по чарункам F7, H7, J7. Клацнемо по OK у вікні функції. Скопіюємо отриману функцію на інші чарунки стовпця Е.

6. Розрахуємо зростання основних показників балансу підприємства по роках (за період з 1.01.2001 по 1.01.2004 рр.) на основі інформації аркуша “Баланс” по формулі: Зростання=

(Значення показника на 1.01.04 р.-Значення показника на 1.01.01р.)/

Значення показника на 1.01.01 р.

Для цього активізувати чарунку G8 поставити в ній знак =. Перейдемо на аркуш Баланс, клацнемо по чарунці J7, поставимо знак віднімання (–) і клацнемо чарунку D7. Отримане вираження в рядку формул укладемо в круглі дужки, поставивши знак ділення(/) і клацнувши по чарунці D7. Натискуємо Enter. Встановимо процентний формат (головне меню Формат/Ячейки/Число/Процентний). Скопіюємо отриману функцію на інші чарункци стовпця G.

7. Визначимо максимальне із значень основних статей балансу (функція МАКС()) представлених за станом на 1.01.02 р.,1.01.03 р.,1.01.04 р. (на основі інформації аркуша “Баланс”). Для цього активізуємо чарунку Н8. За допомогою майстра функцій (Вставка\Функция або кнопка f_x зліва від рядка формул) викликаємо функцію МАКС(), переходимо на аркуш Баланс, клацнемо по чарунці D7, потім затискуємо клавішу Ctrl клацнувши по черзі по чарунках F7, H7, J7. Клацнемо по OK у вікні функції. Скопіюємо отриману функцію на інші чарунки стовпця Н.

8. За результатами розрахунків визначимо період діяльності підприємства, відповідний набутого максимального значення показника. Для цього використовуємо функцію ЕСЛИ (логич_выр; значение_если_истина; значение_если_ложь), що дозволяє визначити дії, якщо деяка умова достеменна або помилково, а саме:

а) якщо набутого максимального значення дорівнює показнику балансу на 1.01.02 р., то результат виконання функції – 2002 р.;

b) якщо набутого максимального значення дорівнює показнику балансу на 1.01.03 р., то результат виконання функції – 2003 р.;

c) інакше – результатом виконання функції буде 2004 р.

Порядок побудови функції:

– активізуємо чарунку H8 (табл. (Аналіз зростання показників)) і виконаємо Вставка\Функция (або кнопка f_x зліва від рядка формул).

– порівнюємо вміст чарунки, що визначає максимальне значення (аркуш "Аналіз зростання показників") і вміст чарунки, що зберігає значення відповідного показника за станом на 1.01.02 р. (аркуш "Баланс"). Для цього обираємо функцію ЕСЛИ() (рис.3.1). У аргумент Логічне вираження вводимо H8=Баланс!F7, що означає питання «Максимальне значення показника (900,9) відповідає показнику Основні засоби і інші необоротні активи за 2002 рік аркуша Баланс».

Рис. 3.1. Робота з функцією ЕСЛИ()

– встановлюємо курсор в аргумент Значение_если_истина. Якщо значення вказаних чарунок збігаються, то вказати абсолютне посилання на 2002 рік аркуша Баланс. Якщо не має – встановлюємо курсор в аргумент Значение_если_ложь і вбудуємо другу функцію ЕСЛИ() (ЕСЛИ()-два). Для цього виконаємо клацання по кнопці із стрілкою зліва від рядка формул, відкроємо перелік вбудовуваних функцій і клацнемо по функції ЕСЛИ (рис.3.2).

Рис. 3.2. Вікно вбудованих функцій

– у вікні, що відкрилося, Аргументи функції ЕСЛИ-два в аргумент Логічне вираження функції якщо-два введемо H8=Баланс!H7, що означає питання «Максимальне значення показника (900,9) відповідає показнику Основні засоби і інші необоротні активи за 2003 рік аркуша Баланс». Якщо значення вказаних чарунок збігаються, то в аргумент Значение_если_истина функція якщо-два введемо абсолютне посилання на 2003 рік аркуша Баланс. Якщо немає – встановлюємо курсор у аргумент Значение_если_ложь і вбудовуємо третю функцію ЕСЛИ() (ЕСЛИ-три). Повторюємо ті ж дії з даними за 2004 р. Правильно побудована функція матиме вигляд:

=ЕСЛИ(H8=Баланс!H7;Баланс!$H$4;ЕСЛИ(H8=Баланс!D7;Баланс!$D$4;ЕСЛИ(H8=Баланс!F7;Баланс!$F$4;ЕСЛИ(H8=Баланс!J7;Баланс!$J$4;ЛОЖЬ))))

9. За результатами розрахунків проаналізуємо загальну тенденцію зростання основних показників роботи підприємства за допомогою функцій: СЧЕТЕСЛИ(інтервал; критерій), що дозволяє визначити кількість значень інтервалу, що задовольняють заданій умові, і ЕСЛИ(). При цьому подрахуємо кількість середніх значень зростання показників активу за три роки, що перевищують 80%, і якщо результат розрахунків більше половини кількості аналізованих показників активу (6/2=3), то видаємо повідомлення Відмічено зростання основних показників балансу, інакше – Відмічено спад основних показників балансу. Для забезпечення компактності розрахунків функцію СЧЕТЕСЛИ() використовуємо як вбудовану функцію. Порядок дій здійснити таким чином:

а) викликати функцію ЕСЛИ() і в полі логическое_выражение викликаємо вбудовану функцію СЧЕТЕСЛИ() з параметрами – інтервал (стовпець із значеннями зростання показників активу балансу) і критерії (>80%);

b) завершивши введення даних по функції СЧЕТЕСЛИ() повертаємося у функцію ЕСЛИ() і в полі логическое_выражение порівнюємо результат виконання функції СЧЕТЕСЛИ() з числом 3;

с) у полі значение_если_истина, вводим текст, що дозволяє зробити виводи про зростання, а в полі значение_если_ложь – про зниження зростання основних показників активу балансу. Правильно побудована функція матиме вигляд:

=ЕСЛИ(СЧЁТЕСЛИ(G8:G13;">80%");"Відбулося зростання основних показників балансу за 3 роки";"Відбулося зменшення основних показників балансу за 3 роки")

10. У категорії Дата і час Майстра функцій знайти і вставити в документ функцію =ТДАТА(), що повертає у вічко значення сьогоднішньої дати і часу роботи з аркушем книги.

Рис. 3.3. Аналіз зростання основних показників балансу