13436

СТВОРЕННЯ ЗВЕДЕННИХ ТАБЛИЦЬ В MICROSOFT EXCEL

Лабораторная работа

Информатика, кибернетика и программирование

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОЇ РОБОТИ №6 СТВОРЕННЯ ЗВЕДЕННИХ ТАБЛИЦЬ В MICROSOFT EXCEL 1. Мета роботи Придбати практичні навички по створенню і використанню зведених таблиць в Microsoft Excel. 2. Задачі роботи Опанувати прийоми формування редагування зміни та

Украинкский

2013-05-11

354.5 KB

29 чел.

МЕТОДИЧНІ ВКАЗІВКИ ДО ЛАБОРАТОРНОЇ РОБОТИ №6

СТВОРЕННЯ ЗВЕДЕННИХ ТАБЛИЦЬ В MICROSOFT EXCEL

 

1. Мета роботи

Придбати практичні навички по створенню і використанню зведених таблиць в Microsoft Excel.

2. Задачі роботи

Опанувати прийоми формування, редагування, зміни та аналізу зведених таблиць.

3. Зміст роботи

При підготовці до даної роботи необхідно створити, на базі раніше виконаних таблиць, початкову таблицю об’ємом 30 рядків і 5-10 стовпців, що містить повторення значень в полях по рядкам і стовпцям (див. приклади в Додатках)

3.1 Завантажити Microsoft Excel і відкрити файл зі створеною таблицею.

3.2 На окремих  аркушах робочої книги  створити зведені таблиці  по завданням п.п.6.1 - 6.8.

3.3 Показати викладачу результати роботи, створивши файл і скласти звіт.

4. Зміст звіту

Звіт повинен включати:

назву і номер роботи, відомості про виконавця;

постановку задачі і відомості про послідовність її  виконання;

відповіді на контрольні питання п.7 вказівок;

таблиці з розрахунками.

До звіту додається дискета з виконаним завданням.

5. Загальні положення

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

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

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

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

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

 

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

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

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

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

У зовнішньому джерелі даних, котрим може бути база даних, текстовий файл, або будь-яке інше джерело, крім книги Microsoft Excel.

У декількох діапазонах консолідації, тобто в декількох областях одного чи більше аркушів Microsoft Excel. При цьому списки і аркуші повинні мати однакові заголовки рядків та стовпців.

У іншій зведеній таблиці, яку можна використовувати для створення на її основі нової ЗТ. Нова ЗТ буде пов’язана з тими ж вихідними даними. При обновленні даних ЗТ - джерела, нова ЗТ також обновляється.

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

5.2.2. На другому кроці в діалоговому вікні  вказується інтервал комірок списку, або бази даних, які повинні зводитись. В загальному випадку повне ім’я інтервалу що задається  у вигляді:

[ім’я_ книги]ім’я_аркушу!інтервал;

Якщо ЗТ будується  в тій же книзі, де знаходиться вихідна таблиця, то ім’я книги вказувати не обов’язково. Для вказівки інтервалу з іншої закритої робочої книги натискується кнопка „Прогорнути”, далі, в діалоговому вікні що відкриється, вибирається диск, каталог і файл закритої книги, уводяться ім’я робочого аркушу і інтервал комірок.

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

Рис. 6.1.  Можливий  варіант макету зведеної таблиці

Розміщення полів в певній області макету виконується шляхом їх „перетягування” при натиснутій лівій кнопці миші.

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

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

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

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

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

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

„Дані” – обов’язково обумовлена область для розміщення полів, по яким підводяться підсумки, відповідно обраній підсумковій  функції; розміщені тут поля можуть бути довільних типів.

 

Приклад 1.

Завдання: По вихідній таблиці „Облік кількості дерев” (Додаток, табл.1) побудувати ЗТ,  згрупувавши дані по породі, № ділянки та віку дерев. Підрахувати кількість дерев в зазначених групах.

Виконання: В макеті  (рис. 6.1) в область „рядок” розміщені два поля: „порода дерева” і „Nп.п. ділянки”, в область „стовпець - поле „Вік”. В області „дані”: „Сума” по полю „Кількість”. Готова ЗТ (рис. 6.2) виводиться на аркуші робочої книги Microsoft Excel в режимі перегляду, де комірки, що містять імена  полів, виділені темним кольором. На перехресті рядків з номерами ділянок  (котрі повторюються для кожної породи дерев) і стовпців з віком, виводяться величини, що означають загальну кількість дерев даного віку і породи дерев, що ростуть на даній ділянці. По зовнішньому полю „Порода” виводяться проміжні підсумки („Дуб всього”, „Клен всього” і т. ін.) і загальні підсумки по рядкам і стовпцям.

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

Дана ЗТ стане більш компактною і це дозволить окремо переглядати дані по кожній породі, якщо перемістити поле „Порода” в область „Сторінка”.

 

Рис. 6.2. Зведена таблиця до прикладу 1

 

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

Рис. 6.3.  Фрагмент зведеної таблиці з полем в області сторінка

 

У зведених таблицях можна обчислити і представити 11 різних підсумкових функцій, таких як „Сума”, „Рахунок” та інші. Крім того для кожного поля області „дані” можна задати одне з 9 додаткових обчислень, котрі задають спосіб представлення чисел (наприклад, замість абсолютних значень можна здійснити виведення процентної величини цих значень по відношенню до певного підсумку).

Для настроювання параметрів полів використовуються діалогові вікна двох типів із загальною назвою „Поле зведеної таблиці”, елементи яких дозволяють:

змінити вихідне ім’я поля, формат числа і т. ін.;

вказати тип підсумків що формуються по значенню поля;

видалити поле з макету і т. ін.

На рис. 6.4 представлений приклад вікна для настроювання параметрів поля „дані, в яких кнопка „Параметри >>” відкриває додаткове поле списку „Показати дані у вигляді” (рис. 6.5) для завдання додаткових обчислень. 

 

Рис. 6.4. Вікно „Поле зведеної таблиці” для настроювання поля області дані

Приклад 2.

Завдання: По даним таблиці „Баланс ресурсів і використання м’яса і м’ясопродуктів” (Додаток, табл.2) визначити: як у процентному відношенні змінився загальний об’єм ресурсів за 1994-1996 рр.? 

Виконання: У зв’язку з тим, що загальний об’єм ресурсів по кожній області знаходиться в стовпці „Підсумок”, потрібно створити ЗТ з обчисленням  суми  по полю „Підсумок” для кожного окремого року і визначити процентну різницю отриманої суми років 1995 і 1996 від 1994.  В макеті в область „сторінка” потрібно помістити поле „Області”, в область „рядок”: „Роки”, в  область  „дані” – „Сума” по полю „Підсумок”; задати додаткове обрахування: „ % різниці від” по  полю „Роки”,  базовий елемент: 1994 (рис. 6.5).

 

Рис. 6.5. Фрагмент вікна „Поле зведеної таблиці”, в котрому задається додаткове обчислення „ % різниці від”

На рис. 6.6а представлена ЗТ, що відображає сумарні значення по полю „Підсумок” за окремо взяті роки. В таблиці на рис. 6.6б  використане додаткове обчислення „% різниці” отриманої суми років 1995 і 1996 від 1994 для поля „Підсумок” області „дані”. По даним цієї ЗТ видно, що загальний об’єм ресурсів по всім областям  зменшився в 1995 році на 6,2%, а в 1996 році на 14,37% в порівнянні з 1994 р. За допомогою стрілки що розкривається поля „Області” можна переглянути аналогічні дані по будь-якій області, що входить в вихідну таблицю.

 

                                а)                                                                  б)

Рис. 6.6. Зведені таблиці для прикладу 2

