Лекція 10 Тема аналіз даних у середовищі табличного процесора Мета заняття



Сторінка1/6
Дата конвертації15.11.2016
Розмір1.38 Mb.
  1   2   3   4   5   6
Лекція 10

Тема

аналіз даних у середовищі табличного процесора

Мета заняття

Описати інтерфейс табличного процесора. Розкрити принципи робати в табличному процесорі: введення даних, форматування, операцій з даними, введення формул та функцій, побудова діаграм..

План

1.Загальні відомості про електронні таблиці

2.Робоча книга Excel

3.Елементи вікна інтерфейсу Excel

4.Операції з робочими книгами.

5.Eлементи вікна робочої книги

6.Форматування клітинок і діапазонів.

7.Робота з аркушами робочої книга

8.Вибір діапазону клітинок

9.Копіювання та переміщення інформації в таблицях

10.Розділення вікна робочої таблиці

11.Робота з рядками і колонками

12. Сортування даних у робочих таблицях

13.Створення та використання формул і функцій Excel

13.1.Формули робочих таблиць

13.2. Відносна адресація клітинок у формулах

13.3. Абсолютна адресація клітинок у формулах

13.4. Змішана адресація клітинок у формулах

13.5. Вмонтовані функції Excel

13.6.Математичні і тригонометричні функції

13.7.Логічні функції

13.8.Функції отримання дати і часу

14.Побудова діаграм






Література


Л1 ст. 256-299, Л2 ст. 379-424

  1. Загальні відомості про електронні таблиці

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

Електронні таблиці дають змогу не тільки швидко перераховувати таблицю, виконуючи досить складні економічні, інженерні, наукові розрахунки, а й аналізувати проблеми типу: "А що буде, якщо . . .?". У багатьох випадках застосування таких програм значно знижує потребу в спеціальному програмному забезпеченні і полегшує перехід до комп`ютеризованої обробки даних для користувача-початківця.



  1. Робоча книга Excel

У багатьох популярних робочих таблицях Windows вже давно використовується те, що мовою Excel називається робочою книгою. Робоча книга (book) - це приклад комп`ютерної моделі, яка імітує реальне життя. Коли ви працюєте над будь-яким проектом, на вашому столі завжди є кілька аркушів паперу з різними цифровими даними, які пов`язані між собою (тобто розробники Excel припустили, що у більшості користувачів є різні, але пов`язані між собою групи цифрових даних, які краще розміщувати не на одному, а на кількох аркушах). У кінці робочого дня ви складаєтє всі ці аркуші в папку і ховаєте її в ящик стола. В Excel кожен з таких аркушів стає окремою робочою таблицею. Ці таблиці помічаються спеціальними ярликами у нижній частині аркуша. Разом аркуші робочих таблиць утворюють робочу книгу (тобто папку, якщо дотримуватись нашої аналогії).

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

Кожна робоча книга Excel може включати до 255 окремих аркушів робочих таблиць, кожен аркуш - близько 16384 рядків і 256 колонк. Крім аркушів робочих таблиць, робоча книга Excel може мати аркуші діаграм (на яких зберігаються діаграми), аркуші модулів (тобто аркуші коду програм, що написані з допомогою Visual Basic for Applications - мовою програмування, яка використовується в Excel), аркуші макросів Excel, а також аркуші діалогових вікон.


  1. Елементи вікна інтерфейсу Excel

   Розглянемо основні елементи вікна інтерфейсу.

Панелі інструментів - на панелях інструментів (toolbars) розміщені кнопки, клацання на яких мишкою викликає на виконання відповідну команду Excel.

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

Поле імени комірки - подає ім`я та адресу активної клітинки в робочій книзі.

Рядок формул - відтворює дані або формули, які вводяться в клітинку.

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

Рядок заголовка - відтворює ім`я програми, з якою працює користувач, і, крім того, на ній розміщені деякі кнопки управління, які можна використовувати для зміни зовнішнього вигляду вікна Excel.

Кнопка закриття - клацання мишкою на цій кнопці закриває вікно Excel та завершує роботу програми.

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

Кнопка мінімізації - згортає вікно Excel, розміщуючи його піктограму на панелі задач Windows 95.

Рядок стану - відтворює різні повідомлення Excel, а також стан клавіш , , .

  1. Операції з робочими книгами.

Створення робочої книги.

Коли користувач починає роботу з Microsoft Excel, відкривається нова робоча книга під назвою Microsoft Excel - Книга1. Спочатку книги містять по 3 чистих аркуші для створення робочих таблиць під назвою Лист1, Лист2, Лист3.

Якщо користувачеві в процесі роботи потрібно створити нову робочу книгу, то це можна зробити одним з із способів:


  • виконати команду Файл та її опцію Создать

клацнути мишкою на кнопці Создать розмішеній на стандартній панелі інструментів.

Відкриття раніше створеної книги.

Щоб відкрити раніше створену робочу книгу, користувач повинен виконати команду Файл та її опцію Открыть. Виберіть потрібний файл та відкрийте його, клацнувши мишкою на кнопку Открыть. Якщо потрібний файл розміщений у іншій папці, потрібно спочатку перейти в цю папку. Для цого знайдіть її в діалоговому вікні і двічі клацніть на неї мишкою.

Отже, у користувача є такі способи відкрити робочу книгу, яка зберігається на диску:


  • виконати команду Файл та її опцію Открыть;

  • клацнути мишкою на кнопці Открыть , розміщеній на стандартній панелі інструментів. 

Збереження книги

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



  • клацнути мишкою на кнопці Сохранить стандартної панелі інструментів;

  • ввести ім`я, з яким користувачеві необхідно зберегти свою робочу книгу, у діалоговому вікні Сохранение документа в полі Имя файла;

  • клацнути мишкою на кнопці ОК

Закриття робочої книги і вихід з Excel

Щоб закрити робочу книгу, виконайте команду Файл та її опцію Закрыть. Якщо користувач вносив у робочу книгу будь-які зміни, які попередньо не були збережені, то при закритті робочої книги Excel запитає, чи бажає користувач зберегти їх (завдяки цьому користувач не забуде зберегти свою роботу при виході з Excel). Вийти з Excel можна, виконавши команду Файл та її опцію Вихід.



  1. Eлементи вікна робочої книги

На малюнку наведене звичайне вікно робочої книги Excel та позначені його основні компоненти. Зазначимо, що вікно робочої книги і вікно інтер­фейсу Excel мають багато одна­кових елементів.

Кнопка выделения всех ячеек рабочей таблицы - клацання мишкою на цій кнопці виділяє всі клітинки активної робочої таблиці.

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

Индикатор активной ячейки (табличный курсор) - це темний контур, який виділяє активну клітинку (одну з 4 194 304 клітинок кожної робочої таблиці).

Заголовки столбцов - кожна з 256 колонк робочої таблиці має заголовок - букву від A до IV. Після колонки Z йде колонка АА, за якою - АВ, АС і т. ін. Після колонки AZ йдуть колонки ВА, ВВ і так до останньої колонки, яка позначена IV. Для виділення всіх клітинок колонки необхідно клацнути мишкою на її заголовку.

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

Кнопка закрытия (выход из программы) - клацання мишкою на цій кнопці закриває робочу книгу та завершує роботу програми.

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

Кнопка минимизации - згортає вікно робочої книги так, що від нього залишається тільки рядок заголовка.

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

Кнопки прокрутки ярлычков листов - дають змогу перегортати (про-кручувати) аркуші робочих таблиць під час їх перегляду.

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

Горизонтальная полоса прокрутки - дає змогу переглядати (прокручувати) аркуш робочої таблиці по горизонталі.

Вертикальная полоса прокрутки - дозволяє переглядати (прокручувати) аркуш робочої таблиці по вертикалі.

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

  1. Форматування клітинок і діапазонів.

Змінювати формат можна у одній комірці або у цілому діапазоні. Виділяється комірка або діапазон комірок,

  • вибирається пункт меню ФорматЯчейки…, або

  • права кнопка на виділеній комірці або діапазоні і пункт з динамічного меню Формат ячеек...

Відкриється вікно форматів з декількома вкладниками:

  • Вкладника Число використовується для надання даним фор­матів, що перелічені у списку Числовые форматы. Тут можна вибрати основний формат і його додаткові параметри.

  • Вкладника Выравнивание використовується для вирівнювання даних по горизонталі та вертикалі (зона ліворуч угорі), для вибо­ру кута розташування даних у комірці (зона праворуч), а покаж­чики, розташовані ліворуч внизу, дозволяють поєднувати комір­ки у суміжному діапазоні, розташовувати текст у комірках в де­кілька рядків та автоматично змінювати ширину стовпчиків, як­що дані не зміщуються у ньому.

  • Вкладника Шрифт дозволяє вибирати параметри шрифта для виділеного діапазону.

  • Вкладника Граница пропонує оформлення виділеного діапа­зону рамками різного типу.

  • Вкладника Вид опрацьовує оформлення тла діапазону різним кольором.

  • Вкладника Защита використовується для захисту змісту ко­мірок у діапазоні (спрацьовує тільки після захисту аркуша).

  1. Робота з аркушами робочої книга

Зміна назви ярличка аркуша таблиці

Якщо користувач працює з робочими таблицями, які організовані в робочу книгу, корисно присвоювати їм імена, які дають уявлення про вміст таблиці. Це справді так, адже назва Лист1 далеко не так інформативна, як назва Налоги. Змінити назву аркуша робочої таблиці неважко, досить клацнути правою кнопкою мишки на будь-якому з ярличків аркуша і з меню, яке з`явилося, вибрати команду Переименовать та ввести нову назву аркуша..



Вибір кількох робочих аркушів відразу

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



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

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

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

Додавання або видалення аркушів таблиць

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



Додавання нового аркуша робочої таблиці

  • Клацнути правою кнопкою мишки на ярлику аркуша, перед якою має з`явитися новий аркуш. У меню, що з`явилося, необхідно вибрати команду Вставка, клацнути мишкою на піктограмі Лист.

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

Видалення аркуша робочої таблиці

  • Клацнути правою кнопкою мишки на ярлику аркуша робочої таблиці, який потрібно видалити. У меню, що з`явилося, необхідно вибрати команду Удалить.

Переміщення аркуша робочої таблиці

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




  1. Вибір діапазону клітинок

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

Для вибору діапазону клітинок можна використовувати також мишку і клавішу :



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

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

У Excel можно робити переривчасте виділення, тобто виділяти діапазони клітинок, які не розмішені поруч. Для цього звичайним способом виберіть перший діапазон. Потім натисніть клавішу i, не відпускаючи її, виберіть другий діапазон (клацніть на першій клітинці і перетягніть курсор мишки). Excel вибирає другий діапазон, не відміняючи при цьому вибір першого діапазону. Наприклад, користувач бажає вибрати два діапазо-ни: В2 - С10 і D12 - E16. Скориставшись запропонованим вище алгоритмом, він здобуває результат, який подано на малюнку.



  1. Копіювання та переміщення інформації в таблицях

Є багато способів переміщення і копіювання інформації в робочих таблицях, які утворюють робочу книгу.

Якщо користувач бажає скопіювати дані з одного місця в інше у межах однієї таблиці або з однієї таблиці в іншу, він повинен виконати такі дії:



  • позначити інформацію для копіювання;

  • клацнути мишкою на кнопці Копировать на стандартній панелі інструментів або виконати команду Правка та її опцію Копировать

  • перейти у першу клітинку, в яку має бути скопійована інформація;

  • клацнути мишкою на кнопці Вставить на стандартній панелі інструментів або виконати команду Правка та її опцію Вставить

Для переміщення даних в інше місце тієї самої таблиці або в іншу таблицю потрібно зробити ось що:

  • позначити інформацію, яку потрібно перемістити;

  • клацнути мишкою на кнопці Вирізати на стандартній панелі інструментів або виконати команду Правка та її опцію Вирізати

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

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

Для копіювання інформації з однієї робочої книги в іншу можна використовувати кнопки Копировать в буфер та Вставить из буфера стандартної панелі інструментів:

  • позначити інформацію, яку потрібно скопіювати;

  • клацнути на кнопці Копировать стандартної панелі інструментів;

  • відкрити робочу книгу, в яку необхідно здійснити копіювання інформації;

  • помістити курсор в клітинку потрібного аркуша робочої таблиці;

  • клацнути мишкою на кнопці Вставити стандартної панелі інструментів.

Для переміщення інформації з однієї робочої книги в іншу потрібно виконати такі дії:

  • позначити інформацію, яку потрібно перемістити;

  • клацнути мишкою на кнопці Вирізати на стандартній панелі інструментів або виконати команду Правка та її опцію Вирізати

  • відкрити робочу книгу, в яку необхідно перенести інформацію;

  • помістити курсор у потрібне місце аркуша робочої таблиці;

  • клацнути мишкою на кнопці Вставити стандартної панелі інструментів.

  1. Розділення вікна робочої таблиці

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

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



  1. Робота з рядками і колонками

Вставляння рядків і колонк у робочу таблицю

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



Для вставляння нового рядка (рядків) користувач може скористатися одним з таких методів:

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

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

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

Видалення рядків і колонк з робочої таблиці

Для видалення рядка або кількох рядків можна скористатися одним з таких методів:



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

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

Дії щодо видалення колонки або кількох колонок робочої таблиці виконуються аналогічно, але використовується команда Правка та її опції Удалить Столбец

Зміна ширини колонок

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

Є кілька способів змінити ширину однієї або кількох колонок.

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

Після виділення необхідних колонок для зміни їх ширини можете виконати одну з таких дій:


  • Встановити курсор мишки на межу між заголовками колонок, щоб курсор набув вигляду двонаправленої стрілки. Перетягувати мишкою праву межу колонки доти, поки колонка не стане потрібної ширини.

  • Виконати команду Формат та її опції Столбец - Ширина і ввести потрібне значення в діалогове вікно Ширина столбца Виконати команду Формат та її опції Столбец - Автоподбор ширины. Ця дія настроює ширину виділеної колонки так, щоб вона відповідала довжині найдовшого рядка даних.

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

Зміна висоти рядків

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

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


  • Встановити курсор мишки на межу між заголовками рядків, щоб курсор набув вигляду двонаправленої стрілки. Перетягуйте мишкою нижню межу рядка доти, поки рядок не стане потрібної.

  • Виконуйте команду Формат та її опції Строка - Высота і введіть потрібне значення (в пунктах) у діалогове вікно Высота строки

  • Виконуйте команду Формат та її опції Строка - Автоподбор высоты

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

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

  1. Сортування даних у робочих таблицях

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

  • за збільшенням значення ключової ознаки;

  • за зменшенням значення ключової ознаки. 

Сортування даних по одній колонкі

Наприклад, необхідно впорядкувати список студентів за алфавітом.

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

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

Сортування даних за двома колонками

Наприклад, необхідно впорядкувати список робітників за номерами цехів, а в кожному цеху - за алфавітом.

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

У разі потреби відсортувати більш ніж одне поле необхідно виконати команду Данные та її опцію Сортировка, при цьому з`явиться діалогове вікно Сортировка диапазона. В списку, що розкривається, Сортировать по виберіть поле і вкажіть порядок сортування (за зменшенням чи за збільшенням значення ключової ознаки). Потім зробіть це саме для другого поля. Якщо необхідно сортувати і за третім полем, визначте останнє в третьому розділі. Якщо активізована опція Идентифицировать поля по Подписям (первая строка диапазона), рядок заголовка колонок не буде впливати на сортування. Клацніть мишкою на кнопці ОК, і рядки швидко перегрупуються.



  1. Створення та використання формул і функцій Excel

    1. Формули робочих таблиць

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

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

Формула, яка введена в комірку повинна починатись зі знака = та може мати будь-які з наступних елементів:


  • оператори (наприклад +, -, *, /, ^);

  • адреса клітинки, включаючи поіменовані клітинки і діапазони;

  • числа;

  • функції робочих таблиць (наприклад, СУММ).

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

Порядок виконання операторів

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

Пріоритет операторів у формулах Excel


    1.  Відносна адресація клітинок у формулах

На рис. відтворена робоча таблиця з формулою, яка введена в клітинку D2. Ця формула, в якій за замовчуванням використовуються відносні адреси клітинок, має такий вигляд:

=В2*С2

Якщо скопіювати цю формулу в дві клітинки, розміщені нижче від клітинки D2, то Excel розмістить у них не зовсім точні копії наведеної формули. Excel змінить їх так:

Клітинка D3 =В3*С3

Клітинка D4 =В4*С4

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

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




    1.  

      А

      В

      С

      D

      Е

      1

      Товари

      Кількість

      Ціна

      Сума

      Сума податку

      2

      Стільці

      16

      125,00

      2000,00

      150,00

      3

      Ліжка

      4

      695,00







      4

      Лампи

      3

      39,95







      5

       













      6

      Ставка податку

      7,50%









      Абсолютна адресація клітинок у формулах

 У деяких випадках необхідно, щоб адреси клітинок копіювалися без змін. На рис. 2.37 наведено приклад, коли в клітинці В6 міститься значення ставки податку на продаж. Формула у клітинці Е2 має такий вигляд:

=(В2*С2)*$В$6 або = D2*$В$6

Зверніть увагу на те, як у цій формулі подано адресу клітинки В6 (попереду адрес колонки В і рядка 6 стоїть знак $ (долар)). Це означає, що адреса цієї клітинки є абсолютною. При копіюванні цієї формули в дві клітинки, розміщені нижче від клітинки Е2, будуть відтворені такі формули:

Клітинка Е3 =(В3*С3)*$В$6 або = D3*$В$6

Клітинка Е4 =(В4*С4)*$В$6 або = D4*$В$6

Відносні адреси клітинок змінились, але адреса клітинки В6 залишилась без змін, оскільки у формулі вона була подана як абсолютна.

Для того, щоб адреса клітинки стала абсолютною, потрібно в рядку введення формул встановити курсор на адресу та натиснути F4.



    1. Змішана адресація клітинок у формулах

 В абсолютній адресі клітинки використовуються два знаки $ (долар). Один - попереду імені колонки, а інший - попереду номера рядка. Однак в Excel є ще і змішана адресація клітинок. В табл. 2.3 подані всі можливі типи адрес клітинок.

    1.  Вмонтовані функції Excel

Excel має велику кількість вмонтованих функцій робочих таблиць, які користувачі можуть застосовувати в своїх формулах. До них належать як досить поширені функції, такі як СУММ (сума), СРЗНАЧ (середнє значення) і КОРЕНЬ (корінь), так і функції, призначені для виконання спеціальних обчислень, наприклад, статистичні функції або прикладні функції. Функції можуть спростити формули і зробити їх зрозумілішими, крім того, вони здебільшого дають змогу робити обчислення, які без них виконати дуже важко.

В Excel входить більше як 300 вмонтованих функцій.



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

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

Для розрахунку середнього значення чисел, розміщених у десяти клітинках (А1:А10), використана така формула:

=(А1+А2+А3+А4+А5+А6+А7+А8+А9+А10)/10

Погодьтесь, що це не дуже зручна формула. Тим більше, якщо виникне потреба ввести в цю формулу нові клітинки, зробити це буде нелегко. Краще замінити цю формулу на одну, дуже просту в користуванні, вбудовану функцію робочої таблиці Excel: =СРЗНАЧ(А1:А10)

Необхідно знайти найбільше значення з чисел, розміщених у діапазоні клітинок (А1:D100). У цьому випадку можна скористатися такою функцією: =МАКС(А1:D100)

Інколи функції дозволяють уникнути ручного редагування. Припустимо, що в робочій таблиці є колонка (наприклад, А), у якій наведений список працівників вашої (установи близько 1000). Всі прізвища введені великими буквами: "ІВАНОВ П. А". Необхідно відтворити цей список так, щоб прізвища мали такий вигляд: "Іванов П. А.". На ручне переоформлення списку потрібно буде витратити кілька годин. Але є можливість значно скоротити час завдяки вмонтованій функції ПРОПНАЧ.

Припустимо, що користувачеві необхідно підрахувати в робочій таблиці комісійні за результатами продажу якого-небудь товару. Якщо продавець продав товару на суму, що перевищує 100 тис. грн., то його ставка комісійних становить 7,5%, якщо на меншу суму, то 5%. Без використання функції користувачеві необхідно створити дві різні формули і правильно їх використати для кожної позиції списку. В цьому випадку необхідно скористатися функцією ЕСЛИ для розрахунку комісійних незалежно від суми продажів. =ЕСЛИ(А1<100000;A1*0,05;A1*0,075)

