Загальний підхід до проектування електронних таблиць

Які бувають таблиці

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

· моделі фінансового аналізу або аналізу даних;

· звіти та презентації;

· керування списками;

· доступ до баз даних.

Електронні таблиці першої категорії охоплюють широке поле застосування — аналізи бюджету та інвестицій, моделювання та статистичний аналіз даних. В залежності від призначення електронні таблиці можуть мати різні рівні складності — від простих таблиць з числами до складних математичних моделей, розроблених для виконання аналізу типу “а що, як…”.

Електронні таблиці другої категорії спроектовані, перш за все, заради їх кінцевого результату — виводу на друк. В цих електронних таблицях використовуються можливості Excel по форматуванню і побудові діаграм, щоб в друкованому вигляді вони мали привабливий вигляд і могли бути використані у звітах та презентаціях.

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

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

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

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

З точки зору розробника, таблиці можна поділити на кілька категорій:

* таблиці для короткострокового власного споживання;

* таблиці для довгострокового власного споживання;

* таблиці для широкого споживання та таблиці на замовлення.

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

До другої категорії таблиць відносяться таблиці, які ніхто, крім власника (розробника) не повинен бачити і використовувати. Вони відрізняються від таблиць “на швидкоруч” тільки тим, що повинні зберігатись у файлах. З часом можливе удосконалення таких таблиць.

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

 

Етапи розробки таблиці

 

Процедуру створення (як для себе, так і для інших користувачів) відносно простих електронних таблиць — без макросів, панелей інструментів користувача та інших розширених можливостей, можна розділити на кілька етапів:

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

® Для задоволення якої нагальної потреби необхідна сáме електронна таблиця? Чому непринадне існуюче розв’язання проблеми?

® Чи дійсно електронна таблиця є найкращим розв’язанням проблеми?

® Як довго передбачається користуватись електронною таблицею?

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

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

® Чи є готові дані, які можуть бути імпортовані?

® Чи будуть мінятись з часом вимоги до проекту?

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

· Визначення кола користувачів. Доцільно визначитись в таких питаннях:

® Чи досвідчені користувачі? Наприклад, чи зможуть вони виконати копіювання, вставку рядка і т. інш.?

® Яке програмне забезпечення буде використовуватись? Наприклад, якщо таблиця розроблена для Excel 97, то треба бути впевненим, що її не завантажать в Excel більш ранніх версій.

® Яке апаратне забезпечення буде використовуватись? Наприклад, якщо на Pentium таблиця виконує розрахунки за лічені хвилини, то на 486-му процесорі — хвилин за 20. Якщо таблиця використовує відеорежим 1024´768, а користувач має екран 800´600 або 640´480, то він бачитиме значно меншу область таблиці.

® Чи можна робити зміни? Часто необхідно бути впевненим в тому, що ніхто не буде вносити зміни у формули. Для цього треба застосувати деякі певні методи захисту.

· Розробка компоновки робочої книги — це одне з головних питань. Бажано розташувати інформацію так, щоб можливі зміни не торкались будь-яких даних, що не мають відношення до змін. Електронні таблиці, як правило, містять окремі блоки інформації. В перших версіях Excel, коли у розпорядженні користувача був один робочий листок, блоки інформації (наприклад, область введення вихідних даних, область обчислень, область звіту) розташовувались уступом. Така блочна компоновка із зміщенням зводить до мінімуму можливість пошкодження, наприклад, при вилученні стовпчика — ця зміна торкнеться тільки однієї області. Якщо області розташовувались би одна під одною, то такі зміни були б неможливі. Оскільки в Excel використовуються багатолистні робочі книги, такий спосіб компоновки використовується рідко. Простіше і ефективніше використовувати окремі листки для кожного блоку інформації.

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

® консолідація робочих книг;

® керування списками;

® зовнішні бази даних;

® структурування таблиць;

® перевірка даних;

® умовне форматування;

® гіперзв’язки;

® статистичний аналіз;

® зведені таблиці;

® керування сценаріями;

® пошук розв’язку;

® створення географічних карт;

® взаємодія з іншими додатками;

® спеціальні меню або панелі інструментів (які потребують створення макросів).

Про деякі з цих засобів мова йтиме далі.

· Доцільне форматування. Необхідно скорегувати формат числових даних так, щоб їх було легко читати. Рекомендації до форматування:

® Попередньо відформатувати всі комірки, що містять числа. Використовувати для чисел формат, який відповідав би змістовному навантаженню комірки. Перевірити, щоб ширина стовпчика була достатньою для відображення найбільших значень.

® Застосовувати тільки основні шрифти. В комп’ютері користувача деякі особливі шрифти можуть бути не розпізнані. Використовувати тільки стандартні шрифти True Type, що входять в поставку Windows.

® Не перевантажувати таблицю великою кількістю шрифтів. Існує “золоте правило” — не використовувати більше двох (а краще однієї) гарнітур в одній робочій книзі і стримуватись в використанні різних розмірів шрифту.

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

® Звернути увагу на активну область. Недосвідчений користувач може прокрутити активну область, а потім її не знайти. Щоб уникнути цього, доцільно сховати всі рядки та стовпчики, які не використовуються, або додати кольоровий фон (наприклад, світло-сірий) до невикористовуваних комірок.

® Вилучити сторонні (невикористовувані) елементи. Можна значно спростити таблицю для користувача, вилучивши, наприклад, автоматичні границі сторінок, лінії сітки, заголовки рядків, стовпчиків, ярлички листків та інш.

· Тестування електронної таблиці. Тестування — це процес перевірки правильності результатів розрахунків за формулами при різних умовах.Рекомендації до тестування:

® Перевірити роботоздатність при введенні екстремальних значень.

® Перевірити роботоздатність при введені фіктивних даних.

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

® Перевірити достовірність даних.

· Застосування захисту. Електронна таблиця може бути дуже тендітною. Часто вилучення однієї формули може викликати ланцюгову реакцію, в результаті чого інші формули можуть видавати хибні значення. Цим проблемам можна запобігти, якщо використовувати вбудовані засоби захисту Excel:

® Захист листка. При застосуванні команди “Сервіс Þ Захист Þ Захистити листок” (Tools Þ Protection Þ Protect Sheet) з’являється діалогове вікно з трьома опціями, які дозволяють встановити спосіб або вид захисту елементів листка:

* вмісту (contents) — при установці цієї опції вміст комірок, яким привласнюється властивість “захищені“, не може бути змінений;

* об’єкти (locked) — при установці цієї опції графічні об’єкти, яким привласнюється властивість “захищені“, не можуть бути змінені;

* сценарії (scenarios) — при установці цієї опції встановлені сценарії, яким привласнюється властивість “захищені“, не можуть бути змінені.

В цьому ж діалоговому вікні можна ввести пароль для дозволу зняття захисту. По умовчанню всім коміркам привласнюється властивість “захищена”. Тому, перед тим як захистити робочий листок, треба зняти захист з комірок, в які користувач буде вводити вихідні дані.

® Захист робочих книг. Це другий тип захисту. При виборі команди “Сервіс Þ Захист Þ Захистити книгу” (Tools Þ Protection Þ Protect Workbook) з’являється діалогове вікно з двома опціями, які дозволяють встановити захист деяких елементів книги:

* структуру (structure) — установка опції захищає вікно робочої книги від переміщення або зміни розмірів;

* вікна (windows) — установка опції запобігає таким змінам в робочій книзі, як додавання, вилучення, переміщення, перейменування, сховування або відкриття листка.

· Оформлення документації на роботу. Це фінальний етап створення електронної таблиці. Рекомендується заповнити поля в діалоговому вікні “Властивості“ (Properties) на вкладці “Документ” (Summary), яке з’являється за командою “Файл Þ Властивості” (Fill Þ Properties). Можна також документувати окремі комірки за допомогою команди “Вставка Þ Примітка” (Insert Þ Note). Примітка з’являється, якщо затримати на кілька секунд вказівник мишки на комірці. Можна також до робочої книги додати новий листок і зберегти в ньому необхідні коментарі. Деякі користувачі ведуть на такому листку облік всіх змін.

 

Структурування робочих листків

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

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

Ці ж дані можуть бути впорядковані і в горизонтальному напрямку по місяцях, по кварталах і містити загальний підсумок за рік.

Excel здатна створювати структуру в обох напрямках.

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

Працюючи із структурами, треба мати на увазі наступне:

* Один робочий листок може мати тільки одну структуру (вертикальну, горизонтальну або обидві). Якщо потрібно мати кілька структур, то треба скопіювати дані на новий робочий листок і на ньому відтворити іншу структуру.

* Можна створити структуру вручну або дозволити Excel зробити це автоматично.

* Можна створити структуру для всіх даних робочого листка або тільки для вибраного діапазону даних.

· Вилучити структуру можна за допомогою лише однієї команди.

· Можна сховати символи структури (щоб вивільнити простір екрана), зберігаючи при цьому саму структуру. Символи структури — це горизонтальні і вертикальні квадратні дужки, що при створені структури з’являються на лівому і верхньому полях таблиці та схематично зображують структуру, і кнопки керування відображенням певного рівня структури.

Структура може мати до восьми вкладених рівнів.

Створення структури

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

Автоматичне створення структури. В більшості випадків доцільно створювати структури в автоматичному режимі. Щоб автоматично створити структуру для деякого діапазону даних, треба помістити табличний курсор в будь-яку комірку цього діапазону і залучити команду “Дані ð Група і структура ð Створення структури” (Data ð Group and Outline ð Auto Outline). Програма Excel проаналізує формули і з виділеного діапазону створить структуру. В залежності від формул буде створена або горизонтальна, або вертикальна, або обидві ці структури. Якщо у робочого листка вже є структура, то буде задане питання, чи є потреба модифікувати існуючу структуру. При стверджувальній відповіді стара структура буде вилучена і створена нова.

Створення структури вручну. При створенні структури вручну мається на увазі створення груп рядків (для вертикальної структури) або груп стовпчиків (для горизонтальної структури). Щоб створити групу рядків, треба виділити повністю всі рядки, які треба включити в цю групу, крім рядка, що містить формули для підрахунку підсумків. Потім залучити команду “Дані ð Група і структура ð Згрупувати” (Data ð Group and Outline ð Group). При створенні структури цієї групи Excel буде відображувати символи структури. Ці дії потрібно повторити для кожної створюваної групи. При стуленні структури рядки, що складають групу, стають схованими. Однак підсумковий рядок, не включений в групу, схованим не буде і залишиться видимим.

Можна обирати також групи груп. Це призведе до створення багаторівневих структур. Створюючи таку структуру, треба починати завжди з самої внутрішньої групи, а потім просуватись зсередини назовні. У випадку помилки при групуванні рядків (стовпчиків) їх можна розгрупувати командою “Дані ð Група і структура ð Розгрупувати” (Data ð Group and Outline ð Ungroup).

В Excel нема панелі інструментів, призначеної виключно для структурування, але на панелі інструментів “Зведені таблиці“ (Pivot Table) є чотири кнопки для роботи з структурами:

· “Розгрупувати” (Ungroup) — виконує розгруповування вибраних рядків та стовпчиків;

· “Групувати” (Group) — виконує групування вибраних рядків та стовпчиків;

· “Відобразити деталі“ (Show Detail) — забезпечує показ деталей (тобто відповідних комірок з даними) для обраної комірки з підсумком;

· “Сховати деталі“ (Hide Detail) — забезпечує приховування деталей (тобто відповідних комірок з даними) для обраної комірки з підсумком.

Крім того, можна користуватись такими клавіатурними еквівалентами:

· < Alt + Shift + ®> — групування вибраних рядків та стовпчиків;

· < Alt + Shift + > — розгруповування вибраних рядків та стовпчиків.