Результат розрахунку

ЗАВДАННЯ 2

Тиражування формул.

ТЕХНОЛОГІЯ РОБОТИ

1. Відкрийте аркуш Освіта_співробітників.

2. Введіть в комірку ЕЗ формулу для автосумування комірок =СУММ(В3:D3).

3. Скопіюйте, перетягнувши маркер заповнення, формулу в комірки Е4:Е8.

4. Перегляньте як змінюються відносні адреси комірок в отриманих формулах (табл.10) і збережіть аркуш.

Таблиця 10

Зміна адрес комірок при тиражуванні формул

ЗАВДАННЯ 3

Створення абсолютного посилання

ТЕХНОЛОГІЯ РОБОТИ

1. Перейдіть на новий аркуш (додайте новий аркуш, якщо не достатньо). Параметри форматування тексту: Times New Roman, 14, обычный.

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

Таблиця 11

Розрахунок зарплати

3. Для комірки В1 використати форматування Денежный, виконавши команду верхнього меню Формат, Ячейки, вкладка Число, параметр Числовые форматы обрати Денежный.

4. В комірку СЗ введіть формулу для розрахунку зарплати Іванова =В1*ВЗ.

При тиражуванні формули даного приклада з відносними посиланнями в комірці С4 з’являється повідомлення про помилку (#ЗНАЧ!), тому що зміниться відносна адреса комірки В1, і в комірку С4 скопіюється формула =В2*В4;

5. Задайте абсолютне посилання на комірку В1, поставивши курсор у рядку формул на В1 і нажавши клавішу [F4], Формула в комірці СЗ буде мати вигляд =$В$1*ВЗ.

6. Скопіюйте формулу в комірки С4 і С5.

7. Збережіть аркуш (табл.12) під ім’ям Зарплатня.

Таблиця 12

Підсумки розрахунку зарплати

ЗАВДАННЯ 4

Обчислення величини середнього значення.

ТЕХНОЛОГІЯ РОБОТИ

Відкрийте аркуш з ім’ям Освіта_співробітників.

1. Виділите комірку F3 і натисніть на кнопку [Мастер функций].

2. У першому вікні діалогу майстра функцій з категорії Статистические виберіть функцію СРЗНАЧ, натисніть на кнопку [Далее].

3. У другому діалоговому вікні майстра функцій повинні бути задані аргументи. Курсор введення перебуває в полі введення першого аргументу. У це поле як аргумент Число1 введіть адресу діапазону B3:D3 (рис. 53).

Примітка. Для введення діапазону комірок у вікно майстра функцій можна мишею обвести на робочому аркуші таблиці цей діапазон (у прикладі B3:D3).

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

5. Натисніть ОК.

6. Скопіюйте отриману формулу в комірки F4:F6 і збережіть файл (табл. 13).

Рис. 53. Введення аргументу в майстер функцій

Таблиця 13

Таблиця результатів розрахунку за допомогою

Майстра функцій

ЗАВДАННЯ 5

Створення імені.

ТЕХНОЛОГІЯ РОБОТИ

1. Відкрийте аркуш Зарплатня. Скопіюйте таблицю на новий аркуш.

2. Виділите комірку В1.

2. Виберіть у меню Вставка, Имя команду Присвоить.

3. Якщо з’явиться Годинна_ставка, натисніть ОК, якщо ні – введіть це ім’я.

4. Виділите комірку В1 і переконаєтеся, що в поле ім’я зазначена Годинна_ставка.

5. Виконайте обчислення з використанням ім’я, для цього в рядку формул установите курсор у те місце, де буде додане ім’я (замість комірки В1).

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

7. Виберіть потрібне ім’я зі списку й натисніть ОК.

8. Збережіть аркуш із ім’ям Імена.

Практична робота №11

Умовний оператор

ЗАВДАННЯ 1

1. Підрахувати кількість дівчат у групі.

2. Підрахувати кількість хлопців у групі.

ТЕХНОЛОГІЯ РОБОТИ

1. Створити нову книгу, зберегти її з ім’ям Практична 4.

2. Виконати таблицю за зразком, дотримуючи необхідного форматування:

3. В комірку С12 введіть: =СЧЁТЕСЛИ(C2:C11;"ж") (категорія – „Статистические”)

Примітка. Слово категорія, та саму категорію функцій вводити не треба!

Категорії функцій вказуються для використання Мастера функций.

4. В комірку С13 введіть: =СЧЁТЕСЛИ(C2:C11;"ч")

5. В комірку С14 введіть: =C12+C13

6. Збережіть аркуш із ім'ям Стать.

ЗАВДАННЯ 2

1. У таблиці набрані назви торговельних фірм, ціна ананасів, за штуку або за кілограм.

2. Обчислити середню ціну за 1 кг і за 1 шт. ананасів по таблиці.

ТЕХНОЛОГІЯ РОБОТИ

1. На новому аркуші виконати таблицю за зразком, зберігаючи потрібне форматування.

2. В комірку С14 введіть формулу:

=СУММЕСЛИ(C3:C13;”кг”;B3:B13)/СЧЁТЕСЛИ(C3:C13;”кг”)

(СУММЕСЛИ – категорія „Математические”, СЧЁТЕСЛИ – категорія „Статистические”)

3. Скопіюйте формулу в комірку С15.

4. Змініть формулу в такий спосіб:

=СУММЕСЛИ(C3:C13;”шт”;B3:B13)/СЧЁТЕСЛИ(C3:C13;”шт”)

5. Збережіть аркуш із ім’ям Ціна ананасів.

ЗАВДАННЯ 3

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

ТЕХНОЛОГІЯ РОБОТИ

1. Виконати таблицю за зразком, зберігаючи необхідне форматування:

2. В комірку D2 уводимо:

=ЕСЛИ(ИЛИ (С2]1000;С2[1000);С2*10;С2) (ИЛИ – категорія „Логические”).

3. З комірки D2 формула копіюється в D3:D11.

4. Збережіть аркуш із ім’ям Внесок.

Практична робота №12

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

ЗАВДАННЯ 1

1. Побудуйте діаграму Освіта співробітників.

2. Дані для побудови діаграми наведені в таблиці 14.

Таблиця 14

Дані для побудови діаграми

ТЕХНОЛОГІЯ РОБОТИ

1. Створіть нову книгу з ім’ям Практична 8. На першому аркуші введіть дані, як показано в табл.14.

2. Виділіть область для створення діаграми A1:D4.

3. Клацніть кнопку [Мастер диаграмм]. З’явиться діалогове вікно Мастер диаграмм (шаг 1 из 4).

4. Виберіть Тип – Гистограмма, Вид – Обычная, потім виберіть Далее. З’явиться діалогове вікно Мастер диаграмм (шаг 2 из 4), на закладці Диапазон данных. Перевірте Диапазон данных.

5. Виберіть Далее. З’явиться діалогове вікно Параметры диаграммы (шаг 3 из 4). Задайте назву заголовка діаграми Освіта співробітників.

6. Уведіть назву осі X – Філії, осі Y – Кількість співробітників.

7. Виберіть Далее й перейдіть до наступного кроку.

8. Виберіть варіант розміщення Поместить диаграмму на текущем листе.

9. Виберіть Готово після завершення.

10. Збережіть аркуш (рис. 54) з ім'ям Освіта_співробітників.хls.

Рис. 54. Діаграма Освіта співробітників

ЗАВДАННЯ 2

Змініть вид діаграми:

• додайте рядок у назву діаграми;

• змініть шрифт назви;

• додайте рамку навколо легенди;

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

• змініть числовий формат осі значень.

ТЕХНОЛОГІЯ РОБОТИ

1. Відкрийте аркуш Освіта_співробітників з діаграмою

2. Додайте другий рядок до назви діаграми.

3. Клацніть у Заголовок диаграммы, щоб помістити курсор уведення в текст.

4. Перейдіть у кінець рядка й натисніть [Enter].

5. Введіть 2006 р. і натисніть [Esc] після завершення.

6. Щоб змінити шрифт назви діаграми:

- клацніть правою кнопкою миші за назвою діаграми для відкриття контекстного меню й виберіть Формат заголовка диаграммы.

- виберіть закладку Шрифт і задайте шрифт Arial, напівжирний, розмір 14.

– натисніть ОК після завершення.

7. Щоб додати рамку з тінню навколо Легенды:

- клацніть правою кнопкою миші Легенду й відкрийте її контекстне меню.

- виберіть Формат легенды й закладку Вид.

- виберіть Обычная и С тенью із групи Рамка й натисніть ОК після завершення.

8. Щоб змінити формат чисел для Оси значений на числовий без десяткових знаків:

- клацніть правою кнопкою миші по Оси значений і відкрийте її контекстне меню.

- виберіть Формат оси й потім виберіть закладку Число.

- виберіть Числовой зі списку Числовые форматы, зменшите число знаків після коми до 0 і натисніть ОК.

9. Щоб змінити фон Области построения на голубой:

- клацніть правою кнопкою миші по Области построения й відкрийте її контекстне меню.

- виберіть Формат області побудови й закладку Вид.

- виберіть блакитний колір із групи Заливка, потім натисніть ОК.

10. Збережіть аркуш (рис. 55).

Рис. 55. Зміна об'єктів діаграми

ЗАВДАННЯ 3

Створити об’ємну кругову діаграму

ТЕХНОЛОГІЯ РОБОТИ

1. Відкрийте аркуш Освіта_співробітників.

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

2. Виберіть несуміжні області А2:А4 і С2:С4, для цього виділіть А2:А4, потім клацніть CTRL, та не відпускаючи її, виділіть С2:С4.

3. Клацніть кнопку [Мастер диаграмм]. З’явиться діалогове вікно Мастер диаграмм (шаг 1 из 4): тип диаграммы.

4. Виберіть Круговая зі списку Тип і потім виберіть Объемный варіант кругової діаграми зі списку Вид.

5. Клацніть кнопку [Далее]. З’явиться діалогове вікно Майстер діаграм (крок 2 з 4): источник данных диаграммы на закладці Диапазон данных.

6. Переконайтеся, що текст =Освіта співробітників!$А$2:$А$4;Освіта співробітників!

$С$2:$С$4 зазначений у поле Диапазон, і що обрано перемикач Ряды в столбцах.

7. Клацніть кнопку [Далее]. З’явиться вікно Мастер диаграмм (шаг 3 из 4): параметры диаграммы.

8. Додайте наступні параметри діаграми: Закладка Заголовки Поле Название диаграммы введіть текст Магазин „Лілія”. Закладка Легенда Поле Размещение виберіть Внизу. Закладка Подписи данных Поле Подписи значений виберіть Доля.

9. Клацніть кнопку [Далее]. У діалоговому вікні Мастер диаграмм (шаг 4 из 4): размещение диаграммы виберіть В имеющемся (розміщення на поточному аркуші) і натисніть ОК.

10. Отримаєте кругову діаграму (рис. 56).

11. Збережіть файл.

Рис. 56. Приклад побудови кругової діаграми

4. 2. ТЕМА ДЛЯ САМОСТІЙНОГО ВИВЧЕННЯ. РОБОТА ЗІ СПИСКАМИ (БАЗАМИ ДАНИХ)

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

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

Для створення списку за допомогою форми (маски введення):

1. Сформуйте заголовний рядок списку. У кожному стовпчику цього рядка введіть назву відповідного поля запису.

2. Клацніть на кожній з комірок заголовного рядка й виберіть команду Данные, Форма.

3. У діалоговому вікні, що містить поля, назва й кількість яких відповідає створеним заголовкам стовпчиків, введіть дані в кожне поле. Ширина полів відповідає самому широкому стовпчику заголовка. Для переходу між полями можна користуватися покажчиком миші, або клавішами [Тab] – для переходу вниз і [Shift+Tab] – для переходу нагору.

4.Натисніть кнопку [Добавить] для розташування значень даного запису в список і введіть наступний запис. Для завершення процесу введення даних натисніть кнопку [Закрыть].

Пошук записів у списку

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

1. Встановіть курсор у будь-яку комірку списку й виберіть команду Данные, Форма.

2. Натисніть кнопку [Критерии].

3. Введіть критерії пошуку в одне або кілька полів запису. Так, для пошуку всіх прізвищ, що починаються на букву А, досить у поле прізвища набрати А. Для пошуку записів з величиною окладу, більшої 500000, у поле окладу варто ввести ]500000.

4. Натискайте кнопки [Назад] и [Далее], або кнопки смуги прокручування для пошуку записів, що відповідають установленому критерію. Для завдання нового критерію пошуку натисніть кнопку [Очистить].

Редагування записів за допомогою форми

Для виправлення значень (але не формул) у будь-якому полі запису:

1. Встановіть курсор у будь-яку комірку списку й виберіть команду Данные, Форма.

2. Знайдіть необхідний запис за допомогою кнопок [Назад] и [Далее].

3. Відредагуйте запис.

4. Натисніть кнопку [Закрыть].

Видалення записів за допомогою форми

Для видалення запису:

1. Встановіть курсор у будь-яку комірку списку й виберіть команду Данные, Форма.

2. Знайдіть необхідний запис і натисніть кнопку [Удалить].

3. Підтвердите видалення нажавши [ОК].

4. Натисніть кнопку [Закрыть].

Додавання записів

При використанні діалогового вікна команди Форма знову створювані записи заносяться в кінець списку. Для додавання запису усередину списку:

1. Встановіть курсор у рядок, перед якою буде вставлений новий рядок.

2. Виберіть команду Вставка, Строка і введіть у неї відповідні значення.

Функції для роботи зі списками

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

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

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

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

Таблиці автоматичної підстановки даних

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

1. Або в окремий стовпчик, або в окремий рядок введіть список значень, які варто підставляти в комірку введення з області критеріїв.

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

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

3. Виділіть діапазон комірок, що містять формули й значення підстановки.

4. Виберіть команду Данные, Таблица подстановки.

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

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

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

ТЕСТ ДЛЯ САМОПЕРЕВІРКИ

1. Для визначення дати створення документа табличного процесора Microsoft Excel необхідно застосувати наступний шлях у меню: „Файл” – „Свойства” –

a) „Общие”;

b) „Документ”;

c) „Статистика”;

d) „Состав”.

2. В електронних таблицях виділена група комірок D2:E3. Скільки комірок входить у цю групу?

a) 6;

b) 4;

c) 5;

d) 3.

3. Результатом обчислень у комірці С1 буде число:

a) 8;

b) 12;

c) 16;

d) 20.

4. Для визначення автора документа табличного процесора Microsoft Excel необхідно застосувати наступний шлях у меню: „Файл” – „Свойства” –

a) „Общие”;

b) „Документ”;

c) „Статистика”;

d) „Состав”.

5. В електронних таблицях виділена група комірок D2:Е4. Скільки комірок входить у цю групу?

a) 6;

b) 4;

c) 5;

d) 3.

6. Результатом обчислень у комірці С1 буде число:

