Вступ 4

Вид материала

Документы

Содержание Iv. варіанти завдань, методичні рекомендації та приклади виконання контрольної роботи заліковий модуль 1 Завдання роботи Варіанти завдань Варіант №10 Методичні рекомендації Майстер зведених таблиць. У списку або базі даних листа Microsoft| Excel. У зовнішньому джерелі даних, яким У іншій звідній таблиці

Подобный материал:

• Вступ, 323.48kb.
• Програма дисципліни кредитний модуль " основи радіоелектроніки" (для груп фф) Вступ, 153.44kb.
• Питания з Програми з курсу «Механіка», що виноситься на зм 1 вступ, 86.8kb.
• Робоча навчальна програма здисципліни: Принципи І методи аналізу художнього твору Спеціальність, 222.75kb.
• План, вступ, викладення змісту теми (як правило, 2 глави), висновок, а також список, 24.79kb.
• Вступ України до Світової огранізації торгівлі. Законодавчі зміни. Галузеві стратегії”, 72.41kb.
• Робоча навчальна програма Модуля Вступ до спеціальності Для студентів спеціальності, 404.17kb.
• Вступ до історії україни 5-й клас (35 годин), 86.86kb.
• Вступ до історії україни 5-й клас (35 годин), 104.36kb.
• М. В. Ломоносов; [вступ ст., сост., примеч. А. А. Морозова]. Ленинград : Сов писатель,, 249.18kb.

IV. ВАРІАНТИ ЗАВДАНЬ, МЕТОДИЧНІ РЕКОМЕНДАЦІЇ ТА ПРИКЛАДИ ВИКОНАННЯ КОНТРОЛЬНОЇ РОБОТИ

ЗАЛІКОВИЙ МОДУЛЬ 1

ЗАВДАННЯ 1

Тема. Системи табличної обробки даних. Табличний процесор MS Excel

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

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

Зміст роботи

1. Завантажити Excel.
   
2. Створити електронну таблицю відповідно до свого варіанту, ввести початкові дані в таблицю об'ємом 5 рядків, які містять повторення значень в полях по рядках і стовпцях (см. приклад в методичних рекомендаціях до виконання завдання). На окремі листи робочої книги скопіювати створену таблицю та виконати завдання п.п.2.1 - 2.8.
   
   1. Введіть за допомогою команди Форми не менш 20 записів.
      
   2. Скопіюйте на Лист 2 створену таблицю з уведеними вхідними даними(лист 1). Виконаєте сортування по одному або декількох полях.
      
   3. Скопіюйте на Лист 3 створену таблицю з уведеними вхідними даними(лист 1). Використовуючи команду Автофильтр зробіть вибірку записів по одній ознаці.
      
   4. Скопіюйте на Лист 4 створену таблицю з уведеними вхідними даними(лист 1). Виконаєте вибірку записів за критерієм, використовуючи команду Розширений фільтр.
      
   5. Скопіюйте на Лист 5 створену таблицю з уведеними вхідними даними(лист 1). Використовуючи команду ИТОГИ обчислити проміжні й остаточні підсумки для будь-якого числового поля.
      
   6. Скопіюйте на Лист 6 створену таблицю з уведеними вхідними даними(лист 1). Створіть зведену таблицю на основі наявної бази даних. Виконайте форматування за допомогою автоформату.
      

3. Побудуйте діаграму для зведеної таблиці. Виконайте її редагування та форматування
   
4. Виконати друк результатів виконання завдання 1 контрольної роботи.
   

Варіанти завдань


Варіант №1

Відомість нарахування націнки на товари

№ п/п	Найменування товару	Дата продажу	Один. вим.	Ціна	Кіль- ть	Вартість	% торгової націнки	Сума торгової націнки, грн.
1.	Цукор	12.09.2011	кг	2,90	300		10%
Разом						*		*

Варіант №2

Відомість нарахування заробітної плати

№ п/п	Ф.І.О	Дата прийняття на роботу	Тар. ставка	Кіль-ть відпрац. годин	Нараховано грн.	Премія		Всього грн.
						%	Сума, грн.
1.	Іванов П.І.	15.01.2001	12,35	162		8%
Разом					*		*	*

Варіант №3

Відомість утримань із заробітної плати

№ п/п	Ф.І.О	Дата виплати з/плати	Нараховано	Прибут. податок, 15%	Пенсійний фонд, 2%	Фонд безробіття, 0,6%	Всього утримано, грн.	Сума до виплати, грн.
1.	Сахно І. С.	10.10.2011	1342,18
Разом			*			*	*	*

Варіант №4

Аналіз реалізації товару

№ п/п	Найменування товару	Дата реалізації	Ціна	К-ть товару по плану	К-ть товару по факту	Вартість реалізованого товару План	Вартість реалізованого товару Факт	Відхилення, грн.
1.	Пальто	12.09.2011	785,89	21	18
Разом						*	*	*

Варіант №5

Розрахунок планового прибутку

№ п/п	Найменування виробу	Оптова ціна за ед. виробу, грн.	Планова собівартість од. виробу, грн.	Прибуток на од. виробу, грн.	К-ть виготовле-них виробів	Прибуток на весь випуск товару, грн.
1.	Кришка закочувальна	0,9	0,75		1300000
Разом					*	*

Варіант №6

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

№ п/п	Найменування товару	Дата надход-ження	Ціна, грн.	Залишок на початок місяця	Посту- пило	Реалі-зовано	Залишок на кінець місяця	Сума залишку на кінець місяця, грн.
1.	Каструля	12.06.11	130,56	12	45	34	*	*
Разом				*	*	*	*	*

Варіант №7

Відомість нарахування відпускних

№ п/п	Ф.І.О	Дата виходу у відпустку	З/плата за 6 місяців	К-ть відпр. днів за 6 місяців	Середньоден-ний заробіток, грн.	К-ть днів відпустки	Сума відпускних, грн.
1.	Малахова С.П.	05.07.11	4276,70	120		28
Разом							*

Варіант №8

Відомість нарахування заробітної плати

№ п/п	Ф.І.О	Дата прийняття на роботу	Оклад	К-ть планів. днів	Серед-ньоденна з/плата, грн.	К-ть відпр. днів	Нарахо-вано, грн.	Премія 12%	Всього грн.
1.	Ярий м.І.	04.03.1997	1250	22		21
Разом							*	*	*

Варіант №9

Платіжна відомість за проведені роботи

№ п/п	Об'єкт	Дата проведення робіт	Найменування роботи	Вартість роботи, грн.	ПДВ 20%, грн.	Податок 5%, грн.	До оплати
1.	Смак	12.07.07	Фарбування	1345,89	*	*	*
Разом				*	*	*	*

Варіант №10

Витратна відомість

№ п/п	№ цехи	Ф.І.О кому видана запчастина	Найменування запчастини	Дата видачі	Вартість одиниці, грн.	Кількість, шт	Сума, грн.
1.	2	Смірнову м.І.	Труба	18.11.2006	23,67	5	*
Разом							*

Методичні рекомендації

та приклад виконання завдання 1 контрольної роботи


Поняття про звідні таблиці

Для усестороннього і ефективного аналізу даних великих таблиць в Excel використовуються зведені таблиці (ЗТ). Функції роботи зі ЗТ відносяться до одного з наймогутніших інструментів Excel|.

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

Застосовувати інструмент ЗТ рекомендується для великих таблиць, де є різні повторення значень в стовпцях і (або) рядках.

ЗТ є “тривимірними”, оскільки до звичних атрибутів таблиць: рядок і стовпець, додається атрибут - сторінка (на основі початкової таблиці з даними по 12 місяцям, можна створити ЗТ, що містить, наприклад, 13 сторінок; “відкриваючи”, спеціальним чином, будь-яку з 12 сторінок, можна проглянути дані якогось одного місяця, а на 13 сторінці - сумарні дані за всі місяці відразу).

Майстер зведених таблиць.

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

На першому кроці пропонується вибрати джерело даних для побудови таблиці. Створити ЗТ можна на основі даних, що знаходяться в одному з нижченаведених джерел (рис. 1).

• У списку або базі даних листа Microsoft| Excel. Під списком розуміється таблиця, що має заголовки стовпців тільки в одному першому рядку. Якщо список містить загальні і проміжні підсумки, їх потрібно видалити, щоб не включати в ЗТ.
  
• У зовнішньому джерелі даних, яким може бути база даних, текстовий файл або будь-яке інше джерело, окрім книги Microsoft Excel.
  
• У декількох діапазонах консолідації, тобто в декількох областях одного або більш листів Excel. При цьому списки і листи повинні мати однакові заголовки рядків і стовпців.
  
