Технологія створення, редагування, форматування електронної таблиці та діаграми в середовищі MS Excel

ВСТУП

Електронні таблиці - це будь-який набір даних, організованих у формі двомірної таблиці, над якими можна проводити двомірні обчислення, а їх результати представляти як в числовому, так і в графічному вигляді. В електронну таблицю можуть входити текстові фрагменти, числові дані, графічні зображення. Відмінність електронної таблиці від звичайної полягає в тому, що над даними, які знаходяться в ній, можна проводити обчислення за спеціально виведеними формулами. Результати обчислення формул можна представити у вигляді графіків та діаграм, що значно полегшує сприйняття числових даних. Для обробки електронних таблиць використовуються спеціальні прикладні програми. Їх називають табличними процесорами. До найбільш популярного на даний час табличного процесора можна віднести Microsoft Excel. Даний програмний продукт має досить велику історію розвитку, починаючи з 1984 року, від примітивних перших версій для операційної системи MS-DOS до світового лідерства в даній галузі використання комп’ютерної техніки. Широкої популярності Microsoft Excel набув починаючи з версії 4.0. В 1993 році створено Microsoft Excel 5.0, яка стала значним етапом в розвитку програм для обробки електронних таблиць. В цій версії вперше введено структуру робочої книги. Раніше файл електронних таблиць являв собою одну велику таблицю, в якій розміщувалась вся інформація. При великій кількості даних така структура значно ускладнює пошук та обробку потрібної інформації. До основних призначень Microsoft Excel належать: обробка табличних даних, вирішення науково-технічних задач, відображення даних у табличному вигляді, робота з базами даних, імпорт та експорт даних в інші системи та мережі.

Microsoft Excel складається з «Робочої книги», яка представляє собою набір декількох електронних таблиць, об'єднаних в одному файлі. Кожна така таблиця називається робочим аркушем. 7]

1. ТЕОРЕТИЧНІ ПОЛОЖЕННЯ

1.1 Робота з майстром функцій Обчислення в таблицях виконуються за допомогою формул. Формула може складатися з математичних операторів, значень, посилань на вічко й імена функцій. Результатом виконання формули є деяке нове значення, що міститься у вічку, де знаходиться формула. Формула починається зі знаку рівняння «=». У формулі можуть використовуватися арифметичні оператори +, -, *, /. Порядок обчислень визначається звичайними математичними законами.

Приклади формул: =(А4+В8)*С6, =F7*С14+B12.

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

Посилання на вічка бувають двох типів:

1. відносні, коли вічка позначаються відносним зсувом від вічка з формулою (наприклад: F7).

2. абсолютні, коли вічка позначають координатами таблиці в сполученні зі знаком $ (наприклад: $F$ 7).

При копіюванні формул відносні посилання змінюються на розмір переміщення. Для звертання до групи вічок використовують спеціальні символи:

: (двокрапка) — формує звертання до вічок, що знаходяться між двома операндами. Наприклад: С4: С7 звертається до вічок С4, С5, С6, С7.

; (крапка з комою) — позначає об'єднання вічок. Наприклад, D2: D4;D6:D8 — звертання до вічок D2, D3, D4, D6, D7, D8.

Для уведення формули у вічко потрібно увести знак '=' і необхідну формулу для обчислення. Після натискання клавіші Enter у вічку з’явиться результат обчислення. Формула з’являється в рядку редагування при виділенні вічка, що містить формулу.

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

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

1.2 Робота з майстром діаграм Робота з об'єктами. Excel дозволяє вставити в робочий лист об'єкти, створені іншими програмами (документи Word, малюнки графічного редактора Paint та ін.)/ Для вставки такого об'єкта слід запустити програму, яка працює з цим об'єктом. Це можна зробити як із середовища Windows-95, так і з середовища Excel. Для запуску програми з середовища Excel слід виконати команду Вставка/Объект. При цьому відкривається вікно Вставка объекта. Вкладка Создание цього вікна (мал. 1) дозволяє вибрати тип об'єкта. Після вибору типу запускається відповідна програма, яка працює з об'єктом вибраного типу. Програма, запущена в середовищі Windows, може переслати об'єкт у буфер обміну або записати його у файл. Для вставки об'єкта з буфера обміну слід виконати команду Правка/Вставить.

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

