Натисніть кнопку ENTER

6 Натисніть кнопку ENTER.

7 Щоб запустити запит з Microsoft Query, виберіть Виконати запит .

Щоб передати таблицю результатів у Microsoft Excel, виберіть вихід з поверненням даних.

5. Побудова зведених таблиць, звітів

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

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

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

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

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

1. Створення зведеної таблиці

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

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

Якщо зведена таблиця створюється на основі даних, що знаходяться в чи списку базі даних Microsoft Excel, виділите осередок списку чи бази даних.

2 Виберіть команду Зведена таблиця в меню Дані.

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

При створенні зведеної таблиці можуть використовуватися різні джерела даних:

¨   Створення зведеної таблиці на основі даних, що знаходяться в списку чи в базі даних Microsoft Excel

¨   Створення зведеної таблиці на основі даних, що знаходяться в зовнішніх джерелі даних

¨   Створення зведеної таблиці на основі даних, що знаходяться в декількох діапазонах консолідації

¨   Створення зведеної таблиці на основі даних, що знаходяться в іншій зведеній таблиці

2. Підведення підсумків і обробка даних зведеної таблиці

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

¨   · Підсумкова функція використовується для виконання основних обчислень (наприклад, «Сума», «Середнє», «Мінімум» і «Максимум»). Щоб одержати опис доступних підсумкових функцій, натисніть кнопку .

¨   · Додаткові обчислення використовуються для чи порівняння індексування, а також для обчислення часткою.

¨   · Поле, що обчислюється, чи елемент, що обчислюється, поля використовується для створення користувальницької формули, що обробляє дані зведеної таблиці.

3. Звіти

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

Наприклад, якщо існують сценарій «Кращий випадок», сценарій «Гірший випадок» і два різних користувальницьких режими «Підсумки» і «Подробиці», те існує можливість створення звіту зі сценарієм «Кращий випадок» і видом «Подробиці» і звіту зі сценарієм «Кращий випадок» і видом «Підсумки».

Створення підсумкового звіту

1 Виберіть команду Сценарії в меню Сервіс.

2 Натисніть кнопку Звіт.

3 Установите перемикач у положення чи Структура Зведена таблиця.

4 В поле Осередку результату введіть ссыл

Діалогові вікна «Додати звіт» і «Редагувати звіт»

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

Ім'я звіту  Щоб привласнити звіту назва, наберіть послідовність символів у поле Ім'я звітів.

Роздягнув для додавання Щоб створити розділ звіту, установите значення полів у наборі полів Роздягнув для додавання.

Лист Щоб задати лист активної книги, що повинний бути використаний у розділі звіту, виберіть його зі списку Лист.

Вид Щоб додати в розділ вид, виберіть його зі списку Вид.

Сценарій Щоб додати в розділ сценарій, виберіть його зі списку Сценарій.

Додати Щоб додати поточний розділ у список Розділи в цьому звіті, натисніть кнопку Додати.

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

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

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

Послідовна нумерація сторінок Установите наскрізну нумерацію сторінок звіту. Якщо даний прапорець не встановлений, то кожен розділ звіту нумерується з 1.

Видалити Щоб видалити розділ, обраний зі списку Розділи в цьому звіті, натисніть кнопку Видалити.

2. ПРАКТИЧНА частина 1. Постановка задачі та її формалізація

За завданням необхідно сформувати базу даних про ринок холодильників у м. Дніпропетровську.

1. Загальна характеристика предметної області

Була зібрана інформація про роздрібний продаж холодильників звертаючи увагу на таки ознаки:

·     магазин, де проводіться роздрібна торгівля,

·     виробник холодильника,

·     назва товару,

·     ціна холодильника,

·     кількість камер в холодильнику.

Крім цього зверталася увага на зовнішній вигляд холодильника, а саме на колір, особисто якщо він суттєво відрізнявся від загально прийнятого – білого.

В розробленій Базі Даних проведені:

·     розрахунок середньої ціни товарної продукції і розкид цін,

·     розрахунок середньої ціни товарної одиниці холодильника різних марок і з різною кількістю камер,

·     розрахунок кількості одиниць асортименту у різних продавців.

2. Проектна частина 1. Опис технологічного процесу збору й опрацювання інформації

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

Таблиця Технологічний процес збору й опрацювання інформації

№	Найменування етапу	Зміст етапу
1.	Збір і реєстрація інформації	Джерело інформації - прайс-лісти магазинів: 1.    Электро Мир, пр. Карла Маркса, 46 2.    АБВ техника, пр. Карла Маркса, 81 3.    LG, пр. Карла Маркса, 109 4.    Whirlpool, ул. Артема, 1
2.	Контроль вводу інформації	Метод контролю – візуальний. Здійснюються перевірка відповідності веденної з клавіатури інформації вихідним даним.
3.	Накопичення і збереження інформації про предметну область	Накопичення і збереження інформації здійснюється на машинному носії у файлі формату MS Excel.
4.	Пошук і опрацювання даних	Пошук і опрацювання даних зроблені засобами MS Excel.
5.	Контроль опрацювання даних	Контроль опрацювання даних проведене методом вибіркового прорахунку окремих розмірів (контрольний приклад).
6.	Вивід результатів опрацювання даних	Вивід результатів опрацювання даних зроблено на друкувальний пристрій.
7.	Передача інформації	Результати опрацювання даних у друкарському виді передаються споживачу.
8.	Ухвалення рішення	Провадиться споживачем на основі аналізу отриманих результатів опрацювання.

Рис. 1. Блок - схеми алгоритму процесу опрацювання інформації

2. Опис вхідних даних і бази даних

В якості вхідних даних використовувалася інформація з власних спостережень і з прайс-листів магазинів.

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

В базі відображено 69 записів.

В базу даних занесена інформація про кожну одиницю товару, яка містить такі ознаки:

·     номер один по одному,

·     магазин, де проводіться роздрібна торгівля,

·     виробник холодильника,

·     назва товару,

·     ціна холодильника,

·     кількість камер в холодильнику.

База сформована засобами MS Exсel у виді двомірної таблиці.

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

У стовпчиках таблиці інформація розташована в такому порядку:

Стовпчик А - номер один по одному,

Стовпчик B - магазин,

Стовпчик С - виробник,

Стовпчик D - назва товару,

Стовпчик Е - ціна холодильника,

Стовпчик F - кількість камер в холодильнику,

Стовпчик G - додаткова інформація

Дані розміщені на листі книги MS Excel, який має назву "Холодильники".

Інформація про записи в базі даних частково відображені на рис. 2. Повний зміст бази даних подано в Додатку А.

	A	B	C	D	E	F	G
1	№	Магазин	Виробник	Назва товару	Ціна, грн	Кількість камер	Додаткова інформація
2	1	1	1	ARF 191/NE	2 645 грн.	1	Бар - холодильник
3	2	1	2	KSR 3895	2 471 грн.	1
4	3	1	2	KGS 3760 IE	3 712 грн.	2	корпус: червоний
5	4	1	2	KSF 3202	4 267 грн.	3

Рис. 2. Записи в базі даних

Дані про магазини і виробників холодильників розташовані на листі "Розрах". Вид таблиць з даними про магазини і виробників приведені в підрозділі де описуються розрахунки.

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

Опрацювання даних виконане засобами MS Excel.

На листі "Розрах" представлені розрахунки:

·     середня ціна товарної одиниці холодильнику,

·     розкид цін для холодильників,

·     середня ціна холодильнику за кількістю камер для різних виробників,

·     кількість одиниць асортименту в різних магазинах.

Для тих виробників для яких не спостерігалися холодильники з якоюсь кількістю камер стоїть знак "-".

На листі "Лист3" представлена інформація яка була оброблена за допомогою автофільтру. Дані згруповані по виробниках і за кількістю камер в холодильника. Повний вигляд згрупованих даних на "Лист3" приведено в Додатку Б.

3. Розрахункова частина. 1. Опис засобів MS Excel, які використовувалися при розрахунках

По перше, був використовуване автофільтр по стовпцях С – Виробник і F – Кількість камер.

Використовувалися такі функції MS Excel:

·     СУММ(число1;число2; ...)

·     МАКС (ячейка1:ячейка2)

·     МІН (ячейка1:ячейка2)

·     СЧЁТЕСЛИ(интервал; критерий)

·    СРЗНАЧ (ячейка1:ячейка2)

Функція СУММ(число1;число2; ...) підсумовує всі числа в інтервалі.

·     Число1, число2, ... - це від 1 до 30 аргументів, для яких потрібно визначити суму.

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

·     Якщо аргумент є масивом чи посиланням, то тільки числа враховуються в масиві чи посиланні. Порожні осередки, логічні значення, тексти і значення помилок у масиві чи посиланні ігноруються.

·     Аргументи, що є значеннями помилок текста, не перетвореними в числа, викликають помилки.

Функція МІН повертає найменше значення з набору даних і має такий синтаксис:

=МІН(число1;число2;...)

·     Число1, число2,...- це від 1 до 30 чисел, серед яких шукається мінімальне значення.

