10 клас. Урок 31.Поняття електронної таблиці.Запуск табличного процесора, відкриття й збереження документа. Огляд інтерфейсу табличного процесора.

Теорія Excel

1. Електронна таблиця
Вікно MS Excel містить наступні основні елементи:
Заголовок - рядок у верхній частині вікна додатка або робочої книги. Звичайно він містить заголовок вікна і/або ім'я файлу, відкритого в цьому вікні.
Рядок меню - перелік пунктів меню, розташований під заголовком додатка. Меню – розкриваючийся перелік команд. Команда - функція або дії, обрані з меню, що розкривається.
У верхньому лівому куті екрану розташовані три кнопки.
Кнопка “Згорнути” - ліва кнопка із трьох кнопок керування вікном у правій частині заголовка. Зменшує вікно додатка до кнопки на панелі завдань унизу екрана.
Кнопка “Розгорнути” - середня із трьох кнопок керування вікном у правій частині заголовка. Розвертає вікно додатка на весь припустимий простір
Кнопка “Закрити” - права із трьох кнопок керування вікном у правій частині заголовка. Закриває вікно додатка MS Excel.
Смуги прокручування - горизонтальна й вертикальна смуги, що дозволяють за допомогою миші прокручувати екран по вертикалі й горизонталі.
Рядок стану - рядок у нижній частині вікна додатка, у якому пояснюються обирані команди або даються вказівки та інструкції.
Ярлички робочих листів - ярлички з іменами робочих листів (Лист1, Лист2, і т.д.) поруч із горизонтальною смугою прокручування.
Кнопки прокручування ярличків - кнопки ліворуч від ярличків робочих листів, що допомагають швидко перебирати листи робочої книги.
Рядок формул - рядок, в який вводиться текст, числа або формули. Він розташований нижче рядка меню і панелі інструментів.
Електронна таблиця — сукупність рядків та стовпців. Стовп­ці зазвичай позначені літерами латинського алфавіту, а рядки — цифрами. На перетині рядка і стовпця утворюється комірка. Кож­на комірка мас свою адресу, яка складається з літери стовпця та цифри рядка (А5.В7.К4,...). Рядки: 1...65536, стовпці: A-IV (усьо­го 256).
Сучасні електронні таблиці володіють власними засобами про­грамування, дозволяють здійснювати публікацію в Web, мають за­соби захисту даних.
У пам'яті комп'ютерів можна зберігати електронні таблиці ве­ликого розміру. На екрані можна створювати кілька вікон іa різни­ми частинами таблиці.


2. Табличний процесор
Табличний процесор — це прикладна програма, яка забезпечує: автоматизацію процесу створення, опрацювання, корегування, збе­рігання і виведення на друк документів у формі таблиць. Табличні процесори входять до складу пакетів OpenOfficе, Ashampoo Office, Microsoft Office та ін.
Основна перевага табличних процесорів перед іншими засоба­ми обчислень полягає в автоматичному переобчисленні результатів під час зміни даних.
Порівняно простим у використанні та поширеним є табличний процесор Microsoft Excel, що входить до складу пакета Microsoft Office.
Елементи (об'єкти) головного вікна Excel.
1) Робоча книга. Файл Excel, який містить декілька робочих листів.
2) Робочий лист. Область в Excel, яка складається з комірок. Кож­на робоча книга містить 3 аркуші. Ми можемо змінювати їх кількість.
3) Комірка. У комірки можна вводити будь-яку інформацію: чис­ла, текст, формули.
4) Рядок. Група комірок, які розташовані а один ряд по горизонта­лі. Кожний рядок мас свій номер.
5) Стовпець. Група комірок, які розташовані в один ряд по вер­тикалі. Кожний стовпець позначається однією чи декількома буквами.
6) Адреса комірки. Ідентифікатор, який однозначно визначає комірку шляхом вказування букви, яка задає стовпець, і поряд­кового номера рядка.
7) Ім'я робочого аркуша. Ім'я кожного робочого аркуша відобра­жається на ярличку, який розташований у нижній частині ві­кна Excel.
8) Рядок заголовка програми містить ім'я додатка та документа. У правому верхньому кутку екрана знаходяться два ряди кно­пок. Кнопки верхнього ряду дозволяють змінювати розміри ві­кна самої Excel, а нижнього      її робочих книг
9) Рядок меню. Дозволяє отримати доступ до всіх можливих команд. Саме з неї починається виконання багатьох завдань в Excel.
10) Поле імені і рядок формул. У полі імені відображається ім'я ак­тивної комірки чи діапазону комірок. Рядок формул викорис­товується для відображення та редагування даних, які знахо­дяться в активній комірці.
11) Смуги прокрутки. Дозволяють проглядати ті частини робочого листа, які не видно на екрані.
12) Поле стану. Відображає різні повідомлення і поточний стан ро­бочої книги. Після запуску програми автоматично створюється документ Кпига 1. Інтерфейс Excel багатодокументний. Кожний документ в Excel називається Робочою книгою. Файли Робочих книг мають розширення xls а шаблонів документів — .xlt.

3.  Аркуш
Кожна книги складається з аркушів. За замовчанням книга міс­тить три аркуші (Аркуш 1, Аркуш 2. Аркуш 3). Аркуш, що у цей момент відкритий, називається активний.
Виділена комірка називається активною або поточною. Адреса активної комірки відображається в полі імені.

4.  Комірка, блок
Діапазон стовпців — це вертикальна смуга таблиці. Наприклад, діапазон В:Е — це вертикальна смуга шириною 4 стовпці від стовпця з ім'ям В до стовпця з ім'ям К включно.
Діапазон рядків — це горизонтальна смуга таблиці. Наприклад. діапазон 3:8 — це горизонтальна смуга шириною 6 рядків від 3 до 8 включно.
Блок комірок — це прямокутник, що задається адресою лівої верхньої і правої нижньої комірок. Наприклад, блок ВЗ:К8 знахо­диться на перетині вертикальної смуги В:Е і горизонтальної смуги 3:8, він містить 4x6 = 24 комірки.
5. Властивості комірки
•   Тип та колір фону.
•   Тип, розмір та колір шрифту.
•   Тип. колір і місце проведення межі.
•   Тип вирівнювання.
•   Тип орієнтації тексту.
•   Формат даних.
•   Захист.
6. Можливості програми

  • Введення і редагування даних.
  • Форматування комірок, рядків і стовпців таблиці.
  • Введення формул (автоматизацій обчислень).
  • Використання широкого спектру різних функцій
  • Побудова, редагування й друкування діаграм.
  • Попередній перегляд та друкування таблиці.
  • Створення й уведення без даних.
Отже, великий попит і використання електронних таблиць пов'язаний з  їх універсальністю). адже без обчислень не можна обійтись  у багатьох сферах нашого життя.
Табличні процесори призначені для виконання обчислень, ство­рення діаграм, звітів, розв'язування складних задач у сфері при­родничих, технічних наук та ін.

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

8.Способи редагування даних
Для редагування вмісту комірки можна використати методи:
Ø  поставити курсор на потрібну комірку і натиснути клавішу F2, потім за допомогою клавіш керування курсорнім, клавіш Delete чи Backspace змінити написане;
Ø  двічі швидко клацнути по комірці, потім змінити написане;
Ø  підвести вказівку миші до рядка формул і клацнути в ньому, в самому рядку формул зробити зміни.
Усі зміни підтверджуються клавішею Enter.
Для одночасного виконання об'єднання та вирівнювання по центру існує кнопка на панелі інструментів —Об'єднати і помістити а центрі.
Якщо почати вводити нові дані у комірку, то старі зникають!

9. Збереження електронної книги
Для збереження нового файла використовується діалогове ві­кно збереження документа:
Виберіть Файл → Зберегти. На екран буде виведене діалогове вікно Збереження документа. Введіть у текстовому полі ім'я файла.
Клацніть на кнопці Зберегти.
Якщо треба зберегти старий файл під новим іменем, необхідно вибрати Файл → Збережіть як.,.

10.Копіювання та вставлення даних
Виконання операцій редагування об'єктів електронної таблиці в табличному процесорі Excel здійснюється тими самими спо­собами, що й у текстовому редакторі Word за допомогою команд Копіювати,  Вирізати,   Вставити  меню  Правка,  відповідних кнопок стандартної панелі інструментів, команд контекстного меню об'єктів, сполучення клавіш, а також перетягуванням за до­помогою миші. Виділений об'єкт електронної таблиці можна ско­піювати або вирізати до Буфера обміну (Правка → Копіювати, Правка, Вирізати), а звідти вставити до інших об'єктів (Правка → Вставити). При цьому об'єкт потрапляє до Буфера обміну, а звідти вставляється на нове місце.
Особливості копіювання формул ми розглянемо на наступних уроках.

11. Автозаповнення
 Просте копіювання даних. У першу комірку вволимо дані, ро­бимо її поточною. Наводимо вказівник миші на маркер автозаповнення  утримуючи ліву клавішу миші, протягуємо вказівник у потрібному напрямку.
Заповнення зі списку. У першу і другу комірку вводимо дані (наприклад, 1, 2 або понеділок, вівторок). Надалі виконуємо дії як за простого копіювання.
Аналогічно до введення даних за допомогою маркера автозаповнення вволяться формули. Для заповнення комірок даними можна скористатися командою Заповнити з пункту меню Правка.

12. Видалення об'єктів
Видалення виділених об'єктів (ко­мірок, рядків, стовпців) електронної таблиці здійснюється командою Bидолити... з меню Правка або контек­стного меню цих об'єктів.
Під часвиаонання операції вида­лення комірок відкривається вікно Видалення комірок, у якому треба ви­брати спосіб видалення вибором відпо­відного перемикача.
Якщо вибрати перемикач Комірки зі зсувом ліворуч, то комірка буде видалена разом із даними, а комірка праворуч від неї будуть зміщена вліво. Відповідним чином здійснюється видалення комірок під час вибору перемикача Комірки зі зсувом угору. Під час вибору перемикача Рядок (стовпець) буде видалений цілий рядок (стовпець), у якому знаходилася виділена комірка.
Очистити комірки від введених даних можна аа допомогою кла­віш Delete та backspace.

 13. Формули.
Найпростіші обчислення можна виконати, почавши зі знака « = » і записавши математичний вираз. Приклад: = 40 + 5 * 2. Ре­зультат — у комірці висвітиться число 50.
Обчислення в середовищі електронних таблиць Excel здійснюється за допомогою формул. Формула може містити числові кон­станти, посилання на комірки і функції, з'єднані знаками математичних операцій. Дужки дозволяють змінювати стандартний порядок виконання дій. Якщо зробити комірку активною, то сама формула відображаються в рядку формул.
14. Повідомлення про помилки.
Якщо формула в комірці не може бути обчислена, Microsoft Excel виводить у комірку повідомлення про помадку. Якщо у формулі є посилання на комірку, що містить значення помилки, тоі замість цієї формули також буде виводитися повідомлення про по милку.
Значення помилок:
•   #### — ширина комірки не дозволяє відобразити число в заданому йому форматі;
•   #NAME? (#ИМЯ?) — ця помилка виникає, якщо Microsoft Excel не може розпізнати текст у формулі;
•   #DIV/0! (# ДЕЛ/0!) — ця помилка виникає в разі ділення числа на нуль (0);
•  #VALUE! (#ЗНАЧ!) — ця помилка виникає в разі використання неприпустимого типу аргументу або операнда;
•   #REF! (#ССЬІЛКА!) — ця помилка виникає, якщо посилання на комірку вказано неправильно;
•   #N/A (#Н/Д)       ця помилка виникає, якщо значення недоступне для функції або формули;
• #NUM! (#ЧИСЛО!) — ця помилка виникає, якщо числові значення у формулі або функції неправильні.
15. Діаграми
Діаграми призначені для графічного подання даних на аркуші В Excel існує низка стандартних типів діаграм. Діаграми можна розміщувати прямо в аркуші поряд із даними, які використовувалися для побудови діаграми. Такі діаграми називаються впровадженими. Крім того, діаграма може займати в книзі окрему сторін ку, яка називається діаграмним аркушем.
Незалежно від того, якам чином була створена діаграма, вона завжди зв'язана з даними аркуша. У разі зміни даних діаграм; буде автоматично оновлюватися.
Діаграми можна створювати декількома методами — за допо­могою швидких клавіш, за допомогою Майстра діаграм та панелі Інструментів Діаграма.
Розглянемо створеним діаграми за допомогою Майстра діаграм  який можна викликати командою Діаграма... з пункту меню Вставка, або кнопкою
Розглянемо побудову діаграми на прикладі побудови графіка функції у = х2 на основі табличних даних.
Крок 1. Вибір типу діаграми.
Стандартні типи діаграм, подані в Excel
Тип діаграми
Опис
Гістограма
Дані відображаються у вигляді вертикальних смуг. Найбільш поширений тип. В Excel використову­ється за умовчанням. Подана у такому вигляді: об'ємна, з накопиченням і нормована на 100 %
Лінійчата
Дані відображаються у вигляді горизонтальних смуг. Використовується під час порівняння величин за один проміжок часу та у випадках, коди підписи категорія надто довгі. Подана в такому вигляді: об'ємна, з накопиченням і нормована на 100 %
Конічна, циліндрична і   пірамідальна
Привабливі тривимірні варіанти гістограми та лінійчатої діаграми
Графік
Дані відображаються у вигляді точок, що з'єднані між собою лініями. Цей тип діаграм використову­ється для відображення зміни даних у масі. Подала у такому вигляді: об’ємна, з накопиченням
З областями
Подібна до графіка, тільки область під лінією зафар­бована. Використовується для відображення великої кількості точок. Моє такі різновиди: об'ємна і з накопиченням
Кругова
Відображає один ряд чи категорію даних. Викорис­товується для того, щоб показати, скільки відсотків складає кожна точка даних від загальної величини. Цей тип діаграм поданий такими видами: об'ємна, розрізна, вторинна і колова. Вторинні діаграми використовуються у тих випадках, коли частину елементів необхідно відокремити від загальної суми і подати їх в окремій коловій діаграмі або гістограмі
Кільцева
Схожа па колову. Використовується для відображення відсотка від загальної суми, але для кількох рядів. Дані відображаються у вигляді кілець. Мож­ливе створення розрізної кільцевої діаграми
Точкова
Відображає маркери для кожної точки даних. За­стосовується для відображення розподілу даних, що часто використовуються у наукових роботах. Подана такими видами: тільки  точки; точки, спо­лучені   згладжувальними лініями; точки, сполучені відрізками
Бульбашкова
Нагадує точкову діаграму, у якій для кожної точки відображається мітка. Чим більше значення, тим більша бульбашка. Може бути подана в об'ємному вигляді
Біржова
Використовується для відображення зміни курсу біржових цін. Подана такими різновидами: для вибору з трьох значень (найвищий курс, найнижчий курс і курс закриття), для вибору з чотирьох значень (курс відкриття, найвищий курс, найнижчий курс, курс закриття), ще для одного набору з чотирьох вивчень (об'єм, найвищий курс, найнижчий курс і курс закриття) та для вибору з п'ят н значень (об'єм, курс відкриття, найвищий курс, найнижчий курс і курс закриття)
Контур
Подібно до топографічних карт, ця діаграма відображає підвищення  та западини для сукупності даних. Різновид цього типу діаграм — контур
Пелюсткова
Вісь значень подана променями, що виходять із спільного центра, а точки даних сполучені відрізками, утворюючи структуру, що нагадує павутиння. Можливий варіант діаграми з полями

В Excel, крім 14-ти стандартних типів діаграм, існує ще 20 не­стандартних.
Крок 2. Вибір діапазонів даних.
Крок 3. Встановлення Параметрів діаграм.
Діаграма зберігає зв'язок з даними на аркуші. Якщо дані змі­нюються, автоматично відбувається оновлення діаграми. Кожна діаграма в Excel складається з ряду об'єктів.
Об'єкти діаграми в Excel
Об’єкти
Опис
Область діаграми
Прямокутна область, на якій відобража­ються елементи діаграми
Область побудови діаграми
Прямокутна область, яка обмежена осями
Лінії сітка
Лінії, які починаються з поділок шкали. Як правило, їх додають до осі 
значень, щоб Ідентифікувати значення точок даних
Заголовки
Назва діаграми, назва осі значень, назва осі категорій
Вісь категорій
Вісь, на якій відображаються заголовки а аркуша
Вісь значень
Вісь, на якій розташовуються значення да­них з аркуша. Вона зазвичай вертикальна
Мітки даних
Додаються о діаграму для відображення конкретного значення точки даних
Точка даних
Елемент ряду даних, який відповідає зна­ченню однієї комірки в аркуші
Шкала
Цифрові поділки па осі значень. Мінімаль­не значення дорівнює 0
Мітка ділень
Маленькі лінії, які відображають поділ шкали по осях категорій І значень
Легенда
Текстове поле а описом рядів даних
Ряд даних
Рядок чи стовпець даних з аркуша. Назви всіх рядів наводяться в легенді
Таблиця даних
Таблиця, яка додасться нижче від осі категорій, у якій відображають вхідні дані діаграми
Маркери виділення
У разі клацання на об'єкті біля нього з'являється декілька чорних квадратів, які показують
 що об’єкт вибраний. Об’єкт вибирають для переміщення чи редагування
Звичайно ж, із часом доводиться форматувати готові діаграми. Перехід у «режим форматування елементів» здійснюється через контекстне меню або подвійним клацанням на елементі.

16. Сортування даних.
Якщо виникає потреба впорядкувати дині (розташувати в ал­фавітному порядку, поставити числа в порядку зростання), то для цього на панелі інструментів є відповідні кнопки.
Також можна скористатися командою меню Дані → Сорту сортування...     
Діалогове вікно Сортування діапазону призначене для вибору поля, на якому відбувається сортування. Якщо задано заголовки полів, то як позначення використовуються саме вони, якщо заголовки відсутні, то як заголовки використовуються позначення стовпців. Перше з вибраних полів визначає режим первинного сортуван­ня. Якщо за збігу значень у цьому полі існують додаткові критерії що визначають порядок записів, то можна також задати поля для вторинного і третинного сортування. На кожному з полів сортування може виконуватися в порядку зростання чи спадання.             

17. Фільтрація бази даних
База даних може містити величезне число записів (у програмі Excel природною межею служить максимальне число рядків робочого аркуша — 66536), тому не завжди треба відображати всі ці записи. Виділення підмножини загального набору записів назива­ється фільтрацією.
Найбільш простим способом фільтрації в програмі Excel c вико­ристання автофільтра.
Застосування автофільтра. Включення режиму фільтрації здій­снюється командою Дані → Фільтр → Автофільтр.
При цьому для кожного поля бази даних автоматично створю­ється набір стандартних фільтрів, доступних через списки, що роз­криваються. Кнопки цих списків, що розкриваються, відобража­ються біля поля заголовка кожного стовпця.
За замовчуванням використовується варіант Усі, який вказує, що записи бази даних повинні відображатися без фільтрації.
Варіант Перші  10 дозволяє відібрати визначене число (чи відсо­ток) записів за яким-небудь критерієм. Варіант Умова дозволяє за­діти спеціальну умову фільтрації. Крім того, є можливість добору записів, що мають у потрібному полі конкретне значення.
У разі вибору розширеної фільтрації командою Дані → Фільтр → Розширений фільтр можна виконати фільтрацію на місці чи ви­тягти відфільтровані записи  і помісти  окремо, в  будь-який робочий аркуш будь-якої відкритої робочої книги.

18. Консолідація даних.
Консолідація — це об'єднання значень із декількох діапазонів даних. Наприклад, якщо є аркуш витрат для кожного з відділів фірми, можна скористатися консолідацією, щоб перетворити всі ці дані на корпоративний аркуш витрат.
У Microsoft Excel є декілька способів консолідації даних. Най зручніший метол полягає у створенні формул, які містять посилання на комірки в кожному діапазоні об'єднаних даних.
Види консолідації
•   Консолідація даних із використанням тривимірних посилань.
•   Консолідація даних за розміщенням.
•   Використання Майстра шаблонів.
•   Консолідація даних за категоріями.
•  Консолідація даних шляхом створення звіту зведеної таблиці. Досить корисним в обробці таблиць є знаходження Підсумків, у таблицях. Підсумки використовують для знаходження різних показників (суми, середнього значеним, мінімального та максимального значень тощо). Підсумки розглядатимуться окремо.
19. Функції
1. Функції — це наперед визначені формули, що виконують обчислення за заданими величинами, які називаються аргументами, у вказаному порядку.
Ці функції дають змогу виконувати прості та складні обчислення.

2. Структура функції
Функція починається зі знака рівності (=), за ним іде ім'я функ­ції, дужка, список аргументів, розділених комами, дужками.

3. Ім'я функції
Щоб відобразити список доступних функцій, клацніть комірку та натисніть клавіші SHIFT + F3.

4. Аргументи
Існують різні типи аргументів: число, текст, логічне значення (TRUE та FALSE), масиви, значення помилки (наприклад # N/A) або посилання на комірку. У кожному окремому випадку потрібно використовувати відповідний тип аргументу. Константи, формули або функції також використовуються як аргументи.
Після вводу функції з'являється випливаючими  підказка із синтаксисом  і аргументами.

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

6. Вкладені функції
У деяких випадках може знадобитися використання функції як одного з аргументів іншої функції. У формулах можна використовувати до семи рівнів вкладення функцій. Якщо функція Б аргументом функції А, функція Б знаходиться на другому рівні вкладення.
Назва категорії відповідає змісту, властивостям функцій. Математичні функції дозволяють проводити прості і складні обчис­лення, наприклад обчислення суми діапазону комірок, обчислення суми комірок діапазону, що задовольняють вкладену умову, окру­глення чисел тощо.
Математичні та тригонометричні функції
ABS — повертає абсолютне значення числа.
ACOS — повертає арккосинус числа.
 ASIN — повертає арксинус числа.
ATAN — повертає арктангенс числа.
СОВ — повертає косинус числа.
DEGREES — перетворює радіани на градуси.
ЕХР — повертає число є , піднесене до вказаного степеня.
FACT — повертає факторіал числа.
 FLOOR — округлює число вниз, у бік нуля.
GCD — повертає найбільший спільний дільник.
 INT — округлює число до найближчого меншого цілого.
 LCM — повертає найменше спільне кратне.
 LN — повертає натуральний логарифм числа.
 LOG — повертає логарифм числа за вказаною основою.
 LOG 10 — повертає десятковий логарифм числа.
MOD — повертає остачу відділення.
MROUND — повертає число, округлене з бажаною точністю.
 РІ — повертає число п .
 POWER — повертає число, піднесене до степеня.
PRODUCT — перемножує аргументи.
QUOTIENT — повертає цілу частину частин відділення.
RADIANS — перетворює градуси на радіани.
RAND — повертає випадкове число в інтервалі від 0 до 1.
RANDBETWEEN — повертає випадкове число у вказаному ін­тервалі.
ROMAN — перетворює число, записане арабськими цифрами, на число римськими цифрами у формі тексту.
 ROUND —   округлює число до вказаної кількості знаків.
SIGN — повертає знак числа.
SIN   —   повертає синус указаного кута.
SQRT   — повертає додатний квадратний корінь.
SUM  —  підсумовує аргументи.
 SUMIF —   підсумовує комірки,  що задовольняють указану умову.
SUMPRODUCT — повертає суму добутків відповідних елементів масивів.
SUMSQ — повертає суму квадратів аргументів.
 SUMX2MY2 — повертає суму різниць квадратів відповідних значень у двох масивах.
SUMX2PY2 — повертає суму сум квадратів відповідних значень двох масивів.
SUMXMY2 — повертає суму квадратів різниць відповідних зна­чень двох масивів.
 TAN — повертає тангенс числа.
TRUNC     виділяє дробову частину числа.
Статистичні функції
AVERAGE — повертає середнє арифметичне аргументів.
AVERAGEA — повертає середнє арифметичне аргументів, вра­ховуючи числа, текст і логічні значення.
COUNT — обчислює кількість чисел у списку аргументів.
COUNTA — обчислює кількість значень у списку аргументів.
COUNTBLANK     обчислює кількість порожніх комірок у діапазоні.
COUNTIF — обчислює кількість не порожніх комірок у діапазо­ні, що відповідають указаній умові.
GEOMEAN — повертає середнє геометричне.
МАХ — повертає найбільше значення у списку аргументів.
МАХА — повертає найбільше значення у списку аргументів, враховуючи числа, текст і логічні значення.
MIN — повертає найменше значення у списку аргументів.
MINA — повертає найменше значення у списку аргументів, враховуючи числа, текст і логічні значення.
 SMALL — повертає k –те найменше за величиною значення в су­купності даних.
Також часто використовуються і вивчаються логічні функції. оскільки в деяких задачах значення комірки (чи виразу) залежить від деякої умови.
Логічні умови використовують знаки   >, < , =, >=, <= , <>.
 •IF (логічна умова; дія за умови TRUE; дія за умови FALSE).
•AND(yмова 1; умова 2; умова 3;...)
 •OR (умова 1; умова 2;…)
У комірку можна вставити текстову примітку — певний опис або зауваження щодо її вмісту. Для цього використовується команда меню Вставка → Примітка.
20. Проміжні підсумки.
Microsoft Excel може автоматично обчислювати проміжні та загальні підсумки у списку. У разі вставки автоматичних проміж­них підсумків розмітка списку змінюється таким чином, що можна вмикати та вимикати показ рядків докладних відомостей для кожного проміжного підсумку. Перш ніж вставляти проміжні підсумки, слід відсортувати список, щоб згрупувати рядки за  якими потрібно підбити підсумки. Після цього можна обчислити проміжні підсумки для кожного стовпця, який містить числа.
Якщо дані не мають формату списку або якщо треба вивести ще один підсумок. можна скористатися автосумою замість авто­матичних підсумків.
Для обчислення значень проміжних підсумків використовуєть­ся підсумкова функція, наприклад. Сума (SUM) або Середнє ариф­метичне (AVERAGE).
Проміжні підсумки можна вивести у списку з використанням одночасно декількох типів обчислення.
Загальні підсумки обчислюються за докладними відомостями. а не за значеннями в рядках проміжних підсумків. Наприклад, у разі використання підсумкової функції AVERAGE загальний під­сумок повертає середнє значення для всіх рядків списку, а не для проміжних підсумків.
Значення проміжних і загальних підсумків переобчислюється автоматично після кожної зміни докладних відомостей.
Вкладення проміжних підсумків
У наявні групи підсумків можна вставляти проміжні підсумки для менших груп.
 Перш ніж вставляти вкладені підсумки, слід відсортувати спи­сок за всіма стовпцями, для яких треба обчислити підсумки, щоб рядки для підбиття були згруповані.
21. Підсумкові звіти та діаграми.
Створення підсумкових звітів. У разі додавання проміжних підсумків до списку його структура стає видною. Підсумковий звіт можна створити, клацаючи символи структури таким чином, щоб приховати докладні відомості та залишити самі підсумки.
Показ підсумкових даних на діаграмі.  Можна створити діаграму, яка відображає лише видимі дані у списку із проміжними підсумками. Показ і приховування відомостей у структурованому списку також відбиватиметься на діаграмі.
22. Використання зведених таблиць.
Зведена таблиця — це інтерактивна таблиця, в якій наведені підсумкові значення, отримані за спеціальними формулами з великих масивів даних. Для їх побудови використовують Майстер зведених таблиць діаграм.
Майстер зведених таблиць і діаграм також використовується  для створення звітів зведених діаграм — графічного різновиду зве­дених таблиць.
Щоб запустити Майстер, слід вибрати команді Зведена таблиця в меню Дані.
Коли Майстер відкриється, треба натиснути кнопку Готово. Можна витратити більше часу на роботу з Майстром, але це не обов'язково. Натиснення кнопки Готово повідомляє Майстру, що йому слід діяти стандартно, а саме:

  • використати далі зі списку Excel або з бази даних;
  • підготувати область макета для створення звіту зведеної таблиці;
  • використати усі дані списку;
  • помістити область макета звіту на новий аркуш.
Менш ніж за секунду Майстер підготує новий аркуш з усім, що потрібно для створення зведеної таблиці даних: зі списком полів зведеної таблиці, з якого перетягуються елементи, областю ма­кета, до якої вони перетягуються, і панеллю інструментів Зведена таблиця,
У Списку полів зведеної таблиці перелічено імена стовпців ви­хідних даних: Країна. Продавець. Обсяг збуту. Дата замовлення та Код замовлення.
Кожному стовпцю вихідних даних відповідає поле з таким са­мим ім'ям. Перетягування полів зі списку до області макета дає змогу створити макет зведеної таблиці.
Область макета складається з декількох обмежених рамками областей, призначених для перетягування полів зі списку полів. Для цього область макета перетворюється в звіт зведеної; таб­лиці.
Написи в областях повідомляють, куди слід перетягати далі для забезпечення бажаної орієнтації звіту. Наприклад, якщо перетяг­нути поле Продавець до області з написом Перетягніть сюди поля рядків, кожному продавцеві у звіті буде відведено окремий рядок. Якщо перетягти поле Продавець до області з написом Перетягніть сюди поля стовпців, кожному продавцеві у звіті буде відведено окремий стовпець.
Після визначення використовуваних полів слід з'ясувати, куди має бути перетягнуто ці поля. Щоб прізвище кожного продавця відображалося в окремому рядку, поле Продавець має опинитися в області Перетягніть сюди  поля рядків. Якщо ви відпустите кнопку миші, у макеті з'являться ці прізвища.
Для відображення сум продажу для кожного продавця слід перетягти поле Обсяг збуту до області з написом Перетягніть сюди елементи даних. До цієї області звичайно перетягуються числові дані, оскільки Excel автоматично підсумовує вміст цієї області. Підсумкові цифри відображаються у звіті. Коли поле Обсяг збуту буде перетягнуто до області для елементів даних, замість кольорових рамок області макета з'явиться готовий  звіт зведеної таблиці.
23.  Використання операторів IF у поєднанні а функцією спеціальної вставки.
Зробити це можна так:
Відсортуйте дані за номером рахунку (стовпець А — Account)
Складіть у стовпці С формулу, яка обчислюватиме зростаючий підсумок для кожного рахунку. Наприклад, у комірці С2: =IF(А2=А1;С1+В2;В2).
Складіть у стовпці D формулу, яка визначатиме останній запис для певного рахунку. Наприклад, у комірці D2: = IF (А2=АЗ;ХИБНІСТЬ;ІСТИНА).
Скопіюйте формули в діапазоні C2:D2 до решти рядків нижче.
Скопіюйте C2:D100. Не знімаючи виділення з цього діапазону. виберіть команду Спеціальна вставка в меню Правка, виберіть пункт Значення та натисніть кнопку ОК. щоб змінити формули у комірках C2:D100 на значення.
 Відсортуйте аркуш за стовпцем D зі спаданням.
Тепер для рядків, що мають значення ІСТИНА у стовпці D, ми маємо унікальний список номерів рахунків у стовпці А й остаточний зростаючий підсумок для кожного рахунку у стовпці С.

24. Отримання списку унікальних записів за допомогою розширеного фільтра.
Опишемо спосіб отримання списку унікальних  номерів рахунків.
Виділіть діапазон А1:А100.
У меню Дані виберіть пункт Фільтр, а потім — команду Роз­ширений фільтр.
Виберіть перемикач Скопіювати результат до іншого розташування.
Установіть прапорець Лише унікальні записи.
Виділіть на аркуші порожній діапазон, у якому має міститися унікальний список. Введіть цей діапазон у полі  Діапазон для результату.
Поле Діапазон для результату стає активним, лише якщо вибрано перемикач Скопіювати результат до іншого розташування.
Натисніть кнопку ОК. У зазначеному діапазоні з'явиться уні­кальний список.
25.  Використання команди Консолідація.
Для використання команди Консолідація треба дотримуватися кількох вимог. Номер рахунку має бути ліворуч від числових полів, які треба підсумувати. Над кожним стовпцем мас бути заголовок. Необхідно призначити ім'я діапазону прямокутній області комі­рок. яка містить номери рахунків у лівому стовпці та заголовки у верхньому рядку. У нашому випадку це діапазон А1:В100.
Порядок дій  
Виділіть діапазон А1:В100.
Призначте цій області ім'я, клацнувши поле імені (ліворуч від рядка формул) і ввівши бажане ім'я, наприклад TotalMе. (Та­кож можна вибрати в меню Вставка команду Ім'я.)
Клацніть на вільному місці аркуша.
У меню Дані виберіть команду Консолідація.
У полі Посилання введіть Ім'я діапазону (TotalMe).
У групі Використовувати як імена установіть прапорці й підписи верхнього рядка та значення лівого стовпця.
Натисніть кнопку ОК.
Усі дії можна виконати, послідовно натиснувши сполучення клавіш ALT + D + N (ім'я діапазону), ALT + T, ALT + I.. ЕNTER Якщо діапазон містить 12 стовпців для місяців, ви отримаєте підсумки для кожного місяця.
Якщо треба онов використати функцію Консолідація на тому самому аркуші, необхідно перед цим стерти старе ім'я діапазону в полі Список діапазонів, натиснувши клавішу DEL. Номер рахуй ку має бути розташований ліворуч від числових даних. Цей спосіб трохи повільніший за використання зведеної таблиці, що стає помітно під час обробки наборів даних, більших за 10000 записів.

26. Використання команди Підсумки.
Ця функція дає результати, не дуже зручні для подальшої роботи. І її використання може бути не таким частим, як у функції Консолідація.
Порядок дій
Відсортуйте аркуш an стовпцем А зі зростанням.
Виділіть будь-яку комірку в діапазоні.
У німію Дані виберіть команду Підсумки.
За умовчанням Excel пропонує підсумувати останній стовпець даних. Це прийнятно для вашого прикладу, але часто для вибору необхідних полів необхідно прокрутити список Додати підсумки до.
Натисніть кнопку ОК. Вставте новий рядок у кожному місці, де змінюється номер рахунку й обчисліть проміжний підсумок.
Коли проміжні підсумки буде обчислено, під полем імені зявляються невеличкі кнопки 1, 2 та 3. Натиснувши кнопку 2, ви побачите для кожного рахунку лише один рядок з підсумками. Натиснення кнопки 3 знову відобразить усі рядки.
Дані спочатку необхідно відсортувати. Обробка великих масивів даних може бути сповільненою. Щоб перемістити підсумки до ін­шого розташування, треба виділити лише видимі комірки, для чого вбрати команду Перехід (меню Правка) та натиснути кнопку Виділити. Для повернення до вихідних даних треба вибрати команду Підсумки (меню Дані), я потім натиснути кнопку Прибрати все.

27. Використання зведеної таблиці.
Зведені таблиці - найбільш гнучке з усіх пропонованих рішень. Вони не потребують сортування даних. Числові стовпці можуть міститися як ліворуч, так і праворуч від номерів рахунків. Обчис­лення можна без утруднень поширити вниз або вздовж сторінки.
Порядок дій:
Виділіть будь-яку комірку в діапазоні.
У меню Дані виберіть команду Зведена таблиця. Натисніть кнопку Далі, щоб прийняти настройки, запропоно­вані на кроці 1. 
Перевірте, чи правильно зазначено діапазон даних на кроці 2, і натисніть кнопку Далі
 На кроці 3 виясніть кнопку Макет.
У діалоговому вікні Макет виділіть і перетягніть кнопку Account (Paxyнок) зі списку праворуч до області рядків.
Перетягніть кнопку Amount (Сума) зі списку праворуч до об­ласті Даних
Натисніть кнопку ОК.
Укажіть, де мають бути розташовані результати (на новому ар куші або в певному розділі на існуючому аркуші) і натисніть кнопку Готово.

Немає коментарів:

Дописати коментар

Підписатися на: Дописи (Atom)