ІНТЕРФЕЙС ТАБЛИЧНОГО ПРОЦЕСОРА 3 страница

При роботі з електронними таблицями часто виникає необхідність їхнього об'єднання. Серед інструментів об'єднання електронних таблиць відзначимо:

організацію межтабличных зв'язків;

консолідацію електронних чи таблиць їхніх частин; об'єднання файлів.

Організація межтабличных зв'язків

Зв'язку між таблицями здійснюються шляхом використання зовнішніх посилань (адрес осередків), що містять крім імені стовпця і номера рядка ім'я файлу, дані з який використовуються. Так, наприклад, якщо ми хочемо використовувати дані з осередку З2 таблиці, що міститься у файлі Exam. wq1. у потрібну нам осередок поточної таблиці ми можемо записати зовнішнє посилання в такий спосіб: [Exam.wql] C2.

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

Між окремими таблицями можливі двосторонні зв'язки (таблиця А посилається на таблицю В, а В, у свою чергу, чи прямо опосредованно, наприклад через таблицю З, посилається на А).

Консолідація електронних чи таблиць їхніх частин

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

Команда об'єднання файлів

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

Приклад 14.14. Допустимо, що цікавляча нас компанія має три магазини, від яких вона одержує регулярні звіти у формі електронних таблиць. Однак, якщо в цілому справи йдуть нормально, керівництво компанії мало цікавить фінансова діяльність кожного з магазинів. Йому хотілося б побачити результати діяльності всієї компанії й усвідомити, який з магазинів приносить прибуток, а який - збитки. Тут виникає задача об'єднання даних із трьох звітів (електронних таблиць) в один. Ця задача може бути вирішена встановленням межтабличных чи зв'язків об'єднанням файлів електронних таблиць.

Допустимо, що вихідні звіти, що надходять від магазинів компанії, мають вид, зазначений у табл. 14.4.

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

Таблиця 14.4. Звіт, що надходить від одного з магазинів

Таблица 14.5. Объединенный отчет, полученный организацией межтабличных связей (в режиме просмотра формул)

Таблица 14.6. Объединенный отчет, полученный при объединении (в режиме копирования)

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

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

МАКРОСИ ЯК ЗАСІБ АВТОМАТИЗАЦІЇ РОБОТИ

Поняття макросу

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

Створення макросу багато в чому нагадує запис програми алгоритмічною мовою, Цей процес може бути представлений як послідовність наступних кроків:

визначення мети, для виконання якої ви створюєте макрос (наприклад, для об'єднання даних, що надходять з окремих магазинів, у загальну електронну таблицю);

визначення послідовності команд (натискань клавіш), що забезпечують досягнення поставленої вами мети;

набір із клавіатури послідовності команд, причому розташування створюваного макросу не повинне заважати введенню в таблицю нових рядків чи стовпців;

присвоєння імені макросу, створеному в процесі виконання кроку 3.

Приклад 14.15. Приведемо приклад створення макросу в електронній таблиці Lotus 1-2-3. Ім'я макросу містить у собі лівий слэш і букву (наприклад, \З чи \У). Уміст макросу починається в сусідньому осередку праворуч від імені і далі продовжується в даному стовпці. Для виконання макросу варто натиснути клавішу <Alt> і відповідну букву, що міститься в його імені. Так, для виконання макросу, що ви бачите нижче, варто набрати на клавіатурі <Alt> <C>.

Приклад макросу

\З (goto}upprleft~

/fcanquarters~store1.wkl~

<down>

/fcanquarters~store2. wkl~

<down>

/fcanquarters~store3 .wkl~

/reallstore

Макрос з ім'ям \Із призначений для об'єднання даних, що надходять від магазинів, у загальну електронну таблицю (послідовність запрограмованих тут дій цілком совладает з діями, що починаються при одержанні табл. 14.6). Жирним шрифтом позначені імена діапазонів переміщуваних осередків. При створенні макросів використовуються спеціальні позначення. В електронній таблиці Lotus 1-2-3, наприклад, використовуються наступні позначення. У прикладі макросу використовується команда об'єднання файлів (Fіle Combіne Add command) для діапазону осередків QUARTERS, узятих з електронної таблиці store1.wkl.

Рис.14.10. Позначення, використовувані при написанні макросів

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

Створення користувальницьких меню

Ідея об'єднання двох і більш макросів привела до створення користувальницьких меню. На мал. 14.11 ми бачимо користувальницьке меню, побудоване для об'єднання електронних таблиць. Меню має чотири пункти (Об'єднати, Видалити, Роздрукувати і Вийти), кожний з який реалізується виконанням спеціально розробленого макросу. Вибір і запуск виконання того чи іншого макросу в залежності від особливостей конкретного табличного процесора можуть здійснюватися в такий спосіб:

спеціальною клавішною комбінацією з наступним введенням імені макросу;

указівкою зухвалої клавіші окремо для кожного макросу;

включенням виклику макросу як нового пункту в системне меню;

додаванням кнопки виклику макросу в панель інструментів;

контекстним викликом менеджера макросів мишею.

Рис. 14.11. Приклад користувальницького меню

ЕЛЕКТРОННА ТАБЛИЦЯ ДЛЯ ПІДТРИМКИ ПРИЙНЯТТЯ РІШЕНЬ

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

Спробуємо використовувати можливості електронної таблиці для рішення питання про можливість покупки в кредит автомобіля. Допустимо, ви хочете знати, "подужаєте" чи ви щомісячний платіж за машину, що купується, величина якого залежить від її ціни, первісного платежу й умов надання кредиту (позички). Іншими словами, вас цікавить:

чи можете ви дозволити собі визначений місячний платіж за машину ?

що буде, якщо ви погодитеся на менший автомобіль і одержите знижку від його виробника ?

що буде, якщо ви в наступне літо заробите деяку додаткову суму для первісного платежу ?

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

Ваше рішення про вибір і покупку автомобіля залежить від відповідей на ці й інші питання. На мал. 14.13 аналіз даної ситуації проведений за допомогою електронної таблиці.

На мал.14.12 ми бачимо шаблон (порожню таблицю), що має відповідні назви рядків і стовпців, а також формули без числових даних. У шаблон додатково вводяться наступні числові дані: ціна автомобіля, знижка виробника, первісний внесок, річна процентна ставка і час повернення позички. Після введення зазначених даних електронна таблиця автоматично обчислює значення щомісячного платежу, використовуючи спеціальну функцію @PMT.

Рис. 14.12. Шаблон таблицы

Рис. 14.13. Аналіз ситуації за допомогою електронної таблиці

Функція @PMT(Pv, Rate, Nper) обчислює суму періодичного платежу, необхідну для погашення позички Pv із процентною ставкою Rate за число платіжних періодів Nper. При цьому значення, що визначаються для Rate, повинні корелюватися з одиницями, використовуваними для Nper. Якщо платежі робляться щорічно, Nper виміряється в літах. Якщо платежі виробляються щомісяця, Nper являє собою число платіжних місяців. Для розрахунку щомісячних платежів при використанні річної процентної ставки її варто розділити на 12. Так, наприклад, у рамках табл. на мал. 14.13 функція @РМТ використовується в наступному виді:

@РМТ(14999,.13/12,36).

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

@РMT(У5,У6/12,У7*12).

Зазначений шаблон дозволяє розглянути кілька альтернатив і одержати представлення про корисність електронних таблиць для прийняття рішень.

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

Погоджуючись на менший автомобіль і одержуючи при цьому знижку, ми трохи зменшуємо розмір щомісячного платежу, доводячи його до 437 діл. (альтернатива 2).

Далі ми бачимо альтернативу 3 - необхідність одержання додаткового доходу в 3000 дол. для внесення первісного платежу.

Остання альтернатива 4 покупки - збільшення терміну повернення позички до 4 років з більш низькою процентною ставкою, можливо, улаштує нас.

Таблиця в такий спосіб ілюструє, як використовувати електронну таблицю для підтримки прийняття рішень. Користувач визначає проблему, уводить необхідна кількість перемінних і потім будує електронну таблицю в декількох версіях, у кожній з який варіюється одна чи трохи перемінних.

15.1. ÎÑÍÎÂͲ ÏÎÍßÒÒß

Çàãàëüí³ ïîëîæåííÿ

Ö³ëü áóäü-ÿêî¿ ³íôîðìàö³éíî¿ ñèñòåìè - îáðîáêà äàíèõ ïðî îá'ºêòè ðåàëüíîãî ñâ³òó. Ó øèðîêîìó çì³ñò³ ñëîâà áàçà äàíèõ - öå ñóêóïí³ñòü çâåäåíü ïðî êîíêðåòí³ îá'ºêòè ðåàëüíîãî ñâ³òó â ÿê³é-íåáóäü ïðåäìåòí³é îáëàñò³. ϳä ïðåäìåòíîþ îáëàñòþ ïðèéíÿòî ðîçóì³òè ÷àñòèíà ðåàëüíîãî ñâ³òó, ùî ï³äëÿãຠâèâ÷åííþ äëÿ îðãàí³çàö³¿ êåðóâàííÿ é ó ê³íöåâîìó ðàõóíêó àâòîìàòèçàö³¿, íàïðèêëàä, ï³äïðèºìñòâî, âóç ³ ò.ä.

Ñòâîðþþ÷è áàçó äàíèõ, êîðèñòóâà÷ ïðàãíå óïîðÿäêóâàòè ³íôîðìàö³þ ç ð³çíèõ îçíàê ³ øâèäêî âèòÿãàòè âèá³ðêó ç äîâ³ëüíèì ñïîëó÷åííÿì îçíàê. Çðîáèòè öå ìîæëèâî, ò³ëüêè ÿêùî äàí³ ñòðóêòóðîâàí³.

Ñòðóêòóðóâàííÿ -ýòîââåäåíèå óãîä ïðî ñïîñîáè ïðåäñòàâëåííÿ äàíèõ.

Íåñòðóêòóðîâàíèìè íàçèâàþòü äàí³, çàïèñàí³, íàïðèêëàä, ó òåêñòîâîìó ôàéë³.

Ïðèêëàä 15.1. Ïðèêëàä íåñòðóêòóðîâàíèõ äàíèõ, ùî ì³ñòÿòü çâåäåííÿ ïðî ñòóäåíò³â (Íîìåð îñîáèñòî¿ ñïðàâè, ïð³çâèùå, ³ì'ÿ, ïî áàòüêîâ³ ³ ðîö³ íàðîäæåííÿ). Ëåãêî ïåðåêîíàòèñÿ, ùî ñêëàäíî îðãàí³çóâàòè ïîøóê íåîáõ³äíèõ äàíèõ, ùî çáåð³ãàþòüñÿ â íåñòðóêòóðîâàíîìó âèä³, à óïîðÿäêóâàòè ïîä³áíó ³íôîðìàö³þ ïðàêòè÷íî íå ïðåäñòàâëÿºòüñÿ ðåàëüíèì.

Îñîáèñòå äàëî N 16493, Ñåð㺺â Ïåòðî Ìèõàéëîâè÷, äàòà íàðîäæåííÿ 1 ñ³÷íÿ 1876 ì; Ë/ä. N 16593. Ïåòðîâà Ãàííà Âîëîäèìèð³âíà, äàòà ðîæä. 15 áåðåçíÿ 1975 ì; N ëè÷í. ñïðàâè 16693, ä.ð. 14.04,78, Àíîõ³í Àíäð³é Áîðèñîâè÷.

Ùîá àâòîìàòèçóâàòè ïîøóê ³ ñèñòåìàòèçóâàòè ö³ äàí³, íåîáõ³äíî âèðîáèòè âèçíà÷åí³ óãîäè ïðî ñïîñîáè ïðåäñòàâëåííÿ äàíèõ, òîáòî äàòó íàðîäæåííÿ ïîòð³áíî çàïèñóâàòè îäíàêîâî äëÿ êîæíîãî ñòóäåíòà, âîíà ïîâèííà ìàòè îäíàêîâó äîâæèíó ³ âèçíà÷åíå ì³ñöå ñåðåä ³íøî¿ ³íôîðìàö³¿. Ö³ æ çàóâàæåííÿ ñïðàâåäëèâ³ ³ äëÿ ³íøèõ äàíèõ (íîìåð îñîáèñòî¿ ñïðàâè, ïð³çâèùå, ³ì'ÿ. ïî áàòüêîâ³).

Ïðèêëàä 15.2. ϳñëÿ ïðîâåäåííÿ íåñêëàäíî¿ ñòðóêòóðèçàö³¿ ç ³íôîðìàö³ºþ, çàçíà÷åíî¿ â ïðèêëàä³ (ìàë. 15.1), âîíà áóäå âèãëÿäàòè òàê, ÿê öå ïîêàçàíî íà ìàë. 15.2.

N îñîáèñòî¿ ñïðàâè Ïð³çâèùå ²ì'ÿ Ïî áàòüêîâ³ Äàòà íàðîäæåííÿ

16493 Ñåð㺺â Ïåòðî Ìèõàéëîâè÷ 01.01.76

16393 Ïåòðîâà Ãàííà Âîëîäèìèð³âíà 15.03.75

16693 Àíîõ³í Àíäð³é Áîðèñîâè÷ 14.04.76

Ðèñ. 15.2. Ïðèêëàä ñòðóêòóðîâàíèõ äàíèõ

Êîðèñòóâà÷àìè áàçè äàíèõ ìîæóòü áóòè ð³çí³ ïðèêëàäí³ ïðîãðàìè, ïðîãðàìí³ êîìïëåêñè, à òàêîæ ôàõ³âö³ ïðåäìåòíî¿ îáëàñò³, ùî âèñòóïàþòü ó ðîë³ ÷è ñïîæèâà÷³â äæåðåë äàíèõ, íàçèâàí³ ê³íöåâèìè êîðèñòóâà÷àìè.