Лекція MS Excel. Використання діаграм, списків, макросів.

Матеріал з Фізмат Вікіпедії
Перейти до: навігація, пошук

Тема: Побудова діаграм засобами MS Excel. Створення та використання списків і макросів.

Мета: навчити учнів будувати графіки та діаграми з допомогою Майстра діаграм, форматувати діаграми, створювати та використовувати списки та макроси. Закріпити знання з попередньої теми. Досягти гарної поведінки за допомогою цікавості.

Тип уроку: Лекція.

Обладнання: проектор, медіадошка, конспект, комп’ютер.

Хід уроку

І. Організаційні моменти.

Готовність учнів до уроку. Дотримання ними правил техніки безпеки Перевірка присутності. Знайомство (якщо новий клас).

ІІ. Актуалізація опорних знань.

Давайте повторимо попередній матеріал.

  1. Що таке електронна таблиця?
  2. Як створити таблицю?
  3. Як ввести інформацію в комірку?
  4. Як вставляти рядки та стовпці в таблицю?
  5. Що таке робоча книга таблиці?
  6. Як копіювати комірки таблиці?
  7. Як можна вирівняти текст в комірці?
  8. Як змінювати розміри комірок?

ІІІ. Подача нового матеріалу.

Створення графіків та діаграм

Табличний процесор MS Excel дає змогу подавати табличні дані в наочнішій та зручнішій для сприйняття графічній формі. Саме діаграми є засобом наочного подання даних і полегшують виконання порівнянь двох і більше величин із метою виявлення тенденцій зміни якого-небудь параметра в часі, відображення відсоткового вмісту кількох компонентів у певному об'єкті тощо. <flash align="center">file=Tabl_proc_prez_158_kav.swf|width=700px|height=700px|quality=best</flash>

Типи діаграм

Microsoft Excel дозволяє створювати різні діаграми. Всього існує більше 30 типів вбудованих діаграм, багато з яких мають ще кілька різновидів (видів). Вибір типу діаграм та графіків проходить при їх створенні і залежить від поставленої мети та значень показників.

Наведемо найбільш часто використовувані типи діаграм:
• Гістограма;
• Лінійчата діаграма;
• Графік;
• Кругова діаграма;
• Кільцева діаграма;
• Діаграма з ділянками;

• Точкова діаграма;

Гістограма, що належить до стобчикових діаграм, є одним із полпулярних засобів ділової графіки. Гістограму зручно використовувати для зображення показників, які порівнюються в межах певного часового інтервалу, тощо. Гістограми дають змогу, наприклад, наочно відобразити планові та фінансові показники виробництва за квартал звітного року, щомісячні платежі за кредитами тощо.
Різновидом гістограм є циліндричні, конічні та пірамідальні діаграми; в них стовбці замінюються відповідними геометричними об'єктами.

Лінійчата діаграма — це така сама гістограма, але з іншою орієнтацією осей. Тут вісь критеріїв X розміщується вертикально, а вісь значень Y — горизонтально. В Excel її часто віддають перевагу, тому що її стовпці розміщуються горизонтально по рядках і добре вписуються в робочий аркуш.

Графік, відзначається своєю простотою та наочністю і відображає одну або кілька залежностей виду y=f(x). Використовується для ілюстрації коливань у часі курсів валют або акцій.

Кругова діаграма відображає співвідношення між цілим і його частинами (наприклад, між усім бюджетом та його окремими статтями, усією інвестиційною програмою та її складовими тощо). Для побудови цієї діаграми необхідно обчислити суму всіх значень певного поля таблиці, а потім визначити, яку частину цієї суми становить кожне значення. Окремі або всі сегменти діаграми можуть висуватися.

Створення діаграм за допомогою програми Майстер діаграм

Загальні правила

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

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

При створенні діаграми типу "Поверхня" має бути два стовпці (рядки) даних, не вважаючи стовпця (рядки) підписів категорій.

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

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

Створення діаграми

1. Виділити клітинки, які містять дані, які повинні бути відображені на діаграмі. Якщо необхідно, щоб у діаграмі були відображені і назви рядків або стовпців, виділіть їх також.
2. Натиснути кнопку Майстер діаграм (Вставка - Діаграма).
3. Дотримуватись інструкцій майстра.

Процес створення діаграм за допомогою Майстра складається з чотирьох характерних кроків. Це:
• Вибір типу та вигляду діаграми, а також перегляд її зразка;
• Зміна або вибір діапазону даних, на основі яких буде побудовано діаграму, і визначення способу формування її рядів;
• Введення заголовків діаграми та її осей; встановлення параметрів діаграми, пов'язаних із її зовнішнім оформленням;

