Табличний процесор Excel – робота з майстром формул

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

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

В Excel широко представлені математичні функції. Наприклад, можна виконувати різні операції з матрицями: множити, знаходити зворотну, транспонувати. У вашому розпорядженні перебуває бібліотека статистичних функцій, за допомогою якої ви можете проводити статистичне моделювання. Крім того, ви можете використовувати у своїх дослідженнях елементи факторного й регресійного аналізу. В Excel можна вирішувати завдання оптимізації й використовувати аналіз Фур'є. Зокрема, в Excel реалізований алгоритм швидкого перетворення Фур'є, за допомогою якого ви можете побудувати амплітудний і фазовий спектр.

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

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

Вхідні параметри, що задаються, повинні мати припустимі для даного аргументу значення. Деякі функції можуть мати необов'язкові аргументи, які можуть бути відсутніми при обчисленні значення функції. Наприклад, можливе використання функцій без параметрів: =ПІ(), що повертає число, =СЕГОДНЯ(), що повертає поточну дату.

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

Майстер функцій викликається командою Вставка\Функции або натисканням на кнопку Мастер функций. Ця кнопка розташована на панелі інструментів Стандартная, а також у рядку формул.

Математичні функції, що часто зустрічаються

ABS - обчислення модуля;

COS - обчислення косинуса;

SIN - обчислення синуса;

TAN - обчислення тангенса;

EXP - обчислення експоненти e^x_;

LN - натуральний логарифм;

LOG - довільний логарифм по заданій основі;

LOG10 - десятковий логарифм;

ЗНАК - повертає 1, якщо x>0; 0, якщо x=0 і –1, якщо x<0;

КОРЕНЬ- обчислення квадратного кореня;

ОСТАТ- повертає залишок від розподілу числа на дільник;

ПРОИЗВЕД - повертає добуток чисел;

СУММ - обчислює суму чисел.

Для прикладу застосування математичних функцій нижче наводиться варіант розрахунку двох формул:

; ; x=0,411; a=3,232; b=1,922

Рис.1.5 - Ілюстрація обчислення за формулами в Excel

1.9. Табличний процесор Excel – робота з діаграмами й графіками

Подання даних у графічному вигляді дозволяє вирішувати найрізноманітніші завдання. Усього Microsoft Excel для Windows пропонує 9 типів плоских діаграм і 6 типів об'ємних. Ці 15 типів включають 102 формати. Якщо їх не достатньо, ви можете створити власний користувальницький формат діаграми.

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

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

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

- Вісь – лінія, що обмежує одну з сторін області побудови.

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

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

Майстер діаграм пропонує чотири кроки, на кожному кроці можна подивитися вид діаграми натисканням кнопки Просмотр результата в нижній частині вікна Мастера. Там же перебувають керуючі кнопки майстра Отмена, Назад, Далее, Готово. Натискання кнопки Готово виконується на останньому кроці, якщо зробити це раніше, то частина операцій по створенню діаграми не буде завершена. Порядок побудови діаграми наступний :

- виділити діапазони комірок, які включаються в діаграму;

- запустити Майстер діаграм.

Крок 1. Вибрати тип і вид діаграми. Натисніть кнопку Далее.

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

Крок 3. Вибрати параметри діаграми – увести назви діаграми й координатних осей, установити виведення і розміщення умовних позначок (Легенда), визначити підписи даних.

Крок 4. Вибрати аркуш для розміщення діаграми. Натиснути кнопку Готово. Діаграма з'явиться на робочому аркуші.

Вбудовані формати діаграм. В Excel можна будувати об'ємні й плоскі діаграми. Існують наступні типи плоских діаграм: Лінійчата, Гистограмма, З областями, Графік, Кругова, Кільцева, Пелюсткова, XY-Крапкова й Змішана. Об'ємні діаграми можна будувати наступних типів: Лінійчата, Гистограмма, З областями, Графік, Кругова й Поверхня. У кожного типу діаграми, як у плоскої, так і в об'ємної існують підтипи.

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

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

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

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

Графік і діаграма з областями мають схоже оформлення. Горизонтальна лінія — це вісь X, вертикальна — вісь Y. Ці осі використовуються при побудові графіків. На стовпчастій діаграмі осі повернені на 90 градусів, вісь Х розташована ліворуч.

Гистограмма — це стовпчаста діаграма з розташуванням осі Х знизу. Є й тривимірні варіанти таких діаграм. Циліндрична, конічна, пірамідальна — все це різновиду гистограмм.

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

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

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

На третьому етапі задайте різні параметри діаграми за допомогою наступних вкладок.

Заголовки — тут задають назва діаграми в цілому, осі Х й осі Y.

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

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

Легенда — тут визначають висновок і місце для умовних позначок.

Подписи данных — тут визначають відображення тексту або значення як підпис даних.

Таблица данных — тут задають, чи потрібно чи виводити виділену область як частину діаграми.

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

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

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

1.10. Табличний процесор Excel – рішення пошукових завдань лінійного програмування

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

Всі завдання цього типу вирішуються за допомогою інструмента Excel Пошук рішення. Цей режим викликається за допомогою пунктів меню Сервис\Поиск решения, при цьому на екрані виникає вікно наступного виду:

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

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

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

Команда Выполнить служить для запуску пошуку рішення поставленого завдання. Команда Закрыть служить для виходу з вікна діалогу без запуску пошуку рішення поставленого завдання. При цьому зберігаються установи, зроблені у вікнах діалогу, що з'являлися після натискань на кнопки Параметры, Добавить, Заменить або Удалить.

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

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

Рис.1.7- Діалогове вікно Добавление ограничений

Настроювання параметрів алгоритму й програми проводиться в діалоговому вікні Параметры поиска решения (рис.1.8). У вікні установлюються обмеження на час рішення завдань, вибираються алгоритми, задається точність рішення, надається можливість для збереження варіантів моделі і їхнього наступного завантаження. Значення й стани елементів управління, використовувані за замовчуванням, підходять для рішення більшості завдань.

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

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

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

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

Рис. 1.8 - Діалогове вікно Параметры поиска решения

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

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

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

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

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

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

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

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

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

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

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

20x₁+18x₂+22x₃ ® max

за умови дотримання наступних обмежень:

9x₁+5x₂+6x₃ £ 600

4x₁+5x₂+6x₃ £ 400

3x₁+4x₂+5x₃ £ 800

x₁³0 , x₂³0, x₃³0

Рішення поставленого завдання проведемо в табличному процесорі Excel за допомогою режиму «Поиск решения». Вихідні дані завдання оптимізації прибутку:

Рис.1.9 - Результат пошуку оптимального рішення

Рис.1.10 - Формули, які введені для пошуку рішення