Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Міністерство освіти і науки України
Лабораторна робота №5
Проведення обчислень в MS Excel
Рівне – 2007
Лабораторна робота №5
Проведення обчислень в MS Excel
5.1. Мета роботи
Освоїти принципи роботи з електронними таблицями. Вивчити інтерфейс та призначення табличного процесора MS Excel. Навчитися застосовувати електронні таблиці для проведення різноманітних обчислень.
5.2. Теоретичні відомості
Електронні таблиці або табличні процесори – це тип прикладних програм, що надають комплексні засоби для збереження різних типів даних та їх обробки. Основний акцент зміщений на перетворення даних, наданий широкий спектр методів для роботи з числовими даними. Основна особливість електронних таблиць полягає у автоматичній зміні вмісту всіх комірок при зміні відношень, заданих математичними або логічними формулами. Такі програми знаходять широке застосування у бухгалтерському обліку, аналізі фінансових та торгівельних ринків, засобах обробки результатів експериментів, тобто у автоматизації регулярно повторюваних обчислень великих об'ємів числових даних.
Однією з найпоширеніших програм для роботи з електронними таблицями є програма Microsoft Excel, що входить до складу ППП Microsoft Office. Вікно програми дуже схоже на вікно Microsoft Word. Але робоча область має вигляд таблиці з великою кількістю однакових комірок. Налаштування інтерфейсу програми здійснюється точно таким же чином як і у програмі MS Word. Під панелями інструментів Microsoft Excel зазвичай знаходиться рядок формул, а в нижній частині вікна – рядок стану. Щоб вивести або забрати ці рядки, треба в меню Вид вибрати відповідні пункти: Строка формул або Строка состояния.
Файл Microsoft Excel називається робочою книгою. Робоча книга складається з робочих листів (аркушів), імена яких (Лист1, Лист2, ...) виведені на ярликах у нижній частині вікна робочої книги. Клацаючи по ярликах, можна переходити від аркуша до аркуша усередині робочої книги.
Робочий аркуш являє собою таблицю, що складається з 256 стовпців і 65536 рядків. Стовпці іменуються латинськими буквами, а рядки – цифрами. Кожна комірка таблиці має адресу, що складається з імені рядка й імені стовпця. Наприклад, якщо комірка перебуває в стовпці F і рядку 7, то вона має адресу F7.
Одна з комірок таблиці завжди є активною. Активна комірка виділяється рамкою. Щоб зробити комірка активною, необхідно клавішами керування курсором підвести рамку до цієї комірки або клацнути в ній мишею.
Для виділення декількох суміжних комірок необхідно встановити вказівник миші в одну з комірок, нажати ліву кнопку миші й, не відпускаючи її, розтягти виділення на всю область. Для виділення декількох несуміжних груп комірок варто виділити одну групу, нажати клавішу Ctrl й, не відпускаючи її, виділити інші комірки.
Для введення даних в комірку необхідно зробити її активною й ввести дані із клавіатури. Дані з'являться в комірці і у рядку редагування. Для завершення введення варто натиснути Enter або одну із клавіш керування курсором. Процес уведення даних закінчиться й активною стане сусідня комірка.
Щоб відредагувати дані в комірці, необхідно:
Робота з формулами
Обчислення в таблицях виконуються за допомогою формул. Формула може складатися з математичних операторів, значень, посилань на комірки та імен функцій. Результатом виконання формули є деяке нове значення, що міститься в комірці, де знаходиться формула. Формула починається зі знака рівності "=". У формулі можуть використовуватися арифметичні оператори +, -, *, /. Порядок обчислень визначається звичайними математичними законами.
Приклади формул: =(А4+В8)*З6, =F7*З14+B12.
Константи – текстові або числові значення, які вводяться в комірку і не можуть змінюватися під час обчислень.
Посилання на комірку або групу комірок – спосіб, яким вказується конкретна комірка або кілька комірок. Посилання на окрему комірку - її координати. Значення порожньої комірки дорівнює нулю.
Посилання на комірки бувають двох типів:
Можуть використовуватись комбінації двох типів (наприклад: F$7).
При копіюванні формул відносні посилання змінюються на розмір переміщення.
Для звертання до групи комірок використовуються спеціальні символи:
: (двокрапка) – формує звертання до блоку комірок. Через двокрапку вказується ліва верхня і права нижня комірка блоку. Наприклад: С4:D6 – звертання до комірок С4, С5, С6, D4, D5, D6.
; (крапка з комою) – позначає об'єднання комірок. Наприклад, D2:D4;D6:D8 – звертання до комірок D2, D3, D4, D6, D7, D8.
Для введення формули в комірку варто ввести знак '=' і формулу для обчислення. Після натискання клавіші Enter в комірці з'явиться результат обчислення. При виділенні комірки, що містить формулу, формула з'являється в рядку редагування.
Функції
Функціями в Microsoft Excel називають об'єднання декількох обчислювальних операцій для рішення певного завдання. MS Exсel має кілька сотень вбудованих функцій, згрупованих по категоріях – математичні, статистичні, логічні та ін. Щоб викликати список вбудованих функцій, необхідно викликати майстер функцій (меню Вставка/Функция), вибрати потрібну категорію функцій і в списку відшукати потрібну функцію.Функції в Microsoft Excel являють собою формули, які мають один або кілька аргументів. Як аргументи вказуються числові значення або адреси комірок.
Наприклад:
=СУМ(А5:А9) – сума комірок А5, А6, А7, А8, А9;
=СРЗНАЧ(G4:G6) – середнє значення комірок G4, G5, G6.
Функції можуть входити одна в іншу, наприклад:
=СУМ(F1:F20)ОКРУГЛ(СРЗНАЧ(H4:H8);2);
Для введення функції в комірку необхідно:
Вставити в комірку функцію суми СУМ можна за допомогою кнопки .
Масиви формул
Масиви формул зручно використати для введення однотипних формул й обробки даних у вигляді таблиць. Наприклад, для обчислення модуля від чисел, розміщених в комірках B1, C1, D1, E1, замість введення формул у кожну комірку можна ввести одну формулу – масив для всіх комірок. Microsoft Excel додає навколо масиву формул фігурні дужки { }, по яких його можна відрізнити.
Для створення масиву формул необхідно:
Для редагування масиву формул необхідно:
Рис.5.1. Введення масиву формул.
Копіювання й переміщення даних
Для переміщення й копіювання вмісту комірок використається буфер проміжного зберігання інформації (Clipboard), що дозволяє переміщати дані як у межах таблиці, так й в інші Windows-програми.
Для копіювання вмісту комірок необхідно:
Фрагмент із буфера скопіюється в зазначене місце.
При копіюванні комірок, що містять формули з відносними посиланнями, координати комірок аргументів змінюються автоматично. Наприклад, при копіюванні формули =А1+А2 з комірки А3 в комірку В3 її вміст зміниться на =В1+В2. Ця формула як і раніше обчислює суму двох комірок ліворуч від комірки з формулою. При копіюванні комірок, що містять формули з абсолютними посиланнями, адреси комірок-аргументів не змінюються.
Для переміщення вмісту комірок необхідно використовувати команду Вырезать меню Правка або клацнути кнопку . При переміщенні комірок, що містять формули, координати комірок аргументів не змінюються. Наприклад, при переміщенні формули =А1+А2 з комірки А3 в комірку В3 формула залишиться =А1+А2.
Натиснення правою клавішею миші на будь-якому об'єкті таблиці викликає контекстне меню, що містить команди для обробки даного об'єкта.
Формат чисел
Кожне число в таблиці можна представити в різних форматах (з різною кількістю десяткових позицій, незначних нулів й ін.). Для зміни формату вмісту комірки необхідно:
Групування елементів таблиці
Microsoft Excel дозволяє групувати елементи у зведеній таблиці для того, щоб створити один елемент. Наприклад, для того, щоб згрупувати місяці у квартали для побудови діаграми або для друку.
Для групування елементів таблиці необхідно:
Так можна створити всі необхідні рівні структури. Наприклад, таблиця на рис.5.2 містить 3 рівні деталізації. Третій рівень приховує рядки 2-4 і 6-8, другий - приховує рядки 2-9. Таким чином, перший рівень деталізації містить тільки рядки 1 і 10. Для переходу між рівнями використовуються кнопки з відповідними цифрами у верхньому лівому куті таблиці.
Щоб зняти групування, варто виділити необхідні елементи, вибрати пункт Группа и структура меню Данные, потім пункт Разгруппировать.
Рис.5.2. Групування елементів таблиці.
5.3. Програма роботи
5.3.1. Вивчити призначення та принципи роботи з електронними таблицями в програмі Microsoft Excel.
5.3.2. Відформатувати та відредагувати вміст готових таблиць.
5.3.3. Створити нову таблицю. Провести обрахунки за заданими алгоритмами.
5.4. Обладнання та програмне забезпечення
5.4.1. Персональний комп’ютер з встановленою операційною системою MS Windows XP, пакетом прикладних програм MS Office XP, підготовлена книга Журнал.xls.
5.5. Порядок виконання роботи і опрацювання результатів
5.5.1. Запустіть програму „Microsoft Excel” (меню Пуск/Программы/Microsoft Excel). Відкрийте в MS Excel файл Журнал викладача1.xls (шлях до файлу - \\E-a\stud_doc\1-k\КТіООР\). Переглянете його, перемикаючи між аркушами.
5.5.2. Перейдіть на аркуш „Лекції”. У вільному лівому стовпці обчисліть суму балів по всіх лекціях для себе. Для цього можете записати власну формулу або використати внутрішню функцію Excel.
5.5.3. Обчисліть суму балів по всіх лекціях для кожного студента потоку. В такому випадку зручно поширити формулу обчислення суми ваших балів на комірки, що перебувають нижче і вище від вашої. Для цього клацніть в комірку з сумою ваших балів і підведіть курсор миші до правого нижнього кута комірки (прямокутник, що знаходиться в цьому куті, називається маркером заповнення). Тепер натисніть ліву кнопку миші і, утримуючи її, протягніть вказівник миші вниз до кінця списку; так само заповніть верхні комірки. В комірках стовпця з'являться формули, і автоматично просумуються бали для кожного студента.
Відформатуйте комірки з сумами балів студентів у числовий формат без відображення чисел після десяткової коми. У верхньому рядку цього стовпця зробіть відповідний підпис.
5.5.4. В наступному стовпці обчисліть середню кількість балів за одну лекцію для кожного студента вашої групи. Для цього можна використати функцію СРЗНАЧ. Підпишіть стовпець як „Середній бал”.
5.5.5. Змініть заливку стовпця суми балів на червоний колір. Для цього виділіть стовпець і в меню Формат/Ячейки на закладці Вид виберіть потрібний колір.
5.5.6. Обчисліть максимальну, мінімальну й середню кількість балів для кожної групи, розмістіть результати в комірках вільного лівого стовпця, введіть відповідні підписи.
Для обчислення максимального значення використовується функція МАКС, мінімального – функція МИН, середнього – СРЗНАЧ.
5.5.7. Збережіть книгу в своїй мережевій папці з ім'ям MyExcel.xls.
5.5.8. Перейдіть до аркуша вашої групи. Обчисліть сумарну кількість балів, отриманих кожним студентом вашої групи на лабораторних заняттях, і розмістіть ці значення у вільному лівому стовпці. У верхній стрічці стовпця введіть підпис „Сума балів”.
5.5.9. У наступному стовпці обчисліть процентне відношення суми ваших балів до максимальної суми балів за формулою, що еквівалентна такому словесному опису:
=ваша сума балів / максимальна сума балів
Максимальна сума балів рівна сумі балів за одне лабораторне заняття помноженій на кількість занять. Методом поширення заповніть комірки даного стовпця для всіх студентів вашої підгрупи. Для того щоб відношення відображалось у процентах, задайте відповідний формат у властивостях комірок. Підпишіть стовпець. Збережіть книгу.
5.5.10. Створіть новий аркуш (меню Вставка/Лист) і назвіть його „Залік”. На цьому аркуші потрібно визначити сумарну кількість балів та зробити висновок про залік кожного студента вашої групи. Для цього створіть таблицю наступного вигляду:
|
Прізвище, Ім'я |
Лабораторні |
Лекції |
МКР |
Сума |
Пропуски |
Залік |
5.5.11. В стовпець „Прізвище, Ім'я” скопіюйте список вашої групи.
5.5.12. У стовпець „Лабораторні” дані можна взяти із аркуша лабораторних робіт вашої групи, оскільки суму балів за лабораторні заняття для кожного студента ви порахували там раніше. Для цього у комірку стовпця поставте знак рівності і виберіть відповідну комірку із аркуша вашої групи. Стовпець „Лекції” заповніть аналогічним методом.
5.5.13. У стовпці „МКР” просумуйте бали за модульні контрольні роботи.
5.5.14. В стовпці „Сума” підрахуйте загальну суму набраних балів кожним студентом. Для цього скористайтеся функцією СУММ.
5.5.15. В стовпці „Пропуски” підрахуйте кількість пропущених лабораторних робіт кожним студентом. Для цього можна використати функцію СЧЁТЕСЛИ з категорії Статистические. Вираз для підрахунку виглядає наступним чином:
=СЧЁТЕСЛИ(Діапазон;"н"),
де Діапазон – це діапазон комірок кожного студента з аркуша лабораторних робіт, в яких функція шукатиме символи „н”.
5.5.5. В стовпці „Залік” в залежності від суми балів потрібно визначити чи зараховано студенту залік, чи ні. Скористайтеся наступним відношенням: якщо Сума<60 або Пропуски>2 – „не зараховано”, в іншому випадку – „зараховано”.
Для логічних дій такого типу зручно скористатися функціями ЕСЛИ та ИЛИ з категорії Логические. Вираз, за яким Excel автоматично визначить залік виглядає наступним чином:
=ЕСЛИ(ИЛИ([Сума]<60; [Пропуски]>2); "не зарах.";"зарах.")
5.5.17. Збережіть книгу у вашій мережевій папці, оскільки на насупному занятті ви продовжите з нею роботу.
5.5.18. Звіт з лабораторної роботи повинен містити:
Таблицю з аркуша Лекції, що містить лише стовпці рядків вашої групи Прізвище Ім'я, Сума балів, Середній бал і стовпець з мінімальним, максимальним і середнім значеннями балів.
Таблицю з аркуша лабораторних робіт вашої групи, що містить лише стовпці Прізвище Ім'я, Сума балів, Процентне відношення рядків вашої підгрупи.
Таблицю з аркуша Залік.
5.6. Контрольні запитання.
5.6.1. Який тип прикладних програм називається табличними процесорами?
5.6.2. В чому полягає основна особливість електронних таблиць? Де вони застосовуються?
5.6.3. Опишіть інтерфейс програми Microsoft Excel.
5.6.4. Яка структура робочої книги Microsoft Excel?
5.6.5. Опишіть роботу з формулами в Microsoft Excel.
5.6.6. Що таке посилання на комірку? Яким воно може бути?
5.6.7. Як скористатись функцією в Microsoft Excel?