«Прикладні програми (Електронні таблиці Excel)»



Скачати 334.76 Kb.
Дата конвертації13.04.2017
Розмір334.76 Kb.
Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Автор: доцент кафедри інформаційних в освіті Коношевський Л.Л.

Лекція 4. Робота з функціями і формулами (2 год.)



Технічне й програмне забезпечення лекції:


    • персональний комп’ютер;

    • операційна система Windows XP;

    • електронні таблиці Excel;

  • мультимедіапроектор;

  • проекційний екран.

План лекції

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

4.2. Поняття функції в MS Excel.
Література:


  1. Вейскорн Джен. Excel 2000. Базовый курс (русифицированная версия). – К.: М., Спб.: Век+Энтроп, Корона, 2000. 464 с.

  2. Информатика для юристов и экономистов /Симонович С.В. и др. – Спб: Питер, 2001. – 688 с.

  3. Коттингхем М. Excel 2000: руководство разработчика. – К.: Ирина; ВНУ, 2000. – 707 с.

  4. Николсон Д., Николсон С. Открой для себя Excel 97. – К.: Комиздат, Диалектика, 1987. – 416 с., ил.

  5. Экономическая информатика: /Под ред. П.В.Коноховского и Д.Н.Колесова. – Спб.: Питер., 2000. – 560 с.

  6. Николь Н., Альбрехт Р. Электронные таблицы Excel 5.0. – М.: Эком., 1995.

  7. Николь Н., Альбрехт Р. Электронные таблицы Excel 5.0 для квалифицирован-ного пользователя. – М.: Эком., 1995.

  8. Пасько В. Microsoft Office 97. – К.: BHV., 1998.

  9. Штайнер Й., Валентин Р. Excel 7 для Windows 95. Справочник. – М.: Бином, 1997.

  10. Іванов Є.О., Матвієнко В.Т., Попов Ю.Д.Основи роботи з системою Microsoft Excel. Учбовий посібник для студентів всіх факультетів Київ Видавничо-поліграфічний центр «Київський університет», 1999.


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

Обчислення

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

Формула може містити у собі операнди, оператори та функції. Як операнди можуть виступати константи різноманітних типів, посилання на клітинки або області таблиці, імена діапазонів.

Оператори



Excel розрізняє чотири типи операторів.

Арифметичні оператори

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



+, – додавання, віднімання,

*, /множення, ділення,

% – визначення значення відсотку,

^ – піднесення до степеня.

Оператори порівняння

Використовуються для порівняння значень і повертають як результат логічне значення ІСТИНА або ХИБНІСТЬ.



= – дорівнює,

<, > – менше, більше,

<= – менше або дорівнює,

>= – більше або дорівнює,

<> – не дорівнює.

Текстовий оператор

Використовується для з’єднання (злиття, конкатенації) текстових значень.



& – з’єднання текстових даних.

Адресні оператори

Використовуються для побудови діапазону значень, об’єднання та перетину діапазонів.



Оператор діапазону (двокрапка) : – повертає посилання на всі клітинки, розташовані між двома вказаними адресами клітинок (включаючи ці клітинки). Наприклад, =СУММ(B2:B5) буде обчислювати суму значень клітинок вказаного діапазону (B2,B3,B4,B5). Якщо у діапазон входить весь рядок або весь стовпчик, то задане посилання можна скоротити, наприклад:

A:A – весь стовпчик A,

1:1 – весь рядок 1,

1:3 – три перші послідовні рядки,

A:IV або 1:65536 – весь робочий аркуш.

Оператор об’єднання (крапка з комою) ; – повертає об’єднання вказаних діапазонів. Наприклад, =СУММ(A1;B2:B5) обчислює суму значень клітинок A1, B2,B3,B4,B5.

Оператор перетину (пропуск) – повертає клітинки, що входять у кожний із вказаних діапазонів. Наприклад, =СУММ(B2:D2 C1:D3) обчислює суму значень клітинок C2 та D2.

Порядок застосування операторів у формулі визначається, як звичайно, розставленими круглими дужками та пріоритетами операцій. Найвищий пріоритет мають оператори адресації (виконуються у першу чергу). За ними виконуються арифметичні операції у такому порядку: (знак від’ємного числа), %, ^, *, /, + і . Далі слідує оператор & злиття тексту. На останньому місці в ієрархії пріоритетів розташовані оператори порівняння. Як звичайно, оператори з однаковим рівнем пріоритету обробляються зліва направо. Для полегшення сприйняття та перевірки при переміщенні курсору усередині формули Excel виділяє відповідні дужки напівжирним накресленням.


Введення формули

Формули практично завжди містять посилання на клітинки, вміст яких використовується в обчисленнях. Для уведення формули:



  • Помістіть покажчик клітинки на клітинку, у якій ви хочете ввести формулу.

  • Вставте як перший символ знак рівності.

  • Введіть частину формули аж до першого посилання, як звичайний текст.

  • Задайте посилання на клітинку (або діапазон клітинок). Це можна зробити або уведенням з клавіатури, або за допомогою миші, клацнувши на потрібній клітинці. Для завдання діапазону можна виділити його за допомогою миші.

  • Після уведення іншої частини формули, завершіть уведення натисканням клавіші <Enter>.

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

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

У формулі можна використовувати імена клітинок і діапазонів клітинок. Список визначених у поточній робочій книзі імен можна відкрити, клацнувши на кнопці зі стрілкою у полі «Имя», яке розташоване зліва у рядку формул. Можна також натиснути клавішу <F5> та вибрати ім’я діапазону у вікні діалогу «Переход».
Пріоритет виконання операцій наступний:

