8.Поняття прогресії

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

1. Виділити діапазон для автозаповнення, першою коміркою якого має бути комірка із введеним значенням. Виконати команду Заполнить і отримати на екрані список методів заповнення. Вибрати у списку методів Прогрессия:. У вікні Прогрессия установити розташування, вид і потрібні значення параметрів прогресії,після чого натиснути кнопку OK.

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

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

Міністерство надзвичайних ситуацій україни

Академія пожежної безпеки ім. Героїв Чорнобиля

Факультет пожежно-профілактичної діяльності

Кафедра вищої математики та інформаційних технологій

ЗАТВЕРДЖУЮ

Начальник кафедри

ВМ та ІТ

к.ф-м.н., доц.,

полковник с.ц.з.

_______ І.П. Частоколенко

“___”______2013р.

Навчальна дисципліна: Інформатика та комп’ютерна техніка

1-й курс (стаціонар, курсанти).

ЛЕКЦІЯ

Тема № 3.1. Створення, редагування та оформлення розрахункових таблиць.

Час: 18, з них 6 години лекцій, 8 години практичних занять, 4 годин самостійної роботи

Тема лекції №12: Створення та редагування формул.

Навчальна мета: Розглянути теоретичний матеріал у відповідності з темою лекції.

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

Матеріально-методичне забезпечення: Дошка, крейда.

Розробив:

викладач

кафедри ВМ та ІТ А.П. Марченко

Лекція обговорена та схвалена на засіданні кафедри

Протокол №____від “__”_____________2013р.

Лекція 12. Створення та редагування формул.

1. Робота з формулами. Будова формули

Будь-яка формула обов’язково починається зі знака “=”. Якщо про цей знак забути, то введене буде сприйнято як звичайний текст. Зрозуміло, що така формула працювати не буде.

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

Основні правила для побудови формул такі:

• формула записується тільки в рядок;

• аргументи функцій обов’язково беруться в дужки;

• дужки у формулах можуть бути тільки круглими;

• аргументи функції відокремлюються один від другого комою (якщо було виконано відповідну настройку);

• знак множення у формулах пропускати не можна;

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

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

Пріоритет	Знак операції	Найменування операції
1 (найвищий)	«-»	унарний мінус
2	«^»	піднесення до степеня
3	«*», «/»	множення і ділення
4	«+», «-»	додавання і віднімання

Основні повідомлення про помилки у формулах такі:

• #ДЕЛ/0! – ділення на нуль. Таке повідомлення виникає в тих випадках, коли значення дільника операції ділення виявляється рівним нулю.

• «Обнаружены ошибки во введённой формуле». Таке повідомлення бачимо, наприклад, при введенні помилкової формули «=10+*2», в якій указано дві операції підряд. При цьому пропонується варіант виправлення формули. Якщо ви погоджуєтесь із запропонованим, то натискуєте кнопку Да. Якщо ви натискуєте кнопку Нет, то це означає, що виправлення ви збираєтесь внести самостійно.

