Завдання 5. Автозаповнення.

1. Уведіть у комірку А5 слово Січень, у комірку А6 слово Лютий. Виділіть комірки А5 і А6.

2. Установіть покажчик миші у правий нижній кут виділеної області. Після перетворення його на хрестик натисніть ліву кнопку миші та протягніть її до комірки А16. У результаті в комірках А5 – А16 з’являться назви 12 місяців року.

3. Виповніть таким же чином автозаповнення арифметичної прогресії (2, 4, 6,…, 24), починаючи з комірок С6, D6 до комірок С17, D17.

4. Обчисліть суму арифметичної прогресії. Для цього установіть курсор у порожню комірку С18 або D18, у якій буде обчислена сума. Натисніть піктограму å в панелі Стандартна.

5. Створіть свій список, який складатиметься з прізвищ студентів підгрупи. Для цього виберіть команду Сервис ►Параметры ►Списки ►Новый список. У вікні Элементы списка введіть прізвища, використовуючи клавішу Enter. Уведіть у будь-яких комірках два прізвища, виділіть ці комірки, встановіть курсор у правий нижній кут виділеної області і, не відпускаючи ліву кнопку миші, протягніть мишу вниз або праворуч.

6. Створіть свій список, який складатиметься з прізвищ студентів підгрупи і розміру їхньої стипендії. Для цього створіть таблицю, в першому стовпці якої введіть прізвища, а в другому – розмір стипендії (наприклад, розмір стипендії -150 грн); виділіть цю таблицю; виберіть команду Сервис ►Параметры ►Списки. Натисніть клавішу Импорт, Импорт списков из столбцов. Зробіть копію створеного списку, використовуючи автозаповнення.

7. Відсортуйте копію списку за розміром стипендії (не всі стипендії мають бути однаковим), потім за прізвищами.

8. Побудуйте діаграму: функція – розмір стипендії, аргумент – прізвища.

 

Завдання 6. Переміщення інформації.

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

 

Завдання 7. Формат комірки.

1. Активізуйте команду Ячейки з підменю Формат. У результаті з’явиться діалогове вікно Формат ячеек.

2. Для комірок від C8 до C17 з арифметичною прогресією встановіть формат числовой, здвома десятковими знаками.

3. Для комірок від D8 до D17 встановіть формат фінансовий з позначенням гривні.

 

Завдання 8. Використовування відносних посилань у формулах та копіювання даних за допомогою клавіші Ctrl.

1. Установіть курсор у комірку В2, потім у рядок формул і натисніть на знак дорівнює, потім уведіть у цю комірку посилання на комірку А3 і натисніть клавішу Enter (або значок у рядку формул у вигляді галочки). У комірці В2 ви повинні одержати 0. Уведіть у комірку А3 цифру 4, клацніть по комірці В2 і ви одержите цифру 4.

2. Скопіюйте в комірку D3 уміст комірки В2. Для цього установіть курсор на ліву межу рамки курсору в комірці В2, натисніть клавішу Ctrl і, не відпускаючи її, відбуксуйте комірку В2 у комірку D3, а потім відпустіть клавішу Ctrl. У комірці D3 ви одержите значення 0. Проаналізуйте, чому.

3. Перейдіть на Лист2 й уведіть у комірку С6 число 7.

4. Перейдіть на Лист1 й уведіть у комірку D4 посилання: =Лист2!С6. Посилання уведіть як формулу зі знаком дорівнює. Під час уведення імені комірки другого аркуша не забудьте перейти на латинський алфавіт. У комірці D4 ви повинні одержати число 7. Проаналізуйте, чому це так.

 

Завдання 9. Копіювання формул з відносними та абсолютними посиланнями у формулах.

1. Уведіть у комірки A1 та A2 відповідно числа: 2, 75 та 3,1. У комірку В1 уведіть формулу =А1+А2. Ви повинні одержати число 5,85.

2. Скопіюйте уміст комірки В1 в комірку D1. Ви повинні одержати значення 0. Проаналізуйте, чому це так.

3. Очистіть комірку D1. У комірку В1 уведіть формулу =$А$1+$А$2 та скопіюйте її у комірку D1. Ви повинні одержати значення 5,85. Проаналізуйте, чому це так.

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 2.6

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

Мета: Навчитися будувати графіки за допомогою майстра діаграм у додатку Excel.

Завдання 1. Створення та форматування таблиці.

1. Завантажте Microsoft Excel.

2. Виконайте аналіз господарської діяльності на підприємстві на підставі наведених у занятті даних.

3. Для розв’язання цієї задачі спочатку побудуйте таблицю, під час побудови таблиці використовуйте шрифт 12.

4. Для заповнення стовпчика з місяцями скористайтеся списками процесора Excel.

5. Щоб перенести текст у комірці на наступний рядок, натисніть у тій самій комірці ліву клавішу Alt і, не відпускаючи її, натисніть Enter.

 

Завдання 2. Виконання розрахунків у таблиці.

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

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

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

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

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

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

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

 

Завдання 3. Побудова простого графіка.

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

2. У першому діалоговому вікні майстра діаграм оберіть Тип – График, Вид – за умовчанням і клацніть Далее.

3. У другому вікні Майстра перейдіть на вкладку Ряд, розташуйте курсор у рядку Подписи оси Х, виділіть стовпчик Місяць і клацніть Далее.

4. У третьому вікні майстра на вкладці Заголовки введіть необхідні надписи й клацніть Готово.

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

Завдання 4. Побудова складного графіка.

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

2. У першому діалоговому вікні майстра діаграм слід обрати Тип - Биржевая, Вид – за умовчанням і клацнути Далее.

3. У другому вікні необхідно повторити дії п. 3.3.

4. У третьому вікні майстра на вкладці Заголовки введіть необхідні надписи й клацніть Готово. По правої клавіші додати лінію тренду.

Завдання 5. Збереження та друк результатів.

1. Збережіть результати роботи.

2. Виконайте попередній перегляд таблиці та графіків і надрукуйте їх.

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 2.7

Тема заняття: Створення та робота з базою даних в табличному процесорі Excel.

Мета: Навчитися створювати базу даних та працювати з нею за допомогою додатку Excel.

Теоретичні зауваження щодо створення бази даних в MS Excel.

1. Уважно прочитайте теоретичні відомості щодо створення бази даних в MS Excel та законспектуйте їх у зошиті.

База даних – це таблиця, що складається з полів та записів. Поля – це стовпці, в яких містяться однотипні дані про всі об’єкти бази даних. Записи являють собою рядки, де записана вся інформація про один об’єкт бази даних. Професійна база даних може створюватися на підставі систем керування базами даних (СУБД). Але MS Excel дає можливість створювати базу даних, яку можна імпортувати у СУБД. У створеній базі даних в MS Excel є можливість додавати записи, вилучати їх та робити вибірку необхідних записів, які відображатимуться у вигляді таблиць відповідно до критерію. Під час створення бази даних потрібно надати стовпцям імена, а потім увести до неї записи. Для роботи з базою даних використовують команду меню Данные ►Форма. У ході виконання цієї команди з’явиться вікно для роботи з базою даних. На рис. 1 показано вікно для створеної бази даних Картотека. У цьому вікні користувач уводить дані у кожне поле бази даних.

Рис. 1. Діалогове вікно для роботи з базою даних Картотека

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

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 2.8

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

Мета: Навчитися застосовувати математичні, статистичні, логічні функції Excel у ході розв’язування різноманітних задач.

Теоретичні відомості щодо використання функцій Excel.

1. Уважно прочитайте теоретичні відомості щодо використання функцій Excel:

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

Логічні функції. Функція ЕСЛИ використовується під час перевірки умов для значень і формул.

Синтаксис: ЕСЛИ (Логічний_вираз; значення_якщо_істина; значення_якщо_хибне)

Логічний_вираз - це будь-яке значення або вираз, що набуває значення ИСТИНА або ЛОЖЬ. Логічний вираз якраз і є тією умовою, що перевіряється,наприклад, A10 =100 - це логічний вираз, якщо значення в комірці A10 дорівнює 100, то вираз набуває значення ИСТИНА. В іншому випадку - ЛОЖЬ. Наприклад, записана функція ЕСЛИ в якійсь комірці і має вигляд: =ЕСЛИ(A2<=100;"У межах бюджету";"Поза бюджетом"). Це означає, якщо в комірці А2 число менше або дорівнює числу 100 (тобто ИСТИНА), формула (функція) набуває значення «У межах бюджету». В іншому випадку - «Поза бюджетом».

Функція И повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення ЛОЖЬ.

Синтаксис: И (логічне_значення 1; логічне_значення 2;...)

Логічне_значення 1, логічне_значення 2,... – це від 1 до 30 умов, що перевіряються, і які можуть мати значення або ЛОЖЬ, або ИСТИНА. Наприклад, = И(2+2=4; 2+3=5), результат - ИСТИНА.

Функція ИЛИ повертає значення ИСТИНА, якщо хоча б один із аргументів має значення ИСТИНА; повертає ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.

Синтаксис: ИЛИ (логічне_значення 1; логічне_значення 2;...)

Логічне_значення 1, логічне_значення 2,... – від 1 до 30 умов, що перевіряються, і які можуть мати значення або ИСТИНА, або ЛОЖЬ. Наприклад, = ИЛИ(2+1=4; 2+3=5), результат - ИСТИНА.

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

Синтаксис: НЕ (логічне_значення)

Логічне_значення - величина або вираз, які можуть набувати двох значення: ИСТИНА або ЛОЖЬ. Наприклад, = НЕ(1+1=2), результат - ЛОЖЬ.

Функції ИСТИНА, ЛОЖЬ аргументів не мають і безпосередньо вводяться у комірки.

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 2.9

Тема заняття: Редагування діаграм і графіків.

Мета: Навчитися редагувати діаграми і графіки; закріпити навички побудови таблиць і діаграм.

Завдання 1. Створення та редагування таблиці.

1. Завантажте Microsoft Excel.

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

 

 

	Таблиця 1. Розрахунок амортизаційних відшкодувань
	Вид основних фондів	Середньорічна вартість основних фондів, грн.	Норма амортизаційних відшкодувань, %	Сума амортизаційних відшкодувань, грн.
	загальна	у тому числі	усього	у тому числі
	на реновацію	на капітальний ремонт	на реновацію	на капітальний ремонт
	Будівлі				6,6
	Механічне обладнання			12,5	2,5
	Холодильне обладнання			8,4	3,6
	Автоматичне і напівавтоматичне обладнання			12,5	2,5
	Інше торговельне обладнання			12,5	3,2
	Усього

 

2. Установіть розмір аркуша А4, орієнтація – книжна, береги: лівий, верхній, нижній - 2 см, правий – 1,5 см.

3. Спочатку заповніть 6-й рядок робочого аркуша номерами граф таблиці: введіть у клітинку А6 число 1 і, тримаючи клавішу Ctrl, протягніть праворуч за маркер заповнення до графи 8. Ви зробили це для того, щоб далі, під час виділення клітинок головки таблиці, не треба було рахувати, скільки стовпців необхідно виділити.

4. Для головки таблиці установіть вирівнювання за горизонталлю і вертикаллю – по центру командою Формат►Ячейки►Выравнивание. Установіть також прапорець Переносить по словам. Виконайте потрібні об’єднання для граф таблиці з 1-ої по 5-ту. Для граф з 6-ої по 8-му об’єднання поки не виконуйте.

5. Зверніть увагу, що клітинки діапазону F3:Н5 об’єднані так само, як і клітинки С3:Е5. Тому скористайтеся таким прийомом. Виділіть діапазон С3:Е5. На панелі Стандартная клацніть по піктограмі Формат по образцу (). Діапазон буде обведений пунктирною лінією. Клацніть по першій клітинці діапазону F3:Н5, тобто по F3. Клітинки діапазону F3:Н5 будуть об’єднані таким же чином, як і С3:Е5.

6. Заповніть у головці таблиці графи з 1-ої по 5-ту. Виділіть діапазон D4:Е5 і правою кнопкою миші перетягніть його на діапазон G4:H5, щоб скопіювати найменування граф. Заповніть повністю головку таблиці. Уведіть заголовок таблиці.

7. Для таблиці установіть розмір шрифту 10, а для заголовку - 12.

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

 

Завдання 2. Виконання розрахунків у таблиці.

1. Заповніть таблицю даними. Виконайте розрахунки у таблиці.

2. Виконайте обмеження таблиці. Використовуючи вкладку Граница діалогового вікна Формат ячеек команди Формат► Ячейки установіть обмеження головки та останнього рядка таблиці (Усього) подвійною лінією. Виконайте також заливку головки та останнього рядка таблиці світло-блакитним кольором.

 

Завдання 3. Побудова діаграми

1. Виділіть клітинки з даними 7-ої та 8-ої граф (крім рядка 6-го та «Усього»), включаючи найменування граф. Клацніть по піктограмі Мастер диаграмм, щоб побудувати діаграму. Оберіть тип діаграми – Гистограмма (трьохвимірна). На вкладці Ряд діалогового вікна, яке з’являється на другому кроці побудови діаграми, у віконці Подписи по оси Х уведіть діапазон клітинок з найменуваннями видів основних фондів (перша графа таблиці). Натисніть кнопку Далее.

2. Установіть заголовки: для діаграми – ­«Амортизаційні відшкодування», для Осі Х – «Види основних фондів», для Осі Z – «Сума, грн». Діаграму розмістіть під таблицею.

 

Завдання 4. Редагування діаграми. _­

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

2. Зробіть так, щоб текст «Сума, грн» був розташований вертикально.

3. Установіть розмір шрифту: для заголовка – 12, для підписів осей – 10, для осей – 8.

4. Клацніть правою кнопкою миші по одному зі стовпчиків діаграми й у контекстному меню оберіть пункт Формат рядов данных. У діалоговому вікні, що з’явилося, на вкладці Порядок рядов поміняйте місцями ряди “на реновацію” та “на капітальний ремонт”.

5. У тому ж вікні на вкладці Параметры установіть: Глубина зазора –50, Ширина зазора – 50.

6. Оберіть пункт меню Диаграмма ►Объемный вид. Установіть Возвышение – 25. Установіть також прапорець Изометрия.

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

8. Оберіть пункт меню Диаграмма ►Параметры диаграммы. У діалоговому вікні на вкладці Подписи данных установіть прапорець Включить в подписи - значение. Клацніть по числам, що з’явилися, й установіть розмір шрифту – 8, жирний. Перемістіть вручну підписи даних. Діаграма повинна мати вигляд як на рисунку 1.

Рис. 1. Амортизаційні відшкодування

 

Завдання 5. Побудова таблиці та графіка.

Прочитайте задачу. Фірма МКС продала у звітний період комп’ютери різних марок (див. таблицю 2). Необхідно визначити суму продажу, побудувати графік залежності плану й суми продажу від типу процесора, а також відредагувати графік відповідно до наведеного.

1. Для розв’язання задачі побудуйте таблицю відповідно до наведеної (таблиця 2) й виконайте обчислення на другому листі.

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

Таблиця 2. Продаж комп’ютерів

Завдання 6. Редагування графіка.

1. У контекстному меню графіка Сума продажу активуйте команду Формат рядов данных... і на вкладці Подписи данных виділіть прапорець Значение. Залиштев “ Области построения” 2 числа (інші потрібно видалити)й замініть їх словами ПЛАН і СУМА ПРОДАЖУ. Надпис СУМА ПРОДАЖУ перемістіть у верх діаграми, а ПЛАН у низ, як показанонаграфіку.

2. У контекстному меню осі У активуйте команду Формат оси і на вкладці Шкала встановіть мінімальне значення 30000, а максимальне 65000.
Клацніть правою кнопкою миші по лінії графіка «ПЛАН». У контекстному меню активуйте команду Формат рядов данных. У діалоговому вікні оберіть вкладку Вид.

3. На вкладці Вид установіть тип лінії – пунктир; товщина – найбільша; колір – червоний.

4. Установіть тип маркера у вигляді трикутника - D; розмір – 9 пт; колір –червоний.

5. Аналогічно встановіть для другої лінії графіка: тип – безперервна, товщина – максимальна, маркер – квадрат, колір - чорний, розмір – 9 пт.

 

Завдання 7. Визначення «погрішності».

1. Клацніть правою кнопкою миші по лінії графіка ПЛАН. У контекстному
меню активуйте команду Формат рядов данных, а потім вкладку Y- погрешность.
На вкладці встановіть “ Мінус” погрішність. Потім ОК.

2. Клацніть правою кнопкою миші по погрішності ПЛАНУ. В контекстному меню активуйте команду Формат полос погрешности, потімвкладку Вид. Установіть для погрішності тип лінії – безперервна, товщина – максимальна, колір – червоний.

3. Аналогічно для другої лінії графіка встановіть “ Плюс” погрішність, колір – чорний, товщина – максимальна. Графік повинен мати вигляд як на рисунку 2.

Рис. 2. План і сума продажу

Завдання 8. Збереження та друк результатів.

1. Збережіть результати роботи у папці Група ** у файлі з ім’ям Редагування діаграми і графіка.

2. Результати виведіть на друк.

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 2.10

Тема заняття: обчислення питомої ваги в табличному процесорі Excel.

Мета: Отримати практичні навички з формування таблиць та виконання фінансово-економічних розрахунків у додатку Excel.

Обчислення питомої ваги кредитів.

1. Завантажте додаток Excel.

2. Установіть Параметры страницы: формат – А4, усі береги – 2 см. Для робочого поля встановіть формат комірок: шрифт – Times New Roman розмір – 12.

3. Побудуйте Таблицю 1. Для введення місяців скористайтеся списками Excel.

4. У комірці К4 знайдіть суму числового діапазону { B4:J4 }.

5. У комірці В5 обчисліть питому вагу за допомогою формули =В4/К4*100, яку відредагуйте, щоб вона мала вигляд =В4/$К$4*100, скориставшися клавішею F4.

6. Для обчислення питомої ваги у комірці С5 (та в інших комірках рядка) скористайтеся маркером автозаповнення комірки В5.

7. Знайдіть суму в комірці К5, яка повинна дорівнювати 100 %.

8. Результати збережіть у папці Група*, у файлі Питома вага.

 

	A	B	C	D	E	F	G	H	I	J	К
	Таблиця 1. Питома вага кредитів
	Місяць	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Сума
	Кредит, у.о.
	Питома вага кредитів, %

 

 

Розділ 3. Спеціальні прийоми роботи в табличному процесорі

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 3.1

Тема заняття: Спеціальні прийоми роботи в середовищі Excel: транспонування таблиць, підбір параметрів.

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

Теоретичні відомості про спеціальні прийми під час роботи з таблицями в Excel.

1. Теоретичні відомості щодо транспонування таблиць (матриць) у роботі з таблицями в Excel.

Транспонування таблиць являє собою переставляння рядків і стовпців. Для транспонування таблиць необхідно виконати такі дії:

- виділити таблицю;

- виконати копіювання таблиці;

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

- виконати команди: Правка ►Специальная вставка ►Транспонировать (установити прапорець).

2. До спеціальних прийомів відноситься також можливість виконувати підбір параметра у таблиці в автоматичному режимі (рис. 1).

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

· Підбір параметра виконується для комірок, які пов’язані формулою.

· Робимо копію таблиці, з якою і працюємо.

· Розташовуємо курсор у комірці з формулою, наприклад, де зазначена сума кредитів.

· У пункті меню Сервис обираємо команду Подбор параметра.

· У діалогову вікні у рядок Значение вводимо необхідне число.

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

 

Рис. 1. Вікно Подбор параметров

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 3.2

Тема заняття: Статистичний аналіз даних.

Мета: Оволодіти навичками статистичного регресивного аналізу даних за допомогою додатка Excel.

Теоретичні відомості щодо статистичного регресивного аналізу даних.

1. Прочитайте наведений нижче текст щодо статистичного регресивного аналізу даних.

Програма Excel має статистичні функції ТЕНДЕНЦИЯ, РОСТ, ПРЕДСКАЗ.

ТЕНДЕНЦИЯ. Повертає значення відповідно до лінійного тренда. Апроксимує прямою лінією (за методом найменших квадратів) масиви: відомі значення y і відомі значення x. Повертає значення y, відповідно до цієї прямої для заданого масиву – нових значень x. Синтаксис: ТЕНДЕНЦИЯ (известные значения y; известные значения x; новые значения x; конст).

РОСТ – Розраховує прогнозоване експонентне зростання на підставі наявних даних. Синтаксис: РОСТ (известные значения y; известные значения x; новые значения x; конст)

ПРЕДСКАЗ – Обчислює чи передбачає майбутнє значення за існуючим значенням. Відомі значення – це x і y, а нове значення у для нового відомого значення x передбачається з використанням лінійної регресії. Синтаксис: ПРЕДСКАЗ (x; известные значения y; известные значения x).

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ №3.3

Тема заняття: ОПТИМІЗАЦІЯ ПОШУКУ РІШЕННЯ У ФІНАНСОВО - ЕКОНОМІЧНИХ ЗАДАЧАХ

Мета: Навчитися оптимізувати рішення у фінансово-економічних задачах в середовищі Excel.

Зміст заняття

1. Теоретичні відомості що до задач оптимізації.

2. Постановка задачі.

3. Створення економіко - математичної моделі задачі.

4. Формування вхідних та проміжних даних до задачі.

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

6. Графічний аналіз результатів.

7. Вивід результатів лабораторної роботи до друку.

Завдання та порядок їх виконання

Завдання 1. Теоретичні відомості що до задач оптимізації

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

Знайти змінні x₁,x₂, …, x_n, які задовольняють системі нерівностей або рівнянь, тобто являються обмеженням для задачі оптимізації

j_i (x₁, x₂, x₃, …, x_n) <= b_i, i = 1, 2, …, m

і при цьому досягає максимуму (мінімуму чи заданого значення) цільова функція

Z = f (x₁,x₂, …, x_n) ® max (min, задане значення).

Умови позитивності змінних, якщо вони є теж, входять в обмеження: x₁>=0, x₂>=0, x₃>=0, …, x_n>=0.

Завдання 2. Постановка задачі (Записати у зошит).

Задача. Розглянемо задачу про управління портфелем активів. Нехай перед деяким інвестором стоїть проблема прийняття рішення про вкладення капіталу який у нього є. Набір характеристик потенційних об’єктів для інвестування, мають умовні імена від A до F, задаються у таблиці 1. Прийняття рішень залежить від максимального прибутку вкладених коштів інвестором.

Таблиця 1. Параметри умовних об’єктів

Назва об’єктів	Прибутковість, %	Строк погашення, рік	Надійність, бали
Фірма А	5, 5
Фірма B	6,0
Фірма C	8,0
Фірма D	7,5
Фірма E	5,5
Фірма F	7,0

Для прийняття рішень при придбанні активів повинні бути дотримані певні умови: 1. Сумарний об’єм капіталу, який повинен бути вкладений, складає $100 000. 2. Доля вкладених коштів в один об’єкт не може перевищувати $25 000. 3. Половина або більша половина коштів від сумарного об’єму капіталу повинна бути вкладена у довгострокові активи (>=50 000) зі строком погашення 2016 -2018 р. 4. В активи, які мають надійність менше ніж 4 бали, вкладення коштів не повинно перевищувати $30000.

Завдання 3. Створення економіко - математичної моделі задачі

Позначено вкладені кошти в активи фірм як Xa, Xb, Xc, Xd, Xe, Xf. Тоді сумарний прибуток можна представити у такому виді: P= 0,055*Xa+ 0,06*Xb+0,08*Xc+0,075* Xd+0,055* Xe+0,07* Xf. Обмеження за умовами задачі: 1. Обмеження на всю суму коштів: Xa+Xb+ Xc+ Xd+ Xe+ Xf <=100000. 2. Обмеження на розмір долі коштів одного об’єкта: Xa<=25000, Xb<=25000, Xc<=25000, Xd<=25000, Xe<=25000, Xf <=25000. 3. Обмеження для довгострокових активів:

Xb+Xc>=50000. 4.Обмеження для ненадійних об’єктів: Xc+Xd<=30000. 5. У мова на невід’ємність змінних, які потрібно знайти: Xa>0, Xb>0, Xc>0, Xd>0, Xe>0, Xf >0.

Завдання 4. Формування вхідних та проміжних даних до задачі

1. У середовищі Excel на першому Листі введіть вхідні дані як на рисунку 1, це поки що не оптимальні значення. Оптимальні знайдемо пізніше.

2. У комірку G 3 (може бути будь - яка вільна комірка ) уведіть формулу цільової функції: = 0,055*A3 + 0,06*B3+ 0,08*C3 + 0,075* D3 + 0,055* E3 + 0,07* F3

3. У комірку D7 уведіть формулу для розрахунку обмеження для відповідних даних: = A3+B3+ C3+ D3+ E3+ F3, у комірку D8 уведіть другу формулу для обмеження = B3+ C3 у комірку D9 уведіть третю формулу = C3+ D3.

Рисунок 1.

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

1. Активізуйте команду Поиск решения із Сервису.

2. У діалоговому вікні Поиск решения введіть комірку з цільовою функцією і установіть
перемикач для Максимального значення. Адресу комірки $ G $3 з цільовою функцією записати у зошит.

3. Визначить комірки для зміни $A$3:$F$3 та запишіть їх у зошит.

4. Додайте обмеження (кнопка Добавить): $ A$3<=25000, $A$3>0, $B$3<=25000, $B$3>0, $C$3<=25000, $C$3>0, $D$3<=25000, $D$3>0, $E$3<=25000, $E$3>0, $F$3 <=25000, $F$3 >0; D7<=100000; D8>=50000; D9<=30000.

5. Після введення даних клацніть по кнопці Выполнить. Переглянете результати у таблиці і зробіть висновки. Результати занести у зошит.

6. Закрийте вікно Результаты поиска решения (установивши перемикач Сохранит найденное решение), клацнув по кнопці ОК.

7. Результати збережіть у папці Група*Прізвище на диску з найбільшим вільним об’ємом у файлі ОптимізаціяПрізвище.

Завдання 6. Графічний аналіз результатів

1. Побудуйте гістограму (рис. 2) залежності Обсягу оптимальних інвестицій від умовних об’єктів. Збережіть результати у файлі ОптимізаціяПрізвище.

Рисунок 2.

Завдання 7. Вивід результатів лабораторної роботи до друку

 

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 3.4

Тема заняття: Пошук рішення у фінансово-економічних задач у середовищі Excel.

Мета: Навчитися робити оптимізацію пошуку розв’язку фінансово-економічних задач.

Завдання та порядок їх виконання

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

Задача (варіанти 1- 10, N:= 1 – 10)

У І-IV кварталах минулого року на підприємстві були отримані економічні показники, подані в таблиці 1. Прибуток підприємства склався із різниці між обсягом продукції і накладними видатками, рекламою, витратами. У наступному році необхідно одержати максимальний прибуток за рік, змінюючи квартальні бюджети за накладними видатками за умови, що річний бюджет за накладними видатками буде не більше 94000 грн і не менше 93000 грн. Необхідно одержати максимальний прибуток за рік, змінюючи також квартальні бюджети за витратами за умови, що річний бюджет за витратами буде не більше 395000 грн і не менше 390000 грн.

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 3.5

Тема заняття: Обмін даними з використанням принципу Ole.

Мета: Навчитися робити обмін даними з використанням принципу Ole.

Забезпечення заняття: персональний комп’ютер, принтер, операційна система Windows, табличний процесор Excel.

Теоретичні відомості щодо обміну даними між додатками

Прочитайте наведений нижче текст щодо обміну даними:

MS Offiсe дозволяє уводити дані, створені в одному додатку, в інший додаток, наприклад, діаграму, створену в додатку Excel, можна уводити в документ Word. Зв’язування та уведення - два методи обміну даними. Зв’язування використовується для динамічного відновлення даних у редакторі Word під час їхньої зміни в Excel, це і є принцип OLE. Для зв’язування даних використовується команда Правка ►Специальная вставка ► Связать ( перемикач ) -це коли дані Word із буфера пам’яті увдодяться в документWordабо Excel, чи навпаки.Якщо дані Excel уводяться з буфера пам’яті в документ Excel, то є можливість установити у вікні Специальная вставка перемикачі як на рис. 1. Якщо вказується операція, то вона виконується з даними, які є у буфері пам’яті та з тими, що копіюються.

Якщо виконується уведення даних з буферу пам’яті без зв’язування, тоді виконується команда: Правка ► Специальная вставка ► Вставить (рис. 1).

 

Рис. 1. Спеціальна вставка

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 3.6

Тема заняття: Фінансові функції в табличному процесорі Excel.

Мета: Сформувати практичні навички роботи з фінансовими функціями Excel та навички з розв’язування фінансово-економічних задач.

Робота з довідкою для фінансових функцій Excel.

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

2. Завантажте додаток Excel. Ознайомтеся з довідковим матеріалом для фінансових функцій: ПС, АПЛ, АСЧ, ДДОБ, ПУО, ФУО.

3. Запишіть у зошиті синтаксис фінансових функцій.

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 3.7

Тема заняття: Рішення фінансово-економічних задач.

Мета: Отримати практичні навички щодо виконання фінансово-економічних розрахунків в Excel.

Завдання 1. Розрахунки рентабельності.

1. Побудуйте на Листе 1 Таблицу 1 і виконайте у ній розрахунки.

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

 

Таблиця 1. Розрахунки рентабельності виробничих фондів

Показники	Минулий рік	Звітний рік	Відхилення (+,-) проти	Виконання плану, %
за планом	фактично	попереднього року	плану
1 Балансовий прибуток, тис. грн.
2 Середньорічна величина виробничих фондів, тис. грн
3 Обсяг реалізації продукції, тис. грн.
4 Рентабельність підприємства, % (р.1:р.2*100)
5 Реалізація на 1 грн. виробничих фондів, коп. (р.3:р.2*100)
6 Прибуток на 1 грн. реалізації, коп. (р.1:р.3*100)

 

Завдання 2. Аналіз фондовіддачі.

1. Побудуйте на Листе 2 Таблицу 2 і виконайте у ній розрахунки. Результати збережіть.

Таблиця 2. Аналіз фондовіддачі

Показники	За планом	Фактично	Відхилення (+,-)	Відхилення, %
1. Обсяг товарної продукції, тис. грн.
2. Середньорічна вартість основних виробничих фондів, тис. грн.		969,3
3. Виробничі площі, м2
4. Кількість верстатів, шт.
5. Кількість годин, відпрацьованих усіма верстатами за рік
6. З них у першу зміну
Розрахункові показники
7. Фондовіддача (рядок 1/рядок 2)
8. Кількість годин, відпрацьованих одним верстатом за рік (р.5/р.4)
9. Випуск продукції на одиницю площі, грн./м2 (р.1/р.3)
10. Середньорічний виробіток одного верстата, грн. (р.1/р.4)
11. Середній виробіток за 1 верстатогод., грн. (р.1/р.5)
12. Коефіцієнт змінності (р.5/р.6)

Завдання 3. Аналіз продуктивності праці.

1. Завантажте додаток Excel. Побудуйте на Листе 3 Таблицу 3 і виконайте у ній розрахунки.

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

Таблиця 3. Аналіз продуктивності праці

Показники	За планом	Фактично	Відхилення (+,-)	Виконання плану, %
1. Товарна продукція, тис. грн.
2. Кількість працівників
3. З них робітників
4. Загальна кількість відпрацьованих усіма робітниками: а) людино-днів б) людино-годин
Розрахункові показники
5. Кількість відпрацьованих днів одним робітником (р.4а/р.3)
6. Тривалість робочого дня (р.4б/р.4а)

Завдання 4. Аналіз виконання плану.

1. Побудуйте на Листе 4 Таблицу 4 і виконайте у ній розрахунки. Результати збережіть.

Таблиця 4. Аналіз виконання плану (тис. грн.)

Показники	За планом	Фактично	Абсолютне відхилення	Відносне відхилення
1. Залишки нереалізованої продукції на початок періоду
2. Випуск товарної продукції
3. Реалізації товарної продукції
4. Залишки реалізованої продукції на кінець періоду
5. Зміна залишків нереалізованої продукції упродовж звітного періоду (р.4 - р.1)

Завдання 5. Аналіз ритмічності виробництва.

1. Побудуйте на Листе 5 Таблицу 5 і виконайте у ній розрахунки. Результати збережіть.

2. Усі результати роботи виведіть на друк (таблиці 1-5).

Таблиця 5. Аналіз ритмічності виробництва

Період	Випуск продукції, тис. грн.	Приріст виробництва
за планом	фактично	(+,-)	відносне
За 1 декаду
За 2 декаду
За 3 декаду
За 4 декаду
Разом

 

Розділ 4. Програмні засоби роботи з базами та сховищами даних

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 4.1

 

Тема заняття: Створення таблиць реляційної бази даних.

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

Теоретичні відомості щодо створення таблиць БД СКБД Access.

Для створення нової таблиці необхідно виконати такі дії: відкрити потрібну БД, активізувати вкладку Таблицы та клацнути по кнопці Создать,у вікні, що відкрилося (Новая база),установити один із режимів створення таблиці: Режим таблицы, Конструктор, Мастер таблиц, Импорт таблиц (шляхом імпорту таблиць із зовнішнього файлу в поточну базу даних), Связь с таблицами (створення таблиць у поточній базі даних, пов’язаних з таблицями зовнішнього файлу). Перехід з режиму конструктора в режим таблиці або навпаки здійснюється через контекстне меню або шляхом вибору команди Вид у рядку головного меню.

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

Таблиця складається з полів та записів. Запис - група взаємопов’язаних елементів даних, які розглядаються як єдине ціле або можна сказати так: певна інформація про один об’єкт, що міститься в таблиці бази даних. Поле – поіменована частина запису бази даних. Таблиця може мати ключове поле, що містить унікальне значення (коди, номери, шифри). Ключове поле не може мати нульових значень або значень, які повторюються. Воно однозначно фіксує запис та використовується для створення зв’язків між таблицями. Для визначення поля ключовим треба виділити потрібне поле, активізувати команди Правка ► Ключевое поле або кнопку Ключевое поле на панелі інструментів, або скористатися контекстним меню.

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 4.2

Тема заняття: Редагування таблиць реляційної бази даних Access.

Мета: Навчитися редагувати таблиці реляційної бази даних за допомогою СКБД Microsoft Office Access.

Завдання 1. Відкриття створеної бази даних.

1. На панелі задач активуйте кнопку Пуск, потім Программы ► Microsoft Office Access.

2. У вікні програми Microsoft Access на панелі області задач клацніть по назві бази даних Університет** або відкрийте створену базу даних, скориставшись командою Файл ►Открыть.

Завдання 2. Створення таблиці шляхом копіювання інформації з іншої таблиці

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

1. У вікні бази даних Університет** на вкладці Таблицы виділітьтаблицю Студенти, клацніть по кнопці Копировать, а потім по кнопці Вставить. У вікні Вставка таблицы введіть ім'я Успішність. Зверніть увагу, що встановлена кнопка-перемикач Структура и Данные. Це означає, що в таблицю Успішність буде скопійована структура таблиці Студенти, а також усі записи, що містяться в ній. Клацніть по кнопці ОК. На вкладці Таблицы з'явиться таблиця Успішність, при чому вона буде виділена.

2. Клацніть по кнопці Конструктор, щоб увійти в режим проектування і змінити структуру таблиці Успішність:

§ Поле Номер залікової книжки зробіть неключовим.

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

§ Додайте до структури таблиці 3 поля: Математика, Інформатика, Бухоблік. Тип даних для всіх полів - числовий.

§ Клацніть по колонці Тип данных у полі Прізвище й оберіть Мастер подстановок. У вікні, що з'явилося, оберіть кнопку Объект «столбец подстановки» будет использовать столбец подстановки из таблицы или запроса і клацніть по кнопці Далее. У наступному вікні оберіть таблицю Студенти і клацніть по кнопці Далее. У наступному вікні оберіть поле Прізвище, для чого клацніть по кнопці >, а потім по кнопці Далее. У наступному вікні клацніть по кнопці Готово. Таким чином, ви зв'язали поле з таблицею Студенти, тобто надалі під час уведення (у режимі таблиці) інформації в це поле, ви будете вибирати значення зі списку, який складається із записів, що містяться в таблиці Студенти. У вікні Свойства поля на вкладці Подстановка установіть Ограничиться списком – Да. Додати ключове поле Счетчик.

§ Перейдіть у режим таблиці й уведіть оцінки з предметів.

 

Завдання 3. Редагування таблиці в режимі таблиці.

1. Установіть курсор у стовпчик Математика і в меню Вставка оберіть Столбец. У таблиці з'явиться новий стовпчик з ім'ям Поле1. Клацніть двічі по заголовку стовпця, щоб перейменувати його. Перейменувати стовпець можна також, обравши в меню Формат пункт Переименовать столбец. Уведіть ім'я стовпця Семестр. Уведіть таким же чином ще один стовпчик, а потім видаліть його, обравши в меню Правка пункт Удалить столбец.

 

Завдання 4. Редагування таблиці в режимі конструктора

1. Перейдіть у режим конструктора, клацнувши по кнопці Вид на панелі інструментів.

2. У полі Семестр змініть Тип данных на Числовой.

3. Додайте ще одне поле перед полем Бухоблік, для чого встановіть курсор у поле Бухоблік і в меню Вставка оберіть пункт Строки. Ім'я поля - Статистика, тип даних - числовий.

4. Збережіть таблицю. Перейдіть у режим таблиці. Введіть оцінки зі статистики для всіх студентів.

5. Закрийте таблицю.

 

Завдання 5. Створення та редагування зв'язків між таблицями.

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

2. Видаліть зв'язок між таблицями Студенти й Успішність. Для цього виділіть зв'язок, клацнувши по лінії зв'язку, а потім натисніть клавішу DEL. Ще раз створіть зв'язок, для чого поле Номер залікової книжки з таблиці Студенти відбуксуйте на однойменне поле в таблиці Успішність. У вікні Связи встановіть прапорці Обеспечение целостности данных, Каскадное обновление связанных полей і Каскадное удаление связанных записей. Клацніть по кнопці Создать.

3. Відредагуйте зв'язок між таблицями Групи і Студенти. Для цього двічі клацніть по зв'язку або виділіть зв'язок, а потім оберіть з меню Связи пункт Изменить связь. Установіть прапорці Обеспечение целостности данных і Каскадное обновление связанных полей і клацніть по кнопці ОК.

4. Закрийте вікно Схема данных.

 

Завдання 6. Редагування таблиці Бібліотека.

1. Відкрийте таблицю Бібліотека в режимі Конструктор, для поля Дисципліна у колонці Тип данных в полі Свойства поля на вкладці Подстановка установіть: Тип элемента управления – Поле со списком, Тип источника строк – Список значений, Источник строк – уведіть назви дисциплін через символ “;”. Збережіть таблицю.

2. Уведіть інформацію в таблицю Бібліотека (не менше 5 записів).

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОГО ЗАНЯТТЯ

№ 4.3

Тема заняття: Побудова запитів у СКБД Access.

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

Завдання 1. Відкиття бази даних.

1. Запустіть Microsoft Access і відкрийте свою базу даних Університет**.

 

Завдання 2. Створення запиту для сортування даних у таблиці.

У вікні бази даних виділіть вкладку Запросы і клацніть по кнопці Создать.

У вікні Новый запрос виділіть Конструктор і клацніть ОК.

У вікні Добавление таблицы оберіть таблицю Студенти і клацніть Добавить, потім закрийте вікно.

У вікні Запрос1: запрос на выборку в першій колонці установіть курсор в рядок Поле. Натисніть на покажчик списку та виберіть зі списку ім'я поля Номер залікової книжки, ім'я таблиці Студенти установиться автоматично.

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

Збережіть запит з ім'ям Сортування.

Побудуй