Теоретична довідка до ПР 16 на тему Табличний процесор MS EXCEL 2010

Теоретична довідка до ПР №16 Табличний процесор MS EXCEL 2010

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

Найпопулярнішим з табличних процесорів є Microsoft Excel.

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

ЗАВАНТАЖЕННЯ  MICROSOFT  EXCEL 2010

Є декілька способів завантаження електронної таблиці, зокрема:

1. Через кнопку "Пуск" вибрати пункт "Все Программы", відкрити папку "Microsoft Office" і  вибрати "Microsoft Excel 2010".

2. Відкрити файл з розширенням .xlsx, знаходячись у середовищі програм "Проводник" або "Мой компьютер".

ВІКНО  MICROSOFT EXCEL 2010

Вікно програми Microsoft Excel містить ряд типових елементів:

– панель швидкого доступу,

– вкладки стрічки,

– групи вкладки, наприклад група Шрифт на вкладці Главная,

– запускачі діалогових вікон,

– рядок формул.

Загальний вигляд вікна MS EXCEL 2010

У таблиці показано призначення вкладок стрічки.

ВІКНА КНИГ. РОБОТА З АРКУШАМИ КНИГ

Документи, що створюються у середовищі Excel, називають книгами. Робоча книга складається з робочих аркушів, імена яких ("Лист1", "Лист2", …)виведені на ярликах у нижній частині вікна робочої книги. Натискаючи по ярликах, можна переходити від аркуша до аркуша усередині робочої книги.

Для прокручування ярликів використовуються кнопки:

Аркуші можна додавати, перейменовувати, переміщувати, видаляти.

Робочий аркуш являє собою таблицю, що складається із стовпців і рядків. Стовпці позначаються латинськими літерами, а рядки – цифрами. Кожна комірка таблиці має адресу, що складається з імені рядка й імені стовпця. Наприклад, якщо комірка знаходиться в стовпці F і рядку 7, то вона має адресу F7.

Одна з комірок таблиці виділена темною прямокутною рамкою. Це так званий табличний курсор. Адреса активної комірки виводиться у лівій частині рядка формул.

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

ВВЕДЕННЯ ДАНИХ

У комірках таблиці можна розміщувати текстові дані, числові дані, формули та функції.

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

ТИПИ ДАНИХ

Комірка може бути порожньою або містити один з таких типів даних:

– число, включаючи дату і час;

– текст;

– формулу.

Можливі такі основні форми числа: ціле, дійсне, з експонентою, дробове. Для цілого числа допустимо використовувати цифри 0 ... 9, а також знаки + і -.  Дійсне число включає додатково кому, яка розділяє цілу і дробову частини.  Для введення дробового числаслід ввести цілу частину числа, потім символ пропуску, далі чисельник, символ / і знаменник. Якщо ціла частина числа відсутня, то слід ввести на її місце 0.

Дата вводиться у форматі ДД.ММ.РР, а час – у форматі ГГ:ХХ. Можна поєднати в одній комірці дату і час.

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

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

Якщо при обчисленні формули сталася помилка, то в комірку виводиться повідомлення про помилку, яке починається із символу "#". Види помилок:

 # дел 0 – спроба поділити на нуль або на порожню комірку;

 # имя ? – формула використовує неіснуюче ім'я;

 # н/д – формула посилається на чарунку з невизначеними  даними;

 # число! – помилка у числі, число неможливо подати в Excel;

 # ссылка ! – формула посилається на неіснуючу комірку;

 # знач ! – помилка при обчисленні функції.

Якщо в результаті введення числа або обчислень за формулою комірка заповнюється символами "#", то це означає, що ширина колонки недостатня для виведення значення.

РЕДАГУВАННЯ ДАНИХ

Дані, введені у комірку, можна змінити, замінити або видалити.

Заміна даних:

– виділити комірку,

– ввести нове значення,

– натиснути клавішу [Enter].

Редагування даних:

1 спосіб

– виділити комірку,