NOT (логічне заперечення);

% (відсоток);

^ (піднесення до степеня);

*, / (множення, ділення), AND (логічне множення);

+, - (додавання, віднімання), OR (логічне додавання);

& (текстовий оператор);

< >, >, >=, <, <=, = (знаки відношень).

При створенні формул, щоб уникнути помилок, викликаних порядком виконання операцій, враховуйте наступне:

- Excel обчислює формули зліва направо.

- кожна формула повинна починатись зі знаку дорівнює.

- елементи в круглих дужках підраховуються в першу чергу.

- інші дії виконуються згідно вище наведеного переліку.

На екрані, як правило, висвітлюються не формули, a їx значення. При активізації конкретної комірки в рядку формул висвітиться формула. Для висвітлення формул в комірках потрібно виконати команду TOOLS (cepвic) => OPTIONS (опції), вибрати кладку VIEW (вигляд) i активізувати опцію FORMULAS (формула).

Заміна формул результатами

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

Для вказаної заміни виділіть потрібну клітинку, скопіюйте її вміст у буфер обміну та виконайте команду «Правка\Специальная вставка…». У вікні діалогу «Специальная вставка», що з’явиться, у групі «Вставить» встановіть перемикач у стан «значения» та закрийте вікно діалогу натисканням кнопки <OK>.

Щоб замінити частину формули результатом, виділіть потрібну частину формули у рядку формул і натисніть клавішу <F9> або сполучення клавіш <Ctrl+=>.


Режими обчислення формул

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



  • Виконайте команду «Сервис\Параметры…» та перейдіть у вікні діалогу, що відкрилося, на вкладку «Вычисления».

  • Активізуйте перемикач «вручную».

Тепер формули робочого аркуша будуть перераховані тільки після натискання клавіші <F9> або перед зберіганням робочої книги, якщо увімкнутий перемикач «пересчет перед сохранением».

Формули масиву



Excel дозволяє будувати формули, результатом обчислення яких є не одне скалярне значення, а цілий масив (сукупність) значень. Наприклад, у множину вбудованих функцій входять функції для роботи з матрицями: обчислення добутку матриць, оберненої матриці. Можна записати і свої власні формули, що застосовуються до діапазонів клітинок, результатом обчислення яких буде діапазон клітинок. Наприклад, =F4:F9–G4:G9.

Для уведення подібних формул:



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

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

  • Завершіть уведення формули натисканням сполучення клавіш <Ctrl+Shift+Enter>.

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

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

Типи адресації

В Excel розрізняють два типи адресації: абсолютну та відносну. Обидва типи можна застосовувати в одному посиланні та створити, таким чином, змішане посилання. Тип адресації аргументу, що застосовується у формулі, грає істотну роль при копіюванні або переміщенні формули. Наявність вказаних типів адресації створює прості та зручні можливості виконання “однотипних” (схожих) обчислень над різноманітними областями даних. Наприклад, для того щоб застосувати однотипну обробку для рядків (або стовпчиків) деякої таблиці, достатньо усього лише один раз, побудувавши потрібну формулу, поширити її шляхом копіювання на відповідні стовпчики (або рядки) таблиці. При цьому, звичайно, користувачу потрібно, щоб деякі аргументи, що задаються посиланнями, змінювалися, “підстроючись” під місце розташування скопійованої формули, а інші посилання, що вказують, наприклад, на деякі “постійні” коефіцієнти або константи зберігали адреси без змін.



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

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

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

При завданні посилання методом вказівки можна змінити тип посилання натисканням клавіші <F4>. Тип поточного посилання буде циклічно змінюватися при кожному натисканні клавіші <F4>.


Натискання

Адреса

Посилання

Один раз

$A$7

Абсолютне посилання

Два рази
A$7

Абсолютне посилання на рядок

Три рази

$A7

Абсолютне посилання на стовпчик

Чотири рази
A7

Відносне посилання

Тип посилання можна змінити і у готовій формулі. Для цього активізуйте натисканням клавіші <F2> режим правки вмісту клітинки, помістіть курсор уведення у потрібне посилання (адресу) та натисніть клавішу <F4>.

Абсолютне посилання може бути задане також шляхом уведення символу $ безпосередньо з клавіатури. Символ $ можна ввести з клавіатури і у режимі правки вмісту клітинки.

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

За умовчанням Excel використовує формат посилання A1: стовпчики робочого аркуша позначаються літерами, а рядки – цифрами. Можна змінити формат, що використовується, задаючи стовпчики своїми номерами. Для цього у вікні діалогу «Параметры», що викликається командою «Сервис\Параметры…», перейдіть на вкладку «Общие» та у групі «Параметры» встановіть прапорець «Стиль ссылок R1C1». При використанні цього формату, наприклад, виразу R2C3 (R – рядок, C – стовпчик) відповідає абсолютне посилання $B$3.

Для завдання відносного посилання у цьому форматі після R і C зазначте потрібну кількість рядків і стовпчиків у квадратних дужках (вони визначають розміри зсуву від поточної клітинки). При цьому позитивне значення задає посилання на клітинку, розташовану на вказану кількість рядків (стовпчиків) нижче (праворуч) клітинки, що містить посилання. Наприклад, R[2]C[3] – посилання на клітинку, яка розташована на два рядки нижче та на три стовпчика праворуч клітинки, в якій записана формула. Від’ємні значення задають посилання на клітинку, яка розташована на вказану кількість рядків (стовпчиків) вище (ліворуч) клітинки, що містить посилання. Наприклад, R[–2]C[–1] – посилання на клітинку, яка розташована на два рядки вище та на один стовпчик ліворуч клітинки, що містить посилання.

