МІНІСТЕРСТВО АГРАРНОЇ ПОЛІТИКИ УКРАЇНИ. Комп ’ ютери та комп ’ ютерні технології

МІНІСТЕРСТВО АГРАРНОЇ ПОЛІТИКИ УКРАЇНИ

ПОЛТАВСЬКА ДЕРЖАВНА АГРАРНА АКАДЕМІЯ

 

Кафедра вищої математики

 

Комп ’ ютери та комп ’ ютерні технології

Електронні таблиці Microsoft Excel

 

 

Методичні вказівки і завдання до лабораторних робіт

для студентів інженерно-технологічного факультету

 

Видання друге, перероблене і доповнене

 

 

Полтава 2008

МІНІСТЕРСТВО АГРАРНОЇ ПОЛІТИКИ УКРАЇНИ

ПОЛТАВСЬКА ДЕРЖАВНА АГРАРНА АКАДЕМІЯ

 

Кафедра вищої математики

 

 

Комп ’ ютери та комп ’ ютерні технології

Електронні таблиці Microsoft Excel

 

Методичні вказівки і завдання до лабораторних робіт

для студентів інженерно-технологічного факультету

 

Видання друге, перероблене і доповнене

 

 

Полтава 2008

 

КОМП’ЮТЕРИ ТА КОМП’ЮТЕРНІ ТЕХНОЛОГІЇ. Електронні таблиці Microsoft Excel . Методичні вказівки і завдання до лабораторних робіт для студентів інженерно-технологічного факультету.

 

Автори

Панченко П.М., асистент кафедри вищої математики.

Рецензенти

Флегантов Л. О., кандидат фізико-математичних наук, доцент, завідувач кафедри вищої математики Полтавської державної аграрної академії;

Негребецький І. С., старший викладач кафедри вищої математики Полтавської державної аграрної академії

Схвалено і рекомендовано до видання

Кафедрою вищої математики Полтавської державної аграрної академії

Протокол № _____ від „_____” __________ 2008 р.

Методичною радою інженерно-технологічного факультету Полтавської державної аграрної академії

Протокол № _____ від „_____” __________ 2008 р.

 

 

© Панченко П.М., 2008 р.

Зміст

Лабораторна робота №1

Основи роботи у програмі Microsoft Excel.................................... 7

Лабораторна робота №2

Використання формул. Сортування та фільтрація даних............ 17

Лабораторна робота №3

Використання назв........................................................................... 33

Лабораторна робота №4

Побудова діаграм………………………………………………… 41

Лабораторна робота №5

Робота з декількома аркушами....................................................... 48

Лабораторна робота №6

Надбудови в Excel………………………………………………… 55

Додаток. Математичні функції програми Excel................................ 64

Вступ

Методична розробка містить методичні вказівки та алгоритми виконання лабораторних робіт з дисципліни „Комп’ютери та комп’ютерна техніка” для студентів інженерних та технологічних спеціальностей.

Кожна лабораторна робота містить список контрольних питань, теоретичний матеріал з теми даної роботи, завдання до виконання на лабораторному занятті та контрольні завдання.

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

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

Лабораторна робота № 1.

Основи роботи у програмі Microsoft Excel

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

Контрольні питання:

1.  Для чого призначена програма Microsoft Excel?

2. Як називаються документи Microsoft Excel?

3. Скільки рядків і стовпчиків у робочого аркуша Microsoft Excel?

4. Які дії можна виконувати з робочими аркушами Microsoft Excel?

5. Якими способами можна виділяти діапазони комірок Microsoft Excel?

6. Де знаходиться маркер автозаповнення Microsoft Excel?

7. Як можна розділити на частини робочий аркуш Microsoft Excel?

8. Як можна редагувати дані у комірці?

9. Як встановити потрібну ширину стовпчиків?

10. Як закінчують введення даних в комірки?

Microsoft Excel – табличний процесор, програма для створення й обробки електронних таблиць. Файл у Microsoft Excel називається робочою книгою. Робоча книга складається з робочих аркушів, імена яких (Лист1, Лист2, …) виведені на ярликах у нижній частині вікна робочої книги (мал.2). Клацаючи по ярликах, можна переходити від аркушу до аркушу усередині робочої книги. Для прокручування ярликів використовуються кнопки ліворуч від назв аркушів :

	–  перехід до ярлику першого аркушу;
	–  перехід до ярлику попереднього аркушу;
	–  перехід до ярлику наступного аркушу;
	–  перехід до ярлику останнього аркушу.

Щоб змінити назву робочого аркуша, треба правою клавішею миші викликати контекстне меню на назві цього аркуша, вибрати пункт Переименовать та ввести нову назву; після цього треба натиснути клавішу Enter. Команди Удалить і Вставка контекстного меню аркуша використовують для додавання або вилучення робочих аркушів. Порядок аркушів можна змінити, перетягуючи їх при натиснутій лівій кнопці миші.

Робочий аркуш являє собою таблицю, що складається з 256 стовпців і 65536 рядків. Стовпці позначаються латинськими літерами, а рядки – цифрами. Кожна комірка таблиці має адрес, що складається з імені рядку й імені стовпця. Наприклад, якщо комірка знаходиться в стовпці F і рядку 7, то вона має адрес F7.

Виділення елементів таблиці

Одна з комірок таблиці завжди є активною, активна комірка виділяється рамкою. Щоб зробити комірку активною, потрібно клавішами керування курсором підвести рамку до цієї комірки або клацнути на ній мишею.

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

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

Для виділення декількох аркушів необхідно натиснути Ctrl і, не відпускаючи її, клацати на ярликах аркушів.

Поділ робочого аркуша здійснюється за місцем розташування поточної комірки командою Окно/Разделить. Поділ скасовують командою Окно/Снять разделение.

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

Завдання:

1. Запустіть програму MS Excel, виконавши послідовність команд Пуск / Программы / MS Excel.

2. Збережіть відкритий файл у свою робочу папку під назвою Вправа1, виконавши послідовність команд Файл/Сохранить как...

3. Виділіть 5 перших рядків, а потім, не знімаючи попереднього виділення, 3 перших стовпчика, для цього:

· проведіть по номерах перших 5 рядків покажчиком миші при натиснутій лівій кнопці;

· утримуючи натиснутою клавішу Ctrl, проведіть покажчиком миші при натиснутій лівій кнопці по заголовках перших трьох стовпчиків.

Результат виконання завдання зображено на малюнку:

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

4. Виділіть мишею й оточіть рамкою прямокутний 5×3 та квадратний 4×4 діапазони комірок, для цього:

· виділіть діапазон А1:С5;

· натисніть клавішу Ctrl та, не відпускаючи її, виділіть діапазон D 6: G 9;

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

· зніміть виділення; комірки, які були виділені, матимуть оточення у вигляді рамок.

Результат виконання завдання зображено на малюнку:

5. Виділіть ті ж діапазони комірок за допомогою поля Имя у лівій частині рядка формул, для цього:

· введіть у поле Имя через крапку з комою адреси діапазонів А1:С5; D 6: G 9

 

· закінчіть введення, натиснувши клавішу Enter.

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

6. За допомогою клавіші Ctrl виділіть діапазони комірок, що утворюють літеру П (висота літери – 5 рядків, ширина – 3 стовпчика).

7. Перейменуйте за допомогою контекстного меню назви аркуша Лист1 у Проба.

8. Перейдіть на Лист2.

9. Встановіть ширину стовпчиків у діапазоні А:АА рівним 3, для цього:

· виділіть діапазон стовпчиків А:АА одним з вже відомих вам способів виділення.

· командою Формат/Столбец/Ширина встановіть задану ширину стовпчика та натисніть Ок.

10. Додайте ще один аркуш.

11. Видаліть аркуш Лист4.

12. Перейменуйте Лист3 у Календар.

13. У перший рядок введіть назви 12 місяців року за допомогою автозаповнення, для цього:

· у комірку А1 введіть назву першого місяця року у форматі Янв;

Зауваження. Для введення даних у комірку необхідно зробити її активною і ввести дані з клавіатури. Для завершення введення слід натиснути клавішу Enter або одну з клавіш керування курсором. Процес введення даних закінчиться й активною буде сусідня комірка.

Щоб відредагувати дані у комірці, необхідно:

1) зробити комірку активною, клацнувши на ній лівою кнопкою миші, і натиснути клавішу F2, або виконати подвійне клацання по комірці, вміст якої треба редагувати;

2) у комірці з'явиться текстовий курсор, який можна пересунути клавішами керування курсором у потрібне місце і відредагувати дані;

3) вийти з режиму редагування натисканням клавіші Enter.

· знов перейдіть на комірку А1, зробивши її активною;

· підведіть покажчик миші до маленького чорного квадратика у правому нижньому куті рамки активної комірки (покажчик миші змінить форму на маленький чорний хрестик);

· натисніть ліву кнопку миші та, не відпускаючи її, протягніть маркер автозаповнення через інші комірки рядка.

14. Додайте в таблицю порожній стовпчик, для цього:

· викличте контекстне меню на заголовку стовпчика А та виберіть пункт Добавить ячейки.

15. У стовпчик А за допомогою автозаповнення введіть числа від 1 до 31, для цього:

· у комірки А2 і А3 введіть числа 1 і 2 відповідно;

· виділіть обидві комірки;

· протягніть маркер автозаповнення через інші комірки стовпчика.

Зауваження. За допомогою автозаповнення можна вводити послідовності чисел з кроком, який відрізняється від 1, наприклад 1, 3, 5... або -21, -20,5, -20, -19,5... тощо. Крок визначається різницею між другим і першим числами послідовності.

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

17. Створіть заголовок таблиці Місяці, для цього:

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

· у комірку В1 введіть заголовок Місяці;

· виділіть комірку В1;

· розтягніть виділення над усіма назвами місяців;

· на панелі інструментів клацніть кнопку Объединить и поместить в центре ;

· форматуйте заголовок напівжирним курсивом шрифту Arial Cyr 16 пунктів.

18. Для першого стовпчика створіть заголовок Числа, для цього:

· у комірку А2 введіть заголовок Числа;

· форматуйте заголовок напівжирним шрифтом Arial Cyr 14 пунктів;

· виконайте команду Формат/Ячейки/Выравнивание;

· у полі Ориентация встановіть значення 45⁰.

19. Зафіксуйте заголовки календаря, для цього:

· зробіть активною комірку В3;

· виконайте команду Окно/Закрепить области.

20. За допомогою смуг прокрутки з’ясуйте, який день тижня припадає на 7 листопада.

21. Створіть таблицю Звіт по продажах за поданим зразком:

№ п/п	Найменува-ння товару	Місто	Ціна за одиницю, грн.	Кількість	Виручка, грн.
1	Комп’ютер	Полтава	5000	2
2	Ноутбук	Полтава	7000	1
3	Принтер	Харків	1500	3
4	Ноутбук	Київ	8000	1
5	Принтер	Харків	1000	2
6	Монітор	Полтава	1300	1
7	Сканер	Київ	500	2
8	Принтер	Київ	800	1
9	Ноутбук	Харків	6500	2
10	Принтер	Харків	750	1
11	Монітор	Полтава	850	4
12	Сканер	Харків	450	2
Всього:

22. Збережіть файл у свою робочу папку під назвою Звіт .xls

Лабораторна робота № 2 .

Використання формул. Сортування та фільтрація даних.

Мета: Навчитися вводити формули, редагувати їх, копіювати та виконувати обчислення.

Контрольні питання:

1.  Що називається формулою Excel?

2. Що є результатом обчислення формули?

3. Який порядок виконання операцій в формулах?

4. Чим відрізняються абсолютна та відносна адреси комірок?

5. Як скопіювати формулу на всі комірки діапазона?

6. Як встановити (зняти) режим відображення формул в комірках?

7. Як вставити у формулу стандартну функцію Excel?

8. Як відсортувати дані?

9. Як встановити автофільтр?

10. Що називається фільтрацією?

11. Яким чином мають бути організовані дані, щоб до них можна було застосувати засіб Автофильтр?

12. Що можна робити за допомогою засобу Автофильтр?

Створення формул

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

Формулою вважається будь-яка інформація в комірці, яка починається зі знаку рівності ("=") і записана за певними правилами.

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

Результатом обчислення формули може бути числове або логічне значення (True – істина, False – хибне значення). Логічний результат отримується при використанні в формулі операторів порівняння.

В формулах можна використовувати такі оператори математичних операцій:

( відкрити дужку,

) закрити дужку,

+ додавання аргументів,

- віднімання аргументів,

* множення аргументів,

/ ділення,

^ піднесення до степеня (наприклад, А3^5 – значення комірки А3 піднести до 5 степеня),

% визначення процента (наприклад, результатом формули "=35%" буде значення 0,35),

& об’єднання текстових рядків (наприклад, "А5&A8").

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

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

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

У формулі в ролі змінних може бути одразу кілька різних адрес комірок. При цьому у випадку виконання автозаповнення даною формулою наступних комірок інколи потрібно, щоб адреси одних комірок у формулі змінювались, а інших – ні. Тому в Excel існують поняття абсолютної та відносної адреси комірки.

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

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

Для того, щоб вказати абсолютні координати комірки, необхідно перед назвою рядка або стовпчика поставити знак $ («знак долара»). Причому, якщо встановити цей знак перед назвою рядка, то не змінюється лише координата рядка даної комірки, а назва стовпчика змінюється, і навпаки.

$D$4 – не змінюється в адресі комірки ні назва стовпчика, ні номер рядка;

$D4 – назва стовпчика не змінюється, а номер рядка може змінюватись;

D$4 – назва стовпчика може змінюватись, а номер рядка не змінюється.

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

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

Для обчислення елементів масиву в Excel можна використовувати єдину формулу на всі елементи масиву. Наприклад, потрібно до кожного елементу масиву, що займає комірки від А1 до А10, додати відповідний елемент з масиву, що займає комірки від В1 до В10. Результати необхідно помістити в комірки від С1 до С10.

Цю операцію можна виконати шляхом введення формули для елементів А1 та В1, тобто "=А1+В1", а тоді скопіювати цю формулу в наступні комірки за допомогою автозаповнення. Але в даному випадку краще скористатись формулою масиву. Для цього потрібно виділити комірки, куди необхідно помістити результат ( в даному випадку від С1 до С10), а тоді ввести формулу:

=А1:А10+В1:В10

Після цього потрібно натиснути комбінацію клавіш Ctrl + Shift + Enter (а не Enter, як в звичайних формулах). В результаті цього в кожній комірці виділеного діапазону буде поміщено формулу: { =А1:А10+В1:В10 }. Фігурні дужки означають, що це формула масиву. Потрібно відмітити, що коли фігурні дужки ввести вручну (з клавіатури), то бажаного результату користувач не отримає (введена формула буде сприйматись як звичайний текст).

Фільтрація даних

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

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

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

Щоб відобразити приховані записи, але залишити випадаючі списки, потрібно виконати команду Данные/Фильтр/Отобразить все. Для видалення випадаючих списків потрібно вибрати команду Данные/Фильтр та зняти прапорець біля команди Автофильтр.

За допомогою засобу Расширенный фильтр можна:

·створювати новий відфільтрований список в іншому місці робочого аркуша;

·сторювати список, що містить тільки унікальні записи (тобто по одному екземпляру всіх існуючих комбінацій значень);

·більш повно контролювати критерії. Наприклад, для одного стовпця можна створити більше двох критеріїв користувача.

При використанні засобу Расширенный фильтр в ролі критеріїв відбору можна використовувати й результат формули.

Завдання:

Коло змінного струму

Розглянемо задачу про обчислення послідовно з’єднаних резисторів і сили струму при заданій напрузі. Нехай маємо коло, що складається з трьох послідовно з’єднаних опорів R 1 (10 Ом), R 2 (20 Ом) та R 3 (25 Ом). До кола прикладена постійна напруга U (220 В). Тоді загальний опір кола дорівнює їх сумі, тобто R 1 + R 2 + R 3.

Нехай напруга у колі в початковий момент часу дорівнює 200 В, максимальна напруга 220 В, частота змінної напруги (вона ж частота струму)  Гц.

1. Запустіть програму Microsoft Excel.

2. Запишіть на робочому аркуші Microsoft Excel дані про величини опорів, напруг та частоту напруги.

3. Обчисліть та запишіть в таблиці загальний опір кола.

4. Обчисліть та запишіть в таблиці повний період коливань, скориставшись співвідношенням .

5. Розбийте період коливань на 20 рівних проміжків та обчисліть у кожний момент часу значення напруги та сили струму. Результат обчислень показаний на малюнку.

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

Підказка. У комірку С10 була введена формула:

=$E$2*COS(2*ПИ()*$E$4*C9)+КОРЕНЬ($E$3^2-$E$2^2)* SIN(2*ПИ()*$E$4*C9),

у комірку С11 – формула =C10/$C$6, а потім формули з цих комірок були скопійовані за допомогою автозаповнення у діапазони комірок D 10: W 10 та D 1 1 : W 1 1 відповідно.

6. Знайдіть середні значення, середнє відхилення модуля та середньоквадратичне відхилення напруги та сили струму від їх середнього значення за період.

Для обчислення середніх значень можна використати функцію СРЗНАЧ(), де в дужках потрібно вказати діапазон комірок, де містяться значення, для яких шукаємо середнє. Для обчислення середнього відхилення модуля можна використати функцію СРОТКЛ(). Середньоквадратичні відхилення обчислюються так: спочатку обчислюється сума квадратів значень (для обчислення суми квадратів значень можна використати функцію СУММКВ()), після чого вона ділиться на кількість доданків та з отриманого значення добувається квадратний корінь (для обчислення квадратного кореня будемо використовувати функцію КОРЕНЬ()).

Підказка. У комірку С14 була введена формула: =СРЗНАЧ(C10:V10), у комірку D 14 – формула =СРОТКЛ(C10:W10) , у комірку Е 14 – формула =КОРЕНЬ(СУММКВ(C10:W10)/20).

Лабораторна робота № 3 .

Використання назв

Мета: Набути вмінь та навичок використання назв для комірок та діапазонів, для формул та констант, а також використання міток при посиланнях на комірки.

Контрольні питання:

1.  Що потрібно зробити, щоб комірці привласнити ім’я?

2. Яких правил слід дотримуватись при роботі з текстовими назвами діапазонів комірок?

3. Яким чином можна надавати імена константам та формулам?

4. Чи можна надавати імена декільком коміркам з різних робочих аркушів? Яким чином це можна зробити?

5. Що називається міткою? Для чого вони використовуються?

В Excel є одна досить цікава концепція, яка дозволяє істотно спростити процедуру роботи з формулами. Головна її ідея полягає у використанні для комірок, діапазонів, формул і констант спеціальних назв.

Лабораторна робота № 4.

Побудова діаграм

Мета: Навчитися будувати діаграми та редагувати їх, а також використовувати діаграми для відображення залежностей між різними величинами.

Контрольні питання:

1.  Що називається діаграмою Excel?

2. Що називається рядом даних?

3. Опишіть алгоритм побудови діаграми.

4. Як можна змінити параметри діаграми?

5. Які дії можна виконувати з діаграмою?

Побудова діаграм

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

Для створення діаграми необхідно:

1. на робочому аркуші виділити дані, для яких слід побудувати діаграму, включаючи комірки, що містять імена категорій або рядів, які будуть використовуватися в діаграмі;

2. виконати команду Вставка/Діаграма або натиснути кнопку на панелі інструментів;

3. у діалогових вікнах Мастер диаграмм слід вибрати тип, формат і інші параметри діаграми;

4. для переходу до наступного кроку використовується кнопка

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

6. в останньому (4-му) вікні натиснути кнопку .

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

Завдання:

Підказка.

· Тип диаграммы: Цилиндрическая, вид – Объёмный;

· Таблица данных/ Таблица данных;

· Линии сетки – прапорець Основные линии для кожної осі;

· Заголовки: название диаграммы – Продаж автомобілів, ось Х – Місяці, ось Y – Автомобілі, ось Z – Кількість.

19. Збережіть файл у свою робочу папку.

20. Зробіть висновки по даній лабораторній роботі та закрийте вікно програми.

Контрольні завдання

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

2. Змініть тип діаграми за вказівкою викладача.

3. Змініть підписи по вісі Ох за вказівкою викладача.

4. Побудуйте об’ємну кругову діаграму, яка відображає співвідношення між сумами, нарахованими першим чотирьом платникам податку з поданого списку.

Лабораторна робота № 5.

Робота з декількома аркушами

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

Контрольні питання:

1. Що називається діаграмою Excel?

2. Що називається рядом даних?

3. Як створити форму для введення даних на декількох листах одночасно?

4. Як виконуються посилання на дані, що знаходяться на іншому аркуші?

Створити деяку форму або шаблон для введення даних на декількох листах одночасно можна, виділивши їх при натиснутій клавіші Ctrl.

Посилання на комірку іншого аркуша у формулі має вигляд:

=назва_аркуша!формула

Наприклад, =Лист1!А15+А17

Завдання:

1. Запустіть програму Microsoft Excel та збережіть файл під назвою Табель.

2. Добавте у книгу ще два робочі аркуші.

3. Назвіть аркуші відповідно „1 чверть”, „2 чверть”, „3 чверть”, „4 чверть”, „за рік”.

4. Створіть таблицю „Табель успеваемости” за поданим нижче зразком одночасно на всіх п’яти аркушах, для цього:

· виділіть всі аркуші, утримуючи натиснутою клавішу Ctrl та клацаючи на назвах аркушів;

· створіть таблицю за поданим нижче зразком.

5. Заповніть таблиці даними. Кінцевий вигляд таблиць дивіться нижче.

Лабораторна робота № 6.

Надбудови в Excel

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

Контрольні питання:

1. Що називається надбудовами?

2. Як відбувається активізація потрібної надбудови?

3. З яких етапів складається процедура пошуку екстремумів функції однієї змінної за допомогою Excel?

4. Яка послідовність побудови графіка функції?

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

· Пакет анализа – дозволяє використовувати додаткові фінансові, статистичні та інженерні функції;

· Пакет анализа VBA – дозволяє застосовувати синтаксис статистичного пакету аналізу даних при використанні фінансових, статистичних та інженерних функцій;

· Мастер суммирования – використовується для сьворення формулсумування даних в списку, які задовольняють задані умови;

· Пересчет в евро – здійснює перетворення значень в євро;

· Помощник по Интернету – дозволяє розміщувати дані Excel в мережі Інтернет;

· Мастер подстановок – створює формулу підстановки для пошуку даних в списку за іншим значенням в цьому ж списку;

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

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

З математики відома задача: знайти значення аргументу х, при якому функція y=f(x) набуває максимального (мінімального) значення. Таку задачу звичайно називають задачею на екстремум.

Пригадаємо, що точкою екстремуму функції однієї змінної y=f(x) називають те значення аргументу х=х₀, при якому функція має максимальне або мінімальне значення. Отже, з погляду математики, речення “розв’язати задачу на екстремум” означає знайти координати точок екстремуму.

Процедура пошуку екстремумів функції однієї змінної за допомогою Excel складається з трьох основних етапів:

· Побудова графіка функції. Цей етап необхідний, щоб локалізувати точки екстремуму - приблизно визначити проміжки, на кожному з яких функція має по одній точці екстремуму.

· Підготовка і розміщення даних на робочому аркуші Excel.

· Виконання стандартної процедури пошуку рішення в Excel. На цьому етапі з заданою точністю знаходиться наближене значення точки екстремуму.

Знайдемо точки екстремуму функції .

Побудова графіка функції

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

Побудову графіка виконаємо в такій послідовності:

1) Створимо таблицю значень функції. Для цього:

· на робочому аркуші Excel введемо у стовпець А діапазон значень аргументу х, у якому функція може мати екстремуми (див.мал.1).

Спочатку у діапазон комірок А2:А24 введемо значення х, наприклад, від –50 до 50 з кроком 5. (Звичайно, межі діапазону та крок зміни аргументу можна взяти інші, виходячи з міркувань зручності).

· У стовпці В обчислимо значення даної функції – введемо у комірку В2 формулу =A2^3+10*(A2-4)^2-A2-400 і скопіюємо її за допомогою автозаповнення у діапазон комірок В3:В24.

2) За даними створеної таблиці побудуємо графік функції. Для цього:

· На робочому аркуші виділяємо діапазон, який містить таблицю значень х і у;

· Викликаємо Мастер диаграмм;

· Крок 1: вибираємо Тип диаграммы – Точечная, со значениями, соединенными сглаживающими линиями без маркеров (3 по порядку), Далее;

· Крок 2: залишаємо графік без змін, Далее;

· Крок 3: на вкладинці Линии сетки для осі Х встановити прапорець Основные линии, Далее;

· Крок 4: розміщуємо діаграму На имеющемся листе, Готово.

Мал.1. Графік функції

 

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

· змініть діапазон значень аргументу в стовпці А, задайте його, наприклад, від –14 до +14, і зменшіть крок зміни аргументу, взявши його, наприклад, рівним 1.

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

З мал.1 бачимо, що функція має два екстремуми - максимум і мінімум. Значення функції у точці максимуму приблизно рівне 550 (по вісі у), а сама точка максимуму – відповідне значення аргументу х, - знаходиться на проміжку від –12 до –6 (по вісі х). Приблизне значення аргументу х в точці максимуму дорівнює –9. Мінімум функції приблизно дорівнює –350. При цьому аргумент х знаходиться на проміжку від 0 до 6, і його значення приблизно дорівнює 3. Звичайно, можна було б значно точніше визначити межі проміжків, на яких знаходиться аргумент х, коли функція у має максимум або мінімум. Але, як побачимо далі, в цьому немає великої потреби. Головне, щоб на кожному з таких проміжків була лише одна точка екстремуму.

Далі для знаходження точного значення точок екстремуму х₀₁ та х₀₂ використаємо стандартний засіб Поиск решения..., який входить до складу Excel.

Підготовка даних

4) Спочатку розмістіть дані на робочому аркуші як показано на мал.2.

Зверніть увагу, що в комірках В10 і С10 обчислюються значення функції в точках екстремуму за формулами, які посилаються відповідно на комірки В3 і С3, де знаходяться невідомі поки що значення точок максимуму х₀₁ і мінімуму х₀₂.

Пошук максимуму

5) Після того як дані розміщені на робочому аркуші, перейдемо до пошуку точок екстремуму.

Мал.2 Розміщення даних

Щоб знайти значення аргументу у точці максимуму, у рядку меню Excel виконайте команду Сервис Þ Поиск решения..., і заповніть діалогове вікно Поиск решения, як показано на мал.3.

Мал.3. Діалогове вікно Поиск решения. Пошук максимуму

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

Мал.4. Діалогове вікно Добавление ограничения

Наприклад, з попереднього аналізу нам вже відомо, що точка максимуму знаходиться на проміжку нижня границя якого –12, а верхня –6. Ці значення розміщені на робочому аркуші, відповідно, в комірках B5 (нижня межа) та B6 (верхня межа) (мал.2). Значення самої точки максимуму має бути обчислене у комірці B2. Тому для пошуку максимуму у діалоговому вікні Добавление ограничения необхідно задати такі обмеження:

B2>=B5
B2<=B6.

Ці обмеження означають, що точку максимуму слід шукати на проміжку від (–12) (комірка В5) до (–6) (комірка В6).

Щоб задати ці обмеження виконайте наступні дії:

Клацніть кнопку Добавить. Відкриється діалогове вікно Добавление ограничения (мал.4).

1. У поле Ссылка на ячейку: введіть координати комірки В2.

2. У наступному полі виберіть знак обмеження “>=”.

3. У поле Ограничение: введіть координати комірки В5.

4. Щоб додати друге обмеження, повторіть п.п. 1-4. Встановіть обмеження B2<=B6.

5. Коли ввод обмежень закінчено, клацніть кнопку О k.

Після заповнення вікна Поиск решения, натисніть кнопку Выполнить: засіб Поиск решения автоматично знайде значення аргументу. Після цього відкриється діалогове вікно Результаты поиска решения (мал.5).

Мал.5. Діалогове вікно Результаты поиска решения

Тепер натисніть О k, щоб побачити відповідь на робочому аркуші Excel (мал.6).

Мал.6. Знайдено точку максимуму

Пошук мінімуму

6) Пошук точки мінімуму виконується аналогічно.

Спочатку виконайте команду Сервис Þ Поиск решения..., і для очистки даних у діалоговому вікні Поиск решения (мал.3) натисніть кнопку Восстановить. Після цього знову заповніть діалогове вікно Поиск решения, як показано на мал. 7.

Мал.7. Фрагмент діалогового вікна Поиск решения. Пошук мінімуму

 

Результат пошуку показано на мал.8.

Мал.8. Знайдено точку мінімуму

Алгоритм пошуку екстремуму функції однієї змінної y=f(x)

1. Побудувати графік функції y=f(x), і за графіком приблизно визначити проміжки, на яких знаходяться точки екстремуму. Головне, щоб на кожному проміжку була лише одна точка екстремуму.

2. Розмістити дані на робочому аркуші Excel (мал.2).

3. Виконати команду Сервис Þ Поиск решения..., заповнити діалогове вікно Поиск решения, як показано на мал.3, і натиснути кнопку Выполнить.

Контрольні завдання

Знайти точки екстремуму функцій:

1. ;

2. ;

3. ;

4. ;

5.

Додаток. Математичні функції програми Excel.

ABS	встановлює модуль (абсолютну величину) числа
ACOS	встановлює арккосинус числа
ACOSH	встановлює гіперболічний арккосоинус числа
ASIN	встановлює арксинус числа
ASINH	встановлює гіперболічний арксинус числа
ATAN	встановлює арктангенс числа
ATAN2	встановлює арктангенс для заданих координат X, Y
ATANH	встановлює гіперболічний арктангенс числа
COS	встановлює косинус вказаного кута (перед цим значення величини кута необхідно перевести з градусної міри в радіанну)
COSH	встановлює гіперболічний косинус кута
EXP	визначає експоненту числа
LN	визначає натуральний логарифм числа
LOG	визначає логарифм числа при заданій основі
LOG10	визначає десятковий логарифм числа
SIN	визначає синус кута (перед цим значення величини кута необхідно перевести з градусної міри в радіанну)
SINH	визначає гіперболічний синус кута
TAN	встановлю тангенс кута (перед цим значення величини кута необхідно перевести з градусної міри в радіанну)
TANH	встановлює гіперболічний тангенс кута
ГРАДУСЫ	перетворює радіанну міру кута в градусну
ЗНАК	результат застосування функції: 1, якщо аргумент від’ємний; 0, якщо аргумент дорівнює нулю; -1, якщо аргумент від’ємний
КОРЕНЬ	встановлює квадратний корінь числа
МОБР	повертає матрицю, обернену до даної
МОПРЕД	обчислює визначник квадратної матриці
МУМНОЖ	повертає добуток двох матриць
НЕЧЁТ	заокруглює число до найближчого непарного цілого
ОКРВВЕРХ	заокруглює число до найближчого цілого
ОКРВНИЗ	заокруглює число до найближчого меншого по модулю
ОКРУГЛ	заокруглює число до найближчого із вказаною кількістю десяткових знаків
ОКРУГЛВВЕРХ	заокруглює число до найближчого більшого по модулю
ОКРУГЛВНИЗ	заокруглює число до найближчого меншого по модулю
ОСТАТ	повертає залишок від ділення двох чисел
ОТБР	заокруглює число до цілого, відкидаючи дробову частину
ПИ	повертає значення константи π
ПРОИЗВЕД	знаходить добуток аргументів (аргументами можуть бути константи, адреси комірок та діапазони)
ПРОМЕЖУТОЧНЫЕ ИТОГИ	встановлює проміжні результати обчислення певної функції в списку або базі даних
РАДИАНЫ	перетворює градуси в радіани
РИМСКОЕ	перетворює число із арабського в римський формат. При цьому воно сприймається як текст.
СЛЧИС	задає випадкове число від 0 до 1
СТЕПЕНЬ	підносить число до заданого степеня
СУММ	знаходить суму аргументів (аргументами можуть бути константи, адреси комірок та діапазони)
СУММЕСЛИ	знаходить суму чисел, що відповідають умові
СУММКВ	знаходить суму квадратів аргументів
СУММКВРАЗН	знаходить суму квадратів різниці відповідних елементів двох масивів
СУММПРОИЗВ	знаходить суму добутків відповідних елементів масивів
СУММРАЗНКВ	знаходить суму різниць квадратів відповідних елементів двох масивів
СУММСУММКВ	знаходить суму сум квадратів відповідних елементів двох масивів
ФАКТР	повертає факторіал числа
ЦЕЛОЕ	заокруглює до найближчого цілого
ЧЁТН	заокруглює число до найближчого парного цілого
ЧИСЛКОМБ	встановлює кількість комбінацій для заданого числа об’єктів.

 

ЛІТЕРАТУРА:

1. Глушаков С.В., Сурядный А.С. Microsoft Excel XP / Худож.-оформитель А.С.Юхтман. – Харьков:Фолио, 2006. – 509 с. – (Учебный курс).

2. Васильев, А.Н. Научные вычисления в Microsoft Excel. – М.: Издательский дом «Вильямс», 2004. – 512 с.: ил.

3. Ларсен С.У. Инженерные расчеты в Excel. М.: Издательский дом «Вильямс», 2002.

4. КОМП’ЮТЕРИ ТА КОМП’ЮТЕРНІ ТЕХНОЛОГІЇ. Електронні таблиці Microsoft Excel . Методичні вказівки і завдання до лабораторних робіт для студентів інженерно-технологічного факультету / Флегантов Л.О., Горда І.М., Панченко П.М. – Полтава, 2007.

 

МІНІСТЕРСТВО АГРАРНОЇ ПОЛІТИКИ УКРАЇНИ

ПОЛТАВСЬКА ДЕРЖАВНА АГРАРНА АКАДЕМІЯ

 

Кафедра вищої математики

 

12345678910Следующая ⇒

Поделиться: