Створення таблиць підстановки 5 страница

Параметри форматування зведеної таблиці можна змінити, встановивши табличний курсор у будь-якій клітинці зведеної таблиці та клацнути на кнопці Формат отчета панелі Сводная таблица. В отриманому діалоговому вікні Автоформат вибрати потрібний автоформат і клацнути на кнопці ОК.

Зведена таблиця, що містить декілька сторінок

Деякі зведені таблиці можна відобразити у вигляді набору однотипних таблиць-сторінок. Щоб створити зведену таблицю, яка складається з кількох сторінок, у вікні макета потрібно в область Страница перетягти назву поля, по якому будуть створюватися сторінки (поле Товар), в область Строка – поле Назва, в область Данные – поле Сума.

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

Хід роботи

1. Внести в таблицю дані. Вказати формат полів:

• поле Дата надходження – тип дата;

• поле Ціна – грошовий;

• поле Кількість - числовий, кількість знаків після коми - 0.

№	Товар	Назва	Дата	Ціна	Кількість
	Кава	Еліт	ВНЕСТИ ДОВІЛЬНІ ДАНІ	ВНЕСТИ ДОВІЛЬНІ ДАНІ	ВНЕСТИ ДОВІЛЬНІ ДАНІ
	Чай	Краснодарський
	Чай	Принцеса Канді
	Чай	Аскольд
	Кава	Гранд
	Чай	Принцеса Ява
	Чай	Ліптон
	Чай	Принцеса Нурі
	Кавовий напій	Цикорій
	Чай	Грузинський
	Кава	Арабіка
	Чай	Батік
	Кавовий напій	Несквік
	Чай	Цейлонський
	Кава	Нескафе
	Чай	Ахмат
	Кавовий напій	Шипшина
	Кава	Жокей
	Кава	Ранія
	Кавовий напій	Галка

2. Додати поле Сума, для знаходження (Сума = Ціна * Кількість).

3. Створити зведену таблицю для визначення вирученої суми за кожен вид товару.

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

Контрольнізапитання

1) Що таке зведені таблиці і для чого вони призначені?

2) Як створити зведену таблицю?

3) Що таке макет зведеної таблиці?

4) У яких випадках доцільно розміщувати зведену таблицю на кількох сторінках?

5) Як керувати відображенням даних у зведеній таблиці?

Практична робота 11

Тема. Використання спеціальних інструментів аналізу та оптимізації.

Мета. Дати поняття про використання в програмі Microsoft Ехсеl інструментів аналізу та оптимізації. Навчити використовувати можливості аналізу “що-як” для обчислень.

Учні повинні знати: використання спеціальних інструментів аналізу та оптимізації для виконання обчислень.

Учні повинні уміти: використовувати можливості аналізу “що-як” для обчислень.

Програмне забезпечення: табличний процесор Microsoft Ехсеl.

Теоретичні відомості

Однією з найважливіших переваг програми Excel є можливість виконувати аналіз та оптимізацію даних. Наприклад, аналіз "що-як" дозволяє простежити залежність кінцевого результату від зміни вихідних параметрів. Можна також виконати протилежну операцію – знайти вихідні дані, що після підстановки у формули дадуть певні результати. Такий аналіз даних виконується методами підбору параметра та пошуку розв'язання.

Аналіз "що-як"

Аналіз "що-як" – це процес зміни значень комірок і дослідження впливу цих змін на результат обчислення формул на робочому аркуші.

Існує три способи виконання аналізу "що-як":

1. вручну;

2. за допомогою таблиць підстановки;

3. за допомогою диспетчера сценаріїв.

Метод виконання аналізу "що-як" вручну

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

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

Засоби Таблиця підстановки

Таблиця підстановки дозволяє відобразити на екрані результати обчислень при зміні одного чи двох вихідних параметрів, а за допомогою диспетчера сценаріїв можна створити підсумковий звіт для будь-якої кількості вихідних параметрів

Створення таблиць підстановки

Таблиці підстановки можна розділити на два види:

1. таблиця підстановки з одним входом – можна відобразити результати розрахунків по одній або декількох формулах при різних значеннях одного вихідного параметру;

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

Приклад

Сім’я вирішила купити квартиру, для чого їй необхідно взяти в банку позику на суму $45 000 на 10 років (тобто на 120 місяців). Обчислити розмір щомісячних виплат і загальну суму виплат по цій позиці, для декількох відсоткових ставок (наприклад, 5%, 5,5%, 6%, 6,5%, 7%, 7,5%, 8% і 8,5%).

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

Для розрахунку щомісячних виплат можна скористатися функцією ППЛАТ (повертає суму чергової виплати за позикою на основі постійних періодичних виплат і постійної відсоткової ставки; належить до категорії Фінансові). В комірку В5 введена наступна формула обчислення щомісячної виплати: =ППЛАТ(В4/12; ВЗ; В2)

В комірку В6 введена формула розрахунку загальної суми виплат: =В5*ВЗ.

Тепер можна починати створювати таблицю підстановки. Для цього потрібно виконати такі дії:

В діапазон А10:А17 ввести значення відсоткових ставок, для яких потрібно обчислити розмір щомісячних виплат та загальної суми виплат.

В комірки В8 і С8 ввести написи з комірок А5 і А6 (Щомісячна виплата і Загальна сума). А в комірки В9 і С9 – посилання на комірки з формулами, за якими буде обчислено результат. В нашому випадку потрібно виконати посилання на комірки В5 і В6.

Виокремити діапазон таблиці підстановки. Це мінімальний діапазон комірок, що містить формули та всі значення діапазону вихідних даних (у даному прикладі – діапазон А9: С17).

Вибрати команду Данные/Таблица подстановки, після чого з'явиться діалогове вікно Таблица подстановки.

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

Клацнути на кнопці ОК, після чого таблицю підстановки буде створено.

Зверніть увагу, що ліва верхня комірка цієї таблиці не використовується.

Щоб дізнатися, якими будуть щомісячні виплати для відсоткових ставок 5%, 5,5%, 6%, 6,5%, 7%, 7,5%, 8% і 8,5% при строках 5, 10, 15 та 20 років (тобто 60, 120, 180 та 240 місяців), доведеться створити таблицю підстановки із двома входами.

Щоб створити таблицю підстановки із двома входами, слід виконати наступні дії.

1. Ввести у діапазон комірок А10:А17 значення відсоткових ставок, для яких слід визначити розмір щомісячних виплат.

2. В діапазон В9: Е9 ввести строки позики: 60, 120, 180 і 240, а в комірку В8 – напис Строк погашення позики, місяці.

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

4. Виокремити діапазон таблиці підстановки, це буде діапазон А9:Е17 (мінімальний діапазон комірок, що містить два діапазони й вихідних значень та формулу).

5. Вибрати команду Данные/Таблица подстановки.

6. В отриманому діалоговому вікні Таблица задати відповідні комірки.

7. У нашому прикладі в поле Подставлять значения по столбцам в слід ввести посилання на комірку В3, а в поле Подставлять значения по строкам в - посилання на комірку В4.

8. Клацнути на кнопці OK, щоб створити таблицю підстановки.

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

Хід роботи

1. Використавши таблицю підстановки з одним входом, обчислити розмір щомісячних виплат і загальну суму виплат по позиці на суму 10 000 грн. на період 2 роки для таких відсоткових ставок: 6%, 7%, 8% 9%, 10%.

2. Використавши таблицю підстановки з двома входами, обчислити розмір щомісячних виплат і загальну суму виплат по позиці на суму 25 000 грн. на періоди 6, 12, 18, 24 місяці для таких відсоткових ставок: 5%, 5,5%, 6%, 6,5%, 7%, 7,5%.

Контрольні запитання

1) Що таке “аналіз "що-як"”?

2) Які є способи виконання аналізу "що-як"?

3) Що таке засіб “Таблиця підстановки”?

4) Які є види таблиць підстановок?

Практична робота 12

Тема. Використання інструменту Подбор параметра.

Мета. Дати поняття про використання в програмі Microsoft Ехсеl інструменту Подбор параметра. Навчити використовувати можливості інструмента для отримання розв’язків та прогнозування.