Обраний формат посилань дійсний для усіх робочих аркушів поточної робочої книги.

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

Для завдання посилання на клітинки іншого робочого аркуша поточної робочої книги простіше скористатися методом вказівки. Записавши частину формули аж до того місця, у якому повинне бути вказане посилання, виберіть потрібний робочий аркуш, клацнувши на його ярлику, та виділіть у аркуші потрібні клітинку або діапазон клітинок. Як звичайно, уведення усієї формули треба закінчити натисканням клавіші <Enter>. Excel сам підставить у необхідному вигляді посилання на клітинки іншого робочого аркуша. У формулі перед посиланням на клітинку буде відображене ім’я робочого аркуша, після якого вказаний знак оклику (наприклад, Лист2!$D$5). Задати посилання на клітинку іншого робочого аркуша можна також уведенням з клавіатури, проте цей спосіб частіше призводить до помилок. При завданні такого посилання шляхом уведення з клавіатури варто враховувати, що коли ім’я аркуша містить символи пропуску, то перед і після імені аркуша у посиланні потрібно вказати апостроф (). При завданні посилання методом вказівки Excel добавить апострофи, у разі потреби, автоматично.

При перейменуванні робочого аркуша його ім’я, що є складовою частиною посилання у формулі, автоматично змінюється. Переміщення клітинок, що впливають на інші робочі аркуші, призводить до автоматичного відновлення імені аркуша у посиланні формули. Видалення такого залежного аркуша призведе до виникнення помилки «#ССЫЛКА!».

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

Зовнішнє посилання може бути задане аналогічно, методом вказівки. Для цього необхідно відкрити обидві робочі книги та задати підходяще розташування їхніх вікон на екрані. Подальші дії не відрізняються від розглянутих вище. В отриманому у такий спосіб посиланні буде вказане ім’я робочої книги, ім’я робочого аркуша та адреса клітинки. Шлях, ім’я робочої книги та ім’я аркуша будуть взяті в одинарні лапки (апострофи), а ім’я робочої книги записане ще й у квадратних дужках. Після імені робочого аркуша у посилання вставляється знак оклику. Наприклад, ‘C:\EXCEL\EXAMPLES\[SKLAD.XLS]Продажі_98’!$B$2. Якщо залежна та вихідна робочі книги збережені в одній папці, вказівка шляху не обов’язкова. У випадку перейменування вихідної робочої книги необхідно відчинити залежну робочу книгу. Тільки у цьому випадку зовнішнє посилання буде автоматично поновлене. Можна видалити зовнішнє посилання, замінивши формулу або відповідну частину формули, що містить зовнішнє посилання, результатом її обчислення. При необхідності обновити існуючі зв’язки вручну можна скористатися вікном діалогу «Связи», що активізується командою «Правка\Связи…», у якому перераховані всі зв’язки поточної робочої книги. Зміна зв’язку може бути виконана за допомогою кнопок «Изменить…» та діалогового вікна «Изменить связи», що дозволяє задати інший шлях до документа, з яким встановлено зв’язок. Видалення залежної робочої книги призведе до виникнення помилки «#ССЫЛКА!».

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

Існує ще одна можливість використання об’ємних (тривимірних) посилань, що дозволяє обробляти за один раз декілька діапазонів різноманітних робочих аркушів. В об’ємному посиланні можна зазначити діапазон клітинок з однаковою адресою декількох суміжних аркушів поточної робочої книги. Задати об’ємне посилання простіше усього методом вказівки. Записавши частину формули аж до того місця, де повинне бути вказане об’ємне посилання, виділіть потрібні аркуші у робочій книзі. Після цього виділіть потрібний діапазон у аркуші. Завершити уведення формули треба, як звичайно, натисканням клавіші <Enter>. У формулі перед посиланням на діапазон клітинок буде представлене посилання на діапазон виділених робочих аркушів, після яких вказаний знак оклику. Наприклад, =СУММ(Лист1:Лист3!B5:F5). Можна задати об’ємне посилання і уведенням з клавіатури, проте цей шлях більш трудомісткий і тому може призвести до помилок. Об’ємні посилання не можуть бути вказані у формулах масиву та при застосуванні оператора перетину діапазонів.


Імена у формулах

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

Імена значно спрощують читання формул і контроль за даними. Імена повинні бути унікальними на рівні робочої книги (хоча можна обмежити сферу унікальності робочим аркушом), і їх можна використовувати у будь-якому робочому аркуші книги. Присвоювати імена можна і несуміжним діапазонам, а також клітинкам, що розташовані у діапазоні, якому вже надане ім’я.

Ім’я може мати довжину не більш 255 символів. Ім’я може починатися з літери, символу підкреслення (_) або оберненої похилої риски (\). В іменах можна використовувати також цифри, такі спеціальні символи, як крапка, символ підкреслення, обернена похила риска та знак питання. При цьому варто враховувати, що ім’я не повинно збігатися з форматом посилання (наприклад, A1 або W55) та містити пропуски. Малі та великі літери в іменах не розрізняються.


Створення імен діапазонів

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

Інший спосіб іменування діапазону: виділіть потрібний діапазон клітинок та виконайте команду «Вставка\Имя\Присвоить…», введіть відповідне ім’я у поле «Имя» вікна діалогу «Присвоение имени». Вказане вікно можна також активізувати сполученням клавіш <Ctrl+F3>.

У полі «Формула» буде представлене посилання на виділений діапазон клітинок. Кнопка «Добавить» дозволяє поповнити список імен. Задайте наступне ім’я та, помістивши курсор у поле «Формула», зазначте потрібний діапазон клітинок шляхом виділення за допомогою миші або введіть посилання з клавіатури. При необхідності можна також видалити непотрібне ім’я, вказавши його та скориставшись кнопкою «Удалить». Вказане вікно діалогу дозволяє також змінити ім’я або діапазон клітинок. Завершити роботу у вікні діалогу можна натисканням кнопок «ОК» або «Закрыть».

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

Текстові заголовки діапазонів можуть бути використані як імена, що значно спрощує роботу з таблицями. Виділивши діапазон клітинок, що містить і клітинки з іменами, виконайте «Вставка\Имя\Создать…», або скористайтесь сполученням клавіш <Ctrl+Shift+F3>. У вікні діалогу, що з’явилося, за допомогою параметрів зазначте, де у виділеному діапазоні розташовані клітинки, текст яких повинний використовуватися як імена.


Присвоєння імені формулі

При присвоєнні імені формулі сама формула не зберігається у клітинці робочого аркуша, але може бути використана у клітинках робочої книги. Виконавши команду «Вставка\Имя\Присвоить…», у вікні діалогу «Присвоение имени», що з’явилося, введіть ім’я для формули. Правила завдання імен для формул ті ж, що і для імен діапазонів клітинок. У полі «Формула» задайте формулу та натисніть кнопку «Добавить». Іменована формула може бути використана у робочій книзі будь-яке число раз. Для цього достатньо зазначити ім’я формули у потрібній клітинці. За допомогою цього способу можна також присвоїти імена визначеним константам і надалі включати їх у формули шляхом вказування відповідного імені.


Застосування імен

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

Ім’я можна вставити також, обравши його у вікні діалогу «Вставка имени», що активізується командою «Вставка\Имя\Вставить…» або клавішею <F3>. У цьому вікні діалогу можна вибрати зі списку також ім’я формули. Кнопка «Все имена» дозволяє помістити у робочий аркуш список усіх визначених імен разом із відповідними їм посиланнями.

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

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

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

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

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

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

При копіюванні формул працюють також команди «Копировать», «Вставить».

Помилки та їх пошук у формулах

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



Значення помилки

Причина

#Дел/0!

Задане ділення на 0

#Знач!

Вказаний невірний аргумент або оператор

#Имя?

Вказане неприпустиме ім’я

#Н/Д

Значення відсутнє

#ПУСТО!

Задана область перетину двох діапазонів, що не перетинаються

#ССЫЛКА!

Вказане некоректне посилання

#ЧИСЛО!

Помилка при використанні або одержанні числа

Формула, що містить посилання на клітинку зі значенням помилки, також повертає значення помилки.

Для пошуку помилок Excel надає у розпорядження користувача можливості для відслідковування залежності, шляхом графічного представлення зв’язку між впливаючими та залежними клітинками. Стрілки у робочому аркуші, проте, будуть відображені тільки у випадку, якщо включений параметр «отображать» у полі «Объекты» на вкладці «Вид» вікна діалогу «Параметры» (викликається командою «Сервис\Параметры»).

Відслідковувати залежності зручно за допомогою панелі інструментів «Зависимости», що містить кнопки для активізації відповідних команд. Задати відображення вказаної панелі на екрані можна командою «Сервис\Зависимости\
Панель зависимостей
», або вибрати ім’я панелі з контекстного меню будь-якої відображеної панелі інструментів, або скористатися знайомою командою «Вид\Панели инструментов». У двох останніх випадках треба ще виконати проміжну команду «Настройка…».

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



  • Помістіть покажчик на клітинку, для якої необхідно визначити впливаючі клітинки.

  • Клацніть на кнопці «Влияющие ячейки» на панелі інструментів «Зависимости». Будуть відображені стрілки тільки від тих клітинок, які безпосередньо впливають на активну клітинку.

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

  • Щоб видалити стрілки до впливаючих клітинок нижчого рівня, клацніть на кнопці «Убрать стрелки к влияющим ячейкам».

  • Щоб прибрати всі відображені стрілки відразу, клацніть на кнопці – «Убрать все стрелки».

Для пошуку залежних клітинок, тобто таких, формули в яких прямо або побічно посилаються на активну клітинку, можна скористатися аналогічним способом. На панелі інструментів «Зависимости» кнопки призначені для відображення та видалення стрілок від активної клітинки до залежних клітинок.

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



  • при зберіганні або закритті робочої книги;

  • при внесенні змін у структуру робочого аркуша, наприклад, при додаванні або видаленні рядків та (або) стовпчиків;

  • при зміні формули.

Позначити стрілкою джерело помилки можна тільки у тому випадку, якщо формула в активній клітинці повертає значення помилки. Для цього виділіть клітинку зі значенням помилки та натисніть кнопку – «Источник ошибки». Механізм відслідкування залежностей позначить червоною стрілкою формулу, що стала причиною повернення значення помилки. Синіми стрілками буде позначений шлях до формули, що породила помилку, від інших впливаючих клітинок.

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

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


