«Робота з табличним процесором Excel. Побудова таблиць. Графічне відображення табличних даних»

Практична робота

 

Тема: Робота з табличним процесором Excel. Побудова таблиць. Форматування таблиць та проведення розрахунків над табличними даними. Графічне відображення табличних даних.

Мета: відпрацювання практичних навиків роботи з табличним процесором Excel.

Завдання до роботи

1. Побудувати електронні таблиці, які реалізують наведені нижче документи (розмістити їх на першому, другому та третьому робочих аркушах відповідно, аркуші назвати Таблиця1, Таблиця2, Таблиця3, змінити колір ярличка аркуша):

Завдання 1. Створити таблицю, заповнити стовпчик “№ п/п” за допомогою засобу автозаповнення; підібрати шрифти та кольорову палітру для заголовку та шапки документу. Заповнити таблицю для 10-ти найменувань товару (придумати самостійно назви, кількість та ціну товару). Розрахувати вартість у ст.5.

Таблиця 1

Відомість обліку товару на складі №____

№ п/п	Найменування товару	Кількість, кг	Ціна, грн.	Вартість, грн.
1	2	3	4	5
				?
	Всього			?

 

Розрахункові формули: ст.5=ст.3*ст.4. Відпрацювати засіб "Автосумма" для показника "Всього".

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

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

 

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

Таблиця 1

Вартість партії товару
Назва товару	Кількість, шт	Ціна, грн.	Відсоток знижки	Сума знижки, грн.	Сума, грн.
Системний блок	4	15000	5	?	?
Монітор	6	5500	6	?	?
Принтер	4	6250	5	?	?
Модем	2	850	6	?	?
Клавіатура	4	350	8	?	?
Усього	?	?	-	?	?

•  установіть для таблиці автоформат „Классический 2”;

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

 

Завдання 3. На аркуші 3 побудувати графіки для наступних функцій:

Y=2*x², для -1<=x<2, k=0,2;

Y=x³+4*x-1, для 0<=x<=2, k=0,1;

Y=x+4*(x²/(x-3*x²)), для 1<=x<4, k=0,2.

Пояснення. Дані оформити у вигляді таблиці, де передбачити два стовпчика або рядка для змінної “х” та функції “Y”. За допомогою засобу автозаповнення і вказаного кроку “к” заповнити значення “х”, потім обчислити значення функції “Y” і тільки потім побудувати графік. Тип діаграми: Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров.

Зберегти файл книги у папці Таблиці (з вашої власної папки) під назвою Практична робота(ПІБ).

 

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

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

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

У рядку редагування формули є три кнопки: щиглик по [v] підтверджує внесені у формулу зміни, по [x] – скасовує, а кнопка [fx] дозволяє вставити функцію MS Excel. Вставка у формулу пробілів і розривів рядка не впливає на результат обчислень, однак не можна відокремлювати ім'я книги від імені листа й комірки. Розрив рядка вводиться комбінацією <Alt+Enter>. Для того, щоб видалити з комірки формулу, залишивши тільки повернуте їй значення, потрібно вибрати комірку, а потім послідовно натиснути <F2>, <F9> та <Enter>.

 

У формулах припустимі наступні операції:

+ Додавання

- Вирахування або знак мінус

* Множення

/ Ділення

^ Зведення в ступінь

& Конкатенація (тобто зчеплення рядків)

=  Логічне порівняння: дорівнює

< Логічне порівняння: більше ніж

> Логічне порівняння: менше ніж

<> Логічне порівняння: не дорівнює

>= Логічне порівняння: не менше ніж

<= Логічне порівняння: не більше ніж

Наведемо приклади формул робочого листа й виконувані формулами дії:

 

=3*2    Множить три на два

=А1+А2   Складає вміст комірок A1 й А2

= А2^2/3   Зводить вміст комірки А2 у ступінь 2/3

=СУММ(А1:А4)  Повертає суму значень із діапазону комірок А1:А4

(використовується вбудована функція СУММ).

=А1>А2 Повертає значення ИСТИНА, якщо вміст комірки А1 більше вмісту комірки А2, і повертає ЛОЖЬ в противному випадку

=Al&B1 Конкатенація (з'єднання) двох текстових рядків з комірок А1 й В1 (якщо в комірці A1 введене – Иван, а в B1 – Сидоров, то формула поверне значення ИванСидоров).

 

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

 

Рекомендації до виконання завдання:

Його виконання краще розбити на два етапи:

1-й етап. Створення таблиці.

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

• встановить поля, розмір і орієнтацію паперу (Файл → Параметры страницы…);
• виконавши команду Сервис → Параметры..., у групі перемикачів Параметры окна активізуйте перемикач Авторазбивка на страницы.

У результаті ви одержите у вигляді вертикальної і горизонтальної пунктирних ліній праву і нижню границі.

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

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

• Вирівняйте і відформатуйте шрифт заголовків (Times New Roman, 14, жирний) та підберіть ширину стовпців, змінюючи її за допомогою миші.
• Уведіть нумерацію в першому стовпці таблиці, скориставшись допомогою маркера заповнення.
• Розлінуйте таблицю, використовуючи лінії різної товщини.
• Встановіть для рядка з заголовками колір для комірок та шрифту на ваш вибір.

На цьому етапі бажано виконати команду Файл → Предварительный просмотр, щоб переконатися, що таблиця цілком уміщається на сторінці по ширині і всі лінії обрамлення на потрібному місці.

 

2-й етап. Заповнення таблиці.

Полягає в заповненні таблиці,  і використанні різних форматів числа.

• Заповніть стовпці використовуйте вставку символів Вставка – Символ і встановлення верхніх і нижніх індексів Формат – Ячейки – Шрифт.
• Установіть числовий  формат числа  в тих комірках таблиці, де будуть розміщені числа і розрахункові формули й установіть необхідне число десяткових знаків - 2. Для цього виконайте команду Формат → Ячейки...(перемикач Число).
• Заповніть стовпчики
• Уведіть формули для розрахунків по запропонованому зразку згідно з теоретичними даними до практичної роботи:

 

ПОБУДОВА ГРАФІКІВ І ПОВЕРХОНЬ. (Excel).

Теорія. Побудова графіків і діаграм в усіх програмах пакета MS Office виконуються за допомогою програми «Диаграмма Microsoft Graph», про яку було розказано при знайомстві з програмою Word. Наведена там послідовність дій цілком підходить для роботи в програмі Excel.

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

За допомогою "Мастера" будь-який графік (діаграма) будується в такій послідовності:

1.   Виділяється та частина таблиці, заданими якої будується графік.
2.   Викликається «Мастер диаграмм» (клацання по піктограмі).
3.   Виконується чотири кроки роботи з "Мастером".

У програмі Excel полегшено (у порівнянні з програмою Word) фор­матування графіків (діаграм). Для цієї мети є панель інструментів «Диаг­раммы», що містить кнопки: «Тип диаграммы», «Легенда», «Таблицадан-ных», «По строкам», «По столбцам», «Текст сверху вниз», «Текст снизу вверх», що значно полегшує трудомісткість побудови графіків (діаграм).

Відкрити файл Ехсеl

Завдання 1. За даними, наведеними у таблиці на листі "Завдання!", на листі "Приклад" побудувати графік з наступними па­раметрами:

1.   Дані для побудови - блок комірок ВЗ:І4.
2.   Тип графіка-«График с маркерами, помечающими точки данных».

3.   Додати до графіка - Лінію тренда у вигляді поліноміального гра­фіка, що описується рівнянням четвертого ступеня.

4.   Назва графіка - Січень.

Послідовність дій при виконанні завдання:

1. Скопіювати з листа "Задание 1" вміст блоку комірок ВЗ:І4 і встави­ти на лист "Приклад".
2. Викликати «Мастер диаграмм».
3. Вибрати на вкладці «Стандартныетип»-«График».
4. Вибрати у вікні «Вид» - «График с маркерами, помечающими точки данных».
5. Клацнути по кнопках: «Далее», «Далее», «Далее», «Готово».
6. Графік побудовано. Помітьте, що «Мастер диаграмм» правильно вибрав «Ряды в строках», правильно вибрав для графіка "Назва" - Січень. Залишається додати до графіка «Лінію тренда».
7. Клацнути правою кнопкою мишки точно по лінії графіка.
8. Вибрати в меню команду «Добавить линию тренда».
9. На вкладці «Тип» установити тип поліномінальний, задати ступінь - четвертий.

10. На вкладці «Параметры» активізувати опцію «Показывать уравнение на диаграмме», ОК.
11. На полі графіка з'явиться рівняння: у = 4,7197х⁴-75,515х³ + 374,92х²-58б,13х + 399.

12. Залишається відформатувати графік.

У завданні 1 за табличними даними було побудовано графік і знай­дено рівняння, що його описує. Можна вирішувати зворотну задачу: за наданим рівнянням побудувати графік.

 

Завдання 2. На робочому листі "Граф/я"побудувати графік функції, заданої рівнянням (для свого варіанта) з такими пара­метрами:

1. Тип діаграми: «Точечная со значениями, соединенными сглажи­вающей линией».
2. Легенду розташувати праворуч.
3. Лінія тренда - «Полиноминальная, третей степени, показать урав­нение на графике».
4. Дані для побудови взяти з наступної таблиці.

Послідовність дій при виконанні завдання для варіанта 0:

1.   Відкрити робочий лист "Графік".
2.   Створити таблицю X - Y.
3.   Рядок X заповнити значеннями відповідно до діапазону зміни х від -6 до 6 (див. таблицю на листі "Завдання 2").
4.   У рядку Y підрахувати значення у по рівнянню: у = 2х³ +4х² - 5х+2. Повинна вийти наступна таблиця:

Вказівки:

1.    На листі "Поверхня" створити таблицю, вигляд якої показано

нижче.

2. У таблицю ввести задані значення Х1 у такій послідовності:
3. В комірку А2 ввести мінімальне значення Х1.
4. В комірку A3 ввести мінімальне значення Х1 + Крок зміни Х1.
5. Виділити комірки А2 і A3.
6. Розмножити вниз так, щоб число значень відповідало заданому
7. Аналогічним чином ввести значення Х2.
8. У комірку В2 записати задану формулу рівняння "поверхні".
9. У формулу ввести символи абсолютних посилань на комірки.

10. Розмножити формулу в усі комірки таблиці.
11. Виділити всю таблицю.

За допомогою «Мастера диаграмм» побудувати поверхню за двома вимірами.

ПОБУДОВА ГІСТОГРАМ ІЗ НАКОПИЧУВАННЯМ. (Ехсеl).

У програмі Excel можна побудувати гістограму, дані для якої будуть братися всього з однієї комірки спеціально створеної таблиці - у цьому випадку вона буде іменуватися «гистограммой с накоплением». Така по­будова може знадобитись при реєстрації даних про кількість відвідувачів якогось заходу, наприклад, виставки.

Відкрити файл Excel

Завдання1.    Побудувати гістограму для реєстрації моменту приходу 100000 відвідувачів виставки.

Послідовність дій при виконанні завдання:

1.    Відкрити робочий лист «Гістограма».

2.   У підготовлену таблицю ввести дані й формули.
3.   У комірку С19 ввести число 100000.
4.   У комірку С20 =СУММ(СЗ:С17).
5.   Виділити комірку С22.
6.   Клацнути по кнопці «Мастер диаграмм».
7.   Вибрати тип діаграми «Гистограмма».
8.   Вибрати вигляд «гистограммы с накоплением» (середня у верхньому ряду).
9.   Два рази клацнути по кнопці «Далее».

10. Відкрити вкладку «Заголовок». У відповідне вікно ввести назв гістограми «Зріст кількості відвідувачів».
11. Відкрити вкладку «Оси» і відключити режим для осі X.
12. Відкрити вкладку «Легенда» і відключити зображення легенди.
13. Відкрити вкладку «Подписи данных» і в розділі «Подписи значений» включити режим «Значения».
14. Клацнути по кнопці «Готово».
15. Виконати подвійне клацання на стовпчику гістограми.
16. У вікні, що з'явилося, «Формат ряду даних» відкрити вкладку «Параметры» і встановити «Ширину зазора»-0.
17. Відкрити вкладку «Вид», натиснути кнопку «Способы заливки».
18. Вибрати «эффект градиентной заливки» двома кольорами, розташованими по вертикалі.
19. У зоні «Образец» вибрати лівий нижній шаблон.
20. Клацнути по кнопці ОК.
21. Виконати подвійне клацання на вертикальній осі координат.
22. У вікні, що з'явилося, «Формат оси» відкрити вкладку «Шкала» надати параметрам "Мінімальне значення" і "Максимальне значення " відповідно 0 і 1.
23. По закінченні настроювання клацнути по кнопці ОК.
24. Ввести кількість відвідувачів за чотири дні:

Гістограма повинна відобразити це таким чином