Суммесли(a2:a10;12;f2:f10)/f13

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

Тема: "Створення елементарних таблиць, побудова діаграм"

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

Лабораторна робота складається з трьох завдань, при виконанні яких студент одержує практичні навики роботи з MS EXCEL. Кожне завдання виконується на окремому листі книги. Книзі привласнюється ім'я – прізвище студента.

Після виконання всіх трьох завдань оформляється звіт наступної структури:

1. Титульна сторінка

2. Зміст

3. Завдання 1

4. Завдання 2

5. Завдання 3

Завдання 1. „Форматування таблиць, Автосума, Майстер діаграм”

1. Завантажити ЕХСЕL.

2. Дати назву листу 1: „Зарплата” (контекстне меню листа - "Переименовать"); задати колір ярличка: жовтий.

3. Створити таблицю "Відомість нарахування заробітної плати" наступного виду:

	A	B	C	D	E	F	G
	Відомість нарахування заробітної плати за січень 2009 року
	Прізвище та ініціали	Нарахо-вано, грн.	Прибутковий податок, грн.	Пенсійний фонд, грн.	Фонд соціального страхування, грн.	Всього утримано, грн.	До виплати, грн.
	Андрієвич Р.М.	2143,45	?	?	?	?	?
	Берзін О.М.	1156,35
	Вержба С.О.	988,32
	Захарченко С.І.	897,72
	Миренко П.Л.	3067,14
	. . .	. . .
	Всього		?	?	?	?	?

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

Таким же чином змінюється висота рядка.

Або при виділеному стовпчику виконати команду "Формат" -"Столбец" -"Ширина"

(при виділеному рядку виконати "Формат" -"Строка" -"Высота").

5. В таблицю додати прізвища (в комірки А8 : А13).

6. До колонки В ввести нараховані суми.

7. Ввести розрахункові формули утримань та суми до виплати для першого робітника:

§ прибутковий податок визначити в розмірі 15% від нарахованої суми, наприклад комірка СЗ задається формулою =ВЗ*15/100 (або =В3*0,15)

§ податки до пенсійного фонду 2%: наприклад комірка DЗ задається формулою =ВЗ*2/100 (або =В3*0,02)

§ до Фонду соціального страхування – 1% від нарахованої суми

§ всього утримано визначити як суму податків (підрахувати за допомогою кнопки "Автосумма" )

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

8. Введені розрахункові формули для першого працівника в списку скопіювати для решти працівників.

Для цього виділити діапазон чарунок СЗ:GЗ, мишу встановити на маркер автозаповнення, щоб покажчик миші прийняв вигляд +, натиснути ліву кнопку миші та, утримуючи її перетягнути на діапазон клітинок С4:G13

9. За допомогою кнопки Автосумма підрахувати загальні суми
нарахувань, усіх видів утримань та суму до виплати.

10. Побудувати діаграму, на якій відобразити види нарахувань та утримань будь якого робітника. Роботу здійснювати за наступною послідовністю:

• виділити діапазон комірок, в яких знаходяться початкові данні, включаючи підписи до них (це діапазон комірок А2:Е13)
• натиснути кнопку "Мастер диаграмм"
• вибрати тип діаграми "Гистограмма"- "Объемный вариант обычной гистограммы".
• відмітити, що данні знаходяться в стовпчиках
• задати назву діаграми «Співвідношення утримань з заробітної плати», вісь категорій – «Прізвища», вісь значень – «Суми»
• на вкладці "Легенда" встановити перемикач на рядок "справа"
• на наступному кроці встановити перемикач на рядок "Поместить диаграмму на имеющемся листе"

11. Здійснити форматування таблиці та підготувати до друкування.

§ Перейти до режиму перегляду сторінки: "Файл" – "Параметры страницы"–"Поля"та задати наступні параметри сторінки.

§ Додати верхній колонтитул.

§ Додати нижній колонтитул, в якому розмістити наступну інформацію:

ліворуч – номер групи; праворуч – прізвище та ім’я.

§ Перевірити зовнішній вигляд сторінки - ; якщо треба – відредагувати таблицю. Таблиця та діаграма мають розміститись на одній сторінці.

§ Роздрукувати завдання.

Завдання 2. „Статистичні функції, перевірка даних, абсолютна адресація, Автофільтр, функції ЕСЛИ, СУММЕСЛИ та СЧЕТЕСЛИ”

Відомі результати сесії (3 іспити).

