Створення електронних таблиць в середовищі Excel

Размещено на http://www.allbest.ru/

25

Размещено на http://www.allbest.ru/

Міністерство освіти і науки України

Сумський державний університет

Кафедра моделювання складних систем

КУРСОВА РОБОТА

з дисципліни Інформатика та комп'ютерна техніка

на тему

Створення електронних таблиць в середовищі Excel

Виконала:

студентка групи

Коротовських Є.Е.

СУМИ 2009

Завдання

Створити шаблон документа з ім'ям WORD_власне прізвище КУРСОВА опис шаблон для подальшого оформлення завдань з такими параметрами:

параметри сторінки:

- поля - 1,5 см. всі;

- орієнтація - книжкова;

- розмір аркуша - А5;

- нумерація - по центру, внизу;

параметри шрифту:

- назва - Times New Roman;

- розмір - 12 пт.;

параметри абзацу:

- вирівнювання - по ширині;

- абзацний відступ - 2см.;

- інтервал між рядками - 1см.;

заголовок :

- від тексту 3 інтервали;

- шрифт - розмір 16пт.;

- орієнтація - по центру;

- накреслення - напівжирний без крапок.

Зміст

1. Завдання на курсову роботу

2. Введення

3. Опис технології створення таблиці

3.1 Введення та редагування вмісту комірок

3.2 Форматування даних

3.3 Внесення формул в таблицю та їх копіювання

3.4 Використання в таблиці стандартних функцій

4. Опис технології створення діаграм та графіків

5. Фільтрація даних

5.1 Фільтрування даних за допомогою команди Автофильтр

5.2 Фільтрування даних за допомогою команди Расширенный фильтр

6. Сортування даних

7. Проміжні підсумки даних

8. Консолідація даних

9. Інструкція по використанню робочої книги

10. Список використаної літератури

Додатки

· Додаток А Таблиця з розрахунками

· Додаток Б Таблиця з формулами

· Додаток В Таблиця після сортування даних

· Додаток Г Таблиця після фільтрації даних (автофільтр)

· Додаток Д Таблиця після фільтрації даних (розширений фільтр)

· Додаток Е Таблиця з результатами консолідації даних

· Додаток К Таблиця з підсумками даних

· Додаток Л Графік

· Додаток М Гістограма

· Додаток Н Кругова діаграма

Завдання на курсову роботу

база даних таблиця еxcel

Завдання. Створити нову робочу книгу за шаблоном:

Вміст колонтитулів:

- верхнього - ім'я робочої книги та поточна дата;

-нижнього -прізвище, група, поточний час;

- шрифт Arial,14 пт.

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

1. дата поставок;

2. найменування постачальника;

3. найменування товару;

4. закупівельна ціна;

5. знижка (у % від Закупівельної ціни);

6. націнка (у % від Закупівельної ціни);

7. гуртова ціна;

8. роздрібна ціна;

9. кількість;

10. вартість.

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

Гуртова ціна = Закупівельна ціна - Знижка (грн.)

Роздрібна ціна = Закупівельна ціна + Націнка (грн.)

Вартість = Кількість * Роздрібна ціна, якщо Кількість не перевищує 10 одиниць -Вартість = Кількість * Гуртова ціна, якщо Кількість перевищує 10 одиниць.

Застосувати при розрахунку Вартості логічну функцію ЕСЛИ.

Створити форму для заповнення бази даних.

Самостійно заповнити створену базу даних 20 записами.

Найменування певних постачальників та окремих товарів слід декілька разів повторити.

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

Використовуючи команди авто фільтр, вивести:

* дані для кожної групи товарів;

* дані для кожного типу товарів, ціни на які більші (менші) зазначеного

числа.

Використовуючи команди розширеного фільтру, вивести дані:

* про окремі товари і постачальників;

* найменування товарів, ціни на які знаходяться у певному інтервалі;

* найменування товарів, ціни, які менші за середні.