·     Можна задавати аргументи, що є числами, порожніми осередками, логічними значеннями або текстовими уявленнями чисел. Аргументи, що є значеннями помилки або текстами, не що перетворяться в числа, викликають значення помилок.

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

·     Якщо аргументи не містять числа, то функція МІН повертає 0.

Функція МАКС повертає найбільше значення з набору даних і має такий синтаксис:

=МАКС(число1;число2;…)

 Значення аргументу задаються таким же уявою як і у функції МИН.

Функція СЧЁТЕСЛИ(интервал; критерий) підраховує кількість осередків усередині інтервалу, що задовольняють заданому критерію.

·     Интервал - це інтервал осередків, що обчислюються.

·     Критерий - це критерій у формі числа, чи вираження у віді тексту, що визначає, яка осередок додається. Наприклад, критерій може бути виражений як 32, "32", ">32", "яблука".

·     Сум_интервал - це фактичні осередки для підсумовування. Осередки в сум_інтервал сумуются, тільки якщо відповідні осередки в аргументі Интервал задовольняють Критерий. Якщо сум_інтервал опущений, то сумуются осередки в аргументі інтервал.

Функція СРЗНАЧ обчисляє середнє арифметичне значення, сумуючи ряд числових значень із наступним розподілом результату на кількість значень. Ця функція має такий синтаксис:

=СРЗНАЧ(число1;число2;...)

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

2. Розрахунок середньої ціни холодильника і розкид цін

Розрахунок середньої ціни холодильника і розкид цін проведені на листі "Розрах" і мають такий вигляд як на рис. 3.

Середня ціна товарної одиниці холодильнику				2 612,61 грн.
Розкид цін		мінімальна	максимальна
		1 687,00 грн.	5 589,00 грн.

Рис. 3.

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

Середня ціна товарної одиниці холодильнику				=СУММ(Холодильники!E3:E71)/69
Розкид цін		мінімальна	максимальна
		=МИН(Холодильники!E:E)	=МАКС(Холодильники!E:E)

Рис. 4.

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

Розрахунок середньої ціни холодильника різних виробників проведені на листі "Розрах" і мають такий вигляд як на рис. 5.

№	Виробник	Середня ціна холодильнику
		кількість камер
		1	2	3
1	Whirlpool	1 906,17 грн.	2 593,60 грн.	-
2	Bosch	2 471,00 грн.	3 484,08 грн.	3 710,50 грн.
3	Samsung	-	3 111,00 грн.	-
4	Zanussi	1 845,00 грн.	2 404,20 грн.	-
5	LG	-	2 698,10 грн.	-
6	Indesit	-	1 916,00 грн.	-
7	Electrolux	-	5 589,00 грн.	-
8	Ardo Co	-	2 111,33 грн.	-

Рис. 5.

У режимі формул ці розрахунки мають вигляд як на рис. 6.

№	Виробник	Середня ціна холодильнику
		кількість камер
		1	2	3
1	Whirlpool	=СРЗНАЧ(Лист3!E3:E8)	=СРЗНАЧ(Лист3!E9:E18)	-
2	Bosch	=СРЗНАЧ(Лист3!E20)	=СРЗНАЧ(Лист3!E21:E32)	=СРЗНАЧ(Лист3!E33:E34)
3	Samsung	-	=СРЗНАЧ(Лист3!E36)	-
4	Zanussi	=СРЗНАЧ(Лист3!E38:E42)	=СРЗНАЧ(Лист3!E43:E52)	-
5	LG	-	=СРЗНАЧ(Лист3!E54:E63)	-
6	Indesit	-	=СРЗНАЧ(Лист3!E65:E69)	-
7	Electrolux	-	=СРЗНАЧ(Лист3!E71)	-
8	Ardo Co	-	=СРЗНАЧ(Лист3!E73:E78)	-

Рис. 6.

За даними таблиці приведеної на листі "Розрах" побудована діаграма (рис. 7.), яка розміщена на листі "Діаграми"

Рис. 7. 4. Розрахунок кількості одиниць асортименту холодильників у різних продавців

Розрахунок кількості одиниць асортименту холодильників у різних продавців проведені на листі "Розрах" і мають такий вигляд як на рис. 8.

№	Магазин	Адреса	Кількість одиниць асортименту
1	Электро Мир	пр. Карла Маркса, 46	18
2	АБВ техника	пр. Карла Маркса, 81	27
3	LG	пр. Карла Маркса, 109	11
4	Whirlpool	ул. Артема, 1	13

Рис. 8.