§ Організуйте введення початкових даних так, щоб при введенні помилкових оцінок (менше за «2» або більше за «5») видавалося повідомлення про помилку.

§ Розрахуйте середній бал сесії по кожному студенту і по групі в цілому.

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

§ Розрахуйте ознаку заборгованості (1- є заборгованість, 0 – ні) для кожного студента, якщо хоч би по одному з предметів у нього є «2».

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

§ Підрахуйте кількість студентів із заданої групи та середній бал по групі в цілому (наприклад, 12).

§ Скласти список студентів-відмінників за допомогою Автофільтру

Порядок виконання завдання

1. Дати назву листу 2: „Сесія”; задати колір ярличка: блакитний.

2. Створити таблицю розрахунку за зразком:

	A	B	C	D	E	F	G	H
	Група	Прізвище	Філософія	ІКТ	Англ. мова	Середній бал	Відхилення від середн.	Заборгован.
		Андреев П.И.				?	?	?
		Борисенко О.Д.
		Верчко М.М.
		Зайцев С.С.
…	…	…..	…	…	…
		Всего	?		?
		Средний балл по группе 12	?
		Число студентов в группе 12	?

1. В таблицю в комірки B6 : B10 ввести прізвища cстудентів (додати прізвища).
2. Для організації введення даних з перевіркою, виділіть діапазон С2:Е11.

Виконайте пункт

"Данные"-"Проверка", виберіть "Тип даних"–"Целое число"

Значення між Мінімум 2 і Максимум 5.

На вкладці "Сообщение об ошибке" задайте

тип повідомлення -"Останов".

Введіть заголовок і текст повідомлення по вашому розсуду.

4. Для розрахунку середнього балу по кожному студенту встановити курсор в комірку F2, скористайтеся майстром функцій, виберіть категорію Статистичніі функцію СРЗНАЧ(). Аргументом даної функції є діапазон комірок С2:Е2. Скопіюйте формулу на діапазон комірок вниз.

5. Для розрахунку середнього балу по групі, в комірку F11 введіть формулу:

= СРЗНАЧ(F2:F10)

6. Для розрахунку відхилення від середнього балу в осередок G2 введіть формулу:

F2 - $F$12

Тут адреса $F$12 – абсолютна адреса, при копіюванні формули на діапазон комірок вниз він не змінюватиметься.

7. Для розрахунку ознаки заборгованості скористайтеся логічними функціями ЕСЛИ() і ИЛИ().

C допомогою майстра функцій в комірку Н2 введіть формулу:

=ЕСЛИ(ИЛИ(C2=2;E2=2;D2=2);1;0)

При введенні 1-го аргументу «Логическое_выражение», ми вибираємо із списку функцій (він знаходиться в лівій частині рядка формул) вкладену логічну функцію ИЛИ(). Заповнивши її аргументи, для коректного набору всієї формули і повернення у вікно функції ЕСЛИ() слід клацнути на ім'я функції ЕСЛИ() у рядку формул.

8. Для побудови графіка середнього балу по групі, виділіть з натиснутою клавішею CTRL діапазони В1:В10 і F1:F10, виконайте пункт "Вставка" – "Диаграмма" і, згідно з вказівками програми-майстра, побудуйте графік на окремому листі.

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

!Самостійно побудуйте діаграму, що відображають результати 1-го і 2-го іспитів.

9. Щоб визначити число студентів в заданій групі, наприклад в 12, в комірку F13 введіть статистичну функцію

= СЧЕТЕСЛИ(A2:A10;12)

Щоб визначити середній бал студентів в групі 12 в комірку F12 введіть функцію

СУММЕСЛИ(A2:A10;12;F2:F10)/F13

10. Здійснити форматування таблиці, додати колонтитули, підготувати до друку. На першій сторінці треба розташувати таблицю та діаграму з результатами 1 та 2 іспитів. На другій сторінці – три різних діаграми, створених в п.8. Роздрукувати завдання.

11. Скласти список студентів-відмінників за допомогою Автофільтру

§ активізувати комірку F1 - Середній бал

§ виконати команди Данные – Фільтр - Автофильтр

§ натиснути кнопку списку середніх балів та вибрати в ньому 5,0

§ переконатися, що в списку тільки відмінники

§ вимкнути режим автофільтрації, виконавши команди Данные – Фільтр – Автофильтр

12.

Роздрукувати список.

Завдання 3. „Функція ЕСЛИ, грошовий формат, сортування, підсумки”