5.2.4 На четвертому кроці  вказується початкова комірка для вставки ЗТ і деякі додаткові параметри ЗТ. В полі „Початкова комірка” вказується координата лівого верхнього кута ЗТ, або на поточному аркуші, або на іншому аркуші поточної робочої книги, або в іншій раніше відкритій робочій книзі. Якщо адреса початкової комірки не визначена, ЗТ створюється на новому робочому аркуші поточної робочої книги починаючи з комірки А1. В цьому ж вікні можна визначити потрібні чи ні в ЗТ „Загальні підсумки по стовпцям і рядкам”, а також інші параметри,  які по умовчанню відзначені хрестиком, тобто включені.

5.4. Редагування зведеної таблиці

Редагувати ЗТ можна, як в режимі перегляду, так і повернувшись, в режимі створення таблиці. Змінам в таблиці можуть піддаватись:

 структура (додавання нових полів, видалення існуючих, зміна місцезнаходження поля);

 тип використовуваної функції);

додаткові обчислення.

Сукупність усіх способів редагування в різних режимах зводиться до наступних дій:

перетягування назви полів у відповідну область;

виклик команди „Дані” головного меню;

звернення до контекстно-залежного меню  поля;

подвійного клацання на імені поля;

клацання на потрібній кнопці панелі інструментів „Запит і зведення”.

Панель інструментів „Запит і зведення” з’являється на екрані монітора автоматично після побудови ЗТ, або викликається командою головного меню „Вид  Панелі інструментів”. За допомогою кнопок панелі інструментів „Запит і зведення” можна швидко переміститись в діалогове вікно 3 із 4 „Майстра зведених таблиць” (тобто для зміни структури таблиці), відкрити діалогове вікно „Поле зведеної таблиці”; відобразити всі сторінки поточної ЗТ на окремі аркуші поточної книги і т. ін..

 

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

6.1. Для створення ЗТ виконайте команду „Дані „Зведена таблиця”. На першому кроці в діалоговому вікні „Майстра зведених таблиць” виберіть опцію „В списку або базі даних Microsoft Excel”. На другому кроці, щоб вказати інтервал  комірок, перейдіть на Лист1, виділіть в ньому, за допомогою миші, всю таблицю разом із рядком заголовків стовпців (в цьому випадку Excel автоматично введе у вікно „діапазон” назву аркуша і виділені комірки) і клацніть лівою кнопкою на кнопці „Крок>”. Розмістіть в макеті по одному полю в областяхрядок, „стовпець” і „дані”. Для поля області „дані” задайте „підсумкову функцію Середнє”, настанови діалогового вікна на четвертому кроці залиште без змін. 

Примітка: При побудові ЗТ використовувати поля вихідної таблиці на власний розсуд, або по указівці викладача, однак таким чином, щоб ЗТ робила аналіз даних вихідної таблиці більш зручним і ефективним.

6.2. Повернувшись в режим створення за допомогою кнопки    панелі інструментів (курсор повинен знаходиться у будь-якій комірці ЗТ), добавити до створеної зведеної таблиці поле в область „сторінка” і клацнути лівою кнопкою миші на кнопці „Закінчити”.

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

6.4. На наступному аркуші книги створити нову зведену таблицю, в якій розмістити в областірядок”  два поля вихідної таблиці.

6.5. В режимі перегляду змінити тип підсумку для зовнішнього поля області „рядок. Змінити тип підсумку можна у вікні „Поле зведеної таблиці”.

6.6. Виключити загальні підсумки по стовпцям і рядкам зведеної таблиці, перейшовши за допомогою команди „Дані Зведена таблиця” і „Крок>” на четвертий крок „Майстра зведених таблиць”.

6.7. На  новому аркуші книги створити зведену таблицю з двома полями в області „сторінка”. Для поля області „дані” задати підсумкову функцію і додаткові обчислення на свій розсуд. Для установки додаткових обчислень, потрібно клацнути на кнопці „Параметри >>” вікна „Поле зведеної таблиці”.

