ЛАБОРАТОРНА РОБОТА №5
[ попередній розділ ] [ зміст ] [ наступний розділ ]Використання формул і функцій в електронних таблицях Excel
Мета: Набути практичних навичок роботи з електронними таблицями Excel.
Порядок виконання роботи
- Набути практичних навичок роботи з електронними таблицями Excel.
- кількість стовпців – не менше 8;
– кількість рядків – не менше 10.
- Створити додаткові стовпці з обов’язковим використанням функцій “ЯКЩО”, “МИН”, “МАКС” та додатковий рядок “СУММ”.
- Відформатувати стовпець у певному форматі (за індивідуальним завданням, у відповідності з таблицею 9, число знаків поза комою 3).
- Зберегти створену таблицю під власним ім’ям.
- Скопіювати створену таблицю на інший листок книги. Усунути 5 рядків.
- Відредагувати вигляд таблиці на листку 2.
- Ввести в таблицю на листку 2 два додаткових стовпці, що мають формат “ДАТА”. Виконати дії над датами таблиці на листку 2.
1 Загальна характеристика табличного процесора Excel
На сьогодні Excel 2003 є досконалим і популярним табличним про–цесором родини Microsoft Excel для WINDOWS. Він дає змогу розв'язувати багато видів досить складних фінансово–економічних задач, здатний задовольнити потреби багатьох фахівців з економіки, банківської справи, менеджменту і маркетингу, а також фахівців з інших галузей знань [8, 10].
Крім обробки та аналізу табличних даних, Excel 2003 здатний:
- надавати користувачеві контекстуально–залежну допомогу;
- подавати дані в наочній графічній формі у вигляді гістограм, графіків і діаграм;
- працювати зі списками (базами даних) — створювати, формату– вати та сортувати списки, проводити пошук і вибір їх елементів за заданими критеріями – автофільтрами;
- оперативно аналізувати економічну діяльність об'єктів (організацій, підприємств, бірж, банків тощо), сприяти прийняттю правильних рішень;
- сортувати табличні дані за алфавітом, зростанням (спа-данням), датами і т. д.;
- сортувати табличні дані за алфавітом, зростанням (спа-данням), датами і т. д.;
- формувати зведені таблиці, звіти і навіть карти з ге-ографічною прив'язкою даних;
- створювати макроси, тобто макрокоманди, які вико-ристовуються для автоматизації розв'язання проце¬дур та задач, що часто повторюються, виконувати ряд інших функцій.
Отже, Excel 2003 є інтегрованим потужним програмним засобом. Від попередніх версій Excel 4.0, 5.0, '95 і відомих табличних процесорів SuperCalc 4, SuperСа1с 5 і Lotus 1–2–3 він відрізняється підвищеною комфортністю та значно розширеними можливостями ство¬рення реляційних баз даних, зведених звітів зі складною структурою й організацією зв’язку з різними додатками MS Office та всесвітньою н формаційною мережею Internet.
Excel взаємодіє з такими програмами–додатками, що підтримують технологію OLE:
- Wordart — програма формування текстових спеціальних ефек-тів;
- Equation Editor — програма введення математичних формул та рівнянь;
- Outlook — програма управління документами (планувальник);
- PowerPoint — потужна графічна програма–редактор;
і багатьма іншими.
Як додатки до Excel можуть використовуватися су¬часні текстові редактори, табличні процесори та СУБД, наприклад Word 7.0, Word'97, Lotus 1–2–3, Quattro Pro, Access'97, Foxpro, Dbase тощо.
В основу функціонування Excel, як і інших доатків програмного середовища Microsoft Office, покладено відомий принцип WYSWYG (What You See Is What You Get – що ви бачите, те й одержуєте). Як наслідок, системний інтерфейс Excel доступний широкому колу користувачів, не потребує спеціальних знань комп'ютерної техніки та інформатики, дає змогу аналізувати результати роботи і бачити їх на екрані в такій формі, в якій вони будуть надруковані на папері.
Управління системним меню Excel, а також вікнами Excel прово-диться за допомогою кнопок, розташованих у лівому та правому верхніх кутах вікна (див. табл. 5.2, 5.3 ).
В разі необхідності користувач може оперативно виключити окремі кнопки з панелі та додати до неї нові. Ця процедура легко виконується на етапі підготовки Excel 2003 до роботи за командою СЕРВИС—НАСТРОЙКА.
Після запуску програми потрібно встановити границі таблиці Excel. Для цього виділяють кількість рядків та стовпців відповідно до індивідуального завдання, а на панелі інструментів обирають кнопку ВСЕ ГРАНИЦИ
з метою налаштування потрібного виду границі.
Таблиця 10 – Основні кнопки стандартної панелі інструментів EXCEL
Табличний процесор (table processor) оперує такими об'єктами, як робочі книги і листки, комірки, діапазони комірок, стовпці та рядки. Робота з будь–яким об'єктом завжди по¬чинається з його виділення. При цьому фактично задається місце положення даних, які стають доступними для введення, виведення й обробки.
Табличний процесор ЕXCEL здатний виконувати над даними такі основні типи операцій: математичні, логічні, статистичні, текстові, фінан-сові та ін. Послідовність дій над операндами записується у вигляді спеціальних формул, що починаються зі знака рівності, наприклад:
= А5 – В7, = СУММ(С1:С10), =СТЕПЕНЬ (А1;А2),
де СУММ і СТЕПЕНЬ — імена стандартних функцій підсумовування і піднесення до степеня, відповідно.
Будь-яка формула, як текст або число, вводиться у обрану комірку робочого листка вручну. З метою економії часу і виключення помилок введення, посилання на комірки або їхні діапазони можна включати у формулу за допомогою миші. Для цього достатньо обрати її лівою кнопкою на поточній комірці або виділити “буксируванням” курсора потрібний діапазон.
У формулу можна також включати імена стандартних функцій, ви-бираючи їх зі спеціального списку вікна МАСТЕР ФУНКЦИЙ. Останнє активізується кнопкою ФУНКЦИИ.
У формулу можна також включати імена стандартних функцій, ви-бираючи їх зі спеціального списку вікна МАСТЕР ФУНКЦИЙ. Останнє активізується кнопкою ФУНКЦИИ.
Активізуйте комірку, у котру потрібно ввести результати обчислення функції.
Для одержання найбільшого числа із заданого діапазону потрібно ввести функцію МАКС (діапазон). Діапазон є аргументом функції – тобто тією величиною, що буде оброблятися. Як аргумент може виступати комірка, діапазон комірок або декілька комірок, розділених комами.
Якщо потрібно одержати найменше число з цього діапазону, скористайтеся функцією МІН (діапазон).
Щоб обчислити середнє арифметичне значення комірок у певному діапазоні, введіть функцію “= СРЗНАЧ” (діапазон). Функція СРЗНАЧ складає числа, розташовані в межах діапазону, і ділить результат на кількість величин, що додаються.
Розглянемо процедуру введення функції ЯКЩО з повним форма-том:
<ЯКЩО (логічний _вираз; значення_якщо_істина;значення_ якщо_хибність)>
Ця логічна функція визначає напрям обчислень і відіграє фактично роль оператора умовного переведенняу. Її перший операнд — логічний вираз, що набуває значень «ПРАВДА» та «НЕПРАВДА». Два інших операнди – це, як правило, значення арифметичних виразів або рядкові константи. Вибір того або іншого значення (константи) визначається істинністю чи помилковістю першого операнда.
Операнди функції вводяться у відповідні поля вікна «ЯКЩО» вручну або напівавтоматично. Напівавтоматичне введення реалізується так: обранням мишею кнопки мінімізації з червоною стрілкою розміри вікна скорочуються до розмірів обраного поля, яке потім послідовно заповнюється компонентами його операнда. При цьому посилання на комірки вводяться виділенням комірок у таблиці, константи і знаки порівняння — вручну, а роздільники – автоматично. Введення операнда завершується обранням кнопки мінімізації його поля, а всієї функції — натисненням на клавішу
МАСТЕР ФУНКЦИЙ автоматизує процес їх введення, залишаючи за користувачем тільки вибір функції та введення деяких констант.
Якщо комірка містить повідомлення #ИМЯ?, то формула, що міс-титься в ній, містить ім’я, яке EXCEL не може розпізнати. Потрібно перевірити, чи не було помилки при введенні адреси комірки або імені функції.
Якщо формула виводить на екран такий текст, відмовляючись виконувати обчислення, то це є ознакою відсутності знака "=".
Якщо отримані результати здаються помилковими, слід ще раз переглянути формули, навіть якщо EXCEL не виводить ніяких повідом-лень про помилки. Крім усього іншого, могли бути включені неправильні посилання на комірки. Іншими словами, подвійна перевірка результатів ніколи не зашкодить. EXCEL виконує будь–які математичні операції, що мають логічний зміст, незалежно від того, що мав на увазі користувач насправді.
Якщо отримані результати здаються помилковими, слід ще раз переглянути формули, навіть якщо EXCEL не виводить ніяких повідом-лень про помилки. Крім усього іншого, могли бути включені неправильні посилання на комірки. Іншими словами, подвійна перевірка результатів ніколи не зашкодить. EXCEL виконує будь–які математичні операції, що мають логічний зміст, незалежно від того, що мав на увазі користувач насправді.
Якщо при створенні нових формул був використаний маркер заповнення, у наступний момент автоматично будуть змінені посилання на комірки, відповідно до їхніх нових положень у таблиці. Тому такі посилання називають відносними. Але буває, що посилання на комірки не можна змінювати, наприклад, при копіюванні формул за допомогою маркера заповнення або будь–яким іншим способом. Посилання, що залишаються незмінними при копіюванні, називають абсолютними. Зараз ви дізнаєтесь, як створювати абсолютні посилання і для чого вони необхідні. Виділіть одну з комірок, що містить 0000, і помилкову формулу.
Рядок формул має досить велику довжину і широко використовується на практиці для введення та корекції даних будь–якого типу. Для виправлення рядка потрібно за допомогою маніпулятора миші обрати відповідну комірку, а потім рядок. Після появи вертикальної риски–курсора можна розпочинати корекцію формули, скориставшись клавішами управління
АВТОСУММИРОВАНИЕ можна застосовувати до діапазонів сусідніх стовпців (рядків), скориставшись відомим маркером заповнення. Виділення підсумкового рядка (стовпця) робочого листка, заповнення його розрахунковими формулами та значеннями проводяться перетягуванням маркера і натисненням на клавішу
Для здобуття підсумкових значень по всіх рядках або стовпцях таблиці необхідно виділити останню, натиснувши на клавіші
При виконанні розрахунків результат часто являє собою множину “довгих” (після десяткової коми) чисел. Якщо виникає необхідність повністю вмістити такі числа в комірках таблиці можна розширити її стовпці або відкинути один чи декілька десяткових розрядів, підібравши відповідний числовий формат. Ці способи дозволяють розв’язати проблему відображення чисел в робочій таблиці при виведенні цієї таблиці на екран монітора. Але при цьому числові значення, що зберігаються в пам’яті, не зміняться. У обчисленнях, як і раніше, будуть брати участь повні числа. Якщо необхідно частково або повністю вилучити десяткові розряди в поданні чисел, потрібно скористатися функціями ОКРУГЛ (ROUND) і ЦЕЛОЕ (IND) або набрати функції ОКРУГЛ і ЦЕЛОЕ вручну.
Якщо ім’я потрібної функції виявлено у вікні списку ФУНКЦИЯ виділіть його, обравши за допомогою маніпулятора миші. Якщо ж потрібна функція відсутня, у списку КАТЕГОРИЯ оберіть категорію, до складу якої входить шукана функція.
Якщо не вдалося знайти функцію ОКРУГЛ, потрібно обрати категорію МАТЕМАТИЧЕСКИЕ, після чого знайдіть функцію ОКРУГЛ у списку функцій. Оберіть кнопку ОК. У діалоговому вікні, що з’явилося, у поле ЧИСЛО введіть число, що округлюється, а в поле КОЛИЧЕСВО ЦИФР введіть кількість десяткових розрядів, до якого буде виконано округлення. В обох випадках можна вводити або число або посилання. Якщо бажаєте, оберіть кнопку згортання діалогового вікна, щоб повернутися до робочої таблиці і виділити в ній потрібне число. Оберіть кнопку ОК. EXCEL помістить результати в робочу таблицю. Функція ОКРУГЛ виконає округлення числа до заданої кількості десяткових розрядів. Якщо буде задано 0 розрядів, то округлення буде виконано до найближчого цілого числа. Якщо заданий один розряд, буде видно 1 розряд після десяткової крапки.
АВТОВЫЧИСЛЕНИЕ забезпечує формування ряду підсумкових значень поза робочим листком, що використовуються для аналізу й оцінки результатів обчислення.
Тип підсумкового значення (середнє, кількість значень і чисел, максимум та ін.) задається за допомогою команд спеціального контекстного меню (рис.5.7), яке активізується за допомогою маніпулятора миші. Обране користувачем підсумкове значення виділеного об’єкта робочого листка відображається в рядку стану.
В EXCEL є декілька форматів дат. Крім того, над ними можна виконувати різні операції. Можна змінювати формати дат (за допомогою вкладки ЧИСЛО діалогового вікна ФОРМАТ ЯЧЕЙКИ) і сортувати їх у порядку зростання чи спадання. Над датами можна також виконувати арифметичні дії. Наприклад, щоб одержати будь–яку дату в майбутньому, можна додати до поточної дати задане число днів. Або, якщо з однієї дати відняти іншу, можна визначити число днів, що пройшли між ними.
Рекомендації: щоб обрати спосіб подання дат в робочій таблиці, виділіть потрібні комірки й у меню ФОРМАТ оберіть команду ЯЧЕЙКИ. У списку ЧИСЛОВИЕ ФОРМАТЫ вкладки ЧИСЛО включіть режим ДАТА, а в списку ТИП із широкого набору форматів дат оберіть потрібний. При цьому спосіб подання дат збережуваних програмою EXCEL не змінюється, змінюється тільки спосіб зовнішнього подання дат у робочій таблиці. Крім арифметичних дій, для обробки дат можна використовувати й інші функції EXCEL. Для одержання докладної інформації про них зверніться до пункту ФУНКЦИИ ДАТИ у вкладці ПРЕДМЕТНЫЙ УКАЗАТЕЛЬ, що міститься у діалоговому вікні СПРАВОЧНАЯ СИСТЕМА.
Багато таблиць містять дуже схожі формули. Наприклад, перший стовпець справа або нижній рядок у таблиці часто містять суми стовпців зверху або рядків ліворуч. Хоча можна вводити усі формули окремо, набагато зручніше користуватися маркером заповнення програми EXCEL, який дозволяє створювати набір ідентичних формул, що різняться лише посиланнями. Коли заповнюється рядок або стовпець, EXCEL відповідним чином налаштовує і посилання на комірки, причому, як правило, не помиляється.
Для того щоб відредагувати Будь-який стовпець за варіантом завдання, потрібно виділити цей стовпець і обрати його за допомогою маніпулятора миші. В меню, що з’явиться, потрібно обрати ФОРМАТ ЯЧЕЕК, як показано на рисунку 11:
Зберегти таблицю під власним ім'ям. Для цього обрати команду ФАЙЛ => СОХРАНИТЬ ЯК... і у вікні, що з’явилося, обрати поле ИМЯ ФАЙЛА, де записати власне ім'я та натиснути кнопку СОХРАНИТЬ.
Виділивши всю таблицю потрібно за допомогою маніпулятора миші обрати її і у вікні, що з’явилося, обрати команду КОПИРОВАТЬ. Після чого внизу головного вікна EXCEL натиснути на вкладку «ЛИСТ 2» та вставити таблицю на новий листок, обравши його правою клавішею миші, і з меню що з’явилося обрати команду ВСТАВИТЬ.
Для того, щоб відредагувати вигляд таблиці, потрібно скористатись вкладками, що подані у вікні на рисунку 12.
Вкладки знаходяться у верхній частині головного вікна EXCEL.
Наприклад, додати знову два нових стовпці до таблиці та, використавши команду ФОРМАТ ЯЧЕЕК, обрати формат ДАТА, виконати функції ВИЧИТАНИЕ (ATAN2), СУММИРОВАНИЕ (СУММ) [1].
1. Які дії потрібно виконати для застосування формули?
2. Для чого потрібен МАСТЕР ФУНКЦИЙ?
3. Як можна обчислити максимальне (мінімальне, середнє) зна-чення чисел, вміщених в декількох комірках?
значення чисел, вміщених в декількох комірках? 4. Які команди слід виконувати, щоб з’явилося діалогове вікно МАСТЕР ФУНКЦИЙ?
5. Як реалізується напівавтоматичне введення операнди функції ЯКЩО?
6. Як вписати формулу в таблицю?
7. Які помилки можуть виникнути при роботі в електронних таблицях EXCEL? Як з ними боротися?
8. Що потрібно зробити, щоб відредагувати комірку з неправильною формулою?
9. Що потрібно зробити, щоб здобути підсумкове значення по всіх рядках або стовпцях таблиці?
10. Для чого використовується функції ОКРУГЛ і ЦЕЛОЕ?
10. Для чого використовується функції ОКРУГЛ і ЦЕЛОЕ? 11. Для чого використовується функція АВТОВИЧИСЛЕНИЕ?
12. Як можна знайти підсумкові значення, середні значення, кількість значень і чисел, максимальне та мінімальне із значень тощо?
13. Яким чином можна змінювати формат дат?
14. Які формати дат є в електронних таблицях EXCEL?
15. Які дії можна виконати над датами в електронних таблицях EXCEL?
16. Які формати дат в EXCEL Вам відомі?
17. Чи можна виконувати операції над датами?
18. Що таке маркер заповнення програми EXCEL?
19. Які функції маркера заповнення програми EXCEL?
20. Запропонуйте дії щоб відредагувати Будь-який стовпець таблиці EXCEL.
21. Як відредагувати вигляд таблиці?
22. Які дії дозволяют зберегти створену таблицю?