Визначення строку платежів

Визначення відсоткової ставки

Вставка проміжних підсумків

Діагностика помилок у формулах

Смарт-тег перевірки помилок

Панель аудиту формул

Вікно контрольного значення

Висновок

У Excel маються чотири види операторів: арифметичні, текстові, оператори порівняння й оператори посилань (адресні оператори).

Операції над числами виконуються за допомогою арифметичних операторів. У Excel використовуються наступні арифметичні оператори:

При роботі з числами і текстом застосовуються ще оператори порівняння. До них відносяться наступні оператори:

Таблиця: Приклади використання арифметичних операторів

Ці оператори використовуються для присвоєння твердженням значень ІСТИНА або НЕПРАВДА. Оператори порівняння можуть застосовуватися для порівняння тексту, записаного в різні комірки. При цьому потрібно враховувати, що оператор «=» застосовується в змісті збігу чи розбіжності текстів, а оператори «<» і «>» - у змісті розташування «до» чи «після» в упорядкованому списку текстових фрагментів.

У Excel є один текстовий оператор:

&. (амперсанд) - Об'єднання послідовностей символів в одну послідовність символів.

Цей оператор застосовується для з'єднання текстів з різних комірок.

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

Синтаксис формул

=СУММ(Е:е) -- визначається сума числових значень, що містяться в стовпці Е;

=СУММ(Е2:с5) -- підраховується сума числових значень, розташованих в діапазоні від Е2 до G5;

=СУММ(5:5) -- підсумовуються дані всіх вічок п'ятого рядка;

=СУММ(2;4) -- підраховується сума 2+4.

Допускається змішаний запис адрес вічок і блоків вічок. В цьому випадку формула може виглядати таким чином:

=СУММ(С8;О4;Е2:Е5;Р5)

Для завдання звернення до всіх вічок, що знаходяться в декількох діапазонах одночасно використовується пересічення. Для завдання пересічення діапазонів використовується пропуск. Наприклад, формула =СУММ(А2:вз В2:сз) обчислює суму чисел в пересічних діапазонах А2:вз і В2:сз. Для звернення до всіх вічок, включених в два діапазони, використовується об'єднання. Якщо у вічку ВЗ записане Петров, а вічку A3 -- Володимир, то формула Аз= вз&", "&АЗ означатиме об'єднання вказаних вище імен, розділених комою (Петров, Володимир). При використанні операторів об'єднання і пересічення зручно користуватися іменованими діапазонами.

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

Відносні й абсолютні посилання

Усі види посилань, що розглядалися, були відносними посиланнями. Цей вид посилань встановлюється в Excel за замовчуванням, і відносність їх виявляється, приміром, у наступному. Якщо записати в комірку A3 формулу =А1+А2 і скопіювали цю формулу в комірку В5, то в підсумку отримається формула =В3+В4. При відносних посиланнях дана вихідна формула представляється як деяка дія, відповідно до якого потрібно обчислити суму двох вищестоящих комірок.

Очевидно, що можливі випадки, коли зміна адрес у посиланнях на деякі комірки небажана, наприклад, якщо в комірки записані оригінальні дані. Тоді встановлюють абсолютні посилання, що не змінюються при копіюванні формули. Абсолютні посилання відрізняються від відносних наявністю знака долара $. Наприклад, розглянута попередня формула, записана в абсолютних посиланнях, буде виглядати як =$А$1+$А$2, і ця формула не буде змінюватися при копіюванні або переміщенні в будь-який осередок.

У розрахунках можна також використовувати абсолютні посилання тільки на рядок або тільки на стовпець. Тоді посилання буде виглядати, приміром, як А$1 чи як $А1 відповідно. Це так званні змішані посилання, що дозволяють "зафіксувати" рядок або стовпець.

Щоб змінити відносне посилання на абсолютне, не обов'язково вручну розставляти знаки згаданої грошової одиниці. Існує простий спосіб зміни посилань за допомогою клавіші F4, що діє як чотирикроковий перемикач. Коли вводиться абсолютне посилання у формулу, необхідно активізувати потрібну комірку, а потім за допомогою послідовних натискань F4 вибрати форму запису посилання з ряду типу А1 - $А$1 - А$1 - $А1.

Наприклад, нам треба підрахувати суму чисел, що зберігаються у вічках Al, A2, A3. Клацнемо двічі вічко А5, і помістимо в неї формулу= А1+А2+АЗ.Аби обчислити суму чисел, що зберігаються у вічках Cl, C2, СЗ, можна не писати заново формулу, а скопіювати її з вічка А5 в G5 (див. главу 14, розділ «Копіювання і переміщення вмісту вічок»). Excel автоматично змінить відносні заслання і формула набере вигляду =С1+С2+СЗ. Змінену формулу можна побачити, клацнувши вічко С5. (Для відображення формул у вікні додатка встановите прапорець формули на вкладці Вигляд діалогового вікна Параметри (мал.).)

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

Мал. Відображення формул з абсолютними, і відносними засланнями у вікні Excel

Функції

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

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

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

МАКС(число!,число2,...)

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

Дужки, що обмежують список аргументів, завжди повинні бути парними; пропуски перед дужками або після них не допускаються. Список аргументів може складатися з чисел, посилань, тексту, масивів, логічних величин (ІСТИНА чи НЕПРАВДА), значеннь помилок (наприклад, #ДЕЛ/0). Окремі аргументи в списку повинні бути розділені комами. Деякі функції можуть мати до 30 аргументів.

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

СУМ(А1:С5,МОПРЕД(О1:Е3))

використано вкладену функцію МОПРЕД, що повертає значення визначника матриці осередків О1:E3. Рівень вкладеності функцій у формулах Excel не повинний перевищувати семи.

Введення функцій

Функції вставляються у формули, а останні починаються зі знаку =. Якщо формулу почати з назви функції, то знак рівності буде підставлений автоматично. Функція включається у формулу за допомогою Майстра функцій, що спрощує процес вставки функції в рядок формул. Викликається Майстер через меню Вставка > Функция.

Діалог Мастер функций містить два списки: Категория і Функция.

У першому списку потрібно вибрати категорію, у якій знаходиться потрібна функція, а в другому - саму функцію. Усього в наборі Excel є 10 категорій функцій: фінансова, інженерні, дата і час, математичні, статистичні, посилання і масиви, робота з базою даних, текстові, логічні, перевірка властивостей і значень. Крім того, список категорій доповнений рядком 10 недавно использовавшихся, котрий містить список функцій, які найчастіше використовувалися останнім часом. Всього вбудованих функцій, що роблять різні типи обчислень, нараховується більше 200.

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

Вибравши потрібну функцію, слід клацнути по кнопці ОК, після чого відкриється наступний діалог Майстра функцій, що називаний панелью формул. Іншим способом виклику панели формул є клацання по кнопці «=» у рядку формул і вибір потрібної функції зі списку в лівій частині рядка формул.

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

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

Математичні функції. Особливості використання математичних функцій

В Excel є наступні типи функцій:

· · фінансові;

· · математичні (включаючи функції матриць і тригонометричні функції);

· · текстові;

· · логічні;

· · інформаційні;

· · командні.

· дата і часу.

· статистичні.

· заслання і масиви.

· робота з базою даних.

· перевірка властивостей і значень.

Функції - це готові формули. Вони виконують заздалегідь визначені операції, які зазвичай вводяться до складу розрахунків, але можуть також виконувати і неарифметичні операції. Функції завжди вертають результат чарунки, в яких вони введені. Наприклад, функція СУММ вертає значення арифметичної суми чисел, а функція ПРОПИСН робить всі букви в тексті прописними.

Функції пропонують більш швидкий спосіб вирішення багатьох задач. Наприклад, використання функції СУММ(ссылка) дозволяє швидко додати значення кожної окремої чарунки, якщо в якості аргументу вказана посилання на велику область чарунок. В потрібних випадках функції пропонують єдиний можливий спосіб вирішення багатьох задач. Наприклад, використання функцій СЕГОДНЯ и ТДАТА - єдиний спосіб звернутися з Excel до годинника-календаря вашого комп'ютера.

Для виконання конкретних операцій більшість функцій потребують специфічної інформації (наприклад, посилання функції в функції СУММ (ссылка)). Така інформація називається аргументами функції. Кількість аргументів в функціях відрізняються, а довжина усіх аргументів функцій обмежена 255 символами, включаючи лапки («»).

Аргумент масив - це множина значень, які записуються особливим образом. Елементи масиву заключаються у фігурні дужки {}, а строки у запису масиву розділяються крапками з комою (;). Наприклад, {5, 6, 7; 3,4,5; 1, 2, 3} - це масив, який має три рядки та три стовпчики. Масив як аргумент може бути введений безпосередньо в формулу, бути результатом формули, яка повертає масив, або адресою, або ім'ям діапазону чарунок, яки має масив або формулу, яка повертає масив.

Одними функціями, які найчастіше використовуються, є математичні.

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

Числа, що використовуються як аргументи в функціях SIN, COS і TAN, повинні бути виражені в радіанах, для того щоб перевести градуси в радіани, помножте значення градусів на pi/180. Кути, які повертаються функціями ACOS, ASIN, ATAN і ATAN2, також виражені в радіанах. Для того щоб перевести радіани в градуси помножте радіани на 180/pi.

Приклад. Для того щоб розрахувати довжину підпірки для підтримки 150-метрової антени прикріпленої одним кінцем до антени, необхідну, щоб підпірка утворювала до поверхні землі кут в 55о, використовуйте функцію SIN: =150/SIN (55*pi/180), яка повертає 183,12 м.

Практична вправа

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

· виділите вічка А1,в1,с1ів1і виберіть в меню Формат команду Вічка. Відкрийте вкладку Вирівнювання і поставте прапорець Об'єднання вічок (див. мал. 15.1);

· введіть у вічко А1 текст: «Продаж комп'ютерів»;

· виберіть в меню Формат команду Рядок, Автопідбір висоти, аби був видний заголовок, написаний крупнішим шрифтом;

· виділите текст і, розкривши список Розмір на панелі форматування, збільште розмір шрифту до 20 пунктів;

· натискуйте кнопку Ж на панелі форматування, аби заголовок був написаний напівжирним шрифтом;

· збільште ширину стовпців А, В, З і D, вибравши в меню Формат команду Стовпець, Автопідбір ширини аби була видно весь текст в стовпцях;

· введіть у вічко А2 назву стовпця Модель; у В2 -- Ціна, в С2 -- Кількість, в D2 -- Сума;

· виділите всі чотири вічка, встановите на панелі форматування для них розмір 14 і розташування По центру;

· заповните вічка, в яких наводяться моделі, ціни і кількість;

· виділите вічко D3 і введіть в неї формулу =ВЗ*СЗ;

· встановите курсор на маркері заповнення вічка D3 (маркер заповнення розташований в правому нижньому кутку вічка і має вигляд чорно го квадратика) і, натискує кнопку миші, розтягніть вниз рамку так, щоб всі рядки з моделями комп'ютерів попали в рамку (в даному випадку це вічка D3 -- D5). У вічках D4 -- D5 відображуватимуться результати розрахунків по скопійованих формулах;

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

у вічко А7 введіть Разом;

· виділите вічко D7, натискуйте кнопку Автосума (AutoSum) на панелі Стандартна. У вічку з'явиться запис = CУMM(D3:D6). Натискуйте клавішу Enter;

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

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

Для роботи з датами і часом використовується більше десятка функцій. Виберіть команду Функція (Function) у меню Вставка (Insert). У діалоговому вікні Майстер функцій -- крок 1 з 2 (Insert Function) (мал. 16.6) у списку або категорію (Function category) виділите Дата і час (Date&Time).

Приклад використання функції Дата і час (Date&Time) розглянутий нижче (див. Вправи).

Текстові

Функція Текстові (Text) перетворить числове значення у форматований текст, і результат більше не бере участь в обчисленнях як число. Амперсанд (&) використовується для об'єднання декількох текстових рядків в один рядок.

Наприклад, у вічку ВЗ ви можете записати прізвище продавця (Петров), у вічку СЗ -- об'єм його продажів (5000). Після запису в якому-небудь вічку Вз& «продал»&ТЕКСТ(СЗ; «0,00 крб.»)&" одиниць товару» при проведенні обчислень станеться об'єднання вмісту вічок в одну фразу: Петров продав на 5000,00 крб. одиниць товару.

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

Аби виконати розрахунок, використовуючи стандартну функцію, виконаєте наступні дії:

1. Виділите вічко, в яке треба вставити функцію, введіть «= »,а потім в списку Функції, що розкривається, в рядку формул виберіть потрібну із списку (див. мал. 16.1). На екрані відображуватиметься діалогове вікно Аргументи функції (Function Arguments) (мал.). Якщо в списку, що розкривається, вибрати Інші функції (More functions) то відкриється діалогове вікно Майстер функцій -- крок 1 з 2 (Insert Function) (мал.).

Інші способи відображення діалогового вікна Майстер функцій -- крок 1 з 2 (Insert Function):

натискуйте кнопку Вставку функції (Insert Function) у рядку формул;

виберіть команду Функція (Function) у меню Вставка (Insert);

натискуйте клавіші Shift+F3.

2. У діалоговому вікні Майстер функцій -- крок 1 з 2 (Insert Function) (мал.) у полі Пошук функції (Search for а function) введіть опис дії, яку ви хотіли б виконати. Наприклад, аби знайти функції, пов'язані з логарифмами чисел, введіть log. Якщо ви не знаєте, до якої категорії відноситься ваша функція, то в списку, що розкривається, або категорія (Or select а category) виберіть рядок Повний алфавітний перелік (АН) і проглянете список всіх функцій в алфавітному порядку. Смуга прокрутки дозволяє проглянути невидимі в даний

Мал. Діалогове вікно майстра функцій -- крок 1 з 2

момент елементи списку. У нижній частині вікна дається визначення виділеної функції і її аргументів. Аби отримати опис функції, виберіть функцію в списку і клацніть заслання Довідка по цій функції (Help on this function). Виділите потрібний рядок в списку Виберіть функцію (Select а function) і натискуйте кнопку (Жили клавішу Enter. 3. На екрані відображуватиметься діалогове вікно Аргументи функції (Function Arguments) (мал.). У верхній частині вікна розміщуються поля, призначені для введення аргументів, в нижній частині -- довідкова інформація: ім'я вибраної функції, всі її аргументи, призначення функції і кожного аргументу, поточний результат функції і всієї формули. У тих випадках, коли аргумент приведений напівжирним шрифтом, він є обов'язковим, якщо звичайним шрифтом, то його можна пропустити. Аби панель формул не закривала діапазон вічок з даними, її можна перемістити, утримуючи такою, що натискує кнопку миші. Аби ввести як аргумент заслання на вічко, клацніть значок розташований біля правої межі поля. ^™*

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

Мал. Використання панелі формул для оцінки дисперсії по вибірці

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

Якщо формула масиву повертає одне значення, вкажіть вічко, в яке необхідно ввести формулу. Якщо формула масиву повертає декілька значень, то виділите діапазон вічок, в які необхідно ввести формулу. Наберіть формулу і натискуйте клавіші Ctrl+Shift+Enter. При введенні формули масиву Excel автоматично бере її у фігурних дужок {} після натиснення вказаних клавіш.

Масиви формул зручно використовувати для уведення однотипних формул і обробки даних у вигляді таблиць. Наприклад, для обчислення модуля від чисел, розміщених у вічках B1, C1, D1, E1 замість уведення формул у кожне вічко можна увести одну формулу - масив для усіх вічок. Microsoft Excel додає навколо масиву формул фігурні дужки { }, по яким його можна відрізнити.

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

· виділити вічка, в яких повинен знаходитися масив формул (мал.14);

· увести формулу звичайним способом, вказавши в якості аргументів групу вічок-аргументів ABS(B1:E1);

· у останньому вікні замість кнопки ОК натиснути комбінацію клавіш Ctrl+Shift+Enter.

Для редагування масиву формул потрібно:

· виділити вічка, в яких знаходиться масив;

· натиснути мишею усередині рядка редагування і відредагувати формулу;

· натиснути комбінацію клавіш Ctrl+Shift+Enter.

Автокорекція при введенні формул

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

Використання поля Введіть питання паю вирішення питань, зв'язаних з використанням функції

Для вирішення питань, зв'язаних з використанням функції, її синтаксисом, визначенням до якої категорії вона відноситься, можна ввести назву функції в поле Введіть питання (Type а question for help) і натискувати клавішу Enter. Так, якщо у вас виникли питання по використанню кореляційної функції, введіть в поле: кореляція (correlation). Будуть запропоновані можливі варіанти використання функції (мал. 16.7).

Мал. Інформація, що надається помічником про функцію

Натискуйте кнопку варіанту відповіді, що цікавить вас. У приведеній довідці буде вказано призначення функції, її синтаксис і надані приклади.

Вправи