• Вибір варіанта розміщення діаграми.

Вибір типу діаграми ( крок 1)

У діалоговому вікні Мастер диаграмм (шаг 1 из 4): тип слід вибрати тип і вид діаграми

Діалогове вікно має дві вкладки: Стандартные і Нестандартные.

У вкладці Стандартные розташовані стандартні типи діаграм Microsoft Excel та їх різновиди (види).

Для перегляду зразкового зовнішнього виду обраної діаграми слід натиснути і утримувати кнопку Просмотр результата.

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

Вибір джерела даних ( крок 2)

У діалоговому вікні Мастер диаграмм (шаг 2 из 4): источник данных диаграммы можна уточнити або заново вибрати джерело даних для діаграми. Діалогове вікно має дві вкладки: Диапазон данных і Ряд.

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

Як правило, незалежно від розміщення даних на аркуші, Microsoft Excel правильно вибирає варіант побудови рядів даних (по рядках або по стовпцях виділеного діапазону) і встановлює відповідний перемикач (на строках или на столбцах). Імена рядів даних, показуються в легенді. При необхідності можна вибрати самостійно, але це суттєво змінить вигляд діаграми.

Вибір основних параметрів ( крок 3)

У діалоговому вікні Мастер диаграмм (шаг 3 из 4): параметры диаграммы можна вибрати і встановити основні параметри діаграми. Діалогове вікно, в залежності від типу створюваної діаграми, має від трьох до шести вкладок: Заголовки, Оси, Линии сетки, Легенда, Подписи данных, Таблица данных.

Місце розташування діаграми( крок 4)

На останньому кроці Майстер діаграм пропонує положенням перемикача задати місце розміщення діаграми: на окремому аркуші або на наявному . За замовчуванням Excel поміщає діаграму на аркуш з даними . Вона зберігається разом з даними і друкується разом з цим листом. Нею зручно користуватися у звітах і презентаціях, так як діаграма розташовується поряд з даними, на підставі яких вона побудована.


Редагування діаграм.

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


Форматування областей діаграм. Розрізняють два великих об’єкти діаграм: область діаграми і область її побудови. Перша – це простір, обмежений зовнішньою рамкою діаграми, а друга - простір між осями координат Х, Y. Будь – яка область активізуються подвійним клацанням лівою клавішею миші на будь - якій її точці.


Під час активізації області діаграми на екрані з’являється вікно форматування “Формат області діаграм” з трьома його вкладками “Вид”, “Шрифт” і “Свойства”. (Також Форма області діаграми можна активізувати з контекстного меню ). Вкладка “Вид” дає змогу встановити колір, товщину та тип лінії рамки областей діаграм, а також колір та спосіб її заливання. Заливання області додає їй привабливого зовнішнього вигляду. Вкладка “Шрифт” забезпечує вибір типу шрифту, його розміру, зображення, кольору, способу підкреслення і т. п. Вкладка “Свойства” забезпечує прив’язку об’єкта до фону області діаграми і захист її комірок. Під час активізації області побудови діаграми на екран викликається вікно “Формат области построения”, який має тільки одну вкладку “Вид”.


Великі можливості редагування областей діаграм надає користувачеві контекстне меню та інструментальна панель “Диаграмма”. Формат області діаграми.


Контекстна меню редагування діаграм. Щоб відкрилося контекстне меню потрібно правою клавішею миші, звичайно коли область діаграми або область її побудови діаграми виділено.


До меню області діаграми входять команда Формат области диаграммы і чотири команди, які практично реалізують функції(кроки) Майстра діаграм. Це – “Тип диаграмм”, ”Исходные данные”, “Параметры диаграммы”, “Размещения”. Також є команди що стосуються параметрів “На передний план” і “На задний план”, це вони забезпечують висування області відносно таблиці даних.


Контекстне меню області діаграми та області її побудови. Інструментальна панель “Диаграмма”. Ця панель активізується за командою Вид-Панель инструментов – Диаграммы, її використання істотно прискорює процес формування та перетворення діаграм. За допомогою відповідних піктограм панелі можемо: Інструментальна панель “Диаграммы”.

  • Вибрати зі списку будь який елемент.
  • Здійснити форматування вибраного об’єкта діаграми.
  • Вибрати тип або вигляд діаграми.
  • Приховати, відобразити та змінити легенду.
  • Добавити таблицю даних.
  • Увімкнути режим по рядках та стовпцях.
  • Змінити положення об’єктів осі категорій та осі значень на 45 градусів за рухом годинникової стрілки або у протилежному напрямі.

Отже, панель “Диаграммы” дає змогу здійснювати форматування всіх об’єктів діаграми.


Вибір і форматування об’єктів діаграми.


Будь – який об’єкт діаграми який підлягає форматуванню, потрібно заздалегідь виділити. Потім на будь – якому його місті клацаємо правою клавішею миші і випадає контекстне меню, але за командою “Формат рядов данных”. Порядок форматування об’єкта залишається попереднім.

Робота зі списками.

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

Сортуванням називають перевпорядковування даних, при якому значення обраних вами полів (їх називають ключовими полями або ключами) розташовуються:
числа – в порядку зростання або зменшення їх величин,
тексти – в алфавітному порядку (прямому чи зворотному),
дати і час - у хронологічному порядку (прямому або зворотному).

Перед сортуванням даних важливо правильно виділити область, у якій відбуватиметься сортування. Якщо перед вибором меню Данные / Сортировка помістити курсор усередину таблиці, то Excel автоматично виділить усі її рядки і стовпці. Якщо ж виділити тільки частину таблиці, то і сортування відбудеться тільки у межах виділеної області. Наприклад, якщо ви виділите тільки стовпець із прізвищами співробітників, але не виділите стовпець із сумами належних їм виплат, то прізвища будуть перевпорядковані, але суми виплат залишаться нерухомими в колишніх комірках. Так можна зіпсувати дані, на введення яких був витрачений значний час. Виправити помилку можна, якщо відразу після неправильного сортування натиснути кнопку скасування, або закрити файл, не зберігаючи на диску внесені зміни.

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

Для сортування за декількома полями слід помістити курсор усередину таблиці або виділити діапазон комірок, що підлягає сортуванню, та вибрати в меню Данные опцію Сортировка. В результаті не екрані з'явиться вікно.

Spusok lection 001.jpg

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

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

У графі Сортировать по виберіть заголовок чи координату стовпця, по якому буде виконуватися сортування, а також напрямок сортування: по возрастанию або по убыванию.

Записи, що містять однакові значення ключового поля, у свою чергу, можуть бути відсортовані по другому ключовому полю, якщо ви заповните графу Затем по. Наприклад, при сортуванні персоналу записи з однаковими прізвищами сортуються по іменах. Аналогічно, записи, що містять однакові значення в двох перших ключових полях, можна піддати сортуванню, указавши третє ключове поле в графі В последнюю очередь по. Наприклад, якщо збігаються і прізвище, і ім'я, то сортування здійснюється за полем по батькові.

Для виконання сортування з обраними параметрами, клацніть мишкою на кнопці ОК.

Підведення підсумків

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

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

Закінчивши сортування, і обравши в меню Даные пункт Итоги, на екран виводиться вікно , у якому тртеба відповісти на наступні питання:
1. Де треба вставляти рядки з підсумками? Якщо, наприклад, ми виберемо тут Дата або Товар, то рядок підсумків буде з'являтися там, де змінюється значення дати чи найменування товару відповідно.
2. Як треба підбивати підсумок? Частіше усього підсумок - це просто сума значень у стовпці. Але замість суми можна вибрати, наприклад, середнє арифметичне, максимальне або мінімальне значення тощо.
3. У яких стовпцях треба підбивати підсумок? Тут слід зазначити найменування стовпців, у яких виконується обчислення суми, середнього і т.п.
4. Чи потрібно вилучати всі старі підсумки перед виводом нових? Якщо ви відмовитеся від вилучення, то нові підсумки будуть додаватися до раніше сформованого. Це дозволяє виводити декілька різних підсумків одночасно, наприклад, до раніше обчислених сум додати ще і середні значення тощо.
5. Чи потрібно після виводу кожного рядка з підсумками переходити на нову сторінку?

6. Де потрібно розміщати рядки з підсумками?(під відповідними групами рядків даних або над ними).

Spusok lection 002.jpg

Вказавши потрібні параметри, клацніть мишкою на кнопці ОК, і у вашій таблиці додадуться рядки з підсумками. Зліва від таблиці з підсумками відображаються символи структури - кнопки з зображеннями знаків плюс і мінус. Користуючись ними, можна сховати деталі даних, залишивши тільки їхні підсумки, або знову відновити відображення деталей.

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

Макрос Excel .

Макрос Excel - це програмка, написана на мові VBA (Visual Basic for Applications), і покликана автоматизувати яку-небудь задачу. Це може бути виконання рутинної операції з використання формату комірки або однакових дій над великою кількістю записів таблиці і т.п. За формою - це записаний за певними правилами текст програми, який зберігається в тих же файлах, що і листи робочої книги, тільки в особливих областях, званих модулями.

