Тема: Табличний процесор MS Excel. Використання стандартних функцій.

Мета роботи: навчитися проводити розрахунки у табличному процесорі MS Excel за допомогою стандартних функцій.

 

Задача №1. Розрахувати суму знижки товару. Якщо кількість придбаного товару більш 3 шт., то сума знижки складає 10 %.

 

Методичні вказівки.

1. Розрахувати Суму придбаного товару:

1.1. в чарунці D3 створити формулу: =В3*С3;

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

2. Розрахувати Суму знижки придбаного товару:

2.1. в чарунці Е3 створити формулу: =Если(В3>3;D3*0,1;0);

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

3. Розрахувати Суму з урахуванням знижки:

3.1. в чарунці F3 створити формулу: = D3-E3;

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

 

Задача №2. Розрахувати суму знижки товару. Якщо кількість придбаного товару більше 3 шт., то сума знижки складає 10 %. Якщо кількість придбаного товару більше 10 шт., то сума знижки - 15%.

 

Методичні вказівки.

1. Розрахувати Суму знижки придбаного товару:

1.1. в чарунці Е3 створити формулу: =Если(В3<=3;0;Если(В3>10;D3*0,15;D3*0,1));

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

 

Задача №3. Розрахувати суму знижки товару. Якщо покупець придбав більше 3 дверей, то йому надається знижка у розмірі 5%. Якщо покупець придбав більше 4 вікон, йому надається знижка у розмірі 10%.

Методичні вказівки.

1. Розрахувати Суму знижки придбаного товару:

1.1. в чарунці Е3 створити формулу:

=Если(И(А3=”Двері”;В3>3);D3*0,05;Если((И(А3=”Вікно”;В3>4);D3*0,1;0));

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

 

Задача №4. Розрахувати в таблиці максимальні і мінімальні значення

Методичні вказівки.

1. Зробити активною чарунку В12.

2. Визвати майстер функцій , зліва у вікні вибрати категорію Статистичні праворуч вибрати функцію МАКС.

3. Клацнути на кнопці звернути , виділити діапазон В3:В10.

4. Розвернути вікно майстра функцій клацнувши на кнопці . Клацнути на кнопці ОК.

Самостійно розрахувати максимальне значення у чарунці Е12. Можна скопіювати формулу використовуючи маркер заповнення.

5. Зробити активною чарунку В13.

6. Визвати майстер функцій , зліва у вікні вибрати категорію Статистичні, праворуч вибрати функцію МИН.

7. Клацнути на кнопці звернути , виділити діапазон В3:В10.

8. Розвернути вікно майстра функцій клацнувши на кнопці . Клацнути на кнопці ОК

Самостійно розрахувати мінімальне значення у чарунці Е13. Можна скопіювати формулу використовуючи маркер заповнення.

 

Задача №4. Розрахувати в таблиці кількість знижок.

Методичні вказівки.

1. Зробити активною чарунку Е14.

2. Визвати майстер функцій , зліва у вікні вибрати категорію Статистичні праворуч вибрати функцію СЧЕТЕСЛИ.

3. Клацнути на кнопці звернути , виділити діапазон Е3:Е10.

4. Розвернути вікно майстра функцій клацнувши на кнопці .

5. У полі Условие ввести >0

6. Клацнути на кнопці ОК.

Індивідуальні завдання для виконання лабораторної роботи №6

1. Заповніть даними і розрахуйте таблицю згідно варіанту.

2. Створіть автосписок для заповнення першої або другої колонки таблиці.

3. Скопіюйте таблицю на Лист 2. Встановіть режим виводу формул.

4. Перейменуйте Лист 2 надавши йому ім’я Формули.

5. Підготуйте створені документи до друку у режимі Предварительный просмотр:

- орієнтація сторінок – альбомна;

- колонтитули: верхній - дата створення, нижній – прізвище студента і група.

6. Роздрукуйте створені документи.

7. Збережіть файл на диску, надавши йому ім’я (прізвище студента).

Варіант №1. Якщо покупець зробив покупку на суму більш ніж 200 грн., то йому надається дисконт у розмірі 5%, якщо товар, придбаний на суму більше 100 грн. і менш ніж 200 грн., дисконт складає 2%, інакше дисконт не надається.

№	Товар	Товарна група	Кількість	Ціна	Дисконт	Сума з урахуванням дисконту
1.						*
						*
10.						*
Мінімальна кількість	*
Загальна сума	*

 

Варіант №2. Якщо дотримані строки сплати, що вказані у договорі, штрафні санкції не накладаються. Якщо прострочення платежу складає від 30 до 60 днів, то нараховується штраф у розмірі 0,1% від суми, що вказана у контракті за кожний прострочений день. Більш ніж 60 днів - 0,2% від суми, що вказана у контракті за кожний прострочений день.

№	Дата складання договору	Найменування фірми	Кількість прострочених днів	Розмір штрафу
1.				*
				*
10.				*
Разом	*
Середне значення розміру штрафу		*

 

Варіант №3. У випадку укладання контракту на суму більше ніж 100000 співробітник отримує бонус у розмірі 1% від суми, яка вказана у контракті. Якщо сума контракту складає від 10000 до 100000, то бонус дорівнює 0,5% від загальної суми контракту. Інакше бонуси не передбачені.

№	П.І.Б. співробітника	Сума контракту	Розмір бонусу
1.			*
			*
10.			*
Загальна сума	*	*
Кількість співробітників, які не отримали бонуси	*

Варіант №4. Розрахувати ціну товару з урахуванням акцизного збору, який складає для ювелірних виробів з дорогоцінних металів – 35%, для високоякісних виробів з фарфору – 30%, для виробів з кришталю, виготовлених ручним способом – 45%.

№	Наймену­вання товару	Товарна група	Ціна	Акцизний збір	Ціна з урахуванням акцизного збору
1.					*
					*
10.					*
Разом	*
Середнє значення	*	*

 

Варіант №5. Якщо студент набирає по модулю від 90 до 100 балів, то він отримує оцінку “відмінно”, від 75 до 89 балів - “добре”, від 60 до 74 - “задовільно”. “Незадовільно” отримує студент який не набрав навіть 60 балів. Визначте оцінку за національною шкалою, згідно отриманим балам.

 

№	П.І.Б.	Група	Предмет	Кількість балів	Оцінка
1.					*
					*
10.					*
Кількість студентів які набрали менше 60 балів	*
Середня оцінка		*

 

Варіант №6. У магазині оголошена святкова акція. На дитячі товари надається знижка 30%, на товари для жінок – 15% і ще 10% знижки, якщо сума покупки перевищує 500 грн. Визначити суму придбаного товару з урахуванням знижки

№	Товар	Товарна група	Сума до знижки	Сума після знижки
1.				*
				*
10.				*
Загальна сума	*
Максимальна сума після знижки	*

Варіант №7. Для співробітників, які мають вищу освіту, премія до свята складає 100 грн., з середньою освітою – 70 грн., без освіти – 50 грн. Премія нараховується лише тим робітникам, хто виконав план за рік.

№	П.І.Б.	Освіта	Відмітка про виконання плану	Сума премії
1.				*
				*
10.				*
Разом	*
Максимальна премія	*

 

Варіант №8. Якщо співробітник має дітей, то йому нараховується премія до новорічних свят у розмірі 50 гривень на кожну дитину. Виплата розповсюджується на співробітників, стаж роботи яких перевищує 5 років.

№	П.І.Б.	Стаж роботи	Кількість дітей	Розмір премії
1.				*
				*
10.				*
Кількість співробітників, які мають дітей	*
Середній розмір премії	*

 

Варіант №9. У випадку укладання контракту на суму більше ніж 250000гр. співробітник отримує бонус у розмірі 3% від суми, яка вказана у контракті. Якщо сума контракту складає від 150000 до 250000, то бонус дорівнює 1,5% від загальної суми контракту. Інакше бонуси не передбачені.

№	П.І.Б. співробітника	Сума контракту	Розмір бонусу
1.			*
			*
10.			*
Загальна сума	*	*
Максимальна сума бонусу	*

Варіант №10. У магазині оголошена акція. На товари з товарної групи галантерея надається знижка 30%, на товари товарної групи побутова хімія – 15% і ще 5% знижки, якщо сума покупки перевищує 300 грн. Визначить суму товару після знижки.

 

№	Товар	Товарна група	Сума до знижки	Сума після знижки
1.				*
				*
10.				*
Загальна сума	*	*
Мінімальна сума після знижки	*

 

Варіант №11. Для співробітників, які мають вищу освіту, премія до свята складає 200 грн., з середньою освітою – 100 грн., без освіти – 50 грн. Додаткова премія у розмірі 150 грн. нараховується тим робітникам, хто перевиконав план за рік.

№	П.І.Б.	Освіта	Відмітка про виконання плану	Сума премії
1.				*
				*
10.				*
Разом	*
Максимальна премія	*

 

Варіант №12. У магазині верхнього одягу оголошена акція. Покупцю, який придбав два або більше хутрових виробів надається знижка у розмірі 30%. Для покупця, що придбав хоча б одну річ з натуральної шкіри надається знижка у розмірі 20%. Розрахувати суму покупки з урахуванням знижки.

 

№	Найменування товару	Товарна група	Ціна	Кількість	Сума з урахуванням знижки
1.					*
					*
10.					*
Загальна сума	*	*
Кількість проданих хутрових виробів	*

 

Варіант №13. Розрахувати ціну автомобілів з урахуванням акцизного збору, який складає для легкових та пасажирських автомобілів марок ЛуАЗ, ЗАЗ-968, ЗАЗ-968М – 10%, для інших марок – 35%.

№	Марка	Ціна	Акцизний збір	Ціна з урахуванням акцизного збору
1.			*	*
			*	*
10.			*	*
Мінімальна ціна	*
Максимальна ціна	*

 

Варіант №14. Позика у розмірі 3000 гривень надається співробітнику підприємства, якщо стаж його роботи перевищує 10 років і він виконав план за останній рік. Якщо співробітник відпрацював від 5 до 10 років і також виконав план, йому надається позика у розмірі 1000 гривень, інакше позика не надається.

№	П.І.Б.	Процент виконання плану за останній рік	Стаж роботи	Розмір позики
1.				*
				*
10.				*
Разом	*
Максимальний стаж роботи	*

Варіант №15. Якщо початковий внесок у банк зроблено в національній валюті, то банківський відсоток дорівнює 8%, якщо у російських рублях, то 3% і 5%, якщо внесок зроблено у американських доларах. Мінімальна сума внеску – 1000 грн.

№	П.І.Б. клієнту	Сума внеску	Валюта	Банківський процент
1.				*
				*
10.				*
Середня сума внеску	*
Загальна сума внеску	*

 

Контрольні питання за темою “Табличний процесор MS Exсel”

1. Табличний процесор MS Excel. Основні поняття.

2. Що таке адреса чарунки? Де відображається адреса активної чарунки?

3. Маркер заповнення. Створення автосписків. Автозаповнення.

4. Робота з листами. Переміщення по листу.

5. Поняття діапазону. Види діапазонів? Виділення діапазонів. Виділення несуміжних діапазонів.

6. Типи даних у чарунках. Як вирівнюються різні типи даних у чарунках?

7. Форматування чарунок. Редагування вмісту чарунок.

8. Робота з формулами. Діагностика помилок у формулах. Типи посилань у формулах. Як вивести таблицю у режимі формул?

9. Діаграми. Створення діаграм.

10. Форматування діаграм. Як додати легенду до створеної діаграми? Як змінити вид діаграми?

11. Що таке зведена таблиця? Для чого потрібні зведені таблиці? Створення зведених таблиць. Як вибрати математичну операцію для поля, що обчислюється? Як відновити дані в зведеній таблиці?

12. Як побудувати діаграму по зведеній таблиці? Як змінити вид діаграми?

13. Функція. Види функцій. Формати логічних функцій.

14. Знайдіть у формулі помилки =ЕСЛИ((В3 “долар;S4*5,05;S4*6,2

15. Друк таблиць. Зміна орієнтації таблиці. Як центрувати таблицю для друку. Як додати колонтитули.

 

Література

1. Додж М., Стинсон К., Эффективная работа с Excel 2000 - СПб: Издательство «Питер», 2000. – 1056 с.

2. Інформатика та комп’ютерна техніка: Навчальний посібник. – К.: НМЦ “Укоопосвіта”, 2000 – 335 с.

3. ЭЙТКЕН П. Microsoft Word 2000: Пер. с англ.. - М., СПб., Киев: Вильямс, 2000. - (Освой самостоятельно!). - 208 с.

4. ВЕЙССКОПФ ДЖ. Excel 2000: Базовый курс/Пер. с англ.. - Киев, М., СПб.: Век, Энтроп, Корона- Принт, 2000. - 400 с.

 

№ п/ч	Щоб виділити	Виконайте дії
1.	Слово	Двічі клацніть слово.
2.	Рисунок	Клацніть рисунок.
3.	Речення	Утримуючи натиснутою клавішу CTRL, клацніть на реченні.
4.	Рядок тексту	Перемістіть покажчик на смугу виділення^***перед реченням і клацніть кнопкою миші.
5.	Абзац	Перемістіть покажчик на смугу виділення^***перед абзацом, і двічі клацніть кнопкою миші. Інший спосіб: тричі клацніть абзац.
6.	Кілька абзаців	Перемістіть покажчик на смугу виділення^*** перед абзацом, і двічі клацніть кнопкою миші, а потім перетягніть покажчик нагору чи вниз.
7.	Невеликий фрагмент тексту	Використовуйте перетаскування. Установіть курсор у початок фрагмента, натисніть клавішу SHIFT і утримуючи її натискайте відповідні клавіші управління курсором ­®¯
8.	Великий блок тексту	Клацніть початок фрагмента, прокрутіть документ так, щоб на екрані з'явився кінець фрагмента, а потім клацніть його, утримуючи натиснутою клавішу SHIFT. Інший спосіб. Встановіть курсор у початок фрагмента, натисніть клавішу SHIFT і утримуючи її натискайте відповідні клавіші управління курсором ­®¯
9.	Весь документ	Перемістіть покажчик на смугу виділення^*** перед текстом, після чого тричі клацніть кнопкою миші.
10.	Колонтитули	У звичайному режимі виберіть Вид - Колонтитули. У режимі розмітки двічі клацніть неяскравий текст колонтитула. Перемістіть покажчик на смугу виділення^*** перед колонтитулом, після чого тричі клацніть кнопкою миші.
11.	Вертикальний блок тексту (крім тексту усередині чарунки таблиці)	Утримуючи натиснутою клавішу ALT, перетягніть покажчик.

 

^***Смуга виділення - ліве поле документа. На смузі виділення покажчик миші приймає вид білої стрілки, спрямованої вправо.

 

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

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

† #ИМЯ? – неможливість розпізнати ім'я, яке використовується. Це значення помилки виникає, коли неправильно зазначене ім'я об'єкта або мається посилання на ім'я, що було вилучено; коли невірно записана функція; коли при записі адрес замість латині використані кирилиця і т.ін.

† #ЗНАЧ! – спроба некоректного використання функції. Звичайною помилкою є невідповідність даних установленому формату, наприклад, замість числа або дати в аргументі записано текст. Це ж значення помилки буде з'являтися, коли для функції або оператора, що вимагають одного значення аргументу, записують декілька.

† #ЧИСЛО! – значення помилки, що означає проблему, зв'язану з представленням або з використанням чисел. Не виключено, що у функції з числовим аргументом використовується аргумент нечислового формату. Можливо також, що в чарунку уведена формула, що повертає занадто велике значення по модулю (понад 10³⁷).

† #ССЫЛКА! – означає наявність проблеми з інтерпретацією посилань, що маються у формулі. Можливо, що формула містить посилання на чарунку, що уже вилучена, чи посилання на чарунку, у яку скопійований вміст інших чарунок.

† #ДЕЛ/0! – спроба ділення на нуль. Така ситуація частіше виникає не через те, що в чарунку записано явне ділення на нуль (оператор /0), а як наслідок використання посилання на порожню чарунку або чарунку, що містить нульове значення.

† #ПУСТО! – значення помилки, що з'являється у випадку завдання в посиланні порожньої безлічі чарунок.

† #Н/Д – скорочення від терміна «невизначені дані». Це значення помилки звичайно спеціально вводиться в чарунки, щоб запобігти обчислення, що не можуть бути зроблені через відсутність даних.

Клавіші	Переміщення
<Home>	У початок поточного рядка
<Ctrl+Home>	У клітинку A1
<Ctrl+End>	В останню заповнену клітинку таблиці
<­­>	На одну клітинку вверх
<¯>	На одну клітинку вниз
<®>	На одну клітинку вправо
<>	На одну клітинку вліво
<Ctrl+­­>	Вверх до першої заповненої клітинки
<Ctrl+¯>	Вниз до першої заповненої клітинки
<Ctrl+®>	Вправо до першої заповненої клітинки
<Ctrl+>	Вліво до першої заповненої клітинки
<Page Up>	Вверх на один екран
<Page Down>	Вниз на один екран
<Alt+Page Up>	Вліво на один екран
<Alt+Page Down>	Вправо на один екран
<Ctrl+Page Up>	До попереднього листа робочої книги
<Ctrl+Page Down>	До наступного листа робочої книги

 

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

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

ü Для заміни вмісту чарунки виберіть потрібну чарунку та введіть нове значення.

ü Для часткової зміни вмісту чарунки можна, обравши її, натиснути клавішу < F2 >, або клацнути мишею у рядку формул, або двічі клацнути безпосередньо на чарунці.

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

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

Абсолютне посилання задає абсолютні координати клітинки у робочому листі (щодо лівого верхнього кута таблиці). Можна наказати Excel інтерпретувати номери рядка та (або) стовпчика як абсолютні шляхом введення символу долара ($) перед іменами рядка та (або) стовпчика. Наприклад, $A$7. При переміщенні або копіюванні формули абсолютне посилання на клітинку (або діапазон клітинок) змінене не буде, і на новому місці скопійована формула буде посилатися на ту ж саме клітинку (діапазон клітинок).

 

Натискання <F4>	Адреса	Посилання
Один раз	$A$7	Абсолютне посилання
Два рази	A$7	Абсолютне посилання на рядок
Тричі	$A7	Абсолютне посилання на стовпчик
Чотири рази	A7	Відносне посилання

 

 

Навчальне видання