4.2. Поняття функції в MS Excel
Використання функцій. Для запису у чарунку функцій використовують клавішу майстер функцій (вона позначається на палітрі інструментів) або можна виконати команду INSERT (вставка) => FUNCNION (функція). З’являється діалогове вікно майстра функцій. Ліворуч знаходимо категорію функції. Праворуч можна вибрати потрібну функцію. В залежності від вибраної функції з’явиться наступне діалогове вікно, в якому потрібно ввести аргументи функції i клацнути мишкою на ОК. Функція буде введена в активну чарунку таблиці.

Використання функцій у формулах

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

Звертання до функції завжди складається з імені функції, круглих дужок і, як правило, аргументів функції. Навіть якщо для функції не потрібно вказувати аргументи, круглі дужки все рівно повинні бути присутніми. Наприклад, функція СЕГОДНЯ() повертає значення поточної дати, а функція ТДАТА() – поточну дату та час у числовому форматі. Аргументи функції, що заключаються у круглі дужки, розділяються крапкою з комою (;).

Функцію можна ввести звичайним шляхом, як і будь-який вміст клітинки, з клавіатури. Регістр (Shift) при уведенні функції не враховується. Ім’я функції автоматично буде записане великими літерами. Але звичайно набагато зручніше для вставки функції скористатися майстром функцій.

Поряд із вбудованими в Excel функціями можна створювати також власні функції користувача. Деякі функції, наприклад, інженерні, доступні тільки після встановлення прапорця у пункті «Пакет анализа» у вікні «Надстройки», що викликається за допомогою команди «Сервис\Надстройки…».

Для простоти користування бібліотекою вбудованих функцій та майстром функцій, який спрощує роботу з бібліотекою, усі наявні функції розбиті на тематичні групи – категорії. Наприклад, є категорії фінансових, статистичних, математичних, текстових функцій, а також функцій дат і часу. Категорія «Полный алфавитный перечень» містить усі функції, впорядковані за їх іменами. Категорія «10 недавно использовавшихся» дозволяє спростити доступ до найбільш уживаних вами функцій.

Для вставки функції за допомогою майстра функцій:


  • Помістіть покажчик клітинки на потрібну клітинку. Функція завжди буде вставлена у поточну клітинку.

  • Виконайте команду «Вставка\Функция…», або скористайтеся кнопкою –«Вставка функции» панелі інструментів «Стандартная», або натисніть комбінацію клавіш <Shift+F3>. На екран буде виведене перше вікно майстра функцій.

  • У списку «Категория» виберіть потрібну категорію функцій.

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

  • Натисніть кнопку «ОК». У другому вікні майстра функцій варто зазначити необхідні аргументи для функції.

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

  • Натисніть кнопку «ОК». Excel вставить функцію у поточну клітинку. У самій клітинці буде відображений результат обчислення функції.

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

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

За допомогою майстра функцій можна також вкласти функції одна в одну, тобто використовувати як аргумент даної функції іншу функцію. Для цього при введенні аргументу зовнішної функції клацніть на кнопці – «Изменить формулу» у рядку формул, а потім на кнопці – «Вставка функции» панелі інструментів «Стандартная» для запуску майстра функцій. У такий спосіб можна записати формулу, що містить до семи рівнів вкладеності функцій.

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

Для уведення функції, що дозволяє швидко визначити суму вмісту діапазону клітинок, є спеціальна кнопка – «Автосумма» на панелі інструментів «Стандартная». Excel вставить функцію суми та запропонує діапазон клітинок, відмічений у робочому аркуші біжучою рамочкою. При необхідності, змініть запропонований діапазон, перетягуючи покажчик миші при натиснутій лівій кнопці. Завершити уведення формули можна натисканням клавіші <Enter>.

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



  • Виділіть клітинку з функцією, що необхідно змінити.

  • Запустіть майстер функцій. Excel відобразить на екрані друге вікно майстра функцій, що дозволить змінити аргументи функції.

  • Виконайте необхідні зміни.

  • Натисніть кнопку «ОК» або клавішу <Enter>.

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

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



Як уже відзначалося, за умовчанням у клітинках робочого аркуша відображаються результати обчислення формул. Щоб задати відображення у клітинках робочого аркуша самих формул замість результатів, встановіть прапорець параметра «Формулы» на вкладці «Вид» вікна діалогу «Параметры», що викликається командою «Сервис\Параметры…». Задане значення параметра дійсне тільки для поточного робочого аркуша. Повернутися до відображення результатів можна, скинувши вказаний прапорець.

Арифметичні та тригонометричні функції. ABS(X) - обчислюється абсолютне значення (модуль) X; ЕХР(Х) - обчислюється е; INT(X) - обчислюється ціла частина числа, наприклад, INT(3.7) =3; LN(X) - обчислюється натуральний логарифм числа X; LOG(X,A) - обчислюється логарифм числа Х при основі А, наприклад, LOG(X,10) - обчислюється десятковий логарифм числа X; COS(X) - обчислюється COSX; ACOS(X) - обчислюється ARCCOSX; ACOSH(X) - обчислюється гіперболічний ARCCOSX; SIN(X) - обчислюється SINX; ASIN(X) - обчислюється ARCSINX; ASINH(X) - обчислюється гіперболічний ARCSINX; ATAN(X) - обчислюється ARCTGX; ATAN2(X,Y) - обчислюється ARCTG(Y/X); ATANH(X) - обчислюється гіперболічний ARCTGX;