В Excel є панель інструментів Рисование, за допомогою якої можна створювати нескладні малюнки. Про призначення кнопок цієї панелі можна дізнатися з довідкової системи.

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

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

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

Побудова діаграми. Діаграма може будуватись на активному листі або на новому.

Для побудови діаграми на активному листі слід скористатися майстром діаграм, натиснувши кнопку Мастер диаграмм на панелі інструментів Стандартная. Курсор при цьому перетворюється в мініатюрну діаграму. Натиснувши ліву кнопку миші, слід тягнути курсор доти, поки рамка під діаграму не набере потрібного розміру. При відпусканні кнопки миші на екрані з’явиться вікно діалогу Мастер диаграмм — шаг 1 из 5. Майстер діаграм створює діаграму в загальному випадку за п’ять кроків (для деяких типів діаграм кількість кроків може бути меншою).

Для створення діаграми на новому листі слід виконати команду Вставка/Диаграмма/На новом листе. В книзі створюється новий лист діаграм з іменем Диаграмма, у верхньому рядку якого виводиться панель інструментів Диаграмма, в якій є кнопка Мастер диаграмм. При натискуванні цієї кнопки на екран виводиться вікно діалогу Мастер диаграмм — шаг 1 из 5. Для кожного кроку виводиться своє вікно діалогу, в якому можна задати параметри для побудови діаграми (вікна діалогу Мастер диаграмм показані на рис. 1).

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

Після вказання діапазону слід натиснути кнопку Далее, і на екран виведеться вікно діалогу Мастер диаграмм — шаг 2 из 5. У цьому вікні слід вибрати одну з 15 типів діаграм (вибрана діаграма зображується ярким кольором). У вікні діалогу Мастер диаграмм — шаг 3 из 5 вибирається вид діаграми даного типу.

У вікні діалогу Мастер диаграмм — шаг 4 из 5 виводиться зразок діаграми. На основі аналізу виділеного діапазону майстер сам визначає, як розміщені ряди даних. Якщо діапазон містять колонку текстових значень, то майстер сприймає, що ряди даних розміщені по рядках діапазону. Якщо діапазон містить рядок текстових значень, то майстер сприймає, що ряди даних розміщені по колонках діапазону. Якщо ж діапазон містить і колонку, і рядок текстових значень або зовсім не містить текстових значень, то майстер визначає ряд даних за кількістю клітин у рядку і колонці виділеного діапазону (якщо рядок діапазону містить більше клітин, ніж колонка, то ряди даних розміщуються по рядках, а якщо менше — то по колонках). 4]

1.3 Визначення бази даних. Типи баз даних. Бази даних в MS Excel

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

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

Створення баз даних в EXCEL. Розмір бази даних. Збереження бази даних.

Розмір баз даних в Excel обмежений числом рядків на робочому аркуші - 65 536. Незважаючи на ці й інші обмеження, засоби керування даними в Excel — це потужний інструмент для створення невеликих баз даних і роботи з вибіркою записів з великих баз даних.

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

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

— Імена полів розташовуються у верхній частині стовпців.

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

1.4 Фільтрація даних в MS Excel

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

Excel має два засоби для фільтрації даних — автофільтр і розширений фільтр. Щоб установити автофільтр, треба помістити курсор усередину таблиці і вибрати в меню Данные опції Фильтрі Автофильтр. В результаті біля заголовків стовпців з’являться кнопки списків, що розгортаються, з яких можна вибрати наступні опції:

(Все) — знімає фільтр із даного стовпця і забезпечує вивід рядків із будь-якими значеннями в даному стовпці;

(Первые 10…) — дозволяє відфільтрувати задану кількість або заданий відсоток найбільших або найменших елементів даного стовпця;

(Условие…)дозволяє задати одну або дві умови фільтрації у формі рівності або нерівності

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

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

Для зняття автофільтра потрібно виконати ті ж дії, що і при його встановленні.

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

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

