Робота з функціями і формулами

Теорія для  лабораторної роботи №2 Exel. «Робота з функціями і формулами»

 

 

Поняття формули  в 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>.

 

Натискання <F4>	Адреса	Посилання
Один раз	$A$7	Абсолютне посилання
Два рази	A$7	Абсолютне посилання на рядок
Три рази	$A7	Абсолютне посилання  на стовпчик
Чотири рази	A7	Відносне посилання

 

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