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

План.

Поняття ЕТ.

Призначення та можливості табличних процесорів.

Інтерфейс таблично­го процесора Excel та його основні елементи.

Подання даних в ЕТ.

Введення даних. Автоматизація уве­дення.

 

Терміни: електронна таблиця, робочий аркуш, робоча книга, комірка,

діапазон комірок, абсолютна адреса комірки, відносна адреса комірки, , автозаповнення, автозавершення.

Електронна таблиця найпоширеніша й потужна інфор­маційна технологія для професійної роботи з даними. Для керування елек­тронною таблицею створені спеціальні програмні продукти табличні процесори.

Електронні таблиці (ЕТ) — це двовимірні маси­ви (які зазвичай називають робочими аркушами), що складаються зі стовпців і рядків. Програмні засоби для проектування ЕТ називають також табличними процесорами. Головна перевага ЕТ — можливість миттєвого перерахування всіх даних, пов'язаних формульними залежностями у випадку змінення значення будь-якого опе­ранда.

Можливості табличних процесорів:

— створення й редагування ЕТ; оформлення й друк ЕТ;

— створення багатотабличних документів, об'єднаних формулами;

— побудова діаграм, їх модифікація й розв'язання задач графічними ме­тодами;

— робота з ЕТ як із базами даних (сортування таблиць, вибірка даних за за­питами);

— створення підсумкових і зведених таблиць;

— розв'язання задач типу «якщо» шляхом підбору параметрів; я.

— розв'язання оптимізаційних задач;

— статистичне опрацювання даних та ін.

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

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

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

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

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

У рядку стану (статусному рядку) користувач знайде відомості про поточний режим роботи програми, ім'я файлу поточної ЕТ, номери поточного вікна й і т. п.

Уся робоча ділянка вікна Excel зайнята чистим робочим аркушем (або таблицею), розділеним на окремі комірки. Стовпці озаглавлені буквами, рядки — цифрами. Рядки пронумеровані від 1 до 65 536, стовпці названі бу­квами від А до Z і комбінаціями букв від АА, АВ і т. д. до IV.

На перетині стовпця й рядка розміщується комірка. Адреса комірки скла­дається із вказівки стовпця й рядка. Для вказівки адреси комірок у формулах зазначаються посилання (наприклад, А2 або С4). В ЕТ існує поняття діапазону комірок, який також має свою унікальну адресу. Як блок комірок може розглядатися рядок або частина рядка, стовпець або частина стовпця, а також прямокутник, який складається із декількох рядків і стовпців або їхніх частин. Адреса блока комірок задається вказівкою посилань першої і останньої його комірок, між якими розташований розділовий знак — символ двокрапка.

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

Формула починається символом «=» і являє собою сукупність операндів, об'єднаних знаками операцій і круглих дужок. Операндом може бути число, текст, логічне значення, адреса комірки (посилання на комірку), функція. У полі комірки після уведення формули може відображатися або формула, або значення, обчислене за формулою.

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

Під час роботи із числами використовується метод автозаповнення. У правому нижньому куті рамки поточної комірки є чорний квадратик — маркер заповнення. У разі наведення на нього покажчик миші (він зазвичай виглядає як товстий білий хрест) набуває форми тонкого чорного хрестика. Перетягування маркера заповнення розглядається як операція «розмножен­ня» вмісту комірки в горизонтальному або вертикальному напрямку.

Нехай, наприклад, комірка А1 містить число 1. Наведіть покажчик миші на маркер заповнення, натисніть праву кнопку миші, перетягніть маркер заповнення так, щоб рамка охопила комірки А1, В1 і СІ, і відпустіть кнопку миші. Якщо тепер вибрати в меню, що відкрилося, пункт Копировать ячейки, всі комірки будуть містити число 1. Якщо ж вибрати пункт Заполнить, то в комірках з'являться числа 1, 2 і 3. Щоб точно сформулювати умови запов­нення комірок, слід дати команду

Правка/Заполнить/Прогрессия.

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

Автозаповнення формулами виконується так само, як автозаповнення числами. Його особливість полягає в необхідності копіювання посилань на інші комірки. У ході автозаповнення до уваги береться характер посилань у формулі: відносні посилання змінюються відповідно до відносного розта­шування копії й оригіналу, абсолютні залишаються без змін. Для прикладу припустимо, що значення стовпця С обчислюються як суми значень у відпо­відних комірках стовпців А і В. Уведемо в комірку СІ формулу =А1 + В1. Тепер скопіюємо цю формулу методом автозаповнення в усі комірки третього стовпця таблиці. Завдяки відносній адресації формула буде правильною для всіх комірок цього стовпця. У таблиці наведені правила відновлення поси­лань у разі автозаповнення уздовж рядка або стовпця.

Посилання у вихідну комірку Посилання в наступну комірку
  У разі заповнення вправо У разі заповнення вниз