У комірки допоміжної таблиці можна включати і нерівності. Наприклад, вираз <100 забезпечить фільтрацію чисел, менших ста.

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

Для накладення розширеного фільтра, необхідно помістити курсор усередині основної таблиці і вибрати в меню Даныеопції Фильтр і Расширенный фильтр. При цьому в поле Исходный диапазон (рис.3) буде автоматично занесено діапазон комірок вхідної таблиці. Для занесення діапазону комірок, допоміжної таблиці, у поле Диапазон условий, потрібно установити курсор у це поле, а потім виділити мишкою допоміжну таблицю разом з шапкою.

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

Для зняття розширеного фільтра потрібно вибрати в меню Данные опції Фильтр і Отобразить все. 3]

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

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

— легко перетворюються;

— дозволяють виконувати автоматичний добір інформації;

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

Опишемо покроковий процес створення зведеної таблиці. Для цього:

1. Виберіть команду Дані | Зведена таблиця. На екрані з’явиться перше вікно Майстра зведених таблиць (рис.3).

Рисунок 3 — Перше вікно «Майстра зведених таблиць»

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

3. На екрані з’явиться друге вікно Майстра зведених таблиць (рис. 5). На цьому кроці ви повинні вказати діапазон, що містить дані, по яких буде будуватися зведена таблиця. Якщо джерело даних знаходиться в іншій робочій книзі, то необхідно скористатися кнопкою Огляд. Отже, після введення посилання на діапазон даних натисніть кнопку Далі. На екрані з’явиться третє вікно Майстра зведених таблиць (рис. 4).

Рисунок 4- Друге вікно «Майстра зведених таблиць»

Рисунок 5 — Третє вікно «Майстра зведених таблиць»

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

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

Рисунок 6- Вікно «Майстер зведених таблиць і діаграм — макет»

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

Рисунок 8 — Вікно Обчислення поля зведеної таблиці

У списку Операція перераховані припустимі операцій: Сума, Кількість значень, Середнє, Максимум, Мінімум, Добуток, Кількість чисел, Зміщене відхилення, Незміщене відхилення, Зміщена дисперсія; Незміщена дисперсія. У даному випадку виберіть Сума. Список, що розкриває, Додаткові обчислення дозволяє розширити безліч припустимих операцій. Його значення: Ні, Відмінність, Частка, Приведена відмінність, З наростаючим підсумком у полі, Частка від суми по рядку, Частка від суми по стовпці, Частка від загальної суми, Індекс. У даному випадку виберіть Ні і натисніть кнопку ОК. Відбудеться повернення у вікно Майстер зведених таблиць і діаграм: макет.

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

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

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

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

Рисунок 9 — Вікно Майстер зведених таблиць і діаграм — макет зі структурою майбутньої зведеної таблиці

Встановимо параметри зведеної таблиці. Як правило, ті значення параметрів, що встановлені за замовчуванням і є оптимальними, їх не треба змінювати. Але для того, щоб краще розібратися з тим, як набудовується зведена таблиця, натисніть кнопку Параметри. На екрані відобразиться вікно Параметри зведеної таблиці (рис.10). У вікні, під полем Ім'я, у яке вводиться ім'я зведеної таблиці, знаходяться дві групи параметрів Формат і Дані. З групи Формат відзначимо тільки прапорці, загальна сума по рядках і загальна сума по стовпцях, що визначають, чи необхідно підводити підсумки по рядках і стовпцях. Дамо характеристику прапорця зберегти дані разом з таблицею групи Дані. При побудові зведеної таблиці всі дані копіюються в сховану кеш-пам'ять. Зведена таблиця відображає зміст цієї кеш-пам'яті, а не вихідного діапазону даних. Якщо вже після того, як зведена таблиця побудована, ви зміните дані, на основі яких вона була побудована, то це не приведе до автоматичної зміни даних у зведеній таблиці. Зведена таблиця не є динамічною таблицею, що автоматично обновлюється при модифікації даних, на основі яких вона побудована. Для відновлення зведеної таблиці потрібно виділити будь-яку чарунку зведеної таблиці і вибрати команду Дані /Обновити дані. При цьому в кеш-пам'ять будуть знову занесені дані, на основі яких будується зведена таблиця. Прапорець зберегти дані разом з таблицею визначає, чи буде в кеш-пам'ять зберігатися старі дані, при змінах зведеної таблиці чи її обновлені.

