Загальний бюджет навчального часу 14 страница

1. Основні поняття про електронну таблицю Excel. Запуск програми.

2. Основні елементи Excel.

3. Створення та збереження файлів книг.

4. Уведення, редагування даних, форматування комірок.

5. Консолідація даних.

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

1. Основні поняття про електронну таблицю Excel. Запуск програми. Часто під час обробки даних виникає потреба зображувати їх у вигляді таблиць. Проводячи розрахунки даних, поданих у табличній формі, широко використовують спеціальні програми для роботи з електронними таблицями. Іноді такі програми називають табличними процесорами. На екрані дисплея електронна таблиця має вигляд прямокутної матриці, що складається з колонок і рядків, на схрещенні яких утворюються клітинки (комірки). Колонка та рядок мають ідентифікатор, так що кожна комірка визначається однозначно. У комірки поміщуються числа, математичні формули й тексти. Зараз найширше використовують табличний процесор Microsoft Excel для Windows.

Для запуску програми Excel слід виконати команду Пуск / Программы / Mиcrosoft Excel, після чого на екрані з’явиться вікно таб­личного процесора (рис. 33). Вікно містить низку типових еле­ментів: рядок заголовка, рядок меню, яке має такі пункти:

- Файл – робота з файлами книг (створення, збереження, відкриття файлів, друкування файлів);

- Правка – редагування книг;

- Вид – перегляд книг;

- Вставка – вставляння в аркуші книг малюнків, діаграм та інших типів даних;

- Формат – форматування книг (установлення параметрів, форматів таблиць);

- Сервис – сервісні функції (установлення параметрів налашту­вання Excel);

- Данны е – робота з базами даних;

- Окно – робота з вікнами книг;

-? – виклик довідкової інформації.

Рис. 33. Вікно програми Excel

Інші типові елементи:

Панелі інструментів. Для вибору потрібної панелі слід скориста­тися командою Вид / Панель инструментов. Стандартно Excel виводить на екран панелі інструментів Стандартная і Форматирование.

Рядок формул. Для введення та редагування формул у першому вікні рядка виводиться адреса поточної комірки.

Рядок стану. У цей рядок виводиться інформація про хід вико­нання деяких операцій.

2. Основні елементи Excel. Книга – це документ, який створюється в табличному процесорі Excel. Кожна книга складається з аркушів різних типів: робочі аркуші, листи діаграм, аркуші макросів модулів і т. п. Робочі аркуші – це електронні таблиці, що складаються з колонок (стовпців) і рядків. Максимальне число колонок таблиці – 255, рядків – 16 384. Колонки позначаються зліва направо літерами – A…Z, AA-AZ…. Рядки позначаються згори вниз цифрами від 1 до 16 384. На перетині колонки й рядка розміщується комірка. Позначення (адреса) комірок складається з позначення колонки та рядка. В Excel може одночасно існувати кілька вікон книг. Для маніпуляцій із вікнами використовують меню Окно. Табличний курсор переміщують по таблиці за допомогою клавіш керування курсором або за допомогою миші.

Excel має могутні засоби адаптації до вимог конкретного користу­вача. За їх допомогою можна змінити зовнішній вигляд екрана програми, параметри редагування, перегляду, збереження, друкування таблиць. Ці засоби реалізуються командою меню Сервис / Параметры. Вікно має низку вкладок. Розгорнувши вкладку, можна встановити відповідні параметри.

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

Вкладка Общие встановлює різні режими для позначки адреси комірок та ін.

3. Створення й збереження файлів книг. Роботу із файлами книг виконують за допомогою пункту горизонтального меню Файл.

Створення таблиці. Створюючи новий документ, натисніть кнопку на панелі інструментів або виберіть команду Файл / Создать. На екрані з’явиться нове вікно документа з іменем Книга 1 або Книга 2, або …) для введення даних таблиці.

Завантаження таблиці. Будь-яку таблицю, яку було збережено під певним іменем, можна завантажити до процесора Excel для подальшого редагування або перегляду. Для цього слід скористатися командою Файл / Открыть або натиснути кнопку на панелі інструментів. На екрані з’явиться діалогове вікно, робота з яким відбу­вається за стандартними правилами, тобто потрібно вказати дисковод, папку, ім’я файла.

Збереження книги. Для збереження таблиці слід виконати команду Файл / Сохранить або натиснути на панелі інструментів кнопку . Якщо книга нова й ця команда виконується для неї вперше, то відкривається діалогове вікно Сохранение документа, де можна вказати дисковод, папку, увести ім’я, під яким файл буде збережено. Стандартно Excel присвоює книгам розширення xls, що вказує на тип інформації файла та на програму-додаток, засобами якої цей файл створено.

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

Якщо потрібно зберегти файл під новим іменем чи в іншій папці або з іншим форматом, слід виконати команду Файл / Сохранить как …, – на екрані з’являється діалогове вікно, у якому слід виконати дії для того, щоб змінити папки розміщення файла чи змінити ім’я документа або вказати новий формат для збереження таблиці.

Закриття файла. Після завершення роботи над таблицею можна закрити цей файл командою Файл / Закрыть або кнопкою в рядку заголовка.

4. Уведення й редагування даних, форматування комірок. Для того, щоб увести або редагувати дані у будь-якій комірці таблиці, слід зробити цю комірку активною. Перед уведенням можна вибрати кнопками панелі інструментів Форматирование шрифт у списку Шрифт, його розмір і тип. Вводити дані в активну комірку можна безпосередньо в полі самої комірки чи в полі рядка формул. Натискування клавіші Enter завершує введення даних у комірці, а клавіші Esc – відміняє його. Щоб відредагувати дані у поточній комірці, слід натиснути клавішу F2.

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

За допомогою команди Формат / Ячейки (рис. 34) можна зміню­вати числові формати даних поточної комірки, вкликати різні режими розміщення даних, переносу тексту за словами в межах однієї комірки, змінювати шрифт, межі комірок, вирівнювання та захист.

5. Використання формул. Формула – це сукупність операндів, з’єднаних між собою знаками операцій. Операндом може бути число, текст, адреса комірки, логічне значення, функція.

Арифметичні операції:

додавання – +;

віднімання – -;

множення – ×;

ділення – /;

піднесення до степені – ^.;

Операції відношень:

більше – <;

менше – >;

дорівнює – =;

менше або дорівнює – <=;

більше або дорівнює – >=;

не дорівнює – <>..

Рис. 34. Діалогове вікно Формат ячеек

Формула електронної таблиці починається зі знака рівності, а далі записуються числа чи адреси тих комірок, у яких містяться потрібні числа та знаки математичних операцій (+, -, ×, /).

Наприклад: = 25 + B1 × C3 – число, яке міститься в комірці В1, помножиться на число, яке розміщене в С3, а потім їхня сума збільшиться на 25.

Адреси комірок можна вводити до формули за допомогою клавіа­тури (перемкнувши розкладку клавіатури на англійську мову) та методом вказівки або клацання мишею на потрібну комірку.

Типовi повiдомлення про помилки:

1) #ЗНАЧ! – користувач намагається виконати недопустиму операцiю, наприклад, вiд текстових даних вiдняти числові;

2) #ІМ’Я? – неправильна адреса комірки (в адресi А1 лiтера А – українська, а має бути англiйська тощо).

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

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

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

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

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

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

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

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

Копiювання формул й автоматичне переобчислення в таблицi – це два головні засоби автоматизацiії обчислень в ЕТ.

5. Консолідація даних полягає в створенні підсумкової таблиці, що дає змогу узагальнити однорідні дані. На­приклад, можна виробити підсумовування даних за товарами одних і тих самих найменувань, що зберігаються на різних складах (рис. 35). Під час консолідації значення, наведені в підсумковій табли­ці, можна розраховувати на основі вихідних даних із використанням різних функцій Excel. Вихідні дані (області) можуть розміщуватися на одному або різних аркушах, в інших відкритих книгах. У процесі консолідації можна створити зв’язки, що забезпечують автоматичне оновлення даних у підсумковій таблиці (області призначення) в разі зміни даних у вихідних областях. Консолідацію даних можна виробити за розмі­щенням комірок, що містять вихідні дані, за категоріями, за допо­могою тривимірних посилань, зведеної таблиці й т. д. При цьому в усіх вихідних діапазонах дані мають розміщуватися в однаковому порядку. Так, якщо ми розглядаємо кількість товарів одного найменування на різних складах, то в усіх звітах, наданих різними складами, рядки та стовпці у таблицях звітів мають бути розміщені в однаковому порядку.

Рис. 35. Приклад таблиці вихідних даних для консолідації

Консолідація даних за розміщенням. Діапазони, дані яких консолідуються й поміщаються у вказаний кінцевий діапазон, називають вихідними областями. Вони можуть розміщуватися на будь-якому аркуші або книзі, на інших відкритих аркушах чи книгах, а також на аркушах Lotus 1–2–3. Якщо в усіх відомостях кількість упаковок “Рибні продукти” наведено в комірці F3, то після консо­лідації в підсумковій відомості буде узагальнено значення, що зберігаються в цій комірці, в усіх відомостях.

Області консолідації

Рис. 36. Діалогове вікно Консолидация

У діалоговому вікні Консолидация в списку, де розкривається Функция (Function), виберіть підсумкову функцію, яку слід вико­ристо­ву­вати для обробки даних, наприклад Сумма ( Sum), якщо підсумову­ватимуться значення. Опис функцій, наведених у списку, подано в табл. 3.

Таблиця 3

Підсумкові функції, використовувані під час побудови

зведених таблиць і діаграм

Операція	Призначення
Сума (Sum)	Обчислення суми чисел, що зберігаються у вихідних комірках. Цю операцію використовують стандартно (поза вибором) для підбиття підсумків за числовими полями
Кількість (Count)	Кількість записів або рядків даних. Цю операцію вико­ристовують стандартно для підбиття підсумків за нечисло­вими полями
Середнє (Average)	Обчислення середнього числа за даними, які зберігаються у вихідних комірках
Максимум (Мах)	Визначення максимального числа за даними, що збері­гаються у вихідних комірках
Мінімум (Min)	Визначення мінімального числа за даними, що зберігаються у вихідних комірках
Добуток (Product)	Обчислення добутку чисел, що зберігаються у вихідних комірках
Кількість чисел (Count Nums)	Кількість записів або рядків, що містять числа
Зміщене відхилення (StdDev)	Зміщена оцінка стандартного відхилення генеральної сукупності за вибіркою даних
Незміщене відхилення (StdDevp)	Обчислення стандартного відхилення генеральної сукупності за вибіркою даних, що зберігаються у вихідних комірках
Зміщена дисперсія (Var)	Зміщена оцінка дисперсії генеральної сукупності за вибіркою даних
Незміщена дисперсія (Varp)	Незміщена оцінка дисперсії генеральної сукупності за вибіркою даних

Після установки курсора в полі Ссылка (Reference) уведіть посилання на перший діапазон даних, який консолідується у вказаний кінцевий діапазон. Посилання введіть уручну або, якщо аркуш, що містить нову вихідну область, є поточним, виділіть на ньому вихідну область. Якщо вихідні області й область призначення розміщені на різних аркушах, використовуйте ім’я аркуша та ім’я або посилання на діапазон. Аби прибрати діалогове вікно Консолидация (Consolidate) на час вибору вихідної області, натискуйте кнопку Свернуть диалоговое окн о (Collapse dialog) у правій частині поля. Повторне натиснення на цю кнопку відновлює вікно. Якщо вихідні дані містяться в іншій книзі, яка в цей момент закрита, натисніть кнопку Обзор (Browse) і виберіть книгу. Дорога до вибраного посилання відображуватиметься в полі Ссылка(Reference). Потім додайте посилання. Натискуйте кнопку Дoбaвumь (Add). Уведене посилання відображуватиметься у вікні Список диапазонів (All references). Повторіть цю операцію для всіх вихідних областей, що консолі­дуються. Аби автоматично онов­лю­вати підсумкову таблицю в разі зміни джерел даних, установіть прапорець Создать связи с исходными данными (Create links to source data). Зв’язки не можна використовувати, якщо вихідна область та область призначення містяться на одному аркуші. Після встановлення зв’язків не можна додавати нові вихідні області та змінювати ті з них, що вже входять у консолідацію. Консолідуючи дані за розміщенням, заголовки категорій вихідних областей не копію­ються автоматично в область призна­чення. Якщо в області призна­чення потрібно розмістити заголовки, скопіюйте або введіть їх уручну.

Консолідація даних за категоріями. Виберіть зі списку, що розкривається, Функция (Function) функцію, яку слід вико­ристо­вувати для обробки даних (рис. 35). Використовувані підсумкові функції наведено в табл. 1. Уведіть вихідну область даних, що консоліду­ються, у поле Ссылка (Reference). Переконайтеся, що вихідна область має заголовок. Натискуйте кнопку Добавить(Add) для дода­ван­ня діапазону до списку вихідних діапазонів консолідації. Повторіть цю операцію для всіх вихідних областей, що консолі­дуються. У наборі прапорців Использовать как имена (Use labels in) установіть прапорці, відповідні розміщенню у вихідній області заголовків: у верхньому рядку, у лівому стовпці або у верхньо­му рядку й у лівому стовпці одночасно.

Хід роботи

1. Запустіть програму Excel. Ознайомтеся з вікном програми.

2. Задайте відповідні параметри для подальшої роботи.

Сервис / Параметры / закладка Вид.

3. Уведіть дані, як показано на рис. 37:

№ з/п

Груші

Звіт із продажу фруктів

Рис. 37. Дані до завдання 3

У стовпці Сума застосуйте формулу. Наприклад, = С3 × D3 і т. д. Використайте метод копіювання формул.

4. Відформатуйте стовпець Сума.

Відцентруйте дані, збільшіть розрядність числового формату, виділіть дані жирним шрифтом і візьміть у рамку. Скористайтеся командою Формат / Ячейки …

5. Повторіть усе це для стовпця D.

6. Скопіюйте таблицю на Аркуш 2

7. Поверніться на Аркуш 1 і продовжіть ручне форматування таблиці. Рядок із заголовком виділіть жирним шрифтом червоного кольору. Фон зробіть синім, а шрифт – білим кольором. Стовпець Товари затонуйте в жовтий колір, а Кількість – у зелений. Розграфіть таблицю.

8. На Аркуші 2 проведіть автоформатування таблиці. Тип авто­форма­тування виберіть довільний, поекспериментуйте. За допо­могою режиму відображення формул перевірте правильність формул у стовпці Сума.

9. Скопіюйте дані з Аркуша 2 на Аркуш 3.

10. Змініть дані в стовпцях Кількість і Ціна. Значення в стовпці Сума зміняться.

11. Виділіть комірку Е14 і знайдіть суму значень у стовпці Сума.

12. На Аркуші 4 проведіть консолідацію трьох таблиць із листів 1, 2 та 3.

Скористайтеся командою Данные / Консолидация. Збережіть книжку під назвою Консолідація.

13. Уведіть дані, як показано на рис. 38.

Рис 38. Дані до завдання 13

14. Перший стовпець заповніть, користуючись командою Правка / Заполнить / Прогрессия ….

15. Під який відсоток річних було взято кредит? Обчислення прове­діть у комірці Е15.

16. Через скільки років кредит сплатять, якщо суму внеску збільшити на 120 грн. Обчислення проведіть у комірці Е16.

17. Скопіюйте формулу з комірки D5 в D6. Чи змінився результат?

18. Який внесок потрібно робити щороку, щоб виплатити кредит у той самий термін, якщо відсоток річних становитиме 20 %. Результат занесіть у комірку Е17.

19. Збережіть книжку під назвою Відсотки.

20. Продемонструйте роботу викладачеві. Завершіть роботу.

Лабораторна робота 19–20

Тема. Технологія застосування функцій у таблицях. Операції реда­гування таблиць. Фільтрація та сортування даних. Підбиття підсумків.

Мета. Ознайомитися із загальними правилами редагування таблиць. Отримати навички роботи з логічними, статистичними функціями. Навчитися використовувати автофільтр та розширений фільтр, упорядковувати й шукати дані, підбивати підсумки, будувати мате­матичні вирази.

План

1. Використання функцій Excel.

2. Редагування даних.

3. Фільтрація даних.

4. Пiдсумки в таблицях.

5. Сортування даних.

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

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

За призначенням функції поділяють на декілька категорій:

· математичні;

· статистичні;

· фінансові;

· функції дати та часу;

· для роботи з посиланнями й масивами;

· текстові;

· для роботи з базою даних;

· логічні;

· для перевірки властивостей та значень.

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

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

Майстер функцій можна завантажити через пункт меню Вставка / Функция … або за допомогою кнопки на панелі інстру­ментів. У будь-якому випадку з’явиться вікно Мастер функций (рис. 39).

Рис. 39. Діалогове вікно Мастер функций

Як будь-який із Майстрів, він містить кроки для вибору та виконання функцій.

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

Категорія 10 недавно использовавшихся містить імена функцій, які користувач нещодавно вже викликав. Із неї також можна вибрати по­трібну для роботи функцію. Після цього вибору натискають OK.

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

Рис. 40. Діалогове вікно для занесення параметрів функції

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

Рис. 41. Мінімізоване вікно для занесення параметрів функції

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

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

Для остаточного виконання функції натискають OK – і результат з’являється в комірці.

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

Microsoft Excel сам визначить найближчий діапазон чисел та запропонує виконати обчислення. Якщо вибраний програмою діапа­зон задовольняє користувача, потрібно просто натиснути Enter, якщо ж ні – спочатку виділити потрібний діапазон і натиснути на Enter.

2. Редагування даних. Дані, уведені в комірку, можна змінити, замінити або вилучити.

Заміна даних: заносять нове значення й натискають Enter.

Зміна даних: вибирають комірку,

· натискають клавішу F2, вносять зміни й натискають Enter, або

· двічі натискають ліву кнопку мишки, вносять зміни та натискають Enter, або

· вносять зміни в рядку формул і натискають Enter.

Зміна даних відбувається в режимі Правка.

На відміну від зміни та заміни даних, вилучення даних можна провадити не з однієї, а з декількох комірок (діапазону).

Діапазони бувають суміжні й несуміжні. У суміжних діапазонах між комірками немає проміжків і вони мають форму прямокутника, а в суміжних можуть бути проміжки. Суміжні діапазони позначаються адресою лівої верхньої комірки діапазону, двокрапкою й адресою правої нижньої комірки діапазону, наприклад A1:B5.

Несуміжні діапазони складаються з декількох суміжних і позначаються їхніми адресами, розділеними крапкою з комою: A2:C6; K3:K10; E2:G2.

Для роботи з діапазоном його потрібно виділити за допомогою мишки або клавіатури.

Виділення суміжного діапазону:

8 – натискають й утримують ліву кнопку мишки та протя­гують її по потрібних комірках;

7 – натискають та утримують клавішу Shift, а потім за допо­могою клавіш переміщенням курсора показують потрібний діапазон (виділений діапазон чорного кольору, а комірка, з якої починалося виділення, лишається світлою).

Виділення несуміжного діапазону:

8+7 – виділяють перший суміжний діапазон, що входить до складу несуміжного, і при натисненій клавіші Ctrl виділяють усі інші суміжні діапазони.

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

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

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

3. Фільтрація даних. Фільтр – це конструкція, призна­чена для відбору тих рядків таблиці, що задовольняють задану умову, і тимчасового приховання інших. Основою фільтра є список, що міс­тить умови відбору рядків. Передусім потрібно виділити діапа­зон, для якого буде створено фільтр. Вибрати команду Данные / Фильтр / Автофильтр. Після цього автоматично в комірках верхнього рядка виділеного діапазону створюється спеціальна кнопка ▼, що розкриває список фільтра.

Натиснувши на кнопку ▼, можна вибрати один із варіантів відбо­ру даних: перші десять рядків списку, задати умову фільтрації та ін. Щоб зняти фільтр потрібно повторно вибрати команду Данные / Фильтр / Автофильтр. Режим фільтрації можна скасувати командою Данные / Фильтр / Показать все.

---

Дата добавления: 2015-11-14