Робота з файлами електронних таблиць

Структурне документування в електронних таблицях MS Excel

Зміст розділу

Типи даних і їх введення в електронну таблицю

Редагування в електронній таблиці

Обчислення в електронній таблиці

Форматування клітинок і діапазонів

Графічне представлення даних електронних таблиць

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

Будова електронної таблиці

Для запуску табличного процесора слід виконати команду Пуск - Программы - Microsoft Excelопераційної системи Windows, після чого на екрані з’являється вікно табличного процесора (рис. 4.1). Це вікно є стандартним для додатків ОС Windows, призначення його елементів докладно розглянуто у попередньому розділі.

Рис. 4.2.1 - Вікно електронної таблиці

Документи, що створюються у середовищі Excel, називають книгами. Кожна книга складається з листів таких типів: робочі листи або просто листи; листи діаграм; листи макросів; листи модулів, написаних мовою Visual Basic; листи діалогу. Поки ми будемо розглядати тільки листи перших двох типів.

Спочатку робоча книгамістить 3 листа, але в процесі роботи користувач може додавати або видаляти листи. У нижній частині вікна розташовані ярлички для позначення листів. На початку роботи вони називаються Лист1, Лист2, Лист3. Саме ці ярлички дозволяють визначити, який листєактивним. Кожна книга може містити до 255 листів.

Робочий лист - це електронна таблиця (ЕТ), що складається із 255 стовпців та 16334 рядків. Найменування стовпців встановлюються у верхній частині вікна. Стовпці позначаються літерами: перші 26 – літерами А - Z, наступні 26 - літерами АА - AZ, і так далі до останнього, 255 стовпця, який позначається літерами IV. Рядки пронумеровані від 1 до 16334 з лівої сторони екрану.

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

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

Типи даних і їх введення в електронну таблицю

Активізував клітинку, можна вводити в неї дані: текст; числа, включаючи дати і час; формули. Під час вводу даних вони послідовно з'являються у клітинці і одночасно в рядку над робочим листом, який називається рядком формул. У рядку формул знаходиться курсор вставки (вертикальна риска, яка показує, де буде введений наступний символ). Між полями ім’я(адреси) клітинки і рядком формул знаходяться три кнопки.

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

Введення текстових даних.Текстові дані можуть складатися із літер алфавіту, цифр і спеціальних символів. Довжина одного запису в клітинці не може перевищувати 255 символів. Ширину клітинки можна збільшувати, або зменшувати відповідно довжині даних. Введені у комірку дані програма ЕХСЕL розпізнає як текст і вирівнює по лівому краю. Якщо вводяться цифри і потрібно, щоб вони були сприйняті як текст, то ввід повинен починатися з символу<'> (апостроф).

Введення числових даних. Використовуються такі основні форми числа: ціле (наприклад, 777), дійсне (наприклад, 0,763), з експонентою (наприклад, 1,256Е+5). Число з експонентою складається з мантиси і порядку, розділених латинською літерою Е. Таке число трактується як мантиса, помножена на 10 у степені, що дорівнює порядку.

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

Найчастіше використовується стисла форма представлення дати. Наприклад, дату 23 жовтня 2006 року записують 23.10.2006.

При введенні часу між годинами, хвилинами і секундами ставлять двокрапку. Наприклад, 14 годин 26 хвилин 9 секунд треба записують14:26:09. Вводити секунди не обов’язково.

Введення формул.Однією з цінних якостей програми ЕХСЕL являється можливість обробляти дані за допомогою формул. Вони можуть бути як елементарними (наприклад, додавання двох значень), так і складними (наприклад, обчислення майбутньої суми особистого рахунку). Можна вводити дані прямо в формулу, наприклад, =5+4+3. Але головним являється те, що ЕХСЕL дозволяє використовувати адреси клітинок у якості операндів у формулах, наприклад, формула =В1+В2+ВЗ обчислює суму значень клітинок В1, В2 та В3. Після заміни даних в цих клітинках сума буде автоматично перерахована.

