Лекція Створення і застосування електронних таблиць у табличному процесорі Microsoft Excel

« Назад

1. Можливості табличного процесора MS Excel

В основу функціонування Excel, як і решти елементів програмного середовища MS Office, покладено принцип WYSIWYG (What You See Is What You Get, тобто “що ви бачите, те й одержуєте”).

Ми вже розглянули елементи ЕТ на прикладі MS Еxcel. А тепер давайте детальніше поговоримо про можливості сучасної версії даної програми Excel 2010. Звичайно вона має всі можливості про які ми говорили вище. Крім оброблення та аналізу табличних даних Excel здатний:

- надавати користувачеві контекстуально-залежну допомогу;

- сортувати табличні дані за алфавітом, зростанням (спаданням), датами, місяцями тощо;

- формувати зведені таблиці, звіти;

- створювати макроси, тобто макрокоманди, які використовуються для автоматизації розв’язання процедур і задач, що часто повторюються;

- проводити обмін даними з іншими програмами та додатками за технологією OLE (Object Linking Embedding – об’єктно-орієнтований зв’язок і вбудовування (упровадження));

- виконувати інші функції.

MS Excel 2010 відрізняється підвищеною комфортністю та значно розширеними можливостями створення реляційних баз даних, зведених звітів зі складною структурою й організацією зв’язку з різними додатками MS Office і всесвітньою інформаційною мережею Internet.

Excel взаємодіє з такими програмами-додатками, що підтримують технологію OLE.

WordArt – програма формування текстових спеціальних ефектів;

Equation – програма математичних формул і рівнянь;

Outlook – програма керування документами (планувальник);

Power Point – потужна графічна програма-редактор і багатьма іншими.

Як додатки до Excel можуть використовуватись: сучасні текстові редактори, табличні процесори та СУБД.

Ще хотілось би поговорити про один принцип роботи Excel, який характерний і для інших програм, які працюють під ОС Windows.

Технологія OLE – це спосіб поєднання документів, створених у різних програмах. На лабораторній роботі ми вже частково використовували цей засіб. Пригадайте лабораторну з діаграмою. А зараз запишемо його технологію детальніше. Принципи вбудовування і пов’язування об’єктів (технологія OLE). Запишемо, що таке пов’язаний і вбудований об’єкти.

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

Вбудований (упроваджений) об’єкт – дані (об’єкт), вставлені у файл. Будучи вбудованим, об’єкт стає частиною файлу. При подвійному клацанні мишкою, вбудований об’єкт відкривається за допомогою програми, у якій був створений. Всі зміни внесені у вбудований об’єкт, відображаються у файлі, що його містить.

Переваги і недоліки:

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

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

2. Елементи вікна програми Excel

А тепер перейдемо до вивчення елементів вікна MS Excel. І для початку нам потрібно завантажити програму одним із способів. Пригадаємо як завантажували Word.

- Пуск–Все программы – Microsoft Office – Excel.

- Пуск–Выполнить–Обзор.

С:\ Program Files \ MS Office\ Excel.exe

- Через ярлик на робочому столі (пригадати, як створювали).

Ці ж способи актуальні і для завантаження програми Excel.

І як зазначалось вище, відкривається вікно програми, у якому відображений робочий лист.

Давайте розглянемо, що ми бачимо (можна опиратись на знання, отримані з попередньої теми).

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

2. Стрічка, яка включає вкладки:

Файл, Главная, Вставка, Разметка страницы, Формулы, Данные, Вид; кнопки згортання/розгортання команд стрічки, виклику довідки, управління розмірами вікна документу справа. (Давайте поглянемо, які пункти меню подібні до програми Word, а які відрізняються?)

На вкладках стрічки розміщені команди та панелі інструментів за групами.

Наприклад, вкладка Главная містить групи:

Рис. 1 - Вкладка Главная в MS Excel

3. Рядок формул. Зліва міститься –

4.  – інструмент «Вствить функцию» для виклику відповідного діалогового вікна.

5. Вікно з поточною адресою вказівника миші.

6. Робоча область (зона).

6.1. Рядок з заголовками стовпців.

6.2. Стовпчик з номерами рядків.

6.3. Полоси прокрутки: вертикальна, горизонтальна.

6.4. Рядок, який містить ярлички листів.

7. Рядок Стану.

Протягом 10 хв. ознайомтесь і законспектуйте назви вкладинок, їх розділів та їх призначення у загальних рисах.

Зверніть увагу: якщо у правому нижньому кутку розділу є такий елемент.

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

Рис. 2 – Формат ячеек в MS Excel

Протягом 10 хв. ознайомтесь і законспектуйте назви розділів пункту Файл – Параметры і їх призначення у загальних рисах.

Рис. 3 – Параметри MS Excel

Давайте обговоримо, що у Вас вийшло.

Зверніть увагу на пункт Формулы розділ Работа с формулами опція «Стиль ссылок R1C1». Подивіться, що відбувається при встановленні відповідного прапорця? (змінюється адресація комірок – на перше місце стає номер рядка і на друге – назва стовпця.)

Поверніть попередній стан налагодження.

Встановити курсор у рядку формул і подивитись які нові елементи з’явились?

Замалювати і записати призначення нових елементів, які з’явились:

 – отмена;

 – ввод;

Встановити курсор у рядку формул, набрати = і подивитись як змінилось вікно з адресою поточної комірки (з’явивися список функцій з 10 функціями, які використовувались останнім часом).

3. Технології розв’язування завдань за допомогою табличного процесора MS Excel

У даному питанні цікаво розглянути деякі прийоми роботи у ТП Excel.

3.1. Прийоми роботи з робочими листами

перейменування робочих листів (ім’я до 31 символу)

1 спосіб: за допомогою стрічки (Главная – Ячейки –Формат – Переименовать лист).

Рис. 4 – Перейменувати лист в MS Excel

2 спосіб: контекстне меню ярлика листа (ПКМ – Переименовать);

3 спосіб: подвійне клацання ЛКМ по ярлику листа;

переміщення робочих листів.

1 спосіб: за допомогою стрічки (Главная – Ячейки –Вставить – Переместить или скопировать лист).

2 спосіб: за допомогою миші (ЛКМ перетягуємо за ярлик листа);

3 спосіб: контекстне меню ярлика листа (ПКМ – Переместить/ скопировать…).

Аналогічно виконується операція копіювання, лише ставиться прапорець «Создавать копию», або затискається клавіша CTRL при виконанні цієї дії за допомогою миші.

У цьому ж розділі можна змінювати колір ярлика робочого листа.

додавання робочих листів;

1 спосіб: за допомогою стрічки (Главная – Ячейки –Вставить – Вставить лист).

Рис. 5 – Вставити лист в MS Excel

2 спосіб: контекстне меню ярлика листа (ПКМ – Добавить – Лист);

3 спосіб: відповідний засіб на панелі ярлика (Комбінація клавіш Shift – F11).

видалення (вилучення) листів (операція незворотня);

1 спосіб: за допомогою стрічки (Главная – Ячейки –Удалить – Удалить лист).

Рис. 6 – Видалити лист в MS Excel

2 спосіб: контекстне меню ярлика листа (ПКМ – Удалить).

виділення групи робочих листів (з метою виконання однакових дій на кількох листах);

суміжних – Shift + ЛКМ по ярличку листа;

несуміжних – Ctrl + ЛКМ по ярличку листа;

якщо необхідно виділити всі листи – контекстне меню ярлика листа (ПКМ – Выделить все листы);

Зняти виділення можна клацаючи ЛКМ по відповідним ярликам листа.

Використовуючи контекстне меню ярлика або стрічку (Главная – Ячейки – Формат – Скрыть или отобразить) можна приховувати або відображати відповідні листи.

Рис. 7 – Формат в Excel

3.2. Прийоми роботи з комірками

Виділення комірок або групи комірок:

1 спосіб: мишкою – метод протягування;

2 спосіб: клавіатура – комбінації клавіш shift + клавіші із стрілочками;

3 спосіб: введенням необхідного діапазону(ів) у вікні із зображенням поточної адреси комірки.

Крім того, можна виділяти несуміжні діапазони комірок, наприклад, мишкою з утриманням клавіші Ctrl.

Виділення рядків (стовпчиків) здійснюється клацанням ЛКМ по заголовку рядка (стовпчика).

Також можна виділяти несуміжні діапазони рядків (стовпчиків), наприклад, мишкою з утриманням клавіші Ctrl.

Є ще можливість присвоєння імені блоку комірок.

Введення (редагування) тексту у комірці:

