Найпростіші формули Excel

МОДУЛЬ 2:

«СИСТЕМИ ОПРАЦЮВАННЯ ДАНИХ, ПОДАНИХ У ТАБЛИЦЯХ»

Клас: 11

Профіль: стандарт

Тема в модулі: «Використання найпростіших формул»

Викладач: Стефаниць Ірина Савівна, вчитель інформатики ЗОШ № 21 міста Маріуполя.

Успішне вивчення теми дозволить:

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

Ключові слова: формула, оператори Excel, пріоритет операторів, копіювання і переміщення формул, модифікація формул.

ІНФОРМАЦІЯ ДЛЯ ВИВЧЕННЯ

§1. Необхідність вивчення теми «Використання найпростіших формул».

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

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

§2. Створення та настроювання діаграм, вибір типу діаграми.

Формули в Excel 2007

У клітинки електронної таблиці, крім чисел і текстів, можна вводити формули.

Формула в Excel 2007 — це вираз, який задає порядок обчислення в електронній таблиці. Починається формула зі знака = і може містити числа, тексти, посилання на клітинки, знаки дій (оператори), дужки та функції.

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

Символ =, яким починається запис формули (цей символ вводиться до комірки із клавіатури або викликається клацанням по кнопці = у рядку форму
Оператори, тобто інструкції для виконання дій (наприклад,+,-,*).
Числа або текстові значення (наприклад, 0,12 або Доход).
Функції з набору вбудованих функційExcel (наприклад, СУММ або СOS).
Посилання на коміри і діапазони – ці компоненти присутні якщо, у формулу потрібно підставити значення, що містяться в інших комірках (наприклад А2, С3:С15).

Зазначимо ще раз, що Excel вважає формулою будь-який запис у комірці який починається із символу =. Однак запис формули можна почати і зі знаків + або -, при цьому наступний запис також буде сприйнятий як формула.

Наведемо приклади формул у комірках ЕТ:

=С1*В1

=СУМ(А3:А12)

=0,35 *D7+1. 4 * F7

Введення формул до ЕТ

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

Після команди введення запис формули в комірці зникає і з’являється результат її виконання або повідомлення про помилку. Відредагувати введену формулу можна як звичайний текст, введений до комірки. Для прикладу наберіть у комірці А4 формулу = А1+А2+А3. Потім відредагуйте її, замінивши на формулу =А1+А2*А3Немае значення, малими чи великими літерами набирається формула. Програма Excel автоматично перетворює всі літери на великі, як тільки подається команда на виконання обчислень.

Оператори Excel (арифметичні, порівняння тощо)

В електронних таблицях Excel є чотири види операторів: арифметичні, текстові, оператори порівняння й оператори посилань (адресні оператори).

Арифметичні оператори

Ці оператори призначені для виконання арифметичних операцій над числами. У Excel можуть застосовуватись сім арифметичних операторів (табл.)

Символ оператора	Назва оператора	Приклад формули	Результат
+	Додавання	=1,5+2,2	3,7
-	Віднімання	=6-5,5	0,5
-	Заперечення	=-33	-33
/	Ділення	=4/5	0,8
*	Множення	*=56**	30
^	Піднесення до степеня	=3^2	9
%	Відсоток	=25%	0,25

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

Оператори порівняння

У роботі з числами і текстом застосовуються оператори порівняння (табл. 51.2). Вони використовуються для надання твердженням значень ИСТИНА або ЛОЖЬ. Якщо твердження правильне, то комірці, що містить формулу, буде надано значення ИСТИНА. Якщо ж твердження неправильне, то до комірки буде занесене значення ЛОЖЬ.

Символ оператора	Назва оператора	Приклад формули	Результат
=	Дорівнює	=2=3	ЛОЖЬ
>	Більше	=2>3	ЛОЖЬ
<	Менше	=2<3	ИСТИНА
>=	Більше або дорівнює	=2>=3	ЛОЖЬ
<=	Менше або дорівнює	=2<=3	ИСТИНА
< >	Не дорівнює	=2<>З	ИСТИНА

Текстовий оператор

В Excel є один текстовий оператор & або, інакше кажучи, амперсанд. Цей оператор об'єднує послідовності символів із різних комірок в одну послідовність, тому його називають також оператором об'єднання.

Нехай до комірок А1 і А2 введені слова «по» і «тяг» відповідно, а до комірки A3 записана формула =А1&А2, тоді результатом у комірці A3 буде слово "потяг".

Текстові значення у формулах беруть у лапки "...". Наприклад, щоб скласти ім'я файла Config.sys зі змісту двох комірок СІ і С2, що мають значення Config і sys відповідно, потрібно записати формулу:

=С1&"."&С2

Адресні оператори

Адресні оператори використовуються при вказівці посилань на комірки таблиці. Нагадаємо, що прямокутні діапазони комірок позначаються за допомогою двокрапки, наприклад, A3: F7 або JJ12: JN17. Koли в посиланні потрібно об'єднати два діапазони комірок, то діапазони записуються через кому, наприклад, A3:F7, В6:K1З. Отже, вираз =СУМ(A3:F7,B6:K13,C5) означає додавання комірок прямокутних діапазонів A3: F7 і В6: К13, а також комірки С5.

Двокрапка і кома, що використовуються при записуванні посилань на комірки, саме і є адресними операторами. Ще один адресний оператор у Excel це пробіл. За допомогою пробілу можна посилатися на ділянку перетину двох діапазонів. Наприклад, запис =СУМ(Fl:F8_F5:F13) означатиме додавання комірок від F5 до F8 (тут символ _ позначає порожній пробіл, а не підкреслення).

Пріоритет операторів

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

Адресні оператори (:, <пробіл>).
Заперечення (використовується для вказівки від'ємних чисел: -1, -0,23 тощо).
Відсоток (%).
Піднесення до степеня (^).
Множення і ділення (* і / ).
Додавання і віднімання ( + і -).
Об'єднання послідовностей символів (&).
Оператори порівняння(=,<,>,<=,>=, <>).

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

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

для позначення арифметичних дій використовуються такі оператори: «+» додавання, « — » віднімання, «*» множення, « / » — ділення;
для позначення дії піднесення до степеня використовується оператор ^;
для позначення дії знаходження відсотків використовується оператор %; наприклад формула знаходження 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. У цьому випадку, якщо імена папки, файлу або аркуша містять пропуски, то ніяких додаткових лапок ставити не потрібно.

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

Під час уведення формули, яка містить ім'я клітинки, можна виконати Формули => Визначені імена => Використовувати у формулі і вибрати потрібне ім'я зі списку, що відкриється, або виконати Формули => Визначені імена => Використовувати у формулі => Вставити імена і вибрати потрібне ім'я у вікні Вставлення імені.

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

При використанні формул в клітинках електронної таблиці можуть з'являтися повідомлення про помилки (табл.).

Повідомлення	Причина помилки
#####	Стовпець недостатньо широкий для відображення числа
#DIV/0!	Спроба поділити на нуль
#VALUE!	У формулі для математичних обчислень міститься посилання на клітинку, вмістом якої є текст
#REF!	Не існує клітинок, посилання на які використовуються у формулі
#N/A	Відсутні дані для обчислень

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

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

Як приклад розпишемо множення двох діапазонів-стовпців. Запишіть у діапазони А1:А6 і В1:В6 числа, а до комірки С1 занесіть формулу =А1*В1

Пересягніть маркер заповнення з комірки С1 до комірки, що розташована нижче, С2:С6. Результат множення двох стовпців буде поміщений у стовпець С.

Модифікація формул.

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

Нехай у клітинці Е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, то під час копіювання такі посилання модифікуються частково: змінюється лише номер рядка або стовпця, біля якого не стоїть символ $.

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

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

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

Щоб змінити відносне посилання на абсолютне, не обов'язково вручну ставити знаки $. Це робиться простіше за допомогою клавіші F4. Введіть відносне посилання до формули, а потім за допомогою послідовних натискань F4 оберіть форму запису посилання з ряду типу Al - $A$1 - а$1 - $а1.

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

Нехай у клітинках діапазону СЗ:С7 (рис.) міститься кількість відмінників у трьох 10-х і двох 11-х класах школи, а в клітинці С8 обчислена їхня загальна кількість. У клітинках діапазону D3:D7 потрібно обчислити відсотки відмінників у кожному із цих класів по відношенню до загальної кількості відмінників у 10-х і 11-х класах.

Для обчислення цих відсотків у клітинках діапазону D3:D7 повинні знаходитися такі формули:

Я кщо проаналізувати всі ці формули, то можна зробити висновок, що посилання в чисельнику змінюються, а посилання в знаменнику — ні. Тому, якщо ми хочемо увести в клітинку D3 формулу =СЗ/С8*100, а потім скопіювати її в клітинки діапазону D4:D7, то потрібно зробити так, щоб посилання СЗ під час копіювання формули модифікувалося, а посилання С8 — ні. Тобто формула в клітинці D3 повинна виглядати так: =С3/$С$8*100.

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

Якщо у формулі для посилання на клітинку використати її ім'я, то під час копіювання цієї формули це посилання модифікуватися не буде.

Отже, ім'я клітинки у формулі є абсолютним посиланням на цю клітинку. Тому в розглянутому вище прикладі можна було клітинці С8 надати ім'я, наприклад, Відмінники, і тоді формула в клітинці D3 виглядала б так: =СЗ/Відмінники*100.

Питання для самоперевірки

Що таке формула?
З яких компонентів може складатися запис формули?
Як вводиться формула в ЕТ?
Які типи операторів в Excel вам відомі?
Назвіть арифметичні оператори й оператори порівняння.
Наведіть приклад використання текстового оператора.
Для чого призначені адресні оператори?
Назвіть оператори Excel у порядку їх пріоритету.
Яких правил потрібно дотримуватися під час уведення формул?
Як записується посилання на клітинку, яка знаходиться на цьому самому аркуші цієї самої книги; на іншому аркуші цієї самої книги?
Як записується посилання на клітинку, яка знаходиться в іншій книзі, відкритій або невідкритій?
У чому полягають переваги використання посилань у формулах?
Як виконати копіювання формули?
Що таке відносні, абсолютні й мішані посилання?
Що таке модифікація формул? Коли і як вона відбувається?
Які повідомлення про помилки можуть з'являтися в клітинках? Про яку помилку повідомляє кожне з них?

ДОДАТКИ

ДОДАТОК№ 1

ЗАВДАННЯ

Задача 1.

Створіть і оформіть таблицю. Стовпець автомобілі заповните за допомогою автозаповнення (наберіть «автомобіль № 1» і розтягніть вниз), відстані вирахуйте за допомогою формули. Зверніть увагу на запис значень часу у вигляді звичайного дробу — ціла частина відокремлена від дробової пропуском. Підрахуйте суму значень в усіх числових стовпцях:

Задача 2.

Знайдіть суму квадратів усіх цілих чисел від 20 до 30 (квадрати чисел вичислені по формулі) :

Задача 3.

Розрахуйте відомість виконання плану товарообігу кіоску №5 за формою:

№	Місяць	Звітний рік	Відхилення від плану
		план	фактично	виконання %
і	M_i	P_i	F_i	V_i	O_i
1	Січень	7 800,00 грн	8 500,00 грн
2	Лютий	3 560,00 грн	2 700,00 грн
3	Березень	8 900,00 грн	7 800,00 грн
4	Квітень	5 460,00 грн	4 590,00 грн
5	Травень	6 570,00 грн	7 650,00 грн
6	Червень	6 540,00 грн	5 670,00 грн
7	Липень	4 900,00 грн	5 430,00 грн
8	Серпень	7 890,00 грн	8 700,00 грн
9	Вересень	6 540,00 грн	6 500,00 грн
10	Жовтень	6 540,00 грн	6 570,00 грн
11	Листопад	6 540,00 грн	6 520,00 грн
12	Грудень	8 900,00 грн	10 000,00 грн

Заповнення стовпця M_i можна виконати протяганням маркера. Значення стовпців V_i і O_i обчислюються по формулах: V_i=F_i / P_i; O_i=F_i - P_i

Задача 4.

Побудуйте на проміжку [- 2, 2] з кроком 0,4 таблицю значень функції

Задача 5.

Скласти таблицю множення. Для заповнення таблиці використовуються формули і абсолютні посилання.

Таблиця множення

	1	2	3	4	5	6	7	8	9
1	1	2	3	4	5	6	7	8	9
2	2	4	6	8	10	12	14	16	18
3	3	6	9	12	15	18	21	24	27
…
9	9	18	27	36	45	54	63	72	81

Задача 7.

Створіть таблицю обліку товарів, порожні стовпці злічіть по формулах.

курс долара	26,80
Таблиця обліку проданого товарів
№ п\п	Назва	Поставлено	Продано	Залишок	ціна в доларах за 1 товар	ціна в гривнях за 1 товар	всього в гривнях
1	товар 1	50	43		170
2	товар 2	65	65		35
3	товар 3	50	43		56
4	товар 4	43	32		243
5	товар 5	72	37		57
Всього

Додаток№2

Приклад використання табличного процесора Excel

для розв'язку завдань по побудові діаграм

Задача.

Розрахуйте ціну товарів у прайс-листі магазину «НАТАЛІ» відповідно до курсу долара.

Хід виконання

Створити нову книгу та зберегти її у файлі. На першому аркуші побудуйте таблицю за зразком, наведеним нижче.

	Прайс-лист магазину "НАТАЛІ"

	Курс долара	26,8 грн

Найменування товару	Ціна в у.о.	Ціна в грн
Зошит в клітинку	$0,20	5,36₴
Зошит в лінієчку	$0,20	5,36₴
Пенал	$2,00	53,60₴
Ручка	$0,50	13,40₴
Олівець	$0,20	5,36₴
Лінійка	$0,30	8,04₴
Гумка	$0,40	4,02₴

Виділіть клітинку В1 і введіть до неї заголовок таблиці Прайс-лист магазину "НАТАЛІ"
У клітинку В3 введіть слова «Курс долара», в С3 - курс долара на сьогодні.
До клітинки С3 застосуйте грошовий формат (Формат  Формат клітинок  Вкладка Число  Числовий формат  Грошовий ( Позначення можна вибрати довільне).
У клітинки А5 : В5 введіть заголовки стовпців таблиці.
Виділіть їх і застосуйте напівжирний стиль зображення і більший шрифт.
У клітинки А6 : А12 і В6 : В12 введіть дані.
У клітинку С6 введіть формулу: = В6*$C$3. ($ означає, що використовується абсолютне посилання).
Виділіть клітинку С6 і протягніть за маркер заповнення вниз до осередку С13.
Виділіть діапазон клітинок С6 : С13 і застосуйте до них грошовий формат.

Задача.

Створити і заповнити таблицю розрахунку вартості, показану на малюнку.

	A	B	C	D	E
1	Вартість програмного забезпечення
2	Найменування	Вартість $	Вартість, грн	Вартість, Євро	Доля в загальній вартості %
3	OC Windows	18
4	Пакет MS Office	32
5	Редактор Corel Draw	15
6	Графічний прискорювач 3D	22
7	Бухгалтерія 1С	50
8	Антивірус DR Web	20
9	Разом	0
10	Курс валюти(до гривни)	28		35

Хід виконання

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

КОНТРОЛЬНО-ОЦІНЮЮЧИЙ БЛОК

Тест по темі : «Створення та настроювання діаграм, вибір типу діаграми»

Завдання

Вірні

Відповіді

Завдання #1

Запитання:

Вираз, який задає порядок обчислення в електронній таблиці – це ...

Запишіть відповідь:__________________________________________

Формула

Завдання #2

Запитання:

З якого символу розпочинається формула в Microsoft Excel?

«+»

+ =

Виберіть один із 5 варіантів відповідей:

1) @

2) =

3) $

4) +

5) &

Завдання #3

Запитання:

Що означає вміст клітинки «#####»?

Виберіть один із 5 варіантів відповідей:

1) ділення на нуль

2) число в клітинці не відповідає допустимому числовому формату

3) ширина клітинки не відповідає формату числа

4) не існує клітинок, посилання на які використовуються у формулі

5) відсутні дані для обчислень

Завдання #4

Запитання:

Які з приведених нижче виразів можуть бути формулами Excel?

Виберіть декілька із 5 варіантів відповідей:

=$R1
=$C$45/A1+4
A5*$C6
*F12+D6
=F12+D6$

1, 2

Завдання #5

Запитання:

Який результат відобразиться в клітинці C4 при копіюванні в неї формули Excel =A2*B$1 з клітинки B2?

Зображення:

	A	B	C	D
1	1	2	3
2	2	4	12
3	3	6	18
4	4	8
5

Запишіть число: ___________________________

Завдання #6

Запитання:

Які компоненти може містити формула?

Виберіть декілька із 5 варіантів відповідей:

1) символ @

2) символ =

3) оператори

4) рядки і стовпці

5) посилання

2, 3, 5

Завдання #7

Запитання:

Виберіть правильну формулу для обчислення y=x³-3x

Виберіть один із 4 варіантів відповідей:

1) x*3-3x

2) x^3-3x

3) =x^3-3x

4) =x^3-3*x

Завдання #8

Запитання:

У формулу треба ввести посилання на клітинку. Що для цього треба зробити?

Виберіть один із 4 варіантів відповідей:

Клацнути по потрібній клітинці лівою кнопкою миші
Натиснути клавішу F4
У рядку формул викликати контекстне меню
Записати значення цієї клітинки

Завдання #9

Запитання:

Вміст активної клітинки відображається в:

Виберіть один із 5 варіантів відповідей:

буфері обміну
рядку стану
заголовку вікна програми
рядку формул
полі імені

Завдання #10

Запитання:

У якому випадку ім'я листа робочої книги, що адресується, поміщене в одинарні лапки:

='[Книга2]Фінансовий звіт'!$A$1+'[Книга2]Фінансовий звіт'!$B$1

Виберіть один із 4 варіантів відповідей:

1) Ім'я листа містить пропуск

2) Робоча книга, що містить лист, що адресується, закрита

3) Ім'я робочого листа використовується у формулі двічі

4) Ім’я листа написане не англійською мовою

Задание #11

Запитання:

У клітинці Е5 записана формула =В3+С4. Як виглядатиме ця формула, якщо її скопіювати в клітинку:

а) Е6

б) А5

в) І7

г) С2

Укажіть відповідність для всіх 4 варіантів відповідей:

1) ___=#ССЫЛКА!+#ССЫЛКА!

2) ___=F5+G6

3) ___=В4+С5

4) ___=#ССЫЛКА!+A1

Задание #12

Запитання:

Виберіть усі формули в яких є абсолютне посилання.

Виберіть декілька із 5 варіантів відповідей:

=$F$6
=58+#G$78
=3*$B9
=47/$D$3
=Q$31+5*A4

1, 4

Задание #13

Запитання:

Вкажіть назву посилання на клітинку відповідно до його відображення у формулі:.

Укажіть відповідність для всіх 5 варіантів відповідей:

1) __________ = $E5/2

2) __________ =H6^4+B4

3) __________ =5*$G$8

мішане

відносне

абсолютне

Задание #14

Запитання:

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

Виберіть один із 5варіантів відповідей

1) автозміна

2) модифікація

3) автопосилання

4) пореміщення

5) форматування

Задание #15

Запитання:

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

Укажіть порядок дотримання всіх 5 варіантів відповідей:

__ Зробити цю клітинку поточною.

__ Натиснути ліву кнопку миші та, утримуючи її натиснутою, виділити потрібний діапазон клітинок.

__ Увести дані в першу клітинку діапазону.

__ Навести вказівник на маркер заповнення (при цьому вказівник виглядатиме як ч орний хрестик .

__ Відпустити ліву кнопку миші.

Запитання	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
Бали	1	1	1	2	2	2	1	1	1	1	2	2	2	1	1

Система оцінювання тесту

Оцінка	1	2	3	4	5	6	7	8	9	10	11	12
Необхідний мінімум % балів	0	8	17	25	33	42	50	58	67	75	83	92

ІНФОРМАЦІЙНЕ ЗАБЕЗПЕЧЕННЯ

Література.

Й.Я. Ривкінд, Т.І. Лисенко, Л.А. Чернікова, В.В. Шакотько, Інформатика 11 клас, Рівень стандарт – Київ, «Генеза», 2011
О.Ю. Гаєвський. Інформатика 7-11 кл.: Навч. посіб. – К.: А.С.К., 2008
Д.М. Златопольский, 1700 заданий по MicrosoftExcel – БХВ, 2003

‹

›

	1	2	3	4	5	6	7	8	9
1	1	2	3	4	5	6	7	8	9
2	2	4	6	8	10	12	14	16	18
3	3	6	9	12	15	18	21	24	27
…
9	9	18	27	36	45	54	63	72	81

	1	2	3	4	5	6	7	8	9
1	1	2	3	4	5	6	7	8	9
2	2	4	6	8	10	12	14	16	18
3	3	6	9	12	15	18	21	24	27
…
9	9	18	27	36	45	54	63	72	81

	1	2	3	4	5	6	7	8	9
1	1	2	3	4	5	6	7	8	9
2	2	4	6	8	10	12	14	16	18
3	3	6	9	12	15	18	21	24	27
…
9	9	18	27	36	45	54	63	72	81