коледж (лабораторні з ІС фінанси) / Лабораторна 4

Лабораторна робота № 4

Тема: Прості функції та обчислювання

Мета:

• сформувати практичні навички створення документів складної структури засобами Microsoft Excel 2010;

• познайомитися з загальними правилами редагування таблиць, розвинути уміння застосування режимів роботи Microsoft Excel 2010;

• отримати навички роботи з логічною функцією ЕСЛИ та з функцією ПРОСМОТР за допомогою майстра функцій. Використання складних функцій.

Програмне забезпечення: Microsoft Excel 2010

І. Теоретичні відомості до лабораторної роботи.

Функція - це зумовлена (заздалегідь задана) формула. Кожна функція має назву і аргументи (обов'язкові або необов'язкові). Після імені функції слідують аргументи, ув’язнені в дужки і що розділяються крапкою з комою. Якщо функція не має аргументів, то після назви функції слідують порожні дужки. Наприклад, COS().

Обчислення відповідно до певної формули або функції - це процес розрахунку по формулах і подальшого відображення значень результатів в осередках, що містять формули. Щоб уникнути зайвих обчислень, Excel 2007-2010 автоматично перераховує формули тільки у разі зміни тих осередків, які безпосередньо пов'язані з цими формулами. Подібні дії, як правило, відбуваються при першому відкритті книги і її редагуванні. Але ця поведінка не є жорстко запрограмованою і може управлятися користувачем.

Під ітерацією розуміється циклічний процес перерахунку листа до тих пір, поки не буде виконано певну числову умову.

Звернете увагу, що Excel не може автоматично розрахувати значення по формулі, яка посилається (безпосередньо або побічно) на осередок, що містить формулу. При цьому формується так зване циклічне посилання, яке зазвичай розцінюється Excel як помилка (за винятком ітераційних обчислень). Якщо у вашій формулі застосовуються ітеративні обчислення (міститься циклічне посилання), слід визначити кількість ітерацій, інакше ви малюєте потрапити в нескінченний цикл. Розробники Excel передбачили можливість виникнення подібної ситуації, у зв'язку з чим надали користувачеві можливість управління максимальною кількістю ітерацій, а також величиною допустимих змін.

Шляхом визначення точності визначається ступінь збіжності обчислень (а разом з цим і кількість ітерацій). Excel зберігає і виконує обчислення з точністю 15 значущих цифр. Проте існує можливість змінити точність обчислень, так що Excel при перерахунку формул використовуватиме для обчислень значення, що не зберігається, а відображається.

■ Сума. Для діставання доступу до цієї функції можна також натиснути "швидкі" клавіші <Alt+=>. Призначення — підсумовування значень в заданому діапазоні осередків. Після вибору цієї функції в осередку, в якому знаходиться покажчик миші, з'являється визначення функції в наступному вигляді: СУМ(число1; число2...). Якщо над осередком, де знаходиться покажчик миші, є ряд чисел, визначення функції може відображатися в наступному вигляді: СУМ(В4:в5), наприклад. При цьому діапазон осередків, що "потрапляють" в область дії функції, виділяється пунктирною лінією. На цьому етапі можна змінити значення аргументів функції. Якщо вас всіх влаштовує, перейдіть в панель формул і натисніть клавішу <Enter>. Тепер в осередку з номером В6 знаходиться функція, яка підсумовує значення, введені в осередки від В4 до В5 (по вертикалі).

■ Середнє. Обчислення середнього значення на базі заданого діапазону осередків.

■ Число. Підрахунок кількості чисел, введених у вказаний діапазон осередків.

■ Максимум. Вибір максимального значення у вказаному діапазоні осередків.

■ Мінімум. Вибір мінімального значення у вказаному діапазоні осередків.

Якщо ж описаний вище набір функцій вас не влаштовує, звернетеся до майстра функцій. Для цього клацніть мишею на піктограмі Сума, потім виберіть пункт меню Інші функції. Після цього на екрані з'явиться діалогове вікно Майстер функцій - крок 1 з 2. Зупинимося докладніше на описі цього діалогового вікна.

Поле Пошук функції. У цьому полі вводиться опис функції, відсутньої в списку заздалегідь певних функцій.

Вибравши функцію ABS (обчислення абсолютного значення) і клацнувши мишею на кнопці ОК. Після цього відкривається друге вікно майстра, Аргументи функції, в якому потрібно вибрати аргументи функції. Як такі зазвичай виступають адреси осередків, в яких і міститься значення аргументу. Як аргумент був вибраний осередок В9 (у полі Число). Потім клацніть мишею на кнопці ОК, після чого створення формули буде завершено.

Обробка циклічних посилань. А зараз ще раз повернемося до горезвісної проблеми циклічних посилань.

Циклічним посиланням називається послідовність посилань, коли формула посилається (за допомогою інших посилань або безпосередньо) на саму себе.

"Шкідливість" циклічних посилань полягає в тому, що Excel 2007-2010 не може автоматично підрахувати кількість всіх відкритих книг, якщо хоч би одна з них містить циклічне посилання. Для видалення циклічного зсипання можна обчислити значення кожного осередку, включеного в "кругову" замкнуту послідовність обходу, використовуючи результати попередніх. Взагалі кажучи, якщо не змінюються відповідні параметри, обчислення припиняються після виконання 100 ітерацій або після того, як приріст кожної величини не перевищуватиме 0,001 за одну ітерацію.

Група Бібліотека функцій. За допомогою команд, що "населяють" цю групу, ви можете "сконструювати" довільну функцію, що допускається правилами Excel 2007-2010.

■ Піктограма Вставити функцію. Для вставки функції на лист клацніть мишею на цій піктограмі (або натисніть комбінацію клавіш <Shift+F3>). Після цього з'являється вже знайоме нам діалогове вікно майстра функцій.

■ Піктограма Автосума. Якщо клацнути лівою кнопкою миші на цій піктограмі, на відкритий лист вставляється функція СУМА. Якщо ж клацнути на стрілці, яка знаходиться правішим за цю піктограму, відображається меню, що містить функції СУМА, СЕРЕДНЄ, ЧИСЛО, МАКСИМУМ і МІНІМУМ.

■ Піктограма Недавно використовувалися. Після клацання лівою кнопкою миші на цій піктограмі ви дістанете доступ до останніх десяти функціям, що використалися.

■ Піктограма Фінансові. Клацніть лівою кнопкою миші на цій піктограмі, після чого ви дістанете доступ до набору фінансових функцій, без яких немислимі Складні економічні розрахунки.

■ Піктограма Логічні. Після клацання лівою кнопкою миші на цій піктограмі ви дістанете доступ до набору логічних функцій.

■ Піктограма Текстові. Клацніть лівою кнопкою миші на цій піктограмі, і перед вами відкриється доступ до обширного масиву функцій роботи з текстом.

■ Піктограма Дата і час. Тут "ховається" обширний набір функцій дати/часу.

■ Піктограма Посилання і масиви. Після клацання на цій піктограмі відкривається доступ до переліку функцій, призначених для роботи з посиланнями і масивами.

■ Піктограма Математичні. Ця піктограма відкриває доступ до переліку математичних функцій.

■ Піктограма Інші функції. Ця піктограма відкриває доступ до таких функцій, як статистичні, інженерні, аналітичні, а також до функцій, призначених для перевірки властивостей і значень.

Група Певні імена. Команди, що знаходяться в цій групі, дозволяють

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

• Раніше певні імена - в цьому списку відображаються раніше визначені вами імена;

• Вставити імена - після вибору цього пункту меню відображається відповідне діалогове вікно.

Піктограма Впливаючі осередки. Клацніть на цій піктограмі лівою кнопкою миші, після чого на екрані відобразяться стрілки, вказуючі на осередки, які "беруть участь" в обчисленнях. Звернете увагу на мал. 4.57, де показані осередки, включені у формулу підсумовування. Якщо потрібно відмінити відображення стрілок, клацніть лівою кнопкою миші на піктограмі Прибрати всі стрілки.

Піктограма Залежні осередки. Виділите осередки, посилання на яких включені до складу формули, і клацніть на цій піктограмі. Після цього відобразяться стрілки, що показують осередки, залежні від значення поточного осередку.

Група Обчислення. Як і виходить з назви, призначення команд з цієї групи - задавати параметри обчислень формули. Зупинимося докладніше на описі команд, створюючих цю вкладку.

■ Піктограма Параметри обчислень. Клацнувши на цій піктограмі лівою кнопкою миші, ви дістаєте доступ до меню, що складається з наступних трьох пунктів: Автоматично; Автоматично, окрім таблиця даних; Уручну. Вибір один з цих пунктів визначає характер обчислень, що проводяться по створених вами формулах.

■ Піктограма Перерахунок. Клацніть лівою кнопкою миші на цій піктограмі (або натисніть клавішу <F9>), після чого буде виконаний автоматичний перерахунок формул, визначених у всій книзі Excel. Ця можливість корисна у тому випадку, коли відключений автоматичний перерахунок формул.

■ Піктограма Провести обчислення. Клацніть лівою кнопкою миші на цій піктограмі (або натисніть клавіші <Shift+F9>), після чого буде виконаний автоматичний перерахунок формул, визначених на поточному листі Excel. Ця можливість корисна у тому випадку, коли відключений автоматичний перерахунок формул.

ІІ. Виконання роботи

1. Виконайте тестове завдання №3.

2. Завдання 1. В діапазоні B3: B10 розміщені оптові ціни на товари, що купуються. У клітинці B1 розмір торгової націнки, що становить 20% від ціни товару. В діапазоніC3: C10 розрахувати роздрібні ціни всіх товарів, якщо роздрібна ціна дорівнює сумі ціни товару і торгової націнки.

Торгова націнка	20%
Назва товару	Ціна товару, грн	Роздрібна ціна, грн
Маргарин	3,5
Масло соняшне	11,2
Мука	8,56
Картопля	1,9
М'ясо	25
Пиво	6,35
Ковбаса	47,62
Згущене молоко	4,65
Сухе молоко	22,4

3. Завдання 2. Оформіть фрагмент таблиці так, як показано на малюнку і заповніть її 5-тью записами.

Річні перевезення

Квартал	Назва фірми	Тип вантажу	Об’єм вантажу	Сума вантажу

4. Завдання 3. Змініть формат комірки. В комірку A1 введіть число 1256,34, заповніть цим числом блок A1:H1 і встановіть такі ж формати для цього числа, як показано на рисунку.

5. Завдання 4. Заповніть блоки комірок послідовностями так, як показано на малюнку.

6. Завдання 5. Створіть відомість розрахунку заробітної плати членів екіпажу заприкладом, який представлено на малюнку.

Розрахунок заробітної плати членів екіпажу

Розмір податкового податку 13%

П.І.Б	Нарахування зарплати	Податковий податок	До видачі
Іванов А.А.
Петров О.Л.
Сидоров С.Д.
Васильєв К.М.
Михайлов Д.В.
Загальна сума:
Всього членів фірми

ІІІ. Домашнє завдання:

1.Відредагувати звіт по лабораторній роботі.

2. Охарактеризувати складні функції.

5