17171

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

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

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

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

Украинкский

2013-06-29

259 KB

75 чел.

Практична робота №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.  Захист роботи.


 

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

26636. САНИТАРНАЯ ОЦЕНКА ТУШ И ОРГАНОВ ПРИ ЧУМЕ СВИНЕЙ И БОЛЕЗНИ АУЕСКИ 6.13 KB
  САНИТАРНАЯ ОЦЕНКА ТУШ И ОРГАНОВ ПРИ ЧУМЕ СВИНЕЙ И БОЛЕЗНИ АУЕСКИ. БОЛЕЗНЬ АУЕСКИ острое инфекционное вирусное заболевание животных характеризующееся поражением ЦНС органов дыхания и сильным зудом в месте проникновения возбудителя; у свиней зуда не бывает. У взрослых свиней доброкачественно 13 дня выздоровление. У свиней овец отек гортани легких в дыхательных путях пенистая жидкость; часто поражаются гортань трахея.
26637. СЕПАРИРОВАНИЕ МОЛОКА. РАСЧЕТ ВЫХОДА СЛИВОК 7.84 KB
  СЕПАРИРОВАНИЕ МОЛОКА. Сепаратор аппарат служащий для разделения цельного молока на сливки и обезжиренное молоко. Через соответствующие отверстия разделенные фракции молока направляются в приемники рожки. Сепараторы могут быть открытые с поступлением молока и отводом сливок и обезжиренного молока открытыми потоками.
26638. СОЗРЕВАНИЕ МЯСА. ТОКСИКОИНФЕКЦИИ ПАРАТИФОЗНОГО ХАРАКТЕРА 21.82 KB
  СОЗРЕВАНИЕ МЯСА. Мясо только что убитого животного имеет плотную консистенцию при варке дает неароматный бульон из такого мяса почти невозможно выделить мясной сок реакция его близка к нейтральной оно жесткое плохо усваивается. В течение первых 24 часов после убоя животного в зависимости от температуры и других факторов пищевые качества и внешние показатели мяса резко меняются: мясо становится нежным мясной сок легко отделяется при варке мясо дает прозрачный ароматный бульон реакция его смещается в кислую сторону мясо хорошо...
26639. СОРТИРОВКА ЖИВОТНЫХ ПО УПИТАННОСТИ 26.52 KB
  Животные как сырье для мясоперерабатывающих предприятий должны отвечать определенным категориям упитанности. 1 категория мускулатура развита удовлетворительно формы туловища несколько угловатые лопатки выделяются бедра слегка подтянуты; остистые отростки спинных и поясничных позвонков седалищные бугры и маклоки выступают но не резко; отложения подкожного жира прощупываются у основания хвоста и на седалищных буграх щуп выполнен слабо; у волов мошонка слабо заполнена жиром и на ощупь мягкая. II категория мускулатура развита менее...
26640. СОСТАВ КОРОВЬЕГО МОЛОКА 14.01 KB
  СОСТАВ КОРОВЬЕГО МОЛОКА. Коровье молоко материнское молоко коров производится в больших количествах и является наиболее продаваемым видом молока животных. Витамины пигменты ферменты гормоны микроколичества Газы 5÷7 см на 100 см молока Углекислый газ 50÷70 Азот 20÷30 Кислород 5÷10 Аммиак следы. Сухой молочный остаток остаток после высушивания навески молока до постоянного веса при t=102÷105 C.
26641. Вертикальная зональность океана 158 KB
  Общепринятой во всех странах схемы вертикальных зон океана к сожалению пока не существует. Кроме того в некоторых частях Мирового океана различают также: псевдобатиаль фауну внутришельфовых депрессий от 250400 до 1200 м отделенных более или менее мелководными порогами обычно менее 200 м от собственно батиальной зоны примеры: фауна более или менее изолированных глубинных котловин норвежских фьордов и района Магелланова пролива Белого и Балтийского морей южной Аляски антарктического шельфа; псевдоабиссаль фауну обширных...
26642. Круговорот веществ в биосфере 88 KB
  Биогеохимические круговороты. Круговорот веществ в биосфере. Круговорот углерода. Круговорот кислорода.
26643. КУЛЬТУРНЫЙ ЛАНДШАФТ 27 KB
  Ландшафт культурный географический ландшафт измененный хозяйственной деятельностью человеческого общества и насыщенный результатами его труда. и природным ландшафтом нет резкой грани: в Л. основывается на познании связей как между компонентами ландшафта так и между его морфологическими составными частями урочищами фациями и предусматривает достижение максимального воспроизводства естественных в первую очередь биологических ресурсов предотвращение неблагоприятных природных процессов создание здоровой среды для жизни человека...
26644. Ландша́фт 38.5 KB
  Landschaft вид местности от Land земля и schaft суффикс выражающий взаимосвязь взаимозависимость понятие употребляющееся в разных но связанных между собою значениях в географии ландшафтной экологии живописи ландшафтной архитектуре компьютерной графике и т. История понятия Пример ландшафтной живописи Питер Брейгель. Впервые слово ландшафт прозвучало в IX веке в трудах монахов Фульдского монастыря в Германии. Ландшафт укладывается в рамки административнотерриториального и административного понятия.