1 спосіб: безпосереднє введення;

2 спосіб: рядок формул з подальшим натисненням клавіші Enter;

3 спосіб: подвійним клацанням ЛКМ по комірці;

4 спосіб: натиснення клавіші F2.

3.3. Автозаповнення (використовується маркер автозаповнення – маленький чорний квадратик у правому нижньому кутку комірки, при наведенні на який вказівника миші перетворюється на чорний +):

1 спосіб: текстом;

2 спосіб: числами;

3 спосіб: формулами.

Розглянути приклади. При автозаповненні формулами, задати питання: які бувають види посилань? Як змінити один вид на інший?

3.4. Автонумерація

1 спосіб: у комірку А1 внести число 1. Маркером автозаповнення, утримуючи на клавіатурі клавішу Ctrl «розтягнути» вміст комірки. Відпустити спочатку ЛКМ, потім Ctrl.

2 спосіб: у комірку А1 внести число 1. У комірку А2 внести число 2. Виділити комірки А1 і А2. Маркером авто заповнення «розтягнути» вміст виділених комірок.

Розглянути приклади. Використовуючи набуті на лекціях знання спробувати:

Порахувати кількість комірок на листі Excel (за допомогою формули – внести у комірки кількість рядків, стовпчиків і виконати дію множення. За допомогою статистичної функції СЧИТАТЬПУСТОТЫ, її аргументом буде весь лист, щоб виділити – клацнути ЛКМ по комірці на перетині стовпчика з номерами рядків і рядка з назвами стовпчиків). Порівняти отримані результати.

Створити різні таблиу і множення (на 2, 3, … 9).

4. Класифікація та характеристика стандартних функцій

Ми з Вами раніше говорили про обчислення в Excel за допомогою формул. І вводили ці формули вручну. Але Excel має велику кількість спеціальних функцій (>400) в яких ці формули вже як би встановлені.

Функція має ім’я і список аргументів у круглих дужках, розділених знаком ;.

Аргументами можуть бути числові та текстові константи, адреси комірок, діапазони комірок.

Для виклику майстра функцій можна скористатись:

рядком головного меню Вставка/Функции;

натиснути кнопку f_х (Мастер функцій) панелі інструментів Стандартная.

При цьому відкривається перше діалогове вікно майстра функцій.

Тут вибираємо категорію і вид функції потім – кнопку „ОК”.

У другому вікні діалогу вказуємо аргументи через знак ; – потім кнопку „ОК”.

Всі функції умовно поділені на категорії:

фінансові

дата і час

математичні

статистичні

посилання і масиви

робота з базою даних

текстові

логічні

перевірка властивостей і значень.

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

Тепер розглянемо деякі стандартні функції.

Наприклад:

Статистичні:

МАКС (число 1; число 2;...) – повертає максимальне значення із списку аргументів до 30 чисел.

Є аналогічна функція МИН.

МИН (число 1; число 2;...) – повертає мінімальне значення із списку аргументів до 30 чисел.

МИН (3; G5; MAKС(B4:B10))

Дана функція має вибрати мінімальне значення серед таких:

З – числова константа:

G5 – значення комірки G5

МАКС (В4:В10) – максимальне значення з представленого діапазону.

Функція МАКС в даному випадку є вкладеною, а МИН – зовнішньою. Розглянемо ще деякі статистичні функції.

СРЗНАЧ (число 1; число 2; ……..) – рахує середнє арифметичне аргументів.

СЧЕТ (значення 1; значення 2; ....) – рахує кількість чисел у списку аргументів.

З багатьма математичних, у т.ч. тригонометричних функцій ви знайомі. Хотілося б звернути увагу на функцію

СУММ (число 1; число 2; ...) – рахує суму всіх чисел, що входять у список аргументів. Ця функція настільки часто вживається, що винесена у вигляді значка Σ на стандартну панель інструментів.

Хотілося б зазначити що є ще цілий ряд функцій для роботи з матрицями. І ще познайомимось з представниками логічних функцій. Їх всього 6.

ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

З представниками даних функцій ви уже знайомились у минулому. Наприклад, вивчаючи мову програмування Basic, ви розглядали логічні оператори: логічне заперечення (інверсія) NOT – це наша функція НЕ, логічне множення (кон’юнкція) AND – це наша функція И, логічне додавання (диз’юнкція) OR – це наша функція ИЛИ. Крім того, у Basic ви вчили оператор умовного переходу IF – це наша функція ЕСЛИ, а гілочки розгалужень “ТАК”, “НІ” – це наші функції відповідно ИСТИНА, ЛОЖЬ.

Розглянемо функцію ЕСЛИ.

Має такий синтаксис:

ЕСЛИ (лог. _выражение; значение_ если_истина; значение_если_ложь)

Повертає 1-е значення, якщо аргумент лог_выражение при обчисленні дає значення ИСТИНА (так) і 2-е – якщо ЛОЖЬ (ні).

Умовно її можна зобразити так:

Розглянемо приклад 1.

Комірка А1=2 B1 =2

У комірці С1 функція:

С1 = ЕСЛИ (А1=В1; „А1”; В1)

Результат: так як умова не виконується A1 # B1, то вибирається друге значення, тобто В1=2, отже результат С1 = 2.

Приклад 2.

С1 = ЕСЛИ(A1 >< B1; “A1”; B1)

Результат: умова виконується A1 # B1, то вибирається перше значення, тобто „А1”. Отже результат С1 = А1.

Приклад 3.

Нехай комірка G5 = 100

G10 = МИН (3; ЕСЛИ (G5<=100; G5*0.01; 1+(G5-100)*0.02))

Результат: МИН (3;1) (у функції ЕСЛИ виконується умова, тому вибирається перше значення 100 * 0.01=1). Отже результат G10 = 1.

Приклад 4.

G5=150

G10 = МИН (3; ЕСЛИ (G5<=100; G5*0.01; 1+(G5-100)*0.02))

Результат: МИН (3;2) (у функції ЕСЛИ не виконується умова, тому вибирається друге значення 1+(150-100)*0.02 = 1+50*0.02=2). Отже, результат G10 = 2.

Приклад 5.

G5=200

G10 = МИН (3; ЕСЛИ (G5<=100; G5*0.01; 1+(G5-100)*0.02))

Результат МИН (3;3) (у функції ЕСЛИ 1+(200-100)*0.02 = 1+ 100*0.02= 3). Отже, результат G10 = 3.

Зауваження: функція ЕСЛИ допускає до 7 вкладених если (лог_выраж; „значения”) (лог_выраж; „значения”).

5. Типи діаграм та їх призначення

У програмі Excel термін діаграма використовується для позначення всіх видів графічного представлення числових даних. В основі побудови графічного зображення лежить ряд даних.

Ряд даних – це група комірок з даними в межах окремого рядочка чи стовпчика. На одній діаграмі можна відображати декілька рядів даних.

Excel володіє широкими можливостями для побудови діаграм різних типів. Наприклад, таких:

Гістограма

Лінійна

Графік

Кругова

Точкова

З областями

Кільцева

Пелюсткова

Поверхнева

Бульбашкова

Біржова

Циліндрична

Конічна

Пірамідальна.

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

6. Технологія створення діаграм та графіків на основі табличних даних. Можливості для редагування та модифікації створених таблиць

Для того щоб створити діаграму можна скористатися майстром діаграм, який запускається:

Вставка – Диаграмма або клацанням по відповідній кнопці на панелі інструментів.

Порядок створення:

1. Вибрати тип

2. Виділити діапазон даних

3. Задати оформлення діаграми

4. Задати місце розміщення діаграми.

Готову діаграму можна відредагувати. Так як діаграма складається з окремих елементів (ряди даних, осі координат, заголовок діаграми, область побудови), то при клацанні лівою кнопкою миші по відповідному елементу, він виділяється маркерами. Через панель меню формат ці елементи можна змінювати.

7. Управління даними в Excel, їх аналіз за допомогою зведених таблиць консолідованих даних

B Excel для управління великими масивами даних використовуються спеціальні програми, призначені для роботи з базами даних – це так звані функції списку. У вигляді списку можна подати таблицю, рядки якої мають різнорідну інформацію. Наприклад, список адрес, в якому вказані назва і адреса різних фірм або прізвища людей.

Список характеризує не вміст таблиці, а спосіб її організації. Структурними компонентами списку або баз є записи, поля і заголовний рядок.

Запис – повний опис конкретного об’єкта: він містить ряд різнотипних, логічно пов’язаних між собою понять.

Наприклад.

Янц Н.А. 1984 студентка КФЕК гр. Ф-22.

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

Поле – це певна характеристика об’єкта або окремий елемент даних у запису. Кожне поле має своє ім’я, йому відповідають дані одного стовпця. Для ефективного використання операцій над списками доцільно записи розбивати на поля, що містять найдрібніші елементи даних. Наприклад, замість одного поля „Прізвище, ім’я, по батькові” краще задати 3 поля: „Прізвище”, „Ім’я”, „По батькові”.

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

Основні операції над списками (базами даних.):

сортування даних:

за зростанням (спаданням) вибраних полів;

за алфавітом і у зворотньому порядку

за 1-м, 2-ма, 3-мя ключами;

за рядами, стовпцями, днями, місяцями тощо.

пошук даних;

видалення;

вставка.

При пошуку даних використовуються символи групових операцій *, ?

Крім того, широко використовують фільтри.

Переваги:

результат запису можна скопіювати в окрему область таблиці;

одразу використати в обчисленнях;

записи не перевпорядковуються;

результат виконання цієї операції – схов (скрытие) записів, що не відповідають заданому критерію.

Види фільтрів:

автофільтр Данные – Фильтр – Автофильтр;

розширений фільтр Данные – Фильтр – Расширенный фильтр

Excel також дозволяє здійснювати аналіз даних. Для цього застосовують так звані надбудови „надстройки” – засоби, які розширюють можливості програми Excel:

Сервис – Надстройки.

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

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

Данные – Консолидация

Створення підсумкового звіту.

Звіт – наочна форма представлення результатів роботи диспетчера сценаріїв. Звіт містить результати обчислень на основі всіх створених сценаріїв поточного робочого місця. Створюється по окремому робочому місці.

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

 

До складу Excel 2010 входить кілька десятків керуючих програм. Для його розміщення необхідний обсяг не менше 10 – 20 Мбайт пам’яті (залежно від комплектації).

Порядок створення пов’язаного об’єкта на основі існуючого листа або діаграми MS Excel.

1. Відкрити документ Word і книгу MS Excel, що містить дані, необхідні для створення пов’язаного об’єкта.

2. Перейти у вікно MS Excel і виділити весь лист, діапазон комірок або потрібну діаграму.

3. Вибрати команду Правка – Копировать.

4. Перейти у документ Word і вибрати місце вставки пов’язаного об’єкта.

5. Вибрати команду Правка – Специальная вставка.

У діалоговому вікні, що відкриється:

1. Вибрати команду Вставить связь.

2. Із списку Как вибрати потрібний параметр (якщо це зробити важко, то виділити його і прочитати опис у рамці Результат).

3. Вставити прапорець “В виде значка” для відображення пов’язаного об’єкта у вигляді значка.

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

Порядок створення вбудованого об’єкта:

Пункти 1 – 5 такі ж, тільки у пункті 1 відповідно вбудованого об’єкта.

6. Вибрати команду Вставить.

7. Із списку Как вибрати лист MS Excel об’єкт або Диаграма MS Excel об’єкт.

8, 9 такі ж.

Порядок створення нового вбудованого листа або діаграми MS Excel:

1. У документі Word вибрати місце вставки нового впровадженого об’єкта: листа або діаграми.

2. Вибрати команду Вставка – Объект, а у вікні – вкладку Создание.

3. У списку Тип объекта вибрати Лист MS Excel, Диаграмма MS Excel або Диаграмма MS Graph.

4, 5 такі ж як 8, 9.

6. Натиснути кнопку OK.

7. Створити лист або діаграму.

Протягом 15 хв. ознайомтесь і законспектуйте підпункти наведених пунктів плану. Обов’язково писати ті підпункти, призначення яких Вам невідоме, або вони відрізняються у Word.

Давайте обговоримо, що у Вас вийшло. Погляньмо, які пункти меню подібні до програми Word, а які відрізняються? (Новий пункт меню Данные і відсутній – Таблица).

Зверніть увагу на підпункти пунктів меню Вставка, Формат, чи такі ж вони як у Word? На роботу з чим вони націлені?

Погляньмо, які вкладинки подібні до програми Word, а які відрізняються?

3 спосіб: Головне меню (Правка – Заполнить – Прогрессия арифметическая). У комірці має бути вказане початкове значення, а у діалоговому вікні вказати кінцеве, або виділити необхідний діапазон комірок.