Табличний процесор Microsoft Excel

Зміст

I. Тема: Обробка даних засобами електронних таблиць. Табличний процесор Microsoft Excel

Лекція № 1

IV.1 Основні поняття електронних таблиць............................................................................ 3

IV.2 Типи даних. Введення даних. Діапазон даних................................................................ 3

IV.3Створення і використання простих формул..................................................................... 4

Лекція № 2

IV.4 Вбудовані функції. Функція ухвалення рішення (ЕСЛИ).............................................. 9

Лекція № 3

IV.5 Зберігання дат і часу доби в Microsoft Excel................................................................... 16

IV.5.1 Формат введення даних типу Дата................................................................... 16

IV.5.2 Формат відображення даних типу Дата............................................................ 17

IV.5.3 Функції обробки даних типу Дата..................................................................... 17.

IV.5.4 Арифметичних операцій над даними типу Дата............................................. 19.

Лекція № 4

IV.6 Побудова діаграм в середовищі Excel.............................................................................20

Лекція № 5

IV.7 Побудова графіка функції. Точкова діаграма................................................................. 25

IV.7.1 Побудова графіка функції, заданої в табличному виді................................... 25

IV.7.2 Побудова графіка функції заданої аналітично................................................ 27

Лекція № 6

IV.8 Створення й робота зі списком....................................................................................... 28

IV.8.1 Перевірка даних при уведенні.......................................................................... 28

IV.8.2 Використання Форми....................................................................................... 28

IV.8.3 Сортування записів списку............................................................................... 29

IV.8.4 Використання фільтра при обробці списку..................................................... 29

Лекція № 7

IV.9 Апроксимація експериментальних даних...................................................................... 31

IV.9.1 Інтерпретація експериментальних даних........................................................ 31

IV.9.2 Наближення експериментальних даних аналітичною залежністю............... 32

IV.9.3 Графічна інтерпретація апроксимації............................................................... 32

IV.9.4 Вибір найбільш відповідної лінії тренда для даних........................................ 33

IV.9.5 Апроксимація даних в середовищі Excel......................................................... 35

IV.10 Друк таблиць Excel......................................................................................................... 38

Додаток............................................................................................................................................ 40

Загальні відомості про вимоги стандартів................................................................................... 40

Лекція №1.

I. Тема: Обробка даних засобами електронних таблиць. Табличний процесор Microsoft Excel.

План лекції:

1. Застосування електронних таблиць.

2. Основні понятті електронних таблиць Excel.

3. Типи даних. Введення даних. Діапазон даних.

4. Операції з осередками.

5. Створення і використання простих формул.

6. Абсолютні і відносні адреси осередків.

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

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

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

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

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

Програма Microsoft Excel призначена для роботи з таблицями даних: числових, текстових, а також формул. При формуванні таблиці виконується введення, редагування і форматування даних. Наявність засобів автоматизації полегшує ці операції. Створена таблиця може бути виведена на друк.

I.1 Основні поняття електронних таблиць

Документ Excel називається робочою книгою. Файли Excel мають розширення.xls. Робоча книга є набором робочих листів, кожен з яких має табличну структуру і може містити одну або декілька таблиць. У вікні документа в програмі Excel відображається тільки поточний робочий лист, з яким ведеться робота (Мал. 1.).

Мал. 1.

Кожен робочий лист має назву, яка відображається на ярличку листа, що відображається в його нижній частині. За умовчанням робочі листи іменуються: Лист1, Лист2 і так далі За допомогою ярличків можна перемикатися до інших робочих листів, що входять в ту ж робочу книгу (клацанням по потрібному ярлику листа). Кількість робочих листів можна міняти: додавати або видаляти. Листи можна також перейменовувати. Ці операції виконуються через контекстне меню ярлика (Мал. 2.).

Мал. 2.

Структура робочого листа. Робочий лист складається з рядків і стовпців. Стовпці озаглавлені прописними латинськими буквами і, далі двохбуквеними комбінаціями. Всього робочий лист може містити 256 стовпців, пронумерованих від A до IV. Рядки послідовно нумеруються цифрами, від 1 до 65 536 (максимально допустимий номер рядка).

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

Мал. 3.

Осередки і їх адресація. На перетині стовпців і рядків утворюються елементи таблиці. Вони є мінімальними елементами для зберігання даних. Позначення окремого осередку поєднує в собі номери стовпця і рядка (у цьому порядку), осередки (її номер) виконує функції її адреси. Наприклад, C6. (Мал. 1.) Адреси осередків використовуються при записі формул, що визначають взаємозв'язок між значеннями, розташованими в різних осередках. Один з осередків завжди є активним і виділяється рамкою активного осередку. Ця рамка в програмі Excel грає роль курсора. Операції введення і редагування завжди проводиться в активному осередку. Перемістити рамку активного осередку можна за допомогою курсорних клавіш або покажчика миші. Адресу активного осередку можна побачити в полі імені (Мал. 1.).

