Уведення формули 2 страница

5. На третьому кроці на вкладці Заголовки введіть назву для діаграми Аналіз товарообігу в розрізі товарних груп за базовий період. Натисніть кнопку Далее.

6. На четвертому кроці виберіть перемикач , натисніть копку ОК.

УВАГА!!! Створену діаграму можна у будь-який момент змінити та перемістити в іншу область листа, якщо діаграма створена не на окремому листі.

Існує можливість також виділити дані, для побудови діаграми на окремому робочому листі, натиснувши клавішу < F11 >. Excel додасть у робочу книгу лист діаграм і створить діаграму на основі формату, що використовується за умовчанням.

Методичні вказівки.

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

1. Виділіть у робочому листі дані для побудови діаграми, чарунки В5:В13 (данні цих чарунок являються категорією), а також С5:С13 і Е5:Е13 (несуміжні діапазони виділяти утримуючи клавішу CTRL).

2. Клацніть на кнопці — «Мастер диаграмм»на панелі інструментів «Стандартная» або виконайте команду «Вставка-Диаграмма…».

3. Виберіть потрібний вигляд діаграми (поля «Тип» та «Вид»), натисніть кнопку Далее.

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

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

6. На четвертому кроці виберіть перемикач , кнопка ОК.

6.4. Аналіз даних за допомогою зведених таблиць.

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

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

6.5. Приклад завдання «Аналіз даних за допомогою зведених таблиць»

Завдання 1. Підготуйте початкові дані для побудови зведеної таблиці, або завантажте готовий файл общие_сводные.xls. Створіть зведену таблицю об'єму продажу по товарам і регіонам (використовуючи майстер зведених таблиць).

У таблиці приведені дані про об'єми продажу телевізорів різних виробників по регіонам за три місяці 2005 року.

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

Методичні вказівки.

1. Виберіть пункт меню Данные – Сводная таблица…. Буде запущений майстер.

2. На першому кроці запрошуються початкові дані для зведеної таблиці - виберіть перемикач в списке или базе данных Microsoft Excel і натисніть кнопку Далее>.

3. На другому кроці виділіть діапазон чарунок A2:D29. Натисніть Далее>.

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

4. На третьому кроці, безпосередньо створюється зведена таблиця - на макет, показаний на рис. 6.1, перетягуємо заголовки таблиці в потрібні області. ОК.

Рисунок 6.1. Макет для створення зведених таблиць

Увага!!! Якщо необхідно вибрати операцію для поля, яке обчислюється клацніть на ньому двічі. Відкриється діалогове вікно Вычисление поля сводной таблицы. Виберіть потрібну операцію (рис.6.2).

Рисунок 6.2. Діалогове вікно Вычисление поля сводной таблицы.

Увага!!! Можна перетягувати кнопки (назв стовпців) як на макет, так і з макету на Панель инструментов Сводная таблица. Таким чином, зведена таблиця завжди може бути реорганізована. Поля із списками в зведеній таблиці дозволяють одержувати зрізи даних.

4. На третьому кроці вибираємо місце розташування таблиці.

Зведена таблиця може мати вигляд, як на рис. 6.3. Як видно, в ній стовпець Об'єм продажу підрахований і узагальнений по стовпцях Регіон і Товар по всім місяцях. Поля Період, Регіони і Товари є списки, які дозволяють вибирати і проглядати зрізи даних по деяких критеріях. У полі Сума по полю Об'єм продажу можна задавати інші операції.

Рисунок 6.3. Зведена таблиця по товарах і регіонах.

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

Завдання 2. Проаналізуйте зрізи даних по місяцях, розкривши поле із списком Період. Розгляньте основні кнопки: панелі інструментів Сводная таблица.

Увага!!! Якщо ви випадково закрили Панель інструментів Сводная таблица, то для її відображення клацніть правою кнопкою миші по Панелі інструментів Excel і в списку, що розкрився, виберіть еп1ёяґпотрібну панель.

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

- Формат отчета дозволяє додати зведеній таблиці "презентабельний" для друку вигляд;

- Мастер диаграмм дозволяє автоматично побудувати діаграму по зведеній таблиці;

- Отобразить детали дозволяє деталізувати зведену таблицю по деякому параметру. Наприклад, виділіть поле Товар, натисніть кнопку, далі в окремому вікні Показати деталі виберіть Період і натисніть ОК - товари будуть деталізовані по всіх періодах;

- Отключить детали відміняє деталізацію;

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

- Параметры поля дозволяє встановити узагальнену операцію по вибраному полю. Наприклад, виділіть на зведеній таблиці поле Сума по полю Об'єм продажів і клацніть по кнопці. У вікні, що з'явилося, замість операції Сума задайте операцію Мінімум (або іншу). Натисніть ОК, проаналізуйте результат, а потім відмініть результат кнопкою.

Завдання 3. Д ля одержаної зведеної таблиці побудуйте об’ємну діаграму.

Методичні вказівки.

1. Клацніть по зведеній таблиці для її виділення. На панелі інструментів Сводная таблица натисніть кнопку Мастер диаграмм. Буде побудована гістограма з накопиченням.

2. Змініть тип гістограми: ще раз натисніть кнопку Мастер диаграмм (можна клацнути правою кнопкою миші по самій гістограмі і із списку, що з'явився, вибрати Тип диаграммы, але тоді гістограма буде побудована на окремому листі). У вікні, що з'явилося, виберіть вигляд - об’ємна гістограма і натисніть Далее.

3. У наступному вікні Мастер диаграмм натисніть Далее.

4. У наступному, останньому вікні вкажіть, куди ви хочете помістити діаграму: виберіть перемикач окремому. Натисніть кнопку Готово. Гістограма буде перетворена в об’ємний вигляд, як на рис. 6.4. Списки на діаграмі дозволяють вибирати і проглядати зрізи даних так само, як на зведеній таблиці.

Рисунок 6.4. Гістограма аналізу продажу товарів по зведеній таблиці.

6.6. Використання стандартних функцій

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

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

- Без аргументу.

- З одним аргументом.

- З фіксованою кількістю аргументів.

- З невизначеною кількістю аргументів.

- З необов'язковими документами.

Звертання до функції завжди складається з імені функції, круглих дужок і, як правило, аргументів функції. Навіть якщо для функції не потрібно вказувати аргументи, круглі дужки все рівно повинні бути присутніми. Наприклад, функція СЕГОДНЯ() повертає значення поточної дати, а функція ТДАТА() — поточну дату та час у числовому форматі. Аргументи функції, що знаходяться у круглих дужках, розділяються крапкою з комою (;).

Функцію можна ввести звичайним шляхом, як і будь-який вміст чарунки, з клавіатури. Регістр (Shift) при уведенні функції не враховується. Ім'я функції автоматично буде записане великими літерами. Але звичайно набагато зручніше для вставки функції скористатися майстром функцій.

Поряд із вбудованими в Excel функціями можна створювати також власні функції користувача. Деякі функції, наприклад, інженерні, доступні тільки після встановлення прапорця у пункті «Пакет анализа» у вікні «Надстройки», що викликається за допомогою команди «Сервис\Надстройки…».

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

Для вставки функції за допомогою майстра функцій:

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

- Виконайте команду «Вставка\Функция…», або скористайтеся кнопкою —«Вставка функции», або натисніть комбінацію клавіш < Shift + F3 >. На екран буде виведене перше вікно майстра функцій.

- У списку «Категория» виберіть потрібну категорію функцій.

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

- Натисніть кнопку «ОК». У другому вікні майстра функцій варто зазначити необхідні аргументи для функції.

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

- Натисніть кнопку «ОК». Excel вставить функцію у поточну чарунку. У самій чарунці буде відображений результат обчислення функції.

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

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

За допомогою майстра функцій можна також вкласти функції одна в одну, тобто використовувати як аргумент даної функції іншу функцію. Для цього при введенні аргументу зовнішньої функції клацніть на кнопці — «Изменить формулу» у рядку формул, а потім на кнопці — «Вставка функции» для запуску майстра функцій. У такий спосіб можна записати формулу, що містить до семи рівнів вкладеності функцій.

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

Для уведення функції, що дозволяє швидко визначити суму вмісту діапазону чарунок, є спеціальна кнопка — «Автосумма» на панелі інструментів «Стандартная». Excel вставить функцію суми та запропонує діапазон чарунок, відмічений у робочому листі біжучою рамочкою. При необхідності, змініть запропонований діапазон, перетягуючи покажчик миші при натиснутій лівій кнопці. Завершити уведення формули можна натисканням клавіші < Enter >.

Логічні функції

Функція ЕСЛИ() дає можливість організувати розгалуження залежно від істинності деякої логічної умови.

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

Приклад

=Если(D3>=0; E3*0,4; E3*0,1)

Якщо вміст чарунки D3 більший або дорівнює «0», то для розрахунку використовується вираження E3*0,4. В іншому випадку (коли вміст чарунки менший «0») розрахунок виконується за вираженням E3*0,1.

Функція И() дає можливість об'єднати кілька логічних умов. Аргументами функції И() є від двох до тридцяти логічних умов. Функція И() повертає значення “Істина” лише тоді, коли кожний її аргумент має значення “Істина”.

Приклад

= И(В5=0;С5>1)

Функція ИЛИ() використовується для об'єднання декількох логічних умов. Аргументами функції ИЛИ() є від двох до тридцяти логічних умов. Функція ИЛИ() повертає значення “Істина” тоді, коли хоча б один її аргумент має значення “Істина”.

Приклад

= ИЛИ(В5=0;С5>1)

Математичні функції

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

Приклад

= СУММ(В3:В10)

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

Приклад

= СУММЕСЛИ(С4:С12;>0)

Статистичні функції

Функція СЧЕТЕСЛИ() використовується для підрахунку кількості чарунок, що задовольняють заданому критерію усередині інтервалу. Першим аргументом функції є інтервал, у якому відбувається підрахунок. Другим аргументом є критерій у вигляді числа, вираження або текстового рядка, що визначає, які чарунки враховуються.

Приклад

= СЧЕТЕСЛИ(С4:С12;=0)

Функція СРЗАЧ() використовується для визначення середнього арифметичного значення аргументів. Аргументами функції є числа, масиви або адресні посилання на діапазон чарунок. Функція дозволяє задавати від 1 до 30 аргументів.

Приклад

= СРЗНАЧ(В4:В10)

Функція МАКС() використовується для визначення найбільшого значення із списку аргументів. Аргументами є від 1 до 30 чисел. Можна задавати аргументи, які є числами, порожніми чарунками, логічними значеннями.

Приклад

= МАКС(В4:В10)

Функція МИН() використовується для визначення найменшого значення із списку аргументів. Аргументами є від 1 до 30 чисел; ігноруються значення помилки або текст, що не може бути перетворений у числа.

Приклад

= МИН(В4:В10)

6.7. Приклад завдання «Використання стандартних функцій»

Задача №1. Розрахувати суму знижки товару. Якщо кількість придбаного товару більш 3 шт., то сума знижки складає 10 %.

Методичні вказівки.

1. Розрахувати Суму придбаного товару:

1.1. в чарунці D3 створити формулу: =В3*С3;

1.2. використовуючи маркер заповнення скопіювати формулу до кінця обчислень;

2. Розрахувати Суму знижки придбаного товару:

2.1. в чарунці Е3 створити формулу: =Если(В3>3;D3*0,1;0)

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

3. Розрахувати Суму з урахуванням знижки:

3.1. в чарунці F3 створити формулу: = D3-E3;

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

Задача №2. Розрахувати суму знижки товару. Якщо кількість придбаного товару більше 3 шт., то сума знижки складає 10 %. Якщо кількість придбаного товару більше 10 шт., то сума знижки - 15%.

Методичні вказівки.

1. Розрахувати Суму знижки придбаного товару:

1.1. в чарунці Е3 створити формулу: =Если(В3<=3;0;Если(В3>10;D3*0,15;D3*0,1))

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

Задача №3. Розрахувати суму знижки товару. Якщо покупець придбав більше 3 дверей, то йому надається знижка у розмірі 5%. Якщо покупець придбав більше 4 вікон, йому надається знижка у розмірі 10%.

Методичні вказівки.

1. Розрахувати Суму знижки придбаного товару:

1.1. в чарунці Е3 створити формулу:

=Если(И(А3=”Двері”;В3>3);D3*0,05;Если(И(А3=”Вікно”;В3>4);D3*0,1;0))

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

Задача №4. Розрахувати в таблиці максимальні і мінімальні значення

Методичні вказівки.

1. Зробити активною чарунку В12.

2. Визвати майстер функцій , зліва у вікні вибрати категорію Статистичні праворуч вибрати функцію МАКС.

3. Клацнути на кнопці звернути , виділити діапазон В3:В10.

4. Розвернути вікно майстра функцій клацнувши на кнопці . Клацнути на кнопці ОК.

Самостійно розрахувати максимальне значення у чарунці Е12. Можна скопіювати формулу використовуючи маркер заповнення.

5. Зробити активною чарунку В13.

6. Визвати майстер функцій , зліва у вікні вибрати категорію Статистичні, праворуч вибрати функцію МИН.

7. Клацнути на кнопці звернути , виділити діапазон В3:В10.

8. Розвернути вікно майстра функцій клацнувши на кнопці . Клацнути на кнопці ОК

Самостійно розрахувати мінімальне значення у чарунці Е13. Можна скопіювати формулу використовуючи маркер заповнення.

Задача №5. Розрахувати в таблиці кількість знижок.

Методичні вказівки.

1. Зробити активною чарунку Е14.

2. Визвати майстер функцій , зліва у вікні вибрати категорію Статистичні праворуч вибрати функцію СЧЕТЕСЛИ.

3. Клацнути на кнопці звернути , виділити діапазон Е3:Е10.

4. Розвернути вікно майстра функцій клацнувши на кнопці .

5. У полі Условие ввести >0

6. Клацнути на кнопці ОК.

6.8.Автоматизація рішення задач економічного планування за допомогою надбудови «Поиск решения» у середовищі Microsoft Excel.

6.8.1.Основні типи задач економічного планування

Задача оптимізації використання ресурсів (задача планування виробництва).

Для виготовлення двох видів продукції P₁ і P₂ використовують чотири види ресурсів S₁, S₂, S₃, S₄. Запаси ресурсів, число одиниць ресурсів, затрачуваних на виготовлення одиниці продукції, наведені в табл. 1 (цифри умовні).

Таблиця 6.1 – Умовні дані задачі оптимізації ресурсів

Вид ресурсу	Запас ресурсу	Число одиниць ресурсів, затрачуваних на виготовлення одиниці продукції
P1	P2
S1
S2
S3		-
S4			-

Ціна реалізації одиниці продукції P₁ і P₂ відповідно, становить 2 і 3 грн.

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

Складемо оптимізаційну модель задачі.

Позначимо:

x₁, x₂ – число одиниць продукції відповідно P₁ і P₂, запланованих до виробництва.

Для їхнього виготовлення (табл. 1) буде потрібно (1* x₁+3* x₂) одиниць ресурсу S₁, (2* x₁+1* x₂) одиниць ресурсу S₂, (1* x₂) одиниць ресурсу S₃, (3* x₁) одиниць ресурсу S₄. У наслідок того, що споживання ресурсів S₁, S₂, S₃, S₄ не мусить перевищувати їхніх запасів, відповідно 18, 16, 5 і 21 одиниць, то зв'язок між споживанням ресурсів і їхніх запасів виражається системою обмежень:

Змінні позитивні x₁³0, x₂³0.

Сумарний прибуток F від реалізації продукції складе:

.

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

Позначимо:

X_j (j=1,2,…,n) – число одиниць продукції P_j, запланованих до виробництва;

b_i (i=1,2,…,m) – запас ресурсу S_i;

a_ij – число одиниць ресурсу S_i, затрачуваного на виготовлення одиниці продукції P_j (числа a_ij – коефіцієнти прямих витрат, які називають технологічними коефіцієнтами);

c_j – прибуток від реалізації одиниці продукції P_j.

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

Знайти такий план X=(x₁, x₂,…x_n) випуску продукції, що задовольняє системі

і умові

,

при якому функція

приймає максимальне значення.

Задача оптимізації складання раціону (задача про дієту, задача про суміші).

Є два види корму I і II, що містять живильні речовини (вітаміни) S₁, S₂, S₃. Кількість одиниць живильних речовин в 1 кг кожного виду корму, необхідний мінімум живильних речовин наведені в табл. 6.2 (цифри умовні).

Таблиця 6.2 – Умовні дані задачі оптимізації складання раціону

Живильна речовина (вітаміни)	Необхідний мінімум живильних речовин	Число одиниць живильних речовин в 1 кг корми
I	II
S1
S2
S3

Дата добавления: 2014-11-06