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

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

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

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

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

- на другому кроці майстра функцій (див. рис. 2.11) в полі аргументу „ Диапазон ” виділіть необхідний діапазон H4:H23;

- в полі аргументу „ Критерий ” введіть за допомогою клавіатури вираження „ >0 ”;

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

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

Рисунок 2.10 –

Вибір функції „СЧЁТЕСЛИ” на першому кроці майстру функцій

Рисунок 2.11 - Введення аргументів функції „СЧЁТЕСЛИ” на другому кроці майстру функцій

3.8. Шляхом автозаповнення введіть формули для підрахування середнього, максимального та мінімального значень по стовпцям „ Ціна однієї одиниці ”, „ Сума ” та „ Результат операції ”:

- виділіть діапазон чарунок з адресами E25:E27;

- наведіть курсор миші на правий нижній кут виділеного діапазону;

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

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

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

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

- виберіть курсором миші діапазон чарунок з адресами F25:H27.

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

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

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

Рисунок 2.12 – Результат введення формул та форматування

4. Створіть зведені таблиці і діаграми.

4.1. Створіть зведену таблицю, що виводить сумарні дані по найменуванню і результату операції, і відповідну діаграму.

Для створення зведеної таблиці:

- виберіть пункт меню „ Данные ”, підпункт „ Сводная таблица ”;

- на першому кроці майстра зведених таблиць і діаграм перемикач (селектор) „ в списке или базе данных Microsoft Excel ” групи „ Создать таблицу на основе данных, находящихся ” залишити активним;

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

- натисніть на кнопку „ Далее > ”;

- на другому кроці майстра зведених таблиць і діаграм необхідно вказати діапазон, що містить вхідні дані, тобто побудовану вхідну таблицю з листа 1. У полі „ Диапазон ” натисніть на кнопку згортки, виберіть курсором миші діапазон B3:H23, тобто, усі дані вхідної таблиці, включно заголовки, але окрім номеру рядків з даними та окрім підсумковихі статистичних даних, потім натисніть на кнопку розгортки;

- натисніть на кнопку „ Далее > ”;

- на третьому кроці майстра зведених таблиць і діаграм необхідно вказати за допомогою селектору, де буде розміщена зведена таблиця – на тому ж самому листі, де вхідна, або іншому існуючому листі та в чарунці з якою адресою, або, що більш зручно, на новому листі електронної таблиці. Для цього зробіть активним в групі селекторів „ Поместить таблицу в ” селектор „ новый лист ”, адресу чарунки в цьому випадку вказувати не потрібно, вона по умовчанню буде А1;

- далі на третьому кроці майстра необхідно розробити макет зведеної таблиці, за допомогою вікна, яке необхідно викликати, натиснувши на відповідну кнопку „ Макет ”;

-

враховуючи завдання, необхідно перетягнуть мітки стовпців (кнопки полів) „ Найменування товару ” та „ Результат операції ” в макет зведеної таблиці. А саме, будемо розміщувати кнопку поля „ Найменування товару ”, яке містить текстові дані, в заголовках рядків зведеної таблиці. Кнопку поля „ Результат операції ”, яке містить чисельні дані грошового формату і по якому може бути підрахована сума (що і потрібно зробити згідно завданню), перетягнемо і розмістимо в області даних зведеної таблиці (див. рис. 2.13). Дані – це та частина макету, що повинна бути заповнена обов’язково, бо саме в ній над даними виконується підсумкова операція (за умовчанням це сума). Інші три частини макету – рядок, стовпець, сторінка – заповнюються при необхідності і по вибору користувача (хоча б одно з них необхідно заповнити для інформативності зведених даних).

Рисунок 2.13 – Макет зведеної таблиці (зведення по найменуванню товару та результату операції)

Натисніть на кнопку „ ОК ”, потім на кнопку „ Готово ”.

В результаті в книзі електронної таблиці з’явилися два нових листа зі зведеними діаграмою і таблицею (див. рис. 2.14).

Рисунок 2.14 – Зведена діаграма і таблиця (зведення по найменуванню товару та результату операції, функція зведення - сума)

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

Для створення другої зведеної таблиці:

- поверніться на лист 1 електронної таблиці (активізуйте лист 1 за допомогою ярлику „ Лист 1 ”;

- виберіть пункт меню „ Данные ”, підпункт „ Сводная таблица ”;

- на першому кроці майстра зведених таблиць і діаграм селектор „ в списке или базе данных Microsoft Excel ” групи „ Создать таблицу на основе данных, находящихся ” залишити активним;

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

- натисніть на кнопку „ Далее > ”;

- на другому кроці майстра зведених таблиць і діаграм у полі „ Диапазон ” знову вкажіть діапазон B3:H23;

- натисніть на кнопку „ Далее > ”;

-
після цього майстер видасть інформаційне вікно (див. рис. 2.15), в якому натисніть на кнопку „ Да ”; це запитання майстра завжди виникає при побудові декількох зведених таблиць на основі тих самих вхідних даних і пропонує користувачу оптимізувати розмір файлу книги за рахунок створення взаємозалежних звітів (зведених таблиць). Це запитання не виникло б, якби ми на першому кроці майстра замість селектору „ в списке или базе данных Microsoft Excel ” групи „ Создать таблицу на основе данных, находящихся ” обрали селектор „ в другой сводной таблице или сводной диаграмме ”;

Рисунок 2.15 – Інформаційне вікно майстра зведених таблиць і діаграм

- натисніть на кнопку „ Далее > ”;

- на третьому кроці майстра зведених таблиць і діаграм зробіть активним в групі селекторів „ Поместить таблицу в ” селектор „ новый лист ”;

- враховуючи завдання, необхідно перетягнуть мітки стовпців (кнопки полів) „ Найменування товару ”, „ Дата операції ” та „ Результат операції ” в макет зведеної таблиці. А саме, будемо розміщувати кнопку поля „ Найменування товару ”, яке містить текстові дані, в заголовках рядків зведеної таблиці. Кнопку поля „ Результат операції ”, яке містить чисельні дані грошового формату і по якому можуть бути підраховані середні значення (потрібні згідно завданню), перетягнемо і розмістимо в області даних зведеної таблиці (див. рис. 2.16). Кнопку поля „Дата операції” можна розмістити як в області стовпця, так і в області сторінки. Якщо розмістити в області сторінки, буде створений так званий фільтр по полю дата, або поле зі списком, що може служити фільтром. За рахунок цього така таблиця буде більш компактною та зручною (дозволяє відображати в таблиці і відповідно діаграмі тільки значення

за потрібну дату);

Рисунок 2.16 - Макет зведеної таблиці (зведення по найменуванню товару, даті та результату операції), вибір операції для обчислення

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

- оберіть операцію „ Среднее ”;

- натисніть на кнопку „ ОК ”, потім ще раз на кнопку „ ОК ”, потім на кнопку „ Готово ”. Отримаємо на новому листі зведену таблицю (див. рис. 2.17).

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

Рисунок 2.17 - Зведена таблиця (зведення по найменуванню товару, даті та результату операції, функція зведення – середнє значення), побудова діаграми через контекстне меню або тематичну панель інструментів

Дивись отриману діаграму на рис. 2.18.

Рисунок 2.18 – Зведена діаграма (зведення по найменуванню товару, даті та результату операції, функція зведення – середнє значення)

Варіант побудови без фільтру по даті операції (в макеті дата операції розміщена в області заголовків стовпців, а не в області сторінки – фільтру) дивись на рис. 2.19.

5. Зробіть графічний аналіз даних вхідної таблиці, розташованої на листі 1. Побудуйте дві діаграми - кругову, і на вибір: гістограму, змішану нестандартну або кільцеву.

5.1. Побудуйте кругову діаграму:

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

Рисунок 2.19 - Зведена діаграма (зведення по найменуванню товару, даті та результату операції) – варіант макету з заголовками стовпців

- викличте майстер діаграм;

- на першому кроці майстра діаграм оберіть тип діаграми – „ Круговая ”, вид – „ Объемный вариант разрезанной круговой диаграммы ”;

- натисніть на кнопку „ Далее > ”;

- уточнювати дані не треба, пропустіть другий крок майстра діаграм і знову натисніть на кнопку „ Далее > ”;

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

- натисніть на кнопку „ Далее > ”;

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

-
натисніть на кнопку „ Готово ” і отримайте на новому листі кругову діаграму. Після зміни гарнітури шрифту на Times New Roman, збільшення розміру шрифту до 12 пт, та невеликого розтягування легенди у ширину дивись отриману діаграму на рис. 2.20. Вона відноситься до аналітичних типів діаграм.

Рисунок 2.20 - Кругова діаграма

5.2. Побудуйте гістограму (або інший порівняльний тип діаграми):

- виділіть діапазон для гістограми B3:E23;G3:G23 (необхідно під час виділення не суміжного стовпця тримати натиснутою клавішу Ctrl);

- викличте майстер діаграм;

- на першому кроці майстра діаграм оберіть тип діаграми – „ Гистограмма ”, нажмите кнопку „ Просмотр результата ”; потім аналогічні дії виконайте, обравши тип діаграми – „ Кольцевая ”. Тому що обрані для порівняння дані мають різний порядок (10² та 10³) та різний формат – числовий та грошовий, спробуємо обрати тип діаграми серед нестандартних (вкладка „ Нестандартные ”). Найбільш підходить тип „График | гистограмма 2 оси”, що являє собою класичну змішану гістограму, де перша ось для гістограм, друга – для графіків;

- натисніть на кнопку „ Далее > ”; уточнювати дані не треба, пропустіть другий крок майстра діаграм і знову натисніть на кнопку „ Далее > ”;

- на третьому кроці майстра діаграм на вкладці „ Легенда ” активізуйте перемикач розміщення легенди „ внизу ”; натисніть на кнопку „ Далее > ”;

- на четвертому кроці майстра діаграм активізуйте перемикач розміщення діаграми на листі „ отдельном ”, введіть у поле для імені листа діаграми текст „ Смешанная гистограмма ”;

- натисніть на кнопку „ Готово ” і отримайте на новому листі змішану діаграму. Після зміни гарнітури шрифту на Times New Roman, зменшення розміру шрифту по осі абсцис до 8 пт дивись отриману діаграму на рис. 2.21. Вона відноситься до порівняльних типів діаграм.

Рисунок 2.21 – Змішана гістограма

Індивідуальні завдання з лабораторної роботи № 2

Згідно варіанту виконайте наступні завдання.

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

2. В таблиці введіть необхідні формули (в стовпцях, які помічені *), використайте функцію ЕСЛИ та при необхідності інші логічні функції для реалізації умов варіанту, використайте можливість автозаповнення для формул.

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

4. Побудуйте 2 зведені таблиці і діаграми згідно своєму варіанту (таблиця 2.1).

5. Побудуйте 2 діаграми по початковій таблиці (кругову, і на вибір: гістограму, змішану нестандартну, кільцеву).

6. Виконайте друк таблиць та діаграм.

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

6.2. Ввімкніть в таблиці з вхідними даними та розрахунками режим відображення формул. Для цього викликайте пункт меню „ Сервис ”, підпункт „ Параметры … ”. На вкладці „ Вид ” у групі прапорців „ Параметры окна ” зробіть активним прапорець „ Формулы ”. Натисніть кнопку „ ОК ”. Якщо деякі стовпці таблиці надмірно розтягнуті або, навпаки, не показують вміст повністю, зробіть автокорекцію їхньої ширини подвійним клацанням лівої кнопки миші на границі між заголовками таких стовпців, або зробіть корекцію ширини власноруч, перетягуванням границі.

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

6.4. Роздрукуйте зведені таблиці.

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

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

Варіант № 1

Облік на продовольчій базі

№ п/п	Наймену-вання товару	Дата відвантаження	Фірма-закупник	Кількість одиниць товару	Ціна однієї одиниці	Сума торгової знижки	Сума
						*	*

В графі "Сума торгової знижки" на закупівлю більш ніж 100 одиниць товару знижка в 10%.

Варіант № 2

Замовлення на виконання реклами

№ п/п	Підприємство-замовник	Дата заявки	Вид реклами	Одиниця вимірю-вання	Обсяг реклами	Ціна однієї одиниці	Вартість послуги
				*			*

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

Варіант № 3

Фірма-дистриб'ютор DAEWOO в Україні

№ п/п	Найменування товару	Дата замовлення	Кількість одиниць товару	Ціна однієї одиниці	Сума	Сума торгової знижки
					*	*

В графі "Сума торгової знижки" на покупку більш ніж на 2000 грн. повинна висвічуватися 5% знижка від загальної суми.

Варіант № 4

Операції, укладені агентством нерухомості

№ п/п	Район	Дата операції	К-ть кімнат	Вид операції	Вартість операції	Прибуток агентства
				*	*

Операції підрозділяються на два різновиди: купівля/продаж або оренда. Під вартістю операції при оренді квартири мається на увазі місячна платня. В графі "Прибуток агентства" прибуток складає 3% від вартості квартири при купівлі/продажу або місячну платню при оренді.

Варіант № 5

Відвантаження товару на булочно-кондитерському комбінаті

№ п/п	Найменування виробу	Дата відвантаження	Кількість	Ціна	Сума	Сума торгової знижки
					*	*

В графі "Сума торгової знижки" на замовлення більше 1000 грн. знижка 7%.

Варіант № 6

Донецький маргариновий завод

№ п/п	Наймену-вання товару	Дата відвантаження	Фірма-закуп-ник	Кількість одиниць	Розмірність	Ціна однієї одиниці	Сума торго-вої знижки	Сума
							*	*

В графі "Сума торгової знижки" на закупівлю більш ніж 100 одиниць товару передбачена знижка в 10%.

Варіант № 7

Поліграфічні послуги

№ п/п	Підприємство-замовник	Дата заявки	Вид друку (кольорова, чорно-біла)	Кількість листів	Вартість одного листа	Вартість послуги
					*	*

В графі «Вартість одного листа» для кольорового друку – 2 грн., для чорно-білої – 0,4 грн.

Варіант № 8

Фірма-дистриб'ютор PANASONIC в Україні

№ п/п	Найменування товару	Дата операції	Вид операції (надходження або відвантаження)	Кількість одиниць товару	Ціна однієї одиниці	Сума	Результат операції
						*	*

В графі "Результат операції" грошові суми записуються з відповідним знаком залежно від виду операції (плюс або мінус).

Варіант № 9

Прийом оплати за оренду приміщень під офіси в Бізнес-центрі

№ п/п	Поверх	Місяць, за який проведена оплата	Площа	Вартість 1 кв. м	Період	Прибуток
				*		*

В графі "Вартість 1 кв. м" вартість складе $33 для першого, 4-го і 5-го поверхів, і $40 для 2-го і 3-го.

Варіант № 10

Видавництво "Донетчина»

№ п/п	Найменування товару	Дата відвантаження	Фірма-закуп-ник	Кількість одиниць товару	Ціна однієї одиниці	Сума торго-вої знижки	Сума
						*	*

В графі "Сума торгової знижки" на закупівлю більш ніж 100 одиниць товару передбачена знижка в 15%.

Варіант № 11

Оплата будівельних робіт будівельної компанії

№ п/п	Замовник	Дата замовлення	Площа	Терміни	Ціна 1 кв. м	Вартість
					*	*

В графі "Ціна 1 кв. м." якщо термін менше півроку, ціна 2000 грн., від півроку до року — 1600 грн., більше року — 1000 грн.

Варіант № 12

Завод холодильників "Норд"

№ п/п	Найменуван-ня товару	Дата відвантаження	Фірма-закупник	Кількість	Ціна	Сума знижки	Сума
						*	*

В графі "Сума знижки": на закупівлю більш ніж 50 одиниць товару передбачена знижка в 13%.

Таблиця 2.1 - Варіанти завдань для побудови зведених таблиць

№ вар	Побудувати зведені таблиці
1.	по найменуванню і сумі по даті і кількості
2.	по підприємствах і вартості послуг по підприємствах, даті заявки, одиниці вимірювання і обсягу реклами
3.	по найменуванню і сумі по найменуванню, даті і сумі торгової знижки
4.	по району і вартості операції по району, даті операції і прибутку агентства
по найменуванню і сумі по найменуванню даті і сумі
6.	по найменуванню і сумі по даті і кількості
7.	по підприємствах і вартості послуг по підприємствах, даті заявки, виду друку
по найменуванню і результату операції по найменуванню, даті і результату операції
9.	по поверху і прибутку по поверху, даті операції і прибутку
10.	по найменуванню і сумі по даті і кількості
11.	по замовнику і вартості по замовнику, даті операції і вартості
12.	по найменуванню і сумі по даті і кількості

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

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

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

Завдання:

1) розглянути принцип побудови та постанову задачі лінійного програмування;

2) знайти оптимальне рішення моделі лінійного програмування в MS Excel при заданих значеннях вхідних параметрів.

Хід роботи

1. Принцип побудови та постанова задачі лінійного програмування.

1.1. Побудова математичних моделей лінійного програмування.

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

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

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