Рисунок 10 — Вікно Параметри зведеної таблиці

5. У групі Помістити таблицю в (див. рис 6) є два перемикачі новий лист і існуючий лист, що задають місце розташування зведеної таблиці. Якщо обраний перемикач існуючий лист, то в поле треба привести посилання на верхню ліву чарунку діапазону, де буде розташовуватися таблиця. У нашому випадку виберіть перемикач новий лист і натисніть кнопку Готово. 5]

2. ПРАКТИЧНА ЧАСТИНА

2.1 Опис функцій, які використані в курсовій роботі

Функція СУММ. Щоб швидко і легко підрахувати суму в Excel, необхідно усього лише задіяти функцію суми, натиснувши кнопку з зображенням знака суми, або з Майстра функцій можна й вручну вдрукувати ім'я функції після знака рівності. Після імені функцій треба відкрити дужку, ввести адреси областей і закрити дужку. У результаті формула виглядатиме так: =СУММ (B1:B3;C4:C5;D2).

Функція СРЗНАЧ. Виділіть комірку, де повинно розташовуватися середнє значення, і клацніть по піктограмі Мастер функций.

Відчиниться діалогове вікно Мастер. У правому полі клацніть за назвою функції СРЗНАЧ. Потім натискуванням по командній кнопці Далее перейдіть до другого кроку роботи з Конструктором.

На другому кроку потрібно зазначити аргументи цієї функції. Тут також для маркірування можна або використовувати мишу в сполученні з клавішею [Ctrl], або вводити адресу з клавіатури.

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

Функція МИН і МАКС. Функція МИН повертає найменше значення з набору даних і має такий синтаксис:

=МИН (число1;число2;…)

Число1, число2,…- це від 1 до 30 чисел, серед яких шукається мінімальне значення.

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

Функція МАКС повертає найбільше значення з набору даних і має такий синтаксис:

=МАКС (число1;число2;…)

Значення аргументу задаються таким же уявою як і у функції МИН.

2.2 Опис діаграм, які побудовані в курсовій роботі

Рисунок 11 — Основна таблиця Лінійна діаграма (графік) — служить для того, щоб прослідкувати за зміною кількох величин при переході від однієї точки до іншої. На рисунку 13 зображена лінійна діаграма з 5 завдання (12 варіант)

.

Рисунок 12 — Лінійна діаграма (графік)

Кругова діаграма — служить для порівняння кількох величин у одній точці. Особливо корисна коли величини в сумі складають 100%.Кругова діаграма розміщується на крузі. Круг=360*. Для того, щоб створити діаграму в Excel виділяємо клітинки А1: В3, створюємо кругову діаграму.

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

Приклад цієї діаграми можна побачити на рисунку 14, яка була використана в курсовій роботі.

Рисунок 13 — Кругова діаграма

2.3 Опис створеного в MS Excel списку згідно з індивідуальним завданням

1.Створити таблицю Еxcel для ровзязання задачі «Розрахунок середніх затрат часу на одиницю однотипної продукції для підприємств галузі» .

2. Заповнити таблицю, ввести не менше 20 записів, що повторюються:

2.1 У поле «Вид продукції» ввести назви: шафа, стіл, стілець тощо.

2.2 У поле «Тимчасові витрати на одиницю продукції» ввести дані в діапазоні від 30 до 60 годин.

2.3 У поле «тимчасові витрати на всю партію» ввести дані від 2000 до 6000 годин .

3. Увести за допомогою функції ВПР до основної таблиці дані поля «Коефіцієнт збірки» (КЗ). Для цього необхідно створити довідкову таблицю з полями «Тимчасові витрати на всю партію» (ТВ) та «Коефіцієнт збірки» (КЗ).

Заповнити цю таблицю залежно від витрат на всю партію:

— ТВ до 3000 годин — 1,0.

— ТВ до 4000 годин — 1,1.

— ТВ до 5000 годин — 1,3.

— ТВ більше 5000 годин -1,5.

4. Визначити :

4.1. Кількість виготовлених виробів КВ=ОКРУГЛ (ТВ/(КЗ*ТО))

4.2 Сумарну кількість виготовлених виробів СКВ.

4.3 Сумарні тимчасові витрати на всю партію.

4.4 Середньо статистичний час виготовлення одиниці продукції:

СР=ТВ/КВ.

4.5 Максимальну та мінімальну кількість виготовлених виробів.

5. Побудувати лінійну діаграму за данними тимчасових затрат на партію.

На діаграмі розмістити заголовок та підписи осі Х.

6. Виконати сортування у порядку зменшення за днями тижня, видами продукції та тимчасовими витратами.

7. Скопіювати основну таблицю на аркуші 3,4,5.

8. Перейменувати аркуш 3 на РЕЗУЛЬТАТИ. Виконати підведення проміжних підсумків, визначивши сумарну кількість виготовлених виробів за видами продукції.

9.Побудувати кругову діаграму.

10.Перейменувати аркуш 4 на АВТОФІЛЬТР. За допомогою АВТОФІЛЬТРА відібрати дані про столи з тимчасовими витратами на 1 одиницю продукції від 45 до 55 годин.

11. Перейменувати аркуш 5 в РОЗШИРЕНИЙ ФІЛЬТР. За допомогою РОЗШИРЕНОГО ФІЛЬТРА відібрати дані про шафи або продукцію з кількістю виробів від 65 до 80 штук.

12. Розробити макет зведеної таблиці на аркуші 6.

13. Описати у пояснювальній записці до курсової роботи:

13.1 Основні операції, які застосовують для роботи з аркушами робочої книги Еxcel.

13.2 Порядок виконання фільтрації даних у таблицях Еxcel.

2.4 Використання автофільтру та розширеного фільтру в курсовій роботі

Наведення та пояснення отриманих результатів фільтрації.

В даній курсовій роботі було використано два види фільтрації: автофільтр та розширений фільтр.

За допомогою афто фільтра були відібрані дані про столи з тимчасовими витратами на 1 одиницю продукції від 45 до 55 годин.

Рисунок 14 — Використання автофільтра За допомогою розширеного фільтра були відібрані дані про шафи або продукцію з кількістю виробів від 65 до 80 штук.

Рисунок 15 — Застосування розширеного фільтра

2.5 Опис макету створеної зведеної таблиці. Наведення та пояснення отриманих результатів використання зведеної таблиці

Одним з найбільших потужних засобів Excel по роботі з базами даних є зведені таблиці. Зведені таблиці призначені для аналізу і для узагальнення інформації, яка зберігається в базі даних, на робочих аркушах, у зовнішніх файлах. Зведені таблиці є динамічними об'єктами, які дозволяють виводити інформацію з різними ступенями деталізації. Майстер зведених таблиць дозволяє створити таблицю з підбиттям проміжних підсумків за різними полями даних одночасно. Результат — підсумкова таблиця, яка узагальнює вхідні дані і дозволяє легко складати наочні звіти, а також будувати діаграми за цими даними. За допомогою зведеної таблиці дані таблиці, що аналізується, можливо вибірково представлятиу вигляді, який найкращим чином дозволяє відображати задежності між даними.

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

Рисунок 16 — Макет зведеної таблиці

Переваги зведеної таблиці перед засобами підведення проміжних підсумків полягає в можливості підведення підсумків за деякими параметрамти (рис.16).

електронний таблиця фільтр excel

Рисунок 17 — Застосування зведеної таблиці

Для створення зведеної діаграми необхідно подати команду Зведена діаграма, яка розташована на вкладці Вставка стрічці інструментів Таблиця (рис.17).

Рисунок 18 — Зведена діаграма

2.6 Відповіді на питання індивідуального завдання. Основні операції, які застосовують для роботи з аркушами робочої книги Еxcel

1 Запуск Microsoft Excel.

Існують кілька варіантів Microsoft Excel для Windows, подальший матеріал орієнтований на русифіковану версію 8.0. Для запуску програми Excel слід виконати команду Пуск/Програми/Місгоsoft Excel Windows-95, після чого на екрані з’явиться вікно процесора Excel.

2 Вікно процесора Excel

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

Рядок заголовка (верхній рядок вікна) містить назву програми «Microsoft Excel». Крайня ліва кнопка є кнопкою виклику управляючого меню. Праворуч розміщені відповідно кнопка згортання, відновлення та закриття вікна.

Рядок меню розміщується під вікном заголовка і включає такі пункти:

" Файл" — робота з файлами книг (створення, збереження, відкритті) файлів, друкування файлів книг);

" Правка" — редагування книг;

" Вид" -перегляд книг;

" Вставка" — вставка у аркуші книг малюнків, діаграм та інші типів даних;

" Формат" — форматування книг (встановлення параметрі форматів таблиць);

" Сервис" — сервісні функції (встановлення параметрі настройки Excel);

" Даннме" — робота з базами даних;

" Окно" — робота вікнами книг;

'' ?'' — виклик довідкової інформації.

Кожний пункт меню має вертикальне підменю. Для відкриття меню слід натиснути клавішу [Alt] або [FIO]. Робота з меню аналогічна тій що ми розглядали при вивченні Windows. 5. В Word існує ще один спосіб виклику команд. Клацання правою кнопкою миші на виділеному тексті, слові призводить до виведення на екран контекстного меню. Це меню містить команди, які можна застосувати до виділеного об'єкту.

Користувач має змогу відмітити останню введену команду, виконавши команду Правка/Отменить.

Панелі інструментів. Під рядком меню розміщуються звичайно панелі інструментів. Панелі інструментів — це рядок кнопок, при натискуванні на які виконується певна дія. Для натискування кнопки слід клацнути мишею по кнопці. При фіксації курсора миші на кнопці під нею з’являється її назва, а в рядку стану — коротка довідка про призначення кнопки. Ряд кнопок дублюють відповідні пункти меню. Однак користуватись кнопками панелі значно швидше і зручніше. Excel забезпечує користувача декількома панелями" інструментів. Для вибору потрібної панелі слід скористатися командою Вид/Панель инструментов. При цьому на екрані з’явиться вікно діалогу Панелін инструментов, у списку якого можна вибрати необхідні панелі. За замовчуванні ням Excel виводить на екран панелі інструментів Стандартная і Форма-тирование.

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

Рядок стану. Цей рядок розміщується у нижній частині вікна Excel. У цей рядок виводиться інформація про хід виконання деяких операцій.

Слід відзначити, що загальний вигляд вікна Excel і наявність деяких елементів залежать від параметрів, встановлених командами Сервис/Парамет-рьі і Вид.

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

3 Вікно діалогу.

Для виконання деяких команд потрібно вводити допоміжну інформацію. Так, для виконання команди Файл/Открыть необхідно вказати дисковод, каталог та ім'я файла. Для введення такої інформації використовуються вікна діалогу.

Вікно діалогу містить ряд елементів: кнопки, списки, прапорці, перемикачі, рядки введення. Ці елементи розміщуються за тематичними групами, які називають полями. Групи мають заголовки, що закінчуються двокрапкою. Перехід від групи до групи здійснюється або за допомогою миші, або при: натискуванні клавіші [ТАВ].

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

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

Кнопка ОТМЕНА (клавіша [Esc]) анулює всі зміни; діалог закінчується, але відповідна команда не виконується.

Крім кнопки ОК і ОТМЕНА в цьому вікні залежно від призначення конкретного вікна можуть бути й інші кнопки управління діалогом.

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

4 Вікна книг. Робота з аркушами книг.

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

робочі листи або просто листа листи діаграм;

листи макросів;

листи модулів, написаних мовою Visual Basic

листи діалогу.

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

Робочі листи — це електронні таблиці, що складаються з колонок і рядків Максимальне число колонок таблиці — 255, рядків — 16 384. Колонки позначаються зліва направо літерами: перші 26 — літерами, А … Z, наступні 26 — літерами AA—AZ і так далі до останньої, 255 колонки, яка позначається літерами IV.

Рядки позначаються зверху вниз цифрами від 1 до 16 384. На перетив колонки і рядка розміщуються клітинки. Позначення (адреса) клітинок складається з позначення колонки та рядка (В3).

В Excel може одночасно існувати кілька вікон книг. Для маніпуляцій вікнами використовують меню Окна. У кожний момент часу одне з вікон активним, воно виводиться на перший план і може закривати інші вікна. Користувач може встановлювати розмір і положення кожного вікна традиційними для Windows способами.

Вікно має ряд типових елементів:

Заголовок вікна розміщується зверху і включає ім'я книги. Ліворуч в імені розміщена кнопка виклику управляючого меню. Праворуч розміще: відповідно кнопки згортання, відновлення і закриття вікна.

Список листів книги розміщується ліворуч у нижньому рядку вікна. Цей список містить імена (ярлики) листів. Якщо книга включає багато листів, то список можна гортати за допомогою кнопок прокрутки, які розміщені ліворуч від списку. За замовчуванням робочим листам присвоюється ім'я Лист…, листам діаграм — ім'я Діаграмма … Один із листів книги є активним (його ім'я виводиться інверсним кольором).

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

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

2.7 Порядок виконання фільтрації даних у таблицях Еxcel

1 Упорядкування даних.

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

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

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

2 Сортування за одним параметром.

Якщо виникає потреба впорядкувати дані (розташувати в алфавітному порядку, поставити числа в порядку зростання/спадання), то для цього на панелі інструментів є кнопки:

Рисунок 18 — Сортування за зростанням Рисунок 19 — Сортування за спаданням

3 Сортування за кількома параметрами.

Для сортування за кількома параметрами можна скористатися командою меню Дані > Сортування…

Рисунок 20

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

4 Фільтрація даних. Автофільтр.

База даних може містити величезне число записів (у програмі Excel природною межею служить максимальне число рядків робочого аркуша — 65 536), тому не завжди треба відображати всі ці записи. Виділення підмножини загального набору записів називається фільтрацією.

Фільтрація — це виділення визначених записів, що задовольняють заданим критеріям.

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

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

Варіант Перші 10 дозволяє відібрати визначене число (чи відсоток) записів за яким-небудь критерієм.

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

ВИСНОВКИ

Інформаційні технології і системи — це перш за все інструмент, що служить для досягнення поставлених цілей шляхом координації виробничо-інформаційних процесів. Але просте володіння цим інструментом, як і будь-яким іншим, ще не гарантує успіху, в той час як його відсутність рівнозначна повному провалу. Тому головна відзнака сучасних інформаційних технологій і систем — не кількість засобів, витрачених на їх розробку і впровадження, а додана ними вартість. Щоб інвестиції приносили користь, необхідно вміти використовувати інформаційні технології, а не просто володіти ними. Володіння інструментом інформаційних технологій — необхідна, але ще не достатня умова успіху в бізнесі.

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

СПИСОК ВИКОРИСТАНИХ ДЖЕРЕЛ

1. А. М. Дибакова Інформатика та комп’ютерна техніка. Київ «Академвидавництво» 2002 р. 318 ст.

2. О.І. Пушкар Інформатика. Комп’ютерна техніка. Комп’ютерні технології. Київ «Академія» 2001 р. 694 ст.

3. В. Д. Руденко, А. М. Макарчук, М. А. Патланжоглу. Курс інформатики. Під ред. Мадзугона В.Н.К.:Фенікс, 1998 р.-368 с.

4. Коссак О., Юрчак І. Сім кроків до Microsoft Word. — Львів:СП" Бак", 1997.-84 с.

5. Бухвалов А. В. и др. Финансовые вычисления для профессионалов.- СПб.: БХВ-Петербург, 2001.-320с. ил.

6. Гарнаев А. Ю. Excel, VBA, Internet в экономике и финансах.;

7. Евдокимов В. В. и др. Экономическая информатика. Учебник для вузов. Под ред. Д.э.н., проф. В. В. Евдокимова. — СПб.: Питер, 1997. — 592с.