Опрацювання даних у Excel, сортування даних

ЛАБОРАТОРНА РОБОТА № 1

ЗНАЙОМСТВО З МОЖЛИВОСТЯМИ ЕЛЕКТРОННИХ ТАБЛИЦЬ EXCEL, ФОРМАТУВАННЯ ТАБЛИЦЬ, РОЗРАХУНКИ В ТАБЛИЦЯХ, РЕДАГУВАННЯ ДАНИХ

1.1 Загальні відомості

1.1.1 Ввод до клітин.

До клітинки можна ввести:

а) значення (константу) одного з чотирьох типів:

1) текст – це послідовність букв, цифр і пробілів між ними. За умовчанням текст, який вводиться до клітинки, вирівнюється по її лівому краю;

2) число – це послідовність цифр від 0 до 9 із використанням спеціальних символів (+,-,/,%), ком, дефісів і круглих дужок. За умовчанням числові дані, які вводяться до клітинки, вирівнюються по її правому краю;

3) дата-час;

4) логічне значення („Истина”, „Ложь”)

б) формулу, результатом якої буде значення одного із перелічених раніше типів чи помилкове значення.

1.1.2 Діапазон клітинок

Діапазон клітинок – це сусідніклітинки, що утворюють прямокутник. Діапазон клітинок може складатися з клітинок одного стовпчика: B2:B15, одного рядка: A5:F5 або з комбінації клітинок, розташованих у різних стовпчиках і рядках: C3:G10. Головна умова: клітинки обов’язково утворюють прямокутник.

1.1.3 Виділення клітинки

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

– курсор виділення клітини, діапазонів клітин;

| – курсор ведення даних;

– курсор копіювання /переміщення даних(активізується, якщо підвести курсор до границі виділеного блоку);

+– курсор копіювання(заповнення) в сусідні клітини (активізується, якщо підвести курсор до правого нижнього кута клітини);

– курсор зміни висоти рядка;

- – курсор зміни ширини стовпця;

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

Редагування даних

Після введення даних можна виправити їх у самій клітинці або у рядку формул. Для цього необхідно виділити потрібну клітинку та клацнути в рядку формул або виділити клітинку та натиснути клавішу F2 або двічі клацнути в потрібній клітинці. Так клітинка переходить у режим редагування (у рядку стану з'являється напис Правка).

Для переміщення курсору по тексту клітинки використовуйте клавіші “стрілка вліво” та “стрілка вправо” або ліву кнопку миші. Для видалення розташованого ліворуч від курсору символу використовуйте клавішу BackSрасе ( вона позначена знаком ), для видалення символу, розташованого праворуч, – клавішу Dеlеtе.Потім введіть потрібні дані. Натисніть клавішу Еntеrабо клацніть на кнопці з зеленою позначкою , що розташована в рядку формул, для введення в клітинку нових даних. Якщо ви передумали і не хочете вводити або зберігати виправлення, клацніть в рядку формул на кнопці скасувати , або натисніть клавішу ЕSС .

Примітка. Для виправлення у клітинці повинно бути встановлено прапорець Правка прямо в ячейке (Сервис/Параметры/вкладка Правка) .

1.1.5 Копіювання даних

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

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

1.1.6 Видалення даних з клітинки

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

1.1.7 Видалення клітинок

Щоб видалити клітини, виділіть потрібний діапазон клітин, відкрийте меню Правка або правоюкнопкою миші активізуйте контекстно-залежне меню і виберіть пункт Удалить . З’явиться діалогове вікно Удаление ячеек . Виберіть одну з опцій: Ячейки, со сдвигом влево ; Ячейки, со сдвигом вверх ; Строку або Столбец та клацніть на кнопці ОК .

1.1.8 Вставка клітинок

Щоб вставити клітинку або діапазон клітинок, можна використовувати пункт Добавить ячейки контекстного меню або пункти меню Вставка→Ячейки , Вставка→Строки , Вставка→Столбцы і, коли з’явиться діалогове вікно додавання клітинок, встановити в ньому прапорець потрібної опції та клацнути на кнопці ОК .

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

1.1.9 Об’єднання клітинок

Щоб, об’єднавши клітинки, утворити заголовок, виділіть діапазон клітинок, у якому він повинен розташовуватися, і відкрийте пункт меню Формат→Ячейки .Коли з’явиться відповідне діалогове вікно (рис.1.1), клацніть на вкладці Вьіравнивание , установіть прапорець на опції Обьединение ячеек та Переносить по словам , якщо текст займає декілька рядків.

Рисунок 1.1 – Форматування у клітинках

В цьому ж вікні можна задати види вирівнювання тексту. Наприклад, щоб вирівняти текст в об’єднаних клітинках по вертикалі, клацніть на стрілці відповідного списку, що випадає, і оберіть у ньому по центру , потім клацніть на кнопці ОК . Виділені клітинки об’єднаються в одну. Введіть текст заголовка в лівий верхній діапазон клітинок, у якому він повинен розташовуватися. Якщо заголовок складається з декількох рядків, натисніть АLТ+ЕNТЕR перед введенням наступного рядка.

Використання формул

Формула складається з одного або декількох адресів клітин зі значеннями і математичних знаків ( ^ – піднесення до ступеня, + –додавання, – – віднімання, * – добуток, / – ділення). Кожна формула повинна починатися зі знака рівності = . Якщо потрібно, вона може містити дужки та функції.

Функція – це складна формула, за якою здійснюються операції над даними певного типу. Кожна функція складається з трьох елементів: знак рівності , ім’я функції , аргумент функції в дужках . Можна ввести функцію в клітинку за допомогою Майстра функцій або самостійно. Наприклад, щоб обчислити суму значень в клітинках А1:Н1, використайте функцію =СУММА(А1:Н1) , кнопка виклику якої å (Автосума), знаходиться на стандартній панелі інструментів.

Для перевірки умов та вибору варіанту рішення використовується логічна функція. Синтаксис логічної функції:

ЕСЛИ (лог_вираз; значення_якщо_істина; значення_якщо_ні) .

Функція ЕСЛИ повертає „значення_якщо_істина”, коли „лог_вираз” має значення „ИСТИНА” (TRUE), і повертає „значення_якщо_ні”, коли „лог_вираз”має значення „ЛОЖЬ” (FALSE).

Наприклад,

=ЕСЛИ (А1>0; ПИ( ); 2*А1) – повертає значення ПИ()=3.1415 , якщо в клітині А1 числове значення >0 , і подвійне значення, якщо в клітині А1≤ 0 .

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

И(лог_вираз1; лог_вираз2; ...) – повертає значення „ИСТИНА”, якщо усі аргументи мають значення „ИСТИНА”, у інших випадках приймає значення „ЛОЖЬ”;

ИЛИ(лог_вираз1;лог_вираз2;...) – повертає значення „ИСТИНА”, якщо хоч один вираз має значення „ИСТИНА”, і повертає „ЛОЖЬ”, якщо усі аргументи мають значення „ЛОЖЬ”;

НЕ (лог_вираз) – змінює значення логічного виразу на зворотне, тобто „ИСТИНА” на „ЛОЖЬ”, а „ЛОЖЬ” на „ИСТИНА”.

Завдання

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

1.2.1 Створення таблиці

Створимо заготовки 2-x таблиць рис. 1.2, виконуючи наступні операції:

Рисунок 1.2 – Створена таблиця

а) форматування рядків назви таблиць, використовуючи команду об’єднання клітин А1:С1, та A7:H7 і розміщення у центрі відповідного тексту;

б) заголовки таблиці „Відомість нарахувань” розміщені в двох рядках (8 та 9) таблиці, які об’єднанні для стовбців A , B , C , D , H ; для тексту використано Полужирный стиль шрифту, а текст „Податки” – по центру об’єднаного діапазону E8:G8 ;

в) для заголовків таблиць встановити формат згідно рис. 1.1;

г) зміна ширини стовпця (залежно від об’єму інформації, що вводиться);

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

е) заповнення клітинок стовпця „№” послідовністю чисел 1, 2, …;

ж) введення формул у верхню клітину стовпців E , F , G , H таблиці „Відомість нарахувань”;

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