В програмі ЕХСЕL формула повинна починатися з символу дорівнює ”=”. Для введення формули необхідно спочатку ввести цей символ, а потім вводити формулу.

В полі клітинки після введення формули звичайно ми бачимо не формулу, а значення, обчислене за формулою. Якщо треба, то можна встановити режим показу формул. Для цього потрібно включити опцію Формула на вкладціВидвікна діалогу Параметры, яке викликається командоюСервис - Параметры.

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

Якщо при обчисленні формули сталася помилка, то в клітинку виводиться повідомлення про помилку, яке починається із символу “#”. Excel виводить такі повідомлення про помилки: #ДЕЛ - 0! – спроба поділити на нуль або порожню клітинку; #ИМЯ? – формула використовує неіснуюче ім’я (наприклад, адреса клітинки написана українськими буквами); #ЧИСЛО! – помилка у числі, число неможливо подати в Excel (наприклад, треба добути квадратний корінь з від’ємного числа, а Excel не оперує з комплексними числами); #ЗНАЧ! – помилка при обчисленні (наприклад, треба помножити число на текст). При отримання таких повідомлень треба проаналізувати формулу і її елементи і внести відповідні виправлення.

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

Суміжний діапазон клітинок складає єдиний прямокутник клітинок. Для введення такого діапазону в команду вказують адресу лівого верхнього і правого нижнього кутів діапазону, розділених двокрапкою. Наприклад, А1:С6. Для діапазону стовпців в адресі можна не вказувати номери рядків (наприклад, А:С – діапазон стовпців від А до С), а для діапазону рядків – імена стовпців (наприклад, 1:6 – діапазон рядків з першого по шостий).

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

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

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

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

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

Засоби автоматизації заповнення ЕТ. У програмі Excel передбачені засоби, які дозволяють спростити складання ЕТ. Розглянемо ці засоби на конкретному прикладі. Складемо ЕТ для обчислення вартості покупки. Така ЕТ зображена у режимі показу формул на рис. 4.2.

Рис. 4.2.2 - Приклад електронної таблиці

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

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

При використанні зазначених команд автоматично змінюються адреси відповідних клітинок, якщо вони мають відносні посилання (як на рис. 4.2.2). Щоб при копіюванні формули адреса потрібної клітинки не змінювалася, її треба написати як абсолютне посилання, додавши перед назвою стовпця і номером рядка знаки долара $ (наприклад, $A$2).

Використання команди Правка - Заполнить - Прогрессия дозволяє автоматично заповнити діапазон клітинок числовими послідовностями, які складають арифметичну або геометричну прогресії. Так, в ЕТ, зображеної на рис.4.2.2, стовпець №№ містить арифметичну прогресію: перший член прогресії (клітинка А6) дорівнює 1, різниця (крок) прогресії теж дорівнює 1. Для заповнення діапазону А6:А11 числами треба записати 1 в клітинку А6, і, залишити на неї табличний курсор, ввести команду Правка - Заполнить - Прогрессия. На екрані з’являється діалогове вікно Прогрессия (рис. 4.2.3). В цьому вікні перемикачі Расположение і Типтреба встановити відповідно у положення по столбцам і арифметическая, у поле Шаг ввести 1, а у полеПредельное значение – 6 і далі натиснути кнопку ОК.

Рис. 4.3 - Діалогове вікно Прогрессия

Редагування в електронній таблиці

У процесі створення ЕТ і введення даних постійно виникає потреба вносити в ЕТ ті чи інші виправлення і зміни. Програма Ехсеl має зручні засоби для виконання цих операцій.

Редагування активної клітинки. Для внесення змін у вміст одної клітинки треба на цю клітинку встановити табличний курсор (зробити її активною). Вміст активної клітинки автоматично переноситься у рядок формули, де його можна редагувати аналогічно тексту у редакторі MS Word. Клацаємо мишею на потрібній позиції у рядку формули, з’являється курсор вставки і проводимо необхідні виправлення. Щоб виправлений текст перемістився у активну клітинку треба натиснути клавішу введення Enter.

Для очищення активної клітинки достатньо натиснути клавішу Delete.

Очищення, вставка, вилучення елементів ЕТ. Для виконання таких дій над елементами ЕТ спочатку треба їх виділити.

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

Очистити виділений діапазон клітинок можна командою Правка - Очистить. При цьому на екрані з’являється підменю, яке включає такі пункти: Все – вилучає формати та значення клітинок;Формати – вилучає тільки формати клітинок;Содержимое –вилучає значення клітинок. При очищенні клітинки діапазону залишаються на своєму місці в таблиці, міняється тільки їх вміст.

Для вставки у таблицю рядка треба поставити табличний курсор на будь-яку клітинку рядка, перед яким треба вставити новий рядок, і подати команду Вставка - Строки. Аналогічно виконується вставка стовпця по команді Вставка - Столбцы.

Команди Правка - Удалить лист іВставка - Лист дозволяють вилучати або вставляти листи у книгу. По замовченню Excel створює книгу, яка має 3 листа. Якщо потрібно більше, то листи можна додати у книгу за допомогою команди Вставка/Лист.

Обчислення в електронній таблиці

Формули. Обчислення в ЕТ здійснюється за допомогою формул. Формула – це сукупність операндів, з’єднаних між собою знаками операцій і круглих дужок. Операндом може бути число, текст, логічне значення, адреса клітинки, функція. У формулах розрізняються арифметичні операції і операції відношень. Excel допускає: арифметичні операції + - додавання, - - віднімання, * - множення, / - ділення, ^ - піднесення до степеня; операції відношень > - більше, < - менше, = - дорівнює, <= - менше або дорівнює,
>= - більше або дорівнює, <> - не дорівнює.

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

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

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

Більшість помилок у формулах з математичними операторами відбувається із-за порушення порядку арифметичних дій. В програмі ЕХСЕL встановленій такий порядок виконання арифметичних операцій:

1) піднесення до степеня і дії які виконуються в дужках,

2) множення, ділення,

3) додавання, віднімання.

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

Формули масивів. Такий тип формул дозволяють обчислити не одне число, а масив чисел, які записуються у діапазон (масив) клітинок. Спочатку треба виділити діапазон клітинок, в який буде записуватися результат обчислень. Далі пишемо потрібну формулу. Введення формули закінчується натискуванням одночасно трьох клавішCtrl, ShiftiEnter. Введена формула береться при цьому у фігурні дужки. Введення звичайної формули (не формули масиву) закінчується лише натискуванням клавіші Enter.

У наведеній на рис. 3.2.2 електронній таблиці для обчислення вартості можна використати формулу масиву. Треба виділити діапазон клітинок F6:F11, далі записати формулу =D6:D11*E6:E11 і натиснути клавіші Ctrl + Shift + Enter.

Є багато функцій (див. таблицю 4.1), які обробляють масиви, і формули з їх використанням треба вводити як формули масиву. Наприклад, всі функції для обчислення з матрицями потребують використання формули масиву.

Формула масиву вводиться не в одну клітинку, а у виділений діапазон (масив) клітинок. Окрема клітинка із цього масиву не має формули, по якою обчислюється її вміст, тому не можна змінити вміст окремої клітинки. При спробі змінити вміст однієї клітинки Excel видає повідомлення “Нельзя изменять часть массива”. Треба заново виділити весь діапазон, а потім внести зміни у формулу масиву.

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

Ввести функцію у формулу можна вручну або з використанням майстра функцій. Краще використовувати майстер функцій. Для роботи з майстром функцій слід натиснути кнопку Мастер функций на панелі інструментів Стандартная або виконати команду Вставка - Функция. При цьому відкривається діалогове вікно Мастер функций – шаг 1 из 2(рис. 4.4), в якому можна вибрати категорію функцій. При виборі категорії в поле Выберите функцию виводиться список функцій даної категорії. У цьому полі можна вибрати потрібну функцію.

Рис. 4.2.4 - Вибір функції

Після вибору функції слід натиснути кнопку ОК, у результаті чого відкривається вікно діалогу Мастер функций – шаг 2 из 2(рис. 4.2.5), в якому треба вказати аргументи функції.

Рис. 4.2.5 - Вікно для введення аргументів функції

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

У таблиці 4.2.1 наведені функції електронної таблиці, які найчастіше використовуються при розв’язуванні прикладних задач.

Таблиця 4.2.1 - Перелік основних функцій

Продовження таблиці 4.2.1

Далі наведені приклади використання функцій електронної таблиці.

Приклад 1. Функції СУММЕСЛИ, СЧЕТЕСЛИ. Особливості використання цих функцій розглянемо на прикладі електронної таблиці, представленої на рис. 4.2.6.

Рис. 4.6 – Розрахунки за функціями СУММЕСЛИ,СЧЕТЕСЛИ

Нехай, наприклад, треба розрахувати сумарну потребу в деталях 20Д305. Для цього спочатку активізують кнопку Вставка функции,у переліку знаходять функцію СУММЕСЛИ. Її аргумент Диапазонмістить значення діапазону комірок з назвами деталей, серед яких відшукуються ті, що задовольняють умову, поставлену в аргументі Критерий.Цей аргумент може містити число, умову, текст, адресу комірки, що містить потрібну назву (наприклад, 45, >1, "20Д305", A3).

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

З урахуванням наведених пояснень для обчислення сумарної потреби в деталях типу 20Д305 у клітинку Е16 вводимо таку формулу

=СУММЕСЛИ(D3;D14;”20Д305”;E3:E14)

і отримуємо в результаті 265.

Функція СЧЕТЕСЛИ розраховує кількість заповнених комірок у діапазоні, які задовольняють поставлену умову. Якщо, наприклад, треба розрахувати, у скількох виробах використовується деталь 20Д305, то у клітинку E18 вводимо таку формулу

=СЧЕТЕСЛИ(D3:D14;"20Д305")

і отримуємо в результаті 3.

Приклад 2. Функція ЕСЛИ. Функція ЕСЛИ використовується для перевірки умови стосовно значень та формул і повертає одне розраховане значення, якщо задана умова після розрахунку дає значення ІСТИНА, й інші розраховані значення, якщо значення умови після розрахунку буде ХИБНІСТЬ. Аргументами функції можуть бути до семи вбудованих функцій в разі складних перевірок.

Нехай треба розрахувати значення функції такого вигляду:

для, наприклад, двох значень аргументу х = 2,1 і х = -1,3.

Спочатку в діапазон клітинок А2:А3 (рис.4.2.7) заносять значення х й активізують клітинку для розрахунку першого значення у (клітинка В2).

Далі активізують кнопку Вставка функции,знаходять функцію ЕСЛИ й активізують кнопку ОК.

Рис. 4.2.7 - Введення аргументів функції ЕСЛИ

В аргументі Лог_выражение(рис. 4.2.7) створюють умову для першого рівняння (х≤0,5), причому х в Excel — це адреса клітинки, що містить значення х. Умова матиме вигляд А2<=0,5.

Аргумент Значение_если_истинамістить розрахунок першого рівняння, якщо умова аргументу Логическое выражениемає значення ІСТИНА. Аргумент Значение_если_ложьмістить розрахунок другого рівняння, якщо умова аргументу Логическое выражениемає значення ХИБНІСТЬ. Після натискування кнопки ОК створена формула заноситься у клітинку В2 і за цією формулою здійснюється обчислення для першого значення х.

Рис. 4.2.8 - Розрахунок за функцією ЕСЛИ

Далі її копіюють для другого значення у, і в результаті електронна таблиця набуває вигляду, показаного на рис. 4.2.8.

Приклад 3. Обчисленняз матрицами. Для роботи з матрицями використовують різні функції (табл. 4.2.1). Матриці можна перемножувати між собою, множити на вектор, транспонувати, створювати обернені матриці тощо.

Розрахунки з матрицями розглянемо на прикладі розв’язування системи лінійних рівнянь, заданої в матричної формі А×Х=С,де А –матриця системи, С – вектор вільних членів, Х – вектор розв’язку (корені) системи. Нехай

Якщо визначник матриці А не дорівнює нулю, то розв’язок системи лінійних рівняньА×Х=С знаходиться за формулою Х=А^-1×С, де А^-1 – обернена матриця А.

Спочатку створюють матрицю, А, наприклад, у діапазоні клітинок А2:С4, а вектора С –у діапазоні клітинок Е2:Е4 (рис. 4.2.9). Після цього над елементами матриці А і вектора С можна виконувати дії, що потрібні для розв’язування системи рівнянь.

Обчислення визначника матриці. Для обчислення визначника матриці застосовується функція МОПРЕД(массив), де массив – діапазон клітинок, в який записана матриця. У прикладі, що розглядається, для обчислення визначника матриці А у клітинку Е6 записана формула =МОПРЕД(А2:С4). Визначник не дорівнює нулю (рис. 4.2.9), таким чином, система має розв’язок.

Створення оберненої матриці.Треба створити обернену матрицю А^-1 з елементів матриці А. Такі обчислення виконуються за формулою масиву. Для цього виділяють діапазон комірок (А9:С11), де буде створюватися нова матриця, активізують кнопку Вставка функции,вибирають функцію МОБР, і заносять відповідні значення елементів матриці А, в результаті отримують формулу =МОБР(А2:С4). Після цього встановлюють курсор на рядок формул та натискують на клавіші Ctrl+Shift+Enter.

Множення матриці на вектор.Нехай, наприклад, треба помножити матрицю на вектор. Для цього використовують функцію МУМНОЖ (Массив1; Массив2),де Массив1, Массив2— це масиви, які перемножуються. Кількість стовпців аргументу Массив1має бути такою, як і кількість рядків аргументу Массив2.Результатом множення матриць є масив з такою самою кількістю рядків, що й масив1, і з такою самою кількістю стовпців, що й масив2. Множення матриці на вектор виконується за формулою масиву.

Для розв’язування системи рівнянь матрицю А^-1 требапомножитина векторС.Результатом будевектор розв’язку Х,для розміщення якого потрібні три клітинки, наприклад, Е9:Е11. Їх треба виділити, активізувати кнопку Вставка функции,вибрати функцію МУМНОЖ і занести відповідні значення діапазонів матриці А^-1 та вектора С (рис. 4.2.9).

Після цього встановлюють курсор на рядок формул, натискують на клавіші Ctrl+Shift+Enterй одержують формулу МУМНОЖ(А9:С11;Е2:Е4).

Для перевірки розв’язку треба матрицю системи Апомножимо на знайдений вектор Х.Отриманий вектор повинен збігатися з вектором С.

Функції, що застосовувалися при розв’язуванні системи лінійних рівнянь, та отриманий розв’язок представлені відповідно на рис. 4.2.9 і 4.2.10.

Рис. 4.2.9 - Функції, що застосовуються для розв’язання системи лінійних рівнянь

Рис. 4.10 - Розраховані значення коренів системи лінійних рівнянь

Імена клітинок і діапазонів. Формула стає більш зрозумілою, якщо клітинкам і діапазонам, які записані у формулі, надати змістовні імена. Так, наприклад, якщо діапазонам D6:D1, E6:E11, F6:F11 (див. рис. 4.2.2) надати відповідно імена Ціна,Кількість,Вартість,то дляобчислення Вартості можна записати формулу =Ціна * Кількість.

Для надання клітинкам або діапазонам певних імен слід спочатку їх виділити, а потім виконати команду Вставка - Имя - Присвоить.На екраніз’явитьсядіалогове вікноПрисвоение имени,в полеИмя якого можна набрати ім’я. Ім’я повинно починатися з літери, може включати будь-які літери і цифри, а також знак підкреслення , знак питання, крапку. Адреса виділеної клітинки або діапазону з’явиться в полі Формула цього вікна, За замовчуванням створене ім’я всій книзі. Якщо треба обмежити дію імені певним листом, то імені, яке набирається в полі Имя, повинно передувати ім’я листа і знак оклику, наприклад, лист1!Ім’я_1. Для вилучення імені слід розкрити список Имя діалогового вікна Присвоить имя, виділити ім’я і натиснути кнопку Удалить.

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

Форматування клітинок і діапазонів

Форматуванням клітинки називають встановлення вигляду відображення вмісту клітинки. Поняття формату включає такі параметри: шрифт (тип, розмір, накреслення, колір); формат числа; спосіб вирівнювання; розміри (ширина і висота) клітинки; обрамлення клітинок; візерунок фону.

Для форматування клітинки або діапазону клітинок використовують команду Формат - Ячейки або кнопки панелі інструментів Форматирование.

Шрифт. За замовчуванням встановлюється шрифт типу Arial Cyr розміру 10 пт, накреслення – звичайне. Для зміни встановлених параметрів шрифту доцільно використовувати кнопки на панелі інструментів Форматирование (рис. 4.2.11). Вони ідентичні аналогічним кнопкам у текстовому редакторі Word.

Рис. 4.2.11 - Кнопки для форматування символів

Формат чисел. Excel може автоматично розпізнавати формат чисел при введенні. Однак існує можливість встановити для виділеного діапазону клітинок формати чисел. Їх можна вибрати зі списку Числовые форматывкладки Число вікна діалогу Формат ячеек (рис. 4.2.12), яке викликається командою Формат - Ячейки

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

Рис. 4.12 - Числові формати

Вирівнювання даних у клітинках. За замовчуванням Excel вирівнює вміст клітинки по нижньому краю, при цьому текст – по лівій межі стовпця, число – по правій. Користувач може змінити ці установки, використовуючи вкладку Выравнивание вікна діалогу Формат ячеек або відповідні кнопки панелі інструментів Форматирование. Порівняно з текстовим редактором Word в Excel відсутнє вирівнювання По ширине, але добавлена можливість орієнтувати текст під різними кутами. Якщо треба встановити негоризонтальну орієнтацію, то слід скористатися опцією Ориентация вкладки Выравнивание вікна діалогу Формат ячеек (рис. 4.13) і встановити потрібний кут.

Розміри стовпців і рядків. За замовчуванням Excel встановлює ширину стовпця, що дорівнює 8,43 символу, а висоту – за розміром найвищого шрифту у рядку. Команди Формат - Строка - Высота іФормат - Столбец - Ширина відкривають діалогові вікна, у яких можна встановити ширину виділених стовпців і висоту виділених рядків. Зручніше змінювати розміри стовпців і рядків за допомогою миші. Для цього слід встановити курсор миші на праву межу стовпця або відповідно на нижню межу рядка (він приймає форму двох стрілок, направлених у різні сторони) і перемістити мишу до потрібних розмірів при натиснутій лівій кнопці.

Рис. 4.2.13 - Вирівнювання даних в клітинці

Обрамлення і встановлення фону виділеного діапазону. Обрамлення виділеного діапазону клітинок здійснюється встановленням параметрів вкладки Граница вікна діалогу Формат ячеек(рис. 4.2.14).

Рис. 4.14 - Обрамлення клітинок

Вкладка дозволяє встановити форму рамки (кругом, зліва, справа, зверху, знизу), тип, товщину і колір лінії рамки.

Для зміни кольору або візерунка виділеного діапазону клітинок слід виконати команду Формат - Ячейки і на вкладці Видвибративізерунок і колір.

Обрамлення і встановлення кольору фону клітинок зручніше виконувати відповідно за допомогою кнопок на панелі інструментів Форматирование

Робота з файлами електронних таблиць

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

Таблиця 4.2.2 - Команди для роботи з файлами книг ЕТ

№№	Операція	Спосіб введення команди
Меню	Кнопка
	Створення	Файл/Создать
	Відкриття	Файл/Открыть
	Збереження	Файл/Сохранить Файл/Сохранить как
	Перегляд	Файл/Предварительный просмотр
	Друкування	Файл/Печать

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

Графічне представлення даних електронних таблиць

Для підвищення наочності отриманих результатів в Excel передбачена можливість їх представлення у вигляді різноманітних графічних діаграм.

Діаграми. Розглянемо приклад побудови діаграми для таблиці на рис. 4.2.15.

Рис. 4.2.15 - Дані для побудови діаграми

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

Для таблиці, наведеної на рис. 4.2.15, кількість рядів даних і категорій залежить від того, як розміщені дані. Якщо вони розміщені по рядках, то рядів даних – 3 (Пилосос, Вентилятор, Водонагрівач), а категорій – 4 (1 квартал, 2 квартал, 3 квартал, 4 квартал). Якщо ряди даних розміщені по стовпцям, то рядів даних – 4 (1 квартал, 2 квартал, 3 квартал, 4 квартал), а категорій – 3 (Пилосос, Вентилятор, Водонагрівач).

Програма Excel дозволяє будувати діаграми різних типів; кожний тип діаграми має декілька видів (рис. 4.2.16). У прикладі, що розглядається, будемо будувати діаграму типу Гистограмма для 3 рядів даних Пилосос, Вентилятор, Водонагрівач.

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

У прикладі, що розглядається, назвами рядів можуть бути слова Пилосос, Вентилятор, Водонагрівач, які розміщені у клітинках А5:А7 електронної таблиці. Excel дозволяє також задати рядам довільні назви, які не містяться у клітинках.

Категорії теж могуть мати мітки, які виводяться під віссю Х. Вісь Х називають віссю категорій, а вісь У – віссю значень. У прикладі, що розглядається мітками категорій можуть бути слова 1 квартал, 2 квартал, 3 квартал, 4 квартал, які містяться у клітинках В4:Е4.

Крім легенди і міток категорій, на діаграму можуть бути нанесені і інші елементи оформлення. Наприклад, заголовки: назва діаграми, назви по осям Х і У.

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

Побудова діаграми. Для побудови діаграми слід скористатися майстром діаграм, натиснувши кнопку Мастер диаграмм на панелі інструментів Стандартнаяабо виконавкомандуВставка - Диаграмма. Курсор при цьому перетворюється у мініатюрну діаграму. Натиснувши ліву клавішу миші, слід тягнути курсор до того місця, де бажано розмістити діаграму. При відпусканні клавіші миші запускається майстер діаграм. Майстер діаграм створює діаграму за чотири кроки (рис. 4.16 – рис. 4.19).

На першому кроці (рис. 4.16) треба вибрати тип і вид діаграми.

На другому кроці (рис.4.17) вводять діапазони клітинок, що містять ряди даних, для яких будуємо діаграму. У рядок Діапазонцього вікна значення діапазону можна ввести вручну. Але краще це зробити шляхом виділення діапазону аналогічно тому, як вводили аргументи функцій. У діапазон, крім рядів даних, доцільно додатково включити клітинки, із яких Excel автоматично прочитає мітки легенди і мітки категорій. Читання цих міток здійснюється по такому правилу: якщо ряди даних розміщені у рядках таблиці, то мітки легенди читаються з першого стовпця, а мітки категорій – з першого рядка введеного діапазону; якщо ряди даних розміщені у стовпцях таблиці, то мітки легенди читаються з першого рядка, а мітки категорій – з першого стовпця виділеного діапазону.

Цільова функція (прибуток): P = 30*x₁+20*x₂+55*x₃,

де x₁, x₂, x₃ – відповідно кількості випущених електрочайників, кавоварок, прасок.

Обмеження:

1) затрати всіх видів ресурсів (електроенергії і металу) на план x₁, x₂, x₃ не повинні перевищувати наявних лімітів.

2*x₁+3*x₂+5*x₃ ≤ 600,

4*x₁+2*x₂,+7*x₃ ≤ 400;

2) властивості плану

x₁, x₂, x₃ ≥ 1; x₁, x₂, x₃ – цілі.

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

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

Рис. 3.21 - Основні області моделі

Для значень змінних x₁, x₂, x₃ виділено клітинки B11:B13. Клітинка B3 є цільовою клітинкою. B цю клітинку заносимо формулу для обчислення прибутку (значення цільової функції)

=CУMMПPOИ3B ($B$11:$B$13;$E$11:$E$13).

B області обмежень вводимо до клітинок B13, B19 формули для обчислення значень витрат на електроенергію та метал, а також до клітинок C13, C19 - задані у задачі обмеження цих значень.

B13: =CУMMПPOИ3B ($B$11:$B$13; $C$11:$C$13),

B19: =CУMMПPOИ3B ($B$11:$B$13; $D$11:$D$13).

Програма Поиск решениязмінює значення к клітинок B11:B13 області змінних доти, доки у цільовій комірці B3 не з'явиться результат, який необхідно отримати.

Для запуску програми Поиск решениявиконується команда Сервис - Поиск решения,яка викликає однойменне діалогове вікно (рис. 4.2.22).

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

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

У полі Изменяя ячейки необхідно вказати діапазон, що містить клітинки, значення яких програма повинна змінити для отримання оптимального значення (діапазон B11:В13 області змінних). При натисканні кнопки Предположить програма виділяє клітинки, на які прямо або опосередковано посилається цільова клітинка.

Для того, щоб задати обмеження, треба натиснути кнопку Добавить. З'являється діалогове вікно (рис. 4.2.23).

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

Рис 4.2.23 - Вікно додавання обмеження

Поле Ограничение цього вікна призначене для введення значення обмеження у вигляді константи або у вигляді адреси клітинки, яка містить це значення. У нашому випадку до обмежень додаються значення клітинок В11:B13, які повинні бути цілими та перевищувати одиницю, а також в області обмежень значення витрат на електроенергію та метал не повинні перевищувати заданих лімітів. По закінченні введення всіх параметрів треба натиснути кнопку OK.

Запуск програми на виконання обчислень здійснюється натисканням кнопки Выполнить у діалоговому вікні програми Поиск решения (рис. 4.2.22). Якщо оптимальне рішення буде знайдено, отримані значення будуть вставлені в таблицю (рис. 4.25) і на екрані з'явиться вікно з інформацією про закінчення цього процесу (рис.4.2.24).

Рис. 4.24 - Визначення результатів пошуку рішення

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

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

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

Аналіз отриманого рішення. Із аналізу таблиці з результатами розв’язування задачі (рис. 4.41) можна зробити такі висновки: для того, щоб отримати максимальний прибуток у сумі 4740 грн. треба виготовити електрочайників 123 штуки, кавоварок – 47 штук, прасок – 2 штуки. При такому плані ліміт металу буде вичерпано повністю, а по електроенергії залишиться запас 3 грн.

Прогнозування у середовищі MS Excel

Підбір параметрів.

Ця операція використовується для прогнозування значення однієї комірки при зміні значення іншої; такі комірки мають бути пов'язані формулою.

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

Рис 4.2.26 - Фінансові показники роботи фірм

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

Рис. 4.27 - Вікно команди Подбор параметра

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

Рис. 4.28 - Результат підбору параметру.

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

Прогнозування за допомогою сценаріїв.

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

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

Розв’язання.

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

Побудова сценаріїв починається по команді Сервис - Сценарии. Відкривається діалогове вікно Диспетчер сценариев, у якому треба натиснути кнопку Добавить. У наступному вікні Изменение сценария(рис.4.2.29) задають ім’я для створення сценарію, заносять діапазон клітинок, значення яких підлягають зміні, і натискають кнопку ОК.

Рис. 4.29 – Вікно Изменение сценария

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

Рис. 4.30 – Визначення значень клітинок для сценарію

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

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

Рис. 4.2.31 – Перелік побудованих сценаріїв

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

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

Рис. 4.2.32 – Структура сценарію

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

Прогнозування за допомогою статистичних функцій.

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

В цьому розділі розглянута лінійна парна регресія. Для апроксимації набору спостережень використовується пряма лінія y=a*x+b. Методом найменших квадратів треба обчислити такі значення коефіцієнтів a та b, щоб пряма лінія щонайкраще відповідала наявним даним. Для розв’язування цієї задачі в MS Excel є статистична функція