6.8. Відобразити „усі сторінки” одного з полів області „сторінка” на окремих аркушах за допомогою кнопки  панелі інструментів і переглянути отриманні аркуші робочої книги.

 

7. Контрольні питання

7.1. Що розуміють під терміном „зведені таблиці”?

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

7.3. З яких кроків складається Майстер зведених таблиць?

7.4. Як задати інтервал комірок початкової таблиці для створення ЗТ?

7.5. Які області складають макет таблиці?

7.6.  Що таке поле-фільтр, як його задати, як використовувати?

7.7. Які способи існують для зміни структури ЗТ?

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

8.1. Зацерковний В.І. Конспект лекцій по дисципліні „Інформатика і програмування” Чернігів ЧДЕІУ -2007, ч.III Microsoft Excel;

8.2 Информатика практикум по технологии работы на компьютере/ Под ред. проф.  Н.В.Макаровой. -М.: Финансы и статистика, 1997. С.308-313

8.3 Н.Николь, Р.Альбрехт Электронные таблиці Excel 5.0 для профессиональных пользователей: Практ. Пособ./Пер. с нем. - М.: ЭКОН., 1995, С.146-160

8.4 Андрей Пробитюк EXCEL 7.0 для Windows 95 К.: Торгово-издатялинаское бюро BHV, 1996,С.167-186

Додатки

  Таблиця1

  

        Облік кількості дерев

  

N п/п ділянки

Порода

Вік, років

Висота, м

Площа обліку, м2 

Кількість дерев, шт.

1

дуб

2

2,25

25

35

1

дуб

3

0,35

25

109

1

клен

2

0,5

25

6

1

липа

10

2,5

50

3

1

ялина

5

0,3

50

5

2

дуб

3

0,35

25

98

2

дуб

5

0,6

10

56

2

дуб

7

0,78

10

22

2

дуб

10

1,1

25

2

2

липа

3

0,41

25

14

3

дуб

10

1,2

25

1

3

ялина

7

0,85

15

1

3

клен

15

3

25

2

3

липа

10

2,5

10

4

3

липа

15

3,1

25

4

4

ялина

6

0,54

25

3

4

ялина

7

0,9

25

2

4

клен

5

1,55

25

1

4

липа

7

1,7

25

5

4

липа

15

3

50

4

5

дуб

3

0,31

25

24

5

клен

2

0,4

10

10

5

клен

5

3

10

7

5

липа

15

3,1

25

21

6

дуб

2

0,23

15

21

6

ялина

7

0,85

15

2

6

липа

7

2

30

5

6

липа

10

2,5

25

4

6

липа

11

2,4

25

14

  

  

  

  

  

  


 Таблиця 2

Баланс ресурсів м’яса і м’ясопродуктів (в перерахунку на м’ясо), тис. тонн

Роки

ОБЛАСТІ

Запаси на початок року

Виробництво

Ввіз, в тому числі імпорт

Всього

Виробничі потреби.

Втрати

Вивіз, в тому числі експорт

Особисті потреби

Залишки на кінець року

1994

Сумська область

17,8

93,9

5,5

117,2

0,5

0,8

34

68,6

13,3

1994

Чернігівська область

27,5

124,4

11,1

163

1,1

1,5

17,4

127,2

15,8

1994

Київська область

29,9

136,3

24,9

191,1

0,5

0,6

1,1

164

24,9

1994

Полтавська область

54,1

284,4

17,4

355,9

0,9

1,2

0,5

286,5

66,8

1994

М. Київ

1236,2

6803,3

1631

9670,5

161,7

68,6

21

8388,9

1030,3

1994

Вінницька область

25,7

158,1

91,8

275,6

0,4

0,7

2,6

250,8

21,1

1994

Закарпатська область

18,3

101,3

2,4

122

1,6

0,2

4

100,8

15,4

