Контрольні питання

1. Що таке А-версії стандартних статистичних функцій?

2. Чи можна за допомогою функцій МИН, МАКС визначити найбільш ранню або останню дату в діапазоні дат?

3. Чи різняться функції СЧЁТ і СЧЁТЗ?

4. Чи можна використовувати функцію СЧЁТЕСЛИ для розрахунку текстових значень?

5. Чи можна не зазначити діапазон підсумовування при використанні функції СУММЕСЛИ?

6. Для чого використовують стандартне відхилення?

7. Як взаємопов’язані стандартне відхилення і дисперсія?

8. Який відсоток значень випадкової величини перебуває в межах одного стандартного відхилення від середнього значення?

9. Призначення функцій ПЗ і НПЗ. Чим вони різняться?

10. Яка фінансова функція дає змогу обчислити суму, нагромаджену в результаті періодичних виплат?

11. Як знайти розмір періодичних виплат для погашення позики?

12. Як оцінити термін погашення позики при фіксованому розмірі періодичних виплат?

Залікова практична робота

Обчислення пропорцій. Учителеві інформатики, безперечно, потрібно мати свій власний комп'ютер у будинку, і дійсно, у багатьох він стає основним робочим місцем. Причому треба врахувати, що куплена, допустимо, два-три року тому назад машина вже безнадійно застаріла. Так що рано або пізно, але наступає Пора для нового придбання.

У Москві є багато фірм, і великих, і зовсім крихітних, що пропонують своєму клієнтам той або інший асортименти комп'ютерних комплектуючих за роздрібними цінами. Власне кажучи, можна взяти й просто готовий комп'ютер, що, втім, тут же й збирається з тих же самих комплектуючих. Звичайно за роботу зі зборки навіть не нараховують ніякої додаткової плати, тому що фірма зацікавлена, щоб як можна більше замовлення зробили саме в неї, а не в конкурентів.

Здавалося б, чого ще бажати покупцеві? Однак якщо він спокушений у цих справах, то часом схильний, прицінившись у різних місцях, усе скомпонувати сам, власними руками, виходячи зі своїх поглядів про склад комп'ютера, який би максимально відповідав наявним коштам. І от зведені з безлічі прайс-листів в одну таблицю ціни, дають загальну картину ринку, постійне відстеження якої, між іншим, саме по собі є немаловажним показником грамотності користувача.

Це реальний стан московського ринку на середину-кінець 1999 року. Заради зручності огляду взяті для розгляду лише процесори виробництва фірми Intel (box-поставка, тобто в коробках), починаючи від самих молодших моделей Celeron, і кінчаючи тільки що з'явилися в продажі останніми розробками PentiumIII, які можуть працювати при частоті системної шини в 133 Мгц.

Порожні клітинки означають відсутність товару на даний день. Але чого немає сьогодні, - те з'явиться завтра, і ми можемо обчислити очікувану ціну, виходячи з наявних даних у сусідніх клітках. Самий прямий підхід - взяти, скажемо, для 400-го Пентіума в «Берізці» средньоарифметичне значення між 350-м й 450-м: вийде (124+168):2=146 доларів. Однак вірніше було б урахувати нерівномірність росту ціни, і розрахувати пропорцію, - по чотирьох осередках діапазону (В13:З14). Тоді вже вийде ледве більше доступна вартість 141, і, головне, 141 буде, очевидно, ближче до істини, чим 146. Отже й у першому стовпці, в «Алмера», 400-й коштує не (124+161 ):2= 142, а «лише» 135.

У такий же спосіб можна підраховувати ціни, позначаючи їх сірими кольорами шрифту, і в інших клітках, де вони відсутні. Правда, не у всіх, - адже пропорцію становлять чотири числа, і, виходить, для пошуку якогось одного з них, три інші повинні бути в наявності.

Зв'язок макросу з малюнком. У процесі подальших підрахунків положення буде полегшуватися тим, що значення, отримані на черговому кроці, потім уже самі можуть служити як вихідна інформація. Так, після визначення передбачуваної ціни 400-го Пентіума в «Берізці» стане можливим обчислювати, скільки він міг би коштувати в «Бэсме». Тільки тепер комірка, що цікавить нас (D13) утворить не верхній правий кут діапазону із чотирьох кліток, що становлять пропорцію, а нижній. Тому формулу прийдеться писати заново, і для наступної автоматизації такого роду дій нам краще скористатися макросами.

Добре б, до речі, викликати їх на виконання не по старинці, за допомогою тієї або іншої клавіатурної комбінації, типу Ctrl + якась буква, а цивілізованим образом, клацаючи мишею по заздалегідь намальованим для цієї мети екранним кнопкам, що утворять як би пульт керування. Кнопок буде потрібно, мабуть, чотири, по числу кутових елементів у пропорції, а щоб вони були розташовані рівно й красиво, треба використати різноманітні прийоми роботи із графічними об'єктами: тиражування, поворотів, вирівнювання й угруповання. У контекстному меню кожної з підготовлених у такий спосіб кнопок повинен бути присутнім пункт «Призначити макрос», до якого ми тепер і звернемося.

«Створити» означало б роботу над текстом процедури безпосередньо мовою VBA, нам же треба просто «Записати» (навіть не міняючи запропонованого імені макросу й не вводячи на з’явившійся слідом за цим додатковій картці ніякого «Сполучення клавіш»): (стати на D13)(уведення формули =C13/C12*D12) Еntег(знову на D13)КопіюватиПравкаСпеціальна вставкаЗначенняОК (тепер на G23) ПравкаСпеціальна вставка  Значення  ОК  ЕSС  Сервіс  Макроси  Зупинити запис.

Тут ми відразу ж подбали про те, щоб у наступних застосуваннях макросу не виникало б «циклічного посилання», коли D13 посилається на С13, а С13 посилається на D13, і саме із цієї причини, закінчивши саме обчислення, відразу замінили використану в комірці формулу на одне лише її числове значення. Так, крім того, ще й другою копією, в комірці G23, теж запаслися, - вона нам буде потрібна пізніше для усереднення результатів.

Використовувати макрос будемо так: ставити курсор у підходящу порожню клітку таблиці, для якої відомі дані, що знаходяться зверху й ліворуч, і клацати по наявній тепер у нашому розпорядженні екранній кнопці. Одне тільки очевидне виправлення необхідно попередньо зробити – забрати з тіла макросу ті два рядки, перший і третій, у яких зафіксований перехід конкретно на комірку D13 (під час самого запису без цього ніяк було не обійтися). Тоді вже цей виправлений нами макрос може бути застосований до будь-якої подібної комірки, на яку буде перед його викликом установлена курсорна рамка.

Повернення в поточну комірку. Аналогічні макроси треба записати й для заповнення порожніх клітинок, оточених даними з інших напрямків – зверху й праворуч, знизу й ліворуч. Але при цьому відразу виникає питання, як діяти з тими, які попадають і в одну, і в іншу категорії одночасно. Наприклад, якому значенню комірки C18 віддати перевагу, - обчисленому з лівої верхньої пропорції (340), або із правої верхньої (324)?

Природньою є така відповідь: до конкретної комірки потрібно застосувати всі можливі макроси по черзі і одержувані результати усереднити. Так, для C18 ми повинні після виконання двох згаданих макросів мати (340+324):2=332. І тут cкладається дивна, неоднозначна ситуація, тому що якщо піти далі, і точно так само обчислити значення в клітці C19, то тоді для C18 з'являться ще дві додаткові можливості, зв'язані тепер уже з нижніми пропорціями. Їх теж треба буде реалізовувати, внаслідок чого C18 знову зміниться, а виходить, прийдеться знову ж і С19 обчислювати ще раз, і т.д. по колу.

Це не так вже страшно, за умови, звичайно, що послідовність наближень виявиться збіжною до якоїсь межі. З технічної ж сторони нам просто треба буде знову мало-мало підправити кожний із чотирьох записаних макросів, так щоб після їхнього виконання курсор повертався назад, у ту ж клітку, на якій був. Текст виправлень легко одержати, записавши ще один, п'ятий макрос, допоміжний, не зв'язаний ні з якою із кнопок і потрібний нам тільки для того, щоб деякі рядки його тексту скопіювати через буфер у вже існуючі чотири.

Цей самий п'ятий макрос спочатку відобразив у собі наступні наші дії: (неважливо куди, наприклад на А22) ВставкаІм'яПривласнити(уведення «поточна»)ОК ПравкаПерейтипоточнаОКВставкаІм'яПривласнитипоточнаВидалить ОКСервісМакросиЗупинити запис

Завершивши запис, ми повинні були закоментувати апострофом закінчення першого програмного рядка, що вказує на стартовий осередок А22 (Аркуш CPU, Рядок 22, Стовпець 1; тут R походить від слова row, а С від column), і замість цього ввести адресу ActiveCell, тобто активна комірка.

Як було три діючі рядки, так і залишилося три. Тільки їхньої копії тепер потрібно розставити по тим чотирьом текстам, де містяться раніше записані макроси. Один від одного вони майже не відрізняються, і в самий початок кожного ми повинні помістити верхній рядок. Інші ж дві, нижні, підуть у самий кінець, перед End Sub. Більше нам п'ятий макрос не буде потрібний і його можна спокійно видалити. Але сам прийом - записувати якісь дії, і потім розбирати їх по рядках, - варто взяти на озброєння.

Обхід помилкових ситуацій. У таблиці цін на процесори, перед тим, як приступити до заповнення порожніх клітинок, нам залишилося розробити механізм усереднення. При виконанні кожного кнопкового макросу, одержуване числове значення копіюється, як ми це й задумували, у спеціально відведену для збору результатів область (F22:G23). Нехай у міру нагромадження даних у її чотирьох клітках, обумовлена середня величина виводиться в комірку А23, якщо тільки при обчисленні не трапиться помилок.

А помилки, легко догадатися, все-таки будуть час від часу виникати. По-перше, їхнім джерелом може служити відсутність чисел в області усереднення, наприклад, на самому початку процесу обробки даних, коли вийде ділення на нуль. Цю ситуацію ми, щоправда, досить легко вже вміємо виправляти, уводячи в комірку А23 відповідну формулу умови ECJIИ(EOШИБKA(CP3HAЧ(F22:G23));" ";OKPУГЛ(CP3HAЧ(F22:G23);0)). Але от що робити, якщо помилка відбудеться вже при обчисленні самої пропорції? Скажемо, якщо для клітки Е14 ми, переплутавши екранні кнопки, випадково викличемо на виконання макрос, що визначає її значення не з правої нижньої пропорції, а із правої верхньої. Тоді і у цый комірці, і у її відображенні на F23 з'явиться неприємне повідомлення #ДЕЛ/0!

Щоб уникнути такого результату ми востаннє внесемо невеликі зміни в текст кожного макросу – додамо по два рядки безпосередньо після того, де задається формула, що вводиться в комірку, обчислення пропорції (це третій і четвертий рядки) : On Error Resume Next - щоб однаково переходити до наступної команди, навіть при помилці; If ActiveCell.Value = 0 Then ActiveCell. Value = "ні " — а замість нулів виводити слово «ні». Тут ми використаємо звертання до вже знайомого об'єкта ActiveCell, але тільки уточнюємо, що мова йде про одну з його властивостей - про Value, тобто про ті значення, що міститься в активній комірці. Причому нулі нам не потрібні - інакше «Підсумок» буде невірним.

А кнопок екранних не перешкодить створити ще трохи, дуже вже вони зручні в роботі. Та, що зі стрілкою нагору, нехай служить для переміщення в розглянуту нами комірку її підсумкового результату, – уже після того, як будуть відпрацьовані всіх чотири макроси обчислення: (куди-небудь на А21, однаково потім адресу поміняємо на ActiveCell)BcтавкaІм’я Привласнити(уведення«поточна»)ОК(на А23)КопіюватиПравкаПерейти поточнаОКПравкаСпеціальна вставказначенняОКВставкаІм'я ПривласнитипоточнаВидалитиОК і на цьому зупинити запис

Перекреслений кружок іноді може знадобитися для очищення області усереднення: (знову на А21, з тим же змістом)ВставкаІм'яПривласнити(уведення «поточна»)ОК (виділяємо F22:G23)  (клавіша Dе1е1е)  Виправлення  Перейти  поточна  ОК  Вставка  Ім'я  ПрисвоїтипоточнаВидалитиОКзупинити запис.

Вкладення макросів. Наша таблиця, у принципі, уже працює, обчислюючи відсутні дані з наявних, але витрати часу занадто великі: треба ж стати на порожню клітку, так клацнути по разу всі екранні кнопки - спочатку ті чотири, які виконують обчислення, потім ту, що фіксує підсумок. Адже порожнійх - не одна й не дві, а без малого чотири десятки, навіть якщо не вважати рядки й стовпці, де немає ні однієї вихідної ціни, і не від чого відштовхнутися. Причому пройти прийдеться кілька кіл, перш ніж коливання усереднених значень затихнуть і картина стане самоузгодженою.

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

Відслідковувати це, до речі, треба б теж не на око, а регулярним способом, використовуючи, наприклад, додаткові дві таблиці, що повторюють нашу робочу й розташовані десь нижче, допустимо, у діапазонах (В26:М42) і (В44:М60). У першу з них треба буде щораз перед тим, як починати новий обхід осередків, копіювати вже наявні до того моменту значення, щоб з ними потім порівнювати те, що вийде. У другий же діапазон треба просто ввести самі формули порівняння: [B44] = ЕСЛИ(В4=В26;0;1) і т.д.

Не змінюючи своєї традиції, процес копіювання результатів ми зв'яжемо із черговою, уже восьмою по рахунку екранною кнопкою. І цей новий макрос, і попередній сьомий, утримуючий у собі виклик п'яти ще більш ранніх, обоє їх треба вкласти в останній, дев'ятий, завершальний, як фінальний акорд, всю нашу роботу. Включивши, як звичайно, запис вироблених дій і запам'ятавши, першою справою, вихідний стан таблиці клацанням по восьмій екранній кнопці, ми всього лише один раз пройдемо вручну по всім потрібним нам порожнім кліткам, роблячи для кожної операцію виробленого по її оточенню підрахунку, тобто клацаючи по сьомій кнопці. Дійшовши ж до кінця, запис зупинимо, і одержимо у своє розпорядження зручний інструмент, за допомогою якого можемо тепер спокійно робити коло за колом.

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

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

Саму роботу з формування критеріїв ми теж автоматизуємо, причому цього разу вже не одними лише макросами, а за допомогою елементів керування, тобто перемикачів, прапорців, прокручиваних або списків, що розкриваються; для їхнього створення є навіть спеціальна панель інструментів «Форми».

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

Почати треба з розміщення шести рамок, – по числу полів з технічними -характеристиками кожної моделі. При цьому уведення потрібних заголовків для рамок, замість чергових «Вікно групи таке-те», передбачений у контекстні меню. Усередині першої рамки «Обсяг» ми розташуємо п'ять однакових по розмірах прапорців, однак тепер уже не обмежуючись однією лише зміною супровідного тексту, а ще й визначаючи (знову ж з контекстного меню, пункт «Формат об'єкта», вкладка «Елемент керування»), з якими саме осередками таблиці їм слід встановити зв'язок – нехай це будуть осередку з R24 по R28. Точно так само в рамках «Буфер» й «Швидкість» розмістимо дві групи перемикачів, з яких перші п'ять усі повинні бути пов'язані з однією й тією ж коміркою, скажемо, U12, а інші три – теж, але зі своєю окремою Т12.

Що стосується трьох нижніх рамок, то в «Ціну» й в «Інтерфейс» ми вставимо поля зі списками. Для них мало лише налагодити зв'язок з комірками W12 й V12, потрібно ще вказати, звідки взяти список виведених на екран (клацанням по чорній стрілці) варіантів, а також їхня кількість. У нашому прикладі це 7 клітинок з діапазону (W4:W10), і ще 3 – з (V4:V6). У рамку ж «Фірма» вставлений просто список, і для нього теж зазначений діапазон комірок з вихідними даними (S4:S10), а також пов'язана із цим елементом керування результуюча комірка - S12.

Нарешті внизу ми поміщаємо кнопку й відразу записуємо для неї макрос, що запускає механізм розширеного фільтра з вихідним діапазоном (АЗ:Н53), діапазоном умов (R17-.W18) і копіюванням результату в інше місце (I20:Р47), спеціально для цього відведене. З метою наочності ми обмежимося всього лише півсотнею найменувань.

Очбчислювальні критерії. Тепер настав час заповнити нижній ряд цього самого діапазону критеріїв (R17:W18) шістьма логічними виразами, у яких будуть фігурувати, прямо або побічно, пов'язані з керуючими елементами комірки. Розглянемо, наприклад, швидкість обертання. Залежно від того, у якому положенні знаходиться даний перемикач, за адресою Т12 виводиться номер 1, 2 або 3. А кліткою нижче ми будемо мати, відповідно до формули [Т13]=ВЫБОР(Т12;Т4;Т5;Т6), або одне із чисел 5400 або 7200, або текст «будь-яка».

Так само по відношенню до «Фірми», «Буфера» й «Інтерфейсу», тобто кліток S13, U13 й V13 - розходження торкнуться лише кількості аргументів у функції ВИБІР. Трохи складніше нам буде з «Ціною», де вибирати прийдеться не прямо з області (W4:W10), оскільки вона, будучи джерелом написів для поля зі списком, містить текстові значення, а з допоміжної (W22:W28), з відповідними числами.

Ще більше турбот з «Обсягом», тому що його прапорцям, на відміну від перемикачів, дозволено займати кілька позицій одночасно. Установка або скидання галочок при цьому супроводжується висновком у пов'язані з ними клітки (R24:R28) логічних значень «ІСТИНА» або «НЕПРАВДА». Вони, у свою чергу, виставлені як умови для функцій, розміщених по сусідству праворуч: [S24]=ECJIИ(R24;R4;" ") і [T24]=ECЛИ(R24;R5;" ") і т.д. За допомогою ж функцій [S23]=MИH(S24:S28) і [Т23]=МАКС(Т24:Т28) виявляються обидві границі. Таким чином, у нашому розпорядженні виявляються всі необхідні для подальшого аналізу величини.

Структура ж самих логічних виразів проста: тут вміст клітинок, що цікавлять нас, піддається чисто механічному порівнянню, а результати цих порівнянь комбінуються один з одним функціями «И», «АБО» і т.п. Так формула в Т18 означає, що ми хочемо відслідковувати настання, принаймні, одного із двох подій. Перше - це коли в клітці Е4, що дає нам відомості про швидкості, стоїть те ж саме число, на яке вказує перемикач, і виходить, дана модель нас влаштовує. Друге - коли перемикач перебуває в положенні «будь-яка», і тому взагалі не має значення, яка швидкість наведена в Е4.

Аналогічно заповнюються й інші критерії, за винятком обсягу, там формула небагато відрізняється: [R18]=И(C4>=$S$23;C4<=$T$23), раз мова йде про інтервал. Разом виходить набір із шести виразів і тим найперший запис бази даних, тобто (А4:Н4) пройде цей фільтр, тільки якщо кожне дасть «ІСТИНУ». А оскільки адресні посилання на четвертий рядок відносні, на противагу іншій, абсолютним, то потім обробці піддадуться й всі інші записи, одна за однією, - такий тут механізм роботи розширеного фільтра.

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

На весь екран. Отже, нами створений інструмент для аналізу наявної бази даних. Він дозволяє виконати ряд вибірок із цього масиву деяких потрібних нам записів. Дії по кожній окремій вибірці зводяться до того, що ми вказуємо ті або інші технічні характеристики жорстких дисків і робимо пошук відповідних моделей.

Звичайно, саме по собі наявність інструмента поки що нічого не означає, - треба ще продумати порядок його застосування. Природно, що в розмові про вінчестери першою справою цікавляться його обсягом і сьогодні початковим рівнем тут уважається значення в 3,2 Гб. Однак навряд чи, мабуть, найдеться такий покупець, що встоїть перед спокусою заплатити мало-мало дорожче (у середньому 99 доларів проти 91), але зате взяти помітно більший диск на 4,3 Гб. Виграш по обсязі виходить 35%, у той час як програш у вартості - усього лише 9% - є різниця! Причому мало того, що обсяг зростає, загалом кажучи, його збільшення адже досягнуте ще й удосконаленням технології виготовлення в цілому. Тому-то всі й повторюють про співвідношення «ціна/продуктивність».

Але, якщо бути послідовним, то чому треба зупинятися на величині 4,3 і не зробити ще один або навіть два кроки на цьому шляху? Логічно було б простежити подальшу залежність ціни від обсягів і тоді приймати остаточне рішення.

Проведене дослідження цієї залежності наочно показує, що оптимальною покупкою варто визнати жорсткий диск десь в 9-13 Гб, тобто на тій ділянці узагальненій кривій, де вона губить лінійний характер і починає круто йти нагору. Між іншим, відомості про кількість пропозицій продажу на ринку, що не ввійшли, на жаль, у вихідну базу даних через економію екранного місця, підтверджують цей висновок, оскільки явними лідерами тут є моделі №22 й №35.

Можна також зробити й інші цікаві спостереження, наприклад, щодо фірми, звертаючи увагу, скажемо, на блакитні стовпчики гістограми. Чим викликана порівняльна дешевизна моделей виробництва цієї фірми? Якщо недостатньо широкою рекламою, тоді потрібно придбати саме їх. Або ж дійсно поганою репутацією в споживачів, - тоді треба триматися від цих виробів подалі.

Література

1. Excel 97. Основні поняття та прийоми роботи. Учбовий посібник,

Т.І. Червякова, В.І.Кривенко, Київ 2000.

2. Практикум з інформатики: Навчальний посібник. Я.М.Глинський. Львів, 1999.

3. Інформатика. Базовый курс: учебник для студентов вузов под редакцией С.В. Симоновича, Издательский дом „Питер", 2000

4. Microsoft Excel. Електронные таблицы и базы данных в задачах. В.М.Нечаев. Москва - 2000.

5.