Не забудьте перед заповненням таблиць зняти виділення з усіх листів.

Загальний вигляд таблиці на перших чотирьох аркушах

 

 

На першому аркуші

На другому аркуші

На третьому аркуші

На четвертому аркуші

Вигляд аркушу «за рік»

6. Підрахуйте підсумкові оцінки по всіх предметах як середнє арифметичне значення оцінок по всіх тижнях. Причому, якщо по всіх тижнях по даному предмету стоїть „н/а”, то й в графу „Підсумкова” також виставляється „н/а”. Для реалізації цієї умови у комірку G4 введіть формулу:

=ЕСЛИ(СЧЁТ(C4:F4)<>0;СРЗНАЧА(C4:F4);"н/а")

та скопіюйте її за допомогою засобу автозаповнення у всі комірки діапазону G 5: G 8.

Примітка. Функція СРЗНАЧА(діапазон_комірок) підраховує середнє арифметичне аргументів, при цьому значення комірок з текстом приймається рівним нулю.

Пояснення до формули. Фрагмент формули СЧЁТ( C 4: F 4) підраховує кількість чисел в діапазоні комірок C 4: F 4. Вираз СЧЁТ( C 4: F 4)<>0 каже, що в діапазоні комірок C 4: F 4 є хоча б одне число (тобто кількість чисел є число, що не дорівнює нулю). Якщо виконується умова СЧЁТ( C 4: F 4)<>0, то в комірку повертається число, знайдене за допомогою виразу СРЗНАЧА(C4:F4), тобто середнє арифметичне аргументів діапазону C4:F4, а якщо умова СЧЁТ( C 4: F 4)<>0 не виконується, то в комірку повертається "н/а".

7. Введіть з клавіатури оцінки за контрольну роботу по кожному предмету по кожній чверті.

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

·  у комірку І4 введіть формулу

=ЕСЛИ(H4>=G4;ОКРУГЛ(G4;0);H4);

· скопіюйте цю формулу у комірки І5 : І8.

Пояснення до формули. Якщо значення в комірці H4 більше або дорівнює значенню в комірці G4 (тобто оцінка за контрольну більша або дорівнює підсумковій оцінці), то в комірку І4 вміщується значення з комірки G4, заокруглене до 0 знаків після коми (оцінка за чверть виставляється як середнє арифметичне оцінок по тижнях), інакше в комірку І4 вміщується значення з комірки Н4 (оцінка за чверть дорівнює оцінці за контрольну роботу).

9. Підрахуйте кількість „н/а” по кожному предмету по всіх чвертях, для цього:

у комірку J 4 введіть формулу =СЧЁТЗ( C4:F4 )-СЧЁТ( C4:F4 ) .

Примітка. Функція СЧЁТЗ(діапазон_комірок) підраховує загальну кількість значень в діапазоні, а функція СЧЁТ(діапазон_комірок) – кількість чисел в діапазоні.

· скопіюйте цю формулу у комірки J 5 : J 8.

10. Знайдіть, скільки процентів складають „н/а” від загальної їх кількості, для цього:

· Встановіть Процентн ый формат комірок діапазону K 4 : K 8 (Формат/Ячейки...).

· у комірку K 4 введіть формулу = J 4 /СЧЁТЗ( C $4 : F $4 );

· скопіюйте цю формулу у комірки K 5 : K 8.

11. Для кожного тижня підрахуйте середню оцінку по предметах за допомогою формул виду =СРЗНАЧА(потрібний_діапазон).

12. Серед оцінок за чверть підрахуйте кількість оцінок „5”, „4”, „3”, „2” та „н/а” та визначіть їх кількість у процентах.

13. Визначіть якість навчання як суму процентного складу оцінок „5” та „4”.

14. Визначіть успішність як суму процентного складу позитивних оцінок.

15. Визначіть середній бал як середнє арифметичне оцінок за чверть.

16. Скопіюйте оцінки за кожну чверть на аркуш за рік, для цього:

· перейдіть на аркуш «за рік»;

· у комірку D 6 введіть формулу ='1 чверть'!N6 та скопіюйте її у комірки D 7: D 17;

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

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

18. З клавіатури введіть дані про оцінки, отримані на контрольній роботі за рік.

19. Визначіть річну оцінку по кожному предмету, для цього:

· у комірку J 6 введіть формулу

=ЕСЛИ(G4="н/а";"н/а";ЕСЛИ(H4>=G4;ОКРУГЛ(G4;0);H4));

· скопіюйте цю формулу у комірки J 7: J 17.

Пояснення до формули. Якщо значення в комірці G 8 дорівнює «н/а», то в комірку J 6 вміщується значення «н/а», інакше перевіряється виконання другої умови: якщо значення в комірці Н4 більше або дорівнює значенню в комірці G4 (оцінка, отримана на контрольній роботі, більша або дорівнює підсумковій оцінці), то в комірку J 6 вміщується значення з комірки G4, заокруглене до 0 знаків після коми (підсумкова оцінка), інакше в комірку J 6 вміщується значення з комірки Н4 (за чверть виставляється оцінка, отримана на контрольній).

20. Підрахуйте кількість та процентний вміст „н/а” по предметах за рік.

21. Серед оцінок за рік підрахуйте кількість оцінок „5”, „4”, „3”, „2” та „н/а” (комірка С11) та визначіть їх кількість у процентах аналогічно до того, як це було зроблено для кожної чверті.

22. Підрахуйте якість (комірка С12), успішність (комірка С13) та середній бал за рік (комірка С14).

23. В комірку С17 введіть формулу, яка на підставі даних за рік формулює висновки наступним чином: якщо Успішність менше 50%, або Середній бал < 3, або кількість „н/а” > 33%, то „Залишити на другий рік”, інакше „Переведений до наступного класу”. Ця формула має вигляд:

=ЕСЛИ(ИЛИ(C 13 <50%;C 14 <3; С11 >33%);" Залишити на 2-й рік ";"Переведен ий в наступни й клас")

24. На кожному робочому аркуші створіть діаграми, які відображають:

a. успішність по предметах;

b. процентне співвідношення кількості та якості навчання.

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

Лабораторна робота № 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

Поделиться: