Рекомендації до виконання завдань

Ці рекомендації доцільні у випадку використання програмного середовища MS Excel. Для інших програмних середовищ керівники пропонують свою методику розв’язування завдань.

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

Завдання 1.

Після активізації середовища MS Excel у ньому автоматично створюється нова робоча книга, яка називається book1.xls. За допомогою команди Save As… (меню File) запишіть у власний каталог поки що порожні електронні таблиці у файл, для якого визначте унікальне ім’я (наприклад, еkb11_12.xls, де ekb11 – ідентифікатор групи, 12 – порядковий номер студента в групі). Визначивши назву файлу, усі наступні його збереження виконуйте за допомогою команди Save (меню File) або відповідної кнопки панелі інструментів.

На першому аркуші (Sheet1) побудуйте таблицю 1:

	A	B	C	D
	База	Магазин	Товар	Кількість поставленого товару
	Б1	М1	Т1
	Б1	М1	Т4
M	M	M	M	M
	Б4	М1	Т1

Перед уведенням даних у таблицю задайте українську абетку, а засобами панелі інструментів – поточні характеристики шрифту, наприклад, гарнітуру Times New Roman, висоту 12, а також зручний масштаб відображення таблиці, наприклад, 100%.

Визначте відповідну ширину стовпця таблиці. Це можна зробити пересуваючи мишкою межі комірок з назвами стовпця або застосувавши до попередньо виокремленого стовпця команду Width… підменю Column (меню Format).

Пересувайтеся комірками таблиці за допомогою мишки, клавіш керування курсором (,­,®,¯) чи клавіші Tab.

За допомогою команди Cells… (меню Format) активізуйте діалогове вікно, через яке задайте режими форматування виокремленої комірки.

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

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

Перейдіть на другий аркуш. Для цього активізуйте корінець аркуша Sheet2. Аналогічно створіть таблицю 2 і заповніть її даними, що відповідають індивідуальному завданню. Наприклад, студент з порядковим номером 40 повинен створити таблицю з такими даними:

	А	В
	Товар	Ціна, грн.
	T1
	T2
	T3
	T4
	T5

1.1. Поверніться на аркуш 1. Виокремте всі комірки таблиці 1 (А1:D9). Скопіюйте виокремлений фрагмент у буфер обміну за допомогою команди Copy (меню Edit) чи відповідної кнопки панелі інструментів. Перейдіть на третій аркуш (Sheet3) і виокремте деяку комірку, наприклад, А1. За допомогою команди Paste (меню Edit) чи відповідної кнопки панелі інструментів відновіть, починаючи з виокремленої комірки, дані, які є у буфері обміну (таблиця 1).

Сформуйте таблицю 3. Для цього після розширення стовпця D і форматування стовпця Е запишіть у комірці Е1 заголовок останнього стовпця таблиці 3 - "Вартість поставленого товару". Обведіть рамкою новоутворену таблицю.

Для обчислення вартості поставленого товару в комірці Е1 запишіть формулу

=D2*VLOOKUP(C2;SHEET2!A$2:B$6;2)

У результаті обчислення за цією формулою знаходимо добуток значення кількості поставленого товару і значення ціни товару. Значення кількості беремо з таблиці 3 (комірка D2), а значення ціни товару, який є в комірці С2, вибираємо із другого стовпця таблиці 2 (SHEET2!A$2:B$6) в результаті перегляду цієї таблиці (функція VLOOKUP).

Зауваження 1. У деяких версіях MS Excel розділювачем між аргументами функцій є не крапка з комою (;), а кома (,).

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

Записану формулу за допомогою послідовного виконання команд Copy і Paste (меню Edit) або за допомогою мишки поширте на діапазон комірок Е2:Е9. За допомогою мишки це виконують так: переводять вказівник мишки у правий нижній кут поточної комірки (де зображення чорного квадратика на перерізі меж комірок); зафіксовують цю позицію, натискаючи на клавішу мишки; не відпускаючи клавіші, перетягують вказівник до кінця діапазону (комірка Е9).

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

Зауваження 4. Будьте уважними щодо використання української та латинської абетки під час створення таблиць 1 і 2, а також запису аргументів наведених нижче формул. Потрібно, щоб літери Т і М були з однієї абетки.

1.2. Двічі скопіюйте таблицю 3 на аркуш 4 (Sheet4) так, щоб одна копія (таблиця 4) починалася, наприклад, з комірки А1, а друга (таблиця 5) – з комірки А20.

Виокремте таблицю 4. Активізуйте команду Subtotals… (меню Data). У діалоговому вікні у відповідних полях: зазначте стовпчик, щодо якого робити підсумки, (поле At Each Change in:) – База; визначте функцію, за якою виконуватимуться обчислення (поле Use Function) – Sum; зазначте стовпець, дані якого є аргументами функції (поле Add Subtotal) – Вартість поставленого товару.

У результаті виконаних дій у таблиці 4 буде підраховано вартість поставок по кожній базі зокрема (наприклад, для бази Б1 – рядок Б1 Total), а також загальна вартість товарів по всіх базах (рядок Grand Total).

Виокремте таблицю 5. Активізуйте команду Sort… (меню Data). У діалоговому вікні в полі Sort by зазначте стовпець, щодо якого сортуватиметься таблиця – Магазин, а також задайте перемикач Ascending, який визначає критерій сортування (за зростанням). Після виконаних операцій дані таблиці 5 буде посортовано за номерами магазинів.

З використанням команди Subtotals… (меню Data) стосовно стовпця Магазин таблиці 5 визначте вартість поставок по кожному магазину.

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

1.3. Поверніться на третій аркуш (Sheet3). Під останнім рядком таблиці 3 (комірка А10) запишіть текст "Кількість поставок вартістю понад 1000грн.”, а у комірці Е10 запишіть формулу

=COUNTIF(E2:E9;“>1000”)

У результаті використання статистичної функції COUNTIF буде обчислено серед даних діапазону E2:E9 кількість чисел, значення яких більші 1000. У випадку k =45 результатом обчислення буде 2.

У комірці А11 запишіть текст "Вартість поставок товару T1", а у комірці Е11 – формулу

=SUMIF(C2:C9;”Т1”;E2:E9)

За допомогою математичної функції SUMIF буде обчислено суму тих чисел з діапазону E2:E9, які відповідають значенню товару Т1 (Т1 вибирають з діапазону C2:C9). У випадку k =45 результат обчислення за цією формулою – 3850.

Дві останні формули можна набрати, послідовно вводячи всі символи за допомогою клавіатури або застосовуючи майстер функцій. В останньому випадку після активізації майстра функцій (команда Function… меню Insert або відповідна кнопка панелі інструментів) спочатку у діалоговому вікні Insert Function в полі Or select a category задають категорію функції, а у вікні Select a Function вибирають конкретне ім’я функції. На наступних етапах через діалогові вікна задають у відповідних полях параметри функції.

У разі вибору функції COUNTIF у діалоговому вікні треба зазначити діапазон застосування функції (поле Range) та критерій (поле Criteria); а для функції SUMIF – діапазон вибору (поле Range), критерій вибору (поле Criteria) і діапазон застосування функції (поле Sum_range).

1.4. Поверніться на перший аркуш (Sheet1). Починаючи, наприклад, з комірки А12, побудуйте критерій (krit1) для створення таблиці 6, а саме:

	A
	Krit1
	=AND(B2="M1";C2="T1")

Використання тут логічної функції AND означає, що умовою копіювання рядка з таблиці 1 у таблицю 6 є одночасна наявність у стовпці В тексту М1, а в стовпці С – Т1.

Після визначення критерію активізуйте команду Advanced Filter підменю Filter (меню Data). У діалоговому вікні задайте перемикач Copy to another location (для того, щоб нова таблиця була побудована за межами таблиці 1) і у відповідних вікнах визначте: діапазон розміщення таблиці 1 (List Range:) – А1:D9; діапазон розміщення критерію (Criteria Range:) – А12:А13; комірку, якої розміщуватиметься новостворювана таблиця 6 (Copy to:), – наприклад, А15.

Для створення таблиці 7 побудуйте критерій krit2, який розмістіть, наприклад, у комірках В12:В13, а саме:

	В
	Krit2
	=OR(C2="Т1";C2="T4")

Використання тут логічної функції OR означає, що умовою копіювання рядка з таблиці 1 у таблицю 7 є наявність у стовпці С тексту Т1 чи Т4.

Після визначення критерію знову використайте команду Advanced Filter. У діалоговому вікні зазначте:

List Range: – А1:D9

Criteria Range: – В12:В13

Copy to: – А21

Для створення таблиці 8 у комірках С12:С13 побудуйте такий критерій:

	С
	Магазин
	*

Активізуйте команду Advanced Filter. У діалоговому вікні зазначте:

List Range: – B1:B9

Criteria Range: – С12:С13

Copy to: – G1

а також задайте опцію Unique records only.

Завдання 2.

2.1. Перейдіть на п’ятий аркуш (Sheet5). Якщо в робочій книзі такого аркуша (чи будь-якого іншого) немає, то його можна створити. Для цього треба активізувати команду Worksheet (меню Insert). На п’ятому аркуші побудуйте таблицю 9, яка містить таку інформацію:

	A	B	С	…	М	….
	Кількість товару			…		…
	Попит	=1000* k /B1
	Пропозиція	=EXP(B1/10)

Щоб заповнити даними перший рядок таблиці, спочатку запишіть у комірку В1 початкове значення кількості товару, наприклад, 10. У комірці С1 задайте наступне значення кількості товару, наприклад, 15, яке дорівнює сумі початкового значення (10) та кроку зміни (5). Виокремте разом комірки В1 та С1. За допомогою мишки продовжіть отриманий ряд з двох чисел на всі наступні комірки до моменту, доки кількість товару не досягне кінцевого значення, що відповідає індивідуальному завданню, наприклад, для k =15 це число 65.

Інший спосіб продовження числового ряду – це використання меню. Після введення у відповідну комірку початкового значення ряду (у нашому випадку в комірку В1 числа 10) активізуйте команду Series… підменю Fill (меню Edit). У діалоговому вікні визначте напрям продовження ряду – по рядку (перемикач Rows у секції Series in), задайте тип прогресії для ряду – лінійну (перемикач Linear у секції Type), а також крок зміни – 5 (у полі Step Value:) і кінцеве значення ряду – 65 (у полі Stop Value:).

У комірки В2 та В3 запишіть відповідні формули для обчислення попиту і пропозиції. Записані формули поширте на увесь, відповідно, другий і третій рядок таблиці 9.

Для побудови графіка використайте майстер діаграм (Chart Wizard). Активізуйте майстер через команду Chart… (меню Insert) чи за допомогою відповідної кнопки панелі інструментів.

Будуйте графік покроково. На кожному кроці з’являтиметься відповідне діалогове вікно майстра діаграм. Переходьте від одного вікна до іншого за допомогою кнопок Next чи Back.

На першому кроці в закладці Standard Type у вікні Chart type виберіть тип діаграми, наприклад, лінійну (Line), а вікні Chart sub-type – один із підвидів у межах вибраного типу.

На другому кроці в закладці Data Range у полі Data range задайте діапазон, у якому розміщена таблиця 9. У нашому випадку це А1:М3. У секції Series in вкажіть, як розмішені дані для побудови діаграми. У нашому випадку задайте перемикач Rows. У закладці Series у вікні Series почергово виберіть окремі послідовності даних, для них у полі Name задайте адресу комірки, якою визначають назву для цієї послідовності, а у полі Values – діапазон значень для побудови діаграми.

На третьому кроці в закладці Titles у полі Chart title задайте назву діаграми, у полі Category (X) axis – назву осі абсцис, а в полі Value (Y) axis – назву осі ординат. У нашому випадку назва діаграми - Товарний ринок, назва осі абсцис – Кількість товару, а осі ординат – Ціна товару.

У закладці Axes задайте перемикачі Category (X) axis та Value (Y) axis з метою відображення координат, відповідно, для осей абсцис та ординат.

У закладці Gridlines визначте характеристики координатної сітки.

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

На четвертому кроці за допомогою перемикачів As new sheet та As object in визначте розміщення діаграми, відповідно, на окремому чи на вже створеному аркуші. У цьому разі у відповідному полі задають назви аркушів. У нашому випадку розмістіть діаграму на аркуші Sheet5.

Побудову графіка завершіть натисканням кнопку Finish. Отриманий графік за допомогою мишки розтягніть або стисніть до розумних пропорцій.

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

Для того, щоб знищити графік, потрібно виокремити його і натиснути на клавішу Del.

2.2. На п’ятому аркуші створіть таку таблицю:

	A	B
	Рівноважний випуск Q
	Попит P1	=1000* k /B11
	Пропозиція P2	=EXP(B11/10)
	Рівняння	=B12-B13

У комірці В11 задайте довільне число з інтервалу (10£ Q £50+ k). У комірках В12 та В13 запишіть формули для обчислення попиту і пропозиції, а в комірці В14 – формулу, за допомогою якої буде обчислено рівняння рівноваги 1000* k / Q =EXP(Q /10), у вигляді =B12‑B13.

Активізуйте команду Goal seek… (меню Tools). У діалоговому вікні задайте адресу комірки, для якої зазначають конкретне значення, (поле Set Cell:) – В14; число, яке визначає це значення, (поле To Value:) – 0 (у цьому випадку В12=В13); визначте адресу комірки, у якій знаходять розв’язок рівняння (поле By Changing Cell:) – В11.

Якщо k =50, то результат буде такий: рівноважний випуск Q * становить 66,26167 (комірка В11), а ціна рівноваги Р *, відповідно, – 754,5841 (комірки В12 та В13).

Завдання 3.

3.1. Перейдіть на шостий аркуш (Sheet6), де створіть таку таблицю (дані відповідають k =0):

	A	В	С
	Початковий внесок
	Відсотки	12%	12%
	Період (роки)
	Щорічний внесок (вилучення коштів)
	Майбутній внесок

У комірці В5 запишіть формулу для обчислення майбутньої вартості внеску, використовуючи фінансову функцію FV з відповідними аргументами:

=FV(В2;В3;В4;В1)

У результаті отримуєте: майбутній внесок становить 12379,82 грн.

У комірці С1 запишіть формулу для обчислення теперішнього внеску, використовуючи фінансову функцію РV з відповідними аргументами:

=РV(С2;С3;С4;С5)

У результаті отримуєте: сьогодні потрібно покласти в банк 730,79грн.

3.2. Перейдіть на сьомий аркуш (Sheet7) і створіть таку таблицю:

	A	B	C
	Порядковий № (i)	Кількість працівників (xi)	Виробіток (yi)
…	…	…	…
	Середнє значення

У комірках В32 та С32 запишіть формули для обчислення середньоарифметичного значення, відповідно, х та у, а саме:

=SUM(B2:B31)/30

=SUM(C2:C31)/30

Математична функція SUM дає змогу обчислити суму чисел визначеного діапазону.

У комірках Е2 та F2 запишіть формули для обчислення квадрата відхилення, відповідно, змінних х₁ та у₁ від отриманих середніх значень, а саме:

=(B2-B$32)^2

=(C2-C$32)^2

У комірку G2 запишіть формулу для обчислення добутку відхилень змінних х₁ та у₁ від отриманих середніх значень:

=(B2-B$32)*(C2-C$32)

Записані у комірках E2, F2 та G2 формули поширте, відповідно, на діапазони E3:E31, F3:F31 та G3:G31.

У комірках E32, F32, G32 запишіть остаточні формули для обчислення, відповідно, дисперсій s_x, s_y та коефіцієнта кореляції r_xy:

=SUM(E2:E31)/30

=SUM(F2:F31)/30

=SUM(G2:G31)/(30*SQRT(E32*F32))

Ті ж результати можна отримати з використанням статистичних функцій. У комірках B33, C33, застосовуючи функцію AVERAGE, запишіть формулу для обчислення середньоарифметичних значень змінних х та у, поточні значення яких розмішені, відповідно, у діапазонах B2:B31 та C2:C31

=AVERAGE(B2:B31)

=AVERAGE(C2:C31)

З використанням стосовно масивів В2:В31 та С2:С31 функції VARP у комірках E33 та F33 обчисліть дисперсію для змінних х та у:

=VARP(B2:B31)

=VARP(C2:C31)

Для обчислення коефіцієнта кореляції між масивами B2:B33 та C2:C33 використайте функцію CORREL. Відповідну формулу запишіть у комірку G33

=CORREL(B2:B31;C2:C31)

Для k =0 будуть такі значення: середні значення =15,5 та =150,7333, дисперсії – s_x =19,25 та s_y =2278,462, коефіцієнт кореляції r_xy =0,788812.

3.3. Перейдіть на восьмий аркуш (Sheet8). Створіть таблицю, у яку введіть коефіцієнти рівнянь (матриця А) та вільні члени (вектор ) (дані відповідають k =0):

	A	B	C	D
			-1
		-3

Для обчислення розв’язку системи методом оберненої матриці відшукайте в комірках А6:С8 обернену до А1:С3 матрицю A ^-1. Для цього послідовно виконайте такі дії. Виділіть діапазон А6:С8. У комірці А6 запишіть формулу з математичною функцією обертання матриці MINVERSE

=MINVERSE(A1:C3)

Для введення цієї формули з одночасним поширенням її на виділений діапазон натисніть на комбінацію клавіші Ctrl+Shift+Enter.

Розв’язок системи (вектор ) буде в комірках D6:D8. Попередньо виокремте їх. У комірці D6 запишіть формулу обчислення добутку двох масивів A6:C8 та D1:D3 - оберненої матриці A ^-1 та вектора вільних членів , використовуючи математичну функцію перемноження матриць MMULT

=MMULT(A6:C8;D1:D3)

Уведіть набрану формулу з поширенням на виділений діапазон (комбінація клавіш Ctrl+Shift+Enter).

Щоб застосувати формули Крамера, потрібно побудувати всі алгебричні доповнення матриці A. Виконайте це так. Скопіюйте дані з діапазону A1:C3 у діапазони H1:J3, H6:J8 та H11:J13. Скоригуйте отримані блоки даних шляхом заміни відповідних елементів компонентами вектора вільних членів . Для цього скопіюйте дані з діапазону D1:D3 у комірки Н1:Н3, I6:I8 та J11:J13.

У комірках F1, F2, F3, F4 обчисліть значення визначників, відповідно, D, D₁, D₂ та D₃. Для цього у згадані комірки запишіть формули, де використана математична функція MDETERM:

=MDETERM(A1:C3)

=MDETERM(H1:J3)

=MDETERM(H6:J8)

=MDETERM (H11:J13)

Остаточні результати (розв’язок системи) будуть у комірках F6, F7 та F8. У комірці F6 запишіть формулу для обчислення значення x₁:

=F2/F$1

Для обчислення значень x₂ та x₃ поширте цю формулу на комірки F7 та F8. У комірках F6, F7 та F8 з’являться значення змінних x₁, x₂ та x₃, які є розв’язком системи. Для k =0 буде: x₁ =10,06329, x₂ =0,632911, x₃ =5,886076.

Оформлення результатів. Отримані результати оформляють як документ, а потім друкують. Сторінки документа оформляють за допомогою команди Page Setup… меню File. У діалоговому вікні задають параметри сторінки (закладка Page), полів сторінки (закладка Margins) та аркуша (закладка Sheet), а також визначають колонтитули (закладка Header/Footer).

Перед друкуванням бажано переглянути документи на екрані. Для цього використовують команду Print Preview меню File. Щоб повернутися до режиму редагування, активізують кнопку Close.

Друкують документ за допомогою команди Print… меню File. Через діалогове вікно, яке виникає після активізації команди, визначають параметри друкування. А саме: у полі Name секції Printer задають тип друкувального пристрою; у секції Print Range – діапазон аркушів, які треба друкувати; у секції Print what – елементи робочої книги, які треба друкувати; у полі Number of Copies секції Copies – кількість копій документа. Активізують процедуру друкування натисканням на клавішу ОК.