Опорний конспект до теми табличний процесор

Опорний конспект

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

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

Оператори, які розпізнає програма Ехсеl:

Оператор	Назва	Приклад	Пояснення
+	Додавання	=А1+В1	Додає значення з комірки А1 в комірку В1
-	Віднімання	=А1–В1	Віднімає значення з комірки В1 із значення комірки А1
*	Множення	=А1*10	Виконує множення значення, що міститься в клітинці А1, на 10
/	Ділення	=А1/В1	Виконує ділення значення, що міститься в клітинці А1, на значення з комірки В2
^	Піднесення до степеня	=А1^2	Підносить значення, що міститься в клітинці А1, до степеня (у даному випадку до степеня 2)
&	Конкатенація	=А1&В1	Виконує об’єднання вмісту комірок  А1 та В1. Як правило, використовується для текстових значень.
=	Логічне порівняння “дорівнює”	=А1=В1	Повертає значення TRUE (ІСТИНА),  якщо значення в клітинці А1 дорівнює значенню в клітинці В1. Інакше повертає значення FALSE (ХИБА)
>	Логічне порівняння “більше ніж”	=А1>В1	Повертає значення TRUE,  якщо значення в клітинці А1 більше значення в клітинці В1. Інакше  повертає значення FALSE
<	Логічне порівняння “менше ніж”	=А1<В1	Повертає значення TRUE,  якщо значення в клітинці А1 менше значення в клітинці В1. Інакше  повертає значення FALSE
>=	Логічне порівняння “більше або дорівнює”	=А1>В1	Повертає значення TRUE,  якщо значення в клітинці А1 більше або дорівнює значенню в клітинці В1. Інакше  повертає значення FALSE
<=	Логічне порівняння “менше або дорівнює”	=А1<В1	Повертає значення TRUE,  якщо значення в клітинці А1 менше або дорівнює значенню в клітинці В1. Інакше  повертає значення FALSE
<>	Логічне порівняння “більше ніж”	=А1<>В1	Повертає значення TRUE,  якщо значення в клітинці А1 не дорівнює значенню в клітинці В1. Інакше повертає значення FALSE

 

Формули можуть містити довільну кількість операторів – як однотипних, так і різних. Послідовність виконання операторів залежить від пріоритету оператора. Найбільший пріоритет має оператор піднесення до степеня  (^), а найменший  - оператори логічного порівняння (<, >). Виконавши операцію піднесення до степеня у формулі, Ехсеl обчислює вираз в дужках, після чого виконує множення і ділення, потім – додавання і віднімання, і тільки після цього інші операції. Якщо ж формула містить декілька операцій множення і ділення, вони виконуються послідовно, зліва направо. Застосовуючи дужки, можна змінити порядок виконання операцій.

У формулах допускається використовувати тільки круглі ( ) дужки. Якщо ввести фігурні {} чи квадратні [ ] дужки, програма відобразить повідомлення про помилку. У формулах можна також використовувати вкладені дужки. В цьому випадку важливо, щоб кількість відкривних дужок дорівнювала кількості закривних.

В Ехсеl  є два способи введення формул в комірки робочого аркуша:

1.                  ввести оператори та адреси комірок вручну.

Наприклад, потрібно в комірку С3 потрібно ввести формулу =А3+В3. Для цього потрібно розмітити табличний курсор в клітинці С3, ввести символ “=”. Далі з допомогою клавіатури, ввести А3+В3. Під час введення формули посилання на комірки та межі навколо окремих комірок позначаються різними кольорами. Після завершення ведення формули натиснути на Enter або клацнути на кнопці в рядку формул.

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

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

Послідовність дій:

1. Активізувати комірку, до якої треба ввести формулу.
2. Ввести в комірку символ “=”.
3. Клацнути мишею на клітинці, адреса якої повинна з’явитися у формулі. Можна навіть вказати комірку, яка знаходиться на іншому робочому аркуші. Посилання на цю комірку з’явиться у формулі, а також у ряжку формул.
4. Ввести арифметичний оператор.
5. Клацнути мишею на наступній клітинці, значення якої бере участь у обчисленні.
6. Натиснути на Enter або клацнути на кнопці в рядку формул.

Формула, що набирається в електронній таблиці, відображається у рядку формул.  Після команди введення формули (натискання клавіші “=” або клацання по кнопці “”) у рядку формул з'являються нові кнопки: і . Клацання по кнопці з червоним хрестиком скасовує внесені до формули зміни, а клацання по кнопці з зеленою «галочкою» дає команду на виконання формули, записаної у комірці. Завершити введення формули можна натисканням на клавішу Enter, або переміщенням курсора до іншої комірки або клацанням по кнопці з зеленою галочкою в рядку формул.

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

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

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

Функції автоматичного обчислення

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

Клацнувши правою кнопкою миші на рядку стану, можна вибрати наступні команди:

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

Количество значений – визначає кількість виокремлених комірок.

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

Максимум – визначає найбільше значення, що містить комірки у виокремленому  діапазоні.

Минимум - визначає найменше значення, що містить комірки у виокремленому  діапазоні.

Сумма -  задає обчислення суми чисел у виокремленому  діапазоні.

Нет – не відображає у рідку ніяких значень.визначає кількість виокремлених коміроккоміроку виокремленому  діапазоні.

Абсолютні, відносні та мішані адреси

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

Посилання поділяються на три типи.

• Відносні. Змінюються при копіюванні формул.
• Абсолютні.  Не змінюються при копіюванні формул.
• Мішані. Є абсолютними тільки частково.

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

 

Абсолютні посилання вказують на фіксовану комірку. При копіюванні формул з абсолютними посиланнями адреси комірок в цих посиланнях гне змінюються. Про те, що посилання є абсолютним, вказує символ “$”, розташований біля кожної з координат  посилання. Наприклад: $А$1, $В$2, $С$5.

Якщо символ “$” стоїть лише біля одної з координат посилання, тоді одна частина посилання є абсолютною, а інші – відносною.  Наприклад, $В5, В$5. Такі посилання називають мішаними. Якщо абсолютною є тільки одна з координат посилання, це посилання завжди буде вказувати на той самий фіксований стовпець або рядок.

Символ “$” можна ввести вручну або переключатися клавішею F4. наприклад, якщо ввести посилання на комірку А1, а потім послідовно натискати F4, програма буде змінювати тип посилання наступним чином: $А$1, А$1, $А1, А1.

Типові помилки при введенні формул

Якщо при введенні формули була допущена помилка, Ехсеl  видає у цій клітинці спеціальне повідомлення.

#DIV/0!	У формулі задане ділення на нуль або на порожню  комірку.
#NAME?	Програма не може розпізнати у формулі введене ім’я комірки чи діапазону або інший текст.
#N/A	Значення недоступне для формули або функції (наприклад, не вказано один або кілька необхідних аргументів функції)
#NUM!	Введення невірного числового значення (наприклад, число занадто велике або замість додатного введено від’ємне)
#REF!	У формулі неправильно задане посилання на комірку (наприклад, посилання на вилучену комірку)
#VALUE!	У формулі використовується операнд чи аргумент неправильного типу або виконуються арифметичні дії з даними текстового типу
#NULL!	Задане перетинання двох областей, що не перетинаються

 

Кілька символів “#”, що заповнюють комірку, свідчать про недостатню ширину стовпця для відображення значення, яке міститься в ньому, або про те, що використовуються від’ємні дата і час. Щоб позбутися цієї помилки, потрібно розширити стовпець або змінити числовий формат комірки.

Якщо формула посилається сама на себе (наприклад, якщо в комірку В1 ввести формулу В2‑В1), виникає циклічне посилання, про яке видається повідомлення:

 

 

Опорний конспект

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

Наприклад, перетворює двійкове число на десяткове, повертає поточну дату або час, перетворює малі літери на великі, підраховує кількість символів у рядку та ін.

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

Структура функції

Введення функції починається зі знаків “=”, “+” або  “”. Усі функції програми Ехсеl складаються з двох основних частин – імені функції і аргументів. Аргументи  функцій задаються в круглих дужках, що йдуть безпосередньо за ім’ям функцій. Функція може мати один, декілька чи не мати жодного аргументу. Аргументом може бути діапазон комірок, наприклад функція  =SUM(A1:A5) знаходить суму значень, які містяться в комірках діапазону A1:A5. Якщо функція має декілька аргументів, вони відокремлюються один від одного крапкою з комою, наприклад функція  =SUM(A1:A5; В1:В5; С1:С5) підсумовує усі значення, що міститься в зазначених діапазонах. Наявність круглих дужок під час уведення функції є обов’язковою, навіть якщо функція не має аргументів.

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

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

Способи введення функцій

1. Введення вручну. Щоб ввести функцію вручну, потрібно ввести в комірку знак “=”, набрати ім’я функції (можна малими літерами), задати список аргументів у круглих дужках, натиснути Enter. Якщо ім’я функції введено правильно, літери автоматично перетворяться на великі.
2. Використання діалогового вікна Вставка функции.

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

Щоб ввести функцію, потрібно виконати такі дії:

1. Виокремити комірку, куди потрібно ввести функцію.
2. Вибрати команду Вставка/Функция (або натиснути кнопку Вставка функции на стандартній панелі інструментів). Відкриється діалогове вікно Вставка функции.

3.      В розкривному списку вибрати потрібну категорію, після цього в полі Выберите функцию з’являться функції, з яких треба вибрати потрібну.
4.      Після вибору функції з’явиться діалогове вікно Аргументы функции. У відповідних полях цього вікна за потрібно задати аргументи функції, ввівши їх адреси вручну або виділивши мишею.
5.      Після задання всіх аргументів потрібно натиснути кнопку ОК, діалогове вікно зникне, а в клітинці з’явиться результат обчислення функції.

 

3. Використання розкривного списку Функции

Як тільки в комірку ввести знак “=”, кнопка розкривного списку Имя в рядку формул автоматично замінюється кнопкою розкривного списку Функции. Список Функции місить перелік з 10 функцій, що використовувались останніми. Якщо клацнути на будь-якій функції з цього списку, її буде введено в комірку, а на екрані з’явиться діалогове вікно Аргументы функции, де можна задати аргументи цієї функції.

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

 

Математичні функції

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

Функція SUM

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

Якщо треба просумувати всі значення рядка 4, треба ввести формулу =SUM (4:4), щоб просумувати всі значення стовпця С, слід ввести формулу =SUM (С:С).

На стандартній панелі інструментів є кнопка Автосумма, яка підсумовує значення з комірок вказаного діапазону. Для автоматичного підсумовування значень потрібно розмітити табличний курсор у клітинці під діапазоном, комірок, що треба підсумувати, клацнути на кнопці Автосумма, виділити мишею рамкою діапазон підсумовування, натиснути Enter.

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

Функція PRODUCT

Ця функція повертає добуток заданих чисел, вона може мати до 30 аргументів, наприклад, функція

=PRODUCT (B3:B8; D3:D8; G5:H12) обчислює добуток значень, що містяться у вказаних діапазонах.

Функції округлення чисел

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

Для округлення чисел можна також використовувати такі функції:

ROUNDUP – округлює число до найближчого більшого за модулем;

ROUNDDOWN – округлює число до найближчого меншого за модулем;

CEILING – округлює число до найближчого  більшого цілого або до кратного значенню точності;

FLOOR – округлює число до найближчого меншого за модулем, кратного значенню точності.

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