У режимі формул ці розрахунки мають вигляд як на рис. 9.

	№		Магазин		Адреса		Кількість одиниць асортименту
1		Электро Мир		пр. Карла Маркса, 46		=СЧЁТЕСЛИ(Холодильники!$B:$B;A23)
2		АБВ техника		пр. Карла Маркса, 81		=СЧЁТЕСЛИ(Холодильники!$B:$B;A24)
3		LG		пр. Карла Маркса, 109		=СЧЁТЕСЛИ(Холодильники!$B:$B;A25)
4		Whirlpool		ул. Артема, 1		=СЧЁТЕСЛИ(Холодильники!$B:$B;A26)

Рис. 9.

За даними таблиці кількості одиниць асортименту холодильників у різних продавців, приведеної на листі "Розрах" побудована діаграма (рис. 10.), яка розміщена на листі "Діаграми".

Рис. 10.

Висновки

За розрахунками, що було проведено і які приведені на рис. 3, 5, 8 та за діаграмами рис. 7, 10 можна зробити такі висновки:

1.       середня ціна холодильника складає 2 612,61 грн.;

2.       мінімальна ціна становить 1 687,00 грн. для холодильника ART 200, виробник Whirlpool, магазин "АБВ техника";

3.       максимальна – 5 589,00 грн. для холодильника Electrolux ER, виробник Electrolux магазин "АБВ техника";

4.       на ринку основну частину складають холодильники, які мають дві камери;

5.       найбільший асортимент холодильників спостерігався в магазині "АБВ техника", пр. Карла Маркса, 81.

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

1.        Блатнер П., Ульрих Л., Кук К. Использование Excel 2000. Специальное издание. -М., К., СПб. 2000.

2.        Карпов Б. Microsoft Office 2000 Справочник. - СПб.: Питер, 2000.

3.        Кириллов В.В. Структуризованный язык запросов (SQL). – СПб.: ИТМО, 1994. – 80 с.

4.        Мартин Дж. Планирование развития автоматизированных систем. – М.: Финансы и статистика, 1984. – 196 с.

5.        Тиори Т., Фрай Дж. Проектирование структур баз данных. В 2 кн., – М.: Мир, 1985. Кн. 1. – 287 с.: Кн. 2. – 320 с.

6.        Хаббард Дж. Автоматизированное проектирование баз данных. – М.: Мир, 1984. – 294 с.

7.        Цикритизис Д., Лоховски Ф. Модели данных. – М.: Финансы и статистика, 1985. – 344 с.

8.        Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичный курс інформатики. - К.: Фенікс, 1997.

9.        Ахметов К., Борзенко А. Современный персональный компьютер. - М.: Компьютер-пресс, 1995.

10.     Визе Макс, Word для Windows. - М.: Биком, 1998.

11.     ДоджМ., Кината К., Стинсон К. Эффективная работа с Excel 7.0 для Windows - СПб.: Питер, 1996.

12.     Фигурнов В.Э. IBM PC для пользователей. – Изд. 7-е. – М.: ИНФРА, 1997.

13.     Колесников А. Windows 98: русифицированная версия – К.: BHV, 2000.

14.     Стоцкий Ю. Office 2000: самоучитель–СПб.: Питер, 2000.

15.     Электронный офис. В 2-х тт./ Каратыгин С. и др. – М.: Нолидж, 1999.

16.     Йорг Шиб. Windows: сотни полезных советов. - М.: Бином, 1996.

17.     Левин А. Самоучитель работы на компьютере. - М.: Нолидж, 1999.

18.     Мартик Альтхауз Михаэль Орлет, Excel 7.0. - М.: Биком, 1998.

19.     Ботт Эд. Использование Microsoft Office 97. - К.: Диалектика, 1997.

20.     Николь Н., Альбрехт Р. Электронные таблицы Excel 7.0. -М.: ЭКОМ., 1998.

21.     Николь Н., Альбрехт Р. Электронные таблицы Excel 7.0 для квалифицированных пользователей. -М.: ЭКОМ., 1998.

22.     Пасько В.П. Word 7.0 для Windows – К.: BHV, 1998.

23.     Новиков Ф.А., Яценко А. Microsoft Office в целом–СПб.: BHV-Санкт-Петербург, 2000.

24.     Пробитюк А. Excel 7.0 для Windows в бюро. -К., BHV, 1997.

25.     Стинсон К. Эффективная работа в Windows 98. - СПб.: Питер, 1998.

26.     Шаффмайстер У., Пасько В. Word 7.0 для Windows в бюро. -К., BHV, 1998.