У магазині введена знижка на товари.	10%, якщо сума покупки < 200 знижка = 15%, якщо 200<= сума покупки <=1000 20%, якщо сума покупки > 1000
Для покупців, що зробили покупки на суму нижче 200 грн. введена знижка у розмірі 10 відсотків. На суму від 200 до 1000 грн. знижка складе 15 відсотків від суми покупки, понад 1000 грн - 20 відсотків.
1). Визначте для покупців відсоток знижки, суму знижки і суму до сплати. 2). Знайдіть середнє значення суми до сплати. 3). Відсортуйте таблицю: а) по прізвищах покупців; б) по сумі до сплати 4). Побудуйте кругову діаграму по стовпцях 1 і 6. 5). Для кожної категорії знижок визначити загальний розмір знижки 6). Для кожної категорії знижок визначити кількість покупців, що одержали дану знижку.

Порядок виконання завдання

1. Дати назву листу 3: „Знижка на товари”; задати колір ярличка: зелений.

2. Скласти макет розрахунку.

Після аналізу завдання можна визначити наступні обов’язкові вхідні дані:

§ прізвище покупця (можливо ще ім’я та по батькові або ініціали);

§ номер кредитної картки;

§ сума покупки.

Вихідні дані для задачі:

§ відсоток знижки;

§ сума знижки;

§ сума до сплати;

§ середнє значення суми до сплати;

Макет розрахунку можна представити в наступному вигляді:

	A	B	C	D	E	F
	ПІБ покупця	Номер кредитної картки	Сума покупки	Знижка, %	Сума знижки, грн.	Сума до сплати
	Іванченко		150 грн.	?	?	?
	Соколов		200 грн.
	Сидоренко		715 грн.
...	. . .	. . .	. . .
	. . .
		Всього	?		?	?
			Середнє значення	?

Спочатку заповніть "шапку" таблиці і введіть початкові дані в стовпці А, В, С.

Додати дані по покупцях (в комірки А6 : С12).

Для стовпця „Сума покупки” необхідно ввести цифри, а потім вказати "Формат" – "Ячейки" та на вкладці "Число" вибрати формат "Денежный"

3. Розрахунки зробити за допомогою майстра формул.

Показник	Комірка, в яку вво-диться формула	Формула	Діапазон для копіювання формули
% знижки	D3	=ЕСЛИ(СЗ<=200;10;ЕСЛИ(СЗ<=1000;15;20))	D3:D12
суми знижки	ЕЗ	=C3*D3/100	Е3:Е12
суми до сплати	F3	=СЗ-Е3	F3:F12
підсумок для суми покупки	С13	= СУММ(С3:С12)
підсумок для суми до сплати	F13	= СУММ(F3:F12)
середнє значення суми до сплати	F14	= СРЗНАЧ (F3:F12)

8. Побудова діаграми для стовпців 1 і 6.

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

Натисніть на панелі інструментів кнопку "Мастер диаграмм" (або пункт меню "Вставка" - "Диаграмма").

За допомогою програми-майстра побудуйте діаграму. Розташувати діаграму на окремому листі.

Роздрукувати діаграму.

9. Здійснити форматування таблиці, додати колонтитули.

10. Сортування.

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

Виконайте пункт меню: "Данные" - "Сортировка"

а) Відсортуйте дані по стовпцю "ПІБ покупця". Роздрукувати результат.

б) Відсортуйте дані по стовпцю "Сума до сплати". Роздрукувати результат.

11. Виконати пункт завдання 5): для кожної категорії знижок визначити загальний розмір знижки і кількість покупців, що одержали дану знижку.

§ виділіть блок комірок A2:F12 та відсортуйте дані по стовпцю "Знижка, %" (4)

§ виконайте пункт меню: "Данные" - "Итоги"

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

12. Виконати наступне: для кожної категорії знижок визначити кількість покупців, що одержали дану знижку:

§ перш за все треба видалити попередні підсумки: : "Данные" - "Итоги" -

§ виділіть блок комірок A2:F12 та відсортуйте дані по стовпцю "Знижка, %" (4)

§ виконайте пункт меню: "Данные" - "Итоги"

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

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

Тема: " Розрахунки за допомогою формул ЕСЛИ, СУММЕСЛИ та СЧЕТЕСЛИ "

Лабораторна робота складається з 2 індивідуальних завдань, при виконанні яких студент закріплює практичні навички, які придбав при виконанні лабораторної роботи №1.