• «Несоответствие скобок». Таке повідомлення виникає в тих випадках, коли у введеній формулі порушено баланс між відкриваючими та закриваючими дужками. Наприклад: «=(2+3*4».

• #ЗНАЧ! – помилка значення, що входить до складу формули. Виникає, наприклад, у випадку «=SIN("кут")», де для функції указано аргумент не числового, а текстового типу.

• #ИМЯ? – помилка в імені. Виникає, наприклад, при обчисленні виразу , якщо застосувати неправильне найменування функції«=КОРЕНЬ(tg(1)+1)». Правильне рішення: «=КОРЕНЬ(TAN(1)+1)».

• #ЧИСЛО! – помилка в аргументі функції. Наприклад, аргумент функції у формулі «=КОРЕНЬ(-5)» – від’ємний.

• #Н/Д – стандартне помилкове значення, яке означає НеДоступно. Правило тут таке: якщо хоча б один аргумент будь-якої функції дорівнює #Н/Д, то і значення цієї функції теж дорівнює #Н/Д. Наприклад, «=COS(#Н/Д)».

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

2. Введення та копіювання формул

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

• безпосередній,

• за схемою автоматичного отримання адрес,

• за допомогою майстра функцій.

В процесі введення формули є доступними всі клавіші редагування для виправлення допущених помилок. Якщо надруковане не влаштовує повністю, то можна натиснути кнопку Отмена (червоний хрестик у рядку формул ) або клавішу Esc. Якщо процес друкування закінчено, то слід натиснути кнопку Ввод (зелена позначка у рядку формул ) або клавішу Enter.

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

3. Умовне форматування

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

Для виконання умовного форматування заданої комірки використовують команду Условное форматирование... вкадки Главная розділу Стили. Внаслідок цього з’являється меню, яке пропонує обрати один з раніше встановлених форматів поведінки. Основні це правила відборі та правила виділення значень. Для того, щоб ввести своє правило скористайтесь пунктом Создать правило… та оберіть тип правила. Доступні наступні типи:

• Форматировать все ячейки на основе их значений – означає, що форматування відбуватиметься у вказаному діапазоні на основі вказаних правил діапазону відбору. Наприклад вказавши мінімальне та максимальне значення, можливо встановити колір заливки (дво-трьокольоровий градієнт), піктограму або гістограму, що відображатиметься в кожні комірці виділеного діапазону.

• Форматировать только ячейки которые содержат – виконуватиме форматування тільки тих комірок, які задовольнятимуть вказаним правилам.

• Форматировать только первые или последние значения – форматуватиме лише крайній діапазон (числовий або відсотковий) від вказаного.

• Форматировать только значения, которые находятся выше или ниже среднего – форматуватиме комірки які містять значення вище або нижче (можливо встановити додаткове відхилення) від середнього значення виділеного діапазону.

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

• Использовать формулу для определения форматируемых ячеек – використовувати форматування згідно введеної формули. Формула повинна повертати значення ИСТИНА або ЛОЖЬ.

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

Видалити правила умовного форматування можна використавши пункт меню Удалить правила, що виникає при натисненні на кнопку Условное форматирования на вкладці Главная.

4. Використання формул

Прості вирази.

Для проведення обчислень використовують формули.

Зображення формули можна переглянути в рядку формул, а в комірці буде відображатися результат обчислень. Введення формули починається з введення знаку «=», за яким можуть бути введені операнди. Під операндами розуміють сталі значення, адреси комірок, імена функцій (стандартних або визначених користувачем), знаки арифметичних операцій («+» – додавання, «–» – віднімання, «*» – множення, «/» – ділення, «^» – піднесення до степеня (2^3=2³), «%» – відсоток), логічні операції порівняння (>, <, =, >=, <=, <>), а також дужки для визначення порядку виконання операцій.

Для введення в формулу адреси комірки можна виділити комірку одинарним натисканням лівою клавішею мишки. Для того, щоб у формулах використати діапазон комірок, його можна виділити, утримуючи натиснутою ліву клавішу мишки, або самостійно задати діапазон, використовуючи знак «двокрапка» (наприклад, А1:А5).

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

Формули з використанням посилань.

У формулах можливо використовувати адреси інших комірок, використовуючи значення, які знаходяться в них.

Наприклад, щоб скласти суму чисел комірок А1, А2 та А3, в комірці В1 можна написати формулу «=А1+А2+А3», і результатом буде саме сума значень усіх трьох комірок. Вказати адресу можна ввівши її власноруч, або зробивши ЛКМ на потрібній комірці вже після того, як почали набирати формулу (після знаку «=» або інших операторів, що йдуть за ним).

5. Відносні та абсолютні адреси.

Якщо не вказано щось інше, то адреса комірки у складі формули розглядається як відносна. Це означає, що при копіюванні формули з однієї комірки в другу адреси, указані в ній, автоматично змінюються на величину відстані між оригіналом та копією. Наприклад, формула, розміщена у комірці C1, містить адресу комірки B3. Якщо скопіювати цю формулу у комірку E4, то наявна в ній адреса B3 автоматично змінюється адресою комірки D6. Дійсно, сама формула була зміщена на два стовпчики вправо і на три рядки вниз. У відповідності з цим змінилась і адреса комірки, яка містилась в ній.

Рисунок 12.1 – Використання відносної адресації

Абсолютна адреса характерна тим, що вона при копіюванні не змінюється. Для вказівки абсолютної адреси застосовується символ «$». У складі адреси комірки абсолютними можуть бути номери рядка і стовпчика як окремо, так і разом. Наприклад, відносні та абсолютні адреси комірки A1 мають вигляд A1 і $A$1 відповідно. А в адресах A$1 і $A1 абсолютним є лише один із компонентів. Вказати даний тип адреси можливо власноруч додавши символ $ до адрес комірок, або ж натиснувши функціональну клавішу F4 при виділеній потрібній комірці.

Використання посилань на комірки інших робочих листів.

У якості вхідних даних дозволяється використовувати комірки не тільки з того листа, де знаходиться формула, а з будь-якого іншого. Відмінність лише в тому, що перед адресою комірки необхідно додатково вказати назву листа, на якому знаходиться бажана комірка. Наприклад, для комірки А1, що знаходиться на листі Лист2, посилання буде наступним: «=Лист2!А1». За тим же правилом використовуються і діапазони.

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

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

Наприклад, маючи формулу “=СУММ(A3; C3)” у комірці E3. Скопіюємо цю формулу у три сусідні комірки, які знаходяться нижче E3. Зробимо це, наприклад, шляхом автозаповнення.

Виділимо комірку E3. Сумістимо вказівник миші з правим нижнім кутком курсора ЕТ. При цьому вказівник миші має набути форми маленького чорного хрестика. Утримуючи ЛКМ у натиснутому стані, протягнемо вказівник миші на три комірки вниз. Після цього ЛКМ відпустимо.

Тепер проконтролюємо вигляд формул у комірках E4, E5 і E6. Як не дивно, але вони всі різні і мають вигляд “=СУММ(A4; C4)”, “=СУММ(A5; C5)” і “=СУММ(A6; C6)” відповідно. Це як раз і означає, що при копіюванні автоматично відбулась настройка формул на нове місце розташування. Принцип настройки в даному випадку полягає в тому, що, як і основна формула, нові формули беруть значення аргументів на тій же відстані зліва відносно. Тепер має бути зрозуміло, чому адреси, використані в цих формулах, називаються відносними.

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

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

Імена комірок. Імена комірок використовують як позначення змінних у складі формул. Імена комірок можуть бути стандартними і нестандартними. Стандартні імена являють собою адреси комірок, побудовані шляхом поєднання позначень стовпчика та рядка. Для будь-якої комірки можна обрати також довільне нестандартне ім’я.

На нестандартні імена накладаються наступні обмеження:

1. вони не можуть містити пропусків (пробілів);

2. вони не повинні співпадати з адресою будь-якої іншої комірки;

3. вони можуть бути тільки абсолютними;

4. великі та малі літери в іменах не розрізняються в будь-якому алфавіті.

Зручність нестандартних імен пов’язана з їх звичністю, а також з можливістю надання їм смислового звучання. Наприклад: X, Y, Результат, Підсумок тощо. А це значно полегшує побудову та сприйняття формул.

Для створення нестандартного імені треба виділити потрібну комірку, а потім ввести вибране ім’я у поле Имя рядка формул.

Нестандартні імена мають наступні властивості:

• Якщо виділити комірку з нестандартним іменем, то саме воно буде тепер відображуватись у полі Имя рядка формул.

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

• Нестандартні імена можуть бути присвоєні не тільки коміркам, але й діапазонам.

• Надані нестандартні імена є глобальними, тобто вони дійсні на будь-яких листах книги. Але їх можна створити і локальними, тобто дійсними в межах тільки свого листа. Для цього до складу імені треба включати також і ім’я листа. Наприклад: Лист3!x. При цьому нестандартні імена листів беруться в одинарні лапки. Наприклад: ‘Результати’!x.

• Одній і тій же комірці або діапазону можна надати довільну кількість імен.

Щоб вилучити існуюче нестандартне ім’я, треба виконати команду Присвоение шимени, для цього необхідно перейти на вкладку «Формулы» де знаходиться кнопка «Диспетчер имен», або ж використати клавіатурне скорочення Ctrl+F3, і в цьому вікні обрати потрібне ім’я та вилучити його зі списку.