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

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

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

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

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

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

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

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

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

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

Поняття функції  в 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.