загрузка...
загрузка...
На головну

Табличний процесор Excel - створення й занесення даних у

  1. Excel для решения прикладных задач
  2. Microsoft Excel
  3. Microsoft Excel
  4. MS Excel 2010 в аналитической обработке данных
  5. Бази даних в Excel. Створення списків. Використання форм
  6. Базы данных в Excel
  7. Ввод и обработка данных в Excel

таблиці

 

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

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

При завантаженні головної програми пакета на екрані виникає головне вікно, показане на рис.1.4. Основні елементи цього вікна показані стрілками.

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

Електронна таблиця, тобто робочий аркуш, складається з рядків і стовпців. Рядки нумеруються від 1 до 16384. Стовпці (від 1 до 255) позначаються латинськими буквами (на початку від A до Z, потім AA, AB,...,AZ, потім BA,BB,BC,...,BZ і т. д. до IU,IV.). На перетинанні рядків і стовпців розташовані комірки електронної таблиці. Робоче поле, тобто вікно робочої книги, складається з: заголовка, робочої електронної таблиці, кнопок, лінійок прокручування і ярликів аркушів. Ці елементи показані на рис.1.4.

Індикатор активноїкомірки, тобто табличний курсор - це темний контур, що виділяє поточну активну комірку (одну з 4 194 304 комірок кожної робочої таблиці). Створення, збереження, відкриття робочої книги в Excel здійснюється за загальними правилами додатків Windows.

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

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

1 метод - копіювання за допомогою кнопок панелі інструментів:

- виділити комірки або блок комірок для копіювання;

- у панелі інструментів Стандартнаяклацнути на кнопці Копировать в буфер ;

- клацнути в комірку, у яку потрібно скопіювати інформацію;

- у панелі інструментів Стандартнаяклацнути на кнопці Вставить из буфера .

2 метод - копіювання за допомогою команд меню:

- виділити комірки або блок комірок для копіювання;

- вибрати команди Правка\ Копировать;

- клацнути в комірку, у яку потрібно скопіювати інформацію;

- вибрати команди Правка \ Вставить.

3 метод - копіювання з використанням контекстних меню:

- виділити комірку або блок комірок для копіювання;

- клацнути правою кнопкою миші і з контекстного меню вибрати команду Копировать;

- клацнути в комірку, у яку потрібно скопіювати інформацію.

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

 

 

 


 

 

Рис. 1.4 - Вікно Excel із трьома відкритими робочими книгами і його основні елементи

Вставка (додавання) комірок виконується наступними операціями:

- виділити область, у яку ви хочете вставити порожні комірки;

- вибрати команди Вставка \ Ячейки (або клацнути правою кнопкою миші на виділених комірках і вибрати команду Добавить з контекстного меню. З'явиться вікно діалогу Добавление ячеек і з пропозиціями зрушення влівоабо вправо;

- указати, куди повинні зрушуватися інші комірки;

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

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

Числові значенняабочисла, виражають різні кількісні співвідношення даних певного типу, наприклад, атомна вага, обсяг продажів і інші. Числові значення, введені в комірки таблиці, можуть використовуватися у формулах або діаграмах. В Excel число може складатися тільки з наступних символів: 0 1 2 3 4 5 6 7 8 9 + - . Для поділу цілої й дробової частин десяткового числа використовується кома. Раціональні дроби необхідно вводити як змішані числа, щоб уникнути їхньої інтерпретації як формати дати, наприклад, слід вводити 7 1/2. Не всякий набір цих символів утворить число, наприклад, набір символів "123e" не є числом, а символи "123e2" є числом 12300. Числа в Excel відображаються в категоріях Числовой, экспоненциальный, Финансовый, Денежный, Процентный, Дробный. Для форматування комірок використовується панель Форматирование, діалогове вікно Формат ячеек або контекстне меню. При форматуванні міняється вид числа, але не його значення. За замовчуванням для чисел використовується формат Общий, у якому не відображаються незначні нулі. Програма Excel відобразить число 020,60 як 20,6. Числові значення можуть являти собою дату (наприклад, 25.06.99) або час (наприклад,11:40:35).

Текст в Excel - це будь-яка послідовність, що складається із цифр, пробілів і нецифрових символів. Наприклад, 10AA109, 127AXY, 12-976, 270026 Одеса. Текст використовується для позначення числових даних, для заголовків стовпців або для відображення деякої інформації про робочу таблицю. Будь-яка послідовність введених в комірки символів, що не може бути інтерпретована Excel як число, формула, дата, час дня, логічне значення або значення помилки, інтерпретується як текст.

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

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

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

- установити курсор у потрібну комірку;

- набрати формулу, почавши її зі знака "=";

- нажати клавішу ENTER.

Як приклад наведемо формули, що обчислюють корінь квадратного тричлена: ax2+bx+c=0. Вони введені в комірки A2 і A3 і мають такий вигляд:

=(-B1+КОРЕНь (B1*B1-4*A1*C1))/2/A1

=(-B 1-КОРЕНь (B1*B1-4*A1*C1))/2/A1

В комірках A1, B1 і C1 перебувають значення коефіцієнтів a, b і с, відповідно. Якщо ви ввели значення коефіцієнтів a=1, b=-5 і с=6 (це означає, що в комірках A1, B1 і C1 записані числа 1, -5 і 6), то в комірках A2 і A3, де записані формули, ви одержите числа 2 і -3. Якщо ви зміните число в комірці A1 на -1, то в комірках з формулами ви одержите числа -6 і 1. У наведених вище формулах використовується функція КОРЕНЬ, що обчислює квадратний корінь числа.

При використанні у формулах посилання можуть бути як відносні (при копіюванні формул вони автоматично перераховуються), так і абсолютні (при копіюванні вони не змінюються, а у формулах перед ними ставиться знак $). Приклад: (А1*А2)/$А3. Посилатися можна також на комірки з іншого аркуша даної книги, наприклад: Лист1! А1.

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

EXP - обчислення експоненти ex;

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.6- Вид вікна в режимі Поиск решения

 

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

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

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

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

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

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

 

 
 

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

 

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

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

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

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

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

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

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

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

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

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

 

  Стіл Стілець Диван Запас
Дошки
Цвяхи
Клей
Прибуток  

 

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

20x1+18x2+22x3 ® max

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

9x1+5x2+6x3 £ 600

4x1+5x2+6x3 £ 400

3x1+4x2+5x3 £ 800

x1³0 , x2³0, x3³0

 

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

 
 

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

 
 

Використовувані в таблиці формули наступні:

 

 

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

 

2. ПРОГРАМУВАННЯ МОВОЮ VBA

Вступ

VBA (Visual Basic for Application) - стандартна мова програмування в додатках MS Office. Слово "стандартна" означає, що незалежно від того, у якому додатку іде робота, досить вивчити стандартний набір команд і методик, щоб успішно їх застосовувати.

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

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

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

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

 

2.2. Робота у вікні модуля. Структура програми

Основним елементом програмування на VBA є процедура - блок операторів програми в модулі. Модуль - це набір оголошень і процедур мовою VBA, зібраних в одну програмну одиницю. Його можна створювати у вікнах будь-яких додатків MS Office. В Microsoft Access створення модуля ведеться в наступній послідовності:

Пуск ® Программы ® MS Access ® Новая база данных

У вікні, що відкриється, вибрати диск, папку, задати ім'я нової бази даних і нажати на кнопку «Создать».

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

Модули ® Создать

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

Після написання модуля його потрібно зберегти командами: Файл ® Сохранить как,у текстовому полі задати ім'я і ОК.

Щоб створити нову процедуру у вже наявному модулі, потрібно відкрити його в режимі "Конструктор", помістити курсор у порожній рядок вікна модуляі виконати команди: Вставка (Insert)® Процедура (Procedure), у вікні, що відкрилося, вибрати тип процедури, область визначення, записати ім'я і ОК.

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

Запуск (Run)® перейти/продолжить (RunSub/UserForm)

 

 
 

Рис.2.1 - Вікно нового модуля

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

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

Структура програми. Правила написання програм. Якщо програма складається тільки з однієї процедури, то в найпростішому випадку її структура наступна:

Sub ім'я()

Оператори



  1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   Наступна

ReDim(1 to n) As Single | Dim a As single | Константа Опис | Оператори переходу. Програмування розгалужуваних | Dim s As Single , c As Single | Sub Р1() | End Sub | End Sub | End Function | Private Function F1(a As Single, ParamArray x) As Single |

загрузка...
© um.co.ua - учбові матеріали та реферати