Тема 1.6 Розширені можливості табличного процесору MS Excel. Використання майстрів, фільтрів та надбудов.

 

Мета: Ознайомитися з розширеними можливостями табличних процесорів та прийомами їх використання.

 

Теоретичні відомості:

 

Див. Лекції, підручники.

 

Завдання:

 

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

Зауваження: Формули для роботи з масивами відрізняються тим, що результатом їх використання є група даних, що зберігаються у діапазоні з кількох комірок. Для завершення введення формули для масиву використовується комбінація клавіш Ctrl/Shift/Enter (якщо натиснути тільки клавішу Enter, то введена формула буде відноситися лише до однієї комірки). Уже введену формулу для масиву можна редагувати тільки для всіх комірок масиву одночасно. Відрізнити формулу для масиву можна за скобками { }, які Excel автоматично дописує перед та після формули. Для того, щоб побачити вже існуючий на аркуші масив, треба скористатися командою ПравкаПерейтиВыделитьТекущий массив.

Розв’яжемо тепер за допомогою масивів систему лінійних алгебраїчних рівнянь (СЛАР):

.

Для цього:

1. Відкрити власну папку і в ній створити новий Лист Microsoft Excel (команда меню Файл/Создать/Лист Microsoft Excel). Дати назву новому файлу СЛАУ. Ввести на першому аркуші початкові дані для розв’язування СЛАР (матрицю системи та вектор правої частини як таблицю 4×5. Замість N необхідно використати номер вашого варіанта. Дати назву першому аркушу Исходные данные (команда Переименовать контекстного меню аркуша, що викликається після натискання правої клавіші мишки на ярличку цього аркуша книги Excel).

2. Другий аркуш необхідно використати для вирішення системи методом оберненої матриці (за формулою ). Для цього треба:

a. скопіювати необхідну числову інформацію з першого аркуша.

b. обчислити обернену матрицю:

1) виділити діапазон пустих комірок 4´4,

2) ввести формулу =МОБР(діапазон розміщення матриці коефіцієнтів системи),

3) натиснути Ctrl/Shift/Enter.

c. обчислити добуток матриці на вектор:

1) виділити діапазон пустих комірок 4´1 для майбутнього розв’язку,

2) ввести формулу =МУМНОЖ(діапазон розміщення оберненої матриці; діапазон розміщення вектору правих частин),

3) натиснути Ctrl/Shift/Enter.

Діапазони задаються адресами лівої верхньої та правої нижньої комірок, наприклад, A1:D4. Зрозуміло, що попередньою дією має бути обчислення визначника матриці системи за допомогою формули =МОПРЕД( діапазон розміщення елементів матриці коефіцієнтів системи). Це дасть змогу відповісти на питання про існування лише одного розв’язку.

3. Другому аркушеві треба змінити назву на Метод обратной матрицы.

4. Третьому аркушеві необхідно дати назву Метод Крамера.

5. Використати метод Крамера для отримання розв’язків системи. Для цього:

a. створити 4 таблиці для обчислення допоміжних визначників (їх 4 тому що у системі 4 невідомих), скопіювавши необхідні стовпці визначника системи та вектор правої частини системи.

b. Користуючись функцією МОПРЕД обчислити ці визначники.

c. Діленням кожного з отриманих визначників на визначник матриці системи (дивись пункт 2) обчислити розв’язки системи.

6. Порівняти отримані двома методами результати.

7. Зберегти змінений файл та закрити вікно цієї книги.

8. Відкрити файл з ім’ям “Основні можливості табличного процесору MS Excel”.

9. Ознайомитись з особливостями використання фільтрів за допомогою команд ДанныеФильтрАвтофильтр та ДанныеФильтрРасширенный фильтр.

10. Знайти за допомогою фільтрів прізвища студентів групи, що отримали найбільший сумарний бал на вступних іспитах; змоделювати самостійно додаткові умови фільтрації.

11. Виписати до зошиту відмінності у можливостях розглянутих фільтрів.

12. Зберегти та закрити книгу.

13. Створити новий документ (команда меню ФайлСоздатьНовый документ). Дати йому назву «Поиск решений».

14. Створити на першому аркуші таблицю значень функції f відповідно варіанту:

Знайти мінімум:	Знайти мінімум:	Знайти максимум:
Знайти значення 0,75:	Знайти максимум:	Знайти мінімум:
Знайти значення 0,9:	Знайти мінімум:	Знайти максимум:
Знайти максимум:	Знайти максимум:	Знайти значення 0,5:

Таблицю функції f(x,y) на області [0..1, 0..1] з кроком 0,1 створити за допомогою автозаповнення за зразком:

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

16. На другому аркуші необхідно дати коміркам першого стовпця по черзі абсолютні імена. Такі імена не зв’язані з нумерацією рядків та стовпців. Для цього:

a. необхідну комірку треба зробити поточною,

b. використавши команду ВставкаИмяПрисвоить, призначити ім’я і натиснути кнопку ОК. При цьому необхідно орієнтуватись на зразок:

Увага! Абсолютні імена не будуть видимі в комірці, тому що це – не вміст комірки.

c. помістити в комірки початкові значення.

17. До комірок другого стовпця usl1, usl2, usl3 треба вмістити формули, які характеризують обмеження для змінних х та у, попередньо привівши їх до форми: <вираз <знак порівняння <константа. Наприклад, для умови x*y+2*y-34<=x формула буде мати вигляд =x*y+2*y-x.

18. До комірок третього стовпця ogr1, ogr2, ogr3 необхідно вмістити константи, що обмежують умови, накладені на змінні х та у. Наприклад, для умови x*y+2*y-34<=x цим числом буде 34.

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

20. Виконати команду меню СервисПоиск решения.

21. У розділі «Установить целевую» вмістити ім’я комірки цільової функції (target) та вказати мету пошуку – максимум, мінімум або певне точне значення.

22. У розділі «Изменяя ячейки» записати через «;» імена комірок, в яких буде здійснюватися перебирання значень змінних у вказаних обмеженнях.

23. У розділі «Ограничения», по черзі для кожного обмеження, натискаючи кнопку «Добавить»

a. внести у форму ім’я комірки, в якій зберігається формула обмеження,

b. вибрати знак порівняння,

c. внести у форму ім’я комірки, в якій зберігається константа обмеження. Наприклад, usl2 <= ogr2.

24. Додатково вказати умови обмеження: 0<=x<=1 и 0<=y<=1.

25. Натиснути кнопку «Выполнить».

26. Якщо розв’язок знайдений, зберегти отриманий розв’язок. Інакше, якщо розв’язок не було знайдено, вмістити до комірок зі змінними інші початкові значення (від 0 до 1). При цьому треба орієнтуватися на отриману попередньо таблицю значень.

27. За певною аналогією з попередніми діями розв’язати самостійно квадратне рівняння х²+2х–15=0, попередньо створивши на новому аркуші допоміжну таблицю функції, побудувавши за допомогою Майстра діаграм графік цієї функції та використавши команду меню СервисПодбор параметра, правильно вказавши параметри у відповідному діалоговому вікні.

28. Зберегти файл.

 

Запитання для контролю та самоконтролю:

 

1) Визначити розширені можливості програм для обробки таблиць. Навести приклади.

2) Пояснити особливості реалізації сортування даних таблиці.

3) Пояснити особливості виконання фільтрації даних таблиці.

4) Пояснити особливості використання надбудов Поиск решения та Подбор параметра.

5) Навіщо та як у табличних процесорах користуються діаграмами.