1994

Одеська  область

199,3

1062,7

211

1473

5,8

5,5

68,8

1213,8

179,1

1994

Львівська область

26

154,3

67,9

248,2

0,8

0,5

9,2

215,9

21,8

1995

Сумська область

13,3

80,6

5,6

99,5

0,3

0,6

18,1

68,4

12,1

1995

Чернігівська область

15,8

120,9

20,6

157,3

1,7

1,4

13,4

127,8

13

1995

Київська область

24,9

116,3

31,5

172,7

0,2

0,3

0,4

155,1

16,7

1995

Полтавська область

66,8

267,4

12,5

346,7

1,4

1,7

5,5

279,8

58,3

1995

М. Київ

1030,3

5795,8

2246,9

9073

134,7

46,2

13,3

8086,8

792

1995

Вінницька область

21,1

136,9

107,4

265,4

0,6

0,7

2,3

246

15,8

1995

Закарпатська область

15,4

90,3

5,9

111,6

1,7

0,1

7,2

90,9

11,7

1995

Одеська  область

179,1

949,9

251,9

1380,9

6,7

5,5

49,3

1172,8

146,6

1995

Львівська область

21,8

137,5

68,4

227,7

0,8

0,7

2,4

204,8

19

1996

Сумська область

12,1

82,3

4

98,4

0,4

0,6

20,5

67

9,9

1996

Чернігівська область

13

98

16

127

0,7

1,1

7,4

109,2

8,6

1996

Київська область

16,7

98,9

48,3

163,9

0,2

0,1

4

147,2

12,4

1996

Полтавська область

58,3

263,1

11,7

333,1

0,5

1,7

18,9

252,6

59,4

1996

М. Київ

795,5

5335,8

2112,9

8244,2

102,4

41,6

34,9

7448,4

616,9

1996

Вінницька область

15,8

134,2

97,7

247,7

0,5

0,6

3,3

230,8

12,5

1996

Закарпатська область

11,7

81,9

9,7

103,3

1,5

0,2

6,8

83

11,8

1996

Одеська  область

146,6

873,2

259,7

1279,5

4,5

4,6

66

1077,2

127,2

1996

Львівська область

19

114,8

72,3

206,1

0,7

0,3

5,1

187,4

12,6

 

PAGE  47


 

А также другие работы, которые могут Вас заинтересовать

66363. ENGLISH MARATHON RACES 49.5 KB
  We have two teams, representing 7”A” and 7”B” forms. They are to pass successfully a number of tasks. Then according to their results we will define the winner, that’s the best and the smartest team. To cope with this task we have a commission of strict jury consisting of...
66364. English Learners’ Party 37 KB
  Presenter 1: Dear teachers, pupils and guests, we are happy to see you here. Welcome to our English Party. Presenter 2: By the way, do you know why the pupils go to school? P1: Maybe to study school subjects? P2: Well, only this? P1: Of course not. To meet their friends!
66366. Математична естафета 95 KB
  Мета проведення: сприяти розвитку полікультурних та комунікативних компетентностей учнів; стимулювати інтерес і зацікавленість до вивчення математики та до підтримки особистої спортивної підготовки на достатньому рівні.
66367. Кожен творець свого щастя 57.5 KB
  Мета уроку: Допомогти дітям усвідомити розуміння тогощо для кожної людини поняття щастя неповторнещо кожна людина може сама творити своє власне щасливе життя. Показати на літературних прикладах та ситуаціях із життящо щастя в кожному із насщо вміння поділитися ним з іншимиробить людину...
66368. Взаєморозуміння — основа дружнього колективу 39 KB
  Кого ви бачите у дзеркальці Вам подобається зображення Чому Поміняйтесь дзеркальцем із товаришем. Змінилось зображенняяке ви бачите А у вашого товариша Чому Кожен з нас неповторний несхожий на іншого має свою зовнішність характер здібності чесноти і недоліки.