Лабораторна робота 3 (6 год.)

Тема роботи: „Обчислення в EXCEL. Формули та функції. Графічне представлення даних”

 

Мета роботи: навчитись створювати формули, здійснювати обчислення, виводити розрахункові дані на графік та розв’язувати фінансово-статистичні задачі

Теоретична частина

1. Створення формул

Існують різні способи створення формул. Якщо формула невелика та містить найпро­стіші операції, то її можна створити безпосередньо з кла­віатури. Для цього потрібно у комірці, в якій повинен знахо­дитись результат, ввести знак ”=” та формулу, за якою буде обчислюватись результат. На­приклад, для того щоб обчислити значення виразу , де і , потрібно ввести вхідні дані у відповідні комірки (мал. 2.1), напри­клад A2 та B2, а після цього в комірку, де буде результат (напри­клад у комірку A4) достатньо ввести з клавіатури вираз =3*a2+b2-0,45. Після введення цього виразу потрібно натиснути клавішу [ Enter ]. У комірці A4 з’явиться результат обчислень: 206,14. Зверніть увагу, що байдуже які літери вводити в адреси комірок – великі чи малі, відіграє роль лише розкладка клавіатури, тобто літери, які вводяться в адреси комірок повинні бути латинськими.

Для обчислення значення цього ж виразу для інших вхідних даних достатньо змінити значення параметрів a та b у відповідних комірках. Після натискання клавіші [ Enter ] результат автоматично зміниться. Якщо Ви активізуєте комірку з результатом, то у рядку формул завжди можна буде бачити формулу, за якою проводиться обчислення. Цю формулу можна також побачити в режимі редагування комірки, натиснувши функціональну клавішу [ F2 ].

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

Після того як в рядку формул уже опиниться запис =3*, потрібно клацнути лівою кнопкою миші в комірці A2. У результаті цього навколо комірки з’явиться пунктирна рамка, а в рядку формул – адреса комірки. Цей спосіб зручний, якщо потрібно створити посилання на комірки, які знаходяться далеко одна від одної.

Якщо в ролі змінної потрібно використати комірку, що знаходиться на іншому робочому аркуші, то перед її адресою необхідно вказати назву цього аркуша і знак ”!” (”оклику”). Наприклад, Дані!B3+Результат!B7 означає до значення комірки B3, яка знаходиться на аркуші Дані, додати значення комірки B7, яка знаходиться на аркуші Результат.

При створенні абсолютного посилання знак долара ”$” необхідно ставити з клавіатури або, активізувавши адресу комірки у формулі, натиснути функціональну клавішу [F4].

2. Відслідковування залежностей у формулах

Для того щоб користувач міг перевірити залежності між комірками (найчастіше це потрібно робити при появі помилки у формулі), застосовують команду меню Сервис Þ Зависимости Þ Панель инструментов. Функція відслідковування залежностей дозволяє графічно позначити зв’язки між залежними комірками та комірками, які впливають на результат (впливаючими комірками). При виборі однієї з можливих команд меню Сервис – Влияющие ячейки, Зависимые ячейки, Источник ошибки, Убрать все стрелки (або відповідних кнопок панелі інструментів Зависимости (мал. 2.2)) користувач може побачити, яким чином зв’язані комірки та, якщо потрібно, визначити джерело помилки. Приклад аркуша, на якому видно застосування функції відслідковування залежностей, подано на мал. 2.3.

Мал. 2.2. Панель інструментів Зависимости

Мал. 2.3. Фрагмент аркуша з відображеними зв’язками між комірками

3. Вставка імені комірки у формули

Якщо у формулі використовуються посилання на комірки, які мають імена, то ці імена можна застосовувати у ролі аргументів. Для цього потрібно зазначити комірки з такими формулами. Після цього активізуйте команду меню Вставка Þ Имя Þ Применить. У діалоговому вікні Применение имен виберіть імена комірок, які виступають у ролі аргументів виділених формул та натисніть кнопку ОК. Програма автоматично замінить посилання на комірки з вказаним ім’ям. При цьому відносні посилання заміняться на абсолютні.

Якщо потрібно встановити режим заміни лише абсолютних посилань, то у діалоговому вікні Применение имен необхідно виключити опцію Игнорировать тип ссылки.

Якщо у формулі вказується неіснуюче чи некоректне ім’я, то в комірці з формулою з’являється помилка #Имя?.

4. Копіювання формул

Скопіювати формулу можна одним з двох способів:

Спосіб 1. Виділіть комірку (в яку введена формула) і скопіюйте в буфер обміну її вміст. Це можна зробити за допомогою: або команди Правка Þ Копировать,

§ або клавіш [ Ctrl+Insert ] або [ Ctrl+C ],

§ або кнопки панелі інструментів,

§ або команди Копировать контекстного меню.

Зазначте комірку, в яку копіюється формула і вставте в неї вміст буферу обміну за допомогою:

§ або команди Правка Þ Вставить,

§ або клавіш [ Shift+Insert ] або [ Ctrl+М ],

§ або кнопки панелі інструментів,

§ або команди Вставить контекстного меню.

Спосіб 2. Виділіть комірку, з якої копіюється формула, розташуйте вказівник мишки на маркері заповнення виділеної комірки і протягніть його вниз (вверх, вліво, вправо) до останньої комірки діапазону копіювання. Після цього відпустіть мишку.

Зауваження. При розташуванні вказівника миші на маркері заповнення він перетворюється на чорний хрестик.

5. Застосування формул масиву

Для обчислення елементів масиву в Excel можна використовувати єдину формулу на всі елементи масиву. Для цього після виділення діапазону, де повинні знаходитись результати обчислень, потрібно ввести необхідну формулу для обчислення елементів масиву, після чого натиснути комбінацію клавіш [Ctrl+Shift+Enter] (а не [ Enter ], як в звичайних формулах). У результаті цього в кожній комірці виділеного діапазону буде знаходитись формула у фігурних дужках.

Зауваження. Коли фігурні дужки ввести вручну (з клавіатури), то бажаного результату користувач не отримає (введена формула буде сприйматись як звичайний текст).

6. Перегляд таблиці у формульному режимі

З формулами можна ознайомитись, якщо помістити курсор на комірку, в якій вона міститься. У цьому випадку в рядку формул видно зміст формули.

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

Для того щоб побачити всі формули, що розміщені в комірках робочого аркуша, потрібно вибрати команду меню Сервис Þ Параметры …, вибрати вкладку Вид, встановити прапорець у полі Формулы і зафіксувати вказівник мишки на кнопці ОК. Коли потрібно заховати формули і показувати лише результати, то прапорець у полі Формулы потрібно зняти.

7. Застосування вбудованих функцій

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

У Excel 2000 вперше з’явилась панель формул. Вона призначена для полегшення роботи з функціями. Для того щоб запустити цю панель, потрібно клацнути вказівником миші по кнопці рядка формул. У результаті цього під рядком формул з’явиться сіра область, на якій знаходяться дві кнопки: ОК та Отмена. Для вибору функції призначено список у лівій частині рядку формул. Цей список містить імена десяти функцій, які використовувалися найближчим часом (мал. 2.4). Якщо потрібної функції у списку немає, користувач повинен вибрати елемент Другие функции… цього списку, в результаті чого відкриється діалогове вікно майстра функцій (мал. 2.5). (Програму Мастер функций можна викликати також за допомогою кнопки стан­дартної панелі інструментів або команди меню Вставка Þ Функция.)

Мал. 2.4. Вигляд екрану з панеллю формул

Мал. 2.5. Діалогове вікно майстра функцій

Зауваження. З усіма категоріями функцій та параметрами аргументів можна ознайомитись в довідковій системі Excel 2000.

Користувачеві потрібно вибрати категорію, до якої належить функція, а в полі Функция – вибрати назву конкретної функції. У нижній частині вікна майстра відображено короткий опис зазначеної функції та її синтаксис. Після вибору функції натисніть кнопку ОК, щоб можна було повернутися на панель формул та задати аргументи. Програма допомагає користувачу задати аргументи за допомогою наступного вікна майстра.

Розглянемо другий крок майстра на прикладі функції ЕСЛИ. У результаті виконання першого кроку програми майстра функцій на екрані з’явиться діалогове вікно, зображене на мал. 2.6.

Мал. 2.6. Вигляд панелі формул для функції ЕСЛИ

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

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

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

8. Графічне представлення даних

Для створення діаграми у програмі існує спеціальний майстер діаграм, який можна викликати за допомогою команди меню Вставка Þ Диаграмма або кнопки стандартної панелі інструментів. Перед викликом майстра необхідно виділити діапазон комірок, дані яких необхідно відобразити на діаграмі. Після виклику майстра на екрані з’явиться діалогове вікно, в якому потрібно вибрати тип діаграми (мал. 2.7). Користувач повинен визначити, який тип діаграми буде найкраще відображати картину.

Мал. 2.7. Діалогове вікно майстра діаграм (1 крок)

Визначивши тип та формат діаграми, клацніть по кнопці Готово, після чого програма створить діаграму, використовуючи параметри автоформатування, які встановлені за замовчуванням. Кнопка Просмотр результатов призначена для попереднього перегляду діаграми у вибраному форматі. Якщо її натиснути, то в діалоговому вікні з’явиться поле Образец, яке містить зображення майбутньої діаграми. Для того щоб перейти на наступний етап створення діаграми, натисніть кнопку Далее.

На екрані з’явиться друге діалогове вікно майстра діаграм (мал. 2.8), де потрібно задати діапазон даних, в яких знаходяться значення, котрі потрібно відобразити графічно. Якщо Ви виклика­ли майстер діаграм після того, як виділили комірки з даними, тоді на вкладці Диапазон данных цей діапазон відобразиться. При потребі його можна змінити. Перемикачі цієї вкладки дозволяють змінити орієнтацію даних. Вкладка Ряд призначена для додавання та знищення рядів даних та задання підписів осі X.

 

Мал. 2.8. Діалогове вікно 2 кроку майстра діаграм

Наступний (третій) крок майстра діаграм дозволяє задати па­раметри елементів діаграми. Кількість та вигляд вкладок цього діа­логового вікна залежать від типу вибраної діаграми. Зокрема, вклад­ка Оси дозволяє встановити режим відображення міток на осях. За замовчуванням усі мітки відображаються. Нанести на діаграму масштабну сітку різної густини можна, задавши відповідні опції на вкладці Линии сетки. Якщо потрібна діаграмі легенда, то користу­вач може задати відповідну умови для її створення на вкладці Легенда. Іноді поруч з діаграмою варто розмістити таблицю даних. У цьому випадку потрібно задати відповідну опцію на вкладці Таблица данных. Задавши усі потрібні опції вибраної діаграми, користувач за допомогою кнопки Далее перейде на наступне вікно майстра діаграм.

Останнім кроком створення діаграми є визначення місце­зна­ходження діаграми: на окремому аркуші чи на аркуші з таблицею. Кнопка Готово остаточно завершує процес створення графічного представлення даних. Створену діаграму можна редагувати. Якщо необхідно змінити якийсь з елементів графіку, його потрібно виді­лити (про це свідчать чорні квадратики у відповідних місцях) і двічі клацнути лівою кнопкою мишки або викликати контекстне меню цього елемента. Щоб змінити розміри області діаграми, зачепіться за маркери зміни розміру вікна та протягніть їх у потрібному напрямку.

Діаграму, яка створена на робочому аркуші, можна скопію­вати на аркуш діаграм та навпаки. Цю операцію можна виконати за допомогою буфера обміну.

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

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

Послідовність виконання лабораторної роботи

1. Створити прайс-лист фірми

Створіть електронну таблицю, ввівши у відповідні комірки текстові дані, запропоновані у табл. 2.4. За допомогою функції Авто­заповнення заповніть діапа­зон A4:A10 послідовністю натуральних чисел. Стовпець Найменування товару (B4:B10) заповніть до­вільними назва­ми даних, а стовпець Ціна в у.о (C4:С10) – числами (цінами в доларах). Відформатуйте належ­ним чином заповнені комірки.

У комірку D4 введіть формулу, за допомогою якої обчислю­ва­тиметься ціна в гривнях (= $D$2*С4), скопіюйте введену формулу в діапазон D5:D10 та задайте відповідний формат коміркам.

У комірках C11 та D11 обчисліть суми значень, які зна­ходяться в діапазонах C4:C10 та D4:D10, використовуючи функцію автосумування. Встановіть у цих комірках відповідні формати.

Вкажіть дату випуску прайс-листа, ввівши функцію Сегодня у комірку D1. У лівому верхньому кутку розмістіть емблему фірми (довільний малюнок, який може характеризувати напрямок діяльності фірми).

Присвойте комірці D2 ім’я ”Курс долара” та застосуйте це ім’я у формулах, де є посилання на цю комірку.

2. Протабулювати функцію на проміжку [0, 1] з кроком 0,1. Побудувати графік функції.