Задамо пенсійний і профспілковий податки на рівні 2% та 1% від окладу відповідно. Для підрахунку розміру цих податків зручно ввести формулу в одну клітину (E10, F10) , а потім розповсюдити її на весь стовпчик. Головне не забути про абсолютні посилання, тому що значення відсотків від окладу відповідних податків задані в таблиці „Нормативно-довідкових даних”. Тобто посилання буде на клітини за межами таблиці, що створюємо.

Враховуючи це, маємо формули:

Е10=С10*$В$4 , F10=C10*$B$3 .

Після введення формул в клітини Е10 та F10 їх треба скопіювати донизу по стовпчику(протягуючи курсор заповнення).

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

=С10*$B$5 ,

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

і за формулою:

=(C10-$C$3-F10)*$B$5 ,

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

Тому в клітину G10 необхідно вести формулу за допомогою якої робиться перевірка умови на наявність дітей у працівника:

G10: =ЕСЛИ(D10=0 ; С10*$B$5 ; (C10-$C$3-F10)*$B$5) .

Формулу треба скопіювати донизу по стовпчику(протягуючи маркер заповнення).

Для визначення „Суми до видачі” залучимо формулу, яка підраховує різницю окладу і податків:

H10: = С1 -E10-F10-G10 .

Після введення формул в клітину H10 її треба скопіювати донизу по стовпчику;

и) заповнення таблиці відповідною текстовою та фіксованою числовою інформацією (стовпчики „ПІБ”, „Оклад”, „Кількість дітей”).

Вносимо інформацію в стовпчики „ПІБ”, „Оклад”, „Кількість дітей” після того, як уведені усі формули. Результат буде підраховуватися відразу після введення даних про оклад.

У завершальному вигляді таблиця буде мати вигляд, як на рис. 1.3 :

Рисунок 1.3 – Таблиця з даними

Після заповнення таблиці „Відомість нарахувань” можна додати рядок для підрахунку загальної суми нарахувань податків та суми до видачі .

1.2.2 Приклад створення діаграми по заданій таблиці

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

а) виділити два стовпця таблиці „Прізвище, ім’я по батькові” та „Сума до видачі” . Виділення стовпців, які не розміщені поряд, необхідно робити при натиснутій клавіші CTRL ;

б) запустити майстер діаграм одним зі способів: вибрати кнопку „Мастер диаграмм” – панелі інструментів, або команду меню Вставка→Диаграмма ;

в) рухаючись по крокам Мастера диаграмм, оберіть тип діаграми – круговая, объемная разрезанная, підписи данних (встановити прапорець долі).

Приблизний вигляд діаграми наведений на рис. 1.4.

Рисунок 1.4 – Діаграма „Сума до видачі”

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

1.3 Контрольні питання

1.3.1 Призначення всіх елементів вікна Excel.

1.3.2 Головне меню ЕТ Excel. Призначення яких пунктів меню Ви вивчили, виконуючи роботу?

1.3.3 Які види даних можна заносити до ЕТ? Що уявляє собою стандартний формат розміщення даних в клітинах ЕТ ?

1.3.4 Як надати імена клітинам та як їх потім використовувати?

1.3.5 В чому полягає процес модифікації формул у клітинах ЕТ?

1.3.6 Як виконати редагування даних в ЕТ ?

1.3.7 Розкажіть докладно про три різних способи копіювання даних. Що відбувається з адресами при цьому? Що таке абсолютна адреса і відносна адреса?

1.3.8 Основні принципи роботи з кількома листами робочої книги. Як надати ім’я листу? Як використовувати це ім’я в формулах?

1.3.9 Які групи функцій існують в ЕТ? Назвіть декілька функцій з кожної групи.

1.4 Варіанти індивідуальних завдань

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

Даних у таблиці, яку потрібнос створити, повинно бути не менше 20 рядків .

Побудувати діаграму згідно наведеного прикладу.

1.4.1Житлове управління нараховує квартплату мешканцям за встановленими тарифами. Відомо, що 1м² площини у межах норми коштує Х коп., норма площини на одну людину встановлює S м², сім’ї, де норма проживання не витримана, сплачують на К% менше від нарахованої суми.

Скласти таблицю „Квартира” нарахування сплати за квартиру, яка містить інформацію: № квартири , її площина , кількість мешканців , площина на одного мешканця , сума до сплати .

1.4.2Відомо, що існує дві категорії споживачів електроенергії . Для першої категорії нарахування за спожиту електроенергію здійснюється за звичайним тарифом, тобто, по Х коп. за 1квт/г, для другої категорії є пільговий тариф – на У коп. менше тарифного.

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

1.4.3Компанія з водопостачання населенню встановила норми споживання на одну людину холодної води – N₁ м³, гарячої – N₂ м³. Відомо, що в межах норми 1м³ холодної води коштує М₁ коп., 1м³ гарячої води коштує М₂ коп. Поза нормою сплата за кожний м³ збільшується на К% , як для холодної так і для гарячої води.

Скласти таблицю „Вода” нарахування сплати за спожиту воду, яка містить: прізвище споживача , кількість спожитої гарячої води (м3) , кількість спожитої холодної води (м3) , розмір платні за гарячу воду , за холодну воду , загальну плату за спожиту воду кожним споживачем.

1.4.4Житлове управління нараховує сплату за опалення мешканцям за встановленими тарифами. Існує дві категорії споживачів. Відомо, що для споживачів 1 категорії пільг сплата встановлена у розмірі N₁ коп. за 1м² , для споживачів 2 категорії – N₂ коп. за 1м² .

Скласти таблицю „Опалення” нарахування сплати за спожите опалювання, яка містить інформацію: номер дому , квартири , прізвище споживача , площа квартири , категорія пільг , до сплати .

1.4.5Відомо, що норма споживання газу на одну людину становить Х м³ і сплата за 1м³ – У коп. Якщо спожитий газ перевищує норму, то сплата збільшується на У₁ коп. за кожний м³ газу, спожитого поза нормою.

Скласти таблицю „Газ” нарахування сплати за спожитий газ, яка містить інформацію: № квартири , кількість мешканців , попереднє показання лічильника газу , теперішнє показання лічильника газу , кількість спожитого газу .Підрахувати сплату за опалення кожним споживачем.

1.4.6На підприємстві у кінці кожного місяця працівникам нараховується додатково премія. Розмір премії становить М% від окладу, якщо робітник відпрацював місячну норму днів N , а в інших випадках розмір премії нараховується 2% за кожний робочий день.

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

1.4.7 Телефонна компанія надає послуги населенню за різними тарифами, враховуючи категорію абонента. Так для абонентів звичайної категорії тариф встановлено в розмірі N коп. за хвилину, пільговій категорії абонентів тариф встановлено на M коп. менше від звичайного тарифу.

Скласти таблицю „Розрахунки за телефон” , яка містить інформацію: прізвище абонента , категорія пільг , номер телефону , тривалість розмов(у хвилинах) , плата за розмови .

1.4.8 Житлове управління нараховує сплату за користування ліфтом мешканцями за встановленими тарифами. Існує дві категорії споживачів. Відомо, що для споживачів 1 категорії пільг сплата встановлена у розмірі N₁ коп. на одного мешканця, для споживачів 2 категорії – на N₂ коп. менше. Крім того, мешканці першого поверху за ліфт не сплачують.

Скласти таблицю „Сплата за користування ліфтом” , яка містить інформацію: номер квартири , прізвище споживача , кількість мешканців , номер поверху , категорія пільг , до сплати .

1.4.9 Підприємство виготовляє деталі двох найменувань, на виготовлення яких існують різні норми та розцінки . Для працівників, які виготовляють ці деталі у разі перевищення норми встановлена додатково премія у розмірі N% від заробітку.

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

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

1.4.10 Для населення встановлена норма споживання електроенергії у розмірі – М кВт/г за місяць, за тарифом – N коп. за одиницю спожитої електроенергії. Відомо, що існує дві категорії споживачів електроенергії. Споживачам першої категорії сплата встановлена за звичайним тарифом, для другої категорії: за нормою споживання сплата становить 50% тарифу, поза нормою – за тарифом.

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

1.4.11 Цех виготовляє деталі одного типу. Відомо, що робітники 1 розряду за кожну виготовлену деталь отримують N₁ коп., 2 розряду – N₂ коп., 3 розряду – N₃ коп.

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

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

1.4.12 Відомо, що мінімальний прожитковий мінімум становить Х крб. Якщо на одного члена сім’ї робітника приходиться менше прожиткового мінімуму, то матеріальна допомога на кожну дитину становить У крб.

Скласти таблицю „Допомога” нарахувань матеріальної допомоги робітникам, яка містить такі відомості: прізвище робітника , заробітну платню , кількість дітей , додатковий прибуток (наприклад, заробіток жінки, або чоловіка) , сума на одного члена сім’ї, розмір допомоги кожному робітнику.

1.4.13 Відомо, що податківець, у якого немає дітей, сплачує податки за бездітність в розмірі N₁ % від прибутку, у якого не більше 2-х дітей – N₂ % від доходу, сім’я, в якій 3 і більше дітей, звільняється від цього податку.

Скласти таблицю „Податок” нарахування податків за бездітність, яка містить відомості: прізвище податкоплатника , кількість дітей , прибуток , розмір податку за бездітність.

Визначити загальну суму податку , найбільший прибуток та його податок .

1.4.14 Туристична фірма на період шкільних канікул встановила додаткові пільги. Для дітей до п’ятирічного віку встановлені знижки у розмірі 70 % від вартості путівки, до десятирічного віку 50 % , старші діти пільг не мають.

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

1.4.15 Продовольчий склад під час поточної інвентаризації для продуктів першої категорії встановив слідуючи нормативи: якщо термін зберігання не закінчився, ціна зменшується на 30% , якщо товар прострочений – його списують . Для продуктів другої категорії: якщо термін зберігання не закінчився , ціна зменшується на 50 % , якщо товар прострочений – його списують.

Скласти таблицю „Нова ціна”, яка містить відомості: назва продукту , категорія , ціна за одиницю , дата зберігання , нова ціна (в цій графі вказувати ціну , або інформацію про списання).

1.4.16 Відомо, що абонентська плата за телефон складає N крб. за місяць, до якої входить плата за М хвилин міських розмов. Кожна хвилина розмов поза норму встановлює N₁ коп. Воїни-ветерани сплачують по тарифу тільки розмови поза нормою.

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

1.4.17 Аптекоуправління встановило дві категорії населення і два типи ліків. Хворі 1 категорії сплачують N₁₁ % за ліки 1 типу , N₁₂ % за ліки 2 типу ; хворі 2 категорії сплачують N₂₁ % за ліки 1 типу , N₂₂ % за ліки 2 типу .

Скласти таблицю „Ліки” розрахунку за ліки , яка містить інформацію: прізвище хворого , категорія пільг , тип ліків , ціна ліків , сума до сплати .

ЛАБОРАТОРНА РОБОТА №2

Ділова графіка в Excel та її використання

2.1 Теоретичні відомості

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

2.1.1 Створення діаграми

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

Кликніть по кнопці Мастер диаграмм, яка розташована на панелі інструментів, та виконуйте інструкції Майстра . Побудова діаграми складається з 4-х етапів.

Етап перший – Тип діаграми.

На цьому етапі необхідно обрати один з можливих типів діаграм. Спочатку оберіть його у вікні Тип . Після вибору якогось типу в правому вікні Вид з’являється зображення різних видів діаграм цього типу. Натисніть на ліву кнопку миші, обираючи бажаний вид діаграми, а потім натисніть на кнопку „Далее”. Після цього почнеться другий етап. Якщо ви побажаєте змінити тип діаграми, то можна буде повернутися до попереднього етапу, натиснувши кнопку „Назад”.

Етап другий – Джерело даних (источник данных).

Якщо перед викликом Мастера диаграмм , ви виділили дані для побудови діаграми, то в полі Диапазон вже записані їх адреси. Натисніть на кнопку Свернуть/Развернуть в цьому полі. Вікно Мастера щезне і можна буде перевірити або змінити виділений діапазон, орієнтуючись на зображену діаграму.

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

Етап третій – Параметри діаграми.

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

Етап четвертий – Розміщення діаграми.

Оберіть лист, на якому буде розташована діаграма, та натисніть кнопку “Готово” .

2.1.2 Зміна зовнішнього вигляду діаграми

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

Можливості для внесення змін до діаграми надає й панель інструментів Диаграммы , яка з’являється після виділення діаграми. Якщо ця панель відсутня, її можна додати за допомогою пункту меню Вид→Панель инструментов→Диаграммы або натиснути на ліву кнопку мишки, коли вона знаходиться на будь-якій панелі інструментів, і обрати пункт Диаграммы .

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

2.1.3 Зміна формату виділеного елементу

Виділіть Область построения диаграммы , а в ній будь-який ряд. Потім оберіть кнопку Формат рядов данных та змініть колір елементів ряду. За допомогою вкладки Порядок рядов змініть їх порядок, спробуйте обрати різні варіанти вкладки Подписи данных . Виділіть мишею найбільший елемент ряду та змініть колір тільки цього елементу.

Виділіть ось ОY , на вкладці Шрифт змініть розмір та колір шрифту, на вкладці Шкала змініть ціну поділки, за допомогою вкладки Выравнивание змініть орієнтацію надписів, додайте Таблицу данных .

2.1.4 Добавка та зміна тексту

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

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

Додавання нових даних

Виділіть діаграму, а потім оберіть в меню Диаграмма пункт Исходные данные , вкладку Ряд , кнопку Добавить . Тепер у вікні Имя вкажіть ім’я цього ряду. У вікні Значения вкажіть адреси клітин нового ряду. Найпростіше зробити це за допомогою миші, згорнувши вікно та виділивши дані в таблиці Excel.

2.1.6 Побудова ліній тренду

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

Для побудови ліній тренду необхідно виконати такі дії:

а) виділити діапазон даних (B10:C19), побудувати гістограму;

б) обрати пункт „Добавить линию тренда” в меню Диаграмма або в контекстному меню, яке з’являється після натискання правої кнопки миші;

в) на вкладці Тип оберіть будь-який тип, наприклад, „Полиномиальная 4 степени”;

г) на вкладці Параметры поставте позначку на текст „показывать уравнение на диаграмме“ та замовте „прогноз вперед на 1 периодов“ ;

д) натисніть кнопку ОК .

Лінія тренду з’явиться на екрані, як показано на рис. 2.1 .

Для видалення лінії тренду необхідно її виділити та натиснути клавішу Delete .

Рисунок 2.1 – Діаграма з лінією тренда

Побудова кругової діаграми в MS Excel розглянута на прикладі в лабораторній роботі №1.

Завдання

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

Приклад. Побудувати таблицю значень функції

y = cos(x) , x [0; π] ,

де n = 10 – кількість розподілу проміжку,

h = ( –крок зміни x ,

який дорівнює π/10 , тобто x приймає значення арифметичної прогресії:

0 ; π/10 ; 2π/10 ; ... ; π .

Для побудови таблиці розкрийте наступний вільний лист. Створимо таблицю початкових значень, як на рис. 2.2 . Введемо до клітин наступний текст: до А1 – Хн , до В1 – Хк , до С1 – n , до D1 – hх . В клітину А2 введіть початкове значення х , тобто 0 , в клітину В2 – значення =ПИ() , в клітину С2 – 10 , а в D2 необхідно ввести формулу =(B2-A2)/C2 .

Рисунок 2.2 – Таблиця початкових значень

До діапазону клітин А5:А15 введемо значення змінної х одним зі способів.

Перший спосіб. До клітини А5 вводимо початкове значення х (=A2) . Обираємо команду Правка→Заполнить→Прогрессия і у вікні діалогу ставимо Размещение (по столбцам) , Тип (арифметическая) , Шаг – (0,314) , Предельное значение – (3,1415) . Після натиснення кнопки ОК , отримаємо заповнений діапазон А5:А15.

Другий спосіб. До клітини А5 введемо формулу =А2 , до клітини А6 – формулу =А5+$В$2 . Виділимо клітину А6 і, використовуючи маркер заповнення , протягнемо до А15, отримаємо числовий ряд потрібної довжини. Цей спосіб має переваги, тому що маючи зв’язок з даними початкової таблиці, дозволяє в автоматичному режимі перераховувати не тільки значення функції, а і значення аргументу.

Заповнюємо діапазон клітинок В5:В15 значенням функції Y . До клітини В5 введемо формулу =соs(A5) .

При введенні формул до клітинки відпрацюйте всі режими: з клавіатури, Мастера функций, відповідної кнопки панелі інструментів Стандартная .

Виділимо клітинку В5, використовуючи маркер заповнення, завершимо процес табулювання функції.

Розглянемо приклад табулювання функції

,

при х є [0;π] , крок зміни х = π/10 .

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

До діапазону С5:С15 вводимо значення z. До клітини С3 вводиться формула, яка використовує логічну функцію ЕСЛИ :

= ЕСЛИ (А5<=ПИ()/2; sin(A5)^2; cos(A5^2)).

Результати розрахунків та графіки представлені на рис.2.3 .

Рисунок 2.3 – Результати розрахунків

2.3 Контрольні питання

2.3.1 Побудова графіків та діаграм в MS Excel.

2.3.2 Які етапи побудови діаграм Ви знаєте? Їх характеристика.

2.3.3 З яких елементів складається діаграма?

2.3.4 Наведіть приклади деяких видів діаграм. Побудова „Точечного” графіка.

2.3.5 Що таке лінія тренду і як її побудувати?

2.3.6 Як виділити діаграму або якийсь конкретний елемент?

2.3.7 Як змінити розмір діаграми та її розташування?

2.3.8 Як коригувати зовнішній вигляд діаграми? Зміна кольору, формату ліній, назв для заголовків таблиці даних і т. д. в MS Excel.

2.4 Варіанти індивідуальних завдань

Для кожного варіанту:

а) побудувати графік функції y=f(x) , де х є [a;b] , n=20 ,

значення змінних a , b підібрати таким чином, щоб функція була визначена;

б) побудувати графік параметрично заданої функції y=f(x) , де

;

в)побудувати графік поверхні z=f(x,y) ,

де х є [-2;2] , ∆x = 0,1 ;

y є [-2;2] , ∆y = 0,1 ;

г) створити рисунок через координати об’єктів і їх математичні формули, тобто побудувати графік складної функції.

2.4.1

а) ; б) ;

в) ; г)

2.4.2

а) ; б) ;

в) ; г)

2.4.3

а) ; б) ;

в) ; г)

2.4.4

а) ; б) ;

в) ; г)

2.4.5

а) ; б) ;

в) ; г)

2.4.6

а) ; б)

в) ; г)

2.4.7

а) ; б)

в) ; г)

2.4.8

а) ; б) ;

в) ; г)

2.4.9

а) ; б) ;

в) ; г)

2.4.10

а) ; б) ;

в) ; г)

2.4.11

а) ; б) ;

в) ; г)

2.4.12

а) ; б) ;

в) ; г)

2.4.13

а) ; б) ;

в) ; г)

2.4.14

а) ; б) ;

в) ; г)

2.4.15

а) ; б) ;

в) ; г)

2.4.16

а) ; б) ;

в) ; г)

2.4.17

а) ; б) ;

в) ; г)

2.4.18

а) ; б) ;

в) ; г)

2.4.19

а) ; б) ;

в) ; г)

2.4.20

а) ; б) ;

в) ; г)

2.4.21

а) ; б) ;

в) ; г)

2.4.22

а) ; б) ;

в) ; г)

2.4.23

а) ; б) ;

в) ; г)

2.4.24

а) ; б) ;

в) ; г)

2.4.25

а) ; б) ;

в) ; г)

2.4.26

а) ; б) ;

в) ; г)

2.4.27

а) ; б) ;

в) г)

2.4.28

а) ; б) ;

в) г)

2.4.29

а) ; б) ;

в) ; г)

2.4.30

а) ; б) ;

в) ; г) .

ЛАБОРАТОРНА РОБОТА №3

Робота з масивами. Функції для роботи з матрицями

3.1 Теоретичні відомості

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

Правила для формул масиву:

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

б) в рядку формул наберіть потрібну формулу обробки вихідного масиву;

в) для фіксації вводу формули масиву натисніть Ctrl+Shift+ Enter. (Excel поставить формулу у фігурні дужки, вказуючи тим самим, що вона є формулою масиву);

г) не треба вводити фігурні дужки самостійно (Excel розглядатиме Ваше введення як текстове значення!);

д) не можна редагувати, очищати, переміщувати, вставляти, видаляти окремі клітинки в діапазоні масиву;

е) можна копіювати клітинки з діапазону масиву, призначати різні формати окремим клітинкам в масиві;

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

В Excel є спеціальні функції для роботи з матрицями:

МОБР ( ) (MINVERSE) – зворотна матриця;

МОПРЕД ( ) (MDETERM) – визначник матриці;

МУМНОЖ ( ; ) (MMULT) – матричний добуток двох матриць;

ТРАНСП ( ) (TRANSPOSE) – транспонування матриці.

При цьому слід пам’ятати, що “:” – це роздільник діапазону, а “;” – роздільник аргументів функції, яка застосовується.

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

Приклад 1. Введіть два числа до A1:F1. Виділіть область A2:F2, такого ж розміру, як і перший масив.

Формула добутку масиву А1:Е1 на число 3: {=А1:Е1*3} . Результат займе виділений горизонтальний діапазон масиву.

Приклад 2. Введіть набір чисел до масиву А4:А9 і до масиву В4:В9. Виділіть область С4:С9. Формула поелементного добутку двох масивів: {=А4:А9*В1:В7}. Результат займає виділений вертикальний діапазон масиву.

Приклад 3. Введіть числа до масиву E4:F5 та E7:F8, виділіть область E10:F11. Формула добутку двох матриць: {=МУМНОЖ (E4:F5; E7:F8)} . Результат займе виділений діапазон.

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

В цій роботі необхідно розв’язати систему лінійних рівнянь методом Крамера та в матричний спосіб.

Нагадаємо, що система лінійних рівнянь

в матричному вигляді записується: Ах=В , де

Відповідно до правила Крамера розв’язання системи:

,

де ∆ – визначник матриці А ;

∆х₁ – визначник матриці

. . .

∆х_п – визначник матриці

Відповідно до метода зворотної матриці, розв’язок системи повинен бути знайдений за формулою:

Х=А^-1 В ,

де А^-1 – зворотна матриця доматриці А .

3.2 Варіанти індивідуальних завдань

Для кожного варіанту:

а) знайти обернену матрицю до матриці А ;

б) знайти визначники матриці А , ∆х1 , ∆х2 , ∆х3 , ∆х4 ;

в) обчислити значення квадратичної форми

Z=BT∙AT∙A∙B ,

де А^Т , В^Т – транспоновані матриці А , В відповідно;

г) розв’язати систему лінійних рівнянь за формулами Крамера і за допомогою зворотної матриці.

При створенні формул назначити підходящі імена відповідним діапазонам клітин.

Скласти перевірку отриманого розв’язку.

3.2.1	3.2.2
3.2.3   3.2.5   3.2.7   3.2.9   3.2.11 3.2.13   3.2.15   3.2.17   3.2.19   3.2.21   3.2.23 3.2.25   3.2.27   3.2.29	3.2.4   3.2.6   3.2.8   3.2.10   3.2.12 3.2.14   3.2.16   3.2.18   3.2.20   3.2.22   3.2.24 3.2.26   3.2.28   3.2.30

3.3 Контрольні питання

3.3.1 Як надати імена клітинкам, діапазону клітинок та як їх потім використовувати у формулах?

3.3.2 Чим відрізняється звичайна формула від формули масиву?

3.3.3 Як виконати редагування даних масиву в ЕТ?

3.3.4 З якими функціями для роботи з масивами Ви познайомилися?

3.3.5 Які дії з клітинами масиву можна робити , а які ні?

3.3.6 Чи можна самостійно вводити { } при наборі формули масиву?

ЛАБОРАТОРНА РОБОТА № 4

Використання функцій Еxcel для роботи з нормативно-довідковими даними

4.1 Теоретичні відомості

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