IV.3 Створення і використання простих формул

⇐ ПредыдущаяСтр 28 из 40Следующая ⇒

Програма Excel розглядає вміст осередку як формулу, якщо воно починається із знаку рівності (=). Тим самим, щоб почати введення формули в осередок, досить натиснути клавішу «=». Проте вводити формулу зручніше, якщо в рядку формул клацнути на кнопці Змінити формулу. В цьому випадку безпосередньо під рядком формул відкривається Палітра формул (Рис.20.), що містить обчислене значення вказаної формули.

Рис. 20.

Під формулою розуміється набір чисел і посилань на числові осередки, сполучених знаками математичних операцій (+ - операція складання; - - операція віднімання; * - операція множення; / - операція ділення; ^ - операція піднесення до ступеня.).

Математичні операції

Операція	Позначення у формулі	Приклад
Піднесення до степеню Множення Ділення Додавання Віднімання Рівно Менше Більше Менше або рівно Більше або рівно Не рівно Діапазон Об'єднання діапазонів Максимум Мінімум	^ * / + - = < > <= >= <> : ; МАКС МІН	=3^2 =А8*С6 =D4/N5 =B2+5 =9-G6 =СУММ(А1:С10) =СУММ(А1;А2;А6) =МАКС(А3:С5) =МІН(Е2:Р7)

Щоб задати посилання на осередок, треба вказати у формулі його ім'я (Рис. 21). Це можна зробити уручну або клацанням на відповідному осередку по ходу введення формули.

Рис. 21.

Після закінчення введення формула в таблиці не відбивається. Замість неї в осередку розміщується обчислене значення. Проте якщо зробити осередок з формулою активною, то формулу можна побачити в рядку формул (Рис. 22.).

Рис. 22.

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

Очевидно, для обрахунку виручки необхідно скористатись формулою

Виручка = Ціна ´ Кількість проданого.

Для обрахунку виручки по товару Товар1 введемо в осередок Е5 формулу

Для обрахунку виручки від Товару2 необхідно би було в осередок Е6 ввести формулу =С6*D6 і аналогічно для всіх товарів. Щоб зекономити час можна скористатися методом розмноження формул. Для цього необхідно:

1. Поставити табличний курсор в осередок Е5.

2. Навести курсор миші на маркер заповнення.При цьому він прийме вид + (хрестика).

3. Протягнути цей курсор вниз по стовпцю.

4. Отримаємо

Переглянувши осередки стовпця Е, в які ми розмножили формулу, переконаємось, що формула з осередку Е5 з модифікацією зкопіювалась в нижче розташовані осередки в виді:

Що нам і потрібно було.

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

Рис. 23.

На Рис. 23. представлений приклад, коли при копіюванні формули =B2*C2 з осередку D2 в осередок D3 відбулася модифікація посилань: посилання B2 замінене посиланням B3, а C2 - C3.

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

Наприклад, якщо в вище розглянутому прикладі, необхідно ще розрахувати дані про виручку в конвертованій валюті, тоді уже розраховану Виручку в гривні необхідно перевести в Виручку в $ за формулою:

Виручка в $ = Виручка / Курс валюти

Яка матиме вид = Е5/В2^

Якщо цю формулу з осередку Е5 розмножити в нижче розташовані осередки, то отримаємо результат:

В осередках F6, F7 очевидні помилки, тому що в результаті розмноження в нижче розташованих осередках були отримані наступні формули:

Адреса осередку D2 (що містить курс валюти) модифікувалась в D3, D4, D5. А нам необхідно щоби ця адреса залишалась незмінною, тобто нам необхідно зберегти абсолютну адресу осередку.

Для того, щоб задати посилання на осередок як абсолютну, треба задати перед номером рядки або ім'ям стовпця символ «$». Таким чином, посилання на осередок, наприклад A1, може бути записана чотирма способами: A1 $A1, A$1 $A$1. При заповненні осередків формулою як відносна розглядається тільки та частина адреси, перед якою немає символу «$». Для зміни способу адресації при редагуванні формули треба виділити посилання на осередок і натиснути клавішу F4 (послідовним натисненням клавіші можна отримати один із способів адресації).

В нашому випадку перед розмноженням формули в ній необхідно адресу D2 зробити абсолютною, а саме перед номером рядка (номер 2) вставити знак $ (долара):

Рис. 24.

На Рис. 25. представлений приклад, коли використання у формулі абсолютного посилання при копіюванні формули це посилання не модифікується.

Рис. 25.

Приклад.

Умова прикладу. Створити зведену екзаменаційнійну відомость, в якій приведені оцінки з дисциплін «Математика», «Фізика», «Інформатика». Вимагається розрахувати і заповнити колонку «Середній бал».

Спочатку введемо назву таблиці «Зведена екзаменаційна відомість» в осередок А1 - треба клацнути мишею на клітці А1, а потім з клавіатури ввести назву. По закінченні - натиснути “Enter”.

Потім в окремі осередки в другому рядку треба ввести назви колонок таблиці: «ФІО студента», «Математика», «Фізика», «Інформатика», «Середній бал». Увага! Якщо при наборі текст виходить за межі елемента таблиці і закриває наступний осередок, то текст в набраному осередку не спотвориться і не віддалиться, якщо клацнути на наступному осередку і перейти до набору тексту наступного осередку. Для того, щоб розміри колонок точно відповідали введеним назвам необхідно: виділити осередки з назвами колонок відомості; активізувати пункт меню Формат ® Столбец ® Автоподбор по ширине.

Далі в колонку «ФІО студента» вводимо прізвища студентів, а в інші - екзаменаційні оцінки. Настроюємо колір «шапки таблиці» і задаємо розділові лінії між елементами таблиці. В результаті таблиця набув вигляд:

Введення формул. Розрахунок середнього бала для кожного студента виконується по простій формулі середнього арифметичного: (Ом + Оф + Ої) / 3, де Ом - оцінка по математиці, Оф - оцінка по фізиці, Оі - оцінка по інформатиці.

Здійснимо введення формули для студента Іванова. В осередку «Середній бал», відповідному Іванову, натиснути знак «=», натиснути знак «(», потім клацнути на осередку з оцінкою по математиці, натиснути знак «+», потім клацнути на осередку з оцінкою по фізиці, натиснути знак «+», потім клацнути на осередку з оцінкою по інформатиці, натиснути знак «)», натиснути знак «/», натиснути «3»

і натиснути {Enter}.

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

Для задання формул розрахунку для інших студентів виконаємо копіювання набраної формули: виділимо осередок з середнім балом Іванова, клацнути правою кнопкою миші і у випадаючому меню вибрати пункт «Копіювати» - повинен з'явитися мерехтливий прямокутник навколо виділеного осередку; виділити блок осередків з середніми балами від Петрова до Бойцова включно; клацнути правою кнопкою миші на виділеному блоці осередків і у випадаючому меню вибрати пункт «Вставити» - повинні з'явитися значення середніх балів. Таблиця набуде вигляду:

Для того, щоб обмежити значення середнього бала двома цифрами в дробовій частині необхідно: виділити блок осередків; клацнути правою кнопкою миші на виділеному блоці осередків і у випадаючому меню вибрати пункт «Формат осередків» - повинен з'явитися діалог форматування вмісту осередків; на вкладці «Число» в списку форматів вибрати «Числовій»

і натиснути {Enter}.