Міністерство аграрної політики україни

Міністерство аграрної політики україни

 

Департамент аграрної освіти та науки

 

Харківський національний технічний університет сільського господарства імені Петра Василенка

 

ЛАБОРАТОРНИЙ ПРАКТИКУМ ДЛЯ ВИВЧЕННЯ ТЕКСТОВОГО ПРОЦЕСОРА Microsoft Excel

(Частина 1)

 

Методичні рекомендації для самостійного навчання

для студентів 1, 2, 5 курсів денної та заочної форми навчання

 

Харків 2005

Автори - укладачі:

Путятін В.П., професор

Чалий І.В., к.т. н., доцент;

Левкін А.В., ст. викладач;

Цуканов В.Ю., к.т. н., доцент;

Коваленко С.Н., асистент;

(Харківський національний технічний університет сільського господарства імені Петра Василенка)

Коваленко С.В., асистент

(Національний технічний університет «ХПІ»)

 

Рецензенти:

Степанов В. П., кандидат технічних наук, професор кафедри «Інформатика» (Харківський Національний технічний університет радіоелектроніки);

Фурман І. А. завідувач кафедри «Автоматизації та комп’ютерних технологій», доктор технічних наук, професор (Харківський національний технічний університет сільського господарства).

 

Схвалено та рекомендовано до видання Радою Навчально-методичного центру по заочній формі навчання у закладах освіти 3-4 рівнів акредитації аграрного профілю (план видання учбово-методичної літератури на 2004-2005 н.р., рег. № 097/2002 від 19.05.2004 р.).

Лабораторна робота № 1

 

Ознайомлення з сучасними табличними процесорами на прикладі програмного середовища Microsoft Excel 97/2000 з інтегрованого пакета Microsoft Office 97/2000 та його використання для вирішення офісних задач.

Ціль роботи: ознайомитися з історією розвитку та основними можливостями сучасних табличних процесорів на прикладі програмного середовища Microsoft Excel 97/2000 з інтегрованого пакету Microsoft Office 97/2000.

 

Вказівки до виконання роботи

Історія появи та розвитку електронних таблиць.

 

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

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

У 1961 р. Ричард Маттессич з Каліфорнійського університету в Берклі випустив статтю, що була присвячена моделюванню бюджетів на комп'ютері. Вона стала основою для написання двох книг та розробці програми на алгоритмічній мові фортран. Отже ідея використовувати комп'ютери для спрощення роботи з відомостями зародилася задовго до появи персональних комп'ютерів.

Ідея роботи з числами вважалася тоді вже очевидною. Тому революційна ідея Маттессича не відразу знайшла розвиток. Тільки в 1978 році Ден Бріклін та Боб Френкстон з Гарвардської школи бізнесу розробили прототип програми для автоматизації табличних обчислень. Вони організували компанію Software Arts Corporation і навесні 1979 року була випущена робоча версія, реклама якої вийшла в травневому випуску журналу Byte. Тоді ж програма була вперше представлена публіці на національній конференції по обчислювальній техніці в Нью-Йорку. По великому рахунку ніхто належної уваги на програму не звернув. Але в ході виставки у авторів відбулося корисне знайомство з молодим Білом Гейтсом та Беном Розеном (згодом став одним із засновників компаній Lotus і Compaq).Останній дав високу оцінку програмі в спеціальному інформаційному листку і восени 1979 року почалися перші продажі.

Справжній успіх до їхній програми, що отримала назву VisiCalc прийшов лише після 1981року коли програма з’явилася на перших IBM-сумісних персональних комп'ютерах. Багато в чому комп'ютери IBM PCстали основною комп'ютерною платформою у світі за допомогою VisiCalc, та трохи пізніше Multiplan і SuperCalc. Керівники підприємств, банків і інших установ, що мали право приймати рішення про придбання великих партій комп'ютерів для оснащення своїх підприємств і навіть цілих галузей, на прикладі цих програм зрозуміли, наскільки їм потрібний і корисний комп'ютер. Вони побачили, як можна ефектно оперувати з цифрами і одержувати не тільки аналізи, але і прогнози за допомогою електронних таблиць. Тільки за період з 1981 по 1983 роки було продано біля мільйона екземплярів програми VisiCalc.

Ці електронні таблиці випускалися до кінця 1983 року і були додатком для MS-DOS. Потім права на програму VisiCalc відійшли до компанії Lotus, що замінила її своїм набагато більш досконалим продуктом Lotus 1-2-3. Можливо було б багато чого цікавого розглянути з історії інших електронних таблиць. І сьогодні широко використовуються Quattro Pro, Lotus 1-2-3, ВАРІТАБ(русифікована версія SuperCalc) та інші. Але зараз пальма першості належить не їм, а різним версіям програмного середовища Excel з інтегрованого пакету Microsoft Office.

 

Ознайомлення з програмним середовищем Microsoft Excel.

Створення і збереження робочих аркушів

 

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

 

Вказівки до виконання роботи

Збереження файлів робочих книг

 

Для збереження змін у файлі, що редагується, використовується кнопка “Сохранить” з меню “Стандартна” чи можна використовувати меню “Файл” – “Сохранить”. При цьому з'явиться діалогове вікно “Сохранение документа”. У нижній частині вікна “Имя файла” відображається ім'я файлу, що табличний процесор дає за замовчуванням новому документу (при цьому назва показана на синім тлі), чи стара назва файлу. Необхідно ввести ім'я файлу, наприклад TabRed (обмежень на ім'я файлу практично немає) без розширення і натиснути Enter чи використовувати кнопку “Сохранить” в цьому вікні. У результаті цієї операції робоча книга буде збережена з назвою TabRed.xls, надалі цей файл може бути використаний для модифікацій.

Збереження робочої книги під іншим ім'ям може бути проведене за допомогою меню “Файл” – “Сохранить как”.

 

Переміщення по робочому листі, усередині робочої книги

 

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

Для переміщення по книзі існує також кнопки прокручування ярличків аркушів, що розташовуються в нижній частині вікна.

 

Форматування робочого листа Microsoft Excel.

 

Ціль: ознайомитися з основними можливостями форматування робочих книг у Microsoft Excel.

 

Додавання рядка, стовпця

 

Якщо необхідно додати рядок чи стовпець у робочий лист, необхідно використовувати меню «Вставка» – «Строки» чи «Столбца». У результаті використання цього меню рядок вставляється в позицію над поточною (зверху) активною коміркою, а стовпець – перед (ліворуч) поточною коміркою. Як окремий випадок вставки може використовуватися «Вставка» - «Ячейки», при цьому відбувається зрушення поточної комірки, у якій знаходився курсор, вправо. Наступний рядок буде відображено без змін.

 

Попередній перегляд

 

У панелі інструментів «Стандартная» клацніть на кнопці «Предыдущий просмотр». З'явиться вікно «Предыдущий просмотр», у якому ви зможете оцінити вид, що буде мати ваш робочий лист при друкуванні на принтері. Це дуже корисна функція, тому що в деяких випадках можливе перекручування таблиць при друкуванні і краще зробити необхідні зміни відразу, до друку.

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

 

Зауваження.

Не можна вставляти пробіли між назвою функції і дужками, у яких записані аргументи. У противному випадку Excel видає наступне повідомлення про помилку: «#ИМЯ?».

 

Excel містить більш 400 убудованих функцій, тому вводити у формулу назви функцій і значення вхідних параметрів безпосередньо з клавіатури не завжди зручно. У Excel є спеціальний засіб для роботи з функціями – Майстер функцій. Для того, щоб його викликати, необхідно клацнути за знаком рівності в рядку формул чи набрати в коміркі знак рівності «=», а потім клацнути по трикутнику, що знаходиться в поле імені осередку. У результаті з'явиться таке вікно.

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

Майстер функцій може викликатися також командою «Вставка» – «Функция» або натисканням на кнопку «Вставка функции» на панелі «Стандартная».

Після вибору необхідної функції варто натиснути кнопку ОК, що приведе до появи діалогового вікна «Майстер функцій - крок 2 з 2».

 

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

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

 

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

3.Розгорнути вікно Майстра формул за допомогою кнопки, розташованої на тім же місці, що і кнопка згортання.

4.У разі потреби задати аналогічним образом посилання на інші комірки.

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

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

 

Використання посилань

 

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

Можна також посилатися на комірки, що знаходяться на інших аркушах робочої книги, в іншій робочій книзі чи навіть на дані іншого додатка. Посилання на комірки інших робочих книг називаються зовнішніми. Посилання на дані в інших додатках називаються вилученими. Посилання на активну комірку виводиться на екран у поле імені, розташованому ліворуч від рядка формул. Посилання на комірки використовують заголовки відповідних рядків і стовпців робочого листа. Оскільки в Excel стовпці позначені латинськими буквами (A,...,Z,AA,...AZ,...і т.д.), а рядки пронумеровані.

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

Крім відносних посилань, у Excel часто використовуються абсолютні посилання, у яких крім назви стовпця і номера рядка використовується спеціальний символ «$», що фіксує дану частину посилання і залишає її незмінної при копіюванні формули з таким посиланням в іншу комірку. Звичайно в таких комірках містяться значення констант, що використовуваються при обчисленнях. Наприклад, якщо необхідно, зафіксувати у формулі значення комірки А1, що не повинне змінюватися у випадку копіювання даної формули, те абсолютне посилання на цю комірку буде мати наступний вид: $A$1. Аналогічно, якщо необхідно зафіксувати в посиланні тільки рядок чи тільки стовпець, у якому знаходиться комірка,що використовуваються, зокрема стовпець А чи рядок 1, то посилання прийме вид $A1 чи A$1, відповідно.

Можна звертатися до комірки не по заголовку стовпця, а по його порядковому номеру. Такий тип посилань називається R1C1, тобто R – рядок, С – стовпець. Наприклад, посилання на комірку С3 поточного робочого листа можна також позначити R3C3.

У наступному прикладі буде розглянуте використання у формулі посилання на різні аркуші робочої книги. Як математичну частину завдання буде розглянута формула F1+ A2+C3+D5, причому комірки розташовані на різних аркушах, F1, A2, C3 розташовані на 1,2, 3 листі Книги1, а D5 - на листі1 Книги2. Тобто, по суті, необхідно організувати обчислення, ґрунтуючись на даних з різних книг і різних аркушів цих книг. Розглянемо докладно, як це можна зробити.

1.Для початку розташуємо вікна книг, у тім порядку, у якому з ними буде зручно працювати. У меню «Окно» виберемо підменю «Расположить».

Випаде вікно «Расположение окон». У ньому виберемо поле «Расположить окна» - «Сверху вниз». У результаті робочі книги у вікні Excel будуть розташована одна під іншою.

2.Припустимо, що результат обчислення математичної формули необхідно записати в комірку А1 Книги1, тому створення формули почнемо в цієї коміркі. Клацніть по ній, а потім за знаком «=» у рядку формул. Виділяємо комірку F1, вона буде підсвічена пунктиром, що миготить, далі ставимо знак «+», вибираємо в нижній частині вікна «Лист2», клацаємо по ньому, при цьому в рядку формул з'являється напис «Лист1!F1+». Далі вибираємо необхідну комірку у «Лист2», далі «+». Такі ж дії проробляємо й у відношенні даних на «Лист3». У результаті будемо мати:

 

3.Далі подвійним клацанням по коміркі D5 у книзі2 одержуємо остаточний вид формули:

 

 

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

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

 

Значення помилок у формулах

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

 

Коди помилок і можливі причини

 

Код помилки	Можливі причини
#ДЕЛ/0	У формулі робиться спроба поділити на нуль
#ИМЯ?	Excel не зміг розпізнати ім'я, що використане у формулі
#ПУСТО!	Було задане перетинання двох областей, що не мають загальних комірок
#Н/Д	Немає доступного значення
#ЧИСЛО!	Невірно зазначений аргумент при операціях з числами, або неможливо прорахувати результат
#ССЫЛКА!	Неправильне посилання на комірку
#ЗНАЧ!	Аргумент чи операнд мають неприпустимий тип

 

Міністерство аграрної політики україни

 

Департамент аграрної освіти та науки

 

Харківський національний технічний університет сільського господарства імені Петра Василенка