I. робота з робочими книгами
Вступ
1.
Дані методичні вказівки призначені для студентів І курсу денної та заочної форми, які вивчають дисципліну «Інформатика», а саме тему «Основи офісного програмування». Базовою системою офісного програмування, яку обов’язково мають опанувати фахівці економічного профілю є процесор електронних таблицьMicrosoft Excel.
Методичні вказівки містять практичні, самостійні та індивідуальну роботу. Цикли практичних і самостійних робіт побудовані за принципом «від простого до складного» і містять матеріал різного ступеню складності.
Для перевірки знань наприкінці кожної самостійної роботи наведені контрольні запитання і додатково тестові завдання.
Завдання, що містяться у методичних вказівках були успішно апробовані у комп’ютерних аудиторіях ХІБС УБС НБУ
Дані методичні вказівки можуть також стати у нагоді при вивченні інших дисциплін: «Інформатика і комп’ютерна техніка», «Економічна інформатика», «Системи обробки економічної інформації» тощо.
Зміст
Практична робота №1
Введення, редагування та форматування даних у програмі Microsoft Excel 6
Практична робота №2
Копіювання і сортування даних, вивчення панелі залежностей, використання приміток, захист інформації в робочій книзі 9
Практична робота №3
Використання функцій, робота з Майстром функцій у програмі Microsoft Excel 11
Практична робота №4
Побудова експериментального графіка.
Обробка даних і застосування статистичних функцій 15
Практична робота №5
Формули, функції і діаграми в електронних таблицях Microsoft Excel 18
Практична робота №6
Робота з функціями і формулами в програмі Microsoft Excel 20
Практична робота №7
Робота з абсолютними посиланнями й іменами комірок у програмі Microsoft Excel 22
Практична робота №8
Робота з електронними списками в програмі Microsoft Excel 25
Практична робота №9
Особливості роботи з діаграмами в програмі Excel 27
Практична робота №10
Застосування фінансових функцій 31
Самостійна робота №1
Знайомство з програмою MS Excel і її основними компонентами 35
Самостійна робота №2
Автозаповнення і прогресія 39
Самостійна робота №3
Математичні функції 41
Самостійна робота №4
Логічні функції 43
Самостійна робота №5
Статистичні функції 45
Самостійна робота №6
Текстові функції 47
Самостійна робота №7
Функції дати і часу 49
Самостійна робота №8
Діаграми 51
Самостійна робота №9
Побудова графіків функцій 55
Самостійна робота №10
Робота зі списками і фільтрами 60
Тестові завдання 62
Індивідуальна робота 66
Практична робота № 1
Введення, редагування і форматування даних у програмі Microsoft Excel
1. Відкрити електронну таблицю Excel. Увести: в комірку D5 - "Рядок тексту»; в комірку D6 - 123456; в комірку D7 - 20/03/2012. Зберегти документ у папці, вказаній викладачем.
2. Використовуючи клавішу F2, в комірку D5 внести зміни - "Рядок якогось тексту". За допомогою рядка формул, в комірки D6 і D7 внести наступні зміни: 987654 і 15/05/2012, відповідно.
3. Виділити дані діапазону комірок D5:D7 і скопіювати їх за допомогою панелі інструментів у комірки, починаючи з I4. Виділити комірки I4:I6 і відформатувати 16 розміром шрифту, напівжирним, червоним. Скопіювати за допомогою правої клавіші миші відформатований діапазон комірок у комірку F10. Діапазон комірок I4:I6 виділити і видалити дані за допомогою команди Очистить содержимое. Дані в діапазоні комірок F10:F12 видалити за допомогою команди Удалить. Визначити розходження між дією команд Очистить содержимое і Удалить. За допомогою мишки перемістити дані, що знаходилися в інтервалі комірок D5:D7 в комірку G8.
4. Перейти на Лист 2 і в комірку B2 увести текст: "Прийоми автоматизації введення даних". Відформатувати текст 14 розміром шрифту, жирним, курсивом, червоним на жовтому фоні.
4.1. У комірку B4 увести число 451 і за допомогою маркера автозаповнювання скопіювати це число в діапазон комірок до B15.
4.2. У комірку D4 увести текст "весна" і за допомогою маркера автозаповнювання скопіювати це слово в діапазон комірок до I4.
4.3. У комірку С6 увести 1, а в комірку С7 - 2. Виділити комірки С6:С7 і, потягнувши мишкою за маркер автозаповнювання, одержати числовий ряд до комірки С16.
4.4. У комірку D7 увести день тижня - Понедельник і за допомогою маркера автозаповнювання, одержати текстовий ряд днів тижня в діапазоні комірок до D13.
4.5. У комірку Е6 увести Январь і за допомогою маркера автозаповнювання ввести текстовий ряд назв місяців до комірки К6.
4.6. Увести в комірки Е7 і Е8 числа 45 і 91 і одержати числовий ряд до комірки E13. Увести в комірки F7 і F8 числа 48 і 90 і одержати числовий ряд до комірки F13. Виділити інтервал комірок Е7:F13 і за допомогою автозаповнювання заповнити числами інтервал комірок до К13. Простежте тенденцію, що відбувається в таблиці.
5. За допомогою головного меню Правка (команда Заполнить/Прогрессия):
5.1. Починаючи з комірки B15 (ввести в комірку 3) увести арифметичну прогресію по строках, с шагом 4,5 ,граничнезначення40;
5.2. Починаючи з комірки В17 (ввести в комірку число - 1) увести геометричну прогресію з кроком 1,5 і граничним значенням 100;
5.3. Починаючи з комірки В19 (ввести в комірку дату 01/01/2012) увести ряд дат робочих днів, із кроком 2, граничною датою 20/01/2012.
6. Перейти на Лист 3. Перейменувати аркуш під ім'я "Продаж товарів". У комірку А1 увести заголовок: Фірма "Спецвузавтоматика". Відформатувати 14 шрифтом, жирним, червоним. Виділити інтервал комірок А1:D1 і відформатувати заголовок по центру виділеного інтервалу.
7. Починаючи з комірки А3 увести таблицю:
| Товар | Кількість | Ціна | Разом |
| Комп'ютери | |||
| Дискети | |||
| Принтери | |||
| Усього |
8. Виконати в таблиці розрахунки і вставити праворуч від неї кругову діаграму відсотків від продажу кожного товару. Відформатувати числа в стовпцях Разом і Ціна наступним форматом - # ##0.00 грн.
Для створення нового формату необхідно виконати команду Формат/Ячейки. На вкладці Число, у поле Числовые форматы вибрати схожий формат, наприклад: # ##0.00. Коли він установиться в поле Тип, наприкінці ввести з клавіатури: грн. Попередньо стовпці Разом і Ціна повинні бути виділені.
9. Використовуючи функцію автозаповнювання, створити й відформатувати відповідно до Додатка 1 таблицю продажу книг.
10. Продемонструвати роботу викладачу і зберегти у власній папці.
Додаток 1
| Продано книг | ||||
| У тому числі | ||||
| Назва місяців | Усього | Наукова | Технічна | Художня |
| Січень | ||||
| Лютий | ||||
| Березень | ||||
| Квітень | ||||
| Травень | ||||
| Червень | ||||
| Липень | ||||
| Серпень | ||||
| Вересень | ||||
| Жовтень | ||||
| Листопад | ||||
| Грудень | ||||
| Разом |
Практична робота №2
Копіювання і сортування даних, вивчення панелі залежностей, використання приміток, захист інформації в робочій книзі
1. Запустіть програму Excel.
1.1. Надати 1 робочому аркушу нове ім’я: "сортування".
1.2. Увести в комірку А1 слово: понедельник, в комірку А2: вторник.
1.3. Виділити діапазон комірок А1:А2.
1.4. За допомогою маркера заповнення ввести дані у наступні п’ять комірок.
1.5. Відсортуйте список в алфавітному і зворотному порядку за допомогою відповідних кнопок на панелі інструментів "Стандартная".
1.6. Таким же чином створіть у колонці В список, який відображає місяці.
1.7. Відразу ж спробуйте створити список місяців у першому рядку, починаючи від комірки В1.
1.8. Відсортуйте список у колонці В.
1.9. Спробуйте відсортувати список у першому рядку. Чи відбулося сортування в рядку?
2. Перейти на новий робочий аркуш.
2.1. Увести в комірки А1 та Е1 довільні числа.
2.2. Ввести в комірку С5 довільну формулу з посиланням на комірки А1 та Е1. Наприклад: = А1+Е1.
2.3. Ввести в комірку С10 довільну формулу з посиланням на комірку С5. Наприклад: = 3*С5.
2.4. Стати на комірку С5.
2.5. Виконати команду Сервис/Зависимости. Послідовно виконати дві команди: Влияющие ячейки і Зависимые ячейки.
2.6. Викликати на екран і роздивитися, які кнопки має панель залежностей.
2.7. Ввімкнути кнопку Предварительный просмотр і проглянути, чи будуть відображатися на друкованому аркуші стрілки.
3. Створіть для залежних та впливаючих комірок примітки (меню Вставка/Примечания).
3.1. У поля для введення тексту введіть: залежна комірка, впливаюча комірка. Зверніть увагу на те, що у верхньому правому куті з’являється маленький червоний трикутник (індикатор).
3.2. На вкладці Вид меню Сервис/Параметри спробуйте встановити інші режими відображення приміток.
4. Стати в будь-яку комірку робочого аркуша і виконати команду Сервис/Защита.
4.1. З’явиться діалогове вікно, в якому можна внести пароль (не обов’язково), ОК.
4.2. Спробуйте ввести інформацію в будь-яку комірку.
4.3. Пам’ятайте: якщо для захисту комірки ви використовуєте пароль його треба обов’язково запам’ятати!
5. Створіть новий аркуш Прейскурант.
5.1. У комірку А1 уведіть: "Прейскурант".
5.2. У комірку А2 уведіть: "Курс перерахунку".
5.3. У комірку В2 уведіть текст: "1 у.од.= ".
5.4. У комірку С2 уведіть поточний курс перерахунку.
5.5. У комірку А3 уведіть: "Найменування товару"
5.6. У комірку В3 уведіть: "Ціна (у.од.)".
5.7. У комірку С3 уведіть текст: "Ціна (грн.)".
5.8. У наступні комірки стовпчика А введіть назви товарів, включених у прейскурант (довільні).
5.9. У відповідні комірки стовпчика В введіть ціни товарів в у.од.
5.10. У комірку С4 уведіть формулу: =В4*$C$2 , що використовується для перерахунку ціни з умовних одиниць у гривні.
5.11. Методом автозаповнювання скопіюйте формули у всі комірки стовпчика С, яким відповідають заповнені комірки стовпчиків А і В.
5.12. Змініть курс перерахунку в комірці С2. Зверніть увагу, що всі ціни в грн. при цьому обновляються автоматично.
5.13. Виділіть діапазон А1:С1 і дайте команду Формат/Ячейки. На вкладці Выравнивание задайте вирівнювання по горизонталі: по центру і встановіть прапорець об'єднання комірок.
5.14. На вкладці Шрифт задайте розмір шрифту 14 пт, у списку Начертание виберіть варіант: полужирный.
5.15. Клацніть правою кнопкою миші на комірці В2 і виберіть у контекстному меню команду Формат ячеек. Задайте вирівнювання по горизонталі: по правому краю.
5.16. Те ж саме проробіть з коміркою С2 по лівому краю.
5.17. Виділіть діапазон В2:С2. Клацніть на кнопці, що розкривається, поруч із кнопкою Границы панелі інструментів Форматирование і задайте для цих комірок широку зовнішню рамку.
5.18. Двічі клацніть на межі між заголовками стовпчиків А і В; В і С; С і D. Зверніть увагу, як при цьому змінюється ширина стовпчиків А, В і С.
5.19. Подивіться, чи влаштовує вас отриманий формат таблиці.
5.20. Збережіть документ і продемонструйте викладачу.
Практична робота №3
Використання функцій, робота з Майстром функцій у програмі Microsoft Excel
1. Відкрити електронну таблицю Excel. Увести дві послідовності:
1.2 Арифметичну в діапазон С5:С15 (початкове значення - 2, крок - 1). Запишіть у звіті початкове і граничне значення прогресії;
1.3 Геометричну в діапазон Е5:Е15 (початкове значення - 1, крок -5). Запишіть у звіті початкове і граничне значення прогресії;
Щоб не вводити граничне значення прогресії, виділіть спочатку діапазон, у якому вона повинна підраховуватися.
2. За допомогою функції СУММ одержати в комірці D2 результати підсумовування діапазонів комірок: С5:С9; E11:E14; C12:C15; E6:E10. Запишіть результат підсумовування, який ви отримали.
3. У комірці В20 за допомогою статистичної функції РАНГ визначити ранг числа, введеного в комірку С10 з діапазону комірок С5:С15. Введіть у комірку С10 число 50. Простежте зміни в комірці В20. Введіть у комірку С15 число 70. Простежте зміни в комірці В20. Запишіть результат змін у комірці В20 в звіт.
4. Використовуючи статистичну функцію СЧЕТЕСЛИ,розрахувати кількість громадян визначеного віку. Як діапазон беруться числові значення стовпця «Рік народження», як умова – одна з комірок стовпця «Рік, за яким виконується підрахунок». Роздрукуйте готову таблицю, використовуючи параметри форматування, наведені в Таблиці 1.
Таблиця 1
| Приклад статистичних функцій | (СЧЕТЕСЛИ) | |||
| № | Прізвища мешканців | Рік народження | Рік, за яким виконується підрахунок | Кількість мешканців |
| Алексєєв | ||||
| Бельченко | ||||
| Вербицький | ||||
| Вернигора | ||||
| Горощенко | ||||
| Гупало | ||||
| Кащенко | ||||
| Курінний | ||||
| Лазарєва | ||||
| Лисогоров | ||||
| Маркова | ||||
| Мітюков | ||||
| Тригуб | ||||
| Яровенко |
5. Ввести наведену в Додатку 2 таблицю. Використовуючи текстову функцію ЛЕВСИМВ зі списку, що містить Прізвище, Ім'я і По батькові створити список Прізвище + ініціали по формулі:
=ЛЕВСИМВ(Х;12)&" "&ЛЕВСИМВ(Y)&"."&ЛЕВСИМВ(Z)&"."
X – посилання на комірку із прізвищем, 12 – число символів, що приділяються для прізвища, можна задати будь-яке інше число, потім обов'язково ставиться пробіл, щоб прізвище і ініціали не зливалися;
Y – посилання на комірку з ім'ям і оскільки число символів не вказуються, то за замовчуванням, функція використовує один символ, після якого потрібно поставити крапку;
Z – посилання на комірку з ім’ям по батькові, після якого ставиться крапка.
Знаючи дати народження, обчислити вік за допомогою функції дати СЕГОДНЯпо формулі:
= СЕГОДНЯ – Х(комірка, у якій знаходиться дата)
Представити результати розрахунку у форматі ГГ (тільки роки). Роздрукуйте готову таблицю, використовуючи параметри форматування, наведені в Таблиці 2.
Таблиця 2
| № | Прізвище | Ім'я | По батькові | Дата народження | Прізвище + ініціали | Вік, роки |
| 1. | Полтавський | Володимир | Володимирович | 12.04.1971 | ||
| 2. | Радченко | Олександр | Віталійович | 06.11.1965 | ||
| 3. | Лайков | Сергій | Миколайович | 23.08.1958 | ||
| 4. | Король | Леонід | Олександрович | 18.03.1967 | ||
| 5. | Головченко | Богдан | Іванович | 10.05.1969 | ||
| 6. | Василько | В'ячеслав | Васильович | 08.06.1963 | ||
| 7. | Михалев | Сергій | Іванович | 21.07.1970 | ||
| 8. | Лісників | Микола | Петрович | 17.09.1949 | ||
| 9. | Дудка | Ірина | Анатоліївна | 16.02.1973 |
6. Перейти на аркуш 2. Ввести текст: «Приклади логічних функцій». У комірку В2 увести число 59; в комірку В3 число –45; в комірку С2 – за допомогою майстра функцій, формулу: =ЕСЛИ(В2; "позитивний"; "негативний"). За допомогою маркера автозаповнювання скопіювати формулу в діапазон С2:С4. Відредагувати формулу так, щоб для позитивних чисел вона видавала слово «позитивний», для негативних - «негативний», для нуля – «нуль», вона повинна виглядати таким чином: =ЕСЛИ(В2>0;"позитивний";ЕСЛИ (В2<0; "негативний";"нуль"))
7. Використовуючи логічну функцію И, записати в комірку D2 формулу, що для діапазону комірок В2:В4 видавала б повідомлення: «усі негативні», коли всі числа діапазону негативні, і «не всі негативні», коли, хоча б одне з чисел діапазону позитивне: =ЕСЛИ (И(В2<0;B3<0;B4<0); "усі негативні";"не всі негативні"). Перевірити роботу формули для різних комбінацій чисел.
8. Використовуючи функцію ИЛИ записати в комірку Е2 формулу, що для діапазону комірок В2:В4 видавала б повідомлення: «усі позитивні», коли хоча б одне з чисел діапазону позитивне, і «не всі позитивні», коли всі числа діапазону негативні, (=ЕСЛИ(ИЛИ(В2>0;B3>0;B4>0); "усі негативні";"не всі негативні"). Перевірити роботу формули для різних комбінацій чисел. Зрозуміти розходження між роботою функцій И та ИЛИ.Запишіть у звіті висновки, що ви зробили щодо розходження в роботі логічних функцій.
9. Перейти на наступний аркуш. Починаючи з комірки В2 увести наведену в Додатку 3 таблицю. Використовуючи математичну функцію СУММЕСЛИ,розрахувати суму продажу по зазначених регіонах.
Діапазон– комірки з даними «Регіон» і «Сума продажу»;
Умова – «Північ», «Захід», «Південь» чи «Схід»;
Діапазон підсумовування – комірки з даними «Сума продажу»;
Введіть у формулу абсолютні посилання для діапазону і діапазону підсумовування, тому що вони незмінні при будь-яких умовах. Підрахуйте підсумкові суми, щоб переконатися в тому, що Ви правильно використали функцію СУММЕСЛИ. Роздрукуйте готову таблицю, використовуючи параметри форматування, наведені в Таблиці 3.
Таблиця 3
| Приклад математичної функції СУММЕСЛИ | ||||
| Регіон | Сума продажу | Сума продажу по регіонах | ||
| 1. | Північ | Північ | ||
| 2. | Захід | Захід | ||
| 3. | Південь | Південь | ||
| 4. | Схід | Схід | ||
| 5. | Північ | Разом | ||
| 6. | Захід | |||
| 7. | Південь | |||
| 8. | Схід | |||
| 9. | Північ | |||
| 10. | Захід | |||
| 11. | Південь | |||
| 12. | Схід | |||
| 13. | Північ | |||
| 14. | Захід | |||
| 15. | Південь | |||
| 16. | Схід | |||
| Разом |
10. Перейти на наступний аркуш. Починаючи з комірки В2 увести наведену нижче таблицю 4. Використовуючи статистичну функцію ТЕНДЕНЦИЯ,розрахувати прогноз випуску продукції від зміни виробничих факторів. При використанні цієї функції необхідно ввести два параметри:
Известные_значения_у – увести діапазон чисел із січня по грудень (січень) по одному зі стовпців: «Випуск продукції», «Трудові ресурси» чи «Виробничі фонди»;
Новые_значения_х– увести 13 чи 14, у залежності від того, тенденцію на який місяць ми хочемо довідатися.
Використовуючи статистичну функцію СРЗНАЧ,підрахуйте середнє значення випуску продукції за рік.
Побудувати графіки залежності зміни Випуску продукції, Трудових ресурсів, Виробничих фондів по місяцях.
Роздрукуйте готову таблицю на одному аркуші з графіками. Використовуйте для таблиці параметри форматування, наведені в Таблиці 4.
Таблиця 4
| Залежність випуску продукції від зміни виробничих факторів | ||||
| Місяць | Номер спостереження | Випуск продукції | Трудові ресурси | Виробничі фонди |
| Січень | ||||
| Лютий | ||||
| Березень | 4,5 | |||
| Квітень | 15,5 | 4,4 | ||
| Травень | ||||
| Червень | 5,5 | 10,5 | ||
| Липень | 16,5 | 10,5 | ||
| Серпень | ||||
| Вересень | 17,5 | 11,5 | ||
| Жовтень | 11,5 | |||
| Листопад | 8,5 | |||
| Грудень | 18,5 | 12,5 | ||
| Січень | ||||
| Лютий | ||||
| Середнє значення випуску продукції за рік: |
Практична робота №4
Побудова експериментального графіка.
Обробка даних і застосування статистичних функцій
1. Пepейменуйте аркуш : “Обробка експерименту”.
1.1. У колонку А, пoчинаючи з комірки А1, уведіть довільний набір значень незалежної змінної.
1.2. У стовпчик В, починаючи з комірки В1 , уведіть довільний набір значень функції.
1.3. Виділіть всі заповнені комірки колонок А і В.
1.4. Клацніть на значку Мастер диaграмм(стандартна панель інструментів).
1.5. У списку тип виберіть: Точечная (длявідображення гpaфiка заданого парами значень). У палітрі Вид виберіть середній гpaфiк (маркери, з’єднані гладкими кривими). Далее.
1.6. Діапазон комірок був виділений заздалегідь, тому майстер дiaгpам автоматично визначає розташування рядів даних. Перeконайтеся, що дані нa дiагpамi були вибрані правильно.
1.7. На вкладці Ряду полі Имязазначте: Результати вимірів. Далее.
1.8. Виберіть вкладку Заголовки. Переконайтеся, що задана назва ряду даних автоматично використана як заголовок дiaгpами. Замініть йогo, ввівши в поле Haзвaние диaграммызаголовок: «Експериментальні крапки». Далее.
1.9. Встановіть перемикач: Отдельном. За бажанням задайте довільне ім'я робочого аркуша, що додаєтъся. Готово.
1.10. Переконайтеся, що діaгpамa побудована і впроваджена в новий робочий аркуш. Роздивіться її і клацніть на побудованій кривій, щоб виділити ряд даних.
1.11. Клацніть кнопку Формат pядов данных. Відкрийте вкладку Вид.
1.12. На панелі Линиявідкрийте вкладку Цветі виберіть: червоний. Із списку Tип линиивиберіть : пунктир.
1.13. На панелі Маркер виберіть у списку Tип маркератрикутний маркер. У палітрах Цветі Фонвиберіть зелений колір.
1.14. Зніміть виділення з ряду даних і подивіться як змінився вигляд графіка.
У цьому завданні можна вивчити різноманітні типи діаграм або відформатувати графік різноманітними способами: задати інший колір, маркер, погрішності, переміщувати області побудови і легенду, формат осі тощо.
2. Надайте робочому aркушу ім'я: «Дані»
2.1. Зробіть поточною комірку А1 і введіть у неї загoловок: «Значення».
2.2. Уведіть в діапазон А2:А10 довільні числа.
2.3. Уведіть в комірку В 1 : «Подвійне значення».
2.4. Уведіть в комірку С1 : «Квадрат значення».
2.5. Уведіть в комірку D 1 : «Квадрат наступного числа».
2.6. Уведіть в коміркуВ2 формулу: =2*А2.
2.7. Уведіть в комірку С2 формулу: = А2^А2.
2.8. Уведіть в комірку D2 формулу: =В2+С2+1 або = (А2+1)*(А2+1).
2.9. Виділіть протягуванням миші комірки В2, С2, D2.
2.10. Наведіть покажчик миші на маркер заповнення в правому нижньому кутку рамки, що охоплює виділений діапазон. Натисніть ліву клавішу миші і перетягніть цей маркер, щоб рамка охопила стільки рядків у стовпчиках В, С і D, скільки є чисел у стовпчику А.
2.11. Переконайтеся, що формули модифікуються таким чином, щоб працювати зі значенням комірки в стовпчику А поточного рядку.
2.12. 3мініть одне зі значень стовпчика А і переконайтеся, що відповідні значення в стовпчиках В, С і D у цьомуж рядку були автоматично перераховані.
2.13. Введіть у комірку Е1 : «Масштабний множник».
2.14. Введіть у комірку Е2 число 5.
2.15. Введіть у комірку F1 : «Масштабування»
2.16. Введіть у комірку F2 формулу: =А2*Е2.
2.17. Використовуйте метод автозаповнювання, щоб скопіювати цю формулу в комірки стовпчика F , що відповідаютъ заповненим коміркам cтoвпчика А.
2.18. Переконайтеся, що результат масштабування виявився невірним. Це пов'язано з тим, що адреса Е2 у формулі задана відносним посиланням.
2.19. Клацніть на комірці F2, а потіму рядку формул. Встановіть текстовий курсор на посилання Е2 і натисніть клавішу F4. Формула перетвориться і буде мати вигляд: =А2*$Е$2. Enter.
2.20. Повторіть заповнення стовпчика F формулою з F2.
2.21. Переконайтеся, що завдяки використанню абсолютної адресації значення комірок стовпчика F тепер обчислюються правильно.
2.22. Зробітъ поточною першу вільну комірку у стовпчику А.
2.23. Клацнітъ на кнопці Автосуммапанелі інструментів.
2.24. Переконайтеся, що пpoгpaма автоматично підставила у формулу функцію SUMM і правильно вибрала діапазон комірок для підсумовування. Enter.
2.25. Зробіть поточною наступну вільну комірку у стовпчику А.
2.26. Клацніть на кнопці Вставка функциина стандартній панелі інструментів.
2.27. У списку категоріївиберіть: статистичні. У списку Функціявиберіть функцію СРЗHАЧ. ОК.
2.28. Якщо палітра формул закриває потрібні комірки, перемістітъ її методом перетягування. Зверніть увагу, що автоматично вибраний діапазон включає всі комірки з числовим змістом, включаючи і ту, що міститъ cуму. Виділіть пpавильний діапазон методом протягування. Епtеr.
2.29. Використовуючи порядок дій, описаний у пп. 2.25-2.28, обчисліть мінімальне число в заданому наборі (функція МИН), максимальне число (функція МАКС), кількістъ елементів у наборі (функція СЧЕТ).
2.30. Віділіть діапазон комірок А1:F10 і виконайте команду Копировать. Перейдіть на 3 аркуш, встановіть табличний курсор у комірку А1 і виконайте в меню Правка команду Специальная вставка(менюПравка), встановіть прапорець Транспонировать.
Для цього завдання можна використовувати різноманітні параметри форматування, друкувати в двох режимах: у режимі відображення формул і в режимі відображення результатів.
Практична робота № 5
Формули, функції і діаграми в електронних таблицях Microsoft Excel
1. Відкрити електронну таблицю Excel. Перейменувати аркуш 1 у «Особистий бюджет». Починаючи з комірки В2 оформити наведену в додатку 1 таблицю, вставити необхідні формули і розрахувати доходи і витрати. Побудувати кругові діаграми відносного внеску статей доходів і витрат (без використання підсумкових даних). Використання легенди в діаграмах дозволить наочно представити дані.
Додаток 1
2. Перейти на аркуш 2 і перейменувати його в «Найпростіші розрахунки». Ввести в комірку А1 заголовок «Найпростіші розрахунки за допомогою операторів Excel»:
2.1. Уведіть у комірку А10 число 216, а в комірку В10 число 42;
2.2. Отримайте в комірці С10 результат зведення в ступінь (1/3) вмісту комірки А10 (=А10^(1/3);
2.3. Отримайте в комірці С11 результат множення вмісту комірок А10 і В10 (=А10*В10);
2.4. Отримайте в комірці С12 результат ділення вмісту комірок А10 на В10 (=А10/В10);
2.5. Отримайте в комірці С13 результат додавання вмісту комірок А10 і В10 (=А10+В10);
2.6. Отримайте в комірці С14 результат вирахування вмісту комірок А10 і В10 (=А10-В10);
2.7. Отримайте в комірці С15 результат об'єднання вмісту комірок А10 і В10 (=А10&В10);
2.8. Отримайте в комірці С16 результат порівняння вмісту комірок А10 і В10 (=А10=В10);
2.9. Отримайте в комірці С 17 результат порівняння вмісту комірок А10 і В10 (=А10<В10);
2.10. Отримайте в комірці С18 результат порівняння вмісту комірок А10 і В10 (=А10>В10).
3. Розв’язати квадратне рівняння.
3.1. Ввести в комірку А20 наступний текст: «Рішення квадратного рівняння», в комірку А22 рівняння Ах2 +Вх+С=0, а починаючи з комірки В24 оформити наступну таблицю:
| а | в | с | х1 | х2 | |
| -1 | -5 | ||||
| -44 | |||||
3.2. Знайти корені квадратного рівняння для зазначених коефіцієнтів. При введенні формули варто використовувати абсолютні посилання на стовпці, а не значення коефіцієнтів. Це дозволить швидко модифікувати її для інших комірок. Для обчислення кореня використовуйте вбудовану математичну функцію КОРЕНЬ[1] .
3.3. За допомогою функції ОКРУГЛ округлити значення коренів до першого знака після коми.
Практична робота № 6
Робота з функціями і формулами в програмі Microsoft Excel
1. Приклади математичних функцій.
1.1 Ввести наведені нижче числа і, за допомогою функції ЦЕЛОЕ, одержати цілі значення цих чисел у сусідньому стовпці.
| 12,365 | |
| 123,56 | |
| 12345,68 | |
| -45,89 | |
| 0,123 |
1.2 Ввести наведені нижче числа і за допомогою функції ОКРУГЛ, одержати округлені числа в сусідньому стовпці для наступних параметрів округлення: 2; 1; 0; -1; -2; -3, відповідно для кожного числа.
| 123,456 | |
| 123,456 | |
| 123,456 | |
| 123,456 | |
| 123,456 |
2. Приклади текстових функцій.
2.1 За допомогою функції СИМВОЛ увести в комірку А15 символ ã (код символу 169).
2.2 За допомогою функції КОДСИМВ увести в комірку В15 код символу ã. =КОДСИМВ(А15)
2.3 Ввести в комірку С15 текст «Володимир Мономах». За допомогою функції ЛЕВСИМВ в комірці D15 одержати текст «Володимир».
2.4 Ввести в комірку А17 текст «Концепція». За допомогою функції ДЛСТР в комірці В17 визначити кількість символів у цьому тексті.
2.5 Ввести в комірку А19 текст « Визначення кількості символів без обліку пробілів». У комірку А20 за допомогою функцій ДЛСТР і СЖПРОБЕЛЫ одержати кількість символів, що містяться в цій фразі без урахування пробілів. =ДЛСТР(СЖПРОБЕЛЫ(А19)). Зробіть висновки щодо використання функції СЖПРОБЕЛЫ.
2.6 Ввести в комірку А21 текст «Правове забезпечення системи». За допомогою функції ПСТР одержати в комірку А22 текст «забезпечення». =ПСТР(А21;10;12)
2.7 Ввести в комірку А23 текст «Тарас Петриненко». За допомогою функції ЗАМЕНИТЬ одержати в комірку А24 текст «Тарас Шевченко». =ЗАМЕНИТЬ(А23;7;10; "Шевченко")
3 Приклади функцій дати і часу.
3.1 Ввести в комірку А26 - 2012, В26 - 04, С26 - 15. За допомогою функції ДАТА одержати в комірці А27 дату 15.04.00
3.2 Використовуючи функцію ДЕНЬ одержати в комірці В27 тільки число днів з комірки А27. Аналогічним чином одержати в комірках С27 і Д27 рік і місяць, використовуючи функції ГОД і МЕСЯЦЬ, відповідно.
3.3 За допомогою функції ДЕНЬНЕД визначити в комірці Е27 день тижня для числа, введеного в комірці А27. В комірку Е28 напишіть назву цього дня тижня.
3.4 Ввести в комірку А29 - 8, В29 - 15, С29 - 0. За допомогою функції ВРЕМЯ одержати в комірці D29 час 8:15:00.Виберіть відповідний формат відображення часу.
3.5 Використовуючи функцію ЧАС, одержати в комірці Е29 - 8, посилаючись на комірку D29.
Роздрукуйте виконані завдання, помістивши їх на одній сторінці. На другому аркуші роздрукуйте це ж завдання, встановивши режим відображення формул. В колонтитулі вкажіть своє прізвище.
Практична робота № 7
Робота з абсолютними посиланнями й іменами комірок у програмі Microsoft Excel
1. Відкрити електронну таблицю Excel. Увести наведену нижче табличку і, використовуючи абсолютне посилання, розрахувати Доход фірми в грн.
| Січень | Лютий | Березень | Квітень | |
| Доход, $ | ||||
| Доход, грн |
| Біржовий курс | 5.55 |
2. Ввести наведену в Додатку 1 таблицю. Відформатувати дані таблиці відповідно до зразка і виконати обрамлення. Коміркам і інтервалам комірок, що використовуються у формулах при розрахунках таблиці, надати відповідні імена. Наприклад: інтервалу комірок, що відповідає обсягам продажу за січень, надати ім'я «січень». Аналогічно надати імена даним продажу за лютий, березень і квітень. При розрахунку графи Загальні доходи надайте імена відповідним коміркам так, щоб формула мала вид: =Підсумки_продаж-загальні_витрати. Формула вводиться в першу комірку діапазону Загальні доходи, а потім копіюється в інші маркером заповнення.
3. Об'єднати текст із різних комірок. Увести в комірку В40 текст: «Фабрика «Червона сукальниця», а в комірку В41 - «Завод «Молодий волочильник». За допомогою оператора приєднати (&), одержати в комірці В42 фразу: = «Нашими постійними клієнтами є найбільші підприємства міста» В40 і В41.
4. Перейти на Аркуш 2. Перейменувати його в Доход акціонерів. Створити проект розрахунку доходів акціонерів. Увести наведену в Додатку 2 таблицю. Відформатувати дані таблиці відповідно до зразка і виконати обрамлення. Коміркам і інтервалам комірок, що використовуються у формулах при розрахунках таблиці, надати відповідні імена. У цьому випадку, формули, для розрахунку будуть мати вигляд:
Випущено акцій(=СУММ(Кількість_акцій));
Доход на одну акцію (=Доход_фірми_за_рік/Випущено_акцій);
Доход на одну акцію з урахуванням податку (=Доход_на_одну_акцію*Коефіцієнт_податку);
Коефіцієнт податку (=1–податок);
Доход для кожного акціонера (=Доход_на_одну_акцію_з_урахуванням_податку*Миколаїв);
(=Доход_на_одну_акцію_з_урахуванням_податку*Іванов);
(=Доход_на_одну_акцію_з_урахуванням_податку* Сидоров).
Вімкніть режим відображення формул і роздрукуйте готову таблицю в максимальному масштабі.
Додаток 1
| Продаж товарів | |||||||
| Січень | Лютий | Березень | Квітень | ||||
| Продукт 1 | |||||||
| Продукт 2 | |||||||
| Продукт 3 | |||||||
| Продукт 4 | |||||||
| Продукт 5 | |||||||
| Підсумки продажів | |||||||
| Накладні витрати | |||||||
| Орендна плата | |||||||
| Технічне обслуговування | |||||||
| Податки | |||||||
| Загальні витрати | |||||||
| Загальні доходи | |||||||
Додаток 2
| Доход акціонерів фірми «Рога і копита» | |||||
| Доход фірми за минулий рік | 67 800 $ | Акціонери | Кількість акцій | Доход | |
| Доход фірми за рік | 50 000 $ | Миколаїв | |||
| Випущено акцій | Іванов | ||||
| Сидоров | |||||
| Доход на одну акцію | |||||
| Доход на одну акцію з урахуванням податку | |||||
| Податок | 20% | ||||
| Коефіцієнт податку | |||||
5. Використовуючи текстові категорії, уведіть нижче таблиці наступні коментарі, які б автоматично змінювалися, зі зміною даних таблиці.
| Загальний доход нашої фірми цього року склав 50 000 грн. з розрахунку на кінець року. | |||||
| Це на 35,6 відсотків менше ніж торік! |
5.1. Під таблицею «Доходи акціонерів...» уведіть дані, наведені в Додатку 3. Надайте імена в стовпцях ліворуч.
5.2. Під таблицями увести формулу, що, використовуючи імена комірок ч_1 і ч_2, відтворила б першу фразу:
=ч_1&Доход_фірми_за_рік&"$"&ч_2
5.3. Перейти в комірку нижче, увести формулу, що, використовуючи імена комірок ч_3, ч_4, ч_5 та арг_1, арг_2, відтворила б другу фразу:
 |
=ч_3&арг_1&ч_4&арг_2&ч_5
Додаток 3
5.4 . Якщо доход фірми збігся з доходом за минулий рік, створити формулу, яка б заміняла другу фразу на: «Доход фірми не змінився в порівнянні з минулим роком». Вона буде мати наступний вигляд:
=ЕСЛИ(Доход_фірми_за_минулий_рік=Доход_фірми_за_рік; «Доход фірми не змінився в порівнянні з минулим роком»;арг_3)
5.5 . Захистити проект таким чином, щоб редагувалися тільки комірки, у яких немає формул і тексту. Для цього необхідно попередньо виділивши аркуш, викликати діалогове вікно Форматячеек. На вкладці Защита зняти параметр Защищаемаяячейка. Потім виділити всі комірки з текстом і формулами і відновити для них цей параметр. Після цього виконати в меню Сервис команду Защита/Защитить лист. Пароль уводити не обов'язково.
Практична робота № 8
Робота з електронними списками в програмі Microsoft Excel
Відкрити електронну таблицю Excel. Увести нижче наведену таблицю.
1.1 Скопіювати таблицю на аркуш 2 і відсортувати по графі «Прізвище».
1.2 Скопіювати таблицю на аркуш 3 і відсортувати по двох графах: «Прізвище і «Номер цеху» (по зростанню).
1.3 Відібрати всіх робітників, що працюють у четвертому цеху (за допомогою автофільтра).
1.4 Відібрати всіх робітників, прізвища яких починаються з літери Б (умова: =Б*).
| Список робітників | ||
| Код | Прізвище | Номер цеху |
| Бєлкін В.У | ||
| Алієв Л.П. | ||
| Омельченко Д.А. | ||
| Баранов В.Д. | ||
| Вольнов К.Н. | ||
| Якіменко О.А. | ||
| Симоненко В.Р. | ||
| Рижков Л.И. | ||
| Петров Н.П. | ||
| Кліменко Е.В. | ||
| Кучеря А.П. | ||
| Ващенко П.И. | ||
| Березін Л.И. | ||
| Чернов Т.Г. |
2. Ввести нижче наведену таблицю на аркуш 2.
Відділ кадрів
| Прізвище І.П. | Телефон | Дата народження | Вік | Оклад |
| Кошкін А.Н. | 67-89-01 | 15/03/75 | ||
| Бурів А.В. | 51-78-89 | 10/08/63 | ||
| Жуків Б.І | 54-63-47 | 25/02/79 | ||
| Сидоров О.М. | 45-87-89 | 06/01/50 | ||
| Кошкін А.Н. | 67-89-01 | 10/02/65 | ||
| Бурів А.В. | 51-78-89 | 08/05/63 | ||
| Іванов І.В. | 23-45-87 | 01/07/54 | ||
| Бурів А.В. | 51-78-89 | 03/09/56 | ||
| Петров С.Г. | 34-58-70 | 05/12/54 | ||
| Бурова А.В. | 51-78-89 | 10/08/30 | ||
| Кисельов В.П. | 56-78-90 | 12/08/63 |
2.1 Розрахувати вік співробітників по формулі: =СЕГОДНЯ() – Х (де Х – посилання на комірку з датою народження співробітника). Відформатувати результати розрахунку в роках (ГГ).
2.2 Зробити сортування даних по полях Прізвище (по зростанню) і Вік (по зменшенню).
2.3 Відібрати записи, що містять прізвище, телефон і оклад тих, хто має прізвище, що починається з літери Б і оклад більше 150. Для цього необхідно використовувати функцію автофільтру. Спочатку виконується умова по полю Прізвище, потім по полю Оклад.
2.4 Відібрати записи, що містять прізвище, телефон і оклад тих, хто має прізвище, що починається з літери Б ИЛИ оклад більше 150. Для виконання цього завдання необхідно використовувати функцію розширеного фільтра, задавши діапазон умов під таблицею (мал. 1). Зверніть увагу на те, що умови відбору задаються на різних рядках, тому що ми використовуємо функцію ИЛИ. Якби ми помістили їх в одному рядку, то виконувалася б функція И.
 |
Рис. 8. 1. Застосування розширеного фільтру
Практична робота № 9
Особливості роботи з діаграмами в програмі Excel
1. Змішані діаграми. Побудувати криву зростання доходів в абсолютному і процентному численні.
1.1 Ввести наведену нижче таблицю вихідних даних. Розрахувати процентну зміну доходів щодо січня.
1.2 За допомогою Майстра діаграм побудувати діаграму, яка б містила дві криві, де одна з кривих представлена у вигляді зафарбованої області, а інша - у вигляді лінії. Крім того, діаграма повинна мати дві осі Y - для абсолютних і процентних значень. Для цього потрібно вибрати нестандартну діаграму, наприклад Графік (гістограма 2), у якому відбивається вісь Y абсолютних значень і вісь Y процентних значень.
1.3 Введіть заголовок діаграми «Крива зростання доходів». Приберіть легенду.
| Місяць | Січень | Лютий | Березень | Квітень | Травень | Червень | Липень | Серпень | Вересень | Жовтень | Листопад | Грудень | |
| Доход | |||||||||||||
| Відсоток | |||||||||||||
| ¯ =В2/$B$2-100% - зверніть увагу, як виглядає формула! |
2. Регресія. Представити ряд дискретних значень у вигляді лінії.
2.1 Ввести наведену нижче таблицю вихідних даних кореляції рекордів Біг - Стрибки в довжину. Побудувати точечну діаграму (стандартну).
2.2 Введіть заголовок діаграми «Кореляція рекордів Біг - Стрибки в довжину». Приберіть легенду.
2.3 Змінити діапазон значень по осі Х від 10.00 до 12.50, а осі Y від 6.00 до 9.00. (Формат оси/Шкала). Приведіть формат підписів на осі у відповідність зі зразком. Зверніть увагу на те, де перетинаються осі.
2.4 На діаграмі виділити дані, викликати діалогове вікно Линия тренда і вибрати тип тренда: линейный.
| Регресія. Кореляція рекордів Біг -Стрибки в довжину. | |||||||||||||||||||||||||||
| Біг, 100м | 10,2 | 10,3 | 10,4 | 10,6 | 10,7 | 10,8 | 11,3 | 11,5 | 11,9 | 12,2 | |||||||||||||||||
| Стрибки в довжину | 8,8 | 8,65 | 8,7 | 7,7 | 7,2 | 6,95 | 6,7 | 6,9 | 6,6 | 6,55 | 6,4 | 6,1 | |||||||||||||||
3. Логарифмічне представлення даних. Значення рівня потужності.
3.1 Ввести вихідні значення, використовуючи наведену нижче таблицю.
| Рівень потужності | 3092,019 | 4168,96 | 5885,937 | 7982,545 | 10146,02 | 16405,87 |
| 22778,64 | 36426,74 | 69879,26 | 231129,5 | 129681,6 | 52143,31 | 33396,71 |
3.2 Побудувати графік. Змінити градацію осі Y від 1000 до 1000000 (Формат оси/ Шкала). На цій вкладці необхідно установити наступні параметри (див. мал. 3.1)
3.3 Установити логарифмічну шкалу. Нанести сітку (основні і проміжні лінії по осі Y).
 |
Рис. 9.1. Діалогове вікно Формат осі
4. Діаграми з планкою погрішності.
4.1 Увести вихідні значення, використовуючи наведену нижче таблицю.
| Діаграма з планками погрішностей | ||||||
| Тест 1 | Тест 2 | Тест 3 | Тест 4 | Тест 5 | Тест 6 | |
| 11,00 | 10,40 | 9,30 | 13,00 | 18,50 | 12,30 | |
| 10,00 | 9,30 | 7,90 | 12,00 | 18,20 | 11,20 | |
| 11,20 | 9,90 | 7,40 | 11,00 | 16,30 | 10,70 | |
| 10,80 | 9,80 | 7,70 | 11,90 | 18,30 | 11,30 | |
| 10,30 | 9,80 | 8,80 | 12,80 | 18,80 | 11,90 | |
| 11,10 | 9,60 | 6,50 | 11,20 | 18,30 | 10,70 | |
| 11,00 | 10,10 | 8,40 | 12,70 | 19,10 | 12,00 | |
| 10,80 | 9,90 | 8,10 | 11,80 | 17,30 | 11,20 | |
| 11,20 | 9,90 | 7,30 | 11,50 | 17,90 | 11,10 | |
| 10,90 | 10,30 | 9,10 | 12,10 | 16,60 | 11,60 | |
| Середнє | ||||||
| Відхилення |
4.2 Для кожного тесту розрахувати середнє значення, використовуючи статистичну функцію СРЗНАЧ, і відхилення від середнього значення за допомогою статистичної функції СТАНДОТКЛОН.
4.3 Побудувати стовпчасту діаграму за середнім значенням із планками помилок. Для цього необхідно побудувати гістограму, викликавши діалогове вікно Формат рядов данных (вкладка Y-погрешности). Вибрати варіант показу обох планок погрішностей по Y. Установити перемикач «пользовательская» і по «+» і по «-» указати однаковий діапазон відхилень.
4.4 На діаграмі «Кореляція рекордів Біг-Стрибки в довжину» установити планку погрішностей для осі Х: відносна погрішність секундоміра 1%, а по осі Y: фіксована погрішність виміру довжини 0.05 м.
4.5 Роздрукувати готові графіки разом з таблицями.
Практична робота №10
Застосування фінансових функцій
Тема: Функції EXCEL для розрахунку по кредитах і позиках
Освоїти:визначення майбутньої вартості за допомогою функційБСіБЗРАСПИС
У пакеті Excel існує група функцій, пр