I.2 Типи даних. Введення даних. Діапазон даних.

Вміст осередків. З погляду програми Excel осередок може містити три види даних.

Текстові дані є рядком тексту довільної довжини. Програма Excel відтворює їх точно в тому вигляді, в якому вони були введені. Осередок, що містить текстові дані, не може брати участь в обчисленнях.

Числові дані – це окреме число, введене в осередок. Як числа розглядаються дані, що визначають дати або грошові суми.

Формули – основний засіб, що дозволяє проводити обчислення. Вміст осередку розглядається як формула, якщо воно починається із знаку рівності (=). Значенням формули є число. Формули в осередках не відображаються. Замість формули відтворюється результат, отриманий при її обчисленні.

Вміст активного осередку відображається в рядку формул.

Введення інформації на робочий лист

- Невід'ємним елементом робочого поля таблиці є курсор. Термін “ КУРСОР” використовується в наступних випадках:

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

- текстовий курсор - миготлива (або не миготлива) риска, що відзначає положення поточного символу при редагуванні осередку, який щось містить;

- курсор миші - покажчик миші, який може приймати вигляд:

Основними об'єктами, над якими проводяться дії в електронних таблицях, є осередки і блоки осередків.

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

Після закінчення введення текстові дані автоматично вирівнюються по лівому краю осередку, а числові - по правому.

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

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

Мал. 4.

Щоб вибрати стовпець або рядок цілком, слід клацнути на заголовку стовпця (рядки) (Мал. 5.).

Мал. 5.

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

Якщо при виборі осередків утримувати натиснутою клавішу {Ctrl}, то можна додавати нові діапазони вже вибраному. Цим прийомом можна створювати навіть незв'язні діапазони (Мал. 6.).

Мал. 6.

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

Мал. 7.

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

1. Натиснення клавіші {Del} приводить не до видалення діапазону, а до його очищення, тобто до видалення вмісту вибраних осередків.

2. Для того, щоб реально видалити осередки вибраного діапазону (що супроводиться зміною структури таблиці), треба вибрати діапазон і дати команду Правка ®Удалить. При цьому відкривається діалогове вікно Видалення осередків (Мал. 8.), в якому можна вибрати напрям зсуву осередків, що займають місце, що звільняється.

Мал. 8.

3. По команді Правка ® Копировать або Правка ® Вырезать осередки вибраного діапазону обводяться пунктирною рамкою (Мал. 9).

Мал. 9.

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

4. Для вставки осередків, що копіюються з буфера обміну, треба зробити поточною осередок у верхньому лівому кутку області вставки і дати команду Правка ® Вставить. Осередки з буфера обміну вставляються у вказане місце. Якщо виконується операція переміщення, то після вставки осередки, з яких переміщаються дані, очищаються.

5. Копіювання і переміщення осередків можна також проводити методом перетягання. Для цього треба встановити покажчик миші на межу поточного осередку або вибраного діапазону. Після того, як він набере вигляду стрілки, можна провести перетягання (Мал. 10.).

Мал. 10.

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

Мал. 11.

Редагування даних в осередку. Введені дані можна редагувати двома способами.

1. Виділити осередок і відредагувати її вміст в рядку формул (заздалегідь клацнувши по рядку формул) (Мал. 12.).

Мал. 12.

2. Редагування вмісту безпосередньо в осередку. Для цього необхідно або двічі клацнути по активному осередку або натиснути клавішу F2.(рамка навколо осередку змінюється і з'являється текстовий курсор (, який можна перемістити в потрібне місце і внести зміни) (Мал. 13.).

Мал. 13.

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

Автозавершення. Для автоматизації введення текстових даних використовується метод автозавершення. Його застосовують при введенні в осередки одного стовпця текстових рядків, серед яких є ті, що повторюються. В ході введення текстових даних в черговий осередок програма перевіряє відповідність введених символів рядкам, наявним в цьому стовпці вище. Якщо виявлений однозначний збіг, введений текст автоматично доповнюється (Мал.14.). Натиснення клавіші {Enter} підтверджує операцію автозавершення, інакше введення можна продовжувати, не обертаючи уваги на пропонований варіант.

Мал. 14.

Автозаповнення числами. При роботі з числами використовується метод автозаповнення. У правій нижньому куті рамки поточного осередку є чорний квадратик – маркер заповнення. При наведенні на нього покажчик миші набуває форми тонкого чорного хрестика (Мал. 15.).

Мал. 15.

Перетягання маркера заповнення розглядається як операція «розмноження» вмісту осередку в горизонтальному або вертикальному напрямі. Якщо вміст осередку містить число, то при перетяганні маркера відбувається копіювання осередків (Мал. 16.) або їх заповнення арифметичною прогресією. Для вибору способу автозаповнення слід проводити спеціальне перетягання з використанням правої кнопки миші. Спосіб заповнення необхідно вибрати в меню, що відкрилося (Мал. 17.).

Мал. 16.

Мал. 17.

Якщо вибрати пункт Заповнити, то всі осередки діапазону A1:A5 будуть заповнені числами 1,2,3,4,5 (Мал. 18.)

Мал. 18.

Якщо потрібний складніший закон зміни послідовності значень, процедура декілька ускладнюється. Щоб точно сформулювати умови заповнення осередків, слід дати команду Правка ® Заполнить ® Прогрессия. У діалоговому вікні, що відкрилося, Прогресія (Мал. 19.) вибирається тип прогресії, величина кроку і граничне значення. Після клацання по кнопці OK програма Excel автоматично заповнює осередки відповідно до заданих правил.

Мал. 19.

I.3 Створення і використання простих формул

Програма Excel розглядає вміст осередку як формулу, якщо воно починається із знаку рівності (=). Тим самим, щоб почати введення формули в осередок, досить натиснути клавішу «=». Проте вводити формулу зручніше, якщо в рядку формул клацнути на кнопці Змінити формулу. В цьому випадку безпосередньо під рядком формул відкривається Палітра формул (Мал.20.), що містить обчислене значення вказаної формули.

Мал. 20.

Під формулою розуміється набір чисел і посилань на числові осередки, сполучених знаками математичних операцій (+ - операція складання; - - операція віднімання; * - операція множення; / - операція ділення; ^ - операція піднесення до ступеня.).

Математичні операції

Операція	Позначення у формулі	Приклад
Піднесення до степеню Множення Ділення Додавання Віднімання Рівно Менше Більше Менше або рівно Більше або рівно Не рівно Діапазон Об'єднання діапазонів Максимум Мінімум	^ * / + - = < > <= >= <> : ; МАКС МІН	=3^2 =А8*С6 =D4/N5 =B2+5 =9-G6     =СУММ(А1:С10) =СУММ(А1;А2;А6) =МАКС(А3:С5) =МІН(Е2:Р7)

Щоб задати посилання на осередок, треба вказати у формулі його ім'я (Мал. 21). Це можна зробити уручну або клацанням на відповідному осередку по ходу введення формули.

Мал. 21.

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

Мал. 22.

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

Очевидно, для обрахунку виручки необхідно скористатись формулою

Виручка = Ціна ´ Кількість проданого.

Для обрахунку виручки по товару Товар1 введемо в осередок Е5 формулу

Для обрахунку виручки від Товару2 необхідно би було в осередок Е6 ввести формулу =С6*D6 і аналогічно для всіх товарів. Щоб зекономити час можна скористатися методом розмноження формул. Для цього необхідно:

1. Поставити табличний курсор в осередок Е5.

2. Навести курсор миші на маркер заповнення. При цьому він прийме вид + (хрестика).

+

3. Протягнути цей курсор вниз по стовпцю.

+

4. Отримаємо

Переглянувши осередки стовпця Е, в які ми розмножили формулу, переконаємось, що формула з осередку Е5 з модифікацією зкопіювалась в нижче розташовані осередки в виді:

Що нам і потрібно було.

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

Мал. 23.

На мал. 23. представлений приклад, коли при копіюванні формули =B2*C2 з осередку D2 в осередок D3 відбулася модифікація посилань: посилання B2 замінене посиланням B3, а C2 - C3.

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

Наприклад, якщо в вище розглянутому прикладі, необхідно ще розрахувати дані про виручку в конвертованій валюті, тоді уже розраховану Виручку в гривні необхідно перевести в Виручку в $ за формулою:

Виручка в $ = Виручка / Курс валюти

Яка матиме вид = Е5/В2^

Якщо цю формулу з осередку Е5 розмножити в нижче розташовані осередки, то отримаємо результат:

В осередках F6, F7 очевидні помилки, тому що в результаті розмноження в нижче розташованих осередках були отримані наступні формули:

Адреса осередку D2 (що містить курс валюти) модифікувалась в D3, D4, D5. А нам необхідно щоби ця адреса залишалась незмінною, тобто нам необхідно зберегти абсолютну адресу осередку.

Для того, щоб задати посилання на осередок як абсолютну, треба задати перед номером рядки або ім'ям стовпця символ «$». Таким чином, посилання на осередок, наприклад A1, може бути записана чотирма способами: A1 $A1, A$1 $A$1. При заповненні осередків формулою як відносна розглядається тільки та частина адреси, перед якою немає символу «$». Для зміни способу адресації при редагуванні формули треба виділити посилання на осередок і натиснути клавішу F4 (послідовним натисненням клавіші можна отримати один із способів адресації).

В нашому випадку перед розмноженням формули в ній необхідно адресу D2 зробити абсолютною, а саме перед номером рядка (номер 2) вставити знак $ (долара):

Мал. 24.

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

Мал. 25.