MS Excel 1
Мета роботи:
Ознайомитися з програмою MS Excel. Отримати навички введення даних та побудови таблиць. Навчитися працювати з формулами, функціями та діаграмами..
Теоретичний матеріал
1. Адреса комірки
Кожна комірка має власну, адресу яка утворюється на перетині стовпчика та рядка та має відповідне позначення (літера стовпчика та номер рядка). Адреса комірки відображається у верхньому лівому куті програми під панеллю інструментів (рис. 1).
Рис.1. Адреса комірки B3
2. Діапазон комірок
Діапазон комірок – це група сусідніх комірок. Діапазон позначається адресами верхньої лівої та нижньої правої комірок через двокрапку. Наприклад діапазон комірок B2:D6 (рис. 2). Перша комірка діапазону завжди виділена білим кольором (В2).
Рис.2. Діапазон комірок B2:D6
Якщо необхідно виділити декілька комірок або діапазонів, необхідно використовувати клавішу Ctrl (Рис. 3)
Рис. 3. Діапазони комірок A1:B2 та B4:B6.
3. Формула
Формула використовується для проведення математичних розрахунків. Формула починається зі знаку „=”. Наприклад:
=А1+А2 (в цьому випадку до числа, яке знаходиться в комірці А1, додається число, яке знаходиться в комірці А2)
=5+4 (в цьому випадку до 5 додається 4)
Після введення знаку „=” адресу комірки А1 вказуємо курсором та лівою кнопкою миші, ставимо знак „+” і клацаємо лівою кнопкою миші по комірці А2.
В комірці, в якій введена формула, відображається кінцевий результат. Щоб переглянути формулу, треба виділити комірку, формула буде відображатися в Рядку формул. В цьому ж рядку формул можна відредагувати формулу (Рис. 4).
Рис. 4. В комірці В4 введена формула, яка додає значення комірки А1 до А2. Формула комірки В4 відображається в рядку формул
4. Функція автозаповнення
Функція автозаповнення дозволяє заповнювати область комірок за певними правилами.
Маркер автозаповнення знаходиться в правому нижньому куті виділеної комірки / діапазону комірок, та має вигляд чорного квадрату (Рис. 5).
Рис. 5. Маркер автозаповнення
Щоб скористатися функцією автозаповнення, треба ввести в комірку / діапазон комірок дані та потягти за маркер автозаповнення. При наведенні курсору на маркер, курсор прийме вигляд чорного хрестика.
Дії, які будуть виконуватися при використанні цієї функції, будуть залежати від даних, які введені в комірку, наприклад:
· Якщо в комірку ввести число або слово та потягти за маркер, то воно скопіюється (Рис. 6)
Рис. 6. Копіювання даних за допомогою функції автозаповнення
· Якщо в попередньому варіанті задати крок, то функція автозаповнення продовжить ланцюг чисел. Наприклад задамо крок рівний 2. Тоді кожне наступне число буде збільшуватися на 2. Для цього вводимо в першу комірку число 1, а в другу – число 3. Виділяємо цей діапазон та тягнемо за маркер автозаповнення. В результаті цих дій ми отримаємо стовпчик з непарних чисел (Рис. 7).
Рис. 7. Створення ряду чисел за допомогою функції автозаповнення
· Якщо в комірку ввести день тижня або назву місяця, то в наступних комірках назви днів тижня / місяців будуть продовжуватися. В деяких версіях програми ця функція не працює з українською мовою, тому назви місяців та днів тижня треба вводити російською мовою (Рис. 8).
Рис. 8. Введення днів тижня за допомогою функції автозаповнення
· При копіюванні формули, адреси комірок, які знаходяться в формулі, будуть змінюватися на сусідні (Рис. 9).
Рис. 9. Копіювання формули
На малюнку видно, що при копіюванні формули вправо адреси комірок А1 та А2 змінилися на В1 та В2 відповідно.
Якщо копіювання формули відбувалося б вниз, то адреси А1 та А2 змінилися б на А2 та А3 відповідно.
· Для того, щоб при копіюванні формули адреси комірок не змінювалися, існує таке поняття як абсолютне посилання.
Виглядає воно наступним чином:
=A1+$A$2
В цій формулі посилання на комірку А2 є абсолютним. Щоб зробити посилання абсолютним, треба помістити курсор між літерою А та цифрою 2 та натиснути клавішу F4.
При копіюванні такої формули вправо, А1 буде змінюватися на В1, а А2 буде залишатися незмінною (Рис. 10).
Рис. 10. При копіюванні формули за допомогою функції автозаповнення, абсолютне посилання не змінюється
5. Функції
Функції – це вбудовані в програму MS Excel формули, які дозволяють проводити складні обчислення, та покликані скоротити час на побудову формул.
Кожна функція має ім’я та аргумент, який знаходиться в круглих дужках. Всі доступні функції знаходяться в Мастере функций, який викликається в меню Вставка – Функции. Аргументом функції може бути число, адреса комірки, діапазон комірок, слова. Наприклад:
=СУММ(А1:А3) – знаходить суму чисел, які знаходяться в комірках А1, А2 та А3
=СРЗНАЧ(А1:А3) – знаходить середнє значення чисел, які знаходяться в діапазоні комірок А1:А3
В Мастере функций всі функції розбиті по категоріям (математичні, статистичні тощо), мають коротке пояснення та рядок пошуку (Рис. 11).
Рис. 11. Майстер функцій
MS Excel дозволяє створювати різноманітні діаграми. Найпростішим способом створення діаграми є використання майстра діаграм, який викликається командою Insert/Вставка, Chart/Диаграмма(Вставка, діаграма), або кнопкою Chart Wizard/Мастер диаграмм (Майстер діаграм). Потрібний тип діаграми можна вибрати із списку Chart type/Тип (Тип) вкладки Standart Types/Стандартные (Стандартні), або вкладки Custom Types (Нестандартні). В області Chart sub-type/Вид (Вигляд) вибирається потрібний вигляд діаграми.
MS Excel пропонує такі основні типи діаграм: Column charts (Гістограми), Bar charts (Лінійні діаграми), Line charts (Графіки), Pie charts (Кругові діаграми), XY (Scatter) charts (Точкові діаграми), Area charts (Діаграми з областями), Doughnut charts (Кільцеві діаграми) та ін.
Елементами діаграми є маркери, легенди, осі, мітки, надписи та ін. Виділити елемент діаграми можна за допомогою миші, перемістивши на нього вказівник і клацнувши лівою кнопкою.
Ряд даних (Data Series/Ряд данных) – це один рядок або один стовпчик даних, які відображуються у діаграмі як сукупність маркерів даних, таких як стовпці, лінії, сектори. По замовчуванню діаграма будується з рядами у рядках. Кожен ряд даних відрізняється кольором чи узором маркера.
Орієнтація даних (Series in/Ряды в) – це вказівка про те, як розмістити ряди даних у робочому листі: чи по рядках (rows/строках), чи по стовпцях (columns/столбцах). Вибирається на вкладці Data Range/Диапазон данных у полі Range/Диапазон данных діалогового вікна Source Data/Исходные данные (Другий крок майстра діаграм чи із контекстного меню області діаграми вибирають відповідну команду). Від вибору орієнтації залежатиме, вміст яких комірок буде використовуватися в якості імен категорій і тексту легенди.
В області діаграми знаходяться всі елементи діаграми (Chart Area), областю побудови діаграми є область лише діаграми (Plot Area).
Точка даних (Data Point/Точка данных) – це одиничний елемент даних у середині будь-якого ряду даних.
Маркер даних (Marker/Маркер) – це графічний елемент представлення точки даних (стовпчик, лінія, сектор, смуга тощо). Для того, щоб змінити тип маркера Format Data Series/Формат рядов данных (Формат ряду даних) із контекстного меню в режимі редагування діаграми для виділеного ряду даних відкривають вікно, в якому на вкладці View/Вид (Вигляд) в області Marker/Маркер (Маркер) активізують режим Custom/Другой (Інший) і вибирають тип (Style/Тип маркера), колір (Foreground/Цвет), фон (Background/Фон), розмір (Size/Размер), з тінню чи без (Shadow/Тень).
Легенда (Legend/Легенда) – спеціальні пояснення до маркерів або символів, що використовуються у діаграмі. Автоматично створюється легенда із назв, які розміщені вздовж короткої сторони діапазону рядів даних.
Категорія даних (Category/Категория) – є групою даних і переважно відкладається по осі категорій. Якщо визначено ряди даних у рядках таблиці, то категорії є стовпчиками. Для включення легенди в діаграму використовується вкладка Legend/Легенда (Легенда) діалогового вікна Chart Options/Параметры диаграммы ((Параметри діаграми). Можна також скористатися кнопкою Legend/Легенда (Легенда) на панелі Інструментів Chart/Диаграмма ((Діаграма). Для вилучення легенди потрібно її виділити і натиснути на клавішу <Delete> або кнопку Legend/Легенда (Легенда) на панелі інструментів Chart/Диаграмма (Діаграма).
Розрізняють такі способи розміщення легенди діаграми: Bottom/Внизу, посередине; Corner/В верхнем правом углу; Top/Внизу, посередине; Left/Слева, посередине; Right/Справа, посередині; задаються вони в області Placement/Размещение (Розміщення) на вкладці Legend/Легенда (Легенда) діалогового вікна, що відкривається командою Chart Options/Параметры диаграммы (Параметри діаграми) зі контекстного меню в режимі редагування діаграми.
Поділки (Tick mark/Засечки) – це маленькі лінії, що розбивають лінії осей, як розмітка на лінійці. Щоб додати заголовки діаграми, заголовки осей і легенди командою Chart Options/Параметры диаграммы (Параметри діаграми) із контекстного меню в режимі редагування діаграми відкривають вікно, у якому на вкладці Title/Заголовки (Заголовки) у полі Chart Title/Название диаграммы зазначають назву діаграми, у полях Category(X) Axis/Ось Х (категорий), Value (Y) Axis/Ось У (значений) підписи до осей X і Y відповідно, на вкладці Legend/Легенда (Легенда) включають режим Show legend/Показывать легенду (Показувати легенду).
В діаграму можна додавати текст, не пов'язаний з елементами діаграми, а також графічні об'єкти, створені за допомогою панелі інструментів Drawing (Рисування). За допомогою вкладки Gridlines/Линии сетки (Лінії сітки) діалогового вікна Chart Options/
Параметры диаграммы (Параметри діаграми) на діаграмі можна відобразити лінії сітки — паралельні осям лінії, що проходять через основні (Major/Основные) й допоміжні (Minor/Прормежуточные) поділки осей.
Форматування об'єктів діаграми здійснюється за допомогою діалогового вікна Format/Формат (Формат), що викликається командою Format, Select object/ Формат, выделенный объект (Формат, виділений об'єкт) або командою Format/ Формат (Формат) із контекстного меню виділеного об'єкта.
MS Excel при створенні плоских діаграм автоматично формує вісь категорій (X) і вісь значень (Y), а при створенні об'ємних діаграм вісь категорій (X), вісь рядів даних (Y) і вісь значень (Z). Зображенням цих осей на екрані можна керувати за допомогою вкладки Axes (Осі) діалогового вікна Chart Options/Параметры диаграммы (Параметри діаграми), що викликається командою Chart, Chart Options/Диаграмма, Параметры диаграммы (Діаграма, параметри діаграми).
Налаштування осей передбачає зміну основних ліній, вибору розміщення поділок. Воно проводиться за допомогою вкладки Patterns/Вид (Вигляд) діалогового вікна Format Axis/Формат оси (Формат осі), що викликається для виділеної осі командою Format Axis/Формат оси (Формат осі). Поділки можна розмістити всередину (Inside/Внутрь), назовні (Outside/Наружу) або представити такими, щоб перетинали вісь (Cross/Пересекают Ось). Мітки поділок (Tick mark labels/Метки делений) можуть розміщуватися поряд із віссю (Next to axis/Рядом с осью), під віссю (Low/Внизу), над віссю (High/Вверху), або не показувати їх (None/Нет).
На вкладці Scale (Шкала) діалогового вікна Format Axis (Формат осі) можна налаштувати шкалу осі категорій (X) і шкалу осі значень (Y), а саме задати граничні значення (Minimum/Минимальное значение, Maximum/ Максимальное значение), основні і допоміжні кроки (Major Unit/Цена основных делений, Minor Unit/Цена промежуточных делений).
В діаграму можна вставити мітки даних (Data Labels/Метки данных), якими можуть бути або значення точок даних, або відповідні до цих точок мітки осі категорій (X). Для цього використовують вкладку Data Labels (Підписи даних) діалогового вікна Chart Options (Параметри діаграми). Для того, щоб додати мітки значень командою Format Data Series/Формат рядов данных (Формат ряду даних) із контекстного меню в режимі редагування діаграми для виділеного ряду даних відкривають вікно, у якому на вкладці Data Labels/Подписи данных (Підписи даних) в області Data Labels/Подписи значений (Підписи значень) активізують режим Show Label/Значения (Значення) або включають режим Show legend key next to Label/Ключ легенды (Показувати поряд із мітками ключ легенди).
Для зміни рамок, кольорів і візерунків об'єктів діаграми використовується вкладка Patterns/Вид (Вигляд). Наприклад, колір і стиль маркерів, які використовуються для позначення точок даних, задаються на вкладці Patterns/Вид (Вигляд) діалогового вікна Format Data Series/Формат рядов данных (Формат ряду даних), яке викликається командою Format, Selected Data Series/Формат, Выделенный ряд (Формат, виділений ряд).
Розмір шрифту текстового фрагмента на діаграмі, його накреслення, колір, фон задається на вкладні Font (Шрифт) діалогового вікна Format (Формат).
Вирівнювання і орієнтація тексту, розміщеного в діаграмі, здійснюється на вкладці Alignment/Выравнивание (Вирівнювання) діалогового вікна Format/Формат (Формат).
Форматування чисел в діаграмі здійснюється на вкладці Number/Число (Число) діалогового вікна Format Axis/Формат оси (Формат осі), яке викликається для виділеної осі командою Format Axis/Формат оси (Формат осі).
Зміна параметрів відображення графічних об'єктів в діаграмі здійснюється за допомогою діалогового вікна Format AuloShape/Формат автофигуры (Формат автофігури), що викликається командою Format, Selected Object/Формат, выделенный объект (Формат, виділений об'єкт).
Для зміни ракурсу об’ємних діаграм використовують команду Chart, 3-D View/Диаграмма, Объёмный вид (Діаграма, Об’ємний вигляд). У однойменному діалоговому вікні задають висоту огляду діаграми (Elevation/Возвышение), кут повороту (Rotation/Поворот), вказують співвідношення переднього й заднього плану (Perspective/Перспектива), висоту вертикальної осі (Z) у відсотках до ширини діаграми по осі (X) (Height % of Base/Высота % нормальной), значення осьових кутів установлюють рівними 90о (Right Angle Axis/Изометрия).__
Практичне завдання 1
1. Введення даних
· В клітинку А1 ввести прізвище, ім’я та по батькові. Розширити стовпчик А так, щоб введені дані повністю було видно.
· У клітинку А2 ввести групу.
· В клітинки В1, В2, В3, В4 ввести числа 25; -10; 12,25; 0,001.
· В клітинку С1 ввести дату народження.
· В клітинку С2 ввести поточну дату.
2. Ввести засобом автозаповнення
· в клітинки D1:D20 числа з 1 до 20.
· в клітинки Е1:Е20 непарні числа починаючи з 5.
· в клітинки F10:F20 дні тижня.
· в клітинки G1:G12 ввести місяці.
3. Перейти на Лист 2 та порахувати наступну таблицю:
Для цього:
значення для х та у заповнити за допомогою функції автозаповнення;
в комірки C2, D2, E2, F2 ввести формули, які зазначені на малюнку;
скопіювати введені формули в нижні комірки (на місце знаків "?") засобом автозаповнення;
в комірки A22 та F22 ввести функції, які зазначено на малюнку.
4. Для діапазону комірок F2:F22 задати відсотковий формат, кількість знаків після коми – 2.
Для цього треба виділити діапазон комірок, зайти в меню Формат => Ячейки => Число => Процентный => Число десятичных знаков => 2.
Створити наступну таблицю:
"..." - ввести будь-які числа
"?" - ввести формули
Для цього:
Об'єднати комірки А1:F1. Для цього треба виділити ці комірки та натиснути кнопку "Объединить и поместить в центре"
Ввести у об'єднані комірки назву таблиці.
Об'єднати наступні групи клітинок: A2:A3, B2:B3, C2:F2.
Для рядку "% виконання плану" (комірки B7:F7) встановити відсотковий формат. Для цього треба виділити ці комірки, зайти в меню "Формат", пункт "Ячейки", вкладка "Число", числові формати "Процентный".
Замість трьох крапок ввести будь-які числа (оборот магазину за квартал).
Замість знаків питання ввести розрахункові формули:
Відхилення від плану = планове завдання - фактично виконано
% виконання плану = фактично виконано / планове завдання
Стовпчик "Всього за рік" знаходиться за допомогою функції СУММ:
Наприклад:
=Сумм(С3:F3) - всього за рік планове завдання
"Всього за рік" в рядку "% виконання плану" знаходиться за формулою: фактично виконано / планове завдання
Для показників "Планове завдання" та "Фактично виконано" побудуємо діаграму. В діаграмі повинні бути присутні підпис даних, легенда. Діаграма повинна розташовуватися на окремому аркуші.
Перейти на наступний аркуш.
Зайти в меню "Вставка", пункт меню "Диаграмма".
Вибрати пункт "Гистограмма" та натиснути кнопку "Далее".
У вікні "Мастер диаграмм" на вкладці "Диапазон данных" в рядку "Диапазон" ввести діапазон клітинок C4:F5. Для цього треба поставити курсор в рядок "Диапазон", потім клацнути на комірку С4 і, не відпускаючи кнопку миші, провести курсором до комірки F5.
Перейти на вкладку "Ряд" та вказати імена рядів та підпис осі Х
На вкладці "Заголовки" ввести назву діаграми "Виконання плану товарообігу" (без лапок). Вказати інші необхідні параметри. Натиснути кнопку "Далее".
3. Побудувати колову діаграму для показнику "Планове завдання". Доповнити діаграму назвою, легендою, підписами даних. Розташувати діаграму поряд з попередньою гістограмою.
4. На іншому аркуші створити наступну таблицю:
5. Таблиця повинна мати 15 маршрутів. Відсутні маршрути (з 2 по 14) необхідно придумати самостійно.
6. У стовпчик "Телефон" номер телефону повинен автоматично розділятися дефісом, при цьому цифри будуть групуватися по 2 цифри. Для цього треба зробити власний формат:
Зайти в меню "Формат", пункт "Ячейки", "Число", "Все форматы".
7. Між стовпчиками "Тривалість" і "Відправлення" вставити стовпчики "Програма" і "Транспорт", між "Відправлення" і "Вартість" - стовпчик "Проживання". Заповнити нові стовпчики.
8. Зафіксуйте стовпчик "В". Для цього зайдіть в меню "Окно", пункт "Закрепить области". Перегляньте інші стовпчики за допомогою лінійок прокрутки.
9. Застосуйте до таблиці автоформатування (Меню "Формат", пункт "Автоформат").
Зберегти роботу.