А1 (відносна) В1 А2
$А1 (абсолютна за стовпцем) $А1 $А2
А$1 (абсолютна за рядком) А$1 А$1
$А$1(абсолютна) $А$1 $А$1

Типи даних, з якими може працювати електронна таблиця: число, текст, формула

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

1. Редагування вмісту комірок.

2. Переміщення

й копіювання комірок у таблиці. Спеціальна вставка.

3. Форматування комі­рок таблиці.

4. Захист від змін.

5. Посилання на інші аркуші й зв'язування робочих книг.

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

Терміни: редагування, переміщення, копіювання, захист, зв'язування таблиць, зовнішнє посилання, вилучене посилання.

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

У випадку незначних помилок дані краще не переписувати, а редагувати в комірках. Для цього слід активізувати режим редагування за допомогою подвійного клацання на комірку, після чого праворуч від умісту комірки з'явиться мерехтливий курсор уведення.

Під час видалення вмісту комірок параметри форматування не видаляють­ся. Якщо, наприклад, був вилучений уміст комірки із процентним стилем, то буде привласнений процентний стиль через уведення числового значення в ту саму комірку. У цьому випадку необхідно змінити параметри форматування для комірки або видалити формат комірки за допомогою команди Очистить форматы меню Правка.

Переміщення й копіювання даних. У Microsoft Excel уміст окремої комір­ки може бути переміщений або скопійований. При цьому вміст комірок, у які здійснюється перенесення, автоматично видаляється. Щоб уникнути цього, необхідно вставити порожні комірки в ту ділянку електронної таблиці, у яку необхідно перенести дані.

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

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

Форматування вмісту комірок.Текстові дані за замовчуванням вирів­нюються за лівим краєм комірки, а числа — за правим. Щоб змінити фор­мат відображення даних у поточній комірці або вибраному діапазоні, слід скористатися командою Формат, Формат ячейки.

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

У MS Excel передбачено кілька рівнів захисту, що дозволяють керувати доступом до даних цієї таблиці та їхньою зміною.

Захист на рівнікниги. Можна застосувати захист до елементів книги, й захистити файл книги від перегляду й внесення змін. Якщо книга є спільною, можна заборонити її монопольне використання й запо­бігти видаленню журналу змін.

Захист елементів аркуша. Можна заборонити вставляння, видалення й форматування рядків і стовпців, змінення вмісту заблокованих комірок або переміщення курсору на заблоковані або розбло­ковані комірки.

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

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

Зв'язок, який посилається на іншу книгу, іноді називають зовнішнім по­силанням. Зв'язок також може посилатися на інший додаток, у цьому випад­ку посилання називають вилученим. Оскільки дані в іншій книзі або іншому додатку можуть змінюватися й більше не відповідати даним у користувацькій книзі, існують параметри для контролю відновлення зв'язків.

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

Коли книга відкрита, посилання містить ім'я книги у квадратних дужках, за яким іде ім'я аркуша, знак оклику (!) і адреса комірок, які впливають на формулу. Наприклад, наведена нижче формула додає комірки С10:С25 із аркуша з назвою Годовой книги, що називається 2006.xls.

=СУММ([2006.хІз]Годовой!С10:С25)

Коли книга закрита, посилання містить повний шлях.