• У іншій звідній таблиці, яку можна використовувати для створення на її основі нової ЗТ. Нова ЗТ буде пов'язана з тими ж початковими даними. При оновленні даних ЗТ - джерела, інша ЗТ також оновлюється.
  

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


Рисунок 1. Майстер зведених таблиць крок 1 з 3


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


У загальному випадку повне ім'я інтервалу задається у вигляді|виді|:

[имя_ книги]имя_листа!интервал ;

Якщо ЗТ будується в тій же книзі, де знаходиться початкова таблиця, то ім'я книги указувати не обов'язково.


Рисунок 2. Майстер зведених таблиць крок 2 з 3


На третьому кроці визначається місце розтащування зведеної таблиці (рис.3) та в макеті таблиці в режимі створення (рис.4) створюється структура ЗТ і визначаються її функції. Макет представлений в центрі вікна і складається з областей: рядок, стовпець, сторінка і дані. Праворуч від макету відображаються всі імена полів (заголовки стовпців) в заданому інтервалі початкової таблиці.





Рисунок 3. Майстер зведених таблиць крок 3 з 3





Рисунок 4. Майстер зведених таблиць - макет


Розміщення полів в певну область макету виконується шляхом їх “перетягання” при натиснутої лівої кнопки миші. Щоб видалити поле з області макету, його перетягують за межі макету. Видалення поля приведе до видалення зі ЗТ всіх залежних від нього величин, але не вплине на початкові дані.

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

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

Рядок - поля цієї області формують заголовки рядків ЗТ; якщо таких полів декілька, то вони розміщуються в макеті зверху вниз, забезпечуючи групування даних ЗТ за ієрархією полів, де для кожного елементу зовнішнього поля, елементи внутрішнього поля повторюються;

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

Сторінка - поля в цій області виступають як фільтри і дозволяють проглядати “на окремих сторінках” дані ЗТ, відповідні різним значенням поля, поміщеного в цю область; поле сторінки в режимі перегляду розташовуються на два рядки вище за основну частину ЗТ в крайньому лівому стовпці. У сусідній справа клітці виводиться (Все) і поряд розкриваюча стрілка для вибору іншого елементу поля; у області сторінка може бути розміщене декілька полів, між якими встановлюється ієрархічний зв'язок, - зверху вниз;

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

 

Приклад виконання завдання 1.

1. Завантажуємо Ms Excel
   
2. Будуємо таблицю (рис. 5)
   
   1. вводимо в таблицю дані, використовуючи команду Дані\Форма. У графі "Вартість" вводимо формулу для розрахунку.
      

Рисунок 5. Введення даних за допомогою Форми

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

Рисунок 6. Автоформатування та сортування даних таблиці.

3. Виконуємо копіювання початкової бази даних на Лист 3. Виконуємо вибірку даних використовуючи користувальницький фильт(Дані\Автофильтр). Вибираємо дані по найменуванню товару, залишивши в таблиці тільки споживачів какао.
   

Рисунок 7. Результат роботи автофильтру


2.4. Виконуємо копіювання початкової бази даних на Лист 4. Виконуємо вибірку даних за допомогою розширеного фильтру(рис. 8): вибираємо дані, вартість товару у яких становить менше 1000 грн.





Рисунок 8. Виконання розширеного фильтру

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

2.5.1. Знайти максимальне значення ціни на цукор і какао та середнє значення кількості товару по кожній группі товару .

Рішення:

1. Скопіювати базу даних з Листа 1 на Лист 7. Виконати сортування по полю "Найменування товару"
   
2. Активізуємо команду Данные\Итоги.
   
3. У діалоговому вікні "Промежуточные итоги" уводимо наступні параметри:
   
   • у полі При каждом изменении в, у списку що відкривається вибираємо назву графи, по якій підбиваємо підсумок – Найменування товару(попередньо відсортувавши);
     
   • у поле Операция, у списку вибираємо відповідну функцію – максимум;
     
   • у поле Добавить итоги по активізуємо перемикачі тих полів, значення яких необхідно обчислити – Ціна.
     

При розрахунку двох і більше операцій(наприклад потрібно ще підрахувати середнє значення по кількості товару) знову активізують команду Данные\Итоги. У діалоговому вікні, наведеному вище встановлюють необхідні параметри, при цьому перемикач Замінити поточні підсумки повинен бути неактивним.

У результаті база даних буде мати такий вигляд: