Посібник “Microsoft Excel Завдання для практичних робіт”
30.09.2010, 21:28
Методичні рекомендації до посібника "Microsoft Excel Завдання для практичних робіт”
Урок 1. Мета: ознайомити з особливостями інтерфейсу програми, навчити працювати з комірками та листами, вводити та редагувати дані. Рекомендації: 1. Наголошуйте на обов’язковому збереженні файлів – вони часто бувають потрібні на наступних уроках. 2. У запропонованих завданнях до уроків часто вказуються адреси комірок, які використані у формулах. Тому рекомендуйте створювати таблиці, починаючи з комірки А1. 3. Уроки 17, 18, 19, 20 бажано використовувати для навчання дітей 10-11 класів та дорослих (для молодших учнів – на розгляд вчителя).
Урок 2. Мета: повторити відомі способи виділення фрагментів, ознайомити зі специфічними способами виділення комірок та діапазонів; повторити поняття формату, пояснити мету використання числових форматів; ознайомити зі способом створення нових форматів (6-8 завдання).
Урок 3. Мета: повторити відомі способи форматування тексту, використання команди "Формат за зразком”; ознайомити зі способами вирівнювання тексту в комірках.
Урок 4. Мета: ознайомити зі способами оформлення тексту і таблиць командами "Умовне форматування”, "Стилі” та "Автоформат”. Рекомендації: файл "Style” для імпортування стилів розміщений у папці "Завдання”.
Урок 5. Мета: ознайомити зі способами вставки формул в Excel. Рекомендації: 1. Поясніть доцільність використання ІІ способу (див. методичку) введення формул. 2. Поясніть на простому прикладі використання формули-зсилки на комірку і її практичне значення. 3. Якщо діапазон потрібно заповнити однотипними (але не однаковими) формулами, можна виділити комірки, ввести формулу чи вставити її з буферу обміну і натиснути комбінацію клавіш CTRL + ENTER. З тією ж метою можна використовувати спосіб автозаповнення. 4. Панель "Зависимости” можна використовувати як для пошуку помилок, так і для відображення зв’язку між комірками, дані з яких використовуються у формулі. Урок 6. Мета: детальніше ознайомити з поняттям зсилки у формулах, випадками застосування формул масиву; пояснити практичне використання операції Автосума, абсолютних зсилок. Масив – це діапазон комірок, вміст яких опрацьовується як єдине ціле. Формула масиву – це формула, яка оперує з одним або кількома масивами. Головною ознакою формули масиву є фігурні дужки. Рекомендації: обов’язково повторити числові формати, пояснити їх практичне значення при оформленні даних, які використовуються у формулах.
Урок 7. Мета: ознайомити з практичним значенням змішаних зсилок, способами введення в таблицю послідовності чисел. Змішану зсилку використовують у формулах, якщо потрібно, щоб при їх копіюванні не змінювалась адреса або стовпчика, або рядка. В формулі таблиці множення (=$A2*B$1) як вихідні дані використовують числа з першого стовпчика і рядка. Тому адреса першого компонента формули означає, що числа будуть вибиратися послідовно з рядків стовпчика А ($A – адреса стовпчика незмінна, 2 – адреса рядка може змінюватися). Адреса другого компонента означає, що у формулі використовуються числа з різних стовпчиків першого рядка. Після створення області вводу формули першого стовпчика і рядка таблиці множення треба змінити, використовуючи абсолютні і відносні зсилки на початкові значення.
Урок 8. Мета: показати практичне використання створеної на попередньому уроці таблиці множення. Рекомендації: 1. Використання формули розрахунку відсотків спочатку варто показати для одного випадку (наприклад, використати дані з області вводу), а потім для таблиці. 2. Залишки – це певна сума, яка залишається на депозитному рахунку на кінець дня. Вона може змінюватись щодня, або кілька днів залишатися незмінною. На цю суму нараховують 3% на рік, тобто 3%/365 щодня. Тому в таблиці для розрахунку процентів по залишках після вставки формули у поле "Залишки” потрібно ввести будь-які дані в кілька комірок на вибір. 3. Складні проценти – це нарахування процентів на основну суму і на нараховані раніше проценти. При цьому до уваги беруть період капіталізації (кількість днів), після закінчення якого здійснюється нарахування. В завданні запропонований період капіталізації 1 день, оскільки в такому випадку не потрібна складна формула.
Урок 9. Мета: ознайомити зі способами вставки функцій, використання простих функцій для математичних розрахунків; навчити створювати та змінювати примітки. Рекомендації: до 7 завдання варто дати пояснення, чому використовуються різні способи заокруглення. Приклад: при розрахунках у всіх комірках результатом є число з 5 десятковими знаками, тому їх варто заокруглити до 2 знаків, тобто копійок. Але при заокругленні ціни за одиницю товару замість числа 1,13825 одержимо 1,14. При перевірці розрахунків у цьому випадку одержимо різну вартість всієї партії товару (порівняйте 1,138251000 = 1138,25 грн. і 1,141000 = 1140 грн.).
Урок 10. Мета: навчити використовувати на практиці прості і складні формули; захист комірок і листів. Рекомендації: можна пояснити, що означають деякі терміни: податок на додану вартість (ПДВ), податковий кредит, податкове зобов’язання.
Урок 11. Мета: навчити використовувати функції обробки текстових даних, створювати складні формули методом вкладання простих. Рекомендації: 1. Для уроку роздрукуйте Додаток 1 з папки "Додатки”. 2. Файл "База даних.doc” зі списком полів розміщений у папці "Завдання”. 3. Метод вкладання простих формул буде використовуватись на багатьох уроках, тому варто детально його пояснити. При створенні складної формули потрібно скопіювати просту формулу і вставити її в іншу замість адреси комірки. Наприклад: в комірці А1 є формула =В8+В9, а в комірці В2 – формула =А1*К11. Після вставки формули замість адреси у комірці В2 одержимо такий результат: =(В8+В9)*К11. 4. На даному уроці не обов’язково заповнювати всю базу даних, для цього відведений час на наступному занятті. Примітка: наголосіть учням, щоб вносили у базу дані, запропоновані у додатку, тому що саме для цих даних складені завдання до уроків.
Урок 12. Мета: навчити працювати з готовою базою даних з допомогою форми та фільтрів. Примітка: готову базу даних потрібно обов’язково зберегти.
Урок 13. Мета: навчити створювати списки та працювати з ними; ознайомити з поділом листа на області для полегшення роботи з великими таблицями.
Урок 14. Мета: навчити користуватися статистичною функцією СЧЕТЕСЛИ, розв’язувати практичні задачі з допомогою формул з функціями. Функція СЧЕТЕСЛИ (категорія "Статистические”) дозволяє підрахувати кількість заповнених комірок, для яких виконується задана умова. Рекомендації: 1. До уроку роздрукуйте Додаток 2 з папки "Додатки”. 2. Перед виконанням 4 завдання повторіть з учнями, як користуватися функцією ОКРУГЛ.
Урок 14. Мета: навчити користуватися логічними функціями та функціями перевірки властивостей і значень при розв’язуванні практичних задач. Функцію ЕПУСТО (категорія "Проверка свойств и значений”) використовують для пошуку порожніх комірок в заданому діапазоні. Якщо комірка порожня, функція повертає значення ИСТИНА, якщо містить дані – значення ЛОЖЬ. Функція ЕСЛИ (категорія "Логические”) дозволяє перевірити, чи виконується задана умова. Для функції можна задавати свої значення, які вона поверне у випадку виконання чи невиконання умови. Функція НАЙТИ (категорія "Текстовые”) служить для пошуку фрагменту тексту в комірці і повертає номер позиції знайденого символу. В полі "Найти_текст” в лапках вказують фрагмент, в полі "Внутр¬_текст” – комірку чи діапазон, в якому слід здійснювати пошук, в полі "Нач_номер” можна вказати позицію знака, з якого починається пошук (поле не обов’язкове). У 7 завданні спочатку потрібно знайти позицію пробілу перед ініціалами, а потім з допомогою функції ЛЕВСИМВ залишити в комірці тільки прізвище. Наприклад, якщо у комірці є текст Іванов І.І., то функція НАЙТИ поверне значення 7, тобто позицію пробілу, а функція ЛЕВСИМВ поверне 7-1 знаків, тобто текст "Іванов”. Функцію И (категорія "Логические”) використовують для перевірки виконання кількох умов, заданих іншими функціями. Якщо умова виконується, то результатом є значення ИСТИНА. Функція И повертає значення ИСТИНА, якщо всі задані комірки містять значення ИСТИНА, і значення ЛОЖЬ, якщо хоча б одна із заданих комірок містить значення ЛОЖЬ.
Урок 16. Мета: навчити використовувати функції для розв’язування практичних задач. Функція ОТБР (категорія "Математические”) дозволяє відкинути в числі дробову частину повністю або частково. Рекомендації: 1. Для роботи використайте файл з попереднього уроку, або скопіюйте з нього дані про стаж роботи та посадові оклади у новий файл. 2. Щоб визначити стаж, обчисліть різницю поточної дати і дати прийому працівника (кількість відпрацьованих днів), а потім поділіть результат на 365,25 для визначення кількості років. 3. Формулу розрахунку розміру премії варто створювати поетапно. Спочатку в окремих комірках з допомогою функції ЕСЛИ визначте розмір премії для 1, 3, 5, 10 і більше 10 років стажу. Наприклад, якщо працівник має стаж менший 1 року, то розмір посадового окладу перемножується на коефіцієнт (для 1 року - 0), якщо стаж більший, то формула звертається до комірки з іншою формулою. Потім методом вкладання створіть складну формулу (або іншими словами модуль) для розрахунку премії.
Урок 17. Мета: навчити присвоювати коміркам та діапазонам імена і використовувати їх у формулах замість адрес; використовувати функції обробки дати і масивів. Рекомендації: 1. Файли "Модуль” і "Електронний табель” розміщені у папці "Завдання”. 2. 2-3 завдання. Модуль в даному випадку дозволяє перетворити дату, вказану в таблиці в звичайний формат типу 25.01.2003. Спочатку модуль використовують на листі "Святкові дні”, а потім в самому табелі в комірках С8:С9. Таке перетворення дати потрібне тому, що в табелі рік, місяць і день вказані у різних комірках. 3. 4-5 завдання. Імена потрібно присвоїти коміркам табеля, які пізніше будуть використовуватись у формулах (Рік, Місяць, День). 4. 8 завдання. Функція ВПР (категорія "Ссылки и массивы”) дозволяє відшукати задану дату серед святкових днів. В поле "Искомое_значение” потрібно ввести адресу комірки з датою в табелі – С9. В поле "Табл_массив” – діапазон комірок, в якому треба знайти вказане значення (в даному випадку це діапазон дат G3:G14 на листі "Святкові дні”). В полі "Номер_индекса_столбца” можна вказати, в якому стовпчику діапазону здійснюється пошук. І, нарешті, в полі "Диапазон_просмотра” потрібно вказати точно (ИСТИНА) чи приблизно (ЛОЖЬ) буде порівнюватись заданий текст зі знайденим. В результаті треба одержати значення ИСТИНА, якщо день святковий, або ЛОЖЬ, якщо ні. Але функція ВПР повертає помилку #Н/Д, замість значення ЛОЖЬ. Тому наступні кроки потрібні для перетворення помилки: функція ЕНД перетворить помилку на значення ИСТИНА, а функція НЕ інвертує це значення, тобто перетворить на протилежне. 5. 10 завдання. З допомогою функції ДЕНЬНЕД (категорія "Дата и время”) можна визначити, якому дню тижня відповідає будь-яка дата (повертає порядковий номер дня тижня). В даному завданні потрібно визначити, які дні будуть вихідними, тобто мати порядковий номер 1 або 7. Для цього і використана наступна функція ЕСЛИ. 6. 11 завдання. Функція ИЛИ перевіряє, чи справджується хоча б одна із заданих умов. Тому, якщо день вихідний або святковий, результатом буде значення ИСТИНА. 7. Складну формулу потрібно створити використовуючи формули з діапазону С25:С30. 8. Перед збереженням таблиці скопіюйте діапазон С8:С10 у діапазон С8:AG10.
Урок 18. Мета: ознайомити з випадками використання функції СУММЕСЛИ, повторити функцію СЧЕТЕСЛИ. Функцію СУММЕСЛИ (категорія "Математические”)використовують для знаходження суми чисел у вказаному діапазоні, якщо виконується задана умова (умова може бути в іншому діапазоні). Рекомендації: 1. В 3 завданні потрібно використовуючи функцію ВПР знайти назву місяця (в поле "Искомое_значение” ввести адресу іменованої комірки Місяць) в третьому стовпчику таблиці з нормативною кількістю годин і днів на листі "Святкові дні” і повернути знайдене число. 2. В 4 завданні можна використати повідомлення про помилку, якщо умова не виконується. Для цього в поле "Если_ложь” введіть текст: "Помилка!”.
Урок 19. Мета: використання функції ЕСЛИ для розв’язування практичних задач з багатьма умовами. Рекомендації: 1. Для роботи роздрукуйте Додаток 3 з папки "Додатки”. 2. Для даної таблиці початковою є комірка А2. 3. П’яте завдання можна пропустити, якщо формула складна для розуміння. Формула діє таким чином: спочатку перевіряє, чи є буква "Н” в комірці В6, тобто чи людина має право на пільги. Якщо буква "Н” є, то в комірці буде 0. Далі здійснюється перевірка комірки В7 – права на стандартні відрахування. Якщо В7=”С”, то в першу чергу сума доходу за попередні місяці і останній порівнюється з сумою 20000, і повертається або сума відрахувань по кате-горіях "Ч” і "Г”, або сума стандартного податкового відрахування. Потім функція шукає максимальне значення в комірках В14:В16 (чорнобильці, герої та інші пільгові категорії) і додає до них суму стандартних відрахувань на дітей. Якщо В7”С”, то повертається значення 0. 4. Для 7 завдання використайте функцію ЕСЛИ. 5. Якщо учні не встигнуть створити складну формулу, то на наступному уроці запропонуйте їм готовий модуль (з папки "Допомога”).
Урок 20. Мета: розв’язування практичних задач. Рекомендації: 1. Якщо на попередньому уроці учні не встигли створити модуль, його можна використати з файлу "Модуль”, розміщеного в папці "Допомога”. 2. В завданнях вказані конкретні адреси комірок для формул, тому наголосіть учням, що таблицю обліку доходів треба починати з комірки Е8. 3. В 10 завданні створіть складну формулу (можна використати функцію СУММ) для знаходження суми відрахувань. Для цього потрібно додати суми за вказаними статтями доходів попередньо перемноживши їх на ставки оподаткування (ставки вказані).
Урок 21. Мета: навчити створювати і змінювати звітні таблиці для аналізу даних.
Урок 22. Мета: навчити створювати формули для аналізу даних. Рекомендації: 1. Для присвоєння імені діапазону його можна виділити разом із заголовком і вибрати команду Вставка/Имя/Создать. 2. В 4 завданні використовується формула масиву, тому бажано повторити випадки її використання та спосіб створення. В даному випадку формула дозволяє здійснювати пошук заданої умови в одному діапазоні і, якщо ця умова знайдена, повертати значення з відповідної комірки другого діапазону. 3. Формулу з кількома функціями можна створити з допомогою майстра функцій. Наприклад, для формули СУММ (ЕСЛИ (умова; ИСТИНА; ЛОЖЬ)) спочатку потрібно вибрати функцію СУММ, а потім в поле першого аргументу вставити функцію ЕСЛИ. 4. При створенні модуля по черзі скопіюйте з формул діапазону В3:В6 функцію ЕСЛИ з аргументами і вставте у формулу в комірці В2 замість другого аргументу функції ЕСЛИ (тобто замість назви діапазону Витрати). Примітка: при редагуванні формули масиву кожного разу після внесення змін потрібно натискати комбінацію клавіш CTRL+SHIFT+ENTER.
Урок 23. Мета: розв’язування практичних задач. Рекомендації: 1. Файл "Домашній бюджет” розміщений у папці "Завдання”. 2. Формулу масиву потрібно вставляти в першу комірку діапазону, а в решту комірок копіювати з допомогою автозаповнення. 3. В таблиці, яка містить дані і в стовпчиках, і в рядках формулу масиву спочатку треба скопіювати автозаповненням у стовпчик, а потім у решту комірок.
Урок 24. Мета: навчити створювати і оформлювати діаграми та графіки в Excel.
Урок 25. Мета: навчити створювати та оформлювати географічні карти. Рекомендації: 1. Рекомендуйте учням створювати карти США, Канади, Мексики, Австралії. Таблиці з даними по інших країнах чи регіонах неповні, тому створена по них карта буде неповною. 2. Після створення карти учні повинні скопіювати її і зберегти в новому файлі, щоб не змінювати файл Mapstats.xls.
Урок 26. Мета: ознайомити зі способами створення та використання макросів; навчити створювати нові панелі інструментів, команди меню та кнопки для запуску макросів.
Урок 27. Мета: ознайомити з настройками параметрів сторінки та навчити виводити на друк таблиці чи їх фрагменти. Рекомендації: молодшим учням можна не показувати підключення сіткового принтера (4 завдання).
