17171

Ms Excel. Організація розгалужень та ітерацій. Метод добирання параметра. Розв’язання задач «Нарахування зарплатні», «Розв’язання нелінійного рівняння»

Практическая работа

Информатика, кибернетика и программирование

Практична робота №16 Тема: Ms Excel. Організація розгалужень та ітерацій. Метод добирання параметра. Розв’язання задач Нарахування зарплатні Розв’язання нелінійного рівняння. Мета: Уміти використовувати логічну функцію ЯКЩО ЕСЛИ IF та абсолютні адреси клітинок д...

Украинкский

2013-06-29

259 KB

74 чел.

Практична робота №16

Тема: Ms Excel. Організація розгалужень та ітерацій. Метод добирання параметра. Розв’язання задач «Нарахування зарплатні», «Розв’язання нелінійного рівняння».

Мета: Уміти використовувати логічну функцію ЯКЩО (ЕСЛИ, IF) та абсолютні адреси клітинок для розв'язування типових економічних і математичних задач.

Обладнання: ПЕОМ. Табличний процесор MS Excel.

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

  1.  Правила ТБ
  2.  Теоретичні відомості

1. Абсолютна і змішана адреси. Розглянемо поняття абсолютної і змішаної адрес клітинки у формулі. Абсолютною називають адресу, в якій є два символи $: один перед назвою стовпця, другий — перед номером рядка, наприклад $Е$3. Змішана адреса містить лише один символ $. Правило: частина адреси після символу $ не модифікується під час копіювання формули.

Абсолютні адреси слугують, зокрема, для посилання на клітинки, які містять константи, що є у формулах. Такою константою є, наприклад, відсотки (12% =0,12) річних у задачі 2. Якщо для задачі 2 число 0,12 занести в клітинку Е3, то в клітинку СЗ можна ввести формулу = В3*$Е$3.

2. Логічні функції. Розгалуження в ЕТ реалізовують за допомогою функції ЯКЩО (ЕСЛИ, IF), яка використовується у формулах і має таку структуру:

ЯКЩО(<логічний вираз>; <вираз 1>; <вираз 2>).

Логічний вираз — це форма запису умови: простої або складеної.

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

Вираз 1 чи вираз 2 також може бути функцією ЯКЩО — так утворюють вкладені розгалуження. Часто виразом 1 чи виразом 2 є лише адреса клітинки, яка містить деяке значення або конкретне число.

Прості умови записують так, як в алгоритмічних мовах — за допомогою операцій порівняння =, >, <, <=, >=, <>, визначених над виразами, наприклад, 7>5, А5<=20 тощо.

Складені умови записують за допомогою логічних функцій

І(< умова1>;<умова2>; ...)  та  АБО(<умова1 >;<умова2>;...).

Функція І (И, AND) істинна, якщо всі умови в списку істинні.

Функція АБО (ИЛИ, OR) істинна, якщо хоч би одна умова в списку її аргументів істинна.

Наприклад, функція ЯКЩО(АБО(5>7; 5<7); 5; 7) набуває значення 5, а функція ЯКЩО( І (5>7; 5<7); 5; 7) — значення 7.

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

Працюючи в Excel, потрібно користуватися російськими (ЕСЛИ, И, ИЛИ) або англійськими (IF, AND, OR) назвами функцій.

3. Дати. В економічних чи бухгалтерських задачах дати трапляються доволі часто. Дати в ЕТ чисто умовно зачисляють до даних типу дата. Насправді дати зберігаються в ЕТ як цілі числа і лише відображаються на екрані в тому чи іншому форматі, наприклад: 12 січня 2006 р. чи 12.01.2006, чи 2006-01-12, чи 38729. За точку відліку дат взято 1 січня 1900 року. Будь-яка дата еквівалентна цілому числу, що дорівнює кількості днів, які минули від точки відліку (38729 для 12 січня 2006 p.). Це дає змогу виконувати над датами такі операції: віднімати дати для визначення проміжку днів між двома датами, додавати чи віднімати від дат ціле число.

Можна застосовувати функції опрацювання дат з метою визначення окремих компонент дати: поточного номера дня в тижні (WEEKDAY), в місяці (DAY), номера місяця в році (MONTH), року (YEAR), поточної дати (TODAY) тощо. Ці функції потрібно вставляти у вирази командами Вставити > Функція > Дати і час. Деякі з цих функцій мають параметри, дія яких описується у відповідному діалоговому вікні.

Час задається годиною, хвилиною, секундою. Йому відповідає десяткове число від 0 до 1, що відображає частину доби.

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

4. Метод добирання параметра. Метод добирання параметра призначений для розв'язування нелінійного рівняння f(x) = с. Суть методу полягає в тому, щоб автоматично визначити (з деякою точністю) таке значення параметра х, для якого функція f(x) одержує потрібне значення с.

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

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

Модель задачі. Нехай А1адреса клітинки, що міститиме відповідь — значення параметра-ставки, a f(A1) — задана функція (мета дослідження), наприклад, зарплатня(А1) = 12*A1. Складаємо рівняння, яке є математичною моделлю задачі: 12*А1 = 1000.

Хоча задача дуже проста, застосуємо для її розв'язування метод підбору параметра, який полягає в тому, що програма сама має підібрати значення А1, щоб задовольнити будь-яке рівняння.

Алгоритм дій користувача такий.

1. У будь-яку клітинку (але не в А1) треба занести формулу f(A1). У нашому випадку формула така: = 12*A1.

2. Вибрати цю клітинку і виконати команду Сервіс > Підбір параметра. Отримаємо діалогове вікно Підбір параметра.

3. Заповнити три поля: а) зазначити адресу формули (вона буде вказана автоматично, якщо клітинка з формулою була вибрана перед цим); б) бажане значення формули, тобто с (у нашому випадку 1000); в) адресу параметра —А1.

4. Натиснути на ОК і у клітинці А1 отримати результат.

Другий спосіб полягає у використанні можливостей програми Solver («Пошук розв'язку»), що додається до Excel. Вона дає змогу розв'язувати задачі з багатьма параметрами і з обмеженнями. Наприклад, такі: скільки треба купити одиниць двох чи трьох найменувань товарів (це параметри), щоб вкластися в заплановану суму (це функція) і щоб кількості товарів не перевищували деяких величин (це обмеження у вигляді нерівностей, див. роботу № 24).

5. Метод простих ітерацій. Продовжимо вивчати застосування електронних таблиць для розв'язування типових математичних задач. Розглянемо ще два способи розв'язування нелінійного рівняння: 1) метод простих ітерацій з побудовою таблиці; 2) метод простих ітерацій з використанням двох клітинок.

Розглянемо метод простих ітерацій. Щоб нелінійне рівняння f(x) = с можна було розв'язати методом простих ітерацій, його зводять до вигляду х =z(х) так, щоб виконувалась нерівність: |z'(x)|< 1. За цієї умови метод простих ітерацій збігається, тобто дає правильний розв'язок. Наприклад, рівняння 2пх-п= sinnx спочатку треба звести до такого вигляду:

х = (sinnx + п)/2п.

Метод простої ітерації реалізують за допомогою рекурентної формули так:

xi+1 = (sinnxi+ п)/2п,

де хо - будь-яке початкове наближення, i=0, 1, 2,..., а замість п треба підставити значення свого варіанта. Домовимося, що коли і=8, то значення xi ( тобто x8) вважатимемо розв'язком рівняння.

Розглянемо реалізацію рекурентної формули в ЕТ. Нехай n=1, а в клітинку А6 введено будь-яке початкове наближення, наприклад 2. Тоді наступне наближення отримаємо в клітинці В6, ввівши туди формулу =(sin(A6)+1 )/2. Це значення приймаємо за початкове для наступної ітерації: в А7 заносимо значення В6. У клітинці В7 отримуємо наступне наближення і т.д. У клітинці В13 буде останнє (восьме) наближення, яке і приймаємо за розв'язок.

Другий спосіб полягає у використанні властивості ЕТ автоматичного багаторазового переобчислення, якщо ввімкнений режим ітерацій у діалоговому вікні Параметри. Тут для розв'язування задачі достатньо двох клітинок (див. рис. 45, рядок 17). Цей спосіб розглянемо під час виконання роботи.

  1.  Індивідуальне завдання

Задача 1 «Нарахування зарплатні»

У відомості нарахування зарплатні є прізвища шести-восьми працівників, які мають один із трьох кваліфікаційних розрядів: 1, 2, 3. Денна тарифна ставка залежить від розряду так (в у.о.):

                    12, якщо розряд = 3;

Ставка =      10, якщо розряд = 2;

                     8,  якщо розряд = 1.

Протягом місяця працівники зайняті різну кількість днів. Треба ввести кількість відпрацьованих днів і нарахувати зарплатню працівникам, якщо відрахування (податки тощо) становлять 13% від нарахувань. Скласти бухгалтерську відомість (див. рис. 1).

Задача 2 «Розв'язування нелінійного рівняння»

Дано нелінійне рівняння 2пх-п=sin x, де п — номер варіанта. Розв'язати рівняння методом простих ітерацій (див. рис. 2) і методом добирання параметра.

Хід роботи

1. Запустіть програму ЕТ, створіть нову книжку, назвіть аркуш Зарплатня і задайте режим відображення формул.

2. Створіть список користувача з прізвищами шести працівників.

Рис. 1. Зразок розв'язування задачі 1.

Не можна вводити дані, дивлячись на цю таблицю.

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

У таблиці вводьте дані лише в стовпці А, В, С, D, пам'ятаючи, що є лише три розряди (1,2,3) і днів у місяці є не більше ніж 31.

Адреси                              Дані

 А1                         Відомість нарахування зарплатні від

 Е1                          =СЕГОДНЯ() або =TODAY()

Примітка: введіть дату і виберіть для неї формат

 B2                        Тарифні ставки:

 D2                            8

 Е2                            10

 F2                            12

 G2                           0,13

 A3                           Номер

 B3                          Прізвище

 С3                           Розряд

 D3                           Днів

 Е3                           Тариф

 F3                            Нараховано

 G3                           Відрахування

 Н3                           Видати

 А4                           1

 B4                          <Конкретне прізвище 1>

 С4                           <конкретний розряд: 1, 2 або 3>

 D4                          <кількість відпрацьованих днів >

 А5                           2

 В5                          <конкретне прізвище>

 С5                          <конкретний розряд >

D5                          <кількість відпрацьованих днів >

і т.д. (введіть дані для шести працівників)

4. Уведіть формули розв'язування задачі:

   Е4         =ЕСЛИ (C4=1;$D$2; ЕСЛИ (С4=2; $Е$2; $F$2))

   F4         = D4 * Е4

   G4        = F4 * $G$2

   Н4        = F4 - G4

5. Скопіюйте формули в усю робочу таблицю.

6. Уведіть формули для обчислення балансу:

   D10      Всього

   F10       <обчисліть суму в стовпці F>

   G10      <обчисліть суму в стовпці G>

   Н10      <обчисліть суму в стовпці Н>

   Н12      =G10+H10

   Н13      ЕСЛИ (Н12=F10;"ОК";"Помилка")

7. Скасуйте режим відображення формул.

Скільки всього нараховано зарплатні? Чи збігається баланс? Скільки повинен отримати другий працівник?

8. Скопіюйте таблицю на другий аркуш.

9. На аркуші 2 підвищить денну оплату праці (тарифні ставки) всім категоріям на три одиниці і зменшить відрахування на 2%.

Скільки всього нараховано зарплатні тепер? Чи збігається баланс? Скільки тепер повинен отримати другий працівник?

10. Зніміть захист з даних у стовпці Днів. Захистіть решту таблиці від несанкціонованих змін, задавши пароль: money.

Виберіть діапазон даних зі стовпця D і ліквідуйте захист його клітинок командами Формат > Клітинки > Захист > Вимкніть перемикач захисту клітинки. Решту клітинок захистіть командою Сервіс Захист > Захистити > Лист. Пам'ятайте: коли вводять пароль, на екрані відображаються зірочки. Переконайтеся, що в стовпець С внести зміни не можна. Внесіть зміни в стовпець D: другий робітник відпрацював 28 днів. Скільки він заробив?

11. Перейдіть на аркуш 3. Додайте до таблиці три стовпці: Початок, Стаж, Премія. У стовпець Початок введіть дати початку трудової діяльності працівників.

12. У наступному стовпці Стаж обчисліть стаж працівників ( у роках).

Від дати в $Е$2 потрібно відняти дати зі стовпця Початок і результат поділити на 365. Формат клітинки задати як числовий загальний.

13. Нарахуйте премію працівникам від нарахованої суми 10%, якщо стаж до 5 років, і 25%, якщо стаж більший.

14. Перейдіть на наступний аркуш для розв'язування задачі 2.

15. Запишіть своє рівняння у вигляді 0=f(x) і розв'яжіть його методом підбору параметра.

Введіть у А1 деяке близьке до х число або будь-яке число. У клітинку А2 введіть =f(Al), тобто формулу = 2*n*Al-n-sin(n*Al), де замість п вводьте номер варіанта. Виконайте команди Сервіс Підбір параметра. Заповніть в отриманому діалоговому вікні три поля так: А2, 0, А1 => ОК. Відповідь буде в клітинці А1.

Рис. 2. Зразок розв'язування задачі 2.

Не можна вводити дані з цієї таблиці.

16. На наступному аркуші розв'яжіть нелінійне рівняння методом простих ітерацій.

Введіть заголовок таблиці і назви стовпців так (рис.2):

  А1  Розв'язування нелінійного рівняння

  А2  <введіть вигляд свого рівняння >

  A3  методом простої ітерації

  А5  Попередня Примітка: маємо на увазі ітерації

  B5  Наступна

  С5  Похибка

17. Уведіть формули розв'язування задачі 2:

  А6  <введіть будь-яке ЧИСЛО

  B6  <введіть свою формулу методу простої ітерації >

  С6  =abs(B6-A6)

  А7  =В6

Більше нічого вводити не треба.

18. Скопіюйте формули з А7, В6, С6 вниз до 13-го рядка. 

Скільки буде виконано ітерацій?

19. Скасуйте режим відображення формул і в клітинці В13 отримаєте результат.

Який результат і яка різниця між двома останніми наближеними значеннями? У скільки разів зменшується ця різниця (похибка ) після кожної ітерації?

20. Сформуйте числові дані, щоб було п'ять знаків після десяткової крапки.

21. Скопіюйте таблицю на наступний аркуш і переробіть її так, щоб розв'язати цю ж задачу, користуючись іменами діапазонів.

Замість назви стовпця Попередня введіть назву х, замість назви Наступна - z. Відповідно змініть формули в клітинках В6 і С6. Виконайте додатково ще дві ітерації. Яка відповідь тепер?

22. На наступному аркуші розв'яжіть нелінійне рівняння засобом Solver (Пошук розв'язку).

Сервіс > Пошук розв'язку. Заповніть діалогове вікно (рис. 3): клітинка-ціль (це А2 чи інша), куди заздалегідь було введено формулу =f(Al), цільове значення — 0, змінюючи клітинку А1. Обмежень немає. Виконати.

Рис. 3. Вікно програми Пошук розв'язку для задачі 2.

23. Розв'яжіть задачу 2 у двох клітинках.

Задайте режим ітерації: Сервіс > Параметри > Обчислення > Ітерації > ОК. У клітинку А17 введіть будь-яке початкове наближення. У клітинку В17 введіть праву частину формули методу простої ітерації як вираз від А17, наприклад, =(sin(A17)+l)/2. У клітинку А17 введіть цю ж формулу як вираз від В17, тобто =(sin(B17)+l)/2. Який одержали результат?

24. Збережіть книжку на диску.

25. Продемонструйте сім аркушів і закінчіть роботу.

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

1. Як реалізуються розгалуження в ЕТ?

2. Яке значення функції ЯКЩО(1+3>5-2; 1; 2)? 8. Як обчислити суму чисел у стовпці?

4. Який загальний вигляд має функція ЯКЩО?

5. Як скопіювати формулу? Яке значення функції ЯКЩО(5=5; МІН(4; 6); МАКС(4; 6))?

6. Які ви знаєте логічні функції?

7. Яке значення функції I(2=2; 3=3; 3<4)?

8. Яке значення функції ЯКЩО(2>1; 10-5; 20/2)?

9. Яке призначення кнопки Автосума?

10. Що таке абсолютна і змішана адреси клітинки?

11. Як скопіювати таблицю на іншу сторінку?

12. Яка відмінність між відносними і абсолютними адресами?

13. Яке значення функції ЯКЩО(1=2; 15; ЯКЩО(TRUE;20;25)?

14. Яке значення функції АБО(1=2; 3=3; 4=5; 0,5>sin(250))?

15. Як ввести дату в клітинку?

16. Який загальний вигляд має логічна функція І?

17. Який розділювач можна використовувати у списках аргументів функції?

18. Як визначити, скільки днів минуло від вашого дня народження?

19. Як вставити стовпець у таблицю? Яке значення ЯКЩО(5>2; ЯКЩО(5<3; 2; 4); 8)?

20. Як заповнити стовпець значеннями арифметичної прогресії?

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

22. Який загальний вигляд має логічна функція АБО?

23. Як вилучити рядок з таблиці?

24. Як розграфити таблицю? Яке значення функції ЯКЩО(АБО(3<5; 4<8); 8; 12)?

25. Яку стандартну функцію заміняє кнопка Автосума?

26. Як виокремити несуміжні діапазони клітинок?

27. У чому полягає метод підбору параметра?

28. Як перейти на іншу сторінку? Як перейменувати сторінку?

29. Як очистити весь стовпець? Яке значення ЯКІЦО(8>2; СУММ(2; 8); МАКС(2;8))?

30. Які задачі можна розв'язати методом підбору параметра?

31. Які є функції для роботи з датами?

32. Яка відмінність між логічними функціями І та АБО?

33. Що таке засіб «Пошук розв'язку» (Solver)?

34. Яке значення функції ЯКЩО(2>1; ЯКЩО(1>2; 5; 8); 6)?

35. Які є способи розв'язування нелінійного рівняння?

  1.  Оформлення звіту.
  2.  Захист роботи.


 

А также другие работы, которые могут Вас заинтересовать

48734. Проектирование электрической сети для электроснабжения потребителей цнлюлозно – бумажной промышленности 1.35 MB
  По заданному расположению источника питания и потребителей электроэнергии составляем схемы электрической сети. Находим расстояние между районной электрической станцией РЭС и подстанциями, и между подстанциями.
48735. Проектирование электрической сети 1.54 MB
  Главным потребителем электроэнергии является промышленность. За последние 10 лет в структуре энергобмена страны более чем в два раза увеличилась доля электроэнергии, потребляемой на потенциально-бытовые нужды
48737. Коэффициент передачи двигателя по регулирующему воздействию 662.5 KB
  Исходя из данной САР для устойчивого состояния системы необходимо чтобы Мвр = Мс. В соответствии с уравнением замыкания системы et = yt – x1t Þ DUt = Uyt – Uтгt 4. мы снова пришли к устойчивому состоянию системы при котором Мвр = Мс. Принцип работы системы.
48738. ІНДИВІДУАЛЬНІ НАВЧАЛЬНО-ДОСЛІДНІ, ТВОРЧІ ТА ТЕСТОВІ ЗАВДАННЯ З ТЕОРІЇ ТА МЕТОДИКИ ВИКЛАДАННЯ ГІМНАСТИКИ 428.5 KB
  Вивчення студентами дисципліни Теорія та методика викладання гімнастики†крім засвоєння теоретичних відомостей щодо основ теорії й організації навчання гімнастичних вправ техніки їх виконання і методики проведення передбачає опанування практичних вмінь і навичок виконання гімнастичних елементів. Виходячи з цього у цій методичній розробці подані елементи практики гімнастичні вправи і тестові завдання що виносяться на підсумковий модульний контроль складання яких є обов’язковим.010203 – Здоров'я людини повинні знати: стройові...
48740. РАЗРАБОТКА МАТЕМАТИЧЕСКИХ МОДЕЛЕЙ ЭЛЕКТРОННЫХ СХЕМ В РАЗЛИЧНЫХ РЕЖИМАХ ИХ РАБОТЫ 1.68 MB
  К основным качественным показателем и параметрам усилителя относятся коэффициент передачи коэффициент усиления Кр входное и выходное сопротивлениях Zвх Zвых динамический диапазон коэффициент нелинейных искажений коэффициент шума...
48741. Журналістський фах. Методичні вказівки 42 KB
  Огляд коментар стаття рецензія лист кореспонденція: характерні риси і тенденції у сучасній пресі на прикладі одного або групи видань 1215. Літературна основа кореспонденції на прикладі. Факт як фундамент кореспонденції на прикладі. Адресність і документальність кореспонденції на прикладі.
48742. Направления совершенствования деятельности предприятия 450.5 KB
  Существует связь между информационными технологиями и менеджментом. Менеджеру все время приходится принимать решения в условиях большой неопределенности: инфляция, изменения валютного курса, изменение налоговых и правовых условий работы...