=CУMM(C:\Reports\[2006.xls]rodoeou'!C10:C25)

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

Зв'язаний об'єкт за замовчуванням обновляється автоматично щоразу або під час відкривання кінцевого файлу, або зміни вихідного файлу, поки кінце­вий файл відкритий. У разі відкривання книги видається запит на відновлення зв'язків. Це основний спосіб відновлення зв'язків, однак обновляти їх можна і вручну. Крім того, можна керувати відповіддю на запит та його відображення.

Якщо для зв'язку з даними з інших програм використовуються формули, то дані обновляються в Microsoft Excel автоматично під час їхнього змінення у вихідній програмі.

'

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

ПЛАН ВІДПОВІДІ

1. Поняття формули в Excel.

2. Поняття функції.

3. Математичні функції.

Термін: формула електронної таблиці, функція електронної таблиці

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

Під формулою в ЕТ розуміють вираз, що складається з операндів і операцій. Формули будуються як вираз для обчислення нового значення. Тип зна­чення, отриманого в результаті обчислення за формулою, визначається типом операндів виразу. Формула завжди починається із символу = (дорівнює). Операнди використовують: числа; текст (вводиться у подвійних лапках, на­приклад «Неявка»); логічні значення (наприклад, ИСТИНА і ЛОЖЬ і т. д.); значення помилки (типа #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! І #ЗНАЧ!); посилання — адреси комірок. Під час переліку по­силання розділяються крапкою з комою, наприклад, С5; СЮ: Е20; убудовані функції Excel.

Операнди у формулах з'єднуються за допомогою символів операцій: ариф­метичних операцій: + (додавання), - (віднімання), / (ділення), * (множення)," (піднесення в степінь); операційні відношення: >, >= (не менше), <, <= (не більше), =, <> (не дорівнює). Обчислюючи значення арифметичного виразу, операції виконують зліва направо із дотриманням трьох рівнів пріоритету: спочатку виконується піднесення в степінь, потім множення й ділення, потім додавання й віднімання. Послідовність виконання операцій можна змінити за допомогою круглих дужок: спочатку обчислюються значення виразів, за­писаних усередині дужок нижчого рівня (у самих внутрішніх дужках) і т. ін. Результатом обчислення арифметичної формули є число. За замовчуванням ЕТ обчислює формули в разі уведення, перераховує їх повторно під час кож­ної зміни у них вихідних даних, які в них входять.

Формули можна копіювати в інші комірки. При цьому залежно від типів посилань, які входять у копійовану формулу, здійснюється їхнє настроюван­ня: автоматичне (для відносних посилань) або напівавтоматичне (для част­ково абсолютних посилань (див. білет А/° 1(2)). Формули можуть містити функції.

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

Функціямає ім'я (наприклад, Корень) і, як правило, аргументи, які за-1 писуються в круглих дужках слідом за ім'ям функції. Дужки — обов'язкова приналежність функції, навіть якщо в неї немає аргументів. Якщо аргу­ментів декілька, один аргумент відокремлюється від іншого комою. Як аргументи функції можуть використовуватися числа,

адреси комірок, діапазо­ни комірок, арифметичні вирази й функції. Зміст і порядок проходження аргументів однозначно визначений описом функ­ції, складеним її автором. Наприклад, якщо в комір­ці А7 записана формула =$IN(A6*2), її значенням буде значення функції синуса комірки А6 у ква­драті.

Програма Excel містить понад 400 вкорінених функцій, отже, вводити у формулу назви функцій і значення вхідних пара­метрів не завжди зручно. Для спрощення введення функцій в Excel перед­бачений спеціальний Мастер функций, який можна викликати натисканням кнопки fx рядка формул. Мастер функций також викликається командою Вставка, Функция.

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

Найпоширеніші математичні функції Excel.

Функція Призначення
ABS (число) Повертає модуль значення аргументу
ACOS (число) Повертає арккосинус числа в радіанах у діапазоні від 0 до п
ASIN (число) Повертає арксинус числа в радіанах у діапазоні від-71/2 до к/2
COS (число) Повертає косинус числа, заданого в радіанах
ЕХР (число) Повертає експоненту числа ех
LN (число) Повертає натуральний логарифм числа (основою є = 2,71828...)
LOG (число) Повертає логарифм числа за заданою основою
LOG10 (число) Повертає десятковий логарифм числа
SIN (число) Повертає синус числа, заданого в радіанах
TAN (число) Повертає тангенс числа, заданого в радіанах
ATAN (число) Повертає арктангенс числа в радіанах у діапазоні від-71/2до л/2
ГРАДУСЫ (угол) Перетворює градуси в радіани
КОРЕНЬ (число) Повертає корінь квадратний із числа
ОКРУГЛ (число, коли­чество ЦИфр) Округляє число до зазначеної кількості десятко­вих знаків
ПИ() Повертає округлене до 15 знаків число п
ПРОИЗВЕД (<список аргументов>) Повертає добуток значень аргументів
РАДИАНЫ (угол) Перетворює радіани в градуси
СЛЧИС () Повертає випадкове число в інтервалі від 0 до 1
СУММ (число 1; число 2;... Підсумовує аргументи
ФАКТР (число) Повертає факторіал цілого числа, заданого як аргумент

Приклад. Знайти значення функцій у = sin#, у = 2sinx, у = sin2х: на ін­тервалі [0; 2,л] з кроком p /12. Округлити результати обчислень до 4 знаків. Використовуючи отримані обчислення, побудувати графіки функцій.

Порядок виконання

Побудуємо таку таблицю: у стовпці А обчислимо значення аргументу, а в стовпцях В, С і D знайдемо значення функцій у = sinx, у = 2sinх, у = sin2х: відповідно. У комірку A3 уведемо 0, у комірку А4 — формулу

=АЗ+ПИ()/12.

Установивши курсор у комірці А4, скопіюємо формулу в діапазон А5:А15. Визначимо значення функцій у = sinx. У комірку ВЗ уведемо формулу

=ОКРУГЛ(SIN(АЗ);4), комірку СЗ — формулу

=ОКРУГЛ(2* SIN(АЗ);4), комірку D3 — формулу

=OKPУГJI(SIN(2*(A3));4). Виділимо комірки B3:D3 і скопіюємо в діапазон комірок B4:D15. Таблиця значень заданих функцій готова. Виділивши отри­мані результати обчислень, побудуємо графіки функцій. На рисунку предста­влені результати виконаного завдання.

Висновки. Основною перевагою табличного процесора Excel є наявність потужного апарата формул і функцій.