Електронні таблиці

Таблиці Excel

Тест 1 по формулам Excel.
Тест 2 по сортуванню та фільтруванню даних.
Тест 3 Підсумковий тест по програмі Microsoft Excel

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

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

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

Створення формули, яка посилається на значення в інших клітинках

1. Виберіть клітинку.
2. Введіть знак рівності (=)
3. Виберіть клітинку або введіть її адресу у вибрану клітинку. 
4. Введіть оператор. Наприклад, – для віднімання. 
5. Виберіть наступну клітинку або введіть її адресу у вибрану клітинку.
6. Натисніть клавішу Enter. Результат обчислення з’явиться в клітинці з формулою.

 

Перегляд формули

1.      Введена в клітинку формула також відображається в рядку формул.

2.      Щоб переглянути формулу, виберіть потрібну клітинку. Формула з’явиться в рядку формул.

           Введення формули з вбудованою функцією

            1.     Виберіть пусту клітинку.

            2.     Введіть знак рівності (=).

            3.  На вкладці формули вибрати необхідну функцію.

            4.     Виберіть діапазон клітинок.

            5.  Натисніть клавішу Enter, щоб отримати результат.

                Excel містить більше ніж 400 вбудованих функцій. Функція має ім'я і
                список аргументів у круглих дужках.

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

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

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

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

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

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

Типи функцій. Для зручності роботи функції в Excel розбиті по категоріях: функції керування базами даних і списками, функції дати і часу, DDE/Зовнішні функції, інженерні функції, фінансові, інформаційні, логічні, функції перегляду і посилань. Крім того, присутні такі категорії функцій: статистичні, текстові та ма­тематичні.

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

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

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

У Excel широко представлені математичні функції. Наприклад, можна виконувати різні операції з матрицями: множити, знаходити зворотну, транспонувати.

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

Функції у електронних таблицях  — це скорочений ваpiaнт формул, які часто застосовують, тобто це програми, що за готовими формулами реалізують обчислення над аргументами, які задаються користувачем згідно з правилами запису (синтаксису) функції. Кожна функція має ім'я (латинські або кирилицею у Excel) та аргументи, які записують у круглих дужках після iмeнi функції через крапку з комою. Аргументами функцій можуть бути числа, посилання на клітинки та діапазони, імена, текст, логічні значення, значення помилок, вкладені функції тощо. Доступ до функції здійснюється за її іменем. 

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

       

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

                                             Арифметичні функції

СУММ(число1; число2;...) - повертає суму аргументів  (SUM).

  ПРОИЗВЕД(число1;число2;...) - повертає добуток своїх аргументів (PRODUCT).

    АВS(число)  - повертає абсолютне значення числа. (модуль числа)

   КОРЕНЬ(число) - повертає додатне значення квадратного кореня числа (SQRT). Якщо число від'ємне, з'являється повідомлення про помилку #ЧИСЛО!.

   СТЕПЕНЬ(число; показник степеня) - повертає результат піднесення до степеня (POWER). Цей вираз є еквівалентним до виразу число^показник степеня.

   ОКРУГЛ(число; кількість розрядів) - повертає результат округлення до зазначеної кількості десяткових розрядів (ROUND).

   ОКРВВЕРХ(х; точність) та ОКРВНИЗ(х; точність) - повертає результат округлення з надлишком (з нестачею) до найближчого числа, що кратне точності (ROUNDDOWN та ROUNDUP).

   ОТБР(число; кількість_розрядів) - залишає зазначену кількість розрядів числа після коми, відкидаючи всі наступні (не виконую­чи округлення) (TRUNC). 

   СЧЁТЕСЛИ(Диапазон;Критерий) - повертає кількість тільки тих ар­гументів, які відповідають певним умовам (COUNTIF). Диапазон — діапазон клітинок, для яких перевіряється крите­рій; Критерий — умова відбору у формі числа, виразу або тексту. 

                                    Статистичні функції

   СРЗНАЧ (AVERAGE) – визначає середнє значення.

   МИН, МАКС (MIN, MAX)– визначає мінімальне та максимальне значення.

   СЧЕТ – визначає кількість числових аргументів.

   КОРРЕЛ – визначає коефіцієнт кореляції між двома множинами даних.

   СРГЕОМ – визначає середнє геометричне.

   СРОТКЛ – повертає середнє абсолютних значень відхилень даних від середнього.

   СРЗНАЧА – визначає середнє арифметичне аргументів, якими можуть бути як числа, так і текст, логічні значення.

  

Абсолютні, відносні і мішані посилання. Посилання на клітинки інших аркушів та інших книг.

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

Адреса прямокутного блоку (діапазону) клітинок, сумісного, складається з адреси лівої верхньої клітинки блоку, двокрапки (оператора діапазону) і адреси правої нижньої клітинки, наприклад А1:Е5. Якщо треба  описати не сумісний діапазон клітинок, то використовують «;», наприклад,  А1;В3;Е5.

Адреси використовують також повні імена (адреси) клітинок. Для зазначення повного імені клітинки до її адреси дописують ім'я листа, де вона розташована, і назву книги (файлу):

[Книга 1]Лист1!А1.    

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

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

Під час копіювання формули відбуваються такі дії:

-формула вводиться в інші клітинки автоматично;

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

Наприклад, під час копіювання формули = В3*С3 з третього рядка у четвертий формула в четвертому рядку набуде вигляду  = В4*С4.

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

Другий спосіб, заповнити першу клітинку початковим значенням 
       -Правка - Заповнити - Прогресія…

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