a) 8;

b) 12;

c) 10;

d) 20.

7. В електронних таблицях виділена група комірок D1:E3. Скільки комірок входить у цю групу?

a) 6;

b) 4;

c) 5;

d) 3.

8. Результатом обчислення у комірці С1 буде число:

a) 8;

b) 12;

c) 16;

d) 20.

9. Для зміни стандартної довідкової інформації документа табличного процесора Microsoft Excel необхідно застосувати наступний шлях у меню: „Файл” – „Свойства” –

a) „Прочие”;

b) „Документ”;

c) „Статистика”;

d) „Состав”.

10. В електронних таблицях виділена група комірок B3:D6. Скільки комірок входить у цю групу?

a) 6;

b) 4;

c) 12;

d) 3.

11. Microsoft Excel це:

a) текстовий редактор;

b) графічний редактор;

c) редактор таблиць;

d) Internet-оглядач.

12. Команда „майстер діаграм” перебуває в пункті меню:

a) файл;

b) правка;

c) вставка;

d) сервіс.

13. Додати комірку можна за допомогою:

a) меню вставка;

b) клацання правої клавіші миші;

c) меню правка;

d) всі варіанти.

14. Помістити таблицю в буфер обміну без видалення можна за допомогою команди:

a) копіювати ;

b) вирізати ;

c) виділити;

d) вставити .

15. Логічна функція „ЯКЩО” перебуває в меню:

a) вставка функція;

b) формат функція;

с) правка функція;

d) вид функція.

16. Промаркувати стовпчик комірок можна за допомогою комбінації клавіш:

a) [Ctrl + ↓];

b) [Caps Lock +↓];

c) [Shift + ↓];

d) [Alt + ↓].

17. Відновити вилучені дані можна за допомогою комбінації клавіш:

a) [Ctrl + Z];

b) [Shift + Z];

c) [Shift + Del];

d) [Alt + Z].

18. Для зведення числа в ступінь використовується клавіша:

a) [$];

b) [*];

c) [/];

d) [^].

19. Введенню формули в комірку повинно передувати натискання клавіші:

a) [=];

b) [Enter];

c) [/];

d) [~].

20. Для встановлення інформаційного зв'язку використовується клавіша:

a) [#];

b) [!];

c) [?];

d) [%].

ПЕРЕЛІК ТЕОРЕТИЧНИХ ПИТАНЬ З ТЕМИ „MS EXCEL”

1. Що таке електронна таблиця (ЕТ) і яке її призначення? Як позначаються стовпчики і рядки в ЕТ? Як змінити ширину стовпчика в ЕТ? Які типи даних опрацьовують ЕТ? Яка структура ЕТ?

2. Яке призначення програми Excel? Яке розширення має файл книги електронної таблиці?

3. Як змінити висоту рядка? Як вибрати (виокремити) комірку? З чого складається робоча книга? Як ввести у комірку дані? Як відредагувати дані в комірці?

4. Наведіть приклади даних числового і текстового типу? Як виокремити в ЕТ потрібну комірку? Як відредагувати дані в комірці?

5. Від чого залежить відображення чисел в ЕТ? Яке призначення формул в ЕТ? Що зображено в комірці після введення в неї формули? Як побачити формулу?

6. Для чого використовують текстові дані в ЕТ? Як відобразити числа з символом грошової одиниці? Як побачити формулу у одній комірці, формули у всій ЕТ? Які засоби автозаповнення таблиці ви знаєте?

7. Яке призначення клавіші [F2] в ЕТ? Як виконати копіювання формули? Як задати режим ручного керування обчисленнями? Як задати чи змінити деякий формат відображення чисел? Як вимкнути режим відображення сітки?

8. Як розграфити таблицю? Як задати колір коміркам і колір шрифту? Які ви знаєте формати числових даних? Як захистити комірки від змін у них?

9. Що таке копіювання формул? В чому суть автоматичного переобчислення в ЕТ? Як заповнити стовпчик зростаючою послідовністю чисел?

10. Що відбувається під час копіювання формули в ЕТ? Які основні прийоми автоматизації обчислень в ЕТ? Яке призначення формул в ЕТ? Що таке копіювання формул?

11. Які є категорії стандартних функцій? Як комірці надати ім'я? Що таке діапазон комірок? Як скопіювати формулу з деякої комірки в діапазон-стовпчик?

12. Як виокремити діапазон? Опишіть пріоритети виконання операцій у виразах. Як вставити рядок у таблицю?

13. Як працює команда Підсумки даних? Як скопіювати формулу з деякої комірки в рядок? Як вилучити стовпчик чи рядок з таблиці?

14. Яке призначення приміток і як їх вставляти? Як виконати пошук потрібних даних в таблиці?

15. Як користуватися командою Автофільтр? Наведіть приклади статистичних функцій. Яке призначення кнопки [Автосума]?

16. Як забрати чи показати деталі у підсумковій таблиці? Як скопіювати таблицю на іншу сторінку? Які математичні функції є в ЕТ?

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

18. Як створити список користувача? Як заховати стовпчик? Як заповнити стовпчик чи рядок елементами списку користувача?

19. Яким символом відокремлюють аргументи у функціях? Як реалізуються розгалуження в ЕТ? Як обчислити суму чисел у стовпчику?

20. Який загальний вигляд має функція ЯКЩО? Які ви знаєте логічні функції?

21. Що таке абсолютна і змішана адреси комірки? Як скопіювати таблицю на іншу сторінку? Яка різниця між відносними і абсолютними адресами комірок?

22. Як ввести дату в комірку? Який розділювач можна використовувати у списках аргументів функції?

23. Яку стандартну функцію заміняє кнопка [Автосума]? Як виокремити несуміжні діапазони комірок? В чому полягає метод підбору параметра?

24. Як перейти на іншу сторінку? Як перейменувати сторінку? Як очистити весь стовпчик?

25. Яке призначення діаграм? Які є типи діаграм? Які є види кругової діаграми? Які осі мають діаграми? З яких елементів складається діаграма?

26. Як отримати на екрані назву елемента діаграми? Які дії визначені над елементами діаграми? Як розмальовувати елемент діаграми? Яке призначення Майстра діаграм? Як виокремити несуміжні діапазони даних?

27. Як працює Майстер діаграм? Як вилучити діаграму зі сторінки? Для чого використовують кругові діаграми? Як підписати осі діаграми? Для чого використовують точкові (X-Y) діаграми? Як зробити зміни в діаграмі?

28. Як увімкнути панель інструментів Діаграма? Для чого використовують стовпчикові діаграми? Яке призначення панелі інструментів Діаграма? Які є різновиди стовпчикових діаграм? Як перемістити діаграму у потрібне місце?

29. Як скопіювати діаграму на іншу сторінку? Як розтягнути діаграму? Як відформатувати заголовок діаграми? Як повернути діаграму? Як змінити тип діаграми?

РОЗДІЛ 5.

СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАНИХ MICROSOFT ACCESS

5. 1. ОСНОВНІ ТЕОРЕТИЧНІ ВІДОМОСТІ

СТВОРЕННЯ І ЗАПОВНЕННЯ БАЗИ ДАНИХ

Об'єкти бази даних

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

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

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

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

Таке поле чи набір полів називають первинним (головним) ключем. Первинний ключ не допускає невизначених значень і завжди повинен мати унікальний індекс. Первинний ключ використовується для зв'язування таблиці з зовнішніми (вторинними) ключами в інших таблицях. У Microsoft Access можна виділити три типи ключових полів: счетчик, простой ключ і составной.

Форма – це об'єкт, призначений в основному для введення даних, відображення їх на екрані чи керування роботою додатка.

Запит – це об'єкт, що дозволяє користувачу одержати потрібні дані з однієї чи декількох таблиць.

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

Режими роботи

В Access розрізняються наступні основні режими роботи:

• Таблица – режим роботи з таблицею;

• Запрос – режим роботи з запитами;

• Форма – режим роботи з формами;

• Отчет – режим роботи зі звітами.

У кожному з названих режимів є підрежими: Просмотр, Конструктор і Создать. Наприклад, у режимі Таблица – Просмотр можна переглянути вміст таблиці, ввести і відредагувати дані. У режимі Таблица – Конструктор можна переглянути і змінити структуру таблиці. У режимі Таблица – Создать можна створити нову таблицю.

Створення і видалення таблиць

Щоб включити в базу даних нові відомості, її варто розширити. В одних випадках досить додати запис чи поле в існуючу таблицю, в інших необхідно створити нову таблицю. У Access існує кілька способів створення таблиць. Можна виконати кроки Майстра, скористатися Конструктором чи внести назви полів у порожній бланк таблиці.

Заповнення і редагування таблиць у режимі Таблица Після створення таблиць потрібно внести в них дані. Можна вводити дані безпосередньо в таблицю чи створити Автоформу (Вставка, Автоформа). Access завжди пропонує зберегти створені об'єкти чи зміни, зроблені в них перед їх закриттям.

Переміщатися по таблиці під час заповнення можна за допомогою клавіш зі стрілками на клавіатурі [←],[↑], [→], [↓], клавіш [Tab], [Enter] і клавіш миші. Під час редагування даних можна змінювати висоту рядків і ширину стовпчиків таблиці.

Завершення роботи

Для завершення роботи з базою даних її потрібно закрити. Якщо в базі даних були зроблені який-небудь зміни, з'явиться питання про збереження. Варто підтвердити збереження змін.

РОЗРОБКА ІНФОЛОГІЧНОЇ МОДЕЛІ І СТВОРЕННЯ СТРУКТУРИ РЕЛЯЦІЙНОЇ БАЗИ ДАНИХ

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

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

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

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

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

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

де F(x) – це вид зв'язку А с В; G(x) – це вид зв'язку В с А.

Зв'язки можуть бути одиничними (U – унарними) і множинними N.

1. Відношення «один-до-одного»

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

2. Відношення «один-до-багатьох»

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

3. Відношення «багато-до-одного»

Такий зв'язок прямо протилежний зв'язки «один-до-багатьох». У цьому випадку багато дітей можуть мати одних батьків.

4. Відношення «багато-до-багатьох»

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

Найбільш розповсюдженими є зв'язки «один-до-багатьох» і «один-до-одного».

На схемі даних загальні поля з'єднані лініями зв'язку. З однієї сторони ця лінія завжди маркірується знаком «1» (одинарною стрілкою), з іншого боку – або знаком «1» (одинарною стрілкою) – зв'язок «один-до-одного», або літерою «М» («N») (подвійною стрілкою) – зв'язок «один-до-багатьох». Зрозуміло, що якщо зв'язуються ключові поля, те це завжди зв'язок «один-до-одного», а якщо ключове поле зв'язане з неключовим, те це зв'язок «один-до- багатьох». За допомогою олівця і паперу розкреслюють зв'язки між таблицями. На рис.57 показаний приклад взаємозв'язку між групою таблиць, що входять у базу даних компанії, що займається трансляцією супутникових телевізійних каналів. Ключові поля таблиць у ній виділені напівжирним шрифтом. Таке креслення називається схемою даних.

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

КЛІЄНТИ		ПІДПИСКА				РОЗРАХУНКИ З КЛІЄНТАМИ
Номер контракту	1 1	Номер контракту				Черговий номер
прізвище		Пакет підписки	М		М	Номер контракту
Ім’я		взаєморозрахунки				Залишок на рахунку
По батькові				СКЛАД ПАКЕТІВ		Надійшло на рахунок
Адреса				Пакет підписки		Дата надходжень
Телефон				ОРТ		Підстава нарахування
Електронна пошта				НТВ		Списання з рахунка
				КРТ		Дата списання

Рис. 57. Схема зв'язків між таблицями

СПОСОБИ СТВОРЕННЯ НОВОЇ БАЗИ ДАНИХ

Існує всього два основних способи створення нової бази даних: ви можете створити нову порожню базу даних, а можете створити нову базу даних на підставі одного з доступних шаблонів. У другому випадку використовується відповідний майстер, що створить для вас базу даних обраного типу й заповнить її всіма необхідними для функціонування об'єктами (таблицями, запитами, формами, звітами, сторінками, макросами, модулями). Для створення нової порожньої бази даних необхідно скористатися командою Новая база данных, що розташована на панелі Создание файла. Якщо панель відсутня на екрані, виберіть пункт Создать меню Файл або натисніть на панелі інструментів однойменну кнопку із зображенням чистого аркуша паперу. Однак кожна із цих дій не приведе до негайного створення порожньої бази даних, а всього лише відкриє панель Создание файла. Після вибору команди Новая база данных на екрані з'явиться діалогове вікно Файл новой базы данных. Вам залишається лише задати шлях, ім'я файлу й натиснути кнопку [Создать]. Вікно тільки що створеної вами бази відкриється, надаючи можливість додавання в неї нових об'єктів.

Щоб створити нову базу даних на підставі шаблона, необхідно скористатися командою Общие шаблоны....Ця команда належить до групи команд Создание с помощью шаблона, розташованих на панелі Создание файла. У результаті дії цієї команди на екрані з'явиться діалогове вікно Шаблоны. Одна із двох вкладок цього вікна – Общие, пропонує ще одну можливість створення нової порожньої бази даних (див. рис. 58). Крім цього, вкладка містить також команди для створення нової порожньої сторінки доступу до даних і нових проектів Access.

Вкладка Бази данных надає можливість вибрати один з 10 шаблонів, що поставляються разом з Microsoft Access (див. рис. 59). Вибравши варіант, що найбільш підходить для вас, натисніть кнопку [ОК]. У результаті буде запущений майстер по створенню нової бази даних, заснованої на обраному вами шаблоні. У процесі роботи майстра вам необхідно задати ім'я й шлях файлу створюваної бази даних, після чого буде продемонстровано кілька діалогових вікон. Деякі із цих вікон є інформативними – вам просто потрібно, попередньо ознайомившись із наведеними відомостями, натиснути кнопку [ОК], щоб перейти до наступного вікна. Інші діалогові вікна дозволять вам указати, які з передбачених шаблоном таблиць і полів таблиць варто залишити в базі, вибрати стиль відображення форм і звітів, а також задати заголовок бази даних.

Рис. 58. Діалогове вікно Шаблоны, відкрите на вкладці Общие. Ця вкладка надає також спосіб створення нової порожньої бази даних

Рис. 59. Вкладка Бази данных діалогового вікна Шаблоны надає вам можливість вибрати один з 10 шаблонів, що поставляються з Microsoft Access

Спочатку треба спробувати створювати нові бази даних по шаблонам. По-перше, у такий спосіб можна підібрати шаблон, що може стати відправною точкою для подальшого розвитку додатка. По- друге, експериментуючи із уже готовими базами, можна краще зрозуміти матеріал. Крім стандартних шаблонів, доступних в Microsoft Access, можна скористатися додатковим набором шаблонів, розташованих на офіційному сайті Microsoft. Для цього служить команда Шаблоны на Microsoft.com панелі Создание файла. На панелі Создание файла перебуває ще одна команда, формально призначена для створення нової бази даних. Це команда выбор файла.... Однак, по суті, ця команда просто копіює зазначену вами існуючу базу даних разом з усією інформацією, що зберігається там, з оригінального місця розташування в задане вами (можливо під новим ім'ям).

БАЗА ДАНИХ MS ACCESS НА ОСНОВІ ШАБЛОНА MS EXCEL

Для набору даних з типом «Документи (дані в MS Access на основі шаблона MS Excel)» буде створюватися база даних MS Access на основі структури шаблона MS Excel. Кожному аркушу шаблона в базі буде відповідати таблиця з таким же ім'ям і з тими ж полями, які визначені в аркуші.

Імена полів формуються за правилом «P» + номер стовпчика на аркуші. Додатково до існуючого набору полів додається поле P0, що містить порядкові номери записів.

В аркуш шаблона ComplSheet (аркуш додається системою автоматично) в іменовану область ComplSheet!RGN_Data записується ім'я сформованої бази MS Access з повним шляхом доступу до неї.

База даних Report.mdb створюється в каталозі для тимчасових файлів, що встановлюється по команді з головного меню системи Вид, Настройка, Рабочие пути у реквізиті „Путь к временным файлам”. При створенні бази даних система із шаблона використовує тільки перший рядок опису області даних для формування полів у таблицях. Тому в цьому випадку не рекомендується використовувати комірки, що обчислюються. Також не рекомендується використовувати опції аркуша, звіту й опції стовпчиків, тому що вони ігноруються.

5. 2. ТЕМА ДЛЯ САМОСТІЙНОГО ВИВЧЕННЯ. СТВОРЕННЯ ЗАПИТІВ. СТВОРЕННЯ СКЛАДНИХ ФОРМ. СТВОРЕННЯ СКЛАДНИХ ЗВІТІВ

Запити і фільтри

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

Основні відмінності запитів і фільтрів полягають у наступному.

• Фільтри не дозволяють в одному рядку відображати дані з декількох таблиць, тобто поєднувати таблиці.

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

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

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

Запити можуть використовуватися тільки з закритою таблицею чи запитом. Фільтри звичайно застосовуються при роботі в режимі Формы чи в режимі Таблицы для перегляду чи зміни підмножини записів. Запит можна використовувати:

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

• для того щоб об'єднати у виді однієї таблиці на екрані дані з декількох таблиць;

• для перегляду окремих полів таблиці;

• для виконання обчислень над значеннями полів.

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

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

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

Створити запити в Access можна вручну або за допомогою майстра запитів. Майстер запитів Access містить спеціальні інструменти, які використовуються для розв'язку досить складних задач (наприклад, для пошуку записів, які повторюються, або перегляду даних запита). Але для виконання простих щоденних операцій, які вимагають простих запитів, можна відкрити запит, додати поле, вставити умову, виконати запит. Щоб створити запит вручну, треба виконати: Вікно конструктора запиту призначене для створення нового запиту. В ньому можна додавати таблиці, що використовуються в запиті, вибрати з таблиць окремі поля, визначити умови, яким мають задовольняти відібрані записи, вибрати порядок сортування, сховати поле таблиці і т.ін. Створивши запит, його можна виконати, зберегти і надрукувати результати виконання. Відкривши вікно запиту, можна приступити до розробки нового запиту; її треба розпочати з додавання таблиць, з яких вибираються дані. Для цього необхідно в діалоговому вікні Добавление таблицы вибрати ту таблицю, або таблиці, які мають використовуватися в запиті. Кількість таблиць, що використовуються в запиті, необмежена. Крім таблиць, в запиті можуть використовуватися інші запити. При створенні запитів із декількома таблицями треба призначити загальне поле, яке зв'язує таблиці, пересуванням його з вікна однієї таблиці у вікно іншої. Запити, що використовують декілька таблиць, називають реляційними. Відкрити діалогове вікно Добавление таблицы можна, натиснувши на кнопці [Добавить таблицу] панелі інструментів або вибравши команду Запрос, Добавить таблицу. Додавши таблицю (таблиці) і/або запит (запити), закрити діалогове вікно Добавление таблицы.

Запити можна зберігати так, як і інші об'єкти Access. Коли вікно перегляду результатів запиту активне, треба вибрати команду Файл, Сохранить або натиснути на кнопку [Сохранить] панелі інструментів. Якщо запит зберігається вперше, з'являється діалогове вікно, в якому треба вказати ім'я запиту. Для друкування результатів запиту треба виконати запит на відображення динамічного набору, потім виконати команду Файл, Печать або натиснути кнопку [Печать] панелі інструментів. У діалоговому вікні Печать, яке з'явиться, треба встановити необхідні параметри і натиснути [ОК].

Access використовує мову бази даних SQL (Structured Query Language – структурована мова запитів) для створення запитів. Незважаючи на те, що запит розробляється візуально у вікні конструктора запиту, при збереженні запиту Access транслює цей візуальний проект в оператор SQL, який і виконується при запуску запиту. Оператор SQL, який відповідає даному запиту, можна побачити у вікні конструктора запиту, якщо вибрати команду Вид, Режим SQL.

MS Access дозволяє виконувати наступні типи запитів: QBE- запити (QBE – Query By Example – Запити за зразком):

• запит на вибірку;

• перехресний запит;

• запит на створення таблиці;

• запит на відновлення;

• запит на додавання записів;

• запит на видалення записів.

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

Запит на вибірку

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