Модуль № 3 прикладні програми пакету microsoft office 2003

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

Електронні таблиці Microsoft Excel 2003

Мета: навчитися створювати і редагувати таблиці, бланки документів, виконувати математичні обчислення та створювати графіки і діаграми.

План

1. Складові електронних таблиць Microsoft Excel 2003.

2. Вікно програми та рядок основного меню.

3. Уведення даних у клітинки.

4. Формат клітинки.

5. Рядок формул.

6. Відносні адреси. Копіювання формул.

7. Форматування таблиць.

8. Діапазони клітинок. Виокремлення діапазону.

9. Використання формул і стандартних функцій.

10. Помилки у формулах.

11. Абсолютні та змішані адреси.

12. Розгалуження. Функція ЕСЛИ().

13. Списки.

14. Сортування та фільтрація даних.

15. Побудова діаграм.

16. Пункти меню Окно і Справка.

Теоретичні відомості

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

Електронна таблиця складається з клітинок (комірок), що утворюють рядки і стовпці. Стовпці таблиці позначені буквами (А, В, С, ...), а рядки цифрами (1, 2, ...). Кожна клітинка має адресу, наприклад, А1 – адреса верхньої клітинки. Стовпців може бути до 256, а рядків до 65 536.

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

Вікно програми (рис. 67) складається з:

• рядка, в якому є назва програми і поточної книги та кнопки керування вікном програми;

• рядка основного меню з кнопками керування вікном поточної книги;

• панелей інструментів, та рядка формул якщо вони ввімкнені;

• робочого поля книги;

• рядка статусу, що містить деякі відомості про поточний лист книги.

Рис. 67. Вікно електронних таблиць Microsoft Excel 2003

Рядок меню включає такі пункти: Файл – робота з файлами книг (створення, збереження, відкриття файлів, друкування файлів книг); Правка – редагування книг; Вид – перегляд книг; Вставка – вставка в аркуші книг малюнків, діаграм та інших типів даних; Формат – форматування книг (встановлення параметрів, форматів таблиць); Сервис – сервісні функції (встановлення параметрів настройки Excel); Данные – робота з базами даних; Окно – робота з вікнами книг; Справка (або ?) – виклик довідкової інформації.

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

Виокремлювати можна не лише одну, але й декілька клітинок (рядків чи стовпців).

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

Якщо почати вводити нове дане у клітинку, то старе зникає.

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

• двічі клацають мишею;

• натискають на клавішу F2;

• очищають клітинку командами з меню: Правка  Очистить  Все  вводять нове дане.

Уведений у клітинку текст (до 255 символів) автоматично вирівнюється вздовж лівого краю, а числа – вздовж правого.

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

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

Рис. 68. Вікно вибору формату клітинки

Відображення даного залежить від формату його зображення. Формати чисел у вибраних клітинках задають командою Формат  Ячейка  закладка Число (рис. 68). Корисним є формат Числовой, де задають кількість десяткових знаків після коми. Є й інші формати: загальний, грошовий, фінансовий, дата, час, процентний, дробовий, експоненціальний, текстовий, додатковий (поштовий індекс, номер телефону, бельний номер), усі формати користувача. Наприклад, число 1230,5 у форматі користувача # ##0,00грн.; [Червоний]-# ##0,00грн. буде зображене на екрані у грошовому форматі 1 230,50 грн., а від’ємне таке ж число буде зображене червоним кольором. Символ 0 у форматі – це вказівка відображати у відповідній позиції конкретну цифру або нуль, символ # – лише значущі цифри, пропуск забезпечує відокремлення груп цифр.

Розподілювачем цілої і дробової частини в числах може бути крапка або кома залежно від налаштування Windows. Розподілювач можна поміняти на закладці Числа у вікні програми Язык и региональные стандарты панелі керування (Пуск  Панель управления  Дата, время, язык и региональные стандарты  Язык и региональные стандарты).

Формули призначені для використання дій над вмістом клітинок (над даними) згідно з умовою конкретної задачі. Вони мають символ = на початку, наприклад, =В2*С2. Після введення формули у клітинці негайно отримують результат, а формулу можна побачити лише у рядку формул.

Формула записується або в поточній клітинці, або у рядку формул (рис. 69 а та б). Якщо клітинка порожня або в ній є деяка інформація, але не формула, рядок формул неактивний (рис. 69 а), тобто зліва відображається адреса клітинки, а справа – інформація. Якщо в клітинці поставити знак = (або натиснути на рядку формул), рядок формул активізується (рис. 69 б). Праворуч можна вибрати потрібну формулу із стандартного списку. Відмінити набрану формулу (вираз) можна, або натиснувши клавішу Esc, або натиснувши . Для того, щоб отримати значення виразу, потрібно або натиснути клавішу Enter, або натиснути на рядку формул.

а)

б)

Рис. 69. Рядок формул: а – у неактивному стані; б – в активному стані

Щоб побачити всі формули у таблиці, треба задати режим відображення формул у клітинках. Це роблять у діалоговому вікні Параметры так: Сервис  Параметры  закладка Вид   формули  ОК.

Щоб знову побачити результати, потрібно зняти режим відображення формул.

Якщо замість результатів ви отримали ######, то це означає, що велике число в клітинці не поміщається, отже, стовпець треба зробити ширшим.

Обчислення в таблиці ведуться зліва-направо і зверху-донизу (але цей порядок можна змінити) автоматично. Це означає, що зміна будь-якого вхідного даного негайно веде до переобчислень всієї таблиці (якщо задано режим  автоматически на закладці Вычисления діалогового вікна Параметры). Режим автоматически можна вимкнути і скористатися ручним керуванням за допомогою клавіші F9.

Адреси клітинок вигляду В3 чи С3 називаються відносними.

У Microsoft Excel є можливість копіювати однотипні формули (а не вводити їх у кожну клітинку зокрема), що прискорює розв’язування задач.

Під час копіювання формули відбуваються такі дві дії:

• формула вводиться в інші клітинки автоматично;

• формула автоматично модифікується – змінюються відносні адреси, на які є посилання у формулі.

Наприклад, під час копіювання формули =В3*С3 з третього рядка у четвертий формула в четвертому рядку набуде вигляду =В4*С4.

Копіювання виконують методом перетягування маркера клітинки у потрібному напрямку. Це інакше називають автозаповненням таблиці.

Копіювати можна не тільки формули, а й текст, і числа.

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

Копіювання формул і автоматичне переобчислення у таблиці – це два основні засоби автоматизації обчислень в Microsoft Excel.

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

Зазвичай таблиця на екрані має сітку, якщо увімкнуто режим відображення сітки, однак під час друкування на папері вона не відображається. Щоб таблиця була відповідним чином розграфлена на папері, треба задати параметри на закладці Граница діалогового вікна Формат ячеек (рис. 69): Формат  Ячейка  Граница.

Розглянемо інші закладки вікна Формат ячеек.

На закладці Выравнивание задають спосіб написання тексту в клітинці: горизонтально, вертикально, під кутом. Також на цій закладці можна встановити режими відображення: режим перенесення тексту по словах (коли потрібно, щоб великий текст був у вузькій клітинці) –  переносить по словам; режим автоматичного підбору ширини клітинки –  автоподбор ширины; режим об’єднання клітинок –  объединение ячеек. Об’єднати клітинки можна також за допомогою кнопки панелі інструментів Форматирование. Якщо в діапазоні клітинок для об’єднання був текст або дані в декількох клітинках, тоді при об’єднанні залишиться тільки та інформація, яка була у верхній лівій клітинці. Якщо необхідно роз’єднати об’єднані клітинки – Формат  Ячейки  закладка Выравнивание  прибрати галочку біля объединение ячеек  ОК.

На закладці Вид можна задати колір клітинок і узор. На закладці Защита – режими захисту клітинок від несанкціонованих змін і ховання формул. Захист треба задавати також у пункті Сервис головного меню (Сервис  Защита  Защитить лист).

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

Діапазони мають прямокутну форму і описуються адресами двох діагонально-протилежних клітинок. Наприклад:

• А1:С1 – прямокутний діапазон;

• А1:А9 – діапазон-стовпець;

• А1:Е1 – діапазон-рядок.

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

Щоб виокремити несуміжні діапазони, треба користуватися клавішею Ctrl.

Діапазонам можна надавати назви і використовувати ці назви у виразах.

Розглянемо правила утворення виразів у формулах. Будуючи вирази, треба пам’ятати про порядок операцій. Як в елементарній математиці, пріоритет виконання операцій (у спадному порядку) наведений у табл. 1.

Таблиця 1