26.09.2017 Інформатика

http://mobile.elar.fizmat.tnpu.edu.ua/bitstream/handle/123456789/695/%D1%83%D1%80%D0%BE%D0%BA%2010%20%D0%BA%D0%BB%D0%B0%D1%81%20%281%29.pdf?sequence=1

1.Формули в Excel 2007.
У клітинки електронної таблиці, крім чисел і текстів, можна вводити формули.
Формула в Excel 2007 — це вираз, який задає порядок обчислення в електронній таблиці. Починається формула зі знака = і може містити числа, тексти, посилання на клітинки, знаки дій (оператори), дужки та функції.
Під час уведення формул потрібно дотримуватися таких правил:
для позначення арифметичних дій використовуються такі оператори: + — додавання, - — віднімання, * — множення, / — ділення;
для позначення дії піднесення до степеня використовується оператор ^;
для позначення дії знаходження відсотків використовується оператор %; наприклад формула знаходження 25% від числа 134 виглядатиме так: =134*25%;
не можна опускати оператор множення;
порядок виконання (пріоритет) операцій збігається з порядком (пріоритетом), прийнятим у математиці, за окремими винятками. Наприклад, в Excel 2007 операція знаходження протилежного числа має вищий пріоритет, ніж операція піднесення до степеня. Тому в Excel 2007 значення виразу -5^2 дорівнює 25, а не -25, як у математиці. Але у виразі 2-5^2 знак мінус означає вже не знаходження протилежного числа, а віднімання, пріоритет якого нижчий, ніж в операції піднесення до степеня, тому значення цього виразу дорівнює, як і в математиці, 2-25 = -23;
для зміни порядку виконання дій використовують круглі дужки;
формула має бути записана у вигляді рядка символів/
За замовчуванням після введення в клітинку формули результат обчислення відображається в цій клітинці, а введена формула відображається в Рядку формул, якщо зробити цю клітинку поточною.
Наприклад, якщо в клітинку С2 ввести формулу =(25+67)/2, то в результаті в цій клітинці відобразиться число 46, а в Рядку формул, якщо зробити клітинку С2 поточною, відобразиться введена формула (рис.).
Як уже зазначалося, у формулах можна використовувати посилання на клітинки.
Посилання на клітинку складається з адреси клітинки, до якої додаються вказівки на місце її розташування, якщо вона знаходиться не на тому самому аркуші, що й клітинка, до якої вводиться формула.
Якщо у формулі використовуються посилання на клітинки, то під час обчислення за цією формулою використовуються дані із цих клітинок.
Наприклад, якщо в клітинці В2 міститься число 5, у клітинці ВЗ число 10, а в клітинку В4 ввести формулу =В2*ВЗ, то в клітинці В4 відобразиться число 50, тобто 5*10 (рис.).
Ім'я клітинки також можна використовувати у формулах як посилання на клітинку. Наприклад, якщо в клітинку з іменем Доход увести число 3500, у клітинку з іменем Витрати - число 2000, а в клітинку Е5 - формулу = Доход-Витрати, то в останній клітинці відображатиметься число 1500.
Якщо у формулах використовуються посилання на клітинки, то при зміні даних у цих клітинках відбувається автоматичне переобчислення значень за всіма формулами, які містять посилання на ці клітинки.
Наприклад, якщо в таблиці, зображеній на рисунку, замість числа 5 у клітинку В2 ввести число 15, то в клітинці В4 автоматично з'явиться число 150.
Використання у формулах посилань на клітинки з даними замість використання у формулах самих даних має ряд переваг. На рисунках зображено дві таблиці для обчислення ПДВ (податок на додану вартість), який платить державі будь-яке підприємство, що отримує прибутки. На сьогодні в Україні розмір цього податку становить 20 %.
У таблиці на рисунку використовуються формули для обчислення ПДВ без посилань на клітинки з цінами товарів: від конкретної ціни кожного товару обчислюється 20 %. А в таблиці на рисунку використовуються формули для обчислення ПДВ з посиланнями на клітинки з цінами товарів. Відповідні формули, що знаходяться в клітинках D7 кожної таблиці, відображаються в Рядках формул.
Результати обчислень в обох таблицях однакові. У чому ж тоді полягає перевага другої таблиці порівняно з першою? Уявімо собі, що ціни на товари змінилися. Тоді в першій таблиці потрібно внести зміни у клітинках двох стовпців (Ціна і ПДВ), а в другій таблиці — лише одного (Ціна), бо у стовпці ПДВ переобчислення з новими значеннями цін виконаються автоматично, тобто потрібно виконати вдвічі менше роботи. А якщо таблиця велика, то економія часу виявиться досить суттєвою.
Під час складання даної таблиці варто також передбачити, що може змінитися й ставка податку. Тоді доцільно виділити для значення ставки ПДВ окрему клітинку і використовувати у формулах посилання на цю клітинку (рис.).
Зверніть увагу: у формулах можна використовувати посилання на клітинки, у яких містяться формули. У таких випадках будуть використані значення, обчислені за цими формулами. Наприклад, якщо в клітинку D9 таблиці, зображеної на рисунку, помістити формулу =D5+D6+D7+D8, то за цією формулою буде обчислена сума 130 + 80 + 240 + 56.
У формулах можна також використовувати посилання на клітинки інших аркушів тієї самої або інших книг.
Для посилань на клітинки іншого аркуша тієї самої книги потрібно перед адресою клітинки вказати ім'я аркуша зі знаком оклику. Наприклад, Аркуш2!А1 є посиланням на клітинку А1 аркуша Аркуш2.
Якщо ж необхідно записати у формулі посилання на клітинку з іншої книги і вона відкрита, то перед адресою клітинки потрібно записати ім'я файлу книги в квадратних дужках й ім'я аркуша зі знаком оклику. Наприклад, [Кошторис.хlsх]Аркуш2!С4 є посиланням на клітинку С4, яка знаходиться на аркуші Аркуш2 в книзі з іменем Кошторис.хlsх, яка відкрита в поточний момент часу роботи з програмою Excel 2007.
Якщо в імені файлу книги або в імені аркуша є пропуски, то таке ім'я потрібно взяти в одинарні лапки. Наприклад, '[Підсумки І семестру. xlsx]10 Б'!С15.
Якщо потрібна книга закрита, то слід указати в одинарних лапках шлях до файлу, у якому записана книга, ім'я файлу книги в квадратних дужках та ім'я аркуша, а за лапками - знак оклику і адресу клітинки. Наприклад, 'D:\ТемаЗ\[Кошторис.хlsх]Аркуш2'!С4 є посиланням на клітинку С4, що знаходиться на аркуші Аркуш2 у файлі Koшторис.xlsx, який збережено в папці ТемаЗ на диску D. У цьому випадку, якщо імена папки, файлу або аркуша містять пропуски, то ніяких додаткових лапок ставити не потрібно.
Для уникнення помилок під час уведення у формулу посилань на клітинки з клавіатури можна необхідні клітинки вибирати вказівником. При цьому посилання у формулі та межі відповідних клітинок виділяються певним кольором для зручності контролю правильності введення формул (рис.). Після введення формули виділення кольорами зникає.
Під час уведення формули, яка містить ім'я клітинки, можна виконати Формули => Визначені імена => Використовувати у формулі і вибрати потрібне ім'я зі списку, що відкриється, або виконати Формули => Визначені імена => Використовувати у формулі => Вставити імена і вибрати потрібне ім'я у вікні Вставлення імені.
Якщо формула містить ім'я клітинки і областю застосування імені є вся книга, то посилання на аркуш указувати не потрібно.
При використанні формул в клітинках електронної таблиці можуть з'являтися повідомлення про помилки (табл.).











2.Модифікація формул.
Якщо вмістом клітинки є формула, яка містить посилання, то під час копіювання вмісту цієї клітинки в інші клітинки у формулі може відбутися автоматична зміна посилань — модифікація формули. Розглянемо кілька прикладів такої модифікації.
Нехай у клітинці Е1 (рис.) записана формула =ВЗ+С2. Скопіюємо її в клітинку G4. У результаті копіювання ця формула модифікується у формулу =D6+Е5. Тут можна помітити таку закономірність. В адресі клітинки G4 (куди копіювалася формула) номер стовпця на 2 більше, а номер рядка на 3 більше, ніж в адресі клітинки Е1 (звідки копіювалася формула). І в усіх посиланнях модифікованої формули номер стовпця на 2 більше, а номер рядка на 3 більше, ніж у посиланнях вихідної формули.
Скопіюємо тепер формулу з клітинки G4 в клітинку D3. У результаті копіювання формула =D6+Е5 модифікується у формулу =А5+В4. Як ми бачимо, і тут має місце закономірність, зазначена в попередньому прикладі. Дійсно, в адресі клітинки D3 номер стовпця на 3 менше, а номер рядка на 1 менше, ніж в адресі клітинки G4. І в усіх посиланнях модифікованої формули номер стовпця на 3 менше, а номер рядка на 1 менше, ніж у посиланнях вихідної формули.
Під час копіювання формул відбувається їхня модифікація за таким правилом: номери стовпців (рядків) у посиланнях змінюються на різницю номерів кінцевого і початкового стовпців (рядків).
Під час переміщення формули не модифікуються.
З наведеного правила випливає, що під час копіювання формул у межах одного рядка (стовпця) номери рядків (стовпців) у формулах не змінюються.
Під час копіювання формул можуть виникнути помилки, аналогічно тим, які виникають при використанні формул (див. табл.). Якщо зробити поточною одну з клітинок, у якій виявилася помилка, то поруч із нею з'являється кнопка зі списком (рис.). За наведення вказівника на кнопку з'являється коментар до помилки, а відкриття списку дає можливість одержати довідку з описом помилки, якщо коментаря виявилося недостатньо для розуміння суті помилки. Досить корисною є можливість переглянути кроки обчислення, що сприяє швидшому знаходженню місця помилки.
Але інколи потрібно, щоб під час копіювання формул певні посилання не змінювалися.
Для того щоб під час копіювання посилання у формулі не модифікувалося, потрібно перед номером стовпця та номером рядка додати символ $. Так посилання B10 буде модифікуватися, а посилання $В$10 — ні.
Якщо в посиланні символ $ додати тільки перед номером стовпця або номером рядка, наприклад $В10 або В$10, то під час копіювання такі посилання модифікуються частково: змінюється лише номер рядка або стовпця, біля якого не стоїть символ $.
Посилання, яке модифікується під час копіювання формули, називається відносним.
Посилання, яке не модифікується під час копіювання формули, називається абсолютним.
Посилання, у якому під час копіювання модифікується або номер стовпця, або номер рядка, називається мішаним.
Розглянемо приклад, який пояснює доцільність використання абсолютних посилань.
Нехай у клітинках діапазону СЗ:С7 (рис.) міститься кількість відмінників у трьох 10-х і двох 11-х класах школи, а в клітинці С8 обчислена їхня загальна кількість. У клітинках діапазону D3:D7 потрібно обчислити відсотки відмінників у кожному із цих класів по відношенню до загальної кількості відмінників у 10-х і 11-х класах.
Для обчислення цих відсотків у клітинках діапазону D3:D7 повинні знаходитися такі формули:
Якщо проаналізувати всі ці формули, то можна зробити висновок, що посилання в чисельнику змінюються, а посилання в знаменнику — ні. Тому, якщо ми хочемо увести в клітинку D3 формулу =СЗ/С8*100, а потім скопіювати її в клітинки діапазону D4:D7, то потрібно зробити так, щоб посилання СЗ під час копіювання формули модифікувалося, а посилання С8 — ні. Тобто формула в клітинці D3 повинна виглядати так: =С3/$С$8*100.
Змінити вид посилання у формулі під час її введення або редагування можна послідовним натисненням клавіші F4, коли дане посилання є поточним. При цьому види посилань змінюються по черзі: відносне — абсолютне — мішане.
Якщо у формулі для посилання на клітинку використати її ім'я, то під час копіювання цієї формули це посилання модифікуватися не буде.
Отже, ім'я клітинки у формулі є абсолютним посиланням на цю клітинку. Тому в розглянутому вище прикладі можна було клітинці С8 надати ім'я, наприклад, Відмінники, і тоді формула в клітинці D3 виглядала б так: =СЗ/Відмінники*100.

Практична робота № 7.
Використання формул в електронних таблицях.
Увага! Під час роботи з комп’ютером дотримуйтеся правил безпеки і санітарно-гігієнічних норм.
Створити електронну книгу «Практична робота №7», у папці Excel, та виконати в ній наступні дії:
Завдання 1.
1.Відкрийте файл Практична робота №6, Лист Завдання2.
2.Скопіюйте дану таблицю у нову книгу Практична робота №7.
3.Виконайте автообчислення для даної таблиці.
4.Збережіть таблицю (Лист Автообчислення).

Завдання 2.
1.Відкрийте файл Практична робота №7.
2.Перейдіть на Лист2.
3.Створіть таблицю, яка буде нагадувати собою товарний чек, де зафіксована купівля декількох найменувань (6- товарів. Вхідні дані: назву товару, ціну, кількість товарів задайте самостійно. Підрахуйте вартість кожного товару і загальну суму покупки.
4.Перейменуйте Лист2 в Товарний чек.
5.Збережіть таблицю у своїй папці.

Завдання 3.
1.Відкрийте файл Практична робота №7.
2.Перейдіть на Лист3.
3.Волинський молокозавод згідно з планом на 2012 рік повинен був випустити 4356000 штук морозива “Каштан”, 2345600 штук морозива “Їжачок” та 1278900 штук морозива “Геркулес”. Насправді ж було випущено на 8% менше морозива “Каштан”, 12% та 20% більше морозива “Їжачок” та “Геркулес” відповідно. Середня ціна становила відповідно 3 грн, 4 грн та 4грн 20 коп. за штуку. Собівартість відповідно 2 грн 50 коп., 3 грн 50 коп. та 3 грн 70 коп за штуку.
4.Сформуйте таблицю, де відобразіть дані, подані нижче, проведіть розрахунки обсягів продажу кожного сорту морозива та торгової виручки – в сумарному та процентному обсязі, а також валового доходу від реалізації. Валовий дохід = (Ціна – Собівартість)*Обсяг реалізації.

Назва
продукції Обсяги реалізації Ціна Собівартість Валова торгова виручка Валовий дохід
План Факт % План Факт План Факт
“Каштан”
“Їжачок”
“Геркулес”

5.Збережіть таблицю, закривши вікно програми.

Домашнє завдання:
1.Вивчити конспект.
2.Опрацювати матеріал на ст. 131-134, ст. 141-143. (Й.Я. Ривкінд, Т.І. Лисенко, Л.А. Чернікова, В.В. Шакотько).
3.Завершити виконання завдань практичної роботи.