МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

ВІДОКРЕМЛЕНИЙ СТРУКТУРНИЙ ПІДРОЗДІЛ

"КИЇВСЬКИЙ ТРАНСПОРТНО-ЕКОНОМІЧНИЙ

ФАХОВИЙ КОЛЕДЖ

НАЦІОНАЛЬНОГО ТРАНСПОРТНОГО УНІВЕРСИТЕТУ"

 

 

ТЕХНОЛОГІЇ ОБРОБКИ ЕКОНОМІЧНОЇ ІНФОРМАЦІЇ. ЗАСОБИ ДЛЯ АНАЛІЗУ ДАНИХ В ЕЛЕКТРОННИХ ТАБЛИЦЯХ MS EXCEL

Методичні вказівки
до самостійного вивчення теми

з курсу "Інформатика і комп'ютерна техніка"

для студентів денної форми навчання

спеціальностей:

242 Туризм і рекреація, освітньо-професійна програма  “Туристичне  обслуговування”;

073  Менеджмент, освітньо-професійна програма "Організація  обслуговування на транспорті”;

275 Транспортні технології (на автомобільному транспорті), освітньо-професійна програма "Організація перевезень і управління на автомобільному  транспорті"             

 

 

 

 

 

Київ 2020

 

Зміст

Передмова

Вступ

1. Інтерфейс програми Microsoft Excel 2007.

Стрічка

Вкладки

Елементи управління

Кнопка "Office"

Панель швидкого доступу

Міні-панель інструментів

Рядок формул

2. Засоби аналізу даних табличного процесора MS Excel

2.1  Таблиці даних (таблиці підстановки)

2.1.1 Таблиці підстановки для однієї змінної

2.1.2 Таблиця підстановки з кількома формулами

2.1.3. Таблиці підстановки для двох змінних

2.2  Засіб Підбір параметра

2.3. Надбудова Пошук рішення

2.3.1 Параметри засобу Пошук рішення

2.4 Консолідація даних

2.5 Зведені таблиці

2.5.1 Форматування зведеної таблиці

2.5.2 Аналіз даних в зведеній таблиці.

2.6 Зведена діаграма

2.7 Моделювання за допомогою сценаріїв

Висновки

Список використаних джерел

Додатки

Тести для перевірки знань з теми "Табличний процесор"

Завдання для практичного самостійного виконання

Передмова

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

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

Дана методична розробка  призначена для кращого засвоєння теми "Робота в електронних таблицях MS Excel" з курсу "Інформатика і комп'ютерна техніка", а також для розширення і доповнення лекційного матеріалу з метою розвитку навичок самостійної практичної роботи студентів, які навчаються за напрямами  Туризм і рекреація  та Менеджмент.

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

 В методичній розробці розглядаються теми, пов'язані, насамперед, з однією із важливих функцій електронних таблиць - можливістю швидкої і легкої постановки так званого експерименту для аналізу ситуації «що-якщо».

Висвітлюються засоби MS Excel  для аналізу даних, створення за їх допомогою конкретних моделей, а також приклади їх практичного застосування.

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

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

Для успішного засвоєння матеріалу потрібне володіння ПК на рівні користувача, включаючи роботу з додатками MS Office.

Вступ

У сучасному інформаційному суспільстві обробка інформації є необхідною умовою організації виробництва. Для прийняття ефективних управлінських рішень виникає необхідність, враховуючи різноманітні фактори, обробляти значну кількість інформації. Стрімке зростання обсягу науково-технічної інформації, з одного боку, і розвиток обчислювальної техніки, з іншого боку, викликали необхідність створення нових інформаційних технологій. Електронні сховища інформації дозволили компактно зберігати, багатократно використовувати, сортувати, редагувати, відображати на екрані дисплея, а при необхідності отримувати копії документів на папері. Такі інформаційні системи забезпечували доступ до інформації великому числу користувачів незалежно від їх географічного місцезнаходження і дозволяли оперувати великими об’ємами даних.

Електронна таблиця – (англ.- spreadsheets) – це програма, що моделює на екрані двовимірну таблицю, яка складається з рядків і стовпців. Основним призначенням електронної таблиці є введення даних до комірок й обробка їх за формулами.

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

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

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

Серед найвідоміших табличних процесорів є такі як: Excel, Lotus 1-2-3, Quattro Pro. Досить велика перевага надається табличному процесору програми Excel. Можливості Excel набагато більші, ніж розуміють під терміном табличний процесор. Опрацювання тексту, статистичний аналіз та прогнозування, ділова графіка, керування базами даних, підготовка числових, текстових та змішаних таблиць, оформлення найрізноманітніших бланків, наведення результатів у формі ділової графіки – програма настільки потужна, що у багатьох випадках перевершує спеціалізовані програми – текстові редактори чи системи керування базами даних.

Наприклад, використовуючи цю програму, на підприємстві можна розраховувати податки і заробітну плату, вести облік кадрів і витрат, планувати виробництво та керувати збутом. А потужні математичні та інженерні функції Excel дають змогу розв’язувати багато задач у галузі природничих та технічних наук.

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

• введення і редагування даних, автоматизація введення (автозаповнення, автозаміна тощо); форматування табличних даних із використанням стандартних засобів, стилів, шаблонів;
•  виконання обчислень за формулами (тут може використовуватися великий набір вбудованих функцій);
• аналіз табличних даних (введення проміжних і загальних підсумків, створення зведених таблиць, добір параметрів, прогнозування розв’язків);
• графічне зображення даних (побудова графіків, діаграм; уведення малюнків, відео матеріалів, географічних карт);
• робота зі списками (упорядкування й фільтрація записів, пошук даних);
• колективна робота з таблицями (обмін файлами в локальній мережі, спільне використання і захист даних, обмін інформацією через Internet);
• розробка програмних додатків, заснованих на вбудованій мові програмування VBA (Visual Basic for Appplications).

 

1. Інтерфейс програми Microsoft Excel 2007.

Для кращого розуміння  користувачем  інтерфейсу Microsoft Excel 2007 представимо основні елементи нового інтерфейсу: вікно, стрічка, контекстні вкладки, елементи управління, панель швидкого доступу,  новий елемент вікна - міні-панель інструментів.

При плануванні випуску системи 2007 Microsoft Office розробниками було поставлено завдання зробити основні додатки Microsoft Office зручніше в роботі. В результаті був створений призначений для користувача інтерфейс Microsoft Office Fluent, який спрощує для користувачів роботу з додатками Microsoft Office і дає їм можливість швидше отримати кращі результати.

У попередніх випусках додатків Microsoft Office для виконання своєї роботи користувачі використали систему меню, панелей інструментів, діалогових вікон. Ця система працювала добре, коли в додатках було обмежене число команд. Тепер, коли програми виконують набагато більше функцій, система меню і панелей інструментів працює не так добре. Надто багато програмних можливостей багатьом користувачам важко знайти.

Метою переробки дизайну призначеного для користувача інтерфейсу Office Fluent було спрощення для користувачів процедур пошуку і використання усього діапазону можливостей, що надаються цими застосуваннями. Крім того, передбачалося запобігти захаращенню  робочої області і, отже, відверненню уваги користувачів для того, щоб вони могли більше часу і енергії витрачати на свою роботу.

Стрічка

Головний елемент призначеного для користувача інтерфейсу Microsoft Excel 2007 є стрічкою, яка йде уздовж верхньої частини вікна кожного застосування, замість традиційних меню і панелей інструментів (мал. 1.1).

 

 

Мал. 1.1  Стрічка

За допомогою стрічки можна швидко знаходити необхідні команди (елементи управління : кнопки, списки, що розкриваються, лічильники, прапорці і тому подібне). Команди впорядковані в логічні групи, зібрані на вкладках. Замінити стрічку панелями інструментів або меню попередніх версій додатка Microsoft Excel не можна. Видалити стрічку також не можна. Проте, щоб збільшити робочу область, стрічку можна приховати (згорнути). Для цього:

1. Натисніть кнопку Налаштування панелі швидкого доступу (мал. 1.2).
2. У меню виберіть команду Згорнути стрічку.

Мал. 1.2  Згортання стрічки

 

3. Стрічка буде прихована, назви вкладок залишаться (мал. 1.3).

 

 

Мал. 1.3  Вікно із згорнутою стрічкою

 

Для використання стрічки в згорнутому стані клацніть по назві потрібної вкладки, а потім виберіть параметр або команду, яку слід використати. Наприклад, при згорнутій вкладці можна виділити текст в документі Excel, клацнути вкладку Головна і в групі Шрифт вибрати потрібний розмір шрифту. Після вибору розміру шрифту стрічка знову згорнеться.

Щоб швидко згорнути стрічку,  двічі клацніть ім'я активної вкладки. Для відновлення стрічки двічі клацніть вкладку. Щоб згорнути або відновити стрічку, можна також натиснути комбінацію клавіш Ctrl + F1.

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

Зовнішній вигляд стрічки залежить від ширини вікна : чим більше ширина, тим детальніше відображаються елементи вкладки. На малюнках приведено відображення стрічки вкладки Головна при різній ширині вікна.

 

 

Мал. 1.4  Відображення стрічки вкладки Головна при ширині вікна 1280 точок

 

 

Мал. 1.5  Відображення стрічки вкладки Головна при ширині вікна 1024 точки

 

 

Мал. 1.6  Відображення стрічки вкладки Головна при ширині вікна 800 точок

При істотному зменшенні ширини вікна (менше 300 точок) стрічка і імена вкладок перестають відображатися.

 

Мал. 1.7  Приховання стрічки і вкладок при зменшенні ширини вікна

Вкладки

За умовчанням у вікні відображається сім постійних вкладок: Головна, Вставка, Розмітка сторінки, Формули, Дані, Рецензування, Вид.

Для переходу до потрібної вкладки досить клацнути по її назві (імені). Кожна вкладка пов'язана з видом виконуваної дії. Наприклад, вкладка Головна, яка відкривається за умовчанням після запуску, містить елементи, які можуть знадобитися на початковому етапі роботи, коли необхідно набрати, відредагувати і відформатувати текст. Вкладка Розмітка сторінки призначена для установки параметрів сторінок документів. Вкладка Вставка призначена для вставки в документи різних об'єктів. І так далі.

Крім того, можна відобразити ще одну вкладку -  Розробник.

1. Клацніть по кнопці Microsoft Office
2. Виберіть команду Параметри Excel.
3. У вкладці Основні діалогового вікна Параметри Excel встановите відповідний прапорець (мал. 1.8).

 

Мал. 1.8  Відображення вкладки Розробник

На вкладці Розробник зібрані засоби створення макросів і форм, а також функції для роботи з XML. Файли і шаблони попередніх версій Excel могли містити призначені для користувача панелі інструментів. В цьому випадку при відкритті таких файлів в Excel 2007 з'являється ще одна постійна вкладка - Надбудови (мал. 1.9).

 

Мал. 1.9  Вкладка Надбудови

Ця вкладка містить елементи панелей інструментів, створених в попередніх версіях Excel. Кожна панель інструментів займає окремий рядок стрічки вкладки. Проте деякі елементи, що були на панелях інструментів, в Excel 2007 можуть бути відсутніми.

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

 

Мал. 1.10  Вкладка Попередній перегляд

Окрім постійних, є цілий ряд контекстних вкладок, наприклад, для роботи з таблицями, малюнками, діаграмами і тому подібне, які з'являються автоматично при переході у відповідний режим або при виділенні об'єкту або установці на нього курсору. Наприклад, при створенні колонтитулів з'являється відповідна вкладка (мал. 1.11).

 

Мал. 1.11  Контекстна вкладка Робота з колонтитулами/Конструктор

 

В деяких випадках з'являється відразу декілька вкладок, наприклад, при роботі з діаграмами з'являються три вкладки: Конструктор, Макет і Формат (мал. 1.12).

 

Мал. 1.12  Контекстні вкладки для роботи з таблицями

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

Елементи управління

Елементи управління на стрічках вкладок об'єднані в групи, пов'язані з видом виконуваної дії. Наприклад, на вкладці Головна є групи для роботи з буфером обміну, установки параметрів шрифту, установки параметрів абзаців, роботи із стилями і редагування (див. рис. 1.1).

Елементами управління є звичайні кнопки, кнопки, що розкриваються, списки, списки, що розкриваються, лічильники, кнопки з меню, прапорці, значки (кнопки) групи.  Кнопки використовуються для виконання якої-небудь дії. Наприклад, кнопка Напівжирний групи Шрифт вкладки Головна встановлює напівжирне зображення шрифту. Щоб натиснути кнопку, потрібно клацнути по ній мишею (мал. 1.13).

 

Мал. 1.13  Використання звичайної кнопки

В деяких випадках натиснення кнопки викликає діалогове вікно.

Кнопки, що розкриваються, мають стрілку в правій нижній частині. При клацанні по стрілці відкривається меню або палітра, в якій можна вибрати необхідну дію або параметр. Вибрана дія або параметр запам'ятовуються на кнопці, і для повторного застосування не вимагається відкривати кнопку. Наприклад, можна клацнути по стрілці кнопки Межа групи Шрифт вкладки Головна і вибрати спосіб, вид і розташування межі (мал. 1.14). Щоб ще раз призначити таку ж межу, не треба клацати по стрілці, досить клацнути по самій кнопці.

 

Мал. 1.14  Використання кнопки, що розкривається

У списку можна вибрати для застосування який-небудь параметр. Для цього потрібно клацнути по ньому мишею. Наприклад, в списку Макети діаграм вкладки Конструктор можна вибрати макет діаграми (мал. 1.15).

 

Мал. 1.15  Використання списку

Для перегляду списку в межах стрічки можна користуватися кнопками Вперед і Назад. Але зазвичай список розгортають, для чого потрібно клацнути по кнопці Додаткові параметри (див. мал. 1.15). У нижній частині розгорнутого списку можуть відображаються відповідні команди меню (мал. 1.16).

 

Мал. 1.16  Розгорнутий список

 

При використанні списку, що розкривається, потрібно клацнути по стрілці списку і вибрати необхідний параметр. Наприклад, в списку, що розкривається, Розмір шрифту групи Шрифт вкладки Головна можна вибрати розмір шрифту (мал. 1.17).

Мал. 1.17  Використання списку, що розкривається

 

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

Лічильники використовують для установки числових значень яких-небудь параметрів. Для зміни значення користуються кнопками лічильника Більше (Вгору) і Менше (Вниз). Наприклад, в лічильнику Масштаб групи Вписати вкладки Розмітка сторінки можна встановити величину масштабу друку документу (мал. 1.18).

 

Мал. 1.18  Використання лічильників

Також значення в полі лічильника можна ввести з клавіатури і натиснути клавішу Enter. Натиснення деяких кнопок не призводить до виконання якої-небудь дії, а тільки відображає меню, в якому слід вибрати потрібну дію. Наприклад, клацнувши по кнопці Орієнтація в групі Вирівнювання вкладки Головна в меню, що з'явилося, можна вибрати напрям тексту в осередку (мал. 1.19).

 

Мал. 1.19  Використання кнопки з меню

В деяких випадках вибір команди меню кнопки викликає діалогове вікно. Прапорці використовуються для застосування якого-небудь параметра. Наприклад, прапорці групи Параметри листа вкладки Розмітка сторінки (мал. 1.21) визначають особливості друку листа.

 

Мал. 1.20  Використання прапорців

Для установки або зняття прапорця досить клацнути по ньому мишею.

Значок (кнопка) групи - маленький квадрат в правому нижньому кутку групи елементів у вкладці. Клацання по значку відкриває те, що відповідає цій групі діалогове вікно або область завдань для розширення функціональних можливостей. Наприклад, значок групи Шрифт вкладки Головна відкриває діалогове вікно Формат осередку (мал. 1.21).

 

Мал. 1.21  Використання значка групи

 

А значок групи Буфер обміну відображає область завдань Буфер обміну (мал. 1.22). Не кожна група має значок.

 

 

Мал. 1.22  Використання значка групи

Для кожного елементу управління можна відобразити спливаючу підказку про призначення цього елементу. Для цього досить навести на нього і на деякий час зафіксувати покажчик миші. Приклади спливаючих підказок наведені на мал. 1.23 - мал. 1.25.

 

Мал. 1.23  Спливаюча підказка для елементу управління

 

Мал. 1.24  Спливаюча підказка для елементу управління

 

 

Мал. 1.25  Спливаюча підказка для елементу управління

В деяких випадках в підказці відображається текст " Для отримання додаткових відомостей натисніть клавішу F1". В цьому випадку при натисненні клавіші F1 з'являється вікно довідкової системи з довідкою, що відноситься безпосередньо до вибраного елементу.

 

Кнопка "Office"

Кнопка "Office" розташована в лівому верхньому кутку вікна. При натисненні кнопки відображається меню основних команд для роботи з файлами, список останніх документів, а також команда для налаштування параметрів додатка(мал. 1.26).

 

Мал. 1.26  Кнопка і меню "Office"

Деякі з команд меню кнопки "Office" мають підпорядковані меню.

 

Панель швидкого доступу

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

1. Натисніть кнопку Налаштування панелі швидкого доступу.
2. У меню виберіть найменування необхідного елементу (мал. 1.27). Елементи, відмічені галочкою, вже є присутніми на панелі.

 

Мал. 1.27  Налаштування панелі швидкого доступу

3. Для додавання елементу, відсутнього в списку, виберіть команду Інші команди (див. мал. 1.28).
4. У розділі Налаштування вікна Параметри Excel в списку, що розкривається, Вибрати команди виберіть вкладку, в якій розташований елемент, що додається, потім виділіть елемент в списку і натисніть кнопку Додати (мал. 1.28).

 

Мал. 1.28  Налаштування панелі швидкого доступу

Для додавання на панель будь-якого елементу з будь-якої вкладки можна також клацнути по цьому елементу правою кнопкою миші і в контекстному меню вибрати команду Додати на панель швидкого доступу.

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

Міні-панель інструментів

Міні-панель інструментів містить основні найчастіше використовувані елементи для оформлення даних. На відміну від інших додатків Office 2007 (Word, PowerPoint та ін.) в Excel 2007 міні-панель не відображається автоматично при виділенні фрагмента листа. Для відображення панелі клацніть правою кнопкою по виділеній області (мал. 1.29).

 

Мал. 1.29  Відображення міні-панелі інструментів

Рядок формул

У Excel 2007 зручніше, ніж в попередніх версіях організована робота з рядком формул. Для перегляду і редагування вмісту виділеного осередку можна збільшити висоту рядка формул. Для цього клацніть по кнопці Розгорнути рядок формул (мал. 1.30).

 

Мал. 1.30  Збільшення висоти рядка формул

Якщо і в цьому випадку відображається не увесь вміст осередку, можна скористатися смугою прокрутки (мал. 1.31).

Мал. 1.31  Перегляд вмісту осередку в рядку формул

Для того, щоб привести рядок формул в початковий стан клацніть по кнопці Згорнути рядок формул.

 

Вправи для самостійного виконання.

Завдання 1

1. Запустіть Microsoft Excel 2007.
2. Згорніть стрічку з використанням Панелі швидкого доступу.
3. Розгорніть стрічку подвійним клацанням по назві вкладки Головна.
4. Згорніть і розгорніть стрічку з використанням комбінації клавіш Ctrl + F1.
5. Зменште розмір вікна так, щоб стрічка і вкладки перестали відображатися.
6. Розгорніть вікно на увесь екран.
7. Закрийте Microsoft Excel 2007.

Завдання 2

1. Запустіть Microsoft Excel 2007.
2. Перейдіть у вкладку Формули.
3. Перейдіть у вкладку Вид.
4. Поверніться у вкладку Головна.
5. Закрийте Microsoft Excel 2007.

Завдання 3

1. Запустіть Microsoft Excel 2007.
2. У вкладці Головна в групі Шрифт натисніть кнопку Напівжирний (Ж). Переконайтеся, що кнопка залишилася натиснутою.
3. У вкладці Головна в групі Шрифт клацніть по стрілці кнопки Заливка і виберіть червоний колір. Переконайтеся, що для виділеного осередку встановлена заливка червоним кольором.
4. У вкладці Головна в групі Шрифт в списку, що розкривається, Розмір шрифту виберіть розмір 26. Переконайтеся, що висота виділеного осередку збільшилася.
5. У вкладці Головна в групі Стилі клацніть по кнопці Стилі осередків і виберіть стиль Хороший. Переконайтеся, що оформлення виділеного осередку змінилося.
6. У вкладці Головна відобразите діалогове вікно групи Шрифт. Закрийте діалогове вікно Шрифт.
7. У вкладці Головна відобразите спливаючу підказку для кнопки Перенесення тексту. Відобразіть вікно довідкової системи для цієї кнопки.
8. Закрийте Microsoft Excel 2007. Зміни в документі не зберігайте.

Завдання 4

1. Запустіть Microsoft Excel 2007.
2. Додайте в Панель швидкого доступу кнопку Відкрити.
3. З вкладки Вставка (група Діаграми) додайте в Панель швидкого доступу кнопку Гістограма.
4. Закрийте Microsoft Excel 2007.

2. Засоби аналізу даних табличного процесора MS Excel

2.1  Таблиці даних (таблиці підстановки)

Таблиця підстановки – це спеціальна таблиця, в якій відображається, як зміна значень однієї або двох змінних, що входять у формулу, впливає на результат цієї формули.   Табличний процесор Excel передбачає створення таблиць підстановки двох видів: у першому варіанті тестується зміна значення однієї або декількох формул при зміні окремого параметра, а для другого варіанту відображається зміна значення тільки однієї формули, але при варіюванні значень декількох змінних.  Щоб створити будь-яку з цих таблиць, необхідно в меню Дані вибрати команду Таблиця даних (для версії MS Excel 2003 Таблиця підстановки).

2.1.1 Таблиці підстановки для однієї змінної

Розглянемо конкретну ситуацію. Припустимо, ви розглядаєте пропозицію щодо купівлі будинку.  Для цього у банку необхідно оформити позику у розмірі $ 200 000 з виплатою на 30 років. Вам необхідно обчислити розмір щомісячних виплат при різних процентних ставках. Розв'язання цієї задачі продемонструємо за допомогою засобу Таблиця підстановки для однієї змінної,  яка зображена на мал. 2.1,  і надає потрібну інформацію.

Мал. 2.1 Побудова вхідних даних для таблиці підстановки

Побудова таблиці підстановки  починається з введення ряду різних процентних ставок і запису формули з функцією ПЛТ. Отже, для вирішення поставленого завдання потрібно виконати наступні дії:

1. Помістити в осередку ВЗ: В9 значення потенційних процентних ставок, як на мал. 2.1.  Цей діапазон називається вхідним діапазоном, оскільки він містить ті самі вхідні значення, які потрібно протестувати
2. Ввести величину кредиту в клітинку поза таблиці даних. У нашому прикладі ми ввели значення $ 200 000 в осередок С1.  Це дозволить потім легко змінювати величину позики при спробі різних сценаріїв.
3. Ввести відповідну формулу. В даному випадку в комірці С2 запишемо формулу  = ПЛТ (А2/12; 360; С1), де А2/12 – місячний відсоток, 360 – термін погашення позики в місяцях, а С1 – посилання на величину суми позики. Зауважимо, що ПЛТ — одна із фінансових функцій, що повертає суму періодичних виплат  на основі сталих сум виплат і сталої процентної ставки.
4.  Зверніть увагу, що ця формула посилається на клітинку А2, яка зараз порожня. При обрахуванні формул Excel присвоює порожнім осередкам значення 0, тому функція в комірці С2 повертає величину щомісячних виплат, розраховану за нульовою процентною ставкою Тобто осередок А2, називається  осередком введення, – це фіксований осередок, через який  Excel буде надалі проводити розрахунок, підставляючи в нього значення з вхідного діапазону.
5.  Закінчивши введення вхідних значень і формули, виділіть діапазон таблиці даних – мінімальний прямокутний блок, що захоплює формулу і весь вхідний діапазон. У нашому випадку потрібно виділити діапазон В2: С9 і в меню Дані вибрати команду Таблиця даних (Таблиця підстановки).
6. На екрані зявиться діалогове вікно Таблиця даних, представлене на мал.2.2. Місце знаходження осередку введення вказується або в полі Підставляти значення по стовпцях в або в полі Підставляти значення по рядках в. Осередок введення – це осередок, на який посилається формула таблиці підстановки, в нашому прикладі нею є осередок А2.  Якщо вхідний діапазон є рядком, посилання на вхідний осередок задається в полі Підставляти значення по стовпцях в, якщо ж навпаки – стовпцем (як в даному випадку), використовується поле Підставляти значення по  рядках в.

Мал. 2.2  У діалоговому вікні Таблиця даних задається осередок введення

Залишилося натиснути кнопку ОК.  Excel помістить результати обчислення формули для кожного вхідного значення у вільні комірки діапазону таблиці підстановки. У нашому прикладі Excel виведе сім значень в діапазон СЗ: С8, як показано на мал. 2.3

Мал.2.3 Тепер щомісячні виплати за позикою для кожної з величин процентної ставки відображаються в таблиці підстановки

При створенні таблиці підстановки програма занесла формулу масиву {ТАБЛИЦЯ = ( А2)} в кожну клітинку діапазону результатів, а в цілому в діапазон СЗ: С9.  За цією формулою обчислюється значення функції ПЛТ для кожного із значень вхідного діапазону в стовпці В. Після побудови таблиці можна в будь-який момент змінювати вихідні значення, і результат обчислень відразу ж буде відображатися на екрані. Функція ТАБЛИЦЯ є прихованою функцією Excel.  Це означає, що її не можна вибрати зі списку діалогового вікна Майстра функцій або ввести вручну.

2.1.2 Таблиця підстановки з кількома формулами

 

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

Змінимо попереднє завдання. Нехай необхідна сума для покупки будинку  береться в банку і її розмір – $ 180 000 з терміном погашення 30 років.  Тепер нам важливо визначити розмір щомісячних платежів по кредиту, а також порівняти його з виплатами для позики в $ 200 000 (мл.2.3). Розширимо побудовану раніше таблицю, включивши в неї обидві формули, в наступному порядку:

1. Занесіть нову формулу = ПЛT (A2/12; 360; Dl) в осередок D2. Зверніть увагу, що ця формула так само, як і перша, посилається на клітинку А2.
2. Введіть величину позики ($ 180 000) в осередок D1 і виділіть діапазон таблиці B2: D9.
3.  Виберіть у меню Дані команду Таблиця даних(Таблиця підстановки) і помістіть в поле Підставляти значення по рядках в діалогового вікна Таблиця даних  посилання на комірку А2 ($ А $ 2). Результат обчислень представлений на мал.2.4

Мал. 2.4  У цій таблиці підстановки розраховуються розміри щомісячних відрахувань за різними кредитами для різних процентних ставок

2.1.3. Таблиці підстановки для двох змінних

 

Досі обчислювалися значення по одній або декількох формулам при різних значеннях однієї змінної.  Але, припустимо, потрібно розрахувати розмір щомісячних виплат по кредиту в $ 200 000 не тільки для різних процентних ставок, але й залежно від терміну виплат – потрібно дізнатися, як їх тривалість впливає на розмір місячного платежу. Щоб створити таку таблицю, виконайте такі дії:

1.  Введіть в діапазон ВЗ: В9 величини процентних ставок. Потім заповніть даними , що визначають тривалість виплат за позикою в місяцях в рядку, розташованому безпосередньо вище першого осередку діапазону процентних ставок, так, як це показано на мал.2.5.  У нашому випадку другий набір вхідних даних знаходиться в діапазоні C2: F2.
2. Після цього вставте значення розміру позики в клітинку поза діапазону таблиці даних, наприклад в комірку І2.
3. Оскільки ми обумовили, що таблиця буде залежна від двох змінних, формулу слід вводити в клітинку, розташовану на перетині рядка і стовпчика, що містять дві множини вхідних значень, тобто в осередок В2. У таблиці підстановки з двома змінними, на відміну від таблиці з однією змінною, може бути тільки одна формула. В нашій задачі  вона записується так: = ПЛТ (А2/12; В1; І2).
4. Формула негайно поверне помилку # ДЕЛ / 0, оскільки обидві комірки, А2 і В1, використовувані як аргументи, порожні(0), і через це виходить ділення на 0. Але, як побачимо пізніше, подібний початок ніяк не впливає на виконання подальших обчислень.
5. Виділяємо діапазон таблиці підстановки – мінімальний блок прямокутної форми, що включає в себе всі вхідні значення і осередок з формулою. У нашому випадку – діапазон B2: F9. Тепер потрібно вибрати команду Таблиця даних(Таблиця підстановки) в меню Дані та задати комірки введення. Оскільки створюється таблиця з двома змінними, таких осередків повинно бути двоє.  Вкажемо в полі Підставляти значення за стовпцями в посилання на комірку В1          ($В$1), а в полі Підставляти значення по рядках в – на клітинку А2.

 

 

Мал. 2.5 Осередок В2 містить формулу з двома змінними

 

6. Залишилося натиснути клавішу Enter або кнопку ОК.  Результат обчислення таблиці даних з двома змінними (з невеликим форматуванням) показаний на мал.2.6.

Мал.2.6  Ця таблиця підстановки обчислює розмір щомісячних виплат по кредиту при різних процентних ставках і термінах погашення кредиту.

Будьте уважні, не переплутайте осередки введення в таблиці з двома змінними.  Якщо таке раптом станеться, для обчислень залучатимуться вхідні осередки з іншого діапазону, що призведе до невірних результатів. Так, у розглянутому випадку,  замість процентних ставок з діапазону ВЗ: В9 в формулу будуть підставлені тимчасові інтервали з діапазону C2: F2, тобто терміни погашення.  Навряд чи ви хочете, щоб витрати на будинок становили щомісяця більш ніж 20 млн доларів.  Щоб не помилитися, рекомендуємо при визначенні осередків введення поглядати на формулу. У нашому прикладі у формулі = ПЛТ (А2/12; В1;І2) вміст комірки А2 є першим аргументом, тобто ставкою.  Процентні ставки розташовуються в стовпці, тому посилання на комірку А2 потрібно ввести в поле Підставляти значення по рядках в.

Редагування таблиць підстановки

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

Діапазон-результат  можна скопіювати в будь-яку іншу частину робочого аркушу.  Це зручно, якщо надалі ви хочете змінити вхідні значення або формули розрахунку, зберігши первісний підсумок.  На мал.2.7  ми скопіювали значення з блоку C3: F9 в осередок C11: F17.  Копії вже не є формулами масиву, це звичайні числові константи – Excel автоматично замінює формули масиву їх числовими значеннями

 

Мал.2.7  При копіюванні діапазону результатів формули масиву автоматично заміщуються числами

2.2  Засіб Підбір параметра

 

Як відомо, формули в Microsoft Excel дозволяють визначити значення функції за її аргументами. Однак, може виникнути ситуація, коли значення функції відомо, а аргумент потрібно знайти,  тобто вирішити рівняння. 

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

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

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

Мал.2.8  Таблиця для розрахунку розміру пенсійних накопичень

 

У цій таблиці вказані вік, починаючи з якого в Пенсійний фонд вносяться платежі (А2), величина щомісячного внеску (В2), період відрахувань, розрахований за формулою = 60-А2, тобто передбачається, що мова йде про чоловіка, який вийде на пенсію в 60 років (С2), а також розмір процентної ставки (D 2).

Сума накопичень розраховується за допомогою функції за такою формулою: = БС (D2; C2;-B2 * 12; 0; 1).

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

БС (ставка; кпер: плата; нз: тип)

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

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

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

Підготувавши таблицю, слід виділити клітинку, відведену для представлення результату (у даному випадку F2), і  виконуємо  команду Дані, група Робота з даними, команда  Підбір параметра. Коли з'явиться діалогове вікно Підбір параметра (мал.2.9), адресу виділеної клітинки буде автоматично вставлено в поле  Встановити в клітинці. Потрібно вказати в полі Значення цільове значення - 1000. Далі помістити курсор у поле  Змінюючи значення клітинки  і виділити клітинку А2, після чого її адреса відобразиться у вказаному полі.

Мал.2.9  Діалогове вікно Підбір параметра із заданими параметрами

 

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

 

 

Мал.2.10  Результати підбору параметра

 

Загальний алгоритм виконання процедури Підбір параметра.

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

2. Викликати команду Підбір параметра  з вкладки Дані. У полі Встановити в клітинці  діалогового вікна буде відображатися адреса  цільової комірки.

3. Поставити в полі  Значення  значення, яке має містити цільова клітинка.

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

5. Натиснути кнопку ОК, і потрібний параметр буде підібраний в діалоговому вікні  Результат підбору параметра. Після закінчення цього процесу в ньому з'являться результати.

Розглянемо засіб  табличного процесора MS Excel Підбір параметра  для розв’язування задач прогнозування, які широко використовуються в економічних розрахунках. Нехай, наприклад, треба визначити, при яких значеннях інвестиційних витрат фірми «Алмаз» (тут — 140,33) термін окупності інвестицій становив би 1,15 (у таблиці — 0,71) На мал.2.11 представлена таблиця з  фінансовими показниками роботи фірм:

 

Мал.2.11 Вхідна таблиця

Встановлюємо курсор у комірку F3, після чого активізуємо команду Підбір параметра з вкладки Дані. В результаті з'являється діалогове вікно (мал.2.12), в якому у полі Встановити в клітинці визначають значення (за умовою задачі — 1,15), яке потрібно знайти для активної комірки F3, а в полі Змінюючи значення клітинки  вводять адресу комірки В3, значення якої необхідно змінити, й активізують кнопку ОК.

Мал.2.12  Вікно команди Підбір параметра

Після цього в наступному вікні (мал. 2.13) виводиться результат підбору параметра.

 

 

 

 

Мал.2.13 - Результат підбору параметра

Якщо рішення знайдено, то при активізації кнопки ОК нове значення залишається в комірці F3 (отримали значення інвестиційних витрат – 229,46), при активізації кнопки Відміна попереднє значення відновлюється.

2.3. Надбудова Пошук рішення

 

Засіб Пошук рішень (у оригіналі Excel Solver) – додаткова надбудова табличного процесора MS Excel, яка призначена для вирішення певних систем рівнянь, лінійних та нелінійних завдань оптимізації, використовується з 1991 року.

Розмір завдання, яке можна вирішити за допомогою базової версії цієї програми, обмежується такими граничними показниками:

• кількість невідомих (decision variable) – 200;
• кількість формульних обмежень (explicit constraint) на невідомих – 100;
• кількість граничних умов (simple constraint) на невідомих – 400.

Розробник програми Solver компанія Frontline System вже давно спеціалізується на розробці могутніх і зручних способів оптимізації, вбудованих в середовище популярних табличних процесорів різноманітних фірм-виробників (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).

Висока ефективність їх застосування пояснюється інтеграцією програми оптимізації і табличного бизнес-документу. Завдяки світовій популярності табличного процесора MS Excel вбудована в його середовище засіб Solver є найбільш поширеним інструментом для пошуку оптимальних рішень у сфері сучасного бізнесу.

За замовчанням в Excel надбудова Пошук рішення відключена. Щоб активізувати її в Excel 2007, клацніть значок Кнопка Microsoft Office, клацніть Параметри Excel, а потім виберіть категорію Надбудови. У полі Управління виберіть значення Надстройки Excel і натисніть кнопку Перейти. У полі Доступні надбудови встановіть прапорець поряд з пунктом Пошук рішення і натисніть кнопку ОК.

Розглянемо можливості засобу Пошук рішення на конкретному прикладі. За підсумками поточного року виробництво звітує за такими показниками (мал.2.14):

Мал.2.14 Показники виробництва

Постановка задачі:

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

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

Позначимо кількість кожного виду продукції, що виробляється, через змінну Х. Тобто: Х₁ - крісла, Х₄ - столи,Х₂ - стільці, Х₅ - полиці,Х₃ -табуретки, Х₆ - карнизи.

Тоді цільова функція буде мати наступний вигляд:

300Х₁ + 200Х₂ + 100Х₃ + 250Х₄ + 80Х₅ + 75Х₆ <= 500000

2.     На шляху досягнення мети є деякі обмеження:

•   виробничі потужності не дозволяють випускати у сумі більш ніж 2500 одиниць виробів, тобто: Х₁ + Х₂ + Х₃ + Х₄ + Х₅ + Х₆ <= 2500
•   виробляються готові вироби, а не комплектуючі до них,тобто:      Х_і = ціле

Не маємо права порушувати асортимент виробів, тобто виробництво кожного виробу не може бути рівним нулю:  Х₁, Х₂, Х₃, Х₄, Х_5, Х₆ >= 1

Почнемо вирішувати цю задачу:

1.                    для більшої зрозумілості того, що відбувається, зробимо копію нашої таблиці на цей же аркуш Excel (мал.2.15)

Мал.2.15 Вхідна таблиця

2.               Починаємо працювати із засобом Пошук рішення;

• встановлюємо табличний курсор у клітинку D23;
• на вкладці Дані у групі Аналіз вибираємо Пошук рішення;
• з’явиться вікно “Поиск решения” і починаємо вводити у відповідні місця цього вікна цільову функцію та обмеження нашої задачі.

Мал.2.16 Вікно Пошуку рішення

На мал.2.16  можна побачити, що введена адреса клітинки, де буде вираховуватися значення цільової функції ($D$23), а також задане значення 500000, якого ця функція повинна досягнути; крім того позначений               діапазон клітинок, значення в яких можна змінювати при пошуці рішення ($B$16:$B$21). Далі будемо вводити обмеження для нашої задачі:

1. обмеження на сумарне виробництво продукції 2500;
2. обмеження на асортимент (>=1);
3. обмеження на ціле значення змінних (=ціле).

Для введення кожного обмеження необхідно клікнути мишкою по кнопці Додати  біля списку Обмеження. З’явиться вікно Додавання обмежень, в яке і будемо вводити перше  і наступні обмеження (мал.2.17).

 

Мал.2.17 Введення обмеження

Після введення даних, кликнути мишкою по кнопці ОК.

Мал.2.18 Введення обмежень закінчено.

Після заповнення всіх необхідних полів, щоб задача була вирішена, необхідно обрати мишкою кнопку Виконати. Можливі два варіанти завершення Пошуку рішення: рішення буде знайдено і з’явиться вікно Результати пошуку рішення, в якому ми обираємо опцію  Зберегти отримане рішення та обираємо у вікні  Тип звіту опцію Результати, після чого кликнути мишкою по кнопці ОК (мал.2.19)

 

 

 

Мал.2.19 Результати пошуку рішення

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

Мал.2.20 Задача вирішена

Крім того, з метою можливого подальшого аналізу отриманого рішення, додається окремий аркуш з назвою  “Отчет по результатам 1”, зміст якого наступний:

Мал.2.21 Аркуш звіту за результатами Пошуку рішення

2.3.1 Параметри засобу Пошук рішення

 

Максимальний час - служить для обмеження часу, відпущеного на пошук рішення задачі. У цьому полі можна ввести час в секундах, що не перевищує 32 767 (приблизно дев'ять годин); значення 100, використовуване за умовчанням, цілком прийнятно для вирішення більшості простих завдань.

 

Мал.2.22 Параметри засобу Пошук рішення

Граничне число ітерацій - управляє часом рішення задачі шляхом обмеження числа обчислювальних циклів (ітерацій).

Відносна погрішність - визначає точність обчислень. Чим менше значення цього параметра, тим вище точність обчислень.

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

Збіжність - застосовується тільки до нелінійних завдань. Коли відносна зміна значення в цільовому осередку за останні п'ять ітерацій стає менше числа, вказаного в полі Збіжність, пошук припиняється.

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

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

Автоматичне масштабування - використовується, коли числа в змінних осередках і в цільовому осередку істотно розрізняються.

Показувати результати ітерацій - припиняє пошук рішення для перегляду результатів окремих ітерацій.

Завантажити модель - після клацання на цій кнопці відривається однойменне діалогове вікно, в якому можна ввести посилання на діапазон осередків, що містять модель оптимізації.

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

Оцінка лінійна - виберіть цей перемикач для роботи з лінійною моделлю.

Оцінка квадратична - виберіть цей перемикач для роботи з нелінійною моделлю.

Різниці прямі - використовується в більшості завдань, де швидкість зміни обмежень відносно невисока. Збільшує швидкість роботи засобу Пошук рішення.

Різниці центральні - використовується для функцій, що мають розривну похідну. Даний спосіб вимагає більше обчислень, проте його застосування може бути виправданим, якщо видано повідомлення про те, що отримати точніше рішення не вдається.

Метод пошуку Ньютона - вимагає більше пам'яті, але виконує менше ітерацій, чим в методі зв'язаних градієнтів.

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

 

2.4 Консолідація даних

 

Консолідація даних  - це збирання та об'єднання даних з декількох аркушів, таблиць. Ця можливість дозволяє економити час, коли треба переглянути й об'єднати подібні дані, які розміщено в різних документах. Існує кілька способів консолідації.

-       із застосуванням тривимірних формул;

-       за допомогою команди Консолідація.

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

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

Нехай ми маємо дані діяльності магазинів за шість місяців, що мають вигляд:

Мал.2.23 Вхідна таблиця

Відсортуємо кожну таблицю за двома першими стовпцями. Додамо до таблиці аркуш «підсумок1» та скопіюємо вміст перших двох стовпців з довільного аркушу. Додамо до таблиці стовпчик «Виторг» та до комірок внесемо формули (мал.2.24).

Мал.2.24 Перетворення вхідної таблиці

Обидві формули є тривимірними і рівноправними. Скопіюємо формулу до всіх інших комірок та отримаємо консолідовану таблицю для подальшої обробки.

Розглянемо другий спосіб консолідування даних. Для цього створимо  у нашій електронній книзі аркуш «підсумок 2» і проведемо підготовчу роботу, аналогічну попередньому завданню. Виділимо пусту комірку викличемо команду Консолідація з групи Робота з даними вкладки Дані.

Виконаємо відповідні налаштування - вкажемо функцію для підведення підсумків, оберемо діапазон тощо (мал.2.25):

Мал.2.25 Налаштування параметрів консолідації

Крім сумування можна при консолідації використовувати і інші функції (мал.2.26):

Мал.2.26 Вибір функції для консолідації

2.5 Зведені таблиці

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

Зведені таблиці застосовують для таких цілей:

• виконання запитів, пов’язаних із великими обсягами даних, різними зручними способами;
• проміжного підсумування й обчислення сукупного значення числових даних, зведення даних за категоріями та підкатегоріями, виконання додаткових обчислень і створення настроюваних формул;
• розгортання та згортання рівнів даних для фокусування на результатах, а також детальний перегляд лише потрібної інформації зі зведених даних;
• переміщення рядків у стовпці або стовпців у рядки («зведення») для перегляду різних зведень даних джерела;
• фільтрування, сортування, групування й умовного форматування найкорисніших і найцікавіших даних для зосередження уваги на потрібній інформації;
• представлення стислих і ефективних звітів із примітками в Інтернеті або на папері.

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

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

 

 

Мал.2.27 Вхідна таблиця
 

За 2 тижні продажів у вхідній таблиці стало 350 записів. Але ця таблиця не вирішує наших проблем. Нам необхідно дізнатися обсяги продажу у грошовому та кількісному виразі по датах і по окремих магазинах, але як це зробити? Сортувати таблицю і підсумовувати окремі її частини? Це вимагає часу, а завтра надійдуть нові дані, і всю роботу потрібно буде знову повторити. 

Ось тут може допомогти зведена таблиця. Для створення зведеної таблиці відкрийте вкладку Вставка, де в групі Таблицы виберіть команду Сводная таблица. Відкриється наступне діалогове вікно (мал2.28):

 

Мал.2.28 Діалогове вікно створення зведеної таблиці

 

У цьому вікні Excel пропонує нам вказати вихідну таблицю або діапазон значень, на підставі яких буде будуватися зведена таблиця. Якщо виконати команду Зведена таблиця, попередньо встановивши курсор на аркуші, де знаходяться будь-які дані, Excel автоматично заповнить це поле. Якщо ж на аркуші дані відсутні, або вони знаходяться в іншому місці, потрібно буде вказати адресу діапазону даних вручну.

Будьте уважні - перший рядок зазначеного діапазону не повинен бути порожнім - в цьому випадку Excel повідомить про помилку. Також радимо обов'язково створити заголовки для кожного стовпця базової таблиці - це зробить налаштування зведеної таблиці набагато зручнішим.

 Крім вибору вихідної таблиці Excel надає можливість використовувати як джерело даних бази даних і таблиці, створені в інших програмах (Access, SQL Server та інших).

І остання опція, яку потрібно встановити у цьому вікні - вибрати місце розташування зведеної таблиці: у новому вікні або на цьому ж аркуші. В останньому випадку потрібно вказати діапазон адрес, де повинна розташовуватися зведена таблиця.

Натиснувши кнопку ОК після налаштування потрібних нам умов, ми отримуємо наступний робочий аркуш (мал2.29):

 

Мал.2.29 Макет зведеної таблиці

У лівій частині знаходиться область розміщення зведеної таблиці. Праворуч бачимо вікно налаштування зведеної таблиці під назвою  Список полей сводной таблицы. Якщо випадково закрити це вікно, достатньо клікнути по області розміщення - і вікно налаштування знову відкриється.

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

Для цього потрібно виконати наступні дії:

  а) у верхній частині вікна налаштувань відзначаємо всі назви необхідних нам стовпців (мал.2.30):

Мал.2.30 Налаштування макету зведеної таблиці

 

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

  б) Поле Точка продаж перетягуємо в область Фильтр отчета. У цьому випадку Excel додає на робочий лист фільтр, за допомогою якого встановлюємо умову для виведення підсумкових даних. Вибравши в нашому прикладі точку продажу, зможемо виводити підсумки з продажу для окремого магазину (мал.2.31).

 

Мал.2.31 Налаштування макету зведеної таблиці

 

 в) Поле Дата перетягуємо в область Названия строк. Excel використовує значення зі стовпця Дата для того, щоб озаглавити рядки таблиці. Таким чином, будемо підсумовувати потрібні нам поля по кожній даті нашого звіту (мал.2.32).

 

Мал.2.32 Налаштування макету зведеної таблиці

 

г) Поля Сумма по полю Объем продаж, шт. і Сумма по полю Сумма выручки перетягуємо в область Значения. Дані всіх стовпців з цієї області Excel підсумує і відобразить в рядках зведеної таблиці. Налаштування нашої таблиці повинне виглядати ось так (мл.2.33):

 

Мал.2.33 Налаштування макету зведеної таблиці

Результат побудови зведеної таблиці буде мати такий вигляд (мал.2.34):

Мал.2.34 Зведена таблиця

 

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

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

Розглянемо додаткову задачу. Припустимо, потрібно дізнатися дані з обсягів продажу не тільки в розрізі магазинів, але і в розрізі торгових марок, і навіть окремих моделей.

  Для цього досить у вікні налаштування відзначити прапорцями  два додаткові поля - Марка телефона та Модель телефона, і перетягнути ці поля в область Фильтр отчета. Excel додасть до зведеної таблиці два нових фільтра, які допоможуть швидко дізнатися потрібну інформацію (мал.2.35):

 

Мал.2.35 Додавання даних до зведеної таблиці

 

 

2.5.1 Форматування зведеної таблиці

 

При створенні нової зведеної таблиці Excel автоматично іменує її стовпці та заголовки. Однак це легко виправити - досить відредагувати клітинку заголовка стовпця або таблиці. Наприклад, ми перейменували попередній заголовок таблиці:

 
 в більш зрозумілий:

Тепер спробуємо змінити зовнішній вигляд таблиці. Excel пропонує дуже зручний інструмент автоматичного форматування з використанням готових стилів. Для встановлення готового стилю таблиці необхідно клікнути по області розміщення зведеної таблиці - в панелі інструментів відкриються вкладки під загальною назвою Работа со сводными таблицами. Перейдіть на вкладку Конструктор і у групі Стили сводной таблицы виберіть той стиль, який відповідає потребі.  

Крім використання готового стилю, звичайно ж, можна форматувати окремі клітинки, рядки та стовпці зведеної таблиці звичайними засобами форматування Excel.

У групі Параметры стилей сводной таблицы на цій же вкладці можна налаштувати обраний стиль, включивши чергування рядків або чергування стовпців, а також додати або прибрати заголовки рядків і стовпців. Додамо  чергування рядків (мал.2.36):

 

Мал.2.36 Форматування зведеної таблиці

 

Група Макет вкладки Конструктор містить кнопки, які дозволяють налаштувати сам макет нашої таблиці, а саме - Макет отчета, Промежуточные итоги, Общие итоги та Пустые строки.

Команда Макет отчета пропонує три варіанти:

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

 

 

 

 

 

 

 

 

 

 

 

 

Мал.2.37 Макет звіту Показать в сжатой форме

 

2.   Показать в форме стркутуры – базовий стиль зведеної таблиці (мал.2.38)

 

Мал.2.38 Макет звіту Показать в форме стркутуры

 

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

 

Мал.2.39 Макет звіту Показать в табличной форме

 

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

  Промежуточные итоги - тут можна вказати, як потрібно виводити проміжні підсумки: на початку групи, в кінці, чи не виводити взагалі.

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

Команда Пустые строки додає в макет зведеної таблиці додатковий порожній рядок після кожної групи даних. На мал. 2.40  таблиця до включення цієї опції і на мал. 2.41 - після включення опції Пустые строки.

 

 

 

 
 

 

 

 

 

 

 

Мал.2.40 Макет без порожніх рядків

           Мал.2.41 Макет після включення опції

 

2.5.2 Аналіз даних в зведеній таблиці.

 

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

Для цього необхідно клікнути на будь-якому заголовку рядка зведеної таблиці (у нашому прикладі - це поля Дата, Точка продажи та Марка телефона), і у групі Работа со сводными таблицами перейти на вкладку Параметры. На ній необхідно натиснути кнопку Параметры поля в групі Активное поле. У вікні, що відкрилось, першою закладкою буде закладка Промежуточные итоги и фильтры (мал.2.42).

 

Мал.2.42 Діалогове вікно Параметри поля

 

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

На закладці Промежуточные итоги и фильтры можна  вибрати умову для виведення проміжних підсумків. Пропонуються наступні умови:

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

Встановивши автоматичне підведення проміжних підсумків, отримаємо таку таблицю, яка містить проміжні підсумки для кожної умови:

 

 

Мал.2.43 Проміжні підсумки зведеної таблиці

 

Якщо налаштування  проміжних підсумків за допомогою команди Параметры поля, не дає видимих результатів, перевірте налаштування відображення проміжних підсумків за допомогою команди Промежуточные итоги групи Макет вкладки Конструктор.

Припустимо, необхідно вивести проміжні підсумки лише для дат, приховавши проміжні підсумки для точок продажів. Для цього встановіть курсор на будь-яке поле таблиці з назвою магазину і викличте контекстне меню. У ньому потрібно прибрати прапорець з умови Промежуточный итог: точка продажи. Як бачимо, проміжні підсумки залишилися тільки для дат (мал.2.44):

 

Мал.2.44 Проміжні підсумки за датами

 

Часто буває необхідно відсортувати дані зведеної таблиці для кращого їх сприйняття. Для цього досить вибрати поле, по якому потрібно провести сортування, перейти на вкладку Общие, у групі Редактирование натиснути на кнопку Сортировка и фильтр і встановити потрібні умови сортування.

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

Для цього потрібно виділити дати, які входять в перший тиждень (15.05-21.05):

 

Мал.2.45 Виділення даних в зведеній таблиці для групування

 

Зверніть увагу, що для зручності виділення згорнули дані по окремих магазинах, скориставшись кнопкою + в лівій частині клітинки з назвою магазину. Далі потрібно виконати команду Группировка по выделенному групи Группировать вкладки Параметры.

У таблиці з'явиться новий стовпчик, в якому поле Группа1 буде об'єднувати обрані поля.

 

Мал.2.46 Групування по даті

 

Залишиться тільки перейменувати назву групи шляхом простого редагування назви клітинки:

 

 

Мал.2.47 Остаточний результат групування даних зведеної таблиці

 

Для скасування групування достатньо скористатися командою Разгруппировать з цієї ж групи, попередньо обравши поле, яке підлягає розгрупуванню. Зверніть увагу, що неможливо розгрупувати поле, яке включили в умову побудови зведеної таблиці - наприклад поле Точка продажи або Дата.

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

Для цього потрібно виділити будь-яку клітинку у стовпці Виручка нашої зведеної таблиці. Після цього потрібно виконати команду Параметры поля в групі Активное поле вкладки Параметры.

 

 

 

 

 

 

 

 

 

 

 

Мал. 2.48 Діалогове вікно Параметри поля

 

У діалоговому вікні (мал.2.48) необхідно перейти на вкладку Дополнительные вычисления і з випадаючого меню вибрати пункт Доля от суммы по столбцу.

Після натискання кнопки ОК, зведена таблиця буде мати такий вигляд:

 

Мал.2.49 Зведена таблиця із виручкою у відсотках

 

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

 

2.6 Зведена діаграма

 

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

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

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

  1. Виберіть необхідну зведену таблицю, клікнувши по ній.

 

Мал.2.50 Зведена таблиця для побудови зведеної діаграми
 

2. На вкладці Вставка у групі Диаграммы виберіть потрібний тип діаграми.

 

Мал.2.51 Типи діаграм
 

Обираємо простий лінійний графік. В результаті з'явився готовий графік, який містить дані зведеної таблиці, а також вікно Область фильтра сводной таблицы (мал.2.52).

 

Мал.2.52 Побудована зведена діаграма
 

Зверніть увагу, вікно Область фильтра сводной таблицы не дозволяє змінити умови побудови діаграми ,  тобто не можна побудувати графік по стовпцях основної таблиці (наприклад - за стовпцем Объем продаж, шт.), якщо вони не включені у зведену таблицю. І навпаки - включення даних в зведену таблицю одночасно відображається на зведеній діаграмі (мал.2.53).

 

Мал. 2.53 Зведена діаграма із включенням даних
 

Вікно Область фильтра сводной таблицы призначено для зручного управління зведеною таблицею і діаграмою, побудованою на її основі (мал.2.54).

 

Мал.2.54 Вікно Область фільтра зведеної таблиці
 

Змінюючи значення фільтрів і полів вісей, можна відображати необхідний сегмент даних для певних значень вісей координат. Наприклад, дуже зручно аналізувати дані про продажі в окремих магазинах за останній тиждень:

 

Мал.2.55 Зведені діаграми зі різними фільтрами значень
 

За замовчуванням зведена діаграма створюється на тому ж аркуші, де знаходиться зведена таблиця. Це не завжди зручно, тому можна перемістити зведену діаграму на новий аркуш за допомогою команди Переместить диаграмму з контекстного меню. Налаштування формату зведених діаграм проводиться так само, як і звичайних, але з використанням команд з групи вкладок Работа со сводными диаграммами, які відкриваються після кліка по зведеній діаграмі.

Зведена діаграма, побудована на основі існуючої зведеної таблиці, тісно з нею пов'язана. Це не завжди зручно, тому часто має сенс зразу будувати зведену діаграму на підставі базової таблиці. Для цього необхідно:

 1. Виділити потрібний нам діапазон даних (або встановити курсор на потрібну нам таблицю - тоді Excel автоматично підставить всю таблицю в діапазон даних);

  2. На вкладці Вставка у групі Таблицы вибрати розділ Сводная таблица, а потім команду Сводная диаграмма.

 

Мал.2.56 Вкладка Вставка стрічки програми
 

3. У вікні Создать сводную таблицу и сводную диаграмму  задати діапазон або джерело даних,  місце розміщення таблиці і діаграми, натиснути ОК. Excel створить нову зведену таблицю і зведену діаграму.

Залишається тільки налаштувати поля та умови зведеної таблиці за допомогою вікна Список полей сводной таблицы (як розглядалось в попередньому розділі). Всі зміни будуть відображатися і на діаграмі.

 

2.7 Моделювання за допомогою сценаріїв

 

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

Сформуємо вхідну таблицю за зразком (мал.2.57).  Для підрахунків використовуйте вбудовані функції за допомогою Майстра функцій.

Прибуток обчислюється як добуток норми прибутку і загальних витрат, а обсяг продаж – як сума загальних витрат і прибутку. В підсумковому рядку вираховується загальні суми прибутку і обсягу продаж. За умовою задачі клітинки, що мають змінювати значення для прогнозу стану загального обсягу продаж, - це Е3:Е8.

 

 

 

 

 

Мал.2.57 Вхідна таблиця

Управління сценаріями виконується за допомогою Диспетчера сценариев в групі Анализ "что если" на вкладке Данные.

Оберіть  Данные > Анализ "что если" > Диспетчер сценариев > Добавить.  Відкривається діалогове вікно Диспетчер сценариев, у якому треба натиснути кнопку Добавить. У наступному вікні Изменение сценария  задають ім’я для створення сценарію, заносять діапазон клітинок, значення яких підлягають зміні, і натискають кнопку ОК (мал.2.58).

 

Мал.2.58 Діалогове вікно Диспетчеру сценаріїв

Відкривається вікно Значения ячеек сценария (мал.2.59) із набором значень клітинок для сценарію.

Мал.2.59 Набір значень, що змінюються, сценарію варіанта 2

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

Для створення наступного варіанту сценарію в цьому вікні достатньо натиснути кнопку Добавить. Повторно відкривається вікно Значения ячеек сценария (мал.2.60), у яке треба ввести новий набір значень норми прибутку.

 

Мал.2.60 Нові значення, що змінюються, сценарію варіанта 3

 

 Закінчивши будувати всі варіанти сценаріїв,  в останньому вікні Значения ячеек сценария натискаємо кнопку ОК. На екрані з’являється вікно диспетчера сценаріїв (мал.2.61), яке містить імена всіх побудованих варіантів сценаріїв.

Мал.2.61 Всі варіанти сценаріїв

 

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

За даними сценаріїв на окремому робочому аркуші можна створити звіт або зведену таблицю. Для цього натискається кнопка Отчет і у вікні, що з’являється, вибирається тип звіту і натискається ОК. Нижче показаний тип звіту Структура (мал.2.62).

 

Мал.2.62 Звіт Структура за сценаріями

 

Після натиснення кнопки Закрыть вікно Диспетчер сценариев закривається, і електронна таблиця буде містити значення норми прибутку, вибрані у сценарію, який виводився останнім.

 

Висновки

Аналіз "що якщо" — це процес зміни значень в клітинках, який дозволяє побачити, як ці зміни впливають на результати формул на аркуші.

 В Excel пропонуються використовувати засоби аналізу "що якщо"  трьох типів: сценарії, таблиці даних та  підбір параметрів.

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

Окрім цих  трьох засобів  можна  встановити надбудови  для виконання аналізу "що якщо", наприклад, надбудову Пошук рішення. Ця надбудова схожа до Підбору параметра, але дозволяє використовувати більше змінних. Також, можна використовувати інші засоби і можливості, що вбудовані  в Excel.

Для більш складних моделей можна використовувати надбудову Пакет аналізу.

Як і усі програми зі складу пакета MS Office, Microsoft Excel є багатоваріантною системою, що з одного боку має переваги для інтелектуально розвинутого користувача, а з другого - викликає деякі труднощі для користувачів, які націлені на роботу з приклад­ними програмами на рівні оволодіння «порядком виконання дій з конкретним описанням їх послідовності та необхідних для цього клавіш». Сьогодні цього замало. Для успішної роботи необхідно мати уявлення про всі можливі варіанти роботи і уміння свідомо вибирати серед них, в залежності від обставин, найдоступніші та найраціональніші.

Лише усвідомлене ставлення до технології роботи у середовищі табличного процесора  Microsoft  Excel надасть можливість осягнути увесь спектр його унікальних можливостей як універсального засо­бу для рішення широкого спектра задач різного рівня складності.

 

 

 

 

 

 

 

 

 

 

Список використаних джерел

 

1. Ярмуш О. В., Редько М. М.. Інформатика і комп’ютерна техніка: Навч. посібник. – К.: Вища освіта, 2006. – 359 с. – С. 239 – 240.
2. Інформатика: Комп’ютерна техніка. Комп’ютерні технології: Посібник / За редакцією О. І. Пушкаря. – К.: ВЦ «Академія», 2001.– 696 с.
3. Баженов В. А. Інформатика. Комп’ютерна техніка. Комп’ютерні технології: Підручник. – 2-ге видання. – К.: Каравела, 2007.– 640 с. – С. 325 – 333.
4. Дибкова Л.М. Інформатика та комп’ютерна техніка: Посібник. – К.: Видавничий центр «Академія», -2002.-692с.
5. Інформатика та комп'ютерна техніка. Навчальний посібник. / Рогоза М.Є. та ін. За редакцією М.Є. Рогози - К.: ВЦ «Ака­демія», 2006 - 368 с.
6. Семчук А.Р., Юрченко І.В. Економічна інформатика. Навчальний посібник.– Чернівці: МВІЦ "Місто", 2008.– 426 с.
7. Офіційний сайт підтримки програмних продуктів MS Office. [Електронний ресурс] - Режим доступу: https://support.office.com/uk-ua/

 

Додатки

Тести для перевірки знань з теми "Табличний процесор"

Варіант1

1. Яке основне призначення електронних таблиць?

А) Введення текстової інформації;

Б) автоматизація обчислень;

В) створення рисунків;

Г) подання числової інформації в графічному вигляді;

Д) пошук інформації.

2. Із яких елементів складається електронна таблиця?

А) з комірок;

Б) з діаграм;

В) з таблиць;

Г) з файлів;

Д) з аркушів;

Е) з книг.

3. Яким чином переважно позначають рядки і стовпці електронної таблиці?

А) Рядки — арабськими числами, стовпці — латинськими літерами;

Б) стовпці — латинськими літерами, рядки — арабськими числами;

В) рядки — римськими числами, стовпці — латинськими літерами;

Г) стовпці — російськими літерами, рядки — арабськими числами;

Д) рядки — арабськими числами, стовпці — римськими числами;

Е) і рядки і стовпці — числами.

4. Яке ім’я має комірка, що знаходиться на перетині стовпця AZ і рядка 123?

А) AZ123;

Б) 123AZ;

В) AZ–123;

Г) 123–AZ;

  Д) AZ 123.

5. Що може розміщуватися в комірці електронної таблиці?

А) Слово або речення;

Б) число або формула;

В) робоча книга;

Г) робочий аркуш;

Д) діаграма.

6. Якими способами можна завантажити програму MS Excel?

А) На робочому столі двічі клацнути на піктограмі MS Excel;

Б) клацнути на піктограмі документа, створеного програмою MS Excel;

В) Пуск → Усі програми → MS Excel;

Г) Пуск → Усі програми → MS Office → MS Excel.

7. Що необхідно зробити після введення даних у комірку?

А) Натиснути клавішу Tab, щоб перейти в комірку праворуч;

Б) натиснути клавішу Enter, щоб перейти в нижню комірку;

В) натиснути клавішу Enter, щоб перейти в комірку праворуч;

Г) натиснути клавішу Tab, щоб перейти в нижню комірку;

Д) натиснути Ctrl + Enter, щоб залишитися на місці.

8. Під час копіювання вмісту комірки він…

А) на новому місці з’являється, а на старому зникає;

Б) на новому місці з’являється, а на старому залишається;

В) на новому місці з’являється, а решта комірок розсуваються;

Г) на новому місці з’являється, а решта комірок зсуваються;

Д) очищається, решта комірок залишаються.

9. У чому суть операції переміщення вмісту комірок?

А) на новому місці з’являється, а на старому зникає;

Б) на новому місці з’являється, а на старому залишається;

В) на новому місці з’являється, а решта комірок розсуваються;

Г) на новому місці з’являється, а решта комірок зсуваються;

Д) очищається, решта комірок залишаються.

10. Як викликати наявний документ, якщо Excel вже завантажено?

А) у меню Файл клацнути на потрібній назві;

Б) на панелі інструментів виконати команду Відкрити...;

В) у меню Файл виконати команду Відкрити...;

Г) у меню Файл виконати команду Створити...;

Д) на панелі інструментів виконати команду Створити...

11. Для виділення несуміжних блоків комірок необхідно...

А) натиснути клавішу F2;

Б) виділити перший блок та за натиснутої клавіші Shift — другий блок;

В) виділити перший блок та за натиснутої клавіші Ctrl — другий блок;

Г) натиснути комбінацію клавіш Ctrl + F2.

12. Для редагування вмісту комірки можна використати...

А) клавішу F2;

Б) комбінацію клавіш Ctrl + F2;

В) подвійне клацання на комірці;

  Г) поле імені комірки.

Варіант 2

1. Скільки аргументів може мати функція КОРЕНЬ?
   1. один
   2. декілька
   3. три
   4. нефіксовану кількість
2. Яку функцію потрібно вибрати для обрахунку кількості комірок, вміст яких задовольняє певному критерію?
   1. CЧЕТЕСЛИ
   2. ЕСЛИ
   3. СУММ
   4. СУММЕСЛИ
3. Яке значення набуде функція НЕ(В5<20),якщо в комірку В5 занесено число 7?
   1. ЛОЖЬ
   2. 7
   3. 20
   4. ИСТИНА
4. Об'єктом діаграми є:
   1. ряд
   2. діапазон комірок
   3. ім'я комірки
   4. формат числа
5. Як визначається результат функції ЕСЛИ?
   1. результат дорівнює другому аргументу функції у разі справдження логічної умови (перший аргумент), або третьому аргументу в протилежному випадку
   2. однозначний результат заноситься у різні комірки залежно від кількості перевірених умов
   3. результатом обчислень є логічний вираз "истина" або "ложь"
   4. результатом обчислень є 1 або 0
6. Для обрахунку кількості непорожніх комірок, вміст яких задовольняє певному критерію, необхідно скористатись функцією:
   1. СЧЕТЕСЛИ
   2. ЕСЛИ
   3. СУММ
   4. СУММЕСЛИ
7. Перед тим як активізувати команду РАСШИРЕННЫЙ ФИЛЬТР необхідно:
   1. створити критерій умов
   2. виділити таблицю
   3. відсортувати дані
   4. розрахувати суми у рядках і колонках
8. Яка функція не може бути використана під час створення зведеної таблиці?
   1. округлення
   2. сума
   3. кількість значень
   4. кількістьчисел
9. Які складові діапазону умов розширеного фільтру?
   1. команди з іменами полів та логічні умови між ними
   2. комірки з логічною умовою
   3. комірки з формулою порівняння
   4. комірки з іменами полів
10. Для вибору з бази даних значень "Відкрито" та "Прикрито" використовується автофільтр користувача з таким шаблоном:
    1. =???крито
    2. =?крито
    3. =??крито
    4. =крито
11. Діапазон клітинок A1:E1 заповнено першими натуральними числами. Яке значення відобразиться в комірці А6, якщо до неї було введено формулу СУММ(A1:E1)?
    1. 15
    2. 12
    3. 17
    4. повідомлення про помилку
12. Діапазон клітинок A1:E1 заповнено першими натуральними числами у порядку зростання. Яке значення відобразиться в комірці А6 якщо до неї було введено формулу СУММ(A1;E1)?
    1. 6
    2. 7
    3. 3
    4. 15
13. Діапазон клітинок A1:E1 заповнено першими натуральними числами у порядку зростання. Яке значення відобразиться в комірці А6 якщо до неї було введено формулу СУММ(A1:С3;E5)?
    1. 11
    2. 13
    3. 8
    4. 9
14. Діапазон клітинок B1:B6 заповнено першими непарними натуральними числами у порядку зростання. Яке число відобразиться в комірці B6 якщо до неї було введено формулу СРЗНАЧ(В1:В6)?
    1. 5
    2. 6
    3. 4
    4. 7
15. Діапазон клітинок A1:E1 заповнено першими натуральними числами у порядку зростання. Яке значення відобразиться в комірці А6 якщо до неї було введено формулу СРЗНАЧ(B1;B3:B5)?
    1. 5,5
    2. 5
    3. 6
    4. 7
16. Діапазон клітинок A1:E1 заповнено першими натуральними числами у порядку зростання. Яке значення відобразиться в комірці А6 якщо до неї було введено формулу СРЗНАЧ(B2;B4;11)?
    1. 7
    2. 9
    3. 5
    4. 6,5
17. Діапазон клітинок A1:B3 заповнено порядково першими парними натуральними числами у порядку зростання. Яке число відобразиться в комірці С4 якщо до неї було введено формулу МАКС(A1:B3)?
    1. 12
    2. 10
    3. 8
    4. 14
18. Діапазон клітинок A1:B3 заповнено порядково першими парними натуральними числами у порядку зростання. Яке число відобразиться в комірці С4 якщо до неї було введено формулу МИН(A1:B2)+СУММ(A2:B3)?
    1. 38
    2. 30
    3. 16
    4. 14
19. Діапазон клітинок A1:B3 заповнено порядково першими парними натуральними числами у порядку зростання. Яке число відобразиться в комірці С4 якщо до неї було введено формулу CРЗНАЧ(СУММ(A1:B2);B3;4)?
    1. 12
    2. 5
    3. 6
    4. 8
20. Діапазон клітинок A1:B2 заповнено порядково першими натуральними числами, кратними трьом, у порядку зростання. Яка з наведених формул не могла бути введеною до цієї комірки?
    1. =МАХ(A1:B2)+MIN(A1:B2) - 2
    2. =CУММ(A1;B2)
    3. =CУММ(A2;B1)
    4. =СРЗНАЧ(A1:B2)/2
21. В формулі містяться посилання на комірку А$1. Чи зміниться дане посилання при копіюванні формули в комірки, розташовані нижче?
    1. Ні
    2. Так
22. Як зробити так, щоб посилання на комірку А1 було абсолютним?
    1. З клавіатури ввести $А$1
    2. Після введення адреси комірки в формулу натиснути клавішу F2
    3. Після введення адреси комірки в формулу натиснути клавішу F4
    4. З клавіатури ввести `A`!`1`
23. Який символ можна використовувати в формулах в якості знаку піднесення в степінь?
    1. ^
    2. ~
    3. ((
    4. **
24. Вибрати коректне задання діапазону комірок:
    1. A5:В5
    2. 5MM:MM9
    3. CC6:6C
25. Яка з формул містить абсолютне посилання
    1. F45/$H$12
    2. G$4 + J6
    3. R74*E63
26. У комірку введено число 0,70 і застосовано процентний формат. Який результат буде відображено у комірці?
    1. 70%
    2. 7000%
    3. 0,7%
    4. 0,07%
27. У комірки A1 і B2 введені числа 24 і 12 відповідно. У комірку C1 введено: A1/В1. Що буде відображено у комірці С1?
    1. A1/B1
    2. 2
    3. 0,5
    4. 24/12
28. У формулу потрібно ввести посилання на комірку. Що для цього потрібно зробити?
    1. Клацнути по потрібній комірці лівою кнопкою миші
    2. Натиснути клавішу F4
    3. В рядку формул викликати контексне меню
    4. Натиснути клавішу F3
29. За допомогою якої функціональної клавіші в Microsoft Excel можна редагувати і вводити дані в активну комірку?
    1. F2
    2. F4
    3. F5
30. Чи зміниться діаграма, якщо внести зміни до даних таблиці, на основі яких вона створена?
    1. Так
    2. Ні
31. Використання маркера заповнення дозволяє розмножувати в комірки:
    1. Всі відповіді правильні
    2. Дані
    3. Формати
    4. Функції
32. Чи можна змінювати параметри після побудови діаграми:
    1. Можна змінити тип діаграми, вихідні дані, ряди побудови та всі інші параметри
    2. Лише розмір і розміщення
    3. Можна змінити всі параметри, крім типу
    4. Ні, треба будувати діаграму знову
33. Діаграми MS Excel — це інструмент, призначений для:
    1. Графічного подання даних вихідної таблиці
    2. Відображення на екрані записів таблиці, значення яких відповідають умовам, які задані користувачем
    3. Здійснення обчислень
    4. Розміщення даних вихідної таблиці в більш зручному для користувача місці
34. Команди форматування в табличному процесорі виконують функції:
    1. Вирівнювання даних в комірках, вибір шрифтів, товщини ліній та інше
    2. Збереження, завантаження файлів книг
    3. Пошуку та заміни
    4. Переміщення, вставки, видалення, заміни
35. Вкажіть правильну адресу комірки в програмі MS Excel:
    1. B1
    2. #A10
    3. "A10000
    4. BZ_99
36. Які параметри форматування можна застосувати до комірок:
    1. Всі варінти правильні
    2. Окреслення меж та заливка кольором
    3. Вирівнювання тексту, висота та ширина
    4. Тип даних
37. Якщо подвійно клацнути по заповненій комірці в програмі MS Excel, активується режим
    1. Редагування даних
    2. Введення даних, якщо це текст
    3. Копіювання вмісту комірки
    4. Введення даних, якщо це формула
38. За правим краєм у комірці автоматично вирівнюють після введення:
    1. Текст
    2. Числа
    3. Формули
    4. Текст і числа
39. Особливими ознаками вікна програми табличного процесора Excel є:
    1. Рядок формул
    2. Рядок заголовку вікна
    3. Вертикальні та горизонтальні смуги прокручування
    4. Рядок стану
40. Що можна видалити за допомогою пунктів меню Правка-Очистити:
    1. Всі варіанти правильні
    2. Формати
    3. Вміст
    4. Формати та вміст
41. Чи можна при написанні формул використовувати значення, що знаходяться на різних листах?
    1. Можна, при цьому потрібно вказати номер аркуша, де розташовано комірку
    2. Неможна, бо можна отримати конфлікт адрес у комірках
    3. Можна, якщо адреси комірок на різних аркушах збігаються
    4. Можна, якщо дати на аркушах однакові величини в комірках
42. Як виконати автоматичне додавання даних у комірках таблиці?
    1. Зробити активною комірку, в якій буде знаходитися результат, натиснути кнопку «Автосума» і виділити потрібні комірки
    2. Натиснути кнопку «Автосума», виділити потрібні комірки в таблиці і додатково натиснути кнопку «Автосума»
    3. Зробить активною комірку, в якій буде розміщено результат, натиснути кнопку =, натиснути кнопку «Автосума» і виділити
    4. Натиснути на кнопку «Автосума», виділити потрібні комірки таблиці і натиснути =
43. Яке значення буде відображене в комірці E2 програми Excel
     
    1. 7
    2. 4
    3. 5
    4. 3
44. Яке значення буде відображене в комірці E2 програми Excel
     
    1. 9
    2. 8
    3. 4
    4. 0
45. Таблиця Excel містить такі дані
    
    Визначте, які функції можна застосувати для отримання такого результату, який міститься в комірці E2(вкажіть три правильні відповіді)
    1. СУММ(А1:D1)
    2. ПРОИЗВЕД(А2;E1)
    3. КОРЕНЬ(D1)*А2
    4. ОСТАТ(D2; D1)* E1
46. Щоб знайти значення функції y=|sinx-cosx| в точці a, що належить вказаному відрізку слід записати формулу
    1. ABS(sin(a)-cos(a))
    2. ABC(sin(a)-cos(a))
    3. ABS(sin(a))-ABS(cos(a))
    4. sin(a)-cos(a)
47. Чи будуть відрізнятися значення функції СУММ(A1;A4) і СУММ(A1:A4) і якщо відрізняються, то чим
    1. Відрізняються. Це можна записати так: СУММ(A1;A4)= A1+A4 , а СУММ(A1:A4)= A1+ A2+A3+A4
    2. Це одне і те ж
    3. Відрізняються. Це можна записати так: СУММ(A1;A4)= A1*A4 , а СУММ(A1:A4)= A1* A2+A3*A4
    4. Відрізняються. Це можна записати так: СУММ(A1;A4)= A1*A4 , а СУММ(A1:A4)= A1* A2*A3*A4
48. Яке значення буде відображене в комірці E2 програми Excel
     
    1. 67
    2. 66
    3. 68
    4. 54
49. Яке значення буде відображене в комірці E2 програми Excel
     
    1. 36
    2. 34
    3. 32
    4. 0
50. Яке значення буде відображене в комірці E2 програми Excel
     
    1. ИСТИНА
    2. ЛОЖЬ
    3. Порожня комірка
    4. 0
51. Яка умова має структуру: Назва поля, Операція порівняння, значення?
    1. Проста логічна умова
    2. Складна логічна умова зі сполучником AND
    3. Складна логічна умова зі сполучником OR
    4. Логічна умова зі сполучником NOT
52. Яка з цих формул є умовою відбору записів, що містять прізвище учня, яке починається на Сол, і оцінку з інформатики >5?
    1. Сол*AND>5
    2. С*OR>=5
    3. Сол*OR>5
    4. СолAND>5
53. Яка з цих формул є умовою відбору записів, що містять прізвище учня, яке починається на літеру Б, або оцінку з інформатики, більшу чи рівну 9?
    1. Б*OR>=9
    2. Б*AND>=9
    3. Б*AND>9
    4. Б*OR>9
54. Чим зв'язані між собою прості умови у складному логічному виразі?
    1. Логічними операціями AND/OR
    2. Операціями порівняння
    3. Одиночними символами *,?
    4. Знаками арифметичних операцій
55. Які логічні оператори використовують під час фільтрації в табличному процесорі?
    1. AND, OR
    2. AND, IF
    3. AND, NOT
    4. IF, OR
56. Графік відображає:
    1. тенденції зміни даних за однакові проміжки часу
    2. зміну даних за певний проміжок часу
    3. співвідношення окремих значень даних
    4. зіставлення даних у часі
57. Кругова діаграма відображає:
    1. величину як частину цілого
    2. зміну даних за певний проміжок часу
    3. співвідношення окремих значень даних
    4. зіставлення даних за величиною
58. Викликати Майстер діаграм можна:
    1. за допомогою вказівки Вставка/Диаграмма
    2. натиснувши Ctrl+Alt+Del
    3. за допомогою вказівки Сервис/Диаграмма
    4. натиснувши Ctrl+D
59. Маркер даних це:
    1. смуга, область, точка, сегмент, що відповідає одному значенню, одній комірці аркуша
    2. олівець для відображення даних
    3. елемент легенди
    4. перелік значень
60. Які з перелічених функцій належать до математичних?
    1. sin(x), cos(x), sqrt(x), exp(x)
    2. МИН, МАКС, СРЗНАЧ, СЧЕТЕСЛИ
    3. ЗАМЕНИТЬ, ПОИСК, СЦЕПИТЬ, ТЕКСТ
    4. FРАСП, ДИСП,КОРРЕЛ, ЛИНЕЙН
61. На перетинані рядка і стовпчмка розташована:
    1. комірка
    2. віконце
    3. клітина
    4. кишеня
62. Рядок, у якому відображено вміст комірок або введені формули, розташовано у верхній частині вікна. Його називають:
    1. рядком формул
    2. рядком меню
    3. областю задач
    4. панеллю інструментів
63. Яка функція обчислює мінімальне значення серед діапазону комірок
    1. МИН
    2. МАКС
    3. ABS
    4. exp(x)
64. Який шрифт та його розмір в Excel беруть за замовчуванням?
    1. Arial, 10
    2. Arial, 12
    3. Times New Roman, 12
    4. Times New Roman, 10
65. Яка функція обчислює максимальне значення серед діапазону комірок
    1. МАКС
    2. МИН
    3. ABS
    4. exp(x)
66. Яка функція обчислює середнє значення серед діапазону комірок
    1. СРЗНАЧ
    2. СЧЕТЕСЛИ
    3. ABS
    4. exp(x)
67. 1, 2, 3, 4, 5, … — це назви:
    1. рядків
    2. аркушів
    3. стовпців
    4. формул
68. A, B, C, D, E, F, … — це назви:
    1. стовпців
    2. аркушів
    3. рядків
    4. формул
69. Команда Диаграмма знаходиться у меню:
    1. вставка
    2. файл
    3. правка
    4. сервис
70. На підставі яких даних будується діаграма?
    1. даних таблиці
    2. даних графічного файлу
    3. даних текстового файлу
71. Мінімальна складова таблиці — це :
    1. комірка
    2. формула
    3. книга
    4. немає вірної відповіді
72. Впорядкуванням значень діапазону комірок називають:
    1. сортуванням
    2. групуванням
    3. фільтруванням
    4. форматуванням
73. Яке форматування можна застосувати до комірки MS Excel?
    1. всі перелічені варіанти
    2. тип даних,ширина, висота
    3. вирівнювання тексту і формат шрифта
    4. обрамлення і заливання
74. Що відбувається, коли у формулі виявлено помилку?
    1. виводиться повідомлення про тип помилки
    2. повертається "0" як значення комірки
    3. виправляється помилка в формулі
    4. видаляється формула з помилкою
75. Як у Excel записати формулу підсумовування діапазону комірок від А1 до А15?:
    1. СУММ (А1:А15)
    2. СУММ (А1-А15)
    3. СУММ (А1;А15)
    4. СУММ (А1+А15)
76. Яким чином позначити комірку А1 у формулі А1+А1/2, якщо її адреса повина залишатися сталою при копіюванні в будь-яку частину таблиці?
    1. $A$1 + $A$1/2
    2. $A1 + $A1/2
    3. A$1 + A$1/2
    4. $(A1 + A1/2)
77. Контексне меню об’єкта містить в собі:
    1. всі команди стосовно цього об’єкта
    2. команди “вирізати”, “копіювати”, “видалити”
    3. команди , які використовуються найчастіше
    4. команди форматування таблиці
78. Чи можна змінювати діапазон даних діаграми, якщо вона вже побудована?:
    1. так
    2. залежить від типу діаграми
    3. ні
79. В документі є 2 аркуші, що називаються “Рахунок” та “Список”. Яку формулу потрібно ввести, щоб на аркуші “Рахунок” у комірці А1 відобразити 20% від вмісту комірки С4 із аркуша “Список”.
    1. =20%*список!C4
    2. =20%*рахунок!C4
    3. =20*список!C4

 

Завдання для практичного самостійного виконання

 

Задача 1.

Побудувати таблицю, провести необхідні розрахунки. Застосувати до таблиці

Автоформатування. Побудувати довільну гістограму для ілюстрації планової

собівартості продукції.

 

Задача 2

1. Створити таблицю та ввести дані за зразком. Для першого рядка таблиці встановити вертикальну орієнтацію тексту.

2. Занести до комірок результати змагань за допомогою математичної функції СЛЧИС (). Помножити функцію на 1000, щоб кількість балів була в межах від 0 до 1000. Встановити формат Числовой, 0 дес. знаків. Занести до комірок суми балів кожного спортсмена за допомогою математичної функції СУММ.

3. Занести до комірок середню суму балів за допомогою статистичної функції СРЗНАЧ. 

4. Обчислити різницю балів лідера й аутсайдера. Вибрати серед статистичних функцій функції МАКС і  МИН. Поставити у формулі знак «—».

5. Відформатувати таблицю.

6. Побудувати діаграму за підсумками змагань і розмістити її під таблицею

 

 

 

 

Задача 3.

Створити таблицю за зразком.

Кожне завдання виконувати на окремому аркуші, копіюючи вхідну таблицю:

1.  Відсортувати інформацію по полю П.І.Б. Аркуш назвати Сортування, колір

ярличка – синій.

2. Встановити автофільтр по полю Стаж роботи. Аркуш назвати Автофільтр,

колір ярличка – жовтий.

3. Підрахувати Проміжні підсумки (суму) по полям Нараховано, Інші види

компенсацій, До видачі при кожній зміні у полі Стаж роботи. Аркуш назвати

Підсумки, колір ярличка – зелений.

4. Побудувати зведену таблицю. У області «Дані» використовувати поля: Нараховано і До видачі (сума). Вибір полів в областях: «Стовпець» – поле Посада, «Рядок» – поле Прізвище, «Сторінка» – поле Стать. Аркуш назвати Зведена таблиця, колір ярличка – фіолетовий.

5. Побудувати зведену діаграму.

 

Задача 4.

1.Створити таблицю «Нарахування заробітної плати викладачам коледжу» (мінімум 5 вчителів, дані вносити на свій розсуд) такого типу:

Назва: «Заробітна плата вчителів школи»

	A	B	C	D	E	F	G	H	I	J	K
1	№ з/п	Прізвище, ім’я, по батькові  викладача	Стаж (к-ть років роботи в коледжі)	Наванта-ження (ставка), к-сть годин за семестр	Ставка, грн.	Оплата, за повну ставку грн	Інші нарахування (Премія, доплата за зошити, класне керівництво, відрядження) грн.	Пенсій- ний фонд, %	Інші вира-ховування, %	Нараху-вання, грн.	До видачі, грн.
2								15%	12%

Примітка: Повна ставка – це 720 год. за семестр. Плата за ставку від 700 (неповна ставка) до 850 грн.

В таблиці повинні бути вчителі, які мають повну ставку, неповну ставку, і більше ставки.

В стовпчиках F(комірка F2) потрібно ввести формули, наприклад: =D2∙ E2:720

В стовпчиках J(комірка J2) потрібно ввести формули, наприклад: =F2+G2+C2*3,3

В стовпчиках K( комірка K2)  потрібно ввести формули, наприклад: =J2–J2∙15:100– J2∙12:100.

 

Задача 5.

Створити новий документ - електронну книгу.

1.  Оформити Довідник посад, що містить оклади. Загальна кількість робітничих днів є константою для кожного місяця.

К-сть роб. днів

19

Довідник посад.

 

 

Довідник посад	Бухгалтер	Інженер	Начальник	Програміст
Оклад	180	140	240	220

2. На тому ж самому аркуші оформити Відомість заробітної плати по відділу за січень.

Відомість заробітної плати

Січень

Прізвища	Посада	Відпрац. дні	Начислено	Премія	Сума	Прибутковий податок	Пенсійний фонд	Сума до видачі
Іващенко	Начальник	19
Сидорук	Бухгалтер	18
Коваленко	Програміст	17
Гаврилов	Програміст	19
Денисенко	Інженер	16
Петренко	Інженер	10
Давидов	Інженер	19
Карпенко	Інженер	10
Симоненко	Інженер	18
Всього:

3.Підрахувати суму начеслених грошей за відпрацьовану кількість днів по формулі:
«Начислено = (Оклад/Загальна кіл-ть днів)*Відпрац. дні»

•        Оклад слідує одержувати з таблиці Довідник посад, застосувавши функцію ПРОСМОТР, де розшукуване значення - це посада кожного з робітників, а масив – це діапазон чарунок, в яких розміщена таблиця Довідник посад (слідкуйте за однаковістю в назві текстових полів).

•        Премія дорівнює 20% від налічених грошей.

•        Загальна сума грошей обчислюється по формулі: «Сума=Начислене+Премія»

4.Підрахувати Прибутковий податок, який розраховується з наступних умов:
        Якщо Сума < 100, то прибутковий податок дорівнює 0.

Якщо Сума < 200, то прибутковий податок дорівнює 10% від Суми.

Якщо Сума >=200, то прибутковий податок дорівнює 20% від Суми. (Використати вкладені функції ЕСЛИ).

5.Підрахувати Пенсійний фонд, який дорівнює 2% від Суми.

6. Підрахувати Суму до видавання, яка розраховується по формулі:
   «Сума до видачі=Сума - Прибутковий податок - Пенсійний фонд»
7. Підрахувати загальну суму по полю Сума до видачі.
8. Скласти аналогічну відомість для лютого. Для цього на Аркуш 2 скопіювати таблицю Довідник посад, змінити в ній дані по полю Кількість відпрацьованих днів: для лютого - 17, та скопіювати таблицю Відомість заробітної плати, де в полі Відпрацьовані дні внести зміни згідно табеля. Простежити за змінами, які відбуваються автоматично при переобчисленні формул.

Табель Відпрацьованих днів за місяць лютий:

Прізвища	Відпрац. дні	Прізвища	Відпрац. дні
Іващенко	15	Петренко	14
Сидорук	16	Давидов	17
Коваленко	17	Карпенко	10
Гаврилов	17	Симоненко	12
Денисенко	16

9.  Скласти на АркушіЗ аналогічну відомість для березня. Кількість відпрацьованих днів: для березня — 21. Табель Відпрацьованих днів за місяць березень:

Прізвища	Відпрац. дні	Прізвища	Відпрац. дні
Іващенко	20	Петренко	18
Сидорук	16	Давидов	17
Коваленко	21	Карпенко	21
Гаврилов	17	Симоненко	20
Денисенко	19

10. Оформити на Аркуші4 загальну відомість за квартал. Загальна відомість за квартал

Прізвища	Посада	Сума	Прізвища	Посада	Сума
Іващенко	Начальник	Петренко	Інженер
Сидорук	Бухгалтер	Давидов	Інженер
Коваленко	Програміст	Карпенко	Інженер
Гаврилов	Програміст	Симоненко	Інженер
Денисенко	Інженер

11.                Підрахувати суму грошей, отриманих кожним робітником за квартал. Сума дорівнює сумі грошей, отриманих за кожний місяць ( у формулі для обчислення суми повинні бути посилання на чарунки Листа1, Листа2, Листа3, де знаходяться суми, які були отримані за кожен місяць).
12.                Зробити Автофильтр (автоматичний фільтр) в таблиці Відомість заробітної плати за березень. Показати всіх робітників, які отримали суму грошей > 150.
13.                Зняти автофільтр.
14.                Побудувати Розширений фільт.
    Основною базою є таблиця Відомість заробітної плати за березень. Спочатку результат фільтрації слід розмістити на місці базової таблиці (у діалоговому вікні Розширеного фільтру включити перемикач Фильтровать список на месте). У вікні Диапазон условий вказати діапазон чарунок, де знаходиться наступна таблиця:

Діапазон умов -

Посада	Відпрац. дні
Інженер	>=18

15. Побудувати аналогічний Розширений фільтр по тій же самій умові фільтрації, тільки результат фільтрації слід розмістити на вільному місці, тобто на чарунках, де знаходиться цільова область. Для цього у діалоговому вікні Розширеного фільтру треба включити перемикач Скопировать результат в другое место. У вікні Поместить результат в диапазон вказати діапазон чарунок, де знаходиться наступна таблиця:

Прізвища

Начислено

Сума до видання

Цільова область -

 

 

 

Коректор: Василенко В.О.

03040 Київ-40, вул. Васильківська,20

                                           

Наклад    прим.          Замовлення №