Metod_Excel_Kuzmina_Kopnyak_9_09

2)Як називають опис рядів даних?

a)Легендою

b)Заголовком

c)Діаграмою

d)Міткою

3)Як називають послідовність значень даних на діаграмі?

a)Рядом даних

b)Категорією

c)Легендою

d)Сіткою

4)Вказати правильний спосіб зміни розміру діаграми в Excel:

a)За допомогою команди Формат

b)Пересуваючи мишею маркери виділення діаграми

c)Розмір змінити не можна

d)За допомогою команди Вставка

5)Що таке ряд даних?

a)Розміщені в рядок дані

b)Розміщені стовпчиком дані

c)Дані, відібрані по принципу спорідненості

d)Дані, відібрані по збільшенню номера розташування комірки у таблиці

6)Що таке точка даних?

a)Одне з числових значень, відібраних для відображення на діаграмі

b)Точка на точковій діаграмі

c)Точка на осі діаграми

d)Підпис осі діаграми

7)Що таке ім’я ряду?

a)Назва ряду, введена з клавіатури у діалоговому вікні

b)Назва ряду, вибрана з підписів шапки таблиці

c)Значення першого елементу ряду даних

d)Назва, введена з клавіатури або вибрана з підписів шапки таблиці

8)Для яких даних доцільно використати діаграму-графік?

a)Числових

b)Таких, що передбачають можливість проміжних даних між відображуваними

c)Непов’язаних дискретних вимірів

d)Таких, що становлять частини одного цілого

9)Скільки рядів даних може мати кругова діаграма?

a)Два

b)Один

c)Три

81

d)Скільки задав користувач

10)Який параметр повинен бути застосований у круговій діаграмі, щоб показати частку точки даних у загальному підсумковому значенні ряду?

a)Доля

b)Значення

c)Категорія

d)Легенда

11)Як виділити точку даних на діаграмі?

a)Подвійним клацанням мишки на точці даних

b)Натисканням правої клавіші мишки на точці даних

c)Послідовним виділенням лівою кнопкою ряду даних і потрібної точки даних

d)Охопленням точки даних прямокутником виділення

12)Коли використовується зміна початкового значення осі Y?

a)При великих значеннях даних у ряду

b)Коли великі абсолютні значення точок у ряду даних і малі відмінності між ними

c)При малих значеннях у ряду даних

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

13)Що таке ключ легенди?

a)Позначка елемента легенди

b)Елемент діаграми, відповідальний за згортання чи розгортання змісту легенди

c)Елемент діаграми, що дозволяє зміну легенди

d)Захист легенди від внесення змін

14)Коли використовують діаграми з двома осями?

a)Коли дані рядів дуже різняться за діапазоном значень

b)Коли дані рядів мають різні одиниці виміру

c)В першому і в другому випадках

d)Немає правильної відповіді

15)Яку діаграму будує система за замовчуванням після першого кроку побудови?

a)Гістограму з мітками осей i легендою

b)Останню перед цим побудовану діаграму

c)Об’ємну гістограму з мітками осей i з легендою

d)Плоску лінійну діаграму

16)Чи можна в одній діаграмі об’єднувати плоскі i об’ємні графіки?

a)Не можна

b)Можна, побудувавши її вручну

c)Можна, за умови встановлення спеціальної надбудови

82

d)Можна, така діаграма відноситься до нестандартного типу

17)Як відіб’ється зміна графіка (кольору, товщини або типу лінії, маркера) на його легенді?

a)Точно також зміниться умовна позначка даного графіка в легенді

b)У легенді жодних змін не відбудеться

c)Система виведе повідомлення про зміну вигляду графіка i необхідності ввести відповідні зміни в легенді

d)Система не дозволить цього зробити

18)До якої категорії діаграм за замовчуванням відноситься змішана діаграма?

a)Стандартные

b)Нестандартные

c)Дополнительные

d)Не існує такого виду діаграми

19)Чи можна створити власний тип діаграм?

a)Ні, оскільки Excel містить певний перелік вбудованих діаграм

b)Можна

c)Можна тільки у випадку, коли встановлена спеціальна надбудова

20)Яким чином виділяються несуміжні діапазони даних для побудови діаграми?

a)За допомогою лівої кнопки миші, утримуючи клавішу Ctrl

b)За допомогою правої кнопки миші, утримуючи клавішу Ctrl

c)За допомогою лівої кнопки миші, утримуючи клавішу Shift

d)За допомогою лівої кнопки миші, утримуючи клавішу Alt

21)Як вивести цифрові значення найбільших елементів рядів побудованої діаграми?

a)Їх слід було задати на третьому кроці побудови діаграми за допомогою майстра

b)За допомогою команди Параметри діаграми контекстного меню діаграми

c)За допомогою команди Формат точки данных контекстного меню найбільшого елементу

d)Усі відповіді вірні

83

ІНДИВІДУАЛЬНО-ПРАКТИЧНЕ ЗАВДАННЯ №1

Тема. Виконання розрахунків економічних показників та їх оцінка за допомогою формул та діаграм у Excel.

Мета. Закріпити навички, засвоєні у процесі виконання лабораторних робіт №1-5, та застосувати їх для розв’язування ситуаційних фахових задач.

ПОРЯДОК ВИКОНАННЯ

1.Відкрити робочу книгу Прізвище_Excel.

2.Після листа Лаб5 створити новий робочий лист з назвою СР1.

3.Відповідно до варіанту, визначеного викладачем, розв’язати задачу з додатку А, для якої:

3.1.Сформувати таблицю за зразком, наведеному в додатку А, використовуючи засоби форматування табличного процесору Excel.

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

3.3.При необхідності скористатись абсолютними посиланнями на деякі комірки.

3.4.На підставі побудованої таблиці провести аналіз даних за допомогою діаграм Excel двох видів, розташувавши їх на цьому ж самому листі.

3.5.Провести форматування побудованих діаграм.

4.Оформити звіт про виконання індивідуально-практичного завдання

відповідно до вимог, наведених у додатках В і Г.

5.Захистити виконане завдання викладачу.

84

ЛАБОРАТОРНА РОБОТА №6

Тема. Робота зі списками та базами даних Excel.

Мета.

1)Ознайомитись з основними правилами створення списків та баз даних в програмі Microsoft Excel.

2)Відпрацювати навички сортування та фільтрації списків.

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

І. КОРОТКІ ТЕОРЕТИЧНІ ВІДОМОСТІ 1. Основні визначення

Список – це набір рядків листа, який містить однорідні дані. Наприклад, список службовців, дані про замовників, прейскурант цін, список замовлень тощо.

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

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

іменами полів.

2. Базові правила створення списків

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

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

Для кожного стовпця потрібно створити заголовок. Перший рядок у списку варто використовувати для заголовків стовпців. Заголовки повинні описувати дані стовпця. Щоб потім скористатися засобами Excel з обробки інформації в списку, заголовки стовпців повинні бути унікальними: не варто використовувати для різних стовпців однакові заголовки.

85

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

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

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

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

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

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

3. Введення даних у список за допомогою форми Форма – це діалогове вікно, що надає зручний спосіб одночасного

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

Для виклику такої форми спочатку потрібно зробити активною одну з комірок списку, а потім вибрати команду Данные – Форма. Щоб додати новий запис, потрібно натиснути кнопку Добавить, заповнити відповідні поля форми та натиснути Enter або кнопку Добавить к форме. Після

86

того, як усі записи будуть введені, слід натиснути кнопку Закрыть.

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

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

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

Первые 10.

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

Якщо необхідно знову зробити видимими всі записи, потрібно вибрати зі списку пункт Все, або виконати команду Данные – Фильтр – Отобразить все.

Щоб вивести на екран перші 10 значень певного поля – або перші 8, або перші 3, і т.п. – потрібно вибрати із відповідного списку пункт Первые 10. Це може знадобитися, наприклад, щоб знайти 5 клієнтів, що мають найбільші обсяги замовлень. При цьому з’являється діалогове вікно

Наложение условия по списку.

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

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

87

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

У випадку порівняння тексту вважається, що літера "а" менше, ніж "б" і т. д. Наприклад, якщо ввести прізвище "Степанов" і вибрати оператор "больше", то будуть відібрані всі прізвища, розташовані за абеткою після Степанова.

Можна здійснювати відбір із використанням символів підстановки, що замінюють у слові одну (?) або декілька літер (*). Для цього потрібно натиснути на елементі керування автофільтром того поля, за яким бажано провести відбір, наприклад, по полю "Прізвище та ініціали". Вибравши пункт Условие, потрібно відкрити діалогове вікно Пользовательский автофильтр. Ввести значення, які потрібно використати для фільтрації. Наприклад, "С*" означає вибір усіх студентів, прізвища яких починаються з літери "С", після якої йде будь-яка кількість інших букв.

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

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

5. Використання форми для знаходження певного запису

Засіб Форма в Excel також надає можливості відбору інформації за деякими критеріями. Щоб скористатися ним, потрібно активізувати будьяку комірку в списку і виконати команду Данные – Форма. Далі натиснути на кнопку Критерии і в потрібному полі ввести значення, за яким бажано провести відбір, та натиснути на кнопку Далее, якщо

88

потрібно, щоб пошук здійснювався нижче поточного запису, або на кнопку Назад, якщо пошук повинен здійснюватися перед поточним записом. Можна використовувати оператори порівняння (=,<,>,<=,>=,<>).

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

6. Сортування даних Сортування даних – це упорядкування записів у списку на підставі

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

Для здійснення сортування в Excel слід виконати такі дії:

1)Активізувати будь-яку комірку у списку і виконати команду Данные – Сортировка. Відкриється діалогове вікно Сортировка диапазона.

2)У розділі Сортировать по потрібно вибрати параметр, за яким треба відсортувати. За замовчуванням Excel сортує дані в порядку зростання.

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

4)Зазвичай у розділі Идентифицировать поля по встановлений режим

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

5)Щоб швидко впорядкувати список за зростанням (спаданням), достатньо виділити комірку в стовпці, по якому повинно здійснюватися сортування, і натиснути на кнопку Сортировка по возрастанию

(убыванию) на панелі інструментів. Цей спосіб дозволяє сортувати тільки по одному стовпцю.

7. Проміжні підсумки

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

1)Відсортувати список по стовпцю, на основі даних якого слід створити групи.

89

2)Виділити будь-яку комірку в списку і виконати команду Данные – Итоги. З’явиться діалогове вікно Промежуточные итоги.

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

4)Зі списку Операция, що розкривається, вибрати потрібну для обчислень функцію (наприклад, Сумма, Количество, Среднее і т.д.).

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

6)Натиснути ОК. На екрані з’являться проміжні результати по групах і загальний результат. При цьому для значень, що повертаються у робочий лист, будуть автоматично вставлені додаткові рядки і стовпці.

На робочому листі автоматично з’явиться структура документа. Символи структури ( - та +) і номери рівнів можна використовувати для того, щоб приховувати і відтворювати дані різних рівнів структури.

8. Використання функцій баз даних

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

Кожна функція має три аргументи: база_данных, поле і критерий. Щоб скористатися функціями баз даних, потрібно виконати такі дії:

1)Визначити на робочому листі область умов (критерій).

2)Підготувати комірки, в яких будуть міститися результати.

3)Викликати Мастер функций.

4)У списку Категория вибрати Работа с базой данных. У списку

Функция вибрати ім’я потрібної функції.

5)Для першого аргументу вибрати комірки листа, що містять БД.

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

7)Перейти до аргументу Критерии і вибрати в листі відповідні комірки області умов.

90