– натиснути клавішу [F2],

– внести зміни і натиснути клавішу [Enter].

2 спосіб

– двічі натиснути ліву кнопку мишки на потрібній комірці,

– внести зміни і натиснути клавішу [Enter].

3 спосіб

– виділити потрібну комірку,

– внести зміни у рядку формул і натиснути клавішу [Enter].

Видалення даних:

1 спосіб

За допомогою кнопки , розташованої на вкладці Главная в групі Редактирование, можна вибрати спосіб видалення:   

2 спосіб

Якщо виділити комірку і натиснути на клавішу [Delete], вилучається тільки зміст, а формати лишаються.

ВИДІЛЕННЯ ДІАПАЗОНУ КОМІРОК

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

Діапазони бувають суміжні і несуміжні. У суміжних діапазонах між комірками немає проміжків і діапазон має форму прямокутника, а у суміжних проміжки можуть бути. Суміжні діапазони позначаються адресою лівої верхньої комірки діапазону, двокрапкою та адресою правої нижньої комірки діапазону, наприклад, А1:В8.

Несуміжні діапазони складаються з декількох суміжних і позначаються їхніми адресами, розділеними крапкою з комою, наприклад, А1:С6; К3; F5:G7.

Способи виділення:

– для виділення суміжного діапазону комірок слід клацнути курсором по одній з кутових комірок діапазону і протягнути курсор при натиснутій лівій кнопці миші по решті комірок діапазону,

– при використанні клавіатури для виділення діапазону слід, утримуючи клавішу [Shift], клавішами переміщення курсора виділити потрібний діапазон,

– для виділення стовпця або рядка слід клацнути мишею по заголовку стовпця або номеру рядка,

– для виділення декількох стовпців або рядків слід натиснути клавішу [Ctrl] і, не відпускаючи її, клацнути по стовпцях і рядках, які треба виділити,

– для виділення несуміжного діапазону комірок слід, утримуючи натиснутою клавішу [Ctrl], пересувати курсор миші по комірках, які потрібно виді­лити.

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

Приклад: на малюнку показаний несуміжний діапазон A2:A4;B3;C4:C5;D3

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

РОБОТА 3 ЕЛЕМЕНТАМИ ТАБЛИЦІ

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

1. Додавання:

– команда "Вставить ячейки",

– команда "Вставить строки на лист" чи "Вставить столбцы на лист".

2. Вилучення:

– команда "Удалить".

3. Зміна ширини (для поточного стовпчика):

–  пункт меню "Формат"/"Автоподбор ширины", або

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

Автоматичне регулювання ширини провадять у випадках, якщо:

– текстові дані виходять за межі комірки,

– числові дані показані з округленням, а потрібні точні значення,

– замість числових даних у комірці містяться знаки ##### ,

– так бажає користувач.

4. Зміна висоти (для поточного рядка):

– пункт меню "Формат"/"Автоподбор ширины", або

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

ФОРМАТУВАННЯ КОМІРОК І ДІАПАЗОНІВ

Форматуванням комірки називають встановлення вигляду відображення вмісту комірки. Для форматування комірки або виділеного діапазону комірок використовують команду "Формат" або кнопки панелі інструментів "Форматирование".

Поняття формату включає такі параметри: шрифт (тип, розмір, накреслення, колір); формат чисел; спосіб вирівнювання; розміри (ширина і висота) комірок; обрамлення комірок; візерунок фону.

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

– Тип і розмір шрифта. Для встановлення типу і розмірів шрифту використовують закладку "Шрифты". На цій закладці можна вибрати тип шрифту, його накреслення і розмір, колір символів. У полі"Образец" відображається вигляд символів для встановлених параметрів. Відповідні параметри можна встановити і кнопками групи  Выравнивание на вкладці Главная.

– Формат чисел. Існує можливість встановити для виділеного діапазону формати чисел. Їх можна вибрати зі списку"Числовые форматы" закладки "Число". Залежно від вибраного формату числа у правій частині закладки виводиться ряд опцій, які дозволяють встановити параметри для вибраного формату. У полі"Образец" наведено вигляд числа активної комірки для вибраного формату. Ряд форматів чисел можна вибрати за допомогою групи  Число на вкладці Главная.

– Вирівнювання даних у комірках. За замовчуванням Excel вирівнює вміст комірки по нижньому краю, при цьому текст – по лівій межі колонки, число – по правій. Користувач може змінити ці установки, використовуючи закладку "Выравнивание" або відповідні кнопки групи  Выравнивание на вкладці Главная.Крім опцій горизонтального і вертикального вирівнювання ця вкладка дозволяє встановити переноси слів у комірках (прапорець"Переносить по словам"), поєднувати комірки у суміжному діапазоні (прапорець"Объединение ячеек"), а також встановити орієнтацію тексту (одна з чотирьох опцій у полі"Ориентация").

– Обрамлення виділеного діапазону. Обрамлення виділеного діапазону комірок здійснюється встановленням параметрів вкладки "Граница". Вкладка дозволяє встановити форму рамки (кругом, зліва, справа, зверху, знизу), тип і колір лінії рамки. Встановити обрамлення можна також за допомогою відповідної кнопки панелі інструментів "Форматирование".

– Встановлення фону. Для зміни кольору або візерунка виділеного діапазону комірок слід на вкладці "Вид" вибрати візерунок і колір. Excel дозволяє використовувати графічне зображення як фон для листа. Для встановлення фону листа слід виконати команду "Формат"/"Лист"/"Подложка". При цьому відкривається діалогове вікно, яке дозволяє вибрати файл формату графіки. Графіка цього файла і буде використовуватись як фоновий візерунок.

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

Автоформат. Excel може автоматично встановити найдоцільніший формат для активної комірки або виділеного діапазону. Для цього слід виконати команду "Форматировать как таблицу" вгрупі  Стили на вкладці Главная..

Стиль форматування. Поняття стилю включає всі атрибути форматування: формат даних, шрифт, вирівнювання, обрамлення, візерунки, захист. За замовчуванням усі комірки мають стиль"Обычный".

АВТОЗАПОВНЕННЯ ДІАПАЗОНІВ ДАНИМИ

Excel дозволяє автоматично заповнювати діапазони, щоб уникнути процедури введення даних вручну.

І. Для заповнення рядка чи стовпця повторюваними значеннями необхідно:

– ввести в комірку значення, яким потрібно заповнити рядок чи стовпець,

– знову клацнути цю комірку,

– підвести покажчик миші до маркера заповнення – квадрата у правому нижньому куті комірки (покажчик миші прийме вид хрестика),

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

Увага! Інколи слід виконувати операцію автозаповнення при натиснутій клавіші CTRL.

ІІ. Арифметичною прогресією називається послідовність чисел, у якій наступне число відрізняється від попереднього на те саме значення (1,2,3,4,… чи  9,6,3,0,-3,…)... Автозаповнення арифметичною прогресією зручно використовувати для нумерації списків.

Для заповнення комірок стовпця чи рядка арифметичною прогресією необхідно:

– виділити першу комірку діапазону, що потрібно заповнити, і ввести початкове значення,

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

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

– протягнути маркер заповнення вправо (для заповнення рядка) чи вниз (для заповнення стовпця).

ІІІ. Щоб заповнити кілька комірок підряд у стовпці чи рядку послідовними датами, необхідно:

– ввести в комірку першу дату,

– клацнути цю комірку,

– протягнути маркер заповненнявправо (для заповнення рядка) чи вниз (для заповнення стовпця).

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

ІV. Excel дозволяє автоматично заповнювати комірки рядка і стовпця послідовностями виду:

Квартал 1, Квартал 2, Квартал 3, Квартал 4

Розділ 1, Розділ 2, Розділ 3, Розділ 4 ....

Питання 1, Питання 2, Питання 3 ... і т. ін.

ІV. Щоб заповнити кілька комірок підряд у рядку чи стовпці комбінаціями тексту і чисел, необхідно:

– ввести у комірку першу комбінацію,

– клацнути цю комірку,

– протягнути маркер заповненнявправо чи вниз.

ВІДНОСНІ, АБСОЛЮТНІ, ЗМІШАНІ АДРЕСИ

Адреси клітинок вигляду В3 чи С3 називаються відносними. В Еxcel є можливість копіювати однотипні формули, що прискорює розв'язування задач.

Під час копіювання формули відбуваються такі дії:

– формула вводиться в інші комірки автоматично;

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

Наприклад, під час копіювання формули = В3*С3 з третього рядка у четвертий формула в четвертому рядку набуде вигляду = В4*С4.

Увага! Якщо у комірці з формулою = В3*С3  виділити адресу В3 і натиснути клавішу F4, то формула набуде вигляду = $В$3*С3, при цьому:

– адреса $В$3 буде називатись абсолютною;

– при копіюванні формули = $В$3*С3 в четвертий рядок вона набуватиме вигляду = $В$3*С4. Тобто абсолютні адреси при копіюванні формул не змінюються.

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

ВИКОРИСТАННЯ АВТОФУНКЦІЙ

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

1. Для того щоб отримати суму в комірках стовпця або рядка таблиці, необхідно:

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

– клацнути кнопку S в групі Редактирование вкладки Главная або в групі Библиотека функций вкладки Формулы.

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

– натиснути клавішу [Enter] або клацнути зелену галочку в рядку формул.

2. Так само слід діяти, щоб отримати середнє, максимальне, мінімальне значення в комірках стовпця або рядка таблиці.

Теоретична довідка до ПР №17 Вбудовані функції

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

Загальний вигляд функції:

=ім'я функції(параметри)

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

Роботу з функціями полегшує спеціальна програма Майстер функцій, яку можна запустити, клацнувши на кнопку , розташовану на вкладці Формули в групі Библиотека функций або в рядку формул.

Майстер має 2 кроки для вибору та виконання функції.

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

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

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

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

Якщо всі дії були правильними, можна побачити отримане значення. Для остаточного виконання функції натискається[Ok] і результат з'явиться у комірці.

ЛОГІЧНІ ФУНКЦІЇ. ФУНКЦІЯ "ЕСЛИ"

Функція "ЕСЛИ" дозволяє при обчисленні значення комірки перевірити вміст інших комірок і в залежності від результату перевірки вибрати той або інший варіант.

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

Щоб визначити значення комірки з використанням функції "ЕСЛИ", необхідно:

– клацнути комірку, в яку буде поміщене шукане значення;

– клацнути кнопку [f_x] на панелі інструментів. Відкриється діалогове вікно "Мастер функций – шаг 1 из 2";

– у списку "Категорія" клацнути "Логические";

– у списку "Функція" клацнути функцію "ЕСЛИ";

– клацнути кнопку [Ok];

– у вікні функції клацнути поле з назвою "Лог_выражение". Ввести логічну умову – вираз, що може набути значення "ИСТИНА" або "ЛОЖЬ". Логічна умова може містити посилання на комірки, числа, текст, операції порівняння – < , > , = , >=, <= і т. ін.;

– у полі "Значение_если_истина" ввести значення, яке повинне бути введене в комірку, якщо логічна умова виконується;

– у полі "Значение_если_ложь" ввести значення, що повинне бути введене в комірку, якщо логічна умова не виконується;

– натиснути кнопку [Ok].

Прості умови записують, як в алгоритмічних мовах, а саме за допомогою операцій порівняння =, >, <, <=, >=, наприклад, 7>5, А5<=20 тощо. Складні умови записують за допомогою логічних функцій И(< умова 1>;<умова 2>; ...) та ИЛИ(<умова1>;<умова 2>;...). Функція И (AND) істинна, якщо всі умови в її списку істинні. Функція ИЛИ (OR) істинна, якщо хоч би одна умова в її списку істинна. Наприклад, функція ЕСЛИ(ИЛИ(5>7; 5<7); 5; 7) отримує значення 5, а функція ЕСЛИ( И (5>7; 5<7); 5; 7) — значення 7.

Теоретична довідка до ПР №18 Побудова діаграм

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

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

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

Приклад: На основі даних таблиці побудувати гістограму кількості заявок.

На першому кроці користувач повинен виділити числові дані стовпця «Кількість заявок» і на вкладці Вставка в групі Диаграммы вибрати кнопку Гистограмма. Із запропонованого списку вибрати за уподобанням вид гістограми. Результатом буде наступний малюнок.

Якщо виділити отриману гістограму, то з'являється вкладка Работа с диаграммами, яка має три вбудовані вкладки Конструктор, Макет, Формат.

На вкладці Конструктор слід натиснути кнопку Выбрать данные.

У діалоговому вікні Выбор источника данных виконати підписи для ряду даних і осі Х:

– натиснути на кнопку  і в діалоговому вікні Изменение ряда в полі Имя ряда клацнути по комірці з текстом Кількість заявок, натиснути ОК;

– натиснути на кнопку  і в діалоговому вікні Подписи оси в полі Диапазон подписей оси виділити всі комірки з назвами країн, натиснути ОК.

Результатом має стати наступний малюнок

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

Приклад використання діаграм в завданнях на побудову графіків:

Теоретична довідка до ПР №19 РОБОТА ІЗ СПИСКАМИ ДАНИХ

Списком називають набір рядків таблиці, що містить пов'я­зані між собою дані.

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

Під час створення списку – бази даних у середовищі Microsoft Excel слід дотримувати певних правил:

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

– список не може містити порожніхрядків (стовпчиків); 

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

– бажано, щоб формати заголовків полів відрізнялися від форматів записів;

– не може бути порожнього рядка або навіть порожньої комірки між заголовками полів і записами;

– в усіх рядках списку в однакових стовпчиках мають бути однотипні дані.

Наприклад, наведена таблиця може бути списком – базою даних:

Назви полів знаходяться у другому рядку робочого аркуша, а самі записи бази містяться в рядках з 3 по 12.

СОРТУВАННЯ ДАНИХ

Для сортування даних таблиці слід виділити таблицю або клацнути в неї курсором. Далі командисортування в порядку зростання або спадання можна знайти:

– на вкладці Дан­ные в групі Сортировка и фильтр, або

– на вкладці Главная в групі Редактирование.

ПОШУК ДАНИХ

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

СТВОРЕННЯ ЗАПИТІВ ДЛЯ ПОШУКУ ДАНИХ ЗА ДОПОМОГОЮ ФІЛЬТРІВ

У разі роботи з базою даних слід пам’ятати, що стовпці називають полями, а рядки називають записами.

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

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

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

Використання Автофільтра

Виділяється список, вибирається вкладкаДан­ные,далікомандаФильтр. Тоді в усіх назвах полів списку розташується кнопка зі стріл­кою: .

При натисненні на цю кнопку у вибраному полі списку відкривається меню:

– пункт «(Выделить все)» виводить усі значення з позначеного поля;

– пункт «Числовые фильтры» пропонує деякі прості критерії для відбору числових даних;

– пункт «Текстовые фильтры» пропонує деякі прості критерії для відбору текстових даних;

–  в пунктах «Числовые фильтры», «Текстовые фильтры» є також команда «Настраиваемый фильтр», яка дозволяє створювати складений критерій по вказаному полю, де можна зазначити одну умову – тоді заповнюється тільки перший рядок, або дві умови – заповнюється і перший, і другий рядок.

Кнопка "И" використовується, коли потрібно одночасне виконання обох введених умов, а кнопка "ИЛИ" – у випадку, коли має виконуватися одна з двох умов. При виборі одного з наведених значень список буде відфільтрований за простим кри­терієм – вказаним значенням.

При занесенні умов можна використовувати знаки ? і *. Знак ? замінює під час пошуку один будь-який текстовий сим­вол, а знак * – будь-яку кількість будь-яких текстових симво­лів.

Після встановлення фільтра стрілка на кнопці набуває вигляду фільтра – це ознака того, що дані у списку відфільтровані.

Для відновлення показу всіх записів списку можна поступо­во із меню кнопки зі стрілкою у полях, де визначався критерій, вибирати пункт "Все", або одразу вибрати на вкладціДан­ные повторнокомандуФильтр.

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

Приклад: У таблиці нижче треба відшукати дані про Фірми, в яких кількість груп більше ніж 20 або менше ніж 10.

1. Спочатку треба встановити курсор у таблицю і виконати на вкладціДан­ные командуФильтр.

2. Натиснути кнопку  у полі"Кількість груп" і вибрати пункт «Числовые фильтры», «Настраиваемый фильтр». Встановити наступні умови:
   

3. Після натиснення кнопки [Ok] будуть показані результати запиту:

ВИКОРИСТАННЯ РОЗШИРЕНОГО ФІЛЬТРА

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

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

Складний критерій містить у собі декілька простих крите­ріїв, сполучених або по вертикалі – декілька умов на одне по­ле, або по горизонталі – по одній умові на декілька полів.

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

Критерії, сполучені по вертикалі, поєднуються зв'язкою "ИЛИ", тобто записи зі списку за таким критерієм вибираються, коли хоча б один із простих критеріїв, що входять у складний критерій, виконується. У таких критеріях допускається заголо­вок поля заносити тільки один раз.

Складений критерій – це сукупність декількох складних критеріїв – містить по декілька умов на декілька полів.

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

Після створення критеріїв можна використовувати Расши­ренный фильтр для організації запитів. Розширений фільтр ви­кликається на вкладціДанныев групіСортировка и фильтркомандоюДополнительно. З'явиться діалогове вікно розширено­го фільтру.

У цьому вікні автоматично позначиться діапазон списку (зона "Исходный диапазон"), якщо список був попередньо виді­лений або курсор мишки розміщувався на будь-якому із запи­сів списку.

У зону"Діапазон условий" треба занести адреси потрібного діапазону критерію і, якщо натиснути на [Ok], у списку будуть показані тільки ті записи, що відповідають уведеному критерію.

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

При повторній фільтрації будуть проглядатися усі рядки, і приховані і відкриті.

Приклад

Нижче наведені приклади створення критеріїв для пошуку такої інформації:

1. Критерій 1 – вибрати зі списку всі записи про фірму "САМ". За правилами створення критеріїв діапазон для них буде розміщуватися у робочому аркуші по рядках.

Такий запит включає одну умову (назву фірми – "САМ"), тому створюється простий критерій для пошуку в полі Фірма/місто фірми "САМ".

У комірку G1 копіюється заголовок поля Фірма/місто, а в комірку G2 – умова для пошуку – "САМ" - Київ:

Таким чином, діапазон створеного простого критерію роз­міщується у комірках G1:G2.

2.  Критерій 2 – вибрати зі списку всі записи, що стосуються або фірми "САМ", або "Вояж", або "Венея".

Такий запит включає три умови, що накладаються на поле Фірма/місто (фірма – або "САМ", або "Вояж", або "Венея"), тому створюється складний критерій із трьох простих, які поєднані зв'язкою "ИЛИ".

У комірку GЗ копіюється умова для пошуку – "Вояж"- Донецьк, а в комірку G4 – " Венея "- Донецьк:

Діапазоном створеного складного критерію будуть комірки G1:G4.

3. Критерій 3 – вибрати зі списку всі записи, де кількість груп більше ніж 10 і менше ніж 25.

Такий запит включає дві умови, тому створюється критерій для пошуку в полі Кількість груп, які поєднані зв'язкою "И".

У комірки G1 і H1 копіюється заголовок поля Кількість груп, а в комірку G2 – умова для пошуку  >10, а в комірку H2 – умова для пошуку <25:

Діапазоном створеного складного критерію будуть комірки G1:H2.

Нижче розміщені дані для фільтрації і результати запитів.

ЗВЕДЕНІ ТАБЛИЦІ

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

Теоретична довідка до ПР №20 Використання засобів оптимізації 

Зa допомогою команди Підбір параметра на вкладціДанные, група Работа с данными, піктограма  Анализ "что если"можна визначити невідоме значення, яке буде давати бажаний результат. Підбірпараметра (підстановка даних) – метод пошуку значення комірки заданої формули. При виконанні процедури підбору параметра значення вказаної комірки варіюються доти, доки залежна формула не поверне шуканий результат. Процедуру підбору параметра слід використовувати для пошуку особливого значення окремої комірки, при якому інша комірка набирає відомого значення.

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

Інша задача: яку встановити тарифну ставку (параметр) дванадцяти працівникам, щоб вкластися в запланований бюджет (функція) 1000 грн? Нехай А1 — адреса клітинки, що містить значення тарифної ставки, f(А1) – запланований бюджет, В1 – адреса комірки, в яку записано функцію f(А1), тоді

f(А1) = В1 — задане рівняння,

Бюджет(А1)=12*А1=1000.

Метод підбору параметра полягає в тому, що програма для будь-якого рівняння обчислює значення параметра А1. Алгоритм дій користувача такий. Спочатку потрібно в комірку В1 записати формулу = f(А1), далі виділити цю комірку і на вкладці Данные через кнопку Анализ «что если» запустити програму Подбор параметра. Отримаємо діалогове вікно, у якому треба заповнити три поля.

Далі слід натиснути ОК і у комірці А1 отримаємо шуканий результат.

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

Програму «Поиск решения» можна запустити на вкладці Данные в групі Анализ.

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

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

На вкладці Файл виберіть команду Параметры, а потім — категорію Надстройки. В полі Управление виберіть елемент Надстройки Excel і натисніть кнопку Перейти. Відкриється діалогове вікно Надстройки.

В полі Доступные надстройки встановіть прапорець потрібної настройки Поиск решения і натисніть кнопку ОК.

Теоретична довідка до ПР №21 Диспетчер сценаріїв

Сценарій (scenario) — це іменовані комбінації значень, заданих для однієї чи декількох змінюваних комірок у моделі "що – якщо". Модель "що – якщо" (what-if model) — це будь-який робочий аркуш, у якому можна підставляти різні значення для змінних (variables), щоб побачити їхній вплив на інші величини, що обчислюються за формулами, які залежать від цих змінних. Змінювані комірки (changing cells) – це комірки, що містять значення, які Ви хочете використовувати як змінні.

Диспетчер сценаріїв дозволяє створити стільки сценаріїв, скільки необхідно для моделі "що – якщо". Потім можна надрукувати звіти з докладними відомостями про всі змінювані і результуючі комірки. При роботі з диспетчером сценаріїв Ви можете:

• створити кілька сценаріїв для однієї моделі "що – якщо", кожний з який може мати свою власну множину змінних;

• поширити модель "що — якщо" між членами робочої групи, щоб вони могли додати свої власні сценарії; потім можна зібрати версії й об'єднати всі сценарії в окремому аркуші;

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

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

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

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

Сценарій – це набір значень, які Microsoft Excel зберігає і може автоматично підставляти на листі. Сценарії можна використовувати для прогнозу результатів моделей і систем розрахунків. Існує можливість створити і зберегти на листі різні групи значень, а потім перемикатися на будь-якому з цих нових сценаріїв для перегляду різних результатів. Якщо потрібно створити бюджет, але доходи точно не відомі, можна визначити різні значення доходу, а потім перемикатися між сценаріями для виконання аналізів «що-якщо».

З повагою ІЦ “KURSOVIKS”!