Табличний процесор 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 - обчислення експоненти 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. Вибрати аркуш для розміщення діаграми. Натиснути кнопку Готово. Діаграма з'явиться на робочому аркуші.

Поле Относительная погрешность служить для завдання точності (припустимої погрішності), з якої визначається відповідність комірки цільовому значенню або наближення до зазначених границь. Поле повинно містити число з інтервалу від 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 - Формули, які введені для пошуку рішення

 

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 ім'я ()

Оператори

⇐ Предыдущая1234567Следующая ⇒