Aргументи функцій

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



  • без аргументів;

  • з одним аргументом;

  • з невизначеною кількістю аргументів;

  • з необов'язковим аргументом.

Прикладом функції, що не використовує аргумент, є функція СЛЧИС, яка генерує випадкове число в діапазоні від 0 до 1. Синтаксис цієї функції такий (хоч і немає аргументу, дужки обов'язкові):

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

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

Наприклад: =SIN(РАДИАНЫ(В9))

Функція РАДИАНЫ перетворює значення аргумента, яке задане в градусах, на радіани, оскільки в усіх тригонометричних функціях Excel аргументи задаються в радіанах. Тому, якщо в клітинці В9 міститься значення кута в градусах, то функція РАДИАНЫ перетворює його на радіани, а функція SIN обчислює синус кута.

Вкладеність функцій обмежується тільки загальною довжиною рядка формули - 1024 символи.

В багатьох функціях як аргумент використовується посилання на діапазон клітинок. Наприклад, в такій функції використовується діапазон клітинок А10:А20: =СУММ(А10:А20)

Якщо користувач додає новий рядок між рядками 10 і 20, то Excel автоматично розширює діапазон у формулі. Наприклад, якщо користувач додав рядок між рядками 12 і 13, то формула матиме такий вигляд: =СУММ(А10:А21).

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

Ручне введення функцій

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



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

Якщо користувач забув закрити дужки, то Excel зробить це автоматично. Наприклад, якщо була набрана формула =СУММ(А1:С12) і натиснута клавіша , то Excel виправить формулу, додавши праву дужку.



Використання Мастера функций

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

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

  • Виконати команду Вставка та її опцію Функция

  • Клацнути мишкою на кнопці виклику Мастера функцій , розміщеній на стандартній панелі інструментів.

У будь-якому випадку на екрані з'явиться перше з двох діалогових вікон Мастера функцій.

У секції Категория цього діалогового вікна подано список 11 категорій функцій (категорій може бути і більше, якщо використовуються спеціальні функції користувача). При виборі однієї з категорій у вікні списку Функция з'являється перелік її функцій.

В категорії 10 недавно использовавшихся перелічені функції, які використовувались зовсім недавно. В категорії Полный алфавитный перечень включені всі функції з усіх категорій. Цей список доцільно використовувати, якщо відома назва функції, але невідомо, до якої категорії вона належить.

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

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


  • Введіть числа (можна вводити будь-які значення) в діапазон клітинок А1:А6.

  • Встановіть табличний курсор у клітинку А7 (ця клітинка буде вміщати функцію).

  • Виберіть Вставка – Функція. На екрані з'явиться перше з двох діалогових вікон. Користувач побачить, що в рядку функцій з'явиться якась формула.

  • Оскільки функція СРЗНАЧ належить до категорії Статистические, виберіть цю категорію зі списку Категория. У вікні Функция буде подано перелік статистичних функцій.

  • З цього переліку виберіть функцію СРЗНАЧ. Внизу діалогового вікна буде подано перелік її аргументів з коротким описом. Клацніть мишкою на кнопці Далее. Excel відтворить на екрані друге діалогове вікно Мастера функций

  • Активізуйте вікно редагування, позначене як Число1, клацнувши на кнопці в кінці рядка.

У робочій таблиці виберіть діапазон клітинок А1:А6. Цей діапазон з'явиться у вікні редагування. В нижній половині вікна буде подано результат обчислення за цією функцією.

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

Після цього в клітинці А7 з'явиться формула, за якою обчислюється середнє значення чисел, розміщених у клітинках з А1 до А6: =СРЗНАЧ(А1:А6)


    1. Математичні і тригонометричні функції

В Excel входять 52 функції цієї категорії. В неї включені як звичайні функції типу СУММ або ЦЕЛОЕ, так і багато інших, одна з яких може виявитися потрібною користувачеві. Розглянемо деякі з них.

СЧЁТЕСЛИ

Ця функція дає змогу підрахувати, скільки разів у діапазоні клітинок трапляється конкретна величина. Вона має два аргументи: посилання на діапазон клітинок, у якому розміщені значення для підрахунку, і критерій, який визначає, що конкретно потрібно підрахувати.

Наприклад =СЧЁТЕСЛИ(В1:В13;D2) підраховує кількість значень в діапазоні В1:В13, які дорівнюють значенню, яке міститься в комірці D2.

 ЦЕЛОЕ

Функція ЦЕЛОЕ повертає цілу частину числа, при цьому вона відкидає всі цифри, які становлять дробову частину числа. Наприклад, якщо в клітинці А1 розміщене число 412,345, а в клітинку В1 записати формулу =ЦЕЛОЕ(А1), то в клітинку В1 ця формула поверне значення 412.



ОКРУГЛ

Функція ОКРУГЛ округлює число до вказаної кількості десяткових розрядів. Ця функція звичайно використовується для контролю точності розрахунків. Функція ОКРУГЛ має два аргументи: перший - значення для округлення, другий - цифра, яка вказує на точність округлення. Якщо другий аргумент від'ємний, то округлення зміщується ліворуч від коми на задану кількість цифр.



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

КОРЕНЬ

Функція КОРЕНЬ повертає значення квадратного кореня з аргументу. Якщо аргумент від'ємний, функція сприймає його як помилку. В результаті виконання наступного прикладу буде повернуто число 32: =КОРЕНЬ(1024)



Примітка. Для розрахунку кубічного кореня використовується піднесення до степеня (1/3). Наприклад, кубічний корінь з 32768 дорівнюватиме 32 =32768^(1/3) (аналогічно можна обчислювати корені будь-яких степенів).

 СУММ

Якщо взяти навмання кілька робочих книг, то можна переконатися, що найчастіше в них використовується функція СУММ. Ця функція - одна з найпростіших. Вона може мати від 1 до 30 аргументів. Для підрахування суми значень клітинок трьох діапазонів (А1:А10 С1:С10 Е1:Е10) можна використати цю функцію з трьома аргументами: =СУММ(А1:А10;С1:С10;Е1:Е10)

Аргументи не обов'язково мають задаватися в такому вигляді. Наприклад, можна використовувати адреси окремих клітинок, посилання на діапазони тощо: =СУММ(А1;С1:С10;125)

СУММЕСЛИ

Функція СУММЕСЛИ схожа з функцією СЧЁТЕСЛИ, яка була описана раніше, але СУММЕСЛИ підсумовує значення, які розміщені в клітинках, а не підраховує їх кількість. Функція СУММЕСЛИ має три аргументи. Перший аргумент - діапазон клітинок, вміст яких має бути відібраним за якимось критерієм, другий - критерій відбору, третій - діапазон клітинок, значення яких потрібно підсумувати, якщо виконується цей критерій.



    1. Логічні функції

До цієї категорії в Excel віднесено тільки шість функцій.

ЕСЛИ

Ця функція наділяє формули користувача здатністю приймати рішення. В ній використовуються три аргументи. Перший аргумент - це логічний вираз, який може набувати значення ИСТИНА або ЛОЖЬ. Другий і третій аргументи - це такі вирази, які виконуються, якщо перший аргумент набуде відповідне значення ИСТИНА або ЛОЖЬ.

У наступному прикладі формула повертає рядок "Додатний", якщо значення в клітинці А1 більше від нуля, і "Від'ємний" - у противному разі:

=ЕСЛИ(А1>0;"Додатний";"Від'ємний")


    1. Функції отримання дати і часу

В Excel передбачено 14 функцій, що працюють з типом значень Дата и время (Date & Time).

СЕГОДНЯ

Функція СЕГОДНЯ не має аргументу. Вона повертає поточну дату, точніше, дату, встановлену в системі.

Наприклад, якщо користувач працює 20-го травня 1997 р., то наступна формула =СЕГОДНЯ() поверне дату 20.05.97.

ДАТА

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

Наприклад, якщо клітинка А1 вміщує 1997, клітинка В1 - 06 і клітинка С1 - 25, то формула

=ДАТА(А1;В1;С1) поверне дату 25.06.97.

ДЕНЬ

Функція ДЕНЬ визначає день місяця в даті, що вказана. Наприклад, якщо клітинка А1 вміщує дату 25.12.97, то формула =ДЕНЬ(А1) поверне 25.



Примітка. Крім цього, в Excel передбачені функції ГОД і МЕСЯЦ, які виділяють з дати відповідно рік і місяць.

ВРЕМЯ

Функція ВРЕМЯ повертає час залежно від своїх трьох аргументів: годин, хвилин і секунд. Ця функція допоможе скомпонувати час на підставі інформації, вміщеної в робочій таблиці.

Наприклад, якщо клітинка А1 вміщує 8, клітинка В1 - 15, а клітинка С1 - 0, то формула

=ВРЕМЯ(А1;В1;С1) поверне 8:15:00.

ЧАС

Функція ЧАС (HOUR) виділяє з часу кількість годин. Наприклад, якщо клітинка А1 вміщує час 8:15:00, то формула



=ЧАС(А1) поверне 8.

  1. Побудова діаграм

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

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

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

Найчастіше будують кругові, точкові, стовпчикові стандартні діаграми різних видів.

Усі діаграми (окрім кругової) мають дві осі: горизонтальну — вісь категорій, вертикальну - вісь значень. Об'ємні діаграми мають третю вісь - вісь рядів.

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



  • область об'єкта-діаграми;

  • область побудови діаграми;

  • легенда;

  • заголовок діаграми;

  • вісь ряду даних;

  • вісь категорій;

  • вісь значень;

  • ряд;

  • назва осі значень;

  • назва осі категорій;

  • стіни, кути (в об'ємних діаграмах).

Елементи діаграми є об'єктами, над якими визначені дії пере­міщення та дії з контекстного меню. За допомогою контекстного меню найчастіше виконують команду Формат елемента. За її допомогою можна, зокрема, замалювати рамку, в якій є елемент, деяким кольором чи текстурою.

Діаграми будують програмою, яка називається Майстер діаграм, її можна запус­тити двома способами:



  • натисканням на кнопку Майстер діаграм на панелі інструментів;

  • командами з меню Вставка Диаграмма...

Рекомендують перед запуском майстра виок­ремлювати діапазони з даними, які треба графічно відобразити. Це, зазвичай, суміжні рядки чи стовпці (часто з назвами). Щоб ви­окремити несуміжні діапа­зони, потрібно натиснути на клавішу Ctrl.

Під керівництвом майстра виконують чотири кроки.



Крок 1: вибирають тип і вигляд діаграми.

Крок 2: задають діапазони з даними (якщо вони не були вибрані).

Крок 3: задають параметри (підписи, легенду) діаграми.

Крок 4: зазначають куди заносити діаграму (на окрему чи поточну сторінку).

Щоб перейти до наступного кроку, натискають на кнопку Далее, а щоб повернутися назад — на кнопку Назад.

Можна пропустити один чи два кроки, натискаючи відразу на кнопку Далее. Щоб завершити (часто достроково) роботу майстра діаграм і отримати діаграму, натискають на кнопку ГОТОВО.

Зміни у створеній діаграмі можна зробити за допомогою кон­текстного меню елементів, команди Діаграма або панелі інстру­ментів з назвою Діаграма.

Щоб вставити в діаграму опущений елемент потрібно викликати контекстне меню діаграми та вибрати команду Параметры диаграммы…

Тип діаграми можна будь-коли поміняти. Для цього діаграму треба вибрати, викликати майстра діаграм, вибрати інший тип і натиснути на кнопку ГОТОВО.




Питання для самоконтролю

  1. Розкрийте призначення основних елементів вікна табличного процесора.

  2. Розкрийте основні прийоми роботи з аркушами робочої книги.

  3. Розкрийте принципи створення робочої книги, відкриття раніше створеної книги, запису книги на диск..

  4. Розкрийте принципи форматування даних в ЕТ.

  5. Розкрийте принципи копіювання, та переміщення інформації в таблиці.

  6. Як відсортувати дані в ЕТ?

  7. Розкрийте правила написання формул.

  8. Які вбудовані функції Вам віідомі?

  9. Що називають абсолютним посиланням?

  10. Що називають відносним посиланням?

  11. Яке посилання називають змішаним?

  12. Розкрийте принципи побудови діаграм в ЕТ.
  1   2   3   4   5   6


База даних захищена авторським правом ©lecture.in.ua 2016
звернутися до адміністрації

    Головна сторінка