Табулювання кожної функції варто здійснювати на окремому аркуші робочої книги. Для того щоб Ваш аркуш мав гарний вигляд та був зрозумілий для користувачів, спочатку напишіть у комірці А1 заголовок завдання ”Табулювання функції однієї змінної і побудова графіка”. Задайте параметри сторінки та введіть потрібну інформацію в колонтитули. Тепер варто записати саму функцію засобами редактора формул Microsoft Equation 3.0. Для цього, активізуйте якусь комірку (наприклад комірку С4), виберіть послідовно пункти меню Вставка Þ Объект Þ Microsoft Equation 3.0 Þ Ok. При цьому завантажиться редактор формул, в якому необхідно ввести аналітичне представлення функції, яку потрібно протабулювати. Вставлений об’єкт можна перемістити за допомогою мишки в інше місце робочого аркуша або можна змінити його розміри, потягнувши за відповідний маркер зміни розміру.

Для побудови таблиці значень функції у при різних значеннях аргументу х (тобто, для табулювання функції) введіть в комірку В5 символ х, в комірку С5 – у.

У діапазон комірок В6:В16 введіть такі значення змінної х: 0; 0,1; 0,2; 0,3; 0,4; 0,5; 0,6; 0,7; 0,8; 0,9; 1. Значення змінної х утворюють арифметичну прогресію, тому заповнити ці комірки можна за допомогою функції авто заповнення.

У комірку С6 введіть формулу =СOS(ПИ()*В6)^2. Для цього виділіть комірку С6 та викличте майстер функцій. У діалоговому вікні майстра функцій виберіть функцію COS, яка належить до категорії Математические, і натисніть кнопку ОК. Після того як на екрані з’явиться наступне діалогове вікно, в полі Число введіть аргумент функції – ПИ()*В6. Аргумент функції можна ввести з клавіатури, однак цей аргумент містить вбудовану функцію ПИ(), для введення якої повторно викличте Мастер функций за допомогою кнопки , розташованої після кнопки у рядку формул, та виберіть зі списку запис Другие функции…

Після завершення введення аргументу функції, натисніть кнопку ОК. У комірці висвітлиться результат обчислення значення функції для одного значення аргументу.

Скопіюйте введену в комірку С6 формулу в діапазон С7:С16, користуючись послугами маркера заповнення.

Зауваження. Таблиця функцій не обов’язково повинна починатися з комірки В5, а може знаходитись у довільному місці аркуша.

Для побудови графіка функції виділіть діапазон комірок С6:С16, який містить таблицю значень функції, і викличте Мастер диаграмм. За допомогою вказівок програми майстра діаграм виберіть вид та тип діаграми (у нашому випадку графік). Після натискання кнопки Далее розкриється діалогове вікно Мастер диаграмм (шаг 2 из 4): источник данных диаграммы. На вклад­ці Диапазон данных зафіксуйте вказівник мишки на радіоклавіші Ряды в столбцах. Активізуйте вкладку Ряд, зафіксуйте курсор в полі Подписи оси Х і виділіть у таблиці діапазон В6:В16. У наступному діалоговому вікні майстра задайте параметри діаграми: на вкладці Заголовки у поле Название диаграммы введіть Графік функції, у поля Ось Х (категорий) і Ось У (значений) введіть х і у відповідно.

Мал. 2.9. Розв’язок задачі 2

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

 

3. Протабулювати функцію

на проміжку [0, 1] з кроком 0,1.

Для табулювання функції у (x) здійсніть дії, аналогічні діям, необхідним для розв’язання попередньої задачі, тільки в комірку С6 введіть формулу:

=ЕСЛИ(В6<0,2; 1+LN(1+B6); ЕСЛИ (И (В6>=0,2; B6<=0,8); (1+B6^(1/2))/(1+B6); 2*EXP(-2*B6))).

Синтаксис логічної функції И:

И (логическое_значение1; логическое_значение2; …).

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

В комірку С6 можна ввести і простішу формулу:

=ЕСЛИ(В6<0,2; 1+LN(1+B6); ЕСЛИ (В6<=0,8; (1+B6^(1/2))/(1+B6); 2*EXP(-2*B6)))

4. Використовуючи текстові функції, створити запис, який містить інформацію з різних стовпчиків.

Нехай нам дано таблицю (див. табл. 2.5), яка містить таку інформацію: Місто, назву вулиці, номер будинку та номер квартири.

Таблиця 2.2.

Вхідні дані задачі

	Львів	Тернопільська
	Київ	Транспортна
	Теребовля	Верхня

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

="місто ٱ "&B1&", ٱ вул."&C1&" ٱ № "&D1&", ٱ кв. ٱ "&E1.

У результаті виконання такої функції Ви отримаєте запис: