Первая страница
Наша команда
Контакты
О нас

    Головна сторінка



Спецкурс «Microsoft Excel у профільному навчанні» Практична робота Тема “Автоматизоване вибирання даних із таблиць»

Скачати 100.52 Kb.

Спецкурс «Microsoft Excel у профільному навчанні» Практична робота Тема “Автоматизоване вибирання даних із таблиць»




Скачати 100.52 Kb.
Дата конвертації16.06.2017
Розмір100.52 Kb.





Спецкурс «Microsoft Excel у профільному навчанні»
Практична робота 5. Тема “Автоматизоване вибирання даних із таблиць»
Мета практичної роботи: навчитися обчислювати підсумкові характеристики для об'єктів, що задовольняють певним критеріям відбору, а також вибирати такі об'єкти.

Завдання 1

За таблицею, яка містить дані спостереження за погодою протягом місяця (рис. 1), необхідно визначити:



  • кількість ясних, хмарних та дощових днів;

  • середню температуру ясних, хмарних та дощових днів;

  • мінімальну вологість у хмарні вітряні дні;

  • середню температуру в дні, коли кількість опадів була меншою або більшою за введену користувачем величину.
    Найвища температура, досягнута за участі людини, ~ 10 трлн К (що є порівнянним з температурою Всесвіту у перші секунди його існування) була досягнута у 2010 році під час зіткнення іонів свинцю, прискорених до світлових швидкостей. Експеримент було проведено на Великому адронному колайдері.
    Крите́рій (від лат. critērium, яке зводиться до грец. χριτήριον - здатність розрізнення; засіб судження, мірило, пов'язаного з грец. χρινω - розділяю, розрізняю) - мірило, вимоги, випробування для визначення або оцінки людини, предмета, явища; ознака, взята за основу класифікації.
    Кількість - в Арістотелівській логіці друга з 10 категорій (класів, розрядів, які спрощують процес розумового визначення будь-якої речі), побічна обставина матеріальних речей , за допомогою якої вони поширюються в просторі, вимірюються якоюсь математичною нормою і здатні бути поділеними на окремі частини.
    Воло́гість - міра насиченості речовини водою. У поняття вологості не входить вміст хімічно зв'язаної (конституційної), а також кристалогідратної води.
    Температу́ра (від лат. temperatura - належне змішування, нормальний стан) - фізична величина, яка описує стан термодинамічної системи.
    Атмосфе́рні о́пади - вода в рідкому чи твердому стані, що випадає з хмар чи безпосередньо з повітря на земну поверхню та предмети. З хмар випадають: дощ, мряка, сніг, мокрий сніг, крупа, град, льодяний дощ.


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


Рис. 1. Дані спостереження за погодою протягом місяця

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



  1. Створіть електронну таблицю на аркуші 1 (дайте йому імя Практ 5-1) за наведеним на рис.
    Електро́н (грец. Ηλεκτρόνιο, англ. electron, нім. Elektron) - стабільна, негативно заряджена елементарна частинка, що входить до складу всіх атомів. Має електричний заряд (-е= −1,6021892(46)×10−19 Кл) і масу (9,109554(906)×10−31 кг).
    1 зразком і збережіть у файлі Практ_5.х1sx, або просто скористайтесь заготовкою Спецкурс ЕТ/Розділ 5/ заготовка до ПР5 завдання 1

  2. У клітинках I1:J12 створіть критерій відбору днів, а нижче – таблицю для занесення результатів обчислень (рис. 2).
    Результат, пі́дсумок, (заст. ску́ток, вислід) - кінцевий наслідок послідовності дій. Можливі результати містять перевагу, незручність, вигоду, збитки, цінність і перемогу. Результат є етапом діяльності, коли визначено наявність переходу якості в кількість і кількості в якість.
    Обчи́слення - є гілкою математики, зосередженою на функціях, похідних, інтегралах, і нескінченному ряду чисел. Цей предмет являє собою важливу частину сучасної математичної освіти. Воно складається з двох основних галузей - диференціального і інтегрального численнь, які пов'язують основні теореми обчислення.
    Щоб не припуститися помилок, назви стовпців у критеріях не вводьте вручну, а копіюйте з заголовку основної таблиці.

  3. У клітинки справа від напису Ясних днів уведіть формулу для обчислення кількості ясних днів =DCOUNTA(A2:G30;A2;I5:I6). Тут A2:G30 – вся таблиця, А2 – заголовок стовпця, у якому шукатиметься кількість не порожніх клітинок (це може бути й інший стовпець) , I5:I6 – критерій відбору днів.

а) б)

Рис. 2. Таблиці для обробки календаря погоди: а — критерії відбору днів; б — таблиця для занесення результатів обчислень



  1. Самостійно введіть формули для обчислення кількості хмарних і дощових днів (порівняно з ясними днями потрібно буде змінити лише критерії відбору).

  2. Самостійно введіть формули для обчислення середньої температури в ясні, хмарні та дощові дні, скориставшись для цього функцією DAVERAGE.

  3. Обчисліть мінімальну вологість у хмарні вітряні дні. Для цього скористайтеся функцією DMIN. Заголовок стовпця, у якому шукатиметься мінімальне значення, міститься у клітинці D2, а критерій відбору — у діапазоні I1:J3.
    Діапазо́н (від грец. δϊα πασον (χορδων) - через усі (струни).
    Його можна розтлумачити так: «погода хмарна і вітер сильний» або «погода хмарна і вітер помірний».

  4. Обчисліть середню температуру в дні, коли кількість опадів була меншою або більшою за введену користувачем величину.
    Найвища теоретично можлива температура - планківська температура. Вища температура за сучасними фізичними уявленнями не може існувати, оскільки надання додаткової енергії системі, нагрітої до такої температури, не збільшує швидкості частинок, а лише породжує у зіткненнях нові частки, за цієї обставини кількість частинок у системі зростає й зростає маса системи. Вище за планківську температуру гравітаційні сили між частинками стають порівняними із силами решти фундаментальних взаємодій. Можна вважати, що це температура «кипіння» фізичного вакууму. Вона приблизно дорівнює 1.41679(11)×10 K (~ 142 нонільйони K).
    Напрямок руху повітря. Більше всього опадів випадає там, де переважають висхідні рухи повітря, і менше - при спадних. Екваторіальна зона низького тиску (2000-3000 мм/рік і більше). Навітряні схили височин і гірських хребтів одержують більше атмосферних опадів, ніж підвітряні схили. Найвологіше місце на Землі, Черапунджі (близько 12 000 мм опадів на рік) знаходиться на навітряному схилі Гімалаїв на висоті близько 1300 м у Індії.
    Для цього також скористайтеся функцією DAVERAGE. Критерій відбору міститиметься у клітинках І11:І12. Коли ви введете відповідну формулу, у клітинку І12 вводьте вирази на кшталт >10 або <20, і обчислюване за формулою значення змінюватиметься. Збережіть отриману таблицю.

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

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


Завдання 2

Застосуємо функції для роботи з базою даних для наближеного розв'язання алгебраїчного рівняння вигляду f(x) = 0 на заданому користувачем відрізку.

Озна́чення, ви́значення чи дефіні́ція (від лат. definitio) - роз'яснення чи витлумачення значення (сенсу) терміну чи поняття. Слід зауважити, що означення завжди стосується символів, оскільки тільки символи мають сенс що його покликане роз'яснити означення.
Алгебра (від араб. الجبر‎ аль-джебр - відновлення) - розділ математики, що вивчає математичні операції і відношення, та утворення, що базуються на них: многочлени, алгебраїчні рівняння, алгебраїчні структури.

Припустимо, що користувач вводить межі відрізка [а;b] у клітинки F1 і F2. Поділимо цей відрізок на 100 рівних інтервалів: обчислимо величину одного інтервалу у клітинці F3, точки поділу (значення х) — у стовпці А, а значення функції f(x) у точках поділу — у стовпці В. Наближеними коренями рівняння будуть ті точки, де функція f(x) змінює знак. Зауважимо, що f(xn) та f(xn 1) мають різний знак тоді і тільки тоді, коли f(xn)-f(xn i) < 0. Враховуючи, що точні корені — це точки, де f(x) = 0, отримаємо критерій, за яким визначатимемо корені рівняння f(xn)-f(xn i) < 0.

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


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

1. Додайте аркуш у електронну книгу з іменем Практ 5-2. Створіть та відформатуйте нову електронну таблицю за зразком, наведеним на рис. 3, встановивши межі для стовпців А:С (де міститимуться розрахункові дані), діапазонів F1:F3 (де зберігатимуться значення кінців відрізку та величина інтервалу поділу), Н1:Н2 (тут міститиметься критерій пошуку значень х) та клітинки Н4 (у ній обчислюватиметься корінь рівняння). Задайте, де потрібно, заливку та введіть заголовки.



Рис. 3. Обчислення кореня рівняння на відрізку

  1. Уведіть у клітинку F3 формулу для обчислення величини інтервалу поділу: h = а)/100 (значення кінців відрізку
    містяться у клітинках F1 і F2).

  2. У клітинку А2 введіть формулу =F1 (перша точка поділу дорівнюватиме а).

  3. У клітинку A3 введіть формулу для обчислення наступної точки поділу: х1 = хо h. Скопіюйте формулу в клітинки діапазону А4:А102. Оскільки значення h у формулі хп 1 = хп h незмінне, то посилання на клітинку F3, де це значення міститься, має бути зафіксоване: F$3.

  4. У клітинку В2 введіть формулу для обчислення значення функції f{x), наприклад =SIN(A2), і скопіюйте її в клітинки діапазону В3:В102.

  5. У клітинку С2 введіть формулу для обчислення величини f(xn)-f(xn 1), тобто =В2*ВЗ, і скопіюйте її в діапазон С3:С101.

  6. Уведіть у клітинку Н2 критерій, якому має задовольняти величина f(xn)-f(xn i), тобто вираз <=0.

  7. У клітинку Н4 введіть формулу для обчислення найменшого кореня рівняння fix) = 0 на відрізку [а;Ь]. Для цього скористайтеся функцією DMIN. її аргументи будуть такими: вся таблиця міститься в діапазоні А1:С101, критерій пошуку - у діапазоні Н1:Н2, а назва стовпця (х), у якому ми шукатимемо значення кореня, — у клітинці А1.

Збережіть електронну таблицю у файлі Практ_5_2.хlsx.

9. Самостійно введіть формули для обчислення кількох наступних коренів рівняння. Розв'язування рівняння sinx = 0 на відрізку [0;10] проілюстровано на рис. 4. У кожній формулі використовується та сама функція DMIN, але різні критерії (Н1:І2, J1:К2 і т. д.). Ці критерії можна сформулювати так: величина зміна знаку менше або дорівнює 0, а значення х більше за значення попереднього кореня. Сформувати умови, що накладаються на значення х, можна за допомогою операції конкатенації рядків, з'єднавши символ «>» зі значенням попереднього кореня. Так, у клітинку 12 введено формулу =">"&Н4, у клітинку К2 — формулу =">"&J4 тощо.



Рис. 4. Обчислення кількох коренів рівняння

Завдання 3

За таблицею, яка містить прайс навчальної літератури Видавничої групи BHV за 2009 рік (рис. 5), потрібно визначити:

♦ середню вартість товстих книжок видавництва (обсяг яких перевищує 300 сторінок);

♦ кількість книжок, співавтором яких є автор Завадський І.О.;

♦ найбільшу кількість сторінок книги, серед авторів якої є Завадський І. О.;


  • чи є у видавництві книга з веб-дизайну та хто її автори;

  • авторів та назву найдешевшої книжки 2006 року;

  • назву видання 2009 року, співавтором якого є Стеценко І.В.;

  • загальну вартість одного повного комплекту посібників для школи.
    Зава́дський (пол. Zawadzki) - польське прізвище. Виникло на ґрунті польської та української мов на означення людини, що приїхала з містечка Завада, Завадів, Завадівка. В основі цих топонімів лежить слово «завада» (перепона, перешкода).
    Співавторство - це спільна творча діяльність щодо створення твору. Співавторство може виникати за наявності письмової або усної угоди про сумісну співпрацю над створенням твору. Співавтори є первісними суб'єктами авторського права.
    Веб-дизайн (англ. web design) - галузь веб-розробки, що охоплює цілий ряд напрямів і дисциплін із створення та супроводу сайтів або веб-застосунків, таких як графічний веб-дизайн, проектування інтерфейсів, авторинг (у тому числі стандартизований код і власницьке програмне забезпечення), використовність та оптимізація для пошукових систем.
    Видавни́цтво - спеціалізоване підприємство, основним видом діяльності якого є підготовка і випуск у світ видавничої продукції (Закон від 05.06.1997 р. № 318/97-ВР «Про видавничу справу»).
    Навчальний посібник - видання, яке частково доповнює або замінює підручник у викладі навчального матеріалу з певного предмета, курсу, дисципліни або окремого його розділу, офіційно затверджений як такий.


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

1. У електронній книзі Практ_5.х1sx, створіть таблицю за зразком, наведеним на рис. 5, на аркущі з іменем Практ 5-3 або скористайтеся заготовкою Спецкурс ЕТ/Розділ 5/ заготовка до ПР5 завдання 3.



Рис. 5. Прайс навчальної літератури Видавничої групи BHV

2. Для визначення середньої вартості товстих книжок (обсягом понад 300 сторінок) введіть у клітинки Н1:Н2 критерій відбору, а в клітинку І3 — формулу =DAVERAGE(A2:G18;F2;H1:H2) (рис. 6).



Рис. 6. Обчислення середньої вартості товстих книжок за допомогою функції DAVERAGE

  1. Уведіть у клітинки Н5:Н6 критерій, зображений на рис. 7, а в клітинки І7 та І8 самостійно введіть формули для визначення кількості книжок, співавтором яких є Завадський І.О., та найбільшої кількості сторінок у таких книжках. Скористайтеся для цього функціями DCOUNTA та DM АХ. ПРИМІТКА. Для відбору всіх книжок, для яких стовпець Автори містить прізвище Завадський І.О., умову слід ввести у вигляді маски *3авадський І.О.*, де символ «*» позначає довільну кількість будь-яких символів.

Рис. 7. Застосування функції DCOUNTA та DMAX

4.

Застосунок, застосовна програма або прикладна програма (англ. application, application software, app) - користувацька комп'ютерна програма, що дає змогу вирішувати конкретні прикладні задачі користувача.
Для визначення авторів книги з веб-дизайну введіть критерій відбору у клітинки Н10:Н11, й формулу — у клітинку І12 (рис. 8). Використайте функцію DGET, підібравши самостійно її аргументи.

ПРИМІТКА. Якщо в базі даних є книга з веб-дизайну і вона одна, то у клітинці І12 буде виведено прізвище авторів книги (рис. 8). Якщо таких книг декілька, то функція DGET поверне значення помилки #NUM!. Якщо книг з веб-дизайну немає, то функція DGET поверне значення помилки «VALUE!

Рис. 8. Застосування функції DGET

5. Авторів та назву найдешевшої книжки 2006 року шукатимемо у два етапи: спочатку визначимо найнижчу ціну на книжки 2006 року видання, а потім — авторів та назву книжки з такою ціною.

а) У клітинки Н14:Н15 введіть критерій відбору (рис. 9).

б) У клітинку Н16 уведіть заголовок критерію для другого запиту (скопіюйте його з клітинки F2), а в клітинку Н17 — формулу =DMIN(A2:G18;F2;H14:H15). Результат обчислення цієї формули буде умовою для наступного запиту



Рис. 9. Результат дії функції DMIN

в) У клітинку І18 уведіть формулу для визначення авторів найдешевшої книжки 2006 року, в якій використайте функцію DGET. Критерій вибору об'єктів міститься в діапазоні Н16:Н17.

г) У клітинку І19 самостійно уведіть формулу для визначення назви найдешевшої книжки 2006 року (рис. 10).

Рис. 10. Визначення авторів та назви найдешевшої книги 2006 року

6. У клітинках Н21:І22 створіть критерій відбору для визначення видання 2009 року, співавтором якого є Стеценко І.В. Оскільки критерій складається з двох частин, з'єднаних сполучником «і», його умови слід записати в одному рядку (рис. 11). Функцію для визначення назви цього видання введіть у клітинку І24 самостійно.



Рис. 11. Критерій для визначення видання 2009 року, співавтором якого є Стеценко І.В.

7. Для визначення вартості повного комплекту посібників для школи застосуйте функцію DSUM (рис. 12).



ї аргументи введіть самостійно.

Рис. 12. Результат застосування функції DSUM
8. Визначте за допомогою функцій для роботи з базою даних середню вартість видань, співавторами яких є Пасічник В.В., Петренко А.І. або Ковалюк Т.В.


Скачати 100.52 Kb.

  • Завдання 3