Застосування розширеного фільтру.

Застосування розширеного фільтру включає 9 кроків.

1. Копіювання заголовку таблиці і перенесення копії в діапазон А30:Е30.

2. Введення в комірку D31 умови на ціну (>=200).

3. Введення в комірку Е31 умови на вартість (>1000).

4. Виконання команди “Данные Фильтр Расширенный фильтр”.

5. Встановлення вихідного діапазону ($A$1:$E$26).

6. Встановлення діапазону умов ($A$30:$E$32).

7. Встановлення перемикача “Обработка” в положення “Скопировать результат в другое место”.

8. Введення діапазону результатів: вводиться тільки верхня ліва комірка діапазону результатів – Лист1!$A$34 (використовувана при виконанні завдання версія Excel 97 не дозволяє одразу ж скопіювати результати фільтрації на інший аркуш).

9. Перенесення комірок з результатами на новий аркуш (команди “Правка Вырезать”; “Правка Вставить”).

Визначення загальної вартості кожного виду приладів.

Виконується команда “Данные Итоги”. В полі “При каждом изменении в” встановлюється поле “Назва приладу”. В полі “Операция” обирається операція “Сумма”. В полі “Добавить итоги по” обирається поле “Вартість”. Натискується кнопка “Ок”.

Побудова кругової діаграми.

За допомогою символів структури список на першому аркуші книги Microsoft Excel згортається до другого рівня структури (тільки проміжні підсумки та загальний підсумок). Виділяються назви приладів, натискується клавіша Ctrl і виділяються відповідні комірки зі стовпця “Вартість”. Виконується команда “Вставка Диаграмма”. В списку “Тип” обирається тип “Круговая”. В списку “Вид” обирається вид “Объёмный вид круговой диаграммы”. Натискується кнопка “Далее”. На другому кроці майстра діаграм (“Диапазон данных) зміни не вносяться. Натискується кнопка “Далее”. На третьому кроці майстра діаграм вводиться заголовок (Розподіл доходів від продажу різних приладів) та тип підписів даних (“доля”). Натискується кнопка “Далее”. Перемикач “Поместить диаграмму на листе” встановлюється в положення “имеющемся”. З поля зі списком обирається назва листа (“Лист3”). Натискається кнопка “Готово”. У відповідь на запит про необхідність внесення діаграми до існуючого аркушу натискається кнопка “Да”.

Завдання 9.Завдання 9.

Нехай ви маєте взяти кредит на суму 10000 грн. під 5% річних терміном на 2 роки. На четвертому аркуші книги Excel оцініть розмір квартальних виплат за цим кредитом з огляду на те, що розрахунки повинні відбуватись на початку кожного кварталу.

Для розв’язання задачі слід скористатися функцією ППЛАТ.

Синтаксис цієї функції є таким:

Ставка відсоткова ставка по сумі.

Кпер загальна кількість виплат по кредиту.

Нз основна сума кредиту.

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

Тип вказівка на те, коли відбувається виплата.

В даному випадку спочатку слід з’ясувати відсоткову ставку за період (квартал). Для цього відсоткову ставку за рік (5%) слід розділити на кількість кварталів у році (4). Формулу (=5%/4) слід ввести в комірку В1. В якості кількості періодів слід взяти кількість кварталів за 2 роки: в одному році 4 квартали, отже у двох роках 8 кварталів. Число 8 слід ввести в комірку В2. Далі у відповідності з умовами задачі вводяться сума кредиту (10000; комірка В3), баланс після останньої виплати (0; комірка В4), вказівка на те, що розрахунки проводяться на початку кварталу (1; комірка В5). Після цього безпосередньо вводиться формула:

=ППЛАТ(B1;B2;B3;B4;B5)

Формула повертає значення -1305,02 р. Для повної відповідності умовам задачі можна отримане значення відформатувати українським грошовим форматом: виконати команду “Формат Ячейки”, на вкладці “Число” в списку “Обозначение” обрати “грн. Украинский” (в полі “Числовые форматы” повинен бути обраний формат “Денежный”. Після натискання кнопки “Ок” значення в комірці буде відформатоване як -1305,02 грн.

Завдання 10.Завдання 10.

Нехай залежність між платою за оренду приміщень (Х) і обсягом продажів обладнання (Y) описується таблицею

Х | 75 | 78 | 80 | 90 | 10 | 110 | 115 | 115 | 120 | 125

Y | 40 | 42 | 45 | 50 | 55 | 56 | 60 | 64 | 65 | 65

Використовуючи просту лінійну регресію, на п’ятому аркуші книги Excel визначте залежність Y від Х і спрогнозуйте обсяг продажів при Х=140. Збережіть робочий документ Excel на дискеті під назвою вправа2.

Лінійна регресія використовується для аналізу впливу на окрему залежну змінну значень однієї або декількох незалежних перемінних. В Excel існують кілька функцій для регресійного аналізу а також діалогові вікна спеціальної надбудови “Пакет аналізу”.

В даному разі слід визначити залежність між Х та Y. Це можна зробити за допомогою функції ЛИНЕЙН (група “Статистические функції” майстра функцій) або за допомогою діалогового вікна “Регрессия” надбудови “Пакет аналізу”. При розв’язанні задачі використовувався другий варіант.

Для визначення залежності Х та Y необхідно:

1. Виконати команду “Сервис Анализ данных Регрессия”.

2. Ввести вхідний інтервал Y (в даному разі $A$2:$K$2).

3. Ввести вхідний інтервал Х ($A$1:$K$1),

4. Встановити прапорець “Метки” (діапазон включає позначки “Х” та “Y”).

5. Встановити перемикач в положення “вихідний інтервал” та обрати у відповідному полі комірку ($A$7).

6. Натиснути кнопку “Ок”.

Як видно з повернутих надбудовою значень, коефіцієнт детермінованості r2 дорівнює 0,264. Коефіцієнт є значимим, але його абсолютне значення є невеликим. З цього слідує, що залежність між Х та Y об’єктивно існує, однак зміни залежної величини (Y) не можна пояснити виключно змінами незалежної величини (Х); напевно існують й інші перемінні, які в даному разі не враховані.

Спрогнозувати обсяг продажів при Х=140 можна за допомогою функції ТЕНДЕНЦИЯ.

Функція має наступний синтаксис:

Изв_знач_х відомі значення х.

Изв_знач_у відомі значення у.

Нов_знач_х нове значення х.

Константа логічне значення, яке визначає, чи потрібно, щоб константа b (Y-перетин прямої) дорівнювала 0.

Підставляючи у формулу наявні значення отримуємо:

Изв_знач_х В2:К2

Изв_знач_у В1:К1

Нов_знач_х 140

Константа

Результат = 61,083.

Завдання 11.Завдання 11.

Створіть в Access базу даних постачальників обладнання. Для цього створіть таблицю “Постачальники” з полями