EVENT(X) (окргл(х)) -число Х заокруглюється до найближчого більшого парного цілого числа; MDETERM (МАТРИЦЯ) - обчислюється детермінант матриці; матриця повинна бути квадратною, елементи матриці задаються по рядках i відокремлюється комою. Рядок від рядка відокремлюється символом ";" , а вся матриця береться в фігурні дужки. Наприклад, MDETERM({3,6;1,1}) = -3; MINVERSE (МАТРИЦЯ) - обчислюється обер-нена матриця, наприклад, MINVERSE({4,-1; 2,0}) ={0, 0.5; -1,2}; MMU( МАТРИЦЯ1, МАТРИЦЯ2) - обчислюється добуток двох матриць, якщо число стовпців nepшої матриці дорівнює числу рядків другої матриці, наприклад, MMU({1,3; 7,2}, {2,0;0,2}) = (2,6;14,4); MOD(X;Y) - обчислюється ціла частина від ділення цілого числа Х на ціле число Y, наприклад, MOD(5;2) =2; ODD(X) - знаходить найближче непарне число, наприклад, ODD(1.5) = 3; PI() - видає значення константи ? зі 15 знаками після коми; може використовуватися в формулах, наприклад, = PI()*6; FACT(N) (фактр(х))- обчислює значення добутку N цілих значень, наприклад, FACT(4)=24; POWER(X;Y) (корень(х,у)) число Х підноситься до степеня Y; PRODUCT(X1;X2; ...;XN) - аргументом даної функції можуть бути або числа або діапазон чарунок i відбувається множення вказаних чисел або множаться числа вказаного діапазону, наприклад, PRODUCT(A2:C2)=2250, якщо А2=5, В2=15, С2=30; RADIANS(X) переводить число Х градуса у радіани; SUМ(ЧИСЛ01; ЧИСЛ02; ...;ЧИCJION) - обчислює суму чисел, записаних в чарунках або діапазоні чарунок. SUMIF (range; criteria; sum_range), де range интервал або імена чарунок , які перевіряємо, criteria - критерій, що задає умову, яка виконується в кожній чарунці цього діапазону, sum_range - задає діапазон або імена чарунок, по яких відбувається сумування. Ця функція видає суму значень з sum_range, для яких range відповідає даному критерію. Наприклад, у стовпці А напишемо імена місяців i присвоїмо йому ім’я - місяць, у стовпці В - кількість проданого товару у цих місяцях i присвоїмо йому ім’я - продаж. У чарунку, де має бути результат після знаку = клацнемо на клавіші f i виберемо функцію SUMIF.

У вікні, що відкриється клацаємо у рядку RANGE (діапазон). Позначаємо стовпець А мишкою, у цьому рядку висвітиться назва місяць. У рядку CRITERIA (критерій) вводимо з клавіатури критерій у лапках, наприклад "лютий". У рядку SUM_RANGE (сума діапазону) позначимо стовпець В i висвітиться назва стовпця продаж, тобто SUMIF(МІСЯЦЬ; "ЛЮТИЙ"; ПРОДАЖ). Можна те саме записати з допомогою дiaпaзoнy чарунок SUMIF (А1:А12;"ЛЮТИЙ";В1:В12). У цьому ж вікні висвітлюється i результат, а якщо клацнути на ОК, то результат отримаємо в активній чарунці; TRANC(X) - заокруглює дійсне число X.



Логічні функції Функція AND( Al; A2;.. .;AN) - приймає значения TRUE (істина), якщо вci лопчні вирази Al, A2,.. .,AN істинні. Наприклад, AND(1 <В4;В4<100) приймає значення TRUE, якщо чарунка В4 містить числа менші 100 i більші 1. Функція NOT(A) - приймає значення TRUE, якщо логічний вираз А приймає значення FALSE i навпаки. Функція OR(A1;A2; ...;AN) - приймає значення TRUE, якщо хоч би один з логічних виразів А1,А2, ...AN приймає значення TRUE, наприклад, OR( 1+1=1; 2+2=5) приймає значення FALSE.

IF(A; Sl; S2) - умовний оператор. Якщо логічний вираз А приймає значення TRUE, тоді функція прийме значення Sl, якщо А приймає значення FALSE - то вираз S2. Наприклад, 1) IF( A4=50; SUM(B2:D2); " "). Якщо чарунка А4 має значення 50, тоді в активну чарунку буде записана сума значень чарунок В2, С2, D2, в іншому випадку ця чарунка буде пустою;

2) IF( D2>E2; "рентабельно"; "не рентабельно"). Якщо вміст чарунки D2 більше ніж Е2, то в активну чарунку буде записано слово рентабельно в іншому випадку - не рентабельно.

3) IF (SUM(A1 :A10)>0; SUM(A1:A10);0). Якщо SUM(A1:A10) більше нуля, то в активну чарунку буде занесена ця сума, в іншому випадку - нуль.

Статистичні функції. Функція AVERAGE(X1;X2;...;XN) - обчислює середнє значення величин, які є аргументом цієї функції. Аргументом можуть бути числа, адреси чарунок, діапазон чарунок. Функція С0UNT(значення1;значення2;...) - обчислює число чарунок, які містять числові значення. Функція МАХ (число1; число2; ...) - обчислює найбільше значення із вказаного списку, MIN (число1; число2;...) - обчислює найменше значення із списку, VAR (число1;-число2;...) - обчислює дисперсію, MODE( число1;число2; ...) - знаходить значення, які найчастіше зустрічаються. Аргументами цих функцій можуть бути числа, адреси чарунок, діапазон чарунок. Наприклад, результатом =MODE (1;4;4;7) буде значення 4.

Приклади використання деяких функцій статистичного аналізу. Excel пропонує широкий діапазон засобів для аналізу статистичних даних. Такі вбудовані функції, як AVERAGE (СРЗНАЧ), MEDIAN (МЕДИАНА), MODE (МОДА), можуть бути корисні для проведення нескладного аналізу даних. Якщо вбудованих статистичних функцій недостатньо, то можна звернутися до пакету aнaлiзy. Пакет аналізу можна використовувати для створення піктограм, утворення випадкових або періодичних вибірок з набору даних, проведення регресійного аналізу, одержання основних статистичних характеристик для вибірки, генерації випадкових чисел з різним розподілом, а також застосувати до cвoїx даних перетворення Фур’є та інші перетворення.

Щоб одержати доступ до пакету аналізу потрібно виконати команду TOOLS (cepвic) => DATA ANALYSIS (аналіз даних). Якщо команда аналіз даних відсутній, то виконуємо команду TOOLS (cepвic) => ADD-INS (налаштування) i aктивізуємо опцію ANASLYSIS TOOLPAK-VBA (пакет аналізу -VBA). Якщо ця опція відсутня, то її потрібно доінсталювати, використовуючи, MICROSOFT EXCEL SETUP.



Генерація випадкових чисел. Вбудована функція RAND (СЛЧИС) генерує випадкові числа, які мають рівномірний розподіл в інтервалі між O i l. іншими словами всі значения між 0 i 1 мають однакову ймовірність появи. Пакет аналізу дозволяє генерувати випадкові числа з розподілами відмінними від piвномipнoro. Такі множини випадкових чисел використовуються в методі Монте - Карло i при інших типах моделювання. Є шість типів розподілу UNIFORM (р;вномірний), NORMAL (нормальний), BERNOULLI (Бернуллі), BINOMIAL (біномінальний), POISSON (Пуассона), DISCRETE (дискретний, який визначається користувачем). Крім того, можна використати PATTERNED (модельний), розподіл для одержання невипадкових чисел у заданих інтервалах. Модельний розподіл може служити в якості альтернативи команді EDIT (редагування) => FILL (заповнення) => SERIES (прогресія). Для генерації випадкових чисел виконуємо команду TOOLS (cepвic) => DATA ANALYSIS (аналіз даних) i вибираємо команду RANDOM NUMBER GENERATION (reнерація випадкових чисел) i клацаємо на ОК. З’явиться вікно, вид якого залежить від вибору типу розподілу, вибраного у рядку DISTRIBUTION (розподіл).