Використовуючи підсумки, визначити:

мінімальні, максимальні та середні ціни для кожної групи товарів;

мінімальні і максимальні знижки та націнки для кожної групи товарів.

Використовуючи Майстер діаграм побудувати графік, кругову діаграму, гістограму (чинники, за якими будуються графік та діаграми, визначити самостійно).

Зберегти файл під ім'ям ЕхсеІ_власне прізвище1.

2. Вступ

Microsoft Excel (повна назва Microsoft Office Excel) -- це пакет прикладних програм, орієнтований на оброблення даних, поданих у табличній формі. Саме тому його часто називають табличним процесором, або електронною таблицею. Типові області застосування Excel:

· Завдяки тому, що лист Excel являє собою готову таблицю, Excel часто використовують для створення документів без усіляких розрахунків, що просто мають табличне представлення (наприклад, прайс-листи в магазинах, розклади);

· У Excel легко можна створювати різні види графіків і діаграм, які беруть дані для побудови з комірок таблиць (графік зниження ваги тіла за вказаний період від початку занять спортом);

· Його можуть використовувати звичайні користувачі для елементарних розрахунків (скільки витратив за цей місяць, що/кому/коли дав/взяв);

· Excel містить багато математичних і статистичних функцій, завдяки чому його можуть використовувати школярі і студенти для розрахунків курсових, лабораторних робіт;

· Сортувати табличні дані за алфавітом, за зростанням чи спаданням, за датами, місяцями і т.д.;

· Excel інтенсивно використовується в бухгалтерії -- у багатьох фірмах це основний інструмент для оформлення документів, розрахунків і створення діаграм. Природно, він має в собі відповідні функції;

· Формувати зведені таблиці, звіти і навіть карти з географічним прив'язуванням данних;

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

3. Опис технології створення таблиці

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

Рядок заголовка має назву додатка - Microsoft Excel та ім'я робочої книги - файла.

Головне меню Excel має у своєму складі 9 спадних меню: Файл, Правка, Вид, Вставка, Формат, Сервис, Данные, Окно і Справка. Кожне з них виконує певні функції, має кілька команд керування процесором і активізується за допомогою миші або натисненням на клавішу <Alt> разом із клавішею літери, підкресленої в його імені.

Панель інструментів. Це групи піктограм - кнопок, за допомогою яких виконується швидкий і наочний вибір і виконання команд. У Excel використовується ряд системних панелей інструментів: «Стандартна», «Форматування», «Діаграми», «Малювання», «Зведені таблиці», «Елементи управління» і ін. Допускаються зміни стандартних панелей, створення призначеної для користувача панелі з ім'ям «Нова».

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

Основою Excel є електронна таблиця. Стандартна таблиця Excel має 65 536 рядків і 256 стовпців. Стандартна ширина стовпця, що призначається системою автоматично, дорівнює 9 символам. Вона може змінюватись користувачем у межах від 1 до 32 767 символів.

Автоматично початкова робоча книга складається з 3 аркушів з іменами «Лист 1», «Лист 2», «Лист 3». Їх «перегортають» за допомогою однойменних кнопок - ярликів, розміщених у нижній частині екрана. Доцільно всі системні імена аркушів поміняти на більш інформативні для користувача імена. Стосовно першого аркуша цю процедуру виконують так:

· Вибирають ярлик «Лист 1», який подвійним клацанням мишею виділяють темним фоном;

· В ярлик вводять нове ім'я і натискають клавішу <Enter>.

Створюємо нову робочу книгу:

Файл > Создать > Новая книга

Задаємо параметри сторінки:

Файл > Параметри страницы

У діалоговому вікні встановлюємо потрібні параметри

Орієнтація - альбомна, розмір паперу - А4.

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

Необхідну кількість аркушів можна додати за допомогою операції Вставка Лист.

3.1 Введення та редагування вмісту комірок

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

Створюємо робочий аркуш «Розрахунки». Виділяємо діапазон комірок А1:J1 шляхом «буксирування» курсору миші, тобто перетягуванням його по екрані при натиснутій лівій клавіші миші. Об'єднуємо комірки та встановлюємо вирівнювання за центром. Поява жирної рамки свідчить про те, що комірка стала активною і до неї можна вводити дані. Вводимо заголовок таблиці «Облік товару для магазину будівельних матеріалів».

Далі послідовно вводимо назви стовпців таблиці: № п/п, дата поставок, найменування постачальника, найменування товару, закупівельна ціна, знижка, націнка, гуртова ціна, роздрібна ціна, кількість, вартість, акційна ціна, вартість у діапазон комірок А2:J2.

Стовпчик № п/п заповнюємо таким чином:

· Виділяємо комірку А3 та вводимо до неї початковий номер 1;

· Виконуємо команди Правка > Заполнить> Прогрессия та у діалоговому вікні встановлюємо параметри:

Далі послідовно заповнюємо початковими даними діапазон комірок B3:D18.

3.2 Форматування даних

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

Для завершення введення даних використовують кнопку Enter в рядку формул.

У робочій книзі створюємо додаткові аркуші, що мають назви «Формули», «Сортування», «Автофільтр», «Розширений фільтр», «Консолідація», «Підсумки», «Графік», «Гістограма», «Кругова діаграма».

Формат комірок і параметри тексту задаємо командою Формат > Ячейки, яка активізує одноіменне вікно із шістьма вкладками: «Число», «Выравнивание», «Шрифт», «Граница», «Вид» та «Защита»

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

У вкладці «Шрифт» задаємо шрифтове оформлення робочого аркуша:

· Для заголовка встановлюємо шрифт Bookman Old Style, розмір шрифту 14, напівжирний;

· Для підписів заголовків стовпчиків таблиці встановлюємо шрифт Arial, розмір 12, накреслення - напівжирний.

Для вмісту всіх інших комірок - шрифт Arial, розмір 10.

Вкладка «Число» використовується для вибору будь-яких форматів даних. Формат «Общий» використовується для відображення як текстових, так і числових даних довільного типу. Кращим для подання дійсних чисел із заданою точністю є формат «Числовой».

Формат «Дата» використовуємо для подання дати у діапазоні комірок В3:В22.

Для відображення формул на аркуші «Формули» обирамо пункт меню Сервис Параметры. На вкладці «Вид» встановлюємо прапорець у команді Параметры окна напроти Формулы.

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

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

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

3.3 Внесення формул в таблицю та їх копіювання

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

Для розрахунку знижки, що становить 7% від закупівельної ціни використовуємо формулу, яку вводимо до комірки E2:

E2 =D3*0,03

З метою уникнення помилок введення посилання на комірки можна включати за допомогою клацанням на неї лівою клавішею миші.

Аналогічно вводимо формули у відповідні комірки для розрахунку націнки, гуртової та роздрібної ціни, акційної ціни:

· Націнка = Закупівельна ціна*0,12=D3*0,12

· Гуртова ціна = Закупівельна ціна - Знижка =D3-G2

· Роздрібна ціна = Закупівельна ціна + Націнка =SUM(D3+F3)

В Excel формули можна копіювати з автоматичним налаштуванням їх за новим місцеположенням. Для цього використовуємо автозаповнення: наводимо курсор в правий нижній кут комірки і тягнемо його до останньої комірки діапазону. Таким чином заповнюємо комірки F4:F18, G4:G18, I4:I18, L4:L18.

3.4 Використання в таблиці стандартних функцій

В даній роботі використана стандартна функція ЕСЛИ.

ЕСЛИ(логическое выражение; значение_если_истина; значение_если_ложь)

Ця логічна функція визначає напрямок обчислень і відіграє роль оператора умовного переходу. ЇЇ перший операнд - логічний вираз, що набуває значення «істина» чи «хибність». Два інші операнди - це значення арифметичних виразів або рядкові константи. Вибір значення визначається істинністю чи хибністю першого операнда.

Для розрахунку вартості та акційної вартості використовуємо стандартну функцію ЕСЛИ з категорії «логічні» Майстра функцій.

Вартість = Кількість* Гуртова ціна, якщо Кількість перевищує 10 одиниць;

Вартість = кількість * Роздрібна ціна, якщо Кількість не перевищує 10 одиниць.



Операнди функції вводять у відповідні поля вікна вручну або напівавтоматично. Напівавтоматично вводять так: клацанням мишею на кнопці мінімізації з червоною стрілкою розміри вікна скорочують до розмірів вибраного поля, яке потім послідовно заповнюється компонентами його операнда. При цьому посилання на комірки вводять виділенням комірок у таблиці, константи і знаки порівняння - вручну, а роздільники - автоматично. Введення операнда завершується клацанням мишею на кнопці мінімізації його поля, а всієї функції - натисканням на клавішу <Enter> або за допомогою кнопки введення рядка формул.

4. Опис технології створення діаграм та графіків

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

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

Активізуємо Мастер диаграм за допомогою команд Вставка > Диаграмма.

1. Вибір типу діаграми (крок 1 із 4). На екран викликається діалогове вікно «Мастер диаграмм (шаг 1 из 4):тип диаграммы» з двома опціями - вкладками: «Стандартные» та «Нестандартные»

Обираємо тип діаграми - гістограма і натискаємо клавішу «Далее»

2. Вибір даних для побудови діаграми (крок 2 із 4). Відкривається діалогове вікно «Мастер диаграмм (шаг 2 из 4): источник данных диаграмм» із двома вкладками «Диапазон данных» і «Ряд». До поля «Диапазон» вводимо діапазон даних =Таблиця з розрахунками!$A$2:$J$18; =Таблиця з розрахунками!$A$2:$J$18 та у вкладці «Ряд» задаємо підписи рядів даних : Закупівельна ціна, Гуртова ціна, Роздрібна ціна.

3. Установлення параметрів діаграми (крок 3 із 4). Цю процедуру виконують в однойменному вікні «Мастер диаграмм (шаг 3 из 4): параметры диаграммы» із шістьма вкладками.

Вкладка «Заголовки» забезпечує ручне введення заголовків діаграми, заголовків осі Х (категорій) та осі Y (значень).

Вкладка «Легенда» дає змогу вмикати легенду та змінювати її положення щодо діаграми.

4. Розміщення діаграм (крок 4 із 4). Діаграму можна розміщувати на одному з аркушів робочої книги або на власному аркуші книги.

Розміри будь-якої діаграми змінюють «буксируванням» її кадрових маркерів у відповідних напрямках. Розташування діаграм на екрані можна змінювати за допомогою кнопок-піктограм вирівнювання тексту, а також «буксируванням» її кадру.

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

Аналогічно за допомогою Майстра діаграм будуємо графік «Порівняння знижки і націнки на окремі види товарів» та кругову діаграму вартості товарів і розміщуємо їх відповідно на аркушах «Графік» та «Кругова діаграма».

5. Фільтрація даних

5.1 Фільтрування даних за допомогою команди Автофільтр

1. Відкриваємо аркуш «Автофільтр» та копіюємо на нього таблицю з розрахунками.

2. Виділяємо діапазон даних А2:М18

3. Виконуємо команди Данные Фильтр Автофильтр

4. У графі «Найменування товару» обираємо «Чайник». В результаті ми отримали дані про цю групу товарів.

5.Відкриваємо аркуш «Автофільтр 2»

6.Виділяємо діапазон даних А2:М18

7. Виконуємо команди Данные Фильтр Автофильтр

8. У графі «Закупівельна ціна» обираємо «більше 670» та «менше 3600»

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



5.2 Фільтрування даних за допомогою команди Розширений фільтр

Технологія формування критеріїв фільтрів і результатів пошуку реалізується командою Данные - Фильтр - Расширенный фильтр, яка викликає на екран однойменне діалогове вікно, в якому задають:

· Режим оброблення бази даних

· Діапазон розміщення початкової бази даних

· Діапазон блоку критеріїв, де записуються вибрані користувачем критерії умови;

· Діапазон, куди потрібно внести результат пошуку

Виведемо дані про товари, ціни на які знаходяться у певному інтервалі (800-2000 грн.):

1. Відкриємо аркуш Розширений фільтр 1.

2. Копіюємо заголовок таблиці в 25 - й рядок.

3. Виділяємо діапазон А2:М18 та виконуємо команди Данные - Фильтр - Расширенный фільтр. У діалоговому вікні задаємо умови відбору даних та діапазон комірок, куди треба розмістити відсортовану таблицю



4. Натиснути <Enter>

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

Аналогічно виводяться дані про товари, ціни на які менші за середні.

6. Сортування даних

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

1. Скопіюємо таблицю з розрахунками на аркуш «Сортування»;

2. Виділяємо діапазон А2:М18;

3. Данные - Сортировка, у діалоговому вікні задаємо ключі сортування

4. Натискаємо <Enter>.

Таким чином ми отримали таблицю, відсортовану за такими ключами: Найменування постачальника, Закупівельна ціна, Вартість.

7. Проміжні підсумки даних

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

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

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

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

8. Інструкція по використанню робочої книги

Завантажити Excel.

Відкрити файл “Курсова Коротовських''

Даний файл має такі аркуші:

«завдання» - таблиця з розрахунками;

«формули» - таблиця з формулами;

«сортування» - таблиця після сортування даних;

таблиці після фільтрації - «автофільтр», «автофільтр2», «Розш.фільтр», «Розш.фільтр(2)»;

«підсумки» - таблиці з підсумками даних;

Листи “графік”, “діаграма”, “гістограма” - графічне представлення даних.

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

Редагувати дані натиснувши клавішу F2, попередньо виділивши клітину. Для видалення даних з клітини потрібно її виділити і натиснути Delete. Для виділення діапазону клітин слід протягти покажчик миші від одного кута діапазону до протилежного.

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

Вставлені рядки будуть такого самого формату, що й виділені. Щоб видалити рядки, необхідно їх виділити, відкрити меню Правка > Удалить. Всі посилання у формулах і іменах відповідно поновлюються.

Для того щоб перейти на інший аркуш, потрібно покажчик миші навести на ярлик потрібного аркуша, розміщеного в нижній частині документа і натиснути один раз лівою кнопкою миші [1].

Для збереження змін в книзі необхідно вибрати меню Файл> Сохранить або натиснути кнопку Сохранить на Стандартній панелі інструментів або на клавіатурі комбінацію клавіш Shift-F12.

Захист формул.

Виділити діапазон клітин, в яких потрібно приховати формули.

Команда головного меню Формат > Ячейки > Защита. Установити прапорець “Спрятать формулы”. Натиснути ОК.

Команда головного меню Сервис > Защита > Защитить лист.

В моїй курсовій роботі захист аркуша не використовувався.

Вихід з Excel: натиснути мишею кнопку в правому верхньому куті екрану або меню Файл > Выход.

Список використаної літератури

1. Інформатика: Комп'ютерна техніка. Комп'ютерні технології. Пос. /За ред. О.І. Пушкаря - К: Видавничий центр “Академія”, 2003.

2. Шовкопляс О.А., Стеценко Л.О. Методичні вказівки для виконання курсової роботи з курсу «Інформатика та комп'ютерна техніка». Ч. 1. Вимоги до виконання і оформлення курсової роботи. Приклад виконання курсової роботи. - Суми: Вид-во СумДУ, 2008. - 62 с.

3. Мережа Інтернет: http://uk.wikipedia.org/wiki/Excel

Размещено на Allbest