Припустимо, треба часто застосовувати напівжирний шрифт Arial 12-го розміру як стиль форматування символів комірок робочих аркушів, до яких треба привернути особливу увагу. З метою скорочення часу, необхідного для форматування тексту, треба записати макрос, який вибирає напівжирний шрифт Arial 12-го розміру і застосовує це форматування до будь-якої комірки або діапазону комірок поточного виділеного фрагмента.

Оскільки необхідно, щоб макрос працював з будь-якою виділеною коміркою або діапазоном комірок, стартовими умовами для цього макросу є відкрита робоча книга з виділеним діапазоном комірок в активному робочому аркуші.

Для запуску макрорекордера в Excel, призначення імені макросу, вибору місця для збереження нового макросу і вибору додаткових опцій треба виконати наступне:
• Вибрати команду "Сервис-Макрос-Начать запись";
• У текстовому вікні "Имя макроса" в якості імені макросу ввести наприклад - ArialBold12. Таке ім'я дозволить запам'ятати, що виконує макрос;
• У полі "Описание" додати наступний текст: Форматує виділені комірки Arial, Bold, 12;
• Список "Сохранить в" слід використовувати для вибору місця, в якому буде збережений записаний макрос. Доступними варіантами є: "Личная книга макросов", "Новая книга", "Эта книга". Оскільки необхідно, щоб цей макрос був доступний у всіх робочих книгах, треба вибрати " Личная книга макросов ";
• Якщо майбутній макрос планується використовувати досить часто, можна призначити для його запуску гарячу клавішу;

• Для початку запису макросу необхідно натиснути кнопку "ОК".

Makros lection 001.jpg

Про процес запису макросу свідчить наявність панелі " Остановить запись", при цьому, у лівому нижньому кутку вікна книги Excel з'являється напис " Запись ".

Makros lection 004.jpg

За замовчуванням панель "Зупинити запис" в Excel містить дві командні кнопки. Ліва кнопка - кнопка "Стоп"; служить для завершення процесу запису макросу. Права кнопка - кнопка " Относительная ссылка ". За замовчуванням Excel записує абсолютні посилання на клітинки у макроси. Якщо, наприклад, почати запис у виділеній комірці А5, а потім виділити клітинку праворуч від А5, тобто В5, то записаний макрос також буде виділяти комірку В5.

Якщо натиснути кнопку "Относительная ссылка", Excel запише відносне посилання на клітинку кожен раз, коли виділяється якась клітинка. Якщо виділеної в даний момент ми знаходимось в комірці А5 і вибирається комірка праворуч від неї під час запису з відносними посиланнями, то Excel записує, що була виділена комірка, що знаходиться на 1 стовпець і 0 рядків правіше від поточної вибраної комірки. Коли макрос буде запущений, то він виділить комірку, що знаходиться безпосередньо праворуч від активної клітинки.

Для запису нашого макросу необхідно виконати наступні дії (після натискання кнопки "ОК" у вікні " Запись макроса "):
• З випадного списку "Шрифт" панелі " Форматирование " вибрати шрифт Arial;
• З випадного списку " Размер шрифта "тієї ж панелі вибрати 12-й кегль;

• Натиснути кнопку "Ж" вибору накреслення шрифту.

Makros lection 002.jpg

Після цього макрорекордер необхідно зупинити, натиснувши кнопку "Стоп" на панелі " Остановить запись". Або вибрати команду " Сервис-Макрос-Остановить запись ").

Наш новий макрос в Excel тепер закінчений і готовий до виконання.

Краще всього винести кнопку з макросом на панель інструменов. Вибираємо меню Сервис —Настройка.

Makros lection 003.jpg

Закладка " Команды ", категорія " Макросы ". Кнопочку зі смайлом пересуваємо на будь-яку зручну і відповідну за змістом панель інструментів. Закриваємо вікно налаштування. Тепер натискаємо смайлик.

Ах так! Ми ж ще не вказали Excel, який саме макрос потрібно виконувати при натисканні смайлика. Не біда - при першому натисканні Excel сам запропонує нам вибрати макрос для присвоєння кнопці. Вибираємо свій макрос.

Тепер все? Якщо пощастить і Ви не наробили помилок, то все. Тепер натискання смайла буде пророблювати над будь-якою коміркою або комірками всі операції, записані нами.

IV.Закріплення нового матеріалу.

Відповімо на наступні запитання:

  1. Які типи діаграм ви знаєте?
  2. Назвіть основні складові діаграми.
  3. На скільки кроків розбивається процес створення діаграми? І назвіть їх.
  4. Що таке список?
  5. Для чого використовується сортування даних?
  6. Макрос - це...

V. Домашнє завдання.

Прочитати дану тему в підручниках. --kit_av 11:24, 8 березня 2011 (UTC)