Інструмент DESCRIPTIVE STATISTIES (описова статистика). Інструмент описова статистика створює таблицю основних статистичних характеристик для однієї або декількох множин вхідних значень. Результатом роботи цього інструменту є таблиця, в якій обчислені MEAN - середнє арифметичне, яке відповідає функції AVERAGE(NUMBER1;...; NUMBERN) (СРЗНАЧ(ЧИСЛ01;...;ЧИСЛОN), STANDARD ERROR -стандартна помилка, аналогічно функції STEYX(KNOWN_Y’S; KNOWN_X’S) (CTOШУХ(відоме_значення_У; відоме_значення_У)) - обчислює середнє арифметичне, але без врахування нормальності початкових даних; MEDIAN медіана, аналогічна функцї MEDIAN (NUMBER1;...; NUMBERN) (МЕДИАНА-(ЧИСЛ01;...;ЧИСЛОN) - обчислює медіану чисел; MODE мода, аналогічна функції MODE(NUMBER1;...;NUMBERN) MOДА(ЧИСЛ01;...;ЧИСЛОN) - обчислює значення, яке частіше інших зустрічається в множині чисел; STANDARD DEVTVATION - стандартне відхилення, аналогічна функції STDEV(NUMBER1;...; NUMBERN) (СТАНДОТК-ЛОН(ЧИСЛ01 ; ...;ЧИСЛОN)) - оцінює розсіювання випадкової величини; SAMPLE VARIANCE - дисперсія вибірки, аналогічна функції VAR(NUMBER1;...; NUMBERN)ДИCП(ЧИCЛ01;...;ЧИCЛON)),KURTOSIS - ексцес, аналогічна функції KURT(NUMBER1;...; NUMBERN) (ЭКСЦЕСС-(ЧИСЛ01;...;ЧИСЛON)) - обчислює ексцес множини даних; SKEWNESS - коефіцієнт асиметрії, аналогічна функції SKEV(NUMBER1;...; NUM-BERN)CKOC (ЧИСЛ01;...;ЧИСЛОN) - обчислює асиметрію розподілу; RANGE - інтервал, обчислює різницю між. максимальним i мінімальним значеннями; MINIMUM- мінімум, аналогічна функції MIN(NUMBER1;...;NUMBERN)(MИH(ЧИСЛ01;...;ЧИСЛON)) - обчислює мінімальне значення, MAXIMUM - максимум, аналогічна функції MAX(NUMBER1;...; NUMBERN) МАКС(ЧИСЛ01;...;ЧИСЛОN) - обчислює максимальне значення, SUM - обчислює суму, аналогічна функції SUM(NUMBER1;...;NUMBERN)(CУMM(ЧИСЛ01;...;ЧИСЛON)) - обчислює суму значень; COUNT - рахунок, аналогічна функції COUNT(NUMBER1;...; NUMBERN)СЧЕТ(ЧИСЛ01;...;ЧИСЛОN) -обчислює кількість чисел у списку аргументів; LARGEST - найбільший, аналогічна функції LARGE (АРРАУ;К)(НАИБОЛЬШИЙ(МАССИВ;К)) - обчислює К-те найбільше значення з масиву даних; SMALLEST найменший , аналогічна функції SMALL (ARRAY;K) (НАИМЕНЬШИЙ(МАССИВ;К)) - обчислює найменше К-те значення з масиву даних. Для використання інструменту описова статистика виконуємо команду TOOLS (cepвic) =>DATA ANALYSIS (аналіз даних), a пoтім вибираємо команду DESCRIPTIVE STATISTIES (описова статистика). Клацаємо на ОК. з’явиться вiкно DESCRIPTIVE STATISTIES (описова статистика).

У рядку INPUT RANGE (вхідний інтервал) вказуємо інтервал, у якому розташовані вхідні дані. з допомогою. опції GROUPED BY (групувати по) вказуємо, де розташовані дані в COLUMNS (стовпець) або ROWS (рядок). Активізуємо опцію LABELS IN FIRST ROW (позначка в першому рядку), якщо перший рядок у вхідному діапазоні містить назви стовпців i EXCEL використовує ix для створення заголовків у вихідній таблиці. Поле OUTPUT RANGE (вихідний інтервал) використовується для того, щоб вказати місце розташування випадкових чисел. Якщо активізувати опцію NEW WORKSHEET PLY (новий аркуш), то числа розташовуються на новому аркуші. А якщо активізована опція NEW WOORKBOOK (нова робоча книга), то результати виведуться в іншому файлі. Якщо активна опція SUMMARY STATISTICS (підсумкова статистика), то висвітиться повний список характеристик. 3 допомогою опцій К-ТН LANGEST (К-тий найбільший) i K-TH SMALLEST (К-тий найменший) вибираємо найбільший елемент, на к-тій позиції, при умові що масив у пам’яті посортований у порядку спадання i найменший елемент на к-тій позиції, якщо масив у пам’яті посортований по зростанню. Якщо К = 1, то її значення співпадають з відповідними значеннями наибільшого i найменшого елементів позначеного діапазону. Клацаємо на ОК. інструмент описова статистика створює таблицю констант.



Інструмент Гістограми. Гістограма - це діаграма, де для початкової множини значень визначається i висвітлюється число значень (частот (FREQUENCY)), які попадають в інтервали розбиття (кишені (BINS)). Числа в діапазоні чарунок повинні бути посортовані по зростанню. Числа можна отримати, виконавши команду EDIT (редагування) => FILL (заповнити) => SERIES (прогресія) або виконавши команду TOOLS (cepвic) DATA ANALYSIS (аналіз даних) i вибираємо команду RANDOM NUMBER GENERATION (генерування випадкових чисел). Тоді виконуємо команду ТООLS (сервіс) => DATA ANALYSIS (аналіз даних) i вибираємо команду HISTOGRAM (гістограма). 3’являється діалогове вікно вигляду:

У рядку INPUT RANGE (вхідний інтервал) задаємо діапазон чарунок числами, рядок BIN RANGE (інтервал кишень) можна залишити пустим. У рядку OUTPUT RANGE (вихідний діапазон) задаємо чарунку з якої починаємо побудову гістограми. Активізуємо опцію CHART OUTPUT (виведення графіка) i клацаємо на ОК. Якщо активна опція PARETO (SORTED HISTOGRAM) (Парето (посортована гістограма)), то сортуються вихідні дані в порядку спадання. Активна опція CUMULATIVE PERCENTAGE (інтегральний відсоток) дає можливість включити в таблицю частоти в відсотках для кожного інтервалу гістограми.



Piвномірний розподіл. Виконуємо команду TOOLS (cepвic) => DATA ANALYSIS (аналіз даних) i вибираємо команду RANDOM NUMBER GENERATION (генерація випадкових чисел). У рядку DISTRIBUTION (розподіл) вибираємо UNIFORM (рівномірний) i тим самим генеруємо множину дійсних чисел, piвномірно розподілених у вказаному інтервалі, який задається початковим i кінцевим значенням. Цей інструмент можна використовувати як більш зручну альтернативу функції RAND (СЛЧИС), якщо потрібний інтервал відмінний від [0,1]. А потім з допомогою інструменту Гістограма можна побудувати графік цього розподілу.

Функції дати i часу. Функція TODAY() (СЕГОДНЯ()) результатом, якoї буде десяткове значення поточної дати. Наприклад, у А1 встановимо формат dd.mm.yy i введемо дату у вигляді 05.12.93. У чарунку В1 введемо формулу = TODAY()-А1. Результатом буде число днів ніж поточною датою i датою вказаною в чарунці А1. Для зручності можемо в чарунці В1 встановити формат YY, який дасть можливість висвітлити різницю в роках.

Контрольні питання


  1. Що таке формула?

  2. Які типи операндів і які операції допустимі у формулі?

  3. Які типи посилань на комірки допустимі у формулах?

  4. Які переваги дає використання імен діапазонів у формулах?

  5. Яким чином можна присвоїти ім’я діапазону комірок?

  6. Якими способами можна вставити функцію у формулу?

  7. Яким чином здійснюються обчислення формул?

  8. Які типи помилок можливі в процесі обчислення формул?


База даних захищена авторським правом ©lecture.in.ua 2016
звернутися до адміністрації

    Головна сторінка