Навчитися робити прогноз за допомогою трендових моделей. 2 страница

Варіант 4

Виробництво яловичини і телятини свіжої, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	8,2	9,1	9,0	10,0	9,7	8,8	9,8	9,4	11,1	10,2	9,3	7,5
	5,1	5,1	5,6	6,3	6,6	7,5	7,8	7,4	8,3	7,5	6,6	6,1
	3,7	5,2	6,6	7,0	7,2	7,4	7,9	8,0	8,2	7,6

Варіант 5

Виробництво яловичини і телятини мороженої, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	3,5	4,2	4,7	4,8	3,9	3,0	2,2	1,8	2,6	2,7	2,4	1,4
	0,8	1,1	1,5	2,5	2,4	2,9	3,0	2,8	2,3	2,2	2,4	1,7
	0,9	1,8	2,7	3,0	3,2	3,3	3,4	3,2	2,9	2,5

Варіант 6

Виробництво свинини свіжої, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	12,7	12,9	11,6	12,4	10,7	9,9	9,2	8,4	8,9	9,8	8,0	11,0
	8,0	8,0	8,2	8,2	6,5	8,6	8,4	8,6	8,3	8,1	7,9	11,3
	7,7	8,7	11,4	11,6	11,0	11,2	11,0	10,5	10,2	9,7

Варіант 7

Виробництво свинини мороженої, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	1,3	1,3	1,0	1,1	1,0	1,0	0,9	1,1	1,7	1,5	1,1	1,0
	0,4	0,3	0,4	0,3	0,4	0,5	0,6	0,5	0,4	0,4	0,3	0,6
	0,5	0,5	0,4	0,4	0,5	0,5	0,6	0,6	0,5	0,4

Варіант 8

Виробництво м'яса і субпродуктів харчових свійської птиці, свіжі чи охолоджені, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	44,0	41,9	44,5	47,9	45,6	43,4	45,0	43,9	46,9	48,4	49,2	54,6
	48,9	47,0	49,9	49,1	50,1	55,7	56,2	53,8	56,2	53,4	55,5	58,1
	54,2	52,4	58,9	55,0	54,1	53,0	54,3	53,9	52,0	54,0

Варіант 9

Виробництво м'яса і субпродуктів харчових свійської птиці, морожені,
тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	7,1	6,0	5,5	6,0	5,8	5,8	6,5	5,5	6,6	7,2	7,0	6,3
	6,8	5,7	8,1	5,5	6,4	6,8	7,0	6,6	7,5	6,6	7,1	6,6
	7,6	7,0	6,8	6,5	6,2	6,7	7,2	7,5	8,0	7,6

Варіант 10

Виробництво ковбасних виробів, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	24,2	25,5	25,3	27,0	26,0	28,1	29,5	28,8	29,6	28,0	24,0	26,2
	20,7	20,1	21,0	22,1	21,0	22,6	23,4	22,6	22,3	21,8	19,3	23,7
	18,2	18,4	22,6	23,4	23,0	22,8	23,2	24,0	22,9	23,1

Варіант 11

Виробництво молока обробленого рідкого, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	62,2	63,6	71,4	66,1	74,8	69,5	68,6	68,4	65,3	64,4	57,5	59,5
	56,6	54,1	65,9	60,7	70,4	67,6	68,2	70,1	63,7	63,8	57,6	59,3
	58,5	54,5	64,8	62,0	63,1	61,5	62,7	65,1	64,0	63,3

Варіант 12

Виробництво вершків жирністю більше 8%, тис. т., по місяцях 2008, 2009 і 2010 років

Рік	Січень	Лютий	Березень	Квітень	Травень	Червень	Липень	Серпень	Вересень	Жовтень	Листопад	Грудень
	0,7	1,0	1,5	1,5	2,3	2,1	2,0	1,7	1,6	1,7	1,2	1,0
	0,9	0,8	1,2	1,2	1,5	1,5	1,7	1,5	1,5	1,3	1,4	1,3
	1,2	1,2	1,4

Методичні вказівки до виконання лабораторної роботи № 2

Тема: Використання формул і функцій MS Excel для аналізу даних

Ціль: вивчити формули і їх синтаксис, засоби адресації в формулах, освоїти основні категорії функцій, принцип зведення даних з використанням функцій в MS Excel

Задачі:

1) створити електронну таблицю згідно умовам, використати прогресію, автозаповнення та автопідстановку для введення даних;

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

3) ввести математичні (підсумкові) і статистичні функції;

4) створити зведені таблиці і діаграми;

5) зробити графічний аналіз.

Хід роботи

1. Створіть нову електронну таблицю.

1.1. Запустіть табличний процесор MS Excel. У вікні, що з'явилося, введіть дані заголовку та „шапки” таблиці згідно рис. 2.1.

Рисунок 2.1 – Назви таблиці та стовпців згідно завданню

Виконайте необхідне форматування введеного тексту:

- виділіть діапазон чарунок A3:H3, викличте пункт основного меню „ Формат ”, підпункт „ Ячейки ”;

- в діалоговому вікні, що відкриється, активізуйте вкладку „ Выравнивание ”;

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

- у розділі елементів управління „ Отображение ” активізуйте прапорець „ переносить по словам ”;

- натисніть кнопку „ ОК ”.

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

1.2. Введіть за допомогою аріфметичної прогресії дані стовпця „№ п/п” (послідовність чисел від 1 до 20) наступним чином:

- введіть в чарунку з адресою A4 число 1;

- наведіть курсор миші на правий нижній кут чарунки с адресою A4. Коли вигляд курсору зміниться, натисніть праву кнопку миші та протягніть його вниз по стовпцю до чарунки з адресою A23. У контекстному меню, що з’явиться, оберіть підпункт „ Заполнить ”.

1.3. Введіть 5 найменувань товарів (умовних) в чарунки B4:B8. Повторіть їх назви в хаотичному порядку ще 15 разів в чарунках B9:B23, користуючись при цьому можливостями автозаповнення та автопідстановки.

1.4. Введіть будь-яку дату згідно з правилами синтаксису дат у чарунку з адресою C4. Потім наведіть курсор миші на правий нижній кут чарунки с адресою C4. Коли вигляд курсору зміниться, натисніть праву кнопку миші та протягніть його вниз по стовпцю до чарунки з адресою C8. У контекстному меню, що з’явиться, оберіть підпункт „Копировать” (див. рис. 2.2).

Аналогічні дії виконайте для чотирьох інших дат в чарунках з адресами C9:C13, C14:C18, C19:C23.

1.5. Введіть у чарунку з адресою D4 вид операції з товаром – „Надходження”. В іншу таку чарунку, наприклад, D5, введіть вид операції з товаром – „Відвантаження”.

Рисунок 2.2 – Заповнення дат шляхом копіювання

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

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

Швидке введення тексту, який вже було введено на даному листі електронної таблиці, можливо завдяки інструменту MS Excel „автопідстановка”. Він заключається у тому, що після введення 1-4 букв текстового фрагменту табличний процесор сам підставляє текст, що повторюється, і достатньо лише дати свою згоду на підстановку, натиснувши клавішу „Enter” (див. рис. 2.3).

1.6. Введіть у чарунки E4:E23 будь-які цілі числа (в діапазоні від 10 до 1000), що будуть позначати кількість товару на складі, що надійшов або був відвантажений в поточну дату (що позначена в рядку товару). Для введення чисел зручно користуватися альтернативною цифровою клавіатурою.

Рисунок 2.3 – Заповнення виду операції шляхом автопідстановки

Формат визначати не треба, для цілих чисел MS Excel визначить його автоматично.

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

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

Виберіть для тексту таблиці параметри шрифту, фону, границі.

В результаті введення усіх умовних даних отримаємо таблицю з вхідними даними, розташовану на листі 1 (див. рис. 2.4).

Рисунок 2.4 – Заповнена даними вхідна частина таблиці

2. Введіть необхідні формули та логічні функції

В стовпцях таблиці „Сума” та „Результат операції” повинні бути введені формули для розрахунку значень. При цьому формула стовпця „Результат операції” буде засновуватися на функції „ЕСЛИ” з категорії логічних.

2.1. Проаналізуємо, з чого повинна складатися формула для розрахунку суми. Сума в даному випадку – це кількість одиниць товару, помножена на ціну товару. Формула для обчислення суми відповідно буде складатися з відносної адреси чарунки, що містить кількість, арифметичної операції множення, та відносної адреси чарунки, що містить ціну. На початку формули повинен стояти знак „=”. Наприклад, для рядку 4:

=E4*F4

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

- розмістіть курсор у чарунці з адресою G4;

- введіть за допомогою клавіатури знак „ = ”;

- виберіть курсором миші чарунку з адресою E4;

- введіть за допомогою клавіатури знак „ * ”;

- виберіть курсором миші чарунку з адресою F4;

- натисніть клавішу Enter.

Зміст чарунки G4 формула =E4*F4, він відображується у рядку формул. На листі електронної таблиці відображується результат обчислення формули 427.5

Для того, щоб сума мала також грошовий формат, використайте інструмент „Формат по образцу” з панелі інструментів „Форматирование”:

- виберіть курсором миші чарунку з адресою F4;

- натисніть на кнопку панелі інструментів „ Форматирование ”;

- виберіть курсором миші чарунку з адресою G4.

Тому що формула повинна бути відтвореною для усіх рядків таблиці (усіх товарів), її потрібно автозаповнити. При цьому відносні адреси чарунок будуть відповідно змінюватися: номер рядку буде нарощуватися на 1 (інкремент) відносно незмінного заголовку стовпця, наприклад, F5, F6, F7 … F23.

Виконайте автозаповнення формули:

- наведіть курсор миші на правий нижній кут чарунки с адресою G4;

- коли вигляд курсору зміниться, натисніть ліву кнопку миші та протягніть його вниз по стовпцю до чарунки з адресою G23.

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

2.2. Проаналізуємо, з чого повинна складатися формула для розрахунку результату операції. Результат операції – це або сума зі знаком „+” (у випадку надходження товару), або сума зі знаком „-” (у випадку відвантаження товару). Для того, щоб створити універсальну формулу, яку можна авто заповнювати для усіх рядків з товарами, потрібна функція, що буде аналізувати вміст чарунки з видом операції (надходження чи відвантаження), та в залежності від результату аналізу видавати суму з відповідним знаком як результат своєї роботи. Це можна зробити за допомогою функції „ЕСЛИ” з категорії логічних функцій.

Функція „ЕСЛИ” потребує заповнення трьох обов’язкових аргументів (вхідних виражень або значень). Перший аргумент – це умова, частіше всього це логічне вираження. У будь-якому випадку (вираження чи значення цей аргумент) результат аналізу першого аргументу – це логічні значення „ИСТИНА” або „ЛОЖЬ”.

Логічне вираження даного прикладу – це порівняння вмісту чарунки з адресою D4 та текстового значення (текстової константи) „Надходження”. Якщо вміст чарунки співпадає з текстовою константою, результат аналізу першого аргументу буде „ИСТИНА”, якщо відрізняється хоча б на один символ, результат аналізу першого аргументу буде „ЛОЖЬ”.

Перший аргумент:

D4= „Надходження”

Другий аргумент (результат роботи функції, якщо результат аналізу першого аргументу буде „ИСТИНА”:

+G4

Другий аргумент (результат роботи функції, якщо результат аналізу першого аргументу буде „ЛОЖЬ”:

-G4

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

= Если (D4= „Надходження”; +G4; -G4)

Адреси чарунок в аргументах для даного прикладу потрібні відносні. При автозаповнюванні вони будуть інкрементуватися (нарощуватися на 1). У кожному рядку формула з функцією „ЕСЛИ” буде видавати різний результат, в залежності від значень аргументів. Тобто вона буде універсальним інструментом обчислення результату операції для усіх рядків з товарами.

Щоб правильно ввести таку формулу, потрібно виконати наступні дії:

- розмістіть курсор у чарунці з адресою H4;

-

викличте майстер функцій за допомогою інструменту „ Вставка функции ”з панелі інструментів„ Стандартная ”. Існує ще декілька способів виклику майстра функцій. Наприклад, натиснути на кнопку зі списком „ Автосумма ” також з панелі інструментів„ Стандартная ”. У списку, що відкриється, обрати варіант „ Другие функции ” та на екрані з’явиться вікно першого кроку майстра функцій для вибору категорії та назви функції (див. рис. 2.5). Ще один варіант – натиснути на кнопку „ Вставка функции ”у рядку формул (див. рис. 2.6). І останній варіант – вибрати пункт меню „ Вставка ”, підпункт „ Функция … ”.

Рисунок 2.5 – Інструмент „Автосумма” для виклику майстра функцій

Коли майстер функцій таким чи іншим способом викликано, на першому його кроці у полі зі списком „Категория” виберіть категорію „Логические”, потім у списку виберіть функцію „ЕСЛИ”. Натисніть на кнопку „ОК”.

На другому кроці майстра функцій необхідно заповнити всі аргументи функції „ЕСЛИ”.

Рисунок 2.6 – Виклик майстра функцій із рядку формул за допомогою кнопки „ Вставка функции ”

Для заповнення аргументів функції „ЕСЛИ” (див. рис. 2.7):

- натисніть на кнопку згортки, яка знаходиться справа в полі „ Лог_выражение ”;

- виберіть курсором миші чарунку з адресою D4;

- натисніть на кнопку розгортки;

- введіть за допомогою клавіатури знак „ = ” та у подвійних лапках текстову константу „ Надходження ”;

- в полі „ Значение_если_истина ” введіть за допомогою клавіатури знак „ + ” та натисніть на кнопку згортки, яка знаходиться справа;

- виберіть курсором миші чарунку з адресою G4;

- натисніть на кнопку розгортки;

- в полі „ Значение_если_ложь ” введіть за допомогою клавіатури знак „ - ” та натисніть на кнопку згортки, яка знаходиться справа;

- виберіть курсором миші чарунку з адресою G4;

- натисніть на кнопку розгортки;

-
натисніть на кнопку „ ОК ”.

Рисунок 2.7 – Введення аргументів функції „ЕСЛИ”

Для того, щоб результат операції мав грошовий формат, знову використайте інструмент „Формат по образцу”:

- виберіть курсором миші чарунку з адресою G4;

- натисніть на кнопку панелі інструментів „ Форматирование ”;

- виберіть курсором миші чарунку з адресою H4.

Виконайте автозаповнення формули:

- наведіть курсор миші на правий нижній кут чарунки с адресою H4;

- коли вигляд курсору зміниться, натисніть ліву кнопку миші та протягніть його вниз по стовпцю до чарунки з адресою H23.

При необхідності розтягніть стовпець H до оптимальної ширини.

3. Введіть математичні (підсумкові) і статистичні функції.

3.1. Введіть в чарунки з адресами A24:A28 підписи для підсумкового і статистичних значень (див. рис. 2.8).

Рисунок 2.8 – Підготування підписів для підсумку і статистики

3.2. Введіть формулу для підрахування сумарного результату операцій:

- розмістіть курсор у чарунці з адресою H24;

-

натисніть на кнопку „ Автосумма ” (без виклику списку) на панелі інструментів „ Стандартная ” (див. рис. 2.9);

Рисунок 2.9 – Введення функції суми для обчислення підсумкового результату операції

- натисніть клавішу Enter.

Функція „ СУММ ”, якою ви скористалися викликом з панелі інструментів, належіть до категорії функцій MS Excel „ Математические ” і могла також бути вставленою шляхом виклику майстра функцій. Але розглянутий варіант дозволяє швидко скористатися найчастіше використовуваними функціями без виклику майстра та проходження його кроків. Діапазон аргументів визначається при цьому автоматично, що також прискорює процес, і запропонований програмою діапазон потрібно лише підтвердити натисненням клавіші Enter.

3.3. Введіть формулу для підрахування середньої кількості товарів:

- розмістіть курсор у чарунці з адресою E25;

- натисніть на кнопку зі списком „ Автосумма ” на панелі інструментів„ Стандартная ”. У списку, що відкриється (див. рис. 2.5), оберіть варіант „ Среднее ”;

- натисніть клавішу Enter.

3.4. Введіть формулу для підрахування максимальної кількості товарів:

- розмістіть курсор у чарунці з адресою E26;

- натисніть на кнопку зі списком „ Автосумма ” на панелі інструментів„ Стандартная ”. У списку, що відкриється (див. рис. 2.5), оберіть варіант „ Максимум ”;

- виділіть необхідний діапазон E4:E23 (діапазон аргументів не суміжний з чарункою функції, тому не може бути визначений автоматично);

- натисніть клавішу Enter.

Функція „ МАКС ”, якою ви скористалися викликом з панелі інструментів, належіть до категорії функцій MS Excel „ Статистические ”.

3.5. Введіть формулу для підрахування мінімальної кількості товарів:

- розмістіть курсор у чарунці з адресою E27;

- натисніть на кнопку зі списком „ Автосумма ” на панелі інструментів„ Стандартная ”. У списку, що відкриється (див. рис. 2.5), оберіть варіант „ Минимум ”;

- виділіть необхідний діапазон E4:E23;

- натисніть клавішу Enter.

Функція „ МИН ”, якою ви скористалися викликом з панелі інструментів, також належіть до категорії функцій MS Excel „ Статистические ”.

3.6. Введіть формулу для підрахування кількості випадків здійснення операцій з товарами:

- розмістіть курсор у чарунці з адресою G28;

- натисніть на кнопку зі списком „ Автосумма ” на панелі інструментів„ Стандартная ”. У списку, що відкриється (див. рис. 2.5), оберіть варіант „ Число ”;

- виділіть необхідний діапазон G4:G23;

- натисніть клавішу Enter.

Функція „ СЧЁТ ”, якою ви скористалися викликом з панелі інструментів, також належіть до категорії функцій MS Excel „ Статистические ”.

Дата добавления: 2015-05-24