Лабораторна робота №1. Excel. Створення електронних таблиць
Завдання. Створити електронну таблицю для занесення початкових даних про комерційну діяльність підприємства, відображення їх на діаграмі та розрахунку показників.
Початкові дані розташувати в електронній таблиці так, як показано в табл.1
Таблиця 1
| Січень | ||||
| Лютий | ||||
| Березень | ||||
| Квітень | ||||
| Травень | ||||
| Червень | ||||
| Липень | ||||
| Серпень | ||||
| Вересень | ||||
| Жовтень | ||||
| Листопад | ||||
| Грудень |
І. Побудувати діаграму товарообігу фірми в 2003-2006 роках для порівняння товарообігу відповідних місяців.
ІІ. В тому ж листі електронної таблиці розташувати формули для визначення сумарного, середньомісячного, мінімального та максимального місячних об’ємів товарообігу, кількість місяців, в яких товарообіг був менший від 2500 тис. грн. – табл. 2.
Таблиця 2
| Показник | Значення |
| Роки | 2003-2006 |
| Максимальний місячний товарообіг | =МАКС(діапазон даних з продажів за усі роки) |
| Мінімальний місячний товарообіг | =МИН(діапазон даних з продажів за усі роки) |
| Загальний об'єм товарообігу | =СУММ(діапазон даних з продажів за усі роки) |
| Середньомісячний об'єм товарообігу | =СРЗНАЧ(діапазон даних з продажів за усі роки) |
| Кількість несприятливих місяців | =СЧЁТЕСЛИ(діапазон даних з продажів за усі роки;"<2500") |
Результати розрахунку показників за весь період оформити так, як це показано в табл. 2.
Приклад: виділіть комірку, в яку ви хочете ввести функцію, та натисніть 
на панелі інструментів. З’явиться вікно майстра функцій.
З запропонованого списку виберіть функцію МАКС та натисніть ОК. З’явиться вікно, в якому можна визначити аргументи даної функції
Задайте необхідний для обчислення функції діапазон (виділіть його на листі Excel за допомогою миші) та натисніть ОК.
В результаті в комірці з’явиться формула з розрахунку максимального місячного товарообігу.
Тим самим способом введіть в інші комірки функції для розрахунку перерахованих параметрів.
ІІІ. Нижче розташувати схему розрахунку показників заданого року так, як це показано в табл. 3 (рік у вигляді цілого числа). Комірці, що буде містити рік, для якого необхідно провести розрахунок, надати ім’я рік. Коміркам-заголовкам зі значенням років надати імена рік1, рік2, рік3, рік4. Відповідно надати імена блокам комірок з даними за роки (відповідно дані_рік1; дані_рік2; дані_рік3; дані_рік4) – рис. а, б.
Рис. а
Рис. б
Алгоритм розв’язку задачі: якщо введено рік, який є в заголовку табл.1, то необхідно розрахувати показники для цього року. Якщо рік не введено або введено рік, дані за який відсутні (в заголовку табл.1 рік відсутній), то виводити текст „Дані відсутні”.
Таблиця 3
| Показник | Значення |
| Рік | |
| Максимальний місячний об'єм товарообігу | |
| Мінімальний місячний об'єм товарообігу | |
| Загальний об'єм товарообігу | |
| Середньомісячний об'єм товарообігу | |
| Кількість несприятливих місяців |
Для розрахунку використати формули: МАКС, МИН, СУММ, СРЗНАЧ, СЧЁТЕСЛИ та ЕСЛИ.
Приклад уведеної функції для розрахунку максимального місячного ТО:
=ЕСЛИ(рік=рік1;МАКС(дані_рік1);ЕСЛИ(рік=рік2;МАКС(дані_рік2);ЕСЛИ(рік=рік3;МАКС(дані_рік3);ЕСЛИ(рік=рік4;МАКС(дані_рік4);"Дані відсутні"))))
Файл зберегти в МОИ ДОКУМЕНТЫ (ім’я файлу – прізвище виконавця).
Приклад екрану з виконаною лабораторною роботою:
Контрольні запитання та завдання:
1. Призначення та синтаксис функцій, використовуваних у лабораторній роботі.
2. Згідно варіанту виконати в тому ж файлі (на другому листі) завдання: в залежності від значення комірки А1 (1 чи 2) виконати необхідні розрахунки у комірці А2. Якщо в комірці А1 немає значень або вони не дорівнюють 1 чи 2, вивести текст „Неправильний код”
| Номер варіанту | ||||
| Розрахувати середнє значення ТО за січень по всіх роках | Вивести суму мінімальних значень ТО всіх років | Вивести кількість місяців 2006 р. (ТО>2900) | Розрахувати різницю між максимальним ТО за 2004 р. та мінімальним ТО за 2005 р. | А1=1 |
| Додати максимальне значення ТО за 2005 р. та мінімальне значення ТО за 2006 р. | Вивести кількість місяців у 2004 р., коли ТО був більший за 3000 | Підрахувати суму ТО за січень та вересень (за всіма роками) | Підсумувати середні значення за червень та липень. | А1=2 |
Лабораторна робота №2. Excel. Робота зі зведеними таблицями, лінії тренду, розширений фільтр.
Відкрити файл Завдання2.xls.На листі Дані з продажу розташована таблиця з інформацією про продаж товарів в різних регіонах.
1. Робота зі зведеними таблицями.
ü Побудувати на основі початкової таблиці зведену таблицю загальної суми продажів по регіонах за три роки. Для цього в меню вибрати пункт Данные, подпункт Сводная таблица.
На першому кроці вказати джерело даних (в списке или базе данных Microsoft Office Excel) та вид створюваного звіту (сводная таблица).
На другому кроці вказати діапазон, з якого вибираються дані для зведеної таблиці.
На третьому кроці вибрати місце розташування зведеної таблиці (новый лист) та натиснути кнопку Макет...
Сформувати макет зведеної таблиці: затиснувши ліву кнопку миші перенести поле Регіон в область Строка, поле Рік – в область Столбец, поле Сума – в область Данные (автоматично параметри поля Сума набувають значення Сумма по полю; в подальшому параметри полів можна змінювати). Натиснути клавішу ОК.
Після формування макету вибрати клавішу Готово та переглянути отриману таблицю. Лист зі зведеною таблицею назвати Загальний ТО. Модернізувати таблицю: перенести поле Місяць у верхню незаповнену частину таблиці (Перетащите сюда поля страниц). Тепер можна переглянути дані по роках за окремий місяць (для цього натискаємо чорну стрілочку біля поля Місяць та обираємо необхідний місяць, натискаємо ОК).
Для того, щоб отримати дані про кільксть операцій продажу, необхідно встановити курсор в поле Сума зведеної таблиці та визвати контекстне меню. Вибрати пункт Параметры поля. Змінити операцію Сумма на Количество. Натиснути ОК. Побудувати діаграму на основі цієї таблиці.
ü Побудувати на окремих листках:
o зведену таблицю загальної суми ТО по клієнтах за три роки із сортуванням по полю Клієнт – вибрати трьох найкращих клієнтів за крітерієм сумарного ТО за лютий (використати Параметри поля/ Дополнительно / Автоотображение лучшей десятки) – лист Трійка найкращіх клієнтів;
Структура таблиці
| Місяць | |
| Рік | |
| Клієнт | Сума |
o зведену таблицю ТО по місяцях для всіх регіонів. Відобразити дані для 2005 року та, змінивши параметри поля Сума, вивести долю кожного регіону в ТО за місяцями (Параметри поля / Дополнительно / Дополнительные вычисления) – листПроцент ТО по регіонах;
Структура таблиці
| Рік | |
| Місяць | |
| Регіон | Сума |
o зведену таблицю з інформацією про ТО за місяці (об’єднати по роках) продажу окремих видів товарів (об’єднати за регіонами) – лист Місячні ТО в регіонах; поле Місяць розбити по кварталах (чотири групи). Для цього упорядкувати місяці так, як вони йдуть у році, та використати контекстне меню (Группа и структура / Группировать ...): об’єднати три Січень, лютий, березень, визвати контекстне меню та згрупувати їх.
Структура таблиці
| Регіон | Товар | ||
| Рік | Місяць | Сума | |
2. Використання розширеного фільтру для виборки даних.
ü Розширений фільтр допомагає вибрати дані з діапазону з використанням складних умов. Спочатку необхідно скопіювати заголовки стовпців (після діапазону початкових даних). Це буде діапазон умов. Умови, які знаходяться на одній і тій же строці, пов’язані логічним оператором І. Умови, які знаходяться на різних строках – оператором АБО.
Для вибору всіх даних по клієнтах за три місяці (січень, лютий і березень) в діапазоні умов в стовпці Місяць ввести назви необхідних місяців. Потім помістити курсор в діапазон початкових даних. В меню вибрати пункт Данные / Фильтр / Расширенный фильтр. В полі Исходный диапазон вказується початковий діапазон, з якого необхідно відфільтрувати дані; в полі Диапазон условий вказується діапазон, який містить умови для фільтрування (разом із відповідним заголовком). Для отримання окремої відфільтрованої таблиці необхідно вказати діапазон в полі Поместить результат в диапазон (на тому ж листі, де і початкові дані). Після отримання таблиці перенести її на окремий лист І квартал.
Для спрощення роботи з розширеним фільтром можна надати імена для відповідних діапазонів: База_данных – длядіапазону початкових даних; Критерии– для діапазону умов;Извлечь– для області вставки результатів. Автоматично адреси цих діапазонів підставляються до відповідних полів у вікні розширеного фільтру.
ü Виберіть всі дані за 2005 рік для регіону Вінниця, де сума була більшою за 3000 грн. Для цього запишемо всі умови для фільрації в одну строку (поле Рік – 2005, поле Регіон – Вінниця, поле Сума - >3000. Результат перенести на окремий лист Вінниця2005.
ü За допомогою розширеного фільтру отримати наступні таблиці (відфільтровані таблиці помістити на окремі листи):
o дані за грудень місяць по фірмі Андреа та за січень місяць по фірмі Рондо – лист Андреа_Рондо;
o дані по продажу у 2006 році листівок ч/б (кількість повинна бути більшою за 2000) та листівок кольорових (кількість більша за 3000) – лист Листівки.
3. Лінії тренду.
ü За допомогою ліній тренду можна графічно відображати тенденції даних та прогнозувати їх подальші зміни. Лінії тренду будуються на основі діаграм та графіків. Існує декілька видів ліній тренду: линейная, логарифмическая, полиномиальная, степенная, экспоненциальная. Окремо існує лінія, яка згладжує нерівномірність даних - скользящее среднее. Лінія тренду найбільше відповідає даним на діаграмі, якщо значення R2 близьке або рівне 1. При апроксимації даних за допомогою ліній тренду значення цього показника розраховується автоматично та може бути виведений на діаграмі.
ü За допомогою розширеного фільтра вибрати дані по продажу кольорових листівок за весь період. Таблицю перенести на новий лист Листівки кольорові. Просумувати дані за допомогою зведеної таблиці по всіх роках та місяцях в залежності від регіону.
Структура таблиці
| Регіон | ||
| Товар | ||
| Рік | Місяць | Сума |
Відобразити дані тільки для Житомира. Сформувати правильну послідовність місяців. Змінити тип діаграми з гістограми на графік. Додати до графіку лінію тренда (тип – Полиномиальная, ступінь – 6, ім’я – Тенденції продажу, прогноз – 1 період вперед, відобразити на діаграмі R2).
ü Змінюючи типи ліній тренда довести, що поліноміальна є найближчою до первісних даних.
Лабораторная работа №3. Изучение надстройки «Поиск решения» на примере задачи лінійного програмування
Пример 1
Предприятие изготавливает и продает краску двух видов: для внутренних и внешних работ. Для производства краски используется два исходных продукта A и B. Расходы продуктов A и B на 1 т. соответствующих красок и запасы этих продуктов на складе приведены в таблице:
| Исходный | Расход продуктов (в тоннах на 1 т. краски) | Запас продукта на | |
| продукт | краска для внутренних работ | краска для внешних работ | складе ( тонн ) |
| A | |||
| B |
Продажная цена за 1 тонну краски для внутренних работ составляет 2 000 рублей, краска для наружных работ продается по 1 000 рублей за 1 тонну. Требуется определить какое количество краски каждого вида следует производить предприятию, чтобы получить максимальный доход.
Рассмотрим поэтапное решение этой задачи несколькими способами: графическим, алгебраическим и с